Not logged in
Log in now
Create an account
Subscribe to LWN
LWN.net Weekly Edition for June 20, 2013
Pencil, Pencil, and Pencil
Dividing the Linux desktop
LWN.net Weekly Edition for June 13, 2013
A report from pgCon 2013
Posted Dec 19, 2011 14:28 UTC (Mon) by khim (subscriber, #9252)
Well, the example you gave (RETURNING versus ::lastInsertId) looks like the sort of trivial-yet-important thing that any reasonable RDBMS-independent database driver really should abstract over.
s/reasonable/unreasonable/. If it's database driver and not ORM then it's good idea to give you access to raw functions of SQL database. For example ODBC just does not give access to these capabilities so you need to use two SQL requests instead of one. Hardly an improvement.
If it doesn't bother, that says volumes...
Sure. It spokes volumes about unreasonable expectations.
Posted Dec 19, 2011 14:46 UTC (Mon) by smurf (subscriber, #17840)
I have no problem with getting access to the raw functions. But that kind of implementation detail wants to be abstracted. It's not something a Web programmer should have to think about.
Posted Dec 19, 2011 15:05 UTC (Mon) by khim (subscriber, #9252)
Returning the last_insert_id is something every reasonable SQL database must do.
Puhlease. Oracle offers totally different way of doing the same thing. You never pull last_insert_id to your program at all: instead you use currval in the next use insert (update, etc).
I have no problem with getting access to the raw functions. But that kind of implementation detail wants to be abstracted.
Well, it only makes sense if you don't care about resource usage. Because practially the only way to abstract these kind of things is to create interface which works awfully slow for all supported databases. Perhaps some wizard can invent something which will support all common databases in the same fashion and still will work fast - but on practice I'm yet to see such implementation.
Posted Dec 19, 2011 16:27 UTC (Mon) by nix (subscriber, #2304)
Writing an abstraction layer which abstracts over all SQL differences is of course over the top... but abstracting over trivial, always-needed stuff like 'what is the sequence number of the row I just inserted' is, well, trivial. I've written such an abstraction layer myself more than once, and that was a little one-man thing, and it was not very much effort. That PHP couldn't do it says volumes.
This is funny...
Posted Dec 20, 2011 0:04 UTC (Tue) by khim (subscriber, #9252)
Your belief that RETURNING doesn't work in Oracle is untrue. It does (though you have to RETURNING a .nextval, indeed), and is the recommended approach rather than using .currval, because it saves a roundtrip to the database.
Of course RETURNING works, but... how exactly will it save the roundtrip? When you use nextval/currval idiom you can just batch all your operation in one statement using BEGIN/END - thus using one single roudtrip. How exactly last_insert_id emulation can beat this is mystery to me.
Sure, sometimes you want to do some manipulations in your application before continuing - and in this case it's natural to use last_insert_id with MySQL (but not with Oracle).
Writing an abstraction layer which abstracts over all SQL differences is of course over the top... but abstracting over trivial, always-needed stuff like 'what is the sequence number of the row I just inserted' is, well, trivial.
Actually it's not trivial. In MySQL LAST_INSERT_ID only gives you one number but in Postresql or Oracle you can generate few different numbers. And since one roundtrip can include quite a few elemental operations it's not easy or simple to transform one to another.
I've written such an abstraction layer myself more than once, and that was a little one-man thing, and it was not very much effort.
How exactly have you processed Oracle's insert statements which used two distinct sequences in MySQL?
That PHP couldn't do it says volumes.
About your unreasonable expectations? Sure. About SQL driver? Not much. DBI example which produces suboptimal results does not expire confidence: I'd rather handle such problems at the application level rather then in intense debugging session which will show that that my perfectly tuned code on some other database uses insert over unindexed table "because driver had no other choice". Thnks... but no, thnks.
Posted Dec 20, 2011 11:38 UTC (Tue) by nix (subscriber, #2304)
(It was not a very pleasant abstraction layer to write, I'll grant you that. But, still, abstracting over this sort of thing *is* the job of an abstraction layer.)
Posted Dec 19, 2011 14:54 UTC (Mon) by niner (subscriber, #26151)
There are several caveats to be aware of with this method if you want to use it for portable applications:
* For some drivers the value may only available immediately after the insert statement has executed (e.g., mysql, Informix).
* For some drivers the $catalog, $schema, $table, and $field parameters are required, for others they are ignored (e.g., mysql).
* Drivers may return an indeterminate value if no insert has been performed yet.
* For some drivers the value may only be available if placeholders have not been used (e.g., Sybase, MS SQL). In this case the value returned
would be from the last non-placeholder insert statement.
* Some drivers may need driver-specific hints about how to get the value. For example, being told the name of the database 'sequence' object
that holds the value. Any such hints are passed as driver-specific attributes in the \%attr parameter.
* If the underlying database offers nothing better, then some drivers may attempt to implement this method by executing ""select max($field)
from $table"". Drivers using any approach like this should issue a warning if "AutoCommit" is true because it is generally unsafe - another
process may have modified the table between your insert and the select. For situations where you know it is safe, such as when you have
locked the table, you can silence the warning by passing "Warn" => 0 in \%attr.
* If no insert has been performed yet, or the last insert failed, then the value is implementation defined.
Given all the caveats above, it's clear that this method must be used with care.
Posted Dec 19, 2011 16:19 UTC (Mon) by khim (subscriber, #9252)
Given all the caveats above, it's clear that this method must be used with care.
It must only be used with MySQL, really. If you want to use Oracle and Postgresql then it's much better to use sequences and their nextval and currval directly (not sure about other databases, but they probably have their own ways to do that).
So while it is available and in practice works, the interface still has to live with the complete lack of standardisation among DBMS.
Right. This just shows that SQL driver is completely wrong level of abstraction to try to unify different databases. It's [relatively] easy for ORM layer or application-specific layer to deal with these differences. If you try to do that on SQL driver layer you'll waste huge amount of resources and and the end result will sometimes still not usable (If the underlying database offers nothing better, then some drivers may attempt to implement this method by executing ""select max($field) from $table"").
NOT a way to go. PHP has many problematic warts, but PDO is not one of them.
Posted Dec 19, 2011 19:35 UTC (Mon) by fatrat (subscriber, #1518)
Have you seen Python's SQLAlchemy? It provides an ORM layer but also a really good SQL level layer.
Posted Dec 20, 2011 0:35 UTC (Tue) by khim (subscriber, #9252)
No, I've never used SQL from python so I never used SQLAlchemy. Looks like fine ORM toolkit, though. As for SQL
as any good SQL driver it includes dialects. This is where you can specify sequences in Oracle, disable autoincrement in MySQL, etc.
Even so: I can not see how exactly you can chain multiple INSERTs in one BEGIN/END Oracle statement or execute multiquery in MySQL (like you can do with PHP).
Posted Dec 20, 2011 7:38 UTC (Tue) by niner (subscriber, #26151)
Not really. It covers the simple case quite well which is the vast majority in our applications. If I want to use PostgreSQL features, I'd use returning to get the new ID back to the application without an additional roundtrip.
Copyright © 2013, Eklektix, Inc.
Comments and public postings are copyrighted by their creators.
Linux is a registered trademark of Linus Torvalds