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

     2.1 --- a/core-update.beta17-beta18.sql	Sat Feb 06 03:34:09 2010 +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 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/update/core-update.beta16-beta17.sql	Sat Feb 06 03:37:55 2010 +0100
     3.3 @@ -0,0 +1,165 @@
     3.4 +BEGIN;
     3.5 +
     3.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     3.7 +  SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL))
     3.8 +  AS "subquery"("string", "major", "minor", "revision");
     3.9 +
    3.10 +COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
    3.11 +
    3.12 +CREATE TABLE "setting_map" (
    3.13 +        PRIMARY KEY ("member_id", "key", "subkey"),
    3.14 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    3.15 +        "key"                   TEXT            NOT NULL,
    3.16 +        "subkey"                TEXT            NOT NULL,
    3.17 +        "value"                 TEXT            NOT NULL );
    3.18 +CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
    3.19 +
    3.20 +COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
    3.21 +
    3.22 +COMMENT ON COLUMN "setting_map"."key"    IS 'Name of the setting, preceded by a frontend specific prefix';
    3.23 +COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
    3.24 +COMMENT ON COLUMN "setting_map"."value"  IS 'Value of a map entry';
    3.25 +
    3.26 +CREATE INDEX "issue_created_idx" ON "issue" ("created");
    3.27 +CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
    3.28 +CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
    3.29 +CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
    3.30 +CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
    3.31 +CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
    3.32 +CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
    3.33 +CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
    3.34 +CREATE INDEX "draft_created_idx" ON "draft" ("created");
    3.35 +CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
    3.36 +
    3.37 +CREATE TYPE "timeline_event" AS ENUM (
    3.38 +  'issue_created',
    3.39 +  'issue_canceled',
    3.40 +  'issue_accepted',
    3.41 +  'issue_half_frozen',
    3.42 +  'issue_finished_without_voting',
    3.43 +  'issue_voting_started',
    3.44 +  'issue_finished_after_voting',
    3.45 +  'initiative_created',
    3.46 +  'initiative_revoked',
    3.47 +  'draft_created',
    3.48 +  'suggestion_created');
    3.49 +
    3.50 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
    3.51 +
    3.52 +CREATE VIEW "timeline_issue" AS
    3.53 +    SELECT
    3.54 +      "created" AS "occurrence",
    3.55 +      'issue_created'::"timeline_event" AS "event",
    3.56 +      "id" AS "issue_id"
    3.57 +    FROM "issue"
    3.58 +  UNION ALL
    3.59 +    SELECT
    3.60 +      "closed" AS "occurrence",
    3.61 +      'issue_canceled'::"timeline_event" AS "event",
    3.62 +      "id" AS "issue_id"
    3.63 +    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
    3.64 +  UNION ALL
    3.65 +    SELECT
    3.66 +      "accepted" AS "occurrence",
    3.67 +      'issue_accepted'::"timeline_event" AS "event",
    3.68 +      "id" AS "issue_id"
    3.69 +    FROM "issue" WHERE "accepted" NOTNULL
    3.70 +  UNION ALL
    3.71 +    SELECT
    3.72 +      "half_frozen" AS "occurrence",
    3.73 +      'issue_half_frozen'::"timeline_event" AS "event",
    3.74 +      "id" AS "issue_id"
    3.75 +    FROM "issue" WHERE "half_frozen" NOTNULL
    3.76 +  UNION ALL
    3.77 +    SELECT
    3.78 +      "fully_frozen" AS "occurrence",
    3.79 +      'issue_voting_started'::"timeline_event" AS "event",
    3.80 +      "id" AS "issue_id"
    3.81 +    FROM "issue"
    3.82 +    WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen"
    3.83 +  UNION ALL
    3.84 +    SELECT
    3.85 +      "closed" AS "occurrence",
    3.86 +      CASE WHEN "fully_frozen" = "closed" THEN
    3.87 +        'issue_finished_without_voting'::"timeline_event"
    3.88 +      ELSE
    3.89 +        'issue_finished_after_voting'::"timeline_event"
    3.90 +      END AS "event",
    3.91 +      "id" AS "issue_id"
    3.92 +    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
    3.93 +
    3.94 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
    3.95 +
    3.96 +CREATE VIEW "timeline_initiative" AS
    3.97 +    SELECT
    3.98 +      "created" AS "occurrence",
    3.99 +      'initiative_created'::"timeline_event" AS "event",
   3.100 +      "id" AS "initiative_id"
   3.101 +    FROM "initiative"
   3.102 +  UNION ALL
   3.103 +    SELECT
   3.104 +      "revoked" AS "occurrence",
   3.105 +      'initiative_revoked'::"timeline_event" AS "event",
   3.106 +      "id" AS "initiative_id"
   3.107 +    FROM "initiative" WHERE "revoked" NOTNULL;
   3.108 +
   3.109 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
   3.110 +
   3.111 +CREATE VIEW "timeline_draft" AS
   3.112 +  SELECT
   3.113 +    "created" AS "occurrence",
   3.114 +    'draft_created'::"timeline_event" AS "event",
   3.115 +    "id" AS "draft_id"
   3.116 +  FROM "draft";
   3.117 +
   3.118 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
   3.119 +
   3.120 +CREATE VIEW "timeline_suggestion" AS
   3.121 +  SELECT
   3.122 +    "created" AS "occurrence",
   3.123 +    'suggestion_created'::"timeline_event" AS "event",
   3.124 +    "id" AS "suggestion_id"
   3.125 +  FROM "suggestion";
   3.126 +
   3.127 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
   3.128 +
   3.129 +CREATE VIEW "timeline" AS
   3.130 +    SELECT
   3.131 +      "occurrence",
   3.132 +      "event",
   3.133 +      "issue_id",
   3.134 +      NULL AS "initiative_id",
   3.135 +      NULL::INT8 AS "draft_id",  -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
   3.136 +      NULL::INT8 AS "suggestion_id"
   3.137 +    FROM "timeline_issue"
   3.138 +  UNION ALL
   3.139 +    SELECT
   3.140 +      "occurrence",
   3.141 +      "event",
   3.142 +      NULL AS "issue_id",
   3.143 +      "initiative_id",
   3.144 +      NULL AS "draft_id",
   3.145 +      NULL AS "suggestion_id"
   3.146 +    FROM "timeline_initiative"
   3.147 +  UNION ALL
   3.148 +    SELECT
   3.149 +      "occurrence",
   3.150 +      "event",
   3.151 +      NULL AS "issue_id",
   3.152 +      NULL AS "initiative_id",
   3.153 +      "draft_id",
   3.154 +      NULL AS "suggestion_id"
   3.155 +    FROM "timeline_draft"
   3.156 +  UNION ALL
   3.157 +    SELECT
   3.158 +      "occurrence",
   3.159 +      "event",
   3.160 +      NULL AS "issue_id",
   3.161 +      NULL AS "initiative_id",
   3.162 +      NULL AS "draft_id",
   3.163 +      "suggestion_id"
   3.164 +    FROM "timeline_suggestion";
   3.165 +
   3.166 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
   3.167 +
   3.168 +COMMIT;

     4.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     4.2 +++ b/update/core-update.beta17-beta18.sql	Sat Feb 06 03:37:55 2010 +0100
     4.3 @@ -0,0 +1,50 @@
     4.4 +BEGIN;
     4.5 +
     4.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     4.7 +  SELECT * FROM (VALUES ('beta18', NULL, NULL, NULL))
     4.8 +  AS "subquery"("string", "major", "minor", "revision");
     4.9 +
    4.10 +CREATE OR REPLACE VIEW "timeline_issue" AS
    4.11 +    SELECT
    4.12 +      "created" AS "occurrence",
    4.13 +      'issue_created'::"timeline_event" AS "event",
    4.14 +      "id" AS "issue_id"
    4.15 +    FROM "issue"
    4.16 +  UNION ALL
    4.17 +    SELECT
    4.18 +      "closed" AS "occurrence",
    4.19 +      'issue_canceled'::"timeline_event" AS "event",
    4.20 +      "id" AS "issue_id"
    4.21 +    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
    4.22 +  UNION ALL
    4.23 +    SELECT
    4.24 +      "accepted" AS "occurrence",
    4.25 +      'issue_accepted'::"timeline_event" AS "event",
    4.26 +      "id" AS "issue_id"
    4.27 +    FROM "issue" WHERE "accepted" NOTNULL
    4.28 +  UNION ALL
    4.29 +    SELECT
    4.30 +      "half_frozen" AS "occurrence",
    4.31 +      'issue_half_frozen'::"timeline_event" AS "event",
    4.32 +      "id" AS "issue_id"
    4.33 +    FROM "issue" WHERE "half_frozen" NOTNULL
    4.34 +  UNION ALL
    4.35 +    SELECT
    4.36 +      "fully_frozen" AS "occurrence",
    4.37 +      'issue_voting_started'::"timeline_event" AS "event",
    4.38 +      "id" AS "issue_id"
    4.39 +    FROM "issue"
    4.40 +    WHERE "fully_frozen" NOTNULL
    4.41 +    AND ("closed" ISNULL OR "closed" != "fully_frozen")
    4.42 +  UNION ALL
    4.43 +    SELECT
    4.44 +      "closed" AS "occurrence",
    4.45 +      CASE WHEN "fully_frozen" = "closed" THEN
    4.46 +        'issue_finished_without_voting'::"timeline_event"
    4.47 +      ELSE
    4.48 +        'issue_finished_after_voting'::"timeline_event"
    4.49 +      END AS "event",
    4.50 +      "id" AS "issue_id"
    4.51 +    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
    4.52 +
    4.53 +COMMIT;