| rev |
line source |
|
jbe@574
|
1 BEGIN;
|
|
jbe@574
|
2
|
|
jbe@574
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@579
|
4 SELECT * FROM (VALUES ('4.0.1-dev', 4, 0, -1))
|
|
jbe@574
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@574
|
6
|
|
jbe@578
|
7 ALTER TABLE "member" ADD COLUMN "role" BOOLEAN NOT NULL DEFAULT FALSE;
|
|
jbe@578
|
8
|
|
jbe@578
|
9 CREATE TABLE "agent" (
|
|
jbe@578
|
10 PRIMARY KEY ("controlled_id", "controller_id"),
|
|
jbe@578
|
11 "controlled_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@578
|
12 "controller_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@578
|
13 CONSTRAINT "controlled_id_and_controller_id_differ" CHECK (
|
|
jbe@578
|
14 "controlled_id" != "controller_id" ) );
|
|
jbe@578
|
15 CREATE INDEX "agent_controller_id_idx" ON "agent" ("controller_id");
|
|
jbe@578
|
16
|
|
jbe@578
|
17 COMMENT ON TABLE "agent" IS 'Privileges for role accounts';
|
|
jbe@578
|
18
|
|
jbe@580
|
19 ALTER TABLE "session" ADD COLUMN "real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
jbe@580
|
20
|
|
jbe@580
|
21 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in, or role account in use';
|
|
jbe@580
|
22 COMMENT ON COLUMN "session"."real_member_id" IS 'Reference to member, who is really logged in (real person rather than role account)';
|
|
jbe@580
|
23
|
|
jbe@580
|
24 CREATE TABLE "role_verification" (
|
|
jbe@580
|
25 "id" SERIAL8 PRIMARY KEY,
|
|
jbe@580
|
26 "requested" TIMESTAMPTZ,
|
|
jbe@580
|
27 "request_origin" JSONB,
|
|
jbe@580
|
28 "request_data" JSONB,
|
|
jbe@580
|
29 "requesting_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@580
|
30 "requesting_real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@580
|
31 "verifying_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@580
|
32 "verified_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@580
|
33 "verified" TIMESTAMPTZ,
|
|
jbe@580
|
34 "verification_data" JSONB,
|
|
jbe@580
|
35 "denied" TIMESTAMPTZ,
|
|
jbe@580
|
36 "comment" TEXT,
|
|
jbe@580
|
37 CONSTRAINT "verified_and_denied_conflict" CHECK (
|
|
jbe@580
|
38 "verified" ISNULL OR "denied" ISNULL ) );
|
|
jbe@580
|
39 CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested");
|
|
jbe@580
|
40 CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;
|
|
jbe@580
|
41 CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id");
|
|
jbe@580
|
42 CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id");
|
|
jbe@580
|
43 CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified");
|
|
jbe@580
|
44 CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied");
|
|
jbe@580
|
45
|
|
jbe@580
|
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)';
|
|
jbe@580
|
47
|
|
jbe@580
|
48 COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify';
|
|
jbe@580
|
49 COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification';
|
|
jbe@580
|
50
|
|
jbe@579
|
51 ALTER TABLE "ignored_area" DROP CONSTRAINT "ignored_area_member_id_fkey";
|
|
jbe@579
|
52 ALTER TABLE "ignored_area" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
jbe@579
|
53
|
|
jbe@574
|
54 CREATE OR REPLACE VIEW "expired_token" AS
|
|
jbe@574
|
55 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
|
|
jbe@574
|
56 "token_type" = 'authorization' AND "used" AND EXISTS (
|
|
jbe@574
|
57 SELECT NULL FROM "token" AS "other"
|
|
jbe@574
|
58 WHERE "other"."authorization_token_id" = "token"."id" ) );
|
|
jbe@574
|
59
|
|
jbe@575
|
60 ALTER TABLE "system_application" RENAME COLUMN "discovery_baseurl" TO "base_url";
|
|
jbe@575
|
61 ALTER TABLE "system_application" ADD COLUMN "manifest_url" TEXT;
|
|
jbe@575
|
62
|
|
jbe@576
|
63 COMMENT ON COLUMN "system_application"."base_url" IS 'Base URL for users';
|
|
jbe@576
|
64 COMMENT ON COLUMN "system_application"."manifest_url" IS 'URL referring to a manifest that can be used for application (type/version) discovery';
|
|
jbe@575
|
65
|
|
jbe@574
|
66 COMMIT;
|