Want SQLite Single Access - How?

Greetings - using Xojo 2019 R3.2

I am using an SQLite database. I am paranoid about the data in the database (it takes a LOT of work to populate the database). To that end, I will implement a backup mechanism but that is not the issue here (that comes next). I have three apps that need to access the database: (1) a full-monte user access with lot of features including data importing from CSV files; (2) a read-only app (will probably be the one mostly used) provided to prevent accidental writes; (3) a read/write app that provides access to raw data without many of the protections and automated features of the full version. The read/write version will seldom be used and probably used only if I mess up the coding of the full version, somehow.

Now, I WANT to insure that only one of the three can connect at any time. I just tried connecting with the read-only version while the full version was connected, and it happily accepted the connection request. How can I prevent this from happening? Only thing I can think of at the moment is a (text) semaphore file but that has its own collision problems. Remember: the three apps are fully independent so what each can “know” about the others is limited. This independence is on purpose as part of the strategy to try to prevent accidental changes to the database.

So, here we are again, asking for ideas. suggestions, examples that work, references to places that discuss this issue, what-ever!

Many thanks for your help!
Jim Wagner, Oregon Research Electronics

Sounds like you want a mutex.
https://documentation.xojo.com/api/language/mutex.html

Ahhh, that was the word I was looking for!

Thank, you! Jim

are they all accessing from the same machine ?

Probably from the same machine. HOWEVER, it would be nice to have the database file on DropBox drive accessed from multiple points on the same LAN. I realize this raises the bar some, and I would be happy with the single machine solution.

Thanks for asking!
Jim

By the way, with a named mutex, it works with differently named apps on a single machine (Mac).

Jim

you can put a text file next to it when the program is active and delete it when you exit. just check for that file. easy to fix if an issue - just remove the file.

Do not use SQLite on a shared drive. Dropbox included.

4 Likes

sqlite works fine on SMB as long as oplocks are off

Thanks for the heads-up on shared drives.I’ll just have to be careful to export the report as CSV, first, and put that on the shared drive. I did not consider DropBox as a shared drive though I guess it is shared somewhere out there, “in the cloud” and activity on one instance could result in the changes on other instances that could really mess up the other database engines.

Jim

PLEASE DON’T SHARE SQLITE FILES…
it works … and one day the file is dead.
it is the same as excel files, if you share them on a drive, it works… and one day the file is dead.

use other database engine like postgres if you have to share a database
it’s free too.

2 Likes

If so, I suppose you already make backup(s) of your sqlite file.

But, you’d better implement many ways to save your data from the .sqlite file. (txt, csv, html, json comes to mind).
Then, add an import method to use the exported data.

At last, once implemented, resume the development where you actually are.

This also allows you to realize your db design is OK (you forget nothing), and eventually modify it safety if needed.

Life is really error prone (and I do not talk about developing software).

1 Like

If you’re intent on using SQLite I’d suggest the only safe way is having a 4th application - a server that exposes an API the other three applications consume. Your server is then the only application that accesses the SQLite file directly.

1 Like

Unwise. You should read:
https://www.sqlite.org/howtocorrupt.html

1 Like

The only safe way (from the file safety standpoint) is building “a server”, some middleware centralizing the access. Your apps could talk “REST” to it and receive JSONs, for example. It comes with few challenges to implement (queuing, locks, timeouts, etc), but it was done before. The files will never be touched directly any other computer, just through the middleware. NEVER put ANY active database files in a folder with any kind of remote synchronization active. It WILL BE CORRUPTED at some point.

1 Like

Following up on several comments, suggestions for backup strategies would also be appreciated.

I know about file level backups using file snapshots (and often using file pseudo-extensions like “bak1”). But, I see that SQLite has some sort of backup facility (about which I know NOTHING).

Is there any sort of standard backup procedure used in Xojo programs? Any recommendations?

Many thanks. again
Jim

My app just create snapshots in some place set as the backup folder using:

https://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-backup

And THIS backup folder can use synchronization, as no one will open and modify the backup file.

In case of system failure, the remote location has the last sync’d backups.

1 Like
VACUUM main INTO '/path/to/backup/folder/mydb.sqlite3';
1 Like

Have never seen VACUUM … INTO … command.

Is there a problem with just copying the database file (when it is unconnected).

Many thanks
Jim

why not use something that has been made for it ?
https://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-backup