My little app use a SQLite database that would like to share with (only) three computers in a Network (we use Mac).
The most of the time we only needs to read data into database, not updating it.
I’ve read (most of them are very old posts) that is totally not recommended to use SQLite in a shared network for the potential corruption.
The recommendation is also available at SQLite web page even if seems a ‘light’ recommendation: (point 5 of their FAQ) If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.
In other words else today SQLite is NOT to use in that scenario or not ?
Or the new version of the database/OS has improved the lock database feature so the risk of the corruption is minimum?
One the solution could be Valentina Server SQLite (so that i’ll keep all the queries and other stuff intact) ?
Any hints on how to make that migration ?
I’ve found this
and this
The Valentina Server needs to be installed on every of the 3 Mac or only on the (dedicated) Server ?
Or it will be a best choice to pass i.e. to PostgreSQL ?
which will set a timeout on your db connection (30 secs in this case). This means if an SQLite command would otherwise get SQLITE_BUSY error, the command will be retried many times for up to 30 secs and therefore hopefully succeed.
Yes, we’re only 3 Mac connected to a Mac Mini Server.
My SQLite database has 1 only table with 25 columns (+ 1 virtual table for full search capability) and have about 100-200 records (or a bit more) with very few texts in it.
Most of the time 1 only computer is connected, at maximum 2.
Great i’ve no idea of WAL mode, with that enabled i can read from the 2 Mac and write on the 3 one in the same time, is it correct (with very few risk of corruption).
May i leave the connection to the same database always open, is it true ?
The issue of corruption could be if i write in the same time from 2 or 3 Mac.
I should execute this code at every executeSQL not also on selectSQL, correct ?
OK, with that small amount of traffic you shouldn’t have a problem, probably don’t need to worry about WAL, even. Just do the timeout setting right after the db.connect (only once needed).
there is a specific plugin for xojo so a lite version should work.
don’y know myself, I use postgres!
download it and see by yourself : https://sqlabs.com/cubesql_download
It’s odd, but when i open the App on (only) 2 Mac i receive on a second Mac a ‘Database is locked’ warning.
Here’s the code, is it correct ?
Var dbFile As FolderItem = SpecialFolder.ApplicationData.Child(“NiP/NiP.db”)
DB = New SQLiteDatabase
DB.DatabaseFile = dbFile
Try
DB.Connect
DB.WriteAheadLogging = True
Catch err As DatabaseException
MessageBox ("Errore: " + err.Message)
Return
End Try
I open the database on open event and leave it opened, and close on close event, is it correct ?
The odd is that when have this test i simply open the App on the 2 Mac and not else …
I’ve read that WAL does not work over a network filesystem, i don’t think is my case …
I have a Mac Mini Server and 3 Mac as said and put the database into server (i access to the shared folder in AFP - Apple Filing Protocol).
In V4RB archive exists
Examples / RBDB_way / SqliteExampleModified
It is modified to demonstrate easy work in both LOCAL and CLIENT mode.
Controlled by a single checkbox
This is the code of function where the difference happens.
As you can see the difference is only where we create DB object - local or remote.
Future work with the DB object is the same.
App.DB = new VSqliteDataBase()
// We are going to create instance of VSqliteDatabase class and CREATE db on disk or under VServer.
if cbClientMode.Value = true then
App.DB.Host = "localhost"
App.DB.UserName = "sa"
App.DB.Password = "sa"
App.DB.Port = 15532
mIsConnected = App.DB.Connect()
if mIsConnected then
// only NOW we specify db_name, and do create on remote Valentina SQLite Server. Must be ADMIN.
APP.DB.databaseName = "example.sqlite"
// assign again into the same variable, will be TRUE if db was created under VServer, so we can use DB.
mIsConnected = App.DB.CreateDatabaseFileEx()
end if
else // checkBox ClientMode is false, i.e. user asks a LOCAL db of sqlite.
Dim dbFile As New FolderItem("example.sqlite")
If dbFile <> Nil And dbFile.Exists Then
dbFile.Delete
End If
App.DB.DatabaseFile = dbFile
mIsConnected = App.DB.CreateDatabaseFile
end if
If mIsConnected Then
CreateStatusLabel.Text = "Created SQLite database."
Else
CreateStatusLabel.Text = "Error creating SQLite database: " + App.DB.ErrorMessage
End If
Hi @Ruslan_Zasukhin,
actually i have created -for full search text capabilities- inside my SQLite Database a virtual table FTS5 (and add some triggers).
The structure of my SQLite database can stay as it is (with virtual table and triggers) ?
LOCAL and CLIENT mode: in LOCAL the (same) database will stay in our desidered directory, while in CLIENT needs to stay inside your Valentina Server?
May i’ll still open my database (from inside your Valentina Server) using DB Browser for SQLite (instead with your Valentina Studio)?
for my application that could be either single or multiuser by having the data file in the same place as the program and images folder under the app folder and without the ASLogin.rsd (sqlite file).
To change to multi user using CubeSQL, smiply have the ASLogin.rsd with the ip address and user name to connect to the database server and moving the data file to the databases folder in CubeSQL
I’m migrating my existing project and SQLite database in Valentina SQLiteServer.
I’m now on my MBP (develop and debug purpose) where i’ve not installed server (and i’m not connected to my network).
So i’ve my SQLite database in my special folder Application Data.
In open app event i have this code (do you think is ok) ?
#If DebugBuild then
Var dbFile As FolderItem = SpecialFolder.ApplicationData.Child(“NiP/NiP.db”)
DB.DatabaseFile = dbFile
Try
DB.Connect()
Catch err As DatabaseException
MessageBox ("Errore: " + err.Message)
Return
End Try
#ElseIf TargetMacOS then
if ArchivioInRete = “SI” then
DB.Host = “localhost”
DB.UserName = “sa”
DB.Password = “sa”
DB.Port = 15532
DB.databaseName = “NiP.db”
Try
DB.Connect()
Catch err As DatabaseException
MessageBox ("Errore: " + err.Message)
Return
End Try
End If
If ArchivioInRete = “NO” Then
rbArchivioInReteNO.Value = True
Var dbFile As FolderItem = SpecialFolder.ApplicationData.Child("NiP/NiP.db")
DB.DatabaseFile = dbFile
Try
DB.Connect()
Catch err As DatabaseException
MessageBox ("Errore: " + err.Message)
Return
End Try
end if
#EndIf
When execute (on my database on my MBP) a query like:
DB.AddRow(“Notifiche”, row)
I receive this error: ERROR DB UNKNOWN and in the debugger:
Database exception ErrorNumber 427264