Upgrade SQLite Schema

[growse]

I've got an app that uses a SQLite database to keep stuff in. Is it possible for the client to look at the database, compare it's schema to a given schema and bring the database up to date without losing any current data?

This is so that I can roll out a new client that uses an extra field without people having to delete their old data file.

 

[oneilldo]

yes it is possible but would be complex to do generically. If your new client just has one table that needs an extra field then can't you just add that field to that one table. i.e. in SQL Server the following would be about right
adding a new field called NickName to a Person table

ALTER TABLE Person ADD NickName varchar(50) NOT NULL

Obviously test this first to make sure that no data is lost, if you are still nervous you could backup the table first before changing the schema
SELECT * INTO tableNameBACKUpDateTime from tableName

Your client would then run this SQL once on startup or from the update screen. i.e. you could check if this field exists and if not then run the alter table SQL above.

IF NOT EXISTS (select 1 from sysobjects so, syscolumns sc where so.id = sc.id and so.xtype = 'U' and sc.name = 'NewFieldName'
and so.Name = 'TableName')
BEGIn
ALTER TABLE Person ADD NickName varchar(50) NOT NULL
END

NB: the systables and SQL may be different for SQLLite andall of the above is from memory so syntax/keywords might not be exactly right.

Hope the above is of some help