int sqlite3VdbeChangeEncoding(Mem *pMem, int desiredEnc){
int rc;
if( !(pMem->flags&MEM_Str) || pMem->enc==desiredEnc ){
return SQLITE_OK;
}
#ifdef SQLITE_OMIT_UTF16
return SQLITE_ERROR;
#else
...
If SQLITE_OMIT_UTF16 is defined then the "rc" variable is unused and compiler (Windows Intel 7.0) emits useless warning.
#e8e8bd 1638 warn active 2006 Jan anonymous 2006 Jan 3 2 rows place change and some row element missing there is a problem with my table row order.I miss one roe header and the next one come to its place.also there is a problem like this in the columns too.it does not occur when the table is list or line.but when I turn it into column mode the problem happens.my table become puzzling
#e8e8bd 1627 warn active 2006 Jan anonymous 2006 Jan 4 4 warnings on BCB and how to resolve them + couple of minor fixes I took 3.3.1 sources (the preprocessed version) and tried to compile them on Borland C++ Builder 6.4. I wanted to keep all compiler warnings switched on. I got some warnings from sqlite source. Some due to strlen() returning unsigned, some due signed/unsigned comparison, some because BCB requires if (a = b) ... to be rewritten as if ((a = b) != 0) ... to avoid warnings. List of places generating warnings and ways to shut then up are bellow. Perhaps these fixes may be applied to the codebase since the warnings may happen with other C++ compilers as well. I cannot promise the fixes will work with 64bit architectures but it seems likely. ----------------------------------------- Some macros, like MASTER_NAME, may cause collisions. Perhaps they all can be prefixed with SQLITE3 or so to avoid such accidents. -------------------------------------------- The trick to enable NDEBUG in sqliteInt.h: #if !defined(NDEBUG) && !defined(SQLITE_DEBUG) # define NDEBUG 1 #endif has unfortunate property of changing NDEBUG settings in those parts of a project using sqlite and not in the others. It is hard to detect reason for possible problems. I suggest: 1) Put on top of each sqlite source file #define THIS_IS_SQLITE_SOURCE 2) Change the trick to: #if !defined(NDEBUG) && !defined(SQLITE_DEBUG) && (defined THIS_IS_SQLITE_SOURCE) # define NDEBUG 1 #endif -------------------------------------------- Small wish for the documentation: could there be information how to switch off all sqlite memory management and checking and leave it all to host's malloc/realloc/free? I have very optimized dmalloc allocator and want to be sure there are no other layers above this. My allocator also does boundary checking and error detection. -------------------------------------------- ---- FIXES TO GET RID OF WARNINGS ON BCB --- -------------------------------------------- vdbmem.c, line 725: assert( strlen(pMem->z)<=pMem->n ); ==>> assert( (int)strlen(pMem->z)<=pMem->n ); -------------------------------------------- vdbeaux.c: line 545: if( strlen(zTemp)+strlen(zNum)+1<=nTemp ){ ==>> if( (int)strlen(zTemp)+(int)strlen(zNum)+1<=nTemp ){ line 1740: if( d1>=nKey1 && sqlite3VdbeSerialTypeLen(serial_type1)>0 ) break; ==>> if( (int)d1>=nKey1 && sqlite3VdbeSerialTypeLen(serial_type1)>0 ) break; line 1742: if( d2>=nKey2 && sqlite3VdbeSerialTypeLen(serial_type2)>0 ) break; ==>> if( (int)d2>=nKey2 && sqlite3VdbeSerialTypeLen(serial_type2)>0 ) break; line 1768: }else if( d1Compiling... attach.c auth.c btree.c btree_rb.c C:\Work\projects\SQLite_lib\src\btree.c(1920) : warning C4761: integral size mismatch in argument; conversion supplied C:\Work\projects\SQLite_lib\src\btree.c(1922) : warning C4761: integral size mismatch in argument; conversion supplied C:\Work\projects\SQLite_lib\src\btree.c(537) : warning C4761: integral size mismatch in argument; conversion supplied C:\Work\projects\SQLite_lib\src\btree.c(541) : warning C4761: integral size mismatch in argument; conversion supplied C:\Work\projects\SQLite_lib\src\btree.c(559) : warning C4761: integral size mismatch in argument; conversion supplied C:\Work\projects\SQLite_lib\src\btree.c(503) : warning C4761: integral size mismatch in argument; conversion supplied C:\Work\projects\SQLite_lib\src\btree.c(504) : warning C4761: integral size mismatch in argument; conversion supplied C:\Work\projects\SQLite_lib\src\btree.c(440) : warning C4761: integral size mismatch in argument; conversion supplied C:\Work\projects\SQLite_lib\src\btree.c(451) : warning C4761: integral size mismatch in argument; conversion supplied build.c copy.c date.c C:\Work\projects\SQLite_lib\src\date.c(234) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(235) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(339) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(340) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(343) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(344) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(345) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(346) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(359) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(360) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(362) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(390) : warning C4244: 'initializing' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(396) : warning C4244: '=' : conversion from 'double ' to 'long ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(503) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(510) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(584) : warning C4244: '+=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(590) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(596) : warning C4244: '+=' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(772) : warning C4244: 'initializing' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(773) : warning C4244: 'initializing' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\date.c(787) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data delete.c expr.c func.c hash.c insert.c main.c opcodes.c os.c C:\Work\projects\SQLite_lib\src\os.c(925) : warning C4244: 'initializing' : conversion from '__int64 ' to 'long ', possible loss of data C:\Work\projects\SQLite_lib\src\os.c(926) : warning C4244: 'initializing' : conversion from '__int64 ' to 'long ', possible loss of data C:\Work\projects\SQLite_lib\src\os.c(1017) : warning C4244: 'initializing' : conversion from '__int64 ' to 'long ', possible loss of data C:\Work\projects\SQLite_lib\src\os.c(1018) : warning C4244: 'function' : conversion from '__int64 ' to 'long ', possible loss of data pager.c C:\Work\projects\SQLite_lib\src\os.c(1018) : warning C4761: integral size mismatch in argument; conversion supplied C:\Work\projects\SQLite_lib\src\pager.c(602) : warning C4244: '=' : conversion from '__int64 ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\pager.c(605) : warning C4244: '=' : conversion from '__int64 ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\pager.c(720) : warning C4244: '=' : conversion from '__int64 ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\pager.c(928) : warning C4244: '=' : conversion from '__int64 ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\pager.c(930) : warning C4244: 'return' : conversion from '__int64 ' to 'int ', possible loss of data parse.c pragma.c parse.c(3985) : warning C4761: integral size mismatch in argument; conversion supplied parse.c(3996) : warning C4761: integral size mismatch in argument; conversion supplied printf.c random.c select.c C:\Work\projects\SQLite_lib\src\select.c(102) : warning C4018: '==' : signed/unsigned mismatch shell.c table.c tclsqlite.c tokenize.c trigger.c update.c util.c vacuum.c vdbe.c C:\Work\projects\SQLite_lib\src\vdbe.c(1295) : warning C4244: 'initializing' : conversion from 'double ' to 'int ', possible loss of data C:\Work\projects\SQLite_lib\src\vdbe.c(1310) : warning C4244: '=' : conversion from 'double ' to 'int ', possible loss of data vdbeaux.c where.c Creating library..._2004-Jul-22 06:42:15 by anonymous:_ {linebreak} Anything happening here ? I actually try to convince my boss to evaluate SQLite for our project but as long as there are so many warnings I have no chance. I think it's quite crucial to get a clean compile, especially those "possible loss of data" warnings are quite horrible for a database ;-) #e8e8bd 503 warn active 2003 Nov anonymous Unknown 2003 Dec anonymous 5 2 Include sqlite source in Visual C++ 6.0 SP5 when I try to use sqlite source in my VC6 project,there a lot of errors with compile,then I change the precompiled header setting to NONE,now no errors,but there so many warning about type,I suggest forced convertion type should be used then it will look more official,and there a "( )" should be inside the MACRO defination for SWAB16(?). #e8e8bd 391 warn active 2003 Jul anonymous 2003 Dec 4 3 signed/unsiged compiler warnings from Borland C++6 When I compile the 2.8.4 source code (as of 2003-07-14) with Borland's C++ Builder 6 compiler I get warnings about signed-unsigned comparisons and suspicious pointer conversions (int * vs unsigned *) in two files. pager.c produces the following messages: [C++ Warning] pager.c(598): W8075 Suspicious pointer conversion [C++ Warning] pager.c(613): W8012 Comparing signed and unsigned values select.c produces the following message: [C++ Warning] select.c(99): W8012 Comparing signed and unsigned values These warnings can be eliminated by the small changes detailed in the attached diff files. #e8e8bd 348 warn active 2003 Jun anonymous Unknown 2003 Dec drh 4 3 SQLite and GCC using enhanced warning levels SQLite doesn't compile cleanly if you enable the following GCC compiler warnings: -Wcast-qual -Wmissing-declarations -Wnested-externs -Wuninitialized -Wwrite-strings Most of these can be fixed by changing "char *" to "char const *" in a few judicious places. #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). #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? #e8e8bd 2476 todo active 2007 Jun anonymous 2007 Jun 4 3 SQLite3 ignores ORDER BY clause when performing SELECT ... GROUP BY I found that sqlite3 ignores the ORDER BY clause when performing SELECT ... GROUP BY ... ORDER BY ... Table schema: CREATE TABLE events ( id integer not null primary key, title integer ); Data: |id|title| |1|hello| |2|hello| Query: SELECT title, id FROM events GROUP BY title ORDER BY id ASC; Result: |title|id| |hello|2| Expected result: |title|id| |hello|1| Note: I don't think this should even work in the first place, because id is not a grouped column, but MySQL and SQLite doesn't seem to have a problem with it. Oracle complains. _2007-Jun-29 07:46:00 by danielk1977:_ {linebreak} In SQL, the sorting specified by the ORDER BY clause is performed (logically) after the grouping specified by the GROUP BY clause. In this case a single row - the ORDER BY clause is redundant. So the problem is that SQLite and MySQL are implementing the non-standard SQL extension of allowing an expression that is neither an aggregate or a part of the GROUP BY clause in the result-set of the SELECT in a different way. #e8e8bd 2274 todo active 2007 Mar anonymous 2007 Mar drh 5 3 Sqlite segfaults consistently in FTS2 Sqlite segfaults consistently in FTS2 sqlite> delete from mail where subject_='backup failed'; bt Program received signal EXC_BAD_ACCESS, Could not access memory. Reason: KERN_PROTECTION_FAILURE at address: 0x00000000 0x00025726 in deleteTerms (v=0x404e20, pTerms=0xbfffe95c, iRowid=10503) at ./ext/fts2/fts2.c:1418 1418 return string_dup_n(s, strlen(s)); (gdb) bt #0 0x00025726 in deleteTerms (v=0x404e20, pTerms=0xbfffe95c, iRowid=10503) at ./ext/fts2/fts2.c:1418 #1 0x00028c49 in fulltextUpdate (pVtab=0x404e20, nArg=1, ppArg=0x180e440, pRowid=0xbfffebf8) at ./ext/fts2/fts2.c:3678 #2 0x000541df in sqlite3VdbeExec (p=0x180de00) at ./src/vdbe.c:4877 #3 0x00012b46 in sqlite3_step (pStmt=0x180de00) at ./src/vdbeapi.c:236 #4 0x0001c392 in sqlite3_exec (db=0x400180, zSql=0x404850 "delete from mail where subject_='backup failed';", xCallback=0x3902
# ## 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"
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. #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. #e8e8bd 2417 new active 2007 Jun anonymous 2007 Nov drh 3 3 Idea for read write concurrency. This is not a problem, but rather an idea on how to resolve the reader/writer concurrency issues encountered in sqlite. The idea is to allow a reader and writer to work concurrently not blocking each other. Dual writers would of course block. When a write occurs: 1. block level changes are made to the database file. 2. Pre-image of that change is written to the journal. Readers: 1. File I/O on the main file would occur normally. 2. If the block encountered is "new" ie one that was written out by the writer. Then get the original block from the Journal file. In order to determine "NEW" a change number could be put on each block. When a READ (select) begins it would first determine the starting global change number. (maybe on the master block?) When a write occurs it would read the Master blocks change number. (increment this in memory) and use write new blocks with the new value. At commit. The Master block would be updated and the txn journal marked for purge if there are pending reads. -- Drawbacks: Reading becomes dependent upon the txn journal. -- Implementation of BLOCK level versioning may ultimately be a simpler approach. Idea would be for a seperate file conaining versioned blocks. This file could be accessed instead of the txn journal. _2007-Nov-08 15:12:00 by anonymous:_ {linebreak} DRH: Also unaddressed in the proposal is how to locate a particular page within the journal file without having to do (performance killing) sequential scan of the possible very large file. Resolution of page access to avoid sequential scans of Txn Journal. When a writer is making the modification to a page first it writes the original page to the journal. At this point the journal file offset location is known. Save this offset in the "NEW" page being written into the database file. This implements a backwards chaining of pages into the txn journal. The reader upon reading the db file page would recognize (see above) that the page is dirty. Acquire the txn journal offset from the dirty page, Read the page from the journal until the starting page is found. This would eliminate any sequential scanning, but may require more than one read request. #e8e8bd 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) #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 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 55 new active 2002 Jun anonymous 2007 Oct drh 5 3 instead of triggers for inserts (updates) on regular tablesxx It would be very useful to be able to define a trigger that only executes the trigger code and will prevent the actual insert (or update) on regular tables (eg. specialised autoincrement fields). This could be done by allowing the same "instead of" syntax as used for views. Even better would be the possibility of return codes in a before trigger that can prevents the insert or raise an error. Ideally, this return code could be given conditionally (implementing foreign key and check like functionality). test ---- _2007-Oct-01 23:37:44 by anonymous:_ {linebreak} {link: http://www.sqlite.org/lang_createtrigger.html create trigger syntax} :{linebreak} A special SQL function RAISE() may be used within a trigger-program, with the following syntax: raise-function ::= RAISE ( ABORT, error-message ) | RAISE ( FAIL, error-message ) | RAISE ( ROLLBACK, error-message ) | RAISE ( IGNORE ) #e8e8bd 2677 new active 2007 Sep anonymous 2007 Sep danielk1977 5 3 CREATE TRIGGER .. {databaseevent ON ... }+ for multiple events I have to write triggers which react on either INSERT or UPDATE of a specific field in a table. In some SQL syntax descriptions I found a possibility to combine the trigger events for more than one condition but not in SQLite3. Isn't there a possibility to use one trigger definition for more than one event or didn't I find the trick? Do I have to copy the whole definition for each event; even if the body text is exactly the same? {linebreak} Suggestion: On INSERT event use the OLD.fieldnames filled with NULL content to avoid problems with UPDATE event. - If not possible please explain why not for my better understanding. Thank you. #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 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 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. #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? #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 #e8e8bd 2520 new active 2007 Jul anonymous 2007 Jul 4 1 User defined aggregate functions are not reentrant When an aggregate function is defined using sqlite3_create_function, it is not possible to execute any sql statement inside the step part or the finalizer. This is due to the fact that aggregate functions are not reentrant. _2007-Jul-20 02:32:36 by anonymous:_ {linebreak} related: Ticket #2242: sqlite3 authorizer is not reentrant #e8e8bd 2484 new active 2007 Jul anonymous 2007 Jul 5 4 Support for RETURNING I was recently trying to get HTSQL (http://htsql.org) to work with SQLite, especially since it'd be nice to work out-of-the-box with Python. One of the hiccups was the lack of a RETURNING clause, this is especially important once you have auto-incremented keys. For example.. INSERT INTO TABLE some_table (a_column) values ('value') RETURNING (serial_column); This acts like a SELECT following the INSERT returning the requested columns on the affected rows. It is quite helpful for cases like UPDATE or DELETE when more than one row is affected. While this feature isn't critical for SQLite, it reduces client-side code significantly. Thank you for your kind consideration. #e8e8bd 2474 new active 2007 Jun anonymous 2007 Jun 5 4 Multiple-record comma-delineated INSERT command I believe that both MySQL and DB2 support this feature. Instead of using separate commands for multiple INSERTS, you could use one command, and delineate the separate INSERT data with commas. Having support for this type of INSERT would make migrating MySQL or DB2 files to SQLite easier. Regular INSERT method: INSERT INTO foo VALUES ('Title1',26,NULL); INSERT INTO foo VALUES ('Title2',24,NULL); INSERT INTO foo VALUES ('Title3',12,NULL); Delineated INSERT method: INSERT INTO foo VALUES ('Title1',26,NULL), ('Title2',24,NULL), ('Title3',12,NULL); _2007-Jun-29 12:32:45 by anonymous:_ {linebreak} From the parsing point of view this is a bit interesting. Imagine the multi-insert statement is 100,000 lines long. Do you parse the entire statement for correctness first and hold this entire parsed tree in memory? Or do you begin a transaction, and try to process each sub-insert row by row and rollback if there's any error? I'd think the latter would be better from both a time and memory point of view. Actually, this multi-insert statement could be optimized to work around the sqlite slow bulk insert issue with multiple keys. #e8e8bd 2456 new active 2007 Jun anonymous 2007 Jun 5 5 REQ: use index where applicable instead of full table scan There are times when you need information from a table that's held entirely in an index. Would it not make sense to scan over the index in these cases as those are likely to be more densely packed and therefore faster and more cache friendly? sqlite> .sc CREATE TABLE t1 ( c1 integer, c2 text, c3 text ); CREATE INDEX idx1 on t1(c1); so here =SELECT SUM(c1) from t1= could be satisfied by scanning over idx1, which might be a lot smaller & more dense than t1. #e8e8bd 2448 new active 2007 Jun anonymous 2007 Jun 4 3 SQLITE needs to identify public exported symbols A number of platforms allow source code tagging of functions which are meant to be public functions exported in a shared library (most notably windows and platforms that use GCC 4.0 and above). This tagging is usually accomplished through some define placed in front of the function declaration and definition (SQLITE3_PUBLIC sqlite3_open() for example), and SQLITE3_PUBLIC is set to the proper declaration for the given platform. Example: windows it would be: __declspec(dllexport) GCC it would be: __attribute__ ((visibility("default"))) see http://gcc.gnu.org/wiki/Visibility for more information. #e8e8bd 2443 new active 2007 Jun anonymous 2007 Jun 3 3 sqlite should return different exit codes for different errors sqlite should return different exit codes for different errors reported. sqlite always returns exit code 0 or 1. It would be helpful to have different codes. I/O error, locked, interrupted, busy etc. should declare defined return codes. If sqlite is executed from a shell script it is difficult to handle plain text that could change or be reformatted in a later version. Thanks. _2007-Jun-21 19:59:41 by anonymous:_ {linebreak} Just run sqlite3 -batch -bail and grep for the error in the last line. #e8e8bd 2438 new active 2007 Jun rse 2007 Jun 3 3 More easily allow the building of SQLite with FTS1 and FTS2 I don't know what the _intended_ way is to build SQLite with FTS1 and/or FTS2, but for the OpenPKG "sqlite" package I at least now use the following change -- as I was unable to find any other automated solution. I know that FTS1 and FTS2 are experimental extensions, but if it is too complicated for people to build SQLite with them, they certainly will never become non-experimental ;-) So I recommend to at least provide some build-time glue for them. In the OpenPKG "sqlite" package I now use the following patch which at least provides this glue (one still has to enable it, of course):
Index: Makefile.in --- Makefile.in.orig 2007-06-14 22:54:38 +0200 +++ Makefile.in 2007-06-20 18:09:00 +0200 @@ -130,6 +130,18 @@ vdbe.lo vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbefifo.lo vdbemem.lo \ where.lo utf.lo legacy.lo vtab.lo +# FTS1 support +ifdef FTS1 +TCC += -DSQLITE_ENABLE_FTS1 +LIBOBJ += fts1.lo fts1_hash.lo fts1_porter.lo fts1_tokenizer1.lo +endif + +# FTS2 support +ifdef FTS2 +TCC += -DSQLITE_ENABLE_FTS2 +LIBOBJ += fts2.lo fts2_hash.lo fts2_porter.lo fts2_tokenizer1.lo +endif + # All of the source code files. # SRC = \ @@ -498,6 +510,23 @@ -o testfixture $(TESTSRC) $(TOP)/src/tclsqlite.c \ libsqlite3.la $(LIBTCL) +fts1.lo: $(TOP)/ext/fts1/fts1.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1.c +fts1_hash.lo: $(TOP)/ext/fts1/fts1_hash.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1_hash.c +fts1_porter.lo: $(TOP)/ext/fts1/fts1_porter.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1_porter.c +fts1_tokenizer1.lo: $(TOP)/ext/fts1/fts1_tokenizer1.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts1/fts1_tokenizer1.c + +fts2.lo: $(TOP)/ext/fts2/fts2.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2.c +fts2_hash.lo: $(TOP)/ext/fts2/fts2_hash.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2_hash.c +fts2_porter.lo: $(TOP)/ext/fts2/fts2_porter.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2_porter.c +fts2_tokenizer1.lo: $(TOP)/ext/fts2/fts2_tokenizer1.c $(HDR) + $(LTCOMPILE) -c $(TOP)/ext/fts2/fts2_tokenizer1.c fulltest: testfixture$(TEXE) sqlite3$(TEXE) ./testfixture $(TOP)/test/all.test#e8e8bd 2411 new active 2007 Jun anonymous 2007 Jun 5 3 fts2 RFE - Separate index and storage It is my understanding of the current implementations of the fts modules that you save your text into the chosen table.column and in the background an index is generated for use by a special search operator. IMHO this puts two distinct concepts together, namely the storage of the text to be indexed, and its actual indexing for full-text-search. What I am asking for here is the ability to create an fts-index without having the system store the text itself. I.e. putting the text into the table.column extends the index, but will not save the text. This allows several things not possible with the current implementations: 1: Storage of the text outside of the database (fts-index joined to path names). 1: Compressed storage of text in the database (fts-index joined to separate blob table). While I currently have no real idea yet about usecases for the first item I do see the following possible applications for the last item: 1: SCM systems which wish to allow fts over all versions of a file, without giving up delta-compression between revisions. 1: Help files which allow fts despite being space-efficient due to compressed storage of the help pages (zlib). In both cases the current implementations of fts would force the applications to choose between either space efficiency, or searchability. _2007-Jun-13 07:23:58 by anonymous:_ {linebreak} This mailing list thread lists additional usage scenarios and arguments in favor of separating FTS index from text storage. It also gives some DB size savings statistics: #e8e8bd 1597 new active 2006 Jan anonymous Unknown 2007 May 1 3 wish: support DROP COLUMN Please - add support for "drop column". For GIS user, as I, the Sqlite3 is a great, lighweight, powerfull tool for mananaging spatial data attributes. However, the lack of "drop column" seriously hampers it's usefullnes and still forces many GIS folks to use eg. Postgres, which is much more hard to handle than Sqlite - especially for newbies. Plese note that Sqlite3 support was recently added to Grass (http://grass.itc.it), the most powerfull FOSS GIS. There were even discussions about making Sqlite3 a default database driver for Grass - easy to use, powerfull and fast. Please see: http://search.gmane.org/?query=sqlite+default&email=&group=gmane.comp.gis.grass.devel&sort=relevance&DEFAULTOP=and&%3E=Next&xP=sqlite.default.&xFILTERS=Gcomp.gis.grass.devel---A Extending the alter table commands, "drop column" most of all, could incline Grass devs to do so even more. There already were sevaral requests from Grass users regarding "drop column" in Grass sqlite driver: http://article.gmane.org/gmane.comp.gis.grass.user/11141 http://thread.gmane.org/gmane.comp.gis.grass.devel/9454 http://grass.itc.it/pipermail/grass5/2006-January/020764.html Any chances? Maciek _2006-Jan-10 22:51:16 by drh:_ {linebreak} http://www.sqlite.org/faq.html#q13 ---- _2006-Oct-24 21:37:43 by anonymous:_ {linebreak} Hope to see this feature too... ---- _2006-Oct-24 21:44:53 by anonymous:_ {linebreak} I did this in a way that I create a parses that undestand the drop column and modify column sintax, but they create a temporary table, copy data between old and new, then create the new table and copy data back. not efficient, but works. ---- _2007-May-20 08:26:54 by anonymous:_ {linebreak} Please give consideration to drop column. One of SQLite's most potent features is the tiny footprint embedding. Requiring a temporary table to perform simple structure alterations effectively halves the maximum safe size of a table with regards to its primary storage context. There's a reason it comes up as often as it does, despite the points in the FAQ. The temporary table hack is frequently a serious limiting factor. It seems likely that this wouldn't be a huge issue for the codebase, considering that other table structural alterations (add column, most notably) are apparently workable. Please? ---- _2007-May-20 12:56:56 by anonymous:_ {linebreak} What would you like DROP COLUMN to do? Copy the old table to a new table without the column in question, or just leave the old table in place without reclaiming space and simply ignore the old column? What about constraints on that column? If they are violated should the DROP COLUMN be ignored? #e8e8bd 1924 new active 2006 Aug anonymous Parser 2007 May 2 3 optimize queries on unions, constant folding Please see the attached patch that optimizes SELECTs on a compound subquery, or VIEWs containing UNIONS. pragma temp_store=memory; CREATE TABLE n1(a integer primary key); INSERT INTO "n1" VALUES(1); INSERT INTO "n1" VALUES(2); INSERT INTO "n1" VALUES(3); INSERT INTO "n1" VALUES(4); INSERT INTO "n1" VALUES(5); INSERT INTO "n1" VALUES(6); INSERT INTO "n1" VALUES(7); INSERT INTO "n1" VALUES(8); INSERT INTO "n1" VALUES(9); INSERT INTO "n1" VALUES(10); CREATE VIEW vu as select v3.a a, v5.a-v2.a*v7.a b from n1 v1,n1 v2,n1 v3,n1 v4,n1 v5,n1 v6,n1 v7; CREATE VIEW v2 as select * from vu union all select 7, 8; select count(*), avg(b) from v2 where a<3; The above query takes 58 seconds in sqlite 3.3.7, using 136M of temp_store. With the patch, it takes just 12 seconds and uses 26M of temp_store. The patch also performs 32 bit integer constant folding: sqlite> explain select 1*2+3-4%5/2|128; 0|Goto|0|4| 1|Integer|131|0| 2|Callback|1|0| 3|Halt|0|0| 4|Goto|0|1| 5|Noop|0|0| _2006-Aug-17 13:03:31 by anonymous:_ {linebreak} TK_REM (the '%' operator) is not handled correctly in the patch. It should follow the logic of TK_SLASH and check 'right' against zero. + case TK_REM: { v = left % right; break; } ... + case TK_SLASH: { + if (right) { + v = left / right; + } else { + return; + } + break; + } ---- _2006-Aug-17 15:50:20 by anonymous:_ {linebreak} These 2 cases can overflow a 32 bit value. The calculation should be done in 64 bit int math, and if the result can fit into 32-bits, then fold it, otherwise return (similar to TK_SLASH). + case TK_PLUS: { v = left + right; break; } + case TK_STAR: { v = left * right; break; } Or just handle all cases in 64-bit math. ---- _2006-Aug-18 10:11:53 by anonymous:_ {linebreak} Wouldn't TK_MINUS also be able to overflow 32-bit, just in the opposite direction, so to speak ? Example: -2000000000 - 2000000000 ---- _2006-Aug-18 13:51:05 by anonymous:_ {linebreak} should you make + i64 left; + i64 right; + i64 v; instead of + int left; + int right; + int v; to avoid int32 overflows. ---- _2006-Aug-18 16:27:21 by anonymous:_ {linebreak} The attachment sqlite337-union-and-constants-opt-v2.diff.txt addresses all reported issues and passes "make test" without any regressions. ---- _2006-Aug-18 19:41:20 by anonymous:_ {linebreak} You're not assuming that "right" could be a i64 value in this last patch... ---- _2006-Aug-19 13:41:14 by anonymous:_ {linebreak} right is a 32 bit value. /* ** If the expression p codes a constant integer that is small enough ** to fit in a 32-bit integer, return 1 and put the value of the integer ** in *pValue. If the expression is not an integer or if it is too big ** to fit in a signed 32-bit integer, return 0 and leave *pValue unchanged. */ int sqlite3ExprIsInteger(Expr *p, int *pValue){ ---- _2007-May-16 20:09:38 by anonymous:_ {linebreak} Updated patch as of May 16, 2007 CVS: http://marc.info/?l=sqlite-users&m=117934558505665&w=2 http://marc.info/?l=sqlite-users&m=117934558505665&q=p3 ---- _2007-May-19 15:47:51 by anonymous:_ {linebreak} Some improvements, new comments and a new test case. http://www.mail-archive.com/sqlite-users%40sqlite.org/msg24859.html http://marc.info/?l=sqlite-users&m=117958960408282&q=p3 #e8e8bd 2207 new active 2007 Jan anonymous 2007 May 3 4 "CREATE TABLE foo AS SELECT * FROM bar" doesn't copy constraints (cut & paste from an email I sent to the list, though afaict it never appeared) When creating a table using AS SELECT ... I noticed it didn't copy the constraints: SQLite version 3.3.8 Enter ".help" for instructions sqlite> .schema CREATE TABLE bar ( t INTEGER NOT NULL PRIMARY KEY, d INTEGER NOT NULL ); sqlite> CREATE TABLE foo AS SELECT * FROM bar; sqlite> .schema CREATE TABLE foo(timestamp INTEGER,download INTEGER); CREATE TABLE bar ( t INTEGER NOT NULL PRIMARY KEY, d INTEGER NOT NULL ); Is this expected behavior? I find myself in a sitation where ideally I would like this to create a table copying contraints (so I can do some processing in dynamically created temporary tables). I had a quick look over the documentation and it doesn't mention this either way. _2007-Jan-30 18:52:49 by drh:_ {linebreak} I have a lot of code that depends "CREATE TABLE ... AS SELECT" not copying the constraints. Changing this so that constraints are copied would break my code - which is something I am disinclined to do. ---- _2007-Jan-30 19:06:34 by anonymous:_ {linebreak} Can we perhaps just document this then as the intended behavior then then close this bug out please? ---- _2007-Jan-30 21:57:10 by anonymous:_ {linebreak} How about a new feature: CREATE TABLE foo *WITH CONSTRAINTS* AS SELECT * FROM bar #e8e8bd 2373 new active 2007 May anonymous 2007 May 4 4 "create table as explain
sqlite3 data.db3 .header on create table ... .qThey can't just cut and paste the script into sqlite3 because of the whitespace. Patch is ...
--- ..\sqlite-source-3_3_13\shell.c 2007-02-13 08:08:34.000000000 -0300 +++ shell.c 2007-02-15 04:18:05.726995200 -0300 @@ -934,22 +934,35 @@ } return val; } /* +** Determine if an input line begins with "." ignoring whitespace. +*/ +static int is_meta_command(const char *z){ + while( isspace((unsigned char)*z) ){ z++; } + return *z == '.'; +} + +/* ** If an input line begins with "." then invoke this routine to ** process that line. ** ** Return 1 on error, 2 to exit, and 0 otherwise. */ static int do_meta_command(char *zLine, struct callback_data *p){ - int i = 1; + int i = 0; int nArg = 0; int n, c; int rc = 0; char *azArg[50]; + /* Skip the "." prefix. + */ + while(zLine[i] != '.'){ i++; } + ++i; + /* Parse the input line into tokens. */ while( zLine[i] && nArg_2007-Mar-01 21:53:33 by anonymous:_ {linebreak} Any possibility of this going in? A simple change. Full patch. Brings meta command whitespace processing to the same functionality as SQL (i.e. ignore leading whitespace). Submitting patches to opensource projects is so frustrating when there is no feedback. #e8e8bd 2233 new active 2007 Feb anonymous 2007 Mar 4 3 extend xBestIndex in vtables to carry the values for each contraint It would be helpful if the xBestIndex method in virtual table carried the actual values for each constraint. This would allow clients of this call to more accurately set the estimatedCost of the query. The values are available in xFilter but the estimated cost has already been set and encoded by then. Thanks... At the time xBestIndex is called, the values are likely runtime variables or unbound host parameters and are thus unknown. #e8e8bd 2258 new active 2007 Feb anonymous 2007 Mar 4 3 Include field name in check constraint failure message When a check constraint on a column fails SQLite just responds with "constraint failed". It'd be nice if it included the column name for which constraint failed and possibly the value that failed to pass the constraint. Thanks, Sam #e8e8bd 1778 new active 2006 Apr anonymous Parser 2007 Mar 1 3 stdev does not work, workaround not possible due to missing sqrt It would be so great but it is too hard to use sqlite for any statistical purposes without having either an easy way of calculating standard deviation (stdev) nor any chance to create an ugly workaround because square root (sqrt) is also not working or missing. And even to square (sqr) values are resulting in long terms; but at least this is possible.{linebreak} Is this because it is a lot of work to calculate the sum of values *and* sum of values squared at the same time during record traversal? Would this basic and standard function be not SQL conform?{linebreak} {linebreak} Example:{linebreak} select count(*), avg(Num), stdev(Num) from Population;{linebreak} {linebreak} My workaround proposal is to use variance due to missing sqrt function:{linebreak} select count(*), avg(Num) as mean,{linebreak} (sum(Num*Num)/(count(*)-1)-sum(Num)*sum(Num)/count(*)/(count(*)-1)) as stdevsquared from Population;{linebreak} Isn't this looking ugly and error prone? Can you imagine the performance if I have to calculate mean and stdev for 20 or more columns? _2006-Apr-20 14:27:31 by anonymous:_ {linebreak} It's pretty simple to add your own custom functions to SQLite. Edit sqlite/src/func.c and take a search for sumStep and sumFinalize for an example. ---- _2006-Apr-20 18:11:06 by anonymous:_ Index: src/func.c =================================================================== RCS file: /sqlite/sqlite/src/func.c,v retrieving revision 1.127 diff -u -r1.127 func.c --- src/func.c 7 Apr 2006 13:26:43 -0000 1.127 +++ src/func.c 20 Apr 2006 18:10:08 -0000 @@ -20,7 +20,7 @@ */ #include "sqliteInt.h" #includeechoOn ) printf("%s\n", zLine); if( (zSql==0 || zSql[0]==0) && _all_whitespace(zLine) ) continue; - if( zLine && zLine[0]=='.' && nSql==0 ){ + if( zLine && is_meta_command(zLine) && nSql==0 ){ rc = do_meta_command(zLine, p); free(zLine); if( rc==2 ){ break; }else if( rc ){
# Compiler options needed for programs that use the readline() library. # READLINE_FLAGS = -DHAVE_READLINE=1 -I/home/mjs/local/include # The library that programs using readline() must link against. # LIBREADLINE = -L/home/mjs/local-linux/lib -lreadline -lncurses#e8e8bd 2266 new active 2007 Mar anonymous 2007 Mar anonymous 3 3 Add support for Row_Number() Over Row_Number() Over is a windowing function included in the SQL:2003 standard. I need it to be able to rank order several groups by size, using a single query. I know SQLite does not support SQL:2003, but it would be nice to have at least this one function supported. _2007-Mar-10 10:06:33 by anonymous:_ {linebreak} You can easily write your own UDF using SQLite API. Here's an example if you need it right now:
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 2254 new active 2007 Feb anonymous 2007 Feb 5 4 ATTACH support IF NOT ATTACHED statement It'd be nice if ATTACH supported an IF NOT ATTACHED option as in ATTACH IF NOT ATTACHED 'C:\db\log.dat' AS Logs so there'd be no harm in issuing an attach statement multiple times (and no need to query the database list to see if a database is already attached).
#e8e8bd 2238 new active 2007 Feb anonymous 2007 Feb 2 3 Streams as datbase Would it be possible to allow the use of streams as a database source? _2007-Feb-18 03:56:46 by anonymous:_ {linebreak} You'll have to be more precise in what you mean by that. SQLite needs to be able to do random access to the database data (ie seek all over the place according to how it is laid out). It also needs the ability to have a journal file alongside the database which is used when writing to do a rollback, or even for readers to know that a rollback needs to be done. I am not aware of any 'streams' that meet those criteria.
#e8e8bd 2235 new active 2007 Feb anonymous 2007 Feb 4 3 Missing xml support in FTS2 for the snippet function The snippet function _may_ output invalid characters when used for an xml stream (like xhtml). Characters &, < and > need to be escaped (&, < >) in this context. The modification proposed is to add a boolean parameter to the snippet function to disable/enable the XML processing mode ; for example, given : =insert into poem (name, text) values ('test', 'Xml string with special < > & entities') ;= =select snippet(poem, '', '', '...', 1) from poem where text match 'xml' ;= output should be: =Xml string with special < > & entities= This modification does not affect the default behaviour of the snippet function. Patch included.
#e8e8bd 2224 new active 2007 Feb scouten 2007 Feb 4 4 Option to have one-bit "journal should exist" flag Per discussion with DRH: Would it be possible to have a one-bit flag in the header page of the DB file that signals that there _should_ be a journal file present. If you attempt to open a database with that flag set, but the journal file is not present, SQLite should fail to open the DB. _2007-Feb-09 13:47:44 by drh:_ {linebreak} Here is the issue: An application that uses SQLite for persistence is receiving database corruption reports from the field. The developers believe that the corruption occurs after a power failure or other crash leaves a hot journal file and then the users manually deletes the hot journal thinking that it is some garbage file left over from the crash. If there is a "journal should exist" flag in the database file and no journal is seen, that would indicate that the journal has been deleted or renamed and that the database has been corrupted. If the application can detect this, it might be able to locate the deleted journal in the trashcan and recover from the user error. Other ideas for addressing this problem: *: Change the "-journal" extension on the journal files to something like "-do-not-delete". *: Make the journal a hidden file. (The problem here is that if somebody goes to move the databaes file and the database has a hot journal, they would likely not know to move the journal too since it is not visible.) *: Change permissions on the journal file so that it is read-only. This doesn't prevent the journal from being deleted by a determined user, but it might at least give them a clue that this is not a file to be deleted without at least due consideration.
#e8e8bd 2221 new active 2007 Feb anonymous 2007 Feb drh 3 4 Store blobs using inode-like lookup of pages rather than linked list In a recent conversation, the matter of how BLOBs are stored came up. Currently, each page of BLOB data is in a linked list. By default each page is 1K so a very large BLOB may have many many pages. The linked list becomes inefficient to find and update BLOBs. DRH mentioned a thought to move to an inode style of page management for BLOBs. This would require updating the file format.
#e8e8bd 2220 new active 2007 Feb anonymous 2007 Feb drh 2 4 fsck for database files The existing recovery strategies for dealing with a corrupted database are entirely manual and could be improved with a reasonable amount of effort. One possible way to mitigate the issue would be the creation of an fsck recovery mechanism. This would be an improved recovery from the current .dump support.
#e8e8bd 2204 new active 2007 Jan anonymous 2007 Feb 5 3 Stable, documented metadata interface In response to #2203, I'd like to suggest that a documented, stable means be added to SQLite3 by which consumers of the API may reliably query column metadata for a table, including the names of the columns, whether they are nullable or not, their types, and what their default values are. Given that, currently, the only way to get this data is via the undocumented table_info pragma, clients who want this data are at your mercy every time that pragma changes. Thanks! _2007-Jan-30 00:07:28 by anonymous:_ {linebreak} How about implementing the sql-standard information_schema? I see something similar at http://www.sqlite.org/cvstrac/wiki?p=InformationSchema The PostgreSQL equivalent: http://www.postgresql.org/docs/current/static/information-schema.html ---- _2007-Jan-31 19:14:48 by anonymous:_ {linebreak} Pragma table_info is documented at http://www.sqlite.org/pragma.html#schema ---- _2007-Jan-31 19:31:48 by anonymous:_ {linebreak} PRAGMAs are deficient because they cannot be used within SELECT statements or as sub-selects. This severely limits their usefulness in an SQL-only context. You have to use SQLite's API to make use of them. ---- _2007-Feb-03 15:10:36 by anonymous:_ {linebreak} Note that I was told by Richard himself that the table_info pragma is not considered a documented interface, and as such is fair game for incompatible changes in point releases (as we saw in 3.3.8). What I'm asking for in this ticket is an interface that is officially sanctioned and documented, and which (barring the occassional bug) can be guaranteed to remain stable (between point releases at the very least).
#e8e8bd 1126 new active 2005 Feb anonymous Unknown 2007 Jan drh 2 3 sqlite 2.8.16 port to djgpp here is a diff to be applied on sqlite 2.8.16 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 functional. dear drh, please apply this patch to mainstream sqlite. waiver of copyright in the patch itself. best regards, alex CREATE TRIGGER trg_upd_dict AFTER UPDATE ON dict BEGIN UPDATE dict SET code = (SELECT code from tmp_connected_user) WHERE old.dict_id = dict_id ; END ;This trigger doesn't work if tmp_connected_user is a temporary table. The message is : SQL error: no such table: main.tmp_connected_user The goal is to have persistant triggers who works with temporary tables. Exemple of use : - Workarround who replace the non existing connection by user / password. When we insert/update, the database doesn't know who insert/update. If we have a table user, we can on each table fill by trigger fields like last_user_id, last_modif_d. The trigger cannot know who make the connection but we stock the user_id when he connects to the db in a temporary table, the trigger will work. - Security (no one can update / insert the database if a special temporary table is not created and filled). ---- _2006-Mar-03 20:25:41 by drh:_ {linebreak} You can create a TEMP trigger that will reference tables in the main database and/or attached databases. But SQLite currently does not allow triggers in the main or attached database to reference tables in other databases. I will enter this as an enhancement request. ---- _2006-Mar-06 16:17:11 by anonymous:_ {linebreak} Workarround for this ticket : if we only need 1 result, we can use user defined function instead temporary table in the trigger. Tested with php : it works :) #e8e8bd 1655 new active 2006 Feb anonymous VDBE 2006 Mar 4 4 Every function can have their private data like agreagates Is it possible to modify the way functions are handled in sqlite ? My idea is to allow functions to have their own private data space to save data from row to row like the agregates have, with that we can have functions that remember last row values, create counters and totalizers that return their updated values for each row. Ex:
select increment(1),* from my_table; 1|car|rose|3 2|sea|bike|7 3|flower|water|33 select sum_and_return_row_by_row(row_value_to_sum),* from my_table; 3|car|rose|3 10|sea|bike|7 43|flower|water|33 select current_row_value + last_row_value(current_row_value),* from my_table; 3|car|rose|3 10|sea|bike|7 40|flower|water|33The structure for that is already there, in fact is the same used by agregates, I was scratching the code but I could not find easily where to introduce code to push the context and pop it for functions that aren't agregates, someone know how to do that ? _2006-Feb-04 20:45:32 by anonymous:_ {linebreak}
/* ** Implementation of the increment() function */ static void incrementFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ assert( argc==1 ); switch( sqlite3_value_type(argv[0]) ){ case SQLITE_INTEGER: { i64 iVal = sqlite3_value_int64(argv[0]); i64 *pi = (i64*)&context->s.zShort; *pi = *pi + iVal; sqlite3_result_int64(context, *pi); break; } } }I've tried that but context are not saved row to row, of course here I was using a hidden member (s.zShort) of the context structure that seems not to be used or damaged, ideally should have a function like "void *sqlite3_set_presistent_data(sqlite3_context *context, void* value, int size_to_be_allocated)" or something like it that will allocate memory and return the actual value stored before if any. #e8e8bd 1693 new active 2006 Feb anonymous Parser 2006 Mar 4 4 Parser sensitive to position of word AUTOINCREMENT Command: create table unique_ids ( 'id' INTEGER AUTOINCREMENT PRIMARY KEY) fails, while the create table unique_ids ( 'id' INTEGER PRIMARY KEY AUTOINCREMENT) suceeds. The only difference is position of the word AUTOINCREMENT. I originally expected the AUTOINCREMENT to be standalone feature, not tied with PRIMARY KEY. Perhaps the parser may be more forgiving here. #e8e8bd 1704 new active 2006 Mar anonymous 2006 Mar 4 3 extern "C" block in sqliteInt.h can we put #ifdef __cplusplus extern "C" { #endif ... #ifdef __cplusplus } /* End of the 'extern "C"' block */ #endif around the declarations in sqliteInt.h. It would help as we need to access SQLite internals from our C++ code. #e8e8bd 1703 new active 2006 Mar anonymous Pager 2006 Mar 2 3 Second parameter to gettimeofday() in os_unix.c should be NULL in os_unix.c, function sqlite3UnixCurrentTime(): the second argument to gettimeofday() should be NULL and the declaration of sTz should be removed. struct timezone seems to cause trouble on Linux systems. #e8e8bd 1702 new active 2006 Mar anonymous 2006 Mar 5 4 feature request: API to write in-memory DB to file. Hello! A feature suggestion regarding in-mem databases: It would be interesting to be able to save this to files as normal sqlite3 dbs. i'm assuming that this is internally a rather simple operation, but i didn't find a function for doing it. Take care, ----- stephan _2006-Jun-28 05:04:05 by anonymous:_ {linebreak} There have been already good solutions. Watch this wiki page. http://www.sqlite.org/cvstrac/wiki?p=InMemoryDatabase #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
create table node ( id integer primary key not null, parent integer, data );All in all, this is not needed very often, but I reckon implementing the API function would be relatively trivial. _2005-Dec-19 16:04:54 by anonymous:_ {linebreak} If you want to re-execute the same query with different parameters, you should just use sqlite3_reset() instead of sqlite3_finalize(). This will reset the query so that it is ready to be executed again. Then issue the sqlite3_bind_...() calls to bind new values to the parameters that you need to change. ---- _2005-Dec-19 16:06:47 by anonymous:_ {linebreak} Oops, sorry I missed the "simultaneously" in your post the first time I read it. #e8e8bd 1558 new active 2005 Dec anonymous VDBE 2005 Dec 3 4 Request more debugging info when SQL statements in progress I am sporadically receiving the error message "Can't commit transaction - SQL statements in progress." I believe my own code is at fault, not anything in SQLite, but I would appreciate help in diagnosing the problem in my code. SQLite obviously knows that I've lost track of one or more prepared statements that haven't run to completion, it isn't telling me *what* statements those are. I'm wondering if there is any way of getting that information. Armed with that knowledge, I can probably fix my code fairly quickly. Note: DRH did provide a workaround that involved recompiling with extra debugging information, which will probably help me in the short run. This feature request is aimed at making that debugging process easier for future developers. :-) #e8e8bd 1552 new active 2005 Dec anonymous Unknown 2005 Dec 5 4 Adding .read FILE support to the C API Hello! i've attempted to implement an SQL function which behaves like the sqlite3 shell's .read FILE feature. The problem is that functions don't have a handle to their DB, and therefor cannot call an sqlite3_exec() to actually evaluate the read-in code. The ability to do this from client code would be "really cool." :) On a related note: i'm using C++, and found that the following won't compile: std::string fname( sqlite3_value_text( argv[i] ) ); because sqlite3_value_text() explicitely returns an unsigned pointer. The STL uses plain old (const char *), so the following workaround is needed: std::string fname( static_cast
static void concatFunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ unsigned char *z; int i; int len = 0; for(i=0;i_2005-Oct-05 14:27:29 by drh:_ {linebreak} Seems kind of pointless since SQLite already supports the SQL-standard || operator. ---- _2005-Oct-09 13:24:49 by anonymous:_ {linebreak} sorry :-) #e8e8bd 1464 new active 2005 Oct anonymous 2005 Oct 4 3 Improving PRAGMA table_info() I have a small feature request regarding PRAGMA table_info() to add a new column named "auto_inc" which contains 1 if the corresponding field has the AUTO INCREMENT in its definition and 0 otherwise. _2005-Oct-04 15:37:31 by anonymous:_ {linebreak} Once suggestions for extending the PRAGMA table_info() are on the agenda, I would like to suggest that the COLLATION sequence used for an INDEX would be made available as well. #e8e8bd 229 new active 2003 Jan anonymous Parser 2005 Sep 5 4 DECLARE ... CURSOR FOR ... would be really nice Cursors can be a tremendous performance aid in GUI-driven applications with large data sets, and it's pretty hard to emulate them if your application is written with the concept in mind. I would really be thrilled if cursors found their way into SQLite! Thanks, b.g. OK, this is now Jan 2005 and "cursors" are not there yet. Any plans? what would be an easy work around. ---- _2005-Sep-14 06:31:57 by anonymous:_ {linebreak} Yes Cursor would be a added advantage so that we dont have to move to and for from application to sqlite for smiple calculation ---- _2005-Sep-14 20:03:40 by anonymous:_ {linebreak} SQLite3's cursors are called sqlite3_prepare(), sqlite3_step(), sqlite3_reset() and sqlite3_finalize() Seriously, the purpose of a cursor is to leave the data on the database server and retrieve only the data and columns you need from a resultset managed by the server. SQLite is an embedded file-based database engine, not a database server -- therefore cursors make no sense. Asking for cursors in sqlite is akin to asking the filesystem to give you a cursor on a textfile so you can only read one line at a time -- when the reality is, you just open the file and read the lines yourself. sqlite3_step() *is* your cursor, it is reading the row from the file, one row at a time. #e8e8bd 1424 new active 2005 Sep anonymous Unknown 2005 Sep drh 5 3 Pivot table creation The beautiful SQLite engine lacks of pivot table creation. How to create cross tables? I tried the cross join but without success.{linebreak} Example: create table tab ( name, department, hours ); Populated with hours that many people spent in some departments{linebreak} How to produce an answer table (with row and column totals), where the sum of hours (or another aggregate function) is shown in each cell of row=name and column=department. The SQL statement is expected to look similar like following{linebreak} *TRANSFORM sum(hours) as Duration{linebreak} SELECT name FROM tab GROUP BY name{linebreak} PIVOT department* {linebreak} In simple words the aggregation of two select statements on some common fields with the other fields in row respective column direction.{linebreak} I would like to use aggregate functions sum, count, avg, *stdev*, min, max as already mentioned in ticket #1381. Introduction of sqrt and macros would be fine to create work arounds for stdev and others. _2005-Sep-14 03:02:24 by anonymous:_ {linebreak} This is a feature request and should be brought up on the mailing list. If you haven't already done so, read {link: http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql Unsupported SQL} where there have been a few requests for OLAP-oriented features. #e8e8bd 1423 new active 2005 Sep anonymous 2005 Sep drh 3 4 Binding NULL does not work as expected There's currently no way to compare to NULL with binding parameters. My suggestion is that you make "= ?" with the NULL bound parameter work like "IS NULL" and "<> ?" like "IS NOT NULL". Currently, neither this nor the normal way to bind to "IS ?" works. "IS ?" returns a syntax error even. Testcase follows. #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. #e8e8bd 1400 new active 2005 Aug anonymous 2005 Sep 5 5 Use of indexes in queries to VIEWS built from UNION SELECTS Attach 2 DBs DB1 and DB2 that have each a table with same schema (say A). Create a view so that all data i seen as one piece, like: VIEW = SELECT * from DB1.A UNION SELECT * from DB2.A Many views are created like this (for a few tables) and queries that do joins are run on the views. Those queries do NOT to use the underlying DB1.A and DB2.A indexes (i.e. they are too slow). Is this a bug? Is this by design? Am I confusing something? Will "UNION ALL" make a difference? Sorry if this is explained in docs but i did a search before entering this and could not find something. _2005-Aug-31 13:09:30 by drh:_ {linebreak} I have difficulty understanding this ticket, but I I think it is an enhancement request for better query optimization. It is a question: does creating a view V made as above - by union-ing 2 tables that have same schema - still use indexes from constituent tables? E.g. with a quey like (select * where V.field = 'value'). It appears not to be the case. #e8e8bd 1381 new active 2005 Aug anonymous Parser 2005 Aug 5 4 missing important builtin function stdev I really miss function *stdev* for support of statistical usage of SQLite. Also missed *sqr* and *sqrt* for square and square root calculations. This would help me very much to simplify my applications if they could be integrated also into the command line tool. An *avg* without *stdev* is of no useful meaning. Another wish is the *support of date and time stamp calculations*. Some few functions to convert literal expressions into float and backwards would be sufficient for me. No time zone consideration required. _2005-Aug-25 11:35:15 by anonymous:_ {linebreak} Proposal for powerful enhancements:{linebreak} Introduce alias scripting to combine built in functions to create new function names. Similar to C language macros. SQR and SQRT to become available as builtin functions.{linebreak}Example for creation:{linebreak} *PRAGMA FUNCTION newfunctionname(#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; } {, +})= * {linebreak}Example for an application: {linebreak}PRAGMA FUNCTION{linebreak} stdev(xsum,xsumcubed,n)=sqrt((n*xsumcubed-sqr(xsum))/(n*(n-1))); {linebreak}SELECT avg(t1.c1) as c1mean,{linebreak} stdev(sum(t1.c1),sum(sqr(t1.c1)),count(t1.c1)) as c1sigma,{linebreak} avg(t1.c2) as c2mean,{linebreak} stdev(sum(t1.c2),sum(sqr(t1.c2)),count(t1.c2)) as c2sigma{linebreak} from t1;{linebreak} This would reduce typing and enables to simulate non built in functions which can be created on basis of built in functions. Should be well supported by expression optimization to gain most benefit. ---- _2005-Aug-29 16:00:39 by anonymous:_ {linebreak} I also lack of sqrt, sqr and stdev (beside some other basics like sin, cos, tan, atan2, deg, rad, ... ). I like the idea of macros, too, because they make simplifications in statements easy. While sqr could be written like MACRO sqr(x)= ((x)*(x)) it is very difficult to live without the sqrt. Even stdev could become a macro but without sqrt the live stays difficult. If macros become part of SQLite3 then there should also be a command PROBE to see the expanded statement as executed by the parser; similar to EXPLAIN command. Priority to this ticket should be higher to become realized. #e8e8bd 1332 new active 2005 Jul anonymous Unknown 2005 Jul 5 5 update 20x slower when using a temp table I have two versions of the same algorithm. The first operates directly on the main db table. The second operates on a temp table containing only the working set. The problem is that the second version is about 20x slower, 1.5 sec versus 30 sec. If the EXISTS line in the second version is commented out the execution time drops to 9 sec. The test dataset has 12000 rows in group_article and the query deals with 6400 rows. The final version will be expected to work with >2 million rows. Version 1: {linebreak} UPDATE group_article SET parent=null WHERE group_id=?; UPDATE group_article SET parent= ( SELECT article.id FROM refs ,article WHERE refs.article_id=group_article.article_id AND reference=hash AND EXISTS (SELECT id FROM group_article WHERE group_id=?1 AND article_id=article.id) ORDER BY refs.id DESC LIMIT 1 ) WHERE group_id=?1Version 2:{linebreak}CREATE TEMP TABLE thrd(aid UNIQUE, parent); INSERT INTO thrd(aid) SELECT article_id FROM group_article WHERE group_id=?; UPDATE thrd SET parent= ( SELECT article.id FROM refs ,article WHERE refs.article_id=thrd.aid AND reference=hash AND EXISTS (SELECT aid FROM thrd WHERE aid=article.id) ORDER BY refs.id DESC LIMIT 1 ); UPDATE group_article SET parent= ( SELECT parent FROM thrd WHERE aid=article_id ) WHERE group_id=? ;Here are partial table defs. {linebreak}table group_article (id PRIMARY, group_id INT, article_id INT); UNIQUE index on (group_id, article_id); table refs (id PRIMARY, article_id int, reference BLOB(15)); index on (article_id) table article (id PRIMARY, hash BLOB(15)); UNIQUE index on (hash)Almost forgot, I'm running this on windows xp, compiled with mingw gcc 3.4.4. The appears to be an enhancment request - you are wanting better optimization in the SQLite. It seems to get the correct answer, you just want to get the answer faster. So I have changed this ticket to an enhancment request and set the priority very low. If you want help optimizing your query, the proper place to ask is on the mailing list. ---- _2005-Jul-24 03:50:31 by anonymous:_ {linebreak} After taking a day away from the code I finnaly found the problem. Declaring a column as UNIQUE without giving it a type means that either the index is not created or it's not used. A full table scan was being used each time in the second version. Adding INT as the type on thrd.aid fixed the performance problem. Assuming I didn't miss this in the docs, this is either a bug or a note is needed so others don't make the same mistake. #e8e8bd 1319 new active 2005 Jul anonymous 2005 Jul 5 4 Extend sqlite3_expired to detect schema changes Hi, It would be nice to have sqlite3_expired() detect if the database schema has changed by, for example, vacuuming. This also requires the statement to be recompiled, and so IMHO it makes sense to have expired handle that. Thanks. #e8e8bd 1313 new active 2005 Jun anonymous 2005 Jun 4 4 Allow headers in imported text files Please enhance the '.import' command to add a switch which allows the user to specify whether or not the imported text file contains headers. If it does, they can be silently discarded. As it stands the header row will be imported as a normal data row. _2007-Jan-26 16:57:48 by anonymous:_ {linebreak} This feature would be very nice, but it would also be great to be able to create a table using the header row as column names during .import. #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 1294 new active 2005 Jun anonymous 2005 Jun 3 3 API to get ordinal table name When a select statement returns a recordset, SQLite current implementation has an API to retrieve all column names. It is great to have an API to retrieve table names for each columns. This is especial usefull to deal with joins and dynamic queries and update the recordset on the fly. I believe MySQL has this feature. It would be great to port this feature over. #e8e8bd 1262 new active 2005 May anonymous VDBE 2005 May 5 3 5-15% performance increase for slow joins Attaching an optimization to sqlite3VdbeRecordCompare() that gives a speedup of 5-15% for certain SELECTs with join. The optimization does comparisons directly on serialized data instead of deserializing and then comparing. It also inlines sqlite3GetVarint32 partly. The way the __inline keyword is used needs to be made portable. _2005-May-22 12:52:08 by anonymous:_ {linebreak} I don't have the neccessary infrastructure to run the unit tests, so it's likely that unit tests fail since I only have done limited testing of the code. As long as it doesn't contain any fundamental logic flaws any bugs should be possible to fix. ---- _2005-May-22 19:56:29 by anonymous:_ {linebreak} Looks like negative doubles don't compare as easy as I thought. Please wait for a fix before comitting. ---- _2005-May-22 21:25:49 by anonymous:_ {linebreak} Attaching file that passes the unit tests. #e8e8bd 1254 new active 2005 May anonymous Pager 2005 May 3 3 better pager_cksum pager_cksum currently reads one byte every 200 bytes in a page and computes a checksum from this. Suggest changing this to reading 32-bits every 200 bytes. This will run a the same speed, but the likelyhood of detecting errors is larger, since 400% more data is used in the checksumming. #e8e8bd 1250 new active 2005 May anonymous Pager 2005 May 1 3 FlushFileBuffers makes Windows sqlite dead slow compared to Linux On Windows, FlushFileBuffers is used to commit changes to the disk. This makes Windows sqlite very slow in comparison with sqlite on Linux, because fsync() doesn't flush the disk controller's cache like FlushFileBuffers does. When opening the database file and journal file, there should be an option to use the FILE_FLAG_WRITE_THROUGH file flag. This will make sure that all writes go through the OS cache directly to the disk before the write function returns. This will give a small slowdown for HUGE commits, but the speedup for small commits is substantial. FILE_FLAG_WRITE_THROUGH is 5-10x faster than FlushFileBuffers for all commits that don't write to a huge number of pages. For commits in the size range of 5-10 megabytes, FILE_FLAG_WRITE_THROUGH is around 2x slower. _2005-May-18 17:49:22 by anonymous:_ {linebreak} Attaching sqlite.patch that adds a global variable that controls the write through status. This is not ideal, but it allows you to benchmark the differences easily. ---- _2006-Jun-06 18:34:18 by anonymous:_ {linebreak} FILE_FLAG_WRITE_THROUGH is the norm for Microsoft's SQL Server. http://support.microsoft.com/default.aspx?scid=kb;en-us;234656 #e8e8bd 1249 new active 2005 May anonymous Unknown 2005 May 5 4 transaction It would be handy to have a sqlite3_in_transaction function that would return whether or not a databases connection was in the middle of a transaction or not. I'm thinking this could be as simple as just returning the value of the autoCommit flag. I suppose it might be interesting to have it return the type of the transaction, but that would be strictly for extra credit. _2005-May-18 15:20:00 by anonymous:_ {linebreak} Actually, what we probably really want is a call that tells us the value of the autoCommit flag. #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 #e8e8bd 1227 new active 2005 Apr anonymous Shell 2005 Apr 5 2 Small patch for timing commands in shell.c Please find attached small patch for timing commands in shell. Patch explains usage of a new .timer command. Patch is tested with OpenWatcom 1.4 + Windows 20a21 > #include80d80 < 778a779 > ".timer start|show Internal timer functions\n" 1385a1387,1414 > }else > > /* > Bartosz Polednia 27.04.2005 - free for SQLite community > > .timer start > Starts internal clock counter and prints it in form : > Start time: Sat Mar 21 15:58:27 2005 > > .timer show > Shows time elapsed in form : > Exec time: nnnnnn.nnn s. Sat Mar 21 15:58:27 2005 > where nnnnnn.nnn is time in sec. since last .timer start command. > Timer is not stopped so user can issue next .timer show command. > To start timer user has to exec .timer start command again. > > */ > if( c=='t' && strncmp(azArg[0], "timer", n)==0 && nArg>=2 ){ > static clock_t timerstart = 0; > time_t now = time( NULL ); > if( strcmp(azArg[1],"start")==0 ){ > timerstart = clock(); > printf("Start time: %13s %s", "", ctime( &now ) ); > }else if (strcmp(azArg[1],"show")==0 ){ > clock_t tmp = clock(); > printf("Exec time: %10.3lf s. %s", (1.0*tmp - timerstart)/CLOCKS_PER_SEC, ctime( &now ) ); > } Regards, Bartosz #e8e8bd 1221 new active 2005 Apr anonymous 2005 Apr 5 4 Implementation of PRAGMA trigger_info(table) Although this is not a bug it would be nice to implement the feature, as it can help developers to get information about table triggers. Also the code to do this is quite simple. See below the part of the code that should be inserted in the PRAGMA section: /// Editing pragma.c and insert this if( sqlite3StrICmp(zLeft, "trigger_info")==0 ){ Table *pTab = sqlite3FindTable(db, zRight, 0); Trigger *pTrg = pTab->pTrigger; sqlite3VdbeSetNumCols(v, 4); sqlite3VdbeSetColName(v, 0, "table", P3_STATIC); sqlite3VdbeSetColName(v, 1, "trigger", P3_STATIC); sqlite3VdbeSetColName(v, 2, "op", P3_STATIC); sqlite3VdbeSetColName(v, 3, "tr_tm", P3_STATIC); while(pTrg) { sqlite3VdbeOp3 (v, OP_String, 0, 0, pTrg->table, 0); sqlite3VdbeOp3 (v, OP_String, 0, 0, pTrg->name, 0); sqlite3VdbeAddOp(v, OP_Integer, pTrg->op, 0); sqlite3VdbeAddOp(v, OP_Integer, pTrg->tr_tm, 0); sqlite3VdbeAddOp(v, OP_Callback, 4, 0); pTrg = pTrg->pNext; } }else /// End of new Insertion #e8e8bd 1220 new active 2005 Apr anonymous 2005 Apr 4 3 Selectable calling convention It would be helpful to be able to specify the calling convention for the SQLite API functions when building the library. This is sometimes an issue when writing wrapper libraries, specifically for .NET, which assumes a stdcall calling convention when calling DLL's. The default cdecl calling convention of a C DLL such as SQLite causes a bit of unnecessary overhead for the .NET runtime. This is accomplished with Visual C++ (and GCC is similar) by adding a modifier to a function declaration... void __stdcall sqlite3_open (); This can be implemented using a macro, so the library can be compiled with the desired calling convention... void SQLITE_CALL sqlite3_open (); In sqlite3.h, the default macro would be empty... #ifndef SQLITE_CALL {linebreak} #define SQLITE_CALL {linebreak} #endif #e8e8bd 1208 new active 2005 Apr anonymous Unknown 2005 Apr 4 4 no version info provided in precompiled sqlite3.dll sometimes i get confused which version i'm using. it would be nice to have an easy way to view the sqlite version. _2005-Jul-14 12:43:28 by anonymous:_ {linebreak} Yes, would be great if the version information could be put in the resources of the dll and not only be accessible through the exported function! Tools like the popular Installer "InnoSetup" could recognize the version of the sqlite3.dll then and check if a newer version is already installed... #e8e8bd 1040 new active 2004 Dec anonymous Unknown 2005 Apr 4 4 pragma needed to enable flexibility of insert statements (esp. for awk-like filter use) In order to work around http://www.sqlite.org/cvstrac/tktview?tn=1032,3 it is possible to create insert statements manually using sed. However, those insert statements are not as flexible as they are documented to be. An error is generated if there are more data than columns. It is too slow to count the number of columns in each row to work around it. This makes the use of sqlite as an awk-like filter impossible, since you can't just define columns f1 through f9 and insert rows that have fewer columns into it. Defaulting is not done. This is inconsistent with the sqlite2 copy command, which is flexible. I propose a pragma to allow more flexibility. There is no fast workaround, but I am not assigning that severity, because in principle it is possible to much more slowly create a disk db and use .import for most purposes. #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 1189 new active 2005 Mar anonymous Parser 2005 Mar 1 1 Make where-clause item count limit dynamic If you enter a query with more then 100 'where clause items' (pieces of a where clause joined by AND or OR) then you get an error saying you must limited yourself to 100 or less items. This is fine for 99.9% of the time, but I ran into a problem when trying to implement ad-hoc bitmap indexes -- in other words, hundreds of columns in a table and therefor hundreds of items in the where clause. I was able to work around this, at the sqlite level, by simply changing a constant in where.c from 101 to 1001 (437 was what was needed for this specific application). The requested I'd like to see filled is that this be more-or-less a dynamic value. Perhaps it reads from some environment variable to determine this, and defaults to 100(+1). This would likely result in a bit of code rewrite as to how where.c works, but I don't think it would be a terribly huge amount. #e8e8bd 1186 new active 2005 Mar anonymous Shell 2005 Mar 4 4 Pragma page_size doesn't set the pagesize immediately Sqlite3 doesn't change the page_size of a DB until you run a ddl command. Allthough this behavior doesn't really qualify as a bug, it is an annoyance to us. Thanks for your great work K.- M. Hansche #e8e8bd 1176 new active 2005 Mar anonymous Unknown 2005 Mar 4 3 Blocking Writes I would be nice if there was an option where a write query would block when the database was locked by another process/thread. I would prefer this over returning SQLITE_BUSY and looping...Is this planned at some point? #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. 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#e8e8bd 1031 new active 2004 Dec anonymous 2005 Jan 3 4 sqlite command line does not allow intermixing of dot commands and sql Dot commands are not fully intermixable with sql commands in sqlite3. Please see bug 1030. #e8e8bd 1061 new active 2005 Jan anonymous Shell 2005 Jan 4 4 shell.c .import from stdin e.g.: in = ( 0 == strcmp( "-", zFile ) ) ? stdin : fopen(zFile, "rb"); #e8e8bd 1011 new active 2004 Nov anonymous 2004 Nov 5 4 No .lib file for in windows precompiled binaries It would be nice if there was .lib file available in precompiled binaries, so one could compile interface modules (like python sqlite) without building the full source. _2004-Nov-21 23:53:45 by anonymous:_ {linebreak} Does the information under "MSVC and SQLite DLL" in http://www.sqlite.org/cvstrac/wiki?p=HowToCompile help you? ---- _2004-Nov-23 05:21:41 by anonymous:_ {linebreak} There should be sqlite3.h with dll for C-compiler. #e8e8bd 965 new active 2004 Oct anonymous VDBE 2004 Oct 3 3 Busy Handler not invoked for SELECT et.al. in SQLite <= 2.8.15 There exist very rare cases when sqlite_exec() can return SQLITE_BUSY but the user-provided busy handler although set with sqlite_busy_handler() was never invoked. Please consider the following patch against vdbe.c. It primarily affects SELECT statements. For PRAGMA similar code snippets might be necessary for the VDBE opcodes ReadCookie/SetCookie. --- sqlite.orig/src/vdbe.c Mon Jul 19 21:30:50 2004 +++ sqlite/src/vdbe.c Tue Oct 19 14:45:11 2004 @@ -2325,8 +2325,11 @@ */ case OP_VerifyCookie: { int aMeta[SQLITE_N_BTREE_META]; + int busy = 1; assert( pOp->p1>=0 && pOp->p1nDb ); - rc = sqliteBtreeGetMeta(db->aDb[pOp->p1].pBt, aMeta); + while( (rc = sqliteBtreeGetMeta(db->aDb[pOp->p1].pBt,aMeta))==SQLITE_BUSY + && db->xBusyCallback + && db->xBusyCallback(db->pBusyArg, "", busy++)!=0 ){} if( rc==SQLITE_OK && aMeta[1]!=pOp->p2 ){ sqliteSetString(&p->zErrMsg, "database schema has changed", (char*)0); rc = SQLITE_SCHEMA; #e8e8bd 944 new active 2004 Oct anonymous Shell 2004 Oct 4 3 Command to abort current expression. It would be convenient if, at the sqlite prompt, it was possible to abort the current expression by entering e.g. a singel dot on a new line. I.e.: sqlite> SELECT * FROM ...> . sqlite>#e8e8bd 883 new active 2004 Sep anonymous Shell 2004 Sep 1 4 Choose appropriate column widths for column output Currently, when you use a select statement and have .mode set to column, it truncates the data based on the values in .width. It can be a problem to set .width properly because sometimes you are only selecting some columns and sometimes all of them. This means you really have to set .width before each select statement. Even when you do, sometimes the width is too wide and space is wasted unless you know in advance the width of the widest value being printed. What I would like is another mode or some option to have SQLite figure out the optimal width of the columns. It would simply set the width of each column to the width of the widest value in the column. MySQL does this. _2004-Sep-02 15:18:20 by drh:_ {linebreak} There are two ways of doing this: 1: Run the query twice. Record the maximum column witdh on the first run then display the results on the second run. 2: Store the entire result set in memory. Compute the maximum column widths prior to displaying anything. Either approach requires a significant increase in resources to do the query. ---- _2005-Jan-21 20:56:53 by anonymous:_ {linebreak} How about instead of, or in addition to this feature, a mode where each column has a default width which corresponds to the data type? E.g. if the type is CHAR(50) the column would be 50 wide. That would be faster and would be good enough for my purposes. ---- _2005-Apr-09 19:41:59 by anonymous:_ {linebreak} I don't need this feature after all, since I wrote a filtering program to convert the tab-separated data from sqlite to produce the column-separated data I wanted. In case someone wants to use the code to implement this feature in sqlite, or in case someone wanting this functionality wants to use it directly, you can download the code for free from: http://personal.nbnet.nb.ca/aerichar/tab2col/ #e8e8bd 845 new active 2004 Aug anonymous 2004 Aug 5 4 Support shared-memory databases It would be nice to have shared memory databases. In-memory databases with :memory are supported, but they can't be shared among multiple processes. #e8e8bd 843 new active 2004 Aug anonymous Unknown 2004 Aug 3 3 Introducing I64 printf size prefix sqlite3_mprintf supports the _ll_ size prefix, so int64 can be formatted using the %llu format specifier. VC++ printf routines doesn't support the _ll_ size prefix but use the _I64_ prefix, so a int64 must be formatted as %I64d or %I64u I think that sqlite3_mprintf should support both size prefixes, so a developer could use the same prefix independently to the function he will use (sqlite3_mprintf, printf, Format, etc....) #e8e8bd 824 new active 2004 Jul anonymous Unknown 2004 Jul 5 4 StrSum - new build-in aggreate function suggestion StrSum(string aggregatingFiels, string separator ) - aggregate function. create table x (y); insert into x(y) values ('one'); insert into x(y) values ('two'); insert into x(y) values ('three'); insert into x(y) values ('four'); insert into x(y) values ('five'); select StrSum(y, ' / ') from x == 'one / two / three / four / five' I have coded this function myself into my wrapper but I think this function will be usefull for all users :) #e8e8bd 823 new active 2004 Jul anonymous Unknown 2004 Jul 5 4 absent VERSIONINFO into any Win32 DLL Hi! There are sqlite_version API entry BUT I need VERSIONINFO resource into WIN32 DLL to create CAB-file witch will replace older shared sqliteX.dll with new versions automaticaly _2004-Jul-26 12:49:33 by drh:_ {linebreak} I have no idea what a VERSIONINFO resource is. ---- _2004-Jul-26 15:19:14 by dougcurrie:_ {linebreak} VERSIONINFO is built with the resource compiler (mingw handles this). Here's how VIM added it: http://www.math.technion.ac.il/pub/vim/patches/6.1.374 Here some MS docn: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tools/tools/versioninfo_resource.asp Maybe someday I'll have time for adding this, but not now. -- e #e8e8bd 239 new active 2003 Feb anonymous Unknown 2004 Jul 4 2 ANSI/Unicode(Wide) version file-handling functions for Win32 As I reported in the ML, since SQLite os.c file-operation functions accept only const char *zFilename, when you build a project with SQLite source code under _UNICODE defined on Win32, their internal expectation of ANSI version function fails. For example,{linebreak}{linebreak} int sqliteOsDelete(const char *zFilename){{linebreak} #if OS_UNIX{linebreak} unlink(zFilename);{linebreak} #endif{linebreak} #if OS_WIN{linebreak} DeleteFile(zFilename);{linebreak} #endif{linebreak} #if OS_MAC{linebreak} unlink(zFilename);{linebreak} #endif{linebreak} return SQLITE_OK;{linebreak} }{linebreak}{linebreak} DeleteFile in this function is replaced with DeleteFileW, the unicode version of DeleteFile, which takes Unicode string(wide characters) as an argument.{linebreak} To compile SQLite in Unicode application, you must change those functions to its ANSI version. For DeleteFile, it's DeleteFileA. The functions that should be presented in os.c as their explicit ANSI version are:{linebreak}{linebreak} DeleteFile -> DeleteFileA{linebreak} GetFileAttributes -> GetFileAttributesA{linebreak} CreateFile -> CreateFileA{linebreak} GetFullPathName -> GetFullPathNameA{linebreak} GetTempPath -> GetTempPathA{linebreak}{linebreak} Namely, all file-handling Win32 APIs that take a path name as its argument, not a handle. The 'vanilla' versions of functions like GetFile() aren't functions, but *symbols* defined in "winbase.h". For example, GetFile() expands to: #ifdef UNICODE{linebreak} #define DeleteFile DeleteFileW{linebreak} #else{linebreak} #define DeleteFile DeleteFileA{linebreak} #endif // !UNICODE{linebreak} So when someone compiles SQLite under Windows, they need to #define _UNICODE (or not), as desired. Unless I completely misunderstand this, there is no reason to change the SQLite code, and doing so would make it less flexible. Additionally, The unicode versions of the functions *will* handle ASCII strings, which are legal UTF8 strings. So the only real consideration left is the 'offical' SQLite Windows DLL. Because older systems (95, 98) lack built-in support for the Unicode versions of most functions, the DLL should probably be built without the Unicode versions. We might want to force the definition of UNICODE when SQLITE_UTF8 is defined, so that the DLL can be queried to see what kind of strings it expects, but then, someone (me) may reasonably want to compile the app to use unicode strings for file handling, but iso8859 strings for data. So at most I would suggest a #pragma message when the two symbols disagree. This is compiler dependent, but one for MSVC, Borland, and gcc would probably catch most current users. Jim Lyon --------------------------------------------------------- (by reporter) In C++ context compiler issues error because const char* of os.c functions doesn't match DeleteFileW signature under _UNICODE defined and this is not convenient in simply adding SQLite source files in UNICODE-defined host app project. Anyway, if os.c Windows-version functions take LPCTSTR zFilename that is translated into const char* in ANSI(MBCS) and const wchar* in _UNICODE in compile-time, you don't have to use explicit ANSI version API. If explicit use of ANSI version API makes SQLite "less flexible", I'd like to propose changing const char* zFilename to LPCTSTR in os.c. Another way is, add to SQLite its own ANSI/Unicode(UCS-2) version APIs as Windows does, in Windows SQLite build. For example "open" is translated in sqlite.h to openA/openW which exists in dll. Fortunately SQLite has os.c as abstraction filter for its body, several changes in os.c will be sufficient. --------------------------------------------------------- (by reporter) It's fixed in between 3.0.0 and 3.0.2 by adding 'A' suffix to those file-handling APIs. Can someone verify and close this ticket? ---- _2004-Jul-22 23:19:23 by anonymous:_ {linebreak} SQLite 2.8.15 os.c is not fixed as of now. I think there's no reason not to apply the same replacement that favors ASCII APIs. #e8e8bd 797 new active 2004 Jul anonymous Parser 2004 Jul 4 3 require basic named subquery / WITH sql support This ticket, derived from a recent discussion list posting, is a request for *simple* named subquery / WITH sql support. The level of support that I'm looking for should only require and update to the "Parser" subsystem, or possibly "CodeGen" too. The fundamental implementation of how SQLite handles subqueries is not changed at all. You still execute the subqueries exactly once, prior to the main queries, as you do now. The subset would be compliant with the SQL-1999 standard. As an additional reference, you can see SQL-2003 Foundation, 7.13 "" (p351). What I want is to be able to make a query like this: WITH first_subq AS ( SELECT id FROM foo WHERE name LIKE '%zoo%' ), second_subq AS ( SELECT a, b, COUNT(c) AS d FROM bar GROUP BY a, b ) SELECT * FROM second_subq AS s INNER JOIN baz AS z ON z.a = s.a AND z.b = s.b WHERE (z.opt1 IN first_subq OR z.opt2 IN first_subq) AND s.d >= z.boo ORDER BY s.d DESC One of the main advantages I cite is that SQL code is a lot cleaner and easier to understand. You can do within complex selects the same thing you can do in complex routines, which is akin to breaking out blocks into named subroutines. This advantage is particularly seen where the same subquery would be getting invoked multiple times within the main query (see example). It now does not have to be declared multiple times, leading to shorter and easier to parse SQL, and the code runs faster, because the subquery only has to be run once. As such, developers can also reduce some use of explicit temporary tables. This ticket is not requesting support for passing arguments to the named subqueries, as SQL-1999 allows, nor having support for recursive named subqueries (those that invoke themselves). Those would be nice some day, but would require more substantial changes to SQLite, such as invoking subqueries during the main query, and multiple times, rather than in advance. So I am not requesting those today. (Note that, should you ever decide to support recursive subqueries later, it should be done the SQL-1999 way, and not Oracle 8's way of start-with connect-by.) In summary, I propose that in the long run, named subqueries should be a lot more useful than inlined subqueries, both for programmer efficiency, and because SQLite itself has less work to do when parsing or optimizing sql queries; the programmer can tell you ways to optimize by reducing redundancy. Thank you. -- Darren Duncan P.S. On the list, Dennis Cote also showed a desire for the same features, saying: I also believe support for named subqueries would be a valuable addition to SQLite. I have previously advocated for this feature on the list as well, though I called it a WITH clause rather than a named subquery. It seems to me to be a fairly simple extension to SQLite that would allow the user to manually perform common sub-expression elimination optimizations. These optimizations are done automatically by other, not so lite, database engines. I also think they make the resulting SQL select statements easier to read. #e8e8bd 755 new active 2004 Jun anonymous TclLib 2004 Jun drh 5 2 Adding aggregate function to the tcl binding The diff against tclsqlite.c to solve the problem can be also submitted by email #e8e8bd 748 new active 2004 May anonymous 2004 May 3 3 option to allow ignoring trailing whitespace in selects According to the SQL-92, when comparing strings using the = operator, the strings are supposed to be padded out with spaces to the same length. For example, a char(5) column contains 'abc ' (two spaces at the end) and you perform select * from table1 where field1 = 'abc' This should match that 'abc ' row. This is because both terms would have been padded with spaces to be matching lengths before comparison. SQLite should at least have the option to turn on SQL-92 compliant padding. #e8e8bd 742 new active 2004 May anonymous VDBE 2004 May anonymous 5 5 database is locked when I tried to access your site, sometimes I could see the message like "the database is locked", and could not access. I think it's because of the so called "SQLITE_BUSY". if SQLITE_BUSY happens, can't we just access the website? if so, it would be nice to have an waiting time option, that is if SQLITE_BUSY happens the vdbe waits a little time and tries again till the appointed times even after if it is impossible, then show "database is locked". How about it? _2004-May-19 10:03:54 by drh:_ {linebreak} The SQLite website is database driven. Not every page requires database access, but many do. If an SQLITE_BUSY occurs, the request is automatically retried multiple times. But if SQLITE_BUSY keeps occurring, the request will eventually timeout. www.sqlite.org runs on User-Mode Linux (UML) partition at http://www.linode.org/. It is a Linode-64 (at the moment) meaning that there are 31 other linux instances running on the same CPU. Sometimes one or more of the other 32 linux instances get really busy and clog up the shared disk I/O channel, resulting in long delays for disk access. When this happens and there are multiple people trying to access the data at the same time, timeouts can occur. #e8e8bd 741 new active 2004 May anonymous CodeGen 2004 May anonymous 5 5 command tool does not support history if command tool support history, it would be great. windows supports its own history but on linux system command tool doesn't support history. _2004-May-23 18:07:31 by anonymous:_ {linebreak} The sqlite command on linux and cygwin have history (internal to sqlite at least). This capability comes from readline if it is installed on your systems. For some more details including libedit and OSX support see ReadLine #e8e8bd 738 new active 2004 May anonymous 2004 May danielk1977 4 1 Sort order array in memory Order is a problem when I have in memory a result of a long query, because I must order the array of the result. For example in Ado I can use recordset.order . Is it possible with sqlite? WHere is the problem? I must do a query very time long ; if I want to sort the array that give me sqlite, I must remake the query. IT is very bad :). This because I use in virtual listview the array. char** paszResults for example Very thanks #e8e8bd 739 new active 2004 May anonymous Unknown 2004 May danielk1977 3 1 Suggestion speed query Suggestion If possible cache the result of query similar or identical. For example I have a table of 100000 record. I do a query with where clausal, this takes 2 seconds. Ok, next time I remake the same query, I spend self time. Is advantageous? No, my idea is to cache results until there are not changes or update. The cache has to be done with caution, invisible for user and without compromise the performance. For example of query result of 100 rows, I must wait another time the time needed to obtain the result. It is not fine! Piero Romani #e8e8bd 737 new active 2004 May anonymous Shell 2004 May anonymous 3 3 xml export Export a sql statement to an oracle-style export xml. This can be done in shell.c following the xthml-table model except without the field escapes (in case we are putting xml in a column).Sqlite will not be able to say what xml a column should have without some kind of schema integration, which seems too complex. #e8e8bd 736 new active 2004 May anonymous 2004 May 4 4 Would like to be able to list the LAST 10 lines of a query This falls outside the scope of pretty much everything, but still...{linebreak} I'd like to be able to get the last 10 lines of a query. You may argue (as the mySQL people did about 2 years back) that I can just reverse the ORDER BY direction, but it wasn't a good argument then either, because I may not be setting a specific order (I call this the natural database order). SELECT * FROM myTable LIMIT -10 makes good sense, especially as it is usually the last n items that a person tends to be most interested in. Since, as far as I know, negative limits are an undefined area, why not stake your claim to SQL fame and introduce something that will be wildly popular? Specifically:{linebreak} LIMIT -n should return the last n rows of a query.{linebreak} LIMIT k, -n should return the n rows just before the kth row{linebreak} LIMIT -k, n should return the n rows starting k rows before the end{linebreak} LIMIT -k, -n should return the kth row from the end till n rows before the end. There is good precedent for this in PHP and javascript string handling, and PHP array handling (see especially http://php.net/array_slice) Anyway, thanks for listening,{linebreak} Csaba Gabor _2004-May-13 16:53:56 by drh:_ {linebreak} "Natural" database order in SQLite is equivalent to "ORDER BY rowid". So you you can say: SELECT whatever FROM whatever ORDER BY rowid DESC LIMIT 10; And it will do what you want. And it is very fast at this. ---- _2004-May-14 02:10:20 by anonymous:_ {linebreak} Cool, that is great, thanks very much. If I understand this correctly, whenever a new insert is made (regardless of whether the location used is that of a deleted row), there is an internal rowid that always gets incremented (and would overwrite any existing rowid if a deleted row was being reused). This is, therefore, essentially an inherent PRIMARY KEY with the restriction that it can't be changed. But it could be used to access and update any existing row. Very handy, thanks. I have a related question/comment. There is a function last_insert_rowid(), but in the case of ignore (or abort) this value does not change (and why should it, after all?). But I may be interested in finding the location that the conflict occurred at. (For example, in my current application, I am entering edges in a graph. That is, I first enter the two vertices that define an edge by doing an INSERT. If the vertices already exist, however, it would be improper to do a REPLACE because previous vertex references would become invalid. No, I should really get the location of the existing vertes causing the conflict). Of course, I could manually wade through all the indeces checking to see if/where there is a conflict, but this could seriously increase my access time (we're talking thousands to millions of points) but is there not a shorthand way to find out last_attemptedInsert_row()? - well, you get the idea. Finally, since this is too tiny to make a separate thread, the last "not" of the first paragraph of text for the insert command (http://sqlite.org/lang.html#insert) should be "no". Also, I would remark that last sentence of the second paragraph for ATTACH DATABASE (http://sqlite.org/lang.html#attach) running into the third paragraph is confusing to me because the first clause of the third paragraph is repeating part of what that previous sentence was saying. Why not remove the entire first clause in the third paragraph and merge the remaining paragraph with the second one. Csaba Gabor #e8e8bd 726 new active 2004 May anonymous Parser 2004 May anonymous 5 5 As to single quote Hello when we insert single quote(') into the query, currently it should be added a more single quote. I think it is not good for most of program espcially in database system uses backslash(\) not single quote. To be appliable to other program, would you change this? _2004-May-07 15:31:06 by anonymous:_ {linebreak} if you don't have any plan, I think putting an escaping option will be great. thanks. #e8e8bd 724 new active 2004 May anonymous 2004 May 4 5 VACUUM resets settings for the current session All settings tuned in current session are reverted to default values after VACUUM command execution. Example: sqlite> PRAGMA synchronous; 1 sqlite> PRAGMA synchronous=off; sqlite> PRAGMA synchronous; 0 sqlite> VACUUM; sqlite> PRAGMA synchronous; 1 I am convinced that this behavior is defined by VACUUM command implementation which reopens the database in the end. However, I think that this is an implementation detail and should not affect the session state. #e8e8bd 723 new active 2004 May anonymous Unknown 2004 May 5 4 BatchScript: Example windows batch script for creating databases. echo CREATE TABLE employee (id, name); | sqlite db echo INSERT INTO employee VALUES (1, 'Adam'); | sqlite db echo INSERT INTO employee VALUES (2, 'Eve'); | sqlite db #e8e8bd 718 new active 2004 May anonymous 2004 May anonymous 2 2 Case insensitive index ? It does not seem to be possible to create case-insensitive indices. Using UPPER() or LIKE is not as efficient and it usually requires to tweak applications in an ugly way to support case-insensitive searches. Maybe the new file format will support this ? If difficult to make it a feature, is it possible to compile sqlite to be case-insensitive for indicies (even for ones built previously); i.e. provide a #ifdef SQLITE_CASE_INSENSITIVE_INDICES #e8e8bd 717 new active 2004 May anonymous Unknown 2004 May drh 2 2 minor fixes and port to dos attached is a diff for sqlite 2.8.13 that allows work in dos gnu environment (32 bit djgpp), even without support of long file names. it also contains minor adjacent bugfixes, in the file names handling area, and for the case of missing libreadline. please take a look, and do apply to the main source tree. i do not ask for anything, even not for personal credits. it is a very small contribution, to make your wonderful work even more popular. best regards, alex _2004-Jul-09 02:22:15 by anonymous:_ {linebreak} after feedback, mainly from hans-juergen taenzer, i have reviewed the port to dos and made better relative path support, applying to unix also. for any questions or remarks, please feel free to contact me, alex, alexbodn@012.net.il. #e8e8bd 714 new active 2004 Apr anonymous Unknown 2004 Apr drh 3 3 Change sqlite_decode_binary to return buffer size It would be nice to be able to have sqlite_decode_binary return the required buffer size, like the encode routine does. The reason is that in managed systems (we're using this in .NET) it avoids a needless allocation and then a copy. For example, without it, we need to allocate a buffer as big as the incoming one, decode, allocate one of the proper size, then copy the bytes. Otherwise, the array we return is incorrectly sized. I've made the following change... // TODO: Mark this up... was... out[i++] = c + e; if (out) out[i++] = c + e; else i++; By checking the out argument, we still get the increment count, but we don't write anywhere. #e8e8bd 712 new active 2004 Apr anonymous Unknown 2004 Apr drh 3 3 Allow temporary databases to be created in specified folder Right now temporary databases are created in a default folder, for example the Temp folder under Windows. It should be possible to customize this location by passing the "context" of the current database to the temporary OS.C function. If that function received the current database, it could parse the folder and optionally create the temporary file in the same location. #e8e8bd 711 new active 2004 Apr anonymous 2004 Apr drh 3 3 Provide a callback function to verify values before insert or update Such a function would be called during an insert or update with the column name, column type, and the value. The function could set an error and error message to deny the action. This would allow develops to create virtual datatypes with very little effort. For example, if a column was defined as type "Fruit", a function could check if the value was "Apple" and allow it, or "Cat" and deny it. _2004-Apr-28 23:00:07 by anonymous:_ {linebreak} use triggers #e8e8bd 710 new active 2004 Apr anonymous Unknown 2004 Apr drh 3 4 Provide the ability to pass a user-data value during sqlite_compile It would be very useful to be able to pass a user-data value during the sqlite_compile command, that could then be retrieved in a user function. This would allow context-sensitive responses by the functions, by allowing them to know what "command" is executing them. I can provide further details if required. #e8e8bd 707 new active 2004 Apr anonymous Unknown 2004 Apr drh 3 3 how to use internal functions? to name a few: 1: "SELECT @@CHECK_INTEGRITY", it will return the integrity of the database. 2: "SELECT @@DATABASE_VERSION", it will return the version of the database. 3: "SELECT @@ENGINE_VERSION", it will return the current version of the sqlite engine 4:"SELECT @@ENGINE_ENCODING", it will return the encoding of the sqlite engine. etc. #e8e8bd 699 new active 2004 Apr anonymous 2004 Apr 5 4 Data types and implicit conversions I am a long-time (since 1986 ...) Oracle user and I tend to use Oracle, definitely more than MySQL, as my reference. I currently have a project of writing a SQL*Plus-like interface to SQLite, rather than the sqlite program, with the idea of providing people with something which can be used for educational purposes on hardware much less impressive than what the latest Oracle versions require. Part of the project includes adding most of the (numerous) Oracle functions. I see dates as a major annoyance. I think that SQLite should be able to return DATE as a basic type besides TEXT and NUMERIC. I understand (even if I don't fully agree with) the type-less philosophy, but my belief is that, even if ordering doesn't belong to relational theory proper, a difference should be made where the expected ordering is different. If DATE is recognized as a basic type, it should also allow some type-checking in functions, more importantly some integrity checking (preventing from entering 06/31/2004 for instance), as well as implicit conversions. Oracle uses a default DD-MON-YY date format (which can be redefined), any string conforming to the default format which is inserted into a DATE column becomes a date without any fuss. Of course, an explicit conversion can be obtained through TO_DATE(). Currently, having a consistent usage of dates in SQLite relies on the user's own discipline - good enough in a carefully written program, but not for an interactive interface. _2004-Apr-21 15:08:39 by anonymous:_ {linebreak} You can let DBMS check the dates. But a good Front-End should check the date before trying a wrong 'insert' or 'update'. #e8e8bd 169 new active 2002 Oct anonymous Unknown 2004 Apr anonymous 5 5 CE version I've successfully ported sqlite to windows CE 3.0 using eVC3.5maybe it was already done ?) I have only modified os.c to take into account the unicode interface. I attach my modified file. Best regards Noel Frankinet Can you attach the full modified source code for me ? (.zip) My personal e-mail is hensel@al.furb.br Thanks ... Andri #e8e8bd 673 new active 2004 Mar anonymous Shell 2004 Apr 4 3 Format .dump nicer (patch) (I've already tried to mail this.So it goes here again.)I wanted the .dump and .schema commands to have niceroutput (better to read).So I wrote a small and simple formatter for sql.Note, that it is really simple, but should grok mostthings.Hope you like it. #e8e8bd 682 new active 2004 Apr anonymous 2004 Apr 5 1 sqlite_aggregate_context() valid in xFunc callback routine One of BIG limitations of SQL i suffer is that no information about previous row(s) is available in current row. Supose a table -------------- oChar | oNum -------------- a | 3 j | 2 w | 4 i would like to get ------------------------------------------------------ oChar | oNum | SQLConcat(oChar) | SQLContSum(oNum) ------------------------------------------------------ a | 3 | a | 3 j | 2 | aj | 5 w | 4 | ajw | 9 If in xFunc routine sqlite_aggregate_context() were valid, then functions like SQLConcat and SQLContSum will be possible, and multi-instace-able. BTW i see no advantage in using pUserData instead of any other global data, since it will be the same data for all instances of the function. Am i wrong? Thank you for SQLite! Best regards. Marcelo #e8e8bd 662 new active 2004 Mar anonymous 2004 Mar drh 5 5 Stored Procedures? I know that sqlite demands not to support Stored Procedures, but why? it is quite good. _2005-Aug-30 20:23:58 by anonymous:_ {linebreak} Stored procedures are becoming a standard practice with database development. Not supporting them in any form makes SQLite obsolete. ---- _2005-Aug-30 20:43:25 by anonymous:_ {linebreak} This is really something that deserves to be discussed on the mailing list. #e8e8bd 660 new active 2004 Mar anonymous 2004 Mar 5 5 compression/encoding feature? Would sqlite support built-in compression/encoding feature like http://www.sqliteplus.com/ does? and I think that the compression feature should use zlib which has source code. _2004-Mar-15 13:01:18 by anonymous:_ {linebreak} I check http://www.sqliteplus.com/ and fount that they have these features: 1.Stored Procedures 2.Encryption 3.Compression 4.Binary encoding for BLOB data will sqlite add these built-in features? #e8e8bd 651 new active 2004 Mar anonymous 2004 Mar 5 4 Update optimisation Possible optimisation on updates I have a big table in my DB. One column is a number which, in my case, can be either 0 or 1 The statement update info set new=0 where type='book' always takes about 60 seconds to run (even though 'type' is indexed) If I change the statement to update info set new=0 where type='book' and new=1 it's usually almost instantaneous. It looks as if SQLite is rewriting the row even though no actual data is being changed, so maybe the update code could be optimised to only rewrite the row if necessary. #e8e8bd 645 new active 2004 Mar anonymous 2004 Mar 5 5 OpenWatcom makefile I have prepared initial version of makefile for OpenWatcom compiler. I use OpenWatcom 1.3 compiled from sources synced yesterday with OpenWatcom Perforce (CVS) server. SQLite.exe gets compiled and linked directly from sources in SQLite /src directory. To get compilation done you need two external tools: egrep - it could be find in source tree for OpenWatcom any wersion of awk.exe - I've used 'gwk311b GNU Awk 3.1.1 for DJGPP v2' vi.exe editor in sed/ex/ed - like mode - vi is included in OpenWatcom package as standard editor. Please create directory /watcom under SQLite dir - on the same level as /src. Copy makefile to it and do wmake. To get lemon.c compiled with no errors I've had to change it. Please find attached diff file. Changes are cosmetic ones so it shouldn't broke anything: 15,21d14 < extern void qsort(); < extern double strtod(); < extern long strtol(); < extern void free(); < extern int access(); < extern int atoi(); < 43d35 < void Action_add(); 377c369 < ap = (struct action *)msort(ap,&ap->next,actioncmp); --- > ap = (struct action *)msort((char *)ap,(char **)&ap->next,actioncmp); 814c806 < Action_add(&stp->ap,SHIFT,sp,newstp); --- > Action_add(&stp->ap,SHIFT,sp,(char *)newstp); 915c907 < Action_add(&stp->ap,REDUCE,lemp->symbols[j],cfp->rp); --- > Action_add(&stp->ap,REDUCE,lemp->symbols[j],(char *)cfp->rp); 1191c1183 < current = (struct config *)msort(current,&(current->next),Configcmp); --- > current = (struct config *)msort((char *)current,(char **)&(current->next),Configcmp); 1198c1190 < basis = (struct config *)msort(current,&(current->bp),Configcmp); --- > basis = (struct config *)msort((char *)current,(char **)&(current->bp),Configcmp); 1449a1442 > return (lem.errorcnt + lem.nconflict); Makefile is not the best one but it works. Futer version will be better. Bartosz Polednia _2004-Mar-04 09:32:30 by anonymous:_ I forgot to append makefile: # # OpenWatcom makefile for SQLite # # Bartosz Polednia # bartosz@chill.com.pl # # 04.03.2004 - 0.01 # # set database version SQLITEVERSION = 2.8.12 # sqlite directory TOP = d:\!progs\sqlite #compiler settings CC = wcc386 CCL = wcl386 C_FLAGS = -wx -I$(TOP)\;.\ CCL_FLAGS = $(C_FLAGS) EXE_OUTPUT = -fe # Should the database engine assume text is coded as UTF-8 or iso8859? # # ENCODING = UTF8 # ENCODING = ISO8859 ENCODING = ISO8859 # Flags controlling use of the in memory btree implementation # # SQLITE_OMIT_INMEMORYDB is defined in order to omit the in-memory # red/black tree driver in the file btree_rb.c # # TEMP_STORE is 0 to force temporary tables to be in a file, 1 to # default to file, 2 to default to memory, and 3 to force temporary # tables to always be in memory. # INMEMORYDB = 1 INCOREFLAGS = -DTEMP_STORE=@TEMP_STORE@ !ifeq $(INMEMORYDB) 0 INCOREFLAGS += -DSQLITE_OMIT_INMEMORYDB=1 !endif # You should not have to change anything below this line ############################################################################### all: sqlite.h sqlite.lib sqlite.exe # Object files for the SQLite library. # LIBOBJ = attach.obj auth.obj btree.obj build.obj copy.obj date.obj & delete.obj expr.obj func.obj hash.obj insert.obj & main.obj opcodes.obj os.obj pager.obj parse.obj pragma.obj & printf.obj random.obj select.obj table.obj tokenize.obj & update.obj util.obj vacuum.obj vdbe.obj vdbeaux.obj & where.obj trigger.obj btree_rb.obj # Only build the in-core DB if it is required. !ifeq $(INMEMORYDB) 1 LIBOBJ += btree_rb.obj !endif # Header files used by all library source files. # HDR = sqlite.h $(TOP)\src\btree.h config.h $(TOP)\src\hash.h opcodes.h & $(TOP)\src\os.h $(TOP)\src\sqliteInt.h $(TOP)\src\vdbe.h parse.h # Header files used by the VDBE submodule # VDBEHDR = $(HDR)$(TOP)\src\vdbeInt.h # This is the default Makefile target. The objects listed here # are what get build when you type just "make" with no arguments. # sqlite.lib: sqlite.h config.h parse.h opcodes.h lemon.exe $(LIBOBJ) for %i in ($(LIBOBJ)) do wlib sqlite.lib -+%i > nul sqlite.exe: $(TOP)\src\shell.c sqlite.lib $(CCL) $(CCL_FLAGS) $(TOP)\src\shell.c sqlite.lib $(EXE_OUTPUT)=sqlite.exe # Rules to build the LEMON compiler generator # lemon.exe: lemon.c $(TOP)\tool\lempar.c $(CCL) $(CCL_FLAGS) $(EXE_OUTPUT)=lemon.exe lemon.c copy $(TOP)\tool\lempar.c . main.obj: $(TOP)\src\main.c $(HDR) $(CC) $(TOP)\src\main.c $(C_FLAGS) # $(CC) $(TOP)\src\$[&.c $(C_FLAGS) # ${INCOREFLAGS} # $(LTCOMPILE) -c ${INCOREFLAGS} $(TOP)\src\main.c opcodes.obj: opcodes.c $(CC) $[@ $(C_FLAGS) opcodes.c: $(TOP)\src\vdbe.c %create temp.vi %write temp.vi set magic %append temp.vi set magicstring = () %append temp.vi atomic %append temp.vi %s/^.*OP_/ "/ %append temp.vi %s/:.*$$/", / %append temp.vi x %create opcodes.c %write opcodes.c /* Automatically generated file. Do not edit */ %write opcodes.c char *sqliteOpcodeNames[] = { "???", egrep "^case OP_" $(TOP)\src\vdbe.c > opcodes.tmp vi -s temp.vi opcodes.tmp rm temp.vi copy opcodes.c+opcodes.tmp opcodes.c echo }; >> opcodes.c rm opcodes.tmp opcodes.h: $(TOP)\src\vdbe.h $(TOP)\src\vdbe.c %create temp.vi %write temp.vi set magic %append temp.vi set magicstring = () %append temp.vi atomic %append temp.vi %s/:// %append temp.vi x %create opcodes.h %write opcodes.h /* Automatically generated file. Do not edit */ egrep "^case OP_" $(TOP)\src\vdbe.c > opcodes.tmp vi -s temp.vi opcodes.tmp rm temp.vi awk '{printf "$#define %-30s %3d\n", $$2, ++cnt}' opcodes.tmp > temp.awk type temp.awk >> opcodes.h rm opcodes.tmp rm temp.awk parse.obj: parse.c $(HDR) $(CC) $[@ $(C_FLAGS) -I$(TOP)\src\ parse.h: parse.c @echo parse.h parse.c: $(TOP)\src\parse.y lemon.exe copy $(TOP)\src\parse.y . .\lemon .\parse.y rm parse.y rm lempar.c # The config.h file will contain a single #define that tells us how # many bytes are in a pointer. This only works if a pointer is the # same size on the host as it is on the target. If you are cross-compiling # to a target with a different pointer size, you'll need to manually # configure the config.h file. # config.h: %write temp.c $#include ...
30.0 30 %append temp.c int main(){ %append temp.c printf("$#define SQLITE_PTR_SZ %d\n",sizeof(char*)); %append temp.c return (0);} $(CCL) $(C_FLAGS) $(EXE_OUTPUT)=temp.exe temp.c .\temp.exe >config.h rm -f temp.c temp.exe temp.obj sqlite.h: $(TOP)\src\sqlite.h.in copy $(TOP)\src\sqlite.h.in sqlite.h %create temp.vi %write temp.vi set magic %append temp.vi set magicstring = () %append temp.vi atomic %append temp.vi %s/--VERS--/$(SQLITEVERSION)/ %append temp.vi %s/--ENCODING--/$(ENCODING)/ %append temp.vi x vi -s temp.vi sqlite.h rm temp.vi .c: $(TOP)\src\ # .c.obj: $(CC) $(TOP)\src\$[&.c $(C_FLAGS) Bartosz #e8e8bd 638 new active 2004 Mar anonymous 2004 Mar 5 5 Lemon.c could not be compiled - source dated 27.02.2004 I try to prepare makefile for OpenWatcom 1.3 and compile SQLite using standard source tree but compilation of lemon ends as in the attachment. Bartosz Polednia _2004-Mar-01 07:57:58 by anonymous:_ {linebreak} Open Watcom Make Version 1.3beta1 Limited Availability Portions Copyright (c) 1988-2002 Sybase, Inc. All Rights Reserved. Source code is available under the Sybase Open Watcom Public License. See http://www.openwatcom.org/ for details. wcl386 -wx -zm -fe=lemon.exe d:\!progs\sqlite\tool\lemon.c Open Watcom C/C++32 Compile and Link Utility Version 1.3beta1 LA Portions Copyright (c) 1988-2002 Sybase, Inc. All Rights Reserved. Source code is available under the Sybase Open Watcom Public License. See http://www.openwatcom.org/ for details. wcc386 d:\!progs\sqlite\tool\lemon.c -wx -zm Open Watcom C32 Optimizing Compiler Version 1.3beta1 LA Portions Copyright (c) 1984-2002 Sybase, Inc. All Rights Reserved. Source code is available under the Sybase Open Watcom Public License. See http://www.openwatcom.org/ for details. d:\!progs\sqlite\tool\lemon.c(814): Error! E1071: Type of parameter 4 does not agree with previous definition d:\!progs\sqlite\tool\lemon.c(814): Note! N2003: source conversion type is 'struct state *' d:\!progs\sqlite\tool\lemon.c(814): Note! N2004: target conversion type is 'char *' d:\!progs\sqlite\tool\lemon.c(915): Error! E1071: Type of parameter 4 does not agree with previous definition d:\!progs\sqlite\tool\lemon.c(915): Note! N2003: source conversion type is 'struct rule *' d:\!progs\sqlite\tool\lemon.c(915): Note! N2004: target conversion type is 'char *' d:\!progs\sqlite\tool\lemon.c(1450): Warning! W107: Missing return value for function 'main' d:\!progs\sqlite\tool\lemon.c(377): Error! E1071: Type of parameter 1 does not agree with previous definition d:\!progs\sqlite\tool\lemon.c(377): Note! N2003: source conversion type is 'struct action *' d:\!progs\sqlite\tool\lemon.c(377): Note! N2004: target conversion type is 'char *' d:\!progs\sqlite\tool\lemon.c(377): Note! N2002: 'msort' defined in: d:\!progs\sqlite\tool\lemon.c(1547) d:\!progs\sqlite\tool\lemon.c(377): Error! E1071: Type of parameter 2 does not agree with previous definition d:\!progs\sqlite\tool\lemon.c(377): Note! N2003: source conversion type is 'struct action **' d:\!progs\sqlite\tool\lemon.c(377): Note! N2004: target conversion type is 'char **' d:\!progs\sqlite\tool\lemon.c(1191): Error! E1071: Type of parameter 1 does not agree with previous definition d:\!progs\sqlite\tool\lemon.c(1191): Note! N2003: source conversion type is 'struct config *' d:\!progs\sqlite\tool\lemon.c(1191): Note! N2004: target conversion type is 'char *' d:\!progs\sqlite\tool\lemon.c(1191): Note! N2002: 'msort' defined in: d:\!progs\sqlite\tool\lemon.c(1547) d:\!progs\sqlite\tool\lemon.c(1191): Error! E1071: Type of parameter 2 does not agree with previous definition d:\!progs\sqlite\tool\lemon.c(1191): Note! N2003: source conversion type is 'struct config **' d:\!progs\sqlite\tool\lemon.c(1191): Note! N2004: target conversion type is 'char **' d:\!progs\sqlite\tool\lemon.c(1198): Error! E1071: Type of parameter 1 does not agree with previous definition d:\!progs\sqlite\tool\lemon.c(1198): Note! N2003: source conversion type is 'struct config *' d:\!progs\sqlite\tool\lemon.c(1198): Note! N2004: target conversion type is 'char *' d:\!progs\sqlite\tool\lemon.c(1198): Note! N2002: 'msort' defined in: d:\!progs\sqlite\tool\lemon.c(1547) d:\!progs\sqlite\tool\lemon.c(1198): Error! E1071: Type of parameter 2 does not agree with previous definition d:\!progs\sqlite\tool\lemon.c(1198): Note! N2003: source conversion type is 'struct config **' d:\!progs\sqlite\tool\lemon.c(1198): Note! N2004: target conversion type is 'char **' d:\!progs\sqlite\tool\lemon.c: 4388 lines, included 1949, 1 warnings, 8 errors Error: Compiler returned a bad status compiling 'd:\!progs\sqlite\tool\lemon.c' ---- _2004-Mar-01 11:20:38 by anonymous:_ The code is K&R style with mismatched prototypes eg.: ...... extern void qsort(); extern double strtod(); extern long strtol(); extern void free(); extern int access(); extern int atoi(); ........ void Action_add(); ..... void Action_add(app,type,sp,arg) struct action **app; enum e_action type; struct symbol *sp; char *arg; { Bartosz ---- _2004-Mar-01 13:15:19 by drh:_ {linebreak} Lemon was written before ANSI C compilers were widely available. It uses char* where void* would be more appropriate, because void* was not available on common compilers when lemon was written. Probably there is an option to Watcom to get it to accept char* in place of void*. I am disinclined to rewrite lemon to accomodate a compiler which I do not use. #e8e8bd 611 new active 2004 Feb anonymous VDBE 2004 Feb 1 3 Remove giant SWITCH statement in VM for better portability This is not really a bug but a major drawback to the platform portability of SQLite. Specifically, this relates to the ease of support for segmented architectures such as Palm OS. The problem is that the enormous SWITCH statement in the sqliteVdbeExec() function contains more than 64K of code. In a 64K segmented architecture this exceeds the code segment limit and the ONLY workaround is to break up the code into subfunctions. This is not an easy task. At present our "workaround" involves creating a "context" data structure that holds necessary state for execution (essentially the locals of sqliteVdbeExec()) then running an elaborate AWK script that parses each CASE in the switch statement and generates a separate function for the CASE that receives the execution context structure (functions are indexed by opcode for speed). This, followed by some hand tuning is our process of porting whenever we need to pick up a new release of SQLite. Over time this has proven to be the easiest way to integrate new releases, however, it is far from optimial and because of the non-automated steps involved, bug prone. After making this change the SQLite engine runs fine on the Palm OS segmented architecture and suffers no visible performance drawbacks. If a similar approach were to be integrated directly into the SQLite codebase it would GREATLY improve the portability of the engine. _2004-Feb-16 23:05:07 by drh:_ {linebreak} I experimented with using separate functions for each opcode and found the results to be about 20% slower than using a switch on GCC for i386. So changing the code to use functions only is clearly not an option for most users. Modifying the code so that it could be compiled either way would complicate the code and it would eliminate the opportunity to use persistent local variables. If absolutely necessary, this could be tolerated. But is it really necessary? The sqliteVdbeExec() function is only about 21K in size when compiled using GCC for i386 - less than a third of the maximum size allowed by PalmOS. Why is it so much bigger on Palm? Wouldn't a better approch to this problem be to fix the compiler on the Palm so that it generated binaries of a more modest size? Surely the Palm does not require more than three times the code space as an i386. Is there a GCC implementation available that targets palm? Have you tried compiling using it? ---- _2004-Mar-24 11:22:01 by anonymous:_ {linebreak} _>Why is it so much bigger on Palm?_ The reason is not the compiler, but processor architecture. Palm's processor is a RISC (reduced instruction set computer) processor (in opposite to x86 CICS (complex instruction set compute)). That means that in general RISC code is much bigger, because action performed by one CISC instruction, takes several RISC instructions to do the same. ---- _2006-Jun-06 18:38:46 by anonymous:_ {linebreak} Perhaps the "workaround" could be implemented as a Palm-specific build script, augmented by some source-level "hints" (ala lint comments) to automate the tasks currently hand-tuned. #e8e8bd 590 new active 2004 Jan anonymous Parser 2004 Jan 5 4 Treat ` like spaces outside of quoted strings. Mysql allows the usage of ` for quoting field names in queries. SQLite interrupts with "unrecognized token". To improve compatibility with mysql ` should either be handled correctly or simply ignored. For ignoring ` the following code can be used: case ' ': case '\t': case '\n': case '\f': case '\r': case '`': { for(i=1; isspace(z[i]) || z[i] == '`'; i++){} starting at line 224 in tokenize.c I know that this is a dirty enhancement but the structure of tokenize.c is rather unknown to me... #e8e8bd 588 new active 2004 Jan anonymous 2004 Jan 5 4 Please provide support for NULLS LAST in ORDER BY clauses. Would be nice to be able to specify NULLS LAST in ORDER BY clauses to force nulls to end of sort order. (Oracle provides this feature) #e8e8bd 93 new active 2002 Jul anonymous 2004 Jan 3 3 Can't open database when TEMP environment variable is not defined well This happened on WIN32 but is relevant to unix too. When the TEMP variable is not configured well the GetTempPath returns it without any checking that the directory exists. The error the database returns is something like "can't find table sqlite_temp_master" while the real error is that the temporary file couldn't be created. For security reason, the best practice is to give the application to set the temp directory. Then as the application writter I know where all the relevant sqlite files will be created and have full control of it. I will be happy to do the fix (If you find it suitable). Thanks in advanced Avner This happens always when using sqlite from a NT / Win2000 / XP service since they don't resolve the default TEMP directory var. #e8e8bd 572 new active 2004 Jan anonymous Unknown 2004 Jan drh 3 3 sqlite 2.8.11 port to djgpp hello friends here is a patch to be applied to sqlite 2.8.11 to work with djgpp in a short file names environment. it's a very small piece of code patches, but i will explicitly stick to the sqlite copyright policy: The author or authors of this code dedicate any and all copyright interest in this code to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights this code under copyright law. please apply this patch to the mainstream sources, for the benefit of all dos djgpp users. best regards, alex #e8e8bd 566 new active 2004 Jan anonymous Unknown 2004 Jan 4 4 [PATCH] Port of sqlite and lemon to an EBCDIC mainframe I have ported sqlite (and lemon - to bootstrap sqlite) to a mainframe which features a POSIX subsystem, but in which all files are stored in EBCDIC, not in ASCII. Very few places in sqlite have codeset dependencies, so the resulting patch is rather small. I have not tested tcl (lacking a tcl-ebcdic port). I tested with a 3MB database of german bank codes and compared against a FreeBSD ASCII version. The diffs for lemon are ~280 lines. Martin Kraemer The Attachment contains the complete patch to make 2.8.9...2.8.11 compile & run on our EBCDIC platform. #e8e8bd 563 new active 2004 Jan anonymous Parser 2004 Jan 4 3 Support for autoincrement type "SERIAL" (from PostgreSQL) I am porting my application from PostgreSQL to SQLite but I want to keep it backwards compatible. With PostgreSQL autoincrement fields are created with type SERIAL. For example: _:CREATE TABLE t1( a SERIAL PRIMARY KEY, b INTEGER); And statements like: _:INSERT INTO t1 VALUES(default,123); are used to create rows with automatically increased ids. #e8e8bd 550 new active 2004 Jan anonymous Unknown 2004 Jan anonymous 1 1 change of MASTER_NAME and TEMP_MASTER_NAME definition error sqlite_master and sqlite_temp_master table name are directly use in main.c, shell.c and vacuum.c so when i try to change MASTER_NAME and TEMP_MASTER_NAME definition in the sqliteInt.h file it returns some errors. so i replace *sqlite_master* and *sqlite_master_table* by _"MASTER_NAME"_ and _"TEMP_MASTER_NAME"_ to solve my problem in {quote: main.c} *: at line 195 in *static char master_schema[]* (in sqliteInitOne) *: at line 203 in *static char temp_master_schema[]* (in sqliteInitOne) *: at lines 228,230 in *static char init_script[]* (in sqliteInitOne) *: at lines 232,234,237 in *static char older_init_script[]* (in sqliteInitOne) *: at line 344 in *sqliteInitOne* *: at line 492 in *sqlite_open* {quote: vacuum.c} *: at line 293 in *int sqliteRunVacuum* *: at line 296 in *int sqliteRunVacuum* {quote: shell.c} *: at line 584 in *static int do_meta_command* *: at line 293 in *static int do_meta_command* *: at lines 793,795,831,832,872,875,882,885 in *static int do_meta_command* #e8e8bd 544 new active 2004 Jan anonymous 2004 Jan 5 4 Feature Request: Indicate UNIQUE columns in table_info. I goofed with ticket #543. I meant that I thought it would be neat if we had a new column in table_info indicating which columns were *UNIQUE*. Sorry about that. #e8e8bd 539 new active 2003 Dec anonymous VDBE 2003 Dec 5 4 Addition of engine 'Suspension' interface For my use of SQLite I require that the engine be suspended temporarily such that other tasks can be performed. At present SQLite has the ability to be interrupted - stopping the current VM in its tracks and aborting whatever it was doing. This isn't what I required - I just need it to stop doing what it was doing and return to me. As the system is implemented as a Virtual Machine and is already capable of being suspended by in order to return its results, the suspension of the engine is relatively simple. The implementation I have used will return the code 'SQLITE_SUSPEND' from an sqlite_step() call if the operation has been suspended. The function sqlite_suspend(sqlite_vm *) is provided to set a flag which will cause the suspend to take effect. The intended use is within an environment where a co-operative environment where SQLite can run for a period before and interrupt triggers the sqlite_suspend() function. Thus the caller can know that the operation is progressing but no results have been returned before the timeout. I can create diffs, or supply other information about the information if necessary. Sadly I've only tried this with the sqlite_step() interface, because that's all I've needed it with. My examination of the code implies that it is safe to perform this suspension in this manner, and it has worked very well within my application. I don't know if it would be of use to others, but I can at least offer it back to anyone who might find a use for it. #e8e8bd 538 new active 2003 Dec anonymous 2003 Dec 4 3 Different warnings in windows If you want the file, please contact me & I'm happy to send it through [c:\]diff -up "C:\C++ Programs\DELISprint\SQLite" "sqlite_source_windows" > "new SQLite.txt" ------------------------------------------------diff code diff -up C:\C++ Programs\DELISprint\SQLite/btree.c sqlite_source_windows/btree.c --- C:\C++ Programs\DELISprint\SQLite/btree.c Tue Dec 30 22:58:12 2003 +++ sqlite_source_windows/btree.c Wed Dec 17 19:37:30 2003 @@ -65,7 +65,7 @@ static BtCursorOps sqliteBtreeCursorOps; ** X is an unsigned integer. SWAB16 byte swaps a 16-bit integer. ** SWAB32 byteswaps a 32-bit integer. */ -#define SWAB16(B,X) ((B)->needSwab? swab16((u16)(X)) : (u16)(X)) +#define SWAB16(B,X) ((B)->needSwab? swab16((u16)X) : ((u16)X)) #define SWAB32(B,X) ((B)->needSwab? swab32(X) : (X)) #define SWAB_ADD(B,X,A) \ if((B)->needSwab){ X=swab32(swab32(X)+A); }else{ X += (A); } @@ -538,7 +538,7 @@ static void freeSpace(Btree *pBt, MemPag if( idx + iSize + size == SWAB16(pBt, pFBlk->iNext) ){ pNext = (FreeBlk*)&pPage->u.aDisk[idx + iSize + size]; if( pBt->needSwab ){ - pFBlk->iSize = swab16((u16)(swab16(pNext->iSize)+iSize+size)); + pFBlk->iSize = swab16((u16)swab16(pNext->iSize)+iSize+size); }else{ pFBlk->iSize += pNext->iSize; } diff -up C:\C++ Programs\DELISprint\SQLite/date.c sqlite_source_windows/date.c --- C:\C++ Programs\DELISprint\SQLite/date.c Tue Dec 30 22:53:08 2003 +++ sqlite_source_windows/date.c Wed Dec 17 19:37:30 2003 @@ -230,8 +230,8 @@ static void computeJD(DateTime *p){ } A = Y/100; B = 2 - A + (A/4); - X1 = (int)(365.25*(Y+4716)); - X2 = (int)(30.6001*(M+1)); + X1 = 365.25*(Y+4716); + X2 = 30.6001*(M+1); p->rJD = X1 + X2 + D + B - 1524.5; p->validJD = 1; p->validYMD = 0; @@ -335,14 +335,14 @@ static int parseDateOrTime(const char *z static void computeYMD(DateTime *p){ int Z, A, B, C, D, E, X1; if( p->validYMD ) return; - Z = (int)(p->rJD + 0.5); - A = (int)((Z - 1867216.25)/36524.25); + Z = p->rJD + 0.5; + A = (Z - 1867216.25)/36524.25; A = Z + 1 + A - (A/4); B = A + 1524; - C = (int)((B - 122.1)/365.25); - D = (int)(365.25*C); - E = (int)((B-D)/30.6001); - X1 = (int)(30.6001*E); + C = (B - 122.1)/365.25; + D = 365.25*C; + E = (B-D)/30.6001; + X1 = 30.6001*E; p->D = B - D - X1; p->M = E<14 ? E-1 : E-13; p->Y = p->M>2 ? C - 4716 : C - 4715; @@ -355,10 +355,10 @@ static void computeYMD(DateTime *p){ static void computeHMS(DateTime *p){ int Z, s; if( p->validHMS ) return; - Z = (int)(p->rJD + 0.5); - s = (int)((p->rJD + 0.5 - Z)*86400000.0 + 0.5); + Z = p->rJD + 0.5; + s = (p->rJD + 0.5 - Z)*86400000.0 + 0.5; p->s = 0.001*s; - s = (int)(p->s); + s = p->s; p->s -= s; p->h = s/3600; s -= p->h*3600; @@ -422,14 +422,14 @@ static int parseModifier(const char *zMo ** to "start of day". */ if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0 - && (n=(int)(r))==r && n>=0 && r<7 ){ + && (n=r)==r && n>=0 && r<7 ){ int Z; computeYMD(p); p->validHMS = 0; p->validTZ = 0; p->validJD = 0; computeJD(p); - Z = (int)(p->rJD + 1.5); + Z = p->rJD + 1.5; Z %= 7; if( Z>n ) Z -= 7; p->rJD += n - Z; @@ -503,19 +503,19 @@ static int parseModifier(const char *zMo }else if( n==5 && strcmp(z,"month")==0 ){ int x, y; computeYMD(p); - p->M += (int)(r); + p->M += r; x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12; p->Y += x; p->M -= x*12; p->validJD = 0; computeJD(p); - y = (int)(r); + y = r; if( y!=r ){ p->rJD += (r - y)*30.0; } }else if( n==4 && strcmp(z,"year")==0 ){ computeYMD(p); - p->Y += (int)(r); + p->Y += r; p->validJD = 0; computeJD(p); }else{ @@ -691,8 +691,8 @@ static void strftimeFunc(sqlite_func *co switch( zFmt[i] ){ case 'd': sprintf(&z[j],"%02d",x.D); j+=2; break; case 'f': { - int s = (int)(x.s); - int ms = (int)((x.s - s)*1000.0); + int s = x.s; + int ms = (x.s - s)*1000.0; sprintf(&z[j],"%02d.%03d",s,ms); j += strlen(&z[j]); break; @@ -706,7 +706,7 @@ static void strftimeFunc(sqlite_func *co y.M = 1; y.D = 1; computeJD(&y); - n = (int)(x.rJD - y.rJD + 1); + n = x.rJD - y.rJD + 1; if( zFmt[i]=='W' ){ sprintf(&z[j],"%02d",(n+6)/7); j += 2; diff -up C:\C++ Programs\DELISprint\SQLite/os.c sqlite_source_windows/os.c --- C:\C++ Programs\DELISprint\SQLite/os.c Tue Dec 30 22:42:48 2003 +++ sqlite_source_windows/os.c Wed Dec 17 19:37:30 2003 @@ -258,7 +258,7 @@ int sqliteOsDelete(const char *zFilename unlink(zFilename); #endif #if OS_WIN - DeleteFileA(zFilename); + DeleteFile(zFilename); #endif #if OS_MAC unlink(zFilename); @@ -274,7 +274,7 @@ int sqliteOsFileExists(const char *zFile return access(zFilename, 0)==0; #endif #if OS_WIN - return GetFileAttributesA(zFilename) != 0xffffffff; + return GetFileAttributes(zFilename) != 0xffffffff; #endif #if OS_MAC return access(zFilename, 0)==0; @@ -350,7 +350,7 @@ int sqliteOsOpenReadWrite( return SQLITE_OK; #endif #if OS_WIN - HANDLE h = CreateFileA(zFilename, + HANDLE h = CreateFile(zFilename, GENERIC_READ | GENERIC_WRITE, FILE_SHARE_READ | FILE_SHARE_WRITE, NULL, @@ -359,7 +359,7 @@ int sqliteOsOpenReadWrite( NULL ); if( h==INVALID_HANDLE_VALUE ){ - h = CreateFileA(zFilename, + h = CreateFile(zFilename, GENERIC_READ, FILE_SHARE_READ, NULL, @@ -482,7 +482,7 @@ int sqliteOsOpenExclusive(const char *zF }else{ fileflags = FILE_FLAG_RANDOM_ACCESS; } - h = CreateFileA(zFilename, + h = CreateFile(zFilename, GENERIC_READ | GENERIC_WRITE, 0, NULL, @@ -556,7 +556,7 @@ int sqliteOsOpenReadOnly(const char *zFi return SQLITE_OK; #endif #if OS_WIN - HANDLE h = CreateFileA(zFilename, + HANDLE h = CreateFile(zFilename, GENERIC_READ, 0, NULL, @@ -679,7 +679,7 @@ int sqliteOsTempFileName(char *zBuf){ "0123456789"; int i, j; char zTempPath[SQLITE_TEMPNAME_SIZE]; - GetTempPathA(SQLITE_TEMPNAME_SIZE-30, zTempPath); + GetTempPath(SQLITE_TEMPNAME_SIZE-30, zTempPath); for(i=strlen(zTempPath); i>0 && zTempPath[i-1]=='\\'; i--){} zTempPath[i] = 0; for(;;){ @@ -899,8 +899,8 @@ int sqliteOsSeek(OsFile *id, off_t offse #endif #if OS_WIN { - LONG upperBits = (LONG) (offset>>32); - LONG lowerBits = (LONG) (offset & 0xffffffff); + LONG upperBits = offset>>32; + LONG lowerBits = offset & 0xffffffff; DWORD rc; rc = SetFilePointer(id->h, lowerBits, &upperBits, FILE_BEGIN); /* TRACE3("SEEK rc=0x%x upper=0x%x\n", rc, upperBits); */ @@ -991,8 +991,8 @@ int sqliteOsTruncate(OsFile *id, off_t n #endif #if OS_WIN { - LONG upperBits = (LONG) (nByte>>32); - SetFilePointer(id->h, (LONG) (nByte), &upperBits, FILE_BEGIN); + LONG upperBits = nByte>>32; + SetFilePointer(id->h, nByte, &upperBits, FILE_BEGIN); SetEndOfFile(id->h); } return SQLITE_OK; @@ -1576,10 +1576,10 @@ char *sqliteOsFullPathname(const char *z char *zNotUsed; char *zFull; int nByte; - nByte = GetFullPathNameA(zRelative, 0, 0, &zNotUsed) + 1; + nByte = GetFullPathName(zRelative, 0, 0, &zNotUsed) + 1; zFull = sqliteMalloc( nByte ); if( zFull==0 ) return 0; - GetFullPathNameA(zRelative, nByte, zFull, &zNotUsed); + GetFullPathName(zRelative, nByte, zFull, &zNotUsed); return zFull; #endif #if OS_MAC diff -up C:\C++ Programs\DELISprint\SQLite/pager.c sqlite_source_windows/pager.c --- C:\C++ Programs\DELISprint\SQLite/pager.c Tue Dec 30 22:45:48 2003 +++ sqlite_source_windows/pager.c Wed Dec 17 19:37:30 2003 @@ -599,17 +599,17 @@ static int pager_playback(Pager *pPager, rc = read32bits(format, &pPager->jfd, &pPager->cksumInit); if( rc ) goto end_playback; if( nRec==0xffffffff || useJournalSize ){ - nRec = (int) ((szJ - JOURNAL_HDR_SZ(3))/JOURNAL_PG_SZ(3)); + nRec = (szJ - JOURNAL_HDR_SZ(3))/JOURNAL_PG_SZ(3); } }else{ - nRec = (int) ((szJ - JOURNAL_HDR_SZ(2))/JOURNAL_PG_SZ(2)); + nRec = (szJ - JOURNAL_HDR_SZ(2))/JOURNAL_PG_SZ(2); assert( nRec*JOURNAL_PG_SZ(2)+JOURNAL_HDR_SZ(2)==szJ ); } rc = read32bits(format, &pPager->jfd, &mxPg); if( rc!=SQLITE_OK ){ goto end_playback; } - assert( pPager->origDbSize==0 || pPager->origDbSize==(int)(mxPg) ); + assert( pPager->origDbSize==0 || pPager->origDbSize==mxPg ); rc = sqliteOsTruncate(&pPager->fd, SQLITE_PAGE_SIZE*(off_t)mxPg); if( rc!=SQLITE_OK ){ goto end_playback; @@ -717,7 +717,7 @@ static int pager_ckpt_playback(Pager *pP if( rc!=SQLITE_OK ){ goto end_ckpt_playback; } - nRec = (int)((szJ - pPager->ckptJSize)/JOURNAL_PG_SZ(journal_format)); + nRec = (szJ - pPager->ckptJSize)/JOURNAL_PG_SZ(journal_format); for(i=nRec-1; i>=0; i--){ rc = pager_playback_one_page(pPager, &pPager->jfd, journal_format); if( rc!=SQLITE_OK ){ @@ -925,9 +925,9 @@ int sqlitepager_pagecount(Pager *pPager) } n /= SQLITE_PAGE_SIZE; if( pPager->state!=SQLITE_UNLOCK ){ - pPager->dbSize = (int)(n); + pPager->dbSize = n; } - return (int)(n); + return n; } /* diff -up C:\C++ Programs\DELISprint\SQLite/parse.c sqlite_source_windows/parse.c --- C:\C++ Programs\DELISprint\SQLite/parse.c Tue Dec 30 23:00:24 2003 +++ sqlite_source_windows/parse.c Wed Dec 17 19:37:30 2003 @@ -3982,7 +3982,7 @@ void sqliteParser( yyTracePrompt,yyTokenName[yymajor]); } #endif - yy_destructor((YYCODETYPE)(yymajor),&yyminorunion); + yy_destructor(yymajor,&yyminorunion); yymajor = YYNOCODE; }else{ while( @@ -3993,7 +3993,7 @@ void sqliteParser( yy_pop_parser_stack(yypParser); } if( yypParser->yyidx < 0 || yymajor==0 ){ - yy_destructor((YYCODETYPE)(yymajor),&yyminorunion); + yy_destructor(yymajor,&yyminorunion); yy_parse_failed(yypParser); yymajor = YYNOCODE; }else if( yypParser->yytop->major!=YYERRORSYMBOL ){ diff -up C:\C++ Programs\DELISprint\SQLite/select.c sqlite_source_windows/select.c --- C:\C++ Programs\DELISprint\SQLite/select.c Tue Dec 30 22:46:48 2003 +++ sqlite_source_windows/select.c Wed Dec 17 19:37:30 2003 @@ -98,7 +98,7 @@ int sqliteJoinType(Parse *pParse, Token for(i=0; i<3 && apAll[i]; i++){ p = apAll[i]; for(j=0; j------------------------------------------------diff code #e8e8bd 477 new active 2003 Oct anonymous 2003 Dec anonymous 4 4 Are there any plans to enchance SQLite to support Unicode Hi, Are there any plans to enchance SQLite to support UNICODE. Did anyone try and had any problems. I would like to use SQLite in my embedded device. I might have to modify SQLite to support UNICODE. If anyone has any points please let me know. Thanks, Use UTF8 encoding, which doesn't require 0 values, and is especially efficient for encoding ASCII text. ------- But sqlite_exec takes a char* for SQL statement. Do I need to change this peace of code or is anything simple that can be done so that I can pass unicode information ------- UTF8 encoding looks like a normal ASCIIZ string; the NUL byte is not valid UTF8 encoding, and therefore any UTF8 string can be given to SQLite, which only cares that the data be an ASCIIZ string. ------- Will SELCT lower(text), upper(text) works ? I suppouse NO... Support of unicode in some form is needed. There is no hack way to do this my client side. #e8e8bd 404 new active 2003 Jul anonymous 2003 Dec 3 3 new C API: sqlite_get_table_with_types with type information Patches for a new C API function: sqlite_get_table_with_types, which is like sqlite_get_table but also adds type information in the return data which apepars right after the column names. Implementation (patch is against 2.8.3) is almost zero overhead and only needs an extra few lines: --- table.c.old Sun Jul 13 11:59:18 2003 +++ table.c Sun Jul 13 14:54:13 2003 @@ -33,6 +33,7 @@ int nColumn; int nData; int rc; + int headHasType; } TabResult; /* @@ -50,7 +51,7 @@ ** we need to remember from this invocation of the callback. */ if( p->nRow==0 && argv!=0 ){ - need = nCol*2; + need = nCol * (p->headHasType ? 3 : 2); }else{ need = nCol; } @@ -69,8 +70,9 @@ ** the names of all columns. */ if( p->nRow==0 ){ + int cols_top = (p->headHasType ? nCol * 2 : nCol); p->nColumn = nCol; - for(i=0; in)==keywords[j].nChar + if( p->n==keywords[j].nChar && sqliteStrNICmp(p->z, keywords[j].zKeyword, p->n)==0 ){ jointype |= keywords[j].code; break; diff -up C:\C++ Programs\DELISprint\SQLite/vdbe.c sqlite_source_windows/vdbe.c --- C:\C++ Programs\DELISprint\SQLite/vdbe.c Tue Dec 30 22:53:08 2003 +++ sqlite_source_windows/vdbe.c Wed Dec 17 19:37:30 2003 @@ -1292,7 +1292,7 @@ case OP_MustBeInt: { if( aStack[tos].flags & STK_Int ){ /* Do nothing */ }else if( aStack[tos].flags & STK_Real ){ - int i = (int)(aStack[tos].r); + int i = aStack[tos].r; double r = (double)i; if( r!=aStack[tos].r ){ goto mismatch; @@ -1307,7 +1307,7 @@ case OP_MustBeInt: { } Realify(p, tos); assert( (aStack[tos].flags & STK_Real)!=0 ); - v = (int)(aStack[tos].r); + v = aStack[tos].r; r = (double)v; if( r!=aStack[tos].r ){ goto mismatch; NUL (ASCII 0) \' \" \b \n \r \t \\ \z #e8e8bd 510 new active 2003 Nov anonymous Unknown 2003 Nov 5 2 trigger: addition of create ability this code COULD be implemented outside the .sql script, but if this were possible it would cut that code by about 70-80%! not to mention makes it 10x easier to handle the db. this is only a chunk: /* create group info table on addtion to master group list /*{linebreak} create trigger group_make_new after insert on group_list {linebreak} begin {linebreak} /* make table of users for group /* {linebreak} create table group_[new.groupID] ( {linebreak} userID unique, {linebreak} userlevel {linebreak} ); {linebreak} /* insert owner as a member /* {linebreak} insert into group_[new.groupID] values (new.owner,3); {linebreak} end; {linebreak} note: you'll have to fix the comments cause of this posting format.... #e8e8bd 498 new active 2003 Nov anonymous Unknown 2003 Nov 4 4 Suggestion: Allow named parameters. The ability to add parameters using "?" is great. However, it would be even better if you could add a meaningful names to the parameters. This would be just for human purposes, as the parser could just ignore the names. For example: "insert into mytable values (?, ?, ?)" could be written like "insert into mytable values (?Day, ?Month, ?Year)" Named parameters could also be used to allow a parameter to appear multiple times in a single query, and yet only require a single call to sqlite_bind to set its value. For example SELECT quantity FROM Inventory WHERE :price < 10 OR :price > 100; would only require a single call to sqlite_bind to set the value of the :price parameter. This feature becomes more valuable with complex queries where multiple sub-selects may require the same parameter. Implementing this feature outside SQLite would require parsing the SQL statments to locate the named parameters, substituting the positional parameters, then using the existing API to set the value of the positional parameters. This seems wasteful since SQLite will have to parse the same SQL statement again to extract the positional parameters. The colon, ":", is used by SQL:1999 as the standard prefix to denote parameter names, and should probably be adopted by SQLite as well if named parameters are implemented. #e8e8bd 259 new active 2003 Mar anonymous CodeGen 2003 Nov 1 2 ORDER BY queries including an integer primary key don't use index G'day, I have a table and index with the following specification: CREATE TABLE Disturbance (Entry INTEGER PRIMARY KEY,Input,Value,Time,HmiQuality,Quality); CREATE INDEX DisturbanceIndex ON Disturbance ( Input, Time, Entry ); When I do a query where the order by includes Input and Time, but not Entry no sorting is required as sqlite uses the index. When I include Entry at the end of the order by the index is not used for sorting. I've traced this to the findSortingIndex() function in where.c. The first two order-by entries pass the appropriate tests in the part of the function beginning at pMatch = 0. The third colum fails the test. On the line if( pOrderBy->a[i+j].pExpr->iColumn!=pIdx->aiColumn[i+nEqCol] ) break; the break is triggered because pOrderBy->a[i+j].pExpr->iColumn = -1, but pIdx->aiColumn[i+nEqCol] = 0. It looks to me like it's the order by version that's wrong, but I didn't want to delve any deeper than this, because there's probably a reason why the value is -1, and not 0 :) I'll leave it for more experienced hands to look at from this point onwards. Any work-around suggestions would also be appreciated as this is software that will be going to a customer in a week :) Benjamin. Yes, the query optimizer, and especially the code that tries to avoid sorting by using an index, needs some work. That has been on the to-do list for some time. Here is a temporary work-around: In the schema you define above, "ORDER BY Input, Time, Entry" and "ORDER BY Input, Time" will always generate the same order. So you should use the second ORDER BY form. Or, consider making Entry an "INT PRIMARY KEY" instead of "INTEGER PRIMARY KEY" to avoid invoking the primary key magic of SQLite. BTW, a value of -1 for a column number means that the column is the key of the record. #e8e8bd 268 new active 2003 Mar anonymous Parser 2003 Nov 1 3 columns of subselects containing joins are not recognized The following is a transcript of a minimal sqlite session exhibiting the problem: SQLite version 2.8.0 Enter ".help" for instructions sqlite> create table A(x); sqlite> create table B(y); sqlite> select * from (select * from A,B) where x = 0; SQL error: no such column: x The problem does not appear to be with the statement _select * from (select * from A,B)_ itself, because that produces the correct results, but with the parser being unable to resolve the column name. Using an alias does not help: sqlite> select * from (select * from A,B) as t1 where t1.x = 0; SQL error: no such column: t1.x Obviously the simpler _select * from A,B where x = 0_ would work, but that's not a workaround in this case - I ran into this because I'm working with a relational algebra to SQL translator, that doesn't know how to simplify the expressions to more idiomatic SQL. #e8e8bd 209 new active 2002 Dec anonymous BTree 2003 Nov 1 1 update libtool The version of libtool shipped with sqlite is very out of date (well over a year old). It does not properly detect the ability to build shared libraries on darwin, for example, which is bad. You need to update to the latest version of gnu libtool. #e8e8bd 338 new active 2003 Jun anonymous Unknown 2003 Nov 5 1 lowerFunc and upperFunc Fail on foreign characters This seems to work better static void upperFunc(sqlite_func *context, int argc, const char **argv){ char *z; int i; if( argc<1 || argv[0]==0 ) return; z = sqlite_set_result_string(context, argv[0], -1); if( z==0 ) return; for(i=0; z[i]; i++){ if ( ( z[i] >= 'a' && z[i] <= 'z' ) || ( z[i] >= '�' && z[i] <= '�' ) || ( z[i] >= '�' && z[i] <= '�' ) ) z[i] = toupper(z[i]); } } static void lowerFunc(sqlite_func *context, int argc, const char **argv){ char *z; int i; if( argc<1 || argv[0]==0 ) return; z = sqlite_set_result_string(context, argv[0], -1); if( z==0 ) return; for(i=0; z[i]; i++){ if ( ( z[i] >= 'A' && z[i] <= 'Z' ) || ( z[i] >= '�' && z[i] <= '�' ) || ( z[i] >= '�' && z[i] <= '�' ) ) z[i] = tolower(z[i]); } } #e8e8bd 235 new active 2003 Jan anonymous Unknown 2003 Nov chw 5 1 Local language do not support! We are from Russia! We need support for local language! I think, this make faster, if you add the WinAPI functions for compare ansi string, or other way. To fix this you can add one small function to the sqlite sources. Note, I don't know about possible damages. --------------------------------------------------------- --- sqlite/src/util.c.locale Sat Aug 31 20:14:35 2002 +++ sqlite/src/util.c Fri Sep 27 14:06:56 2002 @@ -415,6 +415,23 @@ }; /* +** Compile with SQLITE_ISO8859=1 and call this function on startup. +** +** Usage: +** +** setlocale(LC_CTYPE, ""); +** sqliteSetUpperToLower(); +** +*/ +void sqliteSetUpperToLower(){ + /*extern int tolower (int c);*/ + int i; + for (i = 0; i <= 255; i++) { + UpperToLower[i] = (unsigned char)tolower(i); + } +} + +/* ** This function computes a hash on the name of a keyword. ** Case is not significant. */ ----------------------------------------------------------------- #e8e8bd 478 new active 2003 Oct anonymous Parser 2003 Nov drh 1 3 select into is lacking hi, thanks for sqlite. i accidentally tryed a "select into" statement and found it is not parsed by sqlite. best regards, alex #e8e8bd 472 new active 2003 Oct anonymous VDBE 2003 Nov 5 5 [Patch] Using localtime instead UTC time to 'now' --- os.c.orig Mon Oct 6 09:23:03 2003 +++ os.c Mon Oct 6 09:29:16 2003 @@ -1605,20 +1605,35 @@ */ int sqliteOsCurrentTime(double *prNow){ #if OS_UNIX - time_t t; + time_t t, lot; + struct tm ltm; + int ltmsec; time(&t); - *prNow = t/86400.0 + 2440587.5; + /* + * to use localtime, I used localtime - gmtime. + * localtime_r, gmtime_r are thread safe. + * by iosephATuriDATsarangDATnet + */ + localtime_r(&t,<m); + lot = mktime(<m); + gmtime_r(&t, <m); + ltmsec = lot - mktime(<m); + *prNow = (t + ltmsec)/86400.0 + 2440587.5; return 0; #endif #if OS_WIN - FILETIME ft; + FILETIME ft,ft2; /* FILETIME structure is a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (= JD 2305813.5). */ double now; GetSystemTimeAsFileTime( &ft ); - now = ((double)ft.dwHighDateTime) * 4294967296.0; - *prNow = (now + ft.dwLowDateTime)/864000000000.0 + 2305813.5; + /* to use localtime, I used this API. + * by iosephATuriDATsarangDATnet + */ + FileTimeToLocalFileTime(&ft, &ft2); + now = ((double)ft2.dwHighDateTime) * 4294967296.0; + *prNow = (now + ft2.dwLowDateTime)/864000000000.0 + 2305813.5; #endif return 0; -- EOF -- ----------------------- This patch is local patch. I patched both OS_UNIX and OS_WIN to be thread safe. #e8e8bd 491 new active 2003 Nov anonymous 2003 Nov 4 4 Want to use SET col = 'val' with INSERT statement It would be nice if the an additional INSERT syntax is supported because: 1) This makes migrating from MySQL to SQLite easier. 2) The body of UPDATE and INSERT statements are the same using this syntax which makes developing faster and easier. The exact syntax: INSERT [INTO] tbl_name SET col_name=(expression | DEFAULT), ... (From: http://www.mysql.com/doc/en/INSERT.html) _2004-Mar-14 22:29:54 by anonymous:_ {linebreak} I'd like to second this item. From my readings, this is a documented feature of SQL92, and from my own use of SQL in programming languages, it's far easier to use the SET syntax over the VALUES syntax for manipulation queries. ---- _2004-May-10 10:20:25 by anonymous:_ {linebreak} My vote on this one too! When this is implemented, we can finally switch with our entire product range to sqlite :). #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 #e8e8bd 486 new active 2003 Oct anonymous VDBE 2003 Oct 5 4 index on expressions (general solution to case-insensitive problem) Hi. Just wanted to throw a suggestion for this very useful database: It would be very useful to be able to have indexes that use expressions rather than column names (and have the query optimizer be able to use them when it sees those expressions) Oracle has this capability and it's surprisingly general-purpose and useful for a variety of problems, so I'd like to ask that this be considered for a sqlite wish-list (not that I want to try to make sqlite into oracle or use 'oracle has this' as a justification for any feature:-). For example, a frequent request or requirement is to search fields case-insensitively. However, most such requirements also require that the field be displayed in the original case. CREATE INDEX upper_ix ON employees (UPPER(last_name)); makes for an easy solution (though one might want a FOLD() function to correctly deal with internationalization, but that's another subject) Thanks. Mark. #e8e8bd 485 new active 2003 Oct anonymous BTree 2003 Oct 5 4 Comparing strings: user defined callback, strcoll? I think it would be useful to specify the "C" locale character set (like strcoll), at database level, so strings are compared based on that locale. Or at least some callback routine so one could perform those string comparisons. #e8e8bd 479 new active 2003 Oct anonymous Parser 2003 Oct drh 4 3 Sequences Adding sequences to sqlite would be very useful... allowing to do several things that are not possible today. But I'm not sure if default column values must be implemented to take full use of sequences. _2004-Mar-03 13:14:00 by anonymous:_ {linebreak} Any plans to add sequences? #e8e8bd 243 new active 2003 Feb anonymous Unknown 2003 Oct anonymous 3 4 Performance problem with MSVC precompiled DLL After a while the time for queries increase from e.g. 0.46 sec to 1.5 sec or even over 2 sec. The interesting thing is, that time for sqlite_free_table also increase. I recompiled the library with Borland C++ Builder 5 with the effect, that it runs 2 times faster (about 0.25 s for the same query) and produces constant times for queries. Another advantage of this library is : no need for msvcrt.dll. The only dependences are : kernel32.dll and user32.dll For details mail me at : sascha_7777@onlinehome.de and i will send you this version of library. My guess that this is entirely the fault of particularly poor malloc() that comes by default on Windows. Borland C++ Builder 5 is probably tossing in its own (better) malloc() implementation. --- Pablo 10/16/2003 I have confirmed this as well with 2.8.6. I was seeing the behavior reported here and after rebuilding the DLL with BCB 5.5 (which is free now) the memory usage is down and the query times are constant. With the downloaded DLL, I would see the following results (times in seconds): took 0.0400 for 1 rows and 2 columns took 0.0700 for 681 rows and 17 columns took 0.0000 for 8 rows and 2 columns took 0.6010 for 1 rows and 2 columns took 0.0700 for 681 rows and 17 columns took 0.0000 for 8 rows and 2 columns took 0.5210 for 1 rows and 2 columns took 0.0800 for 681 rows and 17 columns took 0.0000 for 8 rows and 2 columns It simply runs the same 3 select statements 3 times. The first time, it is fast because (presumably) the heap is clean. After rebuilding the DLL with BCB 5.5, the results are as follows: took 0.0100 for 1 rows and 2 columns took 0.0300 for 681 rows and 17 columns took 0.0100 for 8 rows and 2 columns took 0.0100 for 1 rows and 2 columns took 0.0300 for 681 rows and 17 columns took 0.0000 for 8 rows and 2 columns took 0.0100 for 1 rows and 2 columns took 0.0300 for 681 rows and 17 columns took 0.0000 for 8 rows and 2 columns #e8e8bd 465 new active 2003 Sep anonymous 2003 Sep 4 3 data types should be implemented as formated strings a real improvement of sqlite would be operating on numeric and date, time fields in a sortable string format, for example: * date as YYYYDDMM, * time as HHMMSSmmm, * integer as hexadecimal string without "0x", * float as WWWWWWWWDDDDDDDDDDDDDEEEEEEE (wholes, decimals, exponent) this way, there would be no need for the special integer keys and indexes, and code will be more feature oriented and less feature evading. hexadecimal values will compress decimals and improve sortability. all numbers should be 0-padded, prefixed by their sign, with null strings for null values. the developing effort required would be to: * make a library of basic mathematical functions for string values. * parse numeric, date, and time literals in queries without quotation marks. * match the right function for the specified type. * it would be very nice to support all the types of c variables. #e8e8bd 457 new active 2003 Sep anonymous 2003 Sep 1 1 Ported to NetWare I've ported SQLite to work on NetWare 5.1 and 6.x. The database file location must be an NSS volume. #e8e8bd 454 new active 2003 Sep anonymous 2003 Sep 4 4 schema column types with parenthesis to automatically invoke functions having a table such as: CREATE TABLE foo (amount NUMERIC(10,2)); and having a function (NUMERIC) that accepts three parameters, it would be nice if: INSERT INTO TABLE foo (10.50); would call: NUMERIC(10.50,10,2); and use its result as the insert. Since this could break compatability, I recommend the use of a PRAGMA that would enable this mode. I am aware that TRIGGERS make it possible to do this, however this would require application-level setup that is too specific for some of the applications I work with; A pragma is not an unrealistic setup for my application, but setting up special INSERT/UPDATE triggers is. As a temporary workaround, my application downloads the sqlite_master table, parses out the columns, and creates the triggers accordingly. This code is somewhat complex, and thus represents an annoyance. #e8e8bd 450 new active 2003 Sep anonymous Shell 2003 Sep 5 4 Make HTML Output XHTML compliant According to the website documentation, sqlite already does this but the latest version I downloaded doesn't. XHTML states that tags should all be lowercase ( as opposed to ). Instead of fixing the website, it would be easier to patch sqlite. There follows a patch to do this and also a patch to change the documentation on the site as it says XHTML compliance but lists non XHTML compliant tags. D'oh - just saw Attach link. Sorry ---- _2005-Sep-08 01:02:45 by anonymous:_ {linebreak} Still an issue in v3.2.5 #e8e8bd 448 new active 2003 Sep anonymous 2003 Sep drh 3 3 a general bug-fix and dos support without lfn there are 2 issues in the included diff: 1. fixed support for djgpp without long file names. djgpp gives brute force to dos in terms of 32bit and 4gb memory, but it still remains a mainly one process at a time os, where programs are being enhanced mainly through libraries. that's why sqlite makes such a big difference to dos users, allowing them to directly use databases. 2. a minor bug in the code if readline isn't on: #if !HAVE_READLINE{linebreak} # define macro1(arg){linebreak} # define macro2(arg){linebreak} #endif{linebreak} if (a){linebreak} macro1(1){linebreak} if (b){linebreak} macro2(2){linebreak} if (c){linebreak} return;{linebreak} will be preprocessed to contain:{linebreak} if (a){linebreak} if (b){linebreak} if (c){linebreak} return;{linebreak} which is equivalent to: if (a && b && c){linebreak} return;{linebreak} as i understand, you meant to write: if (a){linebreak} /*do nothing*/{linebreak} if (b){linebreak} /*do nothing*/{linebreak} if (c){linebreak} return;{linebreak} thus, i've changed the code to: #if !HAVE_READLINE{linebreak} # define macro1(arg) *{}* {linebreak} # define macro2(arg) *{}* {linebreak} #endif{linebreak} feel free to correct me if i'm wrong. best regards and thank you for sqlite, alex please also rename the only non short file names : sqliteInt.h, tclsqlite.c to some 8.3 compliant names. thanks, alex #e8e8bd 442 new active 2003 Sep anonymous 2003 Sep 5 2 [PATCH] sqlite: allow configure --enable-threadsafe attached is a patch against cvs head for incorporating the thread-safety aspect to the sqlite lib. http://cygwin.dev.wapme.net/sqlite/ Please try and comment. This is intended to be included to cvs head. #e8e8bd 428 new active 2003 Aug anonymous TclLib 2003 Aug drh 5 5 missing prepare/fetch functions to be compatible with other DBs To have a compatible behaviour to other database engines when fetching single rows from a database table, it would be nice to be able to have a single fetch function instead of the standard "db eval ..." ability using a callback code. I've found three functions in the file test1.c which comes with the source files and implemented them into the TCL interface (file tclsqlite.c proc DbObjCmd). oops deleted attach. please post it again. #e8e8bd 425 new active 2003 Aug anonymous VDBE 2003 Aug 5 3 Datetime enhancements: fractional seconds, addmonth This code adds support for fractional seconds in the datetime functions. From 0 to 6 digits are supported on creation of timestamp or time strings; up to 12 digits are read from them. YYYY-MM-DD HH:MM:SS.SSSSSSS HH:MM:SS.SSSSSSS The functions TIMESTAMP TIME and SECOND have been modified to accept an optional second argument to specify the number of fractional digits to be produced in the string result. The JD is rounded for the specified precision. A new function ADDMONTHS is also included. It takes a datetime and an integer, and increments the datetime by the number of months specified. The number can be negative, and can be greater than 12. To add years, do ADDMONTHS( , *12). e #e8e8bd 424 new active 2003 Aug anonymous Shell 2003 Aug 4 3 Provide access to internal function _all_whitespace() Can the function _all_whitespace() in shell.c be made available as part of the SQLite API, perhaps with the more appropriate name sqlite_empty(). This function could then be used along with sqlite_complete() when splitting files contain SQL scripts into individual statements that can be executed by SQLite. The problem occurs because sqlite_complete() returns true for an empty statment, but some applications (Borlands dbExpress for example) don't consider an empty statment to be valid SQL and throw an exception when they are executed. So the user must ensure that the statement accepted by sqlite_compltet() is not empty using the sqlite_empty() function before trying to execute it. The name _all_whitespace() is not really appropriate since it also accepts comments which are not "whitespace" characters. I am currently using a modified version of shell.c to build a custom DLL, but changing the SQLite source would be a lot cleaner. #e8e8bd 420 new active 2003 Jul anonymous VDBE 2003 Jul 3 3 Not abort transaction when callback is cancelled When a callback is cancelled (non-zero return value) in a transaction, the changes are undone, but the transaction is not rolled back. This presents a problem as my sqlite wrapper sometimes legitimately cancels a callback in the middle of a transaction and wants to commit the changes made before/after the callback cancel. The way I understand it, a callback is only invoked when a query is being executed (data retrieval) which doesn't impact a transaction anyway. If I am wrong in this assumption, then perhaps this will require more changes to accomplish. [This code works on my box with the limited number of tests I have accomplished. I'm not a linux developer and I don't have tcl on my system so I can't run the full test suite. If someone could do that with these proposed changes I would be very appreciative] My proposed changes are (quite simple):{linebreak} 1) create a new PRAGMA: PRAGMA callback_cancel_no_abort with a default of FALSE (to keep the current behaviour as default){linebreak} 2) if a callback is ABORTed and this pragma is true, then don't return SQLITE_ERROR from sqlite_vbde_exec, but rather SQLITE_DONE The code changes for these proposed changes are (to the official 2.8.5 release): In sqliteInt.h: INSERT this line at line 351: #define SQLITE_CallBackNoAbort 0x00000400 /* Don't abort on callback cancel */{linebreak} In pragma.c: INSERT this code at line 336:{linebreak} if( sqliteStrICmp(zLeft, "callback_cancel_no_abort")==0 ){{linebreak} _:if( getBoolean(zRight) ){{linebreak} _::db->flags |= SQLITE_CallBackNoAbort;{linebreak} _:}else{{linebreak} _::db->flags &= ~SQLITE_CallBackNoAbort;{linebreak} _:}{linebreak} }else{linebreak} In vbde.c: CHANGE line 5751: from{linebreak} if( rc ){ {linebreak}{linebreak} TO {linebreak}{linebreak} if( rc && ( !(db->flags & SQLITE_CallBackNoAbort) || rc != SQLITE_ABORT) ){{linebreak} I don't believe these changes will conflict with existing callback abort responses, however I am not completely familiar with all parts of sqlite, therefore I would like to hear the thoughts of DRH and the sqlite community. #e8e8bd 413 new active 2003 Jul anonymous 2003 Jul 5 1 SQL_CALC_FOUND_ROWS & FOUND_ROWS() not supported for speedup & code compatibility with mysql #e8e8bd 412 new active 2003 Jul anonymous Unknown 2003 Jul drh 1 1 sign function: quick hack for func.c (hope this helps) Here's a quick hack to create the sign function in /src/func.c /* ** Implementation of the sign() function */ static void signFunc(sqlite_func *context, int argc, const char **argv){ double r; char zBuf[3]; assert( argc==1 ); r = atof( argv[0] ); if ( r < 0 ) { zBuf[0]='-'; zBuf[1]='1'; zBuf[2]='\0'; sqlite_set_result_string(context, zBuf, -1); return; } if ( r == 0 ) { zBuf[0]='0'; zBuf[1]='\0'; sqlite_set_result_string(context, zBuf, -1); return; } if ( r > 0 ) { zBuf[0]='1'; zBuf[1]='\0'; sqlite_set_result_string(context, zBuf, -1); return; } } Also note the following change in sqliteRegister where sign is added. ** This function registered all of the above C functions as SQL ** functions. This should be the only routine in this file with ** external linkage. */ void sqliteRegisterBuiltinFunctions(sqlite *db){ static struct { char *zName; int nArg; int dataType; void (*xFunc)(sqlite_func*,int,const char**); } aFuncs[] = { { "min", -1, SQLITE_ARGS, minFunc }, { "min", 0, 0, 0 }, { "max", -1, SQLITE_ARGS, maxFunc }, { "max", 0, 0, 0 }, { "length", 1, SQLITE_NUMERIC, lengthFunc }, { "substr", 3, SQLITE_TEXT, substrFunc }, { "abs", 1, SQLITE_NUMERIC, absFunc }, { "sign", 1, SQLITE_NUMERIC, signFunc }, .... The above is a quick hack; but, it allows the users of sqlite to create pivot table as follows: CREATE TABLE exams ( pkey int(11) , name varchar(15), exam int, score int, PRIMARY KEY (pkey) ); insert into exams (name,exam,score) values ('Bob',1,75); insert into exams (name,exam,score) values ('Bob',2,77); insert into exams (name,exam,score) values ('Bob',3,78); insert into exams (name,exam,score) values ('Bob',4,80); insert into exams (name,exam,score) values ('Sue',1,90); insert into exams (name,exam,score) values ('Sue',2,97); insert into exams (name,exam,score) values ('Sue',3,98); insert into exams (name,exam,score) values ('Sue',4,99); sqlite> select name, sum(score*(1-abs(sign(exam-1)))) as exam1, sum(score*(1-abs(sign(exam-2)))) as exam2, sum(score*(1-abs(sign(exam-3)))) as exam3, sum(score*(1-abs(sign(exam-4)))) as exam4 from exams group by name; Sue|90|97|98|99 Bob|75|77|78|80 ref http://sqlchar.sourceforge.net/index.php Hope this helps, Mike Chirico http://vc2000-08.med.upenn.edu/chirico/ #e8e8bd 407 new active 2003 Jul anonymous 2003 Jul 4 4 sqlite_compile etc. and pragma empty_result_callbacks. Maybe if EMPTY_RESULT_CALLBACKS is set and you use sqlite_compile, sqlite_step etc. with a SELECT query that returns no rows the first invocation of sqlite_step() should return SQLITE_ROW with just columns data. #e8e8bd 184 new active 2002 Oct mike 2003 Jul drh 5 3 auto update mechanism for CVS is it possible to set up CVS in such a way that we are notified of activities on tickets, and check-ins? #e8e8bd 386 new active 2003 Jul anonymous 2003 Jul 5 1 compile on WinCE ? hi, since wince is supported by pythonCE and pysqlite is a wrapper it might be usefull to compile SQLITE on WinCE ( I use winCE 2.1 on ARM (iPAQ) ). .klemens #e8e8bd 381 new active 2003 Jul anonymous 2003 Jul 1 3 Support for DJGPP and DOS I would like to use SQLite in a pure DOS (MS-DOS, DR-DOS, FreeDOS) environment. Thankfully DJGPP provides many of the Unix APIs and tools. But the file name limit has been a prolem. DOS filenames must be 8.3, that is 8 characters plus a 3 character extension. I found 3 places where this is violated. 1) A file named "sqlite.h.in" is included with the source distribution. This unzips to "sqlite.h" since there may be only 1 extension. Since "sqlite.h" is supposed to be generated from the ".in", this causes problems. If I rename the file to "sqlite.hin" and modify the makefile main.mk, all works fine. 2) The temporary files created violate 8.3. The default pattern is "sqlite_XXXXXXXXXXXXXXXX". I modified the pattern to be "sqXXXXXX" so it is 8 characters long. Another possible (better?) solution would be to use mkstemp(), or even tmpnam(). 3) The journal files created violate 8.3 The default is to append "-journal" to the end of the database or temp file name. For database file names like "mydb.sld", this results in "mydb.sld-journal". Since the extension may only be 3 characters, it ends up writing to "mydb.sld". This is really bad since now the journal and DB are the same file. Instead of "-journal", I used ".jnl". This works if the database filename does not already have an extension. A better fix would be to change the extension, if there is one. _2005-Feb-09 03:32:44 by anonymous:_ {linebreak} Use ROM-DOS 7.1(from datalight) #e8e8bd 366 new active 2003 Jun anonymous Unknown 2003 Jun drh 4 3 Functions sqlite_(en/de)code_binary() not in sqlite.h The handy utility functions sqlite_encode_binary() and sqlite_decode_binary() from encode.c don't have any declarations in sqlite.h #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 #e8e8bd 356 new active 2003 Jun anonymous VDBE 2003 Jun 5 2 Returns real error codes Hello, I use succesfully sqlite with the php extension and some C application. While writes drivers for different backends I use, it is a real pain to know what really happen on error. The output message is well down and usefull for a human ;). Once you switch to manager error programmatically, it's quit impossible to know exactly the error code. As you change it to a string output, 'SQL error' or others "useless" message. Is there a chance to get an additionnal argument for the error code? I mean if you can populate the error code? hth pierre The problem is that the returned by reference error message is very descriptive but that the error codes are too coarsely grained to be useful for programmatic decision making. Eg: "select * from foo" when the table foo does not exist provides a nice textual description of the error, but the error code is a generic code that does not distinguish between a missing database, missing table, syntax error etc. ---- _2004-May-19 13:44:28 by anonymous:_ {linebreak} Yup this issue is very severe if you want to do any sort of error handling in your code. Actually a work around is parsing the error messages returned from the initial API call that failed and mapping them to your own error code table. Very klunky and bound to break often too. #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. #e8e8bd 326 new active 2003 May anonymous 2003 May 4 4 Request for additional callback for various functions There are several functions of sqlite which take callbacks as a parameter. Two of them are sqlite_create_function and sqlite_create_aggregate. I'm interfacing the sqlite with the ocaml language and it is required to do some work when the function is about to be "unregisttered", i.e. free some memory, tell the garbage collector to feel free with destroying some data and not to look for something anymore, etc. It is relatively easy to track this with auth/trace callbacks and busy handler, but for functions and aggregates I have to keep track of all registered functions ang aggregates, so the information is stored twice in a very similar structures - once inside the sqlite, once in my code. It would be easier if sqlite_create_function sqlite_create_aggregate will take one "cleanup" function as a parameter which (if not null) will be called at the time when registered function/aggregate is about to be replaced or disabled, or when sqlite structure is destroyed. This can be extended further with registering optional cleanup functions for auth/busy/trace callbacks (with calling them all when closing the database, when disabling, and when replacing), but there are workarounds for it; the real pain is only with functions and aggregates. Mikhail #e8e8bd 321 new active 2003 May anonymous 2003 May 4 3 Make SQLITE_ISO8859 and SQLITE_UTF8 overridable by compiler define For those of us who use the preprocessed sqlite_source.zip: it would be nice if we could override the character encoding with a compiler #define. That way I wouldn't have to manually edit sqlite.h. You could put an #ifndef in sqlite.h: #ifndef SQLITE_UTF8 # define SQLITE_ISO8859 1 #endif The other #defines I use (THREADSAFE, TEMP_FILE_PREFIX) etc. are overridable. Only the encoding is not. #e8e8bd 279 new active 2003 Apr anonymous 2003 May 3 3 WinCE port of CVS version as of 02 Apr 2003 I've re-ported http://cvs.hwaci.com:2080/sqlite/tktview?tn=169 sqlite to Windows CE. Currently only supports MIPS CPU's and x86 but ading a CPU is a 2 liner. Code and diffs attached. The new files (contain _wince in the filename) in my build system are in a sub dir called WinCE_src and are in the include path before the sqlite src dir. The assert_wince.h is actually called assert.h. Same for config.h. The diffs look bigger than they really are as I've re-indented the original Windows code to match the new #IFDEF's. I've include os.h, os.c and tokenize.c for clarity. If you just want the src to build with evc, here is a complete zip ball http://www.geocities.com/clach04/src/sqlite/wince_sqlite_CVS_2003_04_02_src.zip Chris It should be considered that Windows CE doesn't support the FILE_FLAG_DELETE_ON_CLOSE option. This may cause a lot of problems with SQLite temporary files. #e8e8bd 294 new active 2003 Apr anonymous Shell 2003 Apr a.rottmann 1 4 Diff for OpenBSD In order to build on OpenBSD, the following diff must be applied, as per Wild Karl-Heinz . This is on an OpenBSD 3.2 -current build. diff Makefile.in Makefile.in~{linebreak} 52c52{linebreak} < LIBREADLINE = @TARGET_READLINE_LIBS@ -lcurses{linebreak} ---{linebreak} > LIBREADLINE = @TARGET_READLINE_LIBS@{linebreak} Once that change is made to the Makefile.in, sqlite compiles and runs without problem. Otherwise, sqlite dies on '/usr/libexec/ld.so: Undefined symbol "_tgetent" called from sqlite:/usr/lib/libreadline.so.1.0 at 0x40083940'. _2004-Mar-09 12:48:12 by a.rottmann:_ {linebreak} See #303; the build system should have an overhaul. #e8e8bd 273 new active 2003 Mar drh CodeGen 2003 Mar drh 1 1 Make NULL_ALWAYS_DISTINCT a pragma rather than a compile-time option NULL_ALWAYS_DISTINCT is a #define currently set to 0. If set to 1, then SQLite will treat NULLs a distinct in SELECT DISTINCT statements and in UNION operators. This is what the SQL standard calls for. But no database (other than Ocelot) works this way, so SQLite leaves NULL_ALWAYS_DISTINCT set to 0 to enhance compatibility. See http://www.sqlite.org/nulls.html for additional information. This compile-time options should become a run-time option. It should still be off by default, but a pragma should be available to turn it on for those who want it. #e8e8bd 263 new active 2003 Mar anonymous CodeGen 2003 Mar 1 4 sqlite locking strategy can lead to deadlock G'day, I'm currently on a customer site, and as one does when a big upgrade is coming up the next day I was thinking about problems I've encountered over the last few weeks. I realise now that a problem I'd considered not very imporant really should be reported to you: The locking strategy of sqlite when doing operations that modify the database is to set a read lock while data is being analysed, then upgrade that read lock to a write lock when it comes time to modify the data. This strategy can cause deadlock on a UNIX-based system if two processes are executing sql code to modify data simultaneously. The following interleaving results in deadlock: Process 1: Lock file for read (ok) Process 2: Lock file for read (ok) Process 1: Upgrade lock to write lock (go into wait state for process 2 to release read lock) Process 2: Upgrade lock to write lock (deadlock waiting for process 1 to release read lock: fail) I have a slightly modified version of sqlite that uses blocking locks instead of your usual non-blocking lock with delay backoff strategy. I have seen this bug in operation in my build, and although I think it is more likely to occur in my version it is still possible in the vanilla sqlite version. Since most sql statements that can modify data probably do eventually end up modifying data, my suggested fix to the locking strategy is to decide based on the kind of overall statement whether to set a read lock or a write lock. In that scenario there is no interleaving which can cause a problem because every time a lock is obtained the relevant sqlite process is guaranteed to complete it's operation and then release the lock before trying to obtain another lock (unless the user application is trying to do something tricky, say run sqlite statements from two sqlite instances at the same time and in the same thread). For the BEGIN TRANSACTION command's lock I would also recommend a write lock be obtained, since most use of this statement would involve non-const operations during the transaction (otherwise the transaction would have no value!). Benjamin. The scenario described above is not a problem for the standard SQLite build since locks are non-blocking there. But I will consider how locking might be changed to better accomodate blocking locks. This is not a high priority, however. And since the problem described above does not occur for the standard SQLite build with non-blocking locks, I'm changing this ticket to an enhancement request. Further remarks by bug reporter: The locking strategy can have implications for the non-blocking sqlite version as well. Consider the following interleaving: Process 1: Obtain read lock (ok) Process 2: Obtain read lock (ok) Process 1: Upgrade read lock to write lock (failed, read lock is active) This means that even though Process 1 obtained a lock before process 2 the operation fails. If a write lock had been obtained instead of a read lock then it would have been Process 2 that had it's operation fail. This would unfairly balance the contention between a reader and a writer process in favour of the reader in very tightly contentious environments. On the other hand, the non-blocking mechanism is not one that deals with high contention between readers and writers very well anyway ;) That's why I ended up making the locks blocking in the first place for my version. Benjamin. #e8e8bd 252 new active 2003 Feb anonymous 2003 Feb 4 1 Add optional regex matching similar to what Postgres & Oracle have It would be really nice if one could use the ~ operator for regular expression matches. Postgres does this very nicely. This one feature is a limiting factor in moving more completely to sqlite over ascii flatfiles. Using perl to slice thru stuff with regex is the only thing I miss. If I had regex matching I would be in heaven. If you are looking for a regular expression engine, I might recommend http://www.pcre.org/ _2004-May-03 15:33:30 by anonymous:_ {linebreak} I second this request. I am in charge of the Gentoo Linux Tools project, and we are developing various searching tools which we wish to have a proper database backend for. Currently, we're looking at using SQLite for this, but any speed and size advantage SQLite has over flat files for our purposes is easily removed by its inability to do regex SELECTs. I have seen that Brad Campbell has written a patch for this, but it does not appear to have shown up by SQLite release 2.8.11, nor can I find any mention of it in the change log for the past 130 days. #e8e8bd 236 new active 2003 Jan anonymous 2003 Feb 3 3 Add RENAME TABLE - will be easier to work around missing ALTER TABLE Currently the recommended method to ALTER TABLE is: 1: Create a temporary table. 2: Copy all data from original table to temporary table. 3: Drop the original table. 4: Create a new table. 5: Copy all data from the temporary table to the new table. This means that most of the data is copied twice - with a big database this may take quite some time. I suggest adding a RENAME TABLE command, which will cut 50% of the overhead (copy the data only once instead of twice): 1: Rename the original table to a temporary name. 2: Create a new table. 3: Copy data from the temporary/original table to the new table. 4: Drop the temporary/original table. A further enhancement would be to make the RENAME TABLE an internal command (not exposed to the SQL interface), and use it internally in order to implement an ALTER TABLE feature. This may not be the most efficient ALTER TABLE out there, but at least will make SQLite much more compatible with standard SQL commands. It will certainly make life easier during development, when tables need to be altered all the time. This suggestion isn't as easy to implement as it sounds. Because VIEWs, TRIGGERs, FOREIGN KEYs,... can reference a table by name, renaming a table means modifying their schemas to do the renaming, and handling all kinds of cases. One trick might be to have the parser modify the SQL used to create all SQLite TABLES, VIEWS,... by inserting special C-style comments into the SQL following every table name, which would allow you to find and replace them. For example: CREATE TABLE t1 (...); becomes CREATE TABLE t1 /*SQLITE-TABLE-NAME:t1*/ (...); Another way would be to create the temp table with a new name, and then have a SWAP TABLE pragma which ends up swapping the pointers to the tables. Afterwards you have to re-check constraints on both tables. Then you can drop the old table. Jim Lyon --- I agree that just renaming a table can have adverse side-effects as you describe. However, I suggest RENAME TABLE only as an intermediate solution for the missing ALTER TABLE. If used only in the way I suggest, then RENAME TABLE will not have any side effects, as it will just be part of a procedure that results in no change to the original table name: 1: Rename original table to temp name. 2: Create new table with ORIGINAL name. 3: Move data from previous to new table. 4: Drop previous table. As you can see in step 2, the "new" table retains the original name, so any references from other tables should be retained. I think that the SWAP TABLE you suggest is very similar. Of course, references to specific fields may be affected (if the new table structure drops some fields), but this is anyway the case with ALTER TABLE. Eyal Zvi. #e8e8bd 244 new active 2003 Feb anonymous Unknown 2003 Feb drh 5 4 Combining CREATE TABLE with its populating from some flat file Michael Ovcharenko wrote: ------------- _:Hi, Richard, _:SQLite is a brilliant tool which can be used for numerous purposes. It, however, can be especially useful when it comes to producing some information on the base of the data derived from multiple heterogeneous data sources to suck them up onto the SQLite tables, use these for some sophisticated analysis, produce the necessary information, and then drop the unnecessary tables. The whole process can be reproduced at any time, e.g., by running a single script with SQLite. And SQLite is very good and fast here with its ".COPY table-name FROM file-name ..." command. Still, the table should have been created before this operation. _:Don't you think it could be useful for SQLite to combine both (create table and populate it with the data) in one command (which can be considered as an extension of ".COPY..." command - it does not comply with SQL92 anyway) provided that the first row of the source data file contains column names. It seems to be especially reasonable as SQLite is typeless and could potentially "suck up" a source file representing any number and types of columns. In many cases the source data can represent some spreadsheet exported, e.g., in CSV format, so these files usually contain column headers anyway. In an extreme case, table name and column names (when missing) could be generated in according to some convention. ---------------- *RH*: Good idea. I suggest syntax like this: CREATE TABLE AS COPY FROM . Or perhaps just CREATE TABLE FROM . Can you write this up as an enhancement ticket on CVSTrac so that I don't forget? http://cvs.hwaci.com:2080/sqlite/tktnew *MO*: Another opportunity is to extend .COPY command so that it will be able to implicitly create table in case it does not exist yet. Its parameters may establish certain pattern on default names for its columns if (in an extreme case) the first line of the source file does not contain any column names, e.g. something like DEFAULT COLNAME AS 'EMP_COL' (plus its sequential number like EMP_COL1, EMP_COL2, etc.). When created implicitly, the name of the table can be defaulted from "table-name" clause of .COPY command. I think, it could be useful to consider employing ON CONFLICT clause for this case (can it be extednded with something like CREATE TABLE?). Best regards, Michael Ovcharenko. #e8e8bd 68 new active 2002 Jun anonymous 2003 Jan 5 5 PRAGMA table_info(table-name) for queries It would be nice to have a possibility to get columns information, something like from PRAGMA table_info(table-name), also for queries. Maybe It could be a one function which enables to get both information and data. Selecting something from a view which is created with the statement "create view MyView as (select * from MyTable where ...)" is returning invalid datatypes instead of the MyTable datatypes. #e8e8bd 219 new active 2003 Jan anonymous 2003 Jan 5 5 MySQL style enums I would be really cool if SQLite supported enum like MySQL does: {link: http://www.mysql.com/doc/en/ENUM.html} #e8e8bd 216 new active 2003 Jan anonymous 2003 Jan 1 1 Add "DISTINCT ON()" clause to specify the fields to apply the unicity Like postgresql is a good enhancement to the distinct clause allow to specify wich fields to use when decide that a row match the distinct clause. Looking in a way to achieve the above functionality I've discovered that sqlite permit us to have columns in a "select" with "group by" that aren't present in the "group by" clause or in an agregate function. With that functionality ("Bug" a nice one) we can get the same result of "DISTINCT ON()". create table test(pk integer, name text); insert into test(1,'carl'); insert into test(2,'pal'); insert into test(3,'carl'); select distinct on(name) name, pk from test; -- impossible in most databases -- return the same result as select name, pk from test group by name; -- as well impossible in all databases I know except SQLITE Please leave the "group by" functionality ("bug") there allways. ---- _2004-Mar-10 01:45:44 by anonymous:_ {linebreak} That's a good workaround, except it's seemingly impossible to apply ordering first. E.g. I want to select the latest submission for each document in a table like the following: create table submissions ( s_id integer primary key, doc_id integer references documents, entry_time datetime); I do this in postgresql: select distinct on (doc_id) s_id from submissions order by doc_id, entry_time desc; By doing so, I get the s_id for the latest submission of each document. Using the group by workaround in sqlite will not apply the sort before choosing the row, so it does not choose latest submission, but rather some arbitrary one. ---- _2006-Feb-11 13:45:11 by anonymous:_ {linebreak} What is the current status? I have found http://article.gmane.org/gmane.comp.db.sqlite.general/703 and I would like to know if it is now a supported feature instead of a "consequence". I would also like to know if running SELECT a, min(b) FROM t1 GROUP BY a; 1, 2 2, 4 is the proper way to select all rows with distinct a's and the smallest b. Is this equivalent to saying: SELECT a as aa, b FROM t1 where b = (select min(b) from t1 where a = aa) GROUP BY a; It gives the same result with 3.2.8 but the 'explain' output looks longer for the second version... #e8e8bd 203 new active 2002 Dec anonymous Unknown 2002 Dec anonymous 2 1 Missing Win32 open flag if shared database In _Win32_, network and/or shared files should be opened with the _FILE_FLAG_WRITE_THROUGH_ flag in the _os.c_ file. There could be a flag when opening a database that informs the file is to be opened as shared or exclusive, and avoid locking if exclusive. I think this would improve speed considerably in "local" or exclusive databases. #e8e8bd 186 new active 2002 Nov anonymous Shell 2002 Nov 5 4 ./configure --enable-threads-safe ? It would be nice to have a ./configure --enable-threads-safe feature instead of using the template Makefile to compile libsqlite with this option turned on. #e8e8bd 175 new active 2002 Oct anonymous Pager 2002 Oct 5 3 journal file should be kept open In windows at least, the journal file used to manage transactions is being created and deleted for every transaction. this makes transactions slow in the following case: if a virus scanner is active, it will attempt to scan the file being created *every time* , and it will add 50-100 ms to each transaction. While starting an explicit transaction makes the problem smaller, in an environment where many atomic transactions with a single INSERT or UPDATE must be executed, this slows things down quite a lot. I understand that the peculiarity of the virus scanner actually creates the problem, but , is it a good design decision to keep creating and droping the file ? of course, there is the issue of multiple process accessing the same database, in which case multiple journals would exist, but this should/would be resolved by the locking mechanism anyway... #e8e8bd 139 new active 2002 Aug anonymous Pager 2002 Aug 3 3 Named transactions are misleading as documented As documented it appears as though you can assign meaningful names to transactions, but in practice the names are just a throwaway for SQL92 compatibility. Nested transactions could be implemented if names were used -- perhaps by using markers in the journal file. This method would work well and provide for unlimited nesting of transactions given that sqlite does not support concurrent transactions. Example nested transaction: begin transaction t1; -- creates journal: mydb-journal ... inserts, updates, deletes, etc... ... begin transaction t1a; -- adds a marker (e.g. 'begin t1a') to the journal ... inserts, updates, deletes, etc... ... commit transaction t1a; -- adds a marker (e.g. 'commit t1a') to the journal ... begin transaction t1b; -- adds a marker (e.g. 'begin t1b') to the journal ... ... begin transaction t1b1; -- adds a marker (e.g. 'begin t1b1') to the journal ... commit transaction t1b1; ... rollback transaction t1b; -- rolls back the database to journal marker 'begin t1b' rollback transaction t1; -- rolls back to the beginning of the journal, undoing -- the commit performed in transaction t1a. #e8e8bd 82 new active 2002 Jun anonymous 2002 Jun 1 4 Add IsCacheable, IsNullable to sqlite_create_function I did a first release of Ruby-SQLite interface and I've noticed that for some external functions it's not necessary to call then for each row wich will do a big difference in performance, probably adding another field to the sqlite_create_function let's call it "hints" that can tell SQLite that a function returns a constant value for the same entry or null if any of it's parameters are null, this is used in postgresql plpgsql. Let's say that the new prototype of sqlite_create_function could be: int sqlite_create_function( sqlite *db, const char *zName, int nArg, void (*xFunc)(sqlite_func*,int,const char**), void *pUserData, int hints ); The hints field can be a bit field that can hold new future hints like this. #define IS_CACHEABLE (1) #define IS_NULLABLE (1 << 2) #define IS_ANOTHER_HINT (1 << 3) int hints = IS_CACHEABLE | IS_NULLABLE; #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 2006 event active 2006 Sep anonymous 2006 Sep 1 1 Strange data in a table. When dumping a database file, this is what I found: CREATE TABLE TopSites ( XID INTEGER REFERENCES X(ID), YID INTEGER REFERENCES Y(ID), URLID INTEGER REFERENCES TopSitesURLs(ID)); INSERT INTO "TopSites" VALUES(-761955577, 5, 1322);{linebreak} INSERT INTO "TopSites" VALUES(-761955577, 5, 1120);{linebreak} INSERT INTO "TopSites" VALUES(-761955577, 5, 1323);{linebreak} INSERT INTO "TopSites" VALUES(-761955577, 5, 1324);{linebreak} ....................................................... INSERT INTO "TopSites" VALUES(-761955577, 5, 1323);{linebreak} INSERT INTO "TopSites" VALUES(-761955577, 5, 1324);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.bnimanningham.com', NULL);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.wellnesscareoncollins.com.au/Chiropractic-Articles.html', NULL);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.healthyrisepharmacy.com', NULL);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.alextechmelb.com/testimonials.html', NULL);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.ecca.com.au/melbourne-contactus.html', NULL);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.naturopathicwellness.com.au/additionaltherapies.htm', NULL);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.rrr.org.au/sponsors.php', NULL);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.caavic.asn.au/html/s02_article/show_article.asp?id=507&topic_id=-1&category_id=-1', NULL); INSERT INTO "TopSites" VALUES(NULL, 'http://www.cosmeticchoice.com.au/healing_nutrition.php?PHPSESSID=&PHPSESSID=d85928253b38f1bf88200022e7a93218', NULL);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.coca.com.au/vic.htm', NULL);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.embracechiropractic.com.au', NULL);{linebreak} INSERT INTO "TopSites" VALUES(NULL, 'http://www.cooperchiro.com', NULL);{linebreak} The database was created on: os: Mac OS X 10.4.6 jre: 1.5.0_06-64 sqlite: 3.3.4 The code for inserting into the database is: public static String GetTopSitesInsert(int aX, int aY, int aURLID){linebreak} {{linebreak} return "INSERT OR ROLLBACK INTO TopSites (XID, YID, URLID) VALUES {linebreak}(" + aX + ", " + aY + ", " + aURLID + ");";{linebreak} }{linebreak} I think that the last lines are from another table, or from another insert, as the java int could have never been a value like: http://www.bnimanningham.com When trying to delete some rows from this table, sqlite threw "malformed database" exception and the java virtual machine crashed. _2006-Sep-29 12:23:31 by anonymous:_ {linebreak} This is duplicate of #2005 ---- _2006-Sep-29 14:03:50 by drh:_ {linebreak} I'm thinking this and #2005 represent a bug in whatever Java bindings the reporter is using. #e8e8bd 2005 event active 2006 Sep anonymous 2006 Sep 1 1 Multiple rows with the same primary key, and null values in "not null" This is what I have found when dumping a database file: CREATE TABLE TopSitesURLs ( ID INTEGER PRIMARY KEY, URLText TEXT NOT NULL ); INSERT INTO "TopSitesURLs" VALUES(1, 'http://www.backinline.com.au');{linebreak} INSERT INTO "TopSitesURLs" VALUES(2, 'http://www.wellnesscareoncollins.com.au');{linebreak} INSERT INTO "TopSitesURLs" VALUES(3, 'http://www.oakleighdental.com.au/chirodontics.php');{linebreak} INSERT INTO "TopSitesURLs" VALUES(4, 'http://bacinactionchiropractic.com');{linebreak} INSERT INTO "TopSitesURLs" VALUES(5, 'http://melbourne.zpages.com.au/chiropractors');{linebreak} INSERT INTO "TopSitesURLs" VALUES(6, 'http://myname.chiropractic.com.au');{linebreak} INSERT INTO "TopSitesURLs" VALUES(7, 'http://www.melbournechiropractor.com');{linebreak} INSERT INTO "TopSitesURLs" VALUES(8, 'http://www.chiroweb.net/us/fl_melbourne.html');{linebreak} INSERT INTO "TopSitesURLs" VALUES(9, 'http://www.chiropractor.net.au/aridiskin.htm');{linebreak} INSERT INTO "TopSitesURLs" VALUES(10, 'http://www.melbournechiropractic.com.au');{linebreak} INSERT INTO "TopSitesURLs" VALUES(11, 'http://www.melbournechiropractor.com/index.php?page=privacy.php&pageID=-1');{linebreak} INSERT INTO "TopSitesURLs" VALUES(12, 'http://www.vitaminstoday.com.au/chiropractor/index.php?page=grid');{linebreak} INSERT INTO "TopSitesURLs" VALUES(13, 'http://www.goodechiro.com/index.asp');{linebreak} INSERT INTO "TopSitesURLs" VALUES(14, 'http://www.goodechiro.com/FirstVisit.asp');{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, 'http://www.usenature.com/chirodirectory.htm');{linebreak} INSERT INTO "TopSitesURLs" VALUES(16, 'http://www.melbournemeditationcentre.com.au/courses/teacher.htm');{linebreak} INSERT INTO "TopSitesURLs" VALUES(17, 'http://www.yogatree.com.au/Therapies.htm');{linebreak} ................................................................................... INSERT INTO "TopSitesURLs" VALUES(6259, 'http://www.yarravillehealth.com.au/osteopath-melbourne.html');{linebreak} INSERT INTO "TopSitesURLs" VALUES(6260, 'http://www.worldveganday.org.au/forum/viewtopic.php?p=4543&sid=465ea5c2e7452f6fd23470488e277781');{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(14, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} INSERT INTO "TopSitesURLs" VALUES(15, NULL);{linebreak} The database was created on: os: Mac OS X 10.4.6 jre: 1.5.0_06-64 sqlite: 3.3.4 The primary key 15 is duplicated, and the "not null" field is null. _2006-Sep-29 12:03:17 by anonymous:_ {linebreak} What tool (and parameters) did you use to dump the database? ---- _2006-Sep-29 13:13:28 by drh:_ {linebreak} When I run the SQL, I get lots of errors. And the resulting database does not contain any duplicate primary keys or NULLs in NOT NULL columns. Can you attach the database that contains duplicate primary keys and NULLs in NOT NULL columns to this ticket so that I can see it? jre==Java Runtime Engine? Are you using some kind of java binding to SQLite? If so, which one? Is SQLite in a separate DLL, or is your Java binding using a statically linked (and possibly modified and broken) version of SQLite? ---- _2006-Oct-03 15:15:54 by anonymous:_ {linebreak} I am using a java wrapper for sqlite: http://www.ch-werner.de/javasqlite/overview-summary.html I got the same problem again: INSERT INTO "TopSitesURLs" VALUES(13023, 'http://costaricaretirementvacationproperties.com/index.php?op=show_listing&ShowOption=Condo Resales&option=cat'); INSERT INTO "TopSitesURLs" VALUES(13024, 'http://www.hot-tropics.com/costa-rica-links.html'); INSERT INTO "TopSitesURLs" VALUES(13025, 'http://southpacificrealestateservices.com/index.php?PHPSESSID=6b7a257fad5cbd886f09526a2cd59ed8'); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(9, NULL); INSERT INTO "TopSitesURLs" VALUES(13041, 'http://www.livingabroadincostarica.com'); INSERT INTO "TopSitesURLs" VALUES(13042, 'http://limitededitionsre.com/blog.html'); INSERT INTO "TopSitesURLs" VALUES(13043, 'http://www.officecenter.nosaranet.com/property.html'); You can see the index 13025, then up to 13041 you can see only 9. How do I upload a database? ---- _2006-Oct-03 15:17:33 by anonymous:_ {linebreak} I have dumped the database with sqlite3.exe in command line: sqlite3.exe file.db .dump > file.sql ---- _2006-Oct-03 17:44:00 by anonymous:_ How do I upload a database? Use the _Attach_ link near the top right. Note that attachment size is currently limited to 100KB. ---- _2006-Oct-04 06:38:23 by anonymous:_ {linebreak} An integrity check on this database looks like this:{linebreak} C:\Documents and Settings\stefan matei\Desktop>sqlite3 project.db{linebreak} SQLite version 3.3.4{linebreak} Enter ".help" for instructions{linebreak} sqlite> PRAGMA integrity_check;{linebreak} *** in database main ***{linebreak} On tree page 59 cell 10: 2nd reference to page 1077{linebreak} On tree page 59 cell 10: Child page depth differs{linebreak} On tree page 59 cell 11: Child page depth differs{linebreak} On page 871 at right child: 2nd reference to page 1078{linebreak} sqlite> .quit{linebreak} When can this happen? Is there a fix for this (integrity fix or something)? I ask this because the database is perfectly readable. I assume that a tool can be done to check the tables and remove all the data that are not complying to the table definition. #e8e8bd 1939 event active 2006 Aug anonymous Unknown 2006 Aug 2 1 SQLite hangs with WHERE condition in an SELECT with joined table I have an database with 7 Tables. I wanted to make an SELECT in which i join the other Tables with an LEFT OUTER JOIN. That works but when i add an WHERE clause on an Joined Table my Application hangs. The sqlite3.exe hangs at the same query. My Database: CREATE TABLE MMBundesland (ID INTEGER, Bezeichnung varchar(255), Kennung INTEGER); CREATE TABLE MMCoords (ID INTEGER, Laenge DOUBLE, Breite DOUBLE, System varchar(50)); CREATE TABLE MMKFZ (ID INTEGER, Bezeichnung varchar(255)); CREATE TABLE MMKategorie (ID INTEGER,Bezeichnung varchar(255)); CREATE TABLE MMKreis (ID INTEGER, Bezeichnung varchar(255), Kennung INTEGER); CREATE TABLE MMOrte (ID INTEGER, CoordID INTEGER, Name varchar(255), KategorieID INTEGER, KreisID INTEGER, BundeslandID INTEGER, KfzID INTEGER, DefPLZID INTEGER, Kennung INTEGER); CREATE TABLE MMPLZ (ID INTEGER, PLZ varchar(10), CoordID INTEGER, OrtID INTEGER); The Query was: SELECT o.ID, o.Name,p.PLZ as DefPLZ,k.Bezeichnung,b.Bezeichnung as Bundesland, p.ID AS PLZID FROM MMOrte o LEFT OUTER JOIN MMPLZ p ON o.DefPLZID = p.ID OR o.ID = p.OrtID LEFT OUTER JOIN MMKategorie k ON o.KategorieID = k.ID LEFT OUTER JOIN MMBundesland b ON o.BundeslandID = b.ID LEFT OUTER JOIN MMPLZ p2 ON o.ID = p2.OrtID WHERE (p.PLZ like '72141%') ORDER BY o.Name Wenn I use: SELECT o.ID, o.Name,p.PLZ as DefPLZ,k.Bezeichnung,b.Bezeichnung as Bundesland, p.ID AS PLZID FROM *MMPLZ p* LEFT OUTER JOIN *MMOrte o* ON o.DefPLZID = p.ID OR o.ID = p.OrtID LEFT OUTER JOIN MMKategorie k ON o.KategorieID = k.ID LEFT OUTER JOIN MMBundesland b ON o.BundeslandID = b.ID LEFT OUTER JOIN MMPLZ p2 ON o.ID = p2.OrtID WHERE (p.PLZ like '72141%') ORDER BY o.Name The Query Works. It seems to work if i use in WHERE Clause one Column from the Table neer the FROM keyword. The main Problem is that SQLite hangs... I will generate my Query so that it works... _2006-Aug-25 13:32:21 by anonymous:_ {linebreak} "Hanging" is not very descriptive. Your query is working but now it either makes poor use of indexes or is doing full table scans, or possibly an unintended cross join. ---- _2007-Jan-10 16:31:41 by anonymous:_ {linebreak} I am seeing the same problem. It works on a limit of 1 and then fails on a limit of 2. ---- _2007-Jan-10 16:49:09 by drh:_ {linebreak} If you would like me to work on this, please send a reproducible test case. #e8e8bd 1935 event active 2006 Aug anonymous Parser 2006 Aug 4 4 GROUP BY and ORDER BY constant expressions valid? I'm not sure what other databases do in this situation, or whether a constant expression should be deemed a constant in ORDER BY and GROUP BY and result in an error. CREATE TABLE t1(a,b); INSERT INTO "t1" VALUES(1, 2); INSERT INTO "t1" VALUES(1, 3); INSERT INTO "t1" VALUES(2, 3); INSERT INTO "t1" VALUES(2, 4); INSERT INTO "t1" VALUES(2, 5); -- as expected select a, b from t1 group by 0; SQL error: GROUP BY column number 0 out of range - should be between 1 and 2 -- ??? select a, b from t1 group by 0/1; 2|5 select a, b from t1 group by 0.0; 2|5 select a, b from t1 order by 13/2; 1|2 1|3 2|3 2|4 2|5 select a, b from t1 group by 3/9 order by 1/9, 7.0, 0.4-3; 2|5 _2006-Aug-26 07:26:06 by anonymous:_ {linebreak} The integer is the column number. #e8e8bd 1871 event active 2006 Jun anonymous 2006 Jun 2 3 VACUUM should not change 3.x file format VACUUM should not "upgrade" the file format as it violates the principle of least astonishment. VACUUM upgrading the file format prevents users working with older versions of SQLite 3.x from sharing a common database file with users of more recent versions of the library. At the very least, if a version of SQLite can not produce the same version of the database file after VACUUM, it should do nothing, or perhaps return a warning. _2006-Jun-27 13:25:30 by drh:_ {linebreak} What if a user wants to upgrade the file format so that they can take advantage of descending indices, for example? How should they accomplish that? Should they be forced to dump and restore the database? ---- _2006-Jun-27 14:10:16 by anonymous:_ {linebreak} Manually dumping the old database and restoring it with a more recent version of SQLite is reasonable given the incompatible nature of the change. ---- _2006-Jun-27 15:30:32 by anonymous:_ {linebreak} I agree. Can't SQLite do the equivalent of pragma legacy_file_format=1 on the new database created by the VACUUM command if the current file is in the old format? The dump and restore operation is usable for updating the file format, but does require two installed versions of SQLite, one old and one new. Couldn't you add a new command or pragma that would do the format upgrade. Perhaps an optional upgrade argument to the VACUUM command that defualts to off could be used. If it is off the format of the new database is unchanged, if it is on, the format is upgraded. The VACUUM command doesn't seem like the obvious place tto look for a format upgrade option though. A new UPGRADE command would be more obvious, even if it is actually implemented by the same routines that do the VACUUM. It may be better to use something a little lower profile than a new command, perhaps a "PRAGMA upgrade_file_format" would be better. It would also allow future extension to provide a format version number that the database is to be upgraded to (ie PRAGMA upgrade_file_format=4). ---- _2006-Jun-27 17:55:23 by anonymous:_ {linebreak} it's a simple issue. since SQLite know the file format of database that is opened, on VACUUM it should create a file with the same version (such like a internal _sqlite3_open_ex() call that receive the file format that should be created. since SQLite can read/write those formats, there's no reason for doing a 'file format' upgrade. #e8e8bd 1547 event active 2005 Nov anonymous Unknown 2005 Dec 1 1 slow insert into indexed table and slow index create on huge table i have CREATE TABLE test (f1 VARCHAR(40), f2 INTEGER, f3 DOUBLE) with CREATE INDEX test_idx ON test(f1). i try to load 270M(270,000,000) records into this table within transaction. PRAGMA page_size = 8192 PRAGMA cache_size = 40000 PRAGMA syncronouse = OFF i use Dell server : 2 x Intel XEON 2.8 Ghz with Multi-threading. 4 GB memory 2 x 72Gb HD UltraScsi320 for now it's run 7.5 hours and loaded just 30M records _2005-Nov-30 19:03:42 by anonymous:_ {linebreak} What does your INSERT statement look like? Also, its more efficient to create the index for the table AFTER the insert has completed. ---- _2005-Nov-30 21:50:38 by anonymous:_ {linebreak} My Insert statement: INSERT INTO test VALUES (?, ?, ?) I already checked option to create index after insert. Insert took 55 min. Create index was stopped after 24 hours ---- _2005-Nov-30 22:52:32 by anonymous:_ Do you make inserts inside transaction ? If not, begin transaction before all inserts, commit it and then create index ---- _2005-Nov-30 22:57:07 by anonymous:_ {linebreak} it's already done. single begin transaction before inserts, single commit after ---- _2005-Dec-01 00:28:06 by anonymous:_ {linebreak} 250 mln inserts in 55 min (your last statement) is about 76000 inserts/sec. That's a bit slow (for SQLite). My trivial system P-IV 3 GHz HT, 1 GB RAM, 100 GB Maxtor ATA 100 does that with a rate no lower than 105000 inserts/sec (non-indexed db, about 300 mln records, same schema, prepared statements). Anyway, if you're not satisfied with performance, do that with MS SQL Server - you'll easily get 1000 inserts/sec or even lower with 100% CPU usage. ---- _2005-Dec-01 00:36:37 by anonymous:_ {linebreak} Let me guess: you are running Windows with Symantech Antivirus active and Indexing Service turned on, right? ---- _2005-Dec-01 00:43:16 by drh:_ {linebreak} When creating the index, entries have to be inserted at random places throughout the (roughly) 10GiB disk file. Since you probably do not have 10GiB or RAM for caching, you are constanting having to read and write disk pages. In other words, you are thrashing. To make it go faster, you need to establish some kind of locality of reference so pages get reused more often. Reused pages are in cache and work *much* faster. After you have the data loaded but unindexed, perhaps try something like this: ATTACH 'new.db' AS n; CREATE TABLE n.test(f1 text, f2 integer, f3 double); CREATE TEMP TABLE prefix(x text) AS SELECT DISTINCT substr(f1,1,3) FROM test ORDER BY 1; INSERT INTO n.test SELECT f1, f2, f3 FROM prefix, test WHERE x=substr(f1,1,3); CREATE INDEX n.test_idx ON test(f1); Suppose there are N entries in the prefix table. The INSERT statement makes N passes over the original test table inserting all entries with that prefix into the new test table. In the end, the entries in the new test table are approximately sorted. So then when you go to create the index, you have more locality of reference and the index creation should go faster. In theory at least. Your mileage may very. You might want to adjust the size of your prefix depending on what your data looks like and how it is distributed. ---- _2005-Dec-01 07:16:27 by anonymous:_ {linebreak} thank you for inputs. application runing on RHEL4 without antivirus, and any unused daemons. The data is already sorted. "low" performance at insert to unindexed table caused by data generation process. but i don't understand the performance of index creation. most of time one CPU at 100% usage 3 other CPUs at 98% waits. #e8e8bd 1525 event active 2005 Nov anonymous Unknown 2005 Nov anonymous 1 2 error creating database with the 3.2.7 sqlite.dll i could'nt create database when the directories is with 'ç' character like 'c:\françois\' but with the 3.0.2 sqlite.dll it's work _2005-Nov-18 01:44:28 by anonymous:_ {linebreak} I'm pretty sure sqlite3_open() is expecting any non-ASCII characters in the filename to be encoded as UTF-8 rather than ISO8859-1; i.e. a c-cedilla needs to be encoded as two bytes, not one. #e8e8bd 1480 event active 2005 Oct anonymous Unknown 2005 Oct 1 3 Wrong SQL statement crashes PHP+Apache Configuration:
- Windows NT VMSRV01 5.2 build 3790
- Apache/2.0.53 (Win32)
- PHP 5.0.5 Windows binary
- SQLite 2.8.14 (included with PHP 5.0.5)
Windows application event log error:
Faulting application Apache.exe, version 2.0.53.0, faulting module php5ts.dll, version 5.0.5.5, fault address 0x000cd0d5.
Description:
Firing a SQL statement like
SELECT A.f1, A.f2, B.f3, B.f4 FROM t1 AS INNER JOIN t2 ON A.f1 = B.f1 ORDER BY A.f1 ASC, B.f4 DESC;where the alias for t1 is missing, crashes both PHP and Apache.PHP code to reproduce the problem:
<?php ob_end_flush(); $goodSql = ''; $goodSql .= 'SELECT'; $goodSql .= ' A.categoryCode,'; $goodSql .= ' A.categoryStatus,'; $goodSql .= ' A.categoryName,'; $goodSql .= ' A.categoryDescription,'; $goodSql .= ' B.documentCode,'; $goodSql .= ' B.documentStatus,'; $goodSql .= ' B.documentFile,'; $goodSql .= ' B.documentCRC32,'; $goodSql .= ' B.documentSize,'; $goodSql .= ' B.documentTitle,'; $goodSql .= ' B.documentComments,'; $goodSql .= ' B.documentMimeType,'; $goodSql .= ' B.documentAddedOn,'; $goodSql .= ' B.documentAddedBy'; $goodSql .= ' FROM categories AS A INNER JOIN documents AS B'; $goodSql .= ' ON A.categoryCode = B.categoryCode'; $goodSql .= ' WHERE A.categoryStatus = "active" AND B.documentStatus = "active"'; $goodSql .= ' ORDER BY A.categoryCode, B.documentAddedOn DESC;'; $wrongSql = ''; $wrongSql .= 'SELECT'; $wrongSql .= ' A.categoryCode,'; $wrongSql .= ' A.categoryStatus,'; $wrongSql .= ' A.categoryName,'; $wrongSql .= ' A.categoryDescription,'; $wrongSql .= ' B.documentCode,'; $wrongSql .= ' B.documentStatus,'; $wrongSql .= ' B.documentFile,'; $wrongSql .= ' B.documentCRC32,'; $wrongSql .= ' B.documentSize,'; $wrongSql .= ' B.documentTitle,'; $wrongSql .= ' B.documentComments,'; $wrongSql .= ' B.documentMimeType,'; $wrongSql .= ' B.documentAddedOn,'; $wrongSql .= ' B.documentAddedBy'; $wrongSql .= ' FROM categories AS INNER JOIN documents AS B'; $wrongSql .= ' ON A.categoryCode = B.categoryCode'; $wrongSql .= ' WHERE A.categoryStatus = "active" AND B.documentStatus = "active"'; $wrongSql .= ' ORDER BY A.categoryCode, B.documentAddedOn DESC;'; echo '<h2>Running good SQL</h2>'; flush(); test('./db/sagnedb.dat', $goodSql); flush(); echo '<h2>Running wrong SQL in 20 seconds...</h2>'; flush(); for ($i = 20; $i > 0; $i--) { echo "<small>$i...</small>"; echo str_repeat(' ', 4096); flush(); sleep(1); } test('./db/sagnedb.dat', $wrongSql); echo '<h1>It will never print this!!!!!!!!!!!!!!!!!!!!!<h2>'; flush(); function test($dbFilename, $sql) { // ini_set('sqlite.assoc_case', 2); if ($dbConn = sqlite_open($dbFilename, 0666, $dbError)) { $sql = sqlite_escape_string($sql); if ($queryResult = @sqlite_query($dbConn, $sql)) { $n = sqlite_num_fields($queryResult); echo 'cols: ' . $n . '<br />'; echo 'rows: ' . sqlite_num_rows($queryResult) . '<br />'; for ($i = 0; $i < $n; $i++) { echo 'field[' . $i . ']: ' . sqlite_field_name($queryResult, $i) . '<br />'; } echo '<br />data:<pre>'; $data = sqlite_fetch_all($queryResult, SQLITE_ASSOC); print_r($data); echo '</pre>'; } sqlite_close($dbConn); } } ?>Sergio
_2005-Oct-11 22:19:24 by anonymous:_ {linebreak} I had no idea to whom I'd have assigned this. Sorry about that. Sergio ---- _2005-Oct-12 02:58:37 by anonymous:_ {linebreak} This is the wrong place to file this bug. Report this to the PHP folks; Unless you can reproduce the issue with sqlite alone. ---- _2005-Oct-12 15:05:42 by anonymous:_ {linebreak} The alias for t1 is not missing, it's "INNER". This is a perfectly well formed SQL statement. The problem is that SQLite is not reporting the erroneous use of the reserved word INNER as an identifier when it should. It does not do this for all reserved words as shown below. sqlite> select * from t as join; SQL error: near "join": syntax error sqlite> select * from t as "join"; sqlite> select * from t as inner; sqlite> However, I suspect that the error reported for the first select is due to the right hand table being omitted in the join, not because it recognizes that the alias (what the standard calls a correlation name) is a reserved word. The second select does not produce an error when it should, because the alias is still a reserved word even though it is delimited by double quotes. The third select does not produce an error either. ---- _2005-Oct-12 15:09:36 by anonymous:_ {linebreak} Of course this lack of error reporting is not what is causing Apache and/or PHP to crash. The erroneous queries that are accepted by SQLite execute correctly. sqlite> select * from t as inner; 1|2 2|4 sqlite> select * from t as "join"; 1|2 2|4 #e8e8bd 1469 event active 2005 Oct anonymous Parser 2005 Oct 1 1 Having problems with subselects that work on mySQL I'm converting from mySQL. However I've run across several sub-selects that work on mySQL but not SQLite. Apache gives me a error. If I can't get them fixed, I will be forced back to mySQL. Not sure what I'm doing wrong. Basically trying to find the last variety record against a Wine Process Order (wpo). Program is in PHP5 script as follows: ------------------------------------------------- $sql = " SELECT * FROM wpo W, wpo_dtl as D, wpo_varietal V, storage S, variety VM WHERE S.stop_trace <> 'Y' AND D.storage_id = S.storage_id AND D.from_to = 'T' AND D.wpo_num = W.wpo_num AND D.leg_num = W.leg_num AND V.storage_id = S.storage_id AND V.from_to = 'T' AND V.wpo_num = D.wpo_num AND V.leg_num = D.leg_num AND VM.variety_num = V.variety_num AND VM.color = 'R' AND W.wpo_datetime = (SELECT MAX(W1.wpo_datetime) FROM wpo as W1,wpo_varietal as V1 WHERE V1.storage_id = S.storage_id AND V1.from_to = 'T' AND W1.wpo_num = V1.wpo_num AND W1.leg_num = V1.leg_num) ORDER BY S.storage_id"; $query = sqlite_query($link_id, $sql); $result = sqlite_fetch_all($query, SQLITE_ASSOC); foreach ($result as $qd) { ............... ------------------------------------------ _2005-Oct-05 20:08:11 by anonymous:_ {linebreak} You've said it "gives you an error" and "doesn't work" but you don't tell anybody *what* error message it gives you or *how* it fails. Nobody here can help you solve your problem unless you provide that information. #e8e8bd 1426 event active 2005 Sep anonymous Unknown 2005 Sep drh 2 2 Problem with DETACH in 2.8.16 sqlite1.txt: create table documents (a); create index i on documents(a); sqlite2.txt: attach 'x1.dbx' as d1; attach 'x2.dbx' as d2; detach d1; Commands: sqlite x1.dbx.output "/participants.csv" sqlite> .dump part_info sqlite> .quit sqlite3(1593,0xa000ef98) malloc: *** Deallocation of a pointer not malloced: 0x726f7365; This could be a double free(), or free() called with the middle of an allocated block; Try setting environment variable MallocHelp to see tools to help debug Segmentation fault #e8e8bd 1300 event active 2005 Jun anonymous Unknown 2005 Jun 2 2 Apache.exe has encountered a problem and needs to close. I converted a MySql DB to SWLite. They are running on the same system and have identical data. Works on MySQL and not SQLite. SQLite gives Apache problem above. It's a very simple SQL that finds the last WPO (Wine Process Order) for each storage tank. Here is the SQL -------------------------- SELECT s.storage_id, d.from_to, d.leg_num, d.wpo_num, w.wpo_datetime, s.descr FROM wpo_dtl AS d, wpo AS w, storage AS s WHERE d.wpo_num = w.wpo_num AND d.leg_num = w.leg_num AND s.storage_id = d.storage_id AND w.wpo_datetime = ( SELECT max(wa.wpo_datetime) FROM wpo as wa, wpo_dtl as da WHERE da.wpo_num = wa.wpo_num AND da.leg_num = wa.leg_num AND da.storage_id = s.storage_id) -------------------------------------- WPO has; wpo_num int,leg_num int , wpo_datetime datetime ... WPO_DTL has; wpo_num int,leg_num int, storage_id char ... Storage has; storage_id char ... ---------------------------- Thank you for your time.. Have a great day. Dan _2005-Jun-23 19:31:49 by drh:_ {linebreak} Please include the database schema so that we can have at least an outside chance of reproducing the problem. Please also note that version 3.2.2 is current and version 2.8.16 is current in the legacy version 2 series. Your bug has likely already been fixed. #e8e8bd 1295 event active 2005 Jun anonymous Unknown 2005 Jun 1 1 SQL query involving subqueries causes reproduceable segfault in 2.8.14 The query is SELECT COUNT(g.gid) FROM kestassd_games g WHERE ( ( SELECT COUNT(m1.gid) FROM kestassd_memberships m1 WHERE m1.gid=g.gid ) = ( SELECT COUNT(m2.gid) FROM kestassd_memberships m2 WHERE m2.gid=g.gid AND m2.finalized=1 ) OR (".time()." > g.nextproc) ) AND NOT (g.mode = 0) When executed Apache 2 logs a segfault, and you disconnect with no error message. The error which Apache reports is "[Tue Jun 21 15:39:05 2005] [notice] child pid 1107 exit signal Segmentation fault (11)" Here's a zip/tar.gz of the database file which causes the segfault to occur if you run the query, along with a PHP script which will connect and execute the query: http://www.kuliukas.com/segfault.zip or http://www.kuliukas.com/segfault.tar.gz Thanks for the help, please e-mail me if you need more info. _2005-Jun-21 09:28:35 by drh:_ {linebreak} I will look at this. But because there is no easily reproducible script and the bug is against an older version of SQLite (a version to which many bug fixes have already been published), this problem will be investigated at a lower priority. I've attached a copy of the segfault.tar.gz file so that if the files on the http://www.kuliukas.com/ website go away we will still have a copy available. ---- _2005-Jun-21 14:27:27 by anonymous:_ {linebreak} Bear in mind that this is the version used in PHP 5 currently, it's quite a commonly used version I imagine, but I see your point. #e8e8bd 1128 event active 2005 Feb anonymous Shell 2005 Feb 2 3 sqlite3-3.1.2.bin segfaults Linux 2.4.26 (Knoppix 3.4 on hard drive) download: http://sqlite.org/sqlite3-3.1.2.bin.gz attached: strace log In the strace log I notice that the segfault happens just after /etc/nsswitch.conf has been read, if that's of any consequence. #e8e8bd 919 event active 2004 Sep anonymous Unknown 2004 Sep 1 1 'make test' RESETS macOSX at ioerr 2.73.1 make test.. as ioerr 2.73.1 prints, the whole system freezes and MacOSX tells me to restart _2004-Sep-30 06:37:01 by anonymous:_ {linebreak} I run "make test" often on a PowerBook G4 running Mac OS X 10.3.5. I've never seen this behavior. It might be helpful for you to include more information about the circumstances of the crash. --SCG #e8e8bd 727 event active 2004 May anonymous 2004 May 4 3 "wrong" affected row value returned by REPLACE When updating an existing row using the REPLACE syntax sqlite returns 1. This makes it impossible to know of an existing row was affected by the REPLACE call (1: no existing call was affected; 2: an existing row was affected). MySQL (which I assume was used as the example of the REPLACE syntax) returns a more hopeful 2 in this case. This may be due to their implementation which does an INSERT and a conditional DELETE if a contraint violation was detected due to the INSERT. #e8e8bd 720 event active 2004 May anonymous 2004 May 1 1 Write problem on MS Windows 98 I've installed Python 2.3 and PDO on my Win98 machine and use SQLite as my DB. When script is running data is writing into tables, but after it quit there is no data in tables. The same script I run on Linux and Win98, but on Win98 it does not "leave" data in tables. On Linux all is right. (sorry for my English :) #e8e8bd 697 event active 2004 Apr anonymous Unknown 2004 May 3 4 Questionable locking _Situation:_ SELECT some data into a statement handle. Loop through that statement handle using fetchrow_hashref(). For each row, do something, and then UPDATE the database with the return code. My UPDATES were failing with little to no information coming back from the DBI layer. (Even w/ trace on.) _Solution:_ As it turns out, the DB was locked from the first SELECT, and the UPDATES couldn't be written. So, I used fetchall_hashref() instead. That way, I took all the info in, undefined the statement handle, and the UPDATES work fine. This may be as-intended, I can understand the appeal of simple locking for the niche SQLite fills. But, 1) the FAQ item 7, sentence one leads me to believe that SELECTS don't lock, and 2) common sense tells me that SELECTS are read-only and shouldn't lock. I also wrote this up at perlmonks.org as the problem was found. http://www.perlmonks.org/index.pl?node_id=345931 Thanks for SQLite -- it's one of my favorite open source packages. Do one thing and do it well. Thank you. _2004-May-03 19:26:07 by anonymous:_ {linebreak} Well, it seems that SqlLite only allows for one query to be completely executed at a time. This includes SELECT. To test this, create a database *test* as follows, create table test_table(a); INSERT INTO test_table VALUES('test'); Then run the following program. Executing another query does not work, even using a seperate VM. Right now, SELECT blocks, which makes things more difficult... :( Even worse, the DELETE does not even return an error - just fails silently. #include#e8e8bd 592 event active 2004 Feb anonymous Unknown 2004 Feb 1 1 VIEW produces strange result set VIEW produces strange result set, which is different from the select-statment that created the VIEW does. I'll use a simple database to show the problem. My current Sqlite version is : 2.8.11 running on WinXP definition: a simple database to manage purchase and sale of a book --------------- CREATE TABLE buy(book,num); CREATE TABLE sell(book,num); INSERT INTO buy values(1,10); INSERT INTO sell values(1,5); INSERT INTO sell values(1,5); so now I have two tables like these: table buy: sqlite> select * from buy; book num ---------- ---------- 1 10 table sell: sqlite> select * from sell; book num ---------- ---------- 1 5 1 5 now create a view to see how many book 1 are sold: CREATE VIEW v_sell AS SELECT book,sum(num) AS num FROM sell GROUP BY book; view v_sell: sqlite> select * from v_sell; book num ---------- ---------- 1 10 then create a view to see how many book 1 are in stock: CREATE VIEW v_stock AS SELECT buy.book AS book,buy.num - v_sell.num AS stock FROM buy,v_sell WHERE buy.book=v_sell.book; but this produces a strange table: sqlite> select * from v_stock; book stock ---------- ---------- 1 0 1 0 there should be only one row in VIEW v_stock, but it gives out two. when run the query SELECT buy.book AS book,buy.num - v_sell.num AS stock FROM buy,v_sell WHERE buy.book=v_sell.book; in CREATE VIEW v_stock alone, it works just fine, and produces table as this: sqlite> SELECT buy.book AS book,buy.num - v_sell.num AS stock ...> FROM buy,v_sell ...> WHERE buy.book=v_sell.book; book stock ---------- ---------- 1 0 I wonder why VIEW v_stock produces a table which is not exactly the same as the select- statement created it does. Need some help ... :( #e8e8bd 540 event active 2003 Dec drh 2003 Dec 1 5 Database corruption observed The CVSTrac database for SQLite become corrupt sometime between 2003-12-23 and 2003-12-31. The corruption is visible using PRAGMA integrity_check; Note that the EXT3 filesystem on which the database file resided was corrupted by a power outage during this same time interval. The EXT3 errors were (allegedly) fixed by fsck. The EXT3 errors may have been (or was likely) the origin of the database corruption. The database errors were fixed using sqlite sqlite.db .dump | sqlite sqlite.db.new mv sqlite.db.new sqlite.db The old corrupt database has been saved for analysis. Unless additional corruption reports come in, we will assume this was an EXT3 problem, not an SQLite problem. But we want to have a record of the problem just in case... #e8e8bd 180 event active 2002 Oct anonymous 2003 Dec anonymous 1 1 on WIN32 - multithreaded process causes crash on assert(mutext) I'm testing how the sqlite is handling load with multiple threads and processes writing to the same table in the same db. the attached program instantiates 3 threads that are sequentially updating a table. each thread has its own db connection (sqlite * psqlite). I tried running 2 instances of this program simultaneously and the following happens: many times i get "Disk I/O Error" - but then i close an re-open the db and it works (is that the way to handle this problem?) some times i get the error " Error db no such table: ac" (that's a strange one - i also re-open the db to recover) but after a while the program constantly crashes on the following line: line 889 in os.c ( assert( !inMutex );) I'm currently testing it under windows 2000 - but this program should also run on linux. i'm attaching the code and a database (AWDB) on which i tested it. I'm also experiencing this in linux doing multithreaded load tests with 2 threads updateing and 3 threads selecting. Nevermind! This was due to THREADSAFE not being defined at build time. It might be nice to have this defined by default. #e8e8bd 409 event active 2003 Jul anonymous Unknown 2003 Dec anonymous 3 1 sqlite_open will cause segfault when using Rational PurifyPlus/Linux Note: I'm not certain if this is a sqlite or purifyplus problem While trying to troubleshoot some minor memory leaks in my application, I noticed that when using PurifyPlus the appliation will segfault when calling sqlite_open. I encountered the same problem when profiling src/threadtest.c. 1 - thread 11 - (thread) Run @ Sat Jul 19 23:58:54 2003 SIG (Signal 11 Handled: Segmentation fault) It occurs near after: worker_bee [/scratch/hsiab_c/hsiabd/t/sql/sqlite/src/threadtest.c] line 207 main [/scratch/hsiab_c/hsiabd/t/sql/sqlite/src/threadtest.c] line 265 Program exit code: 11 No Memory Leak detected The same problem happens when testing with an incredibly simple program: #include#include int main() { sqlite *d1, *d2; const char *tail1, *tail2; const char **col_data1, **col_names1; const char **col_data2, **col_names2; sqlite_vm *v1, *v2; int i, j; int pN; char del_msg[255]; char *err; d1 = sqlite_open( "test", 0, &err ); if( err != 0 ) printf( "Open error: %s\n", err ); d2 = sqlite_open( "test", 0, &err ); if( err != 0 ) printf( "Open error: %s\n", err ); /* Select one row */ printf( " allocated: %d %d\n", (int)d1, (int)d2 ); i = sqlite_compile( d1, "select * from test_table", &tail1, &v1, &err ); sqlite_step( v1, &pN, &col_data1, &col_names1 ); if( err != 0 ) printf( "Select error: %s\n", err ); printf( "nCols: %d\n", pN ); sprintf( del_msg, "delete from test_table where a=\"%s\"", col_data1[0]); printf( "%s\n", del_msg ); /* // UNCOMMENT FOR THIS TO WORK * * sqlite_finalize( v1, &err ); * if( err != 0 ) printf( "Finalize error: %s\n", err ); * */ /* Delete that row */ j = sqlite_compile( d2, del_msg, &tail2, &v2, &err ); if( err != 0 ) printf( "Delete error: %s\n", err ); sqlite_step( v2, &pN, &col_data2, &col_names2 ); sqlite_finalize( v2, &err ); if( err != 0 ) printf( "Finalize error: %s\n", err ); /* COMMENT FOR THIS TO WORK */ sqlite_finalize( v1, &err ); if( err != 0 ) printf( "Finalize error: %s\n", err ); /****************************/ sqlite_close( d1 ); sqlite_close( d2 ); return 0; } #include #include #ifdef MTRACE #include #endif int main() { sqlite *db; char *azErr; #ifdef MTRACE mtrace(); #endif db = sqlite_open("/tmp/testdb", 0, &azErr); if (db == 0) { printf("Couldn't open db file!\n"); free(azErr); exit(0); } sqlite_close(db); } Oddly enough, when I mtrace this program is complains with: Memory not freed: ----------------- Address Size Caller 0x08049bd0 0x40 at 0x4004a3ac I'm not sure if this is even related. Any ideas? This problem is occuring on Redhat 7.2 with sqlite 2.8.5 and purifyplus 2003.06.00. #e8e8bd 307 event active 2003 May anonymous Shell 2003 Dec anonymous 1 1 fail to rpm install on redhat 9 OS: Redhat 9.0 Kernel: 2.4.20-8 rpm: 4.2 package: sqlite-2.8.0-1.i386.rpm Error: headerRead failed: region trailer: BAD, tag 2053730304 type 956301312 offset -4128768 count 524287 #e8e8bd 207 event active 2002 Dec anonymous Shell 2003 Dec 2 1 After installation cannot create any databases After installation of sqlite 2.7.4, use of the commandline to create a database always ends with a SQL error: database locked. The file of the database is created. I've been experiencing the same issue with 2.8.0, compiled for the Cygwin environment in Windows 95. The issue is not present if I instead compile for the MinGW environment. For my case, I've tracked it down to an issue with the behaviour of advisory locking in Cygwin (based on the fcntl POSIX function). I've patched my copy to work around the issue by clearing any existing locks on a file before attempting to acquire a new one, though I'm not too sure this is the best solution. Russell Reed (rreed@cei.net) ---- Same for me with sqlite 2.8.6. Here's my environment: $ dmesg |head Linux version 2.2.25 (root@sbgpcs22) (gcc version 2.95.4 20011002 (Debian prerelease)) \#1 SMP Mon Mar 31 19:10:06 CEST 2003 BIOS-provided physical RAM map: BIOS-e820: 0009f000 @ 00000000 (usable) BIOS-e820: terface adding patch-sets, bug tracking, and Wiki to CVS. http://www.hwaci.com/sw/cvstrac/. *: SQL Relay: A persistent database connection pooling, proxying and load balancing system with APIs for a wide range of programming languages. http://www.firstworks.com/ --> http://sqlrelay.sourceforge.net *: Zee Cookbook: A cookbook application for Sharp Zaurus PDA. hb interface adding patch-sets, bug tracking, and Wiki to CVS. http://www.hwaci.com/sw/cvstrac/. *: SQL Relay: A persistent database connection pooling, proxying and load balancing system with APIs for a wide range of programming languages. http://www.firstworks.com/ --> http://sqlrelay.sourceforge.net *: Zee Cookb iy #e8e8bd 509 event active 2003 Nov anonymous 2003 Nov 5 3 SQLite on Pocket PC - question - how to do that step by step? Hi! First of all I really appreciate work you guys did with SQLite. My question is; can anyone tell me how can I create the SQLite version working with Unicode (_UNICODE defined on Win32) on Pocket PC platform. The case is I am creating application for pocket PC to cooperate with PC computer, and while I create and access database on the PC, I cant open or even create new database on Pocket PC. I was succesfull in compiling SQLite as it is ported to CE but always I get the error that the database disk image is malformed, even if I am creating new database. Does any one was successful in creating and accessing database on Pocket PC and then in copying it to PC and accessing it on PC? I will not stick to Unicode since I am converting the wide char to char but this is not helping in avoiding the malformed error while creating or accessing database. I would appreciate any help, I am quit new bee for database sources and SQLite looks for me quite complex to get into it. Artur _2004-Aug-20 09:14:36 by anonymous:_ {linebreak} I have the same problem. I create a little parser using scripts for traslating a MySQL database to SQLite database. I pass the db file with ActiveSync. I open the file correctly with a XML generator that I create with Visual Studio, but I'm trying to do the same project with eMbedded Visual C++ 4.0 and I have a lot of errors. Can anybody help me? Thanks a lot. aaronpl@ya.com ---- _2005-May-11 14:30:47 by anonymous:_ {linebreak} Am also trying to port sqlite3.2.1 to WinCE, and have many errors in eMbeddedVC++ Version 4 sp4, mainly ----- btree.c(549) : error C2059: syntax error : 'type' Error in line assert( iCell hdrOffset+3])) ); ----- btree.c(601) : warning C4244: '+=' : conversion from '__int64 ' to 'unsigned int ', possible loss of data Many errors of this form, this line nPayload += pInfo->nKey; ----- os_win.c(394) : error C2065: 'LOCKFILE_FAIL_IMMEDIATELY' : undeclared identifier Error in line res = LockFileEx(id->h, LOCKFILE_FAIL_IMMEDIATELY, 0, SHARED_SIZE,0,&ovlp); ===== #e8e8bd 493 event active 2003 Nov anonymous Unknown 2003 Nov 3 3 Problem compiling sqlite # gmake ./libtool gcc -s -O3 -march=i686 -DOS_UNIX=1 -DOS_WIN=0 -DHAVE_USLEEP=1 -I. -I./src -DHAVE_READLINE=1 -I/usr/local/include/readline -o sqlite ./src/shell.c \ libsqlite.la -lreadline -rpath /usr/local/lib gcc -s -O3 -march=i686 -DOS_UNIX=1 -DOS_WIN=0 -DHAVE_USLEEP=1 -I. -I./src -DHAVE_READLINE=1 -I/usr/local/include/readline -o .libs/sqlite ./src/shell.c ./.libs/libsqlite.so -lreadline -Wl,--rpath -Wl,/usr/local/lib /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.3.1/../../../libreadline.so: undefined reference to `tgetnum' /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.3.1/../../../libreadline.so: undefined reference to `tgoto' /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.3.1/../../../libreadline.so: undefined reference to `tgetflag' /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.3.1/../../../libreadline.so: undefined reference to `BC' /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.3.1/../../../libreadline.so: undefined reference to `tputs' /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.3.1/../../../libreadline.so: undefined reference to `PC' /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.3.1/../../../libreadline.so: undefined reference to `tgetent' /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.3.1/../../../libreadline.so: undefined reference to `UP' /usr/local/lib/gcc-lib/i686-pc-linux-gnu/3.3.1/../../../libreadline.so: undefined reference to `tgetstr' collect2: ld returned 1 exit status gmake: *** [sqlite] Error 1 #e8e8bd 128 event active 2002 Jul anonymous Unknown 2003 Oct 4 4 Workaround for problem reported in ticket 127- Replacing the misuse-5.3 test case with the following enables testfixture to continue running the quick test suite without bus error on Mac OS X 10.1.5/Tcl 8.4b4. do_test misuse-5.3 { db close catch { sqlite_exec_printf $::DB {SELECT * FROM t1} {} } result set result } {21 {library routine called out of sequence}} This problem does not appear under Linux or Win2K. It may be an issue with OS X or with the new Tcl. See also tickets #126, #127, and #129. #e8e8bd 463 event active 2003 Sep anonymous 2003 Sep 2 1 list index out of bounds(1) I am using the *sqlite.dll* with "*sqlitedbu.pas*" package in *Delphi 7*. Database has only one table called "baza". _'create table baza(filename string, bin blob)';_ There is no problem when i am inserting mime encoded files into table. problem is *when i have more than 200 records*. Delphi gives me explanation : "*list index out of bounds(1)*". Same query in sqlite console works with no problems. same problem happens if i index table or when i create unique fields. query I've used is: _select filename from baza where filename like "%" order by filename;_{linebreak} *Delphi code:*{linebreak} procedure TForm1.Edit1Change(Sender: TObject);{linebreak} var t:string;{linebreak} baza:TSQLiteDB;{linebreak} i:integer;{linebreak} begin{linebreak} t:=edit1.Text;{linebreak} ListBox1.Clear;{linebreak} if (DataBaseName<>'') then begin{linebreak} baza:=TSQLiteDB.Create(nil,DataBaseName);{linebreak} baza.sql:='select filename from baza where filename like "'+t+'%" order by filename';{linebreak} baza.ExecSQL;{linebreak} baza.Open;{linebreak} if baza.RecordCount>0 then begin{linebreak} baza.First;{linebreak} while not baza.EOF do begin{linebreak} ListBox1.Items.Add(baza.Fields['filename']);{linebreak} baza.Next;{linebreak} end;{linebreak} end;{linebreak} baza.Close;{linebreak} FreeAndNil(baza);FreeAndNil(i);{linebreak} end;{linebreak} end;{linebreak} #e8e8bd 462 event active 2003 Sep anonymous 2003 Sep 2 1 list index out of bounds(1) I am using the *sqlite.dll* with "*sqlitedbu.pas*" package in *Delphi 7*. Database has only one table called "baza". _'create table baza(filename string, bin blob)';_ There is no problem when i am inserting mime encoded files into table. problem is *when i have more than 200 records*. Delphi gives me explanation : "*list index out of bounds(1)*". Same query in sqlite console works with no problems. same problem happens if i index table or when i create unique fields. please help me to resolve this. #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 335 event active 2003 Jun anonymous Unknown 2003 Jun drh 2 2 ChangeCount still failing Ah, I've traced deeper in my code instead of concluding that the workaround I've implemented in version 2.8.0 is still needed. Perhaps the problem lies a bit deeper. Here's what's happening in the driver: - SQLite_Compile 'update Simpsons set Firstname = "Homer." where Lastname = "Simpson" and Firstname = "Homer" ' - SQLite_Step which returns ok - SQLiteChanges which returns 1 Now the weird thing kicks in (See also my ticket #261): - SQLite_Finalize fails with "call is out of sequence" If I perform the following ticket #335 is relevant - SQLite_Step until EOF - Don't call SQLite_Finalize (reource leaking?) Any next virtual machine will increment the SQLiteChanges with the previous SQLiteChanges, again and again. Perhaps #335 is non relevant if #261 is fixed with the procedure described above. ---- Below are snippets of log from my sqlite dbexpress driver. Every execute line is using a different virtual machine. The first time everything is ok, the second virtual machine however doesn't seem to have reset the changecount, because it returns two changes. This increments to three, four etc... ---- Execute: update Simpsons set Firstname = "Homer." where Lastname = "Simpson" and Firstname = "Homer" *Rows affected: 1* Execute: update Simpsons set Firstname = "Barney." where Lastname = "Gumbles" and Firstname = "Barney" *Rows affected: 2* _etcetera_ I am unable to reproduce this with SQLite. Can you provide a pure SQL script that generates this behavior? Are you certain the problem is in SQLite and not in the DBExpress driver? #e8e8bd 271 event active 2003 Mar anonymous Unknown 2003 Mar drh 3 2 'make test' fails on MacOS X As per a message from drh, adding -DSQLITE_TEST=1 to the make file may fix the problem. Unfortunately adding -DSQLITE_TEST=1 to the makefile broke the sqlite build (not test) with the linker errors below. Adding -DSQLITE_TEST=1 just for the 'make test' did not fix the problem with the tests. {linebreak} {linebreak}---- from 'make test' with or without -DSQLITE_TEST=1 {linebreak} trans-9.1... {linebreak} Error: no such function: randstr {linebreak} trans-9.2.1-0... {linebreak} Error: cannot start a transaction within a transaction {linebreak} trans-9.2.2-0... {linebreak} Error: cannot start a transaction within a transaction {linebreak} trans-9.2.9-0... {linebreak} Error: no such function: randstr {linebreak} trans-9.3.1-0... {linebreak} Error: cannot start a transaction within a transaction {linebreak} trans-9.3.2-0... {linebreak} Error: cannot start a transaction within a transaction {linebreak} trans-9.3.9-0... {linebreak} Error: no such function: randstr {linebreak} trans-9.4.1-0... {linebreak} Error: cannot start a transaction within a transaction {linebreak} trans-9.4.2-0... {linebreak} Error: cannot start a transaction within a transaction {linebreak} trans-9.4.9-0... {linebreak} Error: no such function: randstr {linebreak} trans-9.5.1-0... {linebreak} Error: cannot start a transaction within a transaction {linebreak} *** Giving up... {linebreak} 11 errors out of 13684 tests {linebreak} Failures on these tests: trans-9.1 trans-9.2.1-0 trans-9.2.2-0 {linebreak} trans-9.2.9-0 trans-9.3.1-0 trans-9.3.2-0 trans-9.3.9-0 trans-9.4.1-0 {linebreak} trans-9.4.2-0 trans-9.4.9-0 trans-9.5.1-0 {linebreak} make: *** [test] Error 1 {linebreak} {linebreak}---- from 'make': {linebreak} ld: multiple definitions of symbol _btree_native_byte_order {linebreak} auth.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} btree.lo definition of _btree_native_byte_order in section (__DATA,__data) {linebreak} build.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} delete.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} expr.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} func.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} hash.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} insert.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} main.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} os.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} pager.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} ld: multiple definitions of symbol _journal_format {linebreak} btree.lo definition of _journal_format in section (__DATA,__common) {linebreak} pager.lo definition of _journal_format in section (__DATA,__data) {linebreak} ld: multiple definitions of symbol _pager_refinfo_enable {linebreak} btree.lo definition of _pager_refinfo_enable in section (__DATA,__common) {linebreak} pager.lo definition of _pager_refinfo_enable in section (__DATA,__data) {linebreak} parse.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} printf.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} random.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} select.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} table.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} tokenize.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} update.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} util.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} vdbe.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} where.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} trigger.lo definition of _btree_native_byte_order in section (__DATA,__common) {linebreak} make: *** [libsqlite.la] Error 1 _2004-Aug-14 00:40:23 by anonymous:_ {linebreak} On Mac OSX 10.3.5 with sqlite 3.0.4, "make test" resulted in:{linebreak} _: 0 errors out of 22420 tests I suggest this bug be closed. ---- _2004-Nov-09 20:48:41 by anonymous:_ {linebreak} Server compiled fine here on Mac OS X 10.3.6, none of the tests fail. #e8e8bd 255 event active 2003 Feb anonymous 2003 Feb 1 1 Database locked on HP64 (B11.11) When using %sqlite foo on HP64 (HPUX B11.11, gcc 3.2) and trying to create Table An error message said that the database is locked #e8e8bd 254 event active 2003 Feb anonymous 2003 Feb 1 1 make test does not work on HP64 (B11.11) make test does not work on HP64 (B11.11) #e8e8bd 253 event active 2003 Feb anonymous 2003 Feb 1 1 make install does not work with install-sh script On HP64bits,B11.11 After ./configure and make % make install does not work because install-sh does not have execution rights need to do a chmod +x #e8e8bd 233 event active 2003 Jan anonymous Unknown 2003 Jan 1 4 test bigfile-1.1 dumps core on Tru64 platform I'm trying to get cvstrac (which uses sqlite) up and running on a Tru64 machine (uname -a follows): OSF1 calypso.umc.com.ua V5.1 732 alpha However, cvstrac coredumps constantly. In attempt to track the reason of bug I tried to run 'make test' for sqlite and got: bigfile-1.1...make: Segmentation fault (core dumped) I tried to compile with either gcc-3.0.3 or supplied cc (dont know how to get version of cc, though) - it doesnt matter, coredump doesnt go. If I try to run testfixture under gdb, SIGSEGV kills gdb as well, so I tried to use truss to get a backtrace and got: [lots of open("/var/tmp/sqlite_59JacTYbQOLAC2h")/write/close skipped - they all were succefull ] 185527: fcntl(9, F_SETLK, 0x000000011FFF9000) = 0 185527: fstat(9, 0x000000011FFF8F70) = 0 185527: fcntl(9, F_SETLK, 0x000000011FFF9090) = 0 185527: write(7, "\0\0\003\0\0\0\0\b\0 803".., 1028) = 1028 185527: Incurred fault #32, FLTBOUNDS %pc = 0x0000000120007130 addr = 0x000000011FFF6EA0 185527: Received signal #11, SIGSEGV [default] 185527: siginfo: SIGSEGV SEGV_MAPERR addr=0x000000001FFF7369 Err#139 Error 139 occurred. 185527: *** process killed *** Seems like "write out of page bounds to me". Where can I get a closer look at in attempt to resolve the problem? #e8e8bd 230 event active 2003 Jan anonymous Shell 2003 Jan anonymous 4 5 Problems compiling on AIX 4.3.3 Probably not really a bug, but a problem compiling on AIX 4.3.3 that I fixed and thought you might like to know about... During make, I got the output below. The problem turned out to be the AIX nm command itself. I used nm from gnu's bintools and it made fine. Thought you might like to know. FYI, the relevant output from make was: /usr/bin/nm -B .libs/btree.o .libs/build.o .libs/delete.o .libs/expr.o (etc, etc) nm: .libs/main.o: 0654-206 Cannot process the symbol table. ... gcc -g -O2 -DOS_UNIX=1 -DOS_WIN=0 -DHAVE_USLEEP=1 -I. -I../src -DHAVE_READLINE=0 -o .libs/sqlite (etc, etc) ld: 0711-317 ERROR: Undefined symbol: .sqlite_interrupt ld: 0711-317 ERROR: Undefined symbol: .sqlite_exec ld: 0711-317 ERROR: Undefined symbol: .sqlite_open_aux_file ld: 0711-317 ERROR: Undefined symbol: .sqlite_close ld: 0711-317 ERROR: Undefined symbol: .sqlite_busy_timeout ld: 0711-317 ERROR: Undefined symbol: .sqlite_complete ld: 0711-317 ERROR: Undefined symbol: .sqlite_error_string ld: 0711-317 ERROR: Undefined symbol: .sqlite_open ld: 0711-317 ERROR: Undefined symbol: sqlite_version ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information. collect2: ld returned 8 exit status make: 1254-004 The error code from the last command is 1. Stop. #e8e8bd 204 event active 2002 Dec anonymous Unknown 2002 Dec anonymous 1 1 Select statement takes hours (or more!) On Win32, I have a 500> MB db with 13 tables. The schema for each table is: CREATE TABLE XX (Year integer,Month integer, ProvOD integer,PaisOD integer,Posicion char(10),IOD char(1), PesoI float,UnidadesI float,ValorEstI float, PesoE float,UnidadesE float,ValorEstE float); create index tmpidx on tmp (provod,paisod,posicion,iod); One the the tables is called tmp and it has more than 2.000.000 records. Issuing the statement: select 1994 AS Year,-1 AS Month,ProvOD,PaisOD,Posicion,IOD, SUM(PesoI) AS PesoI,SUM(UnidadesI) AS UnidadesI,SUM(ValorEstI) AS ValorEstI, SUM(PesoE) AS PesoE,SUM(UnidadesE) AS UnidadesE,SUM(ValorEstE) AS ValorEstE FROM tmp GROUP BY ProvOD,PaisOD,Posicion,IOD; takes forever (at least several hours - Pentium III 800Mhz), the I stopped with ^C. Also it "eats" more than 200 Mb of RAM while executin (through the sqlite command line). If I get ride of the "Position" field in the group by it takes 50 seconds. Is it normal? Is there anyway to speed that up? Am I taking sqlite to its limits? Comments? #e8e8bd 164 event active 2002 Oct anonymous CodeGen 2002 Oct anonymous 3 4 Compiler warnings with MS Visual C++ 6.0 Hi, when I incorporated the "sqlite_source.zip" source code into a Microsoft Visual C++6.0 sample application, I got 24 warnings, mostly due to some signed/unsigned mismatch. It looks as it still works fine (no difference as when I used the DLL), but you never know if this holds true alltimes. Not showing up warnings would improve convidence into the product. Regards, Louis Schneider Deleting intermediate files and output files for project 'GENERIC - Win32 Release'. --------------------Configuration: GENERIC - Win32 Release-------------------- Compiling resources... Compiling... GENERIC.C where.c build.c delete.c expr.c C:\samples\techart\tech\win32\generic3\expr.c(1461) : warning C4018: '!=' : signed/unsigned mismatch func.c hash.c insert.c main.c opcodes.c os.c C:\samples\techart\tech\win32\generic3\os.c(495) : warning C4018: '==' : signed/unsigned mismatch pager.c C:\samples\techart\tech\win32\generic3\pager.c(346) : warning C4018: '>' : signed/unsigned mismatch C:\samples\techart\tech\win32\generic3\pager.c(1008) : warning C4018: '>=' : signed/unsigned mismatch parse.c printf.c random.c select.c C:\samples\techart\tech\win32\generic3\select.c(99) : warning C4018: '==' : signed/unsigned mismatch shell.c table.c tokenize.c trigger.c Generating Code... parse.c(6771) : warning C4761: integral size mismatch in argument; conversion supplied parse.c(6782) : warning C4761: integral size mismatch in argument; conversion supplied Compiling... update.c util.c vdbe.c C:\samples\techart\tech\win32\generic3\vdbe.c(2069) : warning C4244: 'initializing' : conversion from 'double ' to 'int ', possible loss of data btree.c C:\samples\techart\tech\win32\generic3\btree.c(2306) : warning C4018: '<' : signed/unsigned mismatch Generating Code... C:\samples\techart\tech\win32\generic3\btree.c(629) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(1762) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(1764) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(516) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(520) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(534) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(538) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(541) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(482) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(483) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(410) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(421) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(432) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(434) : warning C4761: integral size mismatch in argument; conversion supplied C:\samples\techart\tech\win32\generic3\btree.c(1924) : warning C4761: integral size mismatch in argument; conversion supplied Linking... Creating library GENERIC.lib and object GENERIC.exp GENERIC.exe - 0 error(s), 24 warning(s) #e8e8bd 129 event active 2002 Jul anonymous Unknown 2002 Jul 4 4 tcl-2.2 test fails Mac OS X 10.1.5/Tcl8.4b4 When sqlite is compiled with UTF-8 encoding, -DSQLITE_TEST and no memory debugging activated, the tcl-2.2 test fails in the context of running the quick test suite. tcl-2.2... Error: can't read "result(*)": variable isn't array When the tclsqlite.test suite is run individually, all tests pass. I worked around this by modifying the test case to unset the variable 'result' at the top of the test. do_test tcl-2.2 { catch { unset result } execsql "INSERT INTO t\u0123x VALUES(1,2.3)" db eval "SELECT * FROM t\u0123x" result break set result(*) } "a b\u1235" This problem does not appear under Linux or Win2k. It may be an issue with OS X or with the new Tcl. See also tickets #126, #127, and #128. #e8e8bd 126 event active 2002 Jul anonymous VDBE 2002 Jul 4 5 malloc-1.195 test aborts from assert in vdbe.c Release 2.6.2 passes the quick.test suite with no errors. In further testing with the complete suite (all.test), the tests abort at malloc-1.195. The error message is given below. malloc-1.194... Ok malloc-1.195..../src/vdbe.c:5127: failed assertion `p->tos 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 */