[sqlite] urifuncs.c access violation & crash
With the urifuncs extension enabled, the each of following SQL queries causes an access violation & crash: select sqlite3_filename_database(''); select sqlite3_filename_journal(''); select sqlite3_filename_wal(''); Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] zipfile.c memory leak
Checkin f5ee3042 introduces a memory leak in zipfile.c. It is still present on trunk, tested with 0b1dbd60. Cause: zipfileDeflate() calls deflateInit2() twice and allocates zlib stream memory twice in certain situations. Also, deflateEnd(&str); may not be called if aOut is not allocated after an out of memory situation. A test case to reproduce the memory leak follows below. Possible fix: static int zipfileDeflate( const u8 *aIn, int nIn, /* Input */ u8 **ppOut, int *pnOut, /* Output */ char **pzErr/* OUT: Error message */ ){ int rc = SQLITE_OK; sqlite3_int64 nAlloc; z_stream str; u8 *aOut; memset(&str, 0, sizeof(str)); str.next_in = (Bytef*)aIn; str.avail_in = nIn; deflateInit2(&str, 9, Z_DEFLATED, -15, 8, Z_DEFAULT_STRATEGY); nAlloc = deflateBound(&str, nIn); aOut = (u8*)sqlite3_malloc64(nAlloc); if( aOut==0 ){ rc = SQLITE_NOMEM; }else{ int res; str.next_out = aOut; str.avail_out = nAlloc; /* Remove: deflateInit2(&str, 9, Z_DEFLATED, -15, 8, Z_DEFAULT_STRATEGY); */ res = deflate(&str, Z_FINISH); if( res==Z_STREAM_END ){ *ppOut = aOut; *pnOut = (int)str.total_out; }else{ sqlite3_free(aOut); *pzErr = sqlite3_mprintf("zipfile: deflate() error"); rc = SQLITE_ERROR; } /* Move below: deflateEnd(&str); */ } deflateEnd(&str); /* Move from above. */ return rc; } Ralf -- #include #include "sqlite3.h" static void check(int r, int e) { if (r != e) { printf ("ERROR %d, expected %d\n", e, r); } } static int callback (void *user, int nCol, char **r, char **c) { int i; for (i = 0; i < nCol; i++) { printf("%s ", r[i]); } printf("\n"); return 0; } extern int sqlite3_zipfile_init(sqlite3*,char**,const sqlite3_api_routines*); int main(void) { sqlite3 *db, *dbRbu; int rc; remove ("test.db"); remove ("test.zip"); check(SQLITE_OK, sqlite3_open_v2 ("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); check(SQLITE_OK, sqlite3_zipfile_init(db, NULL, NULL)); check(SQLITE_OK, sqlite3_exec(db, "CREATE VIRTUAL TABLE temp.zz USING zipfile('test.zip');", callback, NULL, NULL)); check(SQLITE_OK, sqlite3_exec(db, "INSERT INTO zz(name, mode, mtime, data) VALUES('h.txt'," \ " '-rw-r--r--', 14, 'aabb'" \ ");", callback, NULL, NULL)); sqlite3_close(db); printf("Done - Press ENTER to exit.\n"); getchar(); return 0; } ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RBU fails as of checkin f84a1539 - fixed
On 22.12.2019 17:23, Keith Medcalf wrote: I get: RBU error: near ")": syntax error ERROR 1, expected 101 Done - Press ENTER to exit. with the current trunk ... Thanks for following up on this. I spotted a typo in the test code. Corrected version below. With that, Dan's fix works for me on trunk, currently at 0b1dbd60f5. Ralf -- #include #include "sqlite3.h" #include "sqlite3rbu.h" static void check(int r, int e) { if (r != e) { printf ("ERROR %d, expected %d\n", e, r); } } static int callback (void *user, int nCol, char **r, char **c) { int i; for (i = 0; i < nCol; i++) { printf("%s ", r[i]); } printf("\n"); return 0; } static int runrbu(char *zTarget, char *zRbu) { sqlite3rbu* rbu; int rc; char* zError; rbu = sqlite3rbu_open (zTarget, zRbu, NULL); do { rc = sqlite3rbu_step(rbu); } while (rc == SQLITE_OK); rc = sqlite3rbu_close(rbu, &zError); if (zError) { printf("RBU error: %s\n", zError); sqlite3_free(zError); } return rc; } int main(void) { sqlite3 *db, *dbRbu; int rc; remove ("test.db"); check(SQLITE_OK, sqlite3_open_v2 ("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); check(SQLITE_OK, sqlite3_exec(db, "CREATE TABLE t1(a, b, c PRIMARY KEY);" \ "CREATE INDEX i1 ON t1(a, null, b+1);" \ "CREATE INDEX i2 ON t1(a+1, b+1, c+1);" \ "INSERT INTO t1 VALUES(1, 2, 3);" \ "INSERT INTO t1 VALUES(4, 5, 6);" \ "INSERT INTO t1 VALUES(7, 8, 9);" \ "INSERT INTO t1 VALUES(10, 11, 12);" , callback, NULL, NULL)); sqlite3_close(db); remove ("rbu.db"); check(SQLITE_OK, sqlite3_open_v2 ("rbu.db", &dbRbu, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); check(SQLITE_OK, sqlite3_exec(dbRbu, "CREATE TABLE data_t1(a, b, c, rbu_control);" \ "INSERT INTO data_t1 VALUES(13, 14, 15, 0);" \ "INSERT INTO data_t1 VALUES(NULL, NULL, 6, 1);" \ "INSERT INTO data_t1 VALUES(NULL, 'three', 3, '.x.');", callback, NULL, NULL)); sqlite3_close(dbRbu); check(SQLITE_DONE, runrbu("test.db", "rbu.db")); printf("Done - Press ENTER to exit.\n"); getchar(); return 0; } ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] RBU fails as of checkin f84a1539 - fixed
Replying to myself just to confirm that https://www.sqlite.org/src/info/0b9d8a1202c4220f fixes the problem. Thank you, Dan! Ralf On 20.12.2019 17:48, Ralf Junker wrote: As of Fossil checkin f84a1539, the RBU code in the following C example is no longer executed to completion. Instead, an error message is generated and the result database is not correctly written. The code works fine with Fossil checkin 28091a48. It generates no error messages and produces the expected result database. The problem is still present on trunk, checkin 289158aa at the time of this writing. Could anyone reproduce my findings? Many thanks, Ralf -- #include #include "sqlite3.h" #include "sqlite3rbu.h" static void check(int r, int e) { if (r != e) { printf ("ERROR %d, expected %d\n", e, r); } } static int callback (void *user, int nCol, char **r, char **c) { int i; for (i = 0; i < nCol; i++) { printf("%s ", r[i]); } printf("\n"); return 0; } static int runrbu(char *zTarget, char *zRbu) { sqlite3rbu* rbu; int rc; char* zError; rbu = sqlite3rbu_open (zTarget, zRbu, NULL); do { rc = sqlite3rbu_step(rbu); } while (rc == SQLITE_OK); rc = sqlite3rbu_close(rbu, &zError); if (zError) { printf("RBU error: %s\n", zError); sqlite3_free(zError); } return rc; } int main(void) { sqlite3 *db, *dbRbu; int rc; remove ("test.db"); check(SQLITE_OK, sqlite3_open_v2 ("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); check(SQLITE_OK, sqlite3_exec(db, "CREATE TABLE t1(a, b, c PRIMARY KEY);" \ "CREATE INDEX i1 ON t1(a, null, b+1);" \ "CREATE INDEX i2 ON t1(a+1, b+1, c+1);" \ "INSERT INTO t1 VALUES(1, 2, 3);" \ "INSERT INTO t1 VALUES(4, 5, 6);" \ "INSERT INTO t1 VALUES(7, 8, 9);" \ "INSERT INTO t1 VALUES(10, 11, 12);" , callback, NULL, NULL)); sqlite3_close(db); remove ("rbu.db"); check(SQLITE_OK, sqlite3_open_v2 ("rbu.db", &dbRbu, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); check(SQLITE_OK, sqlite3_exec(db, "CREATE TABLE data_t1(a, b, c, rbu_control);" \ "INSERT INTO data_t1 VALUES(13, 14, 15, 0);" \ "INSERT INTO data_t1 VALUES(NULL, NULL, 6, 1);" \ "INSERT INTO data_t1 VALUES(NULL, 'three', 3, '.x.');", callback, NULL, NULL)); sqlite3_close(dbRbu); check(SQLITE_DONE, runrbu("test.db", "rbu.db")); printf("Done - Press ENTER to exit.\n"); getchar(); return 0; } ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] RBU fails as of checkin f84a1539
As of Fossil checkin f84a1539, the RBU code in the following C example is no longer executed to completion. Instead, an error message is generated and the result database is not correctly written. The code works fine with Fossil checkin 28091a48. It generates no error messages and produces the expected result database. The problem is still present on trunk, checkin 289158aa at the time of this writing. Could anyone reproduce my findings? Many thanks, Ralf -- #include #include "sqlite3.h" #include "sqlite3rbu.h" static void check(int r, int e) { if (r != e) { printf ("ERROR %d, expected %d\n", e, r); } } static int callback (void *user, int nCol, char **r, char **c) { int i; for (i = 0; i < nCol; i++) { printf("%s ", r[i]); } printf("\n"); return 0; } static int runrbu(char *zTarget, char *zRbu) { sqlite3rbu* rbu; int rc; char* zError; rbu = sqlite3rbu_open (zTarget, zRbu, NULL); do { rc = sqlite3rbu_step(rbu); } while (rc == SQLITE_OK); rc = sqlite3rbu_close(rbu, &zError); if (zError) { printf("RBU error: %s\n", zError); sqlite3_free(zError); } return rc; } int main(void) { sqlite3 *db, *dbRbu; int rc; remove ("test.db"); check(SQLITE_OK, sqlite3_open_v2 ("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); check(SQLITE_OK, sqlite3_exec(db, "CREATE TABLE t1(a, b, c PRIMARY KEY);" \ "CREATE INDEX i1 ON t1(a, null, b+1);" \ "CREATE INDEX i2 ON t1(a+1, b+1, c+1);" \ "INSERT INTO t1 VALUES(1, 2, 3);" \ "INSERT INTO t1 VALUES(4, 5, 6);" \ "INSERT INTO t1 VALUES(7, 8, 9);" \ "INSERT INTO t1 VALUES(10, 11, 12);" , callback, NULL, NULL)); sqlite3_close(db); remove ("rbu.db"); check(SQLITE_OK, sqlite3_open_v2 ("rbu.db", &dbRbu, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); check(SQLITE_OK, sqlite3_exec(db, "CREATE TABLE data_t1(a, b, c, rbu_control);" \ "INSERT INTO data_t1 VALUES(13, 14, 15, 0);" \ "INSERT INTO data_t1 VALUES(NULL, NULL, 6, 1);" \ "INSERT INTO data_t1 VALUES(NULL, 'three', 3, '.x.');", callback, NULL, NULL)); sqlite3_close(dbRbu); check(SQLITE_DONE, runrbu("test.db", "rbu.db")); printf("Done - Press ENTER to exit.\n"); getchar(); return 0; } ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fossildelta.c memory leak
I experience a memory leak in fossildelta.c using the delta_parse() table-valued function. Given this schema: CREATE TABLE t (x, y, d); INSERT INTO t VALUES (' + X'112233445566778899AABBCCDDEEFF', X'112233445566778899AABBCCDDEE11', Null);' + UPDATE t SET d = delta_create(x, y); This call triggers the leak: SELECT op, a1 FROM delta_parse((SELECT d FROM t LIMIT 1)); It seems that the memory allocated here https://www.sqlite.org/src/artifact/910510968a30ab77?ln=979 is never freed. Without further testing, the leak seems to be fixed by adding sqlite3_free(pCur->aDelta); to deltaparsevtabClose(): https://www.sqlite.org/src/artifact/910510968a30ab77?ln=850-854 Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] blobio.c error message typo
This readblob() error message "BLOB write failed" is misleading: https://www.sqlite.org/src/artifact?ln=79&name=085bbfa57ea58bb1 To me, "BLOB read failed" would make more sense in the readblob() context. Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RBU conflicts with SQLITE_UNTESTABLE
On 8/25/2018 4:53 PM, Richard Hipp wrote: There are lots of similar situations, where omitting features from SQLite will break extensions that depend on those features. For example SQLITE_OMIT_VIRTUALTABLE will break FTS3, FTS4, FTS5, and RTREE, all of which depend on virtual tables. Dependencies are a given. However, I did not expect that SQLITE_UNTESTABLE disables non-testing functionality, given its name and description (https://www.sqlite.org/compile.html#untestable). We are curious to know, though, what you are doing with RBU? That's a obscure and special purpose extension that we did for a single client. Are you finding it useful for something? So far I have no use case for RBU other than experimentation. Its features (efficiency, background and incremental operation) excited me to try it out. I imagine that more than your single client are using it for database updates. Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] RBU conflicts with SQLITE_UNTESTABLE
I am aware that the use of SQLITE_UNTESTABLE is discouraged. Still I want to point developers to the fact that SQLITE_UNTESTABLE breaks RBU. In particular, RBU relies on SQLITE_TESTCTRL_IMPOSTER to be fully working. With SQLITE_UNTESTABLE defined, this is not the case. RBU functions return errors not related to the problem. The target database is not properly updated. The C code below demonstrates this. It is based on rbusplit.test (https://www.sqlite.org/src/artifact/69271c790732b28b). To see the problem, compile with the C preprocessor directive SQLITE_UNTESTABLE=1 #defined. Tested with MS Visual Studio 2017. Ralf -- #include #include #include "sqlite3.h" #include "sqlite3rbu.h" sqlite3 *db; static void check(int r, int e) { if (r != e) { printf("ERROR %d %s\n", e, sqlite3_errmsg(db)); } } static int callback(void *user, int nCol, char **r, char **c) { int i; for (i = 0; i < nCol; i++) { printf("%s ", r[i]); } printf("\n"); return 0; } #define rbu_db"rbu.db" #define target_db "target.db" int main(void) { int r; sqlite3rbu *rbu; char *zError; // Create rbu_db remove(rbu_db); check(SQLITE_OK, sqlite3_open_v2(rbu_db, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); check(SQLITE_OK, sqlite3_exec(db, "BEGIN;" \ "CREATE TABLE data0_t1(a, b, c, rbu_control);" \ "CREATE TABLE data1_t1(a, b, c, rbu_control);" \ "CREATE TABLE data2_t1(a, b, c, rbu_control);" \ "CREATE TABLE data3_t1(a, b, c, rbu_control);" \ "CREATE TABLE data_t2(a, b, c, rbu_control);" \ "INSERT INTO data0_t1 VALUES(1, 1, 1, 0);" \ "INSERT INTO data0_t1 VALUES(2, 2, 2, 0);" \ "INSERT INTO data0_t1 VALUES(3, 3, 3, 0);" \ "INSERT INTO data0_t1 VALUES(4, 4, 4, 0);" \ "INSERT INTO data1_t1 VALUES(5, 5, 5, 0);" \ "INSERT INTO data1_t1 VALUES(6, 6, 6, 0);" \ "INSERT INTO data1_t1 VALUES(7, 7, 7, 0);" \ "INSERT INTO data1_t1 VALUES(8, 8, 8, 0);" \ "INSERT INTO data3_t1 VALUES(9, 9, 9, 0);" \ "INSERT INTO data_t2 VALUES(1, 1, 1, 0);" \ "INSERT INTO data_t2 VALUES(2, 2, 2, 0);" \ "INSERT INTO data_t2 VALUES(3, 3, 3, 0);" \ "INSERT INTO data_t2 VALUES(4, 4, 4, 0);" \ "INSERT INTO data_t2 VALUES(5, 5, 5, 0);" \ "INSERT INTO data_t2 VALUES(6, 6, 6, 0);" \ "INSERT INTO data_t2 VALUES(7, 7, 7, 0);" \ "INSERT INTO data_t2 VALUES(8, 8, 8, 0);" \ "INSERT INTO data_t2 VALUES(9, 9, 9, 0);" \ "COMMIT;", callback, NULL, NULL)); check(SQLITE_OK, sqlite3_close(db)); // Create target.db remove(target_db); check(SQLITE_OK, sqlite3_open_v2(target_db, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); check(SQLITE_OK, sqlite3_exec(db, "CREATE TABLE t1(a PRIMARY KEY, b, c);" "CREATE TABLE t2(a PRIMARY KEY, b, c);" \ "CREATE INDEX t1c ON t1(c);", callback, NULL, NULL)); check(SQLITE_OK, sqlite3_close(db)); // Apply RBU. rbu = sqlite3rbu_open(target_db, rbu_db, NULL); do r = sqlite3rbu_step(rbu); while (r == SQLITE_OK); check(SQLITE_DONE, r); r = sqlite3rbu_close(rbu, &zError); check(SQLITE_DONE, r); if (zError) { printf("%s\n", zError); sqlite3_free(zError); } printf("Done - Press ENTER to exit."); _getch(); return 0; } ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory leak in csv.c virtual table module
This SQL causes a memory leak in the csv.c virtual table module: CREATE VIRTUAL TABLE if not exists t1 USING csv(filename='FileDoesNotExists.csv'); Cause is that the zIn buffer which is allocated here: http://localhost:8081/artifact?name=1a009b93650732e2&ln=128 is only freed in csv_reader_reset() if the file was opened successfully and the file handle is assigned: http://localhost:8081/artifact?name=1a009b93650732e2&ln=102-106 Hence no file open, no file handle, no buffer freed, memory leak. Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] printf() problem padding multi-byte UTF-8 code points
On 18.02.2018 00:36, Richard Hipp wrote: So I'm not sure whether or not this is something that ought to be "fixed". I want to send a big Thank You! for your efforts to enhance the printf() string formatter: http://www.sqlite.org/src/info/c883c4d33f4cd722 I saw the check-in just now as I am "catching up" from a flu. Feels much better now :-) Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] printf() problem padding multi-byte UTF-8 code points
On 19.02.2018 09:50, Rowan Worth wrote: What is your expected answer for: select length(printf ('%4s', 'です')) 'です' are 2 codepoints according to http://www.fontspace.com/unicode/analyzer/?q=%E3%81%A7%E3%81%99 The requested overall width is 4, so I would expect expect two added spaces and a total length of 4. Ralf PS: SQLite3 returns 2, which is less than the requested width. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] printf() problem padding multi-byte UTF-8 code points
On 18.02.2018 00:36, Richard Hipp wrote: The current behavior of the printf() function in SQLite, goofy though it may be, exactly mirrors the behavior of the printf() C function in the standard library in this regard. SQLite3 is not C. SQLite3 text storage is always Unicode. Thus SQL text processing functions should work on Unicode. The current implementation of the SQLite3 SQL printf() can not reliably be used for string padding. And there is no simple alternative, AFAICS. PostgreSQL returns 4 in all cases: select length(format ('%4s', 'abc')), length(format ('%4s', 'äöü')), length(format ('%-4s', 'abc')), length(format ('%-4s', 'äöü')) MySQL has lpad() and rpad() to achieve the same and also returns 4 in all cases: select length(lpad ('abc', 4, ' ')), length(lpad ('äöü', 4, ' ')), length(rpad ('abc', 4, ' ')), length(rpad ('äöü', 4, ' ')) I strongly believe that SQLite3 should follow suit. Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] printf() problem padding multi-byte UTF-8 code points
Example SQL: select length(printf ('%4s', 'abc')), length(printf ('%4s', 'äöü')), length(printf ('%-4s', 'abc')), length(printf ('%-4s', 'äöü')) Output is 4, 3, 4, 3. Padding seems to take into account UTF-8 bytes instead of UTF-8 code points. Should padding not work on code points and output 4 in all cases as requested? Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);
On 23.01.2018 15:31, Richard Hipp wrote: I'm still unable to reproduce this problem. sqlite3.exe from this ZIP: https://www.sqlite.org/2018/sqlite-tools-win32-x86-322.zip Running on Windows 7: SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT CAST ('9223372036854775807 ' AS NUMERIC); 9.22337203685478e+18 sqlite> SELECT CAST ('9223372036854775807' AS NUMERIC); 9223372036854775807 Notice the trailing white space which makes the difference. Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] zipfile.c: crc32 not calculated for uncompressed files - extraction fails
zipfile.c fails to calculate the CRC32 value if the compression method is explicitly set to 0. Example SQL: INSERT INTO zz(name, mode, mtime, data, method) VALUES('f.txt', '-rw-r--r--', 10, 'abcde', 0); As a result, a CRC32 value of 0 is written to the file. Some archive managers handle CRC32 discrepancies as errors, i.e. http://www.7-zip.org. Extra braces around the highlighted if block ensure that CRC32 is calculated regardless of compression: http://www.sqlite.org/src/artifact?name=cc12e900e12eec23&ln=1340-1349 For testing purposes, it might be helpful to add a CRC32 column to the zipfile virtual table. Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT result different after ANALYZE
On 21.11.2017 15:36, Richard Hipp wrote: I'll be working on some other solution for you. Many thanks, but this is not necessary. I can rebuild from Fossil. Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT result different after ANALYZE
I am presenting a scenario where a SELECT produces a different result after running ANALYZE. To reproduce, download this database file (5.6MB, SHA1 12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now): https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html This SQL returns a single result row with a value of 1: SELECT DISTINCT t2.a FROM t1 INNER JOIN t2 ON t1.t2_id = t2.id WHERE t1.t2_id <> -1; Then run ANALYZE and run the above select again. This time I receive no result. Assuming that SQL SELECTs should always return the same results regardless of optimization, I assume that this might be a bug in SQLite. Tested with the SQLite 3.21.0 CLI on Windows. Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Closing parenthesis missing from doc comment
I believe, a closing parenthesis should be before the final comma in this line: http://www.sqlite.org/src/artifact/0e2603c23f0747c5?ln=4202 Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] json_valid() crash on Windows - was: json() number value parsing
SQLite on Windows crashes when running this test: n_structure_10_opening_arrays.json The crash results from a stack overflow because json_valid() is implemented using a recursive parser. All versions of json1.c up to current trunk are affected. Here is a small SQL snippet that also triggers the overflow: SELECT json_valid(json) FROM ( WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x < 4) -- Eventually increase this! SELECT group_concat('[', '') AS json FROM cnt); Depending on compiler and available stack, you may need to increase 4 to a larger number to reproduce the problem. sqlite3.exe 3.18.0 on Windows 7 ran out of stack at around 35000. The problem might escape Linux testing because it usually has a much larger default stack size than Windows. One solution would be to limit the parser's nesting depth as RFC 7159 allows: https://tools.ietf.org/html/rfc7159#section-9 Ralf On 10.04.2017 13:54, Richard Hipp wrote: > SQLite returns true from json_valid() for the following cases which > should allegedly be false: > >n_multidigit_number_then_00.json >n_string_unescaped_newline.json >n_string_unescaped_tab.json ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] json_patch(): null not removed with 2nd beta for SQLite 3.18.0.
On 24.03.2017 00:50, Richard Hipp wrote: This second beta adds a new SQL function: json_patch(). See https://www.sqlite.org/draft/json1.html#jpatch for details. json_patch() fails to produce the expected result for the last example in https://tools.ietf.org/html/rfc7396#appendix-A SELECT json_patch('{}','{"a":{"bb":{"ccc":null}}}'); should return {"a":{"bb":{}}} but instead returns {"a":{"bb":{"ccc":null}}} As far as I read the document, the "ccc" value should be removed according to this rule: if Value is null: if Name exists in Target: remove the Name/Value pair from Target Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_BUILTIN_TEST renders fix for ticket [da784137] useless
On 07.12.2016 14:21, Richard Hipp wrote: Side note: I notice that the SQLite binaries (Windows, at least) are not compiled with SQLITE_OMIT_BUILTIN_TEST and not affected by the problem. Is there a reason to omit SQLITE_OMIT_BUILTIN_TEST from the builds, as it adds at least some overhead? > The reason to omit SQLITE_OMIT_BUILTIN_TEST is that it renders SQLite untestable. Because of SQLite's well known pre-release tests, I assumed that binaries on sqlite.org would omit test code for best performance. After all, they are meant for production, not testing, are they not? The SQLITE_OMIT_BUILTIN_TEST option is untested and unsupported. This was not clear to me from the docs: http://www.sqlite.org/compile.html#omit_builtin_test There has been a proposal circulating among the developers to disable SQLITE_OMIT_BUILTIN_TEST. In other words, make SQLITE_OMIT_BUILTIN_TEST a no-op. Your report adds fresh impetus to that proposal. I welcome SQLITE_OMIT_BUILTIN_TEST for smaller binaries and better performance. Up to now it has not caused me any problems. So many thanks for the quick fix: http://www.sqlite.org/src/info/afab166313e0b8ad Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OMIT_BUILTIN_TEST renders fix for ticket [da784137] useless
Alarming side effect of SQLITE_OMIT_BUILTIN_TEST: It renders the fix [005d5b87] for ticket [da784137] useless. Ticket: http://www.sqlite.org/src/info/da7841375186386c Fix, trunk: http://www.sqlite.org/src/info/005d5b870625d175 Fix, 3.15.2: http://www.sqlite.org/src/info/27438fb43db4eae9 Reason is that select.c#flattenSubquery() exits early http://www.sqlite.org/src/artifact/672b1af237ad2571?ln=3386 based on the OptimizationDisabled() macro http://www.sqlite.org/src/artifact/c471d791b10c0f21?ln=1464-1470 However, with SQLITE_OMIT_BUILTIN_TEST #defined, OptimizationDisabled() always results in 1 and never evaluates db->dbOptFlags. As a consequence, selects are always flattened. This causes test http://www.sqlite.org/src/artifact/3068f508753af698?ln=209-217 to fail if SQLite is compiled with SQLITE_OMIT_BUILTIN_TEST even with fix [005d5b87] in place. Side note: I notice that the SQLite binaries (Windows, at least) are not compiled with SQLITE_OMIT_BUILTIN_TEST and not affected by the problem. Is there a reason to omit SQLITE_OMIT_BUILTIN_TEST from the builds, as it adds at least some overhead? Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VACUUM causes Out of memory error
I am the author of the DISQLite3 Delphi wrapper. Assurance to all DISQLite3 users: The leak is not DISQLite3 specific. DISQLite3 5.15.0 behaves exactly like the official SQLite 3.15.0 release. The problem can reliably be reproduced using the official sqlite3.exe 3.15.0 binary on Win32. Bisection shows that the error entered the SQLite code base here, which is the first version which runs out of memory during VACUUM: http://www.sqlite.org/src/info/29d63059b4d2bb61 I am investigating further. Ralf On 01.11.2016 14:11, Hennekens, Stephan wrote: Since last version of sqlite (3.15.0) VACUUM causes an 'Out of memory' error, at least on my bigger databases (> 1 Gb). I came across this error when I installed the latest DISQLite3 version (http://www.yunqa.de/delphi/products/sqlite3/index). I was also able to reproduce the error when using Sqlite3.dll directly via a Delphi wrapper class. In case needed you can download a compressed 2 GB file from here: http://www.synbiosys.alterra.nl/downloads/lvd.7z ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Session documentation: Invalid link for sqlite3_changegroup
The invalid link is in red font. Reference: https://www.sqlite.org/sessionintro.html#extended_functionality Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] No messages from this list since outage
Since the previous mailing list outage, I was able to post to this list http://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg98671.html http://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg98672.html but I have not received any messages since. I have logged into the web interface and reconfirmed my details - but still received nothing. I wonder if this affects just me or other users as well? Could anyone please look into this? Thanks, Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] #include - angle brackets instead of quotation marks
The files csv.c and vfsstat.c use #include with angle brackets http://www.sqlite.org/src/artifact/816a3715356e4210?ln=42 http://www.sqlite.org/src/artifact/bf10ef0bc51e1ad6?ln=17 where quotation marks are otherwise used throughout SQLite, i.e. http://www.sqlite.org/src/artifact/16c1b2114eae8804?ln=17 Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] csv.c - Unicode filename failure (Windows, at least)
At current, the csv extension fails to open Unicode file names like CREATE VIRTUAL TABLE t1 USING csv( filename = "äöü.csv"); because internally the filename is passed to fopen() as UTF-8: http://www.sqlite.org/src/artifact/816a3715356e4210?ln=131 However, fopen() does not honor UTF-8, at least not on Windows: https://msdn.microsoft.com/en-us/library/yeby3zcb.aspx Could it be possible to replace fopen() and related functions with the respective VFS functions instead? This would also remove dependency on stdio, which is otherwise carefully avoided in the SQLite library (except for testing etc.). Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_scrub_backup() -> database disk image is malformed
On 26.07.2016 12:53, Dan Kennedy wrote: The corrupt database thing is unexpected, of course. It might be fixed here: http://sqlite.org/src/info/483994a54dee3c7a Please let us know if you get the chance to try the fix above with your databases. I confirm that the fix solves the problem reported. Unlike VACUUM, sqlite3_scrub_backup() should not usually shrink the database. scrub_backup() simply creates a copy of the original database with any unused parts of the file (i.e. free pages or empty space within b-tree pages) zeroed out. Whereas VACUUM rebuilds the db from the ground up so that it is as small as possible. Understood. Thanks for the info! Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_scrub_backup() -> database disk image is malformed
I use the following C code to test sqlite3_scrub_backup() [1]. Unfortunately, it results in a malformed database disk image. Also, I am surprised that VACUUMing the original database produces a smaller file than sqlite3_scrub_backup(). Should they not be the same size? Is this a problem with scrub.c or with my code? Ralf [1] http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/scrub.c --- #include #include "sqlite3.h" sqlite3 *db; static void check(int r, int e) { if (r != e) { printf ("ERROR %d %s\n", e, sqlite3_errmsg(db)); } } static int callback (void *user, int nCol, char **r, char **c) { int i; for (i = 0; i < nCol; i++) { printf("%s ", r[i]); } printf("\n"); return 0; } #define Test1Db "test1.db3" #define Test2Db "test2.db3" int main(void) { char *zErrMsg = 0; check(0, remove (Test1Db)); check(SQLITE_OK, sqlite3_open (Test1Db, &db)); check(SQLITE_OK, sqlite3_exec(db, "DROP TABLE IF EXISTS t;" "CREATE TABLE t(a, b, c);" "WITH r(i) AS (" " SELECT 1 UNION ALL SELECT i+1 FROM r WHERE i<1000" ")" "INSERT INTO t (rowid, a, b, c)" " SELECT i,zeroblob(100),zeroblob(100),zeroblob(100) FROM r;" "SELECT count() FROM t;", callback, NULL, NULL)); check(SQLITE_OK, sqlite3_exec(db, "DELETE FROM t WHERE rowid > 500;" "SELECT count() FROM t;", callback, NULL, NULL)); check(SQLITE_OK, sqlite3_close(db)); check(0, remove (Test2Db)); check(SQLITE_OK, sqlite3_scrub_backup( Test1Db, // Source database filename Test2Db, // Destination database filename &zErrMsg ));// Write error message here if (zErrMsg) { printf ("%s", zErrMsg); sqlite3_free(zErrMsg); } /* VACUUM database 1. */ check(SQLITE_OK, sqlite3_open (Test1Db, &db)); check(SQLITE_OK, sqlite3_exec(db, "VACUUM", callback, NULL, NULL)); check(SQLITE_OK, sqlite3_close(db)); /* Integrity-check database 2. */ check(SQLITE_OK, sqlite3_open (Test2Db, &db)); check(SQLITE_OK, sqlite3_exec(db, "PRAGMA integrity_check;" "SELECT count() FROM t;", callback, NULL, NULL)); check(SQLITE_OK, sqlite3_close(db)); return 0; } ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_scrub_backup() not mentioned in 3.14 release log
Absolutely, thanks for the catch! This link might be even better as it always points to the latest version on trunk: https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/scrub.c Ralf On 24.07.2016 15:19, Simon Slavin wrote: By which you mean, of course, http://www.sqlite.org/cgi/src/artifact/ea0903701e3ac02b ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_scrub_backup() not mentioned in 3.14 release log
On 23.07.2016 17:16, Richard Hipp wrote: Look over the changes. Speak up loudly and quickly if you have any issues. http://127.0.0.1:8080/artifact/ea0903701e3ac02b sqlite3_scrub_backub() is not mentioned in the 3.14 draft release log nor documentation. * Will it be part of the next version? * Is it safe to use for production? * It is officially part of SQLite? Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conversion failure
If you are on Windows, you can use SQLiteSpy to correct such wrongly entered ANSI text to Unicode throughout an entire database: http://yunqa.de/delphi/products/sqlitespy/index Open the database and from the menu pick Execute -> Text to Unicode Convertsion ... A dialog opens where you can check and uncheck the tables and columns to convert. Next choose the "Current Database Codepage". This should be the codepage of the wrongly entered text and defaults to your system codepage. If unsure, use an educated guess or try different codepages. Press OK to execute the conversion. For safety, all changes are by default performed within a transaction. The dialog closes when done and can browse the tables to check the changes. Depending on the outcome, at last manually COMMIT or ROLLBACK. Ralf On 23.06.2016 18:16, Hick Gunter wrote: Your data entry device (I guess a PC running a flavor of windows) is generating a certain sequence of bytes when you press ALT+225. This sequence is probably ISO/ANSI encoded instead of UTF-8 encoded. It has nothing to do with sqlite itself. Sqlite will faithfully reproduce whatever byte sequence you gave to it. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Debugging variable left in fts5_index.c?
These 2 lines seem to serve no purpose. Also, they are not indented like the reset of the code. This makes me think they might have been left in from debugging: http://www.sqlite.org/src/artifact/b271b19dd28d3501?ln=3819-3820 Ralf
[sqlite] FTS5 returns "corrupt" plus trailing zero
My implementation of http://www.sqlite.org/src/artifact/400384798349d658?ln=94-96 returns "corrupt" plus a trailing zero, that is 8 characters in total. Maybe this line http://www.sqlite.org/src/artifact/4fdbc0a321e3a1d7?ln=5364 should be corrected to sqlite3Fts5BufferSet(&rc, &s, 7, (const u8*)"corrupt"); so that the number of characters passed matches the length of "corrupt". Ralf
[sqlite] json1.c: isalnum(), isspace(), and isdigit() usage
On 17.09.2015 20:14, Scott Hess wrote: > The problem is that there are LOCALE settings where tolower() does things C > programmers don't expect. I think tr_TR was one case, the handling of 'I' > (Google "tr_tr locale bug" and you'll see lots of people hitting the same > general problem). It isn't a problem of type safety, it's a problem that > the same inputs might have different outputs for certain library functions > when you change environment variables. I don't remember whether there were > specific problems with other ctype functions, or if I just thought it was a > good idea to be careful, once I realized the class of problem. And this check-in therefore misses the point as it does not address this LOCALE problem IMHO: http://www.sqlite.org/src/info/6713e35b8a8c997a Ralf
[sqlite] json1.c: isalnum(), isspace(), and isdigit() usage
ext/misc/json1.c uses the following functions from the C library: isalnum(): http://www.sqlite.org/src/artifact/541004e47235cefc?ln=564 isspace(): http://www.sqlite.org/src/artifact/541004e47235cefc?ln=635 isdigit(): http://www.sqlite.org/src/artifact/541004e47235cefc?ln=829 Existing source code declares these as unsafe for hi-bit-set characters and introduces safe replacement versions independent of locale: src/sqliteInt.h: https://www.sqlite.org/src/artifact/424a2020efc9736c?ln=3092-3094 ext/fts2/fts2.c: https://www.sqlite.org/src/artifact/72c816a9ae448049?ln=336-353 ext/fts3/fts3_tokenizer1.c: https://www.sqlite.org/src/artifact/5c98225a53705e5e?ln=54-56 Shouldn't json1.c avoid them for the same reasons? Ralf
[sqlite] queryplanner.html invalid links
https://www.sqlite.org/queryplanner.html contains the text "The process is illustrated by figure 2 below." where the "figure 2" part links to https://www.sqlite.org/queryplanner.html#fig2 DocSrc: https://www.sqlite.org/docsrc/artifact/ce9ee7160e98d868?ln=100 The link to the image does not work because the document does not contain an anchor or ID named "fig2". It looks like the tcl documentation generator does not process the #fig2 argument passed here: https://www.sqlite.org/docsrc/artifact/ce9ee7160e98d868?ln=108 This also applies to the remaining images in the document. Ralf
[sqlite] FTS5 Porter extra arguments not passed through
As per the documentation, extra arguments to the Porter stemmer are handed on to the underlying tokenizer: http://www.sqlite.org/docsrc/artifact/9eca0ea509ae3e4d?ln=544-546 Example syntax a few lines below: http://www.sqlite.org/docsrc/artifact/9eca0ea509ae3e4d?ln=556 However, the code does not follow the documentation and specifies 0 instead of the argument parameters: http://www.sqlite.org/src/artifact/30f97a8c74683797?ln=540 Ralf
[sqlite] Access Violation in fts5_expr()
This SQL SELECT fts5_expr(); results in an AV here: https://sqlite.org/src/artifact/56dcbcbdc9029dd7?ln=145 Reason is that fts5ExprFunction() does not check for at least one argument to fts5_expr() here: http://sqlite.org/src/artifact/56dcbcbdc9029dd7?ln=1886 Ralf
[sqlite] FTS5 xColumnSize() documentation
From fts5aux.test 2.3 I conclude that the FTS5 xColumnSize() function accepts negative column values and returns the total token size of all columns from the current row: https://www.sqlite.org/src/artifact/8c687c948cc98e9a?ln=64-66 Indeed, this is also in source: https://www.sqlite.org/src/artifact/0de7ba81488d2c50?ln=1672-1677 However, not in the documentation: https://www.sqlite.org/src/artifact/81d1a92fc2b4bd47?ln=66-67 Unless this is a hidden feature, could you add it to the docs? Ralf
[sqlite] sqlite3rbu: void function returns value
Warning: sqlite3rbu.c 3528: void functions may not return a value in function rbuVfsDlClose. http://www.sqlite.org/src/artifact/d37e1ca2d13e439c?ln=3526-3529 Looks like the return value is not needed. Ralf
[sqlite] sqlite3rbu.c: ANSI C incomatible variable delcaration
sqlite3rbu.c intermingles variable declaration and code, which is not with ANSI C here: http://www.sqlite.org/src/artifact/d37e1ca2d13e439c?ln=623-624 Declaring zSql before calling va_start() solves the problem for me: char *zSql; va_start(ap, zFmt); zSql = sqlite3_vmprintf(zFmt, ap); Ralf
[sqlite] sqlite3rbu.c: Replace sprintf() with sqlite3_mprintf()
sqlite3rbu.c uses sprintf(), which SQLite3 uses nowhere else: http://www.sqlite.org/src/artifact/d37e1ca2d13e439c?ln=2643 Could you replace this with sqlite3_mprintf(), just like one function above? http://www.sqlite.org/src/artifact/d37e1ca2d13e439c?ln=2625-2628 Ralf
Re: [sqlite] 64 bits Dll
On 09.05.2014 13:50, Carlos Ferreira wrote: XE6 itself seems to native have access to both... FireDAC only, and outdated by 2 months at the day of release. Delphi XE5 SQLite is still at 3.7.17, almost one year behind. DISQLite3 has always been up to date for years and Delphi versions back to Delphi 4: http://yunqa.de/delphi/doku.php/products/sqlite3/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64 bits Dll
On 09.05.2014 12:36, Carlos Ferreira wrote: I am using a win32 DLL built "sqlite-dll-win32-x86-3071700" . the DLL is from 20-5-2013. I am using it with Delphi, and it works fine for what I need. I need however a 64 bits version of the DLL. SQLite3 for Delphi, both Win32 and Win64, with many extensions and extras here: http://yunqa.de/delphi/doku.php/products/sqlite3/index Enjoy, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] latest sqlite 3 with Delphi 5 professional
On 24.01.2014 10:06, Max Vlasov wrote: BCC 5.5 (freely downloadable) compiles any version of sqlite3 to object files linkable to Delphi 5 and later, the only drawback I noticed is that for memory-intensive operations (memory databases) the performance is twice as worst comparing to the dll on the site (probably VC compiled), but for databases on disk the difference is small since I/O overhead compensate it. Don't know about DISQLite3 , but one of the main performance issues DISQLite3 does _not_ show the performance issues you describe for your BCB 5.5 compiled object files. Quite the opposite: DISQLite3 outperformed sqlite3.dll whenever I tested. You can test yourself with the example projects located in \DISQLite3_Log_Inserts\ and \DISQLite3_20_Million\ sub-folders of the \Demo\ directory. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] latest sqlite 3 with Delphi 5 professional
On 23.01.2014 11:28, dean gwilliam wrote: More specifically...is there the equivalent of that powerbasic include file for D5 i.e. that enables you to access the dll's function calls unchanged? Failing that...anything that will let me work with the latest sqlite 3 dll The meaner and leaner...the better. DISQLite3 meets your requirements: http://yunqa.de/delphi/doku.php/products/sqlite3/index * Delphi 5 support, among a dozen other Delphi compilers. * Compiles directly into applications, no need for sqlite3.dll. * Supports the *complete* SQLite3 API. * Includes FTS and numerous extensions. * UTF-8 string conversion functions provided. * Many Delphi demo projects. * Exhaustive documentation. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OMIT_FLAG_PRAGMAS issue: sqlite3_busy_timeout() called for all flag pragmas
SQLite 3.8.1 compiled with SQLITE_OMIT_FLAG_PRAGMAS #defined calls sqlite3_busy_timeout() for all flag pragmas. Example: PRAGMA legacy_file_format=100; should do nothing but sets sqlite3_busy_timeout(db, 100); The change was introduced here: http://www.sqlite.org/src/artifact/249742bd762770e5e6e5b67cfcb2fa9b1049?ln=1960 Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS
On 10.10.2013 03:53, Richard Hipp wrote: I think that http://www.sqlite.org/src/info/e97d7d3044 fixes this issue. Works well for me. Please correct me if I've missed something. You committed to the "row-size-est" branch. I guess this will be merged into "trunk" for 3.8.1? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS
On 09.10.2013 15:50, Eric Minbiole wrote: With this change, tests pass again: #if sizeof(p->nRow) == sizeof(long long) sqlite3_snprintf(24, zRet, "%lld", p->nRow); #elseif sizeof(p->Row) = sizeof(long) sqlite3_snprintf(24, zRet, "%ld", p->nRow); #else sqlite3_snprintf(24, zRet, "%d", p->nRow); #endif Slightly off-topic, but I didn't think that sizeof() could be used as part of a preprocessor directive? (I.e., that #if sizeof(x) doesn't work as intended, or at least not portably.) This is more portable: #ifdef SQLITE_64BIT_STATS sqlite3_snprintf(24, zRet, "%lld", p->nRow); #else sqlite3_snprintf(24, zRet, "%d", p->nRow); #endif Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS
analyze.c always prints 32-bit variables as 64-bit here: http://www.sqlite.org/src/artifact/d322972af09e3f8debb45f420dfe3ded142b108b?ln=746 http://www.sqlite.org/src/artifact/d322972af09e3f8debb45f420dfe3ded142b108b?ln=792 This can cause wrong sqlite_statX tables which I have experienced compiling for 32-bit. With this change, tests pass again: #if sizeof(p->nRow) == sizeof(long long) sqlite3_snprintf(24, zRet, "%lld", p->nRow); #elseif sizeof(p->Row) = sizeof(long) sqlite3_snprintf(24, zRet, "%ld", p->nRow); #else sqlite3_snprintf(24, zRet, "%d", p->nRow); #endif Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hints for the query planner
On 11.09.2013 16:07, Ryan Johnson wrote: Perhaps you meant "demote" rather than "degrade" ? That would be a better fit (an external action that does not necessarily make the object worse or less useful), and less vague, but it still carries a negative connotation. "demote" sounds fine to me, especially since its antonym "promote" may be used for a function name to raise an expression's rank for the query planner rather than the 2nd argument. The negative connotation of both "degrade" and "demote" does not feel bad for me as a non native English speaker. Both, however, express an action rather than a quality which is more telling to me than "unlikely" or the other adjectives suggested so far. Maybe the function name could be prefixed by "qp_" (for query planner) or similar to clarify their functionality even more: "qp_demote" and "qp_promote"? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hints for the query planner
I suggest a verb to express what the function is actually doing, namely to reduce its argument in rank or degree for the query planner: DEGRADE 1. to reduce in worth, character, etc; disgrace; 2. to reduce in rank, status, or degree; remove from office; 3. to reduce in strength, quality, intensity, etc Source: http://www.collinsdictionary.com/dictionary/english/degrade On 10.09.2013 21:26, Richard Hipp wrote: Please feel free to suggest other names if you think of any. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A few questions about SQLite Encryption Extension
On 19.07.2013 15:27, Sqlite Dog wrote: >> * Statically link SQLite to your Delphi application. My DISQLite3 >> enables you to do just that and has numerous extensions: One is a >> custom encryption algorithm. This is not compatible with SEE, >> but if you like I can replace it with your original SEE code for you. >> >> http://www.yunqa.de/delphi/doku.php/products/sqlite3/index > > Is it a pascal wrapper around SQLite or something bigger? DISQLite3 is the only Delphi product which includes the _complete_ SQLite API, AFAIK. Using register calling conventions and the Delphi memory manager, DISQLite3 surprised many users to perform noticeably faster than other implementations. Features include: * Complete SQLite API. * Supports Win32 and Win64. * Delphi class wrapper. * TDataSet descendant. * Delphi smart linking for smallest possible binaries. * Full Text Search (FTS) with customizable tokenizer, prefix matching, and optional word stemming for 15 languages. * Custom encryption (not SEE compatible). * Async IO Backend. * SQLite virtual table extensions: rtree, spellfix, fuzzer, closure, wholenumber, amatch. * SQLite SQL function extensions: ieee754, nextchar. * Extensive documentation and lots of demo projects. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A few questions about SQLite Encryption Extension
On 19.07.2013 09:21, Sqlite Dog wrote: > Our database manager is developed using Delphi (Pascal). Thus it is > not possible to statically link SQLite library, SQLite.dll is used. > Is there some other way to support SEE in our project? You have two options: * Create your own sqlite.dll and compile SEE into it. * Statically link SQLite to your Delphi application. My DISQLite3 enables you to do just that and has numerous extensions: One is a custom encryption algorithm. This is not compatible with SEE, but if you like I can replace it with your original SEE code for you. http://www.yunqa.de/delphi/doku.php/products/sqlite3/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] False Error 'no such collation sequence'
On 21.06.2013 05:10, Tom Holden wrote: > Ralf Junker ralfjunker at gmx.de Thu Jun 20 18:44:15 EDT 2013 wrote: > > On 19.06.2013 17:18, Tom Holden wrote: > >> I use the SQLiteSpy compilation of SQLite3 which recently upgraded >> from pre SQLite 3.7.8 to pre 4.2.0. > > You must be mistaken. As the author of SQLiteSpy, I can clearly say > that there is no SQLiteSpy version 3.7.8 nor 4.2.0. The current > version is SQLiteSpy 1.9.3 which implements SQLite 3.7.16.1. > > --- Tom replies: The versions are those of SQLite3 or of > DISQLite3 according to your version history for SQLIteSpy at > http://www.yunqa.de/delphi/doku.php/products/sqlitespy/history. I am > not sure what you mean by "pre SQLite 3.7.8" for SQLiteSpy 1.9.1 - > was it compiled from SQLite 3.7.7? Thanks for pointing this out, the SQLiteSpy version history is now corrected. > I agree that the SQLiteSpy 1.9.3 behaviour is the same as that of > sqlite3.exe 3.7.17 with respect to these error messages for my > examples just as there were no error messages for the same examples > with SQLiteSpy 1.9.1 and sqlite3.exe 3.7.5. Good to know. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] False Error 'no such collation sequence'
On 19.06.2013 17:18, Tom Holden wrote: > I use the SQLiteSpy compilation of SQLite3 which recently upgraded > from pre SQLite 3.7.8 to pre 4.2.0. You must be mistaken. As the author of SQLiteSpy, I can clearly say that there is no SQLiteSpy version 3.7.8 nor 4.2.0. The current version is SQLiteSpy 1.9.3 which implements SQLite 3.7.16.1. > I also occasionally use the SQLite3 Windows command-line shell. I am > now encountering an error with the current versions that I did not > with earlier ones – sorry, I cannot be precise at this time as to > when it began. The problem arises with simple SELECTs on a table > which has one or more fields defined with an unavailable collation > sequence. Previously, the only time an error was thrown would be when > such field was ordered or an index was engaged that included it. Now > the error is thrown on a simple SELECT of the field or of the PRIMARY > KEY, even though the latter is not defined with the missing > collation. No error is thrown if the Primary Key is included with > other fields that do not use the missing collation. The collation is > missing because the database is created by proprietary software. > > Example: CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, > Name TEXT COLLATE CUSTOM, RefNumber TEXT, ...) Your SQL is incomplete and does not allow testing SQLiteSpy. This SQL does raise an error "no such collation sequence: CUSTOM": CREATE TABLE SourceTable ( SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE CUSTOM, RefNumber TEXT); > Error not thrown: SELECT RefNumber ... or any combination of fields > other than the Name field, the only one to have the proprietary > collation > > “Error: no such collation sequence: CUSTOM”: SELECT Name FROM ... > SELECT SourceID FROM ... SELECT SourceID, Name FROM ... SELECT Name > and any combination of other fields FROM ... > > Further, even with ORDER BY, prior versions used to tolerate SELECT > Name COLLATE NOCASE ... FROM table ORDER BY Name The current > versions throw the error. > > From my perspective, this is a bug that limits the tools available > to work with a proprietary database to fewer than there used to be – > the command line shell being one rendered incapable. So far I have not been able to verify a single case where SQLiteSpy does no behave exactly as SQLite. If you believe otherwise, please provide working example SQL for testing. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Security concern with new fts3tokenize virtual table
The new fts3tokenize virtual table calls the fts3_tokenizer() SQL function internally to retrieve the pointer to the tokenizer implementation for a FTS table: http://www.sqlite.org/src/artifact/a29f126b9e6c6a6f1021a8f7440bf125e68af1f9?ln=74-100 However, this call is rejected if, for security reasons, the fts3_tokenizer() function is blocked by an authorizer callback: "SECURITY WARNING: If the fts3/4 extension is used in an environment where potentially malicious users may execute arbitrary SQL, they should be prevented from invoking the fts3_tokenizer() function, possibly using the authorization callback." (http://www.sqlite.org/fts3.html#section_8_1). So the problem is that with the authorizer block in place, the fts3tokneize virtual table does not work. One would have to compromise functionality for security. Is there no other way to retrieve the tokenizer besides calling fts3_tokenizer()? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] amatch extension
I am unsure about how the amatch extension works. According to the documentation, I believe that the SQL below should return at least one record, but it does not. No errors are reported either. I compiled the shell executable with the 201304290917 draft amalgamation and today's amatch.c from Fossil trunk. Is there something I am missing? Ralf DROP TABLE IF EXISTS f; DROP TABLE IF EXISTS c; DROP TABLE IF EXISTS v; CREATE TABLE v (w TEXT, l INTEGER); CREATE INDEX v_index ON v(w); INSERT INTO v VALUES ('abc', 0); INSERT INTO v VALUES ('bcd', 0); INSERT INTO v VALUES ('def', 0); CREATE TABLE c(iLang INTEGER, cFrom TEXT, cTo TEXT, Cost INTEGER); INSERT INTO c VALUES(0, '', 'a', 100); INSERT INTO c VALUES(0, 'b', '', 87); INSERT INTO c VALUES(0, 'o', 'oe', 38); INSERT INTO c VALUES(0, 'oe', 'o', 40); INSERT INTO c VALUES(0, '?', '', 97); INSERT INTO c VALUES(0, '', '?', 98); INSERT INTO c VALUES(0, '?', '?', 99); CREATE VIRTUAL TABLE f USING approximate_match( vocabulary_table=v, vocabulary_word=w, vocabulary_language=l, edit_distances=c ); SELECT * FROM f WHERE word MATCH 'abc'; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.7.17 Preview
Current trunk still does not compile with SQLITE_OMIT_WAL #defined. Details here: http://www.mail-archive.com/sqlite-users@sqlite.org/msg76672.html Ralf On 26.04.2013 17:34, Richard Hipp wrote: > Please visit http://www.sqlite.org/draft/releaselog/3_7_17.html to see the > proposed enhancements and changes in SQLite version 3.7.17. Your comments, > criticisms and suggestions are welcomed and encouraged. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] pager.c does not compile with SQLITE_OMIT_WAL
The current SQLite Fossil snapshot does not compile with SQLITE_OMIT_WAL defined. Reason: The Pager->pWal element is compiled out in pager.c here: http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=691-694 but still accessed here: http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=2876 http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=5243 http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=5333 Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Link pragma.html#pragma_user_version invalid
In the list of PRAGMAs in pragma.html the "PRAGMA user_version" documentation link to http://www.sqlite.org/pragma.html#pragma_user_version is invalid. It should point to http://www.sqlite.org/pragma.html#pragma_schema_version instead. This is also present in today's documentation draft. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] AsyncIO fails with current 3.7.15 trunk
On 07.12.2012 00:19, Joe Mistachkin wrote: > Could you please try the changes in: > > http://www.sqlite.org/src/info/c507ca4a8e > > and then let us know if this clears the issue you are seeing? Yes, this clears my issues. Many thanks for the quick fix! Looks like it updates AsyncIO to support SQLITE_IOERR_DELETE_NOENT. I am surprised that this has not already been noticed since SQLITE_IOERR_DELETE_NOENT was introduced to os_unix.c in 2012-11-09. It seems that AsyncIO is indeed rarely used nowadays ... Two more things I noticed: 1. ext/async/README.txt spells "superceded" which is apparently a common misspelling of "superseded". http://en.wiktionary.org/wiki/supercede 2. ext/async/sqlite3async.h defines these two prototypes without void which causes my compiler to issue warnings: void sqlite3async_shutdown(); void sqlite3async_run(); This change eliminates the warnings: void sqlite3async_shutdown(void); void sqlite3async_run(void); Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] AsyncIO fails with current 3.7.15 trunk
I know that AsyncIO is now superseded by WAL [1], but according to Richard it should still work for existing applications [2]. However, I experience the opposite. The small C application below runs well when linked against SQLite 3.7.14. With SQLite 3.7.15, it mysteriously fails. I see two types of outcomes: 1. Error SQLITE_IOERR, followed by SQLITE_ERROR. Nothing is written to the target database. 2. No errors, but nothing is written to the target database. Both which happen randomly with no obvious pattern. Could this hint at a missing memory initialization or overrun? Even though AsyncIO is no longer actively maintained, can anyone reproduce my findings? I am running on Windows and have limited testing capabilities like no Valgrind, etc. Ralf [1] http://www.sqlite.org/src/info/3d548db7eb [2] http://www.mail-archive.com/sqlite-users@sqlite.org/msg74170.html #include #include #include #include "sqlite3.h" #include "sqlite3async.h" #pragma hdrstop sqlite3 *db; void sqlite3_check(int e) { if (e != SQLITE_OK) { printf("Error %d\n", e); } } #ifdef SQLITE_ENABLE_ASYNCIO int StopThread = 0; DWORD WINAPI MyThreadFunction( LPVOID lpParam ) { do { printf ("sqlite3async_run() ..."); sqlite3async_run(); sqlite3_sleep (0); printf ("OK\n"); } while (!StopThread); return 0; } #endif /* SQLITE_ENABLE_ASYNCIO */ char* FILE_NAME = "test.db3"; int main(int argc, _TCHAR* argv[]) { int i; #ifdef SQLITE_ENABLE_ASYNCIO HANDLE ThreadHandle; #endif /* SQLITE_ENABLE_ASYNCIO */ if (!DeleteFile(FILE_NAME)) { printf("Error deleting file %s\n", FILE_NAME); } #ifdef SQLITE_ENABLE_ASYNCIO sqlite3_check(sqlite3async_initialize(NULL, 1)); sqlite3async_control(SQLITEASYNC_HALT, SQLITEASYNC_HALT_IDLE); ThreadHandle = CreateThread( NULL, // default security attributes 0, // use default stack size MyThreadFunction, // thread function name NULL, // argument to thread function 0, // use default creation flags NULL); // returns the thread identifier #endif /* SQLITE_ENABLE_ASYNCIO */ sqlite3_check(sqlite3_open_v2 (FILE_NAME, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)); sqlite3_check(sqlite3_exec(db, "create table if not exists t (a)", NULL, NULL, NULL)); for (i = 0; i < 10; i++) { char *s = sqlite3_mprintf("insert into t values (%d);", i); sqlite3_check(sqlite3_exec(db, s, NULL, NULL, NULL)); sqlite3_free(s); } sqlite3_check(sqlite3_close(db)); #ifdef SQLITE_ENABLE_ASYNCIO StopThread = 1; WaitForSingleObject(ThreadHandle, INFINITE); CloseHandle(ThreadHandle); sqlite3async_shutdown(); #endif /* SQLITE_ENABLE_ASYNCIO */ printf("Done.\nPress enter a number to quit.\n"); scanf("%d", &i); return 0; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Async IO in SQLite 3.7.15
On 04.12.2012 22:25, Richard Hipp wrote: >> My question is if existing applications which Async IO should continue >> to work with SQLite 3.7.15? Or has something in the SQLite core changed >> so that you'd expect Async IO failures in 3.7.15, or later versions? > > They should continue to work, as far as we are aware. Nothing in the core > has changed to break them. Thanks! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Async IO in SQLite 3.7.15
On 04.12.2012 22:14, Richard Hipp wrote: >> Does this also mean that it will no longer be working with SQLite >> 3.7.15? Is it just deprecated for new development? Or has something else >> changed that I should take care of? > > That means that we are not willing to devote large amounts of time to it > anymore. If you have patches that you want us to put in, we'll be glad to > do that. Thanks, this is well understood and clear from the README note. My question is if existing applications which Async IO should continue to work with SQLite 3.7.15? Or has something in the SQLite core changed so that you'd expect Async IO failures in 3.7.15, or later versions? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Async IO in SQLite 3.7.15
I experience problems with Async IO testing the latest 3.7.15 trunk. Simple statements like CREATE TABLE fail with SQLITE_IOERR. I read the note in the Async IO README.txt that Async IO is now superceded by WAL mode and no longer maintained. Does this also mean that it will no longer be working with SQLite 3.7.15? Is it just deprecated for new development? Or has something else changed that I should take care of? Ralf On 03.12.2012 21:25, Richard Hipp wrote: > We want 3.7.15 to be a good release, so please do have a look at the > amalgamation snapshot described in the previous email (and copied below) if > you have not done so already, and let us know if you encounter any > problems. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to restore from backup or alter db in multi-user envionment?
On 02.12.2012 04:37, Simon Slavin wrote: >> I'm using DISQLite and the online api in delphi, not the command >> line thing. > > I have seen some bad drivers which assume that underlying schema will > not be changed by another user while they have a database connection > open. I have no reason to believe that this is one of them, but it > might be worth reading the documentation. As the author of DISQLite3 I confirm that DISQLite3 is NOT a "bad driver" in that it assumes that the underlying schema will not be changed. DISQLite3 is plain SQLite3, just conveniently made available for the Delphi programming language. What works in SQLite3 also works in DISQLite3. Some extras are added, but nothing left out or modified. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to know what terms were created using FTS
On 17.08.2012 09:30, Mohit Sindhwani wrote: > We're using FTS4 and it works well for many things. One of the things > that we'd like to do is to see what terms are being created by the > tokenizer in use. What would be the easiest way to do that? > > I tried looking through the fts_aux table and the segments and content > tables, but nothing struck me directly as usable. Any suggestions? http://www.sqlite.org/fts3.html#fts4aux Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detecting when a table is created
On 26.06.2012 14:33, Vivien Malerba wrote: > The code is some SQL entered by the user, I have no control over it. There > is effectively the possibility to parse the SQL entered, detect the CREATE > table statement and act accordingly, but I would like to see if SQLite has > some kind of builtin feature I could use first. Two options, both not intended for it, but could work well: - http://sqlite.org/c3ref/set_authorizer.html - http://sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfiglog Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OPEN_CREATE required by named memory database?
Testing SQLite Fossil [0abdc2903d], sqlite_open_v2() returns SQLITE_PERM when trying to open a named memory database if the flag SQLITE_OPEN_CREATE is not passed. This is in contrast to unnamed memory databases. Is this intentional? Basic C code below demonstrates the issue. Ralf -- sqlite3 *db = 0; void check (int i) { switch (i) { case SQLITE_DONE: case SQLITE_OK: case SQLITE_ROW: break; default: printf ("Error %d: %s\n", i, sqlite3_errmsg(db)) ; } }; int main(int argc, char* argv[]) { char * fn; /* Test opening a named memory database using a URI file name. */ fn = "file:test.db?cache=shared&mode=memory"; check(sqlite3_open_v2(fn, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL)); check(sqlite3_close(db)); /* Test opening a named memory database using a URI file name. This fails because SQLITE_OPEN_CREATE is missing. */ fn = "file:test.db?cache=shared&mode=memory"; check(sqlite3_open_v2(fn, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, NULL)); check(sqlite3_close(db)); /* Test opening an unnamed memory database using a URI file name. */ fn = "file::memory:?cache=shared"; check(sqlite3_open_v2(fn, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, NULL)); check(sqlite3_close(db)); /* Test opening an unnamed memory database. */ fn = ":memory:"; check(sqlite3_open_v2(fn, &db, SQLITE_OPEN_READWRITE, NULL)); check(sqlite3_close(db)); } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation glitch: Repeated "or" ...
... at both end and beginning of these lines: http://www.sqlite.org/src/artifact/45a846045ddb8c4318f2919f3a70f011df5ca783?ln=2584-2585 Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Details on New Features
On 04.05.2012 16:39, Richard Hipp wrote: > If a single min() or max() aggregate function appears in a query, then any > other columns that are not contained within aggregate functions and that > are not elements of the GROUP BY will take values from one of the same rows > that satisfied the one min() or max() aggregate function. Given that more than one row satisfies the one min() or max() aggregate function (think of multiple, identical smallest or largest values). Which row will SQLite pick? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE3 64-bit version
On 27.04.2012 15:08, Gabriel Corneanu wrote: > With the amalgamation, it's also quite easy to compile to one obj and > link directly in Delphi (similar to jpeg; that's probably what you also > do). No pun intended, why should I pay for it? - DISQLite3 Personal edition if free! - DISQLite3 is 64-bit and you are looking for 64-bit SQLite. - DISQLite3 is *not* compiled from the amalgamation. This enables Delphi's smart linking by excluding unused features like FTS, RTree, etc. Your application file size shrinks. - DISQLite3 contains the *complete* SQLite3 API, not only those most commonly used. It also includes extensions not part of sqlite3.dll. - DISQLite3 is well tested before each release. New APIs and functionality are added to the internal DUnit test suite. Problems are reported back to the SQLite community. - DISQLite3 uses fastcall / register calling conventions and Delphi's internal memory manager. This makes it faster then sqlite3.dll. - DISQLite3 is regularly updated. This saves you from maintaining your own, local API translations so you can focus on your application. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE3 64-bit version
On 27.04.2012 11:38, Gabriel Corneanu wrote: > There is one more reason to use DLLs, I'm surprised noone mentioned it. > What if you don't use C??? (I use myself Delphi with a header conversion). DISQLite3 compiles right into your Delphi application. Both 32-bit and 64-bit supported: http://www.yunqa.de/delphi/doku.php/products/sqlite3/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What do people think of SQLite Root?
On 05.03.2012 18:45, Rob Richardson wrote: > I can't get to www.yunqa.de now. I tried in IE8 and FireFox. Most strange. Luckily, search engines find plenty of SQLiteSpy download alternatives. Here is one of the more well known ones: http://www.softpedia.com/get/Internet/Servers/Database-Utils/SQLiteSpy.shtml Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What do people think of SQLite Root?
On 05.03.2012 16:11, Rob Richardson wrote: > With the latest version, I think you can. SQLiteSpy grid editing has been around for years, it is available since version 1.6.0., 7 Feb 2007. > But I haven't been able to get the latest version. The link you > provided does not work for me. Do you know any site other than > yunqa.de where the latest SQLite can be downloaded? SQLiteSpy downloads might be available from various portals. But usually http://www.yunqa.de works just fine. Just try again later. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What do people think of SQLite Root?
On 05.03.2012 14:50, Rob Richardson wrote: > I keep hoping to find something better, because SQLiteSpy does not > offer the ability to edit a table inside a grid. Not quite true. SQLiteSpy can edit table cells inside a grid. Double-click the table-icon in the schema treeview to display the table. When you then select a table cell in the grid and press F2, the edit window will pop up. See feature item 2 and the screenshot at http://yunqa.de/delphi/doku.php/products/sqlitespy/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT
On 13.02.2012 10:18, Dan Kennedy wrote: > On 02/10/2012 11:06 PM, Ralf Junker wrote: >> >>> The new feature to insert multiple rows of VALUES in a single INSERT >>> >>>http://www.sqlite.org/src/info/eb3b6a0ceb >>> >>> gives wrong results if SQLite is compiled with >>> SQLITE_OMIT_COMPOUND_SELECT. >> >> Has the team seen this or has it been overlooked? Shall I file a ticket? > > I get this: > > SQLite version 3.7.11 2012-02-13 08:50:23 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE TABLE t1(x, y); > sqlite> INSERT INTO t1 VALUES(1, 2), (3, 4); > Error: near ",": syntax error > > Is this what you are seeing? That the syntax is rejected with > an error message? Or some other problem? I believe Richard has already take care of this and disabled multiple rows in the VALUES clause if SQLITE_OMIT_COMPOUND_SELECT is defined: http://127.0.0.1:8080/info/92131195d0 Before this, I did not see any error message. After, I expect an error message similar to yours (from reading the code, I did not compile and run). So do I guess correctly that you were testing with trunk later than check-in [92131195d0]? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT
On 07.02.2012 12:28, Ralf Junker wrote: > The new feature to insert multiple rows of VALUES in a single INSERT > > http://www.sqlite.org/src/info/eb3b6a0ceb > > gives wrong results if SQLite is compiled with SQLITE_OMIT_COMPOUND_SELECT. Has the team seen this or has it been overlooked? Shall I file a ticket? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT
The new feature to insert multiple rows of VALUES in a single INSERT http://www.sqlite.org/src/info/eb3b6a0ceb gives wrong results if SQLite is compiled with SQLITE_OMIT_COMPOUND_SELECT. Example: CREATE TABLE t10(a,b,c); INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9); SELECT * FROM t10; For me returns 7, 8, 9 It seems that with SQLITE_OMIT_COMPOUND_SELECT, only the last multiple rows value is inserted. Error messages also differ: INSERT INTO t10 VALUES(11,12,13), (14,15); returns the error message: table t10 has 3 columns but 2 values were supplied instead of: all VALUES must have the same number of terms Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_db_release_memory(): Return value undocumented.
Reading the sources, sqlite3_db_release_memory() always returns SQLITE_OK. Will it stay this way or will it some day return another value like the amount of memory released? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_uri_int64() doc error
The sqlite3_uri_int64() doc reads: "If the value of P is something other than an integer, then zero is returned." I found this to be not true. Instead, it returned default. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiplex VACUUM fails to delete chunks on Windows
VACUUM with multiplex does not delete chunks on Windows (fossil [8ce2b74a82]). It seems this is because the file handle(s) are still held open by the multiplex layer when xDelete is triggered. Since Windows can not delete open files, they are kept. I have not investigated this in depth, but closing the file handle before deleting the file works well for my simple test case. Here is the change in multiplexSubClose(): static void multiplexSubClose( multiplexGroup *pGroup, int iChunk, sqlite3_vfs *pOrigVfs ){ sqlite3_file *pSubOpen = pGroup->aReal[iChunk].p; if( pSubOpen ){ pSubOpen->pMethods->xClose(pSubOpen); /* <-- Moved here */ if( pOrigVfs ) pOrigVfs->xDelete(pOrigVfs, pGroup->aReal[iChunk].z, 0); /* pSubOpen->pMethods->xClose(pSubOpen); <-- Moved above */ sqlite3_free(pGroup->aReal[iChunk].p); } sqlite3_free(pGroup->aReal[iChunk].z); memset(&pGroup->aReal[iChunk], 0, sizeof(pGroup->aReal[iChunk])); } By the way: No error is returned if multiplex VACUUM fails to delete a chunk. Maybe it should, to warn curious end users who like to investigate files with uncommon names? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] test/exists.test 2.$tn.1 fails if both DBs are in WAL mode
For me, this test from test/exists.test fails if both database connections are opened in WAL mode, that is if "PRAGMA journal_mode=WAL" is issued right after each DB is opened. # TABLE objects. # do_test{ sql1 { DROP TABLE IF EXISTS t1 } sql2 { CREATE TABLE t1(x) } sql1 { DROP TABLE IF EXISTS t1 } sql2 { SELECT name FROM sqlite_master WHERE type = 'table' } } {} This is against http://www.sqlite.org/src/info/f346dae127. I am running a ported version of the test instead of the original tcl so my finding could wrong. Still, I believe it better to let you know in case you want to check this last-minute change before you release 3.7.6. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] test/exists.test 2.$tn.1 fails if both DBs are in WAL mode
The test passes well if I recreate the database file anew just prior to running it so it acts upon an empty database. If this is what is intended, I apologize for the false alarm. Ralf On 10.04.2011 10:28, Ralf Junker wrote: > For me, this test from test/exists.test fails if both database > connections are opened in WAL mode, that is if "PRAGMA journal_mode=WAL" > is issued right after each DB is opened. > > # TABLE objects. > # > do_test { > sql1 { DROP TABLE IF EXISTS t1 } > sql2 { CREATE TABLE t1(x) } > sql1 { DROP TABLE IF EXISTS t1 } > sql2 { SELECT name FROM sqlite_master WHERE type = 'table' } > } {} > > This is against http://www.sqlite.org/src/info/f346dae127. > > I am running a ported version of the test instead of the original tcl so > my finding could wrong. Still, I believe it better to let you know in > case you want to check this last-minute change before you release 3.7.6. > > Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] test/exists.test 2.$tn.1 fails if both DBs are in WAL mode
For me, this test from test/exists.test fails if both database connections are opened in WAL mode, that is if "PRAGMA journal_mode=WAL" is issued right after each DB is opened. # TABLE objects. # do_test{ sql1 { DROP TABLE IF EXISTS t1 } sql2 { CREATE TABLE t1(x) } sql1 { DROP TABLE IF EXISTS t1 } sql2 { SELECT name FROM sqlite_master WHERE type = 'table' } } {} This is against http://www.sqlite.org/src/info/f346dae127. I am running a ported version of the test instead of the original tcl so my finding could wrong. Still, I believe it better to let you know in case you want to check this last-minute change before you release 3.7.6. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Extremely large values error
On 29.03.2011 15:17, Max Vlasov wrote: > Thanks, this forced me to search more on the topic. I use Delphi and it > appears that all Borland compilers has their own floating-point exception > handling chain and it is on by default so if any division by zero or > overflow appears inside sqlite3_step Delphi will handle this with its own > exception handling. When running SQLite from Delphi, you must disable FPU exceptions. Below is a simple console project which uses the DISQLite3 Delphi port of SQLite to demonstrate how this works. Ralf --- { DISQLite3 example project to show how to disable FPU exceptions so that very large integer numbers are correctly converted to 'Inf' text. Exceptions should always be disabled before running any C library from Delphi. This is important because exceptions do not exist in C and C does not know how to handle them. Exceptions will therefore cause the entire C library stack to unwind undisturbed until the exception is finally handled by Delphi code. The result of this is undefined and might range from simple memory leaks to more severe and complicated problems. This applies to all C libraries, both internal (*.obj) and extenal (*.dll). Visit the DISQLite3 Internet site for latest information and updates: http://www.yunqa.de/delphi/ Copyright (c) 2011 Ralf Junker, Delphi Inspiration - } program DISQLite3_Very_Large_Integer; {$APPTYPE CONSOLE} {$I DI.inc} {$I DISQLite3.inc} uses {$IFDEF FastMM}FastMM4, {$ENDIF}DISystemCompat, SysUtils, DISQLite3Api; var DB: sqlite3_ptr; procedure ExecSql(const SQL: Utf8String); var i: Integer; Stmt: sqlite3_stmt_ptr; begin sqlite3_check(sqlite3_prepare_v2(DB, PUtf8Char(SQL), Length(SQL), @Stmt, nil), DB); try while sqlite3_check(sqlite3_step(Stmt), DB) = SQLITE_ROW do begin for i := 0 to sqlite3_column_count(Stmt) - 1 do begin if i > 0 then Write(', '); Write(sqlite3_column_str(Stmt, i)); end; WriteLn; end; finally sqlite3_finalize(Stmt); end; end; const DB_FILE_NAME = 'test.db3'; begin { Disable FPU exceptions. No need to restore, setting is process specific. } Set8087CW($133F); try sqlite3_initialize; DeleteFile(DB_FILE_NAME); sqlite3_check(sqlite3_open(DB_FILE_NAME, @DB)); try ExecSql('CREATE TABLE t1(v TEXT);'); ExecSql('INSERT INTO t1 VALUES(''' + '123456789012345678901234567890123456789012345678901234567890' + '123456789012345678901234567890123456789012345678901234567890' + '123456789012345678901234567890123456789012345678901234567890' + '123456789012345678901234567890123456789012345678901234567890' + '123456789012345678901234567890123456789012345678901234567890' + '1234567890'');'); ExecSql('INSERT INTO t1 VALUES(''-' + '123456789012345678901234567890123456789012345678901234567890' + '123456789012345678901234567890123456789012345678901234567890' + '123456789012345678901234567890123456789012345678901234567890' + '123456789012345678901234567890123456789012345678901234567890' + '123456789012345678901234567890123456789012345678901234567890' + '1234567890'');'); ExecSql('SELECT length(v), CAST(v AS float) FROM t1;'); finally sqlite3_check(sqlite3_close(DB), DB); sqlite3_shutdown; end; except on e: Exception do WriteLn(e.Message); end; WriteLn; WriteLn('Done - Press ENTER to Exit'); ReadLn; end. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Doc errors: Too many "checked out." in Status Parameters for database connections
There are far too many "checked out." in the following docs, probably left-overs from copy-paste operations: ** ^(SQLITE_DBSTATUS_LOOKASIDE_HIT ** This parameter returns the number malloc attempts that were ** satisfied using lookaside memory. Only the high-water value is meaningful; ** the current value is always zero. ** checked out.)^ ** ** ^(SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE ** This parameter returns the number malloc attempts that might have ** been satisfied using lookaside memory but failed due to the amount of ** memory requested being larger than the lookaside slot size. ** Only the high-water value is meaningful; ** the current value is always zero. ** checked out.)^ ** ** ^(SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL ** This parameter returns the number malloc attempts that might have ** been satisfied using lookaside memory but failed due to all lookaside ** memory already being in use. ** Only the high-water value is meaningful; ** the current value is always zero. ** checked out.)^ Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug? 3.7.5 RC2 FTS4 division by zero exception
This SQL: drop table if exists t11; CREATE VIRTUAL TABLE t11 USING fts4; INSERT INTO t11 VALUES('quitealongstringoftext'); INSERT INTO t11 VALUES('anotherquitealongstringoftext'); UPDATE t11_stat SET value = X''; SELECT matchinfo(t11, 'nxa') FROM t11 WHERE t11 MATCH 'a*'; leads to a division by zero exception in fts3_snippet.c, line 1171: pInfo->aMatchinfo[iCol] = (u32)(((u32)(nToken&0x)+nDoc/2)/nDoc); nDoc is zero here, which is not tested for, but I believe it should. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 + SQLITE_OMIT_PAGER_PRAGMAS causes division by zero exception
Recent changes to FTS3 apparently require that SQLite must be compiled with pager pragmas, otherwise FTS3 will cause a division by zero exception as I have experienced right now. This means that the FTS3 extension can crash an application if the core SQLite library is compiled with SQLITE_OMIT_PAGER_PRAGMAS. The following SQL triggers the exception, based on the table data in fts4aa.test: SELECT docid, mit(matchinfo(t1)) FROM t1 WHERE t1 MATCH 'spake hebrew' ORDER BY docid; Debugging is not available to me, but as far as I read the code, FTS3's dependency on pager pragmas was introduced by check-in 5ae0ba447a on 2010-10-19. With SQLITE_OMIT_PAGER_PRAGMAS, the "PRAGMA %Q.page_size" command in fts3.c, fts3DatabasePageSize() silently returns 0 instead of reporting an appropriate error message, which can then lead to a division by zero exception, most likely in fts3_write.c, sqlite3Fts3SegReaderCost(). Given that the FTS3 extension can potentially be linked against a SQLite core compiled with SQLITE_OMIT_PAGER_PRAGMAS and cause unexpected failure, this might be worth a workaround or error message. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Check-in [876845661a] incomplete
Check-in [876845661a] (http://www.sqlite.org/src/info/876845661a) is incomplete: fts2_tokenizer.c still contains calls to isalnum() and tolower() for FTS2. FTS1 also #includes , just in case you care to remove it from SQLite completely. The changes are just like for FTS2. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a program that converts sqlite database from windows-1252 to utf-8 ?
On 11.09.2010 20:42, Stef Mientki wrote: > is there a program that converts sqlite database from windows-1252 to utf-8? Yes, SQLiteSpy does this: * Open database file * Menu Execute -> Text to Unicode Conversion. * Select tables and columns to convert. * Choose database codepage. * Press OK to start the conversion. After conversion, browse the database contents to verify the result. As SQLiteSpy supports Unicode text display and entry, you can do it right there. If you like what you see, execute "COMMIT" to store the conversion changes. "ROLLBACK" in case of conversion errors and try again with a different codepage. Download: http://yunqa.de/delphi/doku.php/products/sqlitespy/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rollback to savepoint regression / bug with journal_mode=off and locking_mode=exclusive
On 27.08.2010 18:58, Noah Hart wrote: > http://www.sqlite.org/pragma.html#pragma_journal_mode states > > The OFF journaling mode disables the atomic commit and rollback capabilities > of SQLite. The ROLLBACK command no longer works; it behaves in an undefined > way. Applications must avoid using the ROLLBACK command when the journal > mode is OFF I had read this, too, before posting my report, and that's why I acknowledged that it might just be a regression and not a bug. > I don't think this is a bug, just different undefined results. But the fact that this is actually part of the test suite (savepoint.test) where it works well with locking_mode=normal makes me believe that it is not just some undefined behavior, even if the documentation says so. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] rollback to savepoint regression / bug with journal_mode=off and locking_mode=exclusive
SQLite 3.7.2 has a regression with journal_mode=off and locking_mode=exclusive. Here is the SQL reproduce: drop table if exists t1; PRAGMA locking_mode=exclusive; pragma locking_mode; CREATE TABLE t1(a PRIMARY KEY, b); PRAGMA journal_mode = off; BEGIN; INSERT INTO t1 VALUES(13, 14); SAVEPOINT s1; INSERT INTO t1 VALUES(15, 16); ROLLBACK TO s1; ROLLBACK; SELECT * FROM t1; - SQLite3 3.7.2 rolls back the savepoint insert and yields: exclusive exclusive off 13|14 SQLite3 3.6.23.1 commits the savepoint insert and yields: exclusive exclusive off 13|14 15|16 The SQL was shortened from savepoint.test. It works well in locking_mode=normal so I dare say this is most likely a locking_mode=exclusive bug. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] partial index?
On 20.08.2010 00:18, Eric Smith wrote: > Our app is caching what are basically csv files. Hundreds of files, > about 2m records per file. Sometimes we want to delete all the cache > rows for one of the files. We know ahead of time which file it will > be -- let's say it's file 7. > > The schema is roughly > > create table records(__recno INTEGER PRIMARY KEY, fileId, data); > > So sometimes we would *like* to say "delete from records where > fileId=7". > > But that is bad because does a full table scan. > > So the next cut is to say "create index recordsIdxFileId on > records(fileId)". > > But that is bad because it is a huge burden during INSERT and is not > used often enough (or with enough values) to justify its existence. > > What I really want is to be able to say "create index > recordsIdxFileIdOnFile3 on records(fileId) where fileId=7". But > sqlite doesn't do that. I believe you can do this with an index helper table managed by triggers on your main table. Use the insert trigger's when clause to control which records to "index". The delete statement is obviously a little more complex than an ordinary delete, but "explain query plan" shows that it uses indexes instead of a full table scan so it should run quite fast. Example SQL follows below. Ralf drop table if exists records; create table records (id INTEGER PRIMARY KEY, fileid, value); drop table if exists records_idx; create table records_idx (id INTEGER PRIMARY KEY, fileid); create index records_idx_idx on records_idx (fileid); create trigger records_insert_after after insert on records when new.fileid = 7 begin insert into records_idx values (new.rowid, new.fileid); end; create trigger records_delete after delete on records begin delete from records_idx where id = old.rowid; end; insert into records (fileid, value) values (1, 'one'); insert into records (fileid, value) values (7, 'seven 1'); insert into records (fileid, value) values (7, 'seven 2'); insert into records (fileid, value) values (8, 'eight'); select * from records_idx; delete from records where rowid in (select rowid from records_idx where fileid = 7); select * from records; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Different error message after sqlite3_reset() or sqlite3_finalize()
Related to constraint violations, an error message returned by sqlite3_errmsg() changes to the better after calling sqlite3_reset() or sqlite3_finalize(). Example code is below, here is the output: sqlite3_step: 19 constraint failed sqlite3_reset:19 t.c may not be NULL sqlite3_finalize: 0 t.c may not be NULL As can be seen, sqlite3_reset() "enhances" the error message returned by sqlite3_errmsg() by filling in the column name and reason why the constraint failed. I would like to ask if this could be changed so that sqlite3_step() by itself generates the more meaningful error message, even without calling sqlite3_reset() or sqlite3_finalize()? IIRC, having sqlite3_step() generate the "final" error codes and messages was one of the reasons that brought about sqlite3_prepare_v2(). I observe that the error message generated after sqlite3_prepare_v2() ("constraint failed") is far better than that of sqlite3_prepare() ("SQL logic error or missing database"), but it is not as good as it gets after resetting the statement. Ralf Example code: int main(int argc, char* argv[]) { sqlite3* db; sqlite3_stmt* stmt; int e; sqlite3_open("test.db3", &db); sqlite3_exec(db, "create table if not exists t (c text not null)", NULL, NULL, NULL); sqlite3_prepare_v2 (db, "insert into t values (null)", -1, &stmt, NULL); e = sqlite3_step(stmt); printf("sqlite3_step: %d %s\n", e, sqlite3_errmsg(db)); e = sqlite3_reset(stmt); printf("sqlite3_reset:%d %s\n", e, sqlite3_errmsg(db)); e = sqlite3_finalize(stmt); printf("sqlite3_finalize: %d %s\n", e, sqlite3_errmsg(db)); sqlite3_close (db); printf ("\nDone"); scanf ("*%s"); return 0; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE VIEW ... LIMIT 1; -- apparently no effect?
This is SQLite 3.6.23.1 compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT (plus a few others, which should not matter to the problem). The UPDATE ... LIMIT clause works fine when applied to tables, but suppresses any updates when applied to a view with an update trigger. Here is some example SQL: create table test (data,rownum integer); insert into test values ('one',1); insert into test values ('two',2); create view vtest as select * from test; create trigger Trig1 instead of update of data on vtest begin update test set data = new.data where rownum = new.rownum ; end; -- No LIMIT - this works. update vtest set data = 'yyy'; --works -- LIMIT clause present - nothing is updated. update vtest set data = 'zzz' limit 1; It is unfortunately not possible to reproduce this with the reference binaries from sqlite.org since they are compiled without SQLITE_ENABLE_UPDATE_DELETE_LIMIT. Searching the timeline and previous list messages did not turn up any applicable results. So here are my questions: Can anybody confirm my findings? If so, is this the expected behavior? Or should not UPDATE ... LIMIT on views work just like on tables? Could this be a bug worth creating a ticket for? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SUBSTR overload and ALTER TABLE
We just experienced the hard way that overloading certain built-in SQL function can interfere with core SQL commands if the overloaded function behaves differently from the built-in function. Not surprising, after looking at the sources: * ALTER TABLE - alter.c uses SUBSTR and LIKE. * VACUUM - vacuum.c uses SUBSTR, LIKE, and QUOTE. * Possible others? Would it be possible that the SQLite core always uses the built-in functions instead of the overloaded ones? Or should overloading be disabled for "core critical" SQL functions? If not, maybe those "critical" functions could be mentioned in the documentation to warn developers that strange things might happen if they change their behavior? IMHO, LIKE is especially critical since it is a likely candidate for overriding to add UNICODE LIKE to applications. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users