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.
- Adding support for a new DBMS is even easier
- Code is cleaner, less duplication and boilerplate
- Easier to add new features like prepared statements
- 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?
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.
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.