Friday, April 15, 2011

Building demo apps using in-memory databases

A lot of web applications access a database as a primary function. If the user can make changes to that database, it can complicate your ability to provide a demonstration mode for your web app. You probably want the demo user to be able to make changes but since they are not official users yet, you don't want them altering real data. You could duplicate records and give them a demo status of some kind or have a separate demo database, but you still end up having to track the user to undo changes that they made. Fortunately, there's an easier way.

As you probably know, RealSQLDatabase is based on SQLite. SQLite has the ability to create a database, on disk of course, but it also has the ability to create a database that is in-memory only. An in-memory database can be modified but it can't be saved to disk. Using an in-memory database is a great way to allow a demo user to try all the features of your app without giving them the ability to make permanent changes.

To create an in-memory database you simply create a new database object without creating a database file or attaching an existing database file:

Dim Orders as RealSQLDatabase
Orders = New RealSQLDatabase
if Orders.Connect then
//Check Orders.Error
end if

Presto! You've got an in-memory database. You can create tables, add records, perform queries and basically do anything you can do with an on-disk database. If you want to pre-populate the database with tables and records, it's really quite easy to copy the tables and records from an existing RealSQLDatabase into the in-memory database. First, you attach the source database to your in-memory database using the Database.AttachDatabase method:

Dim source as Folderitem = GetFolderItem("Orders.sdb")
If Orders.AttachDatabase(source,"Source") Then

Then, you use SQLExecute to create a table and use a SELECT statement to specify the table and records from the source database. For example, the following code copies a table called “Invoices” and all of its records from the source database into the in-memory database (which in his case is the Orders property):

Orders.SQLExecute("CREATE TABLE Invoices As SELECT * FROM Source.Invoices")

Now you’ve got an in-memory database with a table and data copied from your source database! Pretty sweet huh? When using this with a web project, if you create the Orders property on the Session class, each user can have their own in-memory database that they can change as much as you allow without affecting other users. As soon as the user leaves the application and the session ends, the in-memory database will vanish. Of course, you can use this technique with desktop applications as well.

In-memory databases, along with the ease with which you can copy tables and records into them from another source database, make for a really powerful and easy way to provide demo apps for the web and the desktop.

No comments: