[RFC] PDO / third party DBAL

Note: We are moving the topics of this forum and it will be deleted at some point

Publish your own request for comments/change or patches for the next version of phpBB. Discuss the contributions and proposals of others. Upcoming releases are 3.2/Rhea and 3.3.
User avatar
igorw
Registered User
Posts: 500
Joined: Thu Jan 04, 2007 11:47 pm

[RFC] PDO / third party DBAL

Post by igorw » Fri Apr 30, 2010 6:29 pm

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:
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.
Last edited by igorw on Sat May 01, 2010 4:45 pm, edited 4 times in total.

User avatar
imkingdavid
Registered User
Posts: 1050
Joined: Thu Jul 30, 2009 12:06 pm

Re: [RFC] PDO / third party DBAL

Post by imkingdavid » Fri Apr 30, 2010 6:41 pm

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.
I do custom MODs. PM for a quote!
View My: MODs | Portfolio
Please do NOT contact for support via PM or email.
Remember, the enemy's gate is down.

Oleg
Posts: 1150
Joined: Tue Feb 23, 2010 2:38 am
Contact:

Re: [RFC] PDO / third party DBAL

Post by Oleg » Fri Apr 30, 2010 7:00 pm

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.

User avatar
ToonArmy
Registered User
Posts: 335
Joined: Fri Mar 26, 2004 7:31 pm
Location: Bristol, UK
Contact:

Re: [RFC] PDO / third party DBAL

Post by ToonArmy » Fri Apr 30, 2010 7:26 pm

nn- wrote: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 :-/
Chris SmithBlogXMOOhlohArea51WikiNo support via PM/IM
Image

User avatar
igorw
Registered User
Posts: 500
Joined: Thu Jan 04, 2007 11:47 pm

Re: [RFC] PDO / third party DBAL

Post by igorw » Fri Apr 30, 2010 7:38 pm

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
Registered User
Posts: 122
Joined: Tue Nov 11, 2008 5:44 pm

Re: [RFC] PDO / third party DBAL

Post by Nelsaidi » Fri Apr 30, 2010 9:35 pm

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
Posts: 1150
Joined: Tue Feb 23, 2010 2:38 am
Contact:

Re: [RFC] PDO / third party DBAL

Post by Oleg » Fri Apr 30, 2010 10:51 pm

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.
imkingdavid wrote:(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?

User avatar
naderman
Product Manager
Product Manager
Posts: 1727
Joined: Sun Jan 11, 2004 2:11 am
Location: Karlsruhe, Germany
Contact:

Re: [RFC] PDO / third party DBAL

Post by naderman » Sat May 01, 2010 12:36 am

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.
nn- wrote: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.
nn- wrote:
imkingdavid wrote:(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.
nn- wrote: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.

User avatar
igorw
Registered User
Posts: 500
Joined: Thu Jan 04, 2007 11:47 pm

Re: [RFC] PDO / third party DBAL

Post by igorw » Sat May 01, 2010 12:45 am

naderman wrote:As for existing dbals, you forgot the Database eZ Component.
Thanks, added.

User avatar
igorw
Registered User
Posts: 500
Joined: Thu Jan 04, 2007 11:47 pm

Re: [RFC] PDO / third party DBAL

Post by igorw » Sat May 01, 2010 2:56 am

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

Post Reply