On Mon, 31 Jan 2011 15:33:24 -0500, Gabe da Silveira <g...@websaviour.com> wrote:
> On Mon, Jan 31, 2011 at 7:42 PM, Samuel Adam <a...@certifound.com> wrote: > >> I suggested rewriting your schema. Non-TEXT data which will not be >> subjected to a MATCH search is best stored in another table and JOINed >> with the FTS3 table, as Mr. Hess also explained. Also, specifications >> such as VARCHAR(255) are not meaningful to SQLite3; see >> http://www.sqlite.org/datatype3.html . > > Agreed this would be nice, unfortunately we have an gaming console app > that's already been through the formal QA process based on a complex > SQL schema, and for which the developers are not under our budgetary > control. I'd say we have almost no chance of making this happen at > this point given the complexity of the application and database (the > two fts tables cut across a lot of concerns). I can’t help you there other than to say, give your boss the facts and point out that if QA approved that schema, QA failed its job. (Bear in mind that I’m the man who recently sued a state supreme court; characteristically any advice from me is focused on factual correctness solely, exclusively, and at the potential expense of other considerations such as keeping friends in a department which failed its job, or maintaining your state of employment.) You may also point out that you have a much bigger problem with your schema: > Also, I'm not sure how we would avoid the undefined case anyway, > because the primary keys for these things are all integers. Per my previous mail, FTS3 is quietly ignoring your PRIMARY KEY declaration on "id". To use your ID values as an INTEGER PRIMARY KEY, you will need to INSERT, SELECT, and otherwise manipulate your ID values using one of the identifiers “"docid" [] "rowid", "oid" [or] "_oid_"” according to FTS3 docs. Do the following query on your table to confirm this: SELECT "rowid", "id" FROM "Directors"; Per my tests, you will most probably find different values in each column. (If the values are same, this is a coincidence you cannot rely on.) The "rowid" is the actual PRIMARY KEY in an FTS3 table—and since you are not inserting values for it explicitly, it is automatically assigned. > Presumably this pass-through behavior is what allows integers to be > used as join columns even in an fts table. If this edge case > persists, where a bound integer ends up as a string internally, won't > joins fail as well? No. If you are using an integer PK to do the JOIN (which is a good idea), you need to join on "rowid" in your FTS3 table and an INTEGER PRIMARY KEY in your other table(s). With your current schema, your primary key constraint is not enforced; referential integrity is not guaranteed; and if bad data is accidentally inserted, joins (and other actions) may fail *even* on the box where your "id" is coming out as an INTEGER. > I'm working on a standalone test script to narrow down the problem... I suggest you refocus your efforts; unless you want to dig into FTS3 sources with an eye to patching for your needs, the problem is already diagnosed. 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
