> Again, you've given a relatively broad description of what you're
> trying to do. I could make up a bunch of stuff and answer my own
> question, but you'd probably rather than I considered the problem
> _you_ are having.
Ok, I'll try to be as specific as possible. The main table I have is (the
real version has much more fields, but it isn't important for our example):
CREATE TABLE Songs (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Artist TEXT COLLATE IUNICODE,
Album TEXT COLLATE IUNICODE,
SongTitle TEXT COLLATE IUNICODE,
Path TEXT COLLATE IUNICODE,
Year INTEGER,
Bitrate INTEGER)
This table can have even >100k records, even close to million and is mostly
accessed by SELECTing all fields of some records, i.e.:
SELECT * FROM Songs WHERE {something}
In order to use FTS3, I could take all the text fields from Songs table and
move them to a FTS3 table:
CREATE TABLE SongsBase (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Year INTEGER,
Bitrate INTEGER)
CREATE VIRTUAL TABLE SongsText USING FTS3(TOKENIZE mm,
Artist,
Album,
SongTitle,
Path)
This way I would lose my custom collation (IUNICODE), which would be quite a
problem, particularly for Path field (and if you're asking, yes, I'd like to
include Path in the full-text index). Another problem is that joined SELECT
on SongsBase and SongsText is slower than SELECT on the original Songs
table.
So, the only solution using FTS3 seems to be to use the original Songs table
and add SongsText table, automatically updated by triggers like:
CREATE TRIGGER update_songs UPDATE OF Artist,Album,SongTitle,Path ON Songs
BEGIN
UPDATE SongsText SET Artist=new.Artist, Album=new.Album,
SongTitle=new.Title, Path=new.Path WHERE rowid=new.id;
END;
This solution probably isn't bad, but according to my knowledge of FTS3, it
unnecessarily occupies some DB space (all text fields are actually stored
twice, once in Songs and once in SongsText).
Any ideas or recommedations?
Thanks,
Jiri
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
