[RFC] PDO / third party DBAL

[EXreaction]

Doesn't this seem a bit much for 3.1?

We don't want to end up with another 3.2...the whole point here was to go with more frequent, less significant (change-wise), feature additions. Significant rewrites that are not required for the feature additions should not be done IMO.

[igorw]

I partially agree, it's surely an investment. But if other things come up it could always be pushed back to 3.2. I'd personally like to use this chance and improve the code base. Nobody is forced to spend time on it, if they don't want to or don't think it's worth it, after all.

[Nelsaidi]

To be honest it doesnt have to be as major.

Roll it out slowly. By this i mean use PDO as the core instead of the current DBAL. Extend PDO with alias methods to allow access to queries to be the same as before (this is in addition to the standard PDO methods, ie sql_query, sql_escape, the fetch methods, etc). - Replace helpers with PDO compatible ones. Perhaps have this as legecy.

This wont require rewriting all existing queries, just the DBAL itself.

Also means new code can make use of the benefits PDO provides, ie prepared steatements.

New functions for phpBB can make use of PDO, aswell as mods - whilst old code is slowly moved over to PDO.

Just an idea.

[igorw]

This wont require rewriting all existing queries, just the DBAL itself.

I never suggested rewriting all queries or using PDO API directly. It has always been about rewriting the DBAL to use PDO behind the scenes.

[igorw]

I have added Zend Framework's Zend Db.

EDIT: Interesting information here, under "Zend Framework Components and their dependency to other Zend Framework Components".

[igorw]

Cleaner I might agree with, maybe sql generation and execution could be split so that php dbal can handle sql differences and hand off built statements to pdo for execution. But I doubt there is going to be a noticeable effect in the big picture.

I thought about this a little more and have come up with a concept.

Query abstraction

Query abstraction means providing DB specific queries. phpBB does this a bit for DBMS server info. And it kind-of does it by doing conditional queries depending on the used DBMS (those ugly switch case statements). It's also needed for DDL since the syntax is quite DBMS specific.

By introducing a proper query abstraction layer the query/syntax abstraction is separated from the actual DB access abstraction. This means you have two sets of drivers. You have DBAL drivers and query drivers. The DBAL driver handles access to the DB, so function calls to PDO, mysqli, etc. would go in there. Of course each of these being a separate driver. The second set of drivers are the query drivers. They allow you to build query for DDL, DBMS info and taking care of DBMS specific quirks. The DBAL driver specifies which query driver it wants to use. So the dbal_mysqli and dbal_pdo_mysql drivers could both use the dbal_query_mysql query driver.

Here's my miserable attempt of drawing it.

This would allow a better separation and reuse of DBMS-specific SQL. Do note that the query abstraction is to be pretty rudimentary. Just a mix of code from the current DBAL driver and db_tools in a separate class. And the db-specific ACP backup code could go in there too.

Of course all of this is all inside the DBAL, no outside code has to change for it to work.

[Oleg]

By prepared statements everybody means parameter placeholders, right?

Query abstraction
(snipped)

Please consider cpu time and memory impact of loading unused php code. In particular with respect to database abstractions queries can typically be segregated into data access and data definition. The data definition queries typically are not used by most pages, especially pages that must be fast.

If such a query abstraction is implemented, i suggest splitting data access and data definition into separate classes so that most code only needs to load data access class. Inheriting data definition from data access sounds like a good idea, it will allow for borderline functions to be moved from one to the other easier and probably also make the usage more convenient.

If this is done the data definition query classes could rapidly grow in size as they absorb what umil currently does and other things (fulltext search index creation, for example, could move into the db layer from search).

[igorw]

By prepared statements everybody means parameter placeholders, right?

Parameter binding, which is achieved through the use of prepared statements (even if they are only prepared once). Probably something along the lines of PHP manual example #3. While there's not many places where full advantage can be taken of prepared statements, I can think of a some. Installation of the board and MODs with initial population of the database. Combined with transactions (it currently uses them?) a great speedup can be achieved. But generally the nice thing about prepared statements is that you can delegate escaping to the driver.

If such a query abstraction is implemented, i suggest splitting data access and data definition into separate classes so that most code only needs to load data access class. Inheriting data definition from data access sounds like a good idea, it will allow for borderline functions to be moved from one to the other easier and probably also make the usage more convenient.

Unless the performance hit really is a problem (benchmarks?) I'd prefer to have them all in the same class. Having definition inherit from access may be convenient but not very nice from a design perspective, imo.

[Oleg]

Benchmarks of dbal by itself won't show much thanks to monstrosities like functions.php. With more and more code being moved to classes in 3.1 benchmarking loading all classes vs minimum required ones might show a difference.

Separating data definition will make it essentially free to add support for more ddl constructs which is something that can be taken advantage of.

[MichaelC]

I think this would probably be a too bigger change for 3.1 personally.