liquid_feedback_core

view update/core-update.v2.0.3-v2.0.4.sql @ 257:29538d1ea21e

Added tag v2.0.11 for changeset e818f83e133b
author jbe
date Wed Jun 20 22:25:34 2012 +0200 (2012-06-20)
parents 10a231cfd585
children
line source
1 BEGIN;
3 -- update version number:
4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
5 SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))
6 AS "subquery"("string", "major", "minor", "revision");
8 -- drop NOT NULL constraints on columns "name" and "notify_level"
9 -- in table "member", and add new constraint for "name":
10 ALTER TABLE "member" ALTER COLUMN "notify_level" DROP NOT NULL;
11 ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL;
12 ALTER TABLE "member" ADD CONSTRAINT "name_not_null_if_activated" CHECK ("activated" ISNULL OR "name" NOTNULL);
13 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address, may be NULL if member did not make any selection yet';
14 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
16 -- add table "session":
17 CREATE TABLE "session" (
18 "ident" TEXT PRIMARY KEY,
19 "additional_secret" TEXT,
20 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
21 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
22 "lang" TEXT );
23 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
24 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
25 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
26 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
27 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
28 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
30 -- add column "lang" to table "member":
31 ALTER TABLE "member" ADD COLUMN "lang" TEXT;
32 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
34 -- drop view "pending_notification":
35 DROP VIEW "pending_notification";
37 -- remove column "notify_event_id" of table "member":
38 ALTER TABLE "member" DROP COLUMN "notify_event_id";
40 -- add table "notification_sent":
41 CREATE TABLE "notification_sent" (
42 "event_id" INT8 NOT NULL );
43 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
44 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
45 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
47 -- add view "selected_event_seen_by_member":
48 CREATE VIEW "selected_event_seen_by_member" AS
49 SELECT
50 "member"."id" AS "seen_by_member_id",
51 CASE WHEN "event"."state" IN (
52 'voting',
53 'finished_without_winner',
54 'finished_with_winner'
55 ) THEN
56 'voting'::"notify_level"
57 ELSE
58 CASE WHEN "event"."state" IN (
59 'verification',
60 'canceled_after_revocation_during_verification',
61 'canceled_no_initiative_admitted'
62 ) THEN
63 'verification'::"notify_level"
64 ELSE
65 CASE WHEN "event"."state" IN (
66 'discussion',
67 'canceled_after_revocation_during_discussion'
68 ) THEN
69 'discussion'::"notify_level"
70 ELSE
71 'all'::"notify_level"
72 END
73 END
74 END AS "notify_level",
75 "event".*
76 FROM "member" CROSS JOIN "event"
77 LEFT JOIN "issue"
78 ON "event"."issue_id" = "issue"."id"
79 LEFT JOIN "membership"
80 ON "member"."id" = "membership"."member_id"
81 AND "issue"."area_id" = "membership"."area_id"
82 LEFT JOIN "interest"
83 ON "member"."id" = "interest"."member_id"
84 AND "event"."issue_id" = "interest"."issue_id"
85 LEFT JOIN "supporter"
86 ON "member"."id" = "supporter"."member_id"
87 AND "event"."initiative_id" = "supporter"."initiative_id"
88 LEFT JOIN "ignored_member"
89 ON "member"."id" = "ignored_member"."member_id"
90 AND "event"."member_id" = "ignored_member"."other_member_id"
91 LEFT JOIN "ignored_initiative"
92 ON "member"."id" = "ignored_initiative"."member_id"
93 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
94 WHERE (
95 ( "member"."notify_level" >= 'all' ) OR
96 ( "member"."notify_level" >= 'voting' AND
97 "event"."state" IN (
98 'voting',
99 'finished_without_winner',
100 'finished_with_winner' ) ) OR
101 ( "member"."notify_level" >= 'verification' AND
102 "event"."state" IN (
103 'verification',
104 'canceled_after_revocation_during_verification',
105 'canceled_no_initiative_admitted' ) ) OR
106 ( "member"."notify_level" >= 'discussion' AND
107 "event"."state" IN (
108 'discussion',
109 'canceled_after_revocation_during_discussion' ) ) )
110 AND (
111 "supporter"."member_id" NOTNULL OR
112 "interest"."member_id" NOTNULL OR
113 ( "membership"."member_id" NOTNULL AND
114 "event"."event" IN (
115 'issue_state_changed',
116 'initiative_created_in_new_issue',
117 'initiative_created_in_existing_issue',
118 'initiative_revoked' ) ) )
119 AND "ignored_member"."member_id" ISNULL
120 AND "ignored_initiative"."member_id" ISNULL;
121 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"';
123 -- delete non-activated members in function "delete_private_data":
124 CREATE OR REPLACE FUNCTION "delete_private_data"()
125 RETURNS VOID
126 LANGUAGE 'plpgsql' VOLATILE AS $$
127 BEGIN
128 DELETE FROM "member" WHERE "activated" ISNULL;
129 UPDATE "member" SET
130 "invite_code" = NULL,
131 "last_login" = NULL,
132 "login" = NULL,
133 "password" = NULL,
134 "notify_email" = NULL,
135 "notify_email_unconfirmed" = NULL,
136 "notify_email_secret" = NULL,
137 "notify_email_secret_expiry" = NULL,
138 "notify_email_lock_expiry" = NULL,
139 "password_reset_secret" = NULL,
140 "password_reset_secret_expiry" = NULL,
141 "organizational_unit" = NULL,
142 "internal_posts" = NULL,
143 "realname" = NULL,
144 "birthday" = NULL,
145 "address" = NULL,
146 "email" = NULL,
147 "xmpp_address" = NULL,
148 "website" = NULL,
149 "phone" = NULL,
150 "mobile_phone" = NULL,
151 "profession" = NULL,
152 "external_memberships" = NULL,
153 "external_posts" = NULL,
154 "statement" = NULL;
155 -- "text_search_data" is updated by triggers
156 DELETE FROM "setting";
157 DELETE FROM "setting_map";
158 DELETE FROM "member_relation_setting";
159 DELETE FROM "member_image";
160 DELETE FROM "contact";
161 DELETE FROM "ignored_member";
162 DELETE FROM "area_setting";
163 DELETE FROM "issue_setting";
164 DELETE FROM "ignored_initiative";
165 DELETE FROM "initiative_setting";
166 DELETE FROM "suggestion_setting";
167 DELETE FROM "non_voter";
168 DELETE FROM "direct_voter" USING "issue"
169 WHERE "direct_voter"."issue_id" = "issue"."id"
170 AND "issue"."closed" ISNULL;
171 RETURN;
172 END;
173 $$;
175 COMMIT;

Impressum / About Us