On Mon, Jan 31, 2011 at 9:27 PM, Samuel Adam <a...@certifound.com> wrote:
> 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. Would that it were it so easy. Unfortunately I work for a startup, and this code was developed by a partner, I'm simply helping debug because it runs on our systems. Assigning blame does nothing for me, and thankfully I don't have to deal with organizational politics anyway so my interest is only in the expediency of a solution. > 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. > 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). Okay so when using fts tables, generally they should only ever join on the official primary key (oid). If other foreign key relations are necessary, there should be a corresponding regular sqlite table to hold the foreign keys (and anything that doesn't require MATCHing). That's my takeaway, correct me if I'm wrong. > 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. Not a big concern because this is generated once from a mysql database that *does* have the constraint and never changed for the lifetime of the database. Point taken however. > 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. Good advice, but the speed at which I can deploy a change to the script is orders of magnitude greater than the speed at which an application patch can be developed, tested, and distributed to end users, so I may have to do both. Anyway, thanks for all your help. -gabe _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
