sqlite: adding a column and verify its added

Adding a column to an existing sqlite database.... and verify your change... In my case adding an 'ACTIVE' flag to the map table as a boolean where the default value for the column is 1 (TRUE) First copy my original db to a working copy and altering the work copy (

x.db

).

Pro:bin project$ cp vt.db x.db
Pro:bin project$ sqlite3 x.db 'alter table territory_map add column ACTIVE BOOLEAN DEFAULT 1'

Now validating the structures of the original and copied db by listing them from the sqlite_master

Pro:bin project$ sqlite3 vt.db 'select * from sqlite_master where type="table" and name="TERRITORY_MAP" ' > c1
Pro:bin project$ sqlite3 x.db 'select * from sqlite_master where type="table" and name="TERRITORY_MAP" ' > c2

Verify the columns existence and its default value.

Pro:bin project$ diff c1 c2
21c21
<     UPDATED_BY TEXT DEFAULT 'IMPORT' NULL,
---
>     UPDATED_BY TEXT DEFAULT 'IMPORT' NULL, ACTIVE BOOLEAN DEFAULT 1,

Pro:bin project$ sqlite3 -header x.db 'select map_id, abbrev, category, active from territory_map' | head
MAP_ID|ABBREV|CATEGORY|ACTIVE
1|fnny|REG|1
2|fnny|SC|1
3|clny|REG|1
4|clny|SC|1
5|chvt|SC|1
6|chvt|REG|1