As everyone knows, today Adobe released a public beta of AIR (formerly “Apollo”). As you likely know, since it was announced last week, one of the big new features in this release is an integrated database engine that allows AIR applications to create and use local SQL databases.

Okay, that sounds really boring. But I don’t mean it to. I’m actually incredibly excited by this, because it makes it a lot easier for people like me, with web app experience but not desktop app experience, to create data-driven apps and store persistent data using techniques that I’m familiar with.

And, on a personal note, it means that I finally get to talk about what I’ve been working on for the last couple of months. If you actually follow my web site, you’ve probably picked up on the fact that I was heavily involved in the ActionScript-related documentation for the Flash CS3 release. Well, naturally, now that Flash CS3 is out the door, I’ve been moved onto another project – onto Apollo/AIR, specifically.

More specifically, since I’ve been programming SQL databases for many years, as part of my web app development work, I got pegged (well, I actually volunteered) to do the documentation and samples for the local SQL databases feature.

When I first read the spec for the feature, I was completely floored. I was expecting some minimal support for a few things, but what we’ve got is much more than I could come up with use cases for. Want a good idea of the breadth of the functionality that’s available? Spend some time reading ”SQL support in local databases” (it’s an appendix of the AIR language reference). Views? Indexes? Triggers? They’re in there.

In case you don’t have a free few hours, I’ll just point out my favorite parts of the feature. These will probably be most meaningful if you’ve already faced the joy and pain of working with web-database apps, especially with an OOP language:

• SQLStatement.itemClass: This was my immediate favorite. You specify a class, and SELECT statement result rows are automatically converted into instances of that class (saving lots of boilerplate code to loop through results and turn rows into instances of some other class). If I could have done this in ASP.NET, I’d probably have saved about 25% of the total code I wrote.
• SQLStatement.prepare() and SQLStatement.parameters: Now that I’ve spent some time building apps and working with the code, I’ve gotten a lot of respect for this method. Basically, this is the way to create the equivalent of pre-compiled stored procedures for your AIR app.
• SQLResult.lastInsertRowID: I had to lobby long and hard for this one, which, since I’m a remote employee, meant lots of email exchanges. Finally I managed to clearly articulate my reason, and sure enough, persistence paid off. If you’ve created a database app, chances are you’ve run into the case where you INSERT a row, and you need to get back the auto-generated primary key so that you can insert a related row. The wrong way to do it is SELECT MAX(id_column) FROM table. The right way, in AIR, is to use lastInsertRowID.

I’m excited that I can talk about this now. I’ve got some samples, practices, and information that I’m looking forward to sharing. I’ll start with an answer to a question that I’ve seen asked around (well, mostly I’ve just seen misinformation, not people asking whether it’s right) about the relationship between the AIR local SQL database API and the Google Gears SQL database API:

• Does Apollo “include” part of Google Gears? - No. There is no shared code between AIR and Google Gears (with the possible exception noted in the next answer).
• What do AIR and Google Gears have in common, then? - Both AIR and Google Gears let you create applications that access databases located on a users local machine. Both AIR and Google Gears chose to use SQLite, a free, public domain embedded SQL database engine, to provide that functionality. So whatever code AIR uses that hasn’t been modified from SQLite, is the same as the code that Google Gears uses that hasn’t been modified in its implementation of SQLite.
• What’s this about AIR including the same database API as Google Gears? - To be honest, although I’ve been involved in the AIR database API for a while, the first I heard of Google Gears’ database API was when the public announcements were made. Thinking back, I see now that discussions were going on for a while, and I even unknowingly provided some support to the management team and others who were involved in that discussion. Right now, although the underlying database engines are based on the same engine, since SQLite is written in C, any implementation that doesn’t use C/C++ needs to write its own API. The two implementations (Adobe’s and Google’s) weren’t developed together, and at this point (from what I’ve seen) the two APIs are pretty different. Case in point: synchronous versus asynchronous database operations. In Google Gears, data access operations are synchronous – calls to the database are blocking, meaning the runtime freezes at the line of code that called the database until the result is returned. In AIR, on the other hand, all data operations are asynchronous – you call SQLStatement.execute() to run a query, and when the result comes back an event listener function is called (at which point the result data can be accessed). That alone means a big difference in how you write code to work with the two systems.
• So wait a minute, what about the whole “Adobe and Google are working together on the database API” thing? - Adobe and Google are having “discussions,” and (from what I’ve heard) the plan is to hopefully make the APIs the same or similar enough that a developer who writes data access code for Google Gears will have an easy time writing data access code for AIR (and vice versa). In addition, since the SQL part of both runtimes really is dependent on SQLite much more than the particular runtime implementation, and SQL code probably is interchangeable between the two runtimes, assuming the same database schema etc.

So from me, and the other engineers and stakeholders inside Adobe, please try out the local SQL database functionality of Apollo, and please let us know what we can do to make it better. In particular, let me know what is missing or what you’d like to see in terms of documentation and samples – but don’t limit yourself to that. Please share your comments/suggestions!

On a more personal note…

I’m really excited about this. I really just can’t say in words how excited I am. When I decided to accept an offer to work full-time for Adobe, one of the first “regrets” that crossed my mind was when I considered that it was highly likely that I wouldn’t be doing any more database programming (since my work involves dealing with ActionScript, and up to now there hasn’t really been any direct database access from ActionScript). So I was excited to say the least when I heard about this feature and it was decided that I’d get to work on it.

Suffice it to say, this has been a pretty busy time. This feature was actually slated to appear in a later release, but at the near-last minute the decision was made to get it done in time for the public beta. That meant a lot of writing and application-building in a hurry! Then two weeks and a private beta release later, a group of people including me, engineers and QEs, and other interested folks, went through a few rounds of discussions on what was missing and what we could do to make the API better. The result, which of course still isn’t finished, is what you can download today.

And, although she isn’t a developer and doesn’t use Apollo/AIR at all, it’s an understatement to say that my wife is glad to see this beta out the door, if only because it means I don’t have to work evenings any more (it’s been a very busy month+ =).