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 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.

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.

One Response to Using SQL with Adobe AIR

  1. Ola Muldal says:

    Hi Peter,

    I have a question about Flex/AIR, which is not so much related to this post, but i’ll take my chance 🙂

    How would you go about if your client wanted OSlike arrows as mousecursors when a user resize your Atmospheres Music Player in AIR?

    I have that type of case with a client which i am building an application for. MouseEvents are blocked when NativeWindowBoundsEvent.RESIZING is in action, so i cant use the CursorManager since it will freeze in a position.

    Kind regards
    Ola Muldal
    Look at creating skins for the arrows and check the docs for the component styles. There are probably skins for them.