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?

Warfold

Any reason why you haven't checked the SQLite SQL syntax at sqlite.org? The ALTER TABLE command syntax to add a new column does not have any IF NOT EXISTS type of modifiers, which means you will just have to handle the exception/error in your own code. – Kwok

Adam, I've updated my answer. Please see below. – Ketosis

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:

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.

Ketosis

The user can set and change there default at any time so I can't give it a set value. – Warfold

Thanks again MPelletier that really helped me out! – Warfold

This is how RoundhousE is implemented. github.com/chucknorris/roundhouse/wiki – Democratic

Please provide the query necessary. Much of the difficulty is piecing together everything you mentioned. – Ferule

@TimothyGonzalez it's not a single query. It's a database migration strategy. – Ketosis

OP didn't ask for a database migration strategy. They asked how to conditionalize an add column statement. You said where the data is, but didn't show how to write the conditionalized query. The question still remains unanswered. What's needed is a simple idempotent query. – Ferule

@TimothyGonzalez SQLite does not offer this. – Ketosis

Answer 2

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.

Dilemma

Helpful in case the pragma is not available, but the solution by MPelletier is what any developer should do to handle database versioning and upgrades – Return

PRAGMA is NOT available in the OP's scenario, hence posting it as the correct solution for this particular scenario. – Dilemma

Answer 3

In some cases I execute the command and get the exception for "duplicate column". Just a quick solution, not the perfect.

Cahn

Answer 4

I solved a similar problem using the answer from this question: ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

Use built in user_version parameter to keep track of your updates. You set it using:

PRAGMA user_version = 1

and you retrieve it using

PRAGMA user_version

So basically retrieve user_version (it's 0 by default), check if it's 0. If yes, perform your updates and set it to 1. If you have more updates in the future, check if it's 1, perform updates and set it to 0. And so on...

Adey