Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....
> -Original Message- > From: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Hick Gunter > > In our virtual table implementations, we are using the rowid > to return the location of the record in the backing store > (e.g. record offset in the file used as a backing store, > offset within a shared memory section or maybe even the > memory address of the record image) and also implement fast > lookup by rowid. > > If you don't require such ability, you may as well return a > constant, a global counter value or a counter that is reset > in the xFilter function. > > So, YES you always have to implement the xRowid method. > > It will only get called if your SELECT statement explicitly > mentions it. No "INTEGER PRIMARY KEY" magic is performed for > virtual tables. > Thanks for your input as well; I somehow missed it until just now. Cheers! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....
> Behalf Of Dan Kennedy > Sent: Tuesday, October 17, 2017 11:58 AM > > On 10/17/2017 01:22 PM, Hick Gunter wrote: > > In our virtual table implementations, we are using the > rowid to return the location of the record in the backing > store (e.g. record offset in the file used as a backing > store, offset within a shared memory section or maybe even > the memory address of the record image) and also implement > fast lookup by rowid. > > > > If you don't require such ability, you may as well return a > constant, a global counter value or a counter that is reset > in the xFilter function. > > > > So, YES you always have to implement the xRowid method. > > > > It will only get called if your SELECT statement explicitly > mentions it. No "INTEGER PRIMARY KEY" magic is performed for > virtual tables. > > I think the exception is queries with OR terms. With > FTS[345], if you do > something like: > >CREATE VIRTUAL TABLE t1 USING fts5(x); >EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def'; > > You can see the Rowid opcodes. > > SQLite runs two separate queries on the virtual table - one > with "MATCH > 'abc'" and the other with "MATCH 'def'". It uses the rowids for each > matched row to avoid returning duplicates. If the xRowid > method always > returned 0, then only the first set of matches would be returned > (because SQLite would deem the second set to be duplicates of the > first). Or if xRowid returned arbitrary values your results might > include duplicates. etc. > > Same applies to other virtual table types. > > Dan. Yikes, thanks for the insight on that OR use-case; I'll have to do some analysis to see what is my exposure. The counter trick is an interesting suggestion, but I guess I am still at-risk because I have to make it deterministic/repeatable at least in the context of a statement, which can still a challenge. E.g. in your OR clause example, if the query engine does indeed do two table scans (as opposed to one table scan, and computing all the predicates) then I have some risk that the two scans return different results (since my data is coming from APIs, and dynamic, rather that persisted collections). In other projects I've definitely used the counter trick before, caching the underlying data (to support updates and transactions) but those were known to be small datasets. This stuff coming from APIs could be big, so I wanted to avoid caching it all. But one does what one must Thanks for all the feedback! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite version 1.0.106.0 pre-release packages now available
Pre-release packages of System.Data.SQLite version 1.0.106.0 are now available on the System.Data.SQLite website: https://system.data.sqlite.org/ Please click on the "Pre-Release Download" link in order to view the available pre-release packages. Further information about this upcoming release can be seen at: https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you encounter any problems with these pre-release packages. -- Joe Mistachkin @ https://urn.to/r/mistachkin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....
On 10/17/2017 01:22 PM, Hick Gunter wrote: In our virtual table implementations, we are using the rowid to return the location of the record in the backing store (e.g. record offset in the file used as a backing store, offset within a shared memory section or maybe even the memory address of the record image) and also implement fast lookup by rowid. If you don't require such ability, you may as well return a constant, a global counter value or a counter that is reset in the xFilter function. So, YES you always have to implement the xRowid method. It will only get called if your SELECT statement explicitly mentions it. No "INTEGER PRIMARY KEY" magic is performed for virtual tables. I think the exception is queries with OR terms. With FTS[345], if you do something like: CREATE VIRTUAL TABLE t1 USING fts5(x); EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def'; You can see the Rowid opcodes. SQLite runs two separate queries on the virtual table - one with "MATCH 'abc'" and the other with "MATCH 'def'". It uses the rowids for each matched row to avoid returning duplicates. If the xRowid method always returned 0, then only the first set of matches would be returned (because SQLite would deem the second set to be duplicates of the first). Or if xRowid returned arbitrary values your results might include duplicates. etc. Same applies to other virtual table types. Dan. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von dave Gesendet: Montag, 16. Oktober 2017 21:23 An: 'SQLite mailing list' Betreff: [EXTERNAL] [sqlite] xRowid and read only virtual tables Hi, I am building a system which involves a number of virtual table implementations. They are all read-only, but will be involved in a bunch of joins amongst themselves. My question is this: the documentation http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid seems (to my reading) to be always required to be implemented. But does it really? Is it ever used for read-only tables? I have never seen it invoked, and I have been blithely ignoring implementing it, but I wonder if there is a case where it would be invoked for a read-only query and so I am tempting fate. I ask in particular because implementing it will be quite awkward for the underlying implementation in my case, and I'd very much prefer to skip it. Even a 'without rowid' table would imply specifying some primary key, which in a few cases would also be awkward. Thanks in advance, -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] xRowid and read only virtual tables....
> On 10/16/17, dave wrote: > > Hi, I am building a system which involves a number of virtual table > > implementations. They are all read-only, but will be > involved in a bunch of > > joins amongst themselves. My question is this: > > > > the documentation > > http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid > > seems (to my reading) to be always required to be > implemented. But does it > > really? Is it ever used for read-only tables? I have never seen it > > invoked, and I have been blithely ignoring implementing it, > but I wonder if > > there is a case where it would be invoked for a read-only > query and so I am > > tempting fate. > > I don't think xRowid is ever called if you create a WITHOUT ROWID > virtual table (https://sqlite.org//vtab.html#worid). But, just to be > safe, I think I would include a stub function that always returned 0. > -- > D. Richard Hipp Thanks. OK, I am interpreting that to mean: * you could use WITHOUT ROWID, which will surely obviate the need for a valid xRowid implementation. However that does incur the need for defining PRIMARY KEY, etc. (I have verified this. It is problemattic with at least a few of my vtables) * for a read-only vtable, you do not need a valid imlementation of xRowid, you can just stub it. However it is required to be present (i.e. the xRowid member must not be NULL). -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)
If you're doing 1 gigantic insert to populate the table then it's more efficient to delay creating an index until the end. If you've already got a bunch of data in there and are inserting more, then you get diminishing returns from that. In this case though you're not creating a separate good index, you're creating a duplicate one that doesn't add value even when it's there and complete, so it's doing nothing but slowing things down. When you declare a primary key SQLite implements that by creating a unique index on those fields to keep track of it. (named sqlite_autoindex_map_1 below) When you explicitly declare another index on the same data SQLite shrugs and does what you ask, making a second index of the same things. Depending on your confidence level you could get rid of the "primary key" line and only add the explicit unique index at the end of the initial population. Though I'd advise for any primary key to be in there from the start and only delay the creation of non-unique/helper indexes. Just my own personal preference on that though. CREATE TABLE map( zoom_level INTEGER, tile_column INTEGER, tile_row INTEGER, tile_id VARCHAR(256) NOT NULL, PRIMARY KEY(zoom_level, tile_column, tile_row) ); CREATE UNIQUE INDEX map_index ON map( zoom_level ASC, tile_column ASC, tile_row ASC ); sqlite> select * from sqlite_master where type = 'index' and tbl_name = 'map'; type|name|tbl_name|rootpage|sql index|sqlite_autoindex_map_1|map|5| index|map_index|map|7|CREATE UNIQUE INDEX map_index ON map (zoom_level ASC, tile_column ASC, tile_row ASC) sqlite> pragma index_list(map); seq|name|unique|origin|partial 0|map_index|1|c|0 1|sqlite_autoindex_map_1|1|pk|0 sqlite> pragma index_info(sqlite_autoindex_map_1); seqno|cid|name 0|0|zoom_level 1|1|tile_column 2|2|tile_row sqlite> pragma index_info(map_index); seqno|cid|name 0|0|zoom_level 1|1|tile_column 2|2|tile_row -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Fiona Sent: Monday, October 16, 2017 10:36 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB) >>The setting for synchronous is basically what level of safety net do you want if it dies in the middle of something. Setting it to off shouldn't cause any corruption if things go well, it should only come into play if you saw errors or didn't close things down correctly etc. You're right, my Python code was ended manually sometime for it's taking too much time to finish the INSERT/UPDATE operation, or the image data I get is wrong. >>The unique index you declared is redundant by the way, declaring those three fields as the primary key makes a unique index already to keep track of that. Did you intend to make that on the retry table? The redundant map_index is an attempt to improve insert speed, as I learned drop index before insert operation is a better way to go. But now with my data growing so huge, drop/rebuild index also takes quite a long time, and I never choice to drop then create this index anymore, just leave the index there. Does it still effect my operation and I should just drop it? And if I want to speed up insert operation further more, what measures should I consider? I'v already set synchronous and journal_mode off, use transaction and prepared statement, but when insert data there's still 3 times' speed difference between my code and the *attach-insert* method. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using .testcase and .check in continuous integration test
On Tue, Oct 17, 2017 at 3:16 PM, Lodewijk Duymaer van Twist < lodew...@adesys.nl> wrote: > Thank you for investigating. You're fix works. Should I repost this as a > bug with your fix, or will this be picked up as is right now? > Glad it did. Just sit tight and again wait and see if Dr Hipp agrees the behavior should change or not. I'm hopeful he might, but if he doesn't, there's little you can do about it. I don't think reposting is necessary for now. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using .testcase and .check in continuous integration test
Hi Dominique, Thank you for investigating. You're fix works. Should I repost this as a bug with your fix, or will this be picked up as is right now? Kind regard, Lodewijk Van: "Dominique Devienne" Aan: "sqlite-users" Verzonden: Dinsdag 17 oktober 2017 13:38:13 Onderwerp: Re: [sqlite] Using .testcase and .check in continuous integration test On Tue, Oct 17, 2017 at 12:30 PM, Lodewijk Duymaer van Twist < lodew...@adesys.nl> wrote: > That would be an other way, but what I'm looking for is using the Command > Line Shell ".testcase" and ".check" method. > OK. That's new information :) > Create an example test file: > echo ".testcase 100 > SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1); > .check 1||1 > > .testcase 110 > SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1); > .check 1|5|1" > test-script.sql > > Execute the test: > sqlite3 < test-script.sql > > Bail will not work for that. > shell.c needs to change for that, by 1 character I believe, in do_meta_command. In case of a failure, set rc to 1 instead of 2. That way the shell no longer exits with 0, but 1, i.e. an error. I'm actually surprised the result of do_meta_command() is not subject to .bail, and always exits if non-zero. Either do those changes yourself in your locally compiled shell, or hope Richard agrees the behavior should change. --DD rc = do_meta_command(azCmd[i], &data); if( rc ) return rc==2 ? 0 : rc; static int do_meta_command(char *zLine, ShellState *p){ ... /* Cancel output redirection, if it is currently set (by .testcase) ** Then read the content of the testcase-out.txt file and compare against ** azArg[1]. If there are differences, report an error and exit. */ if( c=='c' && n>=3 && strncmp(azArg[0], "check", n)==0 ){ char *zRes = 0; output_reset(p); if( nArg!=2 ){ raw_printf(stderr, "Usage: .check GLOB-PATTERN\n"); rc = 2; }else if( (zRes = readFile("testcase-out.txt", 0))==0 ){ raw_printf(stderr, "Error: cannot read 'testcase-out.txt'\n"); rc = 2; }else if( testcase_glob(azArg[1],zRes)==0 ){ utf8_printf(stderr, "testcase-%s FAILED\n Expected: [%s]\n Got: [%s]\n", p->zTestcase, azArg[1], zRes); rc = 2; < change to 1 }else{ utf8_printf(stdout, "testcase-%s ok\n", p->zTestcase); p->nCheck++; } sqlite3_free(zRes); }else ... meta_command_exit: if( p->outCount ){ p->outCount--; if( p->outCount==0 ) output_reset(p); } return rc; } ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using .testcase and .check in continuous integration test
On Tue, Oct 17, 2017 at 12:30 PM, Lodewijk Duymaer van Twist < lodew...@adesys.nl> wrote: > That would be an other way, but what I'm looking for is using the Command > Line Shell ".testcase" and ".check" method. > OK. That's new information :) > Create an example test file: > echo ".testcase 100 > SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1); > .check 1||1 > > .testcase 110 > SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1); > .check 1|5|1" > test-script.sql > > Execute the test: > sqlite3 < test-script.sql > > Bail will not work for that. > shell.c needs to change for that, by 1 character I believe, in do_meta_command. In case of a failure, set rc to 1 instead of 2. That way the shell no longer exits with 0, but 1, i.e. an error. I'm actually surprised the result of do_meta_command() is not subject to .bail, and always exits if non-zero. Either do those changes yourself in your locally compiled shell, or hope Richard agrees the behavior should change. --DD rc = do_meta_command(azCmd[i], &data); if( rc ) return rc==2 ? 0 : rc; static int do_meta_command(char *zLine, ShellState *p){ ... /* Cancel output redirection, if it is currently set (by .testcase) ** Then read the content of the testcase-out.txt file and compare against ** azArg[1]. If there are differences, report an error and exit. */ if( c=='c' && n>=3 && strncmp(azArg[0], "check", n)==0 ){ char *zRes = 0; output_reset(p); if( nArg!=2 ){ raw_printf(stderr, "Usage: .check GLOB-PATTERN\n"); rc = 2; }else if( (zRes = readFile("testcase-out.txt", 0))==0 ){ raw_printf(stderr, "Error: cannot read 'testcase-out.txt'\n"); rc = 2; }else if( testcase_glob(azArg[1],zRes)==0 ){ utf8_printf(stderr, "testcase-%s FAILED\n Expected: [%s]\n Got: [%s]\n", p->zTestcase, azArg[1], zRes); rc = 2; < change to 1 }else{ utf8_printf(stdout, "testcase-%s ok\n", p->zTestcase); p->nCheck++; } sqlite3_free(zRes); }else ... meta_command_exit: if( p->outCount ){ p->outCount--; if( p->outCount==0 ) output_reset(p); } return rc; } ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] xRowid and read only virtual tables....
On 10/16/17, dave wrote: > Hi, I am building a system which involves a number of virtual table > implementations. They are all read-only, but will be involved in a bunch of > joins amongst themselves. My question is this: > > the documentation > http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid > seems (to my reading) to be always required to be implemented. But does it > really? Is it ever used for read-only tables? I have never seen it > invoked, and I have been blithely ignoring implementing it, but I wonder if > there is a case where it would be invoked for a read-only query and so I am > tempting fate. I don't think xRowid is ever called if you create a WITHOUT ROWID virtual table (https://sqlite.org//vtab.html#worid). But, just to be safe, I think I would include a stub function that always returned 0. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using .testcase and .check in continuous integration test
Hi Dominique, That would be an other way, but what I'm looking for is using the Command Line Shell ".testcase" and ".check" method. Create an example test file: echo ".testcase 100 SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1); .check 1||1 .testcase 110 SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1); .check 1|5|1" > test-script.sql Execute the test: sqlite3 < test-script.sql Bail will not work for that. Kind regards, Lodewijk Van: "Dominique Devienne" Aan: "sqlite-users" Verzonden: Maandag 16 oktober 2017 13:49:18 Onderwerp: Re: [sqlite] Using .testcase and .check in continuous integration test On Mon, Oct 16, 2017 at 12:32 PM, Lodewijk Duymaer van Twist < lodew...@adesys.nl> wrote: > I would like use .testcase and .check in our GitLab Continuous Integration > test. > > GitLab pipelines will check process return code for success or fail. > > Consider a simple test: > lodewijk@DebianDev:~$ sqlite3 database.db3 < test.sql > testcase-100 ok > testcase-110 ok > lodewijk@DebianDev:~$ echo $? > 0 > > Now if I would have a failure the return value of the sqlite3 process will > also be 0: > lodewijk@DebianDev:~$ sqlite3 octalarm.db3 < test/test-languages.sql > testcase-100 FAILED > Expected: [66] > Got: [67 > ] > lodewijk@DebianDev:~$ echo $? > 0 > > Is there a nice elegant way of making my CI stop on a failure? > C:\Users\ddevienne>sqlite3 -bail bad.db "create table foo(id)" && echo OK OK C:\Users\ddevienne>sqlite3 -bail bad.db "create table bar" && echo OK Error: near "bar": syntax error C:\Users\ddevienne> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance issue for version 3.9.2 compared with 3.16.2
In order to profile sqlite 3.9.2 and sqlite 3.16.2, I run speedtest1.c on my mobile phone. It is weird to find that 3.9.2 is better than 3.16.2, especially in the following case: “270, 1 DELETEs, numeric BETWEEN, indexed" 3.9.2 uses 0.7s while 3.16.2 uses 1.8s; My relevant compilation options are shown below: -DSQLITE_THREADSAFE=1 -DSQLITE_DEFAULT_AUTOVACUUM=1 -DSQLITE_DEFAULT_PAGE_SIZE=4096 -DSQLITE_DEFAULT_JOURNAL_SIZE_LIMIT=1048576 Further more, 3.16.2 performs better when I decrease the delete times from 1 to 1000. So is it expected? If not, then what might be the possible reason? Thanks for your help. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] current_timestamp locale
Yes, I understand that beyond integer, float, and chars, there are no further "types", but I was just questioning whether current_timestamp formats what it stores in a specific manor, or, if it has some dealings with the locale of the system its executed on. You've answered the question. Thanks. :] On Tue, Oct 17, 2017 at 2:34 AM, Hick Gunter wrote: > There is no "DATE" type in SQLite. The current_timestamp is a TEXT value > equivalent to datetime('now') which returns an ISO formatted datetime > string in UTC (-MM-DD HH:MM:SS). > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Stephen Chrzanowski > Gesendet: Dienstag, 17. Oktober 2017 07:59 > An: General Discussion of SQLite Database sqlite.org> > Betreff: [EXTERNAL] [sqlite] current_timestamp locale > > Does SQLite go by users locale to insert date/time information into a row, > or is it a very specific format when using current_timestamp as a default > value? > > I don't want to go start monkeying with my system settings to find out, > so, relying on those with experience. > > Thanks > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > Gunter Hick | Software Engineer | Scientific Games International GmbH | > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 > 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users