Req #38334 [Opn->Csd]: Proper data-type support for PDO_SQLITE

Edit report at https://bugs.php.net/bug.php?id=38334&edit=1 ID: 38334 Updated by: nikic@php.net Reported by: mkhitrov at gmail dot com Summary: Proper data-type support for PDO_SQLITE -Status: Open +Status: Closed Type: Feature/Change Request Package: PDO SQLite Operating System: * PHP Version: 5.2.3 -Assigned To: +Assigned To: nikic Block user comment: N Private report: N New Comment: Implemented in https://github.com/php/php-src/commit/438b025a28cda2935613af412fc13702883dd3a2. Previous Comments: ------------------------------------------------------------------------ [2016-08-09 20:01:00] cmb@php.net Related To: Bug #72798 ------------------------------------------------------------------------ [2011-10-31 18:58:06] mikemol at gmail dot com Similar surprises for me. Thoughts: (1) Mapping INTEGER to int isn't necessarily ideal, because there's no certainty (AFAIK) that sqlite3's integer value will fit within the range offered by int. PHP already has a type-promotion paradigm for handling this (by converting to float, which supports a larger range of precise integer values), but I don't know what it looks like in PHP's source code. (2) If PHP is converting sqlite3's numeric returns to strings, that means that users need to convert strings back to numeric types. That sounds error-prone and potentially lossy for both stages of that conversion, potentially resulting in data loss because of the unnecessary translation[1]. There might even be a vulnerability there, in the event large numbers are being stored, and those numbers are damaged during the translations. (3) If the semantics of the sqlite PDO driver were to change to reflect sqlite3's semantics instead of sqlite2's, that would likely break a lot of existing code. It stands to reason you wouldn't want the new semantics to be the default, but you'd definitely want the old semantics to be deprecated. Perhaps in release $next, there's an attribute to switch which versions' semantics are used, defaulting to current (sqlite2's) behavior, but allowing sqlite3's to be selected. In release $next+1, default to sqlite3's, and deprecate sqlite2's. In $next+2, remove sqlite3's, deprecate the attribute. In $next+4's, remove the attribute. As mkhitrov noted, at first glance, it looks faster to fix PHP's code-in-C[2] than fix one's own code-in-PHP. Realities of the situation, though, are that I'd likely end up waiting through a release or two before sqlite3 semantics are applied. [1] And certainly incorrect behavior, if the user is led to expect sqlite3 semantics; while I was researching to learn how sqlite behaved, I didn't even realize the sqlite2 semantics were in effect anywhere, leading me to write a fair amount of code assuming sqlite3 semantics. (I only discovered that the sqlite3 semantics weren't as expected while I was rechecking and building up my unit test suite for the app I'm on) [2] I don't hack on PHP source, but I frequently have to refer to it to clarify behaviors not clear in the documentation. I can mostly follow what goes on in some places, but I haven't tried modifying or adding anything. I don't trust myself to write the patch, certainly not yet, and probably not on billable time. ------------------------------------------------------------------------ [2006-08-14 14:08:21] tony2001@php.net Since this is the intended behavior, there is no bug and your report should be classified as "feature request". >If not, I have to go back and spend a ton of time writing > wrappers for PDO that will emulate what should have been > proper behavior. You also can spend some time and cook a patch, that adds the functionality you want, we would gladly review it. ------------------------------------------------------------------------ [2006-08-14 13:55:56] maximkh at yahoo dot com Ok listen, until someone actually starts reading Feature/Change Requests, this is a PDO related issue. It's already been nearly 2 weeks, and I have yet to hear anything constructive from anyone. All I want is for someone to spend 5 minutes and tell me if there is even a chance it will be fixed in the next release. If so, great, I'll shut up and won't bother you. If not, I have to go back and spend a ton of time writing wrappers for PDO that will emulate what should have been proper behavior. ------------------------------------------------------------------------ [2006-08-13 15:58:43] maximkh at yahoo dot com Not exactly, it WAS typeless in version 2. As of version 3, it has well defined types as explained in the documentation: http://www.sqlite.org/datatype3.html If I was to store the number 18446744073709551615, SQLite v3 would store it as an 8-byte integer, but since PHP forces a conversion to string, it would take up 20 bytes. I'm sure you can see the problem, especially when dealing with certain scientific applications which is what I'm trying to write and use SQLite with. Considering that there is a perfect match between SQLite and PHP data-types, there is really no reason not to implement them... NULL -> null INTEGER -> integer REAL -> float TEXT -> string BLOB -> binary string After taking a look through the actual PDO_SQLITE code, this kind of change would take half an hour to implement, so I see no real argument against it. ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=38334 -- Edit this bug report at https://bugs.php.net/bug.php?id=38334&edit=1