Req #38334 [Com]: Proper data-type support for PDO_SQLITE

Edit report at https://bugs.php.net/bug.php?id=38334&edit=1 ID: 38334 Comment by: mikemol at gmail dot com Reported by: mkhitrov at gmail dot com Summary: Proper data-type support for PDO_SQLITE Status: Open Type: Feature/Change Request Package: SQLite related Operating System: * PHP Version: 5.2.3 Block user comment: N Private report: N New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [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. ------------------------------------------------------------------------ [2006-08-13 14:54:19] iliaa@php.net SQLite by its nature is a typeless database, adding virtual type support to it, is by its nature a feature. ------------------------------------------------------------------------ [2006-08-07 20:54:02] maximkh at yahoo dot com Looks like it's back in "Feature/Change Request" section... Fine, though I don't think supporting proper data-types is an extra "feature". Ok moving on, I figured out a way to get passed both problems, but it basically eliminates all advantages of PDO, and actually makes things a lot worse. As stated above, if you manually escape the data and don't use variable binding, the data is inserted as proper data types. When performing a select on that data, PDO_SQLITE will always convert it into a string, however, PDOStatement::getColumnMeta does return the correct native_type field (null, double, string, or integer). This means that if you want data types to be preserved when selecting, you need to loop over every row using PDOStatement::fetch(), use the getColumnMeta for every column in that row, do the conversion, and store the result in a PHP array (2-dimensional). As you can imagine, that is one slow process. That sounds like an absolutely terrible solution to me, which is why I again ask for at least some sort of a response from one of the developers on this issue (not that I don't enjoy talking to myself). ------------------------------------------------------------------------ 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