On Fri, Jul 29, 2011 at 10:33 PM, Ray Van Dolson <ra...@bludgeon.org> wrote: > sqlite newbie here. I have a sqlite3 database that was built using the > FTS2 module. As such I get errors about the non-existent fts2 module > when attempting to query the data. > > Is there a way to convert/migrate the data to FTS3 or somehow create an > "alias" FTS2() call that just wraps around FTS3?
The underlying data is stored in %_contents, where % is replaced by the database name. So your .schema output should look something like: CREATE VIRTUAL TABLE OldTable USING fts3(col1, col2); CREATE TABLE 'OldTable_content'(docid INTEGER PRIMARY KEY, 'c0col1', 'c1col2'); CREATE TABLE 'OldTable_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx)); CREATE TABLE 'OldTable_segments'(blockid INTEGER PRIMARY KEY, block BLOB); except "fts3" should look like "fts2", and the OldTable_content won't contain docid. So, you could do something like: CREATE VIRTUAL TABLE NewTable USING fts3(col1, col2); -- almost identical to the fts2 version INSERT INTO NewTable (docid, col1, col2) SELECT rowid, c0col1, c1col2 FROM OldTable_content; you may have to experiment to get this just right, I don't happen to have an fts2-enabled sqlite3 around to test it. Just look a the .schema output, it's all there. Getting rid of the old table is a bit dicier. Without the fts2 module, I don't think you can DROP the OldTable virtual table. You can DROP the backing tables easily enough, though. To drop the virtual table, you'll have to do something like: PRAGMA writable_schema = ON; DELETE FROM sqlite_master WHERE sql='CREATE VIRTUAL TABLE OldTable USING fts2(col1, col2);'; BE VERY CAREFUL. WRITABLE_SCHEMA GIVES YOU THE TOOLS TO DESTROY ALL YOUR DATA. KEEP BACKUPS. EXPERIMENT ON UNIMPORTANT DATA. If you need to do this more frequently, you'd probably be better served to build a sqlite3 with fts2 compiled in, or build fts2 as a loadable module, and migrate the data that way. In that case it would be similar to the INSERT...SELECT statement. -scott _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
