1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('4.0.1-dev', 4, 0, -1))

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

6

7 ALTER TABLE "member" ADD COLUMN "role" BOOLEAN NOT NULL DEFAULT FALSE;

8

9 CREATE TABLE "agent" (

10 PRIMARY KEY ("controlled_id", "controller_id"),

11 "controlled_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

12 "controller_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

13 CONSTRAINT "controlled_id_and_controller_id_differ" CHECK (

14 "controlled_id" != "controller_id" ) );

15 CREATE INDEX "agent_controller_id_idx" ON "agent" ("controller_id");

16

17 COMMENT ON TABLE "agent" IS 'Privileges for role accounts';

18

19 ALTER TABLE "session" ADD COLUMN "real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

20

21 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in, or role account in use';

22 COMMENT ON COLUMN "session"."real_member_id" IS 'Reference to member, who is really logged in (real person rather than role account)';

23

24 CREATE TABLE "role_verification" (

25 "id" SERIAL8 PRIMARY KEY,

26 "requested" TIMESTAMPTZ,

27 "request_origin" JSONB,

28 "request_data" JSONB,

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

30 "requesting_real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

31 "verifying_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

32 "verified_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

33 "verified" TIMESTAMPTZ,

34 "verification_data" JSONB,

35 "denied" TIMESTAMPTZ,

36 "comment" TEXT,

37 CONSTRAINT "verified_and_denied_conflict" CHECK (

38 "verified" ISNULL OR "denied" ISNULL ) );

39 CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested");

40 CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;

41 CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id");

42 CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id");

43 CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified");

44 CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied");

45

46 COMMENT ON TABLE "role_verification" IS 'Request to verify a role account (see table "verification" for documentation of columns not documented for this table)';

47

48 COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify';

49 COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification';

50

51 ALTER TABLE "ignored_area" DROP CONSTRAINT "ignored_area_member_id_fkey";

52 ALTER TABLE "ignored_area" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

53

54 CREATE OR REPLACE VIEW "expired_token" AS

55 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (

56 "token_type" = 'authorization' AND "used" AND EXISTS (

57 SELECT NULL FROM "token" AS "other"

58 WHERE "other"."authorization_token_id" = "token"."id" ) );

59

60 ALTER TABLE "system_application" RENAME COLUMN "discovery_baseurl" TO "base_url";

61 ALTER TABLE "system_application" ADD COLUMN "manifest_url" TEXT;

62

63 COMMENT ON COLUMN "system_application"."base_url" IS 'Base URL for users';

64 COMMENT ON COLUMN "system_application"."manifest_url" IS 'URL referring to a manifest that can be used for application (type/version) discovery';

65

66 COMMIT;