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 +

Impressum / About Us