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
