Keeping several SQLite databases in sync

[corgano]

I have a program I made that reads and writes things to an SQL database, and it's working well. However now I'm at the point where I want 2 or 3 (at most, 5 or 6) computers on the same LAN running this program, but I want the data in the SQLite databases to be the same across all programs. What is the best way to do this? Should I access that SQLite file over network and have only one copy? Can I copy the "master" db to a local location, and then merge the databases together at regular intervals?

What is the best way to sync data in SQLite between a small number of computers running the same program?

I'm rather partial to keeping with SQLite, because it's small and I can just package a DLL with the program and it's ready to go.

[jchd]

I strongly advise you against multiple copies of a DB because of the very hard (in general) issues that need to be solved properly to keep them "in sync".

Due to vicious bugs in all currently available network file sharing locking protocols, I equally strongly warn you that SQLite is not to be used over a network when multiple connections may write to the DB (schema change, insert, update, delete).

A recent announce on the sqlite mailing list drew my attention on cubeSQL, a client/server engine based on pure SQLite. From their website http://www.sqlabs.com/cubesql.php a free license can accept up to 3 simultaneous connections thru their ODBC (ADO) layer. Maybe you could evaluate their product with no risk to see if a paid license for more workstations is acceptable in your context. I can provide a workable UDF for ODBC with only slight changes in function names, e.g. _SQLite_GetTable2d becomming _AdoSQL_GetTable2d.

There used to be another (freeware) SQLite client/server which I can't find trace of anymore but required a C++ interface which would need a wrapper to be used in practice. Something you may not want to go thru. Several other projects have been abandonned long ago.

I've started writing a pure AutoIt SQLite client/server layer based on a rewrite of the current standard SQLite UDF but this is work in (slow) progress. My aim is to keep 99.x% of the current interface unchanged and reliably (but more slowly) manage both local and remote DBs in the most transparent way possible.

Duplicating a DB even while it's being used is no problem by using the SQLite backup UDF (see Examples) but the real problems arise when you want to "merge" changes done independantly. This is essentially unsolvable in the general case, despite what you can read here or there. Very careful planning can make that work for specific contexts but the route is a dragons nest.

Edited June 3, 2015 by jchd
file sharing --> file locking

[Gianni]

Just a wild guess, an embrional idea from a while, that I think it can be feasible without too much troubles (lazyness allowing).
In short the idea is to have a server machine with SQLite running on it;
on the same machine also a tcp server waiting for connections from clients with the purpose of getting sql statements to be send to the SQLite.
In this way would be avoided direct sqlite connections over the network between the clients and the SQLite.
the network connections would be mediate by the tcp dedicated server to act as an interface between clients and the server SQLite, and so all the DB stuff would be performed on a single machine

sorry if I'm not clear, and especially if I do not present anything concrete...
anyway I think it could be a good project to be implemented.

[jchd]

Chimp,

That's exactly what I've been working on. There are several points which need improvement in the current library and some design decisions are not trivial and still in blueprint.

[Gianni]

Damn!... I thought I had had an original idea.... ..... (I'm joking btw )

nice to know that you are working on that
I will stay tuned and hope to hear some news soon about this work.

Thanks

[kaisies]

Just a wild guess, an embrional idea from a while, that I think it can be feasible without too much troubles (lazyness allowing).
In short the idea is to have a server machine with SQLite running on it;
on the same machine also a tcp server waiting for connections from clients with the purpose of getting sql statements to be send to the SQLite.
In this way would be avoided direct sqlite connections over the network between the clients and the SQLite.
the network connections would be mediate by the tcp dedicated server to act as an interface between clients and the server SQLite, and so all the DB stuff would be performed on a single machine

sorry if I'm not clear, and especially if I do not present anything concrete...
anyway I think it could be a good project to be implemented.

​A very interesting thought process. I had actually just started laying framework for a SQLite backend w/a GUI for Knowledge Base articles, and It didn't even occur to me that simultaneous connections would be unwanted (I had toyed with a network shared DB)

Chimp,

That's exactly what I've been working on. There are several points which need improvement in the current library and some design decisions are not trivial and still in blueprint.

​Looks like I'll have to head down this path soon as well, Interesting indeed. 

[corgano]

I was hoping for a solution I can get working in the next few days. I'm not doing any huge queries or anything intensive, nor do I need to support hundreds of clients, so I don't need something too extreme.

Could you provide an example of using TCP instead of SQLite over network? Like how would I _SQLite_GetTable2d() over TCP?
@jchd is any of the UDF useable yet?  It sounds like a really useful UDF to have, especially if it was easy to switch from using a local database to a network one. It sounds like exactly what I need.
 

Edited June 4, 2015 by corgano

[jchd]

Nothing is anywhere close to useable form, sorry. I know it's something that would (will) prove very useful.

[corgano]

So for now I guess I will just do it over the network with a single database...

I read in other threads while searching about the issue with locking the file, so I'll probably implement locking the file myself. A .txt file with the computer name and time the file was locked should be good enough right? then just check for a lock file and if it's there, wait until it isn't.

 

Edited June 4, 2015 by corgano

[jchd]

There is something like this maybe in Examples ou GHS which you can use(*). Just realize that there is no guarantee that such an external locking is not failproof unless you carefully implement a guard delay and re-read:

1) PC1 looks for a locking file and waits until it finds none
2) PC1 creates its locking file
3) PC1 waits "some time" : you have zero guarantee that the file created will actually make it on the magnetic surface by the moment you get a successful return of the function, so waiting for "some time" is necessary. How long should that be is a total mystery to me.
4) PC1 looks again for all locking files
5) If there is only the one it created, then it's OK for PC1 to access the DB
6) If PC2 also created its own locking file at approximately the same time, PC1 and PC2 will collaborate to delete the locking file with the most recent timestamp (requires high-definition time, like µs) and proceed accordingly. "Most recent" may be a different point in time than expected, because PCs are not synchronized down to a small fraction of a second. Yet two or more PCs may use the same timestamp even if the probability is low; then some order of priority based on PC name must take force.
7) Some machinery has to deal with dead locking files after a PC leaves a hot one (app crash, power outage, network disruption, whatever).

In short: manual file locking is cumbersome and slow as hell at best, prone to errors in all cases.

(*) I found that thread: https://www.autoitscript.com/forum/topic/117033-file-locking-with-cooperative-semaphores

Edited June 5, 2015 by jchd
Missing link

[corgano]

You made a post here and I was curious about something:How exactly do begin immediate; begin exclusive and commit work? How do I know which one to use?

[jchd]

These are transaction statements. Transactions are explained in any SQL tutorial and in the SQLite docs but here is a short (?) answer:

A transaction is a way to isolate a given connection from changes made by other connections. Outside of a transaction block, SQLite is in "autocommit" mode, that is, every individual statement automagically starts a transaction which ends (is commited) after the end of the statement execution. This is necessary even for reads (select ...) since you want to obtain a consistent snapshot of the DB thru your select with no possibility for another connection to change what you're reading. This is done by locking the whole DB file with a read lock which still allows others to do the same and perform selects concurrently. Of course, individual (non transacted) write statements (schema change, inserts, updates) use a write lock, thus disabling concurrent writes.

A simple "read" transaction is a way to group read-only statements as if they were a single atomic operation. For instance you can use a read transaction to gather data from various source that can't be put together with a single select statement. This will ensure you get consistent data, where no other connection can modify it under your feet while you're reading.

Now suppose you have to perform an operation similar to cash withdrawing from an ATM (without looking at the gory details of what happens inside the ATM). From the bank point of view, you want to perform an atomic operation consisting of several steps:

1. check that the account exists and disable any change to the account
2. check that the account allows the operation (enough credit or under debit limit)
3. allow the cash to be distributed
4. check that the cash was effectively taken off the machine
5. record the operation
6. subtract the amount given
7. unlock the account so that other operations can now take place

You must ensure that all ("commit") or none ("rollback") of the above steps happen as a whole, else something wrong will happen for one of the parties. For instance you can't allow another money withdrawal (e.g. by bank transfer order) from the account while the operation is taking place. You can't allow account closing during the process either, and so on.

What you need here to perform such a read-modify-write operation is an immediate transaction with "begin immediate" ... "end". This uses an advisory lock placed at once, meaning that the lock owner (the connection) is going to first read but intends to later write to the DB. Before the first write (insert/update) statement starts, the advisory lock is escaladated to an exclusive lock, allowing no other operation on the DB, because its content will be changing at any time from now. When the "end" or "commit" (or "rollback" to cancel the transaction) is encountered, the write lock is released and other operations can resume.

SQLite simply resets the internal autocommit flag for the connection when it encounters the begin of a transaction and sets it back again before the "end" or "commit", which is where all the changes you've made actually occur.

An exclusive transaction places an exclusive (write) lock at the beginning of the transaction, contrary to an immediate transaction.

Now there is another concept which is the journal mode. Default journal mode allows ONE writer OR many readers concurrently. The WAL journal mode allows ONE writer AND many readers concurrently. But note that WAL mode use memory-mapped file(s) and can't be used at all over a network. You can change the journal mode by a pragma and this setting is persistent for the DB until changed again, if ever.

Hope this explains a bit on the transactional machinery.

[corgano]

So "begin immediate"  = begin -> Lock writing -> Read all the crap -> Lock reading and writing -> Write all the crap -> End.
And "Begin exclusive" = begin -> Lock reading and writing -> read all the crap -> write all the crap -> End

So the exclusive SHOULD be more stable, but might more often leave other statements that only need to read waiting? Is there any downside to me wrapping it all in begin explicit?

Syntax wise, how should I use this? do I...

1. _SQL_Execute("begin exclusive;")
2. do my reading in other _sql commands
3. do my modifying in autoit
4. do my writing in whichever _SQL commands
5. _SQL_Execute("end")

OR should I

1. begin exclusive ; from table select whatever... ; end
2. do my modifying and crap in autoit
3. begin exclusive ; update table where whatever...... ; end

Or would it be best to try and figure out everything I need to read and write ahead of time and pack everything into one sql_execute()?

I also want to confirm, even with begin exclusive I should still be using a proper filelock along side it right? Eg this AND begin exclusive? OR is it one or the other?

Edited June 6, 2015 by corgano

[jchd]

I must read your "explicit" as being "exclusive", explicit doesn't exist in SQL.

So the explicit SHOULD be more stable, but might more often leave other statements that only need to read waiting? Is there any downside to me wrapping it all in begin explicit?

The downside of exclusive is indeed that it often locks for more extended periods than immediate without good reason.

Or would it be best to try and figure out everything I need to read and write ahead of time and pack everything into one sql_execute()?

Don't do that! You can't read result of an SQL statement launched by _SQLite_Exec. The correct way of doing is your first sequence 1 to 5.

The reason why the second sequence 1 to 3 is a pitfall is because another connection can change the same data you read in step 1 while you're busy with step 2. Once you rewrite your changed data, the other changes are lost, but the issuer is persuaded they succeeded.

A transaction is meant to make a group of SQL operations atomic in isolation, that is the only way to ensure ACID properties (http://en.wikipedia.org/wiki/ACID)

[corgano]

I must read your "explicit" as being "exclusive", explicit doesn't exist in SQL.

​I have all of the derping. Corrected previous post.

The downside of exclusive is indeed that it often locks for more extended periods than immediate without good reason.

​Not really an issue. I only have 3 clients now, with MAYBE 6 in the future, and they're not sending any large queries or sending more than 1 request every 3 seconds, so I don't think that will be an issue.

The correct way of doing is your first sequence 1 to 5.

​So what does this look like in terms of autoit? Say I wanted to SELECT * FROM table WHERE bar = 6, and then INSERT OR REPLACE INTO table VALUES ('foo', bar + 1). How would I do this in autoit incorporating the ideas discussed above?

Edited June 6, 2015 by corgano

[jchd]

Sorry for delay. Here's a simple program which first demonstrates the various way you can use to perform a bulk insertion. The DB is recreated each time so timings are pretty comparable.

Then I've code a shameless dumb Read-Modify-Write transaction to show how it can be safely done. Chime if something isn't clear.

expandcollapse popup

#include <SQLite.au3>
;~ #include <SQLite.dll.au3>            ; not needed when you have the DLL already setup somewhere
#include <String.au3>
#include  <Array.au3>

Local $memory = Not True

Local $data = _StringRepeat("abcdefghi ", 10)
Local $create = "CREATE table if not exists Test (ID integer primary key, data text);"

Local $sDbName = ($memory ? "" : "tests.db3")
Local $limit = 1000
ConsoleWrite("All tests done with " & $limit & " rows for a " & ($memory ? "memory" : "disk-based") & " DB." & @LF & @LF)
FileDelete($sDbName)

_SQLite_Startup("..\bin\sqlite3.dll")       ; adjust the DLL location to your setup

Local $hDB, $t0
Local $aRows, $iCols, $iRows

$hDB = _SQLite_Open($sDbName)
_SQLite_Exec($hDB, $create)
$t0 = TimerInit()
For $i = 1 To $limit
    _SQLite_Exec($hDB, "INSERT INTO Test (data) VALUES (hex(randomblob(128)));")
Next
ConsoleWrite(StringFormat('%-45s done in % 8.3f', "SQL randomblob inserts", TimerDiff($t0) / 1000) & " s" & @LF)
_SQLite_Close($hDB)
FileDelete($sDbName)

$hDB = _SQLite_Open($sDbName)
_SQLite_Exec($hDB, "pragma journal_mode=wal;")
_SQLite_Exec($hDB, $create)
$t0 = TimerInit()
For $i = 1 To $limit
    _SQLite_Exec($hDB, "INSERT INTO Test (data) VALUES (hex(randomblob(128)));")
Next
ConsoleWrite(StringFormat('%-45s done in % 8.3f', "WAL mode SQL randomblob inserts", TimerDiff($t0) / 1000) & " s" & @LF)
_SQLite_Close($hDB)
FileDelete($sDbName)

$hDB = _SQLite_Open($sDbName)
_SQLite_Exec($hDB, $create)
$t0 = TimerInit()
For $i = 1 To $limit
    _SQLite_Exec($hDB, "INSERT INTO Test (data) VALUES (" & _SQLite_FastEscape($data) & ");")
Next
ConsoleWrite(StringFormat('%-45s done in % 8.3f', "Individual text inserts", TimerDiff($t0) / 1000) & " s" & @LF)
_SQLite_Close($hDB)
FileDelete($sDbName)

$hDB = _SQLite_Open($sDbName)
_SQLite_Exec($hDB, $create)
$t0 = TimerInit()
For $i = 1 To $limit Step 10
    _SQLite_Exec($hDB, "INSERT INTO Test (data) VALUES" & _
        " (" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ");")
Next
ConsoleWrite(StringFormat('%-45s done in % 8.3f', "10-Chained text inserts", TimerDiff($t0) / 1000) & " s" & @LF)
_SQLite_Close($hDB)
FileDelete($sDbName)

$hDB = _SQLite_Open($sDbName)
_SQLite_Exec($hDB, $create)
$t0 = TimerInit()
_SQLite_Exec($hDB, "begin immediate;")
For $i = 1 To $limit
    _SQLite_Exec($hDB, "INSERT INTO Test (data) VALUES (" & _SQLite_FastEscape($data) & ");")
Next
_SQLite_Exec($hDB, "commit;")
ConsoleWrite(StringFormat('%-45s done in % 8.3f', "Transacted text inserts", TimerDiff($t0) / 1000) & " s" & @LF)
_SQLite_Close($hDB)
FileDelete($sDbName)

$hDB = _SQLite_Open($sDbName)
_SQLite_Exec($hDB, $create)
$t0 = TimerInit()
_SQLite_Exec($hDB, "begin immediate;")
For $i = 1 To $limit Step 10
    _SQLite_Exec($hDB, "INSERT INTO Test (data) VALUES" & _
        " (" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ");")
Next
_SQLite_Exec($hDB, "commit;")
ConsoleWrite(StringFormat('%-45s done in % 8.3f', "Transacted 10-chained text inserts", TimerDiff($t0) / 1000) & " s" & @LF)
_SQLite_Close($hDB)
FileDelete($sDbName)

$hDB = _SQLite_Open($sDbName)
_SQLite_Exec($hDB, $create)
$t0 = TimerInit()
_SQLite_Exec($hDB, "begin immediate;")
For $i = 1 To $limit Step 10
    _SQLite_Exec($hDB, "INSERT INTO Test (data) VALUES" & _
        " (" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ")" & _
        ",(" & _SQLite_FastEscape($data) & ");")
Next
_SQLite_Exec($hDB, "commit;")
ConsoleWrite(StringFormat('%-45s done in % 8.3f', "WAL mode transacted 10-chained text inserts", TimerDiff($t0) / 1000) & " s" & @LF)

$t0 = TimerInit()
_SQLite_GetTable($hDB, "SELECT * FROM Test;", $aRows, $iCols, $iRows)
ConsoleWrite(StringFormat('%-45s done in % 8.3f', "GetTable", TimerDiff($t0) / 1000) & " s" & @LF)

$t0 = TimerInit()
_SQLite_GetTable2d($hDB, "SELECT * FROM Test;", $aRows, $iCols, $iRows)
ConsoleWrite(StringFormat('%-45s done in % 8.3f', "GetTable2d", TimerDiff($t0) / 1000) & " s" & @LF)
_SQLite_Close($hDB)
FileDelete($sDbName)


;#####################################################################################
;
; RMW transaction example

$hDB = _SQLite_Open($sDbName)
_SQLite_Exec($hDB, $create)
$t0 = TimerInit()
_SQLite_Exec($hDB, "begin immediate;")      ; speed up bulk insertion
For $i = 1 To $limit
    _SQLite_Exec($hDB, "INSERT INTO Test (data) VALUES (" & "'This is entry number " & Random(0, 100000000, 1) & "');")
Next
_SQLite_Exec($hDB, "commit;")

; now perform a RMW operation
; this could have been all done in only one SQL update, but let's use AutoIt for demo purpose
_SQLite_Exec($hDB, "begin immediate;")

; read
_SQLite_GetTable2d($hDB, "SELECT id, data FROM Test where instr(data, '23') > 1;", $aRows, $iCols, $iRows)
_ArrayDisplay($aRows, "Selected data")

; modify
For $i = 1 To UBound($aRows) - 1
    $aRows[$i][1] = $aRows[$i][1] & " (contains the substring 23)"
Next

; write
For $i = 1 To UBound($aRows) - 1
    _SQLite_Exec($hDB, "update test set data = " & _SQLite_FastEscape($aRows[$i][1]) & " where id = " & $aRows[$i][0])
Next

_SQLite_Exec($hDB, "commit;")

; let's check it worked
_SQLite_GetTable2d($hDB, "SELECT id, data FROM Test where instr(data, '23') > 1;", $aRows, $iCols, $iRows)
_ArrayDisplay($aRows, "Changed data")

_SQLite_Close($hDB)
FileDelete($sDbName)

;#####################################################################################

_SQLite_Shutdown()