Jump to content
corgano

Keeping several SQLite databases in sync

Recommended Posts

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.


0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


Link to post
Share on other sites
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 by jchd
file sharing --> file locking

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Chimp

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.


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
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.

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Chimp

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

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

Thanks


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
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. :ermm:

Share this post


Link to post
Share on other sites
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 by corgano

0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


Link to post
Share on other sites
jchd

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 by corgano

0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


Link to post
Share on other sites
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 by jchd
Missing link
  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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?


0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


Link to post
Share on other sites
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.

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 by corgano

0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


Link to post
Share on other sites
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)


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 by corgano

0x616e2069646561206973206c696b652061206d616e20776974686f7574206120626f64792c20746f206669676874206f6e6520697320746f206e657665722077696e2e2e2e2e

Share this post


Link to post
Share on other sites
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.

#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()

 


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • dangr82
      By dangr82
       
      I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything.
      Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc  
    • Eminence
      By Eminence
      Hello,
      I've been searching on how to select the first row in my database which includes a time column based on the latest start_time date for today's date.
      This is my current code:
      Local $iCurrentDate = _Now() Local $sqlHandle = _dbOpen($sDbPath) Local $aResult, $iRows, $iCols _SQLite_GetTable2d($sqlHandle, "SELECT start_time FROM " & $srawDb & " ORDER BY datetime(start_time) DESC LIMIT 1", $aResult, $iRows, $iCols) _ArrayDisplay($aResult) _dbClose($sDbpath) What it does is it gets the first row entry in the database however it does not select the latest start_time entry in the database, it always select the first row. I've tried changing DESC to ASC to see if that will do it but the issue still remains. Any thoughts on this? Thanks in advance.
       
      *EDIT
      The date format in the database is by MM/DD/YYYY HH:MM:SS.
    • Seminko
      By Seminko
      In my recent project I'm downloading a bunch of data, so I decided to store it in a SQLite database. NOTE: I'm using sqlite3_x64.dll
      Everything is working just fine but I'm struggling with getting the Median value. SQLite has an Average function but not a Median one.
      I googled but all of the provided solutions are way above my pay-grade. After some more searching I found 'extension-functions.c' on the SQLite site where Median is included. After almost an hour of struggling I was able to successfully compile it into a DLL.
      So I downloaded @jchd's SQLiteExtLoad.au3 as seen here:
      But I'm getting these errors:
      "Path\SQLiteExtLoad.au3"(21,40) : warning: $g_hDll_SQLite: possibly used before declaration. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "Path\SQLiteExtLoad.au3"(21,40) : error: $g_hDll_SQLite: undeclared global variable. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ So I tried, copying the function to my file and changing the DLL variable ($g_hDll_SQLite) directly to the DLL location (C:\...\.. .dll), but now this error fires
      If __SQLite_hChk($hConn, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE) To be honest, I don't know what to use as the $hConn - handle of connection.
      I would appreciate any help, be it getting the median using SQLite queries or getting the DLL extension loaded using AutoIt.
      Thanks, S.
       
      EDIT: well, I suspect the $hConn variable refers to the return value of the _SQLite_Open function. Well, at least now _SQLite_EnableExtensions doesn't give errors. Now I run into problems with _SQLite_LoadExtension, which gives error -1, and extended 1. Apparently the 1 constant is a generic error where other error do not apply.
      BTW, anybody knows whether I need to compile the extension DLL "into" x64 when I use a x64 SQLite? That might be the problem...
       
      EDIT2: I recompiled the dll and tried it using the SQLite3.exe and it works, so I'm confident the extension DLL has been created correctly

    • AdamUL
      By AdamUL
      I have been testing AutoIt 3.3.14.3 with SQLite, and the Help File examples.  The examples that I have tested are throwing errors or not doing anything.  I have sqlite3.dll, sqlite3_x64.dll, and sqlite3.exe in the directories with the testing script.  I am copying the examples directly from the help file into a test script for testing with no edits.  The _SQLite_GetTable2d example is returning an error ("Library used incorrectly") with each _SQLite_Exec command.  The _SQLite_FastEncode example returns an empty dialog box.  The _SQLite_Exec example only prints out the SQLite version, and nothing else in the SciTE console.  Currently, I'm still searching for what is causing this issue.  I'm on Windows 7 Enterprise 64-bit.  Is anyone else having this issue?  
       
      Adam
       
    • Burgs
      By Burgs
      Greetings,
        I have SQLite setup within my AutoIT program...I'm trying to accomplish what should be a relatively simple task.  I want to be able to return an array of 'table' names for an established database...I believe this might be possible using the '_SQLite_SQLiteExe' command...since it seems to be able to access SQLite schemas...?  The ".tables" command is one of them...if I am not mistaken that command returns a list of all table names in the active database.
        I am attempting the following:
      #include <SQLite.au3> #include <SQLite.dll.au3> Global $hDb, $sIn, $sOut ... $sIn = ".tables" & @CRLF _SQLite_SQLiteExe($hDb, $sIn, $sOut) if @error == 0 Then ;Show Table (using SQLite3.dll) Else if @error == 2 Then ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF) Else ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF) EndIf ;@error is "2"...OR NOT... EndIf ;@error is "0"...OR NOT... ...   The error being thrown is "ERROR: Sqlite3.exe file not found" ...
        Am I required to have the Sqlite3.exe installed in my directory (i.e. @ScriptsDir)...???  I do not have it in there at present because I did not believe it was necessary with the 'include' calls to "SQLite.au3" and "SQLite.dll.au3"...any advice appreciated.  Thanks in advance. 
      Regards
       
       
       
       
       
       
       
×