ID: 38334 User updated by: maximkh at yahoo dot com Reported By: maximkh at yahoo dot com Status: Open Bug Type: Feature/Change Request Operating System: Linux PHP Version: 5.1.4 New Comment:
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. Previous Comments: ------------------------------------------------------------------------ [2006-08-13 14:54:19] [EMAIL PROTECTED] SQLite by its nature is a typeless database, adding virtual type support to it, is by its nature a feature. ------------------------------------------------------------------------ [2006-08-11 13:41:58] maximkh at yahoo dot com . ------------------------------------------------------------------------ [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). ------------------------------------------------------------------------ [2006-08-05 16:35:25] maximkh at yahoo dot com Switching this bug from "Feature/Change Request" to "SQLite related" issues. This seems to be a really big oversight on the developer's part since it eliminates all data-type support from SQLite. ------------------------------------------------------------------------ [2006-08-05 16:29:12] maximkh at yahoo dot com A small correction to the code above... The request has more to do with the bindParam and bindValue methods. If you use PDO::query like I do above, numbers 123 and 123.45 actually DO get stored in the native data type (INT and REAL in this case), but when retrieved again, they are converted into strings. I've confirmed this using a HEX editor by viewing the database file. So that's two separate issues there. First, is that if you use bindParam and bindValue to insert data, that data will always be converted into text prior to being sent to the database. Second, if you DON'T use bindParam or bindValue and instead use the traditional method of manually escaping data and concatenating it directly with the SQL query, in this case the values will be stored using native data types, but will not be retrieved as such when a select is performed. ------------------------------------------------------------------------ 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 http://bugs.php.net/38334 -- Edit this bug report at http://bugs.php.net/?id=38334&edit=1
