| rev |
line source |
|
jbe@59
|
1 BEGIN;
|
|
jbe@59
|
2
|
|
jbe@59
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@59
|
4 SELECT * FROM (VALUES ('1.2.1', 1, 2, 1))
|
|
jbe@59
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@59
|
6
|
|
jbe@59
|
7 ALTER TABLE "issue" ADD COLUMN "cleaned" TIMESTAMPTZ;
|
|
jbe@59
|
8 ALTER TABLE "issue" ADD CONSTRAINT "clean_restriction"
|
|
jbe@59
|
9 CHECK (
|
|
jbe@59
|
10 "cleaned" ISNULL OR (
|
|
jbe@59
|
11 "closed" NOTNULL AND (
|
|
jbe@59
|
12 "fully_frozen" ISNULL OR "ranks_available"
|
|
jbe@59
|
13 )
|
|
jbe@59
|
14 )
|
|
jbe@59
|
15 );
|
|
jbe@59
|
16
|
|
jbe@59
|
17 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
|
|
jbe@59
|
18
|
|
jbe@59
|
19 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
|
|
jbe@59
|
20 RETURNS VOID
|
|
jbe@59
|
21 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@59
|
22 DECLARE
|
|
jbe@59
|
23 "issue_row" "issue"%ROWTYPE;
|
|
jbe@59
|
24 BEGIN
|
|
jbe@59
|
25 SELECT * INTO "issue_row"
|
|
jbe@59
|
26 FROM "issue" WHERE "id" = "issue_id_p"
|
|
jbe@59
|
27 FOR UPDATE;
|
|
jbe@59
|
28 IF "issue_row"."cleaned" ISNULL THEN
|
|
jbe@59
|
29 UPDATE "issue" SET
|
|
jbe@59
|
30 "closed" = NULL,
|
|
jbe@59
|
31 "ranks_available" = FALSE
|
|
jbe@59
|
32 WHERE "id" = "issue_id_p";
|
|
jbe@59
|
33 DELETE FROM "delegating_voter"
|
|
jbe@59
|
34 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
35 DELETE FROM "direct_voter"
|
|
jbe@59
|
36 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
37 DELETE FROM "delegating_interest_snapshot"
|
|
jbe@59
|
38 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
39 DELETE FROM "direct_interest_snapshot"
|
|
jbe@59
|
40 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
41 DELETE FROM "delegating_population_snapshot"
|
|
jbe@59
|
42 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
43 DELETE FROM "direct_population_snapshot"
|
|
jbe@59
|
44 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
45 DELETE FROM "delegation"
|
|
jbe@59
|
46 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
47 DELETE FROM "supporter"
|
|
jbe@59
|
48 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
49 UPDATE "issue" SET
|
|
jbe@59
|
50 "closed" = "issue_row"."closed",
|
|
jbe@59
|
51 "ranks_available" = "issue_row"."ranks_available",
|
|
jbe@59
|
52 "cleaned" = now()
|
|
jbe@59
|
53 WHERE "id" = "issue_id_p";
|
|
jbe@59
|
54 END IF;
|
|
jbe@59
|
55 RETURN;
|
|
jbe@59
|
56 END;
|
|
jbe@59
|
57 $$;
|
|
jbe@59
|
58
|
|
jbe@59
|
59 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
|
|
jbe@59
|
60
|
|
jbe@59
|
61 COMMIT;
|