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

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:

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). Alternately, there is also an internal value in SQLite called user_version, which can be set with a PRAGMA command. Your code can, on program startup, check for schema version number 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. In your code, add a constant that indicates the latest schema version, say LATEST_DB_VERSION, and set it to 1. In that way, your code and your database have a schema version, and you know you need to synch them if they are not equal.

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

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

I know this is an old question... however.

I've hit this precise problem in the SQLite implementation in Adobe AIR. I thought it would be possible to use the PRAGMA command to resolve, but since adobe air's implementation does not support the PRAGMA command, we need an alternative.

What I did, that I thought would be worth while sharing here, is this:

var sql:SQLStatement = new SQLStatement();
sql.sqlConnection = pp_db.dbConn;
sql.text = "SELECT NewField FROM TheTable";
sql.addEventListener(SQLEvent.RESULT, function(evt:SQLEvent):void {
});

sql.addEventListener(SQLErrorEvent.ERROR, function(err:SQLErrorEvent):void {
    var sql:SQLStatement = new SQLStatement();
    sql.sqlConnection = pp_db.dbConn;
    sql.text = "ALTER TABLE TheTable ADD COLUMN NewField NUMERIC;";
    sql.execute();
    sql.addEventListener(SQLEvent.RESULT, function (evt:SQLEvent):void {
    });
});
sql.execute();

Hope it helps someone.