ADD COLUMN to sqlite db IF NOT EXISTS - flex/air sqlite?

Question

I've got a flex/air app I've been working on, it uses a local sqlite database that is created on the initial application start.

I've added some features to the application and in the process I had to add a new field to one of the database tables. My questions is how to I go about getting the application to create one new field that is located in a table that already exists?

this is a the line that creates the table

                stmt.text = "CREATE TABLE IF NOT EXISTS tbl_status ("+"status_id INTEGER PRIMARY KEY AUTOINCREMENT,"+" status_status TEXT)";

And now I'd like to add a status_default field.

thanks!

Thanks - MPelletier

I've add the code you provided and it does add the field, but now the next time I restart my app I get an error - 'status_default' already exists'.

So how can I go about adding some sort of a IF NOT EXISTS statement to the line you provided?

Answer 1

ALTER TABLE tbl_status ADD COLUMN status_default TEXT;

http://www.sqlite.org/lang_altertable.html

That being said, adding columns in SQLite is limited. You cannot add a column anywhere but after the last column in your table.

As for checking if the column already exists, PRAGMA table_info(tbl_status); will return a table listing the various columns of your table.

ADD ON:

That being said, I've been using a strategy in database design that allows me to distinguish which modifications are required. For this, you will need a new table (call it DBInfo), with one field (Integer, call it SchemaVersion). Your code can, on program startup, check for this table and and apply changes accordingly, one version at a time.

Suppose a function named UpdateDBSchema(). This function will check for your database schema version, handle DBInfo not being there, and determine that the database is in version 0. The rest of this function could be just a large switch with different versions, nested in a loop (or other structure available to your platform of choice).

So for this first version, have an UpgradeDBVersion0To1() function, which will create this new table (DBInfo), add your status_default field, and set SchemaVersion to 1.

When you need to make another change to your schema, set a constant (call it LATEST_DB_VERSION) to 2, make a new UpgradeDBVersion1To2() function that will perform the required changes.

That way, your program can be ported easily, can connect to an upgrade an old database, etc.