1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.beta16-beta17.sql	Sat Feb 06 03:37:55 2010 +0100
     1.3 @@ -0,0 +1,165 @@
     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;