Two processes sharing a file

[JohnOne]

In the past, if I've needed two processes to share data I've used some communication method.

When one process has been AutoIt and the other C/++ I've used filemapping.

I now find myself wanting to just use a file of some sort. I believe you cannot use a text or ini file because of access collisions and such, but what what about xml, can they be used safely with 2 processes accessing them.

What are my options?

EDIT:

Again, one will be AutoIt and the other C++

Edited September 25, 2013 by JohnOne

[jchd]

Again, a super-simple SQLite database can do the job very efficiently, whatever complexity/volume the data can be today or tomorrow.

[JohnOne]

Cheers.

I've never used SQLite before.

I do recall a thread with super simple example scripts though, but cannot find it. It was specific that they were very easy to understand.

Anyone know where it is?

Edited September 25, 2013 by JohnOne

[JohnOne]

Duh!

I never even realized there was a standard UDF.

Looks simple enough, but to transfer application to non autoit machine do I need to keep sqlite3.exe with it?

[Gianni]

maybe >this?

[JohnOne]

Thanks.

Now to see if it's as simple on the C++ end 

[jchd]

JohnOne,

No, sqlite3.exe is not needed at all in fact. It is simply a portable reference implementation as command-line tool but in practice, programs use either a shared library (.dll or .so or whatever depending on OS) just like AutoIt, or embed it by linking to the library object module (a flat C source).

The example pointed out above is simple. Depending on the nature, format, size of the data you want to share you may need a more appropriate design. I'd be happy to help anyway.

The real magic is that you can have up to one writer and multiple readers concurrently using a given SQLite database, thanks to the WAL journalling mode. That's pretty good concurrency with simple source.

The C side is very easy too. I'll point you to some simple example but I need to move right now... Stay tuned

[JohnOne]

Cheers, I'm looking at http://sqlite.org/cintro.html at the moment.

Database will be pretty simple, a table with no more than 100 rows and columns.

"but in practice, programs use either a shared library..."

I see I have sqlite3.dll in my system folder, is that true of all windows OS'?

EDIT:

I've switched to.

http://www.tutorialspoint.com/sqlite/sqlite_c_cpp.htm

EDIT2:

C++ part not that complicated either.

Edited September 25, 2013 by JohnOne

[Gianni]

JohnOne,

No, sqlite3.exe is not needed at all in fact. It is simply a portable reference implementation as command-line tool but in practice, programs use either a shared library (.dll or .so or whatever depending on OS) just like AutoIt, or embed it by linking to the library object module (a flat C source).

The example pointed out above is simple. Depending on the nature, format, size of the data you want to share you may need a more appropriate design. I'd be happy to help anyway.

The real magic is that you can have up to one writer and multiple readers concurrently using a given SQLite database, thanks to the WAL journalling mode. That's pretty good concurrency with simple source.

The C side is very easy too. I'll point you to some simple example but I need to move right now... Stay tuned

 

please jchd

could you also point on some examples on how to do multiple writers and multiple readers concurrently?

thanks

[jchd]

Yes, I'll try to find some time to post a simple example and guidelines. But not before 8-9 hours from now.

[JohnOne]

Still getting race conditions occasionally with "Database is locked" error.

C++ is writing, AutoIt is readind

[jchd]

I'll try to address several points, out of order.

First: sqlite3.dll files floating around. Unless you're short on disk space (unlikely these days), you should use only DLL versions you know you've installed yourself. The reason is that there is a large number of common Windows programs using SQLite and many of them use a custom (or old) version of the dll.  Yes, that defeats the very purpose of a shared library but you know this is Windows and all.

So know your DLL. I'll post later a short code to display the version and main compiled features of an unknown sqlite3.dll file (but x86 vs x64 is making that a bit harder than before).

[jchd]

JohnOne,

Can you post code for both?

[jchd]

To all,

The main SQLite doc is applicable since the standard UDF is just a thin wrapper around basic SQLite3 functions.

Compared to typical C-type application, AutoIt SQLite support doesn't use bound parameters. It is possible to use this approach but it reveals slower than using stringized SQL statements, due to bottleneck slowness of the multiple DllCalls necessary to bind several parameters.

Database: file or memory

Read _SQlite_Open help and decide if you will be working with a memory-based or file-based DB. The former is obviously faster but has to be backed up to disk to make it persistent and can't be shared between processes.

[JohnOne]

expandcollapse popup

//http://www.tutorialspoint.com/sqlite/sqlite_c_cpp.htm
static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   for(i=0; i<argc; i++){
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[])
{
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;
    char *sql;
    const char* data = "Callback function called";

    /* Open database */
    rc = sqlite3_open("test.db", &db);

    if( rc ){
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        exit(0);
    }else{
        fprintf(stderr, "Opened database successfully\n");
    }
    
    // Create SQL statement 
    sql = "CREATE TABLE TESTTABLE("  \
    "COL1 INT PRIMARY KEY     NOT NULL," \
    "COL2       TEXT," \
    "COL3       TEXT," \
    "COL4       TEXT," \
    "COL5       TEXT);";

    // Execute SQL statement 
    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
    if( rc != SQLITE_OK ){
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
    }else{
    fprintf(stdout, "TESTTABLE created successfully\n");
    }

        // Create SQL statement 
    sql = "INSERT INTO TESTTABLE (COL1,COL2,COL3,COL4,COL5) "  \
    "VALUES (1, 'val', 'val2', 'val3', 'val5' ); " \
    "INSERT INTO TESTTABLE (COL1,COL2,COL3,COL4,COL5) "  \
    "VALUES (2, 'val', 'val2', 'val3', 'val4' ); "     \
    "INSERT INTO TESTTABLE (COL1,COL2,COL3,COL4,COL5)" \
    "VALUES (3, 'val', 'val2', 'val3', 'val4' );" \
    "INSERT INTO TESTTABLE (COL1,COL2,COL3,COL4,COL5)" \
    "VALUES (4, 'val', 'val2', 'val3', 'val4' );";

    // Execute SQL statement 
    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
    if( rc != SQLITE_OK ){
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
    }else{
    fprintf(stdout, "Records created successfully\n");
    }
    
    /* Create SQL statement */
    sql = "SELECT * from TESTTABLE";

    /* Execute SQL statement */
    rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
    if( rc != SQLITE_OK ){
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }else{
        fprintf(stdout, "Operation done successfully\n");
    }
    int i = 5;
    std::string s;
    while (1){
        Sleep(1000);
        s = "UPDATE TESTTABLE SET COL1=" + toString(i) + " WHERE COL5='val5'";
        //sql = s.c_str();
        rc = sqlite3_exec(db, s.c_str(), callback, (void*)data, &zErrMsg);
        if( rc != SQLITE_OK ){
            fprintf(stderr, "SQL error: %s\n", zErrMsg);
            sqlite3_free(zErrMsg);
        }else{
            fprintf(stdout, "Operation done successfully\n");
        }

        sql = "SELECT * from TESTTABLE";

        /* Execute SQL statement */
        rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
        if( rc != SQLITE_OK ){
            fprintf(stderr, "SQL error: %s\n", zErrMsg);
            sqlite3_free(zErrMsg);
        }else{
            fprintf(stdout, "Operation done successfully\n");
        }

        i++;
    }
    sqlite3_close(db);
}

Local $aResult, $iRows, $iColumns, $iRval, $sDbName = @ScriptDir & '\test.db'

_SQLite_Startup()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit -1
EndIf
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

;_SQLite_Open() ; Open a :memory: database
;If @error Then
;   MsgBox(16, "SQLite Error", "Can't Load Database!")
;   Exit -1
;EndIf

Local $hDskDb = _SQLite_Open($sDbName) ;this is the path ; Open a permanent disk database
If @error Then
    MsgBox(16, "SQLite Error", "Can't open or create a permanent Database!")
    Exit -1
EndIf

For $i = 1 To 40
    $iRval = _SQLite_GetTable2d(-1, "SELECT * FROM TESTTable;", $aResult, $iRows, $iColumns)
    If $iRval = $SQLITE_OK Then
        _SQLite_Display2DResult($aResult)
    Else
        MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
    EndIf
    Sleep(500)
Next

_SQLite_Close()
;FileDelete($sDbName)
_SQLite_Shutdown()

Edited September 25, 2013 by JohnOne

[jchd]

Journalling mode

Default journalling mode for disk-based DBs is "delete". This allows one writer OR multiple readers to use the DB concurrently.

Journalling mode can be changed to WAL mode by issuing a pragma (persistent setting, needs only be done once). Then this DB will deal with one writer AND multiple readers concurrently, provided the DLL(s) used to access it is/are recent enough (>= v3.7.0).

No DB engine supports multiple concurrent writers (literally), that would break the underlying relational model and the ACID properties (whatever that means). Writes need to be serialized somehow. Unlike most engines, SQlite is ... Lite! That means there is no centralized server where serialization can take place. Instead, concurrent applications use several flavors of file locking to maintain isolation betweens readers and successive writers.

Having applications with several writers is in fact not hard, but needs some thinking. The trick is to overestimate the longer duration of the worst case sequence of worst case transactions and use that as timeout. In practice, keep transactions reasonably sized and use some ridiculous value for timeout (on every DB connection), like 5 minutes. If the DB doesn't go out of busy state for that long (forever in terms of everyday's PC applications), then you have a pending operation which you need to deal with otherwise.

Wrap operations inside transactions when needed. Wrap bulk inserts or updates in transactions as well.

[JohnOne]

Not Sure I'm getting it, here's what I read...

journal_mode Pragma

The journal_mode pragma gets or sets the journal mode which controls how the journal file is stored and processed. Following is the simple syntax:

PRAGMA journal_mode;

PRAGMA journal_mode = mode;

PRAGMA database.journal_mode;

PRAGMA database.journal_mode = mode;

There are five supported journal modes:

Pragma Value Description

DELETE This is default mode. Here at the conclusion of a transaction, the journal file is deleted.

TRUNCATE The journal file is truncated to a length of zero bytes.

PERSIST The journal file is left in place, but the header is overwritten to indicate the journal is no longer valid.

MEMORY The journal record is held in memory, rather than on disk.

OFF No journal record is kept.

But PRAGMA is not recognized by VS2010.

[JohnOne]

Okay I believe I have it now.

/* Open database */
rc = sqlite3_open("test.db", &db);

if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(0);
}else{
fprintf(stderr, "Opened database successfully\n");
}
sqlite3_exec(db, "pragma journal_mode = WAL", NULL, NULL, NULL);

[jchd]

Ok, let's the C part alone for now.

Compile these:

; reader

#include <SQLite.au3>
#include <SQLite.dll.au3>


Local $aResult, $iRows, $iColumns, $iRval, $sDbName = @ScriptDir & '\test.db'

_SQLite_Startup()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit -1
EndIf
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

Local $hDskDb = _SQLite_Open($sDbName) ;this is the path ; Open a permanent disk database
If @error Then
    MsgBox(16, "SQLite Error", "Can't open or create a permanent Database!")
    Exit -1
EndIf

_SQLite_Exec($hDskDb, "pragma journal_mode=WAL;")   ; do it once (but doing it more than once doesn't harm)

_SQLite_SetTimeout($hDskDb, 1000*60*5)  ; 5 minutes timeout!

Local $aRow

For $i = 1 To 400000
    $iRval = _SQLite_QuerySingleRow($hDskDb, "SELECT count(*) FROM TESTTable;", $aRow)
    If $iRval <> $SQLITE_OK Then
        MsgBox(16, "SQLite SELECT Error: " & $iRval, _SQLite_ErrMsg())
    EndIf
Next

_SQLite_Close($hDskDb)
_SQLite_Shutdown()

expandcollapse popup

; writer

#include <SQLite.au3>
#include <SQLite.dll.au3>


Local $aResult, $iRows, $iColumns, $iRval, $sDbName = @ScriptDir & '\test.db'

_SQLite_Startup()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit -1
EndIf
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

Local $hDskDb = _SQLite_Open($sDbName) ;this is the path ; Open a permanent disk database
If @error Then
    MsgBox(16, "SQLite Error", "Can't open or create a permanent Database!")
    Exit -1
EndIf

_SQLite_Exec($hDskDb, "pragma journal_mode=WAL;")   ; do it once (but doing it more than once doesn't harm)

_SQLite_SetTimeout($hDskDb, 1000*60*5)  ; 5 minutes timeout!

_SQLite_Exec($hDskDb, "create table if not exists testtable (col1 integer not null primary key, col2 text, col3 text, col4 text, col5 text);")

For $i = 1 To 4000
    _SQLite_Exec($hDskDb, "insert into teSTTable (col2) values (hex(randomblob(16)));")
    If $iRval <> $SQLITE_OK Then
        MsgBox(16, "SQLite INSERT Error: " & $iRval, _SQLite_ErrMsg())
    EndIf
    Sleep(Random(10, 100, 1))
Next

_SQLite_Close($hDskDb)
_SQLite_Shutdown()

Launch a writer, and as many readers and writers you want.

You don't get any msgbox showing an error.

[jchd]

Yeah, slow typing my side. That one or more side(s) use AutoIt, C, Fortran or ADA is essentially irrelevant. You see that once you get the basic setup working, manipulating data back and forth is very easy and very robust.

In WAL mode (Write Ahead Log) there may be up to 2 files along your DB file itself: a .shm (is a shared memory "file") and a .wal containing hot data not yet writen to the DB. Never mess with these files or you'll corrupt your DB.