SQLite in a very little network (3 Mac): Valentina Server SQLite is the choice?

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 ?

Is this a home network?

How much writing to or updating the database is there?

If you use WAL mode and implement a connection timeout, it will work better. Have you looked at https://www.sqlite.org/whentouse.html ?

If you doin’t use SQLite, then mysql would be the best alternative.

MySQL Database + MySQL Workbench for non commercial application.
PostgreSQL + Valentina Studio

or add a backup if you fear that the sqlfile file get broken in network drive.

documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-writeaheadlogging

in SQLite you can also do:

db.executeSQL ("PRAGMA busy_timeout = 30000")

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.

CubeSQL would be a perfect match for your scenario.
but investing some time in postgresql would be a good choice too.

2 Likes

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 ?

postgresql came with pgAdmin4 (Web Frontend for Managing)

CubeSQL works with Lite version of Xojo or need a Desktop license?
Any tutorial / example to suggest ?

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

1 Like

Valentina Server only needs to be installed on the MacMini (Server).

For up to five connection and internal use the Valentina Server is available for free: Valentina Server Free Version

Hi Fabrizio,

  1. Valentina SQLite Server can be used for free in your home and even business office.

  2. Free edition gives you 10 concurrent connections for SQLite,
    btw yet 5 also concurrent connections for Valentina Report Server

  3. Valentina Server should be installed only on 1 computer of course.

  4. Migration should be trivial. API is the same RBDB Xojo API.
    SQL is the same - you continue to work with SQLite engine.

  5. you can install also our Valentina Studio to easy manage as a local so remote SQLite database (under Valentina Server).

Hi Ruslan,
what do i need to do with my xojo code to connect to the Valentina sqlite server??

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

Hi Richard,

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

Thank you very much

this IS the very definition of network filesystem that sqlite tells you to not use

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

The structure of my SQLite database can stay as it is (with virtual table and triggers)?

I guess yes, we included the most popular extensions.

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?

Yes, under VServer/sqlite_database folder.

if it is missing the first time, create any dummy under VServer using Valentina Studio, so folder also will be created with the correct name.

I guess it is possible to use hard-links instead files if you want that.

May i’ll still open my database (from inside your Valentina Server) using DB Browser for SQLite (instead with your Valentina Studio)?

In theory, you should avoid this, and DB Server should have exclusive rights on db.

VServer on default use SQLite in WAL mode, and, as I remember, this not allows usage of db from different processes.

Valentina Studio works via a sqlite db under VServer not directly, but via TCPI/IP protocol.

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