liquid_feedback_core
changeset 100:994dd8ff5ad1
Merged addition of new field "locked" into branch containing (organizational) units
author | jbe |
---|---|
date | Tue Jan 04 02:18:20 2011 +0100 (2011-01-04) |
parents | 46260129d0ce 741b7a5a2783 |
children | 575559c319e9 |
files | core.sql |
line diff
1.1 --- a/core.sql Tue Jan 04 02:15:22 2011 +0100 1.2 +++ b/core.sql Tue Jan 04 02:18:20 2011 +0100 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('1.3.1', 1, 3, 1)) 1.8 + SELECT * FROM (VALUES ('1.4.0', 1, 4, 0)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -134,7 +134,8 @@ 1.13 1.14 1.15 CREATE TABLE "invite_code" ( 1.16 - "code" TEXT PRIMARY KEY, 1.17 + "id" SERIAL8 PRIMARY KEY, 1.18 + "code" TEXT NOT NULL UNIQUE, 1.19 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.20 "used" TIMESTAMPTZ, 1.21 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE, 1.22 @@ -282,8 +283,34 @@ 1.23 COMMENT ON COLUMN "policy"."majority_strict" IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.'; 1.24 1.25 1.26 +CREATE TABLE "unit" ( 1.27 + "id" SERIAL4 PRIMARY KEY, 1.28 + "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.29 + "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.30 + "name" TEXT NOT NULL, 1.31 + "description" TEXT NOT NULL DEFAULT '', 1.32 + "member_count" INT4, 1.33 + "text_search_data" TSVECTOR ); 1.34 +CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL; 1.35 +CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id"); 1.36 +CREATE INDEX "unit_active_idx" ON "unit" ("active"); 1.37 +CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data"); 1.38 +CREATE TRIGGER "update_text_search_data" 1.39 + BEFORE INSERT OR UPDATE ON "unit" 1.40 + FOR EACH ROW EXECUTE PROCEDURE 1.41 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.42 + "name", "description" ); 1.43 + 1.44 +COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.'; 1.45 + 1.46 +COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed'; 1.47 +COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area'; 1.48 +COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"'; 1.49 + 1.50 + 1.51 CREATE TABLE "area" ( 1.52 "id" SERIAL4 PRIMARY KEY, 1.53 + "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.54 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.55 "name" TEXT NOT NULL, 1.56 "description" TEXT NOT NULL DEFAULT '', 1.57 @@ -291,6 +318,7 @@ 1.58 "member_weight" INT4, 1.59 "autoreject_weight" INT4, 1.60 "text_search_data" TSVECTOR ); 1.61 +CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id"); 1.62 CREATE INDEX "area_active_idx" ON "area" ("active"); 1.63 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data"); 1.64 CREATE TRIGGER "update_text_search_data" 1.65 @@ -573,6 +601,33 @@ 1.66 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings'; 1.67 1.68 1.69 +CREATE TABLE "invite_code_unit" ( 1.70 + PRIMARY KEY ("invite_code_id", "unit_id"), 1.71 + "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.72 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.73 + 1.74 +COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights'; 1.75 + 1.76 + 1.77 +CREATE TABLE "privilege" ( 1.78 + PRIMARY KEY ("unit_id", "member_id"), 1.79 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.80 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.81 + "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE, 1.82 + "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE, 1.83 + "area_manager" BOOLEAN NOT NULL DEFAULT FALSE, 1.84 + "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE, 1.85 + "voting_right" BOOLEAN NOT NULL DEFAULT TRUE ); 1.86 + 1.87 +COMMENT ON TABLE "privilege" IS 'Members rights related to each unit'; 1.88 + 1.89 +COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users'; 1.90 +COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units'; 1.91 +COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters'; 1.92 +COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit'; 1.93 +COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote'; 1.94 + 1.95 + 1.96 CREATE TABLE "membership" ( 1.97 PRIMARY KEY ("area_id", "member_id"), 1.98 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.99 @@ -660,9 +715,9 @@ 1.100 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support'; 1.101 1.102 1.103 -CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue'); 1.104 - 1.105 -COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)'; 1.106 +CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue'); 1.107 + 1.108 +COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)'; 1.109 1.110 1.111 CREATE TABLE "delegation" ( 1.112 @@ -670,24 +725,25 @@ 1.113 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.114 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.115 "scope" "delegation_scope" NOT NULL, 1.116 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.117 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.118 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.119 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"), 1.120 - CONSTRAINT "no_global_delegation_to_null" 1.121 - CHECK ("trustee_id" NOTNULL OR "scope" != 'global'), 1.122 + CONSTRAINT "no_unit_delegation_to_null" 1.123 + CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'), 1.124 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK ( 1.125 - ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR 1.126 - ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR 1.127 - ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ), 1.128 + ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR 1.129 + ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR 1.130 + ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ), 1.131 + UNIQUE ("unit_id", "truster_id"), 1.132 UNIQUE ("area_id", "truster_id"), 1.133 UNIQUE ("issue_id", "truster_id") ); 1.134 -CREATE UNIQUE INDEX "delegation_global_truster_id_unique_idx" 1.135 - ON "delegation" ("truster_id") WHERE "scope" = 'global'; 1.136 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id"); 1.137 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id"); 1.138 1.139 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members'; 1.140 1.141 +COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL'; 1.142 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL'; 1.143 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL'; 1.144 1.145 @@ -1254,19 +1310,24 @@ 1.146 ------------------------------------------ 1.147 1.148 1.149 -CREATE VIEW "active_delegation" AS 1.150 - SELECT "delegation".* FROM "delegation" 1.151 - JOIN "member" ON "delegation"."truster_id" = "member"."id" 1.152 - WHERE "member"."active" = TRUE; 1.153 - 1.154 -COMMENT ON VIEW "active_delegation" IS 'Helper view for views "global_delegation", "area_delegation" and "issue_delegation": Contains delegations where the truster_id refers to an active member and includes those delegations where trustee_id is NULL'; 1.155 - 1.156 - 1.157 -CREATE VIEW "global_delegation" AS 1.158 - SELECT "id", "truster_id", "trustee_id" 1.159 - FROM "active_delegation" WHERE "scope" = 'global'; 1.160 - 1.161 -COMMENT ON VIEW "global_delegation" IS 'Global delegations from active members'; 1.162 +CREATE VIEW "unit_delegation" AS 1.163 + SELECT 1.164 + "unit"."id" AS "unit_id", 1.165 + "delegation"."id", 1.166 + "delegation"."truster_id", 1.167 + "delegation"."trustee_id", 1.168 + "delegation"."scope" 1.169 + FROM "unit" 1.170 + JOIN "delegation" 1.171 + ON "delegation"."unit_id" = "unit"."id" 1.172 + JOIN "member" 1.173 + ON "delegation"."truster_id" = "member"."id" 1.174 + JOIN "privilege" 1.175 + ON "delegation"."unit_id" = "privilege"."unit_id" 1.176 + AND "delegation"."truster_id" = "privilege"."member_id" 1.177 + WHERE "member"."active" AND "privilege"."voting_right"; 1.178 + 1.179 +COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right'; 1.180 1.181 1.182 CREATE VIEW "area_delegation" AS 1.183 @@ -1276,15 +1337,22 @@ 1.184 "delegation"."truster_id", 1.185 "delegation"."trustee_id", 1.186 "delegation"."scope" 1.187 - FROM "area" JOIN "active_delegation" AS "delegation" 1.188 - ON "delegation"."scope" = 'global' 1.189 - OR "delegation"."area_id" = "area"."id" 1.190 + FROM "area" 1.191 + JOIN "delegation" 1.192 + ON "delegation"."unit_id" = "area"."unit_id" 1.193 + OR "delegation"."area_id" = "area"."id" 1.194 + JOIN "member" 1.195 + ON "delegation"."truster_id" = "member"."id" 1.196 + JOIN "privilege" 1.197 + ON "area"."unit_id" = "privilege"."unit_id" 1.198 + AND "delegation"."truster_id" = "privilege"."member_id" 1.199 + WHERE "member"."active" AND "privilege"."voting_right" 1.200 ORDER BY 1.201 "area"."id", 1.202 "delegation"."truster_id", 1.203 "delegation"."scope" DESC; 1.204 1.205 -COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL'; 1.206 +COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right'; 1.207 1.208 1.209 CREATE VIEW "issue_delegation" AS 1.210 @@ -1294,16 +1362,25 @@ 1.211 "delegation"."truster_id", 1.212 "delegation"."trustee_id", 1.213 "delegation"."scope" 1.214 - FROM "issue" JOIN "active_delegation" AS "delegation" 1.215 - ON "delegation"."scope" = 'global' 1.216 - OR "delegation"."area_id" = "issue"."area_id" 1.217 - OR "delegation"."issue_id" = "issue"."id" 1.218 + FROM "issue" 1.219 + JOIN "area" 1.220 + ON "area"."id" = "issue"."area_id" 1.221 + JOIN "delegation" 1.222 + ON "delegation"."unit_id" = "area"."unit_id" 1.223 + OR "delegation"."area_id" = "area"."id" 1.224 + OR "delegation"."issue_id" = "issue"."id" 1.225 + JOIN "member" 1.226 + ON "delegation"."truster_id" = "member"."id" 1.227 + JOIN "privilege" 1.228 + ON "area"."unit_id" = "privilege"."unit_id" 1.229 + AND "delegation"."truster_id" = "privilege"."member_id" 1.230 + WHERE "member"."active" AND "privilege"."voting_right" 1.231 ORDER BY 1.232 "issue"."id", 1.233 "delegation"."truster_id", 1.234 "delegation"."scope" DESC; 1.235 1.236 -COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL'; 1.237 +COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right'; 1.238 1.239 1.240 CREATE FUNCTION "membership_weight_with_skipping" 1.241 @@ -1373,6 +1450,22 @@ 1.242 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table'; 1.243 1.244 1.245 +CREATE VIEW "unit_member_count" AS 1.246 + SELECT 1.247 + "unit"."id" AS "unit_id", 1.248 + sum("member"."id") AS "member_count" 1.249 + FROM "unit" 1.250 + LEFT JOIN "privilege" 1.251 + ON "privilege"."unit_id" = "unit"."id" 1.252 + AND "privilege"."voting_right" 1.253 + LEFT JOIN "member" 1.254 + ON "member"."id" = "privilege"."member_id" 1.255 + AND "member"."active" 1.256 + GROUP BY "unit"."id"; 1.257 + 1.258 +COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table'; 1.259 + 1.260 + 1.261 CREATE VIEW "area_member_count" AS 1.262 SELECT 1.263 "area"."id" AS "area_id", 1.264 @@ -1394,12 +1487,16 @@ 1.265 FROM "area" 1.266 LEFT JOIN "membership" 1.267 ON "area"."id" = "membership"."area_id" 1.268 + LEFT JOIN "privilege" 1.269 + ON "privilege"."unit_id" = "area"."unit_id" 1.270 + AND "privilege"."member_id" = "membership"."member_id" 1.271 + AND "privilege"."voting_right" 1.272 LEFT JOIN "member" 1.273 - ON "membership"."member_id" = "member"."id" 1.274 + ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! 1.275 AND "member"."active" 1.276 GROUP BY "area"."id"; 1.277 1.278 -COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"'; 1.279 +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"'; 1.280 1.281 1.282 CREATE VIEW "opening_draft" AS 1.283 @@ -1693,7 +1790,7 @@ 1.284 CREATE TYPE "delegation_chain_row" AS ( 1.285 "index" INT4, 1.286 "member_id" INT4, 1.287 - "member_active" BOOLEAN, 1.288 + "member_valid" BOOLEAN, 1.289 "participation" BOOLEAN, 1.290 "overridden" BOOLEAN, 1.291 "scope_in" "delegation_scope", 1.292 @@ -1714,13 +1811,16 @@ 1.293 1.294 CREATE FUNCTION "delegation_chain" 1.295 ( "member_id_p" "member"."id"%TYPE, 1.296 + "unit_id_p" "unit"."id"%TYPE, 1.297 "area_id_p" "area"."id"%TYPE, 1.298 "issue_id_p" "issue"."id"%TYPE, 1.299 "simulate_trustee_id_p" "member"."id"%TYPE ) 1.300 RETURNS SETOF "delegation_chain_row" 1.301 LANGUAGE 'plpgsql' STABLE AS $$ 1.302 DECLARE 1.303 - "issue_row" "issue"%ROWTYPE; 1.304 + "scope_v" "delegation_scope"; 1.305 + "unit_id_v" "unit"."id"%TYPE; 1.306 + "area_id_v" "area"."id"%TYPE; 1.307 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 1.308 "loop_member_id_v" "member"."id"%TYPE; 1.309 "output_row" "delegation_chain_row"; 1.310 @@ -1730,13 +1830,41 @@ 1.311 "i" INT4; 1.312 "loop_v" BOOLEAN; 1.313 BEGIN 1.314 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.315 + IF 1.316 + "unit_id_p" NOTNULL AND 1.317 + "area_id_p" ISNULL AND 1.318 + "issue_id_p" ISNULL 1.319 + THEN 1.320 + "scope_v" := 'unit'; 1.321 + "unit_id_v" := "unit_id_p"; 1.322 + ELSIF 1.323 + "unit_id_p" ISNULL AND 1.324 + "area_id_p" NOTNULL AND 1.325 + "issue_id_p" ISNULL 1.326 + THEN 1.327 + "scope_v" := 'area'; 1.328 + "area_id_v" := "area_id_p"; 1.329 + SELECT "unit_id" INTO "unit_id_v" 1.330 + FROM "area" WHERE "id" = "area_id_v"; 1.331 + ELSIF 1.332 + "unit_id_p" ISNULL AND 1.333 + "area_id_p" ISNULL AND 1.334 + "issue_id_p" NOTNULL 1.335 + THEN 1.336 + "scope_v" := 'issue'; 1.337 + SELECT "area_id" INTO "area_id_v" 1.338 + FROM "issue" WHERE "id" = "issue_id_p"; 1.339 + SELECT "unit_id" INTO "unit_id_v" 1.340 + FROM "area" WHERE "id" = "area_id_v"; 1.341 + ELSE 1.342 + RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; 1.343 + END IF; 1.344 "visited_member_ids" := '{}'; 1.345 "loop_member_id_v" := NULL; 1.346 "output_rows" := '{}'; 1.347 "output_row"."index" := 0; 1.348 "output_row"."member_id" := "member_id_p"; 1.349 - "output_row"."member_active" := TRUE; 1.350 + "output_row"."member_valid" := TRUE; 1.351 "output_row"."participation" := FALSE; 1.352 "output_row"."overridden" := FALSE; 1.353 "output_row"."disabled_out" := FALSE; 1.354 @@ -1753,14 +1881,17 @@ 1.355 END IF; 1.356 "output_row"."scope_in" := "output_row"."scope_out"; 1.357 IF EXISTS ( 1.358 - SELECT NULL FROM "member" 1.359 - WHERE "id" = "output_row"."member_id" AND "active" 1.360 + SELECT NULL FROM "member" JOIN "privilege" 1.361 + ON "privilege"."member_id" = "member"."id" 1.362 + AND "privilege"."unit_id" = "unit_id_v" 1.363 + WHERE "id" = "output_row"."member_id" 1.364 + AND "member"."active" AND "privilege"."voting_right" 1.365 ) THEN 1.366 - IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 1.367 + IF "scope_v" = 'unit' THEN 1.368 SELECT * INTO "delegation_row" FROM "delegation" 1.369 WHERE "truster_id" = "output_row"."member_id" 1.370 - AND "scope" = 'global'; 1.371 - ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 1.372 + AND "unit_id" = "unit_id_v"; 1.373 + ELSIF "scope_v" = 'area' THEN 1.374 "output_row"."participation" := EXISTS ( 1.375 SELECT NULL FROM "membership" 1.376 WHERE "area_id" = "area_id_p" 1.377 @@ -1768,9 +1899,12 @@ 1.378 ); 1.379 SELECT * INTO "delegation_row" FROM "delegation" 1.380 WHERE "truster_id" = "output_row"."member_id" 1.381 - AND ("scope" = 'global' OR "area_id" = "area_id_p") 1.382 + AND ( 1.383 + "unit_id" = "unit_id_v" OR 1.384 + "area_id" = "area_id_v" 1.385 + ) 1.386 ORDER BY "scope" DESC; 1.387 - ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 1.388 + ELSIF "scope_v" = 'issue' THEN 1.389 "output_row"."participation" := EXISTS ( 1.390 SELECT NULL FROM "interest" 1.391 WHERE "issue_id" = "issue_id_p" 1.392 @@ -1778,16 +1912,15 @@ 1.393 ); 1.394 SELECT * INTO "delegation_row" FROM "delegation" 1.395 WHERE "truster_id" = "output_row"."member_id" 1.396 - AND ("scope" = 'global' OR 1.397 - "area_id" = "issue_row"."area_id" OR 1.398 + AND ( 1.399 + "unit_id" = "unit_id_v" OR 1.400 + "area_id" = "area_id_v" OR 1.401 "issue_id" = "issue_id_p" 1.402 ) 1.403 ORDER BY "scope" DESC; 1.404 - ELSE 1.405 - RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.'; 1.406 END IF; 1.407 ELSE 1.408 - "output_row"."member_active" := FALSE; 1.409 + "output_row"."member_valid" := FALSE; 1.410 "output_row"."participation" := FALSE; 1.411 "output_row"."scope_out" := NULL; 1.412 "delegation_row" := ROW(NULL); 1.413 @@ -1796,11 +1929,7 @@ 1.414 "output_row"."member_id" = "member_id_p" AND 1.415 "simulate_trustee_id_p" NOTNULL 1.416 THEN 1.417 - "output_row"."scope_out" := CASE 1.418 - WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global' 1.419 - WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area' 1.420 - WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue' 1.421 - END; 1.422 + "output_row"."scope_out" := "scope_v"; 1.423 "output_rows" := "output_rows" || "output_row"; 1.424 "output_row"."member_id" := "simulate_trustee_id_p"; 1.425 ELSIF "delegation_row"."trustee_id" NOTNULL THEN 1.426 @@ -1825,7 +1954,7 @@ 1.427 "loop_v" := FALSE; 1.428 LOOP 1.429 "output_row" := "output_rows"["i"]; 1.430 - EXIT WHEN "output_row" ISNULL; 1.431 + EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! 1.432 IF "loop_v" THEN 1.433 IF "i" + 1 = "row_count" THEN 1.434 "output_row"."loop" := 'last'; 1.435 @@ -1838,7 +1967,7 @@ 1.436 "output_row"."loop" := 'first'; 1.437 "loop_v" := TRUE; 1.438 END IF; 1.439 - IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 1.440 + IF "scope_v" = 'unit' THEN 1.441 "output_row"."participation" := NULL; 1.442 END IF; 1.443 RETURN NEXT "output_row"; 1.444 @@ -1850,13 +1979,16 @@ 1.445 1.446 COMMENT ON FUNCTION "delegation_chain" 1.447 ( "member"."id"%TYPE, 1.448 + "unit"."id"%TYPE, 1.449 "area"."id"%TYPE, 1.450 "issue"."id"%TYPE, 1.451 "member"."id"%TYPE ) 1.452 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; 1.453 1.454 + 1.455 CREATE FUNCTION "delegation_chain" 1.456 ( "member_id_p" "member"."id"%TYPE, 1.457 + "unit_id_p" "unit"."id"%TYPE, 1.458 "area_id_p" "area"."id"%TYPE, 1.459 "issue_id_p" "issue"."id"%TYPE ) 1.460 RETURNS SETOF "delegation_chain_row" 1.461 @@ -1877,6 +2009,7 @@ 1.462 1.463 COMMENT ON FUNCTION "delegation_chain" 1.464 ( "member"."id"%TYPE, 1.465 + "unit"."id"%TYPE, 1.466 "area"."id"%TYPE, 1.467 "issue"."id"%TYPE ) 1.468 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; 1.469 @@ -2013,6 +2146,7 @@ 1.470 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.471 BEGIN 1.472 LOCK TABLE "member" IN SHARE MODE; 1.473 + LOCK TABLE "privilege" IN SHARE MODE; 1.474 LOCK TABLE "membership" IN SHARE MODE; 1.475 LOCK TABLE "policy" IN SHARE MODE; 1.476 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 1.477 @@ -2048,11 +2182,16 @@ 1.478 BEGIN 1.479 LOCK TABLE "member" IN SHARE MODE; 1.480 LOCK TABLE "member_count" IN EXCLUSIVE MODE; 1.481 + LOCK TABLE "unit" IN EXCLUSIVE MODE; 1.482 LOCK TABLE "area" IN EXCLUSIVE MODE; 1.483 + LOCK TABLE "privilege" IN SHARE MODE; 1.484 LOCK TABLE "membership" IN SHARE MODE; 1.485 DELETE FROM "member_count"; 1.486 INSERT INTO "member_count" ("total_count") 1.487 SELECT "total_count" FROM "member_count_view"; 1.488 + UPDATE "unit" SET "member_count" = "view"."member_count" 1.489 + FROM "unit_member_count" AS "view" 1.490 + WHERE "view"."unit_id" = "unit"."id"; 1.491 UPDATE "area" SET 1.492 "direct_member_count" = "view"."direct_member_count", 1.493 "member_weight" = "view"."member_weight", 1.494 @@ -2163,17 +2302,25 @@ 1.495 JOIN "area" ON "issue"."area_id" = "area"."id" 1.496 JOIN "membership" ON "area"."id" = "membership"."area_id" 1.497 JOIN "member" ON "membership"."member_id" = "member"."id" 1.498 + JOIN "privilege" 1.499 + ON "privilege"."unit_id" = "area"."unit_id" 1.500 + AND "privilege"."member_id" = "member"."id" 1.501 WHERE "issue"."id" = "issue_id_p" 1.502 - AND "member"."active" 1.503 + AND "member"."active" AND "privilege"."voting_right" 1.504 UNION 1.505 SELECT 1.506 "issue_id_p" AS "issue_id", 1.507 'periodic'::"snapshot_event" AS "event", 1.508 "member"."id" AS "member_id" 1.509 - FROM "interest" JOIN "member" 1.510 - ON "interest"."member_id" = "member"."id" 1.511 - WHERE "interest"."issue_id" = "issue_id_p" 1.512 - AND "member"."active"; 1.513 + FROM "issue" 1.514 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.515 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.516 + JOIN "member" ON "interest"."member_id" = "member"."id" 1.517 + JOIN "privilege" 1.518 + ON "privilege"."unit_id" = "area"."unit_id" 1.519 + AND "privilege"."member_id" = "member"."id" 1.520 + WHERE "issue"."id" = "issue_id_p" 1.521 + AND "member"."active" AND "privilege"."voting_right"; 1.522 FOR "member_id_v" IN 1.523 SELECT "member_id" FROM "direct_population_snapshot" 1.524 WHERE "issue_id" = "issue_id_p" 1.525 @@ -2291,10 +2438,15 @@ 1.526 'periodic' AS "event", 1.527 "member"."id" AS "member_id", 1.528 "interest"."voting_requested" 1.529 - FROM "interest" JOIN "member" 1.530 - ON "interest"."member_id" = "member"."id" 1.531 - WHERE "interest"."issue_id" = "issue_id_p" 1.532 - AND "member"."active"; 1.533 + FROM "issue" 1.534 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.535 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.536 + JOIN "member" ON "interest"."member_id" = "member"."id" 1.537 + JOIN "privilege" 1.538 + ON "privilege"."unit_id" = "area"."unit_id" 1.539 + AND "privilege"."member_id" = "member"."id" 1.540 + WHERE "issue"."id" = "issue_id_p" 1.541 + AND "member"."active" AND "privilege"."voting_right"; 1.542 FOR "member_id_v" IN 1.543 SELECT "member_id" FROM "direct_interest_snapshot" 1.544 WHERE "issue_id" = "issue_id_p" 1.545 @@ -2748,20 +2900,35 @@ 1.546 RETURNS VOID 1.547 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.548 DECLARE 1.549 - "issue_row" "issue"%ROWTYPE; 1.550 + "area_id_v" "area"."id"%TYPE; 1.551 + "unit_id_v" "unit"."id"%TYPE; 1.552 "member_id_v" "member"."id"%TYPE; 1.553 BEGIN 1.554 PERFORM "lock_issue"("issue_id_p"); 1.555 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.556 + SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.557 + SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.558 DELETE FROM "delegating_voter" 1.559 WHERE "issue_id" = "issue_id_p"; 1.560 DELETE FROM "direct_voter" 1.561 WHERE "issue_id" = "issue_id_p" 1.562 AND "autoreject" = TRUE; 1.563 - DELETE FROM "direct_voter" USING "member" 1.564 - WHERE "direct_voter"."member_id" = "member"."id" 1.565 - AND "direct_voter"."issue_id" = "issue_id_p" 1.566 - AND "member"."active" = FALSE; 1.567 + DELETE FROM "direct_voter" 1.568 + USING ( 1.569 + SELECT 1.570 + "direct_voter"."member_id" 1.571 + FROM "direct_voter" 1.572 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 1.573 + LEFT JOIN "privilege" 1.574 + ON "privilege"."unit_id" = "unit_id_v" 1.575 + AND "privilege"."member_id" = "direct_voter"."member_id" 1.576 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 1.577 + "member"."active" = FALSE OR 1.578 + "privilege"."voting_right" ISNULL OR 1.579 + "privilege"."voting_right" = FALSE 1.580 + ) 1.581 + ) AS "subquery" 1.582 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.583 + AND "direct_voter"."member_id" = "subquery"."member_id"; 1.584 UPDATE "direct_voter" SET "weight" = 1 1.585 WHERE "issue_id" = "issue_id_p"; 1.586 PERFORM "add_vote_delegations"("issue_id_p"); 1.587 @@ -2794,7 +2961,7 @@ 1.588 LEFT JOIN "delegating_voter" 1.589 ON "membership"."member_id" = "delegating_voter"."member_id" 1.590 AND "delegating_voter"."issue_id" = "issue_id_p" 1.591 - WHERE "membership"."area_id" = "issue_row"."area_id" 1.592 + WHERE "membership"."area_id" = "area_id_v" 1.593 AND "membership"."autoreject" = TRUE 1.594 AND "member"."active" 1.595 AND "interest"."autoreject" ISNULL
2.1 --- a/demo.sql Tue Jan 04 02:15:22 2011 +0100 2.2 +++ b/demo.sql Tue Jan 04 02:18:20 2011 +0100 2.3 @@ -61,11 +61,17 @@ 2.4 END; 2.5 $$; 2.6 2.7 -INSERT INTO "area" ("name") VALUES 2.8 - ('Area #1'), -- id 1 2.9 - ('Area #2'), -- id 2 2.10 - ('Area #3'), -- id 3 2.11 - ('Area #4'); -- id 4 2.12 +INSERT INTO "unit" ("name") VALUES ('Main'); 2.13 + 2.14 +INSERT INTO "privilege" ("unit_id", "member_id", "voting_right") 2.15 + SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right" 2.16 + FROM "member"; 2.17 + 2.18 +INSERT INTO "area" ("unit_id", "name") VALUES 2.19 + (1, 'Area #1'), -- id 1 2.20 + (1, 'Area #2'), -- id 2 2.21 + (1, 'Area #3'), -- id 3 2.22 + (1, 'Area #4'); -- id 4 2.23 2.24 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy") 2.25 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE); 2.26 @@ -90,23 +96,23 @@ 2.27 2.28 -- global delegations 2.29 INSERT INTO "delegation" 2.30 - ("truster_id", "scope", "trustee_id") VALUES 2.31 - ( 1, 'global', 9), 2.32 - ( 2, 'global', 11), 2.33 - ( 3, 'global', 12), 2.34 - ( 4, 'global', 13), 2.35 - ( 5, 'global', 14), 2.36 - ( 6, 'global', 7), 2.37 - ( 7, 'global', 8), 2.38 - ( 8, 'global', 6), 2.39 - (10, 'global', 9), 2.40 - (11, 'global', 9), 2.41 - (12, 'global', 21), 2.42 - (15, 'global', 10), 2.43 - (16, 'global', 17), 2.44 - (17, 'global', 19), 2.45 - (18, 'global', 19), 2.46 - (23, 'global', 22); 2.47 + ("truster_id", "scope", "unit_id", "trustee_id") VALUES 2.48 + ( 1, 'unit', 1, 9), 2.49 + ( 2, 'unit', 1, 11), 2.50 + ( 3, 'unit', 1, 12), 2.51 + ( 4, 'unit', 1, 13), 2.52 + ( 5, 'unit', 1, 14), 2.53 + ( 6, 'unit', 1, 7), 2.54 + ( 7, 'unit', 1, 8), 2.55 + ( 8, 'unit', 1, 6), 2.56 + (10, 'unit', 1, 9), 2.57 + (11, 'unit', 1, 9), 2.58 + (12, 'unit', 1, 21), 2.59 + (15, 'unit', 1, 10), 2.60 + (16, 'unit', 1, 17), 2.61 + (17, 'unit', 1, 19), 2.62 + (18, 'unit', 1, 19), 2.63 + (23, 'unit', 1, 22); 2.64 2.65 -- delegations for topics 2.66 INSERT INTO "delegation"
3.1 --- a/init.sql Tue Jan 04 02:15:22 2011 +0100 3.2 +++ b/init.sql Tue Jan 04 02:18:20 2011 +0100 3.3 @@ -2,6 +2,17 @@ 3.4 3.5 BEGIN; 3.6 3.7 +INSERT INTO "unit" ( 3.8 + "active", 3.9 + "name", 3.10 + "description" 3.11 + ) VALUES ( 3.12 + true, 3.13 + 'Default unit', 3.14 + 'Default unit created by init script.' 3.15 + ); 3.16 + 3.17 + 3.18 INSERT INTO "member" ( 3.19 "login", 3.20 "password", 3.21 @@ -63,10 +74,12 @@ 3.22 1, 100 ); 3.23 3.24 INSERT INTO "area" ( 3.25 + "unit_id", 3.26 "active", 3.27 "name", 3.28 "description" 3.29 ) VALUES ( 3.30 + 1, 3.31 TRUE, 3.32 'Generic area', 3.33 DEFAULT );