On Mon, Jan 31, 2011 at 9:15 AM, Samuel Adam <[email protected]> wrote: > On Mon, 31 Jan 2011 11:46:21 -0500, Samuel Adam <[email protected]> wrote: >> On Mon, 31 Jan 2011 11:05:13 -0500, Simon Slavin <[email protected]> >> wrote: >>> In the definition given in the original post, which I quoted, the table >>> was defined as follows: >>> >>>> 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)); >>> >>> Under this situation, would SQLite not use 'id' as an alias to rowid ? > […] >> You were right; I was wrong. I saw FTS3 and somehow missed the PK >> definition. > > Spoke too soon. SQLite actually ignores the PK definition, and does *not* > use "id" as an alias for the rowid. Evidence coming in another post; I > think I found a bug.
As noted in another post, in FTS3, all user-defined columns are explicitly TEXT, no constraints, and there are baked-in columns for rowid with alias docid. There is also a hidden column with the table's name used to refer to all matches in certain cases. I did spend some time thinking about how to allow specification of types and constraints, but it would add a substantial amount of complexity, with no real gain to the full-text-search functionality. It seemed more reasonable to concentrate on getting the full-text-search stuff right, and use existing SQLite functionality to handle the rest. For instance, you can have a separate table with the stuff FTS3 does not support, joined with the FTS3 table on rowid. You could perhaps even masquerade as a unified table using views and triggers (the fact that it's been a few years and nobody has written a virtual table wrapper for FTS3 which does this automatically makes me think that I probably was right in estimating this as a hard-to-solve problem). The FTS3 code mostly passes values directly down to and up from the underlying tables, so if you bound an integer, it is likely to come back out as an integer when you query. But since FTS3 doesn't make any explicit provision for this, I wouldn't recommend depending on it in your code, unless you're happy with periodically finding the edge cases where your integer unexpectedly turns into text. FTS3 accesses the data as TEXT only, without regard to type. -scott _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
