liquid_feedback_core
changeset 9:4af4df1415f9 beta10
Version beta10
Voting will be skipped, if no initiative is admitted for voting
Invite code feature allows people having an invite code to create one account
Contingent system to limit the number of initiatives or text entries to be submitted by each member within a given time
Ability to store a formatting engine for each draft, which can be used to allow initiatives to choose between available wiki parsers
New table setting storing user settings for the frontend (replaced hidden_hints column of beta9)
Better policy support:
- New table allowed_policy to select which policies can be used in each area
- Policies are now ordered by an index field
Bugfixes:
- In function close_voting(...): If there were no voters, this resulted in a NULL value as positive and negative vote counts
- In delete_private_data() function: Secret fields of member table are now deleted too
- Major bug fixed in lf_export, which caused data loss on execution
Voting will be skipped, if no initiative is admitted for voting
Invite code feature allows people having an invite code to create one account
Contingent system to limit the number of initiatives or text entries to be submitted by each member within a given time
Ability to store a formatting engine for each draft, which can be used to allow initiatives to choose between available wiki parsers
New table setting storing user settings for the frontend (replaced hidden_hints column of beta9)
Better policy support:
- New table allowed_policy to select which policies can be used in each area
- Policies are now ordered by an index field
Bugfixes:
- In function close_voting(...): If there were no voters, this resulted in a NULL value as positive and negative vote counts
- In delete_private_data() function: Secret fields of member table are now deleted too
- Major bug fixed in lf_export, which caused data loss on execution
author | jbe |
---|---|
date | Thu Dec 10 12:00:00 2009 +0100 (2009-12-10) |
parents | e6faf5ff83af |
children | effdd7a04ea7 |
files | core.sql demo.sql init.sql lf_export |
line diff
1.1 --- a/core.sql Mon Nov 30 12:00:00 2009 +0100 1.2 +++ b/core.sql Thu Dec 10 12:00:00 2009 +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 ('beta9', NULL, NULL, NULL)) 1.8 + SELECT * FROM (VALUES ('beta10', NULL, NULL, NULL)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -59,7 +59,6 @@ 1.13 "password" TEXT, 1.14 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.15 "admin" BOOLEAN NOT NULL DEFAULT FALSE, 1.16 - "hidden_hints" TEXT[], 1.17 "notify_email" TEXT, 1.18 "notify_email_confirmed" BOOLEAN, 1.19 "name" TEXT NOT NULL UNIQUE, 1.20 @@ -96,7 +95,6 @@ 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"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.'; 1.23 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas'; 1.24 -COMMENT ON COLUMN "member"."hidden_hints" IS 'This field may be used by a frontend to store identification strings for introductory hints, which the user wants to hide.'; 1.25 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to'; 1.26 COMMENT ON COLUMN "member"."notify_email_confirmed" IS 'TRUE, if "notify_email" has been confirmed'; 1.27 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member'; 1.28 @@ -110,12 +108,40 @@ 1.29 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system'; 1.30 1.31 1.32 +CREATE TABLE "invite_code" ( 1.33 + "code" TEXT PRIMARY KEY, 1.34 + "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.35 + "used" TIMESTAMPTZ, 1.36 + "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE, 1.37 + "comment" TEXT, 1.38 + CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) ); 1.39 + 1.40 +COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.'; 1.41 + 1.42 +COMMENT ON COLUMN "invite_code"."code" IS 'Secret code'; 1.43 +COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code'; 1.44 +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.45 +COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code'; 1.46 +COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only'; 1.47 + 1.48 + 1.49 +CREATE TABLE "setting" ( 1.50 + PRIMARY KEY ("member_id", "key"), 1.51 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.52 + "key" TEXT NOT NULL, 1.53 + "value" TEXT NOT NULL ); 1.54 +CREATE INDEX "setting_key_idx" ON "setting" ("key"); 1.55 + 1.56 +COMMENT ON TABLE "setting" IS 'Place to store frontend specific member settings'; 1.57 + 1.58 +COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix'; 1.59 + 1.60 + 1.61 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar'); 1.62 1.63 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member'; 1.64 1.65 1.66 - 1.67 CREATE TABLE "member_image" ( 1.68 PRIMARY KEY ("member_id", "image_type", "scaled"), 1.69 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.70 @@ -170,6 +196,7 @@ 1.71 1.72 CREATE TABLE "policy" ( 1.73 "id" SERIAL4 PRIMARY KEY, 1.74 + "index" INT4 NOT NULL, 1.75 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.76 "name" TEXT NOT NULL UNIQUE, 1.77 "description" TEXT NOT NULL DEFAULT '', 1.78 @@ -185,6 +212,7 @@ 1.79 1.80 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)'; 1.81 1.82 +COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings'; 1.83 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues'; 1.84 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"'; 1.85 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"'; 1.86 @@ -221,6 +249,18 @@ 1.87 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature'; 1.88 1.89 1.90 +CREATE TABLE "allowed_policy" ( 1.91 + PRIMARY KEY ("area_id", "policy_id"), 1.92 + "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.93 + "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.94 + "default_policy" BOOLEAN NOT NULL DEFAULT FALSE ); 1.95 +CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy"; 1.96 + 1.97 +COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area'; 1.98 + 1.99 +COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.'; 1.100 + 1.101 + 1.102 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting'); 1.103 1.104 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period'; 1.105 @@ -325,8 +365,10 @@ 1.106 "id" SERIAL8 PRIMARY KEY, 1.107 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.108 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.109 + "formatting_engine" TEXT, 1.110 "content" TEXT NOT NULL, 1.111 "text_search_data" TSVECTOR ); 1.112 +CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created"); 1.113 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data"); 1.114 CREATE TRIGGER "update_text_search_data" 1.115 BEFORE INSERT OR UPDATE ON "draft" 1.116 @@ -335,6 +377,9 @@ 1.117 1.118 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues'; 1.119 1.120 +COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used'; 1.121 +COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"'; 1.122 + 1.123 1.124 CREATE TABLE "suggestion" ( 1.125 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion" 1.126 @@ -353,6 +398,7 @@ 1.127 "plus1_fulfilled_count" INT4, 1.128 "plus2_unfulfilled_count" INT4, 1.129 "plus2_fulfilled_count" INT4 ); 1.130 +CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); 1.131 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data"); 1.132 CREATE TRIGGER "update_text_search_data" 1.133 BEFORE INSERT OR UPDATE ON "suggestion" 1.134 @@ -592,6 +638,17 @@ 1.135 COMMENT ON COLUMN "vote"."grade" IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.'; 1.136 1.137 1.138 +CREATE TABLE "contingent" ( 1.139 + "time_frame" INTERVAL PRIMARY KEY, 1.140 + "text_entry_limit" INT4, 1.141 + "initiative_limit" INT4 ); 1.142 + 1.143 +COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.'; 1.144 + 1.145 +COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame'; 1.146 +COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame'; 1.147 + 1.148 + 1.149 1.150 ---------------------------- 1.151 -- Additional constraints -- 1.152 @@ -1103,6 +1160,20 @@ 1.153 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"'; 1.154 1.155 1.156 +CREATE VIEW "opening_draft" AS 1.157 + SELECT "draft".* FROM ( 1.158 + SELECT 1.159 + "initiative"."id" AS "initiative_id", 1.160 + min("draft"."id") AS "draft_id" 1.161 + FROM "initiative" JOIN "draft" 1.162 + ON "initiative"."id" = "draft"."initiative_id" 1.163 + GROUP BY "initiative"."id" 1.164 + ) AS "subquery" 1.165 + JOIN "draft" ON "subquery"."draft_id" = "draft"."id"; 1.166 + 1.167 +COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives'; 1.168 + 1.169 + 1.170 CREATE VIEW "current_draft" AS 1.171 SELECT "draft".* FROM ( 1.172 SELECT 1.173 @@ -1193,6 +1264,46 @@ 1.174 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet'; 1.175 1.176 1.177 +CREATE VIEW "member_contingent" AS 1.178 + SELECT 1.179 + "member"."id" AS "member_id", 1.180 + "contingent"."time_frame", 1.181 + CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN 1.182 + ( 1.183 + SELECT count(1) FROM "draft" 1.184 + WHERE "draft"."author_id" = "member"."id" 1.185 + AND "draft"."created" > now() - "contingent"."time_frame" 1.186 + ) + ( 1.187 + SELECT count(1) FROM "suggestion" 1.188 + WHERE "suggestion"."author_id" = "member"."id" 1.189 + AND "suggestion"."created" > now() - "contingent"."time_frame" 1.190 + ) 1.191 + ELSE NULL END AS "text_entry_count", 1.192 + "contingent"."text_entry_limit", 1.193 + CASE WHEN "contingent"."initiative_limit" NOTNULL THEN ( 1.194 + SELECT count(1) FROM "opening_draft" 1.195 + WHERE "opening_draft"."author_id" = "member"."id" 1.196 + AND "opening_draft"."created" > now() - "contingent"."time_frame" 1.197 + ) ELSE NULL END AS "initiative_count", 1.198 + "contingent"."initiative_limit" 1.199 + FROM "member" CROSS JOIN "contingent"; 1.200 + 1.201 +COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.'; 1.202 + 1.203 +COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row'; 1.204 +COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row'; 1.205 + 1.206 + 1.207 +CREATE VIEW "member_contingent_left" AS 1.208 + SELECT 1.209 + "member_id", 1.210 + max("text_entry_limit" - "text_entry_count") AS "text_entries_left", 1.211 + max("initiative_limit" - "initiative_count") AS "initiatives_left" 1.212 + FROM "member_contingent" GROUP BY "member_id"; 1.213 + 1.214 +COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.'; 1.215 + 1.216 + 1.217 1.218 -------------------------------------------------- 1.219 -- Set returning function for delegation chains -- 1.220 @@ -2017,13 +2128,19 @@ 1.221 WHERE "id" = "initiative_row"."id"; 1.222 END IF; 1.223 END LOOP; 1.224 + IF NOT EXISTS ( 1.225 + SELECT NULL FROM "initiative" 1.226 + WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE 1.227 + ) THEN 1.228 + PERFORM "close_voting"("issue_id_p"); 1.229 + END IF; 1.230 RETURN; 1.231 END; 1.232 $$; 1.233 1.234 COMMENT ON FUNCTION "freeze_after_snapshot" 1.235 ( "issue"."id"%TYPE ) 1.236 - IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction'; 1.237 + IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.'; 1.238 1.239 1.240 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE) 1.241 @@ -2215,7 +2332,7 @@ 1.242 "negative_votes" = "subquery"."negative_votes" 1.243 FROM ( 1.244 SELECT 1.245 - "initiative_id", 1.246 + "initiative"."id" AS "initiative_id", 1.247 coalesce( 1.248 sum( 1.249 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 1.250 @@ -2228,11 +2345,14 @@ 1.251 ), 1.252 0 1.253 ) AS "negative_votes" 1.254 - FROM "vote" JOIN "direct_voter" 1.255 - ON "vote"."member_id" = "direct_voter"."member_id" 1.256 - AND "vote"."issue_id" = "direct_voter"."issue_id" 1.257 - WHERE "vote"."issue_id" = "issue_id_p" 1.258 - GROUP BY "initiative_id" 1.259 + FROM "initiative" 1.260 + LEFT JOIN "direct_voter" 1.261 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 1.262 + LEFT JOIN "vote" 1.263 + ON "vote"."initiative_id" = "initiative"."id" 1.264 + AND "vote"."member_id" = "direct_voter"."member_id" 1.265 + WHERE "initiative"."issue_id" = "issue_id_p" 1.266 + GROUP BY "initiative"."id" 1.267 ) AS "subquery" 1.268 WHERE "initiative"."admitted" 1.269 AND "initiative"."id" = "subquery"."initiative_id"; 1.270 @@ -2562,10 +2682,12 @@ 1.271 "issue_row"."fully_frozen" ISNULL AND 1.272 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time" 1.273 THEN 1.274 - "issue_row"."fully_frozen" = now(); -- NOTE: "issue_row" used later 1.275 PERFORM "freeze_after_snapshot"("issue_id_p"); 1.276 + -- "issue" might change, thus "issue_row" has to be updated below 1.277 END IF; 1.278 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.279 IF 1.280 + "issue_row"."closed" ISNULL AND 1.281 "issue_row"."fully_frozen" NOTNULL AND 1.282 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time" 1.283 THEN 1.284 @@ -2614,7 +2736,13 @@ 1.285 DECLARE 1.286 "issue_id_v" "issue"."id"%TYPE; 1.287 BEGIN 1.288 + UPDATE "member" SET 1.289 + "login" = 'login' || "id"::text, 1.290 + "password" = NULL, 1.291 + "notify_email" = NULL, 1.292 + "notify_email_confirmed" = NULL; 1.293 DELETE FROM "session"; 1.294 + DELETE FROM "invite_code"; 1.295 DELETE FROM "contact" WHERE NOT "public"; 1.296 DELETE FROM "direct_voter" USING "issue" 1.297 WHERE "direct_voter"."issue_id" = "issue"."id"
2.1 --- a/demo.sql Mon Nov 30 12:00:00 2009 +0100 2.2 +++ b/demo.sql Thu Dec 10 12:00:00 2009 +0100 2.3 @@ -31,6 +31,7 @@ 2.4 UPDATE "member" SET "password" = "login"; 2.5 2.6 INSERT INTO "policy" ( 2.7 + "index", 2.8 "name", 2.9 "admission_time", 2.10 "discussion_time", 2.11 @@ -39,6 +40,7 @@ 2.12 "issue_quorum_num", "issue_quorum_den", 2.13 "initiative_quorum_num", "initiative_quorum_den" 2.14 ) VALUES ( 2.15 + 1, 2.16 'Default policy', 2.17 '1 hour', '1 hour', '1 hour', '1 hour', 2.18 25, 100, 2.19 @@ -64,6 +66,9 @@ 2.20 ('Area #3'), -- id 3 2.21 ('Area #4'); -- id 4 2.22 2.23 +INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy") 2.24 + VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE); 2.25 + 2.26 INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES 2.27 (1, 9, FALSE), 2.28 (1, 19, FALSE),
3.1 --- a/init.sql Mon Nov 30 12:00:00 2009 +0100 3.2 +++ b/init.sql Thu Dec 10 12:00:00 2009 +0100 3.3 @@ -3,22 +3,20 @@ 3.4 BEGIN; 3.5 3.6 INSERT INTO "member" ( 3.7 - "id", 3.8 "login", 3.9 "password", 3.10 "active", 3.11 "admin", 3.12 "name" 3.13 ) VALUES ( 3.14 - DEFAULT, 3.15 'admin', 3.16 - '', 3.17 + '$1$.EMPTY.$LDufa24OE2HZFXAXh71Eb1', 3.18 TRUE, 3.19 TRUE, 3.20 'Administrator' ); 3.21 3.22 INSERT INTO "policy" ( 3.23 - "id", 3.24 + "index", 3.25 "active", 3.26 "name", 3.27 "description", 3.28 @@ -31,7 +29,7 @@ 3.29 "initiative_quorum_num", 3.30 "initiative_quorum_den" 3.31 ) VALUES ( 3.32 - DEFAULT, 3.33 + 1, 3.34 TRUE, 3.35 'Extensive proceeding', 3.36 DEFAULT, 3.37 @@ -42,7 +40,7 @@ 3.38 10, 100, 3.39 10, 100 3.40 ), ( 3.41 - DEFAULT, 3.42 + 2, 3.43 TRUE, 3.44 'Standard proceeding', 3.45 DEFAULT, 3.46 @@ -53,7 +51,7 @@ 3.47 10, 100, 3.48 10, 100 3.49 ), ( 3.50 - DEFAULT, 3.51 + 3, 3.52 TRUE, 3.53 'Fast proceeding', 3.54 DEFAULT, 3.55 @@ -65,12 +63,10 @@ 3.56 1, 100 ); 3.57 3.58 INSERT INTO "area" ( 3.59 - "id", 3.60 "active", 3.61 "name", 3.62 "description" 3.63 ) VALUES ( 3.64 - DEFAULT, 3.65 TRUE, 3.66 'Generic area', 3.67 DEFAULT );
4.1 --- a/lf_export Mon Nov 30 12:00:00 2009 +0100 4.2 +++ b/lf_export Thu Dec 10 12:00:00 2009 +0100 4.3 @@ -14,7 +14,7 @@ 4.4 if createdb -T "$1" "$EXPORT_DBNAME" 4.5 then 4.6 echo "Deleting private data in copied database..." 4.7 - if psql liquid_feedback -c 'SELECT delete_private_data()' > /dev/null 4.8 + if psql -v ON_ERROR_STOP=1 -c 'SELECT delete_private_data()' "$EXPORT_DBNAME" > /dev/null 4.9 then 4.10 echo "Dumping and compressing copied database to \"$2\"..." 4.11 if pg_dump --no-owner --no-privileges "$EXPORT_DBNAME" | gzip -9 > "$2"