Re: [sqlite] How can SQLite return indication of where SQL failed?
On Sun, 16 Jan 2005 02:42:52 -0800, <[EMAIL PROTECTED]> wrote: I'm using Delphi 4 and various versions of SQLite wrapper. To begin, my only desire is to write SQL in a text box and execute it. What I'd like if a pointer to the place in the SQL where the syntax fails, and I'm presuming the in-DLL parser is capable of telling me that. How can I get that information back? If you're using SQLite 3 use the sqlite3_errmsg function. Granted, that assumes the wrapper supports it. Regards, Peter
Re: [sqlite] ALTER TABLE substitute?
On Thu, 6 Jan 2005 18:07:45 -0500, Tito Ciuro <[EMAIL PROTECTED]> wrote: On 6 ene 2005, at 18:01, aleks ponjavic wrote: What I want to do is drop and add columns, couldn't find something appropriate with sqlite, atleast it doesn't seem as ALTER TABLE works, how can I do it instead? Maybe it isn't possible? Please check the archives. It's been discussed already: http://www.mail-archive.com/sqlite-users@sqlite.org Or simply read the FAQ: http://www.sqlite.org/faq.html#q13 Regards, Peter Bartholdsson
Re: [sqlite] Is there any way to enable recursive triggers?
On Tue, 04 Jan 2005 05:54:04 -0500, D. Richard Hipp <[EMAIL PROTECTED]> wrote: Peter Bartholdsson wrote: Think the topic explains it but there any way to enable recursive triggers? Aka, triggers that run as result of a change by a trigger. Recursive triggers are on the todo list. They are a prerequisite for the planned implementation of foreign keys. One stubling block with recursive triggers is that a recursive trigger can result in an infinite loop. I sent out a query a month or so ago requesting ideas on how to detect and deal with infinite loops in recursive triggers. I got a few helpful responses. More input would be appreciated. Hmm, I don't know how SQLite has been built and if it's feasible but wouldn't a fully adequate solution be to limit the number of recursive calls allowed, either via a PRAGMA or a compiletime constant? I'd consider any code I write that would trigger anything such faulty to begin with so it wouldn't be a limitation of SQLite (granted the limited set high enough). It seems to me a good solution that solves the problem without excessive complexity. Or rather, how would this solution not be enough? On Tue, 4 Jan 2005 08:09:05 -0300 (ART), Claudio Bezerra Leopoldino <[EMAIL PROTECTED]> wrote: I think that a good and simple solution is simply limit the level of recursivity. Maybe 16 nested recursive calls solve 95% of the recursive needs. On the other hand 16 calls can be quite low, a reason why there should at least be possible to change it. As an example I have an application that stores directory paths in a tree structure and caches the size of each directory to improve startup speed. While most users have only a few folders some have truly scary paths. ;) This is still of course limited to how many recursive calls SQLite can actually handle. Regards, Peter Bartholdsson
[sqlite] Is there any way to enable recursive triggers?
Think the topic explains it but there any way to enable recursive triggers? Aka, triggers that run as result of a change by a trigger. Regards, Peter Bartholdsson
Re: [sqlite] Recursive Triggers
On Fri, 03 Dec 2004 19:04:56 -0500, D. Richard Hipp <[EMAIL PROTECTED]> wrote: SQLite does not currently support recursive triggers. On of the main reasons for not supporting recursive triggers is that disallowing recursive triggers was seen as the easiest way to avoid infinite loops like this: CREATE TRIGGER loop AFTER UPDATE OF table1 BEGIN UPDATE table1 SET cnt=(cnt+1)%100 WHERE rowid=old.rowid; END; UPDATE table1 SET cnt=1 WHERE rowid=1; -- Infinite loop By disallowing recursive triggers, SQLite avoids the infinite loop above. But there are useful things one could do with recursive triggers that do not involve infinite loops. I would like to relax the constraint in SQLite and allow some support for recursive triggers as long as the recursive triggers do not cause an infinite loop. But I'm not sure how to do about it? Question: What do other RDBMSes do with triggers that form infinite loops? Does anybody know? Question: Can anybody suggest a way of providing support for recursive triggers which also guarantees that every SQL statement will eventually complete? Question: Is there any progress on this? I know full and well the evils of recursive triggers and trouble they can bring but my program logic won't allow for any cases where a recursive trigger will run into any problems and users can't insert rows manually to screw stuff up. Or maybe, is there some way to enable it to simply run recursive triggers? I don't feel I need any protective code or anything, right now I have to add a custom function that gets called in my update triggers which then do an update upward (in a tree) on records which feels like a bad solution to the problem. Regards, Peter Bartholdsson
Re: [sqlite] index not used?
On Sun, 12 Dec 2004 15:41:48 +0100, Christian Schwarz <[EMAIL PROTECTED]> wrote: Normally, a select-statement would look like this (where 1 is the current day): select * from relations where valid_from < 1 and valid_to > 1 and rel_id_from = 78 and rel_id_to = 9120; This kind of statement is slow (takes between 3 and 4 seconds). It seems that sqlite is doing a full table-scan. You should note that when you include a large part of an index it might be faster to actually not use an index, WHERE col > 1 is a typical case. Also, an index doesn't index all columns individually but merged (at least to my knowledge). This means it might have to check all entries in the index if you use > or <. If you split your index up into 4 indexes it'll probably be faster. Indexes are not some magic algorithm that make everything instant, improperly used they can cause more harm than good. Regards, Peter
[sqlite] Update multiple columns from subquery
Is there some way to write this query in SQLite? UPDATE groups SET (fileCount, size) = (SELECT count(id), sum(size) FROM files where groupId = 15) WHERE groupId = 15; Or must it be written like this? UPDATE groups SET fileCount = (SELECT count(id) FROM files where groupId = 15), size = (SELECT sum(size) FROM files where groupId = 15) WHERE groupId = 15; I mostly want to get the two selects into one and I can't place code between the two.
Re: [sqlite] Release / development timeline
On Mon, 1 Nov 2004 19:23:58 -0800, Jonathon Blake <[EMAIL PROTECTED]> wrote: How about a simple page that gives projected release date for upgrades? Something like Version 3.0.9 beta 10 November Version 3.0.9 final 20 November Version 3.0.10 beta 1 December Version 3.0.10 final 15 December All this would do is create pressure and people asking why stuff ain't done yet like the projected release date states. Especially since coders are optimists by nature when it comes to time estimates. :P
Re: [sqlite] Problems compiling SQLite 3.0.7 with MinGW
On Tue, 21 Sep 2004 08:58:22 -0400, Doug Currie <[EMAIL PROTECTED]> wrote: Tuesday, September 21, 2004, 6:04:53 AM, Peter wrote: Hello all, I've been using MinGW to compile SQLite for over a year already and encountered a problem I've never seen before when trying to compile SQLite 3.0.7. There is a problem in the Makefile in 3.0.7, fixed in CVS yesterday. Windows build doesn't need pthread library at all. Just change LIBPTHREAD=-lpthread to LIBPTHREAD= e The error message is as follows: $ mingw32-make ./libtool --mode=link gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 -I/mingw/include/readline -lpthread \ -o sqlite3 ../src/shell.c libsqlite3.la -lreadline gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 -I/mingw/include/readline -o sqlite3 ../src/shell.c ./.libs/libsqlite3.a -lpthread -lreadline c:\msys\mingw\bin\..\lib\gcc\mingw32\3.4.1\..\..\..\..\mingw32\bin\ld.exe: cannot find -lpthread collect2: ld returned 1 exit status c:\msys\mingw\bin\mingw32-make.exe: *** [sqlite3.exe] Error 1 Oh thank you, this makes me feel a lot better. ^^ Regards, Peter Bartholdsson
Re: [sqlite] Problems compiling SQLite 3.0.7 with MinGW
On Tue, 21 Sep 2004 13:15:52 +0200, Martins Mozeiko <[EMAIL PROTECTED]> wrote: Did you try googling for "pthreads win32": http://sources.redhat.com/pthreads-win32/ If you want thread safety with sqlite then you must install this pthreads library, if not - then just rerun ./configure with --disable-threadsafe Closest to that I googled on was libpthreads win32. >< Thanks a lot for the help and the quick reply. Regards, Peter Bartholdsoon
[sqlite] Problems compiling SQLite 3.0.7 with MinGW
Hello all, I've been using MinGW to compile SQLite for over a year already and encountered a problem I've never seen before when trying to compile SQLite 3.0.7. The error message is as follows: $ mingw32-make ./libtool --mode=link gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 -I/mingw/include/readline -lpthread \ -o sqlite3 ../src/shell.c libsqlite3.la -lreadline gcc -g -O2 -DOS_WIN=1 -I. -I../src -DNDEBUG -DHAVE_READLINE=1 -I/mingw/include/readline -o sqlite3 ../src/shell.c ./.libs/libsqlite3.a -lpthread -lreadline c:\msys\mingw\bin\..\lib\gcc\mingw32\3.4.1\..\..\..\..\mingw32\bin\ld.exe: cannot find -lpthread collect2: ld returned 1 exit status c:\msys\mingw\bin\mingw32-make.exe: *** [sqlite3.exe] Error 1 Just as a note SQLite 3.0.6 compiles without any problem. From googling I've understood this has something to do with threading but everything I found was linux related, none of it related to MinGW. Apparently it's something called liblpthread, however I've not seen this for MinGW and none of the packages I've installed seems to include it. Anyone have any ideas? Regards, Peter Bartholdsson
