On Mon, 31 Jan 2011 06:30:55 -0500, Gabe da Silveira <g...@websaviour.com>
wrote:
> I have an issue with an fts3 table sqlite 3.6.22. 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));
Again I test hereby using the sqlite3 shell compiled with FTS3. First of
all, to set up a minimal test case:
sqlite> CREATE VIRTUAL TABLE "Test" USING fts3(
...> "id" INTEGER PRIMARY KEY,
...> "Content" TEXT,
...> "count" INTEGER);
sqlite> INSERT INTO "Test" ("id", "Content", "count") VALUES(
...> 101,
...> 'This is a test.',
...> 999);
Note that SQLite ignores the PRIMARY KEY definition, and does not use "id"
as an alias for the rowid:
sqlite> SELECT "rowid" FROM "Test" WHERE "id" = 101;
rowid = 1
Yet somehow, SQLite heeds the INTEGER definitions:
sqlite> SELECT typeof("id"), typeof("Content"), typeof("count") FROM
"Test";
typeof("id") = integer
typeof("Content") = text
typeof("count") = integer
Either the docs are wrong, or there is a bug. Quoting §1.2 of the FTS3
docs:
http://www.sqlite.org/fts3.html#section_1_2
If column names are explicitly provided for the FTS table as
part of the CREATE VIRTUAL TABLE statement, then a datatype
name may be optionally specified for each column. This is pure
syntactic sugar, the supplied typenames are not used by FTS or
the SQLite core for any purpose. The same applies to any
constraints specified along with an FTS column name - they are
parsed but not used or recorded by the system in any way.
(Ignoring PRIMARY KEY is consistent with the part about constraints, by
the way.)
The foregoing test results were obtained SQLite 3.7.2. Perhaps later I
should try also with the latest and/or 3.6.22 per OP.
Impact on Mr. da Silveira’s SELECT query:
sqlite> SELECT * FROM "Test" WHERE "id" = 101;
id = 101
Content = This is a test.
count = 999
sqlite> SELECT * FROM "Test" WHERE "id" = '101';
sqlite>
According to the docs, results from those queries should be reversed. I
suspect that Mr. da Silveira’s different platforms actually have different
SQLite versions, and that one version is consistent with the docs whereas
the other is not. But this thread has already seen the dangers of
speculation. Mr. da Silveira, can you confirm whether *all* your
platforms use 3.6.22 and have a proper FTS3 baked in?
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
