On 02/08/2011, at 9:03 PM, Jack Hughes wrote: > Is there a workaround for the lack of support for dropping fields?
As others have said, you can create a new table and insert data from the old to new table. Remember to also recreate any needed triggers and indexes. For example, I get my SQLite management app to write all the necessary SQL when the user changes, adds or deletes a column from a table or view, as shown here: http://www.barefeetware.com/databare/trace.html > I have an application and need to evolve the database schema as features are > added and removed. Leaving fields sitting inside the database that are no > longer used will lead to a schema that is difficult to understand. Especially > so when the field is marked as NOT NULL. Years from now I will need to supply > a value for a field when inserting a row that has long ago ceased to be used. If your app has a dedicated purpose, such as a contact database but is not a general purpose database management app, then you probably shouldn't be redefining your schema columns anyway. Can you give some more specific detail on what you're doing here, such as some sample schema changes? For a dedicated purpose app/database, you should probably be instead adding or removing rows or tables as you add or remove features. For instance, if you have a contact database that includes Person and a MySpace contact, but later decide not to have MySpace but you want Facebook contact, there are three ways to do this: 1. Add and remove a column. This is probably the worst way: old table: create table "Person" ( ID integer primary key not null , "Name First" text collate nocase , "Name Last" text collate nocase , "Email" text collate nocase , "MySpace" text collate nocase ) ; change: begin immediate ; create temp table "Person Cache" as select * from "Person" ; drop table "Person" ; create table "Person" ( ID integer primary key not null , "Name First" text collate nocase , "Name Last" text collate nocase , "Email" text collate nocase , "Facebook" text collate nocase ) ; insert into "Person" (ID, "Name First", "Name Last", "Email") select ID, "Name First", "Name Last", "Email" from "Person Cache" ; drop table temp."Person Cache" ; commit ; 2. Or, add and remove rows. Probably the best way, if it fits your needs schema: create table "Person" ( ID integer primary key not null , "Name First" text collate nocase , "Name Last" text collate nocase , "Email" text collate nocase ) ; create table "Service" ( ID integer primary key not null , Name text collate nocase unique not null ) ; insert into "Service" (Name) values ('MySpace') ; create table "Person Service" ( ID integer primary key not null , "Person" integer not null references "Person" (ID) on delete cascade , "Service" integer not null references "Person" (ID) on delete cascade , Name text collate nocase not null ) ; change: delete from "Service" where name = 'MySpace' ; insert into "Service" (Name) values ('Facebook') ; insert into "Person Service" ("Person", "Service", Name) (select ID from "Person" where "Email" = 'mic...@disney.com') , (select ID from "Service" where Name = 'Facebook') , 'mickeymouse' ; 3. Or, add and remove related tables. If method 2 doesn't fit your needs. schema: create table "Person" ( ID integer primary key not null , "Name First" text collate nocase , "Name Last" text collate nocase , "Email" text collate nocase ) ; create table "Person MySpace" ( ID integer primary key not null references "Person" (ID) on delete cascade , Name text collate nocase not null ) ; change: drop table "Person MySpace" ; create table "Person Facebook" ( ID integer primary key not null references "Person" (ID) on delete cascade , Name text collate nocase not null ) ; insert into "Person Facebook" (ID, Name) (select ID from "Person" where "Email" = 'mic...@disney.com') , 'mickeymouse' ; I hope that helps, Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
