Re: Database abstraction

Hello Andreas, Catching up on old unanswered mail. On 16-Sep-00 10:46:32, you wrote: >>The way I see it that is not quite what developers want. They end up doing >>that because upgrading database schemas is not a trivial task to get done >>right in a fail safe way. >I can see your point - but I don't think that PHP is the right tool >here. >There are existing database modeling tools (e.g. ERWin, Powerdesigner) >that >already give you this functionality. You start by designing your >abstract >data model independant of a specific RDBMS and can later on generate >SQL scripts for the various systems. I'm afraid you are overlooking the power of this approach. You do not use PHP design database schemas. You either define your schema by hand typing the XML file that describes the database tables/fields/indexes/sequences or you use a better tool (that could be something like Erwin) that lets you interactively draw your schema and in the end just writes the XML file for you. PHP (Metabase in this case) is only used to install or upgrade database schema by parsing the schema description file. Keep in mind that when I say PHP, I am not using mod_php but rather the CGI executable version that can be run from the command line. The power of this approach is that Metabase is smart enough to figure how to install a database schema for the first time or upgrade or eventually downgrade a previously installed schema. You only need the XML schema description file. You don't have to go back and fourth with a different set of scripts generated by Erwin or whatever. You even don't need a database client in the host machine to install you scripts. PHP will do. Since you already need PHP access the database, it is already there. The database client may not be available. Another good point of using XML files to describe database schemas is that you can easily integrate in CVS or some other version control system. Since XML files are text files you can clearly see the differences between revisions with a simple cvs diff. There is more than this in favour of this approach, but just this is enough for me to tell you that this approach is a blessing and has saved me hundreds of hours of maintenance of Web projects that have to be upgraded all the time in the development, test and production environments. >>That doesn't work quite like that. It's upto the the developer that uses >>the database abstraction package to specify field properties consistent >>with the limitations of the underlying database. If he asks for a table >>with property values that exceed the limits of the DBMS, the CREATE TABLE >>statmente will fail because the database abstraction can not do anything to >>overcome the DBMS limitations. >That's exactly what existing tools already provide. In addition, if you >don't >want to use these tools, you can always use odbc_gettypeinfo() to get >the >datatype specifications and limits - it's always up to the developer >himself >to work around db specific limitations. I wish that was that simple. odbc_gettypeinfo() is usually less than useful. In the end you need to know the capabilities of the underlying database to take proper advantage of it. If you disagree, tell me for instance how do I do implement something like sequences just using odbc_gettypeinfo() ? In Metabase sequences are implemented natively if possible, or using Auto-increment fields if the underlying database does not have real sequences but has auto-incremented fields. With odbc_gettypeinfo() you can't tell if the underlying database supports sequences. >>I see that the ability to set constraints is good to prevent bugs but not >>needed if the programs do not have such bugs. So, I see foreign key >>support as desired feature, but you can live without it. >When your data model gets more complex you will be grateful for >triggers, >stored procedures and foreign key constraints to maintain database >integrity. >Having all these features prevents your application from being broken by >inexpierenced developers. More important, checking the constraints in >the >application yourself requires additional data transfers between the app >and the database server. Network and IPC communication are slow. >Doing 4 or 5 such data transfers instead of 1 is tremendous overhead. Sure, but my point you can do a lot without that. Just look around and see how many people are using MySQL to develop nice applications. I am not defending DBMS that have less features that desired, but you just can't ignore that they still very useful as limited as they are. >As for the NULL value detection, I don't want to add some quick & dirty >hack >to implement this when it might change again with a major rewrite later. >In the meantime, an additional where clause testing for NULL could help >in some cases. PHP ODBC API (along Informix and mini-SQL) with is denoted in Metabase manual as one of several database API that are broken in this area because they are able to clearly tell whether a result field is NULL. Since this is a fault in PHP ODBC API that should up to the developer to fix, I am not doing more than just warning in Metabase manual. If the developers want to work arround this fault, it's up to them to pay attention to the warning in the manual and adjust their queries, but usually they don't do anything and their programs may break until database API under Metabase are fixed. Regards, Manuel Lemos Web Programming Components using PHP Classes. Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org -- E-mail: mlemos@acm.org URL: http://www.mlemos.e-na.net/ PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp --