SQLite: Copy database to memory and back?
lazarus / 16-Databases

----------------------------------------------------------------------
stem · 2018-04-07 19:15

Hi,

I have an application which uses SQLite. Every update command etc. is
instantly written to file, everything works.

Now I wonder if it would make more sense to copy the database from
file to memory at the beginning (of the program execution), then
working only in memory, and at the end (of program execution) writing
the database back from memory to file.

Another advantage would be that synchronizing software like Dropbox
etc. would synchronize this file only at the end and not with every
SQL update command (because I'm planning to keep the database file
synchronized in future).

What do you think? Would it make sense to change my application in the
way I described?

Are there free pascal examples of moving the database "back and
forth"?

Thank you! 


stem

----------------------------------------------------------------------
GAN · 2018-04-07 21:37

I think more simple solution may be make a copy of de database, work
with this copy, then delete de source database and rewrite the copy.

----------------------------------------------------------------------
mangakissa · 2018-04-09 06:51

QuoteAnother advantage would be that synchronizing software like
Dropbox etc. would synchronize this file only at the end and not with
every SQL update command (because I'm planning to keep the database
file synchronized in future).
First of all: Dropbox isn't a remote drive, but a backupdrive to
backup files. 90% of people using dropbox the wrong way. Just work
with the original file and backup it in a backup program (for example
syncback free) in a schedule or build a copy in your application to
dropbox.
QuoteNow I wonder if it would make more sense to copy the database
from file to memory at the beginning (of the program execution), then
working only in memory, and at the end (of program execution) writing
the database back from memory to file.
Second of all: How many table are you using? Normally when a table is
open (SQLDB / ZEOS) you are working in memory.

----------------------------------------------------------------------
miab3 · 2018-04-10 18:33

@stem,

If you care about the speed, then for ZEOS (7.2 or 7.3) use this
connection for SQLite:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ZConnection
1.Connected:=false;ZConnection1.Properties.Add('synchronous=0');ZConne
ction1.Properties.Add('locking_mode=EXCLUSIVE');                     
                       
ZConnection1.Connected:=true;ZConnection1.ExecuteDirect('PRAGMA
journal_mode=MEMORY');
Then you practically act in memory.
https://www.sqlite.org/pragma.html

Michal

----------------------------------------------------------------------
mangakissa · 2018-04-11 06:30

And how about the inserts / updates/ deletes in real time?