Article by Steven.
Published: April 28, 2009 at 16:46
Category: AIR, ActionScript 3

SQLite database updates: the necessary heavyweight solution

< back to overview

A while ago, I’ve written my first real, technically complex AIR application for a client. Like I mentioned in another post then (http://blog.multimediacollege.be/2009/03/using-the-local-encrypted-database-in-air-theory-and-practise/) , the theory and practice of the SQLite database that is shipped with AIR don’t always match. And so, now I had to find out another annoying thing about this very very very lightweight database.

Because of some additional changes that had to be made to the application, the necessity to update the database structure arose. I had a table column that was datatyped as an integer, but it had to change to a real, because decimal values had to be accepted. So I started programming my SQL statements like I used to do with the usual databases like Oracle, MySQL or even PostgreSQL. Only to see them fail miserably.

So, after doing some research on the internet, I found out that apparently SQLite doesn’t support the ALTER TABLE statement to alter columns. What’s up with that? I always taught SQL-92 was a standard, but it seems that SQLite doesn’t follow this and I can’t think of any reason why. Then I found that in SQLite the data type supposedly doesn’t matter, because it doesn’t use strict data typing (see http://www.sqlite.org/datatype3.html). So, you should be able to place a decimal value into an integer column, but when I tried this out in AIR, I did get SQL errors, so that is not quite right in the documentation.

But anyway, I was stuck with the situation and had to find a solution for it. The obvious solution would be to create a work-around for it. So, this is what I came up with:

1. Create a temporary table with the same structure as the existing one.

query.text = “CREATE TABLE IF NOT EXISTS temp_table ( “

+ ” table_id INTEGER PRIMARY KEY AUTOINCREMENT,”

+ ” field1 TEXT NOT NULL,”

+ ” field2 INTEGER NOT NULL)”;

query.execute();

2. Copy the data into that temporary table

query.text = “INSERT INTO temp_treatment SELECT * FROM treatment”;

query.execute();

Now, normally I would joine these 2 first steps into one statement that would look like this:

query.text = “CREATE TABLE IF NOT EXISTS temp_table “

+ ” AS SELECT * FROM table”;

query.execute();

But, unfortunately, again this is also not supported by the SQLite database in AIR. So that is why the elaborate version is necessary.

3. Drop the existing table

query.text = “DROP TABLE treatment”;

query.execute();

4. Recreate the table again, but this time with the proper data type for the field that has to change

query.text = “CREATE TABLE IF NOT EXISTS temp_table ( “

+ ” table_id INTEGER PRIMARY KEY AUTOINCREMENT,”

+ ” field1 TEXT NOT NULL,”

+ ” field2 REAL NOT NULL)”;

query.execute();

5. Copy the data back from the temporary table

query.text = “INSERT INTO table SELECT * FROM temp_table”;

query.execute();

6. Drop the temporary table

query.text = “DROP TABLE temp_table”;

query.execute();

I find this to be a very heavyweight solution to a very basic problem with maintaining databases. So, hopefully, in the next version of AIR and/or SQLite, a lot more database maintenance support is included, because this is really putting me off using an SQLite database.

< back to overview

Name (required)

Mail (will not be published) (required)

Website