SQLite3, PDO/SQLite and driver specific methods

  110578
June 16, 2020 10:32 phpmailinglists@gmail.com (Peter Bowyer)
Hi list,

4 months ago there was a discussion about the future of SQLite3 and
PDO/SQLite extensions (https://externals.io/message/108256), highlighting
the effort required to maintain two codebases.

Recently I encountered https://bugs.php.net/bug.php?id=64810 and thence
https://github.com/php/php-src/pull/3368. In the pull request Christoph M.
Becker wrote on 8 May:

This PR is still unresolved. The problem is that adding driver specific
> methods to PDO via the currently existing mechanism (basicall `__call` > like), has faced some strong oposition, so this should be discussed on > internals. It probably would make sense to start some general discussion > about this issue, and to hopefully come to a solution, so driver specific > functionality could be added to PDO in the future. > [Link: https://github.com/php/php-src/pull/3368#issuecomment-625731098]
In light of this comment, I'm emailing the list to start a general discussion about this issue. As alluded to earlier, there is a maintenance burden, and for users the lack of parity between the two extensions can be an issue. PDO is well established as providing a standardised access layer across databases. SQLite requires extensions to be loaded to gain feature parity with other databases (e.g. to load geospatial functions) and since it is not a database server these have to be loaded at runtime. PDO has three sqlite-prefixed functions (https://www.php.net/manual/en/ref.pdo-sqlite.php) but not one to load extensions. Is it a problem to add one? If it is, how can we change and improve the current situation? Peter
  110579
June 16, 2020 10:41 Danack@basereality.com (Dan Ackroyd)
On Tue, 16 Jun 2020 at 11:32, Peter Bowyer <phpmailinglists@gmail.com> wrote:
> > If it is, how can we change and improve the current situation? >
Hi Peter, I think what I wrote earlier is still a good approach: https://externals.io/message/100773#100813
> Taking it to a better solution is that the method sqliteCreateFunction > shouldn't exist on the PDO class, but instead on a PDOSqlite that > extends PDO. > > class PDOSqlite extends PDO { > public function createFunction(...) {...} > } > > class PDO { > public static function connect(string $dsn [, string $username [, > string $password [, array $options ]]]) { > if (connecting to SQLite DB) { > return new PDOSqlite(...); > } >. // return new PDO(...); > } > }
Note, I haven't done the work to actually make sure that this plan is actually feasible, so it's not guaranteed to be the correct approach. But it still sounds sensible and would scale out to other custom methods for other connection types, and avoid any magic. If you (or anyone) have the time to work on this, that would be fantastic. cheers Dan Ack
  110580
June 16, 2020 10:50 nikita.ppv@gmail.com (Nikita Popov)
On Tue, Jun 16, 2020 at 12:42 PM Dan Ackroyd <Danack@basereality.com> wrote:

> On Tue, 16 Jun 2020 at 11:32, Peter Bowyer <phpmailinglists@gmail.com> > wrote: > > > > If it is, how can we change and improve the current situation? > > > > Hi Peter, > > I think what I wrote earlier is still a good approach: > https://externals.io/message/100773#100813 > > > Taking it to a better solution is that the method sqliteCreateFunction > > shouldn't exist on the PDO class, but instead on a PDOSqlite that > > extends PDO. > > > > class PDOSqlite extends PDO { > > public function createFunction(...) {...} > > } > > > > class PDO { > > public static function connect(string $dsn [, string $username [, > > string $password [, array $options ]]]) { > > if (connecting to SQLite DB) { > > return new PDOSqlite(...); > > } > >. // return new PDO(...); > > } > > } > > Note, I haven't done the work to actually make sure that this plan is > actually feasible, so it's not guaranteed to be the correct approach. > But it still sounds sensible and would scale out to other custom > methods for other connection types, and avoid any magic. > > If you (or anyone) have the time to work on this, that would be fantastic. >
+1 on this approach. No magic, just normal inheritance. For backwards-compatibility reasons, we'd presumably still have to retain the ability to call those methods on the plain PDO object though :( But at least there would be a way to use it in a sensible way in new code. In particular this also allows you to write things like: if ($pdo instanceof PDOSqlite) { $pdo->loadExtension(...); } Regards, Nikita
  110689
June 20, 2020 14:21 phpmailinglists@gmail.com (Peter Bowyer)
On Tue, 16 Jun 2020 at 11:41, Dan Ackroyd <Danack@basereality.com> wrote:

> Note, I haven't done the work to actually make sure that this plan is > actually feasible, so it's not guaranteed to be the correct approach. > But it still sounds sensible and would scale out to other custom > methods for other connection types, and avoid any magic. > > If you (or anyone) have the time to work on this, that would be fantastic. >
Well, I gave it a go this week! I last wrote C 2 decades ago, and those were simple programs. Digging into the PHP source code is a fun challenge. I worked out how to create a new class [1] and to get PDOSQLite to extend PDO [2]. Extending PDO took some thought; Reflection extension does it but all classes are defined in one file. SPL, DOM etc also do it with their classes. Eventually I saw how SimpleXML did it and was able to apply that. I've got stuck with adding `PDO::connect('sqlite:...')` [3]. Can someone take a look and give me advice? Peter 1. https://github.com/pbowyer/php-src/commit/bcbdce9134e695d4926d11f2fa1a855b3237db6e 2. https://github.com/pbowyer/php-src/commit/90a9c20fa1f8a626df8636f6c9e4f974fd789daf 3. https://github.com/pbowyer/php-src/commit/51654eeae9407dc8991d62155719fbfea211f735
  110693
June 21, 2020 15:12 Danack@basereality.com (Dan Ackroyd)
On Sat, 20 Jun 2020 at 15:22, Peter Bowyer <phpmailinglists@gmail.com> wrote:
> > Digging into the PHP source code is a fun challenge.
It really isn't. Collecting and publishing information on how to work on PHP core, is one thing that takes a lot of time, that we should make easier for people to contribute to.
> I've got stuck with adding `PDO::connect('sqlite:...')` [3]. Can someone take a look and give me advice?
Thomas Weinert has a repo that implements features in core. Each branch shows how to do a separate thing. Adding a class is on this branch: https://github.com/ThomasWeinert/php-extension-sample/tree/class_method Though you would need the ZEND_ACC_STATIC flag for the method entry also. Also, probably useful: http://www.phpinternalsbook.com/ Might be easier for people to help you if you open a pull request, as that has more visibility, easier places to comment. cheers Dan Ack
  110581
June 16, 2020 11:33 php@bohwaz.net (BohwaZ)
On Tue, 16 Jun 2020 11:32:01 +0100 / Peter Bowyer
<phpmailinglists@gmail.com> said :

> Hi list, > > 4 months ago there was a discussion about the future of SQLite3 and > PDO/SQLite extensions (https://externals.io/message/108256), > highlighting the effort required to maintain two codebases.
Yes and I haven't had time to write a RFC or write any code on that yet, but hopefully I will have some time next year...
> PDO is well established as providing a standardised access layer > across databases. SQLite requires extensions to be loaded to gain > feature parity with other databases (e.g. to load geospatial > functions) and since it is not a database server these have to be > loaded at runtime. PDO has three sqlite-prefixed functions > (https://www.php.net/manual/en/ref.pdo-sqlite.php) but not one to > load extensions. > > Is it a problem to add one?
Yes adding driver-specific methods to PDO has been refused by PHP developers when I did a RFC on adding a couple methods for SQLite, even though there's already a bunch of other methods for other drivers like Postgre. What I was thinking was not exactly what Dan was proposing as I remember the driver-specific code to be very much linked to the PDO class and changing that would be a lot of work, so I was more thinking about adding a "getDriver()" method on PDO that would return a "PDO_SQLite" instance (or other drivers) and this class would have driver-specific methods. I dont exactly remember why the inheritance path suggested by Dan seemed way too complex as I haven't had time to work on this for a long time, but if it is actually possible it also seems like a good way to go :) I personally already use a wrapper-class that implements the same PDO methods but using the SQLite3 class underneath as it's the only one to provide access to advanced features like setting the authorizer callback, backup API and others.