| |||||||||||||
Wrong again...Wrong again...Posted Dec 19, 2011 15:05 UTC (Mon) by khim (subscriber, #9252)In reply to: Duh? by smurf Parent article: Cracks in the Foundation (PHP Advent)
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.
(Log in to post comments)
Wrong again... Posted Dec 19, 2011 16:27 UTC (Mon) by nix (subscriber, #2304) [Link]
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.
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) [Link] 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. WOW! Cool. 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.
This is funny... Posted Dec 20, 2011 11:38 UTC (Tue) by nix (subscriber, #2304) [Link]
As for last_insert_id et al, well, every abstraction layer has costs. One cost of this one was that you had to use a not-completely-disgusting data model, i.e., that having more than one sequence tracking a single table was not supported. I never felt the lack: compound PKs are all very well, but if you use a sequence (mysql: autoincrement) as a PK it means that nothing else would do. Why on earth would you want to use a compound PK consisting of multiple sequences? That smacks of very bad table design to me. I thought of various more or less ugly ways to fix this but never implemented them because of a total lack of need.
(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.)
|
|||||||||||||