annotate update/core-update.v4.0.0-v4.0.1.sql @ 579:f6b84db4dd0c
Bugfix in index "ignored_area_member_id_fkey"
author |
jbe |
date |
Fri Mar 02 16:52:32 2018 +0100 (2018-03-02) |
parents |
02a6149822e0 |
children |
78f6833f5f19 |
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@579
|
19 ALTER TABLE "ignored_area" DROP CONSTRAINT "ignored_area_member_id_fkey";
|
jbe@579
|
20 ALTER TABLE "ignored_area" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@579
|
21
|
jbe@574
|
22 CREATE OR REPLACE VIEW "expired_token" AS
|
jbe@574
|
23 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
|
jbe@574
|
24 "token_type" = 'authorization' AND "used" AND EXISTS (
|
jbe@574
|
25 SELECT NULL FROM "token" AS "other"
|
jbe@574
|
26 WHERE "other"."authorization_token_id" = "token"."id" ) );
|
jbe@574
|
27
|
jbe@575
|
28 ALTER TABLE "system_application" RENAME COLUMN "discovery_baseurl" TO "base_url";
|
jbe@575
|
29 ALTER TABLE "system_application" ADD COLUMN "manifest_url" TEXT;
|
jbe@575
|
30
|
jbe@576
|
31 COMMENT ON COLUMN "system_application"."base_url" IS 'Base URL for users';
|
jbe@576
|
32 COMMENT ON COLUMN "system_application"."manifest_url" IS 'URL referring to a manifest that can be used for application (type/version) discovery';
|
jbe@575
|
33
|
jbe@574
|
34 COMMIT;
|