Using SQL with Adobe AIR

I’ve not been happy about the performance of my Atmospheres Music Player. I installed it on my home computer where I have a network disk that holds all my CDs – about 240 of them. When I pointed Atmospheres at the network drive, it took a long time for it to read all of the music information from the disk. I had taken steps to make the program responsive while the files were being read, but still, it took a long time. I told my teammate, Kyle Quevillon, about this and he suggested that I cache the information much like other music players do.

What he meant was that Atmospheres shouldn’t read the file system, especially one on a remote disk, as its primary source of information in the music library. A light went off in my head when I realized I could use the SQL Lite database built into Adobe AIR. This was a perfect application of the database: I could have Atmospheres read the file system and store information into a local database, reading that information whenever it started. I’d only read the file system when new CDs were ripped.

If you didn’t know it already, AIR comes with a genuine, lightweight, database – an implementation of SQL Lite. You can create tables, views, insert data, delete data, and run queries. The API to do this is the flash.data package. If you are interested in using the SQL capabilities of AIR, then read on.

Here’s what I did:

I created a class to read the file system and build the Album objects. The Album class already existed and in fact, the code to read the file system already existed, too, in the MusicLibraryViewer class. I created a new class (FileReader) which creates the Album objects and notifies another class once an album has been read.

The other class is the Cache. One function of the Cache class is to take the information from the FileReader (ie, Albums) and store them into a database. Another function is to read the database and build a list of Albums from that information.

Atmospheres starts by getting the Cache to read the database and build the Album list. This is presented in the Atmosphere’s user interface. At any time, especially the first time you run Atmospheres, you can refresh the music library. Doing this causes the FileReader to create an Album which the Cache compares to what it has read from the database. Anything new is put into the database as well as in memory.

Scanning the file system isn’t lightning fast, but when you are expecting it to be slow, it moves along quickly enough and builds the local database. Reading the database is, in my opinion, astonishingly fast. I’ve got about 2,800 tracks from those CDs stored in the local database. It reads that data very quickly and builds the Album list in an eye-blink.

Download
You can download the code here. This is a Flex 3 Beta 3 project archive. If you do not hav Flex 3 Beta 3, find it here.

Now when Atmospheres starts on my home computer, it comes up right away, showing all 240 albums and is ready to play any of them.