1.1 --- a/core.sql	Sun Sep 17 01:48:45 2017 +0200
     1.2 +++ b/core.sql	Sun Sep 17 13:19:37 2017 +0200
     1.3 @@ -597,21 +597,10 @@
     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 view "verified_privilege" (only active members counted)';
     1.8 +COMMENT ON COLUMN "unit"."member_count"       IS 'Count of members as determined by column "voting_right" in table "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 @@ -1070,18 +1059,6 @@
    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 @@ -3336,20 +3313,6 @@
    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 @@ -3362,10 +3325,10 @@
    1.67      ON "delegation"."unit_id" = "unit"."id"
    1.68    JOIN "member"
    1.69      ON "delegation"."truster_id" = "member"."id"
    1.70 -  JOIN "verified_privilege"
    1.71 -    ON "delegation"."unit_id" = "verified_privilege"."unit_id"
    1.72 -    AND "delegation"."truster_id" = "verified_privilege"."member_id"
    1.73 -  WHERE "member"."active" AND "verified_privilege"."voting_right";
    1.74 +  JOIN "privilege"
    1.75 +    ON "delegation"."unit_id" = "privilege"."unit_id"
    1.76 +    AND "delegation"."truster_id" = "privilege"."member_id"
    1.77 +  WHERE "member"."active" AND "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 @@ -3383,10 +3346,10 @@
    1.82      OR "delegation"."area_id" = "area"."id"
    1.83    JOIN "member"
    1.84      ON "delegation"."truster_id" = "member"."id"
    1.85 -  JOIN "verified_privilege"
    1.86 -    ON "area"."unit_id" = "verified_privilege"."unit_id"
    1.87 -    AND "delegation"."truster_id" = "verified_privilege"."member_id"
    1.88 -  WHERE "member"."active" AND "verified_privilege"."voting_right"
    1.89 +  JOIN "privilege"
    1.90 +    ON "area"."unit_id" = "privilege"."unit_id"
    1.91 +    AND "delegation"."truster_id" = "privilege"."member_id"
    1.92 +  WHERE "member"."active" AND "privilege"."voting_right"
    1.93    ORDER BY
    1.94      "area"."id",
    1.95      "delegation"."truster_id",
    1.96 @@ -3411,10 +3374,10 @@
    1.97      OR "delegation"."issue_id" = "issue"."id"
    1.98    JOIN "member"
    1.99      ON "delegation"."truster_id" = "member"."id"
   1.100 -  JOIN "verified_privilege"
   1.101 -    ON "area"."unit_id" = "verified_privilege"."unit_id"
   1.102 -    AND "delegation"."truster_id" = "verified_privilege"."member_id"
   1.103 -  WHERE "member"."active" AND "verified_privilege"."voting_right"
   1.104 +  JOIN "privilege"
   1.105 +    ON "area"."unit_id" = "privilege"."unit_id"
   1.106 +    AND "delegation"."truster_id" = "privilege"."member_id"
   1.107 +  WHERE "member"."active" AND "privilege"."voting_right"
   1.108    ORDER BY
   1.109      "issue"."id",
   1.110      "delegation"."truster_id",
   1.111 @@ -3433,10 +3396,10 @@
   1.112    SELECT
   1.113      "unit"."id"   AS "unit_id",
   1.114      "member"."id" AS "member_id"
   1.115 -  FROM "verified_privilege"
   1.116 -  JOIN "unit"   ON "unit_id"     = "verified_privilege"."unit_id"
   1.117 -  JOIN "member" ON "member"."id" = "verified_privilege"."member_id"
   1.118 -  WHERE "verified_privilege"."voting_right" AND "member"."active";
   1.119 +  FROM "privilege"
   1.120 +  JOIN "unit"   ON "unit_id"     = "privilege"."unit_id"
   1.121 +  JOIN "member" ON "member"."id" = "privilege"."member_id"
   1.122 +  WHERE "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 @@ -3697,10 +3660,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 "verified_privilege" ON
   1.131 -    "verified_privilege"."member_id" = "member"."id" AND
   1.132 -    "verified_privilege"."unit_id" = "area"."unit_id" AND
   1.133 -    "verified_privilege"."voting_right" = TRUE
   1.134 +  LEFT JOIN "privilege" ON
   1.135 +    "privilege"."member_id" = "member"."id" AND
   1.136 +    "privilege"."unit_id" = "area"."unit_id" AND
   1.137 +    "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 @@ -3713,9 +3676,7 @@
   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 (
   1.146 -    "verified_privilege"."member_id" NOTNULL OR
   1.147 -    "subscription"."member_id" NOTNULL )
   1.148 +  WHERE ("privilege"."member_id" NOTNULL OR "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 @@ -3821,10 +3782,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 "verified_privilege" ON
   1.157 -              "verified_privilege"."member_id" = "recipient_id_p" AND
   1.158 -              "verified_privilege"."unit_id" = "area"."unit_id" AND
   1.159 -              "verified_privilege"."voting_right" = TRUE
   1.160 +            LEFT JOIN "privilege" ON
   1.161 +              "privilege"."member_id" = "recipient_id_p" AND
   1.162 +              "privilege"."unit_id" = "area"."unit_id" AND
   1.163 +              "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 @@ -3838,7 +3799,7 @@
   1.168              AND "self_support"."member_id" ISNULL
   1.169              AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   1.170              AND (
   1.171 -              "verified_privilege"."member_id" NOTNULL OR
   1.172 +              "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 @@ -4064,10 +4025,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 "verified_privilege" ON
   1.181 -    "verified_privilege"."member_id" = "member"."id" AND
   1.182 -    "verified_privilege"."unit_id" = "newsletter"."unit_id" AND
   1.183 -    "verified_privilege"."voting_right" = TRUE
   1.184 +  LEFT JOIN "privilege" ON
   1.185 +    "privilege"."member_id" = "member"."id" AND
   1.186 +    "privilege"."unit_id" = "newsletter"."unit_id" AND
   1.187 +    "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 @@ -4078,7 +4039,7 @@
   1.192      "newsletter"."include_all_members" = TRUE )
   1.193    AND (
   1.194      "newsletter"."unit_id" ISNULL OR
   1.195 -    "verified_privilege"."member_id" NOTNULL OR
   1.196 +    "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 @@ -4269,11 +4230,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 "verified_privilege"
   1.205 -          ON "verified_privilege"."member_id" = "member"."id"
   1.206 -          AND "verified_privilege"."unit_id" = "unit_id_v"
   1.207 +          SELECT NULL FROM "member" JOIN "privilege"
   1.208 +          ON "privilege"."member_id" = "member"."id"
   1.209 +          AND "privilege"."unit_id" = "unit_id_v"
   1.210            WHERE "id" = "output_row"."member_id"
   1.211 -          AND "member"."active" AND "verified_privilege"."voting_right"
   1.212 +          AND "member"."active" AND "privilege"."voting_right"
   1.213          );
   1.214          "simulate_here_v" := (
   1.215            "simulate_v" AND
   1.216 @@ -4978,11 +4939,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 "verified_privilege"
   1.221 -            ON "verified_privilege"."unit_id" = "area"."unit_id"
   1.222 -            AND "verified_privilege"."member_id" = "member"."id"
   1.223 +          JOIN "privilege"
   1.224 +            ON "privilege"."unit_id" = "area"."unit_id"
   1.225 +            AND "privilege"."member_id" = "member"."id"
   1.226            WHERE "issue"."id" = "issue_id_v"
   1.227 -          AND "member"."active" AND "verified_privilege"."voting_right";
   1.228 +          AND "member"."active" AND "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 @@ -5337,13 +5298,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 "verified_privilege"
   1.237 -          ON "verified_privilege"."unit_id" = "unit_id_v"
   1.238 -          AND "verified_privilege"."member_id" = "direct_voter"."member_id"
   1.239 +          LEFT JOIN "privilege"
   1.240 +          ON "privilege"."unit_id" = "unit_id_v"
   1.241 +          AND "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 -            "verified_privilege"."voting_right" ISNULL OR
   1.245 -            "verified_privilege"."voting_right" = FALSE
   1.246 +            "privilege"."voting_right" ISNULL OR
   1.247 +            "privilege"."voting_right" = FALSE
   1.248            )
   1.249          ) AS "subquery"
   1.250          WHERE "direct_voter"."issue_id" = "issue_id_p"