1.1 --- a/core.sql	Wed Jan 13 12:00:00 2010 +0100
     1.2 +++ b/core.sql	Fri Jan 22 12:00:00 2010 +0100
     1.3 @@ -6,7 +6,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('beta16', NULL, NULL, NULL))
     1.8 +  SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -150,11 +150,26 @@
    1.13          "value"                 TEXT            NOT NULL );
    1.14  CREATE INDEX "setting_key_idx" ON "setting" ("key");
    1.15  
    1.16 -COMMENT ON TABLE "setting" IS 'Place to store frontend specific member settings';
    1.17 +COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
    1.18  
    1.19  COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
    1.20  
    1.21  
    1.22 +CREATE TABLE "setting_map" (
    1.23 +        PRIMARY KEY ("member_id", "key", "subkey"),
    1.24 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.25 +        "key"                   TEXT            NOT NULL,
    1.26 +        "subkey"                TEXT            NOT NULL,
    1.27 +        "value"                 TEXT            NOT NULL );
    1.28 +CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
    1.29 +
    1.30 +COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
    1.31 +
    1.32 +COMMENT ON COLUMN "setting_map"."key"    IS 'Name of the setting, preceded by a frontend specific prefix';
    1.33 +COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
    1.34 +COMMENT ON COLUMN "setting_map"."value"  IS 'Value of a map entry';
    1.35 +
    1.36 +
    1.37  CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
    1.38  
    1.39  COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
    1.40 @@ -329,7 +344,13 @@
    1.41            CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
    1.42  CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
    1.43  CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
    1.44 +CREATE INDEX "issue_created_idx" ON "issue" ("created");
    1.45 +CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
    1.46 +CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
    1.47 +CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
    1.48 +CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
    1.49  CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
    1.50 +CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
    1.51  
    1.52  COMMENT ON TABLE "issue" IS 'Groups of initiatives';
    1.53  
    1.54 @@ -375,6 +396,8 @@
    1.55            CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
    1.56          CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
    1.57            CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
    1.58 +CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
    1.59 +CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
    1.60  CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
    1.61  CREATE TRIGGER "update_text_search_data"
    1.62    BEFORE INSERT OR UPDATE ON "initiative"
    1.63 @@ -406,6 +429,7 @@
    1.64          "formatting_engine"     TEXT,
    1.65          "content"               TEXT            NOT NULL,
    1.66          "text_search_data"      TSVECTOR );
    1.67 +CREATE INDEX "draft_created_idx" ON "draft" ("created");
    1.68  CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
    1.69  CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
    1.70  CREATE TRIGGER "update_text_search_data"
    1.71 @@ -436,6 +460,7 @@
    1.72          "plus1_fulfilled_count"    INT4,
    1.73          "plus2_unfulfilled_count"  INT4,
    1.74          "plus2_fulfilled_count"    INT4 );
    1.75 +CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
    1.76  CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
    1.77  CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
    1.78  CREATE TRIGGER "update_text_search_data"
    1.79 @@ -1345,6 +1370,143 @@
    1.80  COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
    1.81  
    1.82  
    1.83 +CREATE TYPE "timeline_event" AS ENUM (
    1.84 +  'issue_created',
    1.85 +  'issue_canceled',
    1.86 +  'issue_accepted',
    1.87 +  'issue_half_frozen',
    1.88 +  'issue_finished_without_voting',
    1.89 +  'issue_voting_started',
    1.90 +  'issue_finished_after_voting',
    1.91 +  'initiative_created',
    1.92 +  'initiative_revoked',
    1.93 +  'draft_created',
    1.94 +  'suggestion_created');
    1.95 +
    1.96 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
    1.97 +
    1.98 +
    1.99 +CREATE VIEW "timeline_issue" AS
   1.100 +    SELECT
   1.101 +      "created" AS "occurrence",
   1.102 +      'issue_created'::"timeline_event" AS "event",
   1.103 +      "id" AS "issue_id"
   1.104 +    FROM "issue"
   1.105 +  UNION ALL
   1.106 +    SELECT
   1.107 +      "closed" AS "occurrence",
   1.108 +      'issue_canceled'::"timeline_event" AS "event",
   1.109 +      "id" AS "issue_id"
   1.110 +    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
   1.111 +  UNION ALL
   1.112 +    SELECT
   1.113 +      "accepted" AS "occurrence",
   1.114 +      'issue_accepted'::"timeline_event" AS "event",
   1.115 +      "id" AS "issue_id"
   1.116 +    FROM "issue" WHERE "accepted" NOTNULL
   1.117 +  UNION ALL
   1.118 +    SELECT
   1.119 +      "half_frozen" AS "occurrence",
   1.120 +      'issue_half_frozen'::"timeline_event" AS "event",
   1.121 +      "id" AS "issue_id"
   1.122 +    FROM "issue" WHERE "half_frozen" NOTNULL
   1.123 +  UNION ALL
   1.124 +    SELECT
   1.125 +      "fully_frozen" AS "occurrence",
   1.126 +      'issue_voting_started'::"timeline_event" AS "event",
   1.127 +      "id" AS "issue_id"
   1.128 +    FROM "issue"
   1.129 +    WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen"
   1.130 +  UNION ALL
   1.131 +    SELECT
   1.132 +      "closed" AS "occurrence",
   1.133 +      CASE WHEN "fully_frozen" = "closed" THEN
   1.134 +        'issue_finished_without_voting'::"timeline_event"
   1.135 +      ELSE
   1.136 +        'issue_finished_after_voting'::"timeline_event"
   1.137 +      END AS "event",
   1.138 +      "id" AS "issue_id"
   1.139 +    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
   1.140 +
   1.141 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
   1.142 +
   1.143 +
   1.144 +CREATE VIEW "timeline_initiative" AS
   1.145 +    SELECT
   1.146 +      "created" AS "occurrence",
   1.147 +      'initiative_created'::"timeline_event" AS "event",
   1.148 +      "id" AS "initiative_id"
   1.149 +    FROM "initiative"
   1.150 +  UNION ALL
   1.151 +    SELECT
   1.152 +      "revoked" AS "occurrence",
   1.153 +      'initiative_revoked'::"timeline_event" AS "event",
   1.154 +      "id" AS "initiative_id"
   1.155 +    FROM "initiative" WHERE "revoked" NOTNULL;
   1.156 +
   1.157 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
   1.158 +
   1.159 +
   1.160 +CREATE VIEW "timeline_draft" AS
   1.161 +  SELECT
   1.162 +    "created" AS "occurrence",
   1.163 +    'draft_created'::"timeline_event" AS "event",
   1.164 +    "id" AS "draft_id"
   1.165 +  FROM "draft";
   1.166 +
   1.167 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
   1.168 +
   1.169 +
   1.170 +CREATE VIEW "timeline_suggestion" AS
   1.171 +  SELECT
   1.172 +    "created" AS "occurrence",
   1.173 +    'suggestion_created'::"timeline_event" AS "event",
   1.174 +    "id" AS "suggestion_id"
   1.175 +  FROM "suggestion";
   1.176 +
   1.177 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
   1.178 +
   1.179 +
   1.180 +CREATE VIEW "timeline" AS
   1.181 +    SELECT
   1.182 +      "occurrence",
   1.183 +      "event",
   1.184 +      "issue_id",
   1.185 +      NULL AS "initiative_id",
   1.186 +      NULL::INT8 AS "draft_id",  -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
   1.187 +      NULL::INT8 AS "suggestion_id"
   1.188 +    FROM "timeline_issue"
   1.189 +  UNION ALL
   1.190 +    SELECT
   1.191 +      "occurrence",
   1.192 +      "event",
   1.193 +      NULL AS "issue_id",
   1.194 +      "initiative_id",
   1.195 +      NULL AS "draft_id",
   1.196 +      NULL AS "suggestion_id"
   1.197 +    FROM "timeline_initiative"
   1.198 +  UNION ALL
   1.199 +    SELECT
   1.200 +      "occurrence",
   1.201 +      "event",
   1.202 +      NULL AS "issue_id",
   1.203 +      NULL AS "initiative_id",
   1.204 +      "draft_id",
   1.205 +      NULL AS "suggestion_id"
   1.206 +    FROM "timeline_draft"
   1.207 +  UNION ALL
   1.208 +    SELECT
   1.209 +      "occurrence",
   1.210 +      "event",
   1.211 +      NULL AS "issue_id",
   1.212 +      NULL AS "initiative_id",
   1.213 +      NULL AS "draft_id",
   1.214 +      "suggestion_id"
   1.215 +    FROM "timeline_suggestion";
   1.216 +
   1.217 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
   1.218 +
   1.219 +
   1.220  
   1.221  --------------------------------------------------
   1.222  -- Set returning function for delegation chains --