bgcolor # Type Status Created By Subsys Changed Assigned Svr Pri Title _Description _Remarks #f2dcdc 2917 code active 2008 Feb anonymous 2008 Feb 4 4 Tcl interface - busy callback confusion script/procedure In the Tcl interface the "busy" method doesn't work if a script is supplied instead of a procedure: % package req sqlite3 3.5.1 % sqlite3 db hl7journal.db % db busy {puts busy; expr 0} % db eval {select count(*) from incoming} busy database is locked Here the callback script is only invoked once, even though it returns 0. If we put this code in a procedure it works as desired/expected: % proc b args {puts busy; after 1000; expr 0} % db busy b % db eval {select count(*) from incoming} busy busy busy ^C _2008-Feb-01 12:31:45 by anonymous:_ {linebreak} After researching this a little it appears this happens because the busy callback is invoked with an extra argument. The extra argument leads to an error but that error is only visible through errorInfo, not the result. I humbly suggest the following changes: * mention the extra argument in the documentation of the Tcl interface * forward the error from the busy callback to Tcl (replacing the "database is locked") * enhance errorInfo to make the invokation of the busy callback apparent. Currently, I'm getting this errorInfo: % db busy {puts hi; after 1000; return 0} % db eval "select count(*) from incoming" hi database is locked % set errorInfo bad option "0": must be -code, -errorcode, or -errorinfo while executing "return 0 0" invoked from within "db eval "select count(*) from incoming"" It would be nicer to get something like bad option "0": must be -code, -errorcode, or -errorinfo while executing "return 0 0" from busy callback "puts hi; after 1000; return 0 0" invoked from within "db eval "select count(*) from incoming"" #f2dcdc 2916 code active 2008 Feb anonymous 2008 Feb 1 1 sqlitedll-3_5_5.zip is older 3.5.4 binary sqlitedll-3_5_5.zip in download section is same with old 3.5.4 binary. _2008-Feb-01 12:13:04 by anonymous:_ {linebreak} Yes , I can confirm it #e8e8bd 2228 new active 2007 Feb anonymous 2008 Feb 3 4 horizontal partitioning I would love to see a feature in the next version of sqlite that includes horizontal table partitioning. I see it currently in MySQL Version 5.1 beta. It would be awesome to see it in SQLite _2008-Feb-01 02:46:29 by anonymous:_ {linebreak} Well it would be great for us - won't need to emulate it. Any thoughts about that? That's not only about large data sets - you can also make it for some sort of horizontal ATTACH of small separate pieces. #e8e8bd 2915 build active 2008 Feb pweilbacher 2008 Feb 4 3 fix Makefile for platforms that need .exe extension There are some targets that need a $(TEXE) added in Makefile.in. Otherwise a platform that needs a .exe extension cannot run e.g. tests. #f2dcdc 2914 code active 2008 Jan anonymous 2008 Jan 3 3 ATTACH returns SQLITE_ERROR when it means SQLITE_BUSY I'm seeing the same behavior as in #2096, with SQLite 3.5.4. ATTACH DATABASE fails with SQLITE_ERROR rather than SQLITE_BUSY when the database to be attached, or the main database of the connection being attached to, is EXCLUSIVE-locked by another database connection. For added confusion, sqlite3_errmsg() says "database is locked" when the ATTACH is done via sqlite3_exec(), but "SQL logic error or missing database" when the ATTACH is done via sqlite3_step(). As a result of this bug, it is difficult to distinguish between fatal and transient ATTACH errors, particularly when sqlite3_step() is used. I am attaching a test program that demonstrates the problem. #f2dcdc 2905 code active 2008 Jan anonymous 2008 Jan pweilbacher 2 2 mutex_os2.c - incorrect mutex implementation The OS/2 version of sqlite3_mutex_alloc() is badly broken. It creates named mutexes which, by design, are global rather than process-specific as intended. This might be minimally acceptable except that the function reuses the same name every time it attempts to create a SQLITE_MUTEX_FAST or SQLITE_MUTEX_RECURSIVE. The result is that every call returns the exact same semaphore to every thread in every process using sqlite3. Once this mutex is owned by one process, other processes calling sqlite3_mutex_enter() will be blocked. Much the same is true for the static mutexes. Every process ends up using the exact same SQLITE_MUTEX_STATIC_MASTER, SQLITE_MUTEX_STATIC_MEM, etc. There's another flaw that is fairly minor compared with the above: in an attempt to avoid concurrency when creating the static mutexes, this function uses an API call that is thoroughly deprecated. The attached patch remedies all of these issues. Since the logic that protects the creation of the static mutexes may not be self-evident, here's an explanation: The existence (or non-existence) of a given named mutex is itself a semaphore. If the isInit flag is false, the code attempts to create a mutex whose name is unique to that process. If the attempt is successful, there are two possibilities: (1) either the current thread is the first to reach this code & may proceed; (2) or while the current thread was making its preparations, another thread created the mutex, did the init, then closed the mutex. Testing isInit immediately after creating the mutex determines which possibility is valid. If mutex creation fails due to a duplicate name, then another thread is currently performing the init. In this case, the current thread simply has to wait a while until the other thread is done & isInit becomes true. Submitted by Rich Walsh (richATe-vertise.com) #f2dcdc 2902 code active 2008 Jan anonymous 2008 Jan drh 3 3 Add watch support to SQLite SQLite currently provides only TRIGGERs and the update_hook() as a way for applications to stay informed about changes to the database. But both of these alternatives do not provide enough details about the actual changes to the underlying database file(s). We've prepared a patch for SQLite 3.5.x to allow applications to install a watch_hook into the database, that will be invoked everytime the database is changed with exact details about the change that was performed. _2008-Jan-22 16:06:59 by anonymous:_ {linebreak} Great idea and nice job. This functionality is very useful. ---- _2008-Jan-31 18:27:16 by anonymous:_ {linebreak} Any chance to get this committed for the next release (i.e. 3.5.6)? ---- _2008-Jan-31 19:38:35 by drh:_ {linebreak} Unlikely, for two reasons: 1: I am unconvinced that this patch solves a problem that needs solving. It is vitally important to a project like SQLite that we work to avoid clutter and cruft. That means that any change must have a compelling rational or else it is rejected. 2: The patches are against version 3.5.4. There were many changes to the core for 3.5.5 and the patches no longer work. ---- _2008-Jan-31 20:58:55 by anonymous:_ {linebreak} We can (and will) port the changes to 3.5.5, so the second point will be done. First the first point, I'm not sure how many projects will actually need this functionality, but I guess there are quite a lot of projects that would benefit, and for the others, there's zero overhead due to this patch. ---- _2008-Jan-31 21:21:30 by drh:_ {linebreak} There is a lot of overhead for me because if I accept this patch, that means I have to maintain it forever. Most of the work is in maintenance, not coming up with the original patch. #e8e8bd 2912 new active 2008 Jan anonymous 2008 Jan 5 4 merge join Would it be possible to implement merge joins? #f2dcdc 2903 code active 2008 Jan anonymous 2008 Jan 2 2 tclinstaller.tcl fails on path and permissions issue When compiling using custom PREFIX, pointing to private directory, tclinstaller.tcl fails, because it tries to remove contents from /usr/share/tcl8.4/sqlite3.
./configure --prefix=/my/private/sqlite/sqlite-3.5.4 ... # success make ... # success make install ... tclsh ./tclinstaller.tcl 3.5 error deleting "/usr/share/tcl8.4/sqlite3": not owner while executing "file delete -force $LIBDIR/sqlite3" (file "./tclinstaller.tcl" line 17) make: *** [tcl_install] Error 1I've found two work-arounds: 1: If you run make install as root. 2: If you use ./configure --disable-tcl _2008-Jan-28 17:47:39 by anonymous:_ {linebreak} I also ran into this problem. make install as root will end up copying files into the system's library directory and is almost certainly not what you want if you specified your own --prefix. #f2dcdc 2911 code active 2008 Jan anonymous 2008 Jan 2 2 Adding parentheses to a FROM clause Hi, Parentheses in a FROM statement seem to mess with the ability to use table aliases in the "what" part. Here is an example: Start SQLite: $ sqlite3 employee.db SQLite version 3.5.4 Enter ".help" for instructions create a couple of tables and populate them with test data: sqlite> create table person (id integer, name text, employerid integer); sqlite> create table employer (id integer, name text); sqlite> insert into person (id, name, employerid) values (1, "Dave", 1); sqlite> insert into employer (id, name) values (1, "ACME"); Run a simple query with *no parentheses* in the FROM statement: sqlite> select b.id from person as a inner join employer as b on a.employerid = b.id; 1 Everything works as expected. Now, repeat that query *with parentheses*: sqlite> select b.id from (person as a inner join employer as b on a.employerid = b.id); SQL error: no such column: b.id There you have it. This may be related to ticket #1822, although that ticket deals with aliases and subqueries. This problem seems to be more fundamental. Many thanks, -- Dave #e8e8bd 2909 build active 2008 Jan aswift 2008 Jan aswift 4 4 Omit the _XOPEN_SOURCE 500 define on Mac OS-X (check for __APPLE__) sqliteInt.h attempts to avoid setting _XOPEN_SOURCE on MacOSX by checking if __DARWIN__ is defined, however on Leopard (as of Mac OS X 10.5) this test fails and should be enhanced by testing for both __DARWIN__ and __APPLE__ Building sqlite succeeds with _XOPEN_SOURCE defined, but it causes _POSIX_C_SOURCE to be defined which leads to failing to define F_FULLFSYNC which prevents use of the fullfsync pragma. #f2dcdc 2613 code active 2007 Sep anonymous 2008 Jan drh 3 3 replace doesn't work with blobs containing \x0, otherwise it does The replace expression function does not work with blobs in case of contained zero terminator character; but it does if there is not this special character included. I expected the function to work similar like substr with blob-safety in case of type is blob only. X'nnnn' is of type blob, so following example should have returned a blob type result X'0102FF0405' in the 2nd and 3rd line. How to get to this result?
SQLite version 3.4.2 Enter ".help" for instructions sqlite> select hex(replace(X'0102030405',X'03',X'FF')); 0102FF0405 sqlite> select hex(replace(X'0102000405',X'00',X'FF')); sqlite> select typeof(replace(X'0102000405',X'00',X'FF')); null sqlite>_2007-Sep-03 04:21:12 by anonymous:_ {linebreak} Replace was designed to work with strings. However, working with blobs would be an interesting extension. ---- _2007-Oct-18 06:13:10 by anonymous:_ {linebreak} I've seen a similar situation where I can't reliably store stings with nulls in the middle of them as TEXT. I can convert them to blobs, in which case length(...) works correctly. I if convert them back to strings, length(...) treats them as C-strings. Is this the expected behavior? I notice the entire column is preserved even when it's has TEXT affinity, I can append data to it as a string, cast back to a blob and see everything (am I explaining this poorly?) This all seems a bit counter intuitive in some ways. Perhaps strings shouldn't treat NULL characters as special? ---- _2007-Oct-27 16:45:41 by anonymous:_ {linebreak} Treatment of length operator is - as fas as I know - dependent on type: {linebreak} As text it is the length number of UTF-8 characters and as blob it is the number of bytes. As long as all the UTF-8 characters out of the lower half ASCII char-set (127 of them), this is identical beside the fact of different 0-terminator interpretation. {linebreak} To append is something different than using the replace operator. My suggestion would be to make the replace operator work with bytes (not UTF-8) in case of all 3 parameters are of type blob. {linebreak} Another suggestion: the UTF aware functions are Private declared and not usable from within a loadable extension dll/so. This should be changed. ---- _2008-Jan-28 19:36:39 by anonymous:_ {linebreak} Will there come a solution for this with the next release? It is really not fair to handle a blob only like text which cannot contain a zero terminator. With this unique useful function a zero-containing blob could be formed into a normal text string without loosing the part behind the zero terminator. It would be really a step forward without too much effort. #f2dcdc 2907 code active 2008 Jan anonymous 2008 Jan 1 1 Issues of sqlite3 with Windows Mobile 5/6 hi. we are currently using sqlite3 for our mobile application. it has been running without a hitch on pocket pc 2003 and previous versions. come windows mobile 5 and 6 we have been getting errors, although not consistent yet. one example is 'EXCEPTION_DATATYPE_MISALIGNMENT'. another is 'SELECT STATMENTS TO THE LEFT AND RIGHT OF UNION ARE NOT EQUAL'. i was wondering if you have any known compatibility issues of your product with this version of windows mobile. thanks in advance. _2008-Jan-28 13:26:26 by anonymous:_ {linebreak} EXCEPTION_DATATYPE_MISALIGNMENT is thrown when you try to use and Odd pointer address. I wrote a custom allocator for WinCE/ARM platform, and I have to take care about memory alignment (I used to align at 2 bytes, and at that time it solved the problem) #f2dcdc 2908 code active 2008 Jan anonymous 2008 Jan 3 3 Add support to examine whether statements modify the database Currently there is no way to check whether a compiled statement will modify the database when being executed. Of course, there is the work-around of misusing the authorizer callback for this purpose, but this is kinda error prone and causes quite some overhead for such a simple purpose. #f2dcdc 2898 code active 2008 Jan anonymous 2008 Jan 1 1 Latest CVS for 3.5.4 fails to build test1.c gcc -pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -I. -I../src -DNDEBUG -I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -DTCLSH=1 -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 -DSQLITE_NO_SYNC=1 -DTEMP_STORE=1 -o .libs/testfixture ../src/attach.c ../src/btree.c ../src/build.c ../src/date.c ../src/expr.c ../src/func.c ../src/insert.c ../src/malloc.c ../src/os.c ../src/os_os2.c ../src/os_unix.c ../src/os_win.c ../src/pager.c ../src/pragma.c ../src/prepare.c ../src/printf.c ../src/select.c ../src/test1.c ../src/test2.c ../src/test3.c ../src/test4.c ../src/test5.c ../src/test6.c ../src/test7.c ../src/test8.c ../src/test9.c ../src/test_autoext.c ../src/test_async.c ../src/test_btree.c ../src/test_config.c ../src/test_hexio.c ../src/test_malloc.c ../src/test_md5.c ../src/test_onefile.c ../src/test_schema.c ../src/test_server.c ../src/test_tclvar.c ../src/test_thread.c ../src/tokenize.c ../src/utf.c ../src/util.c ../src/vdbe.c ../src/vdbeapi.c ../src/vdbeaux.c ../src/vdbemem.c ../src/where.c parse.c ../src/tclsqlite.c ./.libs/libsqlite3.so -L/usr/lib64 -ltcl8.4 -ldl -lpthread -lieee -lm -Wl,--rpath -Wl,/common/pkgs/sqlite-3.5.4.3/lib ../src/build.c: In function 'sqlite3RefillIndex': ../src/build.c:2275: warning: cast to pointer from integer of different size ../src/func.c: In function 'trimFunc': ../src/func.c:919: warning: cast from pointer to integer of different size ../src/func.c: In function 'sqlite3RegisterBuiltinFunctions': ../src/func.c:1464: warning: cast to pointer from integer of different size ../src/func.c:1483: warning: cast to pointer from integer of different size ../src/insert.c: In function 'sqlite3GenerateConstraintChecks': ../src/insert.c:1200: warning: cast to pointer from integer of different size ../src/insert.c:1034: warning: 'j2' may be used uninitialized in this function ../src/insert.c: In function 'sqlite3Insert': ../src/insert.c:373: warning: 'regFromSelect' may be used uninitialized in this function ../src/test1.c: In function 'test_collate_func': ../src/test1.c:2085: warning: cast from pointer to integer of different size ../src/test1.c: In function 'test_collate_needed_cb': ../src/test1.c:2209: warning: cast to pointer from integer of different size ../src/test1.c: In function 'alignmentCollFunc': ../src/test1.c:2258: warning: cast from pointer to integer of different size ../src/test1.c:2259: warning: cast from pointer to integer of different size ../src/test8.c: In function 'echoBestIndex': ../src/test8.c:722: warning: 'nRow' may be used uninitialized in this function ../src/vdbe.c: In function 'sqlite3VdbeExec': ../src/vdbe.c:502: warning: 'pOut' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn3' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn2' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn1' may be used uninitialized in this function ../src/vdbeaux.c: In function 'sqlite3VdbeChangeP4': ../src/vdbeaux.c:529: warning: cast from pointer to integer of different size ../src/vdbemem.c: In function 'sqlite3ValueText': ../src/vdbemem.c:911: warning: cast from pointer to integer of different size /tmp/ccsuOeus.o: In function `reset_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4280: undefined reference to `sqlite3ResetPrngState' /tmp/ccsuOeus.o: In function `restore_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4267: undefined reference to `sqlite3RestorePrngState' /tmp/ccsuOeus.o: In function `save_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4254: undefined reference to `sqlite3SavePrngState' collect2: ld returned 1 exit status make: *** [testfixture] Error 1 _2008-Jan-17 23:54:58 by anonymous:_ {linebreak} Problem appears to be here in libsqlite.3.so.0.8.6 as shown by: nm -A .libs/libsqlite3.so.0.8.6 | grep sqlite3ResetPrngState which shows no entry point. And: nm -A .libs/random.o | grep sqlite3ResetPrngState which also shows no entry point. ---- _2008-Jan-17 23:56:55 by anonymous:_ {linebreak} Ah... It appears -DSQLITE_TEST should be passed when building test1.c and left off when building prior to install. ---- _2008-Jan-21 20:16:00 by anonymous:_ {linebreak} In the makefile the right flag appears to be set, it's just not making it through to the compile for some reason. ---- _2008-Jan-21 20:16:24 by anonymous:_ {linebreak} Still fails the same based on today's cvs update. ---- _2008-Jan-23 03:14:49 by anonymous:_ {linebreak} This bug fixed as of latest cvs pull #f2dcdc 2901 code active 2008 Jan anonymous 2008 Jan 3 3 ROLLBACK and COMMIT statements should not expire Currently, whenever a statement changes the schema of the database, all prepared statements will be expired, no matter whether they actually need to be prepared again or not. This is especially problematic for ROLLBACK statements in a multi-statement transaction. Currently there is no way to guaranty that a multi-statement transaction can at least be rolled back in case of an error, because one has to (re)prepare the ROLLBACK statement to roll back the transaction, which can fail because of OOM (in a multi-threaded application). #e8e8bd 2506 new active 2007 Jul anonymous 2008 Jan 3 2 New API to retrieve ROWID from SQLite3_stmt structire Is it too much trouble to allow an API to retrieve ROWID for non-aggeregate queries directly from SQLite3_stmt structire? It would be very useful to create updatable non aggregate query results for situations when actually internal PK (ROWID) is not gived explicitly in SQL statement nor actual table's PK (if any). SELECT queries that join two or more tables together would be a problem also. ---- _2007-Jul-16 16:51:18 by anonymous:_ {linebreak} It's more of a multi-step process. First you have to enumerate the open cursors on the sqlite3_stmt object. Then you need to resolve the table each cursor goes to, and then fetch the rowid for each active cursor. Of course this may get confusing when you've joined a table onto itself. ---- _2008-Jan-19 10:32:59 by anonymous:_ {linebreak} This is far to old active ticket. Is it in consideration to be implemented in the near future? #f2dcdc 2899 code active 2008 Jan anonymous 2008 Jan 4 4 sqlite3_reset() after exec() takes > 100 ms to complete I'm not entirely sure whether this is a bug or not... I'm not familiar enough with SQLite to know if this is way too unconventional, but I noticed today that running exec() in conjunction with a prepared statement really kills the performance of sqlite3_reset(), if it's called after exec(): // init sqlite3_prepare_v2( db,{linebreak} "SELECT [file_id],[file_name],[file_mime],[file_type],"{linebreak} "[file_size],[date_created],[date_accessed],[data] "{linebreak} "FROM file_cache WHERE [file_id] = ? LIMIT 1;",{linebreak} -1, &sqQueryStatement, &unused ); // query function -- called repeatedly (usually second or third run starts to cause big delays) /* begin function */ sqlite3_reset( sqQueryStatement );{linebreak} sqlite3_bind_int( sqQuerystatement, 1, 292 );{linebreak} sqlite3_step( sqQueryStatement );{linebreak} bla = sqlite3_column_int( sqQueryStatement, 0 ); /* ... */ char *erm; sqlite3_exec( db, "UPDATE file_cache SET [date_accessed] = DATETIME( 'NOW', 'LOCALTIME' ) WHERE [file_id] = 292", NULL, NULL, &erm ); /* ... */ sqlite3_clear_bindings( sqQueryStatement ); /* end function */ If sqlite3_exec() is commented out, consecutive calls to the function run in less than a millisecond. With sqlite3_exec() included, sqlite3_reset() call in this function takes > 100 ms to complete. Tested: Windows Vista Ultimate 32 bit, Visual Studio 2005 8.0.50727.876 #f2dcdc 2885 code active 2008 Jan anonymous 2008 Jan 4 4 (minor) fulltest failures Minor noise in fulltest on a 64-bit machine (everything seems to work otherwise though): 4 errors out of 61527 tests Failures on these tests: exclusive-ioerr-2.280.4 exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4 incrvacuum-ioerr-1.31.4 All memory allocations freed - no leaks details: exclusive-ioerr-2.280.3... Ok exclusive-ioerr-2.280.4... Expected: [8ee59fe0b5bc391ecc5002539379b063] Got: [35e56178ad878809d4789c5797265a23] exclusive-ioerr-2.281.1... Ok exclusive-ioerr-2.281.2... Ok exclusive-ioerr-2.281.3... Ok exclusive-ioerr-2.281.4... Expected: [95762fb35ef83ddba65f681325346ef2] Got: [1c20c63d975ad85b395a5e7701d785c2] exclusive-ioerr-2.282.1... Ok exclusive-ioerr-2.282.2... Ok exclusive-ioerr-2.282.3... Ok exclusive-ioerr-2.282.4... Expected: [2c5ea7db8424f38d17cdff41056da0e0] Got: [c02841c40d3e6e0579922745bd9c0260] exclusive-ioerr-2.283.1... Ok [...] incrvacuum-ioerr-1.31.4... Expected: [ea55042a449c3b4759730a882e8271a0] Got: [9951814984696d0811cacbe862060af8] incrvacuum-ioerr-1.32.1... Ok _2008-Jan-19 00:56:30 by anonymous:_ {linebreak} A test from 20 minutes ago passes cleanly. This could be closed. #f2dcdc 2878 code active 2008 Jan anonymous 2008 Jan 1 1 Memory leaks with latest CVS [4693] This SQL leaks memory with CVS [4693]: CREATE TABLE x(id integer primary key, a TEXT NULL); INSERT INTO x (a) VALUES ('first'); CREATE TABLE tempx(id integer primary key, a TEXT NULL); INSERT INTO tempx (a) VALUES ('t-first'); CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id; One leak is caused by "CREATE TABLE tempx", a second one by "CREATE VIEW tv1". The above SQL is a digest of select7.test, select7-2.1. _2008-Jan-14 17:51:11 by anonymous:_ {linebreak} I have tested again with CVS [4711] and it does no longer show the original leaks. I therefore consider this issue fixed and I will now close this ticket. ---- _2008-Jan-14 23:56:34 by anonymous:_ {linebreak} Doing a fulltest with -MSQLITE_MEMDEUG I see reports of memory leaks. I assume these are of little or minimal interest at present because of the amount of code flux. If you do want details, please let me know (I'll recheck this ticket tomorrow I guess). ---- _2008-Jan-15 08:23:56 by anonymous:_ {linebreak} Thanks for the follow-up. I am not running the original test-suite but have have ported a great number of them to Delphi. If you could just let me know which tests caused the leaks you fixed in [4712] I'd be more than glad the port these test as well and let you know my findings. ---- _2008-Jan-19 00:56:17 by anonymous:_ {linebreak} A test from 20 minutes ago passes cleanly. This could be closed. #f2dcdc 2897 code active 2008 Jan anonymous 2008 Jan 1 1 String or BLOB exceed size limit This error was shown after attemp to read script from SQLite 3.5.4 shell in order to recreate old DB. Details: 1. Database was created with SQLite 3.3.4. Around 20 standard fieds and one BLOB. 2. The only one existed table was dumped with shell of SQLite 3.5.4. SQL script seems to be coorrect. 3. Opened SQLite 3.5.4 and read script in new DB. The error "String or BLOB exceed size limit" are sown for several lines. Many records missing. 4. Attempted to dump table with shell of version 3.3.6 (have no more 3.3.4 shell) and read into new DB with 3.5.4 shell The same errors are shown. The same steps was attempted with 3.3.6. shell only. All seems to be correct. _2008-Jan-17 20:23:25 by drh:_ {linebreak} This size limit on BLOBs in SQLite version 3.5.4 is 1GB. How big is your blob, exactly? ---- _2008-Jan-17 22:22:24 by anonymous:_ {linebreak} BLOB in each record is no more than few MB. Mostly it is few KB (e-client and news application). Whole DB have around 200MB. ---- _2008-Jan-18 02:28:11 by drh:_ {linebreak} This issue is probably resolved by check-in [4636], then. ---- _2008-Jan-18 14:28:13 by anonymous:_ {linebreak} If directive SQLITE_MAX_SQL_LENGTH is not defined it is set to 1,000,000 (10^6) in amalgamation code of 3.5.4. #e8e8bd 2892 new active 2008 Jan anonymous 2008 Jan 5 5 There should be a way in the api to read more precise error message. Currently all errors in sql queries issued via C,C++ api result in error code SQLITE_ERROR = 1 and message "SQL error or missing database". That leaves user completely clueless what mistake in his sql he actually made. Some evidence of confusion can be found here: http://bugs.php.net/bug.php?id=33117 _2008-Jan-16 17:41:00 by drh:_ {linebreak} SQLite gives detailed error information for SQL syntax or logic errors. (Try, for example, entering invalid SQL into the CLI.) I think perhaps that PHP is simply failing to to access those errors and is instead picking up some other error indication from someplace else. ---- _2008-Jan-16 21:59:17 by anonymous:_ {linebreak} Well I shall retest it, but I got the same error message upon a duplicate key in Delphi. Although I'm aware of an enhanced api to get the errormessage. ---- _2008-Jan-17 00:05:58 by drh:_ {linebreak} What error message do you get from the CLI? #f2dcdc 2893 code active 2008 Jan anonymous 2008 Jan 1 1 incorrect integer range tests recently a function that performs integer range tests was added to the cvs (check-in [4706]), but if i am correct there is a problem in the return value of the function in the file vdbemem.c: static i64 doubleToInt64(double r){ ... if( r<(double)minInt ){ return minInt; }else if( r>(double)maxInt ){ return minInt; <-- is this correct, shouldn't it be maxInt? }else{ return (i64)r; } } _2008-Jan-16 17:33:56 by drh:_ {linebreak} See the remarks on ticket #2280. The code duplicates the behavior of the FPU on x86. ---- _2008-Jan-16 18:21:28 by anonymous:_ {linebreak} did you mean ticket #2880? didn't read that ticket before, but since there was no comment regarding that behavior in the function it seemed (to my eyes) that it was a mistake. maybe adding a small comment in there would clarify this issue ---- _2008-Jan-16 18:39:42 by anonymous:_ {linebreak} Just because the double to int overflow behavior happens to be that way with GCC on x86, is it desirable? #f2dcdc 2886 code active 2008 Jan anonymous 2008 Jan 3 3 testfixture: -fPIC needed when building extension(s) (this fix/change is probably needed in older versions too, i meant to send this in earlier) -fPIC is needed when building extensions (some platforms don't need this or don't care --- x86-64 does) diff --git a/test/loadext.test b/test/loadext.test index 81e152f..2a7fa2e 100644 --- a/test/loadext.test +++ b/test/loadext.test @@ -64,7 +64,7 @@ if {![file exists $testextension]} { set srcdir [file dir $testdir]/src set testextsrc $srcdir/test_loadext.c if {[catch { - exec gcc -Wall -I$srcdir -I. -g -shared $testextsrc -o $testextension + exec gcc -Wall -fPIC -I$srcdir -I. -g -shared $testextsrc -o $testextension } msg]} { puts "Skipping loadext tests: Test extension not built..." puts $msg #f2dcdc 2882 code active 2008 Jan anonymous 2008 Jan 3 3 fulltest failure: ./testfixture: wrong # args: should be "cksum db" exclusive-ioerr-2.2.1... Ok ./testfixture: wrong # args: should be "cksum db" while executing "ifcapable vacuum { do_ioerr_test ioerr-2 -cksum true -sqlprep { BEGIN; CREATE TABLE t1(a, b, c); INSERT INTO t1 VALUES(1, randstr(50,..." (file "../test/ioerr.test" line 58) invoked from within "source $testdir/ioerr.test" (file "../test/exclusive3.test" line 50) invoked from within "source $testfile" ("foreach" body line 5) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue ..." ("for" body line 7) invoked from within "for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} { if {$Counter%2} { set ::SETUP_SQL {PRAGMA default_synchronous=off;} } else ..." (file "..//test/all.test" line 85) _2008-Jan-14 23:25:49 by anonymous:_ {linebreak} The latest code seems to have fixed this. I would close this but I don't see how to do that. #e8e8bd 2884 new active 2008 Jan anonymous 2008 Jan 4 4 Way to find out limits The page http://www.sqlite.org/limits.html shows some limits that SQLite has. They are consts in the code, and defaults are listed, but there doesn't seem to be any way to find out what values a particular binary was built with. For example, I've gotten SQLite as part of my OS or another program, and I don't know what value of SQLITE_MAX_COLUMN was used to compile this SQLite. I'm trying to track down a bug in my code, and if this value was too small, that would explain it. (In truth, it's probably not the cause, but I'd like to rule it out.) I'd like a way to print the value of these limits at runtime. It doesn't have to have a stable API -- it would mostly be useful for debugging. If the command-line client had a command ".limits" that printed all of these values, that would be super. #e8e8bd 489 new active 2003 Nov anonymous 2008 Jan 4 4 DLL exports suggestion Just a suggestion: I'm building SQLite using MS C++, and an easily maintained alternative to a .def file for the DLL exports is to incorporate the following into the sqlite.h header... ---- #ifdef _MSC_VER #ifdef SQLITE_EXPORTS #define SQLITE_API __declspec(dllexport) #else #define SQLITE_API __declspec(dllimport) #endif #else #define SQLITE_API #endif // example function declaration SQLITE_API void sqlite_close(sqlite *); ---- SQLITE_EXPORTS is defined when building the library, but not when building client applications. I don't know if other compilers have similar methods of defining library exports, but if so, this could possibly be extended to support them. _2008-Jan-11 01:58:27 by anonymous:_ {linebreak} There is a good reason for adding this feature in some form other than merely avoiding the manual import library creation step. When __declspec(dllimport) is used, it is a hint to MSVC++ to produce more efficient code. This allows the function in the DLL to be called in a single call. Otherwise there is an extra jmp (2 in Debug mode). So those that want maximum performance out of C and DLL combo should take note. I patched this into my copy of the source. But the code given in the ticket assumes that the library is used as a DLL and not statically linked in which case dllimport shouldn't be used. -- Bz ---- _2008-Jan-11 13:28:27 by drh:_ {linebreak} The prefix SQLITE_API appears in front of all interfaces in the {link: /cvstrac/wiki?p=TheAmalgamation amalgamation}. So it seems like this problem could be solved by adding -DSQLITE_API=__declspec(dllexport) to the compiler command line. No? ---- _2008-Jan-12 07:16:44 by anonymous:_ {linebreak} Yes -DSQLITE_API will do, and also it is necessary to define SQLITE_EXTERN because some data symbols: sqlite3_version[] sqlite3_temp_directory sqlite3_io_trace are forward declared with SQLITE_EXTERN but defined with SQLITE_API and these must match. But like I mentioned, this is only half of the issue. The other half is the small performance boost from __declspec(dllimport). It would be nice to have SQLITE_API prefixes in sqlite3.h for users of the DLL. It's low priority, but I thought I would point this out. Another thing to note is that using __declspec(dllexport) instead of DEF file along with the (non default) stdcall convention (callee cleans up the stack) will result in function names in DLL export table being mangled with numeric suffixes. If one links through the auto generated import lib file, this isn't an issue, but it affects looking up a function name with GetProcAddress(). So the DEF file (the original intent of the ticket before I hijacked it) has a benefit in this case. But to use the stdcall convention properly there would have to be yet another prefix for the benefit of clients using the default cdecl convention: #define SQLITE_CALL #define SQLITE_CALL __stdcall SQLITE_API int SQLITE_CALL sqlite3_open( const char *filename, sqlite3 **ppDb ); The 2 prefixes is what MS does with their API functions. I assume there is some small performance boost from __stdcall. -- Bz ---- _2008-Jan-13 02:02:38 by sdwilsh:_ {linebreak} See also Ticket #2448 #e8e8bd 2881 build active 2008 Jan anonymous 2008 Jan 1 1 Latest sqlite-3.5.4 build fail on latest Fedora 2.6.23.12-52.fc7 Two test cases fail. io-4.1... Expected: [3] Got: [2] io-4.2.1... Ok io-4.2.2... Ok io-4.2.3... Expected: [3] Got: [2] io-4.3.1... Ok Let me know how to run individual test cases and how this might be fixed. Here's how I built sqlite using latest CVS. If something is wrong here, let me know and I'll rebuild/retest. I'm building on latest Fedora fc7. Thanks. _______ net1#uname -a Linux net1.coolsurf.com 2.6.23.12-52.fc7 #1 SMP Tue Dec 18 20:27:10 EST 2007 x86_64 x86_64 x86_64 GNU/Linux net1#build_sqlite mkdir -p /build/work/sqlite-3.5.4 cd /build/work/sqlite-3.5.4 unset CDPATH export CFLAGS='-pipe -O3 -g -DSQLITE_DISABLE_DIRSYNC=1 -Wall' rm -rf bld cvs -d :pserver:anonymous@www.sqlite.org:/sqlite -r update . mkdir bld cd bld ../configure --prefix=/common/pkgs/sqlite-3.5.4 --enable-tcl --with-tcl=/usr/lib64 --enable-threadsafe --enable-threads-override-locks make groupadd vuser || /bin/true useradd -M -g vuser -d /vhost/davidfavor.com/users/david -s /bin/zsh david || /bin/true useradd -M -g vuser -d /vhost/livefeast.com/users/yemiah -s /bin/zsh yemiah || /bin/true chown david:vuser -R .. su -c "make test" david _2008-Jan-11 17:18:52 by anonymous:_ {linebreak} Same tests still fail with CVS of today around 11AM CST. ---- _2008-Jan-11 17:41:55 by drh:_ {linebreak} FWIW, both those test cases pass on SuSE 10.1. I do not understand why they are failing on Fedora. But in any event, the tests in question are verifying logic that implements an optimization that is not used on Fedora, ever. So the failures are of no consequence. If those are the only two tests that fail, then you can safely use the build for whatever it is you are trying to do. ---- _2008-Jan-11 19:16:19 by anonymous:_ {linebreak} Failures when 'make fulltest' built with CFLAGS of '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -DSQLITE_MEMDEBUG' exclusive-malloc-1.transient.746...make: *** [fulltest] Segmentation fault Failures when 'make fulltest' built with CFLAGS of '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1' Skipping malloc tests: not compiled with -DSQLITE_MEMDEBUG... 6 errors out of 61998 tests Failures on these tests: exclusive-ioerr-2.280.4 exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4 incrvacuum-ioerr-1.31.4 io-4.1 io-4.2.3 All memory allocations freed - no leaks Maximum memory usage: 14376554 bytes Pre 3.5.x builds work fine on Fedora. If you're open to debugging all these, I'd like to go through and resolve all these one by one, so Fedora has a clean build/fulltest. Please let me know how to run each test individually and I'll try to figure out the problem with each. Thanks. #e8e8bd 2847 new active 2007 Dec anonymous 2008 Jan 5 4 Include major, minor, and patch version numbers in sqlite.h Hi, I'm working on a project where sqlite is being compiled into a DLL. Currently, sqlite.h makes the version number available as both an x.y.z string and an integer value in the form of x*1000000 + y*1000 + z. Unfortunately, neither of these options works particularly well when trying to create a resource file so that the DLL can display the proper version information within Windows. I've tried many different ways of disassembling the integer version number, but limitations in the resource compiler unfortunately prevent them from working. As a result, I've been forced for the time being to define SQLITE_VERSION_MAJOR, SQLITE_VERSION_MINOR, and SQLITE_VERSION_PATCH with manually-given values of x, y, and z respectively in order to accomplish this task. It would be really nice if these could be generated automatically for sqlite.h when running configure in the same way that VERSION and VERSION_NUMBER are so that setting the values manually wouldn't be required for the future. Would you be willing to do that? Thanks in advance. _2007-Dec-17 13:30:22 by anonymous:_ {linebreak} You could invoke this awk script in your make file:
# ## extrvers.awk ## Extract verison parts from sqlite3.h # ## Usage: # %GNU_AWK% -f extrvers.awk sqlite3.h >sqlite3.h.new # rm / del sqlite3.h # mv / ren sqlite3.h.new sqlite3.h # # ## Ignore any previous defines /^#define SQLITE_VERSION_(MAJOR|MINOR|PATCH)/{ next } ## generate extra #define MAJOR/MINOR/PATH lines /^#define[[:blank:]]+SQLITE_VERSION[[:blank:]]/{ split(substr($3,2,length($3) - 2),tmp,".") print "#define SQLITE_VERSION_MAJOR " tmp[1] print "#define SQLITE_VERSION_MINOR " tmp[2] print "#define SQLITE_VERSION_PATCH " tmp[3] } ## Repeat all other lines untouched { print }---- _2007-Dec-18 00:06:53 by anonymous:_ {linebreak} Original poster here. Thanks for the useful script! I'm assuming you're granting a license for this (or a modified version of it) to be included in the source tree if the powers that be are willing to accept it? This would be for the Mozilla project. ---- _2007-Dec-18 00:21:52 by drh:_ {linebreak} First off, I didn't post the script. I don't know who "anonymous" is. Secondly, if you are working for Mozilla, you will get *much* faster service if you identify yourself as such. ---- _2007-Dec-18 00:39:38 by anonymous:_ {linebreak} I just want to make sure I'm not running afoul of anybody by using their work without proper permission. To whoever posted it, you can contact me at ryanvm [at] gmail [dot] com. Thanks again for help! ---- _2007-Dec-26 19:06:27 by anonymous:_ {linebreak} I'm the poster of the script. Of course I don't mind it being used. For the peace of mind of anyone using it: please prepend the code with:
# Copyright (C) 2007 by Kees Nuyt, Rotterdam, Netherlands # The author of this code dedicates any and all copyright # interest in this code to the public domain. I make this # dedication for the benefit of the public at large and # to the detriment of my heirs and successors. I intend # this dedication to be an overt act of relinquishment in # perpetuity of all present and future rights to this # code under copyright law. #Cheers! "Kees Nuyt"
Index: configure =================================================================== RCS file: /sqlite/sqlite/configure,v retrieving revision 1.45 diff -u -3 -p -r1.45 configure --- configure 27 Nov 2007 14:50:07 -0000 1.45 +++ configure 5 Jan 2008 07:41:00 -0000 @@ -18520,9 +18520,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_UNIX=0 OS_WIN=0 OS_OS2=1 - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1" + CFLAGS="$CFLAGS -DOS_OS2=1" if test "$ac_compiler_gnu" == "yes" ; then - TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap" + CFLAGS="$CFLAGS -Zomf -Zexe -Zmap" BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe" fi else @@ -18530,14 +18530,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_WIN=1 OS_OS2=0 tclsubdir=win - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1" + CFLAGS="$CFLAGS -DOS_WIN=1" fi else OS_UNIX=1 OS_WIN=0 OS_OS2=0 tclsubdir=unix - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1" + CFLAGS="$CFLAGS -DOS_UNIX=1" fi @@ -19392,7 +19392,7 @@ fi echo "$as_me:$LINENO: result: $ac_cv_func_usleep" >&5 echo "${ECHO_T}$ac_cv_func_usleep" >&6 if test $ac_cv_func_usleep = yes; then - TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1" + CFLAGS="$CFLAGS -DHAVE_USLEEP=1" fi @@ -19491,7 +19491,7 @@ fi echo "$as_me:$LINENO: result: $ac_cv_func_fdatasync" >&5 echo "${ECHO_T}$ac_cv_func_fdatasync" >&6 if test $ac_cv_func_fdatasync = yes; then - TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1" + CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1" fi Index: configure.ac =================================================================== RCS file: /sqlite/sqlite/configure.ac,v retrieving revision 1.31 diff -u -3 -p -r1.31 configure.ac --- configure.ac 27 Nov 2007 14:50:07 -0000 1.31 +++ configure.ac 5 Jan 2008 07:41:00 -0000 @@ -310,9 +310,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_UNIX=0 OS_WIN=0 OS_OS2=1 - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1" + CFLAGS="$CFLAGS -DOS_OS2=1" if test "$ac_compiler_gnu" == "yes" ; then - TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap" + CFLAGS="$CFLAGS -Zomf -Zexe -Zmap" BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe" fi else @@ -320,14 +320,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_WIN=1 OS_OS2=0 tclsubdir=win - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1" + CFLAGS="$CFLAGS -DOS_WIN=1" fi else OS_UNIX=1 OS_WIN=0 OS_OS2=0 tclsubdir=unix - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1" + CFLAGS="$CFLAGS -DOS_UNIX=1" fi AC_SUBST(BUILD_EXEEXT) @@ -565,13 +565,13 @@ AC_SUBST(TARGET_DEBUG) ######### # Figure out whether or not we have a "usleep()" function. # -AC_CHECK_FUNC(usleep, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1"]) +AC_CHECK_FUNC(usleep, [CFLAGS="$CFLAGS -DHAVE_USLEEP=1"]) #-------------------------------------------------------------------- # Redefine fdatasync as fsync on systems that lack fdatasync #-------------------------------------------------------------------- -AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"]) +AC_CHECK_FUNC(fdatasync, [CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1"]) ######### # Generate the output files._2008-Jan-05 08:24:29 by anonymous:_ {linebreak} It appears that http://www.sqlite.org/sqlite3-3.5.4.bin.gz and http://www.sqlite.org/sqlite-3.5.4.so.gz use sleep and fsync even though usleep and fdatasync are available on Linux. On the Linux man page, it claims that fdatasync is more efficient than fsync: "Unfortunately, fsync() will always initiate two write operations: one for the newly written data and another one in order to update the modification time stored in the inode. If the modification time is not a part of the transaction concept fdatasync() can be used to avoid unnecessary inode disk write operations." #f2dcdc 2872 code active 2008 Jan anonymous 2008 Jan 4 4 Some table scan operations could use an index for better data density If a suitable index covers a column being during a table scan, it makes sense to use the index for IO not the table pages themselves for speed. As (contrived) example: CREATE TABLE t1 ( c1 blob, c2 integer, c3 integer ); CREATE INDEX i1 on t1(c2,c3); CREATE INDEX i2 on t1(c3); T1 populated with 4096 rows, c1 being 64K random blobs (to make c2, c3 access slower in this case), c2 and c3 being small random integers. Now: sqlite> select sum(c3) from t1; sum(c3) ---------- 519895 is very slow (several seconds). A table scan is done. Forcing use of an index, in a way that I know *all* rows will be included: sqlite> select sum(c3) from t1 where c2<1000; sum(c3) ---------- 519895 This is instantaneous. It seems to me that if a table scan has to be performance, it makes sense to grab the data from an index whenever possible. ideally the most densely packed index. (BTW; this is contrived, I know putting the blob as the last column will greatly speed things up). #e8e8bd 2869 new active 2008 Jan anonymous 2008 Jan 5 5 add "sqlite3_open16_v2" to the C API I'm using UTF-16, and if the database file does not exist, "sqlite3_open16" will create a new one, but i wish it fails in such conditions. I notice that there's a "sqlite3_open_v2", but it doesn't support UTF-16, although i can implement a "sqlite3_open16_v2" myself, I think it should exists in the offical releases. #f2dcdc 2867 code active 2008 Jan anonymous 2008 Jan 2 2 doesn't build on Cygwin - wrong sqlite3 exe suffix The new Makefile used $(EXE), which doesn't seem to be defined (typo?) _2008-Jan-02 11:12:39 by anonymous:_ {linebreak} Same on mingw: Following patch fixes things:
--- sqlite-3.5.4/Makefile.in Thu Dec 13 19:17:42 2007 +++ sqlite-3.5.4-mingw-fix/Makefile.in Wed Jan 2 11:37:50 2008 @@ -322,7 +322,7 @@ -o $@ $(TOP)/src/shell.c libsqlite3.la \ $(LIBREADLINE) $(TLIBS) -sqlite3$(EXE): $(TOP)/src/shell.c sqlite3.c sqlite3.h +sqlite3$(TEXE): $(TOP)/src/shell.c sqlite3.c sqlite3.h $(LTLINK) $(READLINE_FLAGS) -o $@ \ -DSQLITE_MAX_SQL_LENGTH=1000000000 \ -USQLITE_THREADSAFE -DSQLITE_THREADSAFE=0 \ @@ -577,7 +577,7 @@ -e 's,$$,\\n",' \ $(TOP)/tool/spaceanal.tcl >spaceanal_tcl.h $(LTLINK) -DTCLSH=2 -DSQLITE_TEST=1 $(TEMP_STORE)\ - -o sqlite3_analyzer$(EXE) $(TESTSRC) $(TOP)/src/tclsqlite.c \ + -o sqlite3_analyzer$(TEXE) $(TESTSRC) $(TOP)/src/tclsqlite.c \ libtclsqlite3.la $(LIBTCL)#e8e8bd 2866 build active 2008 Jan anonymous 2008 Jan 1 3 Problems building Windows native in cygwin/mingw environment Trying to build Windows native version using the Cygwin build environment. $ gcc -v Reading specs from /usr/lib/gcc/i686-pc-cygwin/3.4.4/specs Configured with: /usr/build/package/orig/test.respin/gcc-3.4.4-3/configure --verbose --prefix=/usr --exec-prefix=/usr --sysconfdir=/etc --libdir=/usr/lib --libexecdir=/usr/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-languages=c,ada,c++,d,f77,pascal,java,objc --enable-nls --without-included-gettext --enable-version-specific-runtime-libs --without-x --enable-libgcj --disable-java-awt --with-system-zlib --enable-interpreter --disable-libgcj-debug --enable-threads=posix --enable-java-gc=boehm --disable-win32-registry --enable-sjlj-exceptions --enable-hash-synchronization --enable-libstdcxx-debug Thread model: posix gcc version 3.4.4 (cygming special, gdc 0.12, using dmd 0.125) $ $ CFLAGS=-mno-cygwin ./configure --disable-tcl --enable-threadsafe $ make A) The make appears to build sqlite3.exe just fine, without errors or warnings. This binary does work from cmd.exe, BUT not from within the bash cygwin shell for some reason, unlike other Windows native binaries I've built. Next... $ make install B) The cc sqlite3.c -o sqlite3 fails to rebuild sqlite3.exe correctly with the -mno-cygwin option. The output follows: rm -rf tsrc mkdir -p tsrc cp ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/btmutex.c ./src/btree.c ./src/btree.h ./src/build.c ./src/callback.c ./src/complete.c ./src/date.c ./src/delete.c ./src/expr.c ./src/func.c ./src/hash.c ./src/hash.h ./src/insert.c ./src/journal.c ./src/legacy.c ./src/loadext.c ./src/main.c ./src/malloc.c ./src/mem1.c ./src/mem2.c ./src/mem3.c ./src/mutex.c ./src/mutex_os2.c ./src/mutex_unix.c ./src/mutex_w32.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pager.h ./src/parse.y ./src/pragma.c ./src/prepare.c ./src/printf.c ./src/random.c ./src/select.c ./src/shell.c ./src/sqlite.h.in ./src/sqliteInt.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c ./src/vdbefifo.c ./src/vdbemem.c ./src/vdbeInt.h ./src/vtab.c ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h ./ext/fts1/fts1_tokenizer1.c sqlite3.h ./src/btree.h ./src/btreeInt.h ./src/hash.h ./src/sqliteLimit.h ./src/mutex.h opcodes.h ./src/os.h ./src/os_common.h ./src/sqlite3ext.h ./src/sqliteInt.h ./src/vdbe.h parse.h ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_tokenizer.h ./src/vdbeInt.h tsrc cp: warning: source file `./src/btree.h' specified more than once cp: warning: source file `./src/hash.h' specified more than once cp: warning: source file `./src/sqliteInt.h' specified more than once cp: warning: source file `./src/vdbe.h' specified more than once cp: warning: source file `./ext/fts1/fts1.h' specified more than once cp: warning: source file `./ext/fts1/fts1_hash.h' specified more than once cp: warning: source file `./ext/fts1/fts1_tokenizer.h' specified more than once cp: warning: source file `./src/vdbeInt.h' specified more than once rm tsrc/sqlite.h.in tsrc/parse.y cp parse.c opcodes.c keywordhash.h tsrc tclsh ./tool/mksqlite3c.tcl cc sqlite3.c -o sqlite3 /usr/lib/gcc/i686-pc-cygwin/3.4.4/../../../libcygwin.a(libcmain.o):(.text+0xab): undefined reference to `_WinMain@16' collect2: ld returned 1 exit status make: *** [sqlite3] Error 1 $ #e8e8bd 2864 doc active 2007 Dec anonymous 2007 Dec 5 3 ext/fts3/README.txt File ext/fts3/README.txt reads: This folder contains source code to the second full-text search [...] Shouldn't that be: This folder contains source code to the third full-text search [...] _2007-Dec-30 18:27:08 by anonymous:_ {linebreak} Oh, after Googl'ing a little bit, I found that _fts3_ really is _fts2-with-rowid-fixed_. If both _fts2_ and _fts3_ are considered to be the _"second full-text search extension for SQLite"_, the _README_ files could maybe explain the situation. #f2dcdc 2865 code active 2007 Dec anonymous 2007 Dec 1 2 FTS3 does not build with amalgamation in CVS Grab the latest CVS sources, then run: ./configure make sqlite3.c grep sqlite3Fts3Init sqlite3.c extern int sqlite3Fts3Init(sqlite3*); rc = sqlite3Fts3Init(db); If you compile sqlite3.c with -DSQLITE_ENABLE_FTS3, then sqlite3Fts3Init is unresolved. For some reason, sqlite3Fts3Init and fts3.c was not included in the sqlite3.c amalg. It used to work correctly in 3.5.4. _2007-Dec-30 18:17:57 by anonymous:_ {linebreak} Nevermind, "make sqlite3.c" has never built with the fts3 sources in 3.5.4 or before. You have to run ext/fts3/mkfts3amal.tcl ---- _2007-Dec-30 18:20:56 by anonymous:_ {linebreak} It seems that the sqlite3+fts3 amalg can only be built from main.mk, not Makefile. #f2dcdc 2863 code active 2007 Dec anonymous 2007 Dec 2 3 test cast-3.14, cast-3.18 and cast-3.24 fail test cast-3.{14,18,24} fail on freebsd-6.3-PRERELEASE2: cast-3.14...^M Expected: [9223372036854774784]^M Got: [9223372036854773760]^M cast-3.18...^M Expected: [-9223372036854774784]^M Got: [-9223372036854773760]^M cast-3.24...^M Expected: [9223372036854774784]^M Got: [9223372036854773760]^M I used tcl8.4 from ports with no threads and here was the config line: ../sqlite-3.5.4/configure --prefix=/home/marc/local --with-tcl=/usr/local/lib/tcl8.4/ This was built on an ibm t30 laptop #e8e8bd 2860 todo active 2007 Dec anonymous 2007 Dec 3 1 Database file fragmentation Adding data in database file increases file fragmentation. for example my file which size is 1G, consists of 20000 pieces. (NTFS) This happens because truncation of '-journal' file. I see some ways to reduce fragmentaion: 1. Increase database file size by greater pieces (not by PAGESIZE). 2. SQLite can save '-journal' file in another folder(logical disc). 3. Preallocation of database file(must increase INSERT speed). #f2dcdc 2859 code active 2007 Dec anonymous 2007 Dec drh 3 2 Inconsistent column names with DISTINCT Given the following SQL:{linebreak} CREATE TABLE foo(a,b); INSERT INTO foo (a, b) VALUES (1,2); SQLite returns inconsistent column names when using the DISTINCT clause:{linebreak} SELECT DISTINCT foo.A, foo.B FROM foo; foo.A|foo.B 1|2 SELECT DISTINCT a, b FROM foo; a|b 1|2 SELECT DISTINCT * FROM foo; a|b 1|2 SELECT DISTINCT foo.* FROM foo; a|b 1|2 Compared with SELECT without DISTINCT:{linebreak} SELECT foo.A, foo.B FROM foo; a|b 1|2 SELECT a, b FROM foo; a|b 1|2 SELECT * FROM foo; a|b 1|2 SELECT foo.* FROM foo; a|b 1|2 #f2dcdc 2761 code active 2007 Nov anonymous 2007 Dec 3 3 CLI (shell.c) should be bundled with amalgamation The CLI (shell.c) should be bundled with the amalgamation for database administrative purposes without downloading the matching shell.c from the full source tree. I second that! Qt ships with the amalgamated source files, but we also ship shell.c, whch we have to retrieve from the non-amalgamated source files. ---- _2007-Dec-26 15:20:04 by anonymous:_ {linebreak} I also agree. It is inconvenient to retrieve the matching shell.c from the source tree. #f2dcdc 2857 code active 2007 Dec anonymous 2007 Dec 2 2 GROUP BY cost estimate wrong with WHERE clause There seems to be an issue with the sqlite cost heuristic with an INDEX present on GROUP BY with certain types of WHERE clauses. Given the database formed by running these statements: create table stuff(a,b,c,d); insert into stuff values(1,2,3,4); create temp view v1 as select random()%100, random()%100, random()%1000, random()%10000 from stuff x, stuff y; insert into stuff select * from v1; insert into stuff select * from v1; insert into stuff select * from v1; insert into stuff select * from v1; insert into stuff select * from v1; create index stuff_b on stuff(b); create index stuff_c on stuff(c); create index stuff_d on stuff(d); analyze; Using sqlite.org's sqlite3-3.5.4.bin, this query takes 47 seconds: select c from stuff where a=23 group by c; while this query takes just 2 seconds: select c from stuff where a=23 group by +c; It is more efficient in this case to do a full table scan instead of using the INDEX on column c. _2007-Dec-23 23:14:06 by anonymous:_ {linebreak} The queries above both run in a couple of seconds with this naive patch:
--- src/where.c 12 Dec 2007 17:42:53 -0000 1.266 +++ src/where.c 23 Dec 2007 22:48:37 -0000 @@ -1514,6 +1514,12 @@ static double bestIndex( flags = 0; } + if( pWC && pWC->nTerm>0 && pOrderBy ){ + /* Reduce cost if both an ORDER/GROUP BY exists with a WHERE. */ + cost /= 100; /* A very rough guess. */ + WHERETRACE(("... WHERE + ORDER BY decreases cost to: %.9g\n", cost)); + } + /* If the table scan does not satisfy the ORDER BY clause, increase ** the cost by NlogN to cover the expense of sorting. */ if( pOrderBy ){But it has not been tested on queries with more than one table. Its logic could be flawed. ---- _2007-Dec-24 00:09:00 by drh:_ {linebreak} The complaint is centered around these two queries: /* 1 */ SELECT c FROM stuff WHERE a=23 GROUP BY c; /* 2 */ SELECT c FROM stuff WHERE a=23 GROUP BY +c; Query 1 runs in about 40 seconds and query 2 in about 1.5 seconds on my macbook. But with the patch, both queries run in about 1.5 seconds. Fair enough. But now consider these two queries: /* 3 */ SELECT c FROM stuff WHERE a!=23 GROUP BY c; /* 4 */ SELECT c FROM stuff WHERE a!=23 GROUP BY +c; In this case, query 3 runs in 42 seconds on an unpatched version of 3.5.4 and query 4 runs in about 109 seconds. So in cases where the WHERE clause is not particularly selective, the first version is faster than the second by a good margin. On a patched version of 3.5.4, both queries 3 and 4 run in about 110 seconds. So it seems to me that the patch is robbing Peter to pay Paul. It makes ORDER BY queries with very selective WHERE clauses run faster but at the expense of making queries with unselective WHERE clauses running slower. But notice this: in the current (unpatched) implementation, the programmer at least has the ability to select a different algorithm by the judicious placement of a "+" sign. After the patch, this is no longer possible. The patch forces the second algorithm to be used in all cases, even cases where it is slower. It seems to me to be better to leave things as they are since the current approach at least allows the programmer to override SQLite's algorithm selection if SQLite chooses incorrectly. The only way, it seems to me, to automatically choose the correct algorithm is to devise some test that will determine (in advance) whether or not the WHERE clause weeds out many or few rows from the result set. I'm thinking that determination is going to be very hard (or impossible) to do without first doing a full table scan. ---- _2007-Dec-24 05:40:47 by anonymous:_ {linebreak} It think it would be surprising to average users that _adding_ an index (on column C in this case) may significantly _decrease_ query performance for some queries. It was surprising to me, at least. In my opinion, a query being 20 times slower in a default bad guess situation is worse than a query only being 2.5 times slower with a default bad guess in a worst case scenario. It's a question of relative magnitude of the difference. This is why I think that the database should err on the side of the WHERE clause having a more selective bias. (Side note: the query timings difference is less pronounced if you use PRAGMA temp_store=memory, in which case query 3 running on an unpatched 3.5.4 takes just 50% more time to run than query 4 on my machine.) But you raise a good point in that if there's a wrong guess in the selectivity bias it would be nice to be able to manually override it. How much do you hate this type of syntax that some other databases use? select c from stuff where a!=23 group by /*+stuff_c*/ c; SQLite does not currently offer a way to pick a specific index. I think it would be quite useful. ---- _2007-Dec-24 17:05:16 by anonymous:_ {linebreak} Another option is to collect WHERE clause statistics in a table like create table sqlite_stat2( where_clause_md5 BLOB primary key, where_clause TEXT, rows_examined INT, rows_true INT ); where the last 2 columns are cumulative for each query. The statistics option could be enabled/disabled via a PRAGMA sqlite_collect_statistics. The where_clause column could be a string generated fairly easily from the walking the parse tree of the resolved Select statement's pWhere. This way the where_clause is normalized and a single query with many subselects could generate more than 1 where_clause, and different queries that happen to use the same normalized where clause would update the same entry in the stat2 table. where_clause normalization would strip off aliases and only refer to the original table and column names. For example the 2 queries below: -- CREATE TABLE t1(a, b); -- CREATE TABLE t2(b, c); SELECT t1.a*c as AC, t2.b-a as BA FROM t1, t2 WHERE AC>BA; SELECT *, t1.a Foo FROM t2, t1 WHERE Foo*c > t2.b - t1.a; would generate the same normalized where_clause string "(T1.A*T2.C)>(T2.B-T1.A)". The table information is already encoded within it. The generated VDBE code would have to generate Mem counters that would be incremented by each WHERE test, and lazily updated at the end of transactions or periodically written to the stat2 table to minimize disk use, as this information is not critical. One could also manually set the stat2 table with statistical values they would like their queries to use even if PRAGMA sqlite_collect_statistics=off; Any time the schema is changed, the entire sqlite_stat2 table would be cleared. #e8e8bd 2856 doc active 2007 Dec anonymous 2007 Dec anonymous 4 3 SQLite Documentation - Tcl API - Link broken On the page "The Tcl Interface to the SQLite library", the link "enable load extension" does not work. #e8e8bd 1648 new active 2006 Jan anonymous Shell 2007 Dec 4 3 meaningful error message: constraint failed create table emp( id text unique, sex text check( sex in 'm' or sex in 'f' ); insert into emp values( '1','x' ); SQL error: constraint failed This error message could be better. If there are several constraints, which constraint failed? So I named the constraint create table emp( id text unique, sex text constraint chk_sex check( sex in 'm' or sex in 'f' ); insert into emp values( '1','x' ); SQL error: constraint failed Still no joy . . . It would be nice if the error message were more specific. _2006-Jan-30 16:22:58 by anonymous:_ {linebreak} actually my testing was better than my typing, I used: check (sex = 'm' or sex = 'f' ) ---- _2007-Oct-25 09:47:14 by anonymous:_ {linebreak} This is a really big deal for me and for many others I suspect. If this is not a priority, could you at least throw out some hints about implementing it? I browsed through the code but can't seem to find where this would even go. ---- _2007-Oct-25 10:10:36 by anonymous:_ {linebreak} Hm, ok, the check constraints are stored in the table structure as a single expression which is the AND of all of them. This alone suggests that the task at hand is not simple... ---- _2007-Oct-25 10:17:36 by anonymous:_ {linebreak} Perhaps a new Check type could be created which could basically be Expr plus an extra pointer, which could then be used to make a list of them, similar to how the triggers seem to be stored. I'll keep snooping around, but I thought I'd post what I've found thus far in case anyone else looks at this. ---- _2007-Oct-25 19:40:37 by anonymous:_ {linebreak} I have attached a patch that implements this. I've only tested it lightly by hand. (The test suite failed to run and gave me some strange linking errors) ---- _2007-Dec-20 11:38:03 by anonymous:_ {linebreak} Although this is tagged as shell, the error message comes from the sqlite core. My single biggest problem besides the lack of detail (some of my tables have 5 constraints) is that it also prevents me from localizing the error messages. If I have the constraint name then at least I can look it up in a translation table and tell non-english speakers something meaningful. #f2dcdc 2508 code active 2007 Jul anonymous 2007 Dec 1 1 utf8ToUnicode() does not work on some WinCE devices On some WinCE devices first call to =MultiByteToWideChar()= in =utf8ToUnicode()= always fails. Tried calling =GetLastError()= after it fails and it returns error code 87 -- =ERROR_INVALID_PARAMETER=. To fix this had to change code page from =CP_UTF8= to =CP_ACP= -- no idea why this works. Original =utf8ToUnicode()= ---- static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, NULL, 0); zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ){ return 0; } nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ){ sqliteFree(zWideFilename); zWideFilename = 0; } return zWideFilename; } ---- Fixed =utf8ToUnicode()= ---- static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("MultiByteToWideChar() failed, last error: %d\n", dwError); return 0; } zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ){ return 0; } nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ){ sqliteFree(zWideFilename); zWideFilename = 0; } return zWideFilename; } ---- _2007-Jul-17 23:56:10 by anonymous:_ {linebreak} =unicodeToUtf8()= needs to be fixed the same way. Before: ---- static char *unicodeToUtf8(const WCHAR *zWideFilename){ int nByte; char *zFilename; nByte = WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, 0, 0, 0, 0); zFilename = sqliteMalloc( nByte ); if( zFilename==0 ){ return 0; } nByte = WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, zFilename, nByte, 0, 0); if( nByte == 0 ){ sqliteFree(zFilename); zFilename = 0; } return zFilename; } ---- After: ---- static char *unicodeToUtf8(const WCHAR *zWideFilename){ int nByte; char *zFilename; nByte = WideCharToMultiByte(CP_ACP, 0, zWideFilename, -1, NULL, 0, NULL, NULL); if ( nByte == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("WideCharToMultiByte() failed, last error = %d\n", dwError); return 0; } zFilename = sqliteMalloc( nByte ); if( zFilename==0 ){ return 0; } nByte = WideCharToMultiByte(CP_ACP, 0, zWideFilename, -1, zFilename, nByte, 0, 0); if( nByte == 0 ){ sqliteFree(zFilename); zFilename = 0; } return zFilename; } ---- Note that while original code with =CP_UTF8= works on Windows and SOME WinCE devices, this modified code works well and Windows and all WinCE devices I've tested so far. ---- _2007-Jul-18 16:01:21 by anonymous:_ {linebreak} Why not using the conversions from SQLite internals ? It can change a UTF-16 to UTF-8 and vice-versa. Or using UTF-16 variants in windows ce should be the best case. ---- _2007-Aug-09 20:47:04 by anonymous:_ Why not using the conversions from SQLite internals ? It can change a UTF-16 to UTF-8 and vice-versa. Or using UTF-16 variants in windows ce should be the best case. Not so simple. =unicodeToUtf8()= is used a lot internally regardless of what whether you use UTF-16 or UTF-8 yourself. For example, =unicodeToUtf8()= is used by =sqlite3WinTempFileName()= which is in turn used by =sqlite3PagerOpentemp()= -- I think you get the idea. ---- _2007-Dec-20 00:29:33 by anonymous:_ {linebreak} We've found that using CP_UTF8 fails on WinCE kernels that don't include SYSGEN_CORELOC (http://msdn2.microsoft.com/en-us/library/ms903883.aspx). To make the code handle any device it should be changed to: static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("MultiByteToWideChar() failed, last error: %d\n", dwError); return 0; } } zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ) { return 0; } nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ) { nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ) { sqliteFree(zWideFilename); zWideFilename = 0; } } return zWideFilename; } #e8e8bd 558 build active 2004 Jan anonymous 2007 Dec 4 4 Makefile.in should honor libdir and bindir Please support non-standard installation layouts by honoring configure's --libdir and --bindir flags rather than hard-coding $(exec_prefix)/lib and $(exec_prefix)/bin. (For instance, the layout we often use on Solaris has parallel "lib" and "lib64" directories under a common prefix.) _2007-Dec-18 17:29:26 by anonymous:_ {linebreak} Why is this ticket not solved? The patch is trivial and solves a real problem. Thank you. ---- _2007-Dec-18 17:54:46 by drh:_ {linebreak} The patch does not apply to the current makefile. And I do not understand what the -libdir or -bindir options are for or what they are suppose to do so I do not know how to fix it. #f2dcdc 368 code active 2003 Jun anonymous 2007 Dec 3 4 UPDATE trigger doesn't fire on INSERT OR REPLACE After executing the following SQL, there will be nothing in table T2. I expect to see '1' there: CREATE TABLE T1 ( id, name ); CREATE TABLE T2 ( id ); CREATE TRIGGER T1A AFTER UPDATE ON T1 BEGIN INSERT INTO T2 VALUES( new.id ); END; INSERT INTO T1 VALUES (1, 'Hi'); INSERT INTO T1 VALUES (2, 'There'); INSERT OR REPLACE INTO T1 VALUES (1,'Me'); An INSERT trigger *does* fire on INSERT OR REPLACE if the item exists already -- I would expect an UPDATE trigger. ---- _2004-Sep-21 17:15:37 by anonymous:_ {linebreak} Still repros in 3.0.7 :-( ---- _2007-Dec-17 21:45:03 by anonymous:_ {linebreak} I would say that ON DELETE and ON INSERT better describes what really happens (and not ON UPDATE), because if there would be another columns in the '1' row, their values would not be preserved after INSERT OR REPLACE takes place, as the documentation of the ON REPLACE algorithm states: "When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row". However, neither ON UPDATE nor ON DELETE trigger occurs, which still is a bug. Thank you. #e8e8bd 916 new active 2004 Sep anonymous Unknown 2007 Dec 1 1 No delete notification for INSERT OR REPLACE It would be nice if the "ON DELETE" trigger is called for the row substituted with a new one during REPLACE. Or, even better, one could add the OLD statement for the "ON INSERT" trigger and set it to point to the same row as NEW if a new row is inserted or to the deleting row if replace occurs. Thanks. _2007-Dec-17 21:36:40 by anonymous:_ {linebreak} I have the same problem. My solution would be to stick with the documentation of the ON REPLACE algorithm: "When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row". That is, to call ON DELETE trigger whenever rows are removed. Thank you, and keep going, you do wonderful job anyway. #e8e8bd 2844 build active 2007 Dec anonymous 2007 Dec 4 1 lemon is being built without respecting LDFLAGS lemon is being built without respecting LDFLAGS. I'm attaching a patch which fixes this bug. In other words, why should we fix this? What problem is it causing? _2007-Dec-17 16:22:19 by drh:_ {linebreak} Why is this important? What LDFLAGS settings might a user want to carry through into lemon? ---- _2007-Dec-17 18:00:59 by anonymous:_ {linebreak} > Why is this important? It is considered to be be good practice to respect user's LDFLAGS. A user might want to have all executables and libraries built with identical LDFLAGS. > What LDFLAGS settings might a user want to carry through into lemon? A user might have LDFLAGS="-Wl,-O1,--hash-style=gnu,--sort-common" You can read http://lwn.net/Articles/192082/. Users can also use some other flags. > In other words, why should we fix this? What problem is it causing? It slightly increases the size of lemon executable and it slightly decreases performance. ---- _2007-Dec-17 18:04:31 by drh:_ {linebreak} lemon is used as an intermediate build tool in part of the SQLite build process. It is not a deliverable. If it runs a little slower or uses a little more memory, nobody cares. We only care if it gets the wrong answer. Is it ever possible that the lack of LDFLAGS support might result in lemon getting the wrong answer? ---- _2007-Dec-17 18:27:33 by anonymous:_ {linebreak} Can you comment on Lemon bug in #2835? It produces 2 different sqlite3.c files depending on your malloc implementation. ---- _2007-Dec-17 19:19:01 by anonymous:_ {linebreak} > lemon is used as an intermediate build tool in part of the > SQLite build process. It is not a deliverable. If it runs a > little slower or uses a little more memory, nobody cares. CFLAGS are respected when lemon is being built, so for consistency LDFLAGS also should be respected. (The comment above was not created by me.) #f2dcdc 2842 code active 2007 Dec anonymous 2007 Dec 1 1 .import does not recongnise NULL values .import function fails to see NULL values in csv files as NULL values...instead they are treated as the string "NULL". This is with .mode list and separator , But behaves similarly for .mode csv Also if one outputs a table with NULL values to a file, then re-imports that file, again .import does not recognise the values as NULL, but as "NULL". Everything here also applies to empty strings in files, e.g. instead of "NULL" using nothing... This is a showstopper for us since we want to import a large amount of data with many tables containing NULL values. I can't see any valid reason for .import not to recognise the same syntax as the command line. Note that something like: sqlite3 my.db insert into MY_TABLE values (1,"foo","bar",NULL) ..works fine. It is just .import that appears to be broken. _2007-Dec-14 16:39:51 by rdc:_ {linebreak} .import only inserts string values into database tables. If your column has a declared type that changes the columns affinity to numeric or integer, then those strings will be converted to numeric values by the SQLIte library. The workaround is to simply insert a unique string where ever you want a NULL value, and then run an update that replaces those strings with real NULL values. If you inserted the string 'NULL' then do this after the .import update t set field = null where field = 'NULL'; You will have to repeat this for each field in your table that might contain the 'NULL' string. #e8e8bd 2841 todo active 2007 Dec anonymous 2007 Dec 1 1 The sqlite mailing list has become overrun by trolls The sqlite mailing list is very useful. The S/N is at times a little high but nonetheless quite manageable. Recently (see the DeviceSQL thread) it got really bad. Would moderation be unacceptable during these periods of time where people feel the need to protect their ego's? The sqlite mailing list is primarily about sqlite (well, and lemon), not a marketing vector for other products? Surely they have their own lists and resources for that? #f2dcdc 2721 code active 2007 Oct anonymous 2007 Dec 2 1 if db file is in a folder with non-ansi character some functions fail If database file is located in directory with some non-ANSI characters (in my case with a Russian subdirectory c:\Мои документы\Data_Jobs), or it's name is non-ansi. Some functions fail to execute sql. For example (with defined UNICODE):
TCHAR sql[512]; _stprintf(sql, _T("INSERT INTO tab_SurveyedPoints (name, comment, code,") _T("coordinatetype, b, l, h, solutiontype, sigmah, sigmav)") _T(" VALUES ('%s','%s','%s',0,%lf,%lf,%lf,0,%lf,%lf);"), point.m_name.c_str(), point.m_description.c_str(), point.m_code.c_str(), point.m_coordinates.b, point.m_coordinates.l, point.m_coordinates.h, point.m_sigmah, point.m_sigmav); int rc1 = sqlite3_prepare16(m_db, sqlfmt, -1, &stmt, (const void**)&pszTail); rc != SQLITE_OKBut if I move the file to c:\My documents\Data_Jobs this works ok. It's improbable behaviour, but I can't work around yet. Although, prepare() functions work ok as well in both cases. Yuri Noyanov. _2007-Oct-11 19:33:34 by drh:_ {linebreak} All string arguments to SQLite, and especially filename arguments, must be UTF-8 or UTF-16 (depending on the function). If you use string parameters which are not UTF-8 or UTF-16 (as appropriate) then the behavior of SQLite is undefined and probably not what you want. ---- _2007-Oct-12 04:25:56 by anonymous:_ {linebreak} but ALL programs to handle SQLite DBs (SQLIteBrowser, SQLite Control) fail to handle the files as well. Till I move the file to different directory !!! ---- _2007-Oct-12 04:27:54 by anonymous:_ {linebreak} Also I must note, that I CAN open the database, I CAN execute some SQLs with sqlite_prepare function OK. But sqlite_prepare16 FAILS if I just rename my database !!! ---- _2007-Oct-12 04:31:46 by anonymous:_ {linebreak} Also note to make my issue clearer: sqlite_prepare16() with the same code either works OK either doesn't work. depends on database filename or folder path. The database is opened OK in both cases (I used utf8 conversion). sql_prepare() works ok in both cases. ---- _2007-Oct-13 06:37:43 by anonymous:_ {linebreak} That appears to be only with INSERT sql statement. Both SELECT and UPDATE work fine with sqlite_prepare16. #e8e8bd 2831 new active 2007 Dec anonymous 2007 Dec 3 4 alter view View can't be used after ALTER RENAME TO:
SQLite version 3.5.3 Enter ".help" for istructions sqlite> create table t(a); sqlite> create view v1 as select * from t; sqlite> alter table v1 rename to v2; sqlite> select * from v2; SQL error: no such table: v2 sqlite> select * from v1; SQL error: no such table: v1 sqlite> .schema CREATE TABLE t(a); CREATE VIEW v1 as select * from t; sqlite> select * from sqlite_master; table|t|t|2|CREATE TABLE t(a) view|v1|v1|0|CREATE VIEW v1 as select * from tThis is a feature request, not a bug. ---- _2007-Dec-11 18:40:17 by anonymous:_ {linebreak} Notice that alter table doesn't return an error. After the command neither v1 nor v2 can be used. ---- _2007-Dec-13 08:18:16 by danielk1977:_ {linebreak} [4623] improves the situation by returning an error when the user attempts to rename a view. One reason this feature (renaming views) is not a high priority is because a view can be dropped and recreated with a different name efficiently. This was not the case with tables. #f2dcdc 2825 code active 2007 Dec anonymous 2007 Dec 3 3 FormatMessage (win32) should use extra flag and convert from Unicode The call to FormatMessageA in the win32 source code needs to have the flags changed from: FORMAT_MESSAGE_FROM_SYSTEM to FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS This ensures that any system messages that expect arguments do not try to grab the argument from some random memory location. ref: http://blogs.msdn.com/oldnewthing/archive/2007/11/28/6564257.aspx _2007-Dec-06 14:07:53 by anonymous:_ {linebreak} I also noticed that the result is NOT converted to UTF-8. FormatMessageA returns the text in the local ANSI codepage. FormatMessageW should be used on NT systems, and either result should be converted to the SQLite UTF-8 default. ---- _2007-Dec-11 00:34:37 by anonymous:_ {linebreak} to simplify what is meant even more... http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/os_win.c&v=1.118 Search for FormatMessageA (only 1 instance) - FORMAT_MESSAGE_FROM_SYSTEM, + FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS, No breakage, ensures that no crashes with some messages (e.g. filesystem errors). The encoding issue should be addressed separately. ---- _2007-Dec-11 01:27:07 by anonymous:_ {linebreak} The function should be changed to the following to correctly handle the conversion from Unicode/MBCS.
static void winDlError(sqlite3_vfs *pVfs, int nBuf, char *zBufOut){ int error = GetLastError(); #if OS_WINCE if( error>0x7FFFFFF ){ sqlite3_snprintf(nBuf, zBufOut, "OsError 0x%x", error); }else{ sqlite3_snprintf(nBuf, zBufOut, "OsError %d", error); } #else if( isNT() ){ LPWSTR zWinTemp = NULL; DWORD dwLen = FormatMessageW( FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS, NULL, error, 0, (LPWSTR) &zWinTemp, 0, 0 ); if (dwLen > 0) { char * zOut = unicodeToUtf8(zWinTemp); LocalFree(zWinTemp); sqlite3_snprintf(nBuf, zBufOut, "%s", zOut); free(zOut); } }else{ LPSTR zWinTemp = NULL; DWORD dwLen = FormatMessageA( FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS, NULL, error, 0, (LPSTR) &zWinTemp, 0, 0 ); if (dwLen > 0) { char * zOut = mbcsToUtf8(zWinTemp); LocalFree(zWinTemp); sqlite3_snprintf(nBuf, zBufOut, "%s", zOut); free(zOut); } } #endif }#e8e8bd 2821 new active 2007 Dec anonymous 2007 Dec 3 4 hashtable indicies It would be nice to implement non btree indices. I.e. CREATE INDEX ON table(rowid) AS HASH. Using a hashtable's O(1) properties, you could use the index for very quick lookups when one result is expected. This does have the tradeoff that a hashtable index has no ordering properties (can not be used for sorts or non-equality searching). However, it would be a *huge* win when you have 250,000 rowids in memory, and you want to go fetch another column in the database for each one of those rowids (SELECT * FROM table WHERE rowid=?). _2007-Dec-03 21:58:01 by anonymous:_ {linebreak} For 250,000 rows I doubt you would see that much of an improvement (try it.) You'll almost certainly find log_n is going to be fairly fast (especially for large n.) I personally would prefer some sort of 'virtual' index though, that could be a hash or actually from a user-supplied function so that I can index large blobs by some function (i.e. a hash). And yes, this would be an incompatible file-format change and it's not clear how to update an index when the function isn't loaded (i.e. db reopened with that function.) Perhaps mark the index as 'stale' and ignore it until the function loads then you can do the updates. Of course this starts to get quite complicated. ---- _2007-Dec-03 22:12:17 by anonymous:_ {linebreak} Everything in sqlite depends on btree indexes. You're talking a major rewrite if you support hash-based or other indexing. #f2dcdc 2814 code active 2007 Nov anonymous 2007 Dec 3 3 _XOPEN_SOURCE again Ideally setting _XOPEN_SOURCE should be an opt-in detected by configure, rather than a hardcoded opt-out as it is now. I find you create more problems in setting it than just leaving it out on modern platforms. Can you please give users the option of not defining _XOPEN_SOURCE at all?
+#ifndef SQLITE_DONT_DEFINE_XOPEN_SOURCE #if !defined(_XOPEN_SOURCE) && !defined(__DARWIN__) && SQLITE_THREADSAFE # define _XOPEN_SOURCE 500 /* Needed to enable pthread recursive mutexes */ #endif +#endif_2007-Dec-01 09:23:15 by anonymous:_ {linebreak} Also when using Python, it sets _XOPEN_SOURCE to 600. No idea what the 500 vs 600 difference is about. ---- _2007-Dec-01 15:58:28 by anonymous:_ {linebreak} I've used a couple of different Linux OSes and _XOPEN_SOURCE is not needed. Maybe it's for OSes more than 5 years old. Recursive mutexes are pretty much standard these days since the popularity of Java which uses them extensively. ---- _2007-Dec-01 17:21:05 by drh:_ {linebreak} See also tickets #2673, #2681, and #2741. ---- _2007-Dec-02 02:08:26 by anonymous:_ {linebreak} On Linux, PTHREAD_MUTEX_RECURSIVE is the same as PTHREAD_MUTEX_RECURSIVE_NP: PTHREAD_MUTEX_RECURSIVE = PTHREAD_MUTEX_RECURSIVE_NP, Since PTHREAD_MUTEX_RECURSIVE_NP is always available, you could avoid defining _XOPEN_SOURCE and use this code instead:
- pthread_mutexattr_settype(&recursiveAttr, PTHREAD_MUTEX_RECURSIVE); + pthread_mutexattr_settype(&recursiveAttr, +#ifdef linux + PTHREAD_MUTEX_RECURSIVE_NP +#else + PTHREAD_MUTEX_RECURSIVE +#endif + );---- _2007-Dec-02 02:17:22 by anonymous:_ {linebreak} A quick google search reveals how various projects deal with this recursive mutex declaration problem (in no particular order): *: #define _XOPEN_SOURCE 500 and use PTHREAD_MUTEX_RECURSIVE *: #define _XOPEN_SOURCE 600 and use PTHREAD_MUTEX_RECURSIVE *: #define _GNU_SOURCE and use PTHREAD_MUTEX_RECURSIVE *: don't define anything and use PTHREAD_MUTEX_RECURSIVE_NP on linux, and PTHREAD_MUTEX_RECURSIVE elsewhere. Unfortunately, since PTHREAD_MUTEX_RECURSIVE is an enum on Linux, so you can't use the #ifdef PTHREAD_MUTEX_RECURSIVE compile-time technique. #e8e8bd 2813 build active 2007 Nov anonymous 2007 Nov 1 1 compile error on Windows CE environment: visual c++ 2005 window ce 6.0 customize sdk sqlite-amalgamation-3_5_3 I get error: Error 27 error C2040: 'localtime' : 'tm *(const time_t *)' differs in levels of indirection from 'int ()' d:\SubProjects\Sqlite\sqlite3.c 18574 but if I add code in line 7095: struct tm *__cdecl localtime(const time_t *t); then Success! #f2dcdc 2809 code active 2007 Nov anonymous 2007 Nov 1 1 PRAGMA collation_list shows unregistered collations As presented on the mailing list: Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Next issue "PRAGMA collation_list;" and notice that "unknown" lists next to the other registered collations, even though "unknown" is not registered with the default SQLite3 CLI: sqlite> PRAGMA collation_list; 0|unknown 1|NOCASE 2|BINARY Responses from the mailing list indicate that this is not the expected behaviour. "PRAGMA collation_list;" should list registered collations only. _2007-Nov-28 16:12:17 by anonymous:_ {linebreak} I don't think this is a bug. If the CLI is not aware of the collation, it should not process the query that makes use of the collation because it would certainly be wrong if it simply ignored the collation. This is not unlike a user-registered SQL function that does not exist in the CLI. I would not expect or want the sqlite3 CLI to ignore the unknown function, nor would I want the CLI to process queries ignoring the custom collation. #f2dcdc 2810 code active 2007 Nov anonymous 2007 Nov 1 1 Unregistered collation problems with simple subselects As discussed on the mailing-list: Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Simple queries like "SELECT * FROM a;" work fine. But subselects, in their most basic form and with no sorting or comparisons, result in an error: sqlite> INSERT INTO a VALUES ('one'); sqlite> SELECT * FROM a, (SELECT * FROM a); SQL error: no such collation sequence: unknown sqlite> SELECT * FROM (SELECT * FROM a); SQL error: no such collation sequence: unknown sqlite> SELECT *, * FROM a; one|one This is surprising because the collation sequence should not matter to the queries. In fact, the union without the subselect works just fine and without errors. To demonstrate, here is the explain output of a table with a registered collation sequence. No mention of the collation name here: sqlite> CREATE TABLE b (b collate nocase); sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b); 0|Goto|0|17| 1|Integer|0|0| 2|OpenRead|0|3| 3|SetNumColumns|0|1| 4|Integer|0|0| 5|OpenRead|2|3| 6|SetNumColumns|2|1| 7|Rewind|0|14| 8|Rewind|2|13| 9|Column|0|0| 10|Column|2|0| 11|Callback|2|0| 12|Next|2|9| 13|Next|0|8| 14|Close|0|0| 15|Close|2|0| 16|Halt|0|0| 17|Transaction|0|0| 18|VerifyCookie|0|4| 19|TableLock|0|3|b 20|Goto|0|1| 21|Noop|0|0| #e8e8bd 2808 doc active 2007 Nov anonymous 2007 Nov 4 2 Documentation GIF images take up too much space The GIF format used for the documentation images takes up too much space. I believe that this is in strict contrast to the "small" feature of SQLite. Converting the GIFs to PNG images saves up to 181 KB (64%) of the documentation storage space: Documentation images as is (mostly GIF): 381 KB == 100% Documentation images as PNG: 292 KB == 77% Documentation images as PNG 16 colors: 137 KB == 36% 16 colors are more than plenty -- the diagrams show no visibility degradation. You might even cut it down to 8 colors to save even more ... #f2dcdc 2223 code active 2007 Feb scouten 2007 Nov 3 3 pragma auto_vacuum doesn't survive .dump & reconstitute When you run sqlite3 path/to/database .dump, it does not contain pragma auto_vacuum even if that option was chosen when creating the source database. _2007-Feb-08 18:13:27 by scouten:_ {linebreak} We wonder if other pragmas are also not being propogated. ---- _2007-Feb-08 18:53:42 by anonymous:_ {linebreak} No pragmas are output from .dump. SQLite should have a .dump_with_pragmas command or equivalent. ---- _2007-Nov-27 02:11:05 by anonymous:_ {linebreak} auto_vacuum is especially important since you need to specify it *before* loading the tables in the dump; if you notice that it's missing after loading a dump, it's too late. I'd also appreciate user_version surviving. Would patches be welcome for these? I would be happy to contribute one: mail glasser@davidglasser.net. #f2dcdc 2793 code active 2007 Nov anonymous 2007 Nov 3 3 fts3 lacks scoping It would be nice if the fts3 symbols could optionally be made private/static as the rest of the sqlite3 library. Not sure why sqlite3_api becomes public when used with the amalgamation, for that matter. make TOP=`pwd` BCC=gcc TCC=gcc AR=ar RANLIB=echo NAWK=gawk -f \ main.mk sqlite3.h sqlite3.c fts3amal.c cat fts3amal.c >> sqlite3.c gcc -DSQLITE_THREADSAFE -DSQLITE_API=static -DSQLITE_PRIVATE=static \ -DSQLITE_EXTERN=static -DSQLITE_ENABLE_FTS3 -c sqlite3.c nm sqlite3.o | grep -v ' [trUbd] ' 00000004 C sqlite3_api 00064da2 T sqlite3Fts3HashClear 000652a4 T sqlite3Fts3HashFind 00064d60 T sqlite3Fts3HashInit 0006533b T sqlite3Fts3HashInsert 00064b4c T sqlite3Fts3Init 00066b34 T sqlite3Fts3InitHashTable 000669bd T sqlite3Fts3PorterTokenizerModule 0006702d T sqlite3Fts3SimpleTokenizerModule #e8e8bd 285 build active 2003 Apr anonymous Unknown 2007 Nov 2 2 Configure doesn't honour LDFLAGS during build Right now the configure script in the 2.8.0 tar.gz and CVS can't quite build a working SQLite when Fink is installed - it gets confused with finding readline. (I've not been able to find the right places to add these changes). * if LDFLAGS is set it is used during the check phase of configure but it isn't used during the build, ie. LDFLAGS doesn't get into the Makefile, this leads to the readline support being turned on but the libraries not being available at link time * paths searched for readline.h should include /sw #f2dcdc 2791 code active 2007 Nov anonymous 2007 Nov 1 1 Allow building FTS[123] as part of sqlite library with configure See attached patch. #e8e8bd 2787 build active 2007 Nov anonymous 2007 Nov 4 5 sqlite3.pc is not remade the subject says it all, there is no make rule to rebuild the sqlite3.pc from the .in file. it is only possible by hand (./config.status sqlite3.pc) _2007-Nov-22 12:17:50 by drh:_ {linebreak} I don't know what the sqlite3.pc file does. I certainly do not use it for any of my builds on Linux, Mac OSX, or windows. Why should I leave it in the source tree? Isn't the best solution to this problem to simply delete the file? ---- _2007-Nov-22 17:14:27 by anonymous:_ {linebreak} It is indirectly used by pkgconfig. Here's some info on pkgconfig: http://pkg-config.freedesktop.org/wiki/ ---- _2007-Nov-23 15:22:32 by drh:_ {linebreak} Could somebody who understands what the sqlite3.pc file is used for suggest a makefile rule for rebuilding it? #f2dcdc 2770 code active 2007 Nov anonymous 2007 Nov 1 1 Problem with BLOB in 3.5.x ? After I've switched from 3.3.18 to 3.5.2, selecting from table which contains BLOB LONGER THAN ABOUT 990 BYTES returns error "SQL logic error or missing database" after call to _sqlite3_step(). I'm using preprocessed sources downloaded from here. DEBUG build of preprocessed sources works correctly, problem is only in RELEASE build. I'm using VC6.0 to compile. Any idea what could be wrong? Thank you! Can you try to reproduce this with the sqlite shell tool? Thanks. Large blobs work for me with both release and debug builds (not msvc though, gcc/linux). ---- _2007-Nov-12 18:41:37 by anonymous:_ {linebreak} sqlite3.exe provided here works with the database. Problem is only with release build (static library linked into test application). Here is test app which exits with "Error 1" in release build: int main(int argc, char* argv[]) { int rc; sqlite3* db; sqlite3_stmt* stmt; rc = sqlite3_open("n2.db3", &db); rc = sqlite3_prepare(db, "CREATE TABLE [ttt] ([bbb] BLOB)", -1, &stmt, 0 ); rc = sqlite3_step(stmt); rc = sqlite3_reset(stmt); char text[10000],query[20000]; strnset(text,'a',sizeof(text)-1); sprintf(query,"insert into [ttt] values (?)"); rc = sqlite3_prepare(db, query, -1, &stmt, 0 ); rc = sqlite3_bind_blob(stmt,1,text,sizeof(text), SQLITE_TRANSIENT); rc = sqlite3_step(stmt); rc = sqlite3_reset(stmt); rc = sqlite3_prepare(db, "select * from ttt", -1, &stmt, 0 ); rc = sqlite3_step(stmt); if (rc == SQLITE_ROW) { printf("%s: OK",sqlite3_column_text(stmt,1)); } else if (rc == SQLITE_DONE) { printf("DONE"); } else { printf("Error %d",rc); } return 0; } ---- _2007-Nov-12 18:56:24 by drh:_ {linebreak} You should be using sqlite3_finalize() instead of sqlite3_reset(). You are leaking memory. Also, you should use sqlite3_prepare_v2() to avoid problems with changing schemas. But even without those fixes, I cannot reproduce the problem on Linux. ---- _2007-Nov-12 19:39:31 by anonymous:_ {linebreak} Suggested fixes didn't help. I've tried to debug it. It fails in btree.c, line 3056: if( offset+amt > nKey+pCur->info.nData ){ /* Trying to read or write past the end of the data is an error */ return SQLITE_ERROR; } there seems to be different values in release mode. My debugger does not show values of variables in release mode, so I can be wrong, but it seems in release offset is 5 and in debug it is 4. There can be something wrong with compilation, I'll try to figure this out tomorrow. BTW compilation of static libraty in VC6.0 gives 185 warnings. I don't know if it is ok, it haven't caused problems in older sqlite ---- _2007-Nov-13 08:47:28 by anonymous:_ {linebreak} I've turned off "Maximize Speed" option - this is causing the problem. No optimizations and optimize for size seems to be working. But it still makes me nervous :(( I really don't need corrupted database and now I hope it won't slow down too much. Unfortunately old library does not implement replace function so I don't want to switch back. This could be warning to others, I'm using VC++ 6.0 SP 6. Thank you for your time. ---- _2007-Nov-22 17:20:31 by anonymous:_ {linebreak} I have exactly the same problem here (win XP, vc6 SP2) when I link against my sqlite static or dynamic library in release. I have also used boundschecker to check sqlite, and it detects many dangling pointers ! But the strange thing is that I cannot find why these pointers are dangling, here an example: In prepare.c@188 pTab = sqlite3FindTable(db, zMasterName, db->aDb[iDb].zName); Boundchecker say that zMasterName is a dangling pointer, previously released here: in build.c@711: void sqlite3StartTable( Parse *pParse, /* Parser context */ Token *pName1, /* First part of the name of the table or view */ Token *pName2, /* Second part of the name of the table or view */ int isTemp, /* True if this is a TEMP table */ int isView, /* True if this is a VIEW */ int isVirtual, /* True if this is a VIRTUAL table */ int noErr /* Do nothing if table already exists */ ){ } It does not make sens for me, maybe it a false positive from boundchecker, but it is weird. I don't know if these "errors" are related to the "blob" bug in release mode. I will try to debug these error with some "printf" in release mode. Note: The provided dll (the one from the sqlite site) does not have this "bug". ---- _2007-Nov-22 18:27:35 by anonymous:_ {linebreak} More info: It seems that there is a bug in the VC6 (SP6) compiler. In btree.c, line 3056: if( offset+amt > nKey+pCur->info.nData ){ /* Trying to read or write past the end of the data is an error */ return SQLITE_ERROR; } After adding some printf around, It seems that the "speed optimization" compilation flag of VC6 changes the code order in a way that the offset variable is miss incremented !! Two remarks: *: I've traced the calling function, sqlite3BtreeData, and the it call accessPayload with the good offset value *: VC6 produces an internal error: "fatal error C1001: INTERNAL COMPILER ERROR" in the accessPayload function, if I try to access the offset value before this line: aPayload = pCur->info.pCell + pCur->info.nHeader; A dirty workaround could be to change the code order or the local var usage. I'm trying .... #e8e8bd 2776 warn active 2007 Nov anonymous 2007 Nov 5 1 mailing list sqlite-users-digest doesn't work. It remembers registered addresses but doesn't send any emails. #f2dcdc 2771 code active 2007 Nov anonymous 2007 Nov 4 4 Lemon: Generated parser needs stdlib.h (not in default template) I tested a simple do-nothing parser just to get lemon output, and this doesn't compile (if warnings treated as errors) because of non declaration of the =memset()= function for the following statment: =memset(&yygotominor, 0, sizeof(yygotominor));= (added for the resolution of SQLite ticket #2172). The lempar.c just include =stdio.h=, it would suffice to add =stdlib.h= to get the =memset()= declaration (even if all real parsers must include =stdlib.h= to get something really working). _2007-Nov-14 21:02:25 by anonymous:_ {linebreak} Sorry, the needed header is =string.h=, not =stdlib.h= :-) #f2dcdc 2766 code active 2007 Nov drh 2007 Nov 1 1 TCL transaction started from within a query does not commit This is a problem with the TCL interface. Consider the following TCL script: file delete -force test.db test.db-journal sqlite3 db test.db db eval { CREATE TABLE t1(x,y); INSERT INTO t1 VALUES(1,2); CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(8,9); } db eval {SELECT * FROM t1} { db transaction { db eval {UPDATE t2 SET a=a*2} } } The [db transaction] statement starts a transaction and it is suppose to commit the tranaction at the end of the code block. But because the transaction started while a query was active, the tranaction is unable to commit. The TCL interface never commits the tranaction nor does it give any kind of error indication. It is unclear if an error should be returned or if the commit should be deferred until outer query finishes. If the code within the [db transaction] block throws an error, we really need the transaction to rollback right away. Perhaps there should be a new API that cancels all pending queries. Perhaps a call to sqlite3_interrupt() would suffice for this. Need to investigate further.... #e8e8bd 2417 new active 2007 Jun anonymous 2007 Nov drh 3 3 Idea for read write concurrency. This is not a problem, but rather an idea on how to resolve the reader/writer concurrency issues encountered in sqlite. The idea is to allow a reader and writer to work concurrently not blocking each other. Dual writers would of course block. When a write occurs: 1. block level changes are made to the database file. 2. Pre-image of that change is written to the journal. Readers: 1. File I/O on the main file would occur normally. 2. If the block encountered is "new" ie one that was written out by the writer. Then get the original block from the Journal file. In order to determine "NEW" a change number could be put on each block. When a READ (select) begins it would first determine the starting global change number. (maybe on the master block?) When a write occurs it would read the Master blocks change number. (increment this in memory) and use write new blocks with the new value. At commit. The Master block would be updated and the txn journal marked for purge if there are pending reads. -- Drawbacks: Reading becomes dependent upon the txn journal. -- Implementation of BLOCK level versioning may ultimately be a simpler approach. Idea would be for a seperate file conaining versioned blocks. This file could be accessed instead of the txn journal. _2007-Nov-08 15:12:00 by anonymous:_ {linebreak} DRH: Also unaddressed in the proposal is how to locate a particular page within the journal file without having to do (performance killing) sequential scan of the possible very large file. Resolution of page access to avoid sequential scans of Txn Journal. When a writer is making the modification to a page first it writes the original page to the journal. At this point the journal file offset location is known. Save this offset in the "NEW" page being written into the database file. This implements a backwards chaining of pages into the txn journal. The reader upon reading the db file page would recognize (see above) that the page is dirty. Acquire the txn journal offset from the dirty page, Read the page from the journal until the starting page is found. This would eliminate any sequential scanning, but may require more than one read request. #e8e8bd 2763 build active 2007 Nov anonymous 2007 Nov 4 4 AIX build failure due to explicit _XOPEN_SOURCE definition Our AIX machine has started having problems building the new sqlite 3.5.2 source. I'm no AIX expert, but I did some digging and I think it's due to sqliteInt.h defining _XOPEN_SOURCE without also defining _ALL_SOURCE. AIX system header files usually define this themselves (in /usr/include/ standards.h) but only if _XOPEN_SOURCE was previously undefined. Has anyone else come across this bug? I'm willing to believe it's our build system if not; we can work around it by setting _ALL_SOURCE on the command-line, but I thought it might be useful to raise a bug anyway. See also tickets #2673, #2681, and #2741. I do not have access to an AIX machine and so have no ability to debug this problem. If you have suggested patches we will consider them. Otherwise, there is not much we can do about this ticket. #e8e8bd 2760 new active 2007 Nov anonymous 2007 Nov 5 4 request: sqlite3_unlink() to delete db files. Hi! Today i came across a use case where i would like client code to be able to delete an underlying sqlite3 db, but that code doesn't have immediate access to the file name of that db (without refactoring the db wrapper code). An interesting feature addition would, IMO, be: int sqlite3_unlink( sqlite3 * db, bool closeTheFile ); Unlinks the file associated with the given database. It does not alter the database in any way (thus is it a no-op on a :memory: database). The closeTheFile flag specifies whether the file handle associated with db should also be closed (and thus db must also be closed), or just unlinked (e.g., as temporary databases are unlinked right after creation but kept open). After browsing through the VFS API a bit, i see that there is an xDelete function, but i'm not sure if its semantics require that the underlying file handle be closed. i don't see an extra xClose member of VFS, so i assume that xDelete also handles closing the file handle. If these were split into two features, sqlite3_unlink() could be implemented very easily. :) #e8e8bd 2756 new active 2007 Nov anonymous 2007 Nov 1 1 allow vacuum to change pragma setting instead of using existing ones we've got databases created with page_size of 1k, and we'd like to change that setting to 4k. vacuum creates a temporary db, attach it to the current connection, creates the tables (based on what's in the old db), and then selects from the old db and inserts into the new one. vacuum does exactly what we need (creating a new db from an old one), but it re-uses the existing pragmas for page size, auto vacuum and reserved page size. from sqlite.c, see sqlite3RunVacuum() sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), sqlite3BtreeGetReserve(pMain)); dr hipp points out that the the operands to vacuum are unused. from sqlite.c: sqlite3VdbeAddOp(v, OP_Vacuum, 0, 0); one solution would be to allow the user to specify the page size, reserve page size, and autovacuum as optional params to vacuum. he had an idea of using the signedness of the first operand to represent the autovacuum setting (since after a table is created, you can change the setting from auto to incremental, but you can't change it from none to auto (or none to incremental) #f2dcdc 2755 code active 2007 Nov anonymous 2007 Nov 3 3 trace interfere with transaction Tcl interface When using the transaction method of the Tcl interface to the SQLite with a registered "trace" function, the stack trace is lost in case an error occurs inside the transaction. As an example I provide two outputs, the first one without a registered trace function and the second one with one (in which it *cannot* be seen where the exception cames from): ========= First: > ./a.tcl vorher BUMMM while executing "a" invoked from within "db transaction { puts "vorher" a puts "nachher" }" ("uplevel" body line 1) invoked from within "uplevel 1 [list db transaction { puts "vorher" a puts "nachher" }]" (procedure "b" line 2) invoked from within "b" (file "./a.tcl" line 28) ========= Second: > ./a.tcl BEGIN vorher ROLLBACK while executing "db transaction { puts "vorher" a puts "nachher" }" ("uplevel" body line 1) invoked from within "uplevel 1 [list db transaction { puts "vorher" a puts "nachher" }]" (procedure "b" line 2) invoked from within "b" (file "./a.tcl" line 28) ******** A scritp that demostrates this behaviour is attached. The only workaround is not to trace. Thanks #f2dcdc 2753 code active 2007 Nov anonymous 2007 Nov drh 3 3 Master journal files sometimes not deleted In the 3.4.1 amalgamation, in vdbeCommit, the master journal file is created, and deleted at the end or if there is an error. But it looks like there is one case where it gets closed but not deleted. The code is: for(i=0; rc==SQLITE_OK && i
Index: src/pager.c =================================================================== RCS file: /sqlite/sqlite/src/pager.c,v retrieving revision 1.393 diff -u -3 -p -r1.393 pager.c --- src/pager.c 20 Oct 2007 13:17:55 -0000 1.393 +++ src/pager.c 26 Oct 2007 21:00:33 -0000 @@ -261,11 +261,11 @@ struct PgHdr { PgHdr *pNextHash, *pPrevHash; /* Hash collision chain for PgHdr.pgno */ PagerLruLink free; /* Next and previous free pages */ PgHdr *pNextAll; /* A list of all pages */ - u8 inJournal; /* TRUE if has been written to journal */ - u8 dirty; /* TRUE if we need to write back changes */ - u8 needSync; /* Sync journal before writing this page */ - u8 alwaysRollback; /* Disable DontRollback() for this page */ - u8 needRead; /* Read content if PagerWrite() is called */ + u8 inJournal:1; /* TRUE if has been written to journal */ + u8 dirty:1; /* TRUE if we need to write back changes */ + u8 needSync:1; /* Sync journal before writing this page */ + u8 alwaysRollback:1; /* Disable DontRollback() for this page */ + u8 needRead:1; /* Read content if PagerWrite() is called */ short int nRef; /* Number of users of this page */ PgHdr *pDirty, *pPrevDirty; /* Dirty pages */ #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT---- _2007-Oct-26 21:20:09 by anonymous:_ {linebreak} How about a compromise. What about some optional setting like? #ifndef OMIT_SQLITE_BITFIELDS # define SQLITE_BITFIELD(X) : X #else # define SQLITE_BITFIELD(X) #endif ... u8 inJournal SQLITE_BITFIELD(1); /* TRUE if has been written to journal */ #f2dcdc 2728 code active 2007 Oct anonymous 2007 Oct 4 4 Some indexes could contain pointers not the data It would be nice if there was a class of index(-column) that would contain a reference to the data in (in the table) rather than a copy of the data. This is useful where you have a need to index large strings or blobs and the size penalty of having 2+ copies ends up being *very* expensive. Consider the cost of the implied index on: create table t1 ( c1 BLOB UNIQUE); and store 100s of 1MB objects. The raw file ends up being twice as large as you ideally would like it to be. *WHEN* to use such indexes isn't entirely clear to me, not is the syntax for doing it in general, 30s of thought I can come up with: create table t1 ( c1 BLOB &UNIQUE); and say create index idx1 on t2(c1, &c2, c3); (meaning c2 would be done via reference, c1 & c3 as copies of the data). It's *FAR* from clear what other databases do here (I didn't look). This would also be useful in cases where I have multiple indexes on tables like email (headers) and values which can be very large (100s of bytes in some cases (Subject:) and I might have 5 or more indexes, means a 100-byte column will take over 600 bytes (+ padding)). Allowing for references would be slower in some cases but faster in others because of the smaller footprint and much grater CPU cache utilization. _2007-Oct-14 15:27:12 by anonymous:_ {linebreak} This enhacement has been discussed on the mailing list before, and would break sqlite3 file format compatability. ---- _2007-Oct-15 02:42:02 by anonymous:_ {linebreak} This is non standard sql. Also, the random access to pages to check indexes constraints (unique indexes, also primary keys) will trash the entire database performance. If you need small index disk usage, consider using hashes to your data keys. it´s the best that you could do to solve your problem. ---- _2007-Oct-18 06:07:58 by anonymous:_ {linebreak} Using a hash is what I've tried doing in a couple of cases. It's not very ideal. For one thing is ordering is messed up. I wonder about a virtual index concept, where you can define a function that takes the column value and returns something to actually store in the index? I'm told oracle has this feature, so whilst it's non-standard there is some precedent (albeit in a very different space). #e8e8bd 2736 build active 2007 Oct anonymous 2007 Oct 2 2 build problems on freebsd on freebsd: --disable-threads does not work. it is accepted as a valid option but no defs are added to the makefile -lgcc needs to be included in SHLIB_LD_LIBS pkgIndex.tcl is not built when -DSQLITE_THREADSAFE=0 is added manually, this causes the install target to fail. _2007-Oct-17 21:16:23 by anonymous:_ {linebreak} I forgot to mention this is with the TEA version #f2dcdc 2715 code active 2007 Oct anonymous 2007 Oct 1 1 no authorization needed to remove authorizer there should be a new auth code created and the auth function should be consulted for permission for removal. _2007-Oct-10 01:08:48 by drh:_ {linebreak} I'm assuming that this feature request comes from {quote: RockShox} and that the development language is Tcl. No. If your adversary has the ability to invoke the interface that removes an authorizer, then you system is already pwned. What you really need is the ability to [interp alias] the eval method into a safe interpreter. That way you can: *: Open the database in the main interpreter *: Set up the authorizer in the main interpreter to invoke a script in the main interpreter *: Set up the [interp alias] so that the safe interpreter can do [db eval ...] but not [db auth ...] It seems like an "-interp" option on the "eval" method of the database connection object would likely be the right interface. Or perhaps there should be separate "safeeval" method. Either way, it has been years and years since I have done anything with safe interpreters so I will have to look into what needs to be done to make that happen. ---- _2007-Oct-17 20:11:23 by anonymous:_ {linebreak} ok i think i agree with that. currently you cannot use an interp alias since the target command runs in the target interp and all your variables and commands are in the wrong scope. this means one needs to load sqlite again in the new interp, and sqlite will not load in a safe interp so a regular interp is required. to be useful, a -interp flag would need to execute in the current scope of the interp and not the global scope. #f2dcdc 2714 code active 2007 Oct anonymous 2007 Oct drh 3 4 Shell cannot import large files greater than 2 GB [patch] If I issue an .import command within the SQLite shell and the file size is larger than 2 GB, the shell gives the error message "cannot open file: foo.txt". Putting #define _FILE_OFFSET_BITS 64 before the #include statements in shell.c fixes this problem under Linux. I tested this solution with a 2.6 GB file under Ubuntu Feisty Fawn 7.04 with Linux kernel 2.6.20-16-generic. _2007-Oct-12 18:48:35 by drh:_ {linebreak} I'm not convinced SQLite _should_ be able to import files larger than 2GiB. Does anybody really ever need to read more than 2GiB of SQL text? That is a lot of SQL, don't you think? Is this really a desirable feature? Can you not split it up into two or more smaller files? ---- _2007-Oct-14 09:25:42 by anonymous:_ {linebreak} I'm not 100% certain about this but: #define _FILE_OFFSET_BITS 64 isn't necessarily correct, better to tweak the makefile(s): CFLAGS += $(shell getconf LFS_CLFAGS) (similar for LDFLAGS) should work on 32/64 bit machines and different platforms. For shell.c I really wouldn't bother and think this should be left alone. Using .import for load more than 2GB makes me things the shell should be fleshed out and extended for this sort of abuse, handy as it is right now, it's not a full SQL shell but a minimalist shell and a useful example). ---- _2007-Oct-16 14:08:09 by anonymous:_ {linebreak} I am the original poster. I use the shell to import CSV files in the 3-4 GiB range on a daily basis. I don't see the point of splitting the files or writing my own importer when the shell does a perfectly fine job once large file support has been activated. With all due respect, I don't understand the reluctance to enable a useful feature. Shouldn't the user decide whether it is reasonable for his application to import big files? I work in the health care industry processing insurance claims for several very large companies. There's nothing broken or wrong with my application -- big files are just the nature of the data I'm handling. Neither of you has proposed any disadvantage to enabling LFS in the shell. If there are no drawbacks, why not turn it on? If the core library can open large database files, why shouldn't the shell be able to import large ones? ---- _2007-Oct-16 16:24:22 by anonymous:_ {linebreak} For reasons also unknown to me, the authors appear to regard the shell as a largely unused sample program. Based on my personal experience in talking to various sqlite library users, it is the primary administrative front-end for sqlite. In particular, doing backups and merging databases. #e8e8bd 2729 doc active 2007 Oct anonymous 2007 Oct 1 1 Lemon: %fallback, %wildcard, and @X uncodumented I noticed that the lemon documentation does not mention the %fallback and %wildcard directives. Both are in the code and are apparently doing useful work in SQLite's parse.y. Can other users benefit from them as well? The symbol @X is also undocumented. From a source code comment I read that it "If the argument is of the form @X then substituted the token number of X, not the value of X". A short documentation example would help to understand where and how it can be useful to apply this syntax. Are there other nice but undocumented Lemon goodies lacking documentation? #f2dcdc 2725 code active 2007 Oct anonymous 2007 Oct 1 1 memory leak in sqlite3_open_v2() when it fails only happens with flags = SQLITE_OPEN_READWRITE; and when res = sqlite3_open_v2(sourcename, &conn, flags, NULL); seems to leak 674 bytes per call _2007-Oct-15 07:07:07 by danielk1977:_ {linebreak} Are you calling sqlite3_close(conn) after the error occurs? All calls to sqlite3_open_v2() need to be matched by a call to sqlite3_close(), even if an error occurs. #e8e8bd 2701 new active 2007 Oct anonymous 2007 Oct 5 5 Make INSERT-ing multiple rows MySQL-compatible SQLite syntax allows to insert only one row with
insert into test (a, b, c) values (1, 2, 3);MySQL allows to insert multiple with
insert into test (a, b, c) values (1, 2, 3), (4, 5, 6), (7, 8, 9) -- etcBut SQLite is also capable of inserting multiple by using INSERT...SELECT:
insert into test (a, b, c) select 1, 2, 3 union select 4, 5, 6 -- etcIt would be nice to make INSERT statement syntactically compatible with MySQL, allowing to insert multiple rows with VALUES clause. It can be implemented by simply translating multiple 'VALUES ()()()' to 'select union' - no serious change required at all. _2007-Oct-08 21:45:05 by anonymous:_ {linebreak} You mean "UNION ALL", not "UNION". UNION would remove duplicate rows, and create an ephemeral table that you don't want because it's less efficient. Your idea is a good one and could be implemented largely in the parser. The number of VDBE opcodes would be quite large for such a statement. I wonder if that would present a problem. ---- _2007-Oct-14 08:12:11 by anonymous:_ {linebreak} Patch implementing multi-row INSERT statements against 3.5.1 source tree: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg28337.html #e8e8bd 2727 build active 2007 Oct anonymous 2007 Oct 4 4 building with -malign-double causes strange behavior sqlite 3.5.1 amalgamation has problems when enabling a wide set of compiler features on gcc (GCC) 4.1.2 (Ubuntu 4.1.2-0ubuntu4) on linux/i686 /w glibc-2.5 strange behavior occurs. typical strangeness is that SQLITE_FULL is returned from sqlite3_prepare_v2() (called immediately after a statement was created with sqlite3_mprintf). This happens even though there is a reasonable amount of disk space free (6G). the compile line looks like:{linebreak} cc -g -pg -O1 -march=i686 -msse2 -malign-double -m128bit-long-double -momit-leaf-frame-pointer -minline-all-stringops -D_XOPEN_SOURCE=520 '-DVERSION="0.10r276M"' -Isqlite/ -DSQLITE_OMIT_LOAD_EXTENSION -DTHREADSAFE=0 -DSQLITE_OMIT_EXPLAIN -DSQLITE_ENABLE_COLUMN_METADATA -c -o sqlite/sqlite3.o sqlite/sqlite3.c and valgrind reports:{linebreak} ==15323== Use of uninitialised value of size 4{linebreak} ==15323== at 0x80585B7: insertElement (sqlite3.c:13072){linebreak} ==15323== by 0x807366E: sqlite3HashInsert (sqlite3.c:13290){linebreak} ==15323== by 0x809FE72: unixOpen (sqlite3.c:15403){linebreak} ==15323== by 0x80579A7: sqlite3OsOpen (sqlite3.c:8210){linebreak} ==15323== by 0x806B12F: sqlite3BtreeFactory (sqlite3.c:21317){linebreak} ==15323== by 0x80767BA: openDatabase (sqlite3.c:71237){linebreak} ==15323== by 0x8076BD1: sqlite3_open (sqlite3.c:71337){linebreak} ==15323== by 0x804BEAE: db_init (dbmgr.c:81){linebreak} ==15323== by 0x804CE64: main (main.c:59){linebreak} my dbmgr.c:81:db_init() is: res=sqlite3_open(filename, &system_db); and system_db=NULL and filename="zomg.sqlite" (string literal). so the parameters seem normal. if I turn off -malign-double everything works fine. (this "bug" also seems to be on 3.4.1 amalgamation) _2007-Oct-14 04:35:16 by anonymous:_ {linebreak} This is a compiler issue. -malign-double creates problems for most programs. What are you trying to accomplish? #f2dcdc 2708 code active 2007 Oct anonymous 2007 Oct 4 2 SQL error:disk I/O error I cross-compile sqlite to embedded Linux,but after I insert data to the table ,it failed.the warning is "SQL error:disk I/O error". _2007-Oct-09 05:12:28 by anonymous:_ Why do you think it is SQLite error ?? ---- _2007-Oct-09 05:46:06 by danielk1977:_ {linebreak} We'll need a bit more data than that to figure this out. Did earlier SQLite versions work? Can you post the entire output of the compile process so that we can see if there are any clues there? Can you run strace so that we can see if there really is an IO error, or at least when SQLite believes there to be one? #f2dcdc 2705 code active 2007 Oct anonymous 2007 Oct 4 4 testfixture unresolved externals with SQLITE_OMIT_GET_TABLE Cannot build/run "make test" with -DSQLITE_OMIT_GET_TABLE due to testfixture link error:
In function `test_get_table_printf': ./src/test1.c:526: undefined reference to `sqlite3_get_table' ./src/test1.c:541: undefined reference to `sqlite3_free_table' collect2: ld returned 1 exit status make: *** [testfixture] Error 1#f2dcdc 2704 code active 2007 Oct anonymous 2007 Oct 4 4 "make test" aborts before completion with SQLITE_OMIT_BLOB_LITERAL When compiled with -DSQLITE_OMIT_BLOB_LITERAL make test aborts with this error:
substr-2.5.2... Ok ./testfixture: near "'61626364656667'": syntax error while executing "db eval " DELETE FROM t1; INSERT INTO t1(b) VALUES(x'$hex') "" (procedure "subblob-test" line 2) invoked from within "subblob-test 3.1 61626364656667 1 1 61" (file "./test/substr.test" line 86) invoked from within "source $testfile" ("foreach" body line 5) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue if..." (file "./test/quick.test" line 93) make: *** [test] Error 1#f2dcdc 2703 code active 2007 Oct anonymous 2007 Oct 3 4 make test does not work with SQLITE_OMIT_FLOATING_POINT make test cannot run with SQLITE_OMIT_FLOATING_POINT. Shouldn't these tests be skipped?
./src/test1.c:1255: warning: passing argument 3 of ‘Tcl_GetDouble’ from incompatible pointer type ./src/test1.c: In function ‘sqlite3_mprintf_scaled’: ./src/test1.c:1284: warning: passing argument 3 of ‘Tcl_GetDouble’ from incompatible pointer type ./src/test1.c: In function ‘test_bind_double’: ./src/test1.c:2607: warning: passing argument 3 of ‘Tcl_GetDoubleFromObj’ from incompatible pointer type ./src/tclsqlite.c: In function ‘tclSqlFunc’: ./src/tclsqlite.c:728: warning: passing argument 3 of ‘Tcl_GetDoubleFromObj’ from incompatible pointer type ./src/tclsqlite.c: In function ‘DbObjCmd’: ./src/tclsqlite.c:1609: warning: passing argument 3 of ‘Tcl_GetDoubleFromObj’ from incompatible pointer type ... $ ./testfixture test/select1.test select1-1.1... Ok select1-1.2... Ok select1-1.3... Ok select1-1.4... Ok select1-1.5... Ok select1-1.6... Ok select1-1.7... Ok select1-1.8... Ok select1-1.8.1... Ok select1-1.8.2... Ok select1-1.8.3... Ok ./testfixture: near ".": syntax error while executing "db eval {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)}" ("uplevel" body line 1) invoked from within "uplevel [list $db eval $sql]" (procedure "execsql" line 3) invoked from within "execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)}" (file "test/select1.test" line 69)#f2dcdc 2684 code active 2007 Oct anonymous 2007 Oct 1 1 Accessing sqlite from an NT service will lock the complete databse. Accessing sqlite from a NT service (application 1) will lock the complete database. Any other process trying to open an sqlite db (application 2) will get error "80004005 unable to lock database" If application 1 runs as normal application, started by local user, this problem doesnt occur and both applications can open the db. _2007-Oct-02 15:48:05 by anonymous:_ {linebreak} SQLite has no knowledge of Windows services. How do you propose to work around this Windows anachronism? ---- _2007-Oct-02 17:20:38 by anonymous:_ {linebreak} Suggesion: Try running the service in the same account as the other program that needs to access the database. Anachronism? Service is just another word for daemon. -knu- ---- _2007-Oct-02 17:33:56 by anonymous:_ {linebreak} Re: Anachronism, the OP suggested there was something fundamentally different about file access using a service. You've pointed out that it's just a file permissions issue. ---- _2007-Oct-05 14:45:07 by drh:_ {linebreak} Two points: 1: The error message "80004005 unable to lock database" is not generated by SQLite. There must be some middleware someplace that is producing this message. The problem might be in that middleware and not in SQLite. 2: None of the SQLite developers run windows. Consequently any fixes for this problem will need to come from the community. Please append patches to this ticket if you find a fix. Or close the ticket if you discover that the problem is outside of SQLite. #e8e8bd 55 new active 2002 Jun anonymous 2007 Oct drh 5 3 instead of triggers for inserts (updates) on regular tablesxx It would be very useful to be able to define a trigger that only executes the trigger code and will prevent the actual insert (or update) on regular tables (eg. specialised autoincrement fields). This could be done by allowing the same "instead of" syntax as used for views. Even better would be the possibility of return codes in a before trigger that can prevents the insert or raise an error. Ideally, this return code could be given conditionally (implementing foreign key and check like functionality). test ---- _2007-Oct-01 23:37:44 by anonymous:_ {linebreak} {link: http://www.sqlite.org/lang_createtrigger.html create trigger syntax} :{linebreak} A special SQL function RAISE() may be used within a trigger-program, with the following syntax: raise-function ::= RAISE ( ABORT, error-message ) | RAISE ( FAIL, error-message ) | RAISE ( ROLLBACK, error-message ) | RAISE ( IGNORE ) #f2dcdc 2674 code active 2007 Sep anonymous 2007 Sep 3 4 NFS fails without lock manager *Problem:* SQLite fails entirely if the NFS lock manager is not running on the share hosting the DB -- even when all access to the DB is serialized. Under these circumstances, it becomes difficult to create applications that are capable of running in a multitude of environments because restrictions are now imposed upon the storage location. ---- *Reproduction:* *:setup an NFS share *:disable the lock manager *:attempt to perform any transactions on a db on that share *:start the lock manager *:perform the same operations ---- *Request:* *:Make a change to the error handling, as necessary, to allow processes to access a DB over an NFS share without use of a lock manager. *:Make a big bold flashing sign in the FAQ about this failure-mode. The wording of the current FAQ led us to believe that the transaction would go through, but protection from other processes was not guaranteed. ---- *Shameless Fanmail:* Love the product! ---- *Screenshot:* dev-srs08 ~ #
static int winClose(OsFile **pId){ winFile *pFile; int rc = 1; if( pId && (pFile = (winFile*)*pId)!=0 ){ int rc, cnt = 0; OSTRACE2("CLOSE %d\n", pFile->h); do{ rc = CloseHandle(pFile->h); }while( rc==0 && cnt++ < MX_CLOSE_ATTEMPT && (Sleep(100), 1) ); #if OS_WINCE winceDestroyLock(pFile); // fix begin if( pFile->zDeleteOnClose ){ DeleteFileW(pFile->zDeleteOnClose); sqliteFree(pFile->zDeleteOnClose); } // fix end #endif OpenCounter(-1); sqliteFree(pFile); *pId = 0; } return rc ? SQLITE_OK : SQLITE_IOERR; }Thanks, Fedor _2007-Jul-28 16:41:41 by anonymous:_ {linebreak} The solution is to revert checkin 3836 and re-open ticket #2294.
Looking at the wince locking mechanism, the only time we ever use the zDeleteOnClose flag is when we've opened a database for exclusive access in sqlite3WinOpenExclusive. To save time and resources (and because its not necessary) we never bother creating a locking mechanism for exclusively-opened files. So pFile->hMutex is NULL when hitting winceDestroyLock(), and the file is never deleted.
Is it possible that the original poster of #2294 was trying to close the same connection on multiple threads at the same time? ---- _2007-Jul-31 05:32:39 by anonymous:_ {linebreak} This is actually a duplicate of #2533 ---- _2007-Sep-21 14:20:05 by anonymous:_ {linebreak} So when the fix of [3836] was applied, the code to delete the file was only put in the section that is called when we have a mutex. I wonder, if the deletion of the file should also take place if there was no mutex. Works for me at least: static void winceDestroyLock(winFile *pFile){ if (pFile->hMutex){ /* Acquire the mutex */ winceMutexAcquire(pFile->hMutex); /* The following blocks should probably assert in debug mode, but they are to cleanup in case any locks remained open */ if (pFile->local.nReaders){ pFile->shared->nReaders --; } if (pFile->local.bReserved){ pFile->shared->bReserved = FALSE; } if (pFile->local.bPending){ pFile->shared->bPending = FALSE; } if (pFile->local.bExclusive){ pFile->shared->bExclusive = FALSE; } /* De-reference and close our copy of the shared memory handle */ UnmapViewOfFile(pFile->shared); CloseHandle(pFile->hShared); * if( pFile->zDeleteOnClose ){ * DeleteFileW(pFile->zDeleteOnClose); * sqliteFree(pFile->zDeleteOnClose); * pFile->zDeleteOnClose = 0; * } /* Done with the mutex */ winceMutexRelease(pFile->hMutex); CloseHandle(pFile->hMutex); pFile->hMutex = NULL; } + else + { + if( pFile->zDeleteOnClose ){ + DeleteFileW(pFile->zDeleteOnClose); + sqliteFree(pFile->zDeleteOnClose); + pFile->zDeleteOnClose = 0; + } + } } The code marked with * was put there in #f2dcdc 2653 code active 2007 Sep anonymous 2007 Sep 3 4 Exclusive Transactions do not work with a Database File attached twice Regarding the docs, it is possible to attach the same database file multiple times. After doing so, I wanted to begin an exclusive transaction. Unfortunately, this fails ("database is locked") and surprises me as I did not find any notice on this particular situation and possible side-effects neither in the attach nor in the transaction/locking documentation. If this behaviour is seen as an error, It would be useful to have it error fixed, because if one reads a list of (possibly duplicate) database files but with unique identifiers, it would be helpful to use these defined identifiers when accessing the databases (and that in an exclusive transaction). Real-world case: Each "module" uses its own database file. Some modules share a database file. So there is a list of module -> database file assignments. Now an update process gets some database update scripts from the modules. Every module wants its changes to be done in the right database, so it relies on having its own database attached with an unique identifier - the module's name. On the other hand, the update process need exclusive access to the databases and starts a transaction -- bummer. #e8e8bd 2651 new active 2007 Sep anonymous 2007 Sep 5 4 Add support for overriding home directory location Currently, the history file's and the rc file's location is hard-wired to =$HOME=. It would be nice if this could be overridden. One way is to look for a =SQLITE_HOME= environment variable that points to the location to use. This can be achieved by a simple addition to =find_home_dir()= in =src/shell.c=. #e8e8bd 2649 new active 2007 Sep anonymous 2007 Sep 4 4 Add an "--enable-extensions" (default=no) to the configure script The attached patch adds "--enable-extensions" to the configure script, but is disabled by default (because of the security considerations of having it enabled). #e8e8bd 2645 build active 2007 Sep anonymous 2007 Sep 5 4 Conflict between tclConfig.sh and tclinstaller.tcl I'm using a non-standard location for Tcl: --with-tcl=/home/scott/lib The build process finds and uses the tclConfig.sh file from /home/scott/lib just fine, but tclinstaller.tcl is called without an explicit path and uses the system's tclsh instead of the one in /home/scott/bin and thus tries to install that portion of code into the system's area. While I can change my path to resolve this, I think it makes more sense for tclinstaller.tcl to use the path information that's embedded in tclConfig.sh to be consistent. /s. #f2dcdc 2627 code active 2007 Sep anonymous 2007 Sep 3 2 Improper parsing of nested JOIN SQLite has a problem with multiple nested JOINs. The only way to get it workig is to remove the surrounding brackets. Removing the brackets unfortunately do not work in other DB systems such as MS SQL, mysql etc. This does not work: Select ContactPhone.* From (ContactPhone LEFT OUTER JOIN ContactLocation ON ContactPhone.PHNLCT_ID = ContactLocation.LCT_ID) LEFT OUTER JOIN ContactItem ON ContactLocation.LCTITM_ID = ContactItem.ITM_ID (It complains about LCT_ID or similar) This works after removing the brackets: Select ContactPhone.* From ContactPhone LEFT OUTER JOIN ContactLocation ON ContactPhone.PHNLCT_ID = ContactLocation.LCT_ID LEFT OUTER JOIN ContactItem ON ContactLocation.LCTITM_ID = ContactItem.ITM_ID All other major DB systems require the surrounding brackets. Do you think it is possible to fix it? Apart from this little little SQLite is an awesome project. Thank you Jakub Klos _2007-Sep-06 13:07:32 by anonymous:_ {linebreak} I don't have access to MS SQL Server, but MySQL and Oracle have no issue with the query without parentheses:
create table x1(a int, b int); create table x2(c int, d int); create table x3(e int, f int); mysql> select x1.* from x1 left join x2 on x1.a=x2.c left join x3 on x2.d=x3.e; Empty set (0.00 sec)---- _2007-Sep-06 19:03:33 by anonymous:_ {linebreak} MSSQL also has no problems without the parens. As a matter of fact, the only DB that I know of that requires them is MS Access (JET). ---- _2007-Sep-06 20:11:31 by anonymous:_ {linebreak} I guess he had no luck filing a JET bug. ---- _2007-Sep-11 17:22:28 by anonymous:_ {linebreak} True, MS access requires the parens but all other major DBs support the query syntax with the parens. So why SQLite does not like it? It should simply ignore them if possible. Thank you #f2dcdc 2634 code active 2007 Sep anonymous 2007 Sep 3 3 .schema uses incorrect ORDER BY giving wrong dependency order When the schema is exported, views are sorted by name instead of by dependency. If there are nested views, the schema may be invalid when used to re-create the database. sqlite3 create table t ( f text ); create view v2 as select f from t; create view v1 as select f from v2; .output test.txt .schema .exit sqlite3 .read test.txt SQL error near line 2: no such table: main.v2 _2007-Sep-07 15:33:06 by anonymous:_ {linebreak} Use .dump instead as a workaround. Unlike .schema, .dump does not use ORDER BY in its queries on sqlite_master and it outputs its rows in order of entry.
SQLite version 3.5.0 Enter ".help" for instructions sqlite> create table t ( f text ); sqlite> create view v2 as select f from t; sqlite> create view v1 as select f from v2; sqlite> sqlite> .schema CREATE TABLE t ( f text ); CREATE VIEW v1 as select f from v2; CREATE VIEW v2 as select f from t; sqlite> sqlite> .dump BEGIN TRANSACTION; CREATE TABLE t ( f text ); CREATE VIEW v2 as select f from t; CREATE VIEW v1 as select f from v2; COMMIT;Suggested patch:
Index: src/shell.c =================================================================== RCS file: /sqlite/sqlite/src/shell.c,v retrieving revision 1.167 diff -u -3 -p -r1.167 shell.c --- src/shell.c 7 Sep 2007 01:12:32 -0000 1.167 +++ src/shell.c 7 Sep 2007 15:28:24 -0000 @@ -1411,8 +1411,7 @@ static int do_meta_command(char *zLine, "SELECT sql FROM " " (SELECT * FROM sqlite_master UNION ALL" " SELECT * FROM sqlite_temp_master) " - "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL " - "ORDER BY substr(type,2,1), name", + "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL", callback, &data, &zErrMsg); zShellStatic = 0; } @@ -1421,8 +1420,7 @@ static int do_meta_command(char *zLine, "SELECT sql FROM " " (SELECT * FROM sqlite_master UNION ALL" " SELECT * FROM sqlite_temp_master) " - "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'" - "ORDER BY substr(type,2,1), name", + "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'", callback, &data, &zErrMsg ); }after patch:
SQLite version 3.5.0 Enter ".help" for instructions sqlite> create table t ( f text ); sqlite> create view v2 as select f from t; sqlite> create view v1 as select f from v2; sqlite> .schema CREATE TABLE t ( f text ); CREATE VIEW v2 as select f from t; CREATE VIEW v1 as select f from v2; sqlite> .q#e8e8bd 2629 doc active 2007 Sep anonymous 2007 Sep 4 4 typo in os_unix.c for nolock
Index: src/os_unix.c =================================================================== RCS file: /sqlite/sqlite/src/os_unix.c,v retrieving revision 1.165 diff -u -3 -p -r1.165 os_unix.c --- src/os_unix.c 5 Sep 2007 13:56:32 -0000 1.165 +++ src/os_unix.c 6 Sep 2007 17:53:47 -0000 @@ -2126,7 +2126,7 @@ static const sqlite3_io_methods sqlite3D /* ** This vector defines all the methods that can operate on an sqlite3_file -** for unix with dotlock style file locking. +** for unix with nolock style file locking. */ static const sqlite3_io_methods sqlite3NolockLockingUnixIoMethod = { 1, /* iVersion */#e8e8bd 2607 event active 2007 Aug anonymous 2007 Sep 2 1 Data loss, continuation to Re: [sqlite] how to flush database to disk? See that mailing list. The originator message : ======================== I've just lost a couple of days' worth of data when my app crashed. (Well, the data wasn't a total loss thanks to backup plans, but the database itself essentially reverted to its state of 2 days ago.) This is despite my app doing a COMMIT after every modification of the DB. It's acting as though all the changes were held in memory, or somehow journaled, and when the crash happened, the changes were all lost or rolled back. What I need is a way to force the database to save its data to disk while my app is running, so that in the event of a crash, I lose little or no data. How can I do this? I presume that closing the database would do the trick, but is there a less heavy-handed way? =========== The exact like that data loss occured at me too. Three times, non-repropucible regualrilly. What common in these losses ? {Editing+committing} in the main thread then {navigating, bof/eof checking, reading data} from within different threads then return to the main thread for {editing+committing}. _2007-Aug-31 19:38:26 by drh:_ {linebreak} The COMMIT does not actually occur until you call sqlite3_reset() and/or sqlite3_finalize() on all your prepared statements. Any prepared statement that has not been reset or finalized is still running, is incomplete, and is thus still holding the transaction open. I'm guessing that you have an unreset and unfinialized statement in your application. I wonder what would happen if we changed the definition of COMMIT so that it returned an error if there were active prepared statements. This is, technically, an incompatibility. But we are coming up on a release with several other minor incompatibilities, so now might be a good time to insert such a change. ---- _2007-Aug-31 19:45:36 by drh:_ {linebreak} I looked in the code, and it turns out we already do this. Perhaps the application is not checking the return code from the COMMIT to see that it is failing? ---- _2007-Aug-31 20:40:47 by anonymous:_ {linebreak} No error reports came from COMMIT. The data loss were noticed after UPDATE & COMMIT after massive reading of results of SQL addressing the same virtual (ATTACHed) tables, from within another thread. ---- _2007-Aug-31 20:53:48 by anonymous:_ {linebreak} Is it possible that a pending transaction survive app shutdown & then OS restart ? Is yes, then any DB error would cause rollback to the data on last BEGIN, isn't ? ---- _2007-Aug-31 21:00:18 by anonymous:_ {linebreak} But me committed each smallest change to dat,a then saw these refreshed data in the tables. And on the next day these data were present. Only reading (with full scrolling ) the affected virtual tables from within another thread then new editing then committing caused the loss. ---- _2007-Sep-01 23:12:31 by anonymous:_ {linebreak} You mention virtual tables. Their data is not maintained by the SQLite engine, but by your own module. If that doesn't implement ACID, you're out of luck. By definition:{linebreak} {quote: A virtual table is an interface to an external storage or computation engine that appears to be a table but does not actually store information in the database file.} references:{linebreak}{link: http://www.sqlite.org/lang_createvtab.html CreateVirtualTable}{linebreak} {wiki: VirtualTables VirtualTables} ---- _2007-Sep-04 04:51:15 by anonymous:_ {linebreak} Me was wrong. These were't true virtual tables. Me used a LEFT OUTER query to several tables residing in different ATTACHed databases. ---- _2007-Sep-04 04:52:01 by anonymous:_ {linebreak} were't => were not, above. #e8e8bd 2604 new active 2007 Aug anonymous 2007 Aug 4 4 CREATE VIRTUAL TABLE does not allow IF NOT EXISTS CREATE VIRTUAL TABLE vt IF NOT EXISTS; would help with development since creating a virtual table that exists returns error 1 - as do several "Real" errors. #e8e8bd 2595 doc active 2007 Aug anonymous 2007 Aug 4 4 sqlite3_commit_hook doc typo src/main.c: -** Register a function to be invoked when a transaction comments. +** Register a function to be invoked when a transaction commits. #f2dcdc 2559 code active 2007 Aug anonymous 2007 Aug 4 4 "make clean" does not delete sqlite3.c and tsrc/
Index: Makefile.in =================================================================== RCS file: /sqlite/sqlite/Makefile.in,v retrieving revision 1.179 diff -u -3 -p -r1.179 Makefile.in --- Makefile.in 27 Aug 2007 23:38:43 -0000 1.179 +++ Makefile.in 28 Aug 2007 01:25:55 -0000 @@ -724,7 +724,7 @@ clean: rm -f testfixture$(TEXE) test.db rm -rf doc rm -f common.tcl - rm -f sqlite3.dll sqlite3.lib sqlite3.def + rm -rf sqlite3.dll sqlite3.lib sqlite3.def sqlite3.c tsrc distclean: clean rm -f config.log config.status libtool Makefile config.h sqlite3.pc#e8e8bd 2587 build active 2007 Aug anonymous 2007 Aug 3 4 Build problem when using the SQLITE_OMIT_FLOATING_POINT define. I apologize in advance if the values I chose above are not appropriate. If I define SQLITE_OMIT_FLOATING_POINT=1 and try to build a Windows DLL, I get two errors in loadext.c, line 116 and 192. "error C4028: formal para meter 3 different from declaration" I believe you want to change the include order at the top of loadext.c from: #include "sqlite3ext.h" #include "sqliteInt.h" to: #include "sqliteInt.h" #include "sqlite3ext.h" Reversing the order of include fixes my build. Yes, I know there is no real reason to disable floating point for the Windows DLL. I'm actually porting SqLite for use in an NT kernel mode driver and avoiding floating point operations will save a lot of time if I don't really need them and I don't. So I made sure this was a problem with a supported platform like the Windows DLL and griped about that instead of my insanity. ;-) You can email questons to mspiegel@vipmail.com. If you want to discuss this over the phone, shoot me an email and I'll send you phone numbers. #e8e8bd 2568 new active 2007 Aug anonymous 2007 Aug 3 3 TEMP_STORE is ignored in some cases It seems that sometimes TEMP_STORE is ignored. I've tried to force SQLite to always use memory by setting TEMP_STORE=3, but some etilqs_* temp files are still being created. The call stack that's causing these file to be created is: sqlite3PagerOpentemp(OsFile * *) sqlite3PagerStmtBegin(Pager *) sqlite3BtreeBeginStmt(Btree *) sqlite3VdbeExec(Vdbe *) sqlite3Step(Vdbe *) sqlite3_step(sqlite3_stmt *) It looks like the temp files are being used to store information for undoing earlier parts of a transaction if a later part fails. I'm assuming the fact this part of the code ignores TEMP_STORE is an over site? _2007-Aug-13 15:03:19 by drh:_ {linebreak} The TEMP_STORE compile-time option only changes the storage for temporary database files. The statement journal is not a databaes file and thus does not come under the control of TEMP_STORE. There is currently no mechanism to force the statement journal into memory instead of onto disk. I will reclassify this ticket as a "feature request". ---- _2007-Aug-22 10:42:50 by anonymous:_ {linebreak} Okay, thank you. #f2dcdc 1242 code active 2005 May anonymous Shell 2007 Aug 3 4 EXPLAIN causes segmentation fault on OSX (and linux) Under Mac OS X, EXPLAIN causes a segmentation fault: [jacob@046] ~$ sqlite3 foo.db SQLite version 3.2.1 Enter ".help" for instructions sqlite> CREATE TABLE test (a int, b int); sqlite> EXPLAIN SELECT * FROM test; Segmentation fault The crash dump follows: Host Name: jacobian Date/Time: 2005-05-13 09:17:04.860 -0500 OS Version: 10.4 (Build 8A428) Report Version: 3 Command: sqlite3 Path: /usr/local/bin/sqlite3 Parent: bash [15421] Version: ??? (???) PID: 15544 Thread: 0 Exception: EXC_BAD_ACCESS (0x0001) Codes: KERN_INVALID_ADDRESS (0x0001) at 0x1400fffc Thread 0 Crashed: 0 libSystem.B.dylib 0x90003228 strlen + 8 1 libsqlite3.0.dylib 0x002387c8 sqlite3VdbeList + 284 (vdbeaux.c:609) 2 libsqlite3.0.dylib 0x002376e0 sqlite3_step + 312 (vdbeapi.c:207) 3 libsqlite3.0.dylib 0x0023e5d8 sqlite3_exec + 260 (legacy.c:82) 4 sqlite3 0x00005b64 process_input + 808 (shell.c:1504) 5 sqlite3 0x000062bc main + 1528 (shell.c:1790) 6 sqlite3 0x00001db4 _start + 348 (crt.c:272) 7 sqlite3 0x00001c54 start + 60 Thread 0 crashed with PPC Thread State: srr0: 0x90003228 srr1: 0x0000d030 vrsave: 0x00000000 cr: 0x22444428 xer: 0x00000006 lr: 0x002387c8 ctr: 0x90003220 r0: 0x002387c8 r1: 0xbfffef40 r2: 0x00249a00 r3: 0x1400fffe r4: 0x00000028 r5: 0x00000000 r6: 0x00000001 r7: 0xffffffff r8: 0x00000001 r9: 0x1400fffc r10: 0x00000086 r11: 0x00249180 r12: 0x90003220 r13: 0x00000000 r14: 0x00000000 r15: 0x00000000 r16: 0x00000000 r17: 0xbffff0f8 r18: 0x00000000 r19: 0xbffff17c r20: 0x00000000 r21: 0x000036d0 r22: 0x00303d90 r23: 0x00303d74 r24: 0x01805700 r25: 0x01807e00 r26: 0x00000001 r27: 0x00000004 r28: 0x01805640 r29: 0x01805600 r30: 0x01805200 r31: 0x002386bc Binary Images Description: 0x1000 - 0x7fff sqlite3 /usr/local/bin/sqlite3 0x205000 - 0x248fff libsqlite3.0.dylib /usr/local/lib/libsqlite3.0.dylib 0x8fe00000 - 0x8fe50fff dyld 43 /usr/lib/dyld 0x90000000 - 0x901a6fff libSystem.B.dylib /usr/lib/libSystem.B.dylib 0x901fe000 - 0x90202fff libmathCommon.A.dylib /usr/lib/system/libmathCommon.A.dylib 0x91d33000 - 0x91d53fff libmx.A.dylib /usr/lib/libmx.A.dylib 0x9680c000 - 0x9683afff libncurses.5.4.dylib /usr/lib/libncurses.5.4.dylib 0x969a3000 - 0x969b9fff libedit.2.dylib /usr/lib/libedit.2.dylib Happening to me as well on FC6 sqlite3 version 3.3.6 ---- _2007-Aug-21 17:09:34 by anonymous:_ {linebreak} Try to upgrade to 3.4.2. #e8e8bd 2582 doc active 2007 Aug anonymous 2007 Aug anonymous 5 5 documenation clarification docs for topic `Set A Busy Timeout` int sqlite3_busy_timeout(sqlite3*, int ms); http://sqlite.org/capi3ref.html#sqlite3_busy_timeout the wording "The handler will sleep multiple times until at least "ms" milliseconds of sleeping have been done" implies it will wait the total amount regardless of the lock status, it should perhaps indicate in the same sentence that it will exit early if the lock becomes available. #f2dcdc 2580 code active 2007 Aug anonymous 2007 Aug anonymous 1 2 Can't open a query if text to search is Greek for example: SELECT * FROM mytable WHERE mycolumn LIKE '%some greek text%' I get wrong results, using the 3.4.2 version. No problem instead using other earlier version. I tested only in Windows. #e8e8bd 2567 build active 2007 Aug anonymous 2007 Aug 3 2 Build fails to install I compile under MinGW with Msys. A build error occurs during 'make install'. After checking the makefile. The 'install' target depends on 'sqlite3', when it should be 'sqlite3$(TEXE)'. The workaround is, after configure, edit makefile for target install, and replace 'sqlite3' with 'sqlite3${TEXE}' where needed. I did not have this problem with 3.3.17. I assume this could be fixed just by fixing the configure to produce correct makefile. _2007-Aug-12 04:41:12 by anonymous:_ {linebreak} Do you have a patch? #e8e8bd 2566 build active 2007 Aug anonymous 2007 Aug 2 1 fts2 broken after vacuum Hi there, I'm testing your database and I'm having problems with fts2: --------- sqlite> select * from distB where distB match "MARIANO"; Assertion failed: *pData!='\0', file fts2amal.c, line 16790 This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. --------- Steps: 1) Create a new .db 2) Import data in new distA table 3) Import data in new distB table 4) Create a new distC virtual table (dts2) 5) insert into distC (rowid, f1, f2, f3) select rowid, f1, f2, f3 from DistB Everything working like a charm until here!!! The fts2 works very well, but after 6) vacuum; the fts seems broken... doing a select throws the error I paste at the post of the topic If you want the .db file I can send it to you (607MB) Thanks.- #f2dcdc 2558 code active 2007 Aug anonymous 2007 Aug 2 3 Multiple JOIN USING() gives incorrect results I'm having a problem joining multiple tables with USING. It appears to work, but the results are incorrect. Here is an example to illustrate the problem. I believe the three SELECT statements should be equivalent, but they produce three different results. .header on .mode column CREATE TABLE Main (pk INTEGER PRIMARY KEY, name VARCHAR); CREATE TABLE OptA (pk INTEGER PRIMARY KEY, alpha VARCHAR); CREATE TABLE OptB (pk INTEGER PRIMARY KEY, beta VARCHAR); INSERT INTO Main VALUES (1, 'One'); INSERT INTO Main VALUES (2, 'Two'); INSERT INTO Main VALUES (3, 'Three'); INSERT INTO Main VALUES (4, 'Four'); INSERT INTO OptA VALUES (1, 'Alpha1'); INSERT INTO OptA VALUES (4, 'Alpha4'); INSERT INTO OptB VALUES (2, 'Beta2'); INSERT INTO OptB VALUES (4, 'Beta4'); SELECT * FROM Main LEFT JOIN OptA USING (pk) LEFT JOIN OptB USING (pk); SELECT * FROM Main LEFT JOIN OptB USING (pk) LEFT JOIN OptA USING (pk); SELECT Main.pk, name, alpha, beta FROM Main LEFT JOIN OptA ON Main.pk = OptA.pk LEFT JOIN OptB ON Main.pk = OptB.pk; Joining Main, OptA, and OptB omits Beta2: pk name alpha beta ---------- ---------- ---------- ---------- 1 One Alpha1 2 Two 3 Three 4 Four Alpha4 Beta4 Joining Main, OptB, and OptA omits Alpha1: pk name beta alpha ---------- ---------- ---------- ---------- 1 One 2 Two Beta2 3 Three 4 Four Beta4 Alpha4 Only by using ON instead of USING do we get the correct results: pk name alpha beta ---------- ---------- ---------- ---------- 1 One Alpha1 2 Two Beta2 3 Three 4 Four Alpha4 Beta4 I think this is basically the same issue as ticket #1637, but it's a more serious example. In that one, the query simply failed to compile. In this case, it seems to work, but gives you the wrong results. I've also tried this script in PostgreSQL 8.0.13. All three queries give (the same) correct results. _2007-Aug-08 17:34:27 by anonymous:_ {linebreak} The problem is that SQLite is transforming SELECT * FROM Main LEFT JOIN OptA USING (pk) LEFT JOIN OptB USING (pk); into SELECT Main.pk, name, alpha, beta FROM Main LEFT JOIN OptA ON Main.pk = OptA.pk LEFT JOIN OptB ON OptA.pk = OptB.pk; Here is a workaround to this bug that makes use of a subquery: select * from (SELECT * FROM Main LEFT JOIN OptA USING (pk)) LEFT JOIN OptB USING (pk); Conceivably all LEFT JOIN chains could be transformed into the above form, but that would decrease performance due to the intermediate result set of the subquery. Having it work without the subquery is tricky since sqlite must deduce that the last USING (pk) is equivalent to the first pk in the chain of joined tables, namely Main.pk, and not OptA.pk. Joe Wilson #e8e8bd 2555 new active 2007 Aug anonymous 2007 Aug 1 1 FTS index without original text Is it possible to build FTS index without storing original text? I want to use fts index without features of snippets etc. I just want to find ID of the record not the content of indexed phrase. I suppose that the table myname_content stores this content. I have tried to update all columns of myname_content and set its values to “xyz” (without one column in which I store ID of the record). After this operation FTS search works good, but unfortunately the table isn’t smaller (I cant’t use vacuum on FTS tables). Is there any other way to have pure text indexes without source level changes? #f2dcdc 2547 code active 2007 Aug danielk1977 2007 Aug 5 3 Changing db encoding of an attached db can confuse shared cache mode. This is quite obscure, but in shared-cache mode: 1) Open db A, attach empty db B. 2) Using another connection from the same thread, set the encoding of B to be different from that of A. Add some data to B. 3) Using the original connection, access database B. It assumes the encoding of A (and therefore mangling any text data). The correct response is to return an error - "attached databases must use the same text encoding as main database". #e8e8bd 2488 new active 2007 Jul anonymous 2007 Jul 5 4 autosize on column output mode in sqlite3 program It would be nice if sqlite3 program has a autosizecolumn mode for displaying queries, because it truncates values, and to calculate and use .width size for each column is tedious. _2007-Jul-07 11:42:03 by drh:_ {linebreak} In order to do this, we would have to either run the query twice or load the entire result set into memory. Otherwise, there would be no way to determine the longest element of each column. Neither approach seems attractive for large and complex queries. ---- _2007-Jul-28 07:05:56 by anonymous:_ {linebreak} every query should internally detect the longest column sizes and a new command should enable the user to set these values for any repetition or similar queries. At the end the queries run twice or more but only the first trial would have cause irritations on output. And this solution should be easy enough to implement it. {linebreak} Even the core of sqlite could calculate the maximum length of each column and a new API function could make this available. It would be really nice to get such an enhancement! ---- _2007-Jul-28 18:37:36 by anonymous:_ {linebreak} while you don't move to next row, sqlite doesn't know the contents, so it will be impossible to do, only if you cache the text entirely in memory, but this is ugly, imagine a 1GB recordset into RAM... ---- have you tried .mode tabs ? here, i have correct column width #f2dcdc 2545 code active 2007 Jul anonymous 2007 Jul 1 4 Group by returns table name with field name imaginate a table:
create table test (
id INTEGER PRIMARY KEY,
name varchar(50) not null,
age integer not null
);
Then: insert into test (name,age) values ('foo',22);
insert into test (name,age) values ('foo',23);
insert into test (name,age) values ('bar',22);
insert into test (name,age) values ('bar',35);
insert into test (name,age) values ('bar',72);
Now try this; sqlite> .headers on
sqlite> select test.name, test.age from test order by name;
name|age
bar|22
bar|35
bar|72
foo|22
foo|23
sqlite> select test.name, test.age from test group by name;
test.name|test.age
bar|72
foo|23
You see ? if i use "GROUP BY", the field name contains tablename. Because i use "SELECT test.name" and not "SELECT name". If i set an alias, i get alias, that's ok. The trouble appears to be very high on Copix (http://wwW.copix.org). We create some DAO (Data Access Objects) automatically. The "groupBy" method doesn't works with SQLite... Is this normal ? Mysql, PostgreSql, Oracle... doesn't need to create alias. _2007-Jul-31 15:54:52 by anonymous:_ {linebreak} There's probably 4 other tickets reporting this. I don't think it will get fixed. The workaround is to use aliases (AS "whatever") for the selected columns. ---- _2007-Jul-31 23:02:19 by anonymous:_ {linebreak} Ok, we have created a special support for SQLite. PS: I love this database :) Simple, nice, usefull, quick and easy Regards
#f2dcdc 2543 code active 2007 Jul anonymous 2007 Jul 1 1 Chinese charset not support?? when i create a table. the table name is " " (chinese) after this "alter table add column aaa text null" error why??/ thank you
#f2dcdc 2540 code active 2007 Jul anonymous 2007 Jul 5 4 Display dlopen() errors for errors when loading modules on Unix system If there is an error loading a module the message "unable to open shared library..." is displayed. In the Unix world the dlopen() error could be display to help diagnose the problem (e.g. missing external refs, etc). I have implemented it in our install of SQLite, I'm sure there is a Windows analog. Here is the patch for loadext.c. - Chris - Christopher Hailey Sr. Software Engineer Maxim Systems :::::::::::::: loadext.c.patch :::::::::::::: *** ./src/loadext.c.orig 2007-07-22 00:11:35.000000000 -0700 --- ./src/loadext.c 2007-07-22 00:12:07.000000000 -0700 *************** *** 292,298 **** handle = sqlite3OsDlopen(zFile); if( handle==0 ){ if( pzErrMsg ){ ! *pzErrMsg = sqlite3_mprintf("unable to open shared library [%s]", zFile); } return SQLITE_ERROR; } --- 292,298 ---- handle = sqlite3OsDlopen(zFile); if( handle==0 ){ if( pzErrMsg ){ ! *pzErrMsg = sqlite3_mprintf("unable to open shared library [%s]: %s", zFile,dlerror()); } return SQLITE_ERROR;
#f2dcdc 2530 code active 2007 Jul anonymous 2007 Jul 2 3 Unable to write to windows share, even with exclusive lock It has been mentioned that the file locking does not work on windows shared network drives (Samba or SMB drives from Windows or Linux). It seems that an exclusive lock should be a workaround for this problem if you need to write to a shared drive. Currently a more complicated locking is being attempted and failing on network drives. With an exclusive lock, SQLite could resort to simply holding a open write or append enabled file handle to the database as a more primitive locking system that is more likely to work on network drive. No other process could open the database but that would be expected with an exclusive lock. The following case should then function: grudy@gamma:~$ mount | grep Files //winserver/FileDump on /mnt/Files type cifs (rw,mand,noexec,nosuid,nodev) grudy@gamma:~$ touch /mnt/Files/i_have_write_permissions.txt; rm /mnt/Files/i_have_write_permissions.txt grudy@gamma:~$ sqlite3 /mnt/Files/foo.sqlite SQLite version 3.3.17 Enter ".help" for instructions sqlite> PRAGMA locking_mode = EXCLUSIVE; exclusive sqlite> create table bar (foobar); SQL error: database is locked sqlite>
#e8e8bd 2469 build active 2007 Jun anonymous 2007 Jul 1 1 test fails on Solaris I have a problem running the test suite on Solaris 9. Build was done using gcc 4.2.0. The build completes without error but many tests fail. I've created my own minimal test that exhibits the problem: set testdir [file dirname $argv0] source $testdir/tester.tcl db close file delete -force test.db test.db-journal sqlite db test.db do_test tdb-1 { execsql { PRAGMA auto_vacuum = 1; BEGIN; CREATE TABLE t1(a, b); } execsql { COMMIT; } } {} integrity_check tdb-2 finish_testWhen running this test I get the following output:
tdb-1... Ok tdb-2... Expected: [ok] Got: [{*** in database main *** List of tree roots: 2nd reference to page 1 Page 3 is never used}] Thread-specific data deallocated properly 1 errors out of 3 tests Failures on these tests: tdb-2This error happens on lots of, but not all, tests. I'm happy to do whatever is necessary to help debug this. Thanks, Tim. _2007-Jun-27 10:44:14 by anonymous:_ {linebreak} Further to this, it appears to be related to gcc 4.2.0. It works fine with gcc 3.4.6. ---- _2007-Jun-28 09:54:35 by anonymous:_ {linebreak} Further more, it doesn't appear to be specific to Solaris. The same problem occurs on Linux with gcc 4.2.0. So I guess the subject of this ticket should be changed to "build/test problems with gcc 4.2.0". This is probably a significant problem - the build completes find but the resultant code is broken. People may not notice this until it's too late. ---- _2007-Jun-28 12:24:05 by drh:_ {linebreak} I installed gcc 4.2.0 on my SuSE linux i686 desktop and built test harnesses under three different configurations: gcc420 -g -O0 -Wall -fstrict-aliasing gcc420 -g -O3 -Wall gcc420 -g -O3 -fstrict-aliasing -fomit-frame-pointer The first two configurations used separate source files. The third configuration was built using the amalgamation. I ran the "quick" test under all configurations. All tests ran to completion with no errors. ---- _2007-Jun-28 13:22:20 by anonymous:_ {linebreak} Two ideas: 1. Compile with gcc 4.2.0 using -O0 instead of -O2 and see what happens. Disable any other optimizations you may have. 2. Run truss with full read/write buffer display on the gcc 3.4.6 compiled testfixture running your simple test case and compare its output to the gcc 4.2.0 compiled test case. ---- _2007-Jul-01 19:00:40 by anonymous:_ {linebreak} I've done tests with optimisation, and this appears to tickle the problem. With no optimisation, -O, -O0, -O1 and -03 it works. With -O2 and -Os it's broken. I was compiling with -O2 when I submitted the initial report. Tim. ---- _2007-Jul-01 19:54:54 by drh:_ {linebreak} I can reproduce the problem now on Linux when compiling as follows: gcc420 -g -O2 -Wall ---- _2007-Jul-01 21:50:42 by drh:_ {linebreak} This appears to be a bug in GCC 4.3.0. A work-around is to compile with the -fno-tree-vrp option. GCC appears to miscompile a single loop within the logic that implements the integrity_check PRAGMA. The code that gets miscompiled is in the file vdbe.c (lines numbers added): 4308 for(j=0; j
RCS file: /sqlite/sqlite/src/vdbe.c,v retrieving revision 1.636 diff -u -3 -p -r1.636 vdbe.c --- src/vdbe.c 1 Jul 2007 21:18:40 -0000 1.636 +++ src/vdbe.c 21 Jul 2007 19:10:13 -0000 @@ -4306,7 +4306,8 @@ case OP_IntegrityCk: { pnErr = &p->aMem[j]; assert( (pnErr->flags & MEM_Int)!=0 ); for(j=0; ju.i; } aRoot[j] = 0; popStack(&pTos, nRoot);