New PDO Extension (Thoughts..)

[Eric.Coleman]

I Think this new PDO extension by Wez and others is amazing... Im tired of all the different abs layers, and choices you can use.... FINNALY! A STANDARD

Do you think this will be in php 5 final? Im curious as to if it will make it in as a default extension or not, I want to use it in one of my apps (though I know it just had it's first release)

What's everyone else think about it? Pros / Cons / Gripes?

- Eric

( PS )
if ya don't know what the PDO extension is, read the following:

http://netevil.org/node.php?nid=44
http://www.schlossnagle.org/~george/...s/260_PDO.html

[mlemos]

Originally Posted by Eric.Coleman

What's everyone else think about it? Pros / Cons / Gripes?

This is puzzling.

One of the purposes of using abstraction layers is to provide as much database independence as possible to applications so you do not have to know and implement workarounds in your applications that deal with database differences.

It does not seem that PDO is meant to provide that. Actually it seems that it would provide just common API function names to access different databases. When it comes to portability that is an inferior solution even to PEAR DB which is already a weak solution.

What PHP needed is something that provides superior portability level like PEAR MDB/Metabase, that makes applications database independent not only at the database access level, but also at the schema management level. This means that you do not have to carry database specific SQL code to access or install databases in different DBMS.

Since PDO does not seem to be meant to provide portability, you may as well access databases with the native APIs and do not bother with the issues of whether or not database abstraction layers are slow or not.

You may as well stick to a DAO class generator that produces database specific code by the means of a database independent API. If you are concerned with speed, that is my recommendation.

Given the fact that PDO is PHP 5 dependent, I do not see many people using it any time soon.

[plugged]

Always taking an opportunity to spam your own products. Tisk, tisk, Manuel.

I'm a big fan of PDO.

[kaimeder]

i have to agree with manuel ...

but plugged: thought the same

[seratonin]

So how exactly would PDO be different from PEAR DB, in terms of "portability"? Is it true that a generic PDO interface sits on top database-specific "drivers"? It almost looks like PDO would be a more powerful replacement for something like PEAR DB. If that is the case, I'm all for it. It would be nice to standardize on something. What am I missing here?

Thanks,

JT

[hlellelid]

Originally Posted by mlemos

What PHP needed is something that provides superior portability level like PEAR MDB/Metabase, that makes applications database independent not only at the database access level, but also at the schema management level. This means that you do not have to carry database specific SQL code to access or install databases in different DBMS.

I think there are two different problems here. There is a lot of merit to a common API. For one thing even if some SQL expressions would need to be modified when moving to a new RDBMS, the task is obviously far easier when you have a common API. Ideally, applications will make more frequent use of stored procecures (esp. as these become available in MySQL), in which case the SQL portability issues will be minimal.

In my opinion Metabase and MDB do way more than a DB abstraction layer should. In particular, the XML schema stuff doesn't fit into typical uses of these tools (feels like a tacked-on addition, since the db abstraction layer doesn't actually make use of these types in standard queries -- when determining datatypes of columns etc.). The XML stuff is limited by nature in what it supports (e.g. last I checked no support for things like fulltext indexes); really this should be its own project. (and maybe someday will when someone creates DB_Schema)

What I do like is the idea behind Metastorage (DAO generation tool, for those who don't know), which IMO is the *only* way to solve the SQL portability issues. Of course, I'm not sure how portable metastorage is, but I assume that you have some sort of adapter system such that the correct SQL is generated for the RDBMS, when there are differences. -- e.g. that you use transactions when working with BLOB columns in PostgreSQL, etc.

The problem is that any solution to solve SQL portability is gonna end up being the lowest common denominator. To some extent this can be mitigated by building DAO objects tailored to specific RDBMS solutions, but still the very fundamental features (like which types are supported) are going to be constrained by the features that the db solutions share.

So, I think PDO is awesome; it doens't eliminate the need for DB adapters to help out w/ the abstraction stuff (like LIMIT support, etc.), but it goes a long way to make DB access in PHP more standard.

Originally Posted by mlemos

Given the fact that PDO is PHP 5 dependent, I do not see many people using it any time soon.

There are a lot of people using PHP5 already. I've seen lots of interest in Propel (& Creole) recently & PHP5 isn't even stable yet. I'd wager that it'll catch on a lot faster than most people are expecting. Just look at number of people running coWiki on PHP5 webservers. At work all new web development is PHP5

Hans

[josheli]

Ideally, applications will make more frequent use of stored procecures (esp. as these become available in MySQL), in which case the SQL portability issues will be minimal.

seems to me that stored procedures, which are by nature db specific, more heavily tie one to a particular db, and create reduced, not increased, portability. i've created many PL/SQL procedures in Oracle. moving those to another db would not be trivial. i'm usually wrong though.

as to PDO, i think George's blog describes the benefits well.

as to mlemos, i defy anyone to find a post of his, on any board on the internet, that doesn't link to either Meta* or phpclasses.

[hlellelid]

Originally Posted by josheli

seems to me that stored procedures, which are by nature db specific, more heavily tie one to a particular db, and create reduced, not increased, portability. i've created many PL/SQL procedures in Oracle. moving those to another db would not be trivial. i'm usually wrong though.

No, you do have a good point. I was thinking of stored procedures from the application perspective: i.e. the stored procedure call in the application would not (should not) need to be changed when moving from one RDBMS to another. Of course, I'm speaking out of turn as I don't know how well PDO supports stored procedures (if at all).

In the DB, I don't argue that moving stored procedures from one system to another would be trivial (although I did imply that, for which I stand corrected).

Originally Posted by josheli

as to mlemos, i defy anyone to find a post of his, on any board on the internet, that doesn't link to either Meta* or phpclasses.

Heh, well true. On the other hand you can't get too mad at someone for plugging free software. I'm guilty too of plugging my apps when I can get away with it.

Cheers,
Hans

[plugged]

Hans and Josh: http://derickrethans.nl/ppl_lemos.php

That pretty much sums it up.

[mlemos]

Originally Posted by plugged

Always taking an opportunity to spam your own products. Tisk, tisk, Manuel.

And what is your positive contribute to this thread?

Have you ever tried to develop an abstraction layer to speak of your experience? No? So why are you complaining about those that share their opinion based on real world experience about the subject?

For a long while I have wondered why certain individuals like yourself that bother to complain loudly in public forums about people that share their experience like I have always done.

Some day somebody brought to my attention that once you get a reasonable level of visibility with popular software like I have written and published, there will always be people that will envy your recognition because they wanted to be as successful but they aren't.

I don't know if it your case, but from the systematic off-topic complaints that you make about my postings, it seems to be. In that case, welcome to the Squad Of Envy.

Now, can you please stick to the topic and give up childish personal attacks?

[mlemos]

Originally Posted by seratonin

So how exactly would PDO be different from PEAR DB, in terms of "portability"? Is it true that a generic PDO interface sits on top database-specific "drivers"? It almost looks like PDO would be a more powerful replacement for something like PEAR DB. If that is the case, I'm all for it. It would be nice to standardize on something. What am I missing here?

Among other things, it seems that PDO is not going to provide sequence or range limit emulation. These are not features original from PEAR DB but PEAR DB and ADO-DB borrowed from Metabase to provide greater level of database independence.

[mlemos]

Originally Posted by hlellelid

I think there are two different problems here. There is a lot of merit to a common API. For one thing even if some SQL expressions would need to be modified when moving to a new RDBMS, the task is obviously far easier when you have a common API.

Sure, but anybody that tried to develop applications that work with many databases knows that the differences are much more than just in SQL.

Originally Posted by hlellelid

Ideally, applications will make more frequent use of stored procecures (esp. as these become available in MySQL), in which case the SQL portability issues will be minimal.

I don't thing the SQL differences of the store procedures are minimal because store procedures are just sequences of SQL statements that otherwise would be run one by one from the client. These SQL statements may still have literal values and other constructs that are represented differently on each database.

Originally Posted by hlellelid

In my opinion Metabase and MDB do way more than a DB abstraction layer should. In particular, the XML schema stuff doesn't fit into typical uses of these tools (feels like a tacked-on addition, since the db abstraction layer doesn't actually make use of these types in standard queries -- when determining datatypes of columns etc.).

It is not the typical use of abstraction layers because until Metabase no other package tried to do it, not in PHP nor in any language, AFAIK. That does not mean that is not a desirable feature.

You remind me ADODB author that for a long time claimed that schema management was not important, until someday when some contributor decided to borrow the idea from Metabase and implement an ADODB version. Now it even ships with the standard ADODB distribution. ;-)

Of course the hard part which is computing the differences between the installed schema and a new schema and execute the necessary alteration statements, seems to be missing. That is no surprise because it takes a lot of skill and experience to do it and execute by the safe order. They may as well make it easy by copying Metabase code integrally. ;-)

As a matter of fact Metabase schema management solves very important and difficult problems like how to install a schema without having to write database specific SQL all the time, or how to upgrade a database schema from a previously installed schema arbitrary version.

The use of this capability has several types of consequences. One of them is to make the developer feel more confident to make schema changes without screwing up data that is already stored in the database and can't be at the risk of loss which is what happens when developers try to write schema upgrade scripts manually. Humans make mistakes. Schema maintenance mistakes may be too expensive unless performed by a reliable package such as Metabase schema manager.

Without the risk of making schema change mistakes, developers feel more confident and willing to add more application features that depend on making schema changes.

Originally Posted by hlellilid

The XML stuff is limited by nature in what it supports (e.g. last I checked no support for things like fulltext indexes);

That is not a design limitation. The matter is that nobody felt the need and the courage to implement that.

Originally Posted by hlellilid

Really this should be its own project. (and maybe someday will when someone creates DB_Schema)

I don't think so. The schema management classes are already separated from the core Metabase classes. You only use them if you want. Now, if you want to use them, you still need to call Metabase core classes to establish database connections and execute queries.

Originally Posted by hlellilid

What I do like is the idea behind Metastorage (DAO generation tool, for those who don't know), which IMO is the *only* way to solve the SQL portability issues. Of course, I'm not sure how portable metastorage is, but I assume that you have some sort of adapter system such that the correct SQL is generated for the RDBMS, when there are differences. -- e.g. that you use transactions when working with BLOB columns in PostgreSQL, etc.

Currently it generates code that uses Metabase but in the future it wil generate code that calls native database API PHP functions but they will do the same, probably more optimized. Anyway, the API of the classes generated by Metastorage is database independent.

Metastorage is not storage container specific. The API that is generated will be the same to access different databases or even other storage containers like XML files, flat files, data in LDAP servers, etc.. Even for the same databases, different implementations of the DAO classes may be generated, like for instance using stored procedures for top speed instead of multiple queries executed one at a time when it pays.

Originally Posted by hlellelid

There are a lot of people using PHP5 already. I've seen lots of interest in Propel (& Creole) recently & PHP5 isn't even stable yet. I'd wager that it'll catch on a lot faster than most people are expecting. Just look at number of people running coWiki on PHP5 webservers. At work all new web development is PHP5

I would say that those a great enthusiasts that want to be early adopters. The way I see until PHP 5.0 is officially released, most people will be holding their breath.

Even after that, sensible people will let some time until the release dust rests because it is only natural that the first releases will have a lot of bugs that were not even detected precisely because PHP 5 has not yet attracted so many users to put their code bases to the test.

[lastcraft]

Hi...

Today feels kind of surreal. It's not that pigs can fly, although I did see one this afternoon. It's not that elephants can be pink, in fact I never doubted it. It's not even George Bush as president, I never cared for satire anyway. Nope, it's something else. It's that I am agreeing with Manuel...

Originally Posted by mlemos

Now, can you please stick to the topic and give up childish personal attacks?

Quite right. This is one of the best forums around because we generally stick to the topics and people are free to express their often half baked ideas without being savaged. Also for once a Metabase comment is entirely appropriate.

Hans: You are right. Cutting an abstraction layer at the widest part of the interface is a bad idea. Database "abstraction" at the table level attempts this accross the whole SQL language and the different variations (e.g. auto increment), the common DB type systems (all different) and stored procedures (spectacularily different). Never mind all the schema tricks such as views and triggers. The correct place to cut it is the object level where the actions can be mapped either to common operations or to specialist queries for performance.

PDO does give one useful abstraction though, that of connections. If you are using multiple transactions you will need to handle multiple connections. Having a common connection interface means your connection pool can be used for different systems. That is a slight advance on tha native interfaces. In fact it could be thought of as a refactoring of the PHP libraries. 50+ functions could be dropped from the manual and that is a spring clean I would be all in favour of.

yours, Marcus

[sike]

Originally Posted by plugged

Hans and Josh: http://derickrethans.nl/ppl_lemos.php

That pretty much sums it up.

i am a bit sad seeing this kind of stuff on this forums here. advanced php forum is no kindergarten. fight your personal wars via pm or at least just ignore him?

Sike :[

[McGruff]

I don't know anything about the disagreements referred to above and I don't want to. This is, I believe, a PDO thread not a mlemos-bashing thread.

Let's hope the sitepoint weapons of mass moderation won't need to be dusted down.

[HarryF]

Rasmus seems also to be doubtful of PDO here.

Does seem the main benefit is making life easier for developers - less to remember - as opposed to making portability or otherwise possible. That may not benefit MySQL users much but for Oracle, in particular, it may make life much easier.

Think it's fair to say the native OCI extension exposes you to more detail of Oracle specifics than is actually pleasant - seems to be designed to cover as many exceptional cases as possible while failing to make common cases easy. It's worth reading what George (who seems to be contributing to PDO) says about Scaling Oracle and PHP, in particular the section titled "Managing Your Interaction with Oracle". Think it sheds some light on the motivation behind this.

[mlemos]

Originally Posted by HarryF

Rasmus seems also to be doubtful of PDO here.

Keep in mind that Rasmus is a systems engineer, not a software engineer. So he tends to see things from the practical every day view, not from planned ahead view.

So, it is not uncommon to see Rasmus complaining against things that we all know that are good software practices while Rasmus challenges such practices maybe because they are not 100% optimal or some other itch that he may have with it.

A clear example of Rasmus complaining against good software practices were his complaints against the use of (OOP) classes in PHP scripts. I guess today he does not complain much against OOP in PHP, but in the past it was very odd to see Rasmus speeches against it.

Another more recent and curious example of Rasmus discouraging highly desirable things for PHP is something that was quoted in the Roadsend PHP compiler page.

It is hard to understand if he is just preaching against it because he does not want to encourage closed source PHP application distribution (so PHP developers can make serious money from their work without sharing the source) or he is just saying that developers should give up on very difficult projects like developing a real compiler like it is Roadsend.

Anyway, in the Rasmus message that you mentioned it seems that he is just stating that he doubts that PDO will ever catch on and be used in many PHP applications to justify inclusion as a core extension, citing dbx as a similar extension that also failed to catch on.

Personally I also think dbx did not catch on because it arrived late and it did not provide an portability features to make it appealing to developers that could use it instead of PHP native database API functions.

Considering that PDO requires PHP 5, I have to agree with Rasmus that PDO chances to catch on are even smaller, because it will be a while until PHP 5 catches on.

[dagfinn]

Originally Posted by mlemos

One of the purposes of using abstraction layers is to provide as much database independence as possible to applications so you do not have to know and implement workarounds in your applications that deal with database differences.

It does not seem that PDO is meant to provide that. Actually it seems that it would provide just common API function names to access different databases. When it comes to portability that is an inferior solution even to PEAR DB which is already a weak solution.

It's inferior in the sense of providing less functionality, isn't that what you're saying? But if it does part of the job in a better way (say, faster), it seems like a valid thing to do.

Also, by doing less it has a better chance at becoming a standard because there's less to disagree on.

[mlemos]

Originally Posted by dagfinn

It's inferior in the sense of providing less functionality, isn't that what you're saying?

No. What I am saying is that a good abstraction layer avoids the need for the developer to know a lot about the differences between databases so he does not have to deal with such differences in his application.

Such differences are not just in the API function names of SQL syntax. Databases may differ in so many details, often in unsuspected aspects, that almost no developer wants know that much about databases.

Some developers do not value the importance of database until they need it. That reflects the lack project planning habits and risk evaluation.

The problem is that if some day you need to switch to another database for some unexpected reason rewriting you applications to cover for the database differences is a major headache.

For instance, MySQL always has been the most popular database used in PHP applications. Recently it seems many people felt the need to drop MySQL for PostgreSQL or something else due to the licensing changes that may make a lot of people pay for MySQL commercial license if they do not want to disclose the source code of their projects.

The problem is that the differences between MySQL and every other database exists and not always trivial. If you did not plan to make your applications as much database independent as possible, you will be going to have a lot of manual work fixing your code.

Another situation is the case of the PHP software developer that sells applications or components that access the database. Certainly his development takes less time and database expertise and his market is much broader if he makes his code database independent. So, as you may see database independence is not just a nice theory but also a matter of making you software development business more viable (most customers, more money, etc..)

[dagfinn]

Originally Posted by mlemos

No. What I am saying is that a good abstraction layer avoids the need for the developer to know a lot about the differences between databases so he does not have to deal with such differences in his application.

And to avoid that, it needs functionality, code, that does the work of smoothing over those differences. I'm not saying that's not desirable; I agree with you that it's a good idea. But it is bigger, more complex and more involved. And software packages that do less are sometimes valuable too, because they do what little they do in a better way than the others.

[mlemos]

Originally Posted by dagfinn

But it is bigger, more complex and more involved.

Bigger, yes, because it does more, but not more complex for the developer that is going to use it because the point is to provide the same functionality with a similar API that hides database specific details so you do not have to deal with them in your application.

Originally Posted by dagfinn

And software packages that do less are sometimes valuable too, because they do what little they do in a better way than the others.

My opinion is that a database abstraction layer that is not meant to provide any level of database independence more than just a unified API, is not worth using because will often offer less functionality than the native database API and still adds some runtime execution overhead that may or not be neglectable.

[kaimeder]

Originally Posted by mlemos

My opinion is that a database abstraction layer that is not meant to provide any level of database independence more than just a unified API, is not worth using because will often offer less functionality than the native database API and still adds some runtime execution overhead that may or not be neglectable.

i strongly prefer "simple" unified db-apis over complex, bloated, heavywheight, pseudo-enterprise db-layers.

reasons are simple:
- i want to understand how this thingie is working.
- i dont want to include about 20 files with an average size of 10kbytes just to abstract my database-calls

- database have SO many different features, u just can NOT abstract them properly.
the reasons you choose a specific database ARE the special features a database provides.
or take the point of tuning, optimization:
you have to change indices, tables, etc. pp... rewriting some queries is really the least problem.

so imho, abstracting-everything-apis are more or less useless crap.

[hlellelid]

Originally Posted by kaimeder

- database have SO many different features, u just can NOT abstract them properly.
the reasons you choose a specific database ARE the special features a database provides.
or take the point of tuning, optimization:
you have to change indices, tables, etc. pp... rewriting some queries is really the least problem.

so imho, abstracting-everything-apis are more or less useless crap.

I think this is a great point that perhaps hadn't quite been articulated before in this thread. The fact is that people choose a database for the features. I chose PostgreSQL for a recent project, for example, because I need sub-selects and ON DELETE triggers. Of course moving my application from MySQL to PostgreSQL would have involved *much* SQL rewriting to take advantage of these new features (even if using MDB as my db abstraction layer).

The only way to truly avoid any db-specific code is to use a DAO layer (like Metastorage, DB_DataObject, Propel) which will necessarily reduce the complexity of the queries to basic ANSI-compliant SQL that works in every supported RDBMS. When you want to actually take advantage of the power of your database, you're going to find yourself writing custom SQL in the DAO objects (which is fine: that's why they're in their own layer) and your project will no longer be portable.

I think 90% of the time people want a db abstraction layer not because they move projects, but because they work on many projects with potentially different backends. That's certainly been my experience, anyway. For those purposes, a unified API is going to be a huge help. I want to see the mysql_ and pg_ functions disappear, really.

On the other hand, I have also found a lot of benefit to having some type abstraction. For example, in Creole, which is based on JDBC API, we have a native -> generic type mapping hashes, which is hugely useful when dealing with different databases -- or multiple databases. Knowing how databases represent date & time values & pulling these out into a PHP friendly (e.g. unix timestamp) format, is very useful from a RAD point of view. And LIMIT/OFFSET support is very useful because it's needed so often & differs so much between systems. Furthermore, I've also become increasingly conscious of injection vulnerabilities, and properly escaping all values in queries can be very tedious. It's great when abstraction layers (PDO too, I believe) will handle that escaping automatically -- not to mention proper BLOB/CLOB handling.

Hans

[cyberlot]

PDO is a compiled module/extension for PHP, It stands to reason its going to be faster them Pear MDB, Metabase, ADOdb and the likes which have to be loaded and compiled.

Fact of the matter though, they all serve the needs of diffrent people. You get a Ford and a Chevy lover in the same room and they can argue non-stop about which is better, neither one winning, but again it comes down to the preference and needs of the person driving.

mlemos, your classes are creative and usefull for a person building a application where they need max portibility and be able to attack the largest possible group of people.

The thing you seem to be forgetting, a large majority of us work for a specific company or on projects with specific goals designed to provide the best use and peformance for a small group of people.

Im sure I am going very far on a limb here but I better google could not just up and swap databases overnight. I would bet they have spent countless time and money optimizing there system for there needs. A compiled layer like PDO with even its minimal extra features might of saved them time without affecting there goal of perfomance.

In the end, to each there own, all our needs are diffrent but to behave so childishly looks bad on the php community. More so when it comes from people that are supposed to be "respected members" of this community.

[mlemos]

Originally Posted by cyberlot

The thing you seem to be forgetting, a large majority of us work for a specific company or on projects with specific goals designed to provide the best use and peformance for a small group of people.

If you bother to pay proper attention to what I say, you may realize that I say that if you want to maximize the performance aspect, you ought to use the native API, not any sort of abstraction layer and take the most of database specific features.

Better than that, if you really want to optimize the performance of your applications, you should avoid database accesses as much as possible because that is often where your bottleneck is, on the database server side, not on the client side.

Note down this thought, "Smarter is better than faster", or "Brains beat muscles".

You can do that by using smart caching techniques, concentrated delayed database accesses. Then whatever database access overhead you may think you database access API may impose, it may be irrelevant because cache access will be much faster.

For instance, I use the file based generic cache class to avoid as much database accesses as possible, especially to data that does not change frequently. It is robust as it use safe locking to prevent simulataneous accesses to corrupt cached data during updates, it is persistent as it survives server restarts, it is scalable as it can be used to cache enourmous amounts of cached data without degrading performance.

Bottom line, before you assume that everybody that invests on portability is wasting performance, consider that you may not be aware of all the techniques to make any overhead imposed by portable API neglectable if not irrelevant.