liquid_feedback_core
changeset 59:fdd1729d7e2a v1.2.1
New function clean_issue(issue.id), which deletes all discussion data and votes of an issue
author | jbe |
---|---|
date | Mon Jul 19 22:52:01 2010 +0200 (2010-07-19) |
parents | 1d39bf61365d |
children | e83ff2e2e051 |
files | core.sql update/core-update.v1.2.0-v1.2.1.sql |
line diff
1.1 --- a/core.sql Wed Jul 14 13:30:09 2010 +0200 1.2 +++ b/core.sql Mon Jul 19 22:52:01 2010 +0200 1.3 @@ -6,7 +6,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('1.2.0', 1, 2, 0)) 1.8 + SELECT * FROM (VALUES ('1.2.1', 1, 2, 1)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -341,6 +341,7 @@ 1.13 "fully_frozen" TIMESTAMPTZ, 1.14 "closed" TIMESTAMPTZ, 1.15 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE, 1.16 + "cleaned" TIMESTAMPTZ, 1.17 "admission_time" INTERVAL NOT NULL, 1.18 "discussion_time" INTERVAL NOT NULL, 1.19 "verification_time" INTERVAL NOT NULL, 1.20 @@ -366,6 +367,12 @@ 1.21 "accepted" <= "half_frozen" AND 1.22 "half_frozen" <= "fully_frozen" AND 1.23 "fully_frozen" <= "closed" ), 1.24 + CONSTRAINT "clean_restriction" CHECK ( 1.25 + "cleaned" ISNULL OR ( 1.26 + "closed" NOTNULL AND ( 1.27 + "fully_frozen" ISNULL OR "ranks_available" 1.28 + ) 1.29 + ) ), 1.30 CONSTRAINT "last_snapshot_on_full_freeze" 1.31 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet 1.32 CONSTRAINT "freeze_requires_snapshot" 1.33 @@ -389,6 +396,7 @@ 1.34 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.35 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.36 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 1.37 +COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted'; 1.38 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue'; 1.39 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue'; 1.40 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue'; 1.41 @@ -3169,9 +3177,52 @@ 1.42 1.43 1.44 1.45 ------------------------------- 1.46 --- Deletion of private data -- 1.47 ------------------------------- 1.48 +---------------------- 1.49 +-- Deletion of data -- 1.50 +---------------------- 1.51 + 1.52 + 1.53 +CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 1.54 + RETURNS VOID 1.55 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.56 + DECLARE 1.57 + "issue_row" "issue"%ROWTYPE; 1.58 + BEGIN 1.59 + SELECT * INTO "issue_row" 1.60 + FROM "issue" WHERE "id" = "issue_id_p" 1.61 + FOR UPDATE; 1.62 + IF "issue_row"."cleaned" ISNULL THEN 1.63 + UPDATE "issue" SET 1.64 + "closed" = NULL, 1.65 + "ranks_available" = FALSE 1.66 + WHERE "id" = "issue_id_p"; 1.67 + DELETE FROM "delegating_voter" 1.68 + WHERE "issue_id" = "issue_id_p"; 1.69 + DELETE FROM "direct_voter" 1.70 + WHERE "issue_id" = "issue_id_p"; 1.71 + DELETE FROM "delegating_interest_snapshot" 1.72 + WHERE "issue_id" = "issue_id_p"; 1.73 + DELETE FROM "direct_interest_snapshot" 1.74 + WHERE "issue_id" = "issue_id_p"; 1.75 + DELETE FROM "delegating_population_snapshot" 1.76 + WHERE "issue_id" = "issue_id_p"; 1.77 + DELETE FROM "direct_population_snapshot" 1.78 + WHERE "issue_id" = "issue_id_p"; 1.79 + DELETE FROM "delegation" 1.80 + WHERE "issue_id" = "issue_id_p"; 1.81 + DELETE FROM "supporter" 1.82 + WHERE "issue_id" = "issue_id_p"; 1.83 + UPDATE "issue" SET 1.84 + "closed" = "issue_row"."closed", 1.85 + "ranks_available" = "issue_row"."ranks_available", 1.86 + "cleaned" = now() 1.87 + WHERE "id" = "issue_id_p"; 1.88 + END IF; 1.89 + RETURN; 1.90 + END; 1.91 + $$; 1.92 + 1.93 +COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue'; 1.94 1.95 1.96 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v1.2.0-v1.2.1.sql Mon Jul 19 22:52:01 2010 +0200 2.3 @@ -0,0 +1,61 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('1.2.1', 1, 2, 1)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +ALTER TABLE "issue" ADD COLUMN "cleaned" TIMESTAMPTZ; 2.11 +ALTER TABLE "issue" ADD CONSTRAINT "clean_restriction" 2.12 + CHECK ( 2.13 + "cleaned" ISNULL OR ( 2.14 + "closed" NOTNULL AND ( 2.15 + "fully_frozen" ISNULL OR "ranks_available" 2.16 + ) 2.17 + ) 2.18 + ); 2.19 + 2.20 +COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted'; 2.21 + 2.22 +CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 2.23 + RETURNS VOID 2.24 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.25 + DECLARE 2.26 + "issue_row" "issue"%ROWTYPE; 2.27 + BEGIN 2.28 + SELECT * INTO "issue_row" 2.29 + FROM "issue" WHERE "id" = "issue_id_p" 2.30 + FOR UPDATE; 2.31 + IF "issue_row"."cleaned" ISNULL THEN 2.32 + UPDATE "issue" SET 2.33 + "closed" = NULL, 2.34 + "ranks_available" = FALSE 2.35 + WHERE "id" = "issue_id_p"; 2.36 + DELETE FROM "delegating_voter" 2.37 + WHERE "issue_id" = "issue_id_p"; 2.38 + DELETE FROM "direct_voter" 2.39 + WHERE "issue_id" = "issue_id_p"; 2.40 + DELETE FROM "delegating_interest_snapshot" 2.41 + WHERE "issue_id" = "issue_id_p"; 2.42 + DELETE FROM "direct_interest_snapshot" 2.43 + WHERE "issue_id" = "issue_id_p"; 2.44 + DELETE FROM "delegating_population_snapshot" 2.45 + WHERE "issue_id" = "issue_id_p"; 2.46 + DELETE FROM "direct_population_snapshot" 2.47 + WHERE "issue_id" = "issue_id_p"; 2.48 + DELETE FROM "delegation" 2.49 + WHERE "issue_id" = "issue_id_p"; 2.50 + DELETE FROM "supporter" 2.51 + WHERE "issue_id" = "issue_id_p"; 2.52 + UPDATE "issue" SET 2.53 + "closed" = "issue_row"."closed", 2.54 + "ranks_available" = "issue_row"."ranks_available", 2.55 + "cleaned" = now() 2.56 + WHERE "id" = "issue_id_p"; 2.57 + END IF; 2.58 + RETURN; 2.59 + END; 2.60 + $$; 2.61 + 2.62 +COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue'; 2.63 + 2.64 +COMMIT;