Re: [sqlite] Small enhancement: BEGIN SHARED
[EMAIL PROTECTED] wrote: > Paul Bohme <[EMAIL PROTECTED]> wrote: > >> I have a small patch that adds "BEGIN SHARED" syntax in addition to >> IMMEDIATE and EXCLUSIVE. I have an application that requires a >> consistent view of the data across a number of individual statements. >> > > This is what plain old "BEGIN" does. > > OK, really BEGIN does not acquire the lock until you actually > try to use the database in some way. So another process might > modify the database in between the time when you issue the BEGIN > and the time you actually start using the database. But since > you do not know what the state of the database is when you > issue the BEGIN, why should you care? The change might occur > before or after the BEGIN but since you have know way of knowing > which, why should it matter? > Doah! That's actually true - I'd forgotten (even though I went through the code..) that the BEGIN also turns off the autocommit. Sometimes being habitually pedantic hides the forest amongst the trees. No worries, then. ;-) -P
[sqlite] Small enhancement: BEGIN SHARED
I have a small patch that adds "BEGIN SHARED" syntax in addition to
IMMEDIATE and EXCLUSIVE. I have an application that requires a
consistent view of the data across a number of individual statements.
What is ideal is a way to simply lock the database with a SHARED lock
for the duration, but the only way to do this is run a SELECT.
Unfortunately, this means that I have a SELECT that does nothing other
than hold a lock. This smacks of programming by side effect, so killed
a bit of time on a recent flight by adding the SHARED keyword.
After digging about on the site, don't see the 'right' way to submit a
patch - although I did find the requirements for surrendering
copyright. Odd at first thought, but a damn fine idea. Therefore, as
this code was written on personal time using personal hardware, I
include the following as requested:
/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. /
Attached patch is against 3.2.8. Thoughts?
-P
diff -urN sqlite-3.2.8/src/build.c sqlite-3.2.8-shared/src/build.c
--- sqlite-3.2.8/src/build.c 2005-12-19 11:26:41.0 -0500
+++ sqlite-3.2.8-shared/src/build.c 2006-04-04 12:44:47.0 -0400
@@ -2673,8 +2673,19 @@
v = sqlite3GetVdbe(pParse);
if( !v ) return;
if( type!=TK_DEFERRED ){
+switch( type ) {
+ case TK_EXCLUSIVE:
+type = 2;
+break;
+ case TK_IMMEDIATE:
+type = 1;
+break;
+ case TK_SHARED:
+type = 0;
+break;
+}
for(i=0; inDb; i++){
- sqlite3VdbeAddOp(v, OP_Transaction, i, (type==TK_EXCLUSIVE)+1);
+ sqlite3VdbeAddOp(v, OP_Transaction, i, type);
}
}
sqlite3VdbeAddOp(v, OP_AutoCommit, 0, 0);
diff -urN sqlite-3.2.8/src/parse.y sqlite-3.2.8-shared/src/parse.y
--- sqlite-3.2.8/src/parse.y 2005-12-19 11:25:45.0 -0500
+++ sqlite-3.2.8-shared/src/parse.y 2006-04-04 12:29:17.0 -0400
@@ -120,6 +120,7 @@
transtype(A) ::= DEFERRED(X). {A = @X;}
transtype(A) ::= IMMEDIATE(X). {A = @X;}
transtype(A) ::= EXCLUSIVE(X). {A = @X;}
+transtype(A) ::= SHARED(X).{A = @X;}
cmd ::= COMMIT trans_opt. {sqlite3CommitTransaction(pParse);}
cmd ::= END trans_opt. {sqlite3CommitTransaction(pParse);}
cmd ::= ROLLBACK trans_opt.{sqlite3RollbackTransaction(pParse);}
@@ -174,7 +175,7 @@
ABORT AFTER ANALYZE ASC ATTACH BEFORE BEGIN CASCADE CAST CONFLICT
DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH PLAN QUERY KEY
- OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW STATEMENT
+ OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW SHARED STATEMENT
TEMP TRIGGER VACUUM VIEW
%ifdef SQLITE_OMIT_COMPOUND_SELECT
EXCEPT INTERSECT UNION
diff -urN sqlite-3.2.8/tool/mkkeywordhash.c sqlite-3.2.8-shared/tool/mkkeywordhash.c
--- sqlite-3.2.8/tool/mkkeywordhash.c 2005-12-19 11:25:45.0 -0500
+++ sqlite-3.2.8-shared/tool/mkkeywordhash.c 2006-04-04 12:38:35.0 -0400
@@ -209,6 +209,7 @@
{ "ROW", "TK_ROW", TRIGGER},
{ "SELECT", "TK_SELECT", ALWAYS },
{ "SET", "TK_SET", ALWAYS },
+ { "SHARED", "TK_SHARED", ALWAYS },
{ "STATEMENT","TK_STATEMENT",TRIGGER},
{ "TABLE","TK_TABLE",ALWAYS },
{ "TEMP", "TK_TEMP", ALWAYS },
Re: [sqlite] Persistent user-defined functions
Daniel Franke wrote: > Layering. Wrap sqlite3_* into your own set of functions. Create another > library, say libyourapp. Most functions will just forward the arguments to > sqlite, but others, e.g. yourapp_open_db() will not only open the database, > but also attach a couple of functions, which are also part of libyourapp. > > This additional layer won't cost too much CPU cycles and is also meaningfull > if you ever decide to switch database backends: just reimplemnt those > functions and you are back in business :) > > This is what I've done. For consistency I've also used simple macros to #define sqlite3_ functions to db_ counterparts, so there are no sqlite3_ APIs called directly from the code. The db_open also registers a couple of small C functions at the same time. Also lets us do a few neat things, like use the same base offset for binding and retrieving data as well as centralizing SQLITE_BUSY returns and error reporting. Would second Daniel's recommendation, all except for the part about switching database backends.. ;-) -P
Re: [sqlite] SQLite and Flash memory
[EMAIL PROTECTED] wrote: Has anyone experimented with putting SQLite directly on the Linux MTD flash interface? I.e. by-passing JFSS2. Would this offer any kind of significant efficiency benefit? JFFS2 is designed to wear the flash evenly, preventing you from 'drilling holes' by writing to any single block more than others and wearing it out faster. If there is a performance gain to be had by going directly to flash, you lose the robustness and leveling properties of the JFFS2. Unless of course the 'direct to flash' implementation accounts for power loss, bad blocks and wear leveling I'd be loathe to give up JFFS2. -P
Re: [sqlite] ring buffer table
Julien LEFORT wrote: Hi, I would like to implement a log table with a finite dimension, for exemple a table with 500 records, and when the last record is set in the table I would like to come back at the first tuplet and write over the previous value recorded. I think it's the way SQLite journal is implmented. Is there any way to declare this table with these properties included, so I don't have to add code to do this function? Thanks A guy on this end had a suggestion that I kicked myself for not thinking of earlier: Use a simple numeric ID that you mod by the number of records you want, automatically overwriting the oldest record when a new one comes along.. I may have to put together a smallish trigger to keep the IDs in range, perhaps a manual implementation of a simple sequence to pull a value from, then use that as the record ID. -P
Re: [sqlite] ring buffer table
Cory Nelson wrote: afaik, sqlite doesn't store row counts so count(*) causes a full table scan. On 12/23/05, Axel Mammes (gmail) <[EMAIL PROTECTED]> wrote: Wouldn´t a SELECT COUNT(*) just read the table header and get the amount of records from there? That should be faster and simpler than maintaning a separate table for the counters. Is a good question, I might have to crack into it and have a look (to know for sure what it does..) Depending on the database a single counter is a perfect spot for locking contention so many don't have a count of rows or other entities, thus I didn't really expect SQLite to have it. -Original Message- From: Paul Bohme [mailto:[EMAIL PROTECTED] Sent: Viernes, 23 de Diciembre de 2005 02:52 p.m. To: sqlite-users@sqlite.org Subject: Re: [sqlite] ring buffer table Julien LEFORT wrote: Hi, I would like to implement a log table with a finite dimension, for exemple a table with 500 records, and when the last record is set in the table I would like to come back at the first tuplet and write over the previous value recorded. I think it's the way SQLite journal is implmented. Is there any way to declare this table with these properties included, so I don't have to add code to do this function? Thanks I need something similar, so was planning a couple of simple tricks to keep the overhead low. I want to avoid "select count" queries as much as possible, so how does the following sound: - insert/delete triggers on the table I want to limit the size of - a separate table with a row that contains a single counter, that is incremented and decremented by the above triggers - a process that runs at intervals that checks the counter, and if over the limit trims the appropriate number of records Seems like a reasonable way to keep a table to a limited growth without too much of a hit on every insert. The counter table would be a serious hot spot in other databases, but SQLite's locking is simple enough that it doesn't seem like it will be a problem. -P -- Cory Nelson http://www.int64.org
Re: [sqlite] ring buffer table
Julien LEFORT wrote: Hi, I would like to implement a log table with a finite dimension, for exemple a table with 500 records, and when the last record is set in the table I would like to come back at the first tuplet and write over the previous value recorded. I think it's the way SQLite journal is implmented. Is there any way to declare this table with these properties included, so I don't have to add code to do this function? Thanks I need something similar, so was planning a couple of simple tricks to keep the overhead low. I want to avoid "select count" queries as much as possible, so how does the following sound: - insert/delete triggers on the table I want to limit the size of - a separate table with a row that contains a single counter, that is incremented and decremented by the above triggers - a process that runs at intervals that checks the counter, and if over the limit trims the appropriate number of records Seems like a reasonable way to keep a table to a limited growth without too much of a hit on every insert. The counter table would be a serious hot spot in other databases, but SQLite's locking is simple enough that it doesn't seem like it will be a problem. -P
Re: [sqlite] SQlite for Linux on the ARM processor.
Ben Clewett wrote: Dear SQLite I wish to compile SQLite for the ARM processor running Linux (ARM720T). The compilation will have to use the cross-compiler arm-linux-gcc version 2.95.2 running on Intel Linux, since the devise it's self has no compiler. Does any person have an image for ARM? If not, would any person have any advise on how this can be made? Eg, in the configure, can the arm-Linux-gcc be specified as the compiler? Thanks for any advise, Ben Clewett. The official way is to modify the Makefile (tweak the documented options to suit). Given that there are autotools, this seems a bit 'off'. Unfortunately the autotools scripts are slightly broken for doing cross-compile builds, but aren't hard to make work. I had to tweak the configure script to allow disabling of the readline library (don't have/want it for my ARM target) - I can send along a patch against 3.2.7 for this if you'd like. Then, my configure command line was: config_TARGET_CC=arm-linux-gcc config_BUILD_CC=gcc ./configure --disable-tcl --disable-readline --enable-threadsafe --enable-releasemode --host=arm-linux --prefix=/usr/local/arm You will want to modify the --prefix= option to someplace that you want the ARM version installed so your other build scripts can pick it up. -P P.S. to DRH: If you'd like I can properly package up any changes required to make SQLite more 'cross-compile friendly' without having parallel build mechanisms and pass them along. Doesn't look like any more depth needed than some simple autotools scripting tweaks so no damage to vital organs..
Re: [sqlite] Using time and date values
Brad wrote: I am looking for a simple way of using date and time values with sqlite3. I have some Ctime classes (VC++.net) which have to be stored in the database and they should be compared. If the class you're using has .Value property, or a way to easily transform the date from one format to another, you should probably store that. For instance, I like to store dates in string format, using something like "CCYYMMDDHHmmSS.SS". If I expect to need to deal with time zones, I'll store all the dates/times as GMT, or Universal time, and do the conversions when I create the datetime objects. Storing as strings is great as long as you only ever display the values. Doing range checks on a stringified format becomes prohibitive, at best. If you're going to have to filter/query on the dates, by far your best bet is a simple number that is an offset from an epoch. Classic UNIX calendar time is effective for dates in the proper range for precisions above one second (most of us) or you can do something similar to Java's date representation that measures milliseconds since the epoch. -P
[sqlite] Odd query optimization
OK, little puzzled by this one, wondering if my expectations of behavior are off. (Apologies for the longish post..) This is with 3.2.7. I have a reasonably simple 'users' table, with a single compound index (at first) on user last/first name: create table users ( id integer primary key, account_id integer not null, first_name varchar collate nocase, last_name varchar collate nocase, enabled date default CURRENT_TIMESTAMP, expires date default null, deleted integer ); create index user_name_index on users(last_name, first_name); Then ran the following query: SELECT id, first_name, last_name FROM users WHERE account_id = ? AND last_name >= ? AND last_name < ? AND deleted is null ORDER BY last_name, first_name LIMIT 25 OFFSET ? ; which performs well enough. Thinking that adding an index to account_id would only speed things up, I added the following: create index user_account_index on users(account_id); Which made the following query take 10x longer (!) to produce the same results. This is what left me scratching my head. I ran explain both before and after the addition of the index and got the following (totally different) outputs: Without user_account_index: 0|Noop|0|0| 1|Integer|25|0| 2|MustBeInt|0|0| 3|Negative|0|0| 4|MemStore|0|1| 5|Variable|4|0| 6|MustBeInt|0|0| 7|Negative|0|0| 8|MemStore|1|1| 9|Goto|0|50| 10|Integer|0|0| 11|OpenRead|0|2| 12|SetNumColumns|0|7| 13|Integer|0|0| 14|OpenRead|2|3|keyinfo(2,NOCASE,NOCASE) 15|Variable|3|0| 16|NotNull|-1|19| 17|Pop|1|0| 18|Goto|0|47| 19|MakeRecord|1|0|tt 20|MemStore|3|1| 21|Variable|2|0| 22|NotNull|-1|25| 23|Pop|1|0| 24|Goto|0|47| 25|MakeRecord|1|0|tt 26|MoveGe|2|47| 27|MemLoad|3|0| 28|IdxGE|2|47| 29|RowKey|2|0| 30|IdxIsNull|1|46| 31|IdxRowid|2|0| 32|MoveGe|0|0| 33|Column|0|1| 34|Variable|1|0| 35|Ne|361|46|collseq(BINARY) 36|Column|0|6| 37|NotNull|1|46| 38|MemIncr|1|0| 39|IfMemPos|1|41| 40|Goto|0|46| 41|MemIncr|0|47| 42|Rowid|0|0| 43|Column|0|2| 44|Column|0|3| 45|Callback|3|0| 46|Next|2|27| 47|Close|0|0| 48|Close|2|0| 49|Halt|0|0| 50|Transaction|0|0| 51|VerifyCookie|0|2| 52|Goto|0|10| 53|Noop|0|0| After adding user_account_index: 0|OpenVirtual|1|4|keyinfo(2,NOCASE,NOCASE) 1|Integer|25|0| 2|MustBeInt|0|0| 3|Negative|0|0| 4|MemStore|0|1| 5|Variable|4|0| 6|MustBeInt|0|0| 7|Negative|0|0| 8|MemStore|1|1| 9|Goto|0|64| 10|Integer|0|0| 11|OpenRead|0|2| 12|SetNumColumns|0|7| 13|Integer|0|0| 14|OpenRead|2|4|keyinfo(1,BINARY) 15|Variable|1|0| 16|NotNull|-1|19| 17|Pop|1|0| 18|Goto|0|47| 19|MakeRecord|1|0|i 20|MemStore|2|0| 21|MoveGe|2|47| 22|MemLoad|2|0| 23|IdxGE|2|47|+ 24|RowKey|2|0| 25|IdxIsNull|1|46| 26|IdxRowid|2|0| 27|MoveGe|0|0| 28|Column|0|3| 29|Variable|2|0| 30|Lt|372|46|collseq(NOCASE) 31|Column|0|3| 32|Variable|3|0| 33|Ge|372|46|collseq(NOCASE) 34|Column|0|6| 35|NotNull|1|46| 36|Rowid|0|0| 37|Column|0|2| 38|Column|0|3| 39|MakeRecord|3|0| 40|Column|0|3| 41|Column|0|2| 42|Sequence|1|0| 43|Pull|3|0| 44|MakeRecord|4|0| 45|IdxInsert|1|0| 46|Next|2|22| 47|Close|0|0| 48|Close|2|0| 49|Sort|1|63| 50|MemIncr|1|0| 51|IfMemPos|1|53| 52|Goto|0|62| 53|MemIncr|0|63| 54|Column|1|3| 55|Integer|3|0| 56|Pull|1|0| 57|Column|-1|0| 58|Column|-2|1| 59|Column|-3|2| 60|Callback|3|0| 61|Pop|2|0| 62|Next|1|50| 63|Halt|0|0| 64|Transaction|0|0| 65|VerifyCookie|0|3| 66|Goto|0|10| 67|Noop|0|0| An "explain query plan" of the second also gave me something interesting: 0|0|TABLE users WITH INDEX user_account_index Does this mean it's only able to use one index, so the addition of a separate index knocks the most useful one out of the running? Anyone have any thoughts on why adding an index nailed the plan/performance so remarkably? Thanks, -P
Re: [sqlite]cross compiling with powerpc
Julien LEFORT wrote: Hi, I've been struggling for few hours trying to run the configure script the right way so I can cross compile sqlite3 for a powerpc target. I guess it is only a problem of options I pass into the configure script. here is the command I pass: CC=powerpc-linux-gcc CXX=powerpc-linux-g++ BUILD_CC=gcc ../configure --host=i686-linux --target=powerpc-linux --disable-shared --disable-tcl and the error I get: Version number set to 3002007 checking for i686-linux-gcc... (cached) powerpc-linux-gcc checking whether we are using the GNU C compiler... (cached) yes checking whether powerpc-linux-gcc accepts -g... (cached) yes checking for powerpc-linux-gcc option to accept ANSI C... (cached) none needed configure: error: unable to find a compiler for building build tools Anyone has any idea? Thanks Julien For building ARM I use the following: config_TARGET_CC=arm-linux-gcc config_BUILD_CC=gcc ./configure --disable-tcl --disable-readline --enable-threadsafe --enable-releasemode --host=arm-linux --prefix=`pwd`/install I found that it was choking on the config_TARGET_CC and config_BUILD_CC variables, so set those explicitly. (I also added a check to be able to fully kill readline as I don't have/want it for my target.) -P
