[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules(revisited)
> 1a.- Delete the previous table. > DROP TABLE IF EXIST ftsm > > This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an > erroro in 1a: "database disk image is malformed". > > Note that in previous attemps I believed that the problem was into try to > delete a ftsm table build with the previous modules, but the error happen > when trying delete a table build with the FTS5 module. > > ... >>> Some clues? >> Not really sure why it might fail there. Can you post the entire >> database schema (results of "SELECT * FROM sqlite_master" or the output >> of the .schema shell tool command)? >> > Dan: > > Here is the schema: That all looks Ok. If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm" command does it report an error as well? Thanks, Dan. > > CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 > remove_diacri > tics 0',columnsize=0); > > And here the entire database schema as produced by the shell: > > sqlite> SELECT * FROM sqlite_master; > table|usr|usr|2|CREATE TABLE usr (ky INTEGER PRIMARY KEY,id CHARACTER > UNIQUE,lev > INTEGER,pwd TEXT) > index|sqlite_autoindex_usr_1|usr|3| > table|block|block|4|CREATE TABLE block (Stat INTEGER,User INTEGER,Page > TEXT,Text > INTEGER) > table|FreqUse|FreqUse|5|CREATE TABLE FreqUse (Stat INTEGER,User INTEGER,Page > TEX > T,Text INTEGER) > table|blb|blb|6|CREATE TABLE blb (Id INTEGER PRIMARY KEY,Nm INTEGER) > table|atm|atm|7|CREATE TABLE atm (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl > INTEGER,D > c REAL,Dm REAL,St INTEGER) > table|coco|coco|8|CREATE TABLE coco (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl > INTEGE > R,Dc REAL,Dm REAL,St INTEGER) > table|lnk|lnk|9|CREATE TABLE lnk (So INTEGER NOT NULL,Ta INTEGER NOT NULL,Cl > INT > EGER,Tpt INTEGER,UNIQUE > (So,Ta,Cl),CHECK(typeof(So)='integer'),CHECK(typeof(Ta)= > 'integer'),CHECK((typeof(Cl)='integer') OR (typeof(Cl)='null'))) > index|sqlite_autoindex_lnk_1|lnk|10| > table|prm|prm|11|CREATE TABLE prm(ref INTEGER, val INTEGER, own INTEGER, > UNIQUE( > ref, own)) > index|sqlite_autoindex_prm_1|prm|12| > table|email|email|13|CREATE TABLE email (Id INTEGER PRIMARY KEY, Tit INTEGER, > No > m INTEGER, Org INTEGER,eHnm INTEGER, ePort INTEGER, eUnm INTEGER, ePsw > INTEGER, > eScon INTEGER, eDel INTEGER,sHnm INTEGER, sPort INTEGER, sUnm INTEGER, sPsw > INTE > GER, sScon INTEGER,Enam INTEGER, Rnam INTEGER, Unam INTEGER, Onam INTEGER, > iucs > INTEGER, sec1 INTEGER, sec2 INTEGER, sec3 INTEGER, sec4 INTEGER,Cl INTEGER, > St I > NTEGER, aux1 INTEGER, aux2 INTEGER, aux3 INTEGER, aux4 INTEGER, aux5 INTEGER, > au > x6 INTEGER, aux7 INTEGER) > view|AgVtHolder|AgVtHolder|0|CREATE VIEW AgVtHolder AS SELECT id FROM atm > WHERE > id IN(SELECT so FROM lnk L WHERE L.ta=73 AND L.cl=47) > view|AgVtIDt|AgVtIDt|0|CREATE VIEW AgVtIDt AS SELECT L.ta AS 'Hd', C.nm AS > 'Dt' > FROM atm C, lnk L WHERE C.cl=17 AND C.id IN (SELECT L.so FROM lnk L WHERE > L.cl=4 > 8 AND L.ta IN(SELECT id FROM AgVtHolder)) AND L.so=C.id > view|AgVtPre|AgVtPre|0|CREATE VIEW AgVtPre AS SELECT L.ta AS 'Hd', CAST(Nm AS > IN > T) AS 'Pr' FROM atm C, lnk L WHERE C.cl=17 AND C.id IN(SELECT so FROM lnk L > WHER > E L.cl=49 AND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk > L W > HERE L.ta=73 AND L.cl=47))) AND L.So=C.id > view|AgVtos|AgVtos|0|CREATE VIEW AgVtos AS SELECT D.Hd AS 'Hd', D.Dt AS 'Dt', > P. > Pr AS 'Pr' FROM AgVtIDt D, AgVtPre P WHERE P.Hd=D.Hd > view|AgPdHolder|AgPdHolder|0|CREATE VIEW AgPdHolder AS SELECT id FROM atm > WHERE > id IN(SELECT So FROM lnk L WHERE L.ta=75 AND L.cl=53) > view|AgPdIDt|AgPdIDt|0|CREATE VIEW AgPdIDt AS SELECT L.ta AS 'Hd', C.Nm AS > 'Dt' > FROM atm C, lnk L WHERE C.Cl=18 AND C.id IN (SELECT L.so FROM lnk L WHERE > L.cl=5 > 4 AND L.ta IN(SELECT id FROM AgPdHolder)) AND L.so=C.id > view|AgEfHolder|AgEfHolder|0|CREATE VIEW AgEfHolder AS SELECT id FROM atm > WHERE > id IN(SELECT So FROM lnk L WHERE L.ta=77 AND L.cl=59) > view|AgEfIDt|AgEfIDt|0|CREATE VIEW AgEfIDt AS SELECT L.ta AS 'Hd', C.Nm AS > 'Dt' > FROM atm C, lnk L WHERE C.Cl=19 AND C.id IN (SELECT L.So FROM lnk L WHERE > L.cl=6 > 0 AND L.ta IN(SELECT id FROM AgEfHolder)) AND L.So=C.id > view|AgEfKlv|AgEfKlv|0|CREATE VIEW AgEfKlv AS SELECT L.ta AS 'Hd', C.Nm AS > 'Kl' > FROM atm C, lnk L WHERE C.cl=19 AND C.id IN(SELECT so FROM lnk L WHERE > L.cl=61 A > ND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk L WHERE > L.ta=7 > 7 AND L.cl=59))) AND L.so=C.id > view|AgEfemer|AgEfemer|0|CREATE VIEW AgEfemer AS SELECT D.Hd AS 'Hd', D.Dt AS > 'D > t', P.Kl AS 'Kl' FROM AgEfIDt D, AgEfKlv P WHERE P.Hd=D.Hd > table|ftsm|ftsm|0|CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter > unico > de61 remove_diacritics 0',columnsize=0) > table|ftsm_data|ftsm_data|11332|CREATE TABLE 'ftsm_data'(id INTEGER PRIMARY > KEY, > block BLOB) > table|ftsm_idx|ftsm_idx|11333|CREATE TABLE 'ftsm_idx'(segid, term, pgno, > PRIMARY > KEY(segid, term)) WITHOUT ROWID >
[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules (revisited)
On 12/10/2015 05:15 AM, ajm at zator.com wrote: > Hi list: > > In a C++ Windows app that uses SQLite v. 3.9.1 and behaves well, I try change > the search engine from FTS3/4 modules to FTS5, by means off: > > 1. Define the directive > #define SQLITE_ENABLE_FTS5 1 > > 2.- Create the table: > CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 > remove_diacritics 0',columnsize=0) > > 3.- Populate the table: > INSERT INTO ftsm (row,nm) SELECT id,nm FROM atm WHERE(..) > > After that, the app and the search engine works as espected. > > To update the ftsm table after several inserts and deletes, I try to follow > this steps > > 1a.- Delete the previous table. > DROP TABLE IF EXIST ftsm > > 2a.- Create table (as above) > > 3a.- Populate table (as above). > > This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an > erroro in 1a: "database disk image is malformed". > > Note that in previous attemps I believed that the problem was into try to > delete a ftsm table build with the previous modules, but the error happen > when trying delete a table build with the FTS5 module. > > I managed to drop the ftsm table by means of create a new dbase; create the > same tables (except ftsm); populate the tables, and replacing the previous > dbase with the new one. But obviously, this method is primitive; time > consuming, and has problems when the dbase is in use. > > Some clues? Not really sure why it might fail there. Can you post the entire database schema (results of "SELECT * FROM sqlite_master" or the output of the .schema shell tool command)? Thanks, Dan.
[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules(revisited)
> > Mensaje original > De: Dan Kennedy > Para: sqlite-users at mailinglists.sqlite.org > Fecha: Fri, 11 Dec 2015 02:41:43 +0700 > Asunto: Re: [sqlite] Problem when upgrading from FTS3/4 to FTS5 > modules(revisited) > >> ... >> 1a.- Delete the previous table. >> DROP TABLE IF EXIST ftsm >> >> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives >> an erroro in 1a: "database disk image is malformed". >> >> Note that in previous attemps I believed that the problem was into try to >> delete a ftsm table build with the previous modules, but the error happen >> when trying delete a table build with the FTS5 module. >> ... >> >> Some clues? > >Not really sure why it might fail there. Can you post the entire >database schema (results of "SELECT * FROM sqlite_master" or the output >of the .schema shell tool command)? > Dan: Here is the schema: CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 remove_diacri tics 0',columnsize=0); And here the entire database schema as produced by the shell: sqlite> SELECT * FROM sqlite_master; table|usr|usr|2|CREATE TABLE usr (ky INTEGER PRIMARY KEY,id CHARACTER UNIQUE,lev INTEGER,pwd TEXT) index|sqlite_autoindex_usr_1|usr|3| table|block|block|4|CREATE TABLE block (Stat INTEGER,User INTEGER,Page TEXT,Text INTEGER) table|FreqUse|FreqUse|5|CREATE TABLE FreqUse (Stat INTEGER,User INTEGER,Page TEX T,Text INTEGER) table|blb|blb|6|CREATE TABLE blb (Id INTEGER PRIMARY KEY,Nm INTEGER) table|atm|atm|7|CREATE TABLE atm (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl INTEGER,D c REAL,Dm REAL,St INTEGER) table|coco|coco|8|CREATE TABLE coco (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl INTEGE R,Dc REAL,Dm REAL,St INTEGER) table|lnk|lnk|9|CREATE TABLE lnk (So INTEGER NOT NULL,Ta INTEGER NOT NULL,Cl INT EGER,Tpt INTEGER,UNIQUE (So,Ta,Cl),CHECK(typeof(So)='integer'),CHECK(typeof(Ta)= 'integer'),CHECK((typeof(Cl)='integer') OR (typeof(Cl)='null'))) index|sqlite_autoindex_lnk_1|lnk|10| table|prm|prm|11|CREATE TABLE prm(ref INTEGER, val INTEGER, own INTEGER, UNIQUE( ref, own)) index|sqlite_autoindex_prm_1|prm|12| table|email|email|13|CREATE TABLE email (Id INTEGER PRIMARY KEY, Tit INTEGER, No m INTEGER, Org INTEGER,eHnm INTEGER, ePort INTEGER, eUnm INTEGER, ePsw INTEGER, eScon INTEGER, eDel INTEGER,sHnm INTEGER, sPort INTEGER, sUnm INTEGER, sPsw INTE GER, sScon INTEGER,Enam INTEGER, Rnam INTEGER, Unam INTEGER, Onam INTEGER, iucs INTEGER, sec1 INTEGER, sec2 INTEGER, sec3 INTEGER, sec4 INTEGER,Cl INTEGER, St I NTEGER, aux1 INTEGER, aux2 INTEGER, aux3 INTEGER, aux4 INTEGER, aux5 INTEGER, au x6 INTEGER, aux7 INTEGER) view|AgVtHolder|AgVtHolder|0|CREATE VIEW AgVtHolder AS SELECT id FROM atm WHERE id IN(SELECT so FROM lnk L WHERE L.ta=73 AND L.cl=47) view|AgVtIDt|AgVtIDt|0|CREATE VIEW AgVtIDt AS SELECT L.ta AS 'Hd', C.nm AS 'Dt' FROM atm C, lnk L WHERE C.cl=17 AND C.id IN (SELECT L.so FROM lnk L WHERE L.cl=4 8 AND L.ta IN(SELECT id FROM AgVtHolder)) AND L.so=C.id view|AgVtPre|AgVtPre|0|CREATE VIEW AgVtPre AS SELECT L.ta AS 'Hd', CAST(Nm AS IN T) AS 'Pr' FROM atm C, lnk L WHERE C.cl=17 AND C.id IN(SELECT so FROM lnk L WHER E L.cl=49 AND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk L W HERE L.ta=73 AND L.cl=47))) AND L.So=C.id view|AgVtos|AgVtos|0|CREATE VIEW AgVtos AS SELECT D.Hd AS 'Hd', D.Dt AS 'Dt', P. Pr AS 'Pr' FROM AgVtIDt D, AgVtPre P WHERE P.Hd=D.Hd view|AgPdHolder|AgPdHolder|0|CREATE VIEW AgPdHolder AS SELECT id FROM atm WHERE id IN(SELECT So FROM lnk L WHERE L.ta=75 AND L.cl=53) view|AgPdIDt|AgPdIDt|0|CREATE VIEW AgPdIDt AS SELECT L.ta AS 'Hd', C.Nm AS 'Dt' FROM atm C, lnk L WHERE C.Cl=18 AND C.id IN (SELECT L.so FROM lnk L WHERE L.cl=5 4 AND L.ta IN(SELECT id FROM AgPdHolder)) AND L.so=C.id view|AgEfHolder|AgEfHolder|0|CREATE VIEW AgEfHolder AS SELECT id FROM atm WHERE id IN(SELECT So FROM lnk L WHERE L.ta=77 AND L.cl=59) view|AgEfIDt|AgEfIDt|0|CREATE VIEW AgEfIDt AS SELECT L.ta AS 'Hd', C.Nm AS 'Dt' FROM atm C, lnk L WHERE C.Cl=19 AND C.id IN (SELECT L.So FROM lnk L WHERE L.cl=6 0 AND L.ta IN(SELECT id FROM AgEfHolder)) AND L.So=C.id view|AgEfKlv|AgEfKlv|0|CREATE VIEW AgEfKlv AS SELECT L.ta AS 'Hd', C.Nm AS 'Kl' FROM atm C, lnk L WHERE C.cl=19 AND C.id IN(SELECT so FROM lnk L WHERE L.cl=61 A ND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk L WHERE L.ta=7 7 AND L.cl=59))) AND L.so=C.id view|AgEfemer|AgEfemer|0|CREATE VIEW AgEfemer AS SELECT D.Hd AS 'Hd', D.Dt AS 'D t', P.Kl AS 'Kl' FROM AgEfIDt D, AgEfKlv P WHERE P.Hd=D.Hd table|ftsm|ftsm|0|CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unico de61 remove_diacritics 0',columnsize=0) table|ftsm_data|ftsm_data|11332|CREATE TABLE 'ftsm_data'(id INTEGER PRIMARY KEY, block BLOB) table|ftsm_idx|ftsm_idx|11333|CREATE TABLE 'ftsm_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID table|ftsm_content|ftsm_con
[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules (revisited)
Hi list: In a C++ Windows app that uses SQLite v. 3.9.1 and behaves well, I try change the search engine from FTS3/4 modules to FTS5, by means off: 1. Define the directive #define SQLITE_ENABLE_FTS5 1 2.- Create the table: CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 remove_diacritics 0',columnsize=0) 3.- Populate the table: INSERT INTO ftsm (row,nm) SELECT id,nm FROM atm WHERE(..) After that, the app and the search engine works as espected. To update the ftsm table after several inserts and deletes, I try to follow this steps 1a.- Delete the previous table. DROP TABLE IF EXIST ftsm 2a.- Create table (as above) 3a.- Populate table (as above). This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an erroro in 1a: "database disk image is malformed". Note that in previous attemps I believed that the problem was into try to delete a ftsm table build with the previous modules, but the error happen when trying delete a table build with the FTS5 module. I managed to drop the ftsm table by means of create a new dbase; create the same tables (except ftsm); populate the tables, and replacing the previous dbase with the new one. But obviously, this method is primitive; time consuming, and has problems when the dbase is in use. Some clues? Thanks in advance. -- Adolfo J. Millan
