how to check in sqlite3 whether number of columns are changed or not

Iam coding in c and using sqlite3 as database .I want to ask that how can we check whether no. of columns in a table got changed or not. Situation is like this that i am going to run the application with new executable according to which new columns will be added in the table.So when the DB will be created again application should check whether the table schema is same or not and according to the new schema it should create table.Iam developing application for a embedded enviroment(specifically for a device).

When iam changing the number of columns of table in db and running new executable in the device new tables are not getting created because of the presence of old tables but when iam deleting the old db and creating fresh tables then changes are coming.So how to handle this situation ?

Platform : Linux , gcc compiler Thanks in advance

Pls guide me like this : (assuming Old DB is already present )

That firstly we have to check for the schema of Old DB and if there is any change in some of the tables(like some new columns added or deleted ) then create the new DB according to that .

Answers


Use Versioning and Explicit Column References

You can make use of database versioning to help assist with this sort of problem.

  1. Create a separate table with only one column and one record to store the database version.

  2. Whenever you upgrade your database, set the version number in the separate table.

  3. Design your insert queries to specify the columns.

  4. Define default values for new columns so that old programs insert default values.

Examples
UPDATE databaseVersion SET version=2;
Version 1 Query
INSERT INTO MyTable (id, var1, var2) VALUES (2, '5', '6');
Version 2 Query
INSERT INTO MyTable (id, var1, var2, var3) VALUES (3, '5', '6', '7');

This way your queries should still be compatible on the new DB when using the old program.


Need Your Help

Javafx 2.0 when Applet Security prompt, How to Customize the Background of empty Applet area?

java html applet javafx javafx-2

I have a Javafx 2.0 Applet working well. This applet requires all security permissions. This applet has a Light Grey background (same color as html background page).

Jquery mmenu sub panel with div instead of ul

jquery mmenu

I'm using the excellent Jquery mmenu plugin to create sliding menus. I have a normal menu of ul with li tags and submenus with ul and li works fine, but I'd like to have a submenu that's more like ...