1.1 --- a/core.sql	Sat Sep 16 21:20:38 2017 +0200
     1.2 +++ b/core.sql	Sun Sep 17 01:48:45 2017 +0200
     1.3 @@ -597,10 +597,21 @@
     1.4  COMMENT ON COLUMN "unit"."parent_id"          IS 'Parent id of tree node; Multiple roots allowed';
     1.5  COMMENT ON COLUMN "unit"."active"             IS 'TRUE means new issues can be created in areas of this unit';
     1.6  COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
     1.7 -COMMENT ON COLUMN "unit"."member_count"       IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
     1.8 +COMMENT ON COLUMN "unit"."member_count"       IS 'Count of members as determined by column "voting_right" in view "verified_privilege" (only active members counted)';
     1.9  COMMENT ON COLUMN "unit"."location"           IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
    1.10  
    1.11  
    1.12 +CREATE TABLE "cross_unit_verification" (
    1.13 +        PRIMARY KEY ("unit_id", "trusted_unit_id"),
    1.14 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.15 +        "trusted_unit_id"       INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.16 +
    1.17 +COMMENT ON TABLE "cross_unit_verification" IS 'Enables member verifications in a unit to count for other units as well (non-transitively)';
    1.18 +
    1.19 +COMMENT ON COLUMN "cross_unit_verification"."unit_id"         IS 'Unit for which verification in "trusted_unit_id" is also taken into account';
    1.20 +COMMENT ON COLUMN "cross_unit_verification"."trusted_unit_id" IS 'Unit where verification exists';
    1.21 +
    1.22 +
    1.23  CREATE TABLE "subscription" (
    1.24          PRIMARY KEY ("member_id", "unit_id"),
    1.25          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.26 @@ -1059,6 +1070,18 @@
    1.27  COMMENT ON COLUMN "temporary_suggestion_counts"."id"  IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
    1.28  
    1.29  
    1.30 +CREATE TABLE "verification" (
    1.31 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.32 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.33 +        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.34 +        --"expiry"                TIMESTAMPTZ,  -- TODO
    1.35 +        "comment"               TEXT );
    1.36 +CREATE INDEX "verification_unit_id_idx" ON "verification" ("unit_id");
    1.37 +CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id");
    1.38 +
    1.39 +COMMENT ON TABLE "verification" IS 'Member verification status';
    1.40 +
    1.41 +
    1.42  CREATE TABLE "privilege" (
    1.43          PRIMARY KEY ("unit_id", "member_id"),
    1.44          "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.45 @@ -3313,6 +3336,20 @@
    1.46  COMMENT ON VIEW "issue_for_admission" IS 'Contains up to 1 issue per area eligible to pass from ''admission'' to ''discussion'' state; needs to be recalculated after admitting the issue in this view';
    1.47  
    1.48  
    1.49 +CREATE VIEW "verified_privilege" AS
    1.50 +  SELECT DISTINCT "privilege".* FROM "privilege"
    1.51 +  LEFT JOIN "cross_unit_verification" AS "cross"
    1.52 +    ON "privilege"."unit_id" = "cross"."unit_id"
    1.53 +  LEFT JOIN "verification"
    1.54 +    ON (
    1.55 +      "privilege"."unit_id" = "verification"."unit_id" OR
    1.56 +      "cross"."trusted_unit_id" = "verification"."unit_id"
    1.57 +    )
    1.58 +    AND "privilege"."member_id" = "verification"."member_id";
    1.59 +
    1.60 +COMMENT ON VIEW "verified_privilege" IS 'View on privilege table containing only entries where verification of member in unit has been done';
    1.61 +
    1.62 +
    1.63  CREATE VIEW "unit_delegation" AS
    1.64    SELECT
    1.65      "unit"."id" AS "unit_id",
    1.66 @@ -3325,10 +3362,10 @@
    1.67      ON "delegation"."unit_id" = "unit"."id"
    1.68    JOIN "member"
    1.69      ON "delegation"."truster_id" = "member"."id"
    1.70 -  JOIN "privilege"
    1.71 -    ON "delegation"."unit_id" = "privilege"."unit_id"
    1.72 -    AND "delegation"."truster_id" = "privilege"."member_id"
    1.73 -  WHERE "member"."active" AND "privilege"."voting_right";
    1.74 +  JOIN "verified_privilege"
    1.75 +    ON "delegation"."unit_id" = "verified_privilege"."unit_id"
    1.76 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
    1.77 +  WHERE "member"."active" AND "verified_privilege"."voting_right";
    1.78  
    1.79  COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
    1.80  
    1.81 @@ -3346,10 +3383,10 @@
    1.82      OR "delegation"."area_id" = "area"."id"
    1.83    JOIN "member"
    1.84      ON "delegation"."truster_id" = "member"."id"
    1.85 -  JOIN "privilege"
    1.86 -    ON "area"."unit_id" = "privilege"."unit_id"
    1.87 -    AND "delegation"."truster_id" = "privilege"."member_id"
    1.88 -  WHERE "member"."active" AND "privilege"."voting_right"
    1.89 +  JOIN "verified_privilege"
    1.90 +    ON "area"."unit_id" = "verified_privilege"."unit_id"
    1.91 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
    1.92 +  WHERE "member"."active" AND "verified_privilege"."voting_right"
    1.93    ORDER BY
    1.94      "area"."id",
    1.95      "delegation"."truster_id",
    1.96 @@ -3374,10 +3411,10 @@
    1.97      OR "delegation"."issue_id" = "issue"."id"
    1.98    JOIN "member"
    1.99      ON "delegation"."truster_id" = "member"."id"
   1.100 -  JOIN "privilege"
   1.101 -    ON "area"."unit_id" = "privilege"."unit_id"
   1.102 -    AND "delegation"."truster_id" = "privilege"."member_id"
   1.103 -  WHERE "member"."active" AND "privilege"."voting_right"
   1.104 +  JOIN "verified_privilege"
   1.105 +    ON "area"."unit_id" = "verified_privilege"."unit_id"
   1.106 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
   1.107 +  WHERE "member"."active" AND "verified_privilege"."voting_right"
   1.108    ORDER BY
   1.109      "issue"."id",
   1.110      "delegation"."truster_id",
   1.111 @@ -3396,10 +3433,10 @@
   1.112    SELECT
   1.113      "unit"."id"   AS "unit_id",
   1.114      "member"."id" AS "member_id"
   1.115 -  FROM "privilege"
   1.116 -  JOIN "unit"   ON "unit_id"     = "privilege"."unit_id"
   1.117 -  JOIN "member" ON "member"."id" = "privilege"."member_id"
   1.118 -  WHERE "privilege"."voting_right" AND "member"."active";
   1.119 +  FROM "verified_privilege"
   1.120 +  JOIN "unit"   ON "unit_id"     = "verified_privilege"."unit_id"
   1.121 +  JOIN "member" ON "member"."id" = "verified_privilege"."member_id"
   1.122 +  WHERE "verified_privilege"."voting_right" AND "member"."active";
   1.123  
   1.124  COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
   1.125  
   1.126 @@ -3660,10 +3697,10 @@
   1.127    FROM "member" CROSS JOIN "event"
   1.128    JOIN "issue" ON "issue"."id" = "event"."issue_id"
   1.129    JOIN "area" ON "area"."id" = "issue"."area_id"
   1.130 -  LEFT JOIN "privilege" ON
   1.131 -    "privilege"."member_id" = "member"."id" AND
   1.132 -    "privilege"."unit_id" = "area"."unit_id" AND
   1.133 -    "privilege"."voting_right" = TRUE
   1.134 +  LEFT JOIN "verified_privilege" ON
   1.135 +    "verified_privilege"."member_id" = "member"."id" AND
   1.136 +    "verified_privilege"."unit_id" = "area"."unit_id" AND
   1.137 +    "verified_privilege"."voting_right" = TRUE
   1.138    LEFT JOIN "subscription" ON
   1.139      "subscription"."member_id" = "member"."id" AND
   1.140      "subscription"."unit_id" = "area"."unit_id"
   1.141 @@ -3676,7 +3713,9 @@
   1.142    LEFT JOIN "supporter" ON
   1.143      "supporter"."member_id" = "member"."id" AND
   1.144      "supporter"."initiative_id" = "event"."initiative_id"
   1.145 -  WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
   1.146 +  WHERE (
   1.147 +    "verified_privilege"."member_id" NOTNULL OR
   1.148 +    "subscription"."member_id" NOTNULL )
   1.149    AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
   1.150    AND (
   1.151      "event"."event" = 'issue_state_changed'::"event_type" OR
   1.152 @@ -3782,10 +3821,10 @@
   1.153              LEFT JOIN "supporter" AS "self_support" ON
   1.154                "self_support"."initiative_id" = "initiative"."id" AND
   1.155                "self_support"."member_id" = "recipient_id_p"
   1.156 -            LEFT JOIN "privilege" ON
   1.157 -              "privilege"."member_id" = "recipient_id_p" AND
   1.158 -              "privilege"."unit_id" = "area"."unit_id" AND
   1.159 -              "privilege"."voting_right" = TRUE
   1.160 +            LEFT JOIN "verified_privilege" ON
   1.161 +              "verified_privilege"."member_id" = "recipient_id_p" AND
   1.162 +              "verified_privilege"."unit_id" = "area"."unit_id" AND
   1.163 +              "verified_privilege"."voting_right" = TRUE
   1.164              LEFT JOIN "subscription" ON
   1.165                "subscription"."member_id" = "recipient_id_p" AND
   1.166                "subscription"."unit_id" = "area"."unit_id"
   1.167 @@ -3799,7 +3838,7 @@
   1.168              AND "self_support"."member_id" ISNULL
   1.169              AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   1.170              AND (
   1.171 -              "privilege"."member_id" NOTNULL OR
   1.172 +              "verified_privilege"."member_id" NOTNULL OR
   1.173                "subscription"."member_id" NOTNULL )
   1.174              AND "ignored_initiative"."member_id" ISNULL
   1.175              AND NOT EXISTS (
   1.176 @@ -4025,10 +4064,10 @@
   1.177      "newsletter"."id" AS "newsletter_id",
   1.178      "newsletter"."published"
   1.179    FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
   1.180 -  LEFT JOIN "privilege" ON
   1.181 -    "privilege"."member_id" = "member"."id" AND
   1.182 -    "privilege"."unit_id" = "newsletter"."unit_id" AND
   1.183 -    "privilege"."voting_right" = TRUE
   1.184 +  LEFT JOIN "verified_privilege" ON
   1.185 +    "verified_privilege"."member_id" = "member"."id" AND
   1.186 +    "verified_privilege"."unit_id" = "newsletter"."unit_id" AND
   1.187 +    "verified_privilege"."voting_right" = TRUE
   1.188    LEFT JOIN "subscription" ON
   1.189      "subscription"."member_id" = "member"."id" AND
   1.190      "subscription"."unit_id" = "newsletter"."unit_id"
   1.191 @@ -4039,7 +4078,7 @@
   1.192      "newsletter"."include_all_members" = TRUE )
   1.193    AND (
   1.194      "newsletter"."unit_id" ISNULL OR
   1.195 -    "privilege"."member_id" NOTNULL OR
   1.196 +    "verified_privilege"."member_id" NOTNULL OR
   1.197      "subscription"."member_id" NOTNULL );
   1.198  
   1.199  COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
   1.200 @@ -4230,11 +4269,11 @@
   1.201          END IF;
   1.202          "output_row"."scope_in" := "output_row"."scope_out";
   1.203          "output_row"."member_valid" := EXISTS (
   1.204 -          SELECT NULL FROM "member" JOIN "privilege"
   1.205 -          ON "privilege"."member_id" = "member"."id"
   1.206 -          AND "privilege"."unit_id" = "unit_id_v"
   1.207 +          SELECT NULL FROM "member" JOIN "verified_privilege"
   1.208 +          ON "verified_privilege"."member_id" = "member"."id"
   1.209 +          AND "verified_privilege"."unit_id" = "unit_id_v"
   1.210            WHERE "id" = "output_row"."member_id"
   1.211 -          AND "member"."active" AND "privilege"."voting_right"
   1.212 +          AND "member"."active" AND "verified_privilege"."voting_right"
   1.213          );
   1.214          "simulate_here_v" := (
   1.215            "simulate_v" AND
   1.216 @@ -4939,11 +4978,11 @@
   1.217            JOIN "area" ON "issue"."area_id" = "area"."id"
   1.218            JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.219            JOIN "member" ON "interest"."member_id" = "member"."id"
   1.220 -          JOIN "privilege"
   1.221 -            ON "privilege"."unit_id" = "area"."unit_id"
   1.222 -            AND "privilege"."member_id" = "member"."id"
   1.223 +          JOIN "verified_privilege"
   1.224 +            ON "verified_privilege"."unit_id" = "area"."unit_id"
   1.225 +            AND "verified_privilege"."member_id" = "member"."id"
   1.226            WHERE "issue"."id" = "issue_id_v"
   1.227 -          AND "member"."active" AND "privilege"."voting_right";
   1.228 +          AND "member"."active" AND "verified_privilege"."voting_right";
   1.229          FOR "member_id_v" IN
   1.230            SELECT "member_id" FROM "direct_interest_snapshot"
   1.231            WHERE "snapshot_id" = "snapshot_id_v"
   1.232 @@ -5298,13 +5337,13 @@
   1.233              "direct_voter"."member_id"
   1.234            FROM "direct_voter"
   1.235            JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   1.236 -          LEFT JOIN "privilege"
   1.237 -          ON "privilege"."unit_id" = "unit_id_v"
   1.238 -          AND "privilege"."member_id" = "direct_voter"."member_id"
   1.239 +          LEFT JOIN "verified_privilege"
   1.240 +          ON "verified_privilege"."unit_id" = "unit_id_v"
   1.241 +          AND "verified_privilege"."member_id" = "direct_voter"."member_id"
   1.242            WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   1.243              "member"."active" = FALSE OR
   1.244 -            "privilege"."voting_right" ISNULL OR
   1.245 -            "privilege"."voting_right" = FALSE
   1.246 +            "verified_privilege"."voting_right" ISNULL OR
   1.247 +            "verified_privilege"."voting_right" = FALSE
   1.248            )
   1.249          ) AS "subquery"
   1.250          WHERE "direct_voter"."issue_id" = "issue_id_p"