liquid_feedback_core

changeset 181:e3b0ea7ab2ad

Removed table "invite_code" (invite codes are now stored in "member" table)
author jbe
date Wed Jul 06 23:16:48 2011 +0200 (2011-07-06)
parents 1024224df943
children 4c03ef70dd92
files core.sql demo.sql
line diff
     1.1 --- a/core.sql	Wed Jun 08 20:53:36 2011 +0200
     1.2 +++ b/core.sql	Wed Jul 06 23:16:48 2011 +0200
     1.3 @@ -84,12 +84,14 @@
     1.4  CREATE TABLE "member" (
     1.5          "id"                    SERIAL4         PRIMARY KEY,
     1.6          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
     1.7 +        "invite_code"           TEXT            UNIQUE,
     1.8 +        "activated"             TIMESTAMPTZ,
     1.9          "last_login"            TIMESTAMPTZ,
    1.10          "last_login_public"     DATE,
    1.11          "login"                 TEXT            UNIQUE,
    1.12          "password"              TEXT,
    1.13          "locked"                BOOLEAN         NOT NULL DEFAULT FALSE,
    1.14 -        "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    1.15 +        "active"                BOOLEAN         NOT NULL DEFAULT FALSE,
    1.16          "admin"                 BOOLEAN         NOT NULL DEFAULT FALSE,
    1.17          "notify_email"          TEXT,
    1.18          "notify_email_unconfirmed"     TEXT,
    1.19 @@ -117,7 +119,9 @@
    1.20          "external_posts"        TEXT,
    1.21          "formatting_engine"     TEXT,
    1.22          "statement"             TEXT,
    1.23 -        "text_search_data"      TSVECTOR );
    1.24 +        "text_search_data"      TSVECTOR,
    1.25 +        CONSTRAINT "not_active_without_activated"
    1.26 +          CHECK ("activated" NOTNULL OR "active" = FALSE) );
    1.27  CREATE INDEX "member_active_idx" ON "member" ("active");
    1.28  CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
    1.29  CREATE TRIGGER "update_text_search_data"
    1.30 @@ -129,12 +133,15 @@
    1.31  
    1.32  COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
    1.33  
    1.34 +COMMENT ON COLUMN "member"."created"              IS 'Creation of member record and/or invite code';
    1.35 +COMMENT ON COLUMN "member"."invite_code"          IS 'Optional invite code, to allow a member to initialize his/her account the first time';
    1.36 +COMMENT ON COLUMN "member"."activated"            IS 'Timestamp of first activation of account (set automatically by "set_activated_timestamp" trigger)';
    1.37  COMMENT ON COLUMN "member"."last_login"           IS 'Timestamp of last login';
    1.38  COMMENT ON COLUMN "member"."last_login_public"    IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
    1.39  COMMENT ON COLUMN "member"."login"                IS 'Login name';
    1.40  COMMENT ON COLUMN "member"."password"             IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
    1.41  COMMENT ON COLUMN "member"."locked"               IS 'Locked members can not log in.';
    1.42 -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.';
    1.43 +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.44  COMMENT ON COLUMN "member"."admin"                IS 'TRUE for admins, which can administrate other users and setup policies and areas';
    1.45  COMMENT ON COLUMN "member"."notify_email"         IS 'Email address where notifications of the system are sent to';
    1.46  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.47 @@ -178,24 +185,6 @@
    1.48  COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)';
    1.49  
    1.50  
    1.51 -CREATE TABLE "invite_code" (
    1.52 -        "id"                    SERIAL8         PRIMARY KEY,
    1.53 -        "code"                  TEXT            NOT NULL UNIQUE,
    1.54 -        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.55 -        "used"                  TIMESTAMPTZ,
    1.56 -        "member_id"             INT4            UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
    1.57 -        "comment"               TEXT,
    1.58 -        CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
    1.59 -
    1.60 -COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
    1.61 -
    1.62 -COMMENT ON COLUMN "invite_code"."code"      IS 'Secret code';
    1.63 -COMMENT ON COLUMN "invite_code"."created"   IS 'Time of creation of the secret code';
    1.64 -COMMENT ON COLUMN "invite_code"."used"      IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
    1.65 -COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
    1.66 -COMMENT ON COLUMN "invite_code"."comment"   IS 'Comment on the code, which is to be used for administrative reasons only';
    1.67 -
    1.68 -
    1.69  CREATE TABLE "setting" (
    1.70          PRIMARY KEY ("member_id", "key"),
    1.71          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.72 @@ -758,14 +747,6 @@
    1.73  COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
    1.74  
    1.75  
    1.76 -CREATE TABLE "invite_code_unit" (
    1.77 -        PRIMARY KEY ("invite_code_id", "unit_id"),
    1.78 -        "invite_code_id"        INT8            REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.79 -        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.80 -
    1.81 -COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
    1.82 -
    1.83 -
    1.84  CREATE TABLE "privilege" (
    1.85          PRIMARY KEY ("unit_id", "member_id"),
    1.86          "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.87 @@ -1149,10 +1130,36 @@
    1.88  
    1.89  
    1.90  
    1.91 +---------------------------------------------------
    1.92 +-- Triggers related to member account activation --
    1.93 +---------------------------------------------------
    1.94 +
    1.95 +
    1.96 +CREATE FUNCTION "set_member_activated_timestamp_trigger"()
    1.97 +  RETURNS TRIGGER
    1.98 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.99 +    BEGIN
   1.100 +      IF NEW."activated" ISNULL AND NEW."active" THEN
   1.101 +        NEW."activated" := now();
   1.102 +      END IF;
   1.103 +      RETURN NEW;
   1.104 +    END;
   1.105 +  $$;
   1.106 +
   1.107 +CREATE TRIGGER "set_activated_timestamp"
   1.108 +  BEFORE INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
   1.109 +  "set_member_activated_timestamp_trigger"();
   1.110 +
   1.111 +COMMENT ON FUNCTION "set_member_activated_timestamp_trigger"() IS 'Implementation of trigger "set_activated_timestamp" on table "member"';
   1.112 +COMMENT ON TRIGGER "set_activated_timestamp" ON "member"       IS 'Set "activated" to now(), if it is NULL and "active" is set to TRUE';
   1.113 +
   1.114 +
   1.115 +
   1.116  ----------------------------------------------
   1.117  -- Writing of history entries and event log --
   1.118  ----------------------------------------------
   1.119  
   1.120 +
   1.121  CREATE FUNCTION "write_member_history_trigger"()
   1.122    RETURNS TRIGGER
   1.123    LANGUAGE 'plpgsql' VOLATILE AS $$
     2.1 --- a/demo.sql	Wed Jun 08 20:53:36 2011 +0200
     2.2 +++ b/demo.sql	Wed Jul 06 23:16:48 2011 +0200
     2.3 @@ -3,30 +3,30 @@
     2.4  
     2.5  BEGIN;
     2.6  
     2.7 -INSERT INTO "member" ("login", "name") VALUES
     2.8 -  ('user1',  'User #1'),   -- id  1
     2.9 -  ('user2',  'User #2'),   -- id  2
    2.10 -  ('user3',  'User #3'),   -- id  3
    2.11 -  ('user4',  'User #4'),   -- id  4
    2.12 -  ('user5',  'User #5'),   -- id  5
    2.13 -  ('user6',  'User #6'),   -- id  6
    2.14 -  ('user7',  'User #7'),   -- id  7
    2.15 -  ('user8',  'User #8'),   -- id  8
    2.16 -  ('user9',  'User #9'),   -- id  9
    2.17 -  ('user10', 'User #10'),  -- id 10
    2.18 -  ('user11', 'User #11'),  -- id 11
    2.19 -  ('user12', 'User #12'),  -- id 12
    2.20 -  ('user13', 'User #13'),  -- id 13
    2.21 -  ('user14', 'User #14'),  -- id 14
    2.22 -  ('user15', 'User #15'),  -- id 15
    2.23 -  ('user16', 'User #16'),  -- id 16
    2.24 -  ('user17', 'User #17'),  -- id 17
    2.25 -  ('user18', 'User #18'),  -- id 18
    2.26 -  ('user19', 'User #19'),  -- id 19
    2.27 -  ('user20', 'User #20'),  -- id 20
    2.28 -  ('user21', 'User #21'),  -- id 21
    2.29 -  ('user22', 'User #22'),  -- id 22
    2.30 -  ('user23', 'User #23');  -- id 23
    2.31 +INSERT INTO "member" ("active", "login", "name") VALUES
    2.32 +  (TRUE, 'user1',  'User #1'),   -- id  1
    2.33 +  (TRUE, 'user2',  'User #2'),   -- id  2
    2.34 +  (TRUE, 'user3',  'User #3'),   -- id  3
    2.35 +  (TRUE, 'user4',  'User #4'),   -- id  4
    2.36 +  (TRUE, 'user5',  'User #5'),   -- id  5
    2.37 +  (TRUE, 'user6',  'User #6'),   -- id  6
    2.38 +  (TRUE, 'user7',  'User #7'),   -- id  7
    2.39 +  (TRUE, 'user8',  'User #8'),   -- id  8
    2.40 +  (TRUE, 'user9',  'User #9'),   -- id  9
    2.41 +  (TRUE, 'user10', 'User #10'),  -- id 10
    2.42 +  (TRUE, 'user11', 'User #11'),  -- id 11
    2.43 +  (TRUE, 'user12', 'User #12'),  -- id 12
    2.44 +  (TRUE, 'user13', 'User #13'),  -- id 13
    2.45 +  (TRUE, 'user14', 'User #14'),  -- id 14
    2.46 +  (TRUE, 'user15', 'User #15'),  -- id 15
    2.47 +  (TRUE, 'user16', 'User #16'),  -- id 16
    2.48 +  (TRUE, 'user17', 'User #17'),  -- id 17
    2.49 +  (TRUE, 'user18', 'User #18'),  -- id 18
    2.50 +  (TRUE, 'user19', 'User #19'),  -- id 19
    2.51 +  (TRUE, 'user20', 'User #20'),  -- id 20
    2.52 +  (TRUE, 'user21', 'User #21'),  -- id 21
    2.53 +  (TRUE, 'user22', 'User #22'),  -- id 22
    2.54 +  (TRUE, 'user23', 'User #23');  -- id 23
    2.55  
    2.56  -- set password to "login"
    2.57  UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0';

Impressum / About Us