Re: [sqlite] Performance of VIEW with UNION
--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> >
> > I would think it would not be too difficult to extend SQLite
> > to perform this type of transformation on a view.
> >
> > i.e., transform:
> >
> > SELECT columns0 from (
> >SELECT columns1 WHERE condition1
> >UNION (ALL)
> >SELECT columns2 WHERE condition2
> > )
> > WHERE condition3
> >
> > to
> >
> > SELECT columns0 from (
> >SELECT columns1 WHERE (condition1) AND (condition3)
> >UNION (ALL)
> >SELECT columns2 WHERE (condition2) AND (condition3)
> > }
> >
> > or am I neglecting something?
> >
>
> We await your patch.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
As goaded^H^H^H^H^H^Hrequested, here's a 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|
These optimizations take place on a resolved tree before any VDBE
instructions are generated. This way you have more flexibility
to do complete tree transforms. ORs in WHERE clauses are another good
candidate for optimization, if someone wants to take that on.
The SELECT tree dump utility could be helpful:
http://marc.theaimsgroup.com/?l=sqlite-users=115371405520994=2
"make test" ran with no regressions, but this patch should be considered
experimental. Many more test cases are needed to test these optimizations.
I disclaim copyright to this source code.
Joe Wilson
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com Index: src/parse.y
===
RCS file: /sqlite/sqlite/src/parse.y,v
retrieving revision 1.207
diff -u -3 -p -r1.207 parse.y
--- src/parse.y 14 Aug 2006 14:23:42 - 1.207
+++ src/parse.y 17 Aug 2006 04:07:31 -
@@ -366,6 +366,7 @@ cmd ::= DROP VIEW ifexists(E) fullname(X
The SELECT statement /
//
cmd ::= select(X). {
+ sqlite3SelectOptimize(pParse, X);
sqlite3Select(pParse, X, SRT_Callback, 0, 0, 0, 0, 0);
sqlite3SelectDelete(X);
}
@@ -598,8 +599,10 @@ setlist(A) ::= nm(X) EQ expr(Y). {A =
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F)
VALUES LP itemlist(Y) RP.
{sqlite3Insert(pParse, X, Y, 0, F, R);}
-cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S).
-{sqlite3Insert(pParse, X, 0, S, F, R);}
+cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). {
+ sqlite3SelectOptimize(pParse, S);
+ sqlite3Insert(pParse, X, 0, S, F, R);
+}
%type insert_cmd {int}
insert_cmd(A) ::= INSERT orconf(R). {A = R;}
Index: src/select.c
===
RCS file: /sqlite/sqlite/src/select.c,v
retrieving revision 1.320
diff -u -3 -p -r1.320 select.c
--- src/select.c11 Aug 2006 19:08:27 - 1.320
+++ src/select.c17 Aug 2006 04:07:32 -
@@ -1382,7 +1382,7 @@ static int matchOrderbyToColumn(
Parse *pParse, /* A place to leave error messages */
Select *pSelect,/* Match to result columns of this SELECT */
ExprList *pOrderBy, /* The ORDER BY values to match against columns */
- int iTable, /* Insert this value in iTable */
+ int* iTable,/* Insert this value in iTable */
int mustComplete/* If TRUE all ORDER BYs must match */
){
int nErr = 0;
@@ -1405,6 +1405,12 @@ static int matchOrderbyToColumn(
for(i=0; inExpr; i++){
Expr *pE = pOrderBy->a[i].pExpr;
int iCol = -1;
+if( pE->op==TK_COLUMN ) {
+ /* If type is TK_COLUMN, all column nodes in expression
+ are guaranteed to contain the same iTable value. */
+ *iTable = pE->iTable;
+ continue;
+}
if( pOrderBy->a[i].done ) continue;
if( sqlite3ExprIsInteger(pE, ) ){
if( iCol<=0 || iCol>pEList->nExpr ){
@@ -1435,7 +1441,7 @@ static int matchOrderbyToColumn(
if( iCol>=0 ){
pE->op = TK_COLUMN;
pE->iColumn = iCol;
- pE->iTable
[sqlite] Re: fault tolerance
I realize that sqlite uses the best available techniqes to always fail safe and therefore avoid database corruption, it remains that case that "shit happens" and when it does, sqlite databases become hopelessly corrupted. Traditional file systems have evolved an array of techniques to limit the damage caused by corruption - redudnant records, checksums, and so on. I would like to see features of this type in future versions of sqlite. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl - timeout method
Bud Beacham <[EMAIL PROTECTED]> wrote: > Thanks. Now I understand why I was having a problem with errorcode in that > situation. > > Are there any other Tcl exceptions to errocode's return value? > The general rules is this: If SQLite gives you anything other than SQLITE_OK (or SQLITE_ROW or SQLITE_DONE) then you are going to get a TCL exception. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl - timeout method
Thanks. Now I understand why I was having a problem with errorcode in that situation. Are there any other Tcl exceptions to errocode's return value? [EMAIL PROTECTED] wrote: Bud Beacham wrote: > Personally, a database being busy does not, in my mind, > constitute something going wrong. It is expected behaviour, > and should be handled in a normal manner; i.e. errorcode returns a 5. Regardless of what it ought to do, this is not was the TCL interface has ever done in the past. To change it now would break countless thousands of lines of existing code. So I think it needs to stay as it is. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] announce: libsqlfs
Sorry I meant SQLite 3.2.7. That's the version we use, not 2.8.16. Andy On 8/16/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Andy Tai" <[EMAIL PROTECTED]> wrote: > Hi, I would like to announce the initial release of the libsqlfs... > > [...] > > Currently it has been tested ... with SQLite 2.8.16 SQLite version 3.3.7 is smaller, faster, more robust in the face of power loss, generates smaller database files, and has a much superior BLOB interface. Version 3.x has been out for over 2 years and is being actively enhanced whereas 2.8.x is maintenance only. And 2.8.17 is current, not 2.8.16. So I'm wondering why you choose to use 2.8.16? -- D. Richard Hipp <[EMAIL PROTECTED]> -- Andy Tai, [EMAIL PROTECTED]
Re: [sqlite] announce: libsqlfs
> Currently it has been tested on 32-bit x86 and Strong Arm processors, > with SQLite 2.8.16 and FUSE 2.3.5. It can be built both as an > application library or as an FUSE module. Are you, your company, going to implement a /etc-in-one-file? Or isn't saving settings and small rich-text-files the main target? Stefan de Konink - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] announce: libsqlfs
"Andy Tai" <[EMAIL PROTECTED]> wrote: > Hi, I would like to announce the initial release of the libsqlfs... > > [...] > > Currently it has been tested ... with SQLite 2.8.16 SQLite version 3.3.7 is smaller, faster, more robust in the face of power loss, generates smaller database files, and has a much superior BLOB interface. Version 3.x has been out for over 2 years and is being actively enhanced whereas 2.8.x is maintenance only. And 2.8.17 is current, not 2.8.16. So I'm wondering why you choose to use 2.8.16? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] announce: libsqlfs
Hi, I would like to announce the initial release of the libsqlfs library under the GNU Lesser General Public License (LGPL). Created as part of the ACCESS Linux Platform (ALP), the libsqlfs library is meant to worm together with the SQLite database. The libsqlfs software library was designed to provide a more flexible and convenient way to implement an Open Mobile Alliance-Device Management (OMA-DM) compliant registry. Libsqlfs provides an easy way for applications to put an entire read/write file system into a relational database as a single file in the host file system. Such a file system can easily be moved around, backed up or restored as a single file. In addition, by using the FUSE (File System in User Space) facility on GNU/Linux, a libsqlfs database can be mounted as a file system at the OS level and accessible by applications via the standard Unix file system interfaces. Libsqlfs can be downloaded from http://www.palmsource.com/opensource/downloads.html Currently it has been tested on 32-bit x86 and Strong Arm processors, with SQLite 2.8.16 and FUSE 2.3.5. It can be built both as an application library or as an FUSE module. Andy Tai, [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl - timeout method
Bud Beacham <[EMAIL PROTECTED]> wrote: > Personally, a database being busy does not, in my mind, > constitute something going wrong. It is expected behaviour, > and should be handled in a normal manner; i.e. errorcode returns a 5. Regardless of what it ought to do, this is not was the TCL interface has ever done in the past. To change it now would break countless thousands of lines of existing code. So I think it needs to stay as it is. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl - timeout method
[EMAIL PROTECTED] wrote: Bud Beacham wrote: > Yes. This is a bug, but I have not filed it yet. The problem is that the the > Tcl errorcode command does not return a 5 (SQLITE_BUSY) when the DB is in > use. Instead, Tcl crashes with a "database locked" message. > For example, > sqlite dbCmd $dataBase > dbCmd timeout 3000 > set qryResult [dbCmd eval $query] > set errorCode [dbCmd errorcode] > dbCmd close > > So this means that instead of checking the errorcode to try again on a locked > DB you need to enclose everything in a "catch" statement, and check the > "catch" for an error. > The current behavior is by design. It is as intented. It is the "TCL way" to throw an exception when something goes wrong, and encountering an SQLITE_BUSY error counts as something going wrong. That's great if it were documented. Rather, the documentation implies that errorcode should return a 5, and then the program should handle it. Personally, a database being busy does not, in my mind, constitute something going wrong. It is expected behaviour, and should be handled in a normal manner; i.e. errorcode returns a 5.
Re: [sqlite] Tcl - timeout method
Bud Beacham <[EMAIL PROTECTED]> wrote: > Yes. This is a bug, but I have not filed it yet. The problem is that the > the Tcl errorcode command does not return a 5 (SQLITE_BUSY) when the DB is in > use. Instead, Tcl crashes with a "database locked" message. > For example, >sqlite dbCmd $dataBase > dbCmd timeout 3000 > set qryResult [dbCmd eval $query] > set errorCode [dbCmd errorcode] > dbCmd close > > So this means that instead of checking the errorcode to try again on a > locked DB you need to enclose everything in a "catch" statement, and check > the "catch" for an error. > The current behavior is by design. It is as intented. It is the "TCL way" to throw an exception when something goes wrong, and encountering an SQLITE_BUSY error counts as something going wrong. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl - timeout method
Yes. This is a bug, but I have not filed it yet. The problem is that the the
Tcl errorcode command does not return a 5 (SQLITE_BUSY) when the DB is in use.
Instead, Tcl crashes with a "database locked" message.
For example,
sqlite dbCmd $dataBase
dbCmd timeout 3000
set qryResult [dbCmd eval $query]
set errorCode [dbCmd errorcode]
dbCmd close
So this means that instead of checking the errorcode to try again on a locked
DB you need to enclose everything in a "catch" statement, and check the "catch"
for an error.
All said though, I love this DB and the Tcl interface. I really want to thank
the author for providing these Tcl APIs since Tcl is the most powerful
scripting language ever developed.
Ulrich Schöbel <[EMAIL PROTECTED]> wrote:
Hi all,
I'm using sqlite3 inside a Tcl module (mod_websh) of
apache. Everything works fine, except when it comes
to concurrent write accesses.
There's a small transaction where I update two rows.
When I stress the web server it generates a
"database locked" error. I invoke a "db timeout 2000"
just before the transaction, but it doesn't seem to
have any effect.
When I stumbled over this problem I increased the
timeout to 50, which is much longer than the
stress test takes, but the error persists. What's
going wrong here?
Here's a short code snippet:
db timeout 50
db transaction {
set last_acc [lindex [db eval {
select acc_time from last_hit where site = $site and from_ip = $ip
}] 0]
if {![string length $last_acc]} {
db eval {
insert into last_hit values ($site , $ip , $now)
}
set last_acc 0
} else {
db eval {
update last_hit set acc_time = $now where site = $site and from_ip =
$ip }
}
..
} ;# end of transaction
There's another update inside this transaction, but
it's always the one above that fails.
Maybe the "busy" method is better suited here, but
the docs say nothing about its usage.
Thanks for any help
Ulrich
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Building on SCO Open Server
Clay Dowling uttered: Installing gcc is an excellent idea, but I would not be entirely surprised to learn that the actual failure is in sed. SCO and awk/sed do not have a happy history, with incompatible changes having been introduced at times. Nope, I reckon the SCO compiler just doesn't know what a "long long" is, which isn't surprising as it's basically a new type with C99. Old ANSI compilers will struggle with out of the box SQLite. Clay Steve Baldwin said: Hi, I'm trying to build SQLite 3.3.7 on SCO Open Server 5.0.5. So far, I've installed gnu make (which was reasonably painless) to get around the limitations of the default SCO make. We don't have tcl installed, so I ran configure with the -disable-tcl option. I didn't get any warnings from configure. When I try the build, I get this ... $ make sed -e s/--VERS--/3.3.7/ ../src/sqlite.h.in | \ sed -e s/--VERSION-NUMBER--/3003007/ >sqlite3.h cc -g -belf -o lemon ../tool/lemon.c cp ../tool/lempar.c . cp ../src/parse.y . ./lemon -DSQLITE_OMIT_CURSOR parse.y mv parse.h parse.h.temp awk -f ../addopcodes.awk parse.h.temp >parse.h cat parse.h ../src/vdbe.c | nawk -f ../mkopcodeh.awk >opcodes.h ./libtool --mode=compile cc -g -belf -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -c ../src/alter.c mkdir .libs cc -g -belf -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -c ../src/alter.c -Kpic -DPIC -o .libs/alter.o "./sqlite3.h", line 88: error: invalid type combination "./sqlite3.h", line 89: error: invalid type combination make: *** [alter.lo] Error 1 Here are the lines the compiler is barfing on ... [EMAIL PROTECTED] W]$ head -89 sqlite3.h | tail -2 typedef long long int sqlite_int64; typedef unsigned long long int sqlite_uint64; Is this doable with the standard SCO C compiler or am I banging my head against a brick wall? Thanks for any suggestions. Steve - To unsubscribe, send email to [EMAIL PROTECTED] - -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading a damaged database file?
Olaf Beckman Lapré wrote: I've done some tests by modifying the .db file with a hex editor, to simulate a corrupt database How do I repair the database? I can think of only two possibilities other than the things already discussed in this thread: 1) Undo the modifications made with the hex editor. 2) Use the most recent backup file and the log of transactions since then. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Strange behavior after database save.
I am seeing a behavior I cannot explain, I will try to keep things as simple as possible. I have a simple table that I use for storing phonebook entries (name, phone number, etc). If I start fresh, I am able to Add/Remove/Retrieve records with no problem from my database in memory. My query to retrieve the records is using 'order by name' so that it is sorted for me. I tried all kinds of sequence Add-Retrieve-Add-Add-Retrieve-Retrieve-... and it never seemed to fail. I store it in flash using: "attach database pbook as pbook;" "BEGIN;" "create table if not exists pbook.phonebook as select * from phonebook;" "delete from pbook.phonebook;" "insert into pbook.phonebook select * from phonebook;" "COMMIT;" "detach database pbook" and I recover it at boot time, using the following: "create table phonebook as select * from pbook.phonebook;" AFTER I use the version from flash I seem to be unable to retrieve the records using "order by" in my query, sqlite tells me "no such table" but if I remove "order by" from my query, it works and I retrieve them (unordered of course). Any idea?? Mario Hebert
Re: [sqlite] Suggestion
hongdong <[EMAIL PROTECTED]> wrote: > Drh:thanks a lot for your great SQLite,I have a suggestion:3.37 add some > more feature,I don't think every body need these,so for me I would like a > release just fixs for 3.36 without new feature,what do you think about? > thanks a lot! For about a year now, all new features in SQLite are optional. You can omit them at compile-time if you do not want them. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] starting with unicode
No, you don't need sqlite3_reset() inside the loop. The pseudocode should be: open prepare loop while not at end of file step read repeat finalize close For the "read" portion, use the sqlite_column_xxx() methods. And wrap every single string in your code in the _T() macro, or you'll get errors everywhere when you finally move to a Unicode build. RobR - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Building on SCO Open Server
Installing gcc is an excellent idea, but I would not be entirely surprised to learn that the actual failure is in sed. SCO and awk/sed do not have a happy history, with incompatible changes having been introduced at times. Clay Steve Baldwin said: > Hi, > > I'm trying to build SQLite 3.3.7 on SCO Open Server 5.0.5. So far, I've > installed gnu make (which was reasonably painless) to get around the > limitations of the default SCO make. We don't have tcl installed, so I > ran > configure with the -disable-tcl option. I didn't get any warnings from > configure. > > When I try the build, I get this ... > > $ make > sed -e s/--VERS--/3.3.7/ ../src/sqlite.h.in | \ > sed -e s/--VERSION-NUMBER--/3003007/ >sqlite3.h > cc -g -belf -o lemon ../tool/lemon.c > cp ../tool/lempar.c . > cp ../src/parse.y . > ./lemon -DSQLITE_OMIT_CURSOR parse.y > mv parse.h parse.h.temp > awk -f ../addopcodes.awk parse.h.temp >parse.h > cat parse.h ../src/vdbe.c | nawk -f ../mkopcodeh.awk >opcodes.h > ./libtool --mode=compile cc -g -belf -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. > -I../src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 > -DSQLITE_OMIT_CURSOR -c ../src/alter.c > mkdir .libs > cc -g -belf -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../src -DNDEBUG > -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -c > ../src/alter.c -Kpic -DPIC -o .libs/alter.o > "./sqlite3.h", line 88: error: invalid type combination > "./sqlite3.h", line 89: error: invalid type combination > make: *** [alter.lo] Error 1 > > Here are the lines the compiler is barfing on ... > > [EMAIL PROTECTED] W]$ head -89 sqlite3.h | tail -2 > typedef long long int sqlite_int64; > typedef unsigned long long int sqlite_uint64; > > Is this doable with the standard SCO C compiler or am I banging my head > against a brick wall? > > Thanks for any suggestions. > > Steve > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading a damaged database file?
I've done some tests by modifying the .db file with a hex editor, to
simulate a corrupt database, and it results in a crash. The open() goes ok
but a routine which checks for a table's existence fails. I assume it is
because I messed up the master tables.
I then added some code to check the integrity of the database and bail out
if it's not ok:
if ( sqlite3_exec( g_Db, wxT( "PRAGMA integrity_check;" ), NULL, NULL,
NULL ) != SQLITE_OK )
{
wxGetApp().DisplaySQLiteError();
OnExit();
return false;
}
This detects the problem allright, but doesn't repair it. The error message
I get is: malformed database schema - unrecognized token '#'
How do I repair the database?
- Original Message -
From: <[EMAIL PROTECTED]>
To:
Sent: Tuesday, August 01, 2006 1:35 PM
Subject: Re: [sqlite] Reading a damaged database file?
Gunnar Roth <[EMAIL PROTECTED]> wrote:
> >
> Well what happens if i write the db to a compact flash card and remove
> it while writing and put it back again?
The database should recover automatically.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Tcl - timeout method
On Wednesday 16 August 2006 12:55, Christian Nassau wrote: > Ulrich Schöbel wrote: > > I thought about that, but I didn't want to include > > the selects into the locked phase, keeping lock > > times as short as possible. Shouldn't it work > > correctly with a deferred lock? > > I don't think so: imagine two processes that have succesfully carried > out their selects (possible, since you've only got a shared lock at that > point) and now want to proceed to the update: > > SELECT(1) SELECT(2) > <-- process 1 here<-- process 2 here > UPDATE(1) UPDATE(2) > > At this point SQLite cannot allow UPDATE(1) because it might potentially > invalidate the result of SELECT(2) (and vice versa). So there's no sane > way through and at least one transaction is forced to error out. > > > --- >-- To unsubscribe, send email to [EMAIL PROTECTED] > --- >-- Yes, sounds plausible. It's obviously the way to go. Thanks again Ulrich - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl - timeout method
Ulrich Schöbel wrote: > I thought about that, but I didn't want to include > the selects into the locked phase, keeping lock > times as short as possible. Shouldn't it work > correctly with a deferred lock? I don't think so: imagine two processes that have succesfully carried out their selects (possible, since you've only got a shared lock at that point) and now want to proceed to the update: SELECT(1) SELECT(2) <-- process 1 here<-- process 2 here UPDATE(1) UPDATE(2) At this point SQLite cannot allow UPDATE(1) because it might potentially invalidate the result of SELECT(2) (and vice versa). So there's no sane way through and at least one transaction is forced to error out. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl - timeout method
On Wednesday 16 August 2006 12:16, [EMAIL PROTECTED] wrote:
> "Christian Nassau" <[EMAIL PROTECTED]> wrote:
> > I would guess that your requests acquire (shared) read locks when the
> > transaction starts and then fail/deadlock when they try to upgrade this
> > to a write lock in your insert/update statement. Maybe it would help to
> > start the transactions with "BEGIN IMMEDIATE" or "BEGIN EXCLUSIVE"...?
>
> In TCL, you can do this as:
>
>db transaction immediate {
> # code here
>}
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--
Hi Christian, Richard,
I thought about that, but I didn't want to include
the selects into the locked phase, keeping lock
times as short as possible. Shouldn't it work
correctly with a deferred lock?
Nevertheless, I'll give it a try.
Thanks for your help and special thanks to
Richard for sqlite
Ulrich
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Tcl - timeout method
"Christian Nassau" <[EMAIL PROTECTED]> wrote:
> I would guess that your requests acquire (shared) read locks when the
> transaction starts and then fail/deadlock when they try to upgrade this
> to a write lock in your insert/update statement. Maybe it would help to
> start the transactions with "BEGIN IMMEDIATE" or "BEGIN EXCLUSIVE"...?
>
In TCL, you can do this as:
db transaction immediate {
# code here
}
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: sqlite.h.in
"Dave Worthington" <[EMAIL PROTECTED]> wrote: > Hi there > > I've not had much luck using the mialing list so i thought I would try > emailing you directly :) > > After downloading the source code I cannot find sqlite.h, but instead there > is sqlite.h.in. Whats with the 'in' bit? > sqlite3.h is generated code. It is produced from sqlite.h.in by the Makefile. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Tcl - timeout method
I would guess that your requests acquire (shared) read locks when the
transaction starts and then fail/deadlock when they try to upgrade this
to a write lock in your insert/update statement. Maybe it would help to
start the transactions with "BEGIN IMMEDIATE" or "BEGIN EXCLUSIVE"...?
Ulrich Schöbel wrote:
> Hi all,
>
> I'm using sqlite3 inside a Tcl module (mod_websh) of
> apache. Everything works fine, except when it comes
> to concurrent write accesses.
>
> There's a small transaction where I update two rows.
> When I stress the web server it generates a
> "database locked" error. I invoke a "db timeout 2000"
> just before the transaction, but it doesn't seem to
> have any effect.
>
> When I stumbled over this problem I increased the
> timeout to 50, which is much longer than the
> stress test takes, but the error persists. What's
> going wrong here?
>
> Here's a short code snippet:
>
> db timeout 50
> db transaction {
> set last_acc [lindex [db eval {
> select acc_time from last_hit where site = $site and from_ip = $ip
> }] 0]
> if {![string length $last_acc]} {
> db eval {
> insert into last_hit values ($site , $ip , $now)
> }
> set last_acc 0
> } else {
> db eval {
> update last_hit set acc_time = $now where site = $site and from_ip =
> $ip }
> }
> ..
> } ;# end of transaction
>
> There's another update inside this transaction, but
> it's always the one above that fails.
>
> Maybe the "busy" method is better suited here, but
> the docs say nothing about its usage.
>
> Thanks for any help
>
> Ulrich
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Tcl - timeout method
Hi all,
I'm using sqlite3 inside a Tcl module (mod_websh) of
apache. Everything works fine, except when it comes
to concurrent write accesses.
There's a small transaction where I update two rows.
When I stress the web server it generates a
"database locked" error. I invoke a "db timeout 2000"
just before the transaction, but it doesn't seem to
have any effect.
When I stumbled over this problem I increased the
timeout to 50, which is much longer than the
stress test takes, but the error persists. What's
going wrong here?
Here's a short code snippet:
db timeout 50
db transaction {
set last_acc [lindex [db eval {
select acc_time from last_hit where site = $site and from_ip = $ip
}] 0]
if {![string length $last_acc]} {
db eval {
insert into last_hit values ($site , $ip , $now)
}
set last_acc 0
} else {
db eval {
update last_hit set acc_time = $now where site = $site and from_ip =
$ip }
}
..
} ;# end of transaction
There's another update inside this transaction, but
it's always the one above that fails.
Maybe the "busy" method is better suited here, but
the docs say nothing about its usage.
Thanks for any help
Ulrich
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Building on SCO Open Server
You've already installed GNU make, you might as well install GCC as well. --- Steve Baldwin <[EMAIL PROTECTED]> wrote: > I'm trying to build SQLite 3.3.7 on SCO Open Server 5.0.5. So far, I've > installed gnu make (which was reasonably painless) to get around the > limitations of the default SCO make. We don't have tcl installed, so I ran > configure with the -disable-tcl option. I didn't get any warnings from > configure. > ... > Is this doable with the standard SCO C compiler or am I banging my head > against a brick wall? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
