Advice sought: How to implement the following scenario?

Requirements:

Xojo only (no plugins)
Central SQLite database (nobody can administrate PostGres or MySQL)
Accessed from 1-20 different client computers and mobile devices (iOS and Android)
Fairly low power / data throughput (the biggest will be syncing the database on connection so the client has a local copy to work with)
Clients connected on internal network or if external via VPN
Must be secure (eg encrypted database, encrypted communication)
Must be extremely easy to use

Questions:

How should I structure this? I was thinking a central server app that deals with user management, changes/requests to the database, and communicates with the client apps.

What protocol to use best for communication?

Desktop or Webserver?

Don’t want to reinvent the wheel if anything open source is out there (Aloe?)

Anything else I need to think of?

As I haven’t done ANYTHING yet with servers and inter-app communication I’d be thankful for any advice that comes my way

Markus

web app, and browsers in for all users, local or remote.

A server sounds scary at first. But the communication and the SQL aren’t that different from the local version. Your server connection can go away at any second and your app (desktop or web) needs to handle this.

How much money do you have? There is a simple SQLite server available in Xojo only code. But I haven’t done much testing. 75$ or so as far as I remember. Valentina Server as SQLite server is not as expensive as the SQLite Labs version.

Not sure where you get the $75 from … what I see on the Valentina website is quite a bit more.

50$: http://www.pdtsoftware.com/newsite/index.html

Ah, that’s what you meant. Thanks, will check it out.

Would still like some advice on the original question though.

I use CubeSQL as a central SQLite server. My desktop apps connect to and manipulate data inside the cubesql server. It’s not free, but it’s very good.

However, I think the right solution for the requirements you’ve posted is a simple webapp. Clients interact via their browser, so there is nothing to install. There are no databases to sync because everything lives in the webapp instance. Your scenario is really ideal for a small webapp.

[quote=436226:@Kimball Larsen]I use CubeSQL as a central SQLite server. My desktop apps connect to and manipulate data inside the cubesql server. It’s not free, but it’s very good.

However, I think the right solution for the requirements you’ve posted is a simple webapp. Clients interact via their browser, so there is nothing to install. There are no databases to sync because everything lives in the webapp instance. Your scenario is really ideal for a small webapp.[/quote]
I have to agree. Otherwise, you really need some sort of central server process like CubeSQL or PostgreSQL. I have an app here that just cannot handle concurrent usage of a sqlite database between processes due to Xojo bugs. You’ll be fighting a losing battle with it until you get some kind of server process that is designed for concurrent database access.

So either web app or database server. Either way, you’re not get away from the need for a server.

What do you mean by “processes” - helper apps or Xojo threads?

I thought for a small number of a xojo app using using SQLite in WAL mode would be fine for 10-20 users… That is what what was planning with an Xojo Aloe Server app using threads accessing the SQLite DB…

So what kind of issues did you encounter?

  • karen

Xojo processes != xojo threads. Multiple threads accessing the same db from the same process should be fine. Multiple processes (especially if you have the database file stored on a network share so that multiple different computers are involved) is fraut with danger - corrupted sqlite database is the most likely outcome.

Bottom line: If you have multiple computers involved, either build a web app or host a local database server.

Thom’s bug report seems to imply otherwise if you want the DB to yield during potentially long operations in threads:
<https://xojo.com/issue/55556>

  • karen

You could take a look st the two videos done by Paul which demonstrate how to set up a web app accessing the SQLITE database and another app asking the Web app for information. By using the web app to do the work, it is possible to have many users accessing data from SQLite.

Is there anything to administrate once you’ve set it up? I’ve got clients where I setup Postgres and a nightly backup service app, and the Postgres server has been running for years without so much as a phone call or email to me regarding that specific component.

New 1U server spec’d for growth, RAID drives, make sure everything is secure, regular scheduled DB backups, have some type of alert system in case your backups (to another server) fail for any reason…PostgreSQL will just run.

[quote=436240:@Karen Atkocius]What do you mean by “processes” - helper apps or Xojo threads?

I thought for a small number of a xojo app using using SQLite in WAL mode would be fine for 10-20 users… That is what what was planning with an Xojo Aloe Server app using threads accessing the SQLite DB…

So what kind of issues did you encounter?

  • karen[/quote]
    Both Xojo threads and helper apps actually. I use CriticalSection to control transactions with a single database connection so Xojo threads don’t get their transactions confused. This works great most of the time.

But if I give each thread it’s own connection to the database with wal active, then database locks produce errors. This is to be expected though and once again, CriticalSection would be the answer to ensure that one thread doesn’t try to write while another thread wants to. In this case, one connection per thread or a single connection doesn’t really make a difference.

But here’s where it gets interesting. Using a helper app, a Mutex is needed to control write permission between the helper(s) and the master. But in some Xojo versions, locking Mutex on the main thread makes the app lock permanently.

So it sounds like using a single process with CriticalSection sounds good, right? Well not exactly. SQLite doesn’t yield to threads by default. Using ThreadYieldInterval with a value that is too low to complete the desired work also causes the app to lock permanently. Since the interval must be greater than your longest action - and who really knows what that would be - it also defeats the purpose of the property entirely since it won’t yield any faster than not yielding anyway.

In my experience, trying to “cheat” concurrency with xojo and sqlite will have problems in one way or another. Just use an external engine and make your life simpler. The effort required to get that external engine setup and running will be less than you’ll spend fighting with these behaviors.

[quote=436443:@Thom McGrath]But if I give each thread it’s own connection to the database with wal active, then database locks produce errors. This is to be expected though and once again, CriticalSection would be the answer to ensure that one thread doesn’t try to write while another thread wants to. In this case, one connection per thread or a single connection doesn’t really make a difference.

But here’s where it gets interesting. Using a helper app, a Mutex is needed to control write permission between the helper(s) and the master. But in some Xojo versions, locking Mutex on the main thread makes the app lock permanently.

[/quote]
Can’t you avoid all of that by just checking the Sqlite error code on the connection for a write lock after an attempted write, and if it was locked, after a short interval retry the write, be it from be it from threads or helper apps ?

  • Karen

Things get really complicated when dealing with prepared statements and trying to reexecute with the same values, without allowing the code to continue.

BTW for an in-house app (LAN only) is there a reason not to have client connect directly to say a postgreSQL DB on a machine that has a LAN only IP address?

Isn’t using middleware for DB client access is mainly a security thing? If so, then if the DB can’t be accessed from the outside there would be any issues with that?

  • Karen

[quote=436521:@Karen Atkocius]BTW for an in-house app (LAN only) is there a reason not to have client connect directly to say a postgreSQL DB on a machine that has a LAN only IP address?

Isn’t using middleware for DB client access is mainly a security thing? If so, then if the DB can’t be accessed from the outside there would be any issues with that?

  • Karen[/quote]
    For a lan-only connection, that should be safe.

Middleware provides both security and abstraction. The abstraction layer can change logic in a lot of ways to maintain support for legacy versions and fix bugs without releasing a whole update. The cost is development time, performance, and an added source of potential bugs.

Somewhere in between would be using a direct connection and storing lots of logic in the pg database.

[quote=436521:@Karen Atkocius]BTW for an in-house app (LAN only) is there a reason not to have client connect directly to say a postgreSQL DB on a machine that has a LAN only IP address?

Isn’t using middleware for DB client access is mainly a security thing? If so, then if the DB can’t be accessed from the outside there would be any issues with that?

  • Karen[/quote]

No it is not just a security thing, although it is certainly a big part of it.

  • It’s an administration thing. You will have to manage all users at the database level

  • It’s a data integrity thing. Once users have access rights on a database level they can connect with any tool they like and make changes. So you are going to need a very robust database.

I think you’ll find a lot of people would disagree on that one…

You could use a container and ship a preconfigured instance.