Re: Database abstraction

Hello Andreas, On 15-Sep-00 07:37:45, you wrote: >>BTW, do you know any source of information (URL) or have any pseudo-code >>that can be used to look into data returned by odbc_gettypeinfo() call and >>build a field declaration suitable for use in a CREATE TABLE statement? >In theory, you get all the information required for doing this - you >"only" need a mapping from every possible native datatype to a >corresponding datatype available in the specfic datasource. But how Now, that you mention it, I still can't figure the whole logic of looking into the gettypeinfo results for each type and determine how it should be declared. I wonder if you have some knowlegde that could help me to clear some doubts. >would >you handle a request for a varchar(5000) column when e.g. Oracle only >supports up to 4000? Map it to a CLOB column? Cut it down to 4000 chars? >Or split it to two columns? 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. >The idea of a database abstraction layer sounds compelling, but IMHO >beyond a >certain level it gets impossible. Shure, it would be nice to have an >abstraction for a CREATE TABLE statement - but normally, when building a >web >application, you design your data model, feed it to the database and >that's it. >If you want to support different databases, you supply sql scripts for >them, >probably with the help of a database modeling tool. >After you have installed your application, you will rarely be doing DDL >- >at least you shouldn't be. 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. What usually happens is that because changing an installed database schema is such a hard job to learn how to do right and then do it always without the risks of making mistakes in the necessary statments SQL, that it ends up being something that developers avoid. As consequence of this, developers end up avoiding adding new features that require database schema changes which is usually a very frustrating thing. I developed Metabase precisly with that in mind. If you want to create or change a database schema you don't have to learn the CREATE TABLE or ALTER TABLE syntax, which BTW is not always consistent between databases. Metabase abstracts that for you. Even you make a series of schema changes at once, Metabase will only apply them after the underlying database driver tells that it is able to perform all. You don't have to write SQL scripts because these are DBMS dependent. The driver should know what SQL statements should be performed to achived the requested changes. You even don't have to specify the changes. You edit the current schema definition and Metabase is able to extract the requested changes by comparing with the installed schema. For the developers this is a major relief because it avoids the situation when you ask for the wrong changes by mistake. >Additionally, as Andrew pointed out, ODBC already tries to abstract from >database specific SQL, e.g. by defining the placeholder to be "?". I assume, you mean for replacing values in prepared schema. What I am looking for here is not the data type values but rather the data type definitions. >Another problem related to data design is how to support foreign key >constraints? >While most SQL databases support this, MySQL does not and requires the >application to do this integrity checking - this can't be done by an >abstraction layer. >Shure, you can add the required logic to your application, but why make >the application more complex when your database can handle this more >efficient for you? Personally I think applications should not rely on the ability of the DBMS detect inconsistencies or attempts to violate constraints because applications that run into those problems are necessarily buggy. 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. >You will always have the decide between maximum database portability and >using database specific features depending on your application. >I wouldn't base a complex business/ECommerce application on a database >that >doesn't support transactions or foreign key constraints while a simple >bulletin board might well live without this. Transactions yes, but as I said foreign key is not mandatory. Anyway, if those features are needed the developer has to choose one DBMS that supports them because it is not a thing that any database abstraction will emulate for you. In Metabase you may query the database driver if such features are available and eventually do something else if not, althougth the best option is to switch databases. >IMHO it is good to have an abstraction layer that provides a common >functional or OO interface to databases like Perl's DBI, JDBC, or ODBC. >Apart from being very complicated, such SQL abstraction would add a >serious amount of overhead. That's not necessarily true. I agree that ODBC is very complicated, in such way that Microsoft developed a series of other types of database wrappers like ADO. Anyway, I developed Metabase with the goal to make developers life easier. You may see that as you see the advantage of using a C compiler instead of programming in native assembly language. For instance, Metabase implements a very simple interface that achieves the same effect as with MySQL LIMIT clause to restrict the range of rows that is returned for a result set, except that it works for all supported DBMS. You only have to make a function call before executing a query and the drivers do all the necessary work to make that happen even if the DBMS does not support anything like MySQL LIMIT clause. With some DBMS that do not provide any sort of support for that, there may be some overhead, but it is a ability that many Web developers praise because you need that for display query results split between different pages. These are the things that make database abstractions worth it. >I'm planning to rewrite the ODBC module, lifting it up to ODBC 3.5x - >unfortunatly I'm rather busy these days. Many of the restrictions >mentioned have "historical" reasons - the module was originally >developed >using the Adabas D ODBC driver. It is almost impossible to take into >account >and handle automatically every driver and it's implemantation quirks or >limitations. >I agree that the user should be given more control over setting driver >specific features (e.g. selecting cursor types on statement level) while >keeping things simple and fast for every day use. >Nobody wants to write dozens line of code just to do a simple query and >fetch >the results. Yes, PHP ODBC API needs plenty of work, but for now what it is really needed is a simple function stub for SQLGetInfo and the ability of detect whether a given result column contains a NULL. I was thinking about doing that myself, but since you are the maintainer and I also do not have much time to do it, I wonder if you would not have some time to just add this some time soon. 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 --