1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/core-update.beta16-beta17.sql	Fri Jan 22 12:00:00 2010 +0100
     1.3 @@ -0,0 +1,163 @@
     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.sql	Wed Jan 13 12:00:00 2010 +0100
     2.2 +++ b/core.sql	Fri Jan 22 12:00:00 2010 +0100
     2.3 @@ -6,7 +6,7 @@
     2.4  BEGIN;
     2.5  
     2.6  CREATE VIEW "liquid_feedback_version" AS
     2.7 -  SELECT * FROM (VALUES ('beta16', NULL, NULL, NULL))
     2.8 +  SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL))
     2.9    AS "subquery"("string", "major", "minor", "revision");
    2.10  
    2.11  
    2.12 @@ -150,11 +150,26 @@
    2.13          "value"                 TEXT            NOT NULL );
    2.14  CREATE INDEX "setting_key_idx" ON "setting" ("key");
    2.15  
    2.16 -COMMENT ON TABLE "setting" IS 'Place to store frontend specific member settings';
    2.17 +COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
    2.18  
    2.19  COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
    2.20  
    2.21  
    2.22 +CREATE TABLE "setting_map" (
    2.23 +        PRIMARY KEY ("member_id", "key", "subkey"),
    2.24 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.25 +        "key"                   TEXT            NOT NULL,
    2.26 +        "subkey"                TEXT            NOT NULL,
    2.27 +        "value"                 TEXT            NOT NULL );
    2.28 +CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
    2.29 +
    2.30 +COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
    2.31 +
    2.32 +COMMENT ON COLUMN "setting_map"."key"    IS 'Name of the setting, preceded by a frontend specific prefix';
    2.33 +COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
    2.34 +COMMENT ON COLUMN "setting_map"."value"  IS 'Value of a map entry';
    2.35 +
    2.36 +
    2.37  CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
    2.38  
    2.39  COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
    2.40 @@ -329,7 +344,13 @@
    2.41            CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
    2.42  CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
    2.43  CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
    2.44 +CREATE INDEX "issue_created_idx" ON "issue" ("created");
    2.45 +CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
    2.46 +CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
    2.47 +CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
    2.48 +CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
    2.49  CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
    2.50 +CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
    2.51  
    2.52  COMMENT ON TABLE "issue" IS 'Groups of initiatives';
    2.53  
    2.54 @@ -375,6 +396,8 @@
    2.55            CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
    2.56          CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
    2.57            CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
    2.58 +CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
    2.59 +CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
    2.60  CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
    2.61  CREATE TRIGGER "update_text_search_data"
    2.62    BEFORE INSERT OR UPDATE ON "initiative"
    2.63 @@ -406,6 +429,7 @@
    2.64          "formatting_engine"     TEXT,
    2.65          "content"               TEXT            NOT NULL,
    2.66          "text_search_data"      TSVECTOR );
    2.67 +CREATE INDEX "draft_created_idx" ON "draft" ("created");
    2.68  CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
    2.69  CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
    2.70  CREATE TRIGGER "update_text_search_data"
    2.71 @@ -436,6 +460,7 @@
    2.72          "plus1_fulfilled_count"    INT4,
    2.73          "plus2_unfulfilled_count"  INT4,
    2.74          "plus2_fulfilled_count"    INT4 );
    2.75 +CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
    2.76  CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
    2.77  CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
    2.78  CREATE TRIGGER "update_text_search_data"
    2.79 @@ -1345,6 +1370,143 @@
    2.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.';
    2.81  
    2.82  
    2.83 +CREATE TYPE "timeline_event" AS ENUM (
    2.84 +  'issue_created',
    2.85 +  'issue_canceled',
    2.86 +  'issue_accepted',
    2.87 +  'issue_half_frozen',
    2.88 +  'issue_finished_without_voting',
    2.89 +  'issue_voting_started',
    2.90 +  'issue_finished_after_voting',
    2.91 +  'initiative_created',
    2.92 +  'initiative_revoked',
    2.93 +  'draft_created',
    2.94 +  'suggestion_created');
    2.95 +
    2.96 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
    2.97 +
    2.98 +
    2.99 +CREATE VIEW "timeline_issue" AS
   2.100 +    SELECT
   2.101 +      "created" AS "occurrence",
   2.102 +      'issue_created'::"timeline_event" AS "event",
   2.103 +      "id" AS "issue_id"
   2.104 +    FROM "issue"
   2.105 +  UNION ALL
   2.106 +    SELECT
   2.107 +      "closed" AS "occurrence",
   2.108 +      'issue_canceled'::"timeline_event" AS "event",
   2.109 +      "id" AS "issue_id"
   2.110 +    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
   2.111 +  UNION ALL
   2.112 +    SELECT
   2.113 +      "accepted" AS "occurrence",
   2.114 +      'issue_accepted'::"timeline_event" AS "event",
   2.115 +      "id" AS "issue_id"
   2.116 +    FROM "issue" WHERE "accepted" NOTNULL
   2.117 +  UNION ALL
   2.118 +    SELECT
   2.119 +      "half_frozen" AS "occurrence",
   2.120 +      'issue_half_frozen'::"timeline_event" AS "event",
   2.121 +      "id" AS "issue_id"
   2.122 +    FROM "issue" WHERE "half_frozen" NOTNULL
   2.123 +  UNION ALL
   2.124 +    SELECT
   2.125 +      "fully_frozen" AS "occurrence",
   2.126 +      'issue_voting_started'::"timeline_event" AS "event",
   2.127 +      "id" AS "issue_id"
   2.128 +    FROM "issue"
   2.129 +    WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen"
   2.130 +  UNION ALL
   2.131 +    SELECT
   2.132 +      "closed" AS "occurrence",
   2.133 +      CASE WHEN "fully_frozen" = "closed" THEN
   2.134 +        'issue_finished_without_voting'::"timeline_event"
   2.135 +      ELSE
   2.136 +        'issue_finished_after_voting'::"timeline_event"
   2.137 +      END AS "event",
   2.138 +      "id" AS "issue_id"
   2.139 +    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
   2.140 +
   2.141 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
   2.142 +
   2.143 +
   2.144 +CREATE VIEW "timeline_initiative" AS
   2.145 +    SELECT
   2.146 +      "created" AS "occurrence",
   2.147 +      'initiative_created'::"timeline_event" AS "event",
   2.148 +      "id" AS "initiative_id"
   2.149 +    FROM "initiative"
   2.150 +  UNION ALL
   2.151 +    SELECT
   2.152 +      "revoked" AS "occurrence",
   2.153 +      'initiative_revoked'::"timeline_event" AS "event",
   2.154 +      "id" AS "initiative_id"
   2.155 +    FROM "initiative" WHERE "revoked" NOTNULL;
   2.156 +
   2.157 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
   2.158 +
   2.159 +
   2.160 +CREATE VIEW "timeline_draft" AS
   2.161 +  SELECT
   2.162 +    "created" AS "occurrence",
   2.163 +    'draft_created'::"timeline_event" AS "event",
   2.164 +    "id" AS "draft_id"
   2.165 +  FROM "draft";
   2.166 +
   2.167 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
   2.168 +
   2.169 +
   2.170 +CREATE VIEW "timeline_suggestion" AS
   2.171 +  SELECT
   2.172 +    "created" AS "occurrence",
   2.173 +    'suggestion_created'::"timeline_event" AS "event",
   2.174 +    "id" AS "suggestion_id"
   2.175 +  FROM "suggestion";
   2.176 +
   2.177 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
   2.178 +
   2.179 +
   2.180 +CREATE VIEW "timeline" AS
   2.181 +    SELECT
   2.182 +      "occurrence",
   2.183 +      "event",
   2.184 +      "issue_id",
   2.185 +      NULL AS "initiative_id",
   2.186 +      NULL::INT8 AS "draft_id",  -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
   2.187 +      NULL::INT8 AS "suggestion_id"
   2.188 +    FROM "timeline_issue"
   2.189 +  UNION ALL
   2.190 +    SELECT
   2.191 +      "occurrence",
   2.192 +      "event",
   2.193 +      NULL AS "issue_id",
   2.194 +      "initiative_id",
   2.195 +      NULL AS "draft_id",
   2.196 +      NULL AS "suggestion_id"
   2.197 +    FROM "timeline_initiative"
   2.198 +  UNION ALL
   2.199 +    SELECT
   2.200 +      "occurrence",
   2.201 +      "event",
   2.202 +      NULL AS "issue_id",
   2.203 +      NULL AS "initiative_id",
   2.204 +      "draft_id",
   2.205 +      NULL AS "suggestion_id"
   2.206 +    FROM "timeline_draft"
   2.207 +  UNION ALL
   2.208 +    SELECT
   2.209 +      "occurrence",
   2.210 +      "event",
   2.211 +      NULL AS "issue_id",
   2.212 +      NULL AS "initiative_id",
   2.213 +      NULL AS "draft_id",
   2.214 +      "suggestion_id"
   2.215 +    FROM "timeline_suggestion";
   2.216 +
   2.217 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
   2.218 +
   2.219 +
   2.220  
   2.221  --------------------------------------------------
   2.222  -- Set returning function for delegation chains --