liquid_feedback_core

view update/core-update.v3.0.3-v3.0.4.sql @ 616:ae53fc96c953

Added JSONB column "attr" to table "unit" for extended attributes of any kind
author jbe
date Tue Jul 28 18:57:20 2020 +0200 (7 weeks ago)
parents f5c78b0590c6
children
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('3.0.4', 3, 0, 4))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TABLE "member" ADD COLUMN "authority" TEXT;
8 ALTER TABLE "member" ADD COLUMN "authority_uid" TEXT;
9 ALTER TABLE "member" ADD COLUMN "authority_login" TEXT;
11 COMMENT ON COLUMN "member"."authority" IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)';
12 COMMENT ON COLUMN "member"."authority_uid" IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)';
13 COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)';
15 ALTER TABLE "member" ADD CONSTRAINT "authority_requires_uid_and_vice_versa"
16 CHECK ("authority" NOTNULL = "authority_uid" NOTNULL);
18 ALTER TABLE "member" ADD CONSTRAINT "authority_uid_unique_per_authority"
19 UNIQUE ("authority", "authority_uid");
21 ALTER TABLE "member" ADD CONSTRAINT "authority_login_requires_authority"
22 CHECK ("authority" NOTNULL OR "authority_login" ISNULL);
24 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
26 ALTER TABLE "session" ADD COLUMN "authority" TEXT;
27 ALTER TABLE "session" ADD COLUMN "authority_uid" TEXT;
28 ALTER TABLE "session" ADD COLUMN "authority_login" TEXT;
30 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
31 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
32 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
34 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
35 RETURNS VOID
36 LANGUAGE 'plpgsql' VOLATILE AS $$
37 BEGIN
38 UPDATE "member" SET
39 "last_login" = NULL,
40 "last_delegation_check" = NULL,
41 "login" = NULL,
42 "password" = NULL,
43 "authority" = NULL,
44 "authority_uid" = NULL,
45 "authority_login" = NULL,
46 "locked" = TRUE,
47 "active" = FALSE,
48 "notify_email" = NULL,
49 "notify_email_unconfirmed" = NULL,
50 "notify_email_secret" = NULL,
51 "notify_email_secret_expiry" = NULL,
52 "notify_email_lock_expiry" = NULL,
53 "login_recovery_expiry" = NULL,
54 "password_reset_secret" = NULL,
55 "password_reset_secret_expiry" = NULL,
56 "organizational_unit" = NULL,
57 "internal_posts" = NULL,
58 "realname" = NULL,
59 "birthday" = NULL,
60 "address" = NULL,
61 "email" = NULL,
62 "xmpp_address" = NULL,
63 "website" = NULL,
64 "phone" = NULL,
65 "mobile_phone" = NULL,
66 "profession" = NULL,
67 "external_memberships" = NULL,
68 "external_posts" = NULL,
69 "statement" = NULL
70 WHERE "id" = "member_id_p";
71 -- "text_search_data" is updated by triggers
72 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
73 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
74 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
75 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
76 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
77 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
78 DELETE FROM "session" WHERE "member_id" = "member_id_p";
79 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
80 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
81 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
82 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
83 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
84 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
85 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
86 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
87 DELETE FROM "direct_voter" USING "issue"
88 WHERE "direct_voter"."issue_id" = "issue"."id"
89 AND "issue"."closed" ISNULL
90 AND "member_id" = "member_id_p";
91 RETURN;
92 END;
93 $$;
95 CREATE OR REPLACE FUNCTION "delete_private_data"()
96 RETURNS VOID
97 LANGUAGE 'plpgsql' VOLATILE AS $$
98 BEGIN
99 DELETE FROM "temporary_transaction_data";
100 DELETE FROM "member" WHERE "activated" ISNULL;
101 UPDATE "member" SET
102 "invite_code" = NULL,
103 "invite_code_expiry" = NULL,
104 "admin_comment" = NULL,
105 "last_login" = NULL,
106 "last_delegation_check" = NULL,
107 "login" = NULL,
108 "password" = NULL,
109 "authority" = NULL,
110 "authority_uid" = NULL,
111 "authority_login" = NULL,
112 "lang" = NULL,
113 "notify_email" = NULL,
114 "notify_email_unconfirmed" = NULL,
115 "notify_email_secret" = NULL,
116 "notify_email_secret_expiry" = NULL,
117 "notify_email_lock_expiry" = NULL,
118 "notify_level" = NULL,
119 "login_recovery_expiry" = NULL,
120 "password_reset_secret" = NULL,
121 "password_reset_secret_expiry" = NULL,
122 "organizational_unit" = NULL,
123 "internal_posts" = NULL,
124 "realname" = NULL,
125 "birthday" = NULL,
126 "address" = NULL,
127 "email" = NULL,
128 "xmpp_address" = NULL,
129 "website" = NULL,
130 "phone" = NULL,
131 "mobile_phone" = NULL,
132 "profession" = NULL,
133 "external_memberships" = NULL,
134 "external_posts" = NULL,
135 "formatting_engine" = NULL,
136 "statement" = NULL;
137 -- "text_search_data" is updated by triggers
138 DELETE FROM "setting";
139 DELETE FROM "setting_map";
140 DELETE FROM "member_relation_setting";
141 DELETE FROM "member_image";
142 DELETE FROM "contact";
143 DELETE FROM "ignored_member";
144 DELETE FROM "session";
145 DELETE FROM "area_setting";
146 DELETE FROM "issue_setting";
147 DELETE FROM "ignored_initiative";
148 DELETE FROM "initiative_setting";
149 DELETE FROM "suggestion_setting";
150 DELETE FROM "non_voter";
151 DELETE FROM "direct_voter" USING "issue"
152 WHERE "direct_voter"."issue_id" = "issue"."id"
153 AND "issue"."closed" ISNULL;
154 RETURN;
155 END;
156 $$;
158 COMMIT;

Impressum / About Us