Posts Tagged SQLite

Introduction to Adobe AIR

I know this isn’t the usual content, it being introductory level, but I got to write this piece in order to try to raise the general awareness about Adobe AIR where I work. My true goal being that project managers and account directors get a better feel of AIR and push it more to the clients, resulting,  in the end, that I would get to do more AIR apps (i got plenty of these little machinations). I am not sure it got the attention it deserves, so I am putting it here, where, with a little Google love it might get a bigger audience.

I’ve been playing lately with Adobe AIR and I was thinking that it might be a good idea to give you an overview of what it is to help us all grasp its full potential.

AIR is made by Adobe and is based on the Flash Player. What this means is that it can do almost all of the things that you would expect from a Flash file (.swf). The particularity of AIR comes from the fact that it doesn’t need to be embedded into an HTML page to work. An AIR application is made to runs on your desktop, just like any other application.

Let’s take a look at how an AIR application differentiates itself from a regular Flash file.


In order to run an AIR application, you must first download the AIR runtime; this is similar to how Flash files require the Flash Player. Once the AIR runtime is installed, you can begin downloading and installing AIR packages (.air files).

When offering AIR applications for download, you can use what’s known as a badge. A badge is a little piece of Flash that you put on your website that will do two things:

  1. First, the badge will check if the user has the AIR runtime installed. If not, it will install the runtime first.
  2. Then, the badge will proceed to download and install the desired AIR application.

This makes the installation process remarkably painless. For an example of a badge in action check out Agile Agenda. You will see the badge roughly in the middle of the page.

Automatic Updates

Another nice feature of Air is the fact that an application can keep itself up to day by automatically checking for new versions on a central server. If a newer version is found, the application will download the updated version of itself and install it. This is a great way to manage code enhancements and bug fixes after the initial application release or while the client is reviewing the final product prior to launch.

Cross-platform functionality

Just like Flash, AIR applications should function precisely the same way on all systems, regardless of operating system. This means we should see the same results on Macs as we do on PCs and other platforms.

Database interactions

AIR can create and interact with a local database (SQLite), as well as write and read files from the user’s hard drive. This allows developers to create applications that can function even without the need of an internet connection.

Additional features

On top of the remarkable features noted above, AIR also features:

  • To open a file, simply drag and drop a file over the AIR application;
  • AIR applications can run in the background;
  • Applications can send tray notifications, much like when a user connects to MSN Messenger;
  • Applications can be included within the start menu in the same way as other common applications;
  • AIR can detect whether the system is connected to the internet or not;
  • …and many more.

But what is it made of?

AIR applications are built using ActionScript 3. This means applications can be developed in Flex, Flash, or even by using javascript. The latter means that AIR applications can be developed by Integrators as well, not only Flash developers. Common libraries that I have seen used for this purpose include jQuery and Dojo.

A few notable drawbacks

While AIR has many wonderful qualities, it is not without its faults. It is not yet suited for just any type of desktop application. The current version is 1.5, which means it is still in its infancy. AIR applications can on occasion take up a lot of memory, and it can sometimes run rather slow. Accordingly, its use should be limited to building widgets and small sized applications.

However, even with these limitations, AIR is a great tool to have in your toolbox. I hope you now have a better understanding of this emerging technology.

I also have to give some love to StrategicText, because he did some editing to the text and made it even better.

, ,


Date range query using SQLite and AIR

I decided I would investigate more on Dates and SQLite. As you know I had some trouble when trying to extract a date range from my SQLite database because Flash and SQLite don’t save dates in the same format.  Flash saves it in UTC format and SQLite as a Julian day.

Looking around a bit I found on the php website some information about how to do the conversion from Julian day to UTC.  All I had to do was to turn the formula around. But that didn’t work because the Date in Flash is also offested to account for the timezone of where the computer is. Once that was solved, everything worked fine. Here is a function that will do the conversion:

function UTCToJulianDay(newDate:Date):Number{
  return ( newDate.time - (newDate.getTimezoneOffset()*60000))  / 86400000) + 2440587.5)

The time method of Date object return milliseconds so we have to change everything in milliseconds. The getTimezoneOffset  method returns minutes so by multiplying by 60000 you get its millisecond equivalent. 86400000 is the number of milliseconds in a day and 2440587.5 is the julian day at 1/1/1970 0:00 UTC.

So using this function will enable you to use the greater than, lesser than and BETWEEN operator in SQLite when you want to search for date ranges. Here are some example of SQL query you might do:

sqlStmt.text = "SELECT * FROM main.Entry WHERE dateF >; " + UTCToJulianDay(new Date());
//this will return all entries where dateF is bigger than now
var date1:Date = new Date(2009, 2, 3) ; //March 3rd 2009
var date2:Date = new Date(2009, 3, 3); //April 3rd 2009
sqlStmt.text = "SELECT * FROM main.Entry WHERE dateF BETWEEN " + UTCToJulianDay(date1) + " AND " + UTCToJulianDay(date2) ;
//this will return all entries  Between March 3rd and April 3rd

It is that easy to do date range comparison; you just have to know how.

, , , , , ,


Comparing dates in SQLite and AIR

I was doing an application where I had to compare dates in my SQLite database, actually I needed to get from it all entries from yesterday. It turns out that this is not a so obvious operation in SQLite.

The first part of the operation is to get yesterday in date format. To do so I used the logic from Pixelbox post:

public static const MILLISECOND:Number = 1;
public static const SECOND:Number = MILLISECOND * 1000;
public static const MINUTE:Number = SECOND * 60;
public static const HOUR:Number = MINUTE * 60;
public static const DAY:Number = HOUR * 24;
public static const WEEK:Number = DAY * 7;
var yesterday:Date = new Date();
yesterday.time -= DateUtilities.DAY;

That part was easy.

Now comparing yesterday with dates in the databse was the hard part. It turns out that SQLite stores dates as a Julian day( interval of time in days and fractions of a day, since January 1, 4713 BC Greenwich noon) and that Flash saves them as UTC time (number of milliseconds since midnight January 1, 1970, universal time) which makes it pretty hard to compare.

Now I solved my problem this way:

"SELECT * FROM main.Entry WHERE STRFTIME('%d', dateF) = '" + 
DateUtils.lpad(String(, 2, "0") + "' AND STRFTIME('%m', dateF) = '" + 
DateUtils.lpad(String(yesterday.month + 1), 2, "0") ;

Where lpad is just a function to add a leading 0 if my day or month is smaller than 10 and dateF is the name of my date column in my database.  In this example, I got from the database all entry where the date had the same day and the same month as yesterday. I didn’t compare the year but it follows the same logic, here is a list of other parameters you can feed to the STRFTIME function.

Now this will only help you if you need a single day from your database, but if you are looking for a range comparison this will not help you. I will be looking for a way to convert UTC time to Julian Day and check if that works. Stay posted.

, , ,


AIR SQLite Optimization tricks

I have been playing with AIR lately and I have mostly been optimizing the interactions between the application and its SQLite database because that’s where I noticed the biggest speed impediment. Having a PHP/MySQL background, some of these tricks were not so obvious to me. Most of what I will list here come from this help page from Adobe, but I thought I would give more example since some weren’t so clear to me at first.

Specify column names in a SELECT or INSERT statement.

Well this is not specific to AIR, but it is still really easy to implement so I thought it was worth mentioning. So in a SELECT Statement list the column name you want instead of using the *. Even if you need all the columns, list them all, it’s going to execute faster.

var selectStmt:SQLStatement = new SQLStatement();
//so instead of doing this:
selectStmt.text =  "SELECT * FROM myTable";
//do this:
selectStmt.text =  "SELECT columnName1, columnName2 FROM myTable";

Always write the name of the database when you write the name of a table

I don’t know just how much speed gain this is suppoed to give but it is pretty easy to do so I suggest doing this all the time. Now, at first I was confused by  how I could find the name of the database I wanted to do a query on. It happens that most of the time, the name of your database is just going to be “main”. This can change when you use the attach method of a SQLConnection to add multiple database to a connection to do a query on tables from different database. When you attach a new database you will have to provide a name (you can choose what you want) and the first database will still be called “main”

So from my previous example, if we follow this advise, it will look like this:

var selectStmt:SQLStatement = new SQLStatement();
selectStmt.text =  "SELECT columnName1, columnName2 FROM main.myTable";
//main.myTable instead of just myTable

Parametrize your statements

This is something I was completely new to. From my PHP/MySQL experience here is what I would have written for an UPDATE statement with a WHERE clause:

_updateStmt = new SQLStatement();
_updateStmt.sqlConnection = _conn;
_updateStmt.text = "UPDATE main.myTable SET statusF=" + currentStatus + "  WHERE keyId=" + currentId;

This is ok if you will use that statement only once in your application, but if you are going to use it multiple times like in a for loop, you’d better use a parametrized statement. It seems like a costly operation is preparing the SQLStatement and a statement is prepared everytime you change its text property. Now here is how you would parametrize the previous example:

_updateStmt = new SQLStatement();
_updateStmt.sqlConnection = _conn;
_updateStmt.text = "UPDATE main.myTable SET statusF=@STATUS  WHERE keyId=@ID";
_updateStmt.parameters["@STATUS"] = currentStatus;
_updateStmt.parameters["@ID"] = currentId;

Again, just like this it doesn’t save much, it starts to make sense in a for loop_updateStmt = new SQLStatement();

_updateStmt.sqlConnection = _conn;
_updateStmt.text = "UPDATE main.myTable SET statusF=@STATUS  WHERE keyId=@ID";
for (var i:uint = 0; i < currentArray.length; i++){
  _updateStmt.parameters["@STATUS"] = currentArray[i].status;
  _updateStmt.parameters["@ID"] = currentArray[i].id;

Use Transactions

This is probably the biggest optimisation you can do. The principle of a transaction is that instead of executing all your statement separately, it will execute them all at the same time. Refer to the previously linked help file from Adobe for more information on this. Transactions are best suited for query that don’t need results like INSERT and UPDATE statements. Transactions are easy to implement:_updateStmt = new SQLStatement();

_updateStmt.sqlConnection = _conn;
_updateStmt.text = "UPDATE main.myTable SET statusF=@STATUS  WHERE keyId=@ID";
_conn.begin();//_conn is a SQLConnection, I didn't take the time to write the code for it, but this is where the magic happens
for (var i:uint = 0; i < currentArray.length; i++){
  _updateStmt.parameters["@STATUS"] = currentArray[i].status;
  _updateStmt.parameters["@ID"] = currentArray[i].id;

Only when the application will run the commit method will all the SQLStatement will be executed.

This is all the SQLite optimization tricks I have for now, but I still have to investigate indexes which might also help speed up queries.

, , , ,


My journey into AIR

The last two weeks I got to play around with AIR for the first time. I was fun, but it was also a bumpy ride. Bumpy for the fact that I had a deadline and because it seems that every step of the way there was something that wasn’t what I expected (which I guess is normal when learning something new). What I want to gather here is some quick facts and links about what you should know to get started with AIR.

First things first, you have to be able to compile a swf using AIR APIs. I think this is fairly easy to do with Flex Builder, but I didn’t want to use it. I didn’t want to use Flash either because I forsee in the future that multiple persons could work on the project at the same time and FLAs are not that teamwork friendly. My final choice was tu do an Actionscript project using FlashDevelop. What got me confused, at first, was the fact that when you want to create such a project in FlashDevelop; it is called an AIR AS3 Projector. Well once you are past that, the rest is done for you: the folder structure and the bat files to package your application. Now you can get started.

Since it was my first AIR project, there was one thing that I really wanted to do right: the Update functionality. Since an AIR application has the ability to update itself, if you do that part right, you can make mistakes on the rest since you’ll be able to send updates later. I encourage you to read this article from Adobe Development Center on Using the Adobe Air Update Framework.  You will also need the applicationupdater.swc library that is inlcuded in the Adobe AIR SDK to use the update framework. Another quick thing about the update process is that will updating AIR will erase everything in the folder your application was installed to, so if you want to save stuff from previous version, you will have to move them first.

Because of the previous fact, you may want to know if it is the first time ever that the user open your application. Detecting that is easy once you think about it, but is not obvious when you come from a web background. What you do is check if a certain file (from your application) has  been created on the user’s computer. If it is created that means the user has started the application at least once before. If not, it means this is the first time so you go ahead and create that file and do everything you need for the first run. I have been very vague about the file, but my guess is that every application will need to move at least a config file from the installation folder, so you use that config file to do the check. More information in the help for Adobe.

The rest of what you might want to do should go smoothly: creating  a chromeless application, implementing the grag, minimize, maximise and close function is all a breeze when you consult the help files. Well I said the rest but there is still something that is not so obvious; working whit SQLite.

I am used to working whit database, before doing full time flash I also did some PHP/MySQL, but AIR/SQLite is a different beast. It is mostly details but when you add them up, it makes a pretty big bump (to refer to my previous bumpy ride). You can work synchronously or asynchronously, SQLite DATE primitive is a bit weird and you can parametrize SQLStatements. I will write another post about SQLLite optimization later.

Well that is it for now, that should help people used to AS3 find their way quickly while doing AIR development.

, , , ,

1 Comment

Parse error: syntax error, unexpected ';' in /homepages/25/d169645162/htdocs/wp-content/themes/fusion/footer.php on line 13