liquid_feedback_core: 54ac8c473263 update/core-update.v1.2.9-v1.3.0.sql

liquid_feedback_core

view update/core-update.v1.2.9-v1.3.0.sql @ 142:54ac8c473263

Use an improved definition for "disqualified" initiatives

"initiative"."disqualified" is TRUE, if the initiative may not win, because it either (a) has no better rank than the status quo, or (b) because there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X

author	jbe
date	Wed Jun 01 16:58:00 2011 +0200 (2011-06-01)
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;