On Mon, 31 Jan 2011 07:27:23 -0500, Simon Slavin <slav...@bigfraud.org> 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 <a...@certifound.com> 763 Montgomery Road Hillsborough, NJ 08844-1304 • United States http://certifound.com/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users