I am new on Xojo and I have some questions for web database applications. I am thinking to use sqlite as a database server because I will have around 10000 connections per month. If I open the sqlite database during the App.Open event, can I use the instance of database for all the needed transaction of the web application ? OR can I open and close the database for each transaction ? What it will be the impact, if I have 50 simultaneously users which execute transactions ? Can the Sqlite to open 50 times on the same time or the database is going to corrupt ? For performance, which is the best way to open the database ? Can I have multiple instance of SQLiteDatabase object ? Or to have one instance and all the transactions to execute from that ? In which object (web page/Session/App) to open the database and handle the instance .
Each user (session) must have its own connection to the database. It must be done in the Session.Open event (SQLITEdatabase.connect).
If you enable WAL mode (SQLITEdatabase.multiuser=true), SQLITE can read and write data at the same time. There may be many readers at the same time, but only one writer. This requires that the write transactions are short and fast.
You can run a single instance of sql database in the app space, but not recommended. My recommendation is to use a true server based database, and yes each session should have it’s own connection.
Regardless of whether you use SQLite or a database server, have one connection per Session, and one on the main thread. Be sure you are using the right connection for the thread (Session) where you’re running. Keep transactions tight. Regardless of database, locking and blocking will be your most tenacious opponents.
Looking at your requirements, I’d say they sound either way over-engineered or a bit optimistic for a first project where you are figuring out the art of Web Edition. Either way, start with SQLite and see if it ends up being good enough for the actual task. the WE app is already doing 90% of what a database server has to do, managing multiple connections. Take advantage of that. I think you’d be surprised how well SQLite solutions can scale if you’re smart and creative in your approach.