Friday, March 25, 2005
More like a foolish inconsitency
A foolish consistency is the hobgoblin of little minds.
Ralph Waldo Emerson
You want to use a database for a PHP project. Okay, PHP supports several
different types of databases, like PostgreSQL and MySQL among others, and being SQL, there shouldn't be much
difference bewtween SELECT statements, right? (wrong I
know—there can be huge differences between SQL implementations, but like Alan
Kay said, the simple stuff should be simple). So, I would expect
something like:
$db = db_use('mysql');
db_connect($db,"mysql.example.net","db_user","soopersecretpassword");
db_table($db,"warehouse");
$result = db_query($db,"SELECT widgets FROM parts WHERE color = 'red'");
db_close($db);
$db = db_use('postgresql');
db_connect($db,"pgsql.example.net","db_user","donttellanyone");
db_table($db,"shops");
$result = db_query($db,"SELECT price FROM catalog WHERE parts='widgets'");
db_close($db);
You select the type of database you want to use, and PHP automagically handles the details for you. The simple stuff should be simple, right?
But not in PHP. No. In PHP you get a show bunch of functions that work only on MySQL, and another set for PostgreSQL, and yet another set for Oracle. It's not like once you select a database that you'll end up changing it any time soon, right? Who changes databases? So chances are, once you select a database, that's it and who cares if there's a specific set of functions for this database and a different set for another database?
Well, unless you make it available for other people to use (open source or not) and they want to use one of those other databases? Heaven forbid that happening.
But hey, it could happen, right?
Which gets me to some PHP code I had the pleasure of dealing with today (and no, it wasn't osCommerce):
$result = $system_query("SELECT * FROM sometable$name");
Hello! $system_query? That's a variable! Hmmm
… that's … interesting. Poke around some more …
$list=file("../SQLcalls.txt");
$system_connect = $list[0];
$system_affected_rows = $list[1];
$system_error = $list[2];
$system_insert_id = $list[3];
$system_fetch_row = $list[4];
$system_num_fields = $list[5];
$system_num_rows = $list[6];
$system_query = $list[7];
$system_result = $list[8];
$system_select_db = $list[9];
$system_field_name = $list[10];
$system_connect = ereg_replace("\n","","$system_connect");
$system_affected_rows = ereg_replace("\n","","$system_affected_rows");
$system_error = ereg_replace("\n","","$system_error");
$system_insert_id = ereg_replace("\n","","$system_insert_id");
$system_fetch_row = ereg_replace("\n","","$system_fetch_row");
$system_num_fields = ereg_replace("\n","","$system_num_fields");
$system_num_rows = ereg_replace("\n","","$system_num_rows");
$system_query = ereg_replace("\n","","$system_query");
$system_result = ereg_replace("\n","","$system_result");
$system_select_db = ereg_replace("\n","","$system_select_db");
$system_field_name = ereg_replace("\n","","$system_field_name");
$system_connect = ereg_replace(13,"","$system_connect");
$system_affected_rows = ereg_replace(13,"","$system_affected_rows");
$system_error = ereg_replace(13,"","$system_error");
$system_insert_id = ereg_replace(13,"","$system_insert_id");
$system_fetch_row = ereg_replace(13,"","$system_fetch_row");
$system_num_fields = ereg_replace(13,"","$system_num_fields");
$system_num_rows = ereg_replace(13,"","$system_num_rows");
$system_query = ereg_replace(13,"","$system_query");
$system_result = ereg_replace(13,"","$system_result");
$system_select_db = ereg_replace(13,"","$system_select_db");
$system_field_name = ereg_replace(13,"","$system_field_name");
Um … okay … what's in SQLcalls.txt?
mysql_connect mysql_affected_rows mysql_error mysql_insert_id mysql_fetch_row mysql_num_fields mysql_num_rows mysql_query mysql_result mysql_select_db mysql_field_name
Okay.
A list of the MySQL function calls in PHP are read in, then the trailing line ending characters are stripped. Never mind that could be done as:
$list = file("../SQLcalls.txt");
$system_connect = rtrim($list[ 0]);
$system_affected_rows = rtrim($list[ 1]);
$system_error = rtrim($list[ 2]);
$system_insert_id = rtrim($list[ 3]);
$system_fetch_row = rtrim($list[ 4]);
$system_num_fields = rtrim($list[ 5]);
$system_num_rows = rtrim($list[ 6]);
$system_query = rtrim($list[ 7]);
$system_result = rtrim($list[ 8]);
$system_select_db = rtrim($list[ 9]);
$system_field_name = rtrim($list[10]);
which not only would be slightly faster, but a bit more maintainable and portable (and I don't even know PHP that well and already I'm writing better code in it—sheesh!). But besides, that, the intent of the programmer seems to be a way to isolate the name of the function so that the code can be quickly “ported” to use different databases. Laudable, except for one small little detail—
IT WON'T WORK!
Let's try using PostgreSQL. Well, let's see if we can map the MySQL functions listed to their PostgreSQL or Oracle counterparts:
| MySQL | PostgreSQL | Oracle |
|---|---|---|
| mysql_connect() | pg_connect() | ora_logon() or ora_plogon() |
| mysql_affected_rows() | pg_affected_rows() | ora_numrows() |
| mysql_error() | pg_last_error() | ora_error() |
| mysql_insert_id() | pg_last_oid() (guess) | ? |
| mysql_fetch_row() | pg_fetch_row() | ora_fetch() |
| mysql_num_fields() | pg_num_fields() | ora_numcols() |
| mysql_num_rows() | pg_num_rows() | ora_numrows() |
| mysql_query() | pg_query() | ora_parse() + ora_exec() |
| mysql_result() | pg_get_result() | ? |
| mysql_select_db() | ? | ? |
| mysql_field_name() | pg_field_name() | ? |
It's beginning to look pretty bad, but that's not the worse of it. Let's
just concentrate on the connect functions. mysql_connect()
looks like:
$mysql = mysql_connect("mysql.example.net","db_user","soopersekrit");
But that just connects you to the MySQL server. You still have to select
which database you want to use. Then you have pg_connect():
$pg = pg_connect("host=pgsql.example.net port=5432 dbmame=warehouse user=db_user password=donttellanyone");
In which you can specify the database. Now there's
ora_plogon():
$oracle = ora_plogon("db_user@oracle.example.net","shhhh");
Which supposedly will hook you up with the database in question. I
guess. Because I couldn't locate the PHP Oracle equivalent to
mysql_select_db(). But aside from that, notice anything about
the three calls? Like how they're all completely different?
mysql_connect() takes three parameters (well, there are more,
but they're optional), pg_connect() takes just one, but the
single string argument contains name/value pairs, some of which are
optional! And ora_plogon() takes two.
Nice try, but a wasted effort.
How many variables changed? Let me count the ways …
The previous rant was brought to
you by a site that moved from a Linux 2.4 system running
Insipid, Apache 1.3 with PHP 4.2 and MySQL 3.23.x to a
Linux 2.6 system running Blech, Apache 2.0 with PHP 4.3
and MySQL 3.23.y and wasn't working properly on the new system.
I can't say for certain that the problem isn't Apache, PHP, MySQL or Linux, but each piece works, and they seem to be working together, but given that PHP is the scripting language du jour it's not out of the question that the code which works (for the most part) under PHP 4.2 doesn't work under PHP 4.3.
Or it may be that it works under PHP 4.3 but only under Apache 1.3 and not Apache 2.0.
Or it may be that the database replication from one server to another one didn't work (but all 322 tables have been replicated).
Or it could be that it's a weird interaction with Apache 2.0 and PHP 4.3 and Linux 2.6
And let's not even get into the environmental differences between
Insipid and Blech.
Today was not a fun day (and I never did figure out why the code doesn't work—I may leave that up to the customer to figure out).
