[sqlite] Table creation with open cursors
Hi all, I understand that with 3.1 it's no longer possible to create tables while cursors are open on any table in the database. The source suggests that this is due entirely to the mechanics of auto-vacuum mode: http://www.srcdoc.com/sqlite_3.2.2/btree_8c-source.html#l04716. So I'm curious as to why this restriction is applied indiscriminately, even if auto-vacuum mode is not enabled. Is there another reason for it? Thanks, Blake
RE: [sqlite] Locking
> Isolation in SQLite is SERIALIZABLE. Note that SERIALIZABLE > implies that locking can be no more fine-grained than table-level. > You can obtain table-level locking in SQLite now. Just put each > table in a separate database file and ATTACH as many tables to > your connection as you require. Yes, I did think of that, but it's a bit messy and things like relationships no longer work. I have a question on that also, would attaching databases make queries quite a bit slower? Does SQLite maintain a cache of connections for each of these 'ATTACH'es, or on each query, does it have to make a connection and retrieve info then close it again? That would be quite an overhead would it not? > Beginning with version 3.3.0, you will be able to configure SQLite > so that multiple connections running in the same thread will be > able to select READ UNCOMMITED isolation relative to one another. This sounds really interesting, I think it would help some of our tasks but we do have multiple threads accessing the database abstraction layer so those areas wouldn't be able to use this which is a shame ... I would interested if improvements in concurrency is an ongoing thing with more and more support being added as versions get released? Maybe we could assist development in that area possibly if required, but as it may be a core area you would rather control this part of development yourself. What are your thoughts? Thanks for your answers and I must say thanks a lot for your hard work in the development in SQLite ... I have done *a lot* of investigation in databases for my testing and there is a lot of *rubbish* out there, there is a lot of *expensive* solutions, and a lot of *slow* solutions, SQLite is by far one of the quickest, easiest to use and integrate, excellently documented with good user support (through these lists), small/light, and its *free*!! Well done and Merry Christmas to you.
RE: [sqlite] Locking
> Does your flat file support ACID transactions? That´s the killer feature fo > my app. I want to store financial transactions and I don´t trust normal flat > files. No it isnt acid, its not got critical information in it but it does need very fast read access and write access that doesnt block reads. But this is one of the reasons why we are investigating SQLite, we want a bit more resiliant data storage but also the flexibility that a quick query engine will give us ... Searching for a specific word in all of the records (300k) isnt very quick but with SQLite would be much quicker and more flexible.
RE: [sqlite] ring buffer table
But I think this would:
CREATE TABLE ring_buffer (key INTEGER PRIMARY KEY AUTOINCREMENT, stuff TEXT);
CREATE TRIGGER delete_tail AFTER INSERT ON ring_buffer
BEGIN
DELETE FROM ring_buffer WHERE key%10=NEW.key%10 AND key!=NEW.key;
END;
INSERT INTO ring_buffer (stuff) VALUES('Stuff 1');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 2');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 3');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 4');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 5');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 6');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 7');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 8');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 9');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 10');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 11');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 12');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 13');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 15');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 16');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 17');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 18');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 19');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 20');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 21');
SELECT * FROM ring_buffer;
11|Stuff 11
12|Stuff 12
13|Stuff 13
14|Stuff 15
15|Stuff 16
16|Stuff 17
17|Stuff 18
18|Stuff 19
19|Stuff 20
20|Stuff 21
> -Original Message-
> From: Richard B. Boulton [mailto:[EMAIL PROTECTED]
> Sent: 24 December 2005 10:44
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] ring buffer table
>
>
> oops, just realised that doesn't work when you get further than 10.
>
>
> > -Original Message-
> > From: Richard B. Boulton [mailto:[EMAIL PROTECTED]
> > Sent: 24 December 2005 10:37
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] ring buffer table
> >
> >
> > If you used an INTEGER PRIMARY KEY AUTOINCREMENT could you
> > use a simple trigger and a modulus of the newly inserted rowid?
> >
> > e.g. for a dimension of 10:
> >
> > CREATE TABLE ring_buffer (key INTEGER PRIMARY KEY
> > AUTOINCREMENT, stuff TEXT);
> > CREATE TRIGGER delete_tail AFTER INSERT ON ring_buffer
> > BEGIN
> > DELETE FROM ring_buffer WHERE key=NEW.key%10 AND key!=NEW.key;
> > END;
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 1');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 2');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 3');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 4');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 5');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 6');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 7');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 8');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 9');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 10');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 11');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 12');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 13');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 14');
> > SELECT * FROM ring_buffer;
> >
> > 5|Stuff 5
> > 6|Stuff 6
> > 7|Stuff 7
> > 8|Stuff 8
> > 9|Stuff 9
> > 10|Stuff 10
> > 11|Stuff 11
> > 12|Stuff 12
> > 13|Stuff 13
> > 14|Stuff 14
> >
> > > 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
> > >
> > > Julien
> > >
> > >
> > > Julien LEFORT
> > > Com2gether
> > > 16, Allée de la verte vallée
> > > 14000 CAEN
> > > tel : +33 2 31 15 61 42
> > > fax : +33 2 31 53 76 14
> > >
> >
>
RE: [sqlite] ring buffer table
oops, just realised that doesn't work when you get further than 10.
> -Original Message-
> From: Richard B. Boulton [mailto:[EMAIL PROTECTED]
> Sent: 24 December 2005 10:37
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] ring buffer table
>
>
> If you used an INTEGER PRIMARY KEY AUTOINCREMENT could you
> use a simple trigger and a modulus of the newly inserted rowid?
>
> e.g. for a dimension of 10:
>
> CREATE TABLE ring_buffer (key INTEGER PRIMARY KEY
> AUTOINCREMENT, stuff TEXT);
> CREATE TRIGGER delete_tail AFTER INSERT ON ring_buffer
> BEGIN
> DELETE FROM ring_buffer WHERE key=NEW.key%10 AND key!=NEW.key;
> END;
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 1');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 2');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 3');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 4');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 5');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 6');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 7');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 8');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 9');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 10');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 11');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 12');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 13');
> INSERT INTO ring_buffer (stuff) VALUES('Stuff 14');
> SELECT * FROM ring_buffer;
>
> 5|Stuff 5
> 6|Stuff 6
> 7|Stuff 7
> 8|Stuff 8
> 9|Stuff 9
> 10|Stuff 10
> 11|Stuff 11
> 12|Stuff 12
> 13|Stuff 13
> 14|Stuff 14
>
> > 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
> >
> > Julien
> >
> >
> > Julien LEFORT
> > Com2gether
> > 16, Allée de la verte vallée
> > 14000 CAEN
> > tel : +33 2 31 15 61 42
> > fax : +33 2 31 53 76 14
> >
>
RE: [sqlite] ring buffer table
If you used an INTEGER PRIMARY KEY AUTOINCREMENT could you use a simple trigger
and a modulus of the newly inserted rowid?
e.g. for a dimension of 10:
CREATE TABLE ring_buffer (key INTEGER PRIMARY KEY AUTOINCREMENT, stuff TEXT);
CREATE TRIGGER delete_tail AFTER INSERT ON ring_buffer
BEGIN
DELETE FROM ring_buffer WHERE key=NEW.key%10 AND key!=NEW.key;
END;
INSERT INTO ring_buffer (stuff) VALUES('Stuff 1');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 2');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 3');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 4');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 5');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 6');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 7');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 8');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 9');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 10');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 11');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 12');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 13');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 14');
SELECT * FROM ring_buffer;
5|Stuff 5
6|Stuff 6
7|Stuff 7
8|Stuff 8
9|Stuff 9
10|Stuff 10
11|Stuff 11
12|Stuff 12
13|Stuff 13
14|Stuff 14
> 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
>
> Julien
>
>
> Julien LEFORT
> Com2gether
> 16, Allée de la verte vallée
> 14000 CAEN
> tel : +33 2 31 15 61 42
> fax : +33 2 31 53 76 14
>
