More likely you’re going to get a conflict of some sort.
Let’s say you decide to do some things in a transaction and some things not.
On one session you do this
Start Transaction
Insert a record
On another session, using the same database connection, you do this:
Update a record
Back on the first session,
Insert a record, which fails because of a constraint.
Rollback transaction
What’s going to happen because you’re using one database connection is that the record update on session 2 is going to get rolled back as well because they’re based on connections. This is just one example of the badness. If you’re using SQLite, make sure to put it into multiuser mode, but ALWAYS use a separate database connection for each session.
This isn’t just for Xojo apps by the way. This is true for any multi-user app that has a database back end.
Also, since you’re likely going to need an application update to handle the new schema, just add the database modifications as something you call from app.open. That way, when you update the app, it’ll have the db and ui changes when the users next log in.
These are now two different things, right? The WebApp is shared by all users, but you’ll need to know which active users are using which databases.
How would he determine which ones are connected to which databases? If he has a database per customer, and all customers are using the same WebApp he’ll need to know which users are using which DBs, because he only wants to warn the ones using specific DBs they need to get out for an update.
Yep, point taken. One of the reasons and advantages he wanted to use multiple DBs, one per customer, was not to have everyone get out of the app but only the specific customer that he intends to update their DB for.
Well, from what I understand sqlite can only ever handle one write transaction at a time, no matter how many DB connections there are. That naturally prevents all other sessions or threads from writing to the DB while any write transaction is in progress regardless if there is one or multiple DB connections active.
If some separate process, from a separate thread or something, tries to write to the DB while a write transaction is in progress it would get a SQLITE_BUSY error message.
Hence I se no real problem in using one sqlite DB connection for all sessions, but maybe Im missing something?
1 database file, mutliple database instances (per session) with “WriteAheadLogging = True” (previously called multiuser) witll give you the database you need.
Since xojo web is not multithreaded, and as long as you always start and commit or rollback all your sqlite transactions within the same WebResponse (event), I dont quite see how scenario 1-5 can ever happen, since all transactions will be executed in sequence.
But I may be missing some use case where you need to open a transaction in one WebResponce (event), wail a while, and then commit or rollback in a second WebResponce (event)?
Or maybe if you do some special yielding that lets some other WebResponse execute before your current WebResponce is finished?
Otherwise I see no issues in using one connection for all sessions, since all transactions will be executed in sequence anyway.
Just to confirm each request in Xojo Web will run on its own thread. Using a different connection for each WebSession is the recommended way, as @Greg_O said.
The largest DB’s I’ve used have been with a SIEM I wrote where you can search in a PostgreSQL database of hundreds of millions of log records. 90% of your SQL queries are smart and fast, using indexes, but where you need to perform the odd LIKE ‘%something%’ then the database call can take minutes to retrieve the results.
You could disallow inefficient SQL searches of your large multi-user CRM, but for an increasing number of records each day, this could get slow-creep over time.
My bigger issues against large databases are still backups and security.
