[sqlite] Compression
Hello all, First of all, allow me to wish everyone a Happy New Year and I hope it'll be a good one for all. My question is (and I've raised this topic back in September, but didn't get back to it since), does anyone have a free/commercial add-on for SQLite v3 to perform on-the-fly compression/decompression of data, preferably on a field level (compress just one of the fields, not the whole table)? Thank you, Dennis // MCP, MCSD // ASP Developer Member // Software for animal shelters! // www.smartpethealth.com // www.amazingfiles.com
Re: [sqlite] Precompiled statements in the TCL interface
The TCL interface to SQLite does not currently support precompiled statements. Each SQL statement executed is compiled for that one execution. If the same statement is executed multiple times, it is compiled multiple times. This note considers ways of addressing that deficiency. Incidentally my apsw wrapper for Python also does the same thing. Where is the actual bottleneck - is it the string parsing side or the actual logistics underneath? The major reason why I couldn't do any form of caching etc is because apsw allows multiple SQL statements. Trying to keep around prepared statements gets very difficult if you don't know how long each statement is, and it would be somewhat silly to have another SQL parser in my code to figure that out :-) All three of the approaches you mention are used in various environments by regular expression libraries, which also have the requirements for a virtual machine to be built and executed. In the third approach, the TCL interface caches precompiled statements internally and simply reuses them when the same SQL strings are presented for execution. Legacy programs would require no changes to take advantage of precompiled statements - they just magically run faster. The issue with the third approach is deciding when to clear the precompiled statement cache. Precompiled statements use memory and we do not want them to hang around forever. Ideas for how to deal with this problem include: Most of the regex libraries have a #define and store that many. I have often seen it as high as 100. (4) Create a new method on the database object that enables and clears the precompiled statement cache. This would require minimal changes to legacy code. Perhaps the enabling statement could also set the value for N in (2) above. This is what I would prefer since all languages can then use it. The preferred way of working would be that sqlite3_prepare works as before, but internally it recognises the string and looks it up in the cache. That means that the sqlite library gets to decide when it is most effective to do the caching, and that decision can be improved over time. This is also the simplest way of working when there could be multiple statements in the string presented. Roger
Re: [sqlite] table locked?
On December 31, 2004 06:37 am, D. Richard Hipp wrote:
> Thomas Fjellstrom wrote:
> > It is possible for result items to sit around un finalized for a period,
> > would that cause sqlite to keep a table locked?
>
> In version 3.0.5 and earlier, the commit did not occur until you
> ran sqlite3_finalize(). Beginning with version 3.0.6, the commit
> occurs on the last call to sqlite3_step() - the one that returns
> SQLITE_DONE.
>
> Even in version 3.0.8, if you have a query running on a table
> that has not yet returned SQLITE_DONE, then you are prohibited
> from making changes to that table.
So something as simple as: (riped out of delete_key())
MadSQLiteResult *res = conf->db->preparef("SELECT id FROM conf_key WHERE
parent_id=%i;", kid);
if(!res)
return false;
while(res->step() == MadDB_ROW) {
if(!delete_key(res->columnInt(0)))
return false;
}
delete res;
conf->db->queryf("DELETE FROM conf_key WHERE id=%i", kid);
will fail? :(
Thanks, and sorry for the half written duplicate :(
--
Thomas Fjellstrom
[EMAIL PROTECTED]
http://strangesoft.net
Re: [sqlite] table locked?
On December 31, 2004 06:37 am, D. Richard Hipp wrote: > Thomas Fjellstrom wrote: > > It is possible for result items to sit around un finalized for a period, > > would that cause sqlite to keep a table locked? > > In version 3.0.5 and earlier, the commit did not occur until you > ran sqlite3_finalize(). Beginning with version 3.0.6, the commit > occurs on the last call to sqlite3_step() - the one that returns > SQLITE_DONE. > > Even in version 3.0.8, if you have a query running on a table > that has not yet returned SQLITE_DONE, then you are prohibited > from making changes to that table. so something as simple as: -- Thomas Fjellstrom [EMAIL PROTECTED] http://strangesoft.net
Re: [sqlite] Object Relational Mapping Tools with Sqlite
MyGeneration is a template based code generator that runs in the Win32 environment. It is 100% free and supports SQLite. There is not yet a set of templates that generate code for a specific SQLite supported framework, but the Meta-Data API supports SQLite, so writing your own templates should be a fairly simple task. http://www.mygenerationsoftware.com/ > On Thu, 30 Dec 2004 10:41:50 -0800, [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: >> Has anyone been successful using an object relational mapping tool with >> Sqlite? I'm looking for such a tool that has a non-commercial free >> license. > > For what language? > > > Kirk Haines >
Re: [sqlite] Precompiled statements in the TCL interface
On Fri, 2004-12-31 at 08:30 -0500, D. Richard Hipp wrote: > The issue with the third approach is deciding when to clear the > precompiled statement cache. Precompiled statements use memory > and we do not want them to hang around forever. Why not? Programs that "generate" SQL are often-foolish. Penalize them. Perhaps that's too draconian. Is it possible for the compile command to tell anything about what's calling it (filename, line number; like perl's caller() function). If so, simply reserve one cache entry per exec per file+line.
Re: [sqlite] table locked?
Thomas Fjellstrom wrote: It is possible for result items to sit around un finalized for a period, would that cause sqlite to keep a table locked? In version 3.0.5 and earlier, the commit did not occur until you ran sqlite3_finalize(). Beginning with version 3.0.6, the commit occurs on the last call to sqlite3_step() - the one that returns SQLITE_DONE. Even in version 3.0.8, if you have a query running on a table that has not yet returned SQLITE_DONE, then you are prohibited from making changes to that table. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Precompiled statements in the TCL interface
The TCL interface to SQLite does not currently support precompiled statements. Each SQL statement executed is compiled for that one execution. If the same statement is executed multiple times, it is compiled multiple times. This note considers ways of addressing that deficiency. The most obvious approach is to add to the database object a "compile" method that returns a new object that is the precompiled statement. For example: set stmt [db compile $sql] $stmt eval $stmt finalize An extension of this idea is to allow the precompiled statement to also be used in place of the SQL argument on the "eval" method of the main database object. Like this: set stmt [db compile $sql] db eval $stmt The two techniques above can both be implemented at the same time, allowing the programmer to use whichever seems more convenient. The third approach (and the one that I currently prefer) uses no API change at all and is completely transparent to the programmer. In the third approach, the TCL interface caches precompiled statements internally and simply reuses them when the same SQL strings are presented for execution. Legacy programs would require no changes to take advantage of precompiled statements - they just magically run faster. The issue with the third approach is deciding when to clear the precompiled statement cache. Precompiled statements use memory and we do not want them to hang around forever. Ideas for how to deal with this problem include: (1) Clear the cache at each COMMIT. The primary use of precompiled statements is to do multiple INSERTs without having to recompile the INSERT statement multiple times. But multiple INSERTs are also usually done inside a transaction. So when the transaction commits, it usually means we are done with the INSERTs. (2) Store the N most recent statements where N is a constant in the range of 5 to 10. Here again, the assumption is that precompiled statements are most helpful inside tight loops. (3) Only cache statements that have named parameters. The idea here is that statements without parameters are rarely executed more than once (why would you want to do the same thing twice?) so do not bother filling the cache with statements that will never be reused. (4) Create a new method on the database object that enables and clears the precompiled statement cache. This would require minimal changes to legacy code. Perhaps the enabling statement could also set the value for N in (2) above. An actually implementation would probably implement some combination of the above ideas. The question is, which combination. The third approach might also be combined with the first two. An implementation of all three would provide automatic caching in the common case so that applications could take advantage of the cache with no programmer intervention. But an explicit precompile of the first two techniques would be available to programmers who want more control. Thoughts? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] table locked?
This has really got me confused. I looked at the locking docs on the sqlite website, searched the mailing list for answers, but nothing seems to match. The error message I get says a table is locked, but the error id is just SQLITE_ERROR (0). I had initially figured it was because I didn't handle SQLITE_BUSY correctly (at all really), but now it should be "enough"... I get the following debug info from my program: erase prepare: "BEGIN TRANSACTION;" step: DONE delete_key(14) prepare: "SELECT id FROM conf_key WHERE parent_id=14;" step: ROW delete_key(15) prepare: "SELECT id FROM conf_key WHERE parent_id=15;" step: DONE prepare: "DELETE FROM conf_key WHERE id=14;" step: ERROR (1) failed key: database table is locked prepare: "ROLLBACK;" step: ERROR (1) Now, a little background, I use sqlite3 through a little C++ wrapper I wrote, and on top of that is a registry like config/settings api. The text above starts with a call to ConfKey.erase() which goes and erases its key item, its children items, data items, and data_opt items (lets me specify the keys predefined allowable values) It is possible for result items to sit around un finalized for a period, would that cause sqlite to keep a table locked? At that point, the only "writing" being done is the deleting the keys above. all the other queries were all just SELECTS (and a BEGIN/COMMIT combo right at the start of my program) Thanks :) -- Thomas Fjellstrom [EMAIL PROTECTED] http://strangesoft.net
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] Advice needed for a new group member
Op vrijdag 31 december 2004 02:23, schreef mike cariotoglou: > In the home page of www.sqlite.org there is a "contrib" link in the right > upper area. > It points to http://www.sqlite.org/contrib Thanks Bert
