Database Abstraction With PHP

PEAR does not offer true database independence because it does not abstract field data type values like for instance JDBC does.

This means that PEAR does not handle the differences in format representations between databases.

Think for instance of Date fields. These are represented in different ways in different databases. If you need to process those field values in your application, you need handle the format representation differences in your application, making it database dependent.

If you would like to use a truely database independence abstraction package in PHP, try "Metabase":http://phpclasses.UpperDesign.com/ .

Metabase not only provides data type abstraction, but also abstracts database schema installation and maintenance.

This means that all you need to do to install and maintain a database schema, is to describe its tables, fields, indexes and sequences in a custom XML file.

Metabase takes care of the rest without requiring you to deal with the different ways to do that with the different types of databases.

does it slow down the execution of queries at all?

1. My (early) PHP4 binary installation seemed to mishandle the PEAR part of the installation.
2. I haven't been able to find manual installation instructions - even hints seem to be aimed at UNIX users, which discourages experiment.
But, database Abstraction I could do with! Anybody got any pointers to manual installation instructions for Windows, please?

I saw about a 70% drop in performance when I plugged the PEAR DB class into some very simple scripts. I'd imagine it would just get worse as the queries and code got more complicated. I read somewhere that it's mainly to do with all the error-checking in the class, which is probably true. The fact that it's written in PHP doesn't help either.

I doubt most people will need database independence - it seems more like a fancy idea to get giddy about. The PEAR DB class is sort of in no-mans-land. It's overkill for anyone wanting a simple, standard DB interface to work with, and it's not at all fast enough for any serious use.

Every abstraction layer I have seen always seems to be missing some sort of insert_id like mysql_insert_id. is there such thing in the PEAR abastraction layer for this?

Database independence is a poor argument for DB abstraction layers for a couple reasons. First, in the real world, people have things known as utilities or programming languages. Most text editors that I have worked with include the ability to ‘find and replace…’ text. It would be trivial for me to open up my code in Text Pad, do ‘find and replace’, check ‘all files’ and then have it replace ‘mysql_’ with ‘oracle_’. Pretty easy – no way would I ever expect someone to do that by hand unless they were looking at a particular file or the application is sufficiently small. Secondly, changing RDBMs’ isn’t something you do every day, nor should it be taken lightly. Expecting to not have to modify code is a false assumption for several reasons:

1) For all but the simplest SELECT statements most RDBMs’ SQL implementation differs enough that you’d have to re-write your complex queries. As shown in your example of transactions, Oracle uses sequences to create auto_incrementing integers. MySQL uses a column type of ‘auto_increment’ and requires you to explicitly insert NULL. Sybase and MS SQL use IDENTITY columns but requires that you leave the identity column OUT OF the SQL statement.

2) You should check EVERY SINGLE QUERY to make sure it performs well in your new environment. Oracle may use an index (or two) in a situation when MySQL will table-scan, causing your query to take 200x as long to complete. Switching your backend DB should not be a ‘fire and forget’ situation!

3) Most Enterprise RDBMs’ include the concept of Stored Procedures. These, actually, are a GREAT way to achieve DB independence (to some degree) in your application. For example, say you have some code like this:
*_query( “sp_get_user_info $userid” );
As long as my stored procedure is named the same, takes the same parameters, and behaves in the same manner I don’t have to change that line at all! I can write the SQL in Oracle’s PL/SQL, Sybase/MS SQL’s T-SQL, etc. and the application logic does not have to change.

However, DB abstraction layers are nice since you can include error checking and array-fetching very easily. I’d say the greatest advantage to using an abstraction layer are the class functions you create for it. To place the first and largest argument on ‘DB independence’ horribly weakens the story since anyone who as ever ported something over knows how incorrect of a statement that is.

--
Matt

Available at <a href="http://freshmeat.net/projects/adodb/">http://freshmeat.net/projects/adodb/</a>

ADODB is a set of advanced PHP database wrapper classes. It supports MySQL, PostgreSQL, Interbase, Oracle, MS SQL 7, Sybase, DB2, FrontBase, Foxpro, Access, ADO, and generic ODBC. A metatype system is built in, making it possible to figure out that types such as CHAR, TEXT, and STRING are equivalent in different databases. It also features an SQL to HTML popup menu and SQL to HTML table support.

ADODB has been around for a while now. It's easy to port to different databases using a configuration file to specify your database type.

I agree with cschilbe here, ADODB is a very nice system. I've found that most of the PEAR things are not very well designed. They seem to 1/2-assed attempt modularity. They better get their act together if they plan on being anything like CPAN is for perl (which is why they started PEAR).

I also find it interesting that the author is "ashamed" that he used to use a greatly superior language (perl), and now feels at home with the weak constructs of php.

Haha, good point Jon!

It didn't used to support transactions, but it surely does now. Much thanks to Gemini, BDB and InnoDB table types.

Recent security reports have made a lot of play about the possibility of malicious user input being able to subvert SQL queries by altering the way they are parsed, and the PHP/MySQL interface does not make it easy to avoid the issues.

The *Perl* DBI offers a good way to handle user input securely using placeholders together with the prepare()/execute() combination. This works because there need be no user-supplied data in the string passed to prepare(), which is where the query is parsed; the user-supplied values are passed instead to the execute() call, where they cannot affect the parsing.

If the *PEAR* DBI prepare()/execute() handles queries in the same manner, this may be the strongest reason to adopt it, regardless of concerns about performance loss.

Provided the execute() function does not re-parse the query, but merely substitutes the user-supplied values in the pre-parsed query (as is implied by Icarus's article), the security benefits of this should be present in the PEAR DBI.

I've been trying to choose the best DAL for a while now and I've tried Metabase, PEAR, phpLib, ADODB.

Here are my experiences...
Metabase looks complicated, very promising and I was exited to use it because apparently it should be easy to port ur applications to different databases. Not depended on compilation of server extensions.

PEAR, I'm using Apache and Windows2000 to develop my app before using it on a linux machine but there isn't any documentation to install it on win2000. I was just going to develop my app on linux but my ISP doesn't provide PEAR support. So it seems like that I won't be creating apps depended on a software that has to be compiled on the server.

phpLib, It works ok, not depended on extensions or PEAR, but some database drivers aren't fully developed.

I've tried ADODB and it's easy to use...
You only need to include 1 core file to do all the important functions and other files to do optional handy features. I like it...