On Mon, 31 Jan 2011 07:27:23 -0500, Simon Slavin <[email protected]>
wrote:
> On 31 Jan 2011, at 11:30am, Gabe da Silveira wrote:
>
>> I have a PHP script
>> that builds an sqlite3 client database for a ps3 application.
>> Depending on where I run the build the script (Gentoo or Mac OS X) I
>> get a database file that has different semantics for a column declared
>> as an integer pk:
>>
>> CREATE VIRTUAL TABLE Directors USING fts3(id INTEGER PRIMARY KEY NOT
>> NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
>> show_last_name_first INTEGER DEFAULT 0, normalised_name TEXT NOT NULL,
>> sort_name TEXT NOT NULL, fanships_count INTEGER, image_url
>> VARCHAR(255));
>>
>> For the database file build on OS X or Windows I need to run:
>>
>> SELECT * FROM Directors WHERE id=1
>>
>> For the database file built on Gentoo I need to run:
>>
>> SELECT * FROM Directors WHERE id='1'
>>
>> Otherwise I get no results.
>
> Very strange. Naturally, the form where you need the quotes is wrong.
> You have explicitly defined 'id' as an INTEGER.
Actually since this is an FTS3 table, the form with the quotes is the
correct one. As Mr. da Silveira alluded later in his post, in FTS3, the
row ID and only the row ID is an INTEGER (“with aliases "docid" [] allowed
as an alias along with the usual "rowid", "oid" and "_oid_"” per
sqlite.org/fts3.html). So since the actual values *should* be TEXT, the
first thing that comes to mind is an interaction with SQLite’s type
affinities—but that doesn’t explain the difference on different platforms.
Probative questions:
* Is PHP’s PDO being used here, or PHP’s Sqlite3 class?
* Are weird PHP settings re magic quotes, etc. different between the
platforms? (That could make the quoted string not work on one
platform—but it doesn’t explain in the other direction.)
* Is the database being populated with parameter binding, or not? If
so,
is id being bound as SQLITE3_INTEGER? (I don’t know off the top of my
head what that will do with an FTS3 table.)
* Are the sqlite3 library versions the same on all platforms? (AFAIK
fts3 has been under heavy development. The whole problem could simply be
a difference in behavior between versions.)
* By the way, I don’t know whether the sqlite3 bundled in PHP even
includes/builds with fts3. I have no idea either if this affects the PDO
driver, or if that uses a system-installed library. You may want to check
to see where your PHP is getting its sqlite3 with fts3 support.
Better approach: Cut PHP from the equation and examine the databases in
the sqlite3 shell. Core function typeof() might be helpful to see if the
tables actually contain different data, as they most probably do not.
(Best approach: Cut PHP from the equation, period.)
Very truly,
Samuel Adam <[email protected]>
763 Montgomery Road
Hillsborough, NJ 08844-1304 • United States
http://certifound.com/
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
