liquid_feedback_core: 806561cce3b1 update/core-update.v1.2.9-v1.3.0.sql

liquid_feedback_core

view update/core-update.v1.2.9-v1.3.0.sql @ 398:806561cce3b1

New table "issue_order" for ordering fields to avoid a bottleneck with locking

author	jbe
date	Fri Oct 11 20:07:41 2013 +0200 (2013-10-11)
parents	fb9688f31740
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");

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

10 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.';

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

15 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.';

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

19 -- (thus defaulting to "membership")

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

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

26 CREATE TABLE "ignored_issue" (

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

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

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

30 "new" BOOLEAN NOT NULL DEFAULT FALSE,

31 "accepted" BOOLEAN NOT NULL DEFAULT FALSE,

32 "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE,

33 "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE );

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

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

38 COMMENT ON COLUMN "ignored_issue"."new" IS 'Apply when issue is neither closed nor accepted';

39 COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Apply when issue is accepted but not (half_)frozen or closed';

40 COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Apply when issue is half_frozen but not fully_frozen or closed';

41 COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Apply when issue is fully_frozen (in voting) and not closed';

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

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

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

49 ALTER TABLE "delegation" ADD CONSTRAINT "no_global_delegation_to_null"

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

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

55 DROP TRIGGER "copy_autoreject" ON "interest";

56 DROP FUNCTION "copy_autoreject_trigger"();

59 -- update comments on delegation views

61 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';

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

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

68 -- support for explicitly disabled delegations in "delegation_chain" functions

70 DROP FUNCTION "delegation_chain"

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

72 "area"."id"%TYPE,

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

75 DROP FUNCTION "delegation_chain"

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

77 "area"."id"%TYPE,

78 "issue"."id"%TYPE,

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

81 DROP TYPE "delegation_chain_row";

83 CREATE TYPE "delegation_chain_row" AS (

84 "index" INT4,

85 "member_id" INT4,

86 "member_active" BOOLEAN,

87 "participation" BOOLEAN,

88 "overridden" BOOLEAN,

89 "scope_in" "delegation_scope",

90 "scope_out" "delegation_scope",

91 "disabled_out" BOOLEAN,

92 "loop" "delegation_chain_loop_tag" );

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

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

97 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';

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

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

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

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

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

103

104

105 CREATE FUNCTION "delegation_chain"

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

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

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

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

110 RETURNS SETOF "delegation_chain_row"

111 LANGUAGE 'plpgsql' STABLE AS $$

112 DECLARE

113 "issue_row" "issue"%ROWTYPE;

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

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

116 "output_row" "delegation_chain_row";

117 "output_rows" "delegation_chain_row"[];

118 "delegation_row" "delegation"%ROWTYPE;

119 "row_count" INT4;

120 "i" INT4;

121 "loop_v" BOOLEAN;

122 BEGIN

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

124 "visited_member_ids" := '{}';

125 "loop_member_id_v" := NULL;

126 "output_rows" := '{}';

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

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

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

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

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

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

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

134 LOOP

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

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

137 ELSE

138 "visited_member_ids" :=

139 "visited_member_ids" || "output_row"."member_id";

140 END IF;

141 IF "output_row"."participation" THEN

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

143 END IF;

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

145 IF EXISTS (

146 SELECT NULL FROM "member"

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

148 ) THEN

149 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

150 SELECT * INTO "delegation_row" FROM "delegation"

151 WHERE "truster_id" = "output_row"."member_id"

152 AND "scope" = 'global';

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

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

155 SELECT NULL FROM "membership"

156 WHERE "area_id" = "area_id_p"

157 AND "member_id" = "output_row"."member_id"

158 );

159 SELECT * INTO "delegation_row" FROM "delegation"

160 WHERE "truster_id" = "output_row"."member_id"

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

162 ORDER BY "scope" DESC;

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

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

165 SELECT NULL FROM "interest"

166 WHERE "issue_id" = "issue_id_p"

167 AND "member_id" = "output_row"."member_id"

168 );

169 SELECT * INTO "delegation_row" FROM "delegation"

170 WHERE "truster_id" = "output_row"."member_id"

171 AND ("scope" = 'global' OR

172 "area_id" = "issue_row"."area_id" OR

173 "issue_id" = "issue_id_p"

174 )

175 ORDER BY "scope" DESC;

176 ELSE

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

178 END IF;

179 ELSE

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

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

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

183 "delegation_row" := ROW(NULL);

184 END IF;

185 IF

186 "output_row"."member_id" = "member_id_p" AND

187 "simulate_trustee_id_p" NOTNULL

188 THEN

189 "output_row"."scope_out" := CASE

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

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

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

193 END;

194 "output_rows" := "output_rows" || "output_row";

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

196 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

198 "output_rows" := "output_rows" || "output_row";

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

200 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

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

204 EXIT;

205 ELSE

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

207 "output_rows" := "output_rows" || "output_row";

208 EXIT;

209 END IF;

210 EXIT WHEN "loop_member_id_v" NOTNULL;

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

212 END LOOP;

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

214 "i" := 1;

215 "loop_v" := FALSE;

216 LOOP

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

218 EXIT WHEN "output_row" ISNULL;

219 IF "loop_v" THEN

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

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

222 ELSIF "i" = "row_count" THEN

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

224 ELSE

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

226 END IF;

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

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

229 "loop_v" := TRUE;

230 END IF;

231 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

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

233 END IF;

234 RETURN NEXT "output_row";

235 "i" := "i" + 1;

236 END LOOP;

237 RETURN;

238 END;

239 $$;

240

241 COMMENT ON FUNCTION "delegation_chain"

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

243 "area"."id"%TYPE,

244 "issue"."id"%TYPE,

245 "member"."id"%TYPE )

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

247

248 CREATE FUNCTION "delegation_chain"

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

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

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

252 RETURNS SETOF "delegation_chain_row"

253 LANGUAGE 'plpgsql' STABLE AS $$

254 DECLARE

255 "result_row" "delegation_chain_row";

256 BEGIN

257 FOR "result_row" IN

258 SELECT * FROM "delegation_chain"(

259 "member_id_p", "area_id_p", "issue_id_p", NULL

260 )

261 LOOP

262 RETURN NEXT "result_row";

263 END LOOP;

264 RETURN;

265 END;

266 $$;

267

268 COMMENT ON FUNCTION "delegation_chain"

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

270 "area"."id"%TYPE,

271 "issue"."id"%TYPE )

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

273

274

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

276

277 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)

278 RETURNS VOID

279 LANGUAGE 'plpgsql' VOLATILE AS $$

280 DECLARE

281 "issue_row" "issue"%ROWTYPE;

282 BEGIN

283 SELECT * INTO "issue_row"

284 FROM "issue" WHERE "id" = "issue_id_p"

285 FOR UPDATE;

286 IF "issue_row"."cleaned" ISNULL THEN

287 UPDATE "issue" SET

288 "closed" = NULL,

289 "ranks_available" = FALSE

290 WHERE "id" = "issue_id_p";

291 DELETE FROM "delegating_voter"

292 WHERE "issue_id" = "issue_id_p";

293 DELETE FROM "direct_voter"

294 WHERE "issue_id" = "issue_id_p";

295 DELETE FROM "delegating_interest_snapshot"

296 WHERE "issue_id" = "issue_id_p";

297 DELETE FROM "direct_interest_snapshot"

298 WHERE "issue_id" = "issue_id_p";

299 DELETE FROM "delegating_population_snapshot"

300 WHERE "issue_id" = "issue_id_p";

301 DELETE FROM "direct_population_snapshot"

302 WHERE "issue_id" = "issue_id_p";

303 DELETE FROM "ignored_issue"

304 WHERE "issue_id" = "issue_id_p";

305 DELETE FROM "delegation"

306 WHERE "issue_id" = "issue_id_p";

307 DELETE FROM "supporter"

308 WHERE "issue_id" = "issue_id_p";

309 UPDATE "issue" SET

310 "closed" = "issue_row"."closed",

311 "ranks_available" = "issue_row"."ranks_available",

312 "cleaned" = now()

313 WHERE "id" = "issue_id_p";

314 END IF;

315 RETURN;

316 END;

317 $$;

318

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

320 RETURNS VOID

321 LANGUAGE 'plpgsql' VOLATILE AS $$

322 BEGIN

323 UPDATE "member" SET

324 "last_login" = NULL,

325 "login" = NULL,

326 "password" = NULL,

327 "active" = FALSE,

328 "notify_email" = NULL,

329 "notify_email_unconfirmed" = NULL,

330 "notify_email_secret" = NULL,

331 "notify_email_secret_expiry" = NULL,

332 "notify_email_lock_expiry" = NULL,

333 "password_reset_secret" = NULL,

334 "password_reset_secret_expiry" = NULL,

335 "organizational_unit" = NULL,

336 "internal_posts" = NULL,

337 "realname" = NULL,

338 "birthday" = NULL,

339 "address" = NULL,

340 "email" = NULL,

341 "xmpp_address" = NULL,

342 "website" = NULL,

343 "phone" = NULL,

344 "mobile_phone" = NULL,

345 "profession" = NULL,

346 "external_memberships" = NULL,

347 "external_posts" = NULL,

348 "statement" = NULL

349 WHERE "id" = "member_id_p";

350 -- "text_search_data" is updated by triggers

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

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

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

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

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

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

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

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

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

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

361 DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p";

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

363 DELETE FROM "direct_voter" USING "issue"

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

365 AND "issue"."closed" ISNULL

366 AND "member_id" = "member_id_p";

367 RETURN;

368 END;

369 $$;

370

371 CREATE OR REPLACE FUNCTION "delete_private_data"()

372 RETURNS VOID

373 LANGUAGE 'plpgsql' VOLATILE AS $$

374 BEGIN

375 UPDATE "member" SET

376 "last_login" = NULL,

377 "login" = NULL,

378 "password" = NULL,

379 "notify_email" = NULL,

380 "notify_email_unconfirmed" = NULL,

381 "notify_email_secret" = NULL,

382 "notify_email_secret_expiry" = NULL,

383 "notify_email_lock_expiry" = NULL,

384 "password_reset_secret" = NULL,

385 "password_reset_secret_expiry" = NULL,

386 "organizational_unit" = NULL,

387 "internal_posts" = NULL,

388 "realname" = NULL,

389 "birthday" = NULL,

390 "address" = NULL,

391 "email" = NULL,

392 "xmpp_address" = NULL,

393 "website" = NULL,

394 "phone" = NULL,

395 "mobile_phone" = NULL,

396 "profession" = NULL,

397 "external_memberships" = NULL,

398 "external_posts" = NULL,

399 "statement" = NULL;

400 -- "text_search_data" is updated by triggers

401 DELETE FROM "invite_code";

402 DELETE FROM "setting";

403 DELETE FROM "setting_map";

404 DELETE FROM "member_relation_setting";

405 DELETE FROM "member_image";

406 DELETE FROM "contact";

407 DELETE FROM "session";

408 DELETE FROM "area_setting";

409 DELETE FROM "issue_setting";

410 DELETE FROM "initiative_setting";

411 DELETE FROM "suggestion_setting";

412 DELETE FROM "ignored_issue";

413 DELETE FROM "direct_voter" USING "issue"

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

415 AND "issue"."closed" ISNULL;

416 RETURN;

417 END;

418 $$;

419

420

421 COMMIT;