Jump to content
Sign in to follow this  
JohnOne

Two processes sharing a file

Recommended Posts

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 by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites
jchd

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


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
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 by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites
Chimp

maybe >this?


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

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

  • 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
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 by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites
Chimp

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


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

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


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
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).


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
jchd

JohnOne,

Can you post code for both?


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
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.


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
JohnOne

//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 by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


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

  • 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
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.


AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


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

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


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

  • 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
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.


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
Sign in to follow this  

×