Re: [sqlite] Problem installing TCL bindings
On Sat, 8 Apr 2006, Miguel Bazdresch wrote: [...] It actually needs to say load /usr/lib/tcl8.4/sqlite3/libtclsqlite3.so Sqlite3 [...] Hello Miguel, if everything works correctly and the Tcl install script of SQLite3 did its job right, which it usually does, it should be unnecessary to load the library manually by specifying the full path. Instead you should be able to type package require sqlite3 in your Tcl shell and it should automagically be located and loaded. cu, Thomas
Re: [sqlite] Problem installing TCL bindings
On 4/7/06, Miguel Bazdresch <[EMAIL PROTECTED]> wrote: > 2. For some reason, the libraries needed for tcl interaction > (libtclsqlite3.so) are not installed by 'make install' I finally determined they *are* installed, to /usr/lib/tcl8.4/sqlite3. A mention of this somewhere on the website or README would be nice. > 3. The quickstart.html page is wrong. It states one needs to do: > >load /usr/lib/tclsqlite3.so Sqlite3 > > when actually one needs to do: > > load /usr/lib/libtclsqlite3.so Sqlite3 It actually needs to say load /usr/lib/tcl8.4/sqlite3/libtclsqlite3.so Sqlite3 It took me a few hours to figure this out. Please consider improving the documentation so the installation directory is clearer and the instructions more precise. -- Miguel Bazdresch
Re: [sqlite] Current julian day number
On Sun, 9 Apr 2006, ThomasChust wrote: [...] is there an elegant way to make the current julian day number the default value for a column in SQLite3? [...] Hello, sorry for the last post -- I figured out how it works myself: CREATE TABLE time(time REAL DEFAULT (julianday(current_timestamp))); I just didn't think about putting an additional pair of parentheses around the DEFAULT value, because it's normally not necessary. cu, Thomas
[sqlite] Current julian day number
Hello, is there an elegant way to make the current julian day number the default value for a column in SQLite3? When I try to create a table like this CREATE TABLE time(time REAL DEFAULT julianday(current_timestamp)); I get of course a syntax error, because the calling of functions in the DEFAULT value is apparently not allowed. cu, Thomas
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
Re: [sqlite] Small enhancement: BEGIN SHARED
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? -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Re: Small enhancement: BEGIN SHARED
Paul Bohme 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. 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. I'm not sure why you want to lock immediately. The very first of those "individual statements" will set a SHARED lock. All subsequent statements will see the same data as the first one. You get consistent view of the data anyway. Igor Tandetnik
Re: [sqlite] Efficient Query Processing
Martin Pfeifle <[EMAIL PROTECTED]> wrote: > Is it also possible to force SQLite to retrieve all records according > to their pageid. Not as currently implemented. -- D. Richard Hipp <[EMAIL PROTECTED]>
[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