liquid_feedback_core
changeset 224:06b9c347fd61
Update script for v2.0.4
author | jbe |
---|---|
date | Sat Feb 25 16:37:18 2012 +0100 (2012-02-25) |
parents | ae08a20808d4 |
children | 18e1cd384528 |
files | core.sql update/core-update.v2.0.3-v2.0.4.sql |
line diff
1.1 --- a/core.sql Sat Feb 25 15:22:34 2012 +0100 1.2 +++ b/core.sql Sat Feb 25 16:37:18 2012 +0100 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('2.0.3', 2, 0, 3)) 1.8 + SELECT * FROM (VALUES ('2.0.4', 2, 0, 4)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v2.0.3-v2.0.4.sql Sat Feb 25 16:37:18 2012 +0100 2.3 @@ -0,0 +1,115 @@ 2.4 +BEGIN; 2.5 + 2.6 +-- update version number: 2.7 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.8 + SELECT * FROM (VALUES ('2.0.4', 2, 0, 4)) 2.9 + AS "subquery"("string", "major", "minor", "revision"); 2.10 + 2.11 +-- add table "session": 2.12 +CREATE TABLE "session" ( 2.13 + "ident" TEXT PRIMARY KEY, 2.14 + "additional_secret" TEXT, 2.15 + "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours', 2.16 + "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL, 2.17 + "lang" TEXT ); 2.18 +CREATE INDEX "session_expiry_idx" ON "session" ("expiry"); 2.19 +COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer'; 2.20 +COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)'; 2.21 +COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks'; 2.22 +COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in'; 2.23 +COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language'; 2.24 + 2.25 +-- add column "lang" to table "member": 2.26 +ALTER TABLE "member" ADD COLUMN "lang" TEXT; 2.27 +COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member'; 2.28 + 2.29 +-- drop view "pending_notification": 2.30 +DROP VIEW "pending_notification"; 2.31 + 2.32 +-- remove column "notify_event_id" of table "member": 2.33 +ALTER TABLE "member" DROP COLUMN "notify_event_id"; 2.34 + 2.35 +-- add table "notification_sent": 2.36 +CREATE TABLE "notification_sent" ( 2.37 + "event_id" INT8 NOT NULL ); 2.38 +CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1)); 2.39 +COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out'; 2.40 +COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.'; 2.41 + 2.42 +-- add view "selected_event_seen_by_member": 2.43 +CREATE VIEW "selected_event_seen_by_member" AS 2.44 + SELECT 2.45 + "member"."id" AS "seen_by_member_id", 2.46 + CASE WHEN "event"."state" IN ( 2.47 + 'voting', 2.48 + 'finished_without_winner', 2.49 + 'finished_with_winner' 2.50 + ) THEN 2.51 + 'voting'::"notify_level" 2.52 + ELSE 2.53 + CASE WHEN "event"."state" IN ( 2.54 + 'verification', 2.55 + 'canceled_after_revocation_during_verification', 2.56 + 'canceled_no_initiative_admitted' 2.57 + ) THEN 2.58 + 'verification'::"notify_level" 2.59 + ELSE 2.60 + CASE WHEN "event"."state" IN ( 2.61 + 'discussion', 2.62 + 'canceled_after_revocation_during_discussion' 2.63 + ) THEN 2.64 + 'discussion'::"notify_level" 2.65 + ELSE 2.66 + 'all'::"notify_level" 2.67 + END 2.68 + END 2.69 + END AS "notify_level", 2.70 + "event".* 2.71 + FROM "member" CROSS JOIN "event" 2.72 + LEFT JOIN "issue" 2.73 + ON "event"."issue_id" = "issue"."id" 2.74 + LEFT JOIN "membership" 2.75 + ON "member"."id" = "membership"."member_id" 2.76 + AND "issue"."area_id" = "membership"."area_id" 2.77 + LEFT JOIN "interest" 2.78 + ON "member"."id" = "interest"."member_id" 2.79 + AND "event"."issue_id" = "interest"."issue_id" 2.80 + LEFT JOIN "supporter" 2.81 + ON "member"."id" = "supporter"."member_id" 2.82 + AND "event"."initiative_id" = "supporter"."initiative_id" 2.83 + LEFT JOIN "ignored_member" 2.84 + ON "member"."id" = "ignored_member"."member_id" 2.85 + AND "event"."member_id" = "ignored_member"."other_member_id" 2.86 + LEFT JOIN "ignored_initiative" 2.87 + ON "member"."id" = "ignored_initiative"."member_id" 2.88 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 2.89 + WHERE ( 2.90 + ( "member"."notify_level" >= 'all' ) OR 2.91 + ( "member"."notify_level" >= 'voting' AND 2.92 + "event"."state" IN ( 2.93 + 'voting', 2.94 + 'finished_without_winner', 2.95 + 'finished_with_winner' ) ) OR 2.96 + ( "member"."notify_level" >= 'verification' AND 2.97 + "event"."state" IN ( 2.98 + 'verification', 2.99 + 'canceled_after_revocation_during_verification', 2.100 + 'canceled_no_initiative_admitted' ) ) OR 2.101 + ( "member"."notify_level" >= 'discussion' AND 2.102 + "event"."state" IN ( 2.103 + 'discussion', 2.104 + 'canceled_after_revocation_during_discussion' ) ) ) 2.105 + AND ( 2.106 + "supporter"."member_id" NOTNULL OR 2.107 + "interest"."member_id" NOTNULL OR 2.108 + ( "membership"."member_id" NOTNULL AND 2.109 + "event"."event" IN ( 2.110 + 'issue_state_changed', 2.111 + 'initiative_created_in_new_issue', 2.112 + 'initiative_created_in_existing_issue', 2.113 + 'initiative_revoked' ) ) ) 2.114 + AND "ignored_member"."member_id" ISNULL 2.115 + AND "ignored_initiative"."member_id" ISNULL; 2.116 +COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"'; 2.117 + 2.118 +COMMIT;