Re: DB portability

Hello mwaples, On 03-Jan-01 11:19:53, you wrote: >> > I am looking at undertaking a very large project that will be handled >> > initially in MySQL. Down the road we may choose a corporate db such as >> > Oracle. My thought was since we will have thousands of lines of code to >> > migrate to the new db, I could create a global script that is required by >> > each individual script. >> > >> > This global script would alias typical database functions (connects, >> > queries, etc...) to PHP's MySQL functions for the time being and I could >> > adjust the alias in one central location if we switch from MySQL (I never >> > foresee this happening but you never know). >> > >> > Can any of you see any repercussions of this central database alias >> > script? >> >> I'm afraid things are not that simple. DBMS work in very different ways >> that it isn't >> just a matter of defining alias for each database. >> >> You may want to try Metabase. Not only it abstracts the access to the >> database but it also abstracts the installation of the schemas so your >> applications can be completely database independent. >> >> http://phpclasses.UpperDesign.com/browse.html/package/20 >> >> Manuel Lemos >I haven't looked at metabase yet - but have a few questions - >what about trigers and stored procedures - how does metabase deal with >them ? It doesn't yet. There isn't much that can be done on portability about things that vary so much between databases. Still that doesn't mean you can't have triggers and stored procedures on applications that you access with Metabase. >Im just getting used to using postgresql, interbase, sybase as well as >myql with php - using a simple class to extract and insert data - >but I'm wondering how you deal with the different features of each >database - Mysql lacks a number of things like triggers and stored >procedures, sub ueries etc, would I lose any database specific >functionality by going with metabase ? No, but if you used those database specific features your application would no longer be that much portable. >Would an application have to be simplified to work with MYsql and your >class ? No, for instance Metabase has an interface to handle transactions. If you try to use transactions on databases that don't support them, the transaction handling calls will fail. OTOH, Metabase implements vital features that are not available in all databases but the respective drivers emulate them if necessary. For instance in MySQL you would use the LIMIT clause to restrict the range of rows that a SELECT statement would retrieve. Metabase supports a similar feature in all supported databases so you can rely on that feature in the applications you use with Metabase regardless of the database you have. >Also what advantage does metabase have over the one in PEAR ? >If I use one wouldn't it be better to use one the ships with php as >eventually it would be more wisely used ? Yes, a lot of people would think like that because they have no further information. There are many things that PEAR-DB has still a lot to catch on. To start, Metabase is not just a database abstraction to access databases. It is also capable of installing and maintaining database schemas specified in a portable format based on XML. If you change your schema definition it will apply the changes without affecting any data added since the first time it was installed or the last time it was changed. This alone provides an higher level of database maintainability. Then Metabase assures real portability. For instance, if you want to handle fields with dates, Metabase assures that you only have to handle with one date format (ISO 8601 - YYYY-MM-DD). Then Metabase adds abstractions for many important things for Web application development like the ability to define a row range that the select statements will provide, like it was mentioned above. Metabase also provides a random access to query results where you can pick data from any row at any order. As a consequence of this it is also always able to fetch the number of rows of a query result even with databases that do not have a function to support that. Other than that Metabase has now over 2 years of development and consequent maturity. That can be demonstrated with the number of supported databases and the number of driver conformance tests that are made available in a test suite script that is provided with the package. As a consequence of this there are several ready to use components being made available here and there that rely on Metabase, like for instance this Query result table display class that not only lists the result rows in an HTML table that can be abritrarily customised but also presents links to go to the Next, Previous, First, Last or any other page of results of a query so you can browse over it. http://phpclasses.UpperDesign.com/browse.html/package/130 There are a whole lot more useful database programming components based on Metabase. Stay tuned. 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 --