liquid_feedback_core: 3a86196ed0bf update/core-update.v1.2.8-v1.3.0.sql

liquid_feedback_core

view update/core-update.v1.2.8-v1.3.0.sql @ 86:3a86196ed0bf

Allow to disable delegations (trustee_id = NULL) and to ignore issues

author	jbe
date	Mon Oct 18 19:11:56 2010 +0200 (2010-10-18)
parents	update/core-update.v1.2.8-v1.2.9.sql@1a412ec5e14e
children

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('1.3.0', 1, 3, 0))

5 AS "subquery"("string", "major", "minor", "revision");

7 ALTER TABLE "supporter" ADD COLUMN

8 "auto_support" BOOLEAN NOT NULL DEFAULT FALSE;

10 COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative';

13 -- update comment for column "fully_frozen" of table "issue"

15 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';

18 -- update comment for column "autoreject" of table "membership"

20 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.';

23 -- allow column "autoreject" of table "interest" to be NULL

24 -- (thus defaulting to "membership")

26 ALTER TABLE "interest" ALTER COLUMN "autoreject" DROP NOT NULL;

29 -- new table "ignored_issue" to allow members to ignore particular issues in certain states

31 CREATE TABLE "ignored_issue" (

32 PRIMARY KEY ("issue_id", "member_id"),

33 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

34 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

35 "new" BOOLEAN NOT NULL DEFAULT FALSE,

36 "accepted" BOOLEAN NOT NULL DEFAULT FALSE,

37 "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE,

38 "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE );

39 CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id");

41 COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states';

43 COMMENT ON COLUMN "ignored_issue"."new" IS 'Selects issues which are neither closed nor accepted';

44 COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Selects issues which are accepted but not (half_)frozen or closed';

45 COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Selects issues which are half_frozen but not fully_frozen or closed';

46 COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Selects issues which are fully_frozen (in voting) and not closed';

49 -- allow area and issue delegations with trustee_id set to NULL

50 -- (indicating that global or area delegation is void for that area or issue)

52 ALTER TABLE "delegation" ALTER COLUMN "trustee_id" DROP NOT NULL;

54 ALTER TABLE "delegation" ADD CONSTRAINT "no_global_delegation_to_null"

55 CHECK ("trustee_id" NOTNULL OR "scope" != 'global');

58 -- disable and delete "copy_autoreject" trigger on table "interest"

60 DROP TRIGGER "copy_autoreject" ON "interest";

61 DROP FUNCTION "copy_autoreject_trigger"();

64 -- update comments on delegation views

66 COMMENT ON VIEW "active_delegation" IS 'Helper view for views "global_delegation", "area_delegation" and "issue_delegation": Contains delegations where the truster_id refers to an active member and includes those delegations where trustee_id is NULL';

68 COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL';

70 COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL';

73 --

75 DROP FUNCTION "delegation_chain"

76 ( "member"."id"%TYPE,

77 "area"."id"%TYPE,

78 "issue"."id"%TYPE );

80 DROP FUNCTION "delegation_chain"

81 ( "member"."id"%TYPE,

82 "area"."id"%TYPE,

83 "issue"."id"%TYPE,

84 "member"."id"%TYPE );

86 DROP TYPE "delegation_chain_row";

88 CREATE TYPE "delegation_chain_row" AS (

89 "index" INT4,

90 "member_id" INT4,

91 "member_active" BOOLEAN,

92 "participation" BOOLEAN,

93 "overridden" BOOLEAN,

94 "scope_in" "delegation_scope",

95 "scope_out" "delegation_scope",

96 "disabled_out" BOOLEAN,

97 "loop" "delegation_chain_loop_tag" );

99 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';

100

101 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';

102 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';

103 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';

104 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';

105 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';

106 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';

107 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';

108

109

110 CREATE FUNCTION "delegation_chain"

111 ( "member_id_p" "member"."id"%TYPE,

112 "area_id_p" "area"."id"%TYPE,

113 "issue_id_p" "issue"."id"%TYPE,

114 "simulate_trustee_id_p" "member"."id"%TYPE )

115 RETURNS SETOF "delegation_chain_row"

116 LANGUAGE 'plpgsql' STABLE AS $$

117 DECLARE

118 "issue_row" "issue"%ROWTYPE;

119 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]

120 "loop_member_id_v" "member"."id"%TYPE;

121 "output_row" "delegation_chain_row";

122 "output_rows" "delegation_chain_row"[];

123 "delegation_row" "delegation"%ROWTYPE;

124 "row_count" INT4;

125 "i" INT4;

126 "loop_v" BOOLEAN;

127 BEGIN

128 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

129 "visited_member_ids" := '{}';

130 "loop_member_id_v" := NULL;

131 "output_rows" := '{}';

132 "output_row"."index" := 0;

133 "output_row"."member_id" := "member_id_p";

134 "output_row"."member_active" := TRUE;

135 "output_row"."participation" := FALSE;

136 "output_row"."overridden" := FALSE;

137 "output_row"."disabled_out" := FALSE;

138 "output_row"."scope_out" := NULL;

139 LOOP

140 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN

141 "loop_member_id_v" := "output_row"."member_id";

142 ELSE

143 "visited_member_ids" :=

144 "visited_member_ids" || "output_row"."member_id";

145 END IF;

146 IF "output_row"."participation" THEN

147 "output_row"."overridden" := TRUE;

148 END IF;

149 "output_row"."scope_in" := "output_row"."scope_out";

150 IF EXISTS (

151 SELECT NULL FROM "member"

152 WHERE "id" = "output_row"."member_id" AND "active"

153 ) THEN

154 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

155 SELECT * INTO "delegation_row" FROM "delegation"

156 WHERE "truster_id" = "output_row"."member_id"

157 AND "scope" = 'global';

158 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN

159 "output_row"."participation" := EXISTS (

160 SELECT NULL FROM "membership"

161 WHERE "area_id" = "area_id_p"

162 AND "member_id" = "output_row"."member_id"

163 );

164 SELECT * INTO "delegation_row" FROM "delegation"

165 WHERE "truster_id" = "output_row"."member_id"

166 AND ("scope" = 'global' OR "area_id" = "area_id_p")

167 ORDER BY "scope" DESC;

168 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN

169 "output_row"."participation" := EXISTS (

170 SELECT NULL FROM "interest"

171 WHERE "issue_id" = "issue_id_p"

172 AND "member_id" = "output_row"."member_id"

173 );

174 SELECT * INTO "delegation_row" FROM "delegation"

175 WHERE "truster_id" = "output_row"."member_id"

176 AND ("scope" = 'global' OR

177 "area_id" = "issue_row"."area_id" OR

178 "issue_id" = "issue_id_p"

179 )

180 ORDER BY "scope" DESC;

181 ELSE

182 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';

183 END IF;

184 ELSE

185 "output_row"."member_active" := FALSE;

186 "output_row"."participation" := FALSE;

187 "output_row"."scope_out" := NULL;

188 "delegation_row" := ROW(NULL);

189 END IF;

190 IF

191 "output_row"."member_id" = "member_id_p" AND

192 "simulate_trustee_id_p" NOTNULL

193 THEN

194 "output_row"."scope_out" := CASE

195 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'

196 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'

197 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'

198 END;

199 "output_rows" := "output_rows" || "output_row";

200 "output_row"."member_id" := "simulate_trustee_id_p";

201 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

202 "output_row"."scope_out" := "delegation_row"."scope";

203 "output_rows" := "output_rows" || "output_row";

204 "output_row"."member_id" := "delegation_row"."trustee_id";

205 ELSIF "delegation_row"."scope" NOTNULL THEN

206 "output_row"."scope_out" := "delegation_row"."scope";

207 "output_row"."disabled_out" := TRUE;

208 "output_rows" := "output_rows" || "output_row";

209 EXIT;

210 ELSE

211 "output_row"."scope_out" := NULL;

212 "output_rows" := "output_rows" || "output_row";

213 EXIT;

214 END IF;

215 EXIT WHEN "loop_member_id_v" NOTNULL;

216 "output_row"."index" := "output_row"."index" + 1;

217 END LOOP;

218 "row_count" := array_upper("output_rows", 1);

219 "i" := 1;

220 "loop_v" := FALSE;

221 LOOP

222 "output_row" := "output_rows"["i"];

223 EXIT WHEN "output_row" ISNULL;

224 IF "loop_v" THEN

225 IF "i" + 1 = "row_count" THEN

226 "output_row"."loop" := 'last';

227 ELSIF "i" = "row_count" THEN

228 "output_row"."loop" := 'repetition';

229 ELSE

230 "output_row"."loop" := 'intermediate';

231 END IF;

232 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN

233 "output_row"."loop" := 'first';

234 "loop_v" := TRUE;

235 END IF;

236 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

237 "output_row"."participation" := NULL;

238 END IF;

239 RETURN NEXT "output_row";

240 "i" := "i" + 1;

241 END LOOP;

242 RETURN;

243 END;

244 $$;

245

246 COMMENT ON FUNCTION "delegation_chain"

247 ( "member"."id"%TYPE,

248 "area"."id"%TYPE,

249 "issue"."id"%TYPE,

250 "member"."id"%TYPE )

251 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';

252

253 CREATE FUNCTION "delegation_chain"

254 ( "member_id_p" "member"."id"%TYPE,

255 "area_id_p" "area"."id"%TYPE,

256 "issue_id_p" "issue"."id"%TYPE )

257 RETURNS SETOF "delegation_chain_row"

258 LANGUAGE 'plpgsql' STABLE AS $$

259 DECLARE

260 "result_row" "delegation_chain_row";

261 BEGIN

262 FOR "result_row" IN

263 SELECT * FROM "delegation_chain"(

264 "member_id_p", "area_id_p", "issue_id_p", NULL

265 )

266 LOOP

267 RETURN NEXT "result_row";

268 END LOOP;

269 RETURN;

270 END;

271 $$;

272

273 COMMENT ON FUNCTION "delegation_chain"

274 ( "member"."id"%TYPE,

275 "area"."id"%TYPE,

276 "issue"."id"%TYPE )

277 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';

278

279

280 -- delete entries of "ignored_issue" table in "delete_member"(...) and "delete_private_data"() functions

281

282 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

283 RETURNS VOID

284 LANGUAGE 'plpgsql' VOLATILE AS $$

285 BEGIN

286 UPDATE "member" SET

287 "last_login" = NULL,

288 "login" = NULL,

289 "password" = NULL,

290 "active" = FALSE,

291 "notify_email" = NULL,

292 "notify_email_unconfirmed" = NULL,

293 "notify_email_secret" = NULL,

294 "notify_email_secret_expiry" = NULL,

295 "notify_email_lock_expiry" = NULL,

296 "password_reset_secret" = NULL,

297 "password_reset_secret_expiry" = NULL,

298 "organizational_unit" = NULL,

299 "internal_posts" = NULL,

300 "realname" = NULL,

301 "birthday" = NULL,

302 "address" = NULL,

303 "email" = NULL,

304 "xmpp_address" = NULL,

305 "website" = NULL,

306 "phone" = NULL,

307 "mobile_phone" = NULL,

308 "profession" = NULL,

309 "external_memberships" = NULL,

310 "external_posts" = NULL,

311 "statement" = NULL

312 WHERE "id" = "member_id_p";

313 -- "text_search_data" is updated by triggers

314 DELETE FROM "setting" WHERE "member_id" = "member_id_p";

315 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";

316 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";

317 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

318 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

319 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";

320 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";

321 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";

322 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";

323 DELETE FROM "membership" WHERE "member_id" = "member_id_p";

324 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

325 DELETE FROM "ignored_voting" WHERE "member_id" = "member_id_p";

326 DELETE FROM "direct_voter" USING "issue"

327 WHERE "direct_voter"."issue_id" = "issue"."id"

328 AND "issue"."closed" ISNULL

329 AND "member_id" = "member_id_p";

330 RETURN;

331 END;

332 $$;

333

334 CREATE OR REPLACE FUNCTION "delete_private_data"()

335 RETURNS VOID

336 LANGUAGE 'plpgsql' VOLATILE AS $$

337 BEGIN

338 UPDATE "member" SET

339 "last_login" = NULL,

340 "login" = NULL,

341 "password" = NULL,

342 "notify_email" = NULL,

343 "notify_email_unconfirmed" = NULL,

344 "notify_email_secret" = NULL,

345 "notify_email_secret_expiry" = NULL,

346 "notify_email_lock_expiry" = NULL,

347 "password_reset_secret" = NULL,

348 "password_reset_secret_expiry" = NULL,

349 "organizational_unit" = NULL,

350 "internal_posts" = NULL,

351 "realname" = NULL,

352 "birthday" = NULL,

353 "address" = NULL,

354 "email" = NULL,

355 "xmpp_address" = NULL,

356 "website" = NULL,

357 "phone" = NULL,

358 "mobile_phone" = NULL,

359 "profession" = NULL,

360 "external_memberships" = NULL,

361 "external_posts" = NULL,

362 "statement" = NULL;

363 -- "text_search_data" is updated by triggers

364 DELETE FROM "invite_code";

365 DELETE FROM "setting";

366 DELETE FROM "setting_map";

367 DELETE FROM "member_relation_setting";

368 DELETE FROM "member_image";

369 DELETE FROM "contact";

370 DELETE FROM "session";

371 DELETE FROM "area_setting";

372 DELETE FROM "issue_setting";

373 DELETE FROM "initiative_setting";

374 DELETE FROM "suggestion_setting";

375 DELETE FROM "ignored_voting";

376 DELETE FROM "direct_voter" USING "issue"

377 WHERE "direct_voter"."issue_id" = "issue"."id"

378 AND "issue"."closed" ISNULL;

379 RETURN;

380 END;

381 $$;

382

383

384 COMMIT;