[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
[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules
> Mensaje original > De: Richard Hipp >Fecha: 4/11/2015 23:23 (GMT+01:00) >Para: SQLite mailing list >Asunto: Re: [sqlite] Problem when upgrading from FTS3/4 to FTS5 modules >Here is a hack. >Let the name of your FTS3 table be "xyzzy" >(1) Invoke "PRAGMA writable_schema=ON". (Read the documentation about >the dangers thereof. Do not complain if something goes wrong and you >corrupt your database.) >(2) Run "DELETE FROM sqlite_master WHERE name='xyzzy';" >(3) Set "PRAGMA writable_schema=OFF" again. >(4) Run the following: > >DROP TABLE IF EXISTS xyzzy_content; >DROP TABLE IF EXISTS xyzzy_segments; >DROP TABLE IF EXISTS xyzzy_segdir; >DROP TABLE IF EXISTS xyzzy_docsize; >DROP TABLE IF EXISTS xyzzy_stat; Thanks a lot That tricky worked fine. -- Adolfo
[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules
Hi list:
Using SQLite 3.9.1, suppose a C++ application using FTS3/4 modules with this
pseudocode:
1a In config.h
#define SQLITE_ENABLE_FTS3 1
#define SQLITE_ENABLE_FTS3_PARENTHESIS 1
2a.- Delete the previous table if exist
DROP TABLE IF EXISTS ftsm
3a.- Then create the table
CREATE VIRTUAL TABLE ftsm USING fts4(nm,tokenize=simple,matchinfo=fts3)
4a.- Populate the table
INSERT INTO ftsm (rowid,nm) SELECT id,nm FROM atm WHERE(...)
The app run as espected and the search mechanism work as a charm.
Then try to upgrade the app to use the FTS5 module -within the previous dbases-
with this pseudocode:
1b In config.h
#define SQLITE_ENABLE_FTS5 1
2b.- Delete the previous table if exist (same as previous)
DROP TABLE IF EXISTS ftsm
3b.- Then create the table
CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='unicode61
remove_diacritics 0',columnsize=0)
4b.- Populate the table (same as previous)
INSERT INTO ftsm (rowid,nm) SELECT id,nm FROM atm WHERE(...)
When reaching 2b, the new app show an error: "no such module: fts4"
When accessing the dataBase with the shell, the .tables command show the ftsm
table. Then, using manually the 2a query ("DROP TABLE IF EXISTS ftsm;") the
table is deleted without complain and the .tables command show that the table
not longer exist.
Then, the app step thru 2b without complain and creates and populates the new
table smoothly (steps 3b and 4b).
The question: do is there any method to delete the old table without including
the FTS3/4 modules to those users "in the field"?
Thanks for any idea.
--
Adolfo J. Millan
[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules
On 11/4/15, ajm at zator.com wrote: > The question: do is there any method to delete the old table without > including the FTS3/4 modules to those users "in the field"? > Here is a hack. Let the name of your FTS3 table be "xyzzy" (1) Invoke "PRAGMA writable_schema=ON". (Read the documentation about the dangers thereof. Do not complain if something goes wrong and you corrupt your database.) (2) Run "DELETE FROM sqlite_master WHERE name='xyzzy';" (3) Set "PRAGMA writable_schema=OFF" again. (4) Run the following: DROP TABLE IF EXISTS xyzzy_content; DROP TABLE IF EXISTS xyzzy_segments; DROP TABLE IF EXISTS xyzzy_segdir; DROP TABLE IF EXISTS xyzzy_docsize; DROP TABLE IF EXISTS xyzzy_stat; -- D. Richard Hipp drh at sqlite.org
