[RFC] PDO / third party DBAL

[igorw]

phpBB has had its own DBAL for ages. In the phpBB2 days that was awesome. Since PHP 5.1 however, there is PDO (PHP Data Objects), a standard DBAL that comes with PHP itself. phpBB 3.1 will be PHP 5.2+.

PDO

PDO provides an object oriented API for accessing DBMS'. It has drivers for all DBMS phpBB currently supports, and more (for example sqlite3). Even experimental MSSQL native support exists.

Since PHP provides this abstraction, it would be great if phpBB 3.1 could make use of it. Using PDO directly is not an option, because all existing code would have to be rewritten. But it would be nice if PDO could be used behind the scenes. Instead of using DBMS specific functions in separate drivers, PDO would be used instead.

Advantages:

• Adding support for a new DBMS is even easier
• Code is cleaner, less duplication and boilerplate
• Easier to add new features like prepared statements

Disadvantages:

• Lots of work
• Things could break

Third party DBAL

phpBB's DBAL provides a bit more than just abstraction of DBMS access. There is sql_server_info() for DBMS server information. _sql_query_limit() that takes care of some LIMIT quirks, _sql_like_expression for LIKE expressions, _sql_report for profiling. There are DDL functions in db_tools. There are some generic SQL building functions like sql_in_set(), sql_build_array() or sql_build_query().

PDO does not provide any of those. So we would have to create a custom driver for each nevertheless, although much a thinner one than current DBAL drivers, because PDO could be use for the most part. Another thing that needs to be considered is the lack of query abstraction. While there are not many, DBMS-specific queries do exist in phpBB. It would be a good idea to allow drivers to specify a "family" like "mysql", "sqlite" or "mssql".

Instead of maintaining this "extra functionality" it might be possible to use a third-party solution.

Here's a non-exhaustive-list:

• PEAR's MDB2: does not use PDO
• prosper: seems unstable, does not use PDO and is for PHP 5.3
• Doctrine 2.0 DBAL: built on PDO, but requires PHP 5.3
• Database eZ Component: built on PDO, requires PHP 5.2.1
• Horde's Db component: lots of drivers including PDO
• Zend Db (from Zend Framework), has support for both PDO and native (eg. mysqli) drivers

If no good alternative is found, it might be possible to programatically backport the Doctrine 2 DBAL by resolving the namespaces and replacing them with underscores.

Does anybody know of a good DBAL that provides what we need?

Compatibility

It is important that this change does not break any existing phpBB code. The interface needs to stay the same, but new additions can be made. For example prepared statements.

One important note is that PDO does not support row seeking. Considering that sql_rowseek() is not used at all, I'd suggest dropping it completely. An alternative would be to provide a PHP implementation, something that is already being done by a few DBAL drivers.

Because this feature has a big potential of breaking stuff it's important to have unit tests that test the DBAL extensively. At least that would be highly desirable.

Implementation

I have started a PDO driver for the phpBB DBAL. It's still extremely WIP, there's lots of mysqli stuff in there. But it's a start and basic functionality is working.

[imkingdavid]

I think converting to PDO would be a great idea. It would be ton of work, but I think it would be a lot more efficient and cleaner. I don't know of any third party options, but if I see one that might work I'll let you know.

[Oleg]

One thing I recently learned the hard way is that not all php db extensions support all features of respective databases. In particular mysql extension does not provide a way to specify ssl certificates for ssl connections to db.

I would be great if such edge cases were considered, and in particular ssl support tested on php dbal and pdo implementations of all supported databases.

[ToonArmy]

One thing I recently learned the hard way is that not all php db extensions support all features of respective databases. In particular mysql extension does not provide a way to specify ssl certificates for ssl connections to db.

I would be great if such edge cases were considered, and in particular ssl support tested on php dbal and pdo implementations of all supported databases.

SSL support for MySQL in PDO doesn't seem to exist :-/

[igorw]

That could indeed be a problem. There has been some recent activity on the ticket, but there's not guarantee it will be ready on time.

[Nelsaidi]

I'm a big fan on PDO - and think this would be really good. But agree it would be alot of work.

Best thing about PDO is prepared statements.

[Oleg]

FWIW I don't see much benefit in switching db layers. They are something you write and never really touch again.

Prepared statements? I don't quite see a use case for them in a php script which has to prepare them on every execution.

(pdo) it would be a lot more efficient and cleaner.

How would inserting another layer of code between php dbal and db access extension improve efficiency?

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.

What happens when there is a bug in pdo? Would you require users to update their php installs?

[naderman]

I would very much like to see these as an alternative to the existing drivers. They could especially be used as a basis for future drivers. If they work just as well they could theoretically replace the existing ones. Since we do want to keep a backward compatible interface that means there is no reason to throw away existing drivers.

I would not want to expose the prepared statements interface of PDO directly since there are quite a few db dependent differences there. So we could implement prepared statements for the other databases too, and even provide a fallback php implementation for those dbals where we have not implemented them.

As for existing dbals, you forgot the Database eZ Component.

Prepared statements? I don't quite see a use case for them in a php script which has to prepare them on every execution.

There are a few advantages: You can more easily identify the same query since you do not have to programmatically strip out inserted values to compare them. More importantly, it prevents all sorts of SQL injection.

(pdo) it would be a lot more efficient and cleaner.

How would inserting another layer of code between php dbal and db access extension improve efficiency?

The way pdo works there isn't really any change in speed at all.

What happens when there is a bug in pdo? Would you require users to update their php installs?

Yes, just like we would require people to update their php installs if there is a bug in their native database extension which is no less likely.

[igorw]

As for existing dbals, you forgot the Database eZ Component.

Thanks, added.

[igorw]

I've added Horde's Db component to the list.