What is an AIR database?

As mentioned in my last post I want to explain to the un-database-initiated what a database in AIR is and how it differs to other databases you might have heard of or experienced.

Database Support In AIR

Adobe have recently added support for databases in AIR through support of SQLite. From the SQLite website:

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine.

What this means is that SQLite enables database functionality within a single system file without the need for a running database server or complex database applications. The real merit to this is that it is highly deployable and as such is a great addition to AIR. To better understand this it might pay dividend to see how this is different to traditional databases.

Other Common Databases

If you work in web development the liklihood is you will have worked with or at least heard of SQL Server and/or MySQL. These are the most commonly used databases in web development so we'll start here to help understand SQLite.

SQL Server and MySQL are Database Management Systems (DBMS), in that they work on a schema of tables of data and identifying relationships between those tables. This is very much the same in SQLite, you store your data in tables (think spreadsheets) and you can link ("relate") those tables to each other through the use of "keys" (more on this in a later article).

However, SQL Server and MySQL are Database Servers - they are hosted on a network accessible resource (I.e a server), requests are sent to the database server and information is returned. SQLite differs here because an SQLite database resides on the client machine in a single file and all database operations are carried out on that specific file.

Interactions between a MS SQL Server/MySQL database and an SQLite database are much the same in format (the surprisingly titled 'Structured Query Language') but SQLite interactions are carried out on a file whereas SQL Server/MySQL are carried out on the server. Understanding this difference will be key in assessing when to make use of the built in support for SQLite and when to use traditional DBMSs which will still require the use of a server-side language (e.g. php, .net or java).

When to use SQLite

So database server databases reside on servers (woah :P) and SQLite databases are files on the local filesystem. So are they viable alternatives or are there use-cases for one and use-cases for the other? There absolutely are different use cases for each approach:

Database Servers

Where all users need to share the same information - E.g. Forums and community sites

Users aren't necessarily going to be accessing from the same machine - E.g. Remote vs office working

High volume of data transactions - single file local databases aren't going to cut it with millions of data transactions. Modern database servers can handle much higher loads.

Concurrency Requirements- On a local database file with SQLite your database is either being accessed and therefore locked (I.e. one transaction at a time). database servers have much more advanced complex record locking and update mechanisms which are useful again for high volumes of data requests and updates.

SQLite

Disconnected Working - E.g. For applications when offline working is required

User preferences - Where the user audience need not know each others details E.g. layout settings

Local Processing - If alot of work is being done at the database that doesn't need to be publicly shared E.g. summary reporting, calulations etc.

Best of both worlds

SQLite in AIR allows you to take your data driven applications and make use of local resources and avoid unecessary remote server calls. SQLite will not completely eliminate the need for remote code and databases but it will certainly help to enhance the desktop application experience. Database servers allow for shared access to data and distribution of data, combine this with SQLite for local, disconnected data access and you can really add value to your rich internet desktop applications.