jbe@574: BEGIN;
jbe@574: 
jbe@574: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@579:   SELECT * FROM (VALUES ('4.0.1-dev', 4, 0, -1))
jbe@574:   AS "subquery"("string", "major", "minor", "revision");
jbe@574: 
jbe@578: ALTER TABLE "member" ADD COLUMN "role" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@578: 
jbe@578: CREATE TABLE "agent" (
jbe@578:         PRIMARY KEY ("controlled_id", "controller_id"),
jbe@578:         "controlled_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@578:         "controller_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@578:         CONSTRAINT "controlled_id_and_controller_id_differ" CHECK (
jbe@578:             "controlled_id" != "controller_id" ) );
jbe@578: CREATE INDEX "agent_controller_id_idx" ON "agent" ("controller_id");
jbe@578: 
jbe@578: COMMENT ON TABLE "agent" IS 'Privileges for role accounts';
jbe@578: 
jbe@580: ALTER TABLE "session" ADD COLUMN "real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@580: 
jbe@580: COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in, or role account in use';
jbe@580: COMMENT ON COLUMN "session"."real_member_id"    IS 'Reference to member, who is really logged in (real person rather than role account)';
jbe@580: 
jbe@580: CREATE TABLE "role_verification" (
jbe@580:         "id"                    SERIAL8         PRIMARY KEY,
jbe@580:         "requested"             TIMESTAMPTZ,
jbe@580:         "request_origin"        JSONB,
jbe@580:         "request_data"          JSONB,
jbe@580:         "requesting_member_id"  INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@580:         "requesting_real_member_id"  INT4       REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@580:         "verifying_member_id"   INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@580:         "verified_member_id"    INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@580:         "verified"              TIMESTAMPTZ,
jbe@580:         "verification_data"     JSONB,
jbe@580:         "denied"                TIMESTAMPTZ,
jbe@580:         "comment"               TEXT,
jbe@580:         CONSTRAINT "verified_and_denied_conflict" CHECK (
jbe@580:           "verified" ISNULL OR "denied" ISNULL ) );
jbe@580: CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested");
jbe@580: CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;
jbe@580: CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id");
jbe@580: CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id");
jbe@580: CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified");
jbe@580: CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied");
jbe@580: 
jbe@580: 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: 
jbe@580: COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify';
jbe@580: COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification';
jbe@580: 
jbe@579: ALTER TABLE "ignored_area" DROP CONSTRAINT "ignored_area_member_id_fkey";
jbe@579: ALTER TABLE "ignored_area" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@579: 
jbe@574: CREATE OR REPLACE VIEW "expired_token" AS
jbe@574:   SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
jbe@574:     "token_type" = 'authorization' AND "used" AND EXISTS (
jbe@574:       SELECT NULL FROM "token" AS "other"
jbe@574:       WHERE "other"."authorization_token_id" = "token"."id" ) );
jbe@574: 
jbe@575: ALTER TABLE "system_application" RENAME COLUMN "discovery_baseurl" TO "base_url";
jbe@575: ALTER TABLE "system_application" ADD COLUMN "manifest_url" TEXT;
jbe@575: 
jbe@576: COMMENT ON COLUMN "system_application"."base_url"     IS 'Base URL for users';
jbe@576: COMMENT ON COLUMN "system_application"."manifest_url" IS 'URL referring to a manifest that can be used for application (type/version) discovery';
jbe@575: 
jbe@574: COMMIT;