liquid_feedback_core

annotate update/core-update.v2.0.11-v2.1.0.sql @ 267:5bf2c973ec3c

find changesets by author, revision, files, or words in the commit message
Work on OAuth 2.0 authorization support
author jbe
date Wed Aug 08 15:20:34 2012 +0200 (2012-08-08)
parents 30465830ad9c
children a00b58b7a510
rev   line source
jbe@262 1 BEGIN;
jbe@262 2
jbe@262 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@262 4 SELECT * FROM (VALUES ('2.1.0', 2, 1, 0))
jbe@262 5 AS "subquery"("string", "major", "minor", "revision");
jbe@262 6
jbe@262 7 ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@262 8 ALTER TABLE "policy" ALTER COLUMN "admission_time" DROP NOT NULL;
jbe@262 9 ALTER TABLE "policy" ALTER COLUMN "discussion_time" DROP NOT NULL;
jbe@262 10 ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL;
jbe@262 11 ALTER TABLE "policy" ALTER COLUMN "voting_time" DROP NOT NULL;
jbe@262 12 ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
jbe@262 13 ( "polling" = FALSE AND
jbe@262 14 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
jbe@262 15 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
jbe@262 16 ( "polling" = TRUE AND
jbe@263 17 "admission_time" ISNULL AND "discussion_time" NOTNULL AND
jbe@262 18 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
jbe@262 19 ( "polling" = TRUE AND
jbe@262 20 "admission_time" ISNULL AND "discussion_time" ISNULL AND
jbe@262 21 "verification_time" ISNULL AND "voting_time" ISNULL ) );
jbe@263 22 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues, i.e. polls ("admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for issues)';
jbe@262 23
jbe@262 24 ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@262 25 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';
jbe@262 26
jbe@262 27 ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager";
jbe@262 28 ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE;
jbe@262 29 ALTER TABLE "privilege" ADD COLUMN "polling_right" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@262 30 UPDATE "privilege" SET "initiative_right" = "voting_right";
jbe@262 31 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
jbe@262 32 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
jbe@262 33 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
jbe@262 34 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
jbe@262 35 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';
jbe@262 36
jbe@262 37 DROP TABLE "rendered_issue_comment";
jbe@262 38 DROP TABLE "issue_comment";
jbe@262 39
jbe@262 40 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
jbe@262 41 ( "issue_id_p" "issue"."id"%TYPE )
jbe@262 42 RETURNS VOID
jbe@262 43 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262 44 DECLARE
jbe@262 45 "issue_row" "issue"%ROWTYPE;
jbe@262 46 "policy_row" "policy"%ROWTYPE;
jbe@262 47 "initiative_row" "initiative"%ROWTYPE;
jbe@262 48 BEGIN
jbe@262 49 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@262 50 SELECT * INTO "policy_row"
jbe@262 51 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@262 52 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@262 53 FOR "initiative_row" IN
jbe@262 54 SELECT * FROM "initiative"
jbe@262 55 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@262 56 LOOP
jbe@262 57 IF
jbe@262 58 "initiative_row"."polling" OR (
jbe@262 59 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@262 60 "initiative_row"."satisfied_supporter_count" *
jbe@262 61 "policy_row"."initiative_quorum_den" >=
jbe@262 62 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@262 63 )
jbe@262 64 THEN
jbe@262 65 UPDATE "initiative" SET "admitted" = TRUE
jbe@262 66 WHERE "id" = "initiative_row"."id";
jbe@262 67 ELSE
jbe@262 68 UPDATE "initiative" SET "admitted" = FALSE
jbe@262 69 WHERE "id" = "initiative_row"."id";
jbe@262 70 END IF;
jbe@262 71 END LOOP;
jbe@262 72 IF EXISTS (
jbe@262 73 SELECT NULL FROM "initiative"
jbe@262 74 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@262 75 ) THEN
jbe@262 76 UPDATE "issue" SET
jbe@262 77 "state" = 'voting',
jbe@262 78 "accepted" = coalesce("accepted", now()),
jbe@262 79 "half_frozen" = coalesce("half_frozen", now()),
jbe@262 80 "fully_frozen" = now()
jbe@262 81 WHERE "id" = "issue_id_p";
jbe@262 82 ELSE
jbe@262 83 UPDATE "issue" SET
jbe@262 84 "state" = 'canceled_no_initiative_admitted',
jbe@262 85 "accepted" = coalesce("accepted", now()),
jbe@262 86 "half_frozen" = coalesce("half_frozen", now()),
jbe@262 87 "fully_frozen" = now(),
jbe@262 88 "closed" = now(),
jbe@262 89 "ranks_available" = TRUE
jbe@262 90 WHERE "id" = "issue_id_p";
jbe@262 91 -- NOTE: The following DELETE statements have effect only when
jbe@262 92 -- issue state has been manipulated
jbe@262 93 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@262 94 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@262 95 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@262 96 END IF;
jbe@262 97 RETURN;
jbe@262 98 END;
jbe@262 99 $$;
jbe@262 100
jbe@262 101 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@262 102 RETURNS VOID
jbe@262 103 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262 104 DECLARE
jbe@262 105 "issue_row" "issue"%ROWTYPE;
jbe@262 106 BEGIN
jbe@262 107 SELECT * INTO "issue_row"
jbe@262 108 FROM "issue" WHERE "id" = "issue_id_p"
jbe@262 109 FOR UPDATE;
jbe@262 110 IF "issue_row"."cleaned" ISNULL THEN
jbe@262 111 UPDATE "issue" SET
jbe@262 112 "state" = 'voting',
jbe@262 113 "closed" = NULL,
jbe@262 114 "ranks_available" = FALSE
jbe@262 115 WHERE "id" = "issue_id_p";
jbe@262 116 DELETE FROM "voting_comment"
jbe@262 117 WHERE "issue_id" = "issue_id_p";
jbe@262 118 DELETE FROM "delegating_voter"
jbe@262 119 WHERE "issue_id" = "issue_id_p";
jbe@262 120 DELETE FROM "direct_voter"
jbe@262 121 WHERE "issue_id" = "issue_id_p";
jbe@262 122 DELETE FROM "delegating_interest_snapshot"
jbe@262 123 WHERE "issue_id" = "issue_id_p";
jbe@262 124 DELETE FROM "direct_interest_snapshot"
jbe@262 125 WHERE "issue_id" = "issue_id_p";
jbe@262 126 DELETE FROM "delegating_population_snapshot"
jbe@262 127 WHERE "issue_id" = "issue_id_p";
jbe@262 128 DELETE FROM "direct_population_snapshot"
jbe@262 129 WHERE "issue_id" = "issue_id_p";
jbe@262 130 DELETE FROM "non_voter"
jbe@262 131 WHERE "issue_id" = "issue_id_p";
jbe@262 132 DELETE FROM "delegation"
jbe@262 133 WHERE "issue_id" = "issue_id_p";
jbe@262 134 DELETE FROM "supporter"
jbe@262 135 WHERE "issue_id" = "issue_id_p";
jbe@262 136 UPDATE "issue" SET
jbe@262 137 "state" = "issue_row"."state",
jbe@262 138 "closed" = "issue_row"."closed",
jbe@262 139 "ranks_available" = "issue_row"."ranks_available",
jbe@262 140 "cleaned" = now()
jbe@262 141 WHERE "id" = "issue_id_p";
jbe@262 142 END IF;
jbe@262 143 RETURN;
jbe@262 144 END;
jbe@262 145 $$;
jbe@262 146
jbe@262 147 COMMIT;

Impressum / About Us