OT from the original post, but pertinent to the discussion...
On 5/27/06, Markus Hoenicka <[EMAIL PROTECTED]> wrote:
Martin Kutschker writes: > Have the systems which use sequences some nameing schemes, which build the sequence name from the table name? > SQLite and MySQL don't use sequences, so I can speak only of PostgreSQL (don't know about Oracle, Sybase et al.). If you use the
Oracle does use sequences, and they look quite a bit like Pg sequences but require a little more work to use AFAICT. Sybase (well, the MSSQL version) has a "magical" column property that feels more like MySQL's autoincrement. http://troels.arvin.dk/db/rdbms/#mix-identity
Serial type in PostgreSQL, a sequence is automatically created with a predictable name (tablename_colname_seq). However, you can do this just as well: CREATE SEQUENCE whatever_seq; CREATE TABLE tablename ( colname integer DEFAULT nextval('whatever_seq') NOT NULL A mechanism that relies on a particular naming scheme of sequences would fail in the second case.
While it would take a little work in the Pg libdbi driver to detect the version (if that's not already done), 8.1 has a function called lastval() that returns the last value produced by the last sequence used. This falls down if you use more than one sequence on a table (say, as a default on a non-SERIAL column, as I do in some cases), but it works well for the common case. The driver could use the sequence name if provided, otherwise, under 8.1, it would 'SELECT lastval()' and return the expected value in either case. Applications would have to take care to be coded to Pg 8.1+ in order to use the "magic" sequence stuff, but it's more portably future proof, IMHO. Of course, older apps that provide a sequence name would continue to function as they do now.
> And perhaps dbi couild handle the problem that Mysql doesn't store the id by table or sequence but by connection by automatically calling mysql_last_insert_id() on every insert and storing the ids in a dbi datastructure per table. > I don't see a real problem here. If an application makes sure to store relevant id values after INSERT commands, you'll get a portable code without causing dbi to interfere with tables behind the users back. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de ------------------------------------------------------- All the advantages of Linux Managed Hosting--Without the Cost and Risk! Fully trained technicians. The highest number of Red Hat certifications in the hosting industry. Fanatical Support. Click to learn more http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642 _______________________________________________ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
-- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ------------------------------------------------------- All the advantages of Linux Managed Hosting--Without the Cost and Risk! Fully trained technicians. The highest number of Red Hat certifications in the hosting industry. Fanatical Support. Click to learn more http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642 _______________________________________________ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users