liquid_feedback_core

changeset 262:e02b8c492e26

Added update/core-update.v2.0.11-v2.1.0.sql
author jbe
date Sun Jul 15 21:57:13 2012 +0200 (2012-07-15)
parents 6b2c80028b47
children 30465830ad9c
files update/core-update.v2.0.11-v2.1.0.sql
line diff
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v2.0.11-v2.1.0.sql	Sun Jul 15 21:57:13 2012 +0200
     1.3 @@ -0,0 +1,147 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('2.1.0', 2, 1, 0))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
    1.11 +ALTER TABLE "policy" ALTER COLUMN "admission_time"    DROP NOT NULL;
    1.12 +ALTER TABLE "policy" ALTER COLUMN "discussion_time"   DROP NOT NULL;
    1.13 +ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL;
    1.14 +ALTER TABLE "policy" ALTER COLUMN "voting_time"       DROP NOT NULL;
    1.15 +ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
    1.16 +          ( "polling" = FALSE AND
    1.17 +            "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
    1.18 +            "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
    1.19 +          ( "polling" = TRUE AND
    1.20 +            "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
    1.21 +            "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
    1.22 +          ( "polling" = TRUE AND
    1.23 +            "admission_time" ISNULL AND "discussion_time" ISNULL AND
    1.24 +            "verification_time" ISNULL AND "voting_time" ISNULL ) );
    1.25 +COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues, i.e. polls (time values may be set to NULL, allowing individual timing for issues)';
    1.26 +
    1.27 +ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
    1.28 +COMMENT ON COLUMN "initiative"."polling" IS 'Initiative is an option for a poll (see "policy"."polling"), and does not need to pass the initiative quorum';
    1.29 +
    1.30 +ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager";
    1.31 +ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE;
    1.32 +ALTER TABLE "privilege" ADD COLUMN "polling_right"    BOOLEAN NOT NULL DEFAULT FALSE;
    1.33 +UPDATE "privilege" SET "initiative_right" = "voting_right";
    1.34 +COMMENT ON COLUMN "privilege"."admin_manager"    IS 'Grant/revoke any privileges to/from other members';
    1.35 +COMMENT ON COLUMN "privilege"."member_manager"   IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
    1.36 +COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
    1.37 +COMMENT ON COLUMN "privilege"."voting_right"     IS 'Right to support initiatives, create and rate suggestions, and to vote';
    1.38 +COMMENT ON COLUMN "privilege"."polling_right"    IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';
    1.39 +
    1.40 +DROP TABLE "rendered_issue_comment";
    1.41 +DROP TABLE "issue_comment";
    1.42 +
    1.43 +CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
    1.44 +  ( "issue_id_p" "issue"."id"%TYPE )
    1.45 +  RETURNS VOID
    1.46 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.47 +    DECLARE
    1.48 +      "issue_row"      "issue"%ROWTYPE;
    1.49 +      "policy_row"     "policy"%ROWTYPE;
    1.50 +      "initiative_row" "initiative"%ROWTYPE;
    1.51 +    BEGIN
    1.52 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
    1.53 +      SELECT * INTO "policy_row"
    1.54 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
    1.55 +      PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
    1.56 +      FOR "initiative_row" IN
    1.57 +        SELECT * FROM "initiative"
    1.58 +        WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
    1.59 +      LOOP
    1.60 +        IF
    1.61 +          "initiative_row"."polling" OR (
    1.62 +            "initiative_row"."satisfied_supporter_count" > 0 AND
    1.63 +            "initiative_row"."satisfied_supporter_count" *
    1.64 +            "policy_row"."initiative_quorum_den" >=
    1.65 +            "issue_row"."population" * "policy_row"."initiative_quorum_num"
    1.66 +          )
    1.67 +        THEN
    1.68 +          UPDATE "initiative" SET "admitted" = TRUE
    1.69 +            WHERE "id" = "initiative_row"."id";
    1.70 +        ELSE
    1.71 +          UPDATE "initiative" SET "admitted" = FALSE
    1.72 +            WHERE "id" = "initiative_row"."id";
    1.73 +        END IF;
    1.74 +      END LOOP;
    1.75 +      IF EXISTS (
    1.76 +        SELECT NULL FROM "initiative"
    1.77 +        WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
    1.78 +      ) THEN
    1.79 +        UPDATE "issue" SET
    1.80 +          "state"        = 'voting',
    1.81 +          "accepted"     = coalesce("accepted", now()),
    1.82 +          "half_frozen"  = coalesce("half_frozen", now()),
    1.83 +          "fully_frozen" = now()
    1.84 +          WHERE "id" = "issue_id_p";
    1.85 +      ELSE
    1.86 +        UPDATE "issue" SET
    1.87 +          "state"           = 'canceled_no_initiative_admitted',
    1.88 +          "accepted"        = coalesce("accepted", now()),
    1.89 +          "half_frozen"     = coalesce("half_frozen", now()),
    1.90 +          "fully_frozen"    = now(),
    1.91 +          "closed"          = now(),
    1.92 +          "ranks_available" = TRUE
    1.93 +          WHERE "id" = "issue_id_p";
    1.94 +        -- NOTE: The following DELETE statements have effect only when
    1.95 +        --       issue state has been manipulated
    1.96 +        DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
    1.97 +        DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
    1.98 +        DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
    1.99 +      END IF;
   1.100 +      RETURN;
   1.101 +    END;
   1.102 +  $$;
   1.103 +
   1.104 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
   1.105 +  RETURNS VOID
   1.106 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.107 +    DECLARE
   1.108 +      "issue_row" "issue"%ROWTYPE;
   1.109 +    BEGIN
   1.110 +      SELECT * INTO "issue_row"
   1.111 +        FROM "issue" WHERE "id" = "issue_id_p"
   1.112 +        FOR UPDATE;
   1.113 +      IF "issue_row"."cleaned" ISNULL THEN
   1.114 +        UPDATE "issue" SET
   1.115 +          "state"           = 'voting',
   1.116 +          "closed"          = NULL,
   1.117 +          "ranks_available" = FALSE
   1.118 +          WHERE "id" = "issue_id_p";
   1.119 +        DELETE FROM "voting_comment"
   1.120 +          WHERE "issue_id" = "issue_id_p";
   1.121 +        DELETE FROM "delegating_voter"
   1.122 +          WHERE "issue_id" = "issue_id_p";
   1.123 +        DELETE FROM "direct_voter"
   1.124 +          WHERE "issue_id" = "issue_id_p";
   1.125 +        DELETE FROM "delegating_interest_snapshot"
   1.126 +          WHERE "issue_id" = "issue_id_p";
   1.127 +        DELETE FROM "direct_interest_snapshot"
   1.128 +          WHERE "issue_id" = "issue_id_p";
   1.129 +        DELETE FROM "delegating_population_snapshot"
   1.130 +          WHERE "issue_id" = "issue_id_p";
   1.131 +        DELETE FROM "direct_population_snapshot"
   1.132 +          WHERE "issue_id" = "issue_id_p";
   1.133 +        DELETE FROM "non_voter"
   1.134 +          WHERE "issue_id" = "issue_id_p";
   1.135 +        DELETE FROM "delegation"
   1.136 +          WHERE "issue_id" = "issue_id_p";
   1.137 +        DELETE FROM "supporter"
   1.138 +          WHERE "issue_id" = "issue_id_p";
   1.139 +        UPDATE "issue" SET
   1.140 +          "state"           = "issue_row"."state",
   1.141 +          "closed"          = "issue_row"."closed",
   1.142 +          "ranks_available" = "issue_row"."ranks_available",
   1.143 +          "cleaned"         = now()
   1.144 +          WHERE "id" = "issue_id_p";
   1.145 +      END IF;
   1.146 +      RETURN;
   1.147 +    END;
   1.148 +  $$;
   1.149 +
   1.150 +COMMIT;

Impressum / About Us