jbe@59: BEGIN;
jbe@59: 
jbe@59: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@59:   SELECT * FROM (VALUES ('1.2.1', 1, 2, 1))
jbe@59:   AS "subquery"("string", "major", "minor", "revision");
jbe@59: 
jbe@59: ALTER TABLE "issue" ADD COLUMN "cleaned" TIMESTAMPTZ;
jbe@59: ALTER TABLE "issue" ADD CONSTRAINT "clean_restriction"
jbe@59:   CHECK (
jbe@59:     "cleaned" ISNULL OR (
jbe@59:       "closed" NOTNULL AND (
jbe@59:         "fully_frozen" ISNULL OR "ranks_available"
jbe@59:       )
jbe@59:     )
jbe@59:   );
jbe@59: 
jbe@59: COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
jbe@59: 
jbe@59: CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@59:   RETURNS VOID
jbe@59:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@59:     DECLARE
jbe@59:       "issue_row" "issue"%ROWTYPE;
jbe@59:     BEGIN
jbe@59:       SELECT * INTO "issue_row"
jbe@59:         FROM "issue" WHERE "id" = "issue_id_p"
jbe@59:         FOR UPDATE;
jbe@59:       IF "issue_row"."cleaned" ISNULL THEN
jbe@59:         UPDATE "issue" SET
jbe@59:           "closed" = NULL,
jbe@59:           "ranks_available" = FALSE
jbe@59:           WHERE "id" = "issue_id_p";
jbe@59:         DELETE FROM "delegating_voter"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "direct_voter"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "delegating_interest_snapshot"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "direct_interest_snapshot"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "delegating_population_snapshot"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "direct_population_snapshot"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "delegation"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "supporter"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         UPDATE "issue" SET
jbe@59:           "closed"          = "issue_row"."closed",
jbe@59:           "ranks_available" = "issue_row"."ranks_available",
jbe@59:           "cleaned"         = now()
jbe@59:           WHERE "id" = "issue_id_p";
jbe@59:       END IF;
jbe@59:       RETURN;
jbe@59:     END;
jbe@59:   $$;
jbe@59: 
jbe@59: COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
jbe@59: 
jbe@59: COMMIT;