liquid_feedback_core

annotate update/core-update.v2.0.11-v2.1.0.sql @ 284:4f935e989ff6

find changesets by author, revision, files, or words in the commit message
"non_voter" deletes "direct_voter" and vice versa
author jbe
date Sun Aug 19 19:04:22 2012 +0200 (2012-08-19)
parents a00b58b7a510
children 4868a7d591de
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@284 40 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
jbe@284 41 RETURNS TRIGGER
jbe@284 42 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@284 43 BEGIN
jbe@284 44 DELETE FROM "direct_voter"
jbe@284 45 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
jbe@284 46 RETURN NULL;
jbe@284 47 END;
jbe@284 48 $$;
jbe@284 49
jbe@284 50 CREATE TRIGGER "non_voter_deletes_direct_voter"
jbe@284 51 AFTER INSERT OR UPDATE ON "non_voter"
jbe@284 52 FOR EACH ROW EXECUTE PROCEDURE
jbe@284 53 "non_voter_deletes_direct_voter_trigger"();
jbe@284 54
jbe@284 55 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
jbe@284 56 COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")';
jbe@284 57
jbe@284 58 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
jbe@284 59 RETURNS TRIGGER
jbe@284 60 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@284 61 BEGIN
jbe@284 62 DELETE FROM "non_voter"
jbe@284 63 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
jbe@284 64 RETURN NULL;
jbe@284 65 END;
jbe@284 66 $$;
jbe@284 67
jbe@284 68 CREATE TRIGGER "direct_voter_deletes_non_voter"
jbe@284 69 AFTER INSERT OR UPDATE ON "direct_voter"
jbe@284 70 FOR EACH ROW EXECUTE PROCEDURE
jbe@284 71 "direct_voter_deletes_non_voter_trigger"();
jbe@284 72
jbe@284 73 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
jbe@284 74 COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';
jbe@284 75
jbe@262 76 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
jbe@262 77 ( "issue_id_p" "issue"."id"%TYPE )
jbe@262 78 RETURNS VOID
jbe@262 79 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262 80 DECLARE
jbe@262 81 "issue_row" "issue"%ROWTYPE;
jbe@262 82 "policy_row" "policy"%ROWTYPE;
jbe@262 83 "initiative_row" "initiative"%ROWTYPE;
jbe@262 84 BEGIN
jbe@262 85 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@262 86 SELECT * INTO "policy_row"
jbe@262 87 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@262 88 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@262 89 FOR "initiative_row" IN
jbe@262 90 SELECT * FROM "initiative"
jbe@262 91 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@262 92 LOOP
jbe@262 93 IF
jbe@262 94 "initiative_row"."polling" OR (
jbe@262 95 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@262 96 "initiative_row"."satisfied_supporter_count" *
jbe@262 97 "policy_row"."initiative_quorum_den" >=
jbe@262 98 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@262 99 )
jbe@262 100 THEN
jbe@262 101 UPDATE "initiative" SET "admitted" = TRUE
jbe@262 102 WHERE "id" = "initiative_row"."id";
jbe@262 103 ELSE
jbe@262 104 UPDATE "initiative" SET "admitted" = FALSE
jbe@262 105 WHERE "id" = "initiative_row"."id";
jbe@262 106 END IF;
jbe@262 107 END LOOP;
jbe@262 108 IF EXISTS (
jbe@262 109 SELECT NULL FROM "initiative"
jbe@262 110 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@262 111 ) THEN
jbe@262 112 UPDATE "issue" SET
jbe@262 113 "state" = 'voting',
jbe@262 114 "accepted" = coalesce("accepted", now()),
jbe@262 115 "half_frozen" = coalesce("half_frozen", now()),
jbe@262 116 "fully_frozen" = now()
jbe@262 117 WHERE "id" = "issue_id_p";
jbe@262 118 ELSE
jbe@262 119 UPDATE "issue" SET
jbe@262 120 "state" = 'canceled_no_initiative_admitted',
jbe@262 121 "accepted" = coalesce("accepted", now()),
jbe@262 122 "half_frozen" = coalesce("half_frozen", now()),
jbe@262 123 "fully_frozen" = now(),
jbe@262 124 "closed" = now(),
jbe@262 125 "ranks_available" = TRUE
jbe@262 126 WHERE "id" = "issue_id_p";
jbe@262 127 -- NOTE: The following DELETE statements have effect only when
jbe@262 128 -- issue state has been manipulated
jbe@262 129 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@262 130 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@262 131 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@262 132 END IF;
jbe@262 133 RETURN;
jbe@262 134 END;
jbe@262 135 $$;
jbe@262 136
jbe@262 137 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@262 138 RETURNS VOID
jbe@262 139 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262 140 DECLARE
jbe@262 141 "issue_row" "issue"%ROWTYPE;
jbe@262 142 BEGIN
jbe@262 143 SELECT * INTO "issue_row"
jbe@262 144 FROM "issue" WHERE "id" = "issue_id_p"
jbe@262 145 FOR UPDATE;
jbe@262 146 IF "issue_row"."cleaned" ISNULL THEN
jbe@262 147 UPDATE "issue" SET
jbe@262 148 "state" = 'voting',
jbe@262 149 "closed" = NULL,
jbe@262 150 "ranks_available" = FALSE
jbe@262 151 WHERE "id" = "issue_id_p";
jbe@262 152 DELETE FROM "voting_comment"
jbe@262 153 WHERE "issue_id" = "issue_id_p";
jbe@262 154 DELETE FROM "delegating_voter"
jbe@262 155 WHERE "issue_id" = "issue_id_p";
jbe@262 156 DELETE FROM "direct_voter"
jbe@262 157 WHERE "issue_id" = "issue_id_p";
jbe@262 158 DELETE FROM "delegating_interest_snapshot"
jbe@262 159 WHERE "issue_id" = "issue_id_p";
jbe@262 160 DELETE FROM "direct_interest_snapshot"
jbe@262 161 WHERE "issue_id" = "issue_id_p";
jbe@262 162 DELETE FROM "delegating_population_snapshot"
jbe@262 163 WHERE "issue_id" = "issue_id_p";
jbe@262 164 DELETE FROM "direct_population_snapshot"
jbe@262 165 WHERE "issue_id" = "issue_id_p";
jbe@262 166 DELETE FROM "non_voter"
jbe@262 167 WHERE "issue_id" = "issue_id_p";
jbe@262 168 DELETE FROM "delegation"
jbe@262 169 WHERE "issue_id" = "issue_id_p";
jbe@262 170 DELETE FROM "supporter"
jbe@262 171 WHERE "issue_id" = "issue_id_p";
jbe@262 172 UPDATE "issue" SET
jbe@262 173 "state" = "issue_row"."state",
jbe@262 174 "closed" = "issue_row"."closed",
jbe@262 175 "ranks_available" = "issue_row"."ranks_available",
jbe@262 176 "cleaned" = now()
jbe@262 177 WHERE "id" = "issue_id_p";
jbe@262 178 END IF;
jbe@262 179 RETURN;
jbe@262 180 END;
jbe@262 181 $$;
jbe@262 182
jbe@283 183 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';
jbe@283 184
jbe@262 185 COMMIT;

Impressum / About Us