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