[sqlite] Effectiveness of PRAGMA integrity_check;

2004-04-13 Thread ben . carlyle
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]



[sqlite] Concurrency, MVCC

2004-04-13 Thread Andrew Piskorski
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 

Re: [sqlite] Adding SQL commands

2004-04-13 Thread Greg Obleshchuk
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] Adding SQL commands

2004-04-13 Thread basil . thomas
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.