liquid_feedback_core
view update/core-update.v1.2.0-v1.2.1.sql @ 128:5ea2f9c5ba9a
Set "rank" for all "admitted" initiatives after voting is finished; Additional columns in "initiative" table store voting result information
Changes in detail:
- Renamed column "majority" of table "initiative" to "attainable"
- Added columns "favored", "unfavored" and "eligible" to "initiative" table
- Added constraints to "initiative" table, restricting certain value combinations in the the new columns
- Modified "calculate_ranks" function to update the new columns and always write "rank" information
- Marking the final winner is now done utilizing the new "eligible" column
Changes in detail:
- Renamed column "majority" of table "initiative" to "attainable"
- Added columns "favored", "unfavored" and "eligible" to "initiative" table
- Added constraints to "initiative" table, restricting certain value combinations in the the new columns
- Modified "calculate_ranks" function to update the new columns and always write "rank" information
- Marking the final winner is now done utilizing the new "eligible" column
| author | jbe |
|---|---|
| date | Tue May 24 14:58:42 2011 +0200 (2011-05-24) |
| parents | fdd1729d7e2a |
| children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('1.2.1', 1, 2, 1))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TABLE "issue" ADD COLUMN "cleaned" TIMESTAMPTZ;
8 ALTER TABLE "issue" ADD CONSTRAINT "clean_restriction"
9 CHECK (
10 "cleaned" ISNULL OR (
11 "closed" NOTNULL AND (
12 "fully_frozen" ISNULL OR "ranks_available"
13 )
14 )
15 );
17 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
19 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
20 RETURNS VOID
21 LANGUAGE 'plpgsql' VOLATILE AS $$
22 DECLARE
23 "issue_row" "issue"%ROWTYPE;
24 BEGIN
25 SELECT * INTO "issue_row"
26 FROM "issue" WHERE "id" = "issue_id_p"
27 FOR UPDATE;
28 IF "issue_row"."cleaned" ISNULL THEN
29 UPDATE "issue" SET
30 "closed" = NULL,
31 "ranks_available" = FALSE
32 WHERE "id" = "issue_id_p";
33 DELETE FROM "delegating_voter"
34 WHERE "issue_id" = "issue_id_p";
35 DELETE FROM "direct_voter"
36 WHERE "issue_id" = "issue_id_p";
37 DELETE FROM "delegating_interest_snapshot"
38 WHERE "issue_id" = "issue_id_p";
39 DELETE FROM "direct_interest_snapshot"
40 WHERE "issue_id" = "issue_id_p";
41 DELETE FROM "delegating_population_snapshot"
42 WHERE "issue_id" = "issue_id_p";
43 DELETE FROM "direct_population_snapshot"
44 WHERE "issue_id" = "issue_id_p";
45 DELETE FROM "delegation"
46 WHERE "issue_id" = "issue_id_p";
47 DELETE FROM "supporter"
48 WHERE "issue_id" = "issue_id_p";
49 UPDATE "issue" SET
50 "closed" = "issue_row"."closed",
51 "ranks_available" = "issue_row"."ranks_available",
52 "cleaned" = now()
53 WHERE "id" = "issue_id_p";
54 END IF;
55 RETURN;
56 END;
57 $$;
59 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
61 COMMIT;