liquid_feedback_core

annotate update/core-update.v2.0.11-v2.1.0.sql @ 285:4868a7d591de

find changesets by author, revision, files, or words in the commit message
Moved voting comment to table "direct_voter"
author jbe
date Sun Aug 19 20:29:44 2012 +0200 (2012-08-19)
parents 4f935e989ff6
children 3ac6d4259387
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@285 37 ALTER TABLE "direct_voter" ADD COLUMN "comment_changed" TIMESTAMPTZ;
jbe@285 38 ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT;
jbe@285 39 ALTER TABLE "direct_voter" ADD COLUMN "comment" TEXT;
jbe@285 40 ALTER TABLE "direct_voter" ADD COLUMN "text_search_data" TSVECTOR;
jbe@285 41 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
jbe@285 42 CREATE TRIGGER "update_text_search_data"
jbe@285 43 BEFORE INSERT OR UPDATE ON "direct_voter"
jbe@285 44 FOR EACH ROW EXECUTE PROCEDURE
jbe@285 45 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
jbe@285 46
jbe@285 47 COMMENT ON COLUMN "direct_voter"."comment_changed" IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';
jbe@285 48 COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';
jbe@285 49 COMMENT ON COLUMN "direct_voter"."comment" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
jbe@285 50
jbe@285 51 CREATE TABLE "rendered_voter_comment" (
jbe@285 52 PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@285 53 FOREIGN KEY ("issue_id", "member_id")
jbe@285 54 REFERENCES "direct_voter" ("issue_id", "member_id")
jbe@285 55 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@285 56 "issue_id" INT4,
jbe@285 57 "member_id" INT4,
jbe@285 58 "format" TEXT,
jbe@285 59 "content" TEXT NOT NULL );
jbe@285 60
jbe@285 61 COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)';
jbe@285 62
jbe@285 63
jbe@262 64 DROP TABLE "rendered_issue_comment";
jbe@262 65 DROP TABLE "issue_comment";
jbe@285 66 DROP TABLE "rendered_voting_comment";
jbe@285 67 DROP TABLE "voting_comment";
jbe@262 68
jbe@284 69 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
jbe@284 70 RETURNS TRIGGER
jbe@284 71 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@284 72 BEGIN
jbe@284 73 DELETE FROM "direct_voter"
jbe@284 74 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
jbe@284 75 RETURN NULL;
jbe@284 76 END;
jbe@284 77 $$;
jbe@284 78
jbe@284 79 CREATE TRIGGER "non_voter_deletes_direct_voter"
jbe@284 80 AFTER INSERT OR UPDATE ON "non_voter"
jbe@284 81 FOR EACH ROW EXECUTE PROCEDURE
jbe@284 82 "non_voter_deletes_direct_voter_trigger"();
jbe@284 83
jbe@284 84 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
jbe@284 85 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 86
jbe@284 87 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
jbe@284 88 RETURNS TRIGGER
jbe@284 89 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@284 90 BEGIN
jbe@284 91 DELETE FROM "non_voter"
jbe@284 92 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
jbe@284 93 RETURN NULL;
jbe@284 94 END;
jbe@284 95 $$;
jbe@284 96
jbe@284 97 CREATE TRIGGER "direct_voter_deletes_non_voter"
jbe@284 98 AFTER INSERT OR UPDATE ON "direct_voter"
jbe@284 99 FOR EACH ROW EXECUTE PROCEDURE
jbe@284 100 "direct_voter_deletes_non_voter_trigger"();
jbe@284 101
jbe@284 102 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
jbe@284 103 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 104
jbe@285 105 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
jbe@285 106 RETURNS TRIGGER
jbe@285 107 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@285 108 BEGIN
jbe@285 109 IF NEW."comment" ISNULL THEN
jbe@285 110 NEW."comment_changed" := NULL;
jbe@285 111 NEW."formatting_engine" := NULL;
jbe@285 112 END IF;
jbe@285 113 RETURN NEW;
jbe@285 114 END;
jbe@285 115 $$;
jbe@285 116
jbe@285 117 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
jbe@285 118 BEFORE INSERT OR UPDATE ON "direct_voter"
jbe@285 119 FOR EACH ROW EXECUTE PROCEDURE
jbe@285 120 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
jbe@285 121
jbe@285 122 COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"';
jbe@285 123 COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.';
jbe@285 124
jbe@262 125 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
jbe@262 126 ( "issue_id_p" "issue"."id"%TYPE )
jbe@262 127 RETURNS VOID
jbe@262 128 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262 129 DECLARE
jbe@262 130 "issue_row" "issue"%ROWTYPE;
jbe@262 131 "policy_row" "policy"%ROWTYPE;
jbe@262 132 "initiative_row" "initiative"%ROWTYPE;
jbe@262 133 BEGIN
jbe@262 134 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@262 135 SELECT * INTO "policy_row"
jbe@262 136 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@262 137 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@262 138 FOR "initiative_row" IN
jbe@262 139 SELECT * FROM "initiative"
jbe@262 140 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@262 141 LOOP
jbe@262 142 IF
jbe@262 143 "initiative_row"."polling" OR (
jbe@262 144 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@262 145 "initiative_row"."satisfied_supporter_count" *
jbe@262 146 "policy_row"."initiative_quorum_den" >=
jbe@262 147 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@262 148 )
jbe@262 149 THEN
jbe@262 150 UPDATE "initiative" SET "admitted" = TRUE
jbe@262 151 WHERE "id" = "initiative_row"."id";
jbe@262 152 ELSE
jbe@262 153 UPDATE "initiative" SET "admitted" = FALSE
jbe@262 154 WHERE "id" = "initiative_row"."id";
jbe@262 155 END IF;
jbe@262 156 END LOOP;
jbe@262 157 IF EXISTS (
jbe@262 158 SELECT NULL FROM "initiative"
jbe@262 159 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@262 160 ) THEN
jbe@262 161 UPDATE "issue" SET
jbe@262 162 "state" = 'voting',
jbe@262 163 "accepted" = coalesce("accepted", now()),
jbe@262 164 "half_frozen" = coalesce("half_frozen", now()),
jbe@262 165 "fully_frozen" = now()
jbe@262 166 WHERE "id" = "issue_id_p";
jbe@262 167 ELSE
jbe@262 168 UPDATE "issue" SET
jbe@262 169 "state" = 'canceled_no_initiative_admitted',
jbe@262 170 "accepted" = coalesce("accepted", now()),
jbe@262 171 "half_frozen" = coalesce("half_frozen", now()),
jbe@262 172 "fully_frozen" = now(),
jbe@262 173 "closed" = now(),
jbe@262 174 "ranks_available" = TRUE
jbe@262 175 WHERE "id" = "issue_id_p";
jbe@262 176 -- NOTE: The following DELETE statements have effect only when
jbe@262 177 -- issue state has been manipulated
jbe@262 178 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@262 179 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@262 180 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@262 181 END IF;
jbe@262 182 RETURN;
jbe@262 183 END;
jbe@262 184 $$;
jbe@262 185
jbe@262 186 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@262 187 RETURNS VOID
jbe@262 188 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262 189 DECLARE
jbe@262 190 "issue_row" "issue"%ROWTYPE;
jbe@262 191 BEGIN
jbe@262 192 SELECT * INTO "issue_row"
jbe@262 193 FROM "issue" WHERE "id" = "issue_id_p"
jbe@262 194 FOR UPDATE;
jbe@262 195 IF "issue_row"."cleaned" ISNULL THEN
jbe@262 196 UPDATE "issue" SET
jbe@262 197 "state" = 'voting',
jbe@262 198 "closed" = NULL,
jbe@262 199 "ranks_available" = FALSE
jbe@262 200 WHERE "id" = "issue_id_p";
jbe@262 201 DELETE FROM "delegating_voter"
jbe@262 202 WHERE "issue_id" = "issue_id_p";
jbe@262 203 DELETE FROM "direct_voter"
jbe@262 204 WHERE "issue_id" = "issue_id_p";
jbe@262 205 DELETE FROM "delegating_interest_snapshot"
jbe@262 206 WHERE "issue_id" = "issue_id_p";
jbe@262 207 DELETE FROM "direct_interest_snapshot"
jbe@262 208 WHERE "issue_id" = "issue_id_p";
jbe@262 209 DELETE FROM "delegating_population_snapshot"
jbe@262 210 WHERE "issue_id" = "issue_id_p";
jbe@262 211 DELETE FROM "direct_population_snapshot"
jbe@262 212 WHERE "issue_id" = "issue_id_p";
jbe@262 213 DELETE FROM "non_voter"
jbe@262 214 WHERE "issue_id" = "issue_id_p";
jbe@262 215 DELETE FROM "delegation"
jbe@262 216 WHERE "issue_id" = "issue_id_p";
jbe@262 217 DELETE FROM "supporter"
jbe@262 218 WHERE "issue_id" = "issue_id_p";
jbe@262 219 UPDATE "issue" SET
jbe@262 220 "state" = "issue_row"."state",
jbe@262 221 "closed" = "issue_row"."closed",
jbe@262 222 "ranks_available" = "issue_row"."ranks_available",
jbe@262 223 "cleaned" = now()
jbe@262 224 WHERE "id" = "issue_id_p";
jbe@262 225 END IF;
jbe@262 226 RETURN;
jbe@262 227 END;
jbe@262 228 $$;
jbe@262 229
jbe@285 230 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@285 231 RETURNS VOID
jbe@285 232 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@285 233 DECLARE
jbe@285 234 "area_id_v" "area"."id"%TYPE;
jbe@285 235 "unit_id_v" "unit"."id"%TYPE;
jbe@285 236 "member_id_v" "member"."id"%TYPE;
jbe@285 237 BEGIN
jbe@285 238 PERFORM "lock_issue"("issue_id_p");
jbe@285 239 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@285 240 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@285 241 -- delete timestamp of voting comment:
jbe@285 242 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@285 243 WHERE "issue_id" = "issue_id_p";
jbe@285 244 -- delete delegating votes (in cases of manual reset of issue state):
jbe@285 245 DELETE FROM "delegating_voter"
jbe@285 246 WHERE "issue_id" = "issue_id_p";
jbe@285 247 -- delete votes from non-privileged voters:
jbe@285 248 DELETE FROM "direct_voter"
jbe@285 249 USING (
jbe@285 250 SELECT
jbe@285 251 "direct_voter"."member_id"
jbe@285 252 FROM "direct_voter"
jbe@285 253 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@285 254 LEFT JOIN "privilege"
jbe@285 255 ON "privilege"."unit_id" = "unit_id_v"
jbe@285 256 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@285 257 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@285 258 "member"."active" = FALSE OR
jbe@285 259 "privilege"."voting_right" ISNULL OR
jbe@285 260 "privilege"."voting_right" = FALSE
jbe@285 261 )
jbe@285 262 ) AS "subquery"
jbe@285 263 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@285 264 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@285 265 -- consider delegations:
jbe@285 266 UPDATE "direct_voter" SET "weight" = 1
jbe@285 267 WHERE "issue_id" = "issue_id_p";
jbe@285 268 PERFORM "add_vote_delegations"("issue_id_p");
jbe@285 269 -- set voter count and mark issue as being calculated:
jbe@285 270 UPDATE "issue" SET
jbe@285 271 "state" = 'calculation',
jbe@285 272 "closed" = now(),
jbe@285 273 "voter_count" = (
jbe@285 274 SELECT coalesce(sum("weight"), 0)
jbe@285 275 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@285 276 )
jbe@285 277 WHERE "id" = "issue_id_p";
jbe@285 278 -- materialize battle_view:
jbe@285 279 -- NOTE: "closed" column of issue must be set at this point
jbe@285 280 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@285 281 INSERT INTO "battle" (
jbe@285 282 "issue_id",
jbe@285 283 "winning_initiative_id", "losing_initiative_id",
jbe@285 284 "count"
jbe@285 285 ) SELECT
jbe@285 286 "issue_id",
jbe@285 287 "winning_initiative_id", "losing_initiative_id",
jbe@285 288 "count"
jbe@285 289 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@285 290 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@285 291 UPDATE "initiative" SET
jbe@285 292 "positive_votes" = "battle_win"."count",
jbe@285 293 "negative_votes" = "battle_lose"."count"
jbe@285 294 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@285 295 WHERE
jbe@285 296 "battle_win"."issue_id" = "issue_id_p" AND
jbe@285 297 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@285 298 "battle_win"."losing_initiative_id" ISNULL AND
jbe@285 299 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@285 300 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@285 301 "battle_lose"."winning_initiative_id" ISNULL;
jbe@285 302 END;
jbe@285 303 $$;
jbe@285 304
jbe@283 305 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 306
jbe@262 307 COMMIT;

Impressum / About Us