I'm trying to use sqlite3 as a cache layer for queries on other
database. Actually, replace an existing layer cache which is very
adhoc and propietary (and that sucks), and i'd like to replace it with
something peer reviewed / open source, etc like sqlite.

So... I would like to have a process which periodically gets some
tables from a master database and stores it in a sqlite database.

Then some other processes would access the sqlite database to read and
use such cached data.

So its the tipical scenario of "one writer and many readers".

So the question is... regarding the locking mechanism...

What would be better during the "writer" process's "refresh" cycle for
each table:

1. Create a new table, load the new content (lots of inserts), and
then replace the old table by dropping the old one and renaming the
new one within a transaction.
or
2. Drop the table and then insert the new content within a transaction.

The objective is to block readers the least ammount of time possible
(probably using read uncommited), and of course, not cause writer
starvation.

I don't understand well from the documentation how "read uncommited"
performs when the master table is modified (as in option 1).

And yes, i've considered using a wal journal, but the problem is i'm
scared of the wal file growing out of control as i can't completely
guarantee that ALL prepared statements would be "reset" at the same
time and thus reach a checkpoint. The reason for this is that i must
satisfy a certain api, beyond my control :S for backwards
compatibility reasons.

Any advice is welcome.

Thank you!

Alejandro

-----Original Message-----
From: sqlite-users-bounces*******
[mailto:sqlite-users-bounces*******] On Behalf Of Alejandro Martínez
Sent: dinsdag 6 december 2011 19:35
To: General Discussion of SQLite Database
Subject: [sqlite] Cache design using sqlite3...

I'm trying to use sqlite3 as a cache layer for queries on other database.
Actually, replace an existing layer cache which is very adhoc and propietary
(and that sucks), and i'd like to replace it with something peer reviewed /
open source, etc like sqlite.

---
How much data are we talking here?

Have you considered refreshing by filling a completely separate database and
just switching the databases?

_______________________________________________
sqlite-users mailing list
sqlite-users*******:8080/cgi-bin/mailman/listinfo/sqlite-users

It would be a pretty small ammount of data. Say... 10Mb at most.

How would that be? I mean, the "switching the databases".

It would need to be transparent to the readers. How would they see the newdata?

sqlite-users mailing list
sqlite-users*******:8080/cgi-bin/mailman/listinfo/sqlite-users

At some point (idle?) the readers should close the database connection and
you must make sure any new connection starts to the new database?
(just brainstorming)

Jos

Right.  One way to do it is to have a table in the database used to point to the next database.  Normally that table has zero rows in it.  You can check it with

SELECT COUNT(*) FROM databaseObsolete

when an entry does appear in it the count switches to 1.  If your operating system supports aliases/shortcuts/links you can change one of them to point to whatever file is the current database file.

Simon.

If you run the command:

   PRAGMA wal_checkpoint = restart

Then it blocks (calls the busy-handler) until all frames in the
WAL file have been checkpointed and all readers are reading the
latest database snapshot from the database file. This guarantees
that the next client to write to the database starts writing to
the start of the WAL file.

Other database writers are blocked while the "PRAGMA wal_checkpoint"
is running, but readers are not.

http://www.sqlite.org/pragma.html#pragma_wal_checkpoint

This is such a simple, yet elegant, solution!  Thanks, Simon.

Best Regards,
Mohit.
7/12/2011 | 9:35 PM.