Monday, October 4, 2010

Can a web application use a single user database?

There have been some questions about whether or not a web application could use the single user database engine, REALSQLDatabase. In theory, the app is a server of sorts (managing all the users that are connected) and it would be the only application communicating with the database file which is how many database servers work.

One big difference between REALSQLDatabase and a database server is that database servers handle record locking. When one user wants to edit a record, the record can be locked by the database server so that others users can't edit it. REALSQLDatabase has no facility for that. You could create your own system by storing an array of objects either globally on the app class (or in a module) or as a property of each session where each session would keep track of which record or records it is editing.

REALSQLDatabase is based on SQLite. SQLite allows for any number of users to read the database at the same time but allows only one user to write to the database at a time. If a user attempts to write to the database at the same time another user is doing so, an error will be generated. You can determine this by checking the Error property of your REALSQLDatabase object immediately after doing anything that updates the database. Because of SQLite, REALSQLDatabase is very fast at updating records and in our limited testing, we could not get it to do two updates at the same time and produce an error.

Currently, when you add a database connection to your web project the connection is handled globally rather than by session. If your app's database access is user-driven, as in a case where the user initiates a query for example, then you should connect to your database via code so that you can store the database reference on the session class. With this technique, each session will make a separate connection to the database and SQLite can manage things properly (as best it can). Before we ship our web framework, we will likely change this internally so that database connections added to the project will automatically be session-based.

So depending on the nature of your application and the number of users that would need to write to the database at the same time, REALSQLDatabase very well may work for you. Just keep in mind that you will want to test for an error after any update to the database (which you should be doing anyway) and that if you need some kind of record locking, you'll have to implement your own system for that.

If you have a traditional database-oriented application, you will be better served (no pun intended) by using a traditional database server.

12 comments:

Zozem said...

Wouldn't it just make more sense to use a MySQL Community server?

Karen said...

Does starting a transaction lock the DB or does that only happen on commit?

Geoff Perlman said...

@ Karen - SQLite's locking system is described here: http://www.sqlite.org/lockingv3.html

Geoff Perlman said...

@ Zozem - Most people are not aware of this but the MySQLCommunity Server cannot be used for commercial purposes. That means is not an option for some people.

tkaltschmidt said...

@Geoff - There is a good alternative to MySQL-Server: MariaDB, which claims to be compatible with MySQL and adds even more features. Sounds good, the original MySQL Developer Monty Widenius is working for this project. I'm not sure, if Real Studio needs new database plugin for this, though.

pdfkon said...

Using the RealSQLDatabase in "MultiApp or MultiUser Mode" is an easy task. It needs care on all situations where the records are called. e.g. Read, Write, Delete! Just check the DB for ErrorNumber 5 and then handle the locked DB. For example go into a Loop after Error 5 and try again, until the Error is gone.

Valdemar said...

Someone can post an example of use ? the demo made by Geoff was very interesting... could post the order example used in Web Studio ?
Thanks

Travis said...

@Geoff- actually under the version of the GPL that the 'free' MySQL is licensed under- it would be perfectly fine to use for Web Edition software, because neither the Server nor the Client Libraries are ever publicly redistributed/disseminated.

That is different from desktop apps, where the client libs are by definition distributed with the community plugin.

With that said, I personally think people are generally better off with a "more free" platform on a from-scratch basis as many are available, MySQL just has a long history...

mark said...

I have been visiting your blog for a while now and I always find a your new posts. Thanks for sharing.
real estate transaction software.

jamesee said...

I know that REAL is moving away from REAL Server, but wouldn't that be an ideal server to use with Web Edition? Is this possible?

Geoff Perlman said...

Yes, REAL Server (now from SQLabs and soon to be renamed) can be used with our Web framework. In fact, the great thing about our database connectivity is that you can use any database and write the code the same way you would with a desktop app.

jamesee said...

Thanks Geoff. I was hoping you would say that.