RE: [PHP] DB stuff

Hello Andrew, On 20-Nov-00 11:57:52, you wrote: >> >As long as you use proper escape syntax, ODBC will take that and >> translate any needed changes against the back end. e.g. Select * from >Table where Date = {d '2000-11-17'} will >> determine what order. >> >> Does this work with all ODBC drivers? >It should; it's part of the ODBC API, not something specific to our drivers. Is there any documentation online of ODBC API explaining that? Does it work with all versions of the ODBC API? >> The point is that Metabase is a framework that lets non-database expert >> developers (99% of the Web developers) write applications that can work >> with different database API and so they can switch database backend server >> without having to rewrite his application or learn a new database API. >This is a good thing, I agree. Also, I believe that there are only a >handful of databases that have the characteristics of being a 'web' >database. ODBC does abstract underlying functions in the majority of cases, >leveling the playing field by enforcing additional functionality on the back >end where necessary. ODBC used WITH Metabase to close that last gap is >useful. Yes, but I have had an hard time to implement things that are crucial for Web development, like the ability to access auto-incremented field values or sequences. I'm afraid I need to subclass the Metabase ODBC driver to properly support the underlying databases. This may mean duplicating the native drivers code, so I guess I won't write Metabase ODBC drivers for those databases that I have already developed specific Metabase drivers, despite there is a lot of people trying to access databases like MS-SQL and others via ODBC. >> Developers often don't have the knowlegde to determine which criteria is >> important to make the the choice. A database API like Metabase takes from >> the developer the burden of making the wrong choice because later they can >> change without hassle. >Yes, but doing the research of decision-support vs. transation optimized >databases is part of design. Anyone who starts coding without evaluating >the details of the approach, tools, etc is probably going to run into many Yes, but you have to bear with the real world. If you look arround many PHP developers only started learning it "last week" and in the "week before" they just discovered HTML. You can't ask them to do proper development design because they do not have knowledge or the notion that they need to do it. >problems in the long run, and I don't think any abstraction layer can >alleviate that without defeating the usefulness of design freedom. One thing does not lead necessarily to the other. You can always write non-portable code with Metabase. >> >I believe that abstraction layers need to address impedance >> >mismatch, and go no further, to allow complete abstraction from the back >> >end, without restricting the options of developers. To this end >> work still needs to be done on things like cursor handling, statement >> preparation with bound parameters, etc. >> >> Metabase does that since a long time before it was publically released. >Metabase supports bound paramaters and cursors? Cool, I didn't know that. If you mean, bound parameters as values that you specify to be inserted in places of a query marked with ?, yes it does. Cursors are basically the same as random access to the rows of a result set. Not very memory efficient, but Metabase base supports that and other things that developers ask a lot and ODBC does not guarantee like getting the number of rows of a result set in advance if and when the developer requests that. >> I don't see the point of making database abstraction API integral part of >> PHP. When you want to add new drivers of fix the existing ones >> you need to ask people to upgrade their PHP version which they often >refuse to do even >> if they can. >Perhaps, but open-source without open-standards strikes me as problematic. Why? Metabase API is fully documented and as long its evolution does not imply breaking backwards compatibility I don't see the problem. I deliberately hold Metabase public release for over a year of development. I decided to release it only when I felt its API was fully tested against most of the popular databases being used for Web development. A lot of developers look at Metabase API and question the need for certain procedures. The truth is that they are there for a reason and that reason is often to accomodate for the particularities of each DBMS. For instance Stig Bakken, that design PEAR-DB told me he didn't think that a PHP database abstraction package didn't need all Metabase has. He told that in the early days of PEAR-DB development. He aims to make PEAR-DB a Perl DBI clone. Perl DBI does not provide enough support for portable Web database development. Maybe Stig one day will look back and realize that PEAR-DB needs what Metabase already has to achieve the level of portability that PHP developers need. >Now if Metabase becomes a standard, and proves itself robust across all >databases and for most development paradigms (and I'm not saying it isn't) >then I think it's a valuable addition to PHP. I developed Metabase first and foremost to use in my Web database applications. I eat "my own dog food" quite often and I am very pleased with it. I believe that if Metabase suits me that well, it will suit well to a lot of other PHP developers. Judging from the stats form PHP Classes site, that seems to be quite true. About 3.400 PHP users downloaded it and it is one of the top downloaded components every week and for all time. As for becoming a standard sanctioned by a commitee, I don't think that will ever happen unless there is good will from several parts. Anyway, that doesn't matter. What matters is that PHP developers make good use of it in their applications. Soon I will release a bunch PHP components to do all sorts of things that interact with databases and Metabase will be the API that is the base for them. I think that will certainly make the difference between a nice database API and a database API of choice that many PHP developers will want. >> >The perception that ODBC is an old or slow standard >> exists because there are old and slow implementation of it. >> >> I am not talking about the drivers, but about the capabilities of >> ODBC API. >The API is not inherently slow, this is proven by the fact that we adhere to >the ODBC API strictly and have exceptionally fast drivers. I was not talking of speed but rather of what ODBC API doesn't provide. >> It's a different thing from what I am talking. AFAIK, cursors do >> not avoid >> database full scans on the server side which is a thing that brings down >> many servers when applications are not ready for big audience. >> >> Metabase select row range limiting is specified before executing a query. >> Cursors are used after the query is executed. The difference is that if >> you can specify the range of selected rows that you want to retrieve >> information, the server can act in a more optimized way and stop the query >> search sooner. >Actually, there are several models of cursors. Server side cursors should >of course be used in this case; these include static, forward, keyset >driven, and mixed. Keyset driven is the most 'intelligent'. One set of >index records is pulled at a time, with the cursor sliding along that set, >only querying the next set of records when the cursor reaches the end. >Using this type of cursor, there is not a full scan - the most intensive >query will be to get a set of index results to build the keyset (e.g. 100 >records at a time.) This typically gives you a nice bi-directional walk of >a record set, with increased performance in adjacent record sets, but >doesn't require a full table query. I'm afraid I am not making my point clear. Let me give you an example: SELECT * FROM my_table usually leads to a full scan, but SELECT * FROM my_table LIMIT 10,10 doesn't. However to specify you just want those result rows, you need to hint the database server before executing the query. AFAIK, server side cursors do not let you hint the database server because they only exist after executing the query even when the database supports server side cursors. Bottom line, there is no way to use ODBC cursors to achive the same effect as specifying the values LIMIT clause. Actually if you don't use a clause like that, there is no guarantee that the server will stop traversion the tables to the end before you can get your data. For instance Oracle, has server side cursors, but you need to use DBMS_SQL package to achieve a similar effect as the LIMIT clause of MySQL. >> >> While we are at it, if you are really concerned in making ODBC a better >> >> option for PHP programmers, you may work on it's API to let developers >> >> be able to figure if a given result value is NULL or not, because >> >> currently it is not possible to distinguish if a text result value is >NULL >> >> or is just an empty string. >Yes, ODBC does return <DB NULL> or <EMPTY STRING> in a result set; I agree >that PHP should return this as well. I with the PHP developer in charge of ODBC API could take care of that soon. >Another thing is not being able to figure if the >> >> underlying DBMS supports transactions. >If a database doesn't support transactions, it shouldn't be used for any >serious work. Any database worth it's salt supports a transaction model of >some sort; even MySQL is implementing them. Yes, but that is a different issue. The ODBC API has a way to query the ODBC driver for transaction support but you can't use it from PHP because PHP ODBC API does not support it. >> >> I plan to develop some database specific ODCB drivers for >> Metabase, but I'm not sure when I will do it. So, I am always open to >contributions. >> According to this poll, 20% of Metabase users want to use the ODBC driver. >> >> http://www.egroups.com/surveys/metabase-dev?id=263873 >> >> If you or anybody else is interested to develop any driver subclasses for >> Metabase ODBC driver, feel free to contact me and I will integrate your >> work. >> >> If you are so commited to promote the use of ODBC among the PHP community, >> I think this can be a great opportunity. >To be honest it's beyond my current technical abilities, but I can certainly >work to make ODBC use easier, document the process, identify problems and >try to fix them. I hope this will benefit all the PHP community, Metabase >users included :) >I look forward to working with the PHP community on this in the future! Personally I do not use ODBC at all, but I took the poll above and it happens that a lot of people (20%) want to use ODBC. So, I spent a lot of time improving the Metabase ODBC driver. There isn't much I can do more due to the problems pointed above. If you can do anything for that, it will be good for your business as well. It's up to you to take the hint. :-) 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 --