# [RFC] [Discussion] Implement SQLite "openBlob" feature in PDO - Thread: https://externals.io/message/100773 - Original archive: https://news-web.php.net/php.internals/100773 - Started by BohwaZ/PHP on 2017-09-26T02:03:37+00:00 - Messages: 20 - RFC: PDO driver specific sub-classes (Implemented (PHP 8.4)) — https://externals.io/rfc/pdo_driver_specific_subclasses.md - RFC: Implement SQLite "openBlob" in PDO (Declined) — https://externals.io/rfc/implement_sqlite_openblob_in_pdo.md - Reply by email to internals@lists.php.net - More for agents: search with https://externals.io/search.md?q={query} · RFCs at https://externals.io/rfc.md · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt --- ## #100773 — BohwaZ/PHP — 2017-09-26T02:03:37+00:00 URL: https://externals.io/message/100773#100773 Kia ora, following my patch and discussions on this list, here is the RFC requested by some people here to implement "openBlob" in the pdo_sqlite driver, to match the "openBlob" method from the SQLite3 extension. https://wiki.php.net/rfc/implement_sqlite_openblob_in_pdo Discussion should happen in the next two weeks before going to vote. The actual patch is here: https://github.com/php/php-src/pull/2698 Suggestions and discussions welcome. Cheers. --- ## #100777 — Adam Baratz — 2017-09-27T08:10:44+00:00 URL: https://externals.io/message/100773#100777 · In reply to: #100773 > > following my patch and discussions on this list, here is the RFC requested > by some people here to implement "openBlob" in the pdo_sqlite driver, to > match the "openBlob" method from the SQLite3 extension. Thanks for taking the time to do the writeup. No objections from me. Adam --- ## #100778 — Dan Ackroyd — 2017-09-27T08:47:51+00:00 URL: https://externals.io/message/100773#100778 · In reply to: #100773 > Kia ora, > > https://wiki.php.net/rfc/implement_sqlite_openblob_in_pdo Couple of questions: > $stream = $pdo->sqliteOpenBlob('test', 'data', 1); I tried reading the code but failed; what happens when this is called on a PDO connection that isn't to an SQLite database? Also, there should probably be tests around that behaviour. > Proposed PHP Version(s) > Next PHP 7.x Please can every RFC be explicit about the version it is aimed at? Although it might be 'obvious' to you, in the future when someone is looking back at declined RFCs trying to match up release dates with RFCs that have 'next' as the version number is confusing. cheers Dan Ack --- ## #100779 — Lester Caine — 2017-09-27T09:00:21+00:00 URL: https://externals.io/message/100773#100779 · In reply to: #100778 > > […] > > Couple of questions: > > > […] > I tried reading the code but failed; what happens when this is called > on a PDO connection that isn't to an SQLite database? Also, there > should probably be tests around that behaviour. The bigger question is - Should database specific extensions to PDO be allowed at all? The WHOLE base of PDO was that it would allow easy data management between DIFFERENT databases. This should be implemented in a way that mirrors blobs generically otherwise the generic database driver should be used since a switch to another PDO driver will fail. This should apply to any targeted extension to PDO, so anything that breaks the generic base data needs tidying up. -- Lester Caine - G8HFL ----------------------------- Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk --- ## #100780 — Marco Pivetta — 2017-09-27T09:04:09+00:00 URL: https://externals.io/message/100773#100780 · In reply to: #100779 > > > […] > > The bigger question is - Should database specific extensions to PDO be > allowed at all? The WHOLE base of PDO was that it would allow easy data > management between DIFFERENT databases. This should be implemented in a > way that mirrors blobs generically otherwise the generic database driver > should be used since a switch to another PDO driver will fail. This > should apply to any targeted extension to PDO, so anything that breaks > the generic base data needs tidying up. > First time I agree with Lester here, so please take note :-P Unless the type of the connection is PDOSQLiteConnection, this specific patch adds methods that are not interfaced, and need to be checked for existence every time. This is error-prone and just an annoyance that will likely need abstraction once it reaches "real world" (layers that isolate apps from PDO's inherent radioactivity). Marco Pivetta http://twitter.com/Ocramius http://ocramius.github.com/ --- ## #100781 — Matteo Beccati — 2017-09-27T09:17:35+00:00 URL: https://externals.io/message/100773#100781 · In reply to: #100779 Hi Lester, > The bigger question is - Should database specific extensions to PDO be > allowed at all? The WHOLE base of PDO was that it would allow easy data > management between DIFFERENT databases. This should be implemented in a > way that mirrors blobs generically otherwise the generic database driver > should be used since a switch to another PDO driver will fail. This > should apply to any targeted extension to PDO, so anything that breaks > the generic base data needs tidying up. That's a wrong assumption. PDO was born to allow quickly writing database drivers, not as an abstraction layer that allows you to seamlessly move from one another. I thought the same but I was corrected by someone that was involved in the process. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/ --- ## #100782 — Matteo Beccati — 2017-09-27T09:28:03+00:00 URL: https://externals.io/message/100773#100782 · In reply to: #100780 Hey Marco, > First time I agree with Lester here, so please take note :-P Anything you say can and will be used against you ;-) > Unless the type of the connection is PDOSQLiteConnection, this specific > patch adds methods that are not interfaced, and need to be checked for > existence every time. This is error-prone and just an annoyance that will > likely need abstraction once it reaches "real world" (layers that isolate > apps from PDO's inherent radioactivity). This is the (possibly wrong) pattern that PDO has been using when providing something that's relevant only for a specific driver. Such methods should be used with caution. What's your suggestion? Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/ --- ## #100783 — Lester Caine — 2017-09-27T09:34:53+00:00 URL: https://externals.io/message/100773#100783 · In reply to: #100781 > > > […] > That's a wrong assumption. PDO was born to allow quickly writing > database drivers, not as an abstraction layer that allows you to > seamlessly move from one another. I thought the same but I was corrected > by someone that was involved in the process. The whole base that PDO was allowed to be bundled was that it provided a clean DATA abstraction that could be relied on. The fact that it sidesteps the problems of SQL abstraction was pushed to one side as something that could be handled later. If each driver is now producing DIFFERENT sets of data then the whole generic base is broken. Why not simply move back to the generic drivers which are a LOT more advanced these days and rely on higher level abstraction layers where database transparency is an advantage. openBlob is a specific feature of SQLite so the decision to use it already rules out any other database. IN PDO access to it via the generic blob functions is the proper way forward so that a call for a blob gives a blob what ever the underlying datbase. -- Lester Caine - G8HFL ----------------------------- Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk --- ## #100784 — Christoph M. Becker — 2017-09-27T09:41:50+00:00 URL: https://externals.io/message/100773#100784 · In reply to: #100773 > following my patch and discussions on this list, here is the RFC > requested by some people here to implement "openBlob" in the pdo_sqlite > driver, to match the "openBlob" method from the SQLite3 extension. > > https://wiki.php.net/rfc/implement_sqlite_openblob_in_pdo > > Discussion should happen in the next two weeks before going to vote. > > The actual patch is here: https://github.com/php/php-src/pull/2698 > > Suggestions and discussions welcome. PDO already has support for large objects (LOBs)[1]. I don't know if and how these are supported by the pdo_sqlite driver, but wouldn't it make sense to use the existing API instead of introducing a new method? [1] -- Christoph M. Becker --- ## #100785 — Matteo Beccati — 2017-09-27T09:47:21+00:00 URL: https://externals.io/message/100773#100785 · In reply to: #100783 > openBlob is a specific feature of SQLite so the decision to use it > already rules out any other database. IN PDO access to it via the > generic blob functions is the proper way forward so that a call for a > blob gives a blob what ever the underlying datbase. Seeing the RFC, I gave for granted that SQLite couldn't use the standard LOB api provided by PDO, but maybe that isn't the case? I'll leave the OP to reply. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/ --- ## #100804 — BohwaZ — 2017-10-01T05:46:38+00:00 URL: https://externals.io/message/100773#100804 · In reply to: #100778 On Wed, 27 Sep 2017 09:47:51 +0100 / Dan Ackroyd said : > > > […] > > > Couple of questions: > > > […] > > I tried reading the code but failed; what happens when this is called > on a PDO connection that isn't to an SQLite database? Also, there > should probably be tests around that behaviour. As with other driver-specific methods of PDO, the method won't be defined and an exception (error) will be raised. I'll add that detail ASAP thanks. This is an existing issue when you want to extend PDO to implement lazy connections (eg. you can't call PDOExtended::sqliteCreateFunction until the parent constructor of PDO has been called), but is out of the scope of this RFC. --- ## #100805 — BohwaZ — 2017-10-01T05:55:40+00:00 URL: https://externals.io/message/100773#100805 · In reply to: #100784 On Wed, 27 Sep 2017 11:41:50 +0200 / "Christoph M. Becker" said : > > > > […] > > PDO already has support for large objects (LOBs)[1]. I don't know if > and how these are supported by the pdo_sqlite driver, but wouldn't it > make sense to use the existing API instead of introducing a new > method? > > [1] > Very interesting indeed, didn't know about that feature, I was expecting the creation of a new method was the only way, as this was the way PGSQL was doing it. There's even a bug report about it: https://bugs.php.net/bug.php?id=57691 I will look into that next week and see if it can fit and replace my RFC then. Thank you. --- ## #100806 — BohwaZ — 2017-10-01T07:41:31+00:00 URL: https://externals.io/message/100773#100806 · In reply to: #100785 On Wed, 27 Sep 2017 11:47:21 +0200 / Matteo Beccati said : > > > […] > > Seeing the RFC, I gave for granted that SQLite couldn't use the > standard LOB api provided by PDO, but maybe that isn't the case? I'll > leave the OP to reply. I wasn't aware of that API, I saw what the postgreSQL driver was doing and assumed it was the only way. See my response to @cmb but it seems like a nice option, I'll assess next week whether it can fit with the SQLite C API or not. Hopefully it will! --- ## #100807 — BohwaZ/PHP — 2017-10-02T00:48:27+00:00 URL: https://externals.io/message/100773#100807 · In reply to: #100805 > > […] > > Very interesting indeed, didn't know about that feature, I was > expecting the creation of a new method was the only way, as this was > the way PGSQL was doing it. > > There's even a bug report about it: > https://bugs.php.net/bug.php?id=57691 > > I will look into that next week and see if it can fit and replace my > RFC > then. OK, I took some time to look into that feature and the fact is that it doesn't work at all currently with SQLite, it is not returning a resource handle but a string, and it is consuming a large amount of memory as it is just dumping the LOB in memory. The code seems to be there to handle it though so I don't know what's going on, if the person who implemented that could come forward and tell me more about the implementation. But I tried it and it doesn't cover one of the use of openBlob that I have which is to open, read, and write at the same time. The current way it's done in PDO is that you can either fetch a LOB from a result of a query and read from it, or bind a file resource handler to a statement for writing, but you cannot open a LOB, read from it and write from it without performing a query. So for me the use case is quite different here, and openBlob allows stuff that PDO::PARAM_LOB with bindColumn and bindParam cannot allow currently. In conclusion openBlob is still useful as it allows accessing a BLOB outside of a statement and allows to read and write at the same time without having to load the blob in memory. --- ## #100808 — Lester Caine — 2017-10-02T08:47:30+00:00 URL: https://externals.io/message/100773#100808 · In reply to: #100807 > So for me the use case is quite different here, and openBlob allows > stuff that PDO::PARAM_LOB with bindColumn and bindParam cannot allow > currently. In conclusion openBlob is still useful as it allows accessing > a BLOB outside of a statement and allows to read and write at the same > time without having to load the blob in memory. This is where the limitations of some of the other database engines come into play. In many cases in shared hosting, the database is provided on another machine, so one has to transfer the data results between machines and do not have direct access to the data. PDO can't emulate this function so the question is still SHOULD something that can't be made generically functional be allowed in PDO. Personally I would prefer that for this sort of action the generic driver was used used rather than PDO and I have to do that for other functions in other databases currently anyway. So one does not have to overload PDO with more checks as to if your code will work on the different drivers. -- Lester Caine - G8HFL ----------------------------- Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk --- ## #100810 — BohwaZ/PHP — 2017-10-02T21:48:57+00:00 URL: https://externals.io/message/100773#100810 · In reply to: #100808 > > > […] > > This is where the limitations of some of the other database engines > come > into play. In many cases in shared hosting, the database is provided on > another machine, so one has to transfer the data results between > machines and do not have direct access to the data. PDO can't emulate > this function so the question is still SHOULD something that can't be > made generically functional be allowed in PDO. Personally I would > prefer > that for this sort of action the generic driver was used used rather > than PDO and I have to do that for other functions in other databases > currently anyway. So one does not have to overload PDO with more checks > as to if your code will work on the different drivers. I don't agree with that. You might want to be able to use a generic abstraction layer such as PDO to offer support for multiple database engines without having to create your own abstraction layer with every specific database extension (that would be huge work) but still be able to access driver-specific features if available. This is why I am pushing for PDO to be feature-full, so that you have the choice to use PDO and not have to implement your own abstraction layer just because you need one specific feature in one single case :) If you follow your logic, then PDO::sqliteCreateFunction shouldn't exist, and this would make the PDO sqlite driver pretty much useless as SQLite is missing a number of important functions. --- ## #100813 — Dan Ackroyd — 2017-10-03T13:59:01+00:00 URL: https://externals.io/message/100773#100813 · In reply to: #100810 > > If you follow your logic, then PDO::sqliteCreateFunction shouldn't exist, > and this would make the PDO sqlite driver pretty much useless as SQLite is > missing a number of important functions. That's taking it to the illogical conclusion. 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(...); } } } This might be a mistake in how it was implemented originally. Looking back it seems that it was implemented before we had the RFC process....and is exactly the type of 'sub-optimal' implementation the RFC process is meant to prevent. > This is why I am pushing for PDO to be feature-full, so that you have the > choice to use PDO and not have to implement your own abstraction layer just > because you need one specific feature in one single case :) > That's a great aim! But rather thank just hack in new features building on sub-optimal ways of doing things, I think it would be better to create a plan to clean up the way that connection specific methods are available, with something along the lines of that which I mentioned above. cheers Dan Ack --- ## #100814 — Adam Baratz — 2017-10-03T14:22:17+00:00 URL: https://externals.io/message/100773#100814 · In reply to: #100807 > > PDO already has support for large objects (LOBs)[1]. I don't know if > > […] > > > OK, I took some time to look into that feature and the fact is that it > doesn't work at all currently with SQLite, it is not returning a resource > handle but a string, and it is consuming a large amount of memory as it is > just dumping the LOB in memory. The code seems to be there to handle it > though so I don't know what's going on, if the person who implemented that > could come forward and tell me more about the implementation. I believe that's how PDO::PARAM_LOB is intended to work (based on my reading of the docs and implementations for other drivers). It seems like more of a convenience than anything, though maybe someone had more ideas for how it should work across drivers and never got to follow through on it. The RFC is agreeable to me because it follows the existing ext/sqlite3 API and uses the existing pattern in pdo_sqlite for integrating driver-specific APIs. I'd love it if PDO had better BLOB/LOB types and if we had a better pattern for driver-specific APIs, but I'm comfortable lumping those goals under "future scope." Getting parity with ext/sqlite3 will make pdo_sqlite more usable, which will help grow its community, and the number of people who are able to contribute to these bigger projects. Deprecating the current set of driver-specific APIs in the future, if we have functional equivalents, isn't an impossible project. Thanks, Adam --- ## #100819 — BohwaZ/PHP — 2017-10-03T22:41:17+00:00 URL: https://externals.io/message/100773#100819 · In reply to: #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(...); > } > } > } > > This might be a mistake in how it was implemented originally. Looking > back it seems that it was implemented before we had the RFC > process....and is exactly the type of 'sub-optimal' implementation the > RFC process is meant to prevent. Yes I do agree that the method overloading of PDO by drivers is not the best to say the least. If you feel like rewriting a large part of PDO feel free :) but I don't have time for that, and it's not the subject of this RFC :) --- ## #100820 — BohwaZ/PHP — 2017-10-03T22:49:23+00:00 URL: https://externals.io/message/100773#100820 · In reply to: #100814 > I believe that's how PDO::PARAM_LOB is intended to work (based on my > reading of the docs and implementations for other drivers). It seems > like > more of a convenience than anything, though maybe someone had more > ideas > for how it should work across drivers and never got to follow through > on it. This is not how I understand the documentation: "PDO::PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API." But this seems to be quite chaotic, reading https://secure.php.net/manual/en/pdostatement.bindcolumn.php "Since information about the columns is not always available to PDO until the statement is executed, portable applications should call this function after PDOStatement::execute(). However, to be able to bind a LOB column as a stream when using the PgSQL driver, applications should call this method before calling PDOStatement::execute(), otherwise the large object OID will be returned as an integer." This is quite confusing. And as stated above, with MySQL and SQLite it returns the LOB content as a string on PHP 7+ but a stream handle on PHP 5.6… To me it seems that the LOB handling of PDO via bindColumn/bindParam is completely broken and inconsistent currently :( If I have more time available after this RFC I'll look into fixing it for PHP 7.3. > I'd love it if PDO had better BLOB/LOB types and if we had a better > pattern for driver-specific APIs, but I'm comfortable lumping those > goals > under "future scope." Getting parity with ext/sqlite3 will make > pdo_sqlite > more usable, which will help grow its community, and the number of > people > who are able to contribute to these bigger projects. Deprecating the > current set of driver-specific APIs in the future, if we have > functional > equivalents, isn't an impossible project. Yeah sounds good to me :)