SQLite Open Read Only

[Skysnake]

My SQLite.au3 include contains this entry on line 43

Global Const $SQLITE_READONLY = 8 ; /* Attempt to write a readonly database */

But, is not in the Help file.  The right page, I think is _SQLite_Open.htm, and does not contain a reference to opening a SQLite db as ReadOnly.  Should this be added to the Help File?

 

BUT, what I really want to know is this: What is the best way to determine whether a SQLite db should be opened as ReadOnly?

I have WAL files active.  Can I check for WAL and if exists, force open ReadOnly?  ie, if a user is already logged on, simply for other users to Read Only?

Is this the right question to ask?

 

 

[BrewManNH]

The reason to open a database as readonly would be if you only want to read from it and not alter its contents, even by mistake.

[BrewManNH]

The reason to open a database as readonly would be if you only want to read from it and not alter its contents, even by mistake.

[Skysnake]

Exactly.  I want to allow only the first connection to have default (full) access, all subsequent connections must be read only...

 

[jchd]

12 minutes ago, Skysnake said:

But, is not in the Help file.  The right page, I think is _SQLite_Open.htm, and does not contain a reference to opening a SQLite db as ReadOnly.  Should this be added to the Help File?

Look again: these constants are SQLite error return codes, not DB open filemodes.

[Skysnake]

Hmm.  Thx @jchd, BUT, this is possible ito https://www.sqlite.org/c3ref/open.html. So, (1) I must be able to this with AutoIt and (2) my original question, what is the best way to trigger such a state?

SQLITE_OPEN_READONLY

from https://www.sqlite.org/c3ref/c_open_autoproxy.html

SQLite.au3 Line 62

Global Const $SQLITE_OPEN_READONLY = 0x01 ; /* Database opened as read-only */

 

Edited July 28, 2016 by Skysnake

[Skysnake]

Rephrase - I have a SQLite db that uses WAL files.  How can I determine that a pre-existing connection is already active, and force subsequent connections to READ ONLY?

[jchd]

You can open a DB file as read only using two ways:

o) pass the $SQLITE_OPEN_READONLY as the second parameter to _SQlite_Open
o) use an URI filename, like in _SQlite_Open(" file:/somedir/MyDB.sq3?mode=ro", 0) but I'm not sure if the URI option prevails over the declared mode.

You can't know if other process is using the file. That would need a centralized server, which SQLite doesn't have for obvious reasons.

The problem with opening a DB as RO is that if there is a hot journal or something in WAL files needing to update the actual DB file, then the RO will prevent that.

[Skysnake]

2 minutes ago, jchd said:

The problem with opening a DB as RO is that if there is a hot journal or something in WAL files needing to update the actual DB file, then the RO will prevent that.

No I am really glad I asked first. Thx @jchd, much appreciated!

Edited July 28, 2016 by Skysnake

[jchd]

If you're unsure that the first, initial and unique process has finished writing the DB and releasing the connection, you can also use the access rights extension and sqlite3_set_authorizer() to block unwanted writes.

[Skysnake]

Thx @jchd, you are a star!

My idea so far was to have the first connection write a file to disk, much like the Postgre .pid file for the service, then check if .pid exists or not... not foolproof... other option is to force default connection as READONLY and only use write when necessary.

Best option is to move to real db server... like Postgre or Firebird

Thx

Skysnake

Edited July 29, 2016 by Skysnake

[jchd]

I mean that if you definitely require that users can't write to the DB, you'll have to use Windows access rights and disallow use of anything alse than your application (e.g. third-party SQLite managers). Then compile the SQLite DLL with the userauth extension (in source tree under ext/misc). User authentication this way is rather weak since it relies on user not being able to access the file otherwise (that's where the OS filesystem permissions enter the game).

Then if the only program able to access the file is your application and if user are authorized, then it's pretty simple to track who reads and writes and when.