# HG changeset patch # User jbe # Date 1309987008 -7200 # Node ID e3b0ea7ab2adbbd565f05ce4cb9f4d26a80b4f16 # Parent 1024224df943a2bd1967d6bf1cae43d2d4214c3f Removed table "invite_code" (invite codes are now stored in "member" table) diff -r 1024224df943 -r e3b0ea7ab2ad core.sql --- a/core.sql Wed Jun 08 20:53:36 2011 +0200 +++ b/core.sql Wed Jul 06 23:16:48 2011 +0200 @@ -84,12 +84,14 @@ CREATE TABLE "member" ( "id" SERIAL4 PRIMARY KEY, "created" TIMESTAMPTZ NOT NULL DEFAULT now(), + "invite_code" TEXT UNIQUE, + "activated" TIMESTAMPTZ, "last_login" TIMESTAMPTZ, "last_login_public" DATE, "login" TEXT UNIQUE, "password" TEXT, "locked" BOOLEAN NOT NULL DEFAULT FALSE, - "active" BOOLEAN NOT NULL DEFAULT TRUE, + "active" BOOLEAN NOT NULL DEFAULT FALSE, "admin" BOOLEAN NOT NULL DEFAULT FALSE, "notify_email" TEXT, "notify_email_unconfirmed" TEXT, @@ -117,7 +119,9 @@ "external_posts" TEXT, "formatting_engine" TEXT, "statement" TEXT, - "text_search_data" TSVECTOR ); + "text_search_data" TSVECTOR, + CONSTRAINT "not_active_without_activated" + CHECK ("activated" NOTNULL OR "active" = FALSE) ); CREATE INDEX "member_active_idx" ON "member" ("active"); CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data"); CREATE TRIGGER "update_text_search_data" @@ -129,12 +133,15 @@ COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization'; +COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code'; +COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time'; +COMMENT ON COLUMN "member"."activated" IS 'Timestamp of first activation of account (set automatically by "set_activated_timestamp" trigger)'; COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login'; COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)'; COMMENT ON COLUMN "member"."login" IS 'Login name'; COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.'; -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.'; +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).'; COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas'; COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to'; COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification'; @@ -178,24 +185,6 @@ 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)'; -CREATE TABLE "invite_code" ( - "id" SERIAL8 PRIMARY KEY, - "code" TEXT NOT NULL UNIQUE, - "created" TIMESTAMPTZ NOT NULL DEFAULT now(), - "used" TIMESTAMPTZ, - "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE, - "comment" TEXT, - CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) ); - -COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.'; - -COMMENT ON COLUMN "invite_code"."code" IS 'Secret code'; -COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code'; -COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account'; -COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code'; -COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only'; - - CREATE TABLE "setting" ( PRIMARY KEY ("member_id", "key"), "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, @@ -758,14 +747,6 @@ COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings'; -CREATE TABLE "invite_code_unit" ( - PRIMARY KEY ("invite_code_id", "unit_id"), - "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE, - "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); - -COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights'; - - CREATE TABLE "privilege" ( PRIMARY KEY ("unit_id", "member_id"), "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, @@ -1149,10 +1130,36 @@ +--------------------------------------------------- +-- Triggers related to member account activation -- +--------------------------------------------------- + + +CREATE FUNCTION "set_member_activated_timestamp_trigger"() + RETURNS TRIGGER + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + IF NEW."activated" ISNULL AND NEW."active" THEN + NEW."activated" := now(); + END IF; + RETURN NEW; + END; + $$; + +CREATE TRIGGER "set_activated_timestamp" + BEFORE INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE + "set_member_activated_timestamp_trigger"(); + +COMMENT ON FUNCTION "set_member_activated_timestamp_trigger"() IS 'Implementation of trigger "set_activated_timestamp" on table "member"'; +COMMENT ON TRIGGER "set_activated_timestamp" ON "member" IS 'Set "activated" to now(), if it is NULL and "active" is set to TRUE'; + + + ---------------------------------------------- -- Writing of history entries and event log -- ---------------------------------------------- + CREATE FUNCTION "write_member_history_trigger"() RETURNS TRIGGER LANGUAGE 'plpgsql' VOLATILE AS $$ diff -r 1024224df943 -r e3b0ea7ab2ad demo.sql --- a/demo.sql Wed Jun 08 20:53:36 2011 +0200 +++ b/demo.sql Wed Jul 06 23:16:48 2011 +0200 @@ -3,30 +3,30 @@ BEGIN; -INSERT INTO "member" ("login", "name") VALUES - ('user1', 'User #1'), -- id 1 - ('user2', 'User #2'), -- id 2 - ('user3', 'User #3'), -- id 3 - ('user4', 'User #4'), -- id 4 - ('user5', 'User #5'), -- id 5 - ('user6', 'User #6'), -- id 6 - ('user7', 'User #7'), -- id 7 - ('user8', 'User #8'), -- id 8 - ('user9', 'User #9'), -- id 9 - ('user10', 'User #10'), -- id 10 - ('user11', 'User #11'), -- id 11 - ('user12', 'User #12'), -- id 12 - ('user13', 'User #13'), -- id 13 - ('user14', 'User #14'), -- id 14 - ('user15', 'User #15'), -- id 15 - ('user16', 'User #16'), -- id 16 - ('user17', 'User #17'), -- id 17 - ('user18', 'User #18'), -- id 18 - ('user19', 'User #19'), -- id 19 - ('user20', 'User #20'), -- id 20 - ('user21', 'User #21'), -- id 21 - ('user22', 'User #22'), -- id 22 - ('user23', 'User #23'); -- id 23 +INSERT INTO "member" ("active", "login", "name") VALUES + (TRUE, 'user1', 'User #1'), -- id 1 + (TRUE, 'user2', 'User #2'), -- id 2 + (TRUE, 'user3', 'User #3'), -- id 3 + (TRUE, 'user4', 'User #4'), -- id 4 + (TRUE, 'user5', 'User #5'), -- id 5 + (TRUE, 'user6', 'User #6'), -- id 6 + (TRUE, 'user7', 'User #7'), -- id 7 + (TRUE, 'user8', 'User #8'), -- id 8 + (TRUE, 'user9', 'User #9'), -- id 9 + (TRUE, 'user10', 'User #10'), -- id 10 + (TRUE, 'user11', 'User #11'), -- id 11 + (TRUE, 'user12', 'User #12'), -- id 12 + (TRUE, 'user13', 'User #13'), -- id 13 + (TRUE, 'user14', 'User #14'), -- id 14 + (TRUE, 'user15', 'User #15'), -- id 15 + (TRUE, 'user16', 'User #16'), -- id 16 + (TRUE, 'user17', 'User #17'), -- id 17 + (TRUE, 'user18', 'User #18'), -- id 18 + (TRUE, 'user19', 'User #19'), -- id 19 + (TRUE, 'user20', 'User #20'), -- id 20 + (TRUE, 'user21', 'User #21'), -- id 21 + (TRUE, 'user22', 'User #22'), -- id 22 + (TRUE, 'user23', 'User #23'); -- id 23 -- set password to "login" UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0';