Re: [PHP] database abstraction layer
Ashley Sheridan wrote: On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: function getMax($table, $field) If I saw this sort of code I'd be appalled! It's possibly the worst way to get the auto increment value. You won't notice it testing the site out on your own, but all hell will break loose when you start getting a lot of hits, and two people cause an auto increment at the same time! ADOdb handles SEQUENCE correctly across all databases. Since MySQL does not understand SEQUENCE or GENERATOR, ADOdb simulates it with a dummy table which autoincrements and gets around the problem. Then one can use a secure generic GetID ;) -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk// Firebird - http://www.firebirdsql.org/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
On Wed, Feb 03, 2010 at 06:39:29AM +0100, Rene Veerman wrote: > On Wed, Feb 3, 2010 at 5:49 AM, Jochem Maas wrote: > > you can bet you ass that every other DB out there that's worth it's salt > > has atomic id incrementor functionality exposed in some way or other. > > > > @Rene: all that talk of maxId functions and random retries etc, etc, > is complete pooh. > > don't do it, **please** use the proper tools provided by the DB in > question. > > > > i just checked how my 1 app that did generate over a million hits/day > (all with an insert for stats purposes) > for a few weeks handles the getMax issue, and i see i did use mysql's > auto_increment there. > > i suppose the difference in syntax between sql servers for this one is > acceptable. Am I the only one who's seeing Rene's replies but not the posts which generated them? Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
On Wed, Feb 3, 2010 at 5:49 AM, Jochem Maas wrote: > you can bet you ass that every other DB out there that's worth it's salt > has atomic id incrementor functionality exposed in some way or other. > > @Rene: all that talk of maxId functions and random retries etc, etc, is > complete pooh. > don't do it, **please** use the proper tools provided by the DB in question. > i just checked how my 1 app that did generate over a million hits/day (all with an insert for stats purposes) for a few weeks handles the getMax issue, and i see i did use mysql's auto_increment there. i suppose the difference in syntax between sql servers for this one is acceptable. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Op 2/3/10 12:19 AM, Ashley Sheridan schreef: > On Wed, 2010-02-03 at 00:21 +0100, Rene Veerman wrote: > >> the auto_increment sytnax is not uniform across servers, is it? >> >> On Wed, Feb 3, 2010 at 12:11 AM, Ashley Sheridan >> wrote: >> >>> I saw it happen on a site that was getting only about 3000 hits a day. It >>> just takes the right combination of circumstances and it all goes pear >>> shaped. You really should get out of the habit of doing it. >>> > > > It is a MySQL only function. MSSQL has @@IDENTITY, not sure how other > engines implement it. firebird does it via what they call 'generators', 2 seconds of searching shows postgres has this: CREATE TABLE tableName ( id serial PRIMARY KEY, name varchar(50) UNIQUE NOT NULL, dateCreated timestamp DEFAULT current_timestamp ); you can bet you ass that every other DB out there that's worth it's salt has atomic id incrementor functionality exposed in some way or other. @Rene: all that talk of maxId functions and random retries etc, etc, is complete pooh. don't do it, **please** use the proper tools provided by the DB in question. > > Thanks, > Ash > http://www.ashleysheridan.co.uk > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Lol, damn iPod corrections. The app designers is what was meant. Bastien Sent from my iPod On Feb 2, 2010, at 8:41 PM, Robert Cummings wrote: Phpster wrote: Yep, love those race conditions. We have them all over the app cuz the app ciders don't know shit! Mmmm... apple cider... to cure what ails you or at least get you drunk enough to not care about the horrible race conditions >:) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
On Tue, Feb 02, 2010 at 11:19:29PM +0100, Rene Veerman wrote: > oh, on using adodb.sf.net and 0-overhead for jumping between mysql and > postgresql; > > keep all your queries to as simple & early-standard sql as possible. > the auto_increment incompatibilities can be circumvented with a > relatively simple > function getMax($table, $field) { This approach is guaranteed to run into race conditions. The only way to positively ensure proper results is to let the DB engine take care of it itself. The engines typically track incremental IDs by session, which prevents you from getting an ID someone else has just used. > > in adodb, you'd loop through a huge dataset like this, ensuring proper > comms & mem-usage betweeen the db server and php. > > $dbConn = adoEasyConnection(); //returns adodb connection object, > initialized with values from config.php > $sql = 'select * from data where bladiebla="yep"'; > $q = $dbConn->execute ($sql); > if (!$q || $q->EOF) { > $errorMsg = $dbConn->ErrorMsg(); > handleError ($errorMsg, $sql); > } else { > while (!$q->EOF) { > >//use $q->fields['field_name']; // from the currently loaded record > > $q->MoveNext(); > } > } > > for short resultsets you could call $q->getRows() to get all the rows > returned as 1 multilevel array. > > instead of difficult outer-join constructs and stored procedures, > (that are not portable), i find it much easier to aim for small > intermediate > computation-result arrays in php, which are used to construct > fetch-final-result-sql on the fly. > itnermediate &/ result arrays can be stored on db / disk in json, too ;) For MySQL I would agree. But I prefer the ability to use the full SQL standard when manipulating a database; that is, all joins, foreign keys, etc. For that same reason, I tend to avoid stored procedures as well. If I have to do things like handle foreign key constraints in my PHP code (instead of letting the DBMS handle them), I have to wonder why I'm even using a "relational" DBMS. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Robert Cummings wrote: *snip* Einstein I believe said something along the lines of "A smart person solves a problem. A wise person avoids it in the first place" Might not have been Einstein, but anyway ... Do you mean the following quote? The difference between a smart person and a wise person is that a smart person knows what to say and a wise person knows whether or not to say it. Cheers, Rob. No - this is it: http://www.famousquotes.com/show/1022246/ “A clever person solves a problem. A wise person avoids it.” -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Michael A. Peters wrote: Robert Cummings wrote: Rene Veerman wrote: eh thats "randomize the timing of the retry attempt".. On Wed, Feb 3, 2010 at 12:17 AM, Rene Veerman wrote: and after the sleep(rand(1,3)) it might need a short loop like this; $rnd = rand(1,9); $a=0; for ($i=0; $i<$rnd; $i++) { $a++ } to further randomize the retry attempt.. While this decreases the probability of a collision you're just setting up another race. They teach about this stuff in computer science... right around first year... or at least they once upon a time did. Cheers, Rob. Einstein I believe said something along the lines of "A smart person solves a problem. A wise person avoids it in the first place" Might not have been Einstein, but anyway ... Do you mean the following quote? The difference between a smart person and a wise person is that a smart person knows what to say and a wise person knows whether or not to say it. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Robert Cummings wrote: Rene Veerman wrote: eh thats "randomize the timing of the retry attempt".. On Wed, Feb 3, 2010 at 12:17 AM, Rene Veerman wrote: and after the sleep(rand(1,3)) it might need a short loop like this; $rnd = rand(1,9); $a=0; for ($i=0; $i<$rnd; $i++) { $a++ } to further randomize the retry attempt.. While this decreases the probability of a collision you're just setting up another race. They teach about this stuff in computer science... right around first year... or at least they once upon a time did. Cheers, Rob. Einstein I believe said something along the lines of "A smart person solves a problem. A wise person avoids it in the first place" Might not have been Einstein, but anyway ... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Rene Veerman wrote: On Wed, Feb 3, 2010 at 12:18 AM, Ashley Sheridan wrote: The problem is where 2 people choose the same instant to perform an action on your site that inserts a record into your db. The db engine inserts them one after the other, and then responds about the max(id) to your PHP script. Then, you now have 2 people who have the same max(id) retrieved, but one of the values is wrong. well, i only use getmaxid()s for inserts. the timelag between getmaxid() and the insert is so small it'd take 300-800 insert-requests/sec (on that particular table) before an error condition would arise. in which case, a tested piece of sql would fail, and can be routed through the retry functions . These would imo provide ample timing re-randomization, aswell as a measure of stress-relief for both php and mysql server. You may correct me if i'm wrong :) BTW: php core developers: can we have a sleep() that accepts a float? :) Is the following too difficult? Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Phpster wrote: Yep, love those race conditions. We have them all over the app cuz the app ciders don't know shit! Mmmm... apple cider... to cure what ails you or at least get you drunk enough to not care about the horrible race conditions >:) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Rene Veerman wrote: On Wed, Feb 3, 2010 at 12:35 AM, Ashley Sheridan wrote: It's the reason transactions exist, to prevent things happening like this. When you have two actions where one is dependent on the other, unless you have a way to tie them together so that they can't be broken, you run the risk of collisions. Yea, and i wish they'd standarized features like that across sql servers. But they haven't, so i avoid them like the plague. This is why you're creating your own layer... to smooth the wrinkles between the systems via your abstracted layer. That isn't usually a good reason for you to do it improperly. Whatever dependencies and threading problems might arise, there's always the principle that says: If it doesn't work whlie it should work and threading-timing problems are the only possible cause, then by delay by a random timeperiod and retry the query. Yikes, please cite your reference for that horrible advice. In really advanced cases, one can work with last-modified timestamps and/or build up a simple sort of work-queue (also in a table), whereby threads inform each other of the status of their computations. Wow... this gets ever more complex to do something simple. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Rene Veerman wrote: On Wed, Feb 3, 2010 at 12:18 AM, Ashley Sheridan wrote: The problem is where 2 people choose the same instant to perform an action on your site that inserts a record into your db. The db engine inserts them one after the other, and then responds about the max(id) to your PHP script. Then, you now have 2 people who have the same max(id) retrieved, but one of the values is wrong. well, i only use getmaxid()s for inserts. the timelag between getmaxid() and the insert is so small it'd take 300-800 insert-requests/sec (on that particular table) before an error condition would arise. in which case, a tested piece of sql would fail, and can be routed through the retry functions . These would imo provide ample timing re-randomization, aswell as a measure of stress-relief for both php and mysql server. You may correct me if i'm wrong :) BTW: php core developers: can we have a sleep() that accepts a float? :) This works right up until someone else maintains this system and can't understand why the database is corrupt. Then they find find your database layer and want to stab themselves :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Rene Veerman wrote: eh thats "randomize the timing of the retry attempt".. On Wed, Feb 3, 2010 at 12:17 AM, Rene Veerman wrote: and after the sleep(rand(1,3)) it might need a short loop like this; $rnd = rand(1,9); $a=0; for ($i=0; $i<$rnd; $i++) { $a++ } to further randomize the retry attempt.. While this decreases the probability of a collision you're just setting up another race. They teach about this stuff in computer science... right around first year... or at least they once upon a time did. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Rene Veerman wrote: i haven't had the pleasure yet of writing for sites that generate so many hits/sec that they'd update the max value of any table at exactly the same time. i usually ask for the max value about 2 milliseconds before doing the insert. And if the insert fails, i can auto-retry via a wrapper function after sleep(rand(1,3)); I dare say i could work this way at facebook (not that i really want to, happy with where i am) This is a race condition... all you need are two hits per day... one from person A and one from person B. If they happen within short enough temporal proximity then the race is on. Now... a big fat lock around the table before the request for max ID and the insert query ought to mitigate your issue. Although I wouldn't call my function getMaxId() I would call it getNextId() :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Good lord that is exacty the same logic applied in our app. It only takes about 100 users to create the issue. Bastien Sent from my iPod On Feb 2, 2010, at 6:05 PM, Rene Veerman wrote: i haven't had the pleasure yet of writing for sites that generate so many hits/sec that they'd update the max value of any table at exactly the same time. i usually ask for the max value about 2 milliseconds before doing the insert. And if the insert fails, i can auto-retry via a wrapper function after sleep(rand(1,3)); I dare say i could work this way at facebook (not that i really want to, happy with where i am) On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan wrote: On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: function getMax($table, $field) If I saw this sort of code I'd be appalled! It's possibly the worst way to get the auto increment value. You won't notice it testing the site out on your own, but all hell will break loose when you start getting a lot of hits, and two people cause an auto increment at the same time! Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Yep, love those race conditions. We have them all over the app cuz the app ciders don't know shit! Bastien Sent from my iPod On Feb 2, 2010, at 5:46 PM, Ashley Sheridan wrote: On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: function getMax($table, $field) If I saw this sort of code I'd be appalled! It's possibly the worst way to get the auto increment value. You won't notice it testing the site out on your own, but all hell will break loose when you start getting a lot of hits, and two people cause an auto increment at the same time! Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
On Wed, Feb 3, 2010 at 12:35 AM, Ashley Sheridan wrote: > It's the reason transactions exist, to prevent things happening like this. > When you have two actions where one is dependent on the other, unless you > have a way to tie them together so that they can't be broken, you run the > risk of collisions. > > Yea, and i wish they'd standarized features like that across sql servers. But they haven't, so i avoid them like the plague. Whatever dependencies and threading problems might arise, there's always the principle that says: If it doesn't work whlie it should work and threading-timing problems are the only possible cause, then by delay by a random timeperiod and retry the query. In really advanced cases, one can work with last-modified timestamps and/or build up a simple sort of work-queue (also in a table), whereby threads inform each other of the status of their computations.
Re: [PHP] database abstraction layer
On Wed, 2010-02-03 at 00:31 +0100, Rene Veerman wrote: > On Wed, Feb 3, 2010 at 12:18 AM, Ashley Sheridan > wrote: > > > The problem is where 2 people choose the same instant to perform an > > action on your site that inserts a record into your db. The db engine > > inserts them one after the other, and then responds about the max(id) to > > your PHP script. Then, you now have 2 people who have the same max(id) > > retrieved, but one of the values is wrong. > > > > well, i only use getmaxid()s for inserts. > the timelag between getmaxid() and the insert is so small it'd take 300-800 > insert-requests/sec > (on that particular table) before an error condition would arise. > in which case, a tested piece of sql would fail, and can be routed through > the retry functions . > These would imo provide ample timing re-randomization, aswell as a measure > of stress-relief for both php and mysql server. > You may correct me if i'm wrong :) > > BTW: php core developers: can we have a sleep() that accepts a float? :) It's the reason transactions exist, to prevent things happening like this. When you have two actions where one is dependent on the other, unless you have a way to tie them together so that they can't be broken, you run the risk of collisions. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
On Wed, Feb 3, 2010 at 12:18 AM, Ashley Sheridan wrote: > The problem is where 2 people choose the same instant to perform an > action on your site that inserts a record into your db. The db engine > inserts them one after the other, and then responds about the max(id) to > your PHP script. Then, you now have 2 people who have the same max(id) > retrieved, but one of the values is wrong. > well, i only use getmaxid()s for inserts. the timelag between getmaxid() and the insert is so small it'd take 300-800 insert-requests/sec (on that particular table) before an error condition would arise. in which case, a tested piece of sql would fail, and can be routed through the retry functions . These would imo provide ample timing re-randomization, aswell as a measure of stress-relief for both php and mysql server. You may correct me if i'm wrong :) BTW: php core developers: can we have a sleep() that accepts a float? :)
Re: [PHP] database abstraction layer
On Wed, 2010-02-03 at 00:21 +0100, Rene Veerman wrote: > the auto_increment sytnax is not uniform across servers, is it? > > On Wed, Feb 3, 2010 at 12:11 AM, Ashley Sheridan > wrote: > > > I saw it happen on a site that was getting only about 3000 hits a day. It > > just takes the right combination of circumstances and it all goes pear > > shaped. You really should get out of the habit of doing it. > > It is a MySQL only function. MSSQL has @@IDENTITY, not sure how other engines implement it. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
On Wed, 2010-02-03 at 00:17 +0100, Rene Veerman wrote: > and after the sleep(rand(1,3)) it might need a short loop like this; > $rnd = rand(1,9); $a=0; > for ($i=0; $i<$rnd; $i++) { $a++ } > > to further randomize the retry attempt.. > > > On Wed, Feb 3, 2010 at 12:05 AM, Rene Veerman wrote: > > > i haven't had the pleasure yet of writing for sites that generate so many > > hits/sec that > > they'd update the max value of any table at exactly the same time. > > > > i usually ask for the max value about 2 milliseconds before doing the > > insert. > > And if the insert fails, i can auto-retry via a wrapper function after > > sleep(rand(1,3)); > > I dare say i could work this way at facebook (not that i really want > > to, happy with where i am) > > > > > > On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan > > wrote: > > > >> On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: > >> > >> function getMax($table, $field) > >> > >> > >> If I saw this sort of code I'd be appalled! It's possibly the worst way to > >> get the auto increment value. You won't notice it testing the site out on > >> your own, but all hell will break loose when you start getting a lot of > >> hits, and two people cause an auto increment at the same time! > >> > >> Thanks, > >> Ash > >> http://www.ashleysheridan.co.uk > >> > >> > >> > > The problem is where 2 people choose the same instant to perform an action on your site that inserts a record into your db. The db engine inserts them one after the other, and then responds about the max(id) to your PHP script. Then, you now have 2 people who have the same max(id) retrieved, but one of the values is wrong. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
the auto_increment sytnax is not uniform across servers, is it? On Wed, Feb 3, 2010 at 12:11 AM, Ashley Sheridan wrote: > I saw it happen on a site that was getting only about 3000 hits a day. It > just takes the right combination of circumstances and it all goes pear > shaped. You really should get out of the habit of doing it. >
Re: [PHP] database abstraction layer
eh thats "randomize the timing of the retry attempt".. On Wed, Feb 3, 2010 at 12:17 AM, Rene Veerman wrote: > and after the sleep(rand(1,3)) it might need a short loop like this; > $rnd = rand(1,9); $a=0; > for ($i=0; $i<$rnd; $i++) { $a++ } > > to further randomize the retry attempt.. > >
Re: [PHP] database abstraction layer
and after the sleep(rand(1,3)) it might need a short loop like this; $rnd = rand(1,9); $a=0; for ($i=0; $i<$rnd; $i++) { $a++ } to further randomize the retry attempt.. On Wed, Feb 3, 2010 at 12:05 AM, Rene Veerman wrote: > i haven't had the pleasure yet of writing for sites that generate so many > hits/sec that > they'd update the max value of any table at exactly the same time. > > i usually ask for the max value about 2 milliseconds before doing the > insert. > And if the insert fails, i can auto-retry via a wrapper function after > sleep(rand(1,3)); > I dare say i could work this way at facebook (not that i really want > to, happy with where i am) > > > On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan > wrote: > >> On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: >> >> function getMax($table, $field) >> >> >> If I saw this sort of code I'd be appalled! It's possibly the worst way to >> get the auto increment value. You won't notice it testing the site out on >> your own, but all hell will break loose when you start getting a lot of >> hits, and two people cause an auto increment at the same time! >> >> Thanks, >> Ash >> http://www.ashleysheridan.co.uk >> >> >> >
Re: [PHP] database abstraction layer
On Wed, 2010-02-03 at 00:05 +0100, Rene Veerman wrote: > i haven't had the pleasure yet of writing for sites that generate so many > hits/sec that > they'd update the max value of any table at exactly the same time. > > i usually ask for the max value about 2 milliseconds before doing the > insert. > And if the insert fails, i can auto-retry via a wrapper function after > sleep(rand(1,3)); > I dare say i could work this way at facebook (not that i really want to, > happy with where i am) > > On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan > wrote: > > > On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: > > > > function getMax($table, $field) > > > > > > If I saw this sort of code I'd be appalled! It's possibly the worst way to > > get the auto increment value. You won't notice it testing the site out on > > your own, but all hell will break loose when you start getting a lot of > > hits, and two people cause an auto increment at the same time! > > > > Thanks, > > Ash > > http://www.ashleysheridan.co.uk > > > > > > I saw it happen on a site that was getting only about 3000 hits a day. It just takes the right combination of circumstances and it all goes pear shaped. You really should get out of the habit of doing it. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
i haven't had the pleasure yet of writing for sites that generate so many hits/sec that they'd update the max value of any table at exactly the same time. i usually ask for the max value about 2 milliseconds before doing the insert. And if the insert fails, i can auto-retry via a wrapper function after sleep(rand(1,3)); I dare say i could work this way at facebook (not that i really want to, happy with where i am) On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan wrote: > On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: > > function getMax($table, $field) > > > If I saw this sort of code I'd be appalled! It's possibly the worst way to > get the auto increment value. You won't notice it testing the site out on > your own, but all hell will break loose when you start getting a lot of > hits, and two people cause an auto increment at the same time! > > Thanks, > Ash > http://www.ashleysheridan.co.uk > > >
Re: [PHP] database abstraction layer
On Tue, 2010-02-02 at 23:19 +0100, Rene Veerman wrote: > function getMax($table, $field) If I saw this sort of code I'd be appalled! It's possibly the worst way to get the auto increment value. You won't notice it testing the site out on your own, but all hell will break loose when you start getting a lot of hits, and two people cause an auto increment at the same time! Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] database abstraction layer
oh, on using adodb.sf.net and 0-overhead for jumping between mysql and postgresql; keep all your queries to as simple & early-standard sql as possible. the auto_increment incompatibilities can be circumvented with a relatively simple function getMax($table, $field) { in adodb, you'd loop through a huge dataset like this, ensuring proper comms & mem-usage betweeen the db server and php. $dbConn = adoEasyConnection(); //returns adodb connection object, initialized with values from config.php $sql = 'select * from data where bladiebla="yep"'; $q = $dbConn->execute ($sql); if (!$q || $q->EOF) { $errorMsg = $dbConn->ErrorMsg(); handleError ($errorMsg, $sql); } else { while (!$q->EOF) { //use $q->fields['field_name']; // from the currently loaded record $q->MoveNext(); } } for short resultsets you could call $q->getRows() to get all the rows returned as 1 multilevel array. instead of difficult outer-join constructs and stored procedures, (that are not portable), i find it much easier to aim for small intermediate computation-result arrays in php, which are used to construct fetch-final-result-sql on the fly. itnermediate &/ result arrays can be stored on db / disk in json, too ;) i built a cms that can store media items and their meta-properties in db, with the ability to update some meta-properties of an arbitrary selection of media items to new values, in 1 go. i had no problem switching from postgresql to mysql, at all, using the methods described above. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
On Tue, Feb 02, 2010 at 01:15:22PM -0800, Daevid Vincent wrote: > > -Original Message- > > From: Lars Nielsen [mailto:l...@mit-web.dk] > > Sent: Tuesday, February 02, 2010 12:24 PM > > To: php-general@lists.php.net > > Subject: [PHP] database abstraction layer > > > > Hi List > > > > I am trying to make a Database Abstraction Layer so I can which the DB > > of my application between MySQL and Postgresql. I have been looking at > > the way phpBB does it, and it seems that it is only then php-functions > > which are different. The SQL seems to be the same. > > > > Is it save to assume that I can use the same SQL, or should i > > make some > > exceptions? > > > > Regards > > Lars Nielsen > > There are differences in the actual schema between mySQL and Postgress. > > At least there were a few years back when we looked at converting. In the > end, we decided it was too much hassle to switch all our code and database > tables, so just coughed up the licensing for mysql (we were shipping mysql > on our appliance). > > So, before you jump into writing all this code, I would first try to make > your app run in postgress and find out about which mySQL statements are > 'extensions' to ANSI standard. Case sensitivity was a huge issue for us, as > in one of those RDBMS was very particular about it. There were some other > issues that I can't remember ATM, but perhaps they've been addressed by > now. > > One thing I would maybe suggest is (what I do), write a wrapper around your > wrapper -- AKA "Double Bag It". :) > > Here at Panasonic Avionics (PAC) we use the PEAR::DB class (the really old > version) since we have to interface with mySQL, SQL Server, Oracle (two > versions). That's where PEAR::DB comes in. However, it's very crude and you > have a lot of redundant code in every page. Like this: > http://pear.php.net/manual/en/package.database.db.intro-fetch.php > You always have to open a connection, test for errors, do the query, test > for errors, fetch the rows, etc.. > > When I came on board a year ago, I put an end to that micky mouse crap. I > wrote a nice db.inc.php wrapper that handles all that sort of thing, and > then pumps it up like it's on steroids. I added auto-reconnect in case the > connection dropped. I added color-coded SQL output with substitution for > the '?'. I added a last_insert_it() routine which is proprietary to mySQL > BTW (speaking of incompatibilities). I added routines to get an Enum > column, or to get a simple array pairing, etc. It can even force reads from > slave and writes to master! It pretty much kicks ass. > > Just simply do this: > > $myfoo = sql_query('agis_core', 'SELECT * FROM foo WHERE bar = ?', $bar); > > All the minutia is handled for you and $myfoo is now an array of your > results. :) > > So, now we code using my wrapper and should we want to switch out the DBAL > later to a more modern one, we just change OUR wrapper calls. There is > minimal overhead, and the pros FAR outweigh any cons. +1 Though I would use PDO instead of Pear::DB. Also sequential/autoincrement values are differently specified in MySQL/PostgreSQL. I did something similar to Daevid using PDO, and also wrote a "last_insert_id()" function. It requires the database class to know what flavor of SQL it's using, and implements the proper function to return the ID based on that (PostgreSQL has its own version). I would also suggest that failed queries and commands (not just no useful result) terminate script execution. PDO functions generally return false when you feed them absolute garbage, and you don't want to try to continue execution after that. An awful lot of SQL is the same between engines, but there are a lot of edge cases. The only other alternative is something like Active Record, and I personally wouldn't wish that on anyone. My personal opinion is that a programmer should learn the SQL dialect he's working with and use it, rather than something like Active Record. Internally we use PostgreSQL exclusively. The only time I use MySQL is for customer sites where their hosting companies don't support PostgreSQL. In that case, I simply write SQL targetted at MySQL's dialect. It all goes through the same database class to perform error checking and results return. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] database abstraction layer
> -Original Message- > From: Lars Nielsen [mailto:l...@mit-web.dk] > Sent: Tuesday, February 02, 2010 12:24 PM > To: php-general@lists.php.net > Subject: [PHP] database abstraction layer > > Hi List > > I am trying to make a Database Abstraction Layer so I can which the DB > of my application between MySQL and Postgresql. I have been looking at > the way phpBB does it, and it seems that it is only then php-functions > which are different. The SQL seems to be the same. > > Is it save to assume that I can use the same SQL, or should i > make some > exceptions? > > Regards > Lars Nielsen There are differences in the actual schema between mySQL and Postgress. At least there were a few years back when we looked at converting. In the end, we decided it was too much hassle to switch all our code and database tables, so just coughed up the licensing for mysql (we were shipping mysql on our appliance). So, before you jump into writing all this code, I would first try to make your app run in postgress and find out about which mySQL statements are 'extensions' to ANSI standard. Case sensitivity was a huge issue for us, as in one of those RDBMS was very particular about it. There were some other issues that I can't remember ATM, but perhaps they've been addressed by now. One thing I would maybe suggest is (what I do), write a wrapper around your wrapper -- AKA "Double Bag It". :) Here at Panasonic Avionics (PAC) we use the PEAR::DB class (the really old version) since we have to interface with mySQL, SQL Server, Oracle (two versions). That's where PEAR::DB comes in. However, it's very crude and you have a lot of redundant code in every page. Like this: http://pear.php.net/manual/en/package.database.db.intro-fetch.php You always have to open a connection, test for errors, do the query, test for errors, fetch the rows, etc.. When I came on board a year ago, I put an end to that micky mouse crap. I wrote a nice db.inc.php wrapper that handles all that sort of thing, and then pumps it up like it's on steroids. I added auto-reconnect in case the connection dropped. I added color-coded SQL output with substitution for the '?'. I added a last_insert_it() routine which is proprietary to mySQL BTW (speaking of incompatibilities). I added routines to get an Enum column, or to get a simple array pairing, etc. It can even force reads from slave and writes to master! It pretty much kicks ass. Just simply do this: $myfoo = sql_query('agis_core', 'SELECT * FROM foo WHERE bar = ?', $bar); All the minutia is handled for you and $myfoo is now an array of your results. :) So, now we code using my wrapper and should we want to switch out the DBAL later to a more modern one, we just change OUR wrapper calls. There is minimal overhead, and the pros FAR outweigh any cons. I've attached it here. We have another config.inc.php that has the DB settings for each DEV/TEST/PROD master/slave servers (as they are all different accounts for security reasons. So just make one with entries like this: // AGISCore Database DEV Master mySQL: $global_db_dsn_agis_core_master = array( 'phptype' => 'mysql', 'username' => 'RWMaster', 'password' => 'rwmaster', 'hostspec' => '10.10.10.2:3306', 'database' => 'agis_core', 'persistent' => TRUE ); // AGISCore Database DEV Slave mySQL: $global_db_dsn_agis_core_slave = array( 'phptype' => 'mysql', 'username' => 'ROSlave', 'password' => 'roslave', 'hostspec' => '10.10.10.3:3306', 'database' => 'agis_core', 'persistent' => TRUE ); $GLOBALS['DB_CONNECTIONS'] is a singleton (sans the class overhead) so that you always get the same handle for each database call and don't spawn new ones each time. Nice. :) * @dateCreated: 2009-01-20 * @version CVS: $Id: db.inc.php,v 1.39 2010/01/29 01:35:30 vincentd Exp $ */ require_once '/usr/share/php/DB.php'; $SQL_OPTION['noHTML']= false; $SQL_OPTION['fullQuery'] = true; $SQL_OPTION['useLogger'] = false; $SQL_OPTION['profile'] = 0; $SQL_OPTION['debug'] = false; $SQL_OPTION['outfile'] = false; //set this to a filename, and use $show_sql in your queries and they'll go to this file. $GLOBALS['DB_CONNECTIONS'] = array(); //this will hold each db connection so we'll only create one at a time. like a singleton. /** * A wrapper around the SQL query function that provides many extra features. * * Handles the $db handle, er
Re: [PHP] database abstraction layer
On Tue, Feb 02, 2010 at 09:23:47PM +0100, Lars Nielsen wrote: > Hi List > > I am trying to make a Database Abstraction Layer so I can which the DB > of my application between MySQL and Postgresql. I have been looking at > the way phpBB does it, and it seems that it is only then php-functions > which are different. The SQL seems to be the same. > > Is it save to assume that I can use the same SQL, or should i make some > exceptions? > > Regards > Lars Nielsen Quote of values is different between PostgreSQL and MySQL. I would suggest you do a wrapper class around the PDO classes, which will take care of quoting, etc. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
i'm a fan of adodb.sf.net, which i've used with both postgresql and mysql. On Tue, Feb 2, 2010 at 9:23 PM, Lars Nielsen wrote: > Hi List > > I am trying to make a Database Abstraction Layer so I can which the DB > of my application between MySQL and Postgresql. I have been looking at > the way phpBB does it, and it seems that it is only then php-functions > which are different. The SQL seems to be the same. > > Is it save to assume that I can use the same SQL, or should i make some > exceptions? > > Regards > Lars Nielsen > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Lars Nielsen wrote: Hi List I am trying to make a Database Abstraction Layer so I can which the DB of my application between MySQL and Postgresql. I have been looking at the way phpBB does it, and it seems that it is only then php-functions which are different. The SQL seems to be the same. Is it save to assume that I can use the same SQL, or should i make some exceptions? Simple SQL is almost identical. But there are many of the more advanced functions that have major differences. Check out ADOdb for an existing abstraction layer that handles a lot of them. http://adodb.sourceforge.net/ -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk// Firebird - http://www.firebirdsql.org/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Lars Nielsen wrote: Hi List I am trying to make a Database Abstraction Layer so I can which the DB of my application between MySQL and Postgresql. I have been looking at the way phpBB does it, and it seems that it is only then php-functions which are different. The SQL seems to be the same. Is it save to assume that I can use the same SQL, or should i make some exceptions? Is there a reason why you want to write your own instead of using something like Pear MDB2? With Pear MDB2 - if your SQL syntax is database specific it will work in the specific database but MDB2 will not try to port a specialized SQL string to another database. It will port some features to some databases, IE if you use the MDB2 facilities for prepared statements (highly recommended) and the target database does not support prepared statements, it will emulate them (I think, haven't tried, that's what I recall reading anyway) but for your actual SQL syntax it is best to stick to standard SQL. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] database abstraction layer
Lars Nielsen wrote: Is it save to assume that I can use the same SQL, or should i make some exceptions? Standard SQL should work across all SQL servers with only a few exceptions (for example, MySQL doesn't support full outer joins.) Anything that has to do with server administration, however, such as dealing with users and permissions, will be unique to the db engine. James -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] database abstraction layer
Hi List I am trying to make a Database Abstraction Layer so I can which the DB of my application between MySQL and Postgresql. I have been looking at the way phpBB does it, and it seems that it is only then php-functions which are different. The SQL seems to be the same. Is it save to assume that I can use the same SQL, or should i make some exceptions? Regards Lars Nielsen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: [PHP-DB] PHP Database Abstraction Layer
"Luke Woollard" <[EMAIL PROTECTED]> wrote... : > I once read a great article in the first or second issue of > http://www.phparch.com/ on database abstraction layers. At which point I > used the tutorial as a starting point for creating a very similar structure > I named dbWave. There are only minor differences and a postgresql driver is > now included for the most common pg_* functions. > > I was just wondering if anyone has developed a database abstraction layer > that allows you to separate your SQL queries from your application logic > like dbWave does? I'm looking for a more advanced way of doing this? > > > Attached is dbWave for anyone to look at/use. To run it you need to use the > following tags in your file: > > // DBWave include files > include( "[attached_filename].php" ); > > To instantiate the dbWave object you use the following code in a file name > connect.php > > /* This file instantiates dbWave using our chosen API */ > /* It is automatically generated by the database setup program */ > > // Instantiate dbWave using the MySQL API > $dbWave = new Mysql(); > > // Connect to the database > $dbWave->connect( 'yourhost', 'yourport', 'yourdbname', 'yourdbuser', > 'yourdbpass' ); > ?> ZoomStats uses it: http://sf.net/projects/zoomstats P.S: I wrote that phparch article, and I based it on my ZoomStats experoence which I also founded. -- Maxim Maletsky [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP Database Abstraction Layer
I once read a great article in the first or second issue of http://www.phparch.com/ on database abstraction layers. At which point I used the tutorial as a starting point for creating a very similar structure I named dbWave. There are only minor differences and a postgresql driver is now included for the most common pg_* functions. I was just wondering if anyone has developed a database abstraction layer that allows you to separate your SQL queries from your application logic like dbWave does? I'm looking for a more advanced way of doing this? Attached is dbWave for anyone to look at/use. To run it you need to use the following tags in your file: // DBWave include files include( "[attached_filename].php" ); To instantiate the dbWave object you use the following code in a file name connect.php connect( 'yourhost', 'yourport', 'yourdbname', 'yourdbuser', 'yourdbpass' ); ?> Thanks, Luke Woollard Programmer / Analyst TABORVISION.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database abstraction layer oci
Most powerful meaning being tied to Oracle? :) ODBC is not inherently slower than oci or any native access, and a properly written ODBC driver will actually enforce additional functionality against the back-end database. Best regards, Andrew Hill Director of Technology Evangelism OpenLink Software http://www.openlinksw.com Universal Data Access & Data Integration Technology Providers > -Original Message- > From: Thies C. Arntzen [mailto:[EMAIL PROTECTED]] > Sent: Saturday, March 09, 2002 5:10 AM > To: Andrew Hill > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [PHP] Database abstraction layer oci > > > hi, > > the fastest and most powerful is always to use the native > api. > > i would use the PHP oci driver. > > tc > > On Fri, Mar 08, 2002 at 09:00:17AM -0500, Andrew Hill wrote: > > I suggest simply using ODBC. > > > > Best regards, > > Andrew Hill > > Director of Technology Evangelism > > http://www.openlinksw.com/virtuoso/whatis.htm > > OpenLink Virtuoso Internet Data Integration Server > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > > Sent: Friday, March 08, 2002 5:39 AM > > > To: [EMAIL PROTECTED] > > > Subject: [PHP] Database abstraction layer oci > > > > > > > > > > > > Hi everybody. > > > > > > I would like your opinion on the Database Abstraction Layer > you prefer (I > > > will use it with Oracle 8i) > > > I know that there is Metabase end Pear DB > > > > > > What's your opinion on both or others ? > > > > > > Laurent Drouet > > > > > > > > > > > > -- > > > PHP General Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database abstraction layer oci
hi, the fastest and most powerful is always to use the native api. i would use the PHP oci driver. tc On Fri, Mar 08, 2002 at 09:00:17AM -0500, Andrew Hill wrote: > I suggest simply using ODBC. > > Best regards, > Andrew Hill > Director of Technology Evangelism > http://www.openlinksw.com/virtuoso/whatis.htm > OpenLink Virtuoso Internet Data Integration Server > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: Friday, March 08, 2002 5:39 AM > > To: [EMAIL PROTECTED] > > Subject: [PHP] Database abstraction layer oci > > > > > > > > Hi everybody. > > > > I would like your opinion on the Database Abstraction Layer you prefer (I > > will use it with Oracle 8i) > > I know that there is Metabase end Pear DB > > > > What's your opinion on both or others ? > > > > Laurent Drouet > > > > > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database abstraction layer oci
I suggest simply using ODBC. Best regards, Andrew Hill Director of Technology Evangelism http://www.openlinksw.com/virtuoso/whatis.htm OpenLink Virtuoso Internet Data Integration Server > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Friday, March 08, 2002 5:39 AM > To: [EMAIL PROTECTED] > Subject: [PHP] Database abstraction layer oci > > > > Hi everybody. > > I would like your opinion on the Database Abstraction Layer you prefer (I > will use it with Oracle 8i) > I know that there is Metabase end Pear DB > > What's your opinion on both or others ? > > Laurent Drouet > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Database abstraction layer oci
Hi everybody. I would like your opinion on the Database Abstraction Layer you prefer (I will use it with Oracle 8i) I know that there is Metabase end Pear DB What's your opinion on both or others ? Laurent Drouet -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php