Creating BETTER auto-increment columns in Oracle

Posted on 2009-01-21 by Earl C. Ruby III

I was explaining to a developer today how Oracle doesn’t support auto-increment columns for IDs and how you have to implement your own. I wanted to point him to a tutorial but I found that most of the tutorials have PL/SQL code with unexplained side effects. So I thought I’d post some Oracle PL/SQL code that behaves just like a MySQL or PostgreSQL auto-increment column, but which also fixes the sequence number when an application provides its own ID number for a record, which is one place where MySQL and PostgreSQL autoincrement columns fail.

Step 1: Know the names of your tablespaces. Know which tablespace you want to use for your tables and which tablespace you want to use for your indexes. Don’t assume that Oracle is going to put the table in the right place. To get a list of available tablespaces:

select tablespace_name from user_tablespaces;

Step 2: Create your table. In this example the table is built in the tablespace TDATA and the primary key index is built in the tablespace INDX.

create table example (
    id number not null,
    name varchar2(30),
    constraint example_pk primary key(id) using index tablespace INDX
) tablespace TDATA;

Note that by declaring the id column “not null” you can’t add a record with an empty ID field, ever.

Creating the primary key constraint on a separate line allows us to name the constraint, which can be handy when you’re looking through the data dictionary later on trying to figure out which constraint does what.

Step 3: Create a sequence. In this case I create an ID sequence that starts at 1 and goes on from there. If you want to start your ID sequence with a different number, or increment by some other amount, change these values.

create sequence example_id_seq start with 1 increment by 1;

Step 4: Create a trigger. This is what does the actual work setting the ID number. There are many ways to do this, here are two:

Method 1: Use a sequence-generated ID for all IDs.

create or replace trigger example_insert
before insert on example
for each row
begin
    select example_id_seq.nextval into :new.id from dual;
end;
/

Method 1 will always use a sequence-generated ID no matter what. For example:

SQL> insert into example (id, name) values (100, 'Earl');
1 row created.
SQL> insert into example (name) values ('Cleopatra');
1 row created.
SQL> insert into example (name) values ('Frankenstein');
1 row created.
SQL> select * from example;
        ID NAME
---------- ------------------------------
         1 Earl
         2 Cleopatra
         3 Frankenstein

In this case the trigger-supplied ID 1 overwrote the user-supplied id 100.

Method 2: Allow users to supply their own IDs when they want to.

create or replace trigger example_insert
before insert on example
for each row
declare
    max_id number;
    cur_seq number;
begin
    if :new.id is null then
        -- No ID passed, get one from the sequence
        select example_id_seq.nextval into :new.id from dual;
    else
        -- ID was set via insert, so update the sequence
        select greatest(nvl(max(id),0), :new.id) into max_id from example;
        select example_id_seq.nextval into cur_seq from dual;
        while cur_seq < max_id
        loop
            select example_id_seq.nextval into cur_seq from dual;
        end loop;
    end if;
end;
/

Method 2 will use the sequence-generated ID if the insert statement doesn’t supply an ID, which is what MySQL and PostgreSQL do. It also updates the sequence so that the next value of the sequence won’t collide with IDs supplied by an insert statement.

For example, let’s say I’m using Trigger Method 2 and just loaded the table with a bunch of old data, including old ID numbers.

SQL> delete from example;
3 rows deleted.
SQL> insert into example (id, name) values (200, 'Cleopatra');
1 row created.
SQL> insert into example (id, name) values (300, 'Frankenstein');
1 row created.
SQL> insert into example (name) values ('Earl');
1 row created.
SQL> select * from example;

        ID NAME
---------- ------------------------------
       200 Cleopatra
       300 Frankenstein
       301 Earl

The last record added gets an ID of 301.

Hope you find this useful.

Foxmarks – Firefox Bookmark Synchronization

Posted on 2008-10-27 by Earl C. Ruby III

My desktop boxes at work and home both run Ubuntu Linux, I have a Mac Mini attached to my TV and a Mac Powerbook for when I travel. If I’m headed to my company’s co-lo I take along a company laptop running openSUSE Linux. On all of these systems I run Firefox as my primary web browser.

Until recently I used a tool called BookmarkBridge to try to keep my ever-growing list of bookmarks somewhat synchronized. I say “somewhat” because the the process is manual, requiring copying the bookmark files to one computer, doing the merge, and then copying the files back out to the individual computers that need to be updated. Unfortunately the last time I tried using BookmarkBridge to merge bookmarks from multiple computers I ended up with a file of all of the bookmarks from one computer — the new bookmarks from the other two computers were over-written.

I needed a better solution.

A few months ago I found a better solution when I discovered Foxmarks, a Firefox plugin that keeps all of your bookmarks automatically synchronized all of the time. If I add a bookmark on one computer it shows up on the other four. If I move a bunch of bookmarks into the same folder on one machine the bookmarks show up in the same folder on all of my other machines. No fuss, no muss. Brilliant.

It’s really easy to install and use. Open up Firefox, go to the Foxmarks web site and click the install button on their home page. When you restart Firefox you’ll be asked to create a Foxmarks user account (name and password) and you’ll create your initial set of bookmarks from your browser’s current set of bookmarks.

To sync your bookmarks with another computer running Firefox just install the Foxmarks plugin on the second computer. This time when you restart Firefox enter your Foxmarks user name and password when prompted, then decide if you want to merge the bookmarks on the second computer with your current bookmarks or if you want to replace the bookmarks on the second computer with the bookmarks currently stored in your Foxmarks account.

That’s it. Once its set up your Firefox bookmarks on both computers will remain in sync all of the time.

At this time — Foxmarks only supports Firefox. If you need to merge bookmarks with a copy of IE, Safari, Opera, Seamonkey or Konquerer, you’re out of luck.

If you don’t like the idea of storing your bookmarks on someone else’s system you can run your own Foxmarks server. Foxmarks should run with any WebDAV server and is also compatible with the Chandler Server (Cosmo).

Hope you found this useful.

Change your Skype availability status on Ubuntu via cron

Posted on 2008-10-10 by Earl C. Ruby III

I used Skype when it first came out and then stopped because there were only a few other people I knew who used it regularly. Since they were all in my local calling area, if I wanted to talk to any of them for free I could just use a phone.

I just started using it again — for work — to talk with customers and consultants who do not live in the Bay Area. However, I found that my Thursday telecommute days were causing problems: Skype would be running on my work computer and people trying to call me would get ring-no-answer because I was actually at home.

Turns out you can start and stop Skype via cron the same way you can start and stop Pidgin Instant Messenger via cron, you just need to set a few environment variables and it works great.

First create the script ~/bin/export_x_info which looks like this:

#!/bin/bash
# Export the dbus session address on startup so it can be used by cron
touch $HOME/.Xdbus
chmod 600 $HOME/.Xdbus
env | grep DBUS_SESSION_BUS_ADDRESS > $HOME/.Xdbus
echo 'export DBUS_SESSION_BUS_ADDRESS' >> $HOME/.Xdbus
# Export XAUTHORITY value on startup so it can be used by cron
env | grep XAUTHORITY >> $HOME/.Xdbus
echo 'export XAUTHORITY' >> $HOME/.Xdbus

Create this script, type chmod 700 ~/bin/export_x_info so you can execute it, then execute it, then add it to System > Preferences > Sessions > Startup Programs so it will execute every time you start your computer and record the latest session address and XAUTHORITY value.

This script creates a 4-line file ~/.Xdbus with the current session address and XAUTHORITY value. By sourcing this file in the crontab file your scripts can now use dbus to send messages to X-Windows applications. To start and stop Skype at work I added these lines to my work computer’s crontab file:

# Skype on/off
00 09 * * Mon,Tue,Wed,Fri source ~/.Xdbus; /usr/bin/skype &
30 17 * * Mon,Tue,Wed,Fri killall skype

On my home computer I have:

# Skype on/off
00 09 * * Thu source ~/.Xdbus; /usr/bin/skype &
30 17 * * Thu killall skype

So on Thursdays when I telecommute Skype is running at home, the rest of the week it’s running at my office, and at night it’s turned off so customers aren’t calling me in the middle of the night.

When Skype isn’t running other users see your status as “Offline”. When cron restarts Skype it starts up with the same status it had when the process was killed. I just leave my status set to “Online,” so when cron starts Skype it shows everyone that I’m online. When cron kills the Skype process at the end of the day my status changes to “Offline” and people can’t call me.

Hope you find this useful.

Update Pidgin IM status on Ubuntu using cron

Posted on 2008-08-08 by Earl C. Ruby III

At work we use a Jabber instant messenger (IM) server for internal company communications, so that regardless of whether someone is in the office, working from home, or on the road, they can be reached via IM.

I’m running Ubuntu both at work and at home and I use the Pidgin IM client to talk to the Jabber server. I work from home on Thursdays, and I’m always forgetting to turn Pidgin off when I leave work on Wednesday. I usually end up ssh-ing into my work box from home and killing the Pidgin client off remotely, but sometimes I forget and when I come back to work on Friday there are a half-dozen “Are you there?” -type messages on my Pidgin work-client.

So I figured I’d automate the process, automatically setting Pidgin status to “Away” and “Available” using cron, turning the work-client off entirely on Thursdays and weekends, and automatically turning the home-client on Thursday mornings and off Thursday night.

I did a little digging and found a command-line program called purple-remote that allows me to automatically update the Pidgin status and message lines. The purple-remote program is included in the libpurple-bin package, which I installed with System > Administration > Synaptic Package Manager.

Once purple-remote was installed, I fired up a terminal and did a little experimenting on the command line. I found I could set Pidigin’s status to “Away” and the status message to “At lunch” by typing:

/usr/bin/purple-remote "setstatus?status=away&message=At lunch"

I could set the status to “Available” and blank the status message by typing:

/usr/bin/purple-remote "setstatus?status=available&message="

So far so good. I can also cause Pidgin to exit with:

/usr/bin/purple-remote "quit"

Starting Pidgin is just a matter of running:

/usr/bin/pidgin

Time to set up the cron jobs. I fired up crontab -e and entered:

# IM Status
SHELL=/bin/bash
00 08 * * Mon,Fri /usr/bin/pidgin &
01 08 * * Mon,Tue,Wed,Fri /usr/bin/purple-remote "setstatus?status=away&message="
00 09 * * Mon,Tue,Wed,Fri /usr/bin/purple-remote "setstatus?status=available&message="
30 13 * * Mon,Tue,Wed,Fri /usr/bin/purple-remote "setstatus?status=away&message=At lunch"
30 14 * * Mon,Tue,Wed,Fri /usr/bin/purple-remote "setstatus?status=available&message="
30 17 * * Mon,Tue,Wed,Fri /usr/bin/purple-remote "setstatus?status=away&message="
00 19 * * Wed,Fri /usr/bin/purple-remote "quit"

This would start Pidgin at 8:00am, set my status to “Away” at 8:01am, “Available” at 9:00am, “Away – At lunch” at 1:30pm, “Available” again at 2:30pm, “Away” at 5:30pm. On Wednesday and Friday nights at 7:00pm the client shuts down entirely, on Monday and Friday mornings Pidgin gets restarted. That will leave the client off all day on Thursdays and on weekends when I’m not at the office. If my schedule changes for any reason I can still update my status in Pidgin manually.

Looks good, but this doesn’t work. Although the commands listed above work just fine on the command line, they’d fail when they were executed from cron. Checking my mail I found error messages like this:

Traceback (most recent call last):
File "/usr/bin/purple-remote", line 16, in
obj = dbus.SessionBus().get_object("im.pidgin.purple.PurpleService",
 "/im/pidgin/purple/PurpleObject")
File "/var/lib/python-support/python2.5/dbus/_dbus.py", line 218, in __new__
mainloop=mainloop)
File "/var/lib/python-support/python2.5/dbus/_dbus.py", line 107, in __new__
bus = BusConnection.__new__(subclass, bus_type, mainloop=mainloop)
File "/var/lib/python-support/python2.5/dbus/bus.py", line 121, in __new__
bus = cls._new_for_bus(address_or_type, mainloop=mainloop)
dbus.exceptions.DBusException: org.freedesktop.DBus.Error.Spawn.ExecFailed:
 dbus-launch failed to autolaunch D-Bus session: Autolaunch error:
 X11 initialization failed.

The key part of that message is “dbus-launch failed to autolaunch D-Bus session: Autolaunch error: X11 initialization failed.” dbus is a messaging system, and X11 is the implementation of X-Windows Ubuntu uses, which is what the Gnome desktop runs on. Pidgin is an application running on the Gnome desktop / X-Windows. The error message is saying that the program failed to send a message to X11 (X-Windows) and on to Pidgin.

There are three problems here:

The first is that in order for purple-remote to send messages to Pidigin via DBUS it has to work it needs to know the value of the DBUS_SESSION_BUS_ADDRESS environment variable. (Kudos to explicitly ambiguous and his article at http://ubuntuforums.org/showthread.php?t=632580 for pointing me towards this solution.)
In order to start Pidgin at 8:00am Pidgin needs to know the value of the of the XAUTHORITY environment variable otherwise it won’t be authorized to start up on a screen that I’m logged into.
In order to start Pidgin at 8:00am Pidgin needs to know the value of the DISPLAY environment variable so it knows what screen to start up on.

When you start up Ubuntu the dbus daemon starts up and creates a unique session address. Your applications have to know this session address in order to send messages using the daemon. The address is stored in the DBUS_SESSION_BUS_ADDRESS environment variable when you log in, so if you try to run an application from the command line it gets the session address from the DBUS_SESSION_BUS_ADDRESS environment variable. Since cron runs in it’s own environment it doesn’t know the value of DBUS_SESSION_BUS_ADDRESS, so programs that depend on dbus fail when you try to run them from a cron job.

You can see the value by typing:

> env | grep DBUS_SESSION_BUS_ADDRESS
DBUS_SESSION_BUS_ADDRESS=unix:abstract=
/tmp/dbus-AmUs20000,guid=6cce82d52ca190000000000000000000

Likewise, the value of XAUTHORITY changes every time you restart your computer. You can see the current value by typing:

> env | grep  XAUTHORITY

DISPLAY remains the same between reboots. To get the value for your system, type:

> env | grep DISPLAY
DISPLAY=:0.0

Add the “DISPLAY=:0.0” line at the beginning of your crontab file and it’ll be set for Pidgin.

In order to make the environment variables available to cron jobs I created the program ~/bin/export_x_info which looks like this:

#!/bin/bash
# Export the dbus session address on startup so it can be used by cron
touch $HOME/.Xdbus
chmod 600 $HOME/.Xdbus
env | grep DBUS_SESSION_BUS_ADDRESS > $HOME/.Xdbus
echo 'export DBUS_SESSION_BUS_ADDRESS' >> $HOME/.Xdbus
# Export XAUTHORITY value on startup so it can be used by cron
env | grep XAUTHORITY >> $HOME/.Xdbus
echo 'export XAUTHORITY' >> $HOME/.Xdbus

SHELL=/bin/bash
DISPLAY=:0.0
# IM Status
00 08 * * Mon,Fri source ~/.Xdbus; /usr/bin/pidgin &
01 08 * * Mon,Tue,Wed,Fri source ~/.Xdbus; /usr/bin/purple-remote "setstatus?status=away&message="
00 09 * * Mon,Tue,Wed,Fri source ~/.Xdbus; /usr/bin/purple-remote "setstatus?status=available&message="
30 13 * * Mon,Tue,Wed,Fri source ~/.Xdbus; /usr/bin/purple-remote "setstatus?status=away&message=At lunch"
30 14 * * Mon,Tue,Wed,Fri source ~/.Xdbus; /usr/bin/purple-remote "setstatus?status=available&message="
30 17 * * Mon,Tue,Wed,Fri source ~/.Xdbus; /usr/bin/purple-remote "setstatus?status=away&message="
00 19 * * Wed,Fri source ~/.Xdbus; /usr/bin/purple-remote "quit"

The call to source ~/.Xdbus on each line loads the DBUS_SESSION_BUS_ADDRESS and XAUTHORITY environment variables before executing the purple-remote command or starting Pidgin.

Now the cron works and my Pidgin status is constantly updated. The cron on my home computer is much simpler:

SHELL=/bin/bash
DISPLAY=:0.0
# IM Status
00 08 * * Thu source ~/.Xdbus; /usr/bin/pidgin &
01 08 * * Thu source ~/.Xdbus; /usr/bin/purple-remote "setstatus?status=away&message="
00 09 * * Thu source ~/.Xdbus; /usr/bin/purple-remote "setstatus?status=available&message="
30 13 * * Thu source ~/.Xdbus; /usr/bin/purple-remote "setstatus?status=away&message=At lunch"
30 14 * * Thu source ~/.Xdbus; /usr/bin/purple-remote "setstatus?status=available&message="
30 17 * * Thu source ~/.Xdbus; /usr/bin/purple-remote "setstatus?status=away&message="
00 19 * * Thu source ~/.Xdbus; /usr/bin/purple-remote "quit"

The same technique works for other X-Windows programs as well. For instance, I added this line to my cron:

30 17 * * Mon,Tue,Wed,Fri source ~/.Xdbus; /usr/bin/notify-send "Go Home" "Time to Go"

So every day at 5:30pm a “notify” message pops up reminding me that it’s time to go home.

Hope you find this useful.