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

Re: 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: 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: 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: 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: 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: database abstraction layer

------=_NextPart_000_00C8_01CAA409.C7A7BC00
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

> -----Original Message-----
> From: Lars Nielsen [mailto:lars@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-fetc h.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. :)

------=_NextPart_000_00C8_01CAA409.C7A7BC00
Content-Type: text/plain;
name="db.inc.php"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="db.inc.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.
* @category CategoryName
* @package PackageName
* @see
* @since DART2
* @author Daevid Vincent
* @date Created: 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'] =3D false;
$SQL_OPTION['fullQuery'] =3D true;
$SQL_OPTION['useLogger'] =3D false;
$SQL_OPTION['profile'] =3D 0;
$SQL_OPTION['debug'] =3D false;
$SQL_OPTION['outfile'] =3D false; //set this to a filename, and use =
$show_sql in your queries and they'll go to this file.

$GLOBALS['DB_CONNECTIONS'] =3D 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, errors and echoing of the SQL query itself
* and stores any errors in the $GLOBALS['SQL_ERROR_STRING'];
*
* NOTE: PEAR:DB has many shortcomings, one of which is that the key of =
the returned hash will be lowercased!
*
* Prepared statements can use ?, !, & -- see =
http://pear.php.net/manual/en/package.database.db.intro-exec ute.php for =
more information.
*
* @access public
* @return mixed a hash of data, a result set handle pointer or false
* @param string $database the database to connect to (agis_core) is the =
default
* @param string $sql The SQL query to be executed, this can be SELECT, =
INSERT, UPDATE or DELETE amongst others.
* @param array $sqlvalues The sqlvalues to the $sql. One element per ? =
is required if this parameter is used.
* @param boolean $show_sql output the $sql to the display (for =
debugging purposes usually). false by default.
* @param array $parameters
* int db_fetch_mode (DB_FETCHMODE_ORDERED, DB_FETCHMODE_ASSOC, =
DB_FETCHMODE_OBJECT)
* boolean $show_errors output any errors encountered to the display =
(for debugging purposes usually). true by default.
* boolean $execute useful for debuging when you don't want the SQL =
command to actually execute, but you may want to see the query passed =
i.e. SQL_QUERY($sql, true, true, false); true by default.
* boolean $no_html when using the function in console scripts to =
strip off HTML tags.
* int $parameters['profile'] detail level (1-3) to output the SQL to =
/tmp/SQL_profile.txt.
* profile SQL statements in varying detail levels.
* Detail Levels:
* 1 =3D m/d/y/ h:i:s
* 2 =3D SQL timing
* 3 =3D filename
* @see sql_connect()
* @author Daevid Vincent [daevid.vincent@panasonic.aero]
* @date 06/04/09
* @todo Intercept SELECT vs. INSERT/UPDATE/DELETE and connect to SLAVE =
vs MASTER respectively
*/
function sql_query($database=3D'agis_core', $sql, $sqlvalues=3Darray(), =
$show_sql=3Dfalse, $parameters=3Darray())
{
if (!isset($parameters['db_fetch_mode'])) $parameters['db_fetch_mode'] =
=3D DB_FETCHMODE_ASSOC; //DB_FETCHMODE_ORDERED is the other common one =
to use
if (!isset($parameters['show_errors'])) $parameters['show_errors'] =
=3D true;
if (!isset($parameters['execute'])) $parameters['execute'] =3D =
true;
if (!isset($parameters['no_html'])) $parameters['no_html'] =3D =
false;
if (!isset($parameters['profile'])) $parameters['profile'] =3D 0;
//var_dump($parameters);
//$show_sql =3D true;

//[dv] The PEAR::DB library is so horribly stupid that if you don't =
have any '?' but you do pass in $sqlvalues,
// your result set will be an indexed array instead of a k:v hash =
-- regardless of forcing DB_FETCHMODE_ASSOC. UGH!
if (is_null($sqlvalues) || strpos($sql, '?') ===3D false)
$sqlvalues =3D array();
elseif (!empty($sqlvalues) && !is_array($sqlvalues))
$sqlvalues =3D array($sqlvalues);
//var_dump($sqlvalues);

global $SQL_OPTION;

//[dv] the preg_replace will magically strip out the spaces, newlines, =
tabs and other funky chars to make one nice string.
$sql =3D preg_replace("/\s+/",' ', (preg_replace("/\s/",' =
',trim($sql))) );

if ($SQL_OPTION['debug'] || $show_sql)
{
if ($parameters['no_html'] || $SQL_OPTION['noHTML'] || =
$SQL_OPTION['outfile']) $sql =3D str_replace( array("\n", "\r", ' '), ' =
', $sql);

$out_sql =3D '['.$database.'] '.sql_substitute_values($sql, =
$sqlvalues);
if ($SQL_OPTION['outfile'])
file_put_contents($SQL_OPTION['outfile'], 'SQL: '.$out_sql."\n", =
(FILE_APPEND | LOCK_EX) );
elseif ($parameters['no_html'] || $SQL_OPTION['noHTML'])
echo "SQL: ".$out_sql."\n";
else
{
$out_sql =3D sql_print($out_sql);
echo $out_sql;
}
}

if ($parameters['execute'] ===3D true)
{
// execute query only if it appears to be safe.
if ( ($error_str =3D sql_is_safe_query($sql)) ===3D TRUE )
{
if (!sql_connect($database,false)) return false; //this loads =
$GLOBALS['DB_CONNECTION'][$database]

//start profile stuff for this query
if ($SQL_OPTION['profile'] > 0) $parameters['profile'] =3D =
$SQL_OPTION['profile'];
if ($parameters['profile'] > 0)
{
if (!$handle =3D fopen("/tmp/SQL_profile.txt", 'a'))
{
echo "unable to open file /tmp/SQL_profile.txt\n";
$parameters['profile'] =3D 0;
}
$text =3D date("[m/d/y h:i:s ");
if ($parameters['profile'] >=3D 2) $sql_start =3D microtime(true); =
//start timer
}

//[dv] PEAR::DB is so LAME! that we have to FORCE the mode here,
//despite the fact that it should allow it to be passed as a =
parameter. What Garbage.
=
//http://pear.php.net/manual/en/package.database.db.db-commo n.setfetchmod=
e.php
=
$GLOBALS['DB_CONNECTION'][$database]->setFetchMode($paramete rs['db_fetch_=
mode']);

//determine if we need to do a 'query' or a 'getAll'
//so first grab the very first word of the $sql (stripping out =
newlines)
preg_match('/^\(?(\w+) /', str_replace( array("\n", "\r", ' '), ' ', =
$sql), $matches);
$first_word =3D strtolower($matches[1]);
//echo "first_word =3D $first_word
\n";
if (in_array($first_word, array('select','show','explain')))
{
$result =3D& $GLOBALS['DB_CONNECTION'][$database]->getAll($sql, =
$sqlvalues, $parameters['db_fetch_mode']);
}
elseif ( in_array($first_word, array('insert','update','create', =
'drop', 'delete','set','/*!40014','start')) or =
in_array(strtolower($sql), array('commit','rollback')))
{
//TODO: eventually this should do something like this:
//if ('agis_core' == $database)
// $result =3D& =
$GLOBALS['DB_CONNECTION']['agis_core_master']->query($sql,$s qlvalues);
//else
$result =3D& $GLOBALS['DB_CONNECTION'][$database]->query($sql, =
$sqlvalues);
}
else
{
notification_table('error',"db.inc.php::sql_query() does not know =
how to handle '".$first_word."'
\n".sql_print($sql));
backtrace(true);
exit; //we purposely exit here because any SQL after this point =
might give unexpected results.
}

if (DB::isError($result))
{
//var_dump($result);
preg_match("/\[nativecode=3D(\d+) \*\* (.*)\]/", =
$result->getUserInfo(), $errormatch);
$error =3D ' Error =
'.$errormatch[1]."
\n".$errormatch[2]."
\n";
$result =3D FALSE;
}

//end of profiling stuff for this query
if ($parameters['profile'] > 0)
{
if ($parameters['profile'] >=3D 2) $text .=3D number_format( =
(microtime(true) - $sql_start), 4 ).'s'; //end timer

//we do this here so as not to upset the timer too much
if ($parameters['profile'] >=3D 3)
{
$text .=3D ' '.$_SERVER['SCRIPT_FILENAME'];
$traceArray =3D debug_backtrace();
$text .=3D ' '.$traceArray[1]['file'].' =
('.$traceArray[1]['line'].')';
$text =3D str_replace('/public_html/', '', $text);
}

$sql =3D str_replace("\n", ' ', $sql);
$sql =3D preg_replace('/\s+/',' ', $sql);
if (!fwrite($handle, $text.'] '.$sql."\n"))
{
echo "unable to write to file /tmp/SQL_profile.txt\n";
}

@fclose($handle);
}
}
else
{
$error =3D "Malformed query (".$error_str."). Execution blocked.";
$result =3D FALSE; // indicate that we failed
}

if ($result ===3D false)
{
$GLOBALS['SQL_ERROR_STRING'] =3D '';

// if error has not been set, then we have a 'regular' mysql error. =
Otherwise it is a potentially malicious query.
/*
if(!isset($error))
{
$error =3D mysql_error($GLOBALS['DB_CONNECTION']);
$errno =3D mysql_errno($GLOBALS['DB_CONNECTION']);
}
else $errno =3D 0; // not 'regular' mysql error? well, we need some =
error code anyway.
*/

// trim to size if necessary
if(!$SQL_OPTION['fullQuery']) $error =3D substr($error,0,100)."...";

if ($parameters['show_errors'])
{
if ($parameters['no_html'] || $SQL_OPTION['noHTML']) $sql =3D =
preg_replace("/\s+/",' ', (preg_replace("/\s/",' ',$sql)) );

if (!$out_sql) $out_sql =3D sql_print($sql, $sqlvalues, $database);
$GLOBALS['SQL_ERROR_STRING'] .=3D "SQL ERROR =
::".$error."
SIZE=3D'-2'>".$out_sql."".backtrace(false);

//TODO: [dv] is there a way to determine if we're in a CGI vs. Web =
page?
if ($parameters['no_html'] || $SQL_OPTION['noHTML'])
{
//$GLOBALS['SQL_ERROR_STRING'] =3D preg_replace("/\s+/",' ', =
(preg_replace("/\s/",' ',$GLOBALS['SQL_ERROR_STRING'])) );
echo strip_tags($GLOBALS['SQL_ERROR_STRING'])."\n";
}
else
notification_table('error', $GLOBALS['SQL_ERROR_STRING']);
//echo "

padding: 5px;'>
CLASS=3D'error'>".$GLOBALS['SQL_ERROR_STRING']."

Re: database abstraction layer

On Tue, Feb 02, 2010 at 01:15:22PM -0800, Daevid Vincent wrote:

> > -----Original Message-----
> > From: Lars Nielsen [mailto:lars@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-fetc h.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: 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: database abstraction layer

--=-WyfY1ACSUMc2B4LKjQim
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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



--=-WyfY1ACSUMc2B4LKjQim--

Re: database abstraction layer

--00032555b95af48738047ea621c0
Content-Type: text/plain; charset=ISO-8859-1

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
>
>
>

--00032555b95af48738047ea621c0--

Re: database abstraction layer

--=-p+uodJCiY16wl/S0SvCx
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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



--=-p+uodJCiY16wl/S0SvCx--

Re: database abstraction layer

--000325559b1e81a6fd047ea64f48
Content-Type: text/plain; charset=ISO-8859-1

and after the sleep(rand(1,3)) it might need a short loop like this;
$rnd = rand(1,99999); $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
>>
>>
>>
>

--000325559b1e81a6fd047ea64f48--

Re: database abstraction layer

--=-rxSkvAZd0q4tEfMmMqI0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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,99999); $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



--=-rxSkvAZd0q4tEfMmMqI0--

Re: database abstraction layer

--0015174be6bc63e552047ea652e4
Content-Type: text/plain; charset=ISO-8859-1

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,99999); $a=0;
> for ($i=0; $i<$rnd; $i++) { $a++ }
>
> to further randomize the retry attempt..
>
>

--0015174be6bc63e552047ea652e4--

Re: database abstraction layer

--=-SHc/5pl92VLRJk2wjcQm
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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



--=-SHc/5pl92VLRJk2wjcQm--

Re: database abstraction layer

--0003255566569567e0047ea65ba5
Content-Type: text/plain; charset=ISO-8859-1

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.
>

--0003255566569567e0047ea65ba5--

Re: database abstraction layer

--00032555942ac63268047ea67ea1
Content-Type: text/plain; charset=ISO-8859-1

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? :)

--00032555942ac63268047ea67ea1--

Re: database abstraction layer

--=-8r5Cn264ZNe5531Z67nz
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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



--=-8r5Cn264ZNe5531Z67nz--

Re: database abstraction layer

--00151758f3c480aa85047ea6be9a
Content-Type: text/plain; charset=ISO-8859-1

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.

--00151758f3c480aa85047ea6be9a--

Re: 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: 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: 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: 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,99999); $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: 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: 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: 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: 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?


function fsleep( $seconds )
{
usleep( 1000000 * $seconds );
}

fsleep( .003 );

?>

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: 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,99999); $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: 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,99999); $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: 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: 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: 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: 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: 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: 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: 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