liquid_feedback_core: 05eb08f56f09 update/core-update.v2.0.8-v2.0.9.sql

liquid_feedback_core

view update/core-update.v2.0.8-v2.0.9.sql @ 248:05eb08f56f09

Bugfix in view "unit_member_count"

author	jbe
date	Sat May 12 18:33:56 2012 +0200 (2012-05-12)
parents	4b8cc6fc2d00
children	d4c754c4aeaa

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 CREATE OR REPLACE VIEW "unit_member_count" AS

8 SELECT

9 "unit"."id" AS "unit_id",

10 count("member"."id") AS "member_count"

11 FROM "unit"

12 LEFT JOIN "privilege"

13 ON "privilege"."unit_id" = "unit"."id"

14 AND "privilege"."voting_right"

15 LEFT JOIN "member"

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

17 AND "member"."active"

18 GROUP BY "unit"."id";

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

22 CREATE FUNCTION "delegation_chain_for_closed_issue"

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

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

25 RETURNS SETOF "delegation_chain_row"

26 LANGUAGE 'plpgsql' STABLE AS $$

27 DECLARE

28 "output_row" "delegation_chain_row";

29 "direct_voter_row" "direct_voter"%ROWTYPE;

30 "delegating_voter_row" "delegating_voter"%ROWTYPE;

31 BEGIN

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

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

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

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

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

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

38 LOOP

39 SELECT INTO "direct_voter_row" * FROM "direct_voter"

40 WHERE "issue_id" = "issue_id_p"

41 AND "member_id" = "output_row"."member_id";

42 IF "direct_voter_row"."member_id" NOTNULL THEN

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

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

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

46 RETURN NEXT "output_row";

47 RETURN;

48 END IF;

49 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"

50 WHERE "issue_id" = "issue_id_p"

51 AND "member_id" = "output_row"."member_id";

52 IF "delegating_voter_row"."member_id" ISNULL THEN

53 RETURN;

54 END IF;

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

56 RETURN NEXT "output_row";

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

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

59 END LOOP;

60 END;

61 $$;

63 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"

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

65 "member"."id"%TYPE )

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

68 DROP FUNCTION "delegation_chain"

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

70 "unit"."id"%TYPE,

71 "area"."id"%TYPE,

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

74 DROP FUNCTION "delegation_chain"

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

76 "unit"."id"%TYPE,

77 "area"."id"%TYPE,

78 "issue"."id"%TYPE,

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

81 CREATE FUNCTION "delegation_chain"

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

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

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

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

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

87 RETURNS SETOF "delegation_chain_row"

88 LANGUAGE 'plpgsql' STABLE AS $$

89 DECLARE

90 "scope_v" "delegation_scope";

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

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

93 "issue_row" "issue"%ROWTYPE;

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

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

96 "output_row" "delegation_chain_row";

97 "output_rows" "delegation_chain_row"[];

98 "delegation_row" "delegation"%ROWTYPE;

99 "row_count" INT4;

100 "i" INT4;

101 "loop_v" BOOLEAN;

102 BEGIN

103 IF

104 "unit_id_p" NOTNULL AND

105 "area_id_p" ISNULL AND

106 "issue_id_p" ISNULL

107 THEN

108 "scope_v" := 'unit';

109 "unit_id_v" := "unit_id_p";

110 ELSIF

111 "unit_id_p" ISNULL AND

112 "area_id_p" NOTNULL AND

113 "issue_id_p" ISNULL

114 THEN

115 "scope_v" := 'area';

116 "area_id_v" := "area_id_p";

117 SELECT "unit_id" INTO "unit_id_v"

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

119 ELSIF

120 "unit_id_p" ISNULL AND

121 "area_id_p" ISNULL AND

122 "issue_id_p" NOTNULL

123 THEN

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

125 IF "issue_row"."id" ISNULL THEN

126 RETURN;

127 END IF;

128 IF "issue_row"."closed" NOTNULL THEN

129 IF "simulate_trustee_id_p" NOTNULL THEN

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

131 END IF;

132 FOR "output_row" IN

133 SELECT * FROM

134 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

135 LOOP

136 RETURN NEXT "output_row";

137 END LOOP;

138 RETURN;

139 END IF;

140 "scope_v" := 'issue';

141 SELECT "area_id" INTO "area_id_v"

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

143 SELECT "unit_id" INTO "unit_id_v"

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

145 ELSE

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

147 END IF;

148 "visited_member_ids" := '{}';

149 "loop_member_id_v" := NULL;

150 "output_rows" := '{}';

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

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

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

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

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

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

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

158 LOOP

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

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

161 ELSE

162 "visited_member_ids" :=

163 "visited_member_ids" || "output_row"."member_id";

164 END IF;

165 IF "output_row"."participation" ISNULL THEN

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

167 ELSIF "output_row"."participation" THEN

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

169 END IF;

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

171 IF EXISTS (

172 SELECT NULL FROM "member" JOIN "privilege"

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

174 AND "privilege"."unit_id" = "unit_id_v"

175 WHERE "id" = "output_row"."member_id"

176 AND "member"."active" AND "privilege"."voting_right"

177 ) THEN

178 IF "scope_v" = 'unit' THEN

179 SELECT * INTO "delegation_row" FROM "delegation"

180 WHERE "truster_id" = "output_row"."member_id"

181 AND "unit_id" = "unit_id_v";

182 ELSIF "scope_v" = 'area' THEN

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

184 SELECT NULL FROM "membership"

185 WHERE "area_id" = "area_id_p"

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

187 );

188 SELECT * INTO "delegation_row" FROM "delegation"

189 WHERE "truster_id" = "output_row"."member_id"

190 AND (

191 "unit_id" = "unit_id_v" OR

192 "area_id" = "area_id_v"

193 )

194 ORDER BY "scope" DESC;

195 ELSIF "scope_v" = 'issue' THEN

196 IF "issue_row"."fully_frozen" ISNULL THEN

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

198 SELECT NULL FROM "interest"

199 WHERE "issue_id" = "issue_id_p"

200 AND "member_id" = "output_row"."member_id"

201 );

202 ELSE

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

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

205 SELECT NULL FROM "direct_voter"

206 WHERE "issue_id" = "issue_id_p"

207 AND "member_id" = "output_row"."member_id"

208 );

209 ELSE

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

211 END IF;

212 END IF;

213 SELECT * INTO "delegation_row" FROM "delegation"

214 WHERE "truster_id" = "output_row"."member_id"

215 AND (

216 "unit_id" = "unit_id_v" OR

217 "area_id" = "area_id_v" OR

218 "issue_id" = "issue_id_p"

219 )

220 ORDER BY "scope" DESC;

221 END IF;

222 ELSE

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

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

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

226 "delegation_row" := ROW(NULL);

227 END IF;

228 IF

229 "output_row"."member_id" = "member_id_p" AND

230 "simulate_trustee_id_p" NOTNULL

231 THEN

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

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

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

235 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

237 "output_rows" := "output_rows" || "output_row";

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

239 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

242 "output_rows" := "output_rows" || "output_row";

243 EXIT;

244 ELSE

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

246 "output_rows" := "output_rows" || "output_row";

247 EXIT;

248 END IF;

249 EXIT WHEN "loop_member_id_v" NOTNULL;

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

251 END LOOP;

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

253 "i" := 1;

254 "loop_v" := FALSE;

255 LOOP

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

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

258 IF "loop_v" THEN

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

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

261 ELSIF "i" = "row_count" THEN

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

263 ELSE

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

265 END IF;

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

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

268 "loop_v" := TRUE;

269 END IF;

270 IF "scope_v" = 'unit' THEN

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

272 END IF;

273 RETURN NEXT "output_row";

274 "i" := "i" + 1;

275 END LOOP;

276 RETURN;

277 END;

278 $$;

279

280 COMMENT ON FUNCTION "delegation_chain"

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

282 "unit"."id"%TYPE,

283 "area"."id"%TYPE,

284 "issue"."id"%TYPE,

285 "member"."id"%TYPE )

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

287

288 CREATE TYPE "delegation_info_loop_type" AS ENUM

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

290

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

292

293 CREATE TYPE "delegation_info_type" AS (

294 "own_participation" BOOLEAN,

295 "own_delegation_scope" "delegation_scope",

296 "first_trustee_id" INT4,

297 "first_trustee_participation" BOOLEAN,

298 "first_trustee_ellipsis" BOOLEAN,

299 "other_trustee_id" INT4,

300 "other_trustee_participation" BOOLEAN,

301 "other_trustee_ellipsis" BOOLEAN,

302 "delegation_loop" "delegation_info_loop_type");

303

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

305

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

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

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

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

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

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

312 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)';

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

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

315

316 CREATE FUNCTION "delegation_info"

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

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

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

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

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

322 RETURNS "delegation_info_type"

323 LANGUAGE 'plpgsql' STABLE AS $$

324 DECLARE

325 "current_row" "delegation_chain_row";

326 "result" "delegation_info_type";

327 BEGIN

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

329 FOR "current_row" IN

330 SELECT * FROM "delegation_chain"(

331 "member_id_p",

332 "unit_id_p", "area_id_p", "issue_id_p",

333 "simulate_trustee_id_p")

334 LOOP

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

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

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

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

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

340 END IF;

341 ELSIF

342 "current_row"."member_valid" AND

343 ( "current_row"."loop" ISNULL OR

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

345 THEN

346 IF "result"."first_trustee_id" ISNULL THEN

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

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

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

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

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

352 END IF;

353 ELSIF "result"."other_trustee_id" ISNULL THEN

354 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN

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

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

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

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

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

360 END IF;

361 ELSE

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

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

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

365 END IF;

366 END IF;

367 ELSE

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

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

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

371 END IF;

372 END IF;

373 END IF;

374 END LOOP;

375 RETURN "result";

376 END;

377 $$;

378

379 COMMENT ON FUNCTION "delegation_info"

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

381 "unit"."id"%TYPE,

382 "area"."id"%TYPE,

383 "issue"."id"%TYPE,

384 "member"."id"%TYPE )

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

386

387 COMMIT;