[sqlite] Problem when upgrading from FTS3/4 to FTS5modules(revisited)
On 12/11/2015 08:22 PM, ajm at zator.com wrote: >> Mensaje original >> De: Dan Kennedy >> Para: sqlite-users at mailinglists.sqlite.org >> Fecha: Fri, 11 Dec 2015 15:28:33 +0700 >> Asunto: Re: [sqlite] Problem when upgrading from FTS3/4 to >> FTS5modules(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? >> > Dan: > > Surprisingly, the shell does not complain when using the same query, and > indeed, drop the table. > > Yeah, I also believe that the problem is in my roof, although that code has > been behaving Ok from ages with the FTS3/4 modules (only changes some > directives in other places of the code). > > Any way, when running again the code when the table has been previously > erased (with the shell), the routine works without complaint (executes the > query correctly), and despite the warning, the rest of the app behaves > correctly and the queries related with the search works fine. > > When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, > but sqlite3_step() ends with error 11. Are you able to compile a debugging build of SQLite and set a breakpoint in sqlite3CorruptError()? Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS statement (pretty good chance), the stack trace will tell us more about the form of corruption SQLite thinks it has found. Thanks, Dan. > > When the table is already deleted, sqlite3_prepare_v2() ends Ok, and > sqlite3_step() ends with 101. > > Frankly, because the routine is the same in both times, and there are not > variables to bind, really I don't know where to look. Anyway thanks for > your's attention. > > Cheers! > > -- > Adolfo J. Millan > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ABOUT ROWID
Thanks for your help! What you have said makes sense. I will try it! As soon as possible, I will tell you the testing result on Monday.. At 2015-12-11 20:16:24, "Keith Medcalf" wrote: > >Actually, you need to pull the power plug after you shut it down for more than >30 seconds, then plug it in and reboot. Only then are you sure the all the >cache has been flushed. > >Alternatively, run the test many times (say 1000) and average the results. > >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of ??? >> Sent: Friday, 11 December, 2015 01:29 >> To: SQLite mailing list >> Subject: Re: [sqlite] ABOUT ROWID >> >> 1.Reboot computer >> 2.Shut down computer ,and then start computer >> >> the two method above , can also solve the cache perfectly. >> the testing computer is dell. >> >> At 2015-12-11 16:22:44, "Clemens Ladisch" wrote: >> >Hick Gunter wrote: >> 2) run each query in a new process (so each one >> will need to read the data from disk) > >This does not help against the >> file cache of the OS. > > >Regards, >Clemens >-- > >> This communication >> (including any attachments) is intended for the use >> of the intended >> recipient(s) only and may contain information that is >> confidential, >> privileged or legally protected. Any unauthorized use or >> dissemination >> of this communication is strictly prohibited. If you >> have received this >> communication in error, please immediately notify >> the sender by return >> e-mail message and delete all copies of the >> original communication. >> Thank you for your cooperation. > >This e-mail contains public information >> intended for any subscriber of >this mailing list and for anybody else who >> bothers to read it; it will >be copied, disclosed and distributed to the >> public. If you think you >are not the intended recipi >> ent, please commit suicide immediately. >These terms apply also to any e- >> mails quoted in, referenced from, or >answering this e-mail, and supersede >> any confidentiality notices in >those e-mails. Additionally, >> confidentiality notices in those e-mails >will incur legal processing fees >> of $42 per line; you have agreed to >this by reading this confidentiality >> notice. >___ >sqlite-users >> mailing list >sqlite-users at mailinglists.sqlite.org >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Functions: "That assembly does not allow partially trusted"
Hello After migration from Windows Server 2003 to Windows Server 2012R2 the following error occurs in the C# library SQLite (version 1.0.98.0): (NuGet Package) "That assembly does not allow partially trusted" The server is shared and there is no possibility to change system settings. The program have to use ASP.NET 4.0 C# library under IIS and Visual Studio 2015 Any help is greatly appreciated. Eduard Laederach Kernkraftwerk Goesgen-Daeniken AG CH-4658 Daeniken, Switzerland Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
[sqlite] ABOUT ROWID
1.Reboot computer 2.Shut down computer ,and then start computer the two method above , can not also solve the cache perfectly. the testing computer is dell. At 2015-12-11 16:29:09, "???" <2004wqg2008 at 163.com> wrote: >1.Reboot computer > 2.Shut down computer ,and then start computer > >the two method above , can also solve the cache perfectly. >the testing computer is dell. > >At 2015-12-11 16:22:44, "Clemens Ladisch" wrote: >Hick >Gunter wrote: >> 2) run each query in a new process (so each one will need to >read the data from disk) > >This does not help against the file cache of the >OS. > > >Regards, >Clemens >-- > >> This communication (including any >attachments) is intended for the use >> of the intended recipient(s) only and >may contain information that is >> confidential, privileged or legally >protected. Any unauthorized use or >> dissemination of this communication is >strictly prohibited. If you >> have received this communication in error, >please immediately notify >> the sender by return e-mail message and delete >all copies of the >> original communication. Thank you for your cooperation. > >>This e-mail contains public information intended for any subscriber of >this >mailing list and for anybody else who bothers to read it; it will >be copied, >disclosed and distributed to the public. If you think you >are not the >intended recipi > ent, please commit suicide immediately. >These terms apply also to any > e-mails quoted in, referenced from, or >answering this e-mail, and supersede > any confidentiality notices in >those e-mails. Additionally, confidentiality > notices in those e-mails >will incur legal processing fees of $42 per line; > you have agreed to >this by reading this confidentiality notice. > >___ >sqlite-users mailing list > >sqlite-users at mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ABOUT ROWID
1.Reboot computer 2.Shut down computer ,and then start computer the two method above , can also solve the cache perfectly. the testing computer is dell. At 2015-12-11 16:22:44, "Clemens Ladisch" wrote: >Hick Gunter wrote: >> 2) run each query in a new process (so each one will need to read the data from disk) > >This does not help against the file cache of the OS. > > >Regards, >Clemens >-- > >> This communication (including any attachments) is intended for the use >> of the intended recipient(s) only and may contain information that is >> confidential, privileged or legally protected. Any unauthorized use or >> dissemination of this communication is strictly prohibited. If you >> have received this communication in error, please immediately notify >> the sender by return e-mail message and delete all copies of the >> original communication. Thank you for your cooperation. > >This e-mail contains public information intended for any subscriber of >this mailing list and for anybody else who bothers to read it; it will >be copied, disclosed and distributed to the public. If you think you >are not the intended recipient, please commit suicide immediately. >These terms apply also to any e-mails quoted in, referenced from, or >answering this e-mail, and supersede any confidentiality notices in >those e-mails. Additionally, confidentiality notices in those e-mails >will incur legal processing fees of $42 per line; you have agreed to >this by reading this confidentiality notice. >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with accumulating decimal values
Hi all I am having a problem accumulating decimal values. I am actually using Python, but I can reproduce it in the sqlite3 interactive terminal. SQLite version 3.8.6 2014-08-15 11:46:33 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); sqlite> INSERT INTO fmtemp VALUES (1, 0); sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; 123.45 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 246.9 I repeat this a number of times, and it runs fine, until this happens - sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; 5802.15 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; 5925.599 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; 6049.049 Can anyone explain what is going on, and is there a way to avoid it? Thanks Frank Millman
[sqlite] ABOUT ROWID
Because of I want to test some funtions of the SQLite. According to Clemens said, the testing result may be influenced by cache. How could avoid the influence of cache? Is there funtion can eliminate the influence? best regards. WQG At 2015-12-11 15:40:56, "Clemens Ladisch" wrote: >??? wrote: >> For example, tableA contains two columns: implicit rowid, A_id. >> we create index on A_id. >> firstly, we used rowid to select the row, cost about 400 seconds; >> secondly, we used A_id to select the row, cost about 200 seconds; >> thirdly, we also used rowid to select the row, cost about 200 seconds. > >This can be explained by the data being cached. > >> if firstly,we used A_id to select the row, cost about 200 seconds; >> the create index on A_id may have some influence to do with the rowid. >> and from this example, I also think the rowid is not implicit index. > >If all columns of the table are contained in the index, then the "select *" >can read all values from the index itself, without having to look up the >row in the original table. This is called a covering index. > >If your table has more columns, lookups on A_id will become slower. > > >Regards, >Clemens >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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] ABOUT ROWID
HI,Hick Gunter, Thanks for you reply. I find a strange problem. For example, tableA contains two columns: implicit rowid, A_id. we create index on A_id. firstly, we used rowid to select the row, cost about 400 seconds; secondly, we used A_id to select the row, cost about 200 seconds; thirdly, we also used rowid to select the row, cost about 200 seconds. if firstly,we used A_id to select the row, cost about 200 seconds; secondly, we also used rowid to select the row, cost about 200 seconds. the create index on A_id may have some influence to do with the rowid. and from this example, I also think the rowid is not implicit index. what do you think about this phenomenon? best regards! At 2015-12-11 15:15:16, "Hick Gunter" wrote: >There is always an implicit index on the SQLite rowid and this is the fastest >method to locate a row. > >The next best thing for retrieval speed is an index that matches the where >clause. If you do not have one, SQLite may decide to create a temporary index >anyway, but this depends on the query. Unless you have what is called a >"covering index" (i.e. all fields mentioned in the query are present in the >index), SQLite will retrieve a rowid from the index and use that to read the >row from the table. > >Your second method uses a full table scan, i.e. SQLite is forced to retrieve >every single row of the table to check i fit matches, which ist he slowest >access method > >-Urspr?ngliche Nachricht- >Von: sqlite-users-bounces at mailinglists.sqlite.org >[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von ??? >Gesendet: Freitag, 11. Dezember 2015 07:40 >An: sqlite-users at mailinglists.sqlite.org >Betreff: [sqlite] ABOUT ROWID > >Hi , Everyone, >When I use methodone : select * from table_name where rowid = somenumber ? >When I use methodtwo : select * from table_name where ordinaryid = > somenumber ? (ordinary is not index) >The table_name have at least two columns. >I have some questions, as follows: > 1.The methodone is faster than the methodtwo? > 2.is rowid the default index? > >best wishes! > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >___ > Gunter Hick >Software Engineer >Scientific Games International GmbH >FN 157284 a, HG Wien >Klitschgasse 2-4, A-1130 Vienna, Austria >Tel: +43 1 80100 0 >E-Mail: hick at scigames.at > >This communication (including any attachments) is intended for the use of the >intended recipient(s) only and may contain information that is confidential, >privileged or legally protected. Any unauthorized use or dissemination of this >communication is strictly prohibited. If you have received this communication >in error, please immediately notify the sender by return e-mail message and >delete all copies of the original communication. Thank you for your >cooperation. > > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ABOUT ROWID
Hi , Everyone, When I use methodone : select * from table_name where rowid = somenumber ? When I use methodtwo : select * from table_name where ordinaryid = somenumber ? (ordinary is not index) The table_name have at least two columns. I have some questions, as follows: 1.The methodone is faster than the methodtwo? 2.is rowid the default index? best wishes!
[sqlite] Problem with accumulating decimal values
Frank Millman Friday, December 11, 2015 9:21 AM > > I am having a problem accumulating decimal values. > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; SELECT bal FROM > sqlite> fmtemp; > 6049.049 > > Can anyone explain what is going on, and is there a way to avoid it? > It's because of the way that floating point math and display happens. Simply put, there's no way to avoid it if you really want to use floating point numbers. Bernardo's suggestion about using integer math may be a fairly easy workaround, especially if you're just adding numbers. -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message.
[sqlite] Problem with accumulating decimal values
I *think* this is due to you creating an integer when you first create the entries Try changing from INSERT INTO fmtemp VALUES (1, 0); to INSERT INTO fmtemp VALUES (1, 0.0); Just did macpro:js rwillett$ sqlite3 SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); sqlite> INSERT INTO fmtemp VALUES (1, 0.0); sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; Error: no such column: bal sqlite> SELECT balance FROM fmtemp; 123.45 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 1234.5 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 1357.95 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 4567.65 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45;
[sqlite] Problem when upgrading from FTS3/4 to FTS5modules(revisited)
> > Mensaje original > De: Dan Kennedy > Para: sqlite-users at mailinglists.sqlite.org > Fecha: Fri, 11 Dec 2015 15:28:33 +0700 > Asunto: Re: [sqlite] Problem when upgrading from FTS3/4 to > FTS5modules(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? > Dan: Surprisingly, the shell does not complain when using the same query, and indeed, drop the table. Yeah, I also believe that the problem is in my roof, although that code has been behaving Ok from ages with the FTS3/4 modules (only changes some directives in other places of the code). Any way, when running again the code when the table has been previously erased (with the shell), the routine works without complaint (executes the query correctly), and despite the warning, the rest of the app behaves correctly and the queries related with the search works fine. When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, but sqlite3_step() ends with error 11. When the table is already deleted, sqlite3_prepare_v2() ends Ok, and sqlite3_step() ends with 101. Frankly, because the routine is the same in both times, and there are not variables to bind, really I don't know where to look. Anyway thanks for your's attention. Cheers! -- Adolfo J. Millan
[sqlite] ABOUT ROWID
On 11 Dec 2015, at 8:29am, ??? <2004wqg2008 at 163.com> wrote: > 1.Reboot computer > 2.Shut down computer ,and then start computer I am sorry to say that ??? (copy and paste, I don't know Chinese !) has given the first correct answer. There are at least three levels of caching involved in fetching data from disk, and there is no way to tell the computer to flush all of the levels. Testing different options for disk speed can only be done by either 1) Using a huge amount of data which is bigger than any cache you may have (at least 4 gigabytes on a standard setup) 2) Turning the computer off and on again Simon.
[sqlite] Problem with accumulating decimal values
Looks like you thought you could have a DECIMAL type (such as MySQL DECIMAL) here. But SQLite does not allow for this. My workaround usually is: create table accounts(account_number integer, balance integer); create view accounts_view as select account_number, balance / 100.0 from accounts; You may want to use text (or another relational system) if you get to gargantuan values as integer is limited to signed 8 bytes (which I think means up to positive 9223372036854775807, needs confirmation).
[sqlite] Problem with accumulating decimal values
Hi Frank, You want to store an INTEGER type using the lowest used unit (cents or mills). This page https://www.sqlite.org/datatype3.html may be of assistance next time -- Bernardo Sulzbach
[sqlite] ABOUT ROWID
On Fri, 11 Dec 2015 05:14:24 -0700 "Keith Medcalf" wrote: > Far better is to run the queries multiple times in succession (say, > 1000) and then average the result. Good advice. Sometimes it seems like caching is "cheating": we don't know the performance of something if we're using the cache. But the cache is part of reality; it will affect performance during real-world use of the software. Pretending it doesn't exist will lead to optimizing the wrong thing. OTOH, testing can leave the cache "hotter" than it would be under normal use, depending on what "normal" is. if the OS is answering to a lot of disparate requests, SQLite will suffer more cache misses than if it's the only application running. If the data are large enough, even one table scan could remove more interesting bits from the cache. If you want to test the effect of caching, one suggestion that's been missing from this thread is umount(8). Unmounting the device is bound to release any kernel resources associated with it. Remounting it will bring only the barest information from the filesystem into memory. One difference between unmounting and using the Big Red Switch is that the drive's own cache is in play. But that cache is relatively small and afaik has a write lifetime measured in milliseconds. Unless you're reading the same 8 MB over and over, the effect of the drive cache won't skew your test results. --jkl
[sqlite] Problem with accumulating decimal values
On Fri, Dec 11, 2015 at 8:18 AM, Adam Devita wrote: > A good start at the long answer can be found in the archives of this list. > > > http://sqlite.1065341.n5.nabble.com/Simple-Math-Question-td85140.html#a85157 > also found at > > https://www.mail-archive.com/sqlite-users at > mailinglists.sqlite.org/msg04587.html > (web search sqlite "simple math question") > > It has background, theory, and they show how the conversions of > decimals to floating point and how they add works, using several > examples. > +1 -- Scott Robison
[sqlite] Problem with accumulating decimal values
A good start at the long answer can be found in the archives of this list. http://sqlite.1065341.n5.nabble.com/Simple-Math-Question-td85140.html#a85157 also found at https://www.mail-archive.com/sqlite-users at mailinglists.sqlite.org/msg04587.html (web search sqlite "simple math question") It has background, theory, and they show how the conversions of decimals to floating point and how they add works, using several examples. regards, Adam D. On Fri, Dec 11, 2015 at 9:55 AM, Richard Hipp wrote: > On 12/11/15, Frank Millman wrote: >> >> Can anyone explain what is going on, and is there a way to avoid it? >> > > Short answer: https://www.sqlite.org/faq.html#q16 > > I don't have a longer answer readily at hand, but as questions about > floating point numbers come up a lot, probably I should write up a > tutorial. I'll try to get that done before the end of the year... > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] Problem with accumulating decimal values
On 12/11/15, Frank Millman wrote: > > Can anyone explain what is going on, and is there a way to avoid it? > Short answer: https://www.sqlite.org/faq.html#q16 I don't have a longer answer readily at hand, but as questions about floating point numbers come up a lot, probably I should write up a tutorial. I'll try to get that done before the end of the year... -- D. Richard Hipp drh at sqlite.org
[sqlite] ABOUT ROWID
Hick Gunter wrote: > 2) run each query in a new process (so each one will need to read the data > from disk) This does not help against the file cache of the OS. Regards, Clemens -- > This communication (including any attachments) is intended for the use > of the intended recipient(s) only and may contain information that is > confidential, privileged or legally protected. Any unauthorized use or > dissemination of this communication is strictly prohibited. If you > have received this communication in error, please immediately notify > the sender by return e-mail message and delete all copies of the > original communication. Thank you for your cooperation. This e-mail contains public information intended for any subscriber of this mailing list and for anybody else who bothers to read it; it will be copied, disclosed and distributed to the public. If you think you are not the intended recipient, please commit suicide immediately. These terms apply also to any e-mails quoted in, referenced from, or answering this e-mail, and supersede any confidentiality notices in those e-mails. Additionally, confidentiality notices in those e-mails will incur legal processing fees of $42 per line; you have agreed to this by reading this confidentiality notice.
[sqlite] ABOUT ROWID
??? wrote: > For example, tableA contains two columns: implicit rowid, A_id. > we create index on A_id. > firstly, we used rowid to select the row, cost about 400 seconds; > secondly, we used A_id to select the row, cost about 200 seconds; > thirdly, we also used rowid to select the row, cost about 200 seconds. This can be explained by the data being cached. > if firstly,we used A_id to select the row, cost about 200 seconds; > the create index on A_id may have some influence to do with the rowid. > and from this example, I also think the rowid is not implicit index. If all columns of the table are contained in the index, then the "select *" can read all values from the index itself, without having to look up the row in the original table. This is called a covering index. If your table has more columns, lookups on A_id will become slower. Regards, Clemens
[sqlite] Problem with accumulating decimal values
On Fri, Dec 11, 2015 at 8:21 AM, Frank Millman wrote: > Hi all > > I am having a problem accumulating decimal values. > > I am actually using Python, but I can reproduce it in the sqlite3 > interactive terminal. > > SQLite version 3.8.6 2014-08-15 11:46:33 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); > sqlite> INSERT INTO fmtemp VALUES (1, 0); > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 123.45 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT balance FROM fmtemp; > 246.9 > > I repeat this a number of times, and it runs fine, until this happens - > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 5802.15 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 5925.599 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 6049.049 > > Can anyone explain what is going on, and is there a way to avoid it? > > Thanks > > Frank Millman > > ?This is a common problem. It has been discussed here, and elsewhere, quite a bit. Basically, you want _decimal_ accuracy from a _binary_ floating point format. But _decimal_ floating point numbers may not have an exact _binary_ floating point representation. Perhaps these will be of some help: http://dba.stackexchange.com/questions/62491/why-does-sqlite-return-incorrect-sum http://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html (generic despite being from Oracle Corp.) The real solution is IEEE 754-2008 decimal floating point implementation. https://en.wikipedia.org/wiki/Decimal_floating_point https://software.intel.com/en-us/articles/intel-decimal-floating-point-math-library The only _hardware_ implementation that I know of for this format is from IBM, on their Power6 (and after) and z9 (and after) series machines. It is definitely not (yet) available on an Intel based machine. -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] ABOUT ROWID
1) disregard the results of the first query timing (this one has to read the data into the cache) 2) run each query in a new process (so each one will need to read the data from disk) -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von ??? Gesendet: Freitag, 11. Dezember 2015 09:13 An: SQLite mailing list Betreff: Re: [sqlite] ABOUT ROWID Because of I want to test some funtions of the SQLite. According to Clemens said, the testing result may be influenced by cache. How could avoid the influence of cache? Is there funtion can eliminate the influence? best regards. WQG At 2015-12-11 15:40:56, "Clemens Ladisch" wrote: >??? wrote: >> For example, tableA contains two columns: implicit rowid, A_id. >> we create index on A_id. >> firstly, we used rowid to select the row, cost about 400 seconds; >> secondly, we used A_id to select the row, cost about 200 seconds; >> thirdly, we also used rowid to select the row, cost about 200 seconds. > >This can be explained by the data being cached. > >> if firstly,we used A_id to select the row, cost about 200 seconds; >> the create index on A_id may have some influence to do with the rowid. >> and from this example, I also think the rowid is not implicit index. > >If all columns of the table are contained in the index, then the "select *" >can read all values from the index itself, without having to look up >the row in the original table. This is called a covering index. > >If your table has more columns, lookups on A_id will become slower. > > >Regards, >Clemens >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] ABOUT ROWID
On 2015-12-11 02:28, ??? wrote: > The first time your reading from Disk, the 2nd and subsequent times you > reading from cache > > HI,Hick Gunter, > Thanks for you reply. > I find a strange problem. > For example, tableA contains two columns: implicit rowid, A_id. > we create index on A_id. > firstly, we used rowid to select the row, cost about 400 seconds; > secondly, we used A_id to select the row, cost about 200 seconds; > thirdly, we also used rowid to select the row, cost about 200 seconds. > > if firstly,we used A_id to select the row, cost about 200 seconds; > secondly, we also used rowid to select the row, cost about 200 seconds. > > the create index on A_id may have some influence to do with the rowid. and > from this example, I also think the rowid is not implicit index. > what do you think about this phenomenon? > > best regards! > > At 2015-12-11 15:15:16, "Hick Gunter" wrote: > >> There is always an implicit index on the SQLite rowid and this is the >> fastest method to locate a row. The next best thing for retrieval speed is >> an index that matches the where clause. If you do not have one, SQLite may >> decide to create a temporary index anyway, but this depends on the query. >> Unless you have what is called a "covering index" (i.e. all fields mentioned >> in the query are present in the index), SQLite will retrieve a rowid from >> the index and use that to read the row from the table. Your second method >> uses a full table scan, i.e. SQLite is forced to retrieve every single row >> of the table to check i fit matches, which ist he slowest access method >> -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at >> mailinglists.sqlite.org [mailto:sqlite-users-bounces at >> mailinglists.sqlite.org] Im Auftrag von ??? Gesendet: Freitag, 11. Dezember >> 2015 07:40 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] >> ABOUT ROWID Hi , Everyone, When I use methodone : select * from table_name where rowid = somenumber ? When I use methodtwo : select * from table_name where ordinaryid = somenumber ? (ordinary is not index) The table_name have at least two columns. I have some questions, as follows: 1.The methodone is faster than the methodtwo? 2.is rowid the default index? best wishes! ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users [1] ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users [1] > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users [1] Links: -- [1] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ABOUT ROWID
There is always an implicit index on the SQLite rowid and this is the fastest method to locate a row. The next best thing for retrieval speed is an index that matches the where clause. If you do not have one, SQLite may decide to create a temporary index anyway, but this depends on the query. Unless you have what is called a "covering index" (i.e. all fields mentioned in the query are present in the index), SQLite will retrieve a rowid from the index and use that to read the row from the table. Your second method uses a full table scan, i.e. SQLite is forced to retrieve every single row of the table to check i fit matches, which ist he slowest access method -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von ??? Gesendet: Freitag, 11. Dezember 2015 07:40 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] ABOUT ROWID Hi , Everyone, When I use methodone : select * from table_name where rowid = somenumber ? When I use methodtwo : select * from table_name where ordinaryid = somenumber ? (ordinary is not index) The table_name have at least two columns. I have some questions, as follows: 1.The methodone is faster than the methodtwo? 2.is rowid the default index? best wishes! ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] ABOUT ROWID
Actually, you need to pull the power plug after you shut it down for more than 30 seconds, then plug it in and reboot. Only then are you sure the all the cache has been flushed. Alternatively, run the test many times (say 1000) and average the results. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of ??? > Sent: Friday, 11 December, 2015 01:29 > To: SQLite mailing list > Subject: Re: [sqlite] ABOUT ROWID > > 1.Reboot computer > 2.Shut down computer ,and then start computer > > the two method above , can also solve the cache perfectly. > the testing computer is dell. > > At 2015-12-11 16:22:44, "Clemens Ladisch" wrote: > >Hick Gunter wrote: >> 2) run each query in a new process (so each one > will need to read the data from disk) > >This does not help against the > file cache of the OS. > > >Regards, >Clemens >-- > >> This communication > (including any attachments) is intended for the use >> of the intended > recipient(s) only and may contain information that is >> confidential, > privileged or legally protected. Any unauthorized use or >> dissemination > of this communication is strictly prohibited. If you >> have received this > communication in error, please immediately notify >> the sender by return > e-mail message and delete all copies of the >> original communication. > Thank you for your cooperation. > >This e-mail contains public information > intended for any subscriber of >this mailing list and for anybody else who > bothers to read it; it will >be copied, disclosed and distributed to the > public. If you think you >are not the intended recipi > ent, please commit suicide immediately. >These terms apply also to any e- > mails quoted in, referenced from, or >answering this e-mail, and supersede > any confidentiality notices in >those e-mails. Additionally, > confidentiality notices in those e-mails >will incur legal processing fees > of $42 per line; you have agreed to >this by reading this confidentiality > notice. >___ >sqlite-users > mailing list >sqlite-users at mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ABOUT ROWID
> 1) disregard the results of the first query timing (this one has to read > the data into the cache) More correctly, you need to exclude the effects of any operation which primes the cache. This is not necessarily the "first" operation. Far better is to run the queries multiple times in succession (say, 1000) and then average the result. > 2) run each query in a new process (so each one will need to read the data > from disk)> This is untrue. The Operating System caches data between processes. Using a new process will only get rid of in-process cache effects, which are almost non-existant. You are far better to run the test a number of times (say, 1000) and average the results. UInmless of course you are running in a real bitty fondle-slab that has insufficient memory to cache anything at all. > -Urspr?ngliche Nachricht- > Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] Im Auftrag von ??? > Gesendet: Freitag, 11. Dezember 2015 09:13 > An: SQLite mailing list > Betreff: Re: [sqlite] ABOUT ROWID > > Because of I want to test some funtions of the SQLite. > According to Clemens said, the testing result may be influenced by > cache. > How could avoid the influence of cache? Is there funtion can > eliminate the influence? > > best regards. > WQG > > > > > > > > > At 2015-12-11 15:40:56, "Clemens Ladisch" wrote: > >??? wrote: > >> For example, tableA contains two columns: implicit rowid, A_id. > >> we create index on A_id. > >> firstly, we used rowid to select the row, cost about 400 seconds; > >> secondly, we used A_id to select the row, cost about 200 seconds; > >> thirdly, we also used rowid to select the row, cost about 200 > seconds. > > > >This can be explained by the data being cached. > > > >> if firstly,we used A_id to select the row, cost about 200 seconds; > >> the create index on A_id may have some influence to do with the > rowid. > >> and from this example, I also think the rowid is not implicit index. > > > >If all columns of the table are contained in the index, then the "select > *" > >can read all values from the index itself, without having to look up > >the row in the original table. This is called a covering index. > > > >If your table has more columns, lookups on A_id will become slower. > > > > > >Regards, > >Clemens > >___ > >sqlite-users mailing list > >sqlite-users at mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: hick at scigames.at > > This communication (including any attachments) is intended for the use of > the intended recipient(s) only and may contain information that is > confidential, privileged or legally protected. Any unauthorized use or > dissemination of this communication is strictly prohibited. If you have > received this communication in error, please immediately notify the sender > by return e-mail message and delete all copies of the original > communication. Thank you for your cooperation. > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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] maybe bug in regexp and replace with newlines?
On 12/10/2015 9:01 AM, michael wrote:
> The statement:
> select distinct text from v_term_item where replace(lower(text),0x0A,'')
> REGEXP('.*/some_text/.*');
> doesn't work like that:
Try x'0A' instead (that's a BLOB literal, and should be converted to a
string). 0x0A is an integer, I suspect it gets converted to the string '10'
--
Igor Tandetnik
