Posts Tagged SQL

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.

, , , ,