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
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
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: 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
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 l...@mit-web.dk 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
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
-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. :)
?php
/**
* All of the database wrapper functions
*
* This is a wrapper around the PEAR::DB class. It provides many enhancements
including
* a singleton for database handle connections, retries for connections,
debugging with ? substitutions,
* handy routines to populate arrays, select boxes, IN() statements, etc. It
can do SQL timing profiling.
* There are routines for INSERT and UPDATE by simply passing in an array of
key/value pairs.
*
* Confidential property of Panasonic Avionics. Do not copy or distribute.
* @copyright 2006-2010 Panasonic Avionics. All rights reserved.
* @categoryCategoryName
* @package PackageName
* @see
* @since DART2
* @author Daevid Vincent daevid.vinc...@panasonic.aero
* @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.
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
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, 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
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 g (not that i really want to, happy with where i am) On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: 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 g (not that i really want to, happy with where i am) On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: 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
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 rene7...@gmail.com 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 g (not that i really want
to, happy with where i am)
On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan a...@ashleysheridan.co.uk
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
eh thats randomize the timing of the retry attempt..
On Wed, Feb 3, 2010 at 12:17 AM, Rene Veerman rene7...@gmail.com 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
the auto_increment sytnax is not uniform across servers, is it? On Wed, Feb 3, 2010 at 12:11 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: 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
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 rene7...@gmail.com 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 g (not that i really want
to, happy with where i am)
On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan a...@ashleysheridan.co.uk
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
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 a...@ashleysheridan.co.ukwrote: 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, Feb 3, 2010 at 12:18 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: 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:31 +0100, Rene Veerman wrote: On Wed, Feb 3, 2010 at 12:18 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: 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:35 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: 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
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 a...@ashleysheridan.co.uk 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
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 rene7...@gmail.com 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 g (not that i really want to, happy with where i am) On Tue, Feb 2, 2010 at 11:46 PM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: 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
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 g (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
Rene Veerman wrote:
eh thats randomize the timing of the retry attempt..
On Wed, Feb 3, 2010 at 12:17 AM, Rene Veerman rene7...@gmail.com 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: On Wed, Feb 3, 2010 at 12:18 AM, Ashley Sheridan a...@ashleysheridan.co.ukwrote: 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
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 rene7...@gmail.com 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: *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
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
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 rob...@interjinn.com 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
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 a...@ashleysheridan.co.ukwrote: 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
On Wed, Feb 3, 2010 at 5:49 AM, Jochem Maas joc...@iamjochem.com 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
On Wed, Feb 03, 2010 at 06:39:29AM +0100, Rene Veerman wrote: On Wed, Feb 3, 2010 at 5:49 AM, Jochem Maas joc...@iamjochem.com 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 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
