Two processes sharing a file

[JohnOne]

Thanks for your time jchd, I appreciate it.

Does reader need to exec pragma?

I've been testing it with just c++ writer using it without any problems you see.

Would I be likely to run into any?

[jchd]

No, WAL mode is persistent and only needs to be set once, at any time. Journalling mode may also be changed at any time, but don't use OFF without good reasons!

Setting a very long timeout is the key to seamless operations. Well, of course don't leave transactions open for too long or else other apps will loose patience!

I didn't use a transaction in the demo insert pgm, just to demo that random reads can work along with random writes.

In practice, place bulk inserts/updates inside transactions to speed them up.

Argh, almost forgot: once you setup a long enough timeout, you better use "BEGIN IMMEDIATE;" .. "COMMIT;" transactions. Then you don't even have to test for errors using SQL, unless you debug it. The rationale is that if an error occurs, then the DB is corrupt for some reason, or the disk is full or dying, or memory is exhausted or some other catastrophic failure. Having a clean msgbox displayed at this time is certainly not going to solve the problem!

Edited September 25, 2013 by jchd

[jchd]

A couple of things:

foreign keys are (still) disabled by default for every new connection. If you use foreign keys at all in a DB, you need to issue a pragma to enable their enforcement for every connection on this DB.

there are countless features either inside SQLite itself, or available as extensions (think plugins).

Have fun messing with your prefered device (GPS, smartphone, tablet, router, ...) embedded DBs : SQLite DBs are portable on every platform able to run SQLite.

[JohnOne]

I'm not even sure what a transaction is to be honest.

Some good tips to be thinking about though.

One last question, I don't know much (anything really) about sql statements.

I noticed "CREATE TABLE IF NOT EXISTS TESTTABLE..."

Is there a way of incorporating that "if not exists" condition into INSERT?

[jchd]

I knew I was forgetting something important (again!).

Do not use SQLite over a network, or do so at your own risk.

The reason is that every file sharing protocol available today still carries bugs inside remote file locking operations. Since SQLite heavily relies on this to ensure integrity of its DBs, you can expect spurious corruption from remote operation (LAN included).

Beyond the warning, moderate concurrency appears to work satisfactorily over fast and reliable Windows LANs but don't sue me if you hit a wall!

Edited September 25, 2013 by jchd

[JohnOne]

I did read that processes must be on same physical machines when journal = WAL.

Same machine for me though.

[jchd]

There are several possibilities in SQLite:

REPLACE (equivalent to INSERT OR REPLACE) will delete a previous row conflicting with what you want to insert anew.

INSERT OR IGNORE

INSERT ... ON CONFLICT ***

See the SQLite railroad diagrams and their comments for details

[jchd]

 

I did read that processes must be on same physical machines when journal = WAL.

Yes, because of the shared memory thing.

[JohnOne]

Got It.

Thanks again pal for your time and expertise.

++

[jchd]

You're welcome anytime.

I very very strongly recommend SQLite Expert to manipulate/experiment with SQLite DBs (the freeware version is sufficient for most basic needs). It saves countless hours by letting you design, experiment, change things without writing a single line of code.

EDIT: I apologize for the number of posts and lack of material structure.

Edited September 25, 2013 by jchd