liquid_feedback_core

annotate update/core-update.beta16-beta17.sql @ 94:fb9688f31740

find changesets by author, revision, files, or words in the commit message
Corrected "delete_member" and "delete_private_data" functions to refer to "ignored_issue" table instead of non-existing "ignored_voting" table; Delete "ignored_issue" entries in "clean_issue" function as well
author jbe
date Sun Oct 31 02:38:05 2010 +0200 (2010-10-31)
parents f0460e206bc6
children
rev   line source
jbe@25 1 BEGIN;
jbe@16 2
jbe@16 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@16 4 SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL))
jbe@16 5 AS "subquery"("string", "major", "minor", "revision");
jbe@16 6
jbe@16 7 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
jbe@16 8
jbe@16 9 CREATE TABLE "setting_map" (
jbe@16 10 PRIMARY KEY ("member_id", "key", "subkey"),
jbe@16 11 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@16 12 "key" TEXT NOT NULL,
jbe@16 13 "subkey" TEXT NOT NULL,
jbe@16 14 "value" TEXT NOT NULL );
jbe@16 15 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
jbe@16 16
jbe@16 17 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
jbe@16 18
jbe@16 19 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@16 20 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
jbe@16 21 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
jbe@16 22
jbe@16 23 CREATE INDEX "issue_created_idx" ON "issue" ("created");
jbe@16 24 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
jbe@16 25 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
jbe@16 26 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
jbe@16 27 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
jbe@16 28 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
jbe@16 29 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
jbe@16 30 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
jbe@16 31 CREATE INDEX "draft_created_idx" ON "draft" ("created");
jbe@16 32 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
jbe@16 33
jbe@16 34 CREATE TYPE "timeline_event" AS ENUM (
jbe@16 35 'issue_created',
jbe@16 36 'issue_canceled',
jbe@16 37 'issue_accepted',
jbe@16 38 'issue_half_frozen',
jbe@16 39 'issue_finished_without_voting',
jbe@16 40 'issue_voting_started',
jbe@16 41 'issue_finished_after_voting',
jbe@16 42 'initiative_created',
jbe@16 43 'initiative_revoked',
jbe@16 44 'draft_created',
jbe@16 45 'suggestion_created');
jbe@16 46
jbe@16 47 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
jbe@16 48
jbe@16 49 CREATE VIEW "timeline_issue" AS
jbe@16 50 SELECT
jbe@16 51 "created" AS "occurrence",
jbe@16 52 'issue_created'::"timeline_event" AS "event",
jbe@16 53 "id" AS "issue_id"
jbe@16 54 FROM "issue"
jbe@16 55 UNION ALL
jbe@16 56 SELECT
jbe@16 57 "closed" AS "occurrence",
jbe@16 58 'issue_canceled'::"timeline_event" AS "event",
jbe@16 59 "id" AS "issue_id"
jbe@16 60 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
jbe@16 61 UNION ALL
jbe@16 62 SELECT
jbe@16 63 "accepted" AS "occurrence",
jbe@16 64 'issue_accepted'::"timeline_event" AS "event",
jbe@16 65 "id" AS "issue_id"
jbe@16 66 FROM "issue" WHERE "accepted" NOTNULL
jbe@16 67 UNION ALL
jbe@16 68 SELECT
jbe@16 69 "half_frozen" AS "occurrence",
jbe@16 70 'issue_half_frozen'::"timeline_event" AS "event",
jbe@16 71 "id" AS "issue_id"
jbe@16 72 FROM "issue" WHERE "half_frozen" NOTNULL
jbe@16 73 UNION ALL
jbe@16 74 SELECT
jbe@16 75 "fully_frozen" AS "occurrence",
jbe@16 76 'issue_voting_started'::"timeline_event" AS "event",
jbe@16 77 "id" AS "issue_id"
jbe@16 78 FROM "issue"
jbe@16 79 WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen"
jbe@16 80 UNION ALL
jbe@16 81 SELECT
jbe@16 82 "closed" AS "occurrence",
jbe@16 83 CASE WHEN "fully_frozen" = "closed" THEN
jbe@16 84 'issue_finished_without_voting'::"timeline_event"
jbe@16 85 ELSE
jbe@16 86 'issue_finished_after_voting'::"timeline_event"
jbe@16 87 END AS "event",
jbe@16 88 "id" AS "issue_id"
jbe@16 89 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
jbe@16 90
jbe@16 91 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
jbe@16 92
jbe@16 93 CREATE VIEW "timeline_initiative" AS
jbe@16 94 SELECT
jbe@16 95 "created" AS "occurrence",
jbe@16 96 'initiative_created'::"timeline_event" AS "event",
jbe@16 97 "id" AS "initiative_id"
jbe@16 98 FROM "initiative"
jbe@16 99 UNION ALL
jbe@16 100 SELECT
jbe@16 101 "revoked" AS "occurrence",
jbe@16 102 'initiative_revoked'::"timeline_event" AS "event",
jbe@16 103 "id" AS "initiative_id"
jbe@16 104 FROM "initiative" WHERE "revoked" NOTNULL;
jbe@16 105
jbe@16 106 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
jbe@16 107
jbe@16 108 CREATE VIEW "timeline_draft" AS
jbe@16 109 SELECT
jbe@16 110 "created" AS "occurrence",
jbe@16 111 'draft_created'::"timeline_event" AS "event",
jbe@16 112 "id" AS "draft_id"
jbe@16 113 FROM "draft";
jbe@16 114
jbe@16 115 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
jbe@16 116
jbe@16 117 CREATE VIEW "timeline_suggestion" AS
jbe@16 118 SELECT
jbe@16 119 "created" AS "occurrence",
jbe@16 120 'suggestion_created'::"timeline_event" AS "event",
jbe@16 121 "id" AS "suggestion_id"
jbe@16 122 FROM "suggestion";
jbe@16 123
jbe@16 124 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
jbe@16 125
jbe@16 126 CREATE VIEW "timeline" AS
jbe@16 127 SELECT
jbe@16 128 "occurrence",
jbe@16 129 "event",
jbe@16 130 "issue_id",
jbe@16 131 NULL AS "initiative_id",
jbe@16 132 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
jbe@16 133 NULL::INT8 AS "suggestion_id"
jbe@16 134 FROM "timeline_issue"
jbe@16 135 UNION ALL
jbe@16 136 SELECT
jbe@16 137 "occurrence",
jbe@16 138 "event",
jbe@16 139 NULL AS "issue_id",
jbe@16 140 "initiative_id",
jbe@16 141 NULL AS "draft_id",
jbe@16 142 NULL AS "suggestion_id"
jbe@16 143 FROM "timeline_initiative"
jbe@16 144 UNION ALL
jbe@16 145 SELECT
jbe@16 146 "occurrence",
jbe@16 147 "event",
jbe@16 148 NULL AS "issue_id",
jbe@16 149 NULL AS "initiative_id",
jbe@16 150 "draft_id",
jbe@16 151 NULL AS "suggestion_id"
jbe@16 152 FROM "timeline_draft"
jbe@16 153 UNION ALL
jbe@16 154 SELECT
jbe@16 155 "occurrence",
jbe@16 156 "event",
jbe@16 157 NULL AS "issue_id",
jbe@16 158 NULL AS "initiative_id",
jbe@16 159 NULL AS "draft_id",
jbe@16 160 "suggestion_id"
jbe@16 161 FROM "timeline_suggestion";
jbe@16 162
jbe@16 163 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
jbe@16 164
jbe@25 165 COMMIT;

Impressum / About Us