liquid_feedback_core

changeset 182:4c03ef70dd92

Removed trigger "set_activated_timestamp"; Updated update script
author jbe
date Fri Jul 29 15:23:42 2011 +0200 (2011-07-29)
parents e3b0ea7ab2ad
children ed2f94a397cd
files core.sql demo.sql update/core-update.v1.4.0-v1.5.0.sql
line diff
     1.1 --- a/core.sql	Wed Jul 06 23:16:48 2011 +0200
     1.2 +++ b/core.sql	Fri Jul 29 15:23:42 2011 +0200
     1.3 @@ -85,6 +85,7 @@
     1.4          "id"                    SERIAL4         PRIMARY KEY,
     1.5          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
     1.6          "invite_code"           TEXT            UNIQUE,
     1.7 +        "admin_comment"         TEXT,
     1.8          "activated"             TIMESTAMPTZ,
     1.9          "last_login"            TIMESTAMPTZ,
    1.10          "last_login_public"     DATE,
    1.11 @@ -135,13 +136,14 @@
    1.12  
    1.13  COMMENT ON COLUMN "member"."created"              IS 'Creation of member record and/or invite code';
    1.14  COMMENT ON COLUMN "member"."invite_code"          IS 'Optional invite code, to allow a member to initialize his/her account the first time';
    1.15 -COMMENT ON COLUMN "member"."activated"            IS 'Timestamp of first activation of account (set automatically by "set_activated_timestamp" trigger)';
    1.16 +COMMENT ON COLUMN "member"."admin_comment"        IS 'Hidden comment for administrative purposes';
    1.17 +COMMENT ON COLUMN "member"."activated"            IS 'Timestamp of activation of account (i.e. usage of "invite_code"); needs to be set for "active" members';
    1.18  COMMENT ON COLUMN "member"."last_login"           IS 'Timestamp of last login';
    1.19  COMMENT ON COLUMN "member"."last_login_public"    IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
    1.20  COMMENT ON COLUMN "member"."login"                IS 'Login name';
    1.21  COMMENT ON COLUMN "member"."password"             IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
    1.22  COMMENT ON COLUMN "member"."locked"               IS 'Locked members can not log in.';
    1.23 -COMMENT ON COLUMN "member"."active"               IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in (has to be set to TRUE by frontend).';
    1.24 +COMMENT ON COLUMN "member"."active"               IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in (has to be set to TRUE by frontend on every login).';
    1.25  COMMENT ON COLUMN "member"."admin"                IS 'TRUE for admins, which can administrate other users and setup policies and areas';
    1.26  COMMENT ON COLUMN "member"."notify_email"         IS 'Email address where notifications of the system are sent to';
    1.27  COMMENT ON COLUMN "member"."notify_email_unconfirmed"   IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
    1.28 @@ -1130,31 +1132,6 @@
    1.29  
    1.30  
    1.31  
    1.32 ----------------------------------------------------
    1.33 --- Triggers related to member account activation --
    1.34 ----------------------------------------------------
    1.35 -
    1.36 -
    1.37 -CREATE FUNCTION "set_member_activated_timestamp_trigger"()
    1.38 -  RETURNS TRIGGER
    1.39 -  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.40 -    BEGIN
    1.41 -      IF NEW."activated" ISNULL AND NEW."active" THEN
    1.42 -        NEW."activated" := now();
    1.43 -      END IF;
    1.44 -      RETURN NEW;
    1.45 -    END;
    1.46 -  $$;
    1.47 -
    1.48 -CREATE TRIGGER "set_activated_timestamp"
    1.49 -  BEFORE INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
    1.50 -  "set_member_activated_timestamp_trigger"();
    1.51 -
    1.52 -COMMENT ON FUNCTION "set_member_activated_timestamp_trigger"() IS 'Implementation of trigger "set_activated_timestamp" on table "member"';
    1.53 -COMMENT ON TRIGGER "set_activated_timestamp" ON "member"       IS 'Set "activated" to now(), if it is NULL and "active" is set to TRUE';
    1.54 -
    1.55 -
    1.56 -
    1.57  ----------------------------------------------
    1.58  -- Writing of history entries and event log --
    1.59  ----------------------------------------------
     2.1 --- a/demo.sql	Wed Jul 06 23:16:48 2011 +0200
     2.2 +++ b/demo.sql	Fri Jul 29 15:23:42 2011 +0200
     2.3 @@ -3,30 +3,30 @@
     2.4  
     2.5  BEGIN;
     2.6  
     2.7 -INSERT INTO "member" ("active", "login", "name") VALUES
     2.8 -  (TRUE, 'user1',  'User #1'),   -- id  1
     2.9 -  (TRUE, 'user2',  'User #2'),   -- id  2
    2.10 -  (TRUE, 'user3',  'User #3'),   -- id  3
    2.11 -  (TRUE, 'user4',  'User #4'),   -- id  4
    2.12 -  (TRUE, 'user5',  'User #5'),   -- id  5
    2.13 -  (TRUE, 'user6',  'User #6'),   -- id  6
    2.14 -  (TRUE, 'user7',  'User #7'),   -- id  7
    2.15 -  (TRUE, 'user8',  'User #8'),   -- id  8
    2.16 -  (TRUE, 'user9',  'User #9'),   -- id  9
    2.17 -  (TRUE, 'user10', 'User #10'),  -- id 10
    2.18 -  (TRUE, 'user11', 'User #11'),  -- id 11
    2.19 -  (TRUE, 'user12', 'User #12'),  -- id 12
    2.20 -  (TRUE, 'user13', 'User #13'),  -- id 13
    2.21 -  (TRUE, 'user14', 'User #14'),  -- id 14
    2.22 -  (TRUE, 'user15', 'User #15'),  -- id 15
    2.23 -  (TRUE, 'user16', 'User #16'),  -- id 16
    2.24 -  (TRUE, 'user17', 'User #17'),  -- id 17
    2.25 -  (TRUE, 'user18', 'User #18'),  -- id 18
    2.26 -  (TRUE, 'user19', 'User #19'),  -- id 19
    2.27 -  (TRUE, 'user20', 'User #20'),  -- id 20
    2.28 -  (TRUE, 'user21', 'User #21'),  -- id 21
    2.29 -  (TRUE, 'user22', 'User #22'),  -- id 22
    2.30 -  (TRUE, 'user23', 'User #23');  -- id 23
    2.31 +INSERT INTO "member" ("activated", "active", "login", "name") VALUES
    2.32 +  ('now', TRUE, 'user1',  'User #1'),   -- id  1
    2.33 +  ('now', TRUE, 'user2',  'User #2'),   -- id  2
    2.34 +  ('now', TRUE, 'user3',  'User #3'),   -- id  3
    2.35 +  ('now', TRUE, 'user4',  'User #4'),   -- id  4
    2.36 +  ('now', TRUE, 'user5',  'User #5'),   -- id  5
    2.37 +  ('now', TRUE, 'user6',  'User #6'),   -- id  6
    2.38 +  ('now', TRUE, 'user7',  'User #7'),   -- id  7
    2.39 +  ('now', TRUE, 'user8',  'User #8'),   -- id  8
    2.40 +  ('now', TRUE, 'user9',  'User #9'),   -- id  9
    2.41 +  ('now', TRUE, 'user10', 'User #10'),  -- id 10
    2.42 +  ('now', TRUE, 'user11', 'User #11'),  -- id 11
    2.43 +  ('now', TRUE, 'user12', 'User #12'),  -- id 12
    2.44 +  ('now', TRUE, 'user13', 'User #13'),  -- id 13
    2.45 +  ('now', TRUE, 'user14', 'User #14'),  -- id 14
    2.46 +  ('now', TRUE, 'user15', 'User #15'),  -- id 15
    2.47 +  ('now', TRUE, 'user16', 'User #16'),  -- id 16
    2.48 +  ('now', TRUE, 'user17', 'User #17'),  -- id 17
    2.49 +  ('now', TRUE, 'user18', 'User #18'),  -- id 18
    2.50 +  ('now', TRUE, 'user19', 'User #19'),  -- id 19
    2.51 +  ('now', TRUE, 'user20', 'User #20'),  -- id 20
    2.52 +  ('now', TRUE, 'user21', 'User #21'),  -- id 21
    2.53 +  ('now', TRUE, 'user22', 'User #22'),  -- id 22
    2.54 +  ('now', TRUE, 'user23', 'User #23');  -- id 23
    2.55  
    2.56  -- set password to "login"
    2.57  UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0';
     3.1 --- a/update/core-update.v1.4.0-v1.5.0.sql	Wed Jul 06 23:16:48 2011 +0200
     3.2 +++ b/update/core-update.v1.4.0-v1.5.0.sql	Fri Jul 29 15:23:42 2011 +0200
     3.3 @@ -6,7 +6,16 @@
     3.4    SELECT * FROM (VALUES ('1.5.0_devel', 1, 5, -1))
     3.5    AS "subquery"("string", "major", "minor", "revision");
     3.6  
     3.7 +ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE;
     3.8 +ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT;
     3.9 +ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ;
    3.10 +ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE;
    3.11  ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT;
    3.12 +
    3.13 +COMMENT ON COLUMN "member"."created"           IS 'Creation of member record and/or invite code';
    3.14 +COMMENT ON COLUMN "member"."invite_code"       IS 'Optional invite code, to allow a member to initialize his/her account the first time';
    3.15 +COMMENT ON COLUMN "member"."activated"         IS 'Timestamp of activation of account (i.e. usage of "invite_code"); needs to be set for "active" members';
    3.16 +COMMENT ON COLUMN "member"."active"            IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in (has to be set to TRUE by frontend on every login).';
    3.17  COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
    3.18  
    3.19  CREATE TABLE "rendered_member_statement" (
    3.20 @@ -1034,6 +1043,18 @@
    3.21  
    3.22  BEGIN;
    3.23  
    3.24 +UPDATE "member" SET "activated" = "created";
    3.25 +
    3.26 +UPDATE "member" SET
    3.27 +  "created" = "invite_code"."created",
    3.28 +  "invite_code" = "invite_code"."code",
    3.29 +  "admin_comment" = "invite_code"."comment"
    3.30 +  FROM "invite_code"
    3.31 +  WHERE "member"."id" = "invite_code"."member_id";
    3.32 +
    3.33 +DROP TABLE "invite_code_unit";
    3.34 +DROP TABLE "invite_code";
    3.35 +
    3.36  UPDATE "initiative" SET
    3.37      "direct_majority"        = "rank" NOTNULL,
    3.38      "indirect_majority"      = "rank" NOTNULL,
    3.39 @@ -1105,4 +1126,6 @@
    3.40  
    3.41  COMMIT;
    3.42  
    3.43 +ALTER TABLE "member" ADD CONSTRAINT "not_active_without_activated"
    3.44 +  CHECK ("activated" NOTNULL OR "active" = FALSE);
    3.45  ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;

Impressum / About Us