By tatonca on

I'm trying to understand why I want to use db_next_id instead of just letting a table with an autoincrement do its thing. Drupal docs says "for compatibility reasons" but I'm not sure what incompatibilities there might be. Granted I only know mysql, and only enough to get me by...

Here is an example from aggregator.module ( vrs 1.213.2.3)

    $edit['fid'] = db_next_id('{aggregator_feed}_fid');
    db_query('INSERT INTO {aggregator_feed} (fid, title, url, refresh, block) VALUES (%d, \'%s\', \'%s\', %d, %d)',
                  $edit['fid'],$edit['title'], $edit['url'], $edit['refresh'], $edit['block']);

The aggregator fid column is autoincrement, so why not just pass it a NULL or 0?

The reason I ask is I'm playing with some ideas for a per user RSS feeds module, and I would like to reuse some of the aggregator functions. However, trying to get at the last ID added to the aggregator table without using something like LAST_INSERT_ID() is troublesome and slow. Since LAST_INSERT_ID only stores the last id when the autoincrement is allowed to 'go off' and do it's thing, using db_next_id and inserting the returned value directly doesn't register anything with LAST_INSERT_ID...

I'm thinking this is a postgre versus mysql thing...

Looking at db_next_id it uses nextval() to get the id - i thought I might be able to use currval() to do the same thing as LAST_INSERT_ID() but that didn't work out...

So if someone could

1) explain the compatibility issues with using autoincrement and
2) maybe suggest a different way of getting the last inserted ID

I would be very greatful.

Thanks in advance...

~Tat~

Comments

knite’s picture

knite commented

Several other modules already use auto-increment, and pgSQL does have support for auto-increment as well. The comment about "compatibility" is related to ANSI standards, but it isn't strictly observed even in drupal core.

auto-increment requires no table locks and is more performant. I say go for it.

nefertitian’s picture

nefertitian commented

i am finding myself in a similar situation here but for a custom module of my own.

while using auto-increment seems rather attractive in terms of performance, consider a case where you would need the value of the auto-increment'ed number from your last insert query. how would you get that? a select query immediately after the insert query will definitely fail if there are a lot of simulatenous db connections to the database doing similar insert queries to the same table.

for mysql, i guess you could simply use the mysql_insert_id function:
http://us2.php.net/manual/en/function.mysql-insert-id.php .

but for databases other than mysql, i am unable to find a function that offers similar functionality. for these reasons, i am forced to stick to the drupal 'db_next_id' function. please drop me a hint if you find alternative ways to do this.

khoogheem’s picture

khoogheem commented

One of the problems I see with db_next_id is that if you create a module that already has values in a data base and forget to also load the most current sequence number it restarts at 1.

There should be an enhancement to db_next_id to check the current value of the table column passed.

so if I put in db_next_id('{new_node_type}_nid')
it should check to see the most current value of nid if the new_nod_type_nid is not in the sequance table.

does that make sense?!?