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;