liquid_feedback_core: d09c17b01c5d update/core-update.v2.0.8-v2.0.9.sql

liquid_feedback_core

view update/core-update.v2.0.8-v2.0.9.sql @ 244:d09c17b01c5d

Update script to v2.0.9

author	jbe
date	Thu May 10 18:32:43 2012 +0200 (2012-05-10)
parents
children	4b8cc6fc2d00

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('2.0.9', 2, 0, 9))

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

7 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';

9 CREATE FUNCTION "delegation_chain_for_closed_issue"

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

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

12 RETURNS SETOF "delegation_chain_row"

13 LANGUAGE 'plpgsql' STABLE AS $$

14 DECLARE

15 "output_row" "delegation_chain_row";

16 "direct_voter_row" "direct_voter"%ROWTYPE;

17 "delegating_voter_row" "delegating_voter"%ROWTYPE;

18 BEGIN

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

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

21 "output_row"."member_valid" := TRUE;

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

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

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

25 LOOP

26 SELECT INTO "direct_voter_row" * FROM "direct_voter"

27 WHERE "issue_id" = "issue_id_p"

28 AND "member_id" = "output_row"."member_id";

29 IF "direct_voter_row"."member_id" NOTNULL THEN

30 "output_row"."participation" := TRUE;

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

32 "output_row"."disabled_out" := NULL;

33 RETURN NEXT "output_row";

34 RETURN;

35 END IF;

36 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"

37 WHERE "issue_id" = "issue_id_p"

38 AND "member_id" = "output_row"."member_id";

39 IF "delegating_voter_row"."member_id" ISNULL THEN

40 RETURN;

41 END IF;

42 "output_row"."scope_out" := "delegating_voter_row"."scope";

43 RETURN NEXT "output_row";

44 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];

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

46 END LOOP;

47 END;

48 $$;

50 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"

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

52 "member"."id"%TYPE )

53 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';

55 DROP FUNCTION "delegation_chain"

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

57 "unit"."id"%TYPE,

58 "area"."id"%TYPE,

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

61 DROP FUNCTION "delegation_chain"

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

63 "unit"."id"%TYPE,

64 "area"."id"%TYPE,

65 "issue"."id"%TYPE,

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

68 CREATE FUNCTION "delegation_chain"

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

70 "unit_id_p" "unit"."id"%TYPE,

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

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

73 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )

74 RETURNS SETOF "delegation_chain_row"

75 LANGUAGE 'plpgsql' STABLE AS $$

76 DECLARE

77 "scope_v" "delegation_scope";

78 "unit_id_v" "unit"."id"%TYPE;

79 "area_id_v" "area"."id"%TYPE;

80 "issue_row" "issue"%ROWTYPE;

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

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

83 "output_row" "delegation_chain_row";

84 "output_rows" "delegation_chain_row"[];

85 "delegation_row" "delegation"%ROWTYPE;

86 "row_count" INT4;

87 "i" INT4;

88 "loop_v" BOOLEAN;

89 BEGIN

90 IF

91 "unit_id_p" NOTNULL AND

92 "area_id_p" ISNULL AND

93 "issue_id_p" ISNULL

94 THEN

95 "scope_v" := 'unit';

96 "unit_id_v" := "unit_id_p";

97 ELSIF

98 "unit_id_p" ISNULL AND

99 "area_id_p" NOTNULL AND

100 "issue_id_p" ISNULL

101 THEN

102 "scope_v" := 'area';

103 "area_id_v" := "area_id_p";

104 SELECT "unit_id" INTO "unit_id_v"

105 FROM "area" WHERE "id" = "area_id_v";

106 ELSIF

107 "unit_id_p" ISNULL AND

108 "area_id_p" ISNULL AND

109 "issue_id_p" NOTNULL

110 THEN

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

112 IF "issue_row"."id" ISNULL THEN

113 RETURN;

114 END IF;

115 IF "issue_row"."closed" NOTNULL THEN

116 IF "simulate_trustee_id_p" NOTNULL THEN

117 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';

118 END IF;

119 FOR "output_row" IN

120 SELECT * FROM

121 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

122 LOOP

123 RETURN NEXT "output_row";

124 END LOOP;

125 RETURN;

126 END IF;

127 "scope_v" := 'issue';

128 SELECT "area_id" INTO "area_id_v"

129 FROM "issue" WHERE "id" = "issue_id_p";

130 SELECT "unit_id" INTO "unit_id_v"

131 FROM "area" WHERE "id" = "area_id_v";

132 ELSE

133 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';

134 END IF;

135 "visited_member_ids" := '{}';

136 "loop_member_id_v" := NULL;

137 "output_rows" := '{}';

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

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

140 "output_row"."member_valid" := TRUE;

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

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

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

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

145 LOOP

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

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

148 ELSE

149 "visited_member_ids" :=

150 "visited_member_ids" || "output_row"."member_id";

151 END IF;

152 IF "output_row"."participation" ISNULL THEN

153 "output_row"."overridden" := NULL;

154 ELSIF "output_row"."participation" THEN

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

156 END IF;

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

158 IF EXISTS (

159 SELECT NULL FROM "member" JOIN "privilege"

160 ON "privilege"."member_id" = "member"."id"

161 AND "privilege"."unit_id" = "unit_id_v"

162 WHERE "id" = "output_row"."member_id"

163 AND "member"."active" AND "privilege"."voting_right"

164 ) THEN

165 IF "scope_v" = 'unit' THEN

166 SELECT * INTO "delegation_row" FROM "delegation"

167 WHERE "truster_id" = "output_row"."member_id"

168 AND "unit_id" = "unit_id_v";

169 ELSIF "scope_v" = 'area' THEN

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

171 SELECT NULL FROM "membership"

172 WHERE "area_id" = "area_id_p"

173 AND "member_id" = "output_row"."member_id"

174 );

175 SELECT * INTO "delegation_row" FROM "delegation"

176 WHERE "truster_id" = "output_row"."member_id"

177 AND (

178 "unit_id" = "unit_id_v" OR

179 "area_id" = "area_id_v"

180 )

181 ORDER BY "scope" DESC;

182 ELSIF "scope_v" = 'issue' THEN

183 IF "issue_row"."fully_frozen" ISNULL THEN

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

185 SELECT NULL FROM "interest"

186 WHERE "issue_id" = "issue_id_p"

187 AND "member_id" = "output_row"."member_id"

188 );

189 ELSE

190 IF "output_row"."member_id" = "member_id_p" THEN

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

192 SELECT NULL FROM "direct_voter"

193 WHERE "issue_id" = "issue_id_p"

194 AND "member_id" = "output_row"."member_id"

195 );

196 ELSE

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

198 END IF;

199 END IF;

200 SELECT * INTO "delegation_row" FROM "delegation"

201 WHERE "truster_id" = "output_row"."member_id"

202 AND (

203 "unit_id" = "unit_id_v" OR

204 "area_id" = "area_id_v" OR

205 "issue_id" = "issue_id_p"

206 )

207 ORDER BY "scope" DESC;

208 END IF;

209 ELSE

210 "output_row"."member_valid" := FALSE;

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

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

213 "delegation_row" := ROW(NULL);

214 END IF;

215 IF

216 "output_row"."member_id" = "member_id_p" AND

217 "simulate_trustee_id_p" NOTNULL

218 THEN

219 "output_row"."scope_out" := "scope_v";

220 "output_rows" := "output_rows" || "output_row";

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

222 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

224 "output_rows" := "output_rows" || "output_row";

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

226 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

229 "output_rows" := "output_rows" || "output_row";

230 EXIT;

231 ELSE

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

233 "output_rows" := "output_rows" || "output_row";

234 EXIT;

235 END IF;

236 EXIT WHEN "loop_member_id_v" NOTNULL;

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

238 END LOOP;

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

240 "i" := 1;

241 "loop_v" := FALSE;

242 LOOP

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

244 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!

245 IF "loop_v" THEN

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

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

248 ELSIF "i" = "row_count" THEN

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

250 ELSE

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

252 END IF;

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

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

255 "loop_v" := TRUE;

256 END IF;

257 IF "scope_v" = 'unit' THEN

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

259 END IF;

260 RETURN NEXT "output_row";

261 "i" := "i" + 1;

262 END LOOP;

263 RETURN;

264 END;

265 $$;

266

267 COMMENT ON FUNCTION "delegation_chain"

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

269 "unit"."id"%TYPE,

270 "area"."id"%TYPE,

271 "issue"."id"%TYPE,

272 "member"."id"%TYPE )

273 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';

274

275 CREATE TYPE "delegation_info_loop_type" AS ENUM

276 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');

277

278 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';

279

280 CREATE TYPE "delegation_info_type" AS (

281 "own_participation" BOOLEAN,

282 "own_delegation_scope" "delegation_scope",

283 "first_trustee_id" INT4,

284 "first_trustee_participation" BOOLEAN,

285 "first_trustee_ellipsis" BOOLEAN,

286 "other_trustee_id" INT4,

287 "other_trustee_participation" BOOLEAN,

288 "other_trustee_ellipsis" BOOLEAN,

289 "delegation_loop" "delegation_info_loop_type");

290

291 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';

292

293 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';

294 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';

295 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';

296 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';

297 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';

298 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';

299 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';

300 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';

301 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';

302

303 CREATE FUNCTION "delegation_info"

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

305 "unit_id_p" "unit"."id"%TYPE,

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

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

308 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )

309 RETURNS "delegation_info_type"

310 LANGUAGE 'plpgsql' STABLE AS $$

311 DECLARE

312 "current_row" "delegation_chain_row";

313 "result" "delegation_info_type";

314 BEGIN

315 "result"."own_participation" := FALSE;

316 FOR "current_row" IN

317 SELECT * FROM "delegation_chain"(

318 "member_id_p",

319 "unit_id_p", "area_id_p", "issue_id_p",

320 "simulate_trustee_id_p")

321 LOOP

322 IF "current_row"."member_id" = "member_id_p" THEN

323 "result"."own_participation" := "current_row"."participation";

324 "result"."own_delegation_scope" := "current_row"."scope_out";

325 IF "current_row"."loop" = 'first' THEN

326 "result"."delegation_loop" := 'own';

327 END IF;

328 ELSIF

329 "current_row"."member_valid" AND

330 ( "current_row"."loop" ISNULL OR

331 "current_row"."loop" != 'repetition' )

332 THEN

333 IF "result"."first_trustee_id" ISNULL THEN

334 "result"."first_trustee_id" := "current_row"."member_id";

335 "result"."first_trustee_participation" := "current_row"."participation";

336 "result"."first_trustee_ellipsis" := FALSE;

337 IF "current_row"."loop" = 'first' THEN

338 "result"."delegation_loop" := 'first';

339 END IF;

340 ELSIF "result"."other_trustee_id" ISNULL THEN

341 IF "current_row"."participation" THEN

342 "result"."other_trustee_id" := "current_row"."member_id";

343 "result"."other_trustee_participation" := TRUE;

344 "result"."other_trustee_ellipsis" := FALSE;

345 IF "current_row"."loop" = 'first' THEN

346 "result"."delegation_loop" := 'other';

347 END IF;

348 ELSE

349 "result"."first_trustee_ellipsis" := TRUE;

350 IF "current_row"."loop" = 'first' THEN

351 "result"."delegation_loop" := 'first_ellipsis';

352 END IF;

353 END IF;

354 ELSE

355 "result"."other_trustee_ellipsis" := TRUE;

356 IF "current_row"."loop" = 'first' THEN

357 "result"."delegation_loop" := 'other_ellipsis';

358 END IF;

359 END IF;

360 END IF;

361 END LOOP;

362 RETURN "result";

363 END;

364 $$;

365

366 COMMENT ON FUNCTION "delegation_info"

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

368 "unit"."id"%TYPE,

369 "area"."id"%TYPE,

370 "issue"."id"%TYPE,

371 "member"."id"%TYPE )

372 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';

373

374 COMMIT;