jbe@224: BEGIN; jbe@224: jbe@224: -- update version number: jbe@224: CREATE OR REPLACE VIEW "liquid_feedback_version" AS jbe@224: SELECT * FROM (VALUES ('2.0.4', 2, 0, 4)) jbe@224: AS "subquery"("string", "major", "minor", "revision"); jbe@224: jbe@225: -- drop NOT NULL constraints on columns "name" and "notify_level" jbe@225: -- in table "member", and add new constraint for "name": jbe@225: ALTER TABLE "member" ALTER COLUMN "notify_level" DROP NOT NULL; jbe@225: ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL; jbe@225: ALTER TABLE "member" ADD CONSTRAINT "name_not_null_if_activated" CHECK ("activated" ISNULL OR "name" NOTNULL); jbe@225: 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'; jbe@225: COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet'; jbe@225: jbe@224: -- add table "session": jbe@224: CREATE TABLE "session" ( jbe@224: "ident" TEXT PRIMARY KEY, jbe@224: "additional_secret" TEXT, jbe@224: "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours', jbe@224: "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL, jbe@224: "lang" TEXT ); jbe@224: CREATE INDEX "session_expiry_idx" ON "session" ("expiry"); jbe@224: COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer'; jbe@224: COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)'; jbe@224: COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks'; jbe@224: COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in'; jbe@224: COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language'; jbe@224: jbe@224: -- add column "lang" to table "member": jbe@224: ALTER TABLE "member" ADD COLUMN "lang" TEXT; jbe@224: COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member'; jbe@224: jbe@224: -- drop view "pending_notification": jbe@224: DROP VIEW "pending_notification"; jbe@224: jbe@224: -- remove column "notify_event_id" of table "member": jbe@224: ALTER TABLE "member" DROP COLUMN "notify_event_id"; jbe@224: jbe@224: -- add table "notification_sent": jbe@224: CREATE TABLE "notification_sent" ( jbe@224: "event_id" INT8 NOT NULL ); jbe@224: CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1)); jbe@224: COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out'; jbe@224: COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.'; jbe@224: jbe@224: -- add view "selected_event_seen_by_member": jbe@224: CREATE VIEW "selected_event_seen_by_member" AS jbe@224: SELECT jbe@224: "member"."id" AS "seen_by_member_id", jbe@224: CASE WHEN "event"."state" IN ( jbe@224: 'voting', jbe@224: 'finished_without_winner', jbe@224: 'finished_with_winner' jbe@224: ) THEN jbe@224: 'voting'::"notify_level" jbe@224: ELSE jbe@224: CASE WHEN "event"."state" IN ( jbe@224: 'verification', jbe@224: 'canceled_after_revocation_during_verification', jbe@224: 'canceled_no_initiative_admitted' jbe@224: ) THEN jbe@224: 'verification'::"notify_level" jbe@224: ELSE jbe@224: CASE WHEN "event"."state" IN ( jbe@224: 'discussion', jbe@224: 'canceled_after_revocation_during_discussion' jbe@224: ) THEN jbe@224: 'discussion'::"notify_level" jbe@224: ELSE jbe@224: 'all'::"notify_level" jbe@224: END jbe@224: END jbe@224: END AS "notify_level", jbe@224: "event".* jbe@224: FROM "member" CROSS JOIN "event" jbe@224: LEFT JOIN "issue" jbe@224: ON "event"."issue_id" = "issue"."id" jbe@224: LEFT JOIN "membership" jbe@224: ON "member"."id" = "membership"."member_id" jbe@224: AND "issue"."area_id" = "membership"."area_id" jbe@224: LEFT JOIN "interest" jbe@224: ON "member"."id" = "interest"."member_id" jbe@224: AND "event"."issue_id" = "interest"."issue_id" jbe@224: LEFT JOIN "supporter" jbe@224: ON "member"."id" = "supporter"."member_id" jbe@224: AND "event"."initiative_id" = "supporter"."initiative_id" jbe@224: LEFT JOIN "ignored_member" jbe@224: ON "member"."id" = "ignored_member"."member_id" jbe@224: AND "event"."member_id" = "ignored_member"."other_member_id" jbe@224: LEFT JOIN "ignored_initiative" jbe@224: ON "member"."id" = "ignored_initiative"."member_id" jbe@224: AND "event"."initiative_id" = "ignored_initiative"."initiative_id" jbe@224: WHERE ( jbe@224: ( "member"."notify_level" >= 'all' ) OR jbe@224: ( "member"."notify_level" >= 'voting' AND jbe@224: "event"."state" IN ( jbe@224: 'voting', jbe@224: 'finished_without_winner', jbe@224: 'finished_with_winner' ) ) OR jbe@224: ( "member"."notify_level" >= 'verification' AND jbe@224: "event"."state" IN ( jbe@224: 'verification', jbe@224: 'canceled_after_revocation_during_verification', jbe@224: 'canceled_no_initiative_admitted' ) ) OR jbe@224: ( "member"."notify_level" >= 'discussion' AND jbe@224: "event"."state" IN ( jbe@224: 'discussion', jbe@224: 'canceled_after_revocation_during_discussion' ) ) ) jbe@224: AND ( jbe@224: "supporter"."member_id" NOTNULL OR jbe@224: "interest"."member_id" NOTNULL OR jbe@224: ( "membership"."member_id" NOTNULL AND jbe@224: "event"."event" IN ( jbe@224: 'issue_state_changed', jbe@224: 'initiative_created_in_new_issue', jbe@224: 'initiative_created_in_existing_issue', jbe@224: 'initiative_revoked' ) ) ) jbe@224: AND "ignored_member"."member_id" ISNULL jbe@224: AND "ignored_initiative"."member_id" ISNULL; jbe@224: 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"'; jbe@224: jbe@226: -- delete non-activated members in function "delete_private_data": jbe@226: CREATE OR REPLACE FUNCTION "delete_private_data"() jbe@226: RETURNS VOID jbe@226: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@226: BEGIN jbe@226: DELETE FROM "member" WHERE "activated" ISNULL; jbe@226: UPDATE "member" SET jbe@226: "invite_code" = NULL, jbe@226: "last_login" = NULL, jbe@226: "login" = NULL, jbe@226: "password" = NULL, jbe@226: "notify_email" = NULL, jbe@226: "notify_email_unconfirmed" = NULL, jbe@226: "notify_email_secret" = NULL, jbe@226: "notify_email_secret_expiry" = NULL, jbe@226: "notify_email_lock_expiry" = NULL, jbe@226: "password_reset_secret" = NULL, jbe@226: "password_reset_secret_expiry" = NULL, jbe@226: "organizational_unit" = NULL, jbe@226: "internal_posts" = NULL, jbe@226: "realname" = NULL, jbe@226: "birthday" = NULL, jbe@226: "address" = NULL, jbe@226: "email" = NULL, jbe@226: "xmpp_address" = NULL, jbe@226: "website" = NULL, jbe@226: "phone" = NULL, jbe@226: "mobile_phone" = NULL, jbe@226: "profession" = NULL, jbe@226: "external_memberships" = NULL, jbe@226: "external_posts" = NULL, jbe@226: "statement" = NULL; jbe@226: -- "text_search_data" is updated by triggers jbe@226: DELETE FROM "setting"; jbe@226: DELETE FROM "setting_map"; jbe@226: DELETE FROM "member_relation_setting"; jbe@226: DELETE FROM "member_image"; jbe@226: DELETE FROM "contact"; jbe@226: DELETE FROM "ignored_member"; jbe@226: DELETE FROM "area_setting"; jbe@226: DELETE FROM "issue_setting"; jbe@226: DELETE FROM "ignored_initiative"; jbe@226: DELETE FROM "initiative_setting"; jbe@226: DELETE FROM "suggestion_setting"; jbe@226: DELETE FROM "non_voter"; jbe@226: DELETE FROM "direct_voter" USING "issue" jbe@226: WHERE "direct_voter"."issue_id" = "issue"."id" jbe@226: AND "issue"."closed" ISNULL; jbe@226: RETURN; jbe@226: END; jbe@226: $$; jbe@226: jbe@224: COMMIT;