ADD COLUMN to sqlite db IF DOESN'T EXIST - flex / air sqlite?

Question

I have a flex / air application that I worked on, it uses the local sqlite database, which is created when the first application starts.

I added some functions to the application, and in the process I had to add a new field to one of the database tables. My questions are: how do I get an application to create one new field that is in a table that already exists?

this is the row 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 would like to add the status_default field.

thank!

Thanks - MPelletier

I added the code you provided and it adds this field, but now, the next time I restart my application, I get an error message - status_default already exists.

So, how can I add some 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

However, adding columns to SQLite is limited. You cannot add a column anywhere but after the last column in the table.

Regarding verifying that a column already exists, PRAGMA table_info(tbl_status); will return a table listing the various columns of your table.

ADD ON:

I use a strategy in database design that allows me to discern which changes are needed. To do this, you will need a new table (name it DBInfo ), with one field (Integer, name it SchemaVersion ). In addition, inside SQLite is called the internal value user_version , which can be set using the PRAGMA . Your code can, at program startup, check the version number of the circuit and apply the changes accordingly, one version at a time.

Assume a function called UpdateDBSchema() . This function will check your version of the database schema, not process DBInfo and determine that the database is in version 0. The rest of this function may just be a large switch with different versions nested in a loop (or an accessible different structure to your choice platform) .

So, for this first version, use the 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 version of the circuit, say LATEST_DB_VERSION , and set it to 1 Thus, your code and your database have a version of the schema, and you know that you need to synchronize 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 create a new function UpgradeDBVersion1To2() that will make the necessary changes.

Thus, your program can be easily ported, can connect and update an old database, etc.

Answer 2

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

I ran into this exact problem in implementing SQLite in Adobe AIR. I thought it would be possible to use the PRAGMA command to resolve, but since the adobe air implementation does not support the PRAGMA command, we need an alternative.

What I did, which, in my opinion, would be worth 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 this helps someone.

Answer 3

I solved a similar problem using the answer to this question: ALTER TABLE ADD COLUMN IF NOT EXISTING IN SQLite

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

 PRAGMA user_version = 1 

and you will get it with

 PRAGMA user_version 

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

Answer 4

In some cases, I execute the command and get an exception for the "duplicate column". Just a quick fix, not an ideal one.

Answer 5

Adding " IF NOT EXISTS to my query made it work for me.

My SQL query in my .sql file was:

 ALTER TABLE results ADD caseid INT UNSIGNED DEFAULT 0; 

and adding IF NOT EXISTS made it work:

 ALTER TABLE results ADD caseid IF NOT EXISTS INT UNSIGNED DEFAULT 0;