AIR, local SQL databases, and my role

Posted June 11, 2007 5:37 pm
Filed under: AIR, AS3, ActionScript, Articles by Paul, SQL, Writing, local SQL database

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:

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:

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+ =).

You can leave a comment, or trackback from your own site.

17 Comments so far


  1. Google Gears Blog - is reported to have said:

    links from Technoratito create and use local SQL databases. Okay, that sounds really boring. But I don’t mean it to Posted in Words, punctuated - by Paul Robertson… ([IMG View
    Technorati URL search] 10 links from 7 sites) by probertson [IMG] Original post by Words, punctuated - by Paul Robertson…


  2. JD on EP is reported to have said:

    AIR links, day two…

    AIR links, day two: I’ll update this entry with interesting links found throughout the day. They’ll be in reverse-chronological order, with the most-recently-found on top. (RSS clients may not work for such ongoing changes.) Should be a mix of commer…


  3. baby steps is reported to have said:

    Paul Robertson (adobe) on AIR…

    The addition of embedded database is possibly my favorite new feature in this mountain of features Adobe dropped on us this week. Paul Robertson, an Adobe developer, lets us know why he agrees with me.
    ……


  4. Mike Brunt is reported to have said:

    This is just so bloody exciting, we have a client who deploys personnel in major retail outlets throughout the USA and Canada. They then report back on findings and I can see the incredible value in reporting on a laptop for instance whilst on site and then synchronize that when they have an Internet connection.


  5. DannyT is reported to have said:

    Talking of synchronisation, are there any built in methods or features for syncing back to a master db, for example if you have a number of users using their apollo app whilst out and about is there (currently or planned) some built in functionality to pull all their respective databases into one?


  6. Metah Blog » Blog Archive » Adobe® Integrated Runtime (AIR), and SQLite is reported to have said:

    […] read on Paul Robertson blog’s about some features offered with AIR and local database. The news has been out from some times now […]


  7. Matt is reported to have said:

    well done on getting ‘SQLResult.lastInsertRowID’ added - it would have been a nightmare if that was missing!

    I think AIR has to be the most exciting technology to come from Adobe in a long time, and the SQLite support only makes me more excited about it!


  8. Russ is reported to have said:

    I would really like to see more info/examples on working with pre-populated databases, and synchronization between online and local db. Other than that, great work on the docs.

    Russ


  9. Mike is reported to have said:

    I’m also really exited about the inclusion of local database access for AIR - and am also very pleased about ‘SQLResult.lastInsertRowID’ being part of the API - this was absolutely necessary. However, I’m currently building a small app to try the API out and am running into problems with the asynchronous event driven way of doing things. Within the app I am dealing with a number of databases and am opening, closing and creating databases, but am running into serious problems. I would be really interested in some examples for how to handle these kinds of operations.


  10. Joshua Beall is reported to have said:

    As discussed elswhere (Adobe Labs Forums, ApolloCoders Yahoo Group), there is a strong desires for a synchronous API. I can’t state strongly enough how important I think this feature is. In the word of “rgrzywinski” from the Labs forums, ‘Asynchronous SQLStatements bring pain.’ Forget about proper data modeling or anything like ActiveRecord, without resorting to nasty anonymous inner functions and their ilk.

    I’ve heard rumor that at one of the recent stops of the On AIR tour bus, they demoed an app that used a synchronous sqlite API in AIR. Is this true!? If so, when we expect to get our hands on this API?

    References:
    http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=72&catid=641&threadid=1276219&enterthread=y
    http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=641&threadid=1289146
    http://tech.groups.yahoo.com/group/apollocoders/message/1611


  11. Paul is reported to have said:

    @Joshua:

    I (and others) are certainly aware of all the requests for a synchronous API. Internally that was one of the first questions that came up, too. I’m an almost-daily reader of the Labs forums (under the name “adbe_paul”) and the ApolloCoders list (under the gmail address “paulslists”). I’m not complaining, though — that’s the reason we have those forums, so that we can see how the community feels about our work-in-progress.

    Honestly, the more I’ve worked with the API, the more I find the asynchronous API to be pretty straightforward to work with. It’s different than with PHP/Java/.NET/etc., certainly, and you end up with lots of event listeners, but (to me at least) it’s consistent with remote data loading in ActionScript has worked for years, so I feel like I’ve gotten a pretty good handle on it and I can make it work for what I need it to do. But I know not everyone agrees with me — even among Adobe employees =) — and I’m 100% certain that there are cases where it won’t work — I just haven’t run into them myself.

    The asynchronous API is certainly the recommended approach in general, in order to have better perceived performance (with the database engine running in the background rather than blocking the main thread — the one that causes the screen to redraw). That’s especially true for potentially-large SELECT statements or other groups of multiple statements.

    All warnings and disclaimers aside, however, the synchronous API is in our internal builds, and assuming we get all the bugs worked out, I expect that it will be included in the next public release. (Sorry no dates at this time =)


  12. Tim Anderson’s ITWriting - Tech writing blog » Why doesn’t Adobe’s AIR dev guide mention SQLite? is reported to have said:

    Kramer auto Pingback[…] Paul Robertson has more: http://probertson.com/articles/2007/06/11/adobe-air-local-sql-databases-and-my-role/ […]


  13. davea0511 is reported to have said:

    Thptptp!

    Unless you give Adobe an annual pound of flesh ($300/yr) your application install process will look like a freaking virus is being installed on your client’s machine when they install your AIR program.

    No thanks. I’ll stick with SWF2EXE programs (Northcode, Zinc, Janus, mProjector).


  14. davea0511 is reported to have said:

    PS -
    No offense intended, but man … you’re site is painful to read. Gray lettering on a blue background? Ughh!


  15. Paul is reported to have said:

    Hi Dave,

    Thanks for the comments. One clarification I should make is that the $300/year (or whatever the cost of an app signing certificate is, since it varies) isn’t paid to Adobe and Adobe doesn’t get even a penny from that money. It all goes to whatever certificate provider you buy the certificate from.

    In any case it’s obvious that the cost of a certificate is a big concern for you, and I can definitely pass that feedback on to the AIR management team. I agree with you on this point — I have a few small apps that I’ve built, and some ideas for bigger ones, but since none of them are something I expect to make money from I haven’t been able to justify the expense of a certificate (plus the difficulty of getting one for an individual developer). But it’s still disappointing to know that the apps have the warning message associated with a self-signed certificate.

    I also appreciate your feedback on the site design, although I can’t promise any quick fixes in that regard. It reads pretty well (to me) on the monitors I own and use regularly, although a couple of times I have viewed it on monitors where it didn’t come across very well so I realize it doesn’t always work. Obviously I was going for basics — with more emphasis on getting a site up than on fancy flourishes. I’ve even drawn up some redesign ideas, but I highly doubt I’ll have time anytime soon to implement them. Since I’m not paid for my web site, everything I do for it is voluntary. My assumption has been that people are better served by new content and addressing questions and comments than they would be by a redesign, so that’s where I choose to spend my time. That could be a faulty assumption, of course =)


  16. davea0511 is reported to have said:

    Actually, if the installation popup wasn’t so alarming in appearance it wouldn’t be a big deal.

    If the goal is indeed merely to serve the same function as the unverified/unsigned notification that Microsoft pops up for installing unsecured EXE’s then why didn’t Adobe make it look similar?

    Microsoft’s unsecured-software dialogs have a familiar and non-threatening light-gray background with familiar graphical elements and text. The one AIR provides looks more like a virus warning with it’s black background punctuated with blood-red icons (hyperbole I know but the shoe fits). It’s all about presentation.

    If Adobe would make it look like the unsigned software dialog people are used to seeing from unsigned executables then you’d get no complaints from me.


  17. avi is reported to have said:

    Hi!!!!
    I am trying to store videos(.swf and .flv format) in SQLite locally for AIR1.5 and Flex application.Then,I want to play these videos in video player(made in Flex builder3).There are around 100 files and I want to play them in such a way that we click on particular file name on left panel and file should play in video player in right panel.How can I do this.Kindly give me code and steps for this as soon as possible.

Add your comment





Comment notes

Please keep comments on topic. Comments that are inappropriate or offensive will be edited or removed.

Paragraphs and line breaks are automatically converted to HTML, and quotation marks are converted to “smart” quotes.

The following XHTML tags can be used: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> . All others will be removed.

Articles by Type

Articles by Topic

Random Reading

Currently...

Subscribe