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 
) 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.
Great article, very informative
I do however think that while I nearly agree with the scenario assessments you mention as to when it’s viable to use SQLite vs a DBMS, I do think there will be situations where the use of both are not only benficial (say if I need to sync or localize an exact replica of an online DB for use in an offline situation) but also preferred if I want to only distribute selected portions of a DBMS to end users in an embedded form so it speeds up queries, negates server side interaction and is not dependent on a connection while still being able to correlate with it’s larger parent when the end user syncs up (since it’s structure is an exact replica of the DBMS but only comprised of the tables the developer chooses to distribute).
As a developer, I’m not so much excited about the fact I can create a standalone application with an embedded database since it’s not really new ground (ZINC, SWF Studio, etc already do it…and also use other localized DB types like Access) and while of course I will (and others) have need of that in the future, to me the real power is the ability to interact with a larger MySQL (for instance) database if I want and distribute whatever portion of that larger database I choose to localize. Syncronization routines, and applications that need them, will (at least to me) always be far more interesting to develop and what you will see the larger systems that adopt AIR begin to showcase as opposed to an address book app that simply interacts with a local DB. The things that AIR already does far and above what Flash/Flex users are used to with a commercial wrapper (dedicated html engine as opposed to a buggy ActiveX instance, etc, etc etc)…in conjunction with it’s internal ability to now interact with an SQLite DB…will absolutely drive this platform to the top of the heap IMO because what a lot of us Flashers always wanted to do with desktop applications is becoming a reality. The fact it also supports non Flash/Flex users and three targeted OS’s makes it a force to be reckoned with. I see a bright future for AIR indeed.
As far as using a forum for the example in the DBMS portion of the article….I think anything is fodder for localization…even forums ( http://www.flex-fanatic.com/index.php?cid=3&did=215 )
Chris
Thanks for your thoughts Chris, I do agree that it will definitely be preferable in some circumstances to be able sync up data and take processing to the client-side and offline.
I must admit before looking at your link I couldn’t think of a use-case for SQLite based local forum syncing, but seeing it in action makes it more apparent that yes “even forums” can benefit from it.
I also definitely think the ideas and use-cases that haven’t been thought of and documented yet will be the most interesting! Thanks for bringing your post to my attenention looks good
I wanted to add (to clarify) that while commercial wrappers support SQLite, it’s on the end user to bring it onboard. AIR on the other hand has made it part of itself….a very different thing….especially for new users. You’ll see tons of examples of .MDB DB’s being interacted with as example downloads to promote other systems (not mentioning who…they are easily found
) but nearly none that show real world working examples of SQLite being used. AIR on the other hand…even in beta…will have more and more examples surface every day and already has more working examples in the public domain than every other platform that does this stuff combined. That, like every other decision Adobe has made (including using a dedicated runtime) will be what makes AIR stand in a category by itself.
About the time of Apollocamp, I said that IMO two things were needed to make this platform really draw in existing projector/desktop cdevelopers (everything else is cream)…..embedded DB’s and internally handled encryption (not a third party class…but inherent to the API).
I got one wish….crossing my fingers for number two
Hey hi danny!! well i am writing now an AIR database and it is not a whole dream as many of us think…. it actually is a pain trying to code sql databases, i mean zinc can handle databases in three lines including result handlers but AIR sorry guys but i am kinda angry is so annoying and not user-friendly, just as an example within zinc and sqlite i can setup a full database in 5 lines maybe more but not more than 10 but AIR handle all that in like 100 lines!!!! god!!! is that user-friendly??
AS3…strict typing…and three lines? No way man. ZINC offers an SWC that gives users zero control anyway. This is apples and oranges and in an asynchronous setting (because with DB’s being scalable you surely don’t want a synchronous setting) using AS3 there is no way you are running three line anything with wrapped Flex in ZINC conforming to strict typing (synchronous or not).
I also might add that while AIR’s SQLite command set is not only far superior to Zincs in the arena of end user options it also has the ability to not require any Flash content at all (let’s see Zinc create a desktop app with html/javascript) so while you’re actual line count might be higher…it would be anyway in an AS3 strictly typed enviro (say compared with AS2).
As for ease of use….I can safely say that right now there are more…available…downloadable…working sqlite examples…with source code than all existing wrappers combined. I see no bevy of working examples over at MDM and see a rather confused lot of forums posts related to the subject…especially as it relates to AS3. AIR has many examples of Flex/CS3 with SQLite and the SDK just got a major injection of them from Kevin Hoyt which are widely being included with IDE’s like Aptana.
For example…here is an example from Kevin which was originally made for html/javascript and the SDK (called “applied” distributed in Aptana) and ported to Flex in about five minutes. If you are familiar with SQLite and ZINC as you say…I’m really confused as to what is so confusing in AIR other than the fact AS3 requires more lines of code.
(I also recently posted a script from my AIR forum at my site in reply to a message there):
Disregard the hint to an example above ….the comment stripped the script. Feel free to stop by and I’ll provide it.