PEAR Database Abstraction is almost Useless!

Hi,

I am working as a PHP developer on a big project. I have been working with PHP since about 1998.

Just recently, during the last update developement, I began to look at database abstraction layer and I have been working with PEAR:B, PEAR::MDB2 and ADOdb.

I have been testing all solutions on MySQL and PostgreSQL.

Now all in all the main idea behind these layers are great. And the decrease in speed isn't a problem as long as the database doesn't get really large.

One thing, which on the other hand, makes the hole idea fall to the ground, in my opinion, is that none of these solutions provide a way to create tables with queries which will work in all the supported databases! What is the hole point of this when you only can do a SELECT, INSERT and DELETE but not CREATE? Without a CREATE statement which will work on all the supported databases there isn't any tables to work on.

Now I am not talkiing about a simple CREATE, but a simple CREATE with AUTO INCREMENT or SERIAL as it is called on PostgreSQL.

For example, could someone provide a single CREATE query with an ID field which has AUTO INCREMENT enabled for both MySQL and PostgreSQL, not to talk about Oracle and MSSQL - all in one and the same query? Using PEAR DB.

If not, the reason to use these abstraction layes are gone because I still has to write database specific queries, and when I have to write specific queries I might as well just develope a different product for each supported database and then GAIN in speed.

Comments and help are greatly appreceated.

Rico.

Well I wouldn't say useless - but difficult at times to write SQL statements that work cross platform. I personally try to follow the ANSI SQL format since most DB's will recognize and work with it. Generally speaking it usually works - but can require some interesting work arounds (take for example dates - each DB works with them differently with different functions. I used PHP to handle them rather then the DB.)

The Create statements - well you could always make seperate Create files and ask the user which DB they are using and then load that file up. Not great - but workable. As for why the DB don't all follow the same syntax - talk to their creators ;) . There is an SQL ANSI standard - though it is way behind the modern database capabilities.

My $0.02 - take it or leave it as you choose.

Hi,

To be frank I have the same problem. First I must do some tricks to be able to create my databases (one MySQL and one Postgress). All turn arround the DSN . Because I create an MDB2 connection with my DSN and if the connection failed I try to create the database. But the createTable try to use the database name from the DSN to connect to the database???? In a second timne I create an inherited class to be able to implement the good behavior depending on the database:
. Postgres : create a temporary DSN with the database name "template1". Inspiration : http://www.phpfreaks.com/postgresqlmanual/page/managing-databases.html. Thank you to them
. MySQL : create a temporary DSN without the database name.

After what I descovered that my sequences are not declared on my table fields.

And finally I discovered that my blobs are converted for OId on postgres.

I already used database layer abstraction on other platforms and other PEAR packages. But I'm not sure that I'll continue with this package.

To answer the initial question:
MDB2 provides ways to create tables and other database objects in a portable way.

MDB2_Schema even makes it possible to write your schema in a portable XML syntax. It can even compute necessary alterations if you change your XML file and compare it to the old version.

As for Christians issues. I replied to his OID question in the thread he started on this.

As for the other issues. To create a database you need to use createDatabase(). If you are having issues because you already set a database name in the DSN you can set it to an empty string using setDatabase(). However if this is necessary than its a bug in MDB2 that should be fixed. Please file a bug in that case.

Furthermore MDB2 supports sequence (emulated if necessary) or autoincrement (emulated if necessary). If you want autoincrement then you need to set the field option "autoincrement" in your createTable() call.

regards,
Lukas