Autoincrement & Sequences

I am particularly interested in the sequence issue because this is where I ran into my biggest portability issues between mysql and postgres. Jason Lotito wrote: >The idea of simulating auto_increment in MySQL seems less than >optimized (going by what I have read in your manual/tutorial), and is >something that really turned me off. It appears that his is how sequences are supposed to work in PEAR as well (i.e., simulated using a MySQL sequences table). From DB tutorial by T Cox: " Sequences is a way of offering unique IDs for data rows. If you do most of you work with e.g. MySQL, think of sequences as another way of doing AUTO_INCREMENT. It's quite simple, first you request an ID, and then you insert that value in the ID field of the new row you're creating. You can have more than one sequence for all your tables, just be sure that you always use the same sequence for any particular table. ...// Get an ID (if the sequence doesn't exist, it will be created) $id = $db->nextID('mySequence'); // Use the ID in your INSERT query $res = $db->query("INSERT INTO myTable (id,text) VALUES ($id,'foo')");... Using a sequences table is how PHPLIB works as well. I think that if you want to do portable db programming you need to resort to using a sequences table even thought this is inefficient because 1) it involves an extra query and 2) alot of the processing is done in PHP userspace (versus c space with the MySQL autoincrement feature). The only other option I can think off would be to somehow intercept the queries before they hit the db and rewrite them to a format that works for the particular db. $sql = "INSERT INTO myTable (text) VALUES ('foo')"; $db>query($sql, "auto", id); For MySQL the SQL would would not need to be rewritten. For Postgres the SQL would need to be rewritten (behind the scenes). Not sure exactly how Metabase works in this case as I have not had a chance to look at it too much yet. Regards, Paul Meagher