liquid_feedback_core
changeset 42:0dfc5e164b52
Save timestamps of last login and member deactivation; Deletion of suggestions with attached opinions now possible (on delete cascade)
author | jbe |
---|---|
date | Thu Mar 25 18:33:11 2010 +0100 (2010-03-25) |
parents | d0c3390ed42b |
children | 1779e2303c08 |
files | core.sql update/prepare-beta25-beta26.sql |
line diff
1.1 --- a/core.sql Mon Mar 08 19:08:08 2010 +0100 1.2 +++ b/core.sql Thu Mar 25 18:33:11 2010 +0100 1.3 @@ -6,7 +6,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('beta24', NULL, NULL, NULL)) 1.8 + SELECT * FROM (VALUES ('beta26', NULL, NULL, NULL)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -56,6 +56,7 @@ 1.13 CREATE TABLE "member" ( 1.14 "id" SERIAL4 PRIMARY KEY, 1.15 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.16 + "last_login" TIMESTAMPTZ, 1.17 "login" TEXT NOT NULL UNIQUE, 1.18 "password" TEXT, 1.19 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.20 @@ -118,9 +119,10 @@ 1.21 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.22 "until" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.23 "login" TEXT NOT NULL, 1.24 + "active" BOOLEAN NOT NULL, 1.25 "name" TEXT NOT NULL ); 1.26 1.27 -COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members'; 1.28 +COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names, login names and active flag of members'; 1.29 1.30 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)'; 1.31 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid'; 1.32 @@ -601,7 +603,7 @@ 1.33 "member_id" INT4, 1.34 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0), 1.35 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE, 1.36 - FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.37 + FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.38 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.39 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id"); 1.40 1.41 @@ -792,9 +794,14 @@ 1.42 RETURNS TRIGGER 1.43 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.44 BEGIN 1.45 - IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN 1.46 - INSERT INTO "member_history" ("member_id", "login", "name") 1.47 - VALUES (NEW."id", OLD."login", OLD."name"); 1.48 + IF 1.49 + NEW."login" != OLD."login" OR 1.50 + NEW."active" != OLD."active" OR 1.51 + NEW."name" != OLD."name" 1.52 + THEN 1.53 + INSERT INTO "member_history" 1.54 + ("member_id", "login", "active", "name") 1.55 + VALUES (NEW."id", OLD."login", OLD."active", OLD."name"); 1.56 END IF; 1.57 RETURN NULL; 1.58 END;
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/prepare-beta25-beta26.sql Thu Mar 25 18:33:11 2010 +0100 2.3 @@ -0,0 +1,25 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('incomplete_update_from_beta24_to_beta25', NULL, NULL, NULL)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +ALTER TABLE "member" ADD COLUMN "last_login" TIMESTAMPTZ; 2.11 +ALTER TABLE "member_history" ADD COLUMN "active" BOOLEAN; 2.12 + 2.13 +UPDATE "member_history" SET "active" = TRUE; 2.14 +INSERT INTO "member_history" ("member_id", "login", "active", "name") 2.15 + SELECT "id", "login", TRUE AS "active", "name" 2.16 + FROM "member" WHERE "active" = FALSE; 2.17 + 2.18 +COMMIT; 2.19 + 2.20 +-- Complete the update as follows: 2.21 +-- ========================================= 2.22 +-- pg_dump --disable-triggers --data-only DATABASE_NAME > tmp.sql 2.23 +-- dropdb DATABASE_NAME 2.24 +-- createdb DATABASE_NAME 2.25 +-- psql -v ON_ERROR_STOP=1 -f core.sql DATABASE_NAME 2.26 +-- psql -v ON_ERROR_STOP=1 -f tmp.sql DATABASE_NAME 2.27 +-- rm tmp.sql 2.28 +