Anonymous avatar Anonymous committed fdd1729

New function clean_issue(issue.id), which deletes all discussion data and votes of an issue

Comments (0)

Files changed (2)

 BEGIN;
 
 CREATE VIEW "liquid_feedback_version" AS
-  SELECT * FROM (VALUES ('1.2.0', 1, 2, 0))
+  SELECT * FROM (VALUES ('1.2.1', 1, 2, 1))
   AS "subquery"("string", "major", "minor", "revision");
 
 
         "fully_frozen"          TIMESTAMPTZ,
         "closed"                TIMESTAMPTZ,
         "ranks_available"       BOOLEAN         NOT NULL DEFAULT FALSE,
+        "cleaned"               TIMESTAMPTZ,
         "admission_time"        INTERVAL        NOT NULL,
         "discussion_time"       INTERVAL        NOT NULL,
         "verification_time"     INTERVAL        NOT NULL,
           "accepted"     <= "half_frozen" AND
           "half_frozen"  <= "fully_frozen" AND
           "fully_frozen" <= "closed" ),
+        CONSTRAINT "clean_restriction" CHECK (
+          "cleaned" ISNULL OR (
+            "closed" NOTNULL AND (
+              "fully_frozen" ISNULL OR "ranks_available"
+            )
+          ) ),
         CONSTRAINT "last_snapshot_on_full_freeze"
           CHECK ("snapshot" = "fully_frozen"),  -- NOTE: snapshot can be set, while frozen is NULL yet
         CONSTRAINT "freeze_requires_snapshot"
 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.';
 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.';
 COMMENT ON COLUMN "issue"."ranks_available"       IS 'TRUE = ranks have been calculated';
+COMMENT ON COLUMN "issue"."cleaned"               IS 'Point in time, when discussion data and votes had been deleted';
 COMMENT ON COLUMN "issue"."admission_time"        IS 'Copied from "policy" table at creation of issue';
 COMMENT ON COLUMN "issue"."discussion_time"       IS 'Copied from "policy" table at creation of issue';
 COMMENT ON COLUMN "issue"."verification_time"     IS 'Copied from "policy" table at creation of issue';
 
 
 
-------------------------------
--- Deletion of private data --
-------------------------------
+----------------------
+-- Deletion of data --
+----------------------
+
+
+CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
+  RETURNS VOID
+  LANGUAGE 'plpgsql' VOLATILE AS $$
+    DECLARE
+      "issue_row" "issue"%ROWTYPE;
+    BEGIN
+      SELECT * INTO "issue_row"
+        FROM "issue" WHERE "id" = "issue_id_p"
+        FOR UPDATE;
+      IF "issue_row"."cleaned" ISNULL THEN
+        UPDATE "issue" SET
+          "closed" = NULL,
+          "ranks_available" = FALSE
+          WHERE "id" = "issue_id_p";
+        DELETE FROM "delegating_voter"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "direct_voter"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "delegating_interest_snapshot"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "direct_interest_snapshot"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "delegating_population_snapshot"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "direct_population_snapshot"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "delegation"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "supporter"
+          WHERE "issue_id" = "issue_id_p";
+        UPDATE "issue" SET
+          "closed"          = "issue_row"."closed",
+          "ranks_available" = "issue_row"."ranks_available",
+          "cleaned"         = now()
+          WHERE "id" = "issue_id_p";
+      END IF;
+      RETURN;
+    END;
+  $$;
+
+COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
 
 
 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

update/core-update.v1.2.0-v1.2.1.sql

+BEGIN;
+
+CREATE OR REPLACE VIEW "liquid_feedback_version" AS
+  SELECT * FROM (VALUES ('1.2.1', 1, 2, 1))
+  AS "subquery"("string", "major", "minor", "revision");
+
+ALTER TABLE "issue" ADD COLUMN "cleaned" TIMESTAMPTZ;
+ALTER TABLE "issue" ADD CONSTRAINT "clean_restriction"
+  CHECK (
+    "cleaned" ISNULL OR (
+      "closed" NOTNULL AND (
+        "fully_frozen" ISNULL OR "ranks_available"
+      )
+    )
+  );
+
+COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
+
+CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
+  RETURNS VOID
+  LANGUAGE 'plpgsql' VOLATILE AS $$
+    DECLARE
+      "issue_row" "issue"%ROWTYPE;
+    BEGIN
+      SELECT * INTO "issue_row"
+        FROM "issue" WHERE "id" = "issue_id_p"
+        FOR UPDATE;
+      IF "issue_row"."cleaned" ISNULL THEN
+        UPDATE "issue" SET
+          "closed" = NULL,
+          "ranks_available" = FALSE
+          WHERE "id" = "issue_id_p";
+        DELETE FROM "delegating_voter"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "direct_voter"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "delegating_interest_snapshot"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "direct_interest_snapshot"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "delegating_population_snapshot"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "direct_population_snapshot"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "delegation"
+          WHERE "issue_id" = "issue_id_p";
+        DELETE FROM "supporter"
+          WHERE "issue_id" = "issue_id_p";
+        UPDATE "issue" SET
+          "closed"          = "issue_row"."closed",
+          "ranks_available" = "issue_row"."ranks_available",
+          "cleaned"         = now()
+          WHERE "id" = "issue_id_p";
+      END IF;
+      RETURN;
+    END;
+  $$;
+
+COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
+
+COMMIT;
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.