1.1 --- a/update/core-update.beta16-beta17.sql	Mon Mar 07 13:54:43 2011 +0100
     1.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.3 @@ -1,165 +0,0 @@
     1.4 -BEGIN;
     1.5 -
     1.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL))
     1.8 -  AS "subquery"("string", "major", "minor", "revision");
     1.9 -
    1.10 -COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
    1.11 -
    1.12 -CREATE TABLE "setting_map" (
    1.13 -        PRIMARY KEY ("member_id", "key", "subkey"),
    1.14 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.15 -        "key"                   TEXT            NOT NULL,
    1.16 -        "subkey"                TEXT            NOT NULL,
    1.17 -        "value"                 TEXT            NOT NULL );
    1.18 -CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
    1.19 -
    1.20 -COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
    1.21 -
    1.22 -COMMENT ON COLUMN "setting_map"."key"    IS 'Name of the setting, preceded by a frontend specific prefix';
    1.23 -COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
    1.24 -COMMENT ON COLUMN "setting_map"."value"  IS 'Value of a map entry';
    1.25 -
    1.26 -CREATE INDEX "issue_created_idx" ON "issue" ("created");
    1.27 -CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
    1.28 -CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
    1.29 -CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
    1.30 -CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
    1.31 -CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
    1.32 -CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
    1.33 -CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
    1.34 -CREATE INDEX "draft_created_idx" ON "draft" ("created");
    1.35 -CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
    1.36 -
    1.37 -CREATE TYPE "timeline_event" AS ENUM (
    1.38 -  'issue_created',
    1.39 -  'issue_canceled',
    1.40 -  'issue_accepted',
    1.41 -  'issue_half_frozen',
    1.42 -  'issue_finished_without_voting',
    1.43 -  'issue_voting_started',
    1.44 -  'issue_finished_after_voting',
    1.45 -  'initiative_created',
    1.46 -  'initiative_revoked',
    1.47 -  'draft_created',
    1.48 -  'suggestion_created');
    1.49 -
    1.50 -COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
    1.51 -
    1.52 -CREATE VIEW "timeline_issue" AS
    1.53 -    SELECT
    1.54 -      "created" AS "occurrence",
    1.55 -      'issue_created'::"timeline_event" AS "event",
    1.56 -      "id" AS "issue_id"
    1.57 -    FROM "issue"
    1.58 -  UNION ALL
    1.59 -    SELECT
    1.60 -      "closed" AS "occurrence",
    1.61 -      'issue_canceled'::"timeline_event" AS "event",
    1.62 -      "id" AS "issue_id"
    1.63 -    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
    1.64 -  UNION ALL
    1.65 -    SELECT
    1.66 -      "accepted" AS "occurrence",
    1.67 -      'issue_accepted'::"timeline_event" AS "event",
    1.68 -      "id" AS "issue_id"
    1.69 -    FROM "issue" WHERE "accepted" NOTNULL
    1.70 -  UNION ALL
    1.71 -    SELECT
    1.72 -      "half_frozen" AS "occurrence",
    1.73 -      'issue_half_frozen'::"timeline_event" AS "event",
    1.74 -      "id" AS "issue_id"
    1.75 -    FROM "issue" WHERE "half_frozen" NOTNULL
    1.76 -  UNION ALL
    1.77 -    SELECT
    1.78 -      "fully_frozen" AS "occurrence",
    1.79 -      'issue_voting_started'::"timeline_event" AS "event",
    1.80 -      "id" AS "issue_id"
    1.81 -    FROM "issue"
    1.82 -    WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen"
    1.83 -  UNION ALL
    1.84 -    SELECT
    1.85 -      "closed" AS "occurrence",
    1.86 -      CASE WHEN "fully_frozen" = "closed" THEN
    1.87 -        'issue_finished_without_voting'::"timeline_event"
    1.88 -      ELSE
    1.89 -        'issue_finished_after_voting'::"timeline_event"
    1.90 -      END AS "event",
    1.91 -      "id" AS "issue_id"
    1.92 -    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
    1.93 -
    1.94 -COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
    1.95 -
    1.96 -CREATE VIEW "timeline_initiative" AS
    1.97 -    SELECT
    1.98 -      "created" AS "occurrence",
    1.99 -      'initiative_created'::"timeline_event" AS "event",
   1.100 -      "id" AS "initiative_id"
   1.101 -    FROM "initiative"
   1.102 -  UNION ALL
   1.103 -    SELECT
   1.104 -      "revoked" AS "occurrence",
   1.105 -      'initiative_revoked'::"timeline_event" AS "event",
   1.106 -      "id" AS "initiative_id"
   1.107 -    FROM "initiative" WHERE "revoked" NOTNULL;
   1.108 -
   1.109 -COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
   1.110 -
   1.111 -CREATE VIEW "timeline_draft" AS
   1.112 -  SELECT
   1.113 -    "created" AS "occurrence",
   1.114 -    'draft_created'::"timeline_event" AS "event",
   1.115 -    "id" AS "draft_id"
   1.116 -  FROM "draft";
   1.117 -
   1.118 -COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
   1.119 -
   1.120 -CREATE VIEW "timeline_suggestion" AS
   1.121 -  SELECT
   1.122 -    "created" AS "occurrence",
   1.123 -    'suggestion_created'::"timeline_event" AS "event",
   1.124 -    "id" AS "suggestion_id"
   1.125 -  FROM "suggestion";
   1.126 -
   1.127 -COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
   1.128 -
   1.129 -CREATE VIEW "timeline" AS
   1.130 -    SELECT
   1.131 -      "occurrence",
   1.132 -      "event",
   1.133 -      "issue_id",
   1.134 -      NULL AS "initiative_id",
   1.135 -      NULL::INT8 AS "draft_id",  -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
   1.136 -      NULL::INT8 AS "suggestion_id"
   1.137 -    FROM "timeline_issue"
   1.138 -  UNION ALL
   1.139 -    SELECT
   1.140 -      "occurrence",
   1.141 -      "event",
   1.142 -      NULL AS "issue_id",
   1.143 -      "initiative_id",
   1.144 -      NULL AS "draft_id",
   1.145 -      NULL AS "suggestion_id"
   1.146 -    FROM "timeline_initiative"
   1.147 -  UNION ALL
   1.148 -    SELECT
   1.149 -      "occurrence",
   1.150 -      "event",
   1.151 -      NULL AS "issue_id",
   1.152 -      NULL AS "initiative_id",
   1.153 -      "draft_id",
   1.154 -      NULL AS "suggestion_id"
   1.155 -    FROM "timeline_draft"
   1.156 -  UNION ALL
   1.157 -    SELECT
   1.158 -      "occurrence",
   1.159 -      "event",
   1.160 -      NULL AS "issue_id",
   1.161 -      NULL AS "initiative_id",
   1.162 -      NULL AS "draft_id",
   1.163 -      "suggestion_id"
   1.164 -    FROM "timeline_suggestion";
   1.165 -
   1.166 -COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
   1.167 -
   1.168 -COMMIT;

     2.1 --- a/update/core-update.beta17-beta18.sql	Mon Mar 07 13:54:43 2011 +0100
     2.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.3 @@ -1,50 +0,0 @@
     2.4 -BEGIN;
     2.5 -
     2.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.7 -  SELECT * FROM (VALUES ('beta18', NULL, NULL, NULL))
     2.8 -  AS "subquery"("string", "major", "minor", "revision");
     2.9 -
    2.10 -CREATE OR REPLACE VIEW "timeline_issue" AS
    2.11 -    SELECT
    2.12 -      "created" AS "occurrence",
    2.13 -      'issue_created'::"timeline_event" AS "event",
    2.14 -      "id" AS "issue_id"
    2.15 -    FROM "issue"
    2.16 -  UNION ALL
    2.17 -    SELECT
    2.18 -      "closed" AS "occurrence",
    2.19 -      'issue_canceled'::"timeline_event" AS "event",
    2.20 -      "id" AS "issue_id"
    2.21 -    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
    2.22 -  UNION ALL
    2.23 -    SELECT
    2.24 -      "accepted" AS "occurrence",
    2.25 -      'issue_accepted'::"timeline_event" AS "event",
    2.26 -      "id" AS "issue_id"
    2.27 -    FROM "issue" WHERE "accepted" NOTNULL
    2.28 -  UNION ALL
    2.29 -    SELECT
    2.30 -      "half_frozen" AS "occurrence",
    2.31 -      'issue_half_frozen'::"timeline_event" AS "event",
    2.32 -      "id" AS "issue_id"
    2.33 -    FROM "issue" WHERE "half_frozen" NOTNULL
    2.34 -  UNION ALL
    2.35 -    SELECT
    2.36 -      "fully_frozen" AS "occurrence",
    2.37 -      'issue_voting_started'::"timeline_event" AS "event",
    2.38 -      "id" AS "issue_id"
    2.39 -    FROM "issue"
    2.40 -    WHERE "fully_frozen" NOTNULL
    2.41 -    AND ("closed" ISNULL OR "closed" != "fully_frozen")
    2.42 -  UNION ALL
    2.43 -    SELECT
    2.44 -      "closed" AS "occurrence",
    2.45 -      CASE WHEN "fully_frozen" = "closed" THEN
    2.46 -        'issue_finished_without_voting'::"timeline_event"
    2.47 -      ELSE
    2.48 -        'issue_finished_after_voting'::"timeline_event"
    2.49 -      END AS "event",
    2.50 -      "id" AS "issue_id"
    2.51 -    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
    2.52 -
    2.53 -COMMIT;

     3.1 --- a/update/core-update.beta21-beta22.sql	Mon Mar 07 13:54:43 2011 +0100
     3.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.3 @@ -1,16 +0,0 @@
     3.4 -BEGIN;
     3.5 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     3.6 -  SELECT * FROM (VALUES ('beta22', NULL, NULL, NULL))
     3.7 -  AS "subquery"("string", "major", "minor", "revision");
     3.8 -ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
     3.9 -ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK (
    3.10 -  ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    3.11 -  ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    3.12 -  ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    3.13 -  ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    3.14 -  ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    3.15 -  ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    3.16 -  ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    3.17 -  ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    3.18 -  ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) );
    3.19 -COMMIT;

     4.1 --- a/update/core-update.beta22-beta23.sql	Mon Mar 07 13:54:43 2011 +0100
     4.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     4.3 @@ -1,199 +0,0 @@
     4.4 -BEGIN;
     4.5 -
     4.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     4.7 -  SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL))
     4.8 -  AS "subquery"("string", "major", "minor", "revision");
     4.9 -
    4.10 -CREATE OR REPLACE FUNCTION "create_snapshot"
    4.11 -  ( "issue_id_p" "issue"."id"%TYPE )
    4.12 -  RETURNS VOID
    4.13 -  LANGUAGE 'plpgsql' VOLATILE AS $$
    4.14 -    DECLARE
    4.15 -      "initiative_id_v"    "initiative"."id"%TYPE;
    4.16 -      "suggestion_id_v"    "suggestion"."id"%TYPE;
    4.17 -    BEGIN
    4.18 -      PERFORM "global_lock"();
    4.19 -      PERFORM "create_population_snapshot"("issue_id_p");
    4.20 -      PERFORM "create_interest_snapshot"("issue_id_p");
    4.21 -      UPDATE "issue" SET
    4.22 -        "snapshot" = now(),
    4.23 -        "latest_snapshot_event" = 'periodic',
    4.24 -        "population" = (
    4.25 -          SELECT coalesce(sum("weight"), 0)
    4.26 -          FROM "direct_population_snapshot"
    4.27 -          WHERE "issue_id" = "issue_id_p"
    4.28 -          AND "event" = 'periodic'
    4.29 -        ),
    4.30 -        "vote_now" = (
    4.31 -          SELECT coalesce(sum("weight"), 0)
    4.32 -          FROM "direct_interest_snapshot"
    4.33 -          WHERE "issue_id" = "issue_id_p"
    4.34 -          AND "event" = 'periodic'
    4.35 -          AND "voting_requested" = TRUE
    4.36 -        ),
    4.37 -        "vote_later" = (
    4.38 -          SELECT coalesce(sum("weight"), 0)
    4.39 -          FROM "direct_interest_snapshot"
    4.40 -          WHERE "issue_id" = "issue_id_p"
    4.41 -          AND "event" = 'periodic'
    4.42 -          AND "voting_requested" = FALSE
    4.43 -        )
    4.44 -        WHERE "id" = "issue_id_p";
    4.45 -      FOR "initiative_id_v" IN
    4.46 -        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
    4.47 -      LOOP
    4.48 -        UPDATE "initiative" SET
    4.49 -          "supporter_count" = (
    4.50 -            SELECT coalesce(sum("di"."weight"), 0)
    4.51 -            FROM "direct_interest_snapshot" AS "di"
    4.52 -            JOIN "direct_supporter_snapshot" AS "ds"
    4.53 -            ON "di"."member_id" = "ds"."member_id"
    4.54 -            WHERE "di"."issue_id" = "issue_id_p"
    4.55 -            AND "di"."event" = 'periodic'
    4.56 -            AND "ds"."initiative_id" = "initiative_id_v"
    4.57 -            AND "ds"."event" = 'periodic'
    4.58 -          ),
    4.59 -          "informed_supporter_count" = (
    4.60 -            SELECT coalesce(sum("di"."weight"), 0)
    4.61 -            FROM "direct_interest_snapshot" AS "di"
    4.62 -            JOIN "direct_supporter_snapshot" AS "ds"
    4.63 -            ON "di"."member_id" = "ds"."member_id"
    4.64 -            WHERE "di"."issue_id" = "issue_id_p"
    4.65 -            AND "di"."event" = 'periodic'
    4.66 -            AND "ds"."initiative_id" = "initiative_id_v"
    4.67 -            AND "ds"."event" = 'periodic'
    4.68 -            AND "ds"."informed"
    4.69 -          ),
    4.70 -          "satisfied_supporter_count" = (
    4.71 -            SELECT coalesce(sum("di"."weight"), 0)
    4.72 -            FROM "direct_interest_snapshot" AS "di"
    4.73 -            JOIN "direct_supporter_snapshot" AS "ds"
    4.74 -            ON "di"."member_id" = "ds"."member_id"
    4.75 -            WHERE "di"."issue_id" = "issue_id_p"
    4.76 -            AND "di"."event" = 'periodic'
    4.77 -            AND "ds"."initiative_id" = "initiative_id_v"
    4.78 -            AND "ds"."event" = 'periodic'
    4.79 -            AND "ds"."satisfied"
    4.80 -          ),
    4.81 -          "satisfied_informed_supporter_count" = (
    4.82 -            SELECT coalesce(sum("di"."weight"), 0)
    4.83 -            FROM "direct_interest_snapshot" AS "di"
    4.84 -            JOIN "direct_supporter_snapshot" AS "ds"
    4.85 -            ON "di"."member_id" = "ds"."member_id"
    4.86 -            WHERE "di"."issue_id" = "issue_id_p"
    4.87 -            AND "di"."event" = 'periodic'
    4.88 -            AND "ds"."initiative_id" = "initiative_id_v"
    4.89 -            AND "ds"."event" = 'periodic'
    4.90 -            AND "ds"."informed"
    4.91 -            AND "ds"."satisfied"
    4.92 -          )
    4.93 -          WHERE "id" = "initiative_id_v";
    4.94 -        FOR "suggestion_id_v" IN
    4.95 -          SELECT "id" FROM "suggestion"
    4.96 -          WHERE "initiative_id" = "initiative_id_v"
    4.97 -        LOOP
    4.98 -          UPDATE "suggestion" SET
    4.99 -            "minus2_unfulfilled_count" = (
   4.100 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   4.101 -              FROM "issue" CROSS JOIN "opinion"
   4.102 -              JOIN "direct_interest_snapshot" AS "snapshot"
   4.103 -              ON "snapshot"."issue_id" = "issue"."id"
   4.104 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   4.105 -              AND "snapshot"."member_id" = "opinion"."member_id"
   4.106 -              WHERE "issue"."id" = "issue_id_p"
   4.107 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   4.108 -              AND "opinion"."degree" = -2
   4.109 -              AND "opinion"."fulfilled" = FALSE
   4.110 -            ),
   4.111 -            "minus2_fulfilled_count" = (
   4.112 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   4.113 -              FROM "issue" CROSS JOIN "opinion"
   4.114 -              JOIN "direct_interest_snapshot" AS "snapshot"
   4.115 -              ON "snapshot"."issue_id" = "issue"."id"
   4.116 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   4.117 -              AND "snapshot"."member_id" = "opinion"."member_id"
   4.118 -              WHERE "issue"."id" = "issue_id_p"
   4.119 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   4.120 -              AND "opinion"."degree" = -2
   4.121 -              AND "opinion"."fulfilled" = TRUE
   4.122 -            ),
   4.123 -            "minus1_unfulfilled_count" = (
   4.124 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   4.125 -              FROM "issue" CROSS JOIN "opinion"
   4.126 -              JOIN "direct_interest_snapshot" AS "snapshot"
   4.127 -              ON "snapshot"."issue_id" = "issue"."id"
   4.128 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   4.129 -              AND "snapshot"."member_id" = "opinion"."member_id"
   4.130 -              WHERE "issue"."id" = "issue_id_p"
   4.131 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   4.132 -              AND "opinion"."degree" = -1
   4.133 -              AND "opinion"."fulfilled" = FALSE
   4.134 -            ),
   4.135 -            "minus1_fulfilled_count" = (
   4.136 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   4.137 -              FROM "issue" CROSS JOIN "opinion"
   4.138 -              JOIN "direct_interest_snapshot" AS "snapshot"
   4.139 -              ON "snapshot"."issue_id" = "issue"."id"
   4.140 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   4.141 -              AND "snapshot"."member_id" = "opinion"."member_id"
   4.142 -              WHERE "issue"."id" = "issue_id_p"
   4.143 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   4.144 -              AND "opinion"."degree" = -1
   4.145 -              AND "opinion"."fulfilled" = TRUE
   4.146 -            ),
   4.147 -            "plus1_unfulfilled_count" = (
   4.148 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   4.149 -              FROM "issue" CROSS JOIN "opinion"
   4.150 -              JOIN "direct_interest_snapshot" AS "snapshot"
   4.151 -              ON "snapshot"."issue_id" = "issue"."id"
   4.152 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   4.153 -              AND "snapshot"."member_id" = "opinion"."member_id"
   4.154 -              WHERE "issue"."id" = "issue_id_p"
   4.155 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   4.156 -              AND "opinion"."degree" = 1
   4.157 -              AND "opinion"."fulfilled" = FALSE
   4.158 -            ),
   4.159 -            "plus1_fulfilled_count" = (
   4.160 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   4.161 -              FROM "issue" CROSS JOIN "opinion"
   4.162 -              JOIN "direct_interest_snapshot" AS "snapshot"
   4.163 -              ON "snapshot"."issue_id" = "issue"."id"
   4.164 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   4.165 -              AND "snapshot"."member_id" = "opinion"."member_id"
   4.166 -              WHERE "issue"."id" = "issue_id_p"
   4.167 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   4.168 -              AND "opinion"."degree" = 1
   4.169 -              AND "opinion"."fulfilled" = TRUE
   4.170 -            ),
   4.171 -            "plus2_unfulfilled_count" = (
   4.172 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   4.173 -              FROM "issue" CROSS JOIN "opinion"
   4.174 -              JOIN "direct_interest_snapshot" AS "snapshot"
   4.175 -              ON "snapshot"."issue_id" = "issue"."id"
   4.176 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   4.177 -              AND "snapshot"."member_id" = "opinion"."member_id"
   4.178 -              WHERE "issue"."id" = "issue_id_p"
   4.179 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   4.180 -              AND "opinion"."degree" = 2
   4.181 -              AND "opinion"."fulfilled" = FALSE
   4.182 -            ),
   4.183 -            "plus2_fulfilled_count" = (
   4.184 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   4.185 -              FROM "issue" CROSS JOIN "opinion"
   4.186 -              JOIN "direct_interest_snapshot" AS "snapshot"
   4.187 -              ON "snapshot"."issue_id" = "issue"."id"
   4.188 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   4.189 -              AND "snapshot"."member_id" = "opinion"."member_id"
   4.190 -              WHERE "issue"."id" = "issue_id_p"
   4.191 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   4.192 -              AND "opinion"."degree" = 2
   4.193 -              AND "opinion"."fulfilled" = TRUE
   4.194 -            )
   4.195 -            WHERE "suggestion"."id" = "suggestion_id_v";
   4.196 -        END LOOP;
   4.197 -      END LOOP;
   4.198 -      RETURN;
   4.199 -    END;
   4.200 -  $$;
   4.201 -
   4.202 -COMMIT;

     5.1 --- a/update/core-update.beta23-beta24.sql	Mon Mar 07 13:54:43 2011 +0100
     5.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     5.3 @@ -1,60 +0,0 @@
     5.4 -BEGIN;
     5.5 -
     5.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     5.7 -  SELECT * FROM (VALUES ('beta24', NULL, NULL, NULL))
     5.8 -  AS "subquery"("string", "major", "minor", "revision");
     5.9 -
    5.10 -COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
    5.11 -
    5.12 -COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
    5.13 -
    5.14 -CREATE OR REPLACE FUNCTION "delete_private_data"()
    5.15 -  RETURNS VOID
    5.16 -  LANGUAGE 'plpgsql' VOLATILE AS $$
    5.17 -    DECLARE
    5.18 -      "issue_id_v" "issue"."id"%TYPE;
    5.19 -    BEGIN
    5.20 -      UPDATE "member" SET
    5.21 -        "login"                        = 'login' || "id"::text,
    5.22 -        "password"                     = NULL,
    5.23 -        "notify_email"                 = NULL,
    5.24 -        "notify_email_unconfirmed"     = NULL,
    5.25 -        "notify_email_secret"          = NULL,
    5.26 -        "notify_email_secret_expiry"   = NULL,
    5.27 -        "password_reset_secret"        = NULL,
    5.28 -        "password_reset_secret_expiry" = NULL,
    5.29 -        "organizational_unit"          = NULL,
    5.30 -        "internal_posts"               = NULL,
    5.31 -        "realname"                     = NULL,
    5.32 -        "birthday"                     = NULL,
    5.33 -        "address"                      = NULL,
    5.34 -        "email"                        = NULL,
    5.35 -        "xmpp_address"                 = NULL,
    5.36 -        "website"                      = NULL,
    5.37 -        "phone"                        = NULL,
    5.38 -        "mobile_phone"                 = NULL,
    5.39 -        "profession"                   = NULL,
    5.40 -        "external_memberships"         = NULL,
    5.41 -        "external_posts"               = NULL,
    5.42 -        "statement"                    = NULL;
    5.43 -      -- "text_search_data" is updated by triggers
    5.44 -      UPDATE "member_history" SET "login" = 'login' || "member_id"::text;
    5.45 -      DELETE FROM "invite_code";
    5.46 -      DELETE FROM "setting";
    5.47 -      DELETE FROM "setting_map";
    5.48 -      DELETE FROM "member_relation_setting";
    5.49 -      DELETE FROM "member_image";
    5.50 -      DELETE FROM "contact";
    5.51 -      DELETE FROM "session";
    5.52 -      DELETE FROM "area_setting";
    5.53 -      DELETE FROM "issue_setting";
    5.54 -      DELETE FROM "initiative_setting";
    5.55 -      DELETE FROM "suggestion_setting";
    5.56 -      DELETE FROM "direct_voter" USING "issue"
    5.57 -        WHERE "direct_voter"."issue_id" = "issue"."id"
    5.58 -        AND "issue"."closed" ISNULL;
    5.59 -      RETURN;
    5.60 -    END;
    5.61 -  $$;
    5.62 -
    5.63 -COMMIT;

     6.1 --- a/update/core-update.beta24-beta25.sql	Mon Mar 07 13:54:43 2011 +0100
     6.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     6.3 @@ -1,5 +0,0 @@
     6.4 -BEGIN;
     6.5 -
     6.6 --- no changes in database between beta24 to beta25
     6.7 -
     6.8 -COMMIT;

     7.1 --- a/update/prepare-beta18-beta19.sql	Mon Mar 07 13:54:43 2011 +0100
     7.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     7.3 @@ -1,60 +0,0 @@
     7.4 -BEGIN;
     7.5 -
     7.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     7.7 -  SELECT * FROM (VALUES ('incomplete_update_from_beta18_to_beta19', NULL, NULL, NULL))
     7.8 -  AS "subquery"("string", "major", "minor", "revision");
     7.9 -
    7.10 -ALTER TABLE "issue" RENAME COLUMN "latest_snapshot_event" TO "tmp";
    7.11 -ALTER TABLE "direct_population_snapshot"     RENAME COLUMN "event" TO "tmp";
    7.12 -ALTER TABLE "delegating_population_snapshot" RENAME COLUMN "event" TO "tmp";
    7.13 -ALTER TABLE "direct_interest_snapshot"       RENAME COLUMN "event" TO "tmp";
    7.14 -ALTER TABLE "delegating_interest_snapshot"   RENAME COLUMN "event" TO "tmp";
    7.15 -ALTER TABLE "direct_supporter_snapshot"      RENAME COLUMN "event" TO "tmp";
    7.16 -
    7.17 -ALTER TABLE "issue" ADD COLUMN "latest_snapshot_event" TEXT;
    7.18 -ALTER TABLE "direct_population_snapshot"     ADD COLUMN "event" TEXT;
    7.19 -ALTER TABLE "delegating_population_snapshot" ADD COLUMN "event" TEXT;
    7.20 -ALTER TABLE "direct_interest_snapshot"       ADD COLUMN "event" TEXT;
    7.21 -ALTER TABLE "delegating_interest_snapshot"   ADD COLUMN "event" TEXT;
    7.22 -ALTER TABLE "direct_supporter_snapshot"      ADD COLUMN "event" TEXT;
    7.23 -
    7.24 -ALTER TABLE "issue" ADD COLUMN "admission_time"    INTERVAL;
    7.25 -ALTER TABLE "issue" ADD COLUMN "discussion_time"   INTERVAL;
    7.26 -ALTER TABLE "issue" ADD COLUMN "verification_time" INTERVAL;
    7.27 -ALTER TABLE "issue" ADD COLUMN "voting_time"       INTERVAL;
    7.28 -
    7.29 -UPDATE "issue" SET "latest_snapshot_event"  = "tmp";
    7.30 -UPDATE "direct_population_snapshot"     SET "event" = "tmp";
    7.31 -UPDATE "delegating_population_snapshot" SET "event" = "tmp";
    7.32 -UPDATE "direct_interest_snapshot"       SET "event" = "tmp";
    7.33 -UPDATE "delegating_interest_snapshot"   SET "event" = "tmp";
    7.34 -UPDATE "direct_supporter_snapshot"      SET "event" = "tmp";
    7.35 -
    7.36 -UPDATE "issue" SET "latest_snapshot_event" = 'full_freeze' WHERE "latest_snapshot_event" = 'start_of_voting';
    7.37 -UPDATE "direct_population_snapshot"     SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';
    7.38 -UPDATE "delegating_population_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';
    7.39 -UPDATE "direct_interest_snapshot"       SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';
    7.40 -UPDATE "delegating_interest_snapshot"   SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';
    7.41 -UPDATE "direct_supporter_snapshot"      SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting';
    7.42 -
    7.43 -UPDATE "issue" SET
    7.44 -  "admission_time"    = "policy"."admission_time",
    7.45 -  "discussion_time"   = "policy"."discussion_time",
    7.46 -  "verification_time" = "policy"."verification_time",
    7.47 -  "voting_time"       = "policy"."voting_time"
    7.48 -  FROM "policy" WHERE "issue"."policy_id" = "policy"."id";
    7.49 -
    7.50 --- remove "tmp" columns indirectly
    7.51 -DROP TYPE "snapshot_event" CASCADE;
    7.52 -
    7.53 -COMMIT;
    7.54 -
    7.55 --- Complete the update as follows:
    7.56 --- =========================================
    7.57 --- pg_dump --disable-triggers --data-only DATABASE_NAME > tmp.sql
    7.58 --- dropdb DATABASE_NAME
    7.59 --- createdb DATABASE_NAME
    7.60 --- psql -v ON_ERROR_STOP=1 -f core.sql DATABASE_NAME
    7.61 --- psql -v ON_ERROR_STOP=1 -f tmp.sql DATABASE_NAME
    7.62 --- rm tmp.sql
    7.63 -

     8.1 --- a/update/prepare-beta25-beta26.sql	Mon Mar 07 13:54:43 2011 +0100
     8.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     8.3 @@ -1,25 +0,0 @@
     8.4 -BEGIN;
     8.5 -
     8.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     8.7 -  SELECT * FROM (VALUES ('incomplete_update_from_beta25_to_beta26', NULL, NULL, NULL))
     8.8 -  AS "subquery"("string", "major", "minor", "revision");
     8.9 -
    8.10 -ALTER TABLE "member" ADD COLUMN "last_login" TIMESTAMPTZ;
    8.11 -ALTER TABLE "member_history" ADD COLUMN "active" BOOLEAN;
    8.12 -
    8.13 -UPDATE "member_history" SET "active" = TRUE;
    8.14 -INSERT INTO "member_history" ("member_id", "login", "active", "name")
    8.15 -  SELECT "id", "login", TRUE AS "active", "name"
    8.16 -  FROM "member" WHERE "active" = FALSE;
    8.17 -
    8.18 -COMMIT;
    8.19 -
    8.20 --- Complete the update as follows:
    8.21 --- =========================================
    8.22 --- pg_dump --disable-triggers --data-only DATABASE_NAME > tmp.sql
    8.23 --- dropdb DATABASE_NAME
    8.24 --- createdb DATABASE_NAME
    8.25 --- psql -v ON_ERROR_STOP=1 -f core.sql DATABASE_NAME
    8.26 --- psql -v ON_ERROR_STOP=1 -f tmp.sql DATABASE_NAME
    8.27 --- rm tmp.sql
    8.28 -