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;

Impressum / About Us