Add inactive state to categories.

A new 'state' column replaces confirmed and deleted, allowing
categories to be unconfirmed, confirmed, deleted or inactive.

3 files changed, 94 insertions, 5 deletions

diff --git a/db/downgrade_0051---0050.sql b/db/downgrade_0051---0050.sql
new file mode 100644
index 000000000..424420e29
--- /dev/null
+++ b/db/downgrade_0051---0050.sql
@@ -0,0 +1,36 @@
+BEGIN;
+
+ALTER TABLE contacts ADD confirmed boolean;
+ALTER TABLE contacts ADD deleted boolean;
+
+UPDATE contacts SET confirmed='t', deleted='t' WHERE state = 'deleted';
+UPDATE contacts SET confirmed='f', deleted='t' WHERE state = 'inactive';
+UPDATE contacts SET confirmed='t', deleted='f' WHERE state = 'confirmed';
+UPDATE contacts SET confirmed='f', deleted='f' WHERE state = 'unconfirmed';
+
+ALTER TABLE contacts ALTER COLUMN confirmed SET NOT NULL;
+ALTER TABLE contacts ALTER COLUMN deleted SET NOT NULL;
+ALTER TABLE contacts DROP COLUMN state;
+
+ALTER TABLE contacts_history ADD confirmed boolean;
+ALTER TABLE contacts_history ADD deleted boolean;
+
+UPDATE contacts_history SET confirmed='t', deleted='t' WHERE state = 'deleted';
+UPDATE contacts_history SET confirmed='f', deleted='t' WHERE state = 'inactive';
+UPDATE contacts_history SET confirmed='t', deleted='f' WHERE state = 'confirmed';
+UPDATE contacts_history SET confirmed='f', deleted='f' WHERE state = 'unconfirmed';
+
+ALTER TABLE contacts_history ALTER COLUMN confirmed SET NOT NULL;
+ALTER TABLE contacts_history ALTER COLUMN deleted SET NOT NULL;
+ALTER TABLE contacts_history DROP COLUMN state;
+
+CREATE OR REPLACE FUNCTION contacts_updated()
+    returns trigger as '
+    begin
+        insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted);
+        return new;
+    end;
+' language 'plpgsql';
+
+COMMIT;
+
diff --git a/db/schema.sql b/db/schema.sql
index 18c1533d9..d35071c0f 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -69,8 +69,12 @@ create table contacts (
     body_id integer not null references body(id),
     category text not null default 'Other',
     email text not null,
-    confirmed boolean not null,
-    deleted boolean not null,
+    state text not null check (
+        state = 'unconfirmed'
+        or state = 'confirmed'
+        or state = 'inactive'
+        or state = 'deleted'
+    ),
 
     -- last editor
     editor text not null,
@@ -102,8 +106,12 @@ create table contacts_history (
     body_id integer not null,
     category text not null default 'Other',
     email text not null,
-    confirmed boolean not null,
-    deleted boolean not null,
+    state text not null check (
+        state = 'unconfirmed'
+        or state = 'confirmed'
+        or state = 'inactive'
+        or state = 'deleted'
+    ),
 
     -- editor
     editor text not null,
@@ -118,7 +126,7 @@ create table contacts_history (
 create function contacts_updated()
     returns trigger as '
     begin
-        insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted);
+        insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, state) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.state);
         return new;
     end;
 ' language 'plpgsql';
diff --git a/db/schema_0051-inactive-contact-state.sql b/db/schema_0051-inactive-contact-state.sql
new file mode 100644
index 000000000..f29455f18
--- /dev/null
+++ b/db/schema_0051-inactive-contact-state.sql
@@ -0,0 +1,45 @@
+BEGIN;
+
+ALTER TABLE contacts ADD state text;
+
+ALTER TABLE contacts ADD CONSTRAINT contacts_state_check CHECK (
+    state = 'unconfirmed'
+    or state = 'confirmed'
+    or state = 'inactive'
+    or state = 'deleted'
+);
+
+UPDATE contacts SET state = 'deleted' WHERE deleted;
+UPDATE contacts SET state = 'confirmed' WHERE confirmed AND NOT deleted;
+UPDATE contacts SET state = 'unconfirmed' WHERE NOT confirmed AND NOT deleted;
+
+ALTER TABLE contacts ALTER COLUMN state SET NOT NULL;
+ALTER TABLE contacts DROP COLUMN confirmed;
+ALTER TABLE contacts DROP COLUMN deleted;
+
+ALTER TABLE contacts_history ADD state text;
+
+ALTER TABLE contacts_history ADD CONSTRAINT contacts_history_state_check CHECK (
+    state = 'unconfirmed'
+    or state = 'confirmed'
+    or state = 'inactive'
+    or state = 'deleted'
+);
+
+UPDATE contacts_history SET state = 'deleted' WHERE deleted;
+UPDATE contacts_history SET state = 'confirmed' WHERE confirmed AND NOT deleted;
+UPDATE contacts_history SET state = 'unconfirmed' WHERE NOT confirmed AND NOT deleted;
+
+ALTER TABLE contacts_history ALTER COLUMN state SET NOT NULL;
+ALTER TABLE contacts_history DROP COLUMN confirmed;
+ALTER TABLE contacts_history DROP COLUMN deleted;
+
+CREATE OR REPLACE FUNCTION contacts_updated()
+    returns trigger as '
+    begin
+        insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, state) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.state);
+        return new;
+    end;
+' language 'plpgsql';
+
+COMMIT;