liquid_feedback_core
changeset 10:effdd7a04ea7 beta11
Version beta11
Support is now automatically deleted, when interest is deleted
All tables dealing with delegations now contain a column scope (including table delegation)
Changes in fields related to notify_email of member
Policies can now specify larger majorities to be reached (e.g. 2/3)
New column agreed in table initiative to mark, if initiative has reached majority specified by policy
Minor bugfix in constraint non_admitted_initiatives_cant_contain_voting_results of initiative table
Function delete_private_data() no longer deletes invite codes, which have been already used
Removed view battle_participant
Added index on opinion (member_id, initiative_id)
Support is now automatically deleted, when interest is deleted
All tables dealing with delegations now contain a column scope (including table delegation)
Changes in fields related to notify_email of member
Policies can now specify larger majorities to be reached (e.g. 2/3)
New column agreed in table initiative to mark, if initiative has reached majority specified by policy
Minor bugfix in constraint non_admitted_initiatives_cant_contain_voting_results of initiative table
Function delete_private_data() no longer deletes invite codes, which have been already used
Removed view battle_participant
Added index on opinion (member_id, initiative_id)
author | jbe |
---|---|
date | Fri Dec 25 12:00:00 2009 +0100 (2009-12-25) |
parents | 4af4df1415f9 |
children | 015825e225ca |
files | core.sql demo.sql |
line diff
1.1 --- a/core.sql Thu Dec 10 12:00:00 2009 +0100 1.2 +++ b/core.sql Fri Dec 25 12:00:00 2009 +0100 1.3 @@ -6,7 +6,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('beta10', NULL, NULL, NULL)) 1.8 + SELECT * FROM (VALUES ('beta11', NULL, NULL, NULL)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -60,7 +60,9 @@ 1.13 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.14 "admin" BOOLEAN NOT NULL DEFAULT FALSE, 1.15 "notify_email" TEXT, 1.16 - "notify_email_confirmed" BOOLEAN, 1.17 + "notify_email_unconfirmed" TEXT, 1.18 + "notify_email_secret" TEXT, 1.19 + "notify_email_secret_expiry" TIMESTAMPTZ, 1.20 "name" TEXT NOT NULL UNIQUE, 1.21 "identification" TEXT UNIQUE, 1.22 "organizational_unit" TEXT, 1.23 @@ -77,9 +79,7 @@ 1.24 "external_memberships" TEXT, 1.25 "external_posts" TEXT, 1.26 "statement" TEXT, 1.27 - "text_search_data" TSVECTOR, 1.28 - CONSTRAINT "notify_email_null_check" 1.29 - CHECK ("notify_email" NOTNULL = "notify_email_confirmed" NOTNULL) ); 1.30 + "text_search_data" TSVECTOR ); 1.31 CREATE INDEX "member_active_idx" ON "member" ("active"); 1.32 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data"); 1.33 CREATE TRIGGER "update_text_search_data" 1.34 @@ -91,21 +91,23 @@ 1.35 1.36 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization'; 1.37 1.38 -COMMENT ON COLUMN "member"."login" IS 'Login name'; 1.39 -COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; 1.40 -COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.'; 1.41 -COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas'; 1.42 -COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to'; 1.43 -COMMENT ON COLUMN "member"."notify_email_confirmed" IS 'TRUE, if "notify_email" has been confirmed'; 1.44 -COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member'; 1.45 -COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member'; 1.46 -COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to'; 1.47 -COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization'; 1.48 -COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"'; 1.49 -COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications'; 1.50 -COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in'; 1.51 -COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization'; 1.52 -COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system'; 1.53 +COMMENT ON COLUMN "member"."login" IS 'Login name'; 1.54 +COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; 1.55 +COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.'; 1.56 +COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas'; 1.57 +COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to'; 1.58 +COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification'; 1.59 +COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"'; 1.60 +COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"'; 1.61 +COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member'; 1.62 +COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member'; 1.63 +COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to'; 1.64 +COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization'; 1.65 +COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"'; 1.66 +COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications'; 1.67 +COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in'; 1.68 +COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization'; 1.69 +COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system'; 1.70 1.71 1.72 CREATE TABLE "invite_code" ( 1.73 @@ -207,7 +209,10 @@ 1.74 "issue_quorum_num" INT4 NOT NULL, 1.75 "issue_quorum_den" INT4 NOT NULL, 1.76 "initiative_quorum_num" INT4 NOT NULL, 1.77 - "initiative_quorum_den" INT4 NOT NULL ); 1.78 + "initiative_quorum_den" INT4 NOT NULL, 1.79 + "majority_num" INT4 NOT NULL DEFAULT 1, 1.80 + "majority_den" INT4 NOT NULL DEFAULT 2, 1.81 + "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE ); 1.82 CREATE INDEX "policy_active_idx" ON "policy" ("active"); 1.83 1.84 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)'; 1.85 @@ -218,10 +223,13 @@ 1.86 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"'; 1.87 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"'; 1.88 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"'; 1.89 -COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"'; 1.90 -COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"'; 1.91 -COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of quorum to be reached by an initiative to be "admitted" for voting'; 1.92 -COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of quorum to be reached by an initiative to be "admitted" for voting'; 1.93 +COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"'; 1.94 +COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"'; 1.95 +COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting'; 1.96 +COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting'; 1.97 +COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon'; 1.98 +COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon'; 1.99 +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.100 1.101 1.102 CREATE TABLE "area" ( 1.103 @@ -291,13 +299,16 @@ 1.104 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.105 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ), 1.106 CONSTRAINT "state_change_order" CHECK ( 1.107 - "created" <= "accepted" AND 1.108 - "accepted" <= "half_frozen" AND 1.109 - "half_frozen" <= "fully_frozen" AND 1.110 + "created" <= "accepted" AND 1.111 + "accepted" <= "half_frozen" AND 1.112 + "half_frozen" <= "fully_frozen" AND 1.113 "fully_frozen" <= "closed" ), 1.114 - CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet 1.115 - CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL), 1.116 - CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) ); 1.117 + CONSTRAINT "last_snapshot_on_full_freeze" 1.118 + CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet 1.119 + CONSTRAINT "freeze_requires_snapshot" 1.120 + CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL), 1.121 + CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" 1.122 + CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) ); 1.123 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id"); 1.124 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id"); 1.125 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL; 1.126 @@ -305,9 +316,9 @@ 1.127 COMMENT ON TABLE "issue" IS 'Groups of initiatives'; 1.128 1.129 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 1.130 -COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting'; 1.131 -COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed'; 1.132 -COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active'; 1.133 +COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.'; 1.134 +COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.'; 1.135 +COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.'; 1.136 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 1.137 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated'; 1.138 COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables'; 1.139 @@ -332,12 +343,17 @@ 1.140 "satisfied_informed_supporter_count" INT4, 1.141 "positive_votes" INT4, 1.142 "negative_votes" INT4, 1.143 + "agreed" BOOLEAN, 1.144 "rank" INT4, 1.145 "text_search_data" TSVECTOR, 1.146 CONSTRAINT "revoked_initiatives_cant_be_admitted" 1.147 CHECK ("revoked" ISNULL OR "admitted" ISNULL), 1.148 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" 1.149 - CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) ); 1.150 + CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)), 1.151 + CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null" 1.152 + CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL), 1.153 + CONSTRAINT "non_agreed_initiatives_cant_get_a_rank" 1.154 + CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) ); 1.155 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 1.156 CREATE TRIGGER "update_text_search_data" 1.157 BEFORE INSERT OR UPDATE ON "initiative" 1.158 @@ -345,17 +361,18 @@ 1.159 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.160 "name", "discussion_url"); 1.161 1.162 -COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed'; 1.163 +COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.'; 1.164 1.165 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative'; 1.166 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative'; 1.167 -COMMENT ON COLUMN "initiative"."admitted" IS 'True, if initiative reaches the "initiative_quorum" when freezing the issue'; 1.168 +COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue'; 1.169 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.170 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.171 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.172 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.173 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.174 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.175 +COMMENT ON COLUMN "initiative"."agreed" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"'; 1.176 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"'; 1.177 1.178 1.179 @@ -375,7 +392,7 @@ 1.180 FOR EACH ROW EXECUTE PROCEDURE 1.181 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); 1.182 1.183 -COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues'; 1.184 +COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.'; 1.185 1.186 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used'; 1.187 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"'; 1.188 @@ -406,7 +423,7 @@ 1.189 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.190 "name", "description"); 1.191 1.192 -COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft'; 1.193 +COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted'; 1.194 1.195 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.196 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.197 @@ -438,7 +455,7 @@ 1.198 "voting_requested" BOOLEAN ); 1.199 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id"); 1.200 1.201 -COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue'; 1.202 +COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.'; 1.203 1.204 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure'; 1.205 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply'; 1.206 @@ -451,7 +468,7 @@ 1.207 "accepted" BOOLEAN NOT NULL DEFAULT TRUE ); 1.208 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id"); 1.209 1.210 -COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts'; 1.211 +COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts; Frontends must ensure that initiators are not added or removed from half_frozen, fully_frozen or closed initiatives.'; 1.212 1.213 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" = FALSE, then the member was invited to be a co-initiator, but has not answered yet.'; 1.214 1.215 @@ -462,11 +479,11 @@ 1.216 "initiative_id" INT4, 1.217 "member_id" INT4, 1.218 "draft_id" INT8 NOT NULL, 1.219 - FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.220 + FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE, 1.221 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.222 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id"); 1.223 1.224 -COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally)'; 1.225 +COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.'; 1.226 1.227 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; 1.228 1.229 @@ -480,26 +497,34 @@ 1.230 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE, 1.231 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.232 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.233 -CREATE INDEX "opinion_member_id_idx" ON "opinion" ("member_id"); 1.234 +CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id"); 1.235 1.236 -COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives)'; 1.237 +COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives); Frontends must ensure that opinions are not created modified or deleted when related to fully_frozen or closed issues.'; 1.238 1.239 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support'; 1.240 1.241 1.242 +CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue'); 1.243 + 1.244 +COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)'; 1.245 + 1.246 + 1.247 CREATE TABLE "delegation" ( 1.248 "id" SERIAL8 PRIMARY KEY, 1.249 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.250 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.251 + "scope" "delegation_scope" NOT NULL, 1.252 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.253 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.254 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"), 1.255 - CONSTRAINT "area_id_or_issue_id_can_be_set_but_not_both" CHECK ("area_id" ISNULL OR "issue_id" ISNULL), 1.256 + CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK ( 1.257 + ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR 1.258 + ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR 1.259 + ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ), 1.260 UNIQUE ("area_id", "truster_id", "trustee_id"), 1.261 UNIQUE ("issue_id", "truster_id", "trustee_id") ); 1.262 -CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx" 1.263 - ON "delegation" ("truster_id", "trustee_id") 1.264 - WHERE "area_id" ISNULL AND "issue_id" ISNULL; 1.265 +CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx" 1.266 + ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global'; 1.267 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id"); 1.268 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id"); 1.269 1.270 @@ -531,6 +556,7 @@ 1.271 "event" "snapshot_event", 1.272 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.273 "weight" INT4, 1.274 + "scope" "delegation_scope" NOT NULL, 1.275 "delegate_member_ids" INT4[] NOT NULL ); 1.276 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id"); 1.277 1.278 @@ -564,6 +590,7 @@ 1.279 "event" "snapshot_event", 1.280 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.281 "weight" INT4, 1.282 + "scope" "delegation_scope" NOT NULL, 1.283 "delegate_member_ids" INT4[] NOT NULL ); 1.284 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id"); 1.285 1.286 @@ -602,7 +629,7 @@ 1.287 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE ); 1.288 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id"); 1.289 1.290 -COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue'; 1.291 +COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; Frontends must ensure that no voters are added or removed to/from this table when the issue has been closed.'; 1.292 1.293 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table'; 1.294 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature'; 1.295 @@ -613,6 +640,7 @@ 1.296 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.297 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.298 "weight" INT4, 1.299 + "scope" "delegation_scope" NOT NULL, 1.300 "delegate_member_ids" INT4[] NOT NULL ); 1.301 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id"); 1.302 1.303 @@ -633,7 +661,7 @@ 1.304 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.305 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id"); 1.306 1.307 -COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions'; 1.308 +COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions; Frontends must ensure that no votes are added modified or removed when the issue has been closed.'; 1.309 1.310 COMMENT ON COLUMN "vote"."grade" IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.'; 1.311 1.312 @@ -981,12 +1009,6 @@ 1.313 ------------------------------------------ 1.314 1.315 1.316 -CREATE TYPE "delegation_scope" AS ENUM 1.317 - ('global', 'area', 'issue'); 1.318 - 1.319 -COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'''; 1.320 - 1.321 - 1.322 CREATE VIEW "global_delegation" AS 1.323 SELECT 1.324 "delegation"."id", 1.325 @@ -994,9 +1016,7 @@ 1.326 "delegation"."trustee_id" 1.327 FROM "delegation" JOIN "member" 1.328 ON "delegation"."trustee_id" = "member"."id" 1.329 - WHERE "delegation"."area_id" ISNULL 1.330 - AND "delegation"."issue_id" ISNULL 1.331 - AND "member"."active"; 1.332 + WHERE "delegation"."scope" = 'global' AND "member"."active"; 1.333 1.334 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members'; 1.335 1.336 @@ -1004,22 +1024,18 @@ 1.337 CREATE VIEW "area_delegation" AS 1.338 SELECT "subquery".* FROM ( 1.339 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id") 1.340 - "area"."id" AS "area_id", 1.341 - "delegation"."id" AS "id", 1.342 - "delegation"."truster_id" AS "truster_id", 1.343 - "delegation"."trustee_id" AS "trustee_id", 1.344 - CASE WHEN "delegation"."area_id" ISNULL THEN 1.345 - 'global'::"delegation_scope" 1.346 - ELSE 1.347 - 'area'::"delegation_scope" 1.348 - END AS "scope" 1.349 + "area"."id" AS "area_id", 1.350 + "delegation"."id", 1.351 + "delegation"."truster_id", 1.352 + "delegation"."trustee_id", 1.353 + "delegation"."scope" 1.354 FROM "area" JOIN "delegation" 1.355 - ON ("delegation"."area_id" ISNULL OR "delegation"."area_id" = "area"."id") 1.356 - AND "delegation"."issue_id" ISNULL 1.357 + ON "delegation"."scope" = 'global' 1.358 + OR "delegation"."area_id" = "area"."id" 1.359 ORDER BY 1.360 "area"."id", 1.361 "delegation"."truster_id", 1.362 - "delegation"."area_id" NULLS LAST 1.363 + "delegation"."scope" DESC 1.364 ) AS "subquery" 1.365 JOIN "member" ON "subquery"."trustee_id" = "member"."id" 1.366 WHERE "member"."active"; 1.367 @@ -1030,33 +1046,19 @@ 1.368 CREATE VIEW "issue_delegation" AS 1.369 SELECT "subquery".* FROM ( 1.370 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 1.371 - "issue"."id" AS "issue_id", 1.372 - "delegation"."id" AS "id", 1.373 - "delegation"."truster_id" AS "truster_id", 1.374 - "delegation"."trustee_id" AS "trustee_id", 1.375 - CASE 1.376 - WHEN 1.377 - "delegation"."area_id" ISNULL AND 1.378 - "delegation"."issue_id" ISNULL 1.379 - THEN 'global'::"delegation_scope" 1.380 - WHEN 1.381 - "delegation"."area_id" NOTNULL 1.382 - THEN 'area'::"delegation_scope" 1.383 - ELSE 'issue'::"delegation_scope" 1.384 - END AS "scope" 1.385 + "issue"."id" AS "issue_id", 1.386 + "delegation"."id", 1.387 + "delegation"."truster_id", 1.388 + "delegation"."trustee_id", 1.389 + "delegation"."scope" 1.390 FROM "issue" JOIN "delegation" 1.391 - ON ( 1.392 - "delegation"."area_id" ISNULL OR 1.393 - "delegation"."area_id" = "issue"."area_id" 1.394 - ) AND ( 1.395 - "delegation"."issue_id" ISNULL OR 1.396 - "delegation"."issue_id" = "issue"."id" 1.397 - ) 1.398 + ON "delegation"."scope" = 'global' 1.399 + OR "delegation"."area_id" = "issue"."area_id" 1.400 + OR "delegation"."issue_id" = "issue"."id" 1.401 ORDER BY 1.402 "issue"."id", 1.403 "delegation"."truster_id", 1.404 - "delegation"."issue_id" NULLS LAST, 1.405 - "delegation"."area_id" NULLS LAST 1.406 + "delegation"."scope" DESC 1.407 ) AS "subquery" 1.408 JOIN "member" ON "subquery"."trustee_id" = "member"."id" 1.409 WHERE "member"."active"; 1.410 @@ -1196,19 +1198,11 @@ 1.411 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction'; 1.412 1.413 1.414 -CREATE VIEW "battle_participant" AS 1.415 - SELECT "issue_id", "id" AS "initiative_id" FROM "initiative" 1.416 - WHERE "admitted" 1.417 - AND "positive_votes" > "negative_votes"; 1.418 - 1.419 -COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle" view'; 1.420 - 1.421 - 1.422 CREATE VIEW "battle" AS 1.423 SELECT 1.424 "issue"."id" AS "issue_id", 1.425 - "winning_initiative"."initiative_id" AS "winning_initiative_id", 1.426 - "losing_initiative"."initiative_id" AS "losing_initiative_id", 1.427 + "winning_initiative"."id" AS "winning_initiative_id", 1.428 + "losing_initiative"."id" AS "losing_initiative_id", 1.429 sum( 1.430 CASE WHEN 1.431 coalesce("better_vote"."grade", 0) > 1.432 @@ -1218,23 +1212,24 @@ 1.433 FROM "issue" 1.434 LEFT JOIN "direct_voter" 1.435 ON "issue"."id" = "direct_voter"."issue_id" 1.436 - JOIN "battle_participant" AS "winning_initiative" 1.437 - ON "issue"."id" = "winning_initiative"."issue_id" 1.438 - JOIN "battle_participant" AS "losing_initiative" 1.439 - ON "issue"."id" = "losing_initiative"."issue_id" 1.440 + JOIN "initiative" AS "winning_initiative" 1.441 + ON "issue"."id" = "winning_initiative"."issue_id" 1.442 + AND "winning_initiative"."agreed" 1.443 + JOIN "initiative" AS "losing_initiative" 1.444 + ON "issue"."id" = "losing_initiative"."issue_id" 1.445 + AND "losing_initiative"."agreed" 1.446 LEFT JOIN "vote" AS "better_vote" 1.447 - ON "direct_voter"."member_id" = "better_vote"."member_id" 1.448 - AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id" 1.449 + ON "direct_voter"."member_id" = "better_vote"."member_id" 1.450 + AND "winning_initiative"."id" = "better_vote"."initiative_id" 1.451 LEFT JOIN "vote" AS "worse_vote" 1.452 - ON "direct_voter"."member_id" = "worse_vote"."member_id" 1.453 - AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id" 1.454 + ON "direct_voter"."member_id" = "worse_vote"."member_id" 1.455 + AND "losing_initiative"."id" = "worse_vote"."initiative_id" 1.456 WHERE 1.457 - "winning_initiative"."initiative_id" != 1.458 - "losing_initiative"."initiative_id" 1.459 + "winning_initiative"."id" != "losing_initiative"."id" 1.460 GROUP BY 1.461 "issue"."id", 1.462 - "winning_initiative"."initiative_id", 1.463 - "losing_initiative"."initiative_id"; 1.464 + "winning_initiative"."id", 1.465 + "losing_initiative"."id"; 1.466 1.467 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another'; 1.468 1.469 @@ -1382,7 +1377,7 @@ 1.470 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 1.471 SELECT * INTO "delegation_row" FROM "delegation" 1.472 WHERE "truster_id" = "output_row"."member_id" 1.473 - AND "area_id" ISNULL AND "issue_id" ISNULL; 1.474 + AND "scope" = 'global'; 1.475 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 1.476 "output_row"."participation" := EXISTS ( 1.477 SELECT NULL FROM "membership" 1.478 @@ -1391,9 +1386,8 @@ 1.479 ); 1.480 SELECT * INTO "delegation_row" FROM "delegation" 1.481 WHERE "truster_id" = "output_row"."member_id" 1.482 - AND ("area_id" ISNULL OR "area_id" = "area_id_p") 1.483 - AND "issue_id" ISNULL 1.484 - ORDER BY "area_id" NULLS LAST; 1.485 + AND ("scope" = 'global' OR "area_id" = "area_id_p") 1.486 + ORDER BY "scope" DESC; 1.487 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 1.488 "output_row"."participation" := EXISTS ( 1.489 SELECT NULL FROM "interest" 1.490 @@ -1402,9 +1396,11 @@ 1.491 ); 1.492 SELECT * INTO "delegation_row" FROM "delegation" 1.493 WHERE "truster_id" = "output_row"."member_id" 1.494 - AND ("area_id" ISNULL OR "area_id" = "issue_row"."area_id") 1.495 - AND ("issue_id" ISNULL OR "issue_id" = "issue_id_p") 1.496 - ORDER BY "issue_id" NULLS LAST, "area_id" NULLS LAST; 1.497 + AND ("scope" = 'global' OR 1.498 + "area_id" = "issue_row"."area_id" OR 1.499 + "issue_id" = "issue_id_p" 1.500 + ) 1.501 + ORDER BY "scope" DESC; 1.502 ELSE 1.503 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.'; 1.504 END IF; 1.505 @@ -1426,20 +1422,7 @@ 1.506 "output_rows" := "output_rows" || "output_row"; 1.507 "output_row"."member_id" := "simulate_trustee_id_p"; 1.508 ELSIF "delegation_row"."trustee_id" NOTNULL THEN 1.509 - "output_row"."scope_out" := CASE 1.510 - WHEN 1.511 - "delegation_row"."area_id" ISNULL AND 1.512 - "delegation_row"."issue_id" ISNULL 1.513 - THEN 'global' 1.514 - WHEN 1.515 - "delegation_row"."area_id" NOTNULL AND 1.516 - "delegation_row"."issue_id" ISNULL 1.517 - THEN 'area' 1.518 - WHEN 1.519 - "delegation_row"."area_id" ISNULL AND 1.520 - "delegation_row"."issue_id" NOTNULL 1.521 - THEN 'issue' 1.522 - END; 1.523 + "output_row"."scope_out" := "delegation_row"."scope"; 1.524 "output_rows" := "output_rows" || "output_row"; 1.525 "output_row"."member_id" := "delegation_row"."trustee_id"; 1.526 ELSE 1.527 @@ -1644,12 +1627,17 @@ 1.528 ) THEN 1.529 "delegate_member_ids_v" := 1.530 "member_id_p" || "delegate_member_ids_p"; 1.531 - INSERT INTO "delegating_population_snapshot" 1.532 - ("issue_id", "event", "member_id", "delegate_member_ids") 1.533 - VALUES ( 1.534 + INSERT INTO "delegating_population_snapshot" ( 1.535 + "issue_id", 1.536 + "event", 1.537 + "member_id", 1.538 + "scope", 1.539 + "delegate_member_ids" 1.540 + ) VALUES ( 1.541 "issue_id_p", 1.542 'periodic', 1.543 "issue_delegation_row"."truster_id", 1.544 + "issue_delegation_row"."scope", 1.545 "delegate_member_ids_v" 1.546 ); 1.547 "sub_weight_v" := 1 + 1.548 @@ -1776,12 +1764,17 @@ 1.549 ) THEN 1.550 "delegate_member_ids_v" := 1.551 "member_id_p" || "delegate_member_ids_p"; 1.552 - INSERT INTO "delegating_interest_snapshot" 1.553 - ("issue_id", "event", "member_id", "delegate_member_ids") 1.554 - VALUES ( 1.555 + INSERT INTO "delegating_interest_snapshot" ( 1.556 + "issue_id", 1.557 + "event", 1.558 + "member_id", 1.559 + "scope", 1.560 + "delegate_member_ids" 1.561 + ) VALUES ( 1.562 "issue_id_p", 1.563 'periodic', 1.564 "issue_delegation_row"."truster_id", 1.565 + "issue_delegation_row"."scope", 1.566 "delegate_member_ids_v" 1.567 ); 1.568 "sub_weight_v" := 1 + 1.569 @@ -2195,11 +2188,15 @@ 1.570 ) THEN 1.571 "delegate_member_ids_v" := 1.572 "member_id_p" || "delegate_member_ids_p"; 1.573 - INSERT INTO "delegating_voter" 1.574 - ("issue_id", "member_id", "delegate_member_ids") 1.575 - VALUES ( 1.576 + INSERT INTO "delegating_voter" ( 1.577 + "issue_id", 1.578 + "member_id", 1.579 + "scope", 1.580 + "delegate_member_ids" 1.581 + ) VALUES ( 1.582 "issue_id_p", 1.583 "issue_delegation_row"."truster_id", 1.584 + "issue_delegation_row"."scope", 1.585 "delegate_member_ids_v" 1.586 ); 1.587 "sub_weight_v" := 1 + 1.588 @@ -2328,34 +2325,49 @@ 1.589 ) 1.590 WHERE "id" = "issue_id_p"; 1.591 UPDATE "initiative" SET 1.592 - "positive_votes" = "subquery"."positive_votes", 1.593 - "negative_votes" = "subquery"."negative_votes" 1.594 - FROM ( 1.595 - SELECT 1.596 - "initiative"."id" AS "initiative_id", 1.597 - coalesce( 1.598 - sum( 1.599 - CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 1.600 - ), 1.601 - 0 1.602 - ) AS "positive_votes", 1.603 - coalesce( 1.604 - sum( 1.605 - CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 1.606 - ), 1.607 - 0 1.608 - ) AS "negative_votes" 1.609 - FROM "initiative" 1.610 - LEFT JOIN "direct_voter" 1.611 - ON "direct_voter"."issue_id" = "initiative"."issue_id" 1.612 - LEFT JOIN "vote" 1.613 - ON "vote"."initiative_id" = "initiative"."id" 1.614 - AND "vote"."member_id" = "direct_voter"."member_id" 1.615 - WHERE "initiative"."issue_id" = "issue_id_p" 1.616 - GROUP BY "initiative"."id" 1.617 - ) AS "subquery" 1.618 - WHERE "initiative"."admitted" 1.619 - AND "initiative"."id" = "subquery"."initiative_id"; 1.620 + "positive_votes" = "vote_counts"."positive_votes", 1.621 + "negative_votes" = "vote_counts"."negative_votes", 1.622 + "agreed" = CASE WHEN "majority_strict" THEN 1.623 + "vote_counts"."positive_votes" * "majority_den" > 1.624 + "majority_num" * 1.625 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.626 + ELSE 1.627 + "vote_counts"."positive_votes" * "majority_den" >= 1.628 + "majority_num" * 1.629 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.630 + END 1.631 + FROM 1.632 + ( SELECT 1.633 + "initiative"."id" AS "initiative_id", 1.634 + coalesce( 1.635 + sum( 1.636 + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 1.637 + ), 1.638 + 0 1.639 + ) AS "positive_votes", 1.640 + coalesce( 1.641 + sum( 1.642 + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 1.643 + ), 1.644 + 0 1.645 + ) AS "negative_votes" 1.646 + FROM "initiative" 1.647 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 1.648 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 1.649 + LEFT JOIN "direct_voter" 1.650 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 1.651 + LEFT JOIN "vote" 1.652 + ON "vote"."initiative_id" = "initiative"."id" 1.653 + AND "vote"."member_id" = "direct_voter"."member_id" 1.654 + WHERE "initiative"."issue_id" = "issue_id_p" 1.655 + AND "initiative"."admitted" 1.656 + GROUP BY "initiative"."id" 1.657 + ) AS "vote_counts", 1.658 + "issue", 1.659 + "policy" 1.660 + WHERE "vote_counts"."initiative_id" = "initiative"."id" 1.661 + AND "issue"."id" = "initiative"."issue_id" 1.662 + AND "policy"."id" = "issue"."policy_id"; 1.663 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p"; 1.664 END; 1.665 $$; 1.666 @@ -2444,14 +2456,11 @@ 1.667 BEGIN 1.668 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 1.669 -- Prepare matrix for Schulze-Method: 1.670 - SELECT count(1) INTO "dimension_v" 1.671 - FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 1.672 + SELECT count(1) INTO "dimension_v" FROM "initiative" 1.673 + WHERE "issue_id" = "issue_id_p" AND "agreed"; 1.674 IF "dimension_v" = 1 THEN 1.675 - UPDATE "initiative" SET 1.676 - "rank" = 1 1.677 - FROM "battle_participant" 1.678 - WHERE "initiative"."issue_id" = "issue_id_p" 1.679 - AND "initiative"."id" = "battle_participant"."initiative_id"; 1.680 + UPDATE "initiative" SET "rank" = 1 1.681 + WHERE "issue_id" = "issue_id_p" AND "agreed"; 1.682 ELSIF "dimension_v" > 1 THEN 1.683 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) 1.684 "i" := 1; 1.685 @@ -2568,11 +2577,9 @@ 1.686 -- write preliminary ranks: 1.687 "i" := 1; 1.688 FOR "initiative_id_v" IN 1.689 - SELECT "initiative"."id" 1.690 - FROM "initiative" JOIN "battle_participant" 1.691 - ON "initiative"."id" = "battle_participant"."initiative_id" 1.692 - WHERE "initiative"."issue_id" = "issue_id_p" 1.693 - ORDER BY "initiative"."id" 1.694 + SELECT "id" FROM "initiative" 1.695 + WHERE "issue_id" = "issue_id_p" AND "agreed" 1.696 + ORDER BY "id" 1.697 LOOP 1.698 UPDATE "initiative" SET "rank" = "rank_ary"["i"] 1.699 WHERE "id" = "initiative_id_v"; 1.700 @@ -2737,12 +2744,14 @@ 1.701 "issue_id_v" "issue"."id"%TYPE; 1.702 BEGIN 1.703 UPDATE "member" SET 1.704 - "login" = 'login' || "id"::text, 1.705 - "password" = NULL, 1.706 - "notify_email" = NULL, 1.707 - "notify_email_confirmed" = NULL; 1.708 + "login" = 'login' || "id"::text, 1.709 + "password" = NULL, 1.710 + "notify_email" = NULL, 1.711 + "notify_email_unconfirmed" = NULL, 1.712 + "notify_email_secret" = NULL, 1.713 + "notify_email_secret_expiry" = NULL; 1.714 DELETE FROM "session"; 1.715 - DELETE FROM "invite_code"; 1.716 + DELETE FROM "invite_code" WHERE "used" ISNULL; 1.717 DELETE FROM "contact" WHERE NOT "public"; 1.718 DELETE FROM "direct_voter" USING "issue" 1.719 WHERE "direct_voter"."issue_id" = "issue"."id"
2.1 --- a/demo.sql Thu Dec 10 12:00:00 2009 +0100 2.2 +++ b/demo.sql Fri Dec 25 12:00:00 2009 +0100 2.3 @@ -89,35 +89,35 @@ 2.4 2.5 -- global delegations 2.6 INSERT INTO "delegation" 2.7 - ("truster_id", "trustee_id") VALUES 2.8 - ( 1, 9), 2.9 - ( 2, 11), 2.10 - ( 3, 12), 2.11 - ( 4, 13), 2.12 - ( 5, 14), 2.13 - ( 6, 7), 2.14 - ( 7, 8), 2.15 - ( 8, 6), 2.16 - (10, 9), 2.17 - (11, 9), 2.18 - (12, 21), 2.19 - (15, 10), 2.20 - (16, 17), 2.21 - (17, 19), 2.22 - (18, 19), 2.23 - (23, 22); 2.24 + ("truster_id", "scope", "trustee_id") VALUES 2.25 + ( 1, 'global', 9), 2.26 + ( 2, 'global', 11), 2.27 + ( 3, 'global', 12), 2.28 + ( 4, 'global', 13), 2.29 + ( 5, 'global', 14), 2.30 + ( 6, 'global', 7), 2.31 + ( 7, 'global', 8), 2.32 + ( 8, 'global', 6), 2.33 + (10, 'global', 9), 2.34 + (11, 'global', 9), 2.35 + (12, 'global', 21), 2.36 + (15, 'global', 10), 2.37 + (16, 'global', 17), 2.38 + (17, 'global', 19), 2.39 + (18, 'global', 19), 2.40 + (23, 'global', 22); 2.41 2.42 -- delegations for topics 2.43 INSERT INTO "delegation" 2.44 - ("area_id", "truster_id", "trustee_id") VALUES 2.45 - (1, 3, 17), 2.46 - (2, 5, 10), 2.47 - (2, 9, 10), 2.48 - (3, 4, 14), 2.49 - (3, 16, 20), 2.50 - (3, 19, 20), 2.51 - (4, 5, 13), 2.52 - (4, 12, 22); 2.53 + ("area_id", "truster_id", "scope", "trustee_id") VALUES 2.54 + (1, 3, 'area', 17), 2.55 + (2, 5, 'area', 10), 2.56 + (2, 9, 'area', 10), 2.57 + (3, 4, 'area', 14), 2.58 + (3, 16, 'area', 20), 2.59 + (3, 19, 'area', 20), 2.60 + (4, 5, 'area', 13), 2.61 + (4, 12, 'area', 22); 2.62 2.63 INSERT INTO "issue" ("area_id", "policy_id") VALUES 2.64 (3, 1); -- id 1