# HG changeset patch
# User jbe
# Date 1269538391 -3600
# Node ID 0dfc5e164b5266bf618a092d13ffab2378d828e7
# Parent  d0c3390ed42b627b1afbb10cf6c54d2b6356f941
Save timestamps of last login and member deactivation; Deletion of suggestions with attached opinions now possible (on delete cascade)

diff -r d0c3390ed42b -r 0dfc5e164b52 core.sql
--- a/core.sql	Mon Mar 08 19:08:08 2010 +0100
+++ b/core.sql	Thu Mar 25 18:33:11 2010 +0100
@@ -6,7 +6,7 @@
 BEGIN;
 
 CREATE VIEW "liquid_feedback_version" AS
-  SELECT * FROM (VALUES ('beta24', NULL, NULL, NULL))
+  SELECT * FROM (VALUES ('beta26', NULL, NULL, NULL))
   AS "subquery"("string", "major", "minor", "revision");
 
 
@@ -56,6 +56,7 @@
 CREATE TABLE "member" (
         "id"                    SERIAL4         PRIMARY KEY,
         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
+        "last_login"            TIMESTAMPTZ,
         "login"                 TEXT            NOT NULL UNIQUE,
         "password"              TEXT,
         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
@@ -118,9 +119,10 @@
         "member_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
         "until"                 TIMESTAMPTZ     NOT NULL DEFAULT now(),
         "login"                 TEXT            NOT NULL,
+        "active"                BOOLEAN         NOT NULL,
         "name"                  TEXT            NOT NULL );
 
-COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members';
+COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names, login names and active flag of members';
 
 COMMENT ON COLUMN "member_history"."id"    IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
@@ -601,7 +603,7 @@
         "member_id"             INT4,
         "degree"                INT2            NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
         "fulfilled"             BOOLEAN         NOT NULL DEFAULT FALSE,
-        FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
+        FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
         FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
 
@@ -792,9 +794,14 @@
   RETURNS TRIGGER
   LANGUAGE 'plpgsql' VOLATILE AS $$
     BEGIN
-      IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN
-        INSERT INTO "member_history" ("member_id", "login", "name")
-          VALUES (NEW."id", OLD."login", OLD."name");
+      IF
+        NEW."login"  != OLD."login"  OR
+        NEW."active" != OLD."active" OR
+        NEW."name"   != OLD."name"
+      THEN
+        INSERT INTO "member_history"
+          ("member_id", "login", "active", "name")
+          VALUES (NEW."id", OLD."login", OLD."active", OLD."name");
       END IF;
       RETURN NULL;
     END;
diff -r d0c3390ed42b -r 0dfc5e164b52 update/prepare-beta25-beta26.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/update/prepare-beta25-beta26.sql	Thu Mar 25 18:33:11 2010 +0100
@@ -0,0 +1,25 @@
+BEGIN;
+
+CREATE OR REPLACE VIEW "liquid_feedback_version" AS
+  SELECT * FROM (VALUES ('incomplete_update_from_beta24_to_beta25', NULL, NULL, NULL))
+  AS "subquery"("string", "major", "minor", "revision");
+
+ALTER TABLE "member" ADD COLUMN "last_login" TIMESTAMPTZ;
+ALTER TABLE "member_history" ADD COLUMN "active" BOOLEAN;
+
+UPDATE "member_history" SET "active" = TRUE;
+INSERT INTO "member_history" ("member_id", "login", "active", "name")
+  SELECT "id", "login", TRUE AS "active", "name"
+  FROM "member" WHERE "active" = FALSE;
+
+COMMIT;
+
+-- Complete the update as follows:
+-- =========================================
+-- pg_dump --disable-triggers --data-only DATABASE_NAME > tmp.sql
+-- dropdb DATABASE_NAME
+-- createdb DATABASE_NAME
+-- psql -v ON_ERROR_STOP=1 -f core.sql DATABASE_NAME
+-- psql -v ON_ERROR_STOP=1 -f tmp.sql DATABASE_NAME
+-- rm tmp.sql
+