Re: [sqlite] Question about in memory db's
Wow! This is quite cool. Of course, it prompts another question or two: Instead of attaching the database to a file, is it possible to attach to a TCP socket? If so, then if there are two instances of SQLite running in-memory databases (with identical schema) in two separate processes (same or different computers), is there already a similar method to copy the databases from one process to another (either by a pull model or a push model)? Or does one have to set up the plumbing and the handshaking first? If this is possible, then it may be possible to get some form of "high-availability" with a dozen lines of SQLite code. THAT appeals to my desire for simplicity (and perhaps to my laziness). Curiously, Jay Godse - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, March 12, 2004 7:37 PM Subject: Re: [sqlite] Question about in memory db's > Keith Herold wrote: > > Is there a fast way to dump the in-memory db to disk, *and* periodically > > reload the file into an in-memory db? > > > > The ATTACH command can be used for this. > > Suppose you do an sqlite_open() on the in-memory database > and you want to transfer the complete contents of an > in-memory table XYZ to an on-disk database named "abc.db" > You can do something like this: > > ATTACH 'abc.db' AS external; > BEGIN; > DELETE FROM external.xyz; > INSERT INTO external.xyz SELECT * FROM xyz; > COMMIT; > DETACH external; > > The same technique works in reverse to load an in-memory > table from disk: > > ATTACH 'abc.db' AS external; > BEGIN; > DELETE FROM xyz; > INSERT INTO xyz SELECT * FROM external.xyz; > COMMIT; > DETACH external; > > Use WHERE clauses creatively if you only want to transfer > part of the data. > > Note that it is not necessary to ATTACH and DETACH every > time you want to do this. If you are always using the > same external database file, you can just ATTACH it once > when you open the in-memory database and it will always > be there for you. > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Question about in memory db's
Keith Herold wrote: Well that's kinda nifty! Does the ATTACH lock the external file for as long as it is ATTACHed or only when transactions are active? If it is only locked during updates, this is probably the way I will do it, since I can sacrifice a little bit of time for periodic writes to the DB file. The ATTACHed file is only locked for the duration of transactions that involve the ATTACHed file. So if you have a transaction on just the in-memory database (or on another ATTACHed database) the ATTACHed file never gets locked. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Question about in memory db's
Well that's kinda nifty! Does the ATTACH lock the external file for as long as it is ATTACHed or only when transactions are active? If it is only locked during updates, this is probably the way I will do it, since I can sacrifice a little bit of time for periodic writes to the DB file. --Keith > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Friday, March 12, 2004 4:37 PM > Cc: [EMAIL PROTECTED] > Subject: Re: [sqlite] Question about in memory db's > > > Keith Herold wrote: > > Is there a fast way to dump the in-memory db to disk, > *and* periodically > reload the file into an in-memory db? > > > The ATTACH command can be used for this. > > Suppose you do an sqlite_open() on the in-memory database > and you want to transfer the complete contents of an > in-memory table XYZ to an on-disk database named "abc.db" > You can do something like this: > > ATTACH 'abc.db' AS external; > BEGIN; > DELETE FROM external.xyz; > INSERT INTO external.xyz SELECT * FROM xyz; > COMMIT; > DETACH external; > > The same technique works in reverse to load an in-memory > table from disk: > > ATTACH 'abc.db' AS external; > BEGIN; > DELETE FROM xyz; > INSERT INTO xyz SELECT * FROM external.xyz; > COMMIT; > DETACH external; > > Use WHERE clauses creatively if you only want to transfer > part of the data. > > Note that it is not necessary to ATTACH and DETACH every > time you want to do this. If you are always using the > same external database file, you can just ATTACH it once > when you open the in-memory database and it will always > be there for you. > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Question about in memory db's
Keith Herold wrote: > Is there a fast way to dump the in-memory db to disk, *and* periodically > reload the file into an in-memory db? > The ATTACH command can be used for this. Suppose you do an sqlite_open() on the in-memory database and you want to transfer the complete contents of an in-memory table XYZ to an on-disk database named "abc.db" You can do something like this: ATTACH 'abc.db' AS external; BEGIN; DELETE FROM external.xyz; INSERT INTO external.xyz SELECT * FROM xyz; COMMIT; DETACH external; The same technique works in reverse to load an in-memory table from disk: ATTACH 'abc.db' AS external; BEGIN; DELETE FROM xyz; INSERT INTO xyz SELECT * FROM external.xyz; COMMIT; DETACH external; Use WHERE clauses creatively if you only want to transfer part of the data. Note that it is not necessary to ATTACH and DETACH every time you want to do this. If you are always using the same external database file, you can just ATTACH it once when you open the in-memory database and it will always be there for you. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Question about in memory db's
I am considering using SQLite as the 'file-based' version to the back-end of one of our products. I have been doing some testing, and the only way to allow read and write access at acceptable speeds seems to be to lock the db file for all the updates, allowing no reads through. Unfortunately, the application is GUI-based, and I have to service the read requests in a reasonable amount of time. I was experimenting with SQLite's in-memory database, which is much faster on updating than the db file, but still suffers from lag on reads. One approach is to use the in-memory database for updates, and periodically dump the in-memory db to a file, so that reads can use the current file version (there will be a difference between in-memory and file, but for the application it's ok). Is there a fast way to dump the in-memory db to disk, *and* periodically reload the file into an in-memory db? --Keith - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
