Re: Database Abstraction in Pear (Consistency)

On Sun, 4 Aug 2002 15:56:41 -0700 "jeff" <jeff@altahost.com> wrote: > How consistent is the DA in pear across the different supported > databases. For example I read somewhere that the problem with DA is that > some features in mysql are not supported in oracle, like the > mysql_num_rows function. > > Is there ever a reason why my DB code created for mysql using the pear > DA will not work naturally on Oracle? Where can I learn more about this > topic? Hi Jeff, PEAR::DB usually emulates functionality that is not supported by a specific DBMS in software. However, to ensure that your code works flawlessly on both MySQL and Oracle (or maybe others), you have to take care not to use any SQL features specific to a database. I.e. you can´t use MySQL´s autoincrement function, because it´s not supported by Oracle. Instead, you can use Sequences - which aren´t supported by MySQL, but can be emulated by PEAR::DB. In general, you should try to stick to ANSI92-SQL wherever possible and not use database-specific dialects. If you _have_ to use DBMS-specific funcions, you can for example write a class named "dbfunctions_common" which contains methods for executing queries on the database. Write this class to be functioning with the DBMS you use more frequently. When you have to migrate to Oracle, you can simply extend this class and overload just the few methods that aren´t working anymore with new ones that match Oracle´s specific needs. You can then use a configuration variable in your scripts that tells them which DBMS is used, so that the proper class can be included. Another possibility might be using MDB instead. MDB is a new database abstraction layer in PEAR and unfortunately only deals with MySQL and PostgreSQL yet which is the reason why I didn´t try it out myself (so far). It has a very interesting concept that was "borrowed" from Metabase, a non-PEAR abstraction layer, that should reduce the need to write your own SQL to a minimum, so the out-of-the-box compatibility to other DBMS should be better than with PEAR::DB. Hope this helps. Regards, Markus -- Markus Wolff <wolff@21st.de>