Re: [sqlite] Cache design using sqlite3...
On 7/12/2011 6:33 AM, Simon Slavin wrote: 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. This is such a simple, yet elegant, solution! Thanks, Simon. Best Regards, Mohit. 7/12/2011 | 9:35 PM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cache design using sqlite3...
On 12/07/2011 12:25 PM, Dan Kennedy wrote: On 12/07/2011 01:35 AM, Alejandro Martínez wrote: 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. 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cache design using sqlite3...
On 12/07/2011 01:35 AM, Alejandro Martínez wrote: 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. 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cache design using sqlite3...
On 6 Dec 2011, at 9:58pm, Jos Groot Lipman wrote: > At some point (idle?) the readers should close the database connection and > you must make sure any new connection starts to the new database? 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cache design using sqlite3...
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 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alejandro Martínez Sent: dinsdag 6 december 2011 20:56 To: General Discussion of SQLite Database Subject: Re: [sqlite] Cache design using sqlite3... 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 new data? On Tue, Dec 6, 2011 at 5:23 PM, Jos Groot Lipman wrote: > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] 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@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cache design using sqlite3...
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 new data? On Tue, Dec 6, 2011 at 5:23 PM, Jos Groot Lipman wrote: > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] 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@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cache design using sqlite3...
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 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@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
