Re: [Phplib-users] nextid - how does it work?

[Nathaniel P. <np...@te...>]

----- Original Message ----- 
From: "Neha Mathur" <ne...@sp...>
To: <php...@li...>
Sent: Wednesday, July 16, 2003 2:20 PM
Subject: [Phplib-users] nextid - how does it work?


> Hello,
>
> I'm trying to use nextid but even after reading the documentation notes on
> it, I'm still quite confused on how to use it.
>
> I have a db_sql instanced and I need to find out the next primary key in
> sequence in a table in my database so I can insert a row with this data as
> such -
>
> $db = new DB_SQL_SubClass(connection params);
> $nextIDinSeq = nextid("???"); \\ i have no clue what goes within the
parens
> there
>
> $insertQuery = "insert into xyztable ('Next in Sequence') values
> ($nextIDinSeq)";
> $db->query($insertQuery);
> etc etc...

Hmm, interesting question... I admit that in my apps, I just use the
sequence system provided by the database (PostgreSQL, in my case), so I
don't know much about it either...

Looking at the code for db_pgsql.inc and db_mysql.inc it looks like this is
what it does:

There is a table defined by the class variable $Seq_Table in DB_Sql, which
is by default set to use a table named 'db_sequence'. From what I can tell
from the code, it looks like this table would have a schema that looks like
this:

db_sequence
-------------
seq_name (varchar, primary key)
nextid (int)

Note that this table isn't set up with the SQL scripts included with the
PHPlib distribution. You'll have to set it up yourself. Once it's set up,
however, you should be able to do something like this in your code:

$db = new DB_Sql_subclass
$my_id = $db->nextid('unique_seq_name');
$db->query("insert into my_table values ($my_id, $my_data);");
$my_2nd_id = $db->nextid('unique_seq_name');
$db->query("insert into my_table values ($my_2nd_id, $more_data);");
...

Obviously, you can adapt it for loops or whatever to be more efficient, but
hey, I'm just demoing it here.

What will happen is the first time $db->nextid('unique_seq_name') is called,
PHPlib will check to see if a sequence by the name of 'unique_seq_name'
exists in the db_sequence table. If there isn't one, it will insert a record
into the db_sequence table to keep track of the id number, then return 1 as
its first id number. On subsequent calls, it will find the sequence name,
lock the table to prevent concurrent accesses which could corrupt your
sequences, increment the sequence by one, update it back to the database
with the incremented value, unlock the table, then return the incremented
value back to your script.

It seems to be a nice solution for developing cross-platform scripts that
don't depend on a specific database for sequences, but it lacks some of the
sequence manipulation functionality that is present in PostgreSQL, for
example; although I imagine that one could add back some of that
functionality by extending the class with additional methods. Or even
convert it to be an abstraction layer over DB-specific sequences.

Anyway, hope this helps.

_________________________________
Nathaniel Price <np...@te...>
Webmaster