liquid_feedback_core

changeset 97:58451b5565ae

Introduced (organizational) units

- New table "unit"
- Each "area" refers to one unit
- "invite_code"s are valid for one or many units
- New table "privilege" stores "voting_right" for units
- Delegation scope 'global' is replaced by delegation scope 'unit'
- View "global_delegation" is replaced by "unit_delegation"
- Removed helper view "active_delegation"
- Checking that members have "voting_right" in several views and functions
- New view "unit_member_count" to update "member_count" column of new unit table
- New argument list for function "delegation_chain"(...) containing "unit_id"
- Renamed column "member_active" to "member_valid" in "delegation_chain_row"
- Modified demo.sql and init.sql to support units
author jbe
date Tue Dec 07 00:04:44 2010 +0100 (2010-12-07)
parents 07e6a4f11b5b
children 741b7a5a2783
files core.sql demo.sql init.sql
line diff
     1.1 --- a/core.sql	Mon Dec 06 23:50:32 2010 +0100
     1.2 +++ b/core.sql	Tue Dec 07 00:04:44 2010 +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.0', 1, 3, 0))
     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 @@ -132,7 +132,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 @@ -280,8 +281,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 @@ -289,6 +316,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 @@ -571,6 +599,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 @@ -658,9 +713,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 @@ -668,24 +723,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 @@ -1252,19 +1308,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 @@ -1274,15 +1335,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 @@ -1292,16 +1360,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 @@ -1371,6 +1448,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 @@ -1392,12 +1485,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 @@ -1691,7 +1788,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 @@ -1712,13 +1809,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 @@ -1728,13 +1828,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 @@ -1751,14 +1879,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 @@ -1766,9 +1897,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 @@ -1776,16 +1910,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 @@ -1794,11 +1927,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 @@ -1836,7 +1965,7 @@
   1.427            "output_row"."loop" := 'first';
   1.428            "loop_v" := TRUE;
   1.429          END IF;
   1.430 -        IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
   1.431 +        IF "scope_v" = 'unit' THEN
   1.432            "output_row"."participation" := NULL;
   1.433          END IF;
   1.434          RETURN NEXT "output_row";
   1.435 @@ -1848,13 +1977,16 @@
   1.436  
   1.437  COMMENT ON FUNCTION "delegation_chain"
   1.438    ( "member"."id"%TYPE,
   1.439 +    "unit"."id"%TYPE,
   1.440      "area"."id"%TYPE,
   1.441      "issue"."id"%TYPE,
   1.442      "member"."id"%TYPE )
   1.443    IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
   1.444  
   1.445 +
   1.446  CREATE FUNCTION "delegation_chain"
   1.447    ( "member_id_p" "member"."id"%TYPE,
   1.448 +    "unit_id_p"   "unit"."id"%TYPE,
   1.449      "area_id_p"   "area"."id"%TYPE,
   1.450      "issue_id_p"  "issue"."id"%TYPE )
   1.451    RETURNS SETOF "delegation_chain_row"
   1.452 @@ -1875,6 +2007,7 @@
   1.453  
   1.454  COMMENT ON FUNCTION "delegation_chain"
   1.455    ( "member"."id"%TYPE,
   1.456 +    "unit"."id"%TYPE,
   1.457      "area"."id"%TYPE,
   1.458      "issue"."id"%TYPE )
   1.459    IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
   1.460 @@ -2011,6 +2144,7 @@
   1.461    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.462      BEGIN
   1.463        LOCK TABLE "member"     IN SHARE MODE;
   1.464 +      LOCK TABLE "privilege"  IN SHARE MODE;
   1.465        LOCK TABLE "membership" IN SHARE MODE;
   1.466        LOCK TABLE "policy"     IN SHARE MODE;
   1.467        PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
   1.468 @@ -2046,11 +2180,16 @@
   1.469      BEGIN
   1.470        LOCK TABLE "member"       IN SHARE MODE;
   1.471        LOCK TABLE "member_count" IN EXCLUSIVE MODE;
   1.472 +      LOCK TABLE "unit"         IN EXCLUSIVE MODE;
   1.473        LOCK TABLE "area"         IN EXCLUSIVE MODE;
   1.474 +      LOCK TABLE "privilege"    IN SHARE MODE;
   1.475        LOCK TABLE "membership"   IN SHARE MODE;
   1.476        DELETE FROM "member_count";
   1.477        INSERT INTO "member_count" ("total_count")
   1.478          SELECT "total_count" FROM "member_count_view";
   1.479 +      UPDATE "unit" SET "member_count" = "view"."member_count"
   1.480 +        FROM "unit_member_count" AS "view"
   1.481 +        WHERE "view"."unit_id" = "unit"."id";
   1.482        UPDATE "area" SET
   1.483          "direct_member_count" = "view"."direct_member_count",
   1.484          "member_weight"       = "view"."member_weight",
   1.485 @@ -2161,17 +2300,25 @@
   1.486          JOIN "area" ON "issue"."area_id" = "area"."id"
   1.487          JOIN "membership" ON "area"."id" = "membership"."area_id"
   1.488          JOIN "member" ON "membership"."member_id" = "member"."id"
   1.489 +        JOIN "privilege"
   1.490 +          ON "privilege"."unit_id" = "area"."unit_id"
   1.491 +          AND "privilege"."member_id" = "member"."id"
   1.492          WHERE "issue"."id" = "issue_id_p"
   1.493 -        AND "member"."active"
   1.494 +        AND "member"."active" AND "privilege"."voting_right"
   1.495          UNION
   1.496          SELECT
   1.497            "issue_id_p"                 AS "issue_id",
   1.498            'periodic'::"snapshot_event" AS "event",
   1.499            "member"."id"                AS "member_id"
   1.500 -        FROM "interest" JOIN "member"
   1.501 -        ON "interest"."member_id" = "member"."id"
   1.502 -        WHERE "interest"."issue_id" = "issue_id_p"
   1.503 -        AND "member"."active";
   1.504 +        FROM "issue"
   1.505 +        JOIN "area" ON "issue"."area_id" = "area"."id"
   1.506 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.507 +        JOIN "member" ON "interest"."member_id" = "member"."id"
   1.508 +        JOIN "privilege"
   1.509 +          ON "privilege"."unit_id" = "area"."unit_id"
   1.510 +          AND "privilege"."member_id" = "member"."id"
   1.511 +        WHERE "issue"."id" = "issue_id_p"
   1.512 +        AND "member"."active" AND "privilege"."voting_right";
   1.513        FOR "member_id_v" IN
   1.514          SELECT "member_id" FROM "direct_population_snapshot"
   1.515          WHERE "issue_id" = "issue_id_p"
   1.516 @@ -2289,10 +2436,15 @@
   1.517            'periodic'    AS "event",
   1.518            "member"."id" AS "member_id",
   1.519            "interest"."voting_requested"
   1.520 -        FROM "interest" JOIN "member"
   1.521 -        ON "interest"."member_id" = "member"."id"
   1.522 -        WHERE "interest"."issue_id" = "issue_id_p"
   1.523 -        AND "member"."active";
   1.524 +        FROM "issue"
   1.525 +        JOIN "area" ON "issue"."area_id" = "area"."id"
   1.526 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.527 +        JOIN "member" ON "interest"."member_id" = "member"."id"
   1.528 +        JOIN "privilege"
   1.529 +          ON "privilege"."unit_id" = "area"."unit_id"
   1.530 +          AND "privilege"."member_id" = "member"."id"
   1.531 +        WHERE "issue"."id" = "issue_id_p"
   1.532 +        AND "member"."active" AND "privilege"."voting_right";
   1.533        FOR "member_id_v" IN
   1.534          SELECT "member_id" FROM "direct_interest_snapshot"
   1.535          WHERE "issue_id" = "issue_id_p"
   1.536 @@ -2746,20 +2898,35 @@
   1.537    RETURNS VOID
   1.538    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.539      DECLARE
   1.540 -      "issue_row"   "issue"%ROWTYPE;
   1.541 +      "area_id_v"   "area"."id"%TYPE;
   1.542 +      "unit_id_v"   "unit"."id"%TYPE;
   1.543        "member_id_v" "member"."id"%TYPE;
   1.544      BEGIN
   1.545        PERFORM "lock_issue"("issue_id_p");
   1.546 -      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.547 +      SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   1.548 +      SELECT "id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   1.549        DELETE FROM "delegating_voter"
   1.550          WHERE "issue_id" = "issue_id_p";
   1.551        DELETE FROM "direct_voter"
   1.552          WHERE "issue_id" = "issue_id_p"
   1.553          AND "autoreject" = TRUE;
   1.554 -      DELETE FROM "direct_voter" USING "member"
   1.555 -        WHERE "direct_voter"."member_id" = "member"."id"
   1.556 -        AND "direct_voter"."issue_id" = "issue_id_p"
   1.557 -        AND "member"."active" = FALSE;
   1.558 +      DELETE FROM "direct_voter"
   1.559 +        USING (
   1.560 +          SELECT
   1.561 +            "direct_voter"."member_id"
   1.562 +          FROM "direct_voter"
   1.563 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   1.564 +          LEFT JOIN "privilege"
   1.565 +          ON "privilege"."unit_id" = "unit_id_v"
   1.566 +          AND "privilege"."member_id" = "direct_voter"."member_id"
   1.567 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   1.568 +            "member"."active" = FALSE OR
   1.569 +            "privilege"."voting_right" ISNULL OR
   1.570 +            "privilege"."voting_right" = FALSE
   1.571 +          )
   1.572 +        ) AS "subquery"
   1.573 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.574 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   1.575        UPDATE "direct_voter" SET "weight" = 1
   1.576          WHERE "issue_id" = "issue_id_p";
   1.577        PERFORM "add_vote_delegations"("issue_id_p");
   1.578 @@ -2792,7 +2959,7 @@
   1.579            LEFT JOIN "delegating_voter"
   1.580              ON "membership"."member_id" = "delegating_voter"."member_id"
   1.581              AND "delegating_voter"."issue_id" = "issue_id_p"
   1.582 -          WHERE "membership"."area_id" = "issue_row"."area_id"
   1.583 +          WHERE "membership"."area_id" = "area_id_v"
   1.584            AND "membership"."autoreject" = TRUE
   1.585            AND "member"."active"
   1.586            AND "interest"."autoreject" ISNULL
     2.1 --- a/demo.sql	Mon Dec 06 23:50:32 2010 +0100
     2.2 +++ b/demo.sql	Tue Dec 07 00:04:44 2010 +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	Mon Dec 06 23:50:32 2010 +0100
     3.2 +++ b/init.sql	Tue Dec 07 00:04:44 2010 +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 );

Impressum / About Us