I’ve come across a very neat trick in Oracle that I thought I’d share. Its useful for situations where you can have circular references in the data diagram, for example when the table includes a column which is a reference for another entry in the same table. Usually it would be necessary to write some function which could be called recursively and allow you to traverse this tree layout, however in Oracle there is the CONNECT BY PRIOR syntax.

The users and groups example

Sticking with my earlier example let’s say I’ve got an employees table and the employee can have a manager, which is an employee

users

select first_name || ' ' || last_name, level
from users
connect by prior id = manager
start with id = 1

Will output the list of people followed by their reports. We can distinguish who is where in the tree using the magical column “level” which is available when we connect in this way. I’ve used the level to indent the name column by two spaces for each level down the tree the person is and to show their job title:

select lpad('  ', (level - 1) * 14) || first_name || ' ' || last_name, job_title
from users
connect by prior id = manager
start with id = 1

Today I’m working on a complicated database driven web page. I edit the source using vim on the main file server. In order to get the file onto the webserver, I then quit out of the file and a script checks my syntax and ftps the file into the correct location on the webserver. Once its there, my prompt returns and I know its time to refresh the web page. Which is interesting, but if you are still reading then you must be wondering what my point is.

Its this, the file check and write takes too long. Its not ages, its like, seven seconds. But its long enough for me to jump to the other window (or in this case, tab) and read the next headline in my feedreader, or the message in my chat window … and you can guess what happens next! I read the article, or reply to the message, or whatever. About three minutes later (if I’m lucky), I’ll remember to refresh the page, check my changes and go on with the task in hand.

This is why all programmers need fast hardware and efficient tools to develop with. My waiting attention span just isn’t long enough to wait more than about three seconds for compile/upload/reload/whatever else is needed. I think I can multitask but actually I end up distracting myself completely!

Speaking of which, my upload is finished ….

I need to operate on a big table with lots of records, but only on rows where the primary key starts with a number. Because obviously its a good idea to differentiate types of records in this way …. only joking! Its just one of those little tasks that crops up from time to time.

The Fast and Dirty Way

select code
from products
where substr(code,1,1) in (0,1,2,3,4,5,6,7,8,9)

Oracle Regular Expression Method

select code
from products
where regexp_like(code,'^[[:digit:]]')

Further Resources

I got this from this article on technology and also the definition of some of the character classes from this very helpful resource on psoug

I came across this when reading an article on Mike’s site yesterday:

_“The best advice I ever received from a manager is that it is easier to get forgiveness than permission.”

The article is at Top 3 Ways to Fight Bureaucratic Inertia in IT and it makes for interesting reading. More than once I’ve had projects cancelled or stalled indefinitely because of two superiors’ inability to agree on the requirements and Mike’s article has given me some ideas on how to manage this type of situation in the workplace.

Its a funny thing but the smallest details when you are looking for a job are actually the most important. I’ve never actually asked to see inside the ladies when I’ve been at a job interview but I’ve always wished I had once I got the job!

I’ve been a big fan of Joel Spolsky for some time, but he posted an advert to his site today that had me riveted. He’s looking for a sysadmin for Fog Creek, and in the advert he writes

“We’re still a small friendly company and we all eat lunch together every day at one big table.”

This is what I’m looking for in a company, this sums up my requirements for my entire working life, and he’s nailed it in a single sentence.

The search functionality works!!

I have PHP 5.1.2 on a Windows XP machine (I know, I know, its my work desktop) and Zend Framework preview release 1.3. I’m using it to index the files in a local copy of DokuWiki – this is an easy starting point as the pages are text files and all held in directories.

zend_lib.php
I have a library file which has some settings used both when building the index and when searching it. This file also includes a function which turns the url of the returned page into a path that dokuwiki can understand.

zend_text.php
The indexing page starts from $startpoint (set in zend_lib.php) and recurses into directories indexing file size, url and content of each file.

zend_text2.php
The search page shows some searching tips and a search box. When a search is performed it then prints links to the pages in dokuwiki and their relevance, ordered by relevance.

I’m so excited that I’m including the files here for now and might think about turning this into a proper dokuwiki plugin – although, personally I like their current search functionality, this current exercise is a PHB requirement. Here are the files:

zend_lib.php

zend_text.php

zend_text2.php

I work with vim and its fabulous. Although I’ve been a casual linux shell user for some years, I’ve never had to get to grips with vim as my main editor which I’m using eight hours a day until now. And I love it.

Cheat Sheets

Its vital to get a good cheat sheet to start with. This is like a menu of commands to remind you how to do things. Then when you think “wouldn’t it be cool if this program did …”, you can look up how to do it (and I guarantee vim has the feature you wanted, whatever it is. Some of my favourite tricks not always listed on cheat sheets are:

gv reselect your most recent selection
% when on a bracket (either ( or { ), jump to its partner

For more cheat sheets, probably best to look on my del.icio.us page (here and linked in left hand bar) as I keep my favourite links of the moment updated there

Colour Syntax

I have finally managed to get my vim working with colour highlighting which is making my life much easier (and prettier, of course). I’m running vim 6.2 on AIX 5.3 and found that the only way to get my vim into colourful mode was to turn on the syntax and set my terminal to dtterm.

To change your terminal type, at the prompt type:

export TERM=dtterm

Then when you do

echo $TERM

it should tell you that your term type is now dtterm. Unfortunately the change in terminal type made my function keys stop working (argh – see my earlier post on this topic). As a compromise I have aliased vim to set the terminal type when it runs, by adding the following line to my .kshrc file (if you’re running bash then add it to .bashrc instead)

alias vim=’vim -T dtterm’

Arrow keys

I normally use h,j,k,l to navigate in vim (left, down, up and right respectively), but I get stressed by the cursor not wrapping at the end of the lines. I googled for the problem and found that adding this to my .kshrc helped:

set -o emacs
alias __A=$(print ’\0020’) # ^P = up = previous command
alias __B=$(print ’\0016’) # ^N = down = next command
alias __C=$(print ’\0006’) # ^F = right = forward a character
alias __D=$(print ’\0002’) # ^B = left = back a character
alias __H=$(print ’\0001’) # ^A = home = beginning of line

Regex

Regular expressions in vim are more powerful than I can imagine, and I’m loving the find-and-replace, especially because you can use the pattern you matched in your replacement expression by typing \0 as part of the expression. Its so powerful.

For my next trick, I will figure out how to group parts of my pattern to use bits of them in the replacement – I feel a tutorial coming on.

I’ve been getting wound up recently by Opera spamming my apache logs with errors about missing favicon.ico files. So here’s some instructions for removing this annoying default behaviour:

http://groups.google.co.uk/group/opera.general/browse_thread/thread/601683ed17b42762/ac5685ea6a310180?lnk=st&q=opera+favicon+request+error&rnum=1#ac5685ea6a310180

Symptom

You’ll spot the problem because there will be lines in the apache error.log file which look like this (this error is from a windows machine)

File does not exist: C:/www/favicon.ico, referer:

Normally, on a public website, I’d ignore this unless you do have a favicon set up. However I’m developing locally and so its my copy of Opera that is causing this crud in the files.

I made some progress with getting Xapian set up, but not to the point where I was all ready to go. I’m not all that familiar with C++ and somehow I lost the will to live somewhere along the way this afternoon.

PHPDig

I should mention PHPDig here because it is a really good product and widely used. However a mysql database is a no-no, as I mentioned earlier.

This leads me back around to ….

ZSearch

The Lucene implementation in the Zend Framework for PHP 5. Wish me luck!

EDIT You can read the follow-up post here

Well, TikiWiki claims to support Oracle … great! So I’ll install it, and try it.

(insert comedy failure noise here)

The installation doesn’t work! Mostly because you can only name oracle things with names of less than thirty characters in length, and this product doesn’t respect that when installing on Oracle, so action is needed.

Here’s the file of corrected statements I ran to get all the tables created successfully and also reinstate triggers and indexes that failed (I’m not promising its perfect). Where I needed to modify a correlating php page, that’s documented as well. I hope this helps someone in the future – me, next time I need to do this, perhaps?

tiki_installation_corrections.txt