[sqlite] Adding SQL commands
I would like to add some commands to SQLite to make my SQL(ite) programming life easier!!! The commands are: IF - e.g IF ((select count(*) from foo) = 100) select "good"; ELSE select "bad"; END EXISTS - e.g IF EXISTS(select * from sqlite_master where name = 'foo') DROP TABLE foo; END local variables - e.g. DECLARE @var TEXT; select @var = name from foo; I am not familiar with how compilers work but would be willing to write the "c" code to make these commands work. Does anyone have a simple way to add a command using the lemon parser??? I find the documentation hard to understand and I would like to add these command s using the c++ compiler from Visual Studio.Net B.Thomas This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par un autre moyen.
Re: [sqlite] Adding SQL commands
Hi Basil, The first use of IF is same syntax as the case statement so I don't think it is required and as IF isn't SQL92 I doubt it will be included so your command is replaced by case when (select count(*) from foo) = 100 then 'good' else 'bad' end I like the idea of exist but then again you can do this (in a query) count where (Select count(*) from foo where col1 ='a') = 1 but what I really think you are talking about is a command language like TSQL for Microsoft or P/SQL for Oracle (is it P/SQL what ever?) At the moment you can't do that type of thing is SQLite. I think the reason here is the it is a Database system that is included in your application and therefore you application can make these decisions far better that a SQL language Greg O Don't for get www.SQL-Scripts.Com - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 14, 2004 8:03 AM Subject: [sqlite] Adding SQL commands I would like to add some commands to SQLite to make my SQL(ite) programming life easier!!! The commands are: IF - e.g IF ((select count(*) from foo) = 100) select "good"; ELSE select "bad"; END EXISTS - e.g IF EXISTS(select * from sqlite_master where name = 'foo') DROP TABLE foo; END local variables - e.g. DECLARE @var TEXT; select @var = name from foo; I am not familiar with how compilers work but would be willing to write the "c" code to make these commands work. Does anyone have a simple way to add a command using the lemon parser??? I find the documentation hard to understand and I would like to add these command s using the c++ compiler from Visual Studio.Net B.Thomas This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par un autre moyen.
[sqlite] Concurrency, MVCC
How feasible would it be to add support for higher concurrency to SQLite, especially via MVCC? (If it turns out to be feasible and desirable, I'd be willing to work on it in my free time.) What I would really like in SQLite would be: - Good concurrency, preferably similar or better to that of nsv/tsv (see below). - MVCC (multi-version concurrency control) model preferred, as in PostgreSQL and Oracle. How feasible is that? What designs have been or should be considered for it? How much work does this seem to be? Does it become any easier if it's considered for in-memory databases only, rather than both in-memory and on-disk? After some searching around I found these previous proposals or discussions of increasing concurrency in SQLite: http://www.sqlite.org/concurrency.html http://www.sqlite.org/cvstrac/wiki?p=BlueSky http://www.sqlite.org/cvstrac/attach_get/100/shadow.pdf http://citeseer.ist.psu.edu/131901.html http://www.mail-archive.com/cgi-bin/htsearch?config=sqlite-users_sqlite_org===concurrency Many of the suggestions in Dr. Hipp's 2003-11-22 concurrency draft sound very useful, especially blocking (rather than polling) locks. (In fact, I was surprised to learn that SQLite does NOT block on locks now.) But, that draft never mentions MVCC at all. Why not? Since MVCC both gives better concurrency and is friendlier to use than pessimistic locking models, I was surprised not to at least see it mentioned. Is an MVCC implementation thought to be too complicated? Or? Doug Currie's <[EMAIL PROTECTED]> "Shadow Paging" design sounds promising. Unfortunately, I have not been able to download the referenced papers at all (where can I get them?), but as far as I can tell, it seems to be describing a system with the usual Oracle/PostgreSQL MVCC semantics, EXCEPT of course that Currie proposes that each Write transaction must take a lock on the database as a whole. But, other than the locking granularity, in what way is Currie's Shadow Paging design the same or different from PostgreSQL's MVCC implementation, both in terms of user-visible transaction semantics, and the underlying implementation? I believe PostgreSQL basically marks each row with a transaction id, and keeps track of whether each transaction id is in progress, committed, or aborted. Here are a few links about that: http://developer.postgresql.org/pdf/transactions.pdf http://openacs.org/forums/message-view?message_id=176198 Since Currie's design has only one db-wide write lock, it is semantically equivalent to PostgreSQL's "serializable" isolation level, correct? How could this be extended to support table locking and PostgreSQL's default "read committed" isolation level? Would the smallest locking granularity possible in Currie's design be one page of rows, however many rows that happens to be? The one process, many threads aspect of Currie's design sounds just fine to me. The one write lock for the whole database, on the other hand, could be quite limiting. How much more difficult would it be to add table locks to the design? It would also be a nice bonus if the design at least contemplates how to add row locks (or locks for pages of rows) in the future, but my guess is that table locks would be good enough in practice. Currie's design also seems to defer writing any data to disk until the transaction commits, which seems odd to me. I did not follow many of the details of that design so I'm probably missing something here, but since most write transactions commit rather than abort, in any sort of MVCC model wouldn't it be better to write data to disk earlier rather than later? I'm pretty sure that's what both Oracle and PostgreSQL do. My particular interest in SQLite: Now that I've asked lots of questions above, I'll describe some of the real-world use cases that got me thinking about this, in case it helps clarify how and why I'm interested in a high-concurrency SQLite: A while back I wrote a high-performance multi-threaded application that basically just accepted data requests, used various ugly low level proprietary C APIs to fetch data from remote servers, and then organized the data and fed it back to the client application as simple rows of CSV-style text. Using all those low-level C APIs meant tracking lots of somewhat complicated housekeeping data. If my application ever crashed all housekeeping data instantly became worthless anyway, so I definitely didn't want to store it persistently in Oracle or PostgreSQL; for both simplicity and performance, I wanted it in-memory only. So in my case, I used AOLserver's nsv's for this. (The Tcl Thread Extension has "tsv" which is just like "nsv", except better.) Nsvs are basically just associative arrays for storing key/value pairs (like Tcl arrays or Perl hashes), but nsv operations are both atomic and highly concurrent, as they were designed for inter-thread communication in AOLserver. (Mutex locking is
[sqlite] Effectiveness of PRAGMA integrity_check;
G'day,
I'm trying to write some defensive code that is able to recover from
database corruption. The idea is that if a disk fails and a database
becomes corrupt it can be detected and synchronised from a backup copy.
To this end, I've just been trying to write a function that returns true
only when it is sure the database is ok. I use PRAGMA integrity check; and
compare the returned string with "ok". When I tried this with a few random
database changes, though, I had a hard time trying to get the corruption
to trigger. I did the following:
CREATE TABLE foo(bar);
INSERT INTO foo VALUES("bar");
I then went in with a text editor and started modifying the bar record. I
changed "bar" to "car", but the change was not detected. I started
modifying characters to the left and right of the "car" string, but still
no corruption. I was able to get corruption to be detected when I
truncated the file.
Can I take it from this behaviour that there isn't any checksum checking
going on apart from headers and the BTrees themselves? Will the
integrity_check at least guarantee me that I won't at some later stage get
an SQLITE_CORRUPT return?
Benjamin.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
