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 );

Impressum / About Us