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=lnkkid=107521bid=248729dat=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=lnkkid=107521bid=248729dat=121642
___
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
