liquid_feedback_core
changeset 8:e6faf5ff83af beta9
Version beta9
Added missing indicies on TSVECTOR fields
New field latest_snapshot_event in table issue specifying the type of the latest snapshot taken
Added weight field also for (intermediate) delegating members in snapshot and voter tables
Possibility for an initiative to specify a URL where an external discussion takes place (discussion_url)
Export concept for creating a database dump, without some non-public information (e.g. private contact data), including:
- Shell script lf_export
- Database function delete_private_data()
Field in member table to be used by a frontend to store information about hints being hidden by the user
Minor cleanup / New comments
Added missing indicies on TSVECTOR fields
New field latest_snapshot_event in table issue specifying the type of the latest snapshot taken
Added weight field also for (intermediate) delegating members in snapshot and voter tables
Possibility for an initiative to specify a URL where an external discussion takes place (discussion_url)
Export concept for creating a database dump, without some non-public information (e.g. private contact data), including:
- Shell script lf_export
- Database function delete_private_data()
Field in member table to be used by a frontend to store information about hints being hidden by the user
Minor cleanup / New comments
author | jbe |
---|---|
date | Mon Nov 30 12:00:00 2009 +0100 (2009-11-30) |
parents | 69d84040fb93 |
children | 4af4df1415f9 |
files | README core.sql lf_export |
line diff
1.1 --- a/README Mon Nov 23 12:00:00 2009 +0100 1.2 +++ b/README Mon Nov 30 12:00:00 2009 +0100 1.3 @@ -18,6 +18,10 @@ 1.4 within the same transaction, e.g. issues can't exist without 1.5 an initiative and vice versa. 1.6 1.7 +To create an export file, which is containing all but private data, 1.8 +you may use the lf_export shell-script: 1.9 +$ lf_export liquid_feedback export.sql.gz 1.10 + 1.11 To uninstall the software, delete the lf_update binary 1.12 and drop the database by entering the following command: 1.13 $ dropdb liquid_feedback
2.1 --- a/core.sql Mon Nov 23 12:00:00 2009 +0100 2.2 +++ b/core.sql Mon Nov 30 12:00:00 2009 +0100 2.3 @@ -6,7 +6,7 @@ 2.4 BEGIN; 2.5 2.6 CREATE VIEW "liquid_feedback_version" AS 2.7 - SELECT * FROM (VALUES ('beta8', NULL, NULL, NULL)) 2.8 + SELECT * FROM (VALUES ('beta9', NULL, NULL, NULL)) 2.9 AS "subquery"("string", "major", "minor", "revision"); 2.10 2.11 2.12 @@ -35,7 +35,7 @@ 2.13 BEGIN 2.14 RETURN ts_headline( 2.15 'pg_catalog.simple', 2.16 - replace(replace("body_p", '\\', '\\\\'), '*', '\\*'), 2.17 + replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'), 2.18 "text_search_query"("query_text_p"), 2.19 'StartSel=* StopSel=* HighlightAll=TRUE' ); 2.20 END; 2.21 @@ -52,12 +52,14 @@ 2.22 -- Tables and indicies -- 2.23 ------------------------- 2.24 2.25 + 2.26 CREATE TABLE "member" ( 2.27 "id" SERIAL4 PRIMARY KEY, 2.28 "login" TEXT NOT NULL UNIQUE, 2.29 "password" TEXT, 2.30 "active" BOOLEAN NOT NULL DEFAULT TRUE, 2.31 "admin" BOOLEAN NOT NULL DEFAULT FALSE, 2.32 + "hidden_hints" TEXT[], 2.33 "notify_email" TEXT, 2.34 "notify_email_confirmed" BOOLEAN, 2.35 "name" TEXT NOT NULL UNIQUE, 2.36 @@ -80,6 +82,7 @@ 2.37 CONSTRAINT "notify_email_null_check" 2.38 CHECK ("notify_email" NOTNULL = "notify_email_confirmed" NOTNULL) ); 2.39 CREATE INDEX "member_active_idx" ON "member" ("active"); 2.40 +CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data"); 2.41 CREATE TRIGGER "update_text_search_data" 2.42 BEFORE INSERT OR UPDATE ON "member" 2.43 FOR EACH ROW EXECUTE PROCEDURE 2.44 @@ -93,6 +96,7 @@ 2.45 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; 2.46 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.'; 2.47 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas'; 2.48 +COMMENT ON COLUMN "member"."hidden_hints" IS 'This field may be used by a frontend to store identification strings for introductory hints, which the user wants to hide.'; 2.49 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to'; 2.50 COMMENT ON COLUMN "member"."notify_email_confirmed" IS 'TRUE, if "notify_email" has been confirmed'; 2.51 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member'; 2.52 @@ -202,6 +206,7 @@ 2.53 "autoreject_weight" INT4, 2.54 "text_search_data" TSVECTOR ); 2.55 CREATE INDEX "area_active_idx" ON "area" ("active"); 2.56 +CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data"); 2.57 CREATE TRIGGER "update_text_search_data" 2.58 BEFORE INSERT OR UPDATE ON "area" 2.59 FOR EACH ROW EXECUTE PROCEDURE 2.60 @@ -216,6 +221,11 @@ 2.61 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature'; 2.62 2.63 2.64 +CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting'); 2.65 + 2.66 +COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period'; 2.67 + 2.68 + 2.69 CREATE TABLE "issue" ( 2.70 "id" SERIAL4 PRIMARY KEY, 2.71 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.72 @@ -227,6 +237,7 @@ 2.73 "closed" TIMESTAMPTZ, 2.74 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE, 2.75 "snapshot" TIMESTAMPTZ, 2.76 + "latest_snapshot_event" "snapshot_event", 2.77 "population" INT4, 2.78 "vote_now" INT4, 2.79 "vote_later" INT4, 2.80 @@ -245,23 +256,25 @@ 2.81 "half_frozen" <= "fully_frozen" AND 2.82 "fully_frozen" <= "closed" ), 2.83 CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet 2.84 - CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL) ); 2.85 + CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL), 2.86 + CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) ); 2.87 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id"); 2.88 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id"); 2.89 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL; 2.90 2.91 COMMENT ON TABLE "issue" IS 'Groups of initiatives'; 2.92 2.93 -COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 2.94 -COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting'; 2.95 -COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed'; 2.96 -COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active'; 2.97 -COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 2.98 -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'; 2.99 -COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 2.100 -COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"'; 2.101 -COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"'; 2.102 -COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting'; 2.103 +COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 2.104 +COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting'; 2.105 +COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed'; 2.106 +COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active'; 2.107 +COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 2.108 +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'; 2.109 +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'; 2.110 +COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 2.111 +COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"'; 2.112 +COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"'; 2.113 +COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting'; 2.114 2.115 2.116 CREATE TABLE "initiative" ( 2.117 @@ -269,6 +282,7 @@ 2.118 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.119 "id" SERIAL4 PRIMARY KEY, 2.120 "name" TEXT NOT NULL, 2.121 + "discussion_url" TEXT, 2.122 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 2.123 "revoked" TIMESTAMPTZ, 2.124 "admitted" BOOLEAN, 2.125 @@ -284,13 +298,16 @@ 2.126 CHECK ("revoked" ISNULL OR "admitted" ISNULL), 2.127 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" 2.128 CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) ); 2.129 +CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 2.130 CREATE TRIGGER "update_text_search_data" 2.131 BEFORE INSERT OR UPDATE ON "initiative" 2.132 FOR EACH ROW EXECUTE PROCEDURE 2.133 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name"); 2.134 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 2.135 + "name", "discussion_url"); 2.136 2.137 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed'; 2.138 2.139 +COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative'; 2.140 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative'; 2.141 COMMENT ON COLUMN "initiative"."admitted" IS 'True, if initiative reaches the "initiative_quorum" when freezing the issue'; 2.142 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 2.143 @@ -310,6 +327,7 @@ 2.144 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.145 "content" TEXT NOT NULL, 2.146 "text_search_data" TSVECTOR ); 2.147 +CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data"); 2.148 CREATE TRIGGER "update_text_search_data" 2.149 BEFORE INSERT OR UPDATE ON "draft" 2.150 FOR EACH ROW EXECUTE PROCEDURE 2.151 @@ -335,6 +353,7 @@ 2.152 "plus1_fulfilled_count" INT4, 2.153 "plus2_unfulfilled_count" INT4, 2.154 "plus2_fulfilled_count" INT4 ); 2.155 +CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data"); 2.156 CREATE TRIGGER "update_text_search_data" 2.157 BEFORE INSERT OR UPDATE ON "suggestion" 2.158 FOR EACH ROW EXECUTE PROCEDURE 2.159 @@ -444,11 +463,6 @@ 2.160 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL'; 2.161 2.162 2.163 -CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting'); 2.164 - 2.165 -COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period'; 2.166 - 2.167 - 2.168 CREATE TABLE "direct_population_snapshot" ( 2.169 PRIMARY KEY ("issue_id", "event", "member_id"), 2.170 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.171 @@ -470,6 +484,7 @@ 2.172 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.173 "event" "snapshot_event", 2.174 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.175 + "weight" INT4, 2.176 "delegate_member_ids" INT4[] NOT NULL ); 2.177 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id"); 2.178 2.179 @@ -477,6 +492,7 @@ 2.180 2.181 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 2.182 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member'; 2.183 +COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight'; 2.184 COMMENT ON COLUMN "delegating_population_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_population_snapshot"'; 2.185 2.186 2.187 @@ -501,6 +517,7 @@ 2.188 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.189 "event" "snapshot_event", 2.190 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.191 + "weight" INT4, 2.192 "delegate_member_ids" INT4[] NOT NULL ); 2.193 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id"); 2.194 2.195 @@ -508,6 +525,7 @@ 2.196 2.197 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 2.198 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member'; 2.199 +COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight'; 2.200 COMMENT ON COLUMN "delegating_interest_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_interest_snapshot"'; 2.201 2.202 2.203 @@ -523,7 +541,7 @@ 2.204 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE ); 2.205 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id"); 2.206 2.207 -COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot)'; 2.208 +COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")'; 2.209 2.210 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 2.211 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative'; 2.212 @@ -548,12 +566,14 @@ 2.213 PRIMARY KEY ("issue_id", "member_id"), 2.214 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.215 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.216 + "weight" INT4, 2.217 "delegate_member_ids" INT4[] NOT NULL ); 2.218 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id"); 2.219 2.220 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table'; 2.221 2.222 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member'; 2.223 +COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight'; 2.224 COMMENT ON COLUMN "delegating_voter"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_voter"'; 2.225 2.226 2.227 @@ -1021,6 +1041,13 @@ 2.228 END; 2.229 $$; 2.230 2.231 +COMMENT ON FUNCTION "membership_weight_with_skipping" 2.232 + ( "area"."id"%TYPE, 2.233 + "member"."id"%TYPE, 2.234 + INT4[] ) 2.235 + IS 'Helper function for "membership_weight" function'; 2.236 + 2.237 + 2.238 CREATE FUNCTION "membership_weight" 2.239 ( "area_id_p" "area"."id"%TYPE, 2.240 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[] 2.241 @@ -1035,6 +1062,11 @@ 2.242 END; 2.243 $$; 2.244 2.245 +COMMENT ON FUNCTION "membership_weight" 2.246 + ( "area"."id"%TYPE, 2.247 + "member"."id"%TYPE ) 2.248 + IS 'Calculates the potential voting weight of a member in a given area'; 2.249 + 2.250 2.251 CREATE VIEW "member_count_view" AS 2.252 SELECT count(1) AS "total_count" FROM "member" WHERE "active"; 2.253 @@ -1067,7 +1099,6 @@ 2.254 ON "membership"."member_id" = "member"."id" 2.255 AND "member"."active" 2.256 GROUP BY "area"."id"; 2.257 --- TODO: count delegations 2.258 2.259 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"'; 2.260 2.261 @@ -1481,6 +1512,7 @@ 2.262 "issue_delegation_row" "issue_delegation"%ROWTYPE; 2.263 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE; 2.264 "weight_v" INT4; 2.265 + "sub_weight_v" INT4; 2.266 BEGIN 2.267 "weight_v" := 0; 2.268 FOR "issue_delegation_row" IN 2.269 @@ -1509,12 +1541,18 @@ 2.270 "issue_delegation_row"."truster_id", 2.271 "delegate_member_ids_v" 2.272 ); 2.273 - "weight_v" := "weight_v" + 1 + 2.274 + "sub_weight_v" := 1 + 2.275 "weight_of_added_delegations_for_population_snapshot"( 2.276 "issue_id_p", 2.277 "issue_delegation_row"."truster_id", 2.278 "delegate_member_ids_v" 2.279 ); 2.280 + UPDATE "delegating_population_snapshot" 2.281 + SET "weight" = "sub_weight_v" 2.282 + WHERE "issue_id" = "issue_id_p" 2.283 + AND "event" = 'periodic' 2.284 + AND "member_id" = "issue_delegation_row"."truster_id"; 2.285 + "weight_v" := "weight_v" + "sub_weight_v"; 2.286 END IF; 2.287 END LOOP; 2.288 RETURN "weight_v"; 2.289 @@ -1606,6 +1644,7 @@ 2.290 "issue_delegation_row" "issue_delegation"%ROWTYPE; 2.291 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE; 2.292 "weight_v" INT4; 2.293 + "sub_weight_v" INT4; 2.294 BEGIN 2.295 "weight_v" := 0; 2.296 FOR "issue_delegation_row" IN 2.297 @@ -1634,12 +1673,18 @@ 2.298 "issue_delegation_row"."truster_id", 2.299 "delegate_member_ids_v" 2.300 ); 2.301 - "weight_v" := "weight_v" + 1 + 2.302 + "sub_weight_v" := 1 + 2.303 "weight_of_added_delegations_for_interest_snapshot"( 2.304 "issue_id_p", 2.305 "issue_delegation_row"."truster_id", 2.306 "delegate_member_ids_v" 2.307 ); 2.308 + UPDATE "delegating_interest_snapshot" 2.309 + SET "weight" = "sub_weight_v" 2.310 + WHERE "issue_id" = "issue_id_p" 2.311 + AND "event" = 'periodic' 2.312 + AND "member_id" = "issue_delegation_row"."truster_id"; 2.313 + "weight_v" := "weight_v" + "sub_weight_v"; 2.314 END IF; 2.315 END LOOP; 2.316 RETURN "weight_v"; 2.317 @@ -1744,14 +1789,15 @@ 2.318 PERFORM "create_population_snapshot"("issue_id_p"); 2.319 PERFORM "create_interest_snapshot"("issue_id_p"); 2.320 UPDATE "issue" SET 2.321 - "snapshot" = now(), 2.322 + "snapshot" = now(), 2.323 + "latest_snapshot_event" = 'periodic', 2.324 "population" = ( 2.325 SELECT coalesce(sum("weight"), 0) 2.326 FROM "direct_population_snapshot" 2.327 WHERE "issue_id" = "issue_id_p" 2.328 AND "event" = 'periodic' 2.329 ), 2.330 - "vote_now" = ( 2.331 + "vote_now" = ( 2.332 SELECT coalesce(sum("weight"), 0) 2.333 FROM "direct_interest_snapshot" 2.334 WHERE "issue_id" = "issue_id_p" 2.335 @@ -1910,6 +1956,8 @@ 2.336 RETURNS VOID 2.337 LANGUAGE 'plpgsql' VOLATILE AS $$ 2.338 BEGIN 2.339 + UPDATE "issue" SET "latest_snapshot_event" = "event_p" 2.340 + WHERE "id" = "issue_id_p"; 2.341 UPDATE "direct_population_snapshot" SET "event" = "event_p" 2.342 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic'; 2.343 UPDATE "delegating_population_snapshot" SET "event" = "event_p" 2.344 @@ -2011,6 +2059,7 @@ 2.345 "issue_delegation_row" "issue_delegation"%ROWTYPE; 2.346 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE; 2.347 "weight_v" INT4; 2.348 + "sub_weight_v" INT4; 2.349 BEGIN 2.350 "weight_v" := 0; 2.351 FOR "issue_delegation_row" IN 2.352 @@ -2030,18 +2079,23 @@ 2.353 "delegate_member_ids_v" := 2.354 "member_id_p" || "delegate_member_ids_p"; 2.355 INSERT INTO "delegating_voter" 2.356 - ("member_id", "issue_id", "delegate_member_ids") 2.357 + ("issue_id", "member_id", "delegate_member_ids") 2.358 VALUES ( 2.359 - "issue_delegation_row"."truster_id", 2.360 - "issue_id_p", 2.361 - "delegate_member_ids_v" 2.362 - ); 2.363 - "weight_v" := "weight_v" + 2.364 - 1 + "weight_of_added_vote_delegations"( 2.365 "issue_id_p", 2.366 "issue_delegation_row"."truster_id", 2.367 "delegate_member_ids_v" 2.368 ); 2.369 + "sub_weight_v" := 1 + 2.370 + "weight_of_added_vote_delegations"( 2.371 + "issue_id_p", 2.372 + "issue_delegation_row"."truster_id", 2.373 + "delegate_member_ids_v" 2.374 + ); 2.375 + UPDATE "delegating_voter" 2.376 + SET "weight" = "sub_weight_v" 2.377 + WHERE "issue_id" = "issue_id_p" 2.378 + AND "member_id" = "issue_delegation_row"."truster_id"; 2.379 + "weight_v" := "weight_v" + "sub_weight_v"; 2.380 END IF; 2.381 END LOOP; 2.382 RETURN "weight_v"; 2.383 @@ -2549,4 +2603,28 @@ 2.384 2.385 2.386 2.387 +------------------------------ 2.388 +-- Deletion of private data -- 2.389 +------------------------------ 2.390 + 2.391 + 2.392 +CREATE FUNCTION "delete_private_data"() 2.393 + RETURNS VOID 2.394 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.395 + DECLARE 2.396 + "issue_id_v" "issue"."id"%TYPE; 2.397 + BEGIN 2.398 + DELETE FROM "session"; 2.399 + DELETE FROM "contact" WHERE NOT "public"; 2.400 + DELETE FROM "direct_voter" USING "issue" 2.401 + WHERE "direct_voter"."issue_id" = "issue"."id" 2.402 + AND "issue"."closed" ISNULL; 2.403 + RETURN; 2.404 + END; 2.405 + $$; 2.406 + 2.407 +COMMENT ON FUNCTION "delete_private_data"() IS 'DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.'; 2.408 + 2.409 + 2.410 + 2.411 COMMIT;
3.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 3.2 +++ b/lf_export Mon Nov 30 12:00:00 2009 +0100 3.3 @@ -0,0 +1,34 @@ 3.4 +#!/bin/sh 3.5 + 3.6 +if [ -z "$1" -o -z "$2" ]; then 3.7 + echo "Usage: $0 <dbname> <filename>.sql.gz" 3.8 + exit 1 3.9 +fi 3.10 + 3.11 +EXPORT_DBNAME=liquid_feedback_autoexport 3.12 +retval=0 3.13 + 3.14 +echo "Dropping database \"$EXPORT_DBNAME\" if existent..." 3.15 +dropdb "$EXPORT_DBNAME" 2> /dev/null 3.16 +echo "Copying database \"$1\" to new database \"$EXPORT_DBNAME\"..." 3.17 +if createdb -T "$1" "$EXPORT_DBNAME" 3.18 +then 3.19 + echo "Deleting private data in copied database..." 3.20 + if psql liquid_feedback -c 'SELECT delete_private_data()' > /dev/null 3.21 + then 3.22 + echo "Dumping and compressing copied database to \"$2\"..." 3.23 + if pg_dump --no-owner --no-privileges "$EXPORT_DBNAME" | gzip -9 > "$2" 3.24 + then 3.25 + else 3.26 + retval=4 3.27 + fi 3.28 + else 3.29 + retval=3 3.30 + fi 3.31 +else 3.32 + retval=2 3.33 +fi 3.34 +echo "Dropping database \"$EXPORT_DBNAME\"..." 3.35 +dropdb "$EXPORT_DBNAME" 3.36 +echo "DONE." 3.37 +exit $retval