bgcolor # Type Status Created By Subsys Changed Assigned Svr Pri Title _Description _Remarks #e8e8bd 1483 doc active 2005 Oct anonymous Unknown 2005 Oct xdong 1 3 expression document:describe ERROR. http://www.sqlite.org/lang_expr.html about the "CASE" expression,"CASE [expr] ( WHEN expr THEN expr )+ [ELSE expr] END " does not work. May the right is "CASE [expr] WHEN condition THEN expr [ELSE expr] END " others:all the documents about SQLite are scattered, someone should organize them for the user.It is very useful,sometime is important than the software itself's development. infree at Beijing #e8e8bd 490 new active 2003 Nov anonymous VDBE 2003 Nov xdong 4 3 a few api improvements for recordset operations hi friends, i'm working on a visual user interface for sqlite under dos. sqlite works great and i will post a patch for short file name support. there are a few api enhancements needed for automatic synchronization of data in a recordset with the database. 1. please add a new format in printf (like %k) to convert a string to a valid sql identifier. (like rename field "from" to "[from]"). 2. additional information on a selection result will be great also: add the source table name for each column, and the rowid of each field coming from a table. a timestamp for each such field will also be of great importance, though i understand it is not present in the current code. this information will be repeated of course when there are several fields of the same table, and other way to have this information will be welcome too (such like querying the vdbe for each column of interest, every row). 3. please provide a structured way to query the fields information for a table, index, view or view order part. 4. the database dump facility should be provided as an atomic routine in the utils library. best regards, alex #f2dcdc 1862 code active 2006 Jun anonymous TclLib 2006 Jun tclguy 1 1 SQLite cannot load/import data from file I found the problem when I tried to load a data file into a table. To reproduce the problem, I got a mini testcase. DATA FILE - test.dat --------------------------- 1 0 0 2 90000 0 3 366000 0 --------------------------- Log from SQLite: ------------------------------------------------------ khronos-yajun>sqlite3 test SQLite version 3.3.6 Enter ".help" for instructions sqlite> create table test (id INT, x1 INT, x2 INT); sqlite> .import test.dat test test.dat line 1: expected 3 columns of data but found 1 sqlite> .exit ------------------------------------------------------- The problem also exists when I use tcl wrapper (sql copy abort test test.dat). I looked into the code in src/tclsqlite.c, In Lines 1045 nByte = strlen(zSql); 1046 rc = sqlite3_prepare(pDb->db, zSql, 0, &pStmt, 0); 1047 sqlite3_free(zSql); Is the third argument of sqlite3_prepare supposed to be the length of zSql, hence nByte? Also in lines 1070 zSql[j++] = ')'; 1071 zSql[j] = 0; 1072 rc = sqlite3_prepare(pDb->db, zSql, 0, &pStmt, 0); 1073 free(zSql); If I change these two places to reflect the length of zSql, I seem to succeed. Yajun _2006-Sep-27 16:25:47 by anonymous:_ {linebreak} This is a duplicate of #1797 #f2dcdc 2671 code active 2007 Sep shess 2007 Sep shess 2 4 Fts field-based queries are not correctly case-insensitive. CREATE VIRTUAL TABLE t USING fts2(A, B); -- At the SQL level, things are case-insensitive: INSERT INTO t (A, b) VALUES ('Test', 'Columns'); INSERT INTO T (a, B) VALUES ('Second', 'Test'); -- Unfortunately, fts cannot do field-level queries: SELECT rowid FROM t WHERE t MATCH 'test'; -- works SELECT rowid FROM t WHERE b MATCH 'test'; -- works SELECT rowid FROM t WHERE t MATCH 'b:test'; -- no results SELECT rowid FROM t WHERE t MATCH 'B:test'; -- no results It doesn't work because fts is keeping the column name as 'B', but the query parsing uses the results from the tokenizer, which are case-folded, and 'b' != 'B'. I'm thinking on the solution. A quick fix would be to make the azColumn storage be lowercase, but the core problem is that field names probably shouldn't be run through the tokenizer in the first place. #f2dcdc 2219 code active 2007 Feb shess 2007 Feb shess 2 2 Creating an fts table in an attached database works wrong. ATTACH DATABASE 'test2.db' AS two; CREATE VIRTUAL TABLE two.t2 USING fts2(content); will put t2_content, t2_segments, and t2_segdir in database 'main' rather than database 'two'. In some cases everything will appear to work, because the tables will be defaults for that name. #f2dcdc 2046 code active 2006 Oct anonymous 2006 Nov shess 1 1 FTS1 - Error closing database due to unfinished statements The following script causes an error in SQLite3.exe with FTS1. The error will surface only AFTER the script has finished AND you have typed .exit at the sqlite> prompt to quit SQLite3. The problem seems that the SELECT statement is not properly finalized due to an internal error. -- The next line is for Windows only, please adopt it -- if running Linux or use a FTS1-enabled SQLite3 binary. select load_extension ('fts1.dll'); CREATE TABLE Snippets( SnippetID INTEGER PRIMARY KEY, SnippetTitle TEXT, FtsID INTEGER); CREATE VIRTUAL TABLE SnippetsFts USING FTS1 (SnippetTitle, SnippetText); INSERT INTO Snippets (SnippetTitle) VALUES ('one'); INSERT INTO Snippets (SnippetTitle) VALUES ('two'); SELECT SnippetID FROM Snippets JOIN SnippetsFts ON FtsID = +SnippetsFts.RowID WHERE SnippetsFts MATCH 'one'; -- After the script is done, type .exit at the prompt to close the database. -- -- SQLite3 will close, but report the following error before doing so: -- -- "error closing database: Unable to close due to unfinalised statements" -- -- Does this qualify for a bug? The script is also attached to this ticket. _2006-Nov-27 22:58:49 by shess:_ {linebreak} Attached tighter version of the replication script, generated in isolating what mattered to the bug. #f2dcdc 1992 code active 2006 Sep anonymous 2006 Nov shess 1 1 FTS1: Problems after dropping utility tables There are problems if FTS1 utilities tables are dropped from a database. See following SQL for details. drop table if exists x; -- Create a FTS1 table. create virtual table x using fts1 ('content'); -- Drop table x_content: Works fine, but should this be allowed? -- The same errors below also show if table x_term is dropped. drop table x_content; -- All attempts to access table x now result in errors, -- including dropping table x. There seems to be no way out -- except of recreating the database. All three commands below -- cause the same error, regardless if executed in sequence -- or individually: insert into x (content) values ('one two three'); -- Error! delete from x; -- Error! drop table x; -- Error! Added "not exists" to allow dropping an fts table with corrupted backing. Allowing updates to such tables is unlikely to happen (not even clear what it would mean, in most cases!). #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) #e8e8bd 971 todo active 2004 Oct anonymous Unknown 2004 Oct peter 2 1 how to recompile sqlite with THREADSAFE preprocessor macro set to 1? I have just installed sqlite-2.8.15 on my linux machine. After that I've tried to run the sqlite.php file which returns the fatal error as like below: Call to undefined function: sqlite_open() in /home/maintain/public_html/tmp/sqlite.php on line 2 As per the instruction given in the forum, I have learn that I have to recompile the sqlite with THREADSAFE preprocessor macro set to 1, but I really don't know how to do this. Kindly help me on this issue. Thanks and Regards, Rajesh Kannan M #e8e8bd 1647 doc active 2006 Jan anonymous Unknown 2006 Jan paul 3 1 i want to use this lib in my project HI, I want to use this sqlite source in my project for quick firing of quaries. I want dataled doc of source or how to use these source in my project. Hoping for quick reply, Bye thaks & Regards Sumant Kadam 9422615104 _2006-Jan-30 20:43:26 by anonymous:_ {linebreak} This is not a bug report. Please use the mailing list for this type of question. #e8e8bd 1801 new active 2006 May anonymous Pager 2006 May owensmk 5 4 Include Support for User-defined Lock Synchronization SQLite uses a busy-wait model for locking. For high concurrency applications this can be become inefficient. I have written a patch for =pager.c= which introduces two hooks - =lock()= and =unlock()= - whereby the application can participate in locking/sychronization of connections. This can in some cases increase overall concurrency by an order of magnitude. The callback implementation is very similar to the busy handler. All of the synchronization is implemented by the application. SQLite simply calls the hooks at the appropriate times, if they are registered. A full description of the patch is available at {link: http://www.gintana.com/sqlite/}. #e8e8bd 331 new active 2003 May anonymous Unknown 2003 Jun mike 5 5 Went to compile Win32 project and found a missing header reference I created a project in VS.NET 2003, specifically a Windows Dll project. I included all of the *.c files from your windows source distribution. The build went ok with a bunch of warnings until I go to the tcl.h file inclusion. The build barfed. The header "tcl.h" is only required when building the tcl-enabled version of SQLite used for testing. To compile a normal (non-testing) version, omit the file "tclsqlite.c". If you are building using the tarball, omit the files "md5.c", "test1.c", "test2.c", "test3.c" as well. This is covered in the wiki pages HowToCompile, HowToCompileWithVsNet. The tcl version can be built under Windows if the tcl development tools are installed. Perhaps this should be converted to a "request for enhancement" for a MSVC project file or at least a readme.txt file to be placed in the Windows download. #f2dcdc 1485 code active 2005 Oct anonymous Unknown 2005 Oct jshen 3 1 cyrilic problem(suppose Unicode as a whole, for PPC) I think there is a problem in the utf.c file. _2005-Oct-14 06:18:45 by anonymous:_ {linebreak} Pocket PC support isn't provided in the default SQLite provider. The code necessary to support the Pocket PC is at http://sourceforge.net/projects/sqlite-wince and its there that a bug report should be filed. #e8e8bd 362 event active 2003 Jun anonymous Shell 2003 Jun jadams 4 2 Problem with select count select count() nonetable; select count without FROM always return 1, not return error message :-) #e8e8bd 363 new active 2003 Jun anonymous VDBE 2003 Jun jadams 5 5 sqlite support domain as user defined type sqlite support domain as user defined type #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. #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 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 #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. #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
#f2dcdc 2413 code active 2007 Jun anonymous 2007 Jun drh 1 1 1 bug and 2 suggestions in lemon Hello, ... {linebreak} Sorry for my english :-) and if i post this with Severity/Priority error. {linebreak} I found some not serious bug and have some suggetions. {linebreak} ============================================================================={linebreak} BUG FIX: {linebreak} lemon.c for Win32. It not found lempar.c - backslash-bug. {linebreak} function: {linebreak} PRIVATE char *pathsearch(argv0,name,modemask); {linebreak} PATCH: {linebreak} ---- CUT --------------------------------------------------------------------{linebreak} --- C:/lemon.c Wed Jun 13 15:02:37 2007 {linebreak} +++ D:/Den/Lemon/lemon.c Wed Jun 13 16:25:22 2007 {linebreak} @@ -2911,7 +2911,11 @@ {linebreak} c = *cp; {linebreak} *cp = 0; {linebreak} path = (char *)malloc( strlen(argv0) + strlen(name) + 2 ); {linebreak} - if( path ) sprintf(path,"%s/%s",argv0,name); {linebreak} + #ifdef __WIN32__ {linebreak} + if( path ) sprintf(path,"%s\\%s",argv0,name); {linebreak} + #else {linebreak} + if( path ) sprintf(path,"%s/%s",argv0,name); {linebreak} + #endif {linebreak} *cp = c; {linebreak} }else{ {linebreak} extern char *getenv(); {linebreak} @@ -2920,11 +2924,19 @@ {linebreak} path = (char *)malloc( strlen(pathlist)+strlen(name)+2 ); {linebreak} if( path!=0 ){ {linebreak} while( *pathlist ){ {linebreak} - cp = strchr(pathlist,':'); {linebreak} + #ifdef __WIN32__ {linebreak} + cp = strchr(pathlist,';'); {linebreak} + #else {linebreak} + cp = strchr(pathlist,':'); {linebreak} + #endif {linebreak} if( cp==0 ) cp = &pathlist[strlen(pathlist)]; {linebreak} c = *cp; {linebreak} *cp = 0; {linebreak} - sprintf(path,"%s/%s",pathlist,name); {linebreak} + #ifdef __WIN32__ {linebreak} + sprintf(path,"%s\\%s",pathlist,name); {linebreak} + #else {linebreak} + sprintf(path,"%s/%s",pathlist,name); {linebreak} + #endif {linebreak} *cp = c; {linebreak} if( c==0 ) pathlist = ""; {linebreak} else pathlist = &cp[1]; {linebreak} ---- CUT --------------------------------------------------------------------{linebreak} ============================================================================= {linebreak} SUGGESTION 1: {linebreak} Why we allocate parser with mallocProc parameter of ParseAlloc function {linebreak} and free with freeProc of ParseFree function? {linebreak} We do this because we want what parser is user-allocatable {linebreak} with USER-DEFINED-MEMORY-ALOCATION-WAY but not with "malloc"/"free" from stdlib... am i right? {linebreak} If so... why we still allocate memory for parser stack with "realloc" function? {linebreak} It's bad for solutions where is no stdlib. {linebreak} My suggestion is {linebreak} FIRST WAY: {linebreak} To add to yyParser struct 3 variables like {linebreak} void *mem_alloc_fn; {linebreak} void *mem_realloc_fn; {linebreak} void *mem_free_fn; {linebreak} and add 3 directives like {linebreak} %memory_alloc {linebreak} %memory_realloc {linebreak} %memory_free {linebreak} and if it declared - use it for allocating/free/reallocating memory in parser. {linebreak} and {linebreak} - void *ParseAlloc(void *(*mallocProc)(size_t)); {linebreak} will now as void *ParseAlloc(); {linebreak} - void ParseFree(void *pParser, void (*freeProc)(void*)); {linebreak} will now as void ParseFree(void *pParser); {linebreak} OR SECOND WAY (very simple): {linebreak} To add to yyParser struct 1 variable like {linebreak} void *mem_realloc_fn; {linebreak} - void *ParseAlloc(void *(*mallocProc)(size_t)); {linebreak} will now as void *ParseAlloc(void *(*mallocProc)(size_t), void *(*reallocProc)(void *, size_t)); {linebreak} store reallocProc in mem_realloc_fn in yyParser {linebreak} and in yyGrowStack something like this: {linebreak} ... yyGrowStack (...) {linebreak} { {linebreak} .... {linebreak} if(pParser->mem_realloc_fn != NULL) {linebreak} { {linebreak} pNew = pParser->mem_realloc_fn(p->yystack, newSize*sizeof(pNew[0])); {linebreak} } {linebreak} else {linebreak} { {linebreak} pNew = realloc(p->yystack, newSize*sizeof(pNew[0])); {linebreak} } {linebreak} .... {linebreak} } {linebreak} and use it for reallocating memory in parser. {linebreak} In this ways - memory allocating in parser is under FULL user control. {linebreak} ============================================================================= {linebreak} SUGGESTION 2: {linebreak} I build lemon with VC 8.0 with option /Wp64 (Detect 64-Bit Portability Issues) {linebreak} and have warnings. Type int, size_t, pointer and unsigned long have diferent size on x32 and x64 platforms. {linebreak} Can you fix type difference, please? {linebreak} Only you can choice better way for this - type conversion OR change type of 'warning' variables. {linebreak} WARNINGS: {linebreak} d:\den\lemon\lemon.c(1331) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1337) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1455) : warning C4113: 'int (__cdecl *)()' differs in parameter lists from 'int (__cdecl *)(const void *,const void *)' {linebreak} d:\den\lemon\lemon.c(1578) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1578) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1581) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1581) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1586) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1586) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1588) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1588) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1590) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1590) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1592) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1592) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1595) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1595) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1596) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1596) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1624) : warning C4311: 'type cast' : pointer truncation from 'char **' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1624) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1628) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1628) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1629) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1629) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1658) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1661) : warning C4267: '+=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1774) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1774) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1785) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1785) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1883) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(2722) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3171) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3173) : warning C4018: '>=' : signed/unsigned mismatch {linebreak} d:\den\lemon\lemon.c(3184) : warning C4267: '+=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3340) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3346) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3542) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} Ups ... drh, sorry - title change. #f2dcdc 154 code active 2002 Sep drh Pager 2007 Jun drh 3 3 Prohibit links on database files. If a database file is aliased using either hard or symbolic links, it can happen that an aborted transaction will not roll back correctly. Consider this scenario. The database file is named both a.db and a.db. Application one opens a.db and starts to make a change. This creates a journal file a.db-journal. But application one crashes without completing the transaction. Later, application two attempts to open the database as b.db. App two looks for a journal file to rollback, but it thinks the journal should be named b.db-journal. So it fails to see the a.db-journal that app one left and fails to rollback the transaction. The only way I can think of to prevent this kind of thing it to refuse to open any database file that contains two or more hard links and to refuse to open a file through a symbolic link. _2004-Mar-16 20:46:17 by anonymous:_ {linebreak} What if the journal file name wasn't based on the database name, but instead was based on the starting inode of the database file? For instance, "journal-10293" would be used if the starting inode for the associated database file was 10293. ---- _2004-Mar-20 17:17:41 by anonymous:_ {linebreak} Using Inode-numbers to solve this problem is a dangerous proposition, as disk defragmenters can alter the inode the db starts at in between a crash and a subsequent roll-back attempt. ---- _2007-Jun-05 03:57:17 by anonymous:_ {linebreak} On unix, you can use ftok() to solve this problem. It guarantees to return the same key for all paths to the same file, including symbolic and hard links. I have to experience programming in Windows, but have no doubt a similar function call exists in that API. #f2dcdc 2320 code active 2007 Apr anonymous 2007 Apr drh 1 1 sqlite3_open(sFN_with_umlaut) Do it in a standard MS Visual Studio Project:# Prepare the database: # file delete -force test.db test.db-journal sqlite3 db test.db db eval { CREATE TABLE t1 ( x TEXT UNIQUE NOT NULL, y BLOB ); } # Open a second connection to the database that will be # used to lock the database file. # set DB2 [sqlite3 db2 test.db] if {$DB2==""} { set DB2 [sqlite3_connection_pointer db2] } # A small cache will cause an early cache spill. # db eval {PRAGMA cache_size=10} # Acquire read lock on the database file using the second connection. # set STMT [sqlite3_prepare $DB2 {SELECT rowid FROM sqlite_master} -1 TAIL] sqlite3_step $STMT # Insert a short record into the index (10 bytes) and a large record # into the table (15K). The index record goes in Ok, but during the # insert into the table, SQLite attempts to upgrade to an EXCLUSIVE # lock to do a cache flush. When this happens, the cache is left in # an inconsistent state. # set zShort [string repeat 0123456789 1] set zLong [string repeat 0123456789 1500] db eval {BEGIN} set rc [catch { db eval {INSERT INTO t1 VALUES($zShort, $zLong)} } msg] puts "rc=$rc msg=$msg" sqlite3_finalize $STMT db eval {COMMIT} db close sqlite3 db test.db puts [db eval {PRAGMA integrity_check}]
#include_2005-Sep-12 09:26:19 by ghaering:_ {linebreak} forgot to log in ... ---- _2005-Sep-12 09:38:22 by anonymous:_ {linebreak} The index of the sqlite3_bind_* and sqlite3_column_* is starting from 1 not 0. see http://www.sqlite.org/capi3ref.html#sqlite3_bind_null ---- _2005-Sep-12 09:49:26 by drh:_ {linebreak} I think what Gerhard is proposing is to have a new operator which can be used to compare for NULL so that it works against bound parameters even if the parameter is bound to NULL. You cannot use == for his. x=='abc' and x==123 both work, but x==NULL always fails. So saying x==:param is dangerous because if :param is bound to NULL it does not do what you really want. I have previously proposed extending the IS operator for this purpose. You can already say "x IS NULL". Why shouldn't you also be able to say "x IS 'abc'" and "x IS 123"? Then you could do things like "x IS :param" and it would work with bound parameters. I proposed this on the mailing list once, if I recall, and it was resoundingly rejected. But maybe people just didn't understand the question or the problem it was trying to resolve. #e8e8bd 1417 new active 2005 Sep anonymous 2005 Sep drh 3 3 Fix successive access to a DB handler (unix broken thread file lock) This patch allows threads to access successively to a DB handle and remove the heavy restriction of the SQLITE_MISUSE. In case of simultaneous access, concurent threads get SQLITE_BUSY until the OsFile is unlocked. patch against os_unix.c in version 3.2.5. _2005-Sep-09 20:26:58 by drh:_ {linebreak} I do not believe this patch works. When a handle is moved between threads on a system where separate threads cannot override each others locks, then then lockInfo structure for that handle needs to be released and a new lockInfo structure suitable for the new thread needs to be allocated. There is a separate lockInfo structure for each thread/file combination so when moving a handle from one thread to another it is important to get a new lockInfo structure. ---- _2005-Sep-10 01:26:19 by anonymous:_ {linebreak} Why this lockInfo structure should need to be released for another thread? This patch resets safely the thread(a)/file combination to a thread(b)/file combination until the next move. This allows successive access to a DB handle and manage properly concurrent access with SQLITE_BUSY. ---- _2005-Sep-21 15:16:44 by drh:_ {linebreak} Suppose this patch is run on a system where threads cannot override each others locks. (Ex: RedHat 9). Two handles are opened on separate threads. This gives them different lockKey values. After opening, the handles are passed to the same thread. The first handle does "BEGIN; UPDATE ....;" but does not yet commit. The second handle then does an UPDATE. The first handle does ROLLBACK. At that point the database has likely been corrupted. ---- _2005-Sep-21 19:50:17 by anonymous:_ {linebreak} I can not suppose this because it's "impossible". This patch is not active on a system where threads cannot override each others locks: # define CHECK_THREADID(X) ( threadsOverrideEachOthersLocks>0 && check_threadid(X) ) Or if you suppose this is possible, this means that, on the current version of SQLite: *: the testThreadLockingBehavior function is wrong *: data corruption can happen on a system where threads can override each others locks All this is nonsense. #f2dcdc 1415 code active 2005 Sep anonymous Unknown 2005 Sep drh 1 1 Querying for BLOB type fields How do I query for BLOB type fields? I tried 1: field LIKE 'abc' 2: field LIKE quote('abc') and 3: field LIKE X'616263' but nothing seems to return back the record that I am interested in. _2005-Sep-13 08:00:28 by anonymous:_ {linebreak} Using version 3.2.5, you might use the quote function to convert a blob into a string which you can filter using the like operator: select * from test where quote(text) like '%6263%'; is working and usable but may not work as expected because like '%26%' would find the same and this was not expected, isn't it? select * from test where like(quote(text),'%6263%'); doesn't work and select * from test where like(quote(text),'%6263%','%'); doesn't work either ---- _2005-Oct-04 05:44:08 by anonymous:_ {linebreak} This should really be taken to the mailing list, preferably with descriptions of how other DBMSs handle LIKE as applied to BLOB columns. #f2dcdc 1382 code active 2005 Aug anonymous 2005 Aug drh 1 1 Assert nErr==0 on corrupt db I'm working on an embedded filesystem where files can be randomly altered. Sometimes my .db files get messed up. I've attached an example db. I'd like to catch the asserts and return an error rather than crash. $ sqlite corrupt-assert.db 'select count(*) from sensor' sqlite: src/main.c:120: sqliteInitCallback: Assertion `nErr==0' failed. Aborted #e8e8bd 1240 new active 2005 May anonymous Pager 2005 May drh 1 3 Need integration of Apple's file locking callbacks improvement Now that OSX 10.4 has been released to the public; Apple has dedicated their changes to improve and support locking on the OSX platform (as well as others) to the public domain. These changes will also make it possible to use sqlite on prior versions of OSX. They externalize the necessary file locking into a callback system which greatly simplifies maintaining this area of persistent headaches. Apple has also included support for F_FULLFSYNC as described here: http://lists.apple.com/archives/darwin-dev/2005/Feb/msg00072.html Apple's changes are to a base distro of sqlite 3.1.3 The sources can be found at: http://www.opensource.apple.com/darwinsource/tarballs/other/SQLite-28.tar.gz #f2dcdc 1235 code active 2005 May anonymous Unknown 2005 May drh 4 3 inconsistent pragma handling The pragma user_version and schema_version are handled inconsistently in this respect : the result set returned contains a single column that has no name. all other pragmas return named columns. since some high-level languages complain about db fields with no names (most wrappers will gag at this), I suggest that a simulated "column_name" is also generated here, as with all other pragmas. #e8e8bd 1204 new active 2005 Apr anonymous TclLib 2005 Apr drh 4 2 (visible) version provided by tclsqlite package As a new version of sqlite is released it would be good to see the tclsqlite package version modified accordingly. On the file tclsqlite.c, function Sqlite3_Init, it gives a fixed version to the function Tcl_PkgProvide "3.0". I believe this should be replaced with SQLITE_VERSION (macro defined in sqlite3.h). Is there a reason this should not happen? Or was simply overlooked? #e8e8bd 1190 new active 2005 Mar anonymous Unknown 2005 Mar drh 4 4 strftime() does not Support Years with 3 or 5 digits It looks like strftime() doesn't support years before 1000 CE or after 9999 CE: sqlite> select strftime('%Y', '2004-01-01T02:34:56'); strftime('%Y', '2004-01-01T02:34:56') ------------------------------------- 2004 sqlite> select strftime('%Y', '200-01-01T02:34:56'); strftime('%Y', '200-01-01T02:34:56') ------------------------------------ [null] sqlite> select strftime('%Y', '20000-01-01T02:34:56'); strftime('%Y', '20000-01-01T02:34:56') -------------------------------------- [null] The same applies to years BCE: strftime('%Y', '-2000-01-01T02:34:56') -------------------------------------- -2000 sqlite> select strftime('%Y', '-20000-01-01T02:34:56'); strftime('%Y', '-20000-01-01T02:34:56') --------------------------------------- [null] sqlite> select strftime('%Y', '-200-01-01T02:34:56'); strftime('%Y', '-200-01-01T02:34:56') ------------------------------------- [null] One can get around the requirement for pre-1000 by using a preceding 0 (and the ISO-8601 spec may well require this; I'm not sure), but the lack of support for five or more digits in the year has no workaround. So much for my science fiction database! ;-) _2005-Mar-30 23:45:53 by anonymous:_ {linebreak} Ah, found this link, which says that years must be represented by at least four digits. So 0200 is correct and works, while 200 is not. Sorry 'bout that. Ignore that part of the report. http://www.absoluteastronomy.com/encyclopedia/I/IS/ISO_86012.htm But it still would be nice to support more than four digits for the year, so that astronomy folks can use SQLite. :-) ---- _2005-Mar-31 18:24:53 by drh:_ {linebreak} I think this is an enhancement request, not a bug report. I deliberately limited the span of years that SQLite would handle to be 1000 through 9999 as a means of detecting faulty input. I reasoned that astronomers and others who were doing date calculations outside of this range are likely using their on date/time library anyhow and so the limited range of dates that SQLite would handle was not seen as a serious handicap. Even if an astronomer wanted to use SQLite, the changes to the SQLite code base to enable a larger span of years are minor and could be done on a case by case basis. I'm not sure this is something that needs to be in the standard release. ---- _2005-Mar-31 18:32:36 by anonymous:_ {linebreak} I certainly understand the desire to detect faulty input, but since dates are simply stored in TEXT columns in SQLite (I think that's right--please correct me if I'm wrong), there isn't actually any fault input detection going on until someon uses strftime() or another date/time function. Furthermore, 10000-12-19 is not faulty input; it's perfectly valid. So is -10001-02-28 (for the archaeologists). It may be easy to patch SQLite to support such dates, but that's relatively out of the hands of mere mortals. Now if there was a compile directive to enable such support, that might be a decent compromise. Then, you'd still have the validation support, but at the same time, those who need astronomical or archaeological dates could easily get them without having to learn any C or the SQLite source code. ---- _2005-Mar-31 18:55:48 by drh:_ {linebreak} I will consider the request to support 5- or 6-digit dates. But the date algorithms used in SQLite doe not work correctly for negative Julian days (that is, dates prior to -4713-11-24T12:00:00). In fact, the date algorithms always assume the Gregorian calendar, which wasn't invented until the 16th century, was not in wide use until the 18th century. So any really old dates are suspect. Due to changing standards and politics, date/time computations can be amazingly complex, especially when you start to consider things like daylight savings time. It is not the mission of SQLite to provide a complete date/time management system. The date/time functions provided are intended to give a baseline of functionality that meets 90% of the need. Users who need more can add their own code using the sqlite3_create_function() API. The date/time functions are already one of the largest modules within SQLite and I am very hesitant to go make them even larger. ---- _2005-Mar-31 19:08:30 by anonymous:_ {linebreak} Understood, thanks for the consideration. #e8e8bd 1187 build active 2005 Mar anonymous Parser 2005 Mar drh 3 3 OMIT_TRIGGER OMIT_VIEW do not compile properly SQLITE_OMIT_TRIGGER and SQLITE_OMIT_VIEW are needed by lemon to process parse.y{linebreak} They are not passed from CFLAGS to Makefile.in OPTS. #e8e8bd 1162 new active 2005 Mar anonymous 2005 Mar drh 1 2 Column names in sqlite views differ from oracle/informix column names I tried to use views with sqlite 3.1.5 with short_column_names (as default within 3.x). Below is the script of my try. As with CheckIns 2230 - 2232 (Tickets #269, etc.) I thought I can access view elements without defining an alias. That does work if I create the view like this: create view yy2 as select y1.*, y2.* from y1, y2 where y1.a=y2.c; But does not work, if I create it this way. create view yy as select y1.a, y1.b, y2.c, y2.d from y1, y2 where y1.a=y2.c; It would be a hard job to change all my view definitions (which where fine for informix and oracle) to fit sqlite. _Reply from Dr Richard Hipp: If you will write a ticket that describes the column names assigned to views by Informix, Oracle, and PostgreSQL, I'll change SQLite to generate exactly the same column names._ Below i posted the behaviour of informix 9.3 and oracle. I currently have no postgreSQl available.#include #include "sqlite3.h" int main() { sqlite3* db; sqlite3_stmt* st; const char* tail; int rc; sqlite3_open(":memory:", &db); /* create table */ sqlite3_prepare(db, "create table test(foo)", 0, &st, &tail); rc = sqlite3_step(st); assert(rc == SQLITE_DONE); rc = sqlite3_finalize(st); assert(rc == SQLITE_OK); /* insert row */ sqlite3_prepare(db, "insert into test(foo) values (null)", 0, &st, &tail); rc = sqlite3_step(st); assert(rc == SQLITE_DONE); rc = sqlite3_finalize(st); assert(rc == SQLITE_OK); /* query 1 */ rc = sqlite3_prepare(db, "select count(*) from test where foo is ?", 0, &st, &tail); if (rc != SQLITE_OK) { printf("query 1: %s\n", sqlite3_errmsg(db)); } /* query 2 */ sqlite3_prepare(db, "select count(*) from test where foo = ?", 0, &st, &tail); sqlite3_bind_null(st, 0); rc = sqlite3_step(st); assert(rc == SQLITE_ROW); printf("number of rows: %i\n", sqlite3_column_int(st, 0)); rc = sqlite3_finalize(st); assert(rc == SQLITE_OK); return 0; }
C:\devel\js\head\dst>sqlite3 ..\..\sqlite\problem.db SQLite version 3.1.5 Enter ".help" for instructions sqlite> create table y1(a,b); sqlite> create table y2(c,d); sqlite> create view yy as select y1.a, y1.b, y2.c, y2.d from y1, y2 where y1.a=y2.c; sqlite> insert into y1 values (1,2); sqlite> insert into y2 values (1,3); sqlite> .headers on sqlite> select * from yy; y1.a|y1.b|y2.c|y2.d 1|2|1|3 sqlite> select * from yy where a=1; SQL error: no such column: a sqlite> select * from yy where y1.a=1; SQL error: no such column: y1.a sqlite> pragma short_column_names; short_column_names 1 sqlite> pragma full_column_names; full_column_names 0 sqlite> create view yy2 as select y1.*, y2.* from y1, y2 where y1.a=y2.c; sqlite> select a,b,c,d from yy2; a|b|c|d 1|2|1|3 sqlite> select * from yy2; a|b|c|d 1|2|1|3 ------------- INFORMIX ----------- CREATE TABLE y1 (a CHAR(20), b CHAR(20)); Table created CREATE TABLE y2 (c CHAR(20), d CHAR(20)); Table created INSERT INTO y1 VALUES ('1','2'); 1 row(s) inserted INSERT INTO y2 VALUES ('1','3'); 1 row(s) inserted CREATE VIEW yy AS SELECT y1.a, y1.b, y2.c, y2.d FROM y1, y2 WHERE y1.a=y2.c; View created select * from yy; a 1 b 2 c 1 d 3 1 row(s) retrieved. ----------- Oracle --------- CREATE TABLE y1 (a VARCHAR2(20), b VARCHAR2(20)); Table created. CREATE TABLE y2 (c VARCHAR2(20), d VARCHAR2(20)); Table created. INSERT INTO y1 VALUES ('1','2'); 1 row created. INSERT INTO y2 VALUES ('1','3'); 1 row created. CREATE VIEW yy AS SELECT y1.a, y1.b, y2.c, y2.d FROM y1, y2 WHERE y1.a=y2.c; View created. DESC yy; Name Null? Type A VARCHAR2(20) B VARCHAR2(20) C VARCHAR2(20) D VARCHAR2(20) SELECT * FROM yy; A B C D 1 2 1 3#f2dcdc 1134 code active 2005 Feb anonymous 2005 Feb drh 3 3 select command with a view and a condition gets no result In a view with more than one tables it is no longer possible to use that view within a select command; the columns of the view are not found. #f2dcdc 709 code active 2004 Apr anonymous Unknown 2005 Jan drh 1 1 Unable to unregister or replace functions I believe this started with 2.8.13, as it did work previously. There appears to be a show-stopper with unregistering or replacing existing functions. Specifically, if one tries to replace (or remove by passing nulls) one of the built-in functions, for example "like" or "upper", the function does not get replaced, and is in fact still called and available. The odd thing is that if you try to replace one of the functions with an underscore, such as "change_count", it works fine! This is causing problems as we replace a lot of the existing functions, and allow users to add and replace their own functions, which are now failing. _2004-Apr-26 20:56:19 by anonymous:_ {linebreak} Alright, turns out this is due to mismatch in argument count when unregistering functions. It would be useful if we could unregister all instances of a function name, irregardless of argument counts. ---- _2004-Apr-26 23:11:43 by anonymous:_ {linebreak} Turns out there really is a bug... the problem is in the sqliteFindFunction function's matching of inexact argument counts, when being called from sqliteExprCheck with >0 argument count. This is the scenario. I override the "upper" function with my own, but first removing the old "upper", specifying 1 argument and null for the function. I then register a new "upper" with -1 for the argument count, and a valid function. When sqliteFindFunction attempts to locate the upper function, It locates the new function, but because it is registered with -1, it tries to find a better match. It then runs into the original one, and because it has a null function pointer, it fails. This causes sqliteExprCheck to try again with -1 as the count, and since that matches, it reports an error of wrong_num_args. Unfortunately this means there is no way to override an existing method (it would be good if we could just delete them, rather than override them, although I still think this behaviour with -1 is wrong). ---- _2004-Apr-26 23:29:23 by anonymous:_ {linebreak} I've applied the following patch in the sqliteFindFunction function, that I believe addresses the problem:
/* Change this if( p && !createFlag && p->xFunc==0 && p->xStep==0 ){ return 0; } */ /* To this */ if( p && !createFlag && p->xFunc==0 && p->xStep==0 ){ return pMaybe;By returning pMaybe we provide a function that will work, while returning 0 if no variable argument function was found. #f2dcdc 822 code active 2004 Jul anonymous Unknown 2004 Jul drh 1 4 port of sqlite to dos with djgpp + small general bugfixes here is a diff to be applied on sqlite 2.8.14 to make it work with djgpp. some of the fixes are needed for general purpose, such as relative path handling, and bypass of history and readline wherever not present. anyway, i see no harm to apply this patch to mainstream sqlite. port for version 2.8.15 will come soon. best regards, alex
typedef unsigned long long int rowNumberContext;
// aggregate step callback
void rowNumberStep(sqlite3_context *context, int argc, sqlite3_value **argv) {
// initialize or get aggregate function context
rowNumberContext *agg_context = (rowNumberContext*)sqlite3_aggregate_context(context, sizeof(rowNumberContext));
(*agg_context)++;
}
// aggregate final callback
void rowNumberFinal(sqlite3_context *context) {
sqlite3_result_int64(context, *((rowNumberContext *)sqlite3_aggregate_context(context, sizeof(rowNumberContext))));
}
// then just create function:
sqlite3_create_function(db_handle, "row_number", 0, SQLITE_ANY, null, null, rowNumberStep, rowNumberFinal);
I hope I get it right. This piece of sample code has *NOT* been tested! Use it at your own risk. ----------- The code above is a good implementation of count(*), but not row_number(). I think Row_Number() is one of the SQL2003 windowing functions. Implementing these would require modifications to the parser, compiler and vdbe layers of sqlite. Not possible using current APIs. At the current time queries that use Row_Number() will have to be rewritten to use temp tables as intermediate steps.
#e8e8bd 2205 build active 2007 Jan anonymous 2007 Jan anonymous 1 1 Problem while using with tcl on ARM I am using the sqlite-3.3.12. I have compiled this version for ARM and mandrake linux. On PC it is working fine. But on the Hand Held device with tcl, it produce error after creating the database file that "database disk image is malformed" while executing query for creating table. Another problem is that on executing sqlite3 executable on PC it shows version 3.3.11 But on executing sqlite3 executable on hand held it shows version 3.3.12 though these both executables were compiled from same source that is sqlite 3.3.12.
#f2dcdc 2061 code active 2006 Nov anonymous 2006 Nov anonymous 5 5 cleanup for quickstart.html just compiled the C example from quickstart.html (gcc/glibc Debian SID) a small hint (a bit pea-counting): to avoid warnings either #include#ifndef SQLITE_OMIT_MEMORYDB /* ** Clear a PgHistory block */ static void clearHistory(PgHistory *pHist){ sqliteFree(pHist->pOrig); sqliteFree(pHist->pStmt); pHist->pOrig = 0; pHist->pStmt = 0; } #else #define clearHistory(x) #endif#e8e8bd 1683 new active 2006 Feb anonymous Unknown 2006 Feb anonymous 4 3 .mode html produces uppercase tags Quote from one of sqlite docs: "The last output mode is "html". In this mode, sqlite writes the results of the query as an XHTML table. The beginning
.mode columns .headers on select 'Create two tables, with nasty column names.' as remark; create table t_a (c_a integer); create table t_b (c_a integer); select 'Create two views which each alias the column names of the above tables.' as remark; create view v_a as select c_a as pretty from t_a; create view v_b as select c_a as pretty from t_b; select 'Insert some data' as remark; insert into t_a values (1); insert into t_b values (2); select 'Notice that the views work fine by themselves.' as remark; select 'The column names are both as we asked.' as remark; select pretty from v_a; select pretty from v_b; select 'Notice that used in concert, with a join, the column name is now wrong.' as remark; select pretty from v_a union select pretty from v_b; select 'Aliasing the name of the column in the first half of the join is no help.' as remark; select pretty as pretty from v_a union select pretty from v_b; select 'Alias the name of the column in the second half of the join "fixes" the result.' as remark; select pretty from v_a union select pretty as pretty from v_b;_2005-Oct-25 03:05:27 by anonymous:_ {linebreak} same as ticket 1228 ---- _2005-Oct-26 07:59:17 by anonymous:_ {linebreak} see also #1327 #e8e8bd 1475 new active 2005 Oct anonymous Parser 2005 Oct anonymous 5 5 sytax error near autoincrement Consider following sql syntax. create table t1 (id integer primary key autoincrement); // no error create table t2 (id integer autoincrement primary key); // error create table t3 (id integer autoincrement, primary key(id)); // error _2005-Oct-07 03:15:23 by anonymous:_ {linebreak} Your last two examples don't follow the syntax shown in the documentation. AUTOINCREMENT is a modifier for a PRIMARY KEY constraint and can only come after one. #e8e8bd 241 new active 2003 Feb anonymous 2005 Jun anonymous 5 5 lack of alter table statement Have you any plans for the alter table statement? #e8e8bd 1209 doc active 2005 Apr anonymous Unknown 2005 Apr anonymous 3 5 SQLite3_Free_Table returns not #SQLITE_OK if suceeds Using the function SQLITE3_Get_Table I get back a pointer to memory where the data table is stored. After reading this table I try to free the memory under use of command SQLITE3_Free_Table (using Windows DLL V3.2.1). I expect that, if freeing succeds, the answer "#SQLITE_OK". But instead I get mostly #SQLITE_ERROR, but sometimes very big numbers. In fact I checked the memory usage of my program. When I use SQLITE_Free_Table with a wrong pointer, the answer of DLL is #SQLITE_OK while the memory usage of my program increases. Whe I use the correct pointer the answer of SQLITE3_Free_Table us #SQLITE_ERROR, but the memory usage of my program remains the same as before the SQLITE3_Get_Table command. For me it seems as if the answer for the "free" command is not correct. #f2dcdc 923 code active 2004 Sep anonymous 2004 Sep anonymous 3 2 Missing quotes in 2.8.15 .dump cause data loss when loading in sqlite3 When converting a database by means of the command: sqlite old.db .dump | sqlite3 new.db the content of char/varchar fields is dumped by sqlite without quotes (e.g. 00001) and then when reloaded by sqlite3 it looses the heading zeroes (i.e. becomes '1', which is a really different thing for an alphanumeric field). This could be solved by a new release (sqlite 2.8.16 ?) which add quotes to alphanumeric fields (as sqlite3 does), or by a filter script that adds the quotes to the sqlite2 .dump output (I used a quick and dirty perl script to fix my dump...). _2005-Jul-11 20:08:11 by anonymous:_ {linebreak} This does not appear to be solved in sqlite 2.8.16. #f2dcdc 832 code active 2004 Jul anonymous Unknown 2004 Jul anonymous 4 4 os_win.c :: sqlite3OsUnlock() method has an unused variable: rc os_win.c :: sqlite3OsUnlock() method has an unused variable: rc #e8e8bd 802 warn active 2004 Jul anonymous 2004 Jul anonymous 1 1 Unable to compile the sqlite - compilation of many modules produces warnings, warnings, warnings, warnings ...................... warnings. - Borland 5.02 is not able to compile some modules at all (compiler internal error). #e8e8bd 762 todo active 2004 Jun anonymous Unknown 2004 Jun anonymous 4 3 Windows pre-compiled binaries are 2.8.13, not 2.8.14 Windows pre-compiled binaries are 2.8.13, not 2.8.14 #e8e8bd 746 warn active 2004 May anonymous CodeGen 2004 May anonymous 5 5 How can I make sqlite support serial term? How can I make sqlite support "serial" term? I just put some code into build.c and this is the cord that I changed. void sqlite3AddPrimaryKey(Parse *pParse, IdList *pList, int onError){
./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 #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 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 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 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 #e8e8bd 2716 new active 2007 Oct anonymous 2007 Oct 5 1 Create Clear Command I want a command caled "clear" like in MySQL. This command should erase the screen and then put the sqlite pointer on top of the screen _2007-Oct-11 07:41:45 by anonymous:_ {linebreak} How about a cookie instead? ---- _2007-Oct-30 08:02:04 by anonymous:_ {linebreak} Clearing the screen and moving the cursor are platform-dependent operations. On Unix they are not only platform-dependent, but also terminal-dependent. Thus such a feature does not really belong in the cross-platform and minimalistic sqlite3 shell (in my opinion). #e8e8bd 2749 warn active 2007 Oct anonymous 2007 Oct 3 4 SQLITE_OMIT_FLOATING_POINT, int constants too large Hiya! i'm working from the 3.5.1 (CVS version) of the amalgamation. The code was pulled from CVS sometime around Oct 27th 2007. Platform: Kubuntu Linux 7.04, i386-32, gcc 4.1.2. The compiler output says it all: gcc -c -DSQLITE_OMIT_FLOATING_POINT sqlite3.c sqlite3.c: In function 'bestVirtualIndex': sqlite3.c:65531: warning: integer constant is too large for 'long' type sqlite3.c: In function 'bestIndex': sqlite3.c:65600: warning: integer constant is too large for 'long' type sqlite3.c: In function 'sqlite3WhereBegin': sqlite3.c:66223: warning: integer constant is too large for 'long' type sqlite3.c:66248: warning: integer constant is too large for 'long' type sqlite3.c:66254: warning: integer constant is too large for 'long' type These warnings would seem to indicate potentially serious problems, though i admittedly have not investigated whether overflows are really fatal in the affected contexts. #e8e8bd 2748 warn active 2007 Oct anonymous 2007 Oct 4 4 amalgamation: SQLITE_OMIT_ALTERTABLE warnings Hiya! i'm working from the 3.5.1 (CVS version) of the amalgamation. The code was pulled from CVS sometime around Oct 27th 2007. The compiler output says it all: gcc -c -DSQLITE_OMIT_ALTERTABLE sqlite3.c sqlite3.c:6909: warning: 'sqlite3AlterRenameTable' used but never defined sqlite3.c:6916: warning: 'sqlite3AlterFinishAddColumn' used but never defined sqlite3.c:6917: warning: 'sqlite3AlterBeginAddColumn' used but never defined The relevant code is: sed -ne '6909p;6916p;6917p' sqlite3.c SQLITE_PRIVATE void sqlite3AlterRenameTable(Parse*, SrcList*, Token*); SQLITE_PRIVATE void sqlite3AlterFinishAddColumn(Parse *, Token *); SQLITE_PRIVATE void sqlite3AlterBeginAddColumn(Parse *, SrcList *); Platform: Kubuntu Linux 7.04, i386, gcc 4.1.2. #f2dcdc 2392 code active 2007 May anonymous 2007 Oct 4 4 Reduce MemPage and PgHdr struct sizes. Better page memory utilization. Patch to reduce sizeof(MemPage) below. It saves 8 bytes per cached page or in-memory page on Linux. sizeof(MemPage) on Linux: original: 84 patched: 76 Patched "make test" runs without regressions on Linux and Windows. Timings for "make test" (elapsed): original: 1:20.74 patched: 1:20.22 Size of sqlite3.o when compiled from almalogmation with all sqlite features enabled with gcc flags -O3 -fomit-frame-pointer: original: 586976 bytes patched: 587880 bytes Patched sqlite3.o is 904 bytes larger. Index: src/btreeInt.h =================================================================== RCS file: /sqlite/sqlite/src/btreeInt.h,v retrieving revision 1.4 diff -u -3 -p -r1.4 btreeInt.h --- src/btreeInt.h 16 May 2007 17:28:43 -0000 1.4 +++ src/btreeInt.h 30 May 2007 16:26:03 -0000 @@ -269,15 +269,15 @@ typedef struct BtLock BtLock; */ struct MemPage { u8 isInit; /* True if previously initialized. MUST BE FIRST! */ - u8 idxShift; /* True if Cell indices have changed */ u8 nOverflow; /* Number of overflow cell bodies in aCell[] */ - u8 intKey; /* True if intkey flag is set */ - u8 leaf; /* True if leaf flag is set */ - u8 zeroData; /* True if table stores keys only */ - u8 leafData; /* True if tables stores data on leaves only */ - u8 hasData; /* True if this page stores data */ - u8 hdrOffset; /* 100 for page 1. 0 otherwise */ - u8 childPtrSize; /* 0 if leaf==1. 4 if leaf==0 */ + u8 hdrOffset:7; /* 100 for page 1. 0 otherwise */ + u8 zeroData:1; /* True if table stores keys only */ + u8 childPtrSize:3; /* 0 if leaf==1. 4 if leaf==0 */ + u8 leaf:1; /* True if leaf flag is set */ + u8 idxShift:1; /* True if Cell indices have changed */ + u8 intKey:1; /* True if intkey flag is set */ + u8 leafData:1; /* True if tables stores data on leaves only */ + u8 hasData:1; /* True if this page stores data */ u16 maxLocal; /* Copy of Btree.maxLocal or Btree.maxLeaf */ u16 minLocal; /* Copy of Btree.minLocal or Btree.minLeaf */ u16 cellOffset; /* Index in aData of first cell pointer */ _2007-Sep-07 05:58:45 by anonymous:_ {linebreak} Any word on applying this patch? It saves 8 bytes per MemPage. For the default 2000 page cache it would save 16000 bytes. Larger page caches would save considerably more memory. ---- _2007-Oct-26 21:08:03 by anonymous:_ {linebreak} With this patch sizeof(PgHdr) is reduced by 4 bytes (from 48 bytes to 44 bytes) for gcc. No regressions. "make test" runs in the same amount of time.
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. #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. #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 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);