Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Generating portable SQL primary key sequences

by tachyon (Chancellor)
on Dec 07, 2002 at 13:13 UTC ( #218243=perlquestion: print w/replies, xml ) Need Help??
tachyon has asked for the wisdom of the Perl Monks concerning the following question:

I have a Webmail app that uses a database backend for its address book, task list, calendar etc. Currently it runs against MySQL and uses MySQL's non ANSI standard AUTO INCREMENT feature to generate the sequence for the id column primary key:

$sql =<<SQL; CREATE TABLE address_book ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(20), first_name VARCHAR(30), last_name VARCHAR(30), [blah] PRIMARY KEY (id) ) SQL

There are of course a number of ways of generating a unique primary key like CREATE SEQUENCE. What I would like is suggestions of the most portable way short of creating a table called primary_keys with columns for the tables that hold the next primary key for each table and doing atomic read/updates on these.




Replies are listed 'Best First'.
Re: Generating portable SQL primary key sequences
by lachoy (Parson) on Dec 07, 2002 at 14:54 UTC

    Generating IDs can be broken down into two cases:

    • perform an action before the insert (call a sequence, generator or pull the next value from a table)
    • perform an action after the insert (generally by asking the driver for a property -- 'mysql_insertid' -- or calling another statement to fetch a value -- 'SELECT @@IDENTITY')

    So the easiest way I've found to do this is create pre/post actions for the insert and register certain generative capabilities with certain drivers. (This is how we do it in SPOPS.) If you're using PostgreSQL/Oracle/FirebirdSQL you need to fetch the next value from a sequence/generator. And if you're using MySQL/Sybase you need to create an INSERT using all fields except the ID field, then get the ID afterwards.

    IME trying to be portable with something like this tends to get you in trouble, since most people already have some scheme for doing this that works quickly, atomically and reliably.

    Best of luck,

    M-x auto-bs-mode

      The databases I am familiar with that use sequences support getting the previous value. With PostgreSQL it is:
      SELECT currval('table_key_seq')
      With Oracle it is:
      SELECT table_key_seq.currval FROM dual
      Both of them can use sequences for the default value on the primary key. This makes the usage model the same as MySQL or SQL Server of insert the row without the key, and fetch the just-inserted value of the key.
Re: Generating portable SQL primary key sequences
by adrianh (Chancellor) on Dec 07, 2002 at 16:52 UTC

    If you've got transactions, why not create a table of primary keys? Seems the most straightforward mechanism, and can be written in standard SQL so its portable across databases.

    If you've not got transactions I've found DBIx::Sequence useful.

    You might also find this discussion of interest.

Re: Generating portable SQL primary key sequences
by FamousLongAgo (Friar) on Dec 07, 2002 at 13:34 UTC
    A naive question - why not create a wrapper function or method called get_unique_id and change the implementation based on the backend in use? For MySQL, you would just need to insert an empty row using the AUTO_INCREMENT behavior - for other db's, you might need some combination of locking or dummy INSERT statements, but it would at least be portable.

      That is a good thought. Something like this would be fine if the database supported trasactions (which MySQL does not). You would then create the table with or without AUTO_INCREMENT. For MySQL we return the required NULL value for id otherwise we generate one in an atomic select/update/commit/rollback.

      use DBI; my $DB_TYPE = 'mysql'; my $AUTO_COMMIT = 1; my $DB = 'test'; my $DB_USERNAME = 'user'; my $DB_PASSWORD = 'pwd; my $dbh = DBI->connect( "dbi:$DB_TYPE:$DB", $DB_USERNAME, $DB_PASSWORD +, {AutoCommit => $AUTO_COMMIT} ) or die_nice( DBI->errstr() ); END { $dbh->disconnect } # do_sql('DROP TABLE unique_id'); # for debugging my $sql =<<SQL; CREATE TABLE unique_id ( id INT UNSIGNED NOT NULL, address_book INT UNSIGNED NOT NULL, task_list INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) SQL # create unique id table; do_sql($sql); # initialize table; $sql = 'INSERT INTO unique_id VALUES (?,?,?)'; do_sql($sql, 1, 0, 0); print get_unique_id('address_book'), "\n" for 1..10; sub get_unique_id { my ( $table_name ) = @_; return 'NULL' if $DB_TYPE = 'mysql'; my ($select_sth, $update_sth); my $select_sth = get_sth("SELECT $table_name from unique_id where +id = 1" ); my $update_sth = get_sth("UPDATE unique_id SET $table_name = ? whe +re id = 1"); my $success = 1; $select_sth->execute() or $success = 0; my ($id) = $select_sth->fetchrow_array or $success = 0; $id++; $update_sth->execute($id) or $success = 0; $success = ($success ? $dbh->commit : $dbh->rollback) unless $AUTO +_COMMIT; die_nice( "Couldn't finish get_unique_id transaction: " . $dbh->er +rstr ) unless $success; $select_sth->finish; $update_sth->finish; return $success ? $id : 0; } sub do_sql { my $sql = shift; my $sth = $dbh->prepare($sql) or die_nice( "Could not prepare SQL statement\n\n$sql\n" . $dbh-> +errstr() ); $sth->execute(@_) or die_nice( "Could not execute SQL statement\n\n$sql\n" . $sth-> +errstr() ); $sth->finish; $dbh->commit unless $AUTO_COMMIT; } sub get_sth { my $sql = shift; my $sth = $dbh->prepare_cached($sql) or die_nice( "Could not prepare SQL statement\n\n$sql\n" . $dbh-> +errstr() ); return $sth; } sub die_nice { die shift; }




        Update - MySQL 4.1 and higher support transactions on the InnoDB and BDB table types.

        We are the carpenters and bricklayers of the Information Age.

        Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

        I shouldn't have to say this, but any code, unless otherwise stated, is untested


      A safe, transaction safe sequence emulation for MySQL. The docs explain some reasons you might want to allocate the object ID first, rather than afterwards.

      Extracted out of a commercial app I wrote once.

      I really need to write the equivalent for some other non-sequence-friendly database one of these days.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://218243]
Approved by valdez
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (8)
As of 2017-09-24 09:26 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (273 votes). Check out past polls.