/*
PostgreSQL Backup
Database: cas-ticket-registry/public
Backup Time: 2018-02-07 02:49:30
*/

DROP TABLE IF EXISTS "public"."locks";
DROP TABLE IF EXISTS "public"."saml2_artifacts";
DROP TABLE IF EXISTS "public"."saml2_attribute_query_tickets";
DROP TABLE IF EXISTS "public"."serviceticket";
DROP TABLE IF EXISTS "public"."ticketgrantingticket";
DROP FUNCTION IF EXISTS "public"."lo_manage()";
DROP FUNCTION IF EXISTS "public"."lo_oid(""public"".""lo"")";
CREATE TABLE "public"."locks" (
  "application_id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "expiration_date" timestamp(6),
  "unique_id" varchar(255) COLLATE "pg_catalog"."default",
  "lockver" int4 NOT NULL DEFAULT 0
)
;ALTER TABLE "public"."locks" OWNER TO "casapp";
CREATE TABLE "public"."saml2_artifacts" (
  "type" varchar(31) COLLATE "pg_catalog"."default" NOT NULL,
  "id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "number_of_times_used" int4,
  "creation_time" timestamp(6),
  "expiration_policy" oid(0) NOT NULL,
  "last_time_used" timestamp(6),
  "previous_last_time_used" timestamp(6),
  "authentication" oid(0) NOT NULL,
  "issuer" varchar(500) COLLATE "pg_catalog"."default",
  "relyingparty" varchar(500) COLLATE "pg_catalog"."default",
  "samlobject" text COLLATE "pg_catalog"."default",
  "service" oid(0) NOT NULL,
  "ticketgrantingticket_id" varchar(255) COLLATE "pg_catalog"."default"
)
;ALTER TABLE "public"."saml2_artifacts" OWNER TO "casapp";
CREATE TABLE "public"."saml2_attribute_query_tickets" (
  "type" varchar(31) COLLATE "pg_catalog"."default" NOT NULL,
  "id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "number_of_times_used" int4,
  "creation_time" timestamp(6),
  "expiration_policy" oid(0) NOT NULL,
  "last_time_used" timestamp(6),
  "previous_last_time_used" timestamp(6),
  "relyingparty" varchar(255) COLLATE "pg_catalog"."default",
  "samlobject" text COLLATE "pg_catalog"."default",
  "service" oid(0) NOT NULL,
  "ticketgrantingticket_id" varchar(255) COLLATE "pg_catalog"."default"
)
;ALTER TABLE "public"."saml2_attribute_query_tickets" OWNER TO "casapp";
CREATE TABLE "public"."serviceticket" (
  "type" varchar(31) COLLATE "pg_catalog"."default" NOT NULL,
  "id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "number_of_times_used" int4,
  "creation_time" timestamp(6),
  "expiration_policy" oid(0) NOT NULL,
  "last_time_used" timestamp(6),
  "previous_last_time_used" timestamp(6),
  "from_new_login" bool NOT NULL,
  "ticket_already_granted" bool NOT NULL,
  "service" oid(0) NOT NULL,
  "ticketgrantingticket_id" varchar(255) COLLATE "pg_catalog"."default"
)
;ALTER TABLE "public"."serviceticket" OWNER TO "casapp";
CREATE TABLE "public"."ticketgrantingticket" (
  "type" varchar(31) COLLATE "pg_catalog"."default" NOT NULL,
  "id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "number_of_times_used" int4,
  "creation_time" timestamp(6),
  "expiration_policy" oid(0) NOT NULL,
  "last_time_used" timestamp(6),
  "previous_last_time_used" timestamp(6),
  "authentication" oid(0) NOT NULL,
  "descendant_tickets" oid(0) NOT NULL,
  "expired" bool NOT NULL,
  "proxied_by" oid(0),
  "proxy_granting_tickets" oid(0) NOT NULL,
  "services_granted_access_to" oid(0) NOT NULL,
  "ticketgrantingticket_id" varchar(255) COLLATE "pg_catalog"."default"
)
;ALTER TABLE "public"."ticketgrantingticket" OWNER TO "casapp";
CREATE OR REPLACE FUNCTION "public"."lo_manage"()
  RETURNS "pg_catalog"."trigger" AS '$libdir/lo', 'lo_manage'
  LANGUAGE c VOLATILE
  COST 1;ALTER FUNCTION "public"."lo_manage"() OWNER TO "mkromarek";
CREATE OR REPLACE FUNCTION "public"."lo_oid"("public"."lo")
  RETURNS "pg_catalog"."oid" AS $BODY$SELECT $1::pg_catalog.oid$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;ALTER FUNCTION "public"."lo_oid"("public"."lo") OWNER TO "mkromarek";
BEGIN;
LOCK TABLE "public"."locks" IN SHARE MODE;
DELETE FROM "public"."locks";
INSERT INTO "public"."locks" ("application_id","expiration_date","unique_id","lockver") VALUES ('cas-ticket-registry-cleaner', NULL, NULL, 1);
COMMIT;
BEGIN;
LOCK TABLE "public"."saml2_artifacts" IN SHARE MODE;
DELETE FROM "public"."saml2_artifacts";
COMMIT;
BEGIN;
LOCK TABLE "public"."saml2_attribute_query_tickets" IN SHARE MODE;
DELETE FROM "public"."saml2_attribute_query_tickets";
COMMIT;
BEGIN;
LOCK TABLE "public"."serviceticket" IN SHARE MODE;
DELETE FROM "public"."serviceticket";
COMMIT;
BEGIN;
LOCK TABLE "public"."ticketgrantingticket" IN SHARE MODE;
DELETE FROM "public"."ticketgrantingticket";
COMMIT;
ALTER TABLE "public"."locks" ADD CONSTRAINT "locks_pkey" PRIMARY KEY ("application_id");
ALTER TABLE "public"."saml2_artifacts" ADD CONSTRAINT "saml2_artifacts_pkey" PRIMARY KEY ("id");
ALTER TABLE "public"."saml2_artifacts" ADD CONSTRAINT "fknhj7y3ubnj4xdghefiw1rgyqe" FOREIGN KEY ("ticketgrantingticket_id") REFERENCES "public"."ticketgrantingticket" ("id") ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE "public"."saml2_attribute_query_tickets" ADD CONSTRAINT "saml2_attribute_query_tickets_pkey" PRIMARY KEY ("id");
ALTER TABLE "public"."saml2_attribute_query_tickets" ADD CONSTRAINT "fk2w4i8q7blrtx0ksgnoohgc9uh" FOREIGN KEY ("ticketgrantingticket_id") REFERENCES "public"."ticketgrantingticket" ("id") ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE "public"."serviceticket" ADD CONSTRAINT "serviceticket_pkey" PRIMARY KEY ("id");
ALTER TABLE "public"."serviceticket" ADD CONSTRAINT "fk60oigifivx01ts3n8vboyqs38" FOREIGN KEY ("ticketgrantingticket_id") REFERENCES "public"."ticketgrantingticket" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE TRIGGER "t_exp_policy" BEFORE UPDATE OR DELETE ON "public"."serviceticket"
FOR EACH ROW
EXECUTE PROCEDURE "public"."lo_manage"('expiration_policy');
CREATE TRIGGER "t_service" BEFORE UPDATE OR DELETE ON "public"."serviceticket"
FOR EACH ROW
EXECUTE PROCEDURE "public"."lo_manage"('service');
ALTER TABLE "public"."ticketgrantingticket" ADD CONSTRAINT "ticketgrantingticket_pkey" PRIMARY KEY ("id");
ALTER TABLE "public"."ticketgrantingticket" ADD CONSTRAINT "fkiqyu3qw2fxf5qaqin02mox8r4" FOREIGN KEY ("ticketgrantingticket_id") REFERENCES "public"."ticketgrantingticket" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
CREATE TRIGGER "t_authentication" BEFORE UPDATE OR DELETE ON "public"."ticketgrantingticket"
FOR EACH ROW
EXECUTE PROCEDURE "public"."lo_manage"('authentication');
CREATE TRIGGER "t_descendant_tickets" BEFORE UPDATE OR DELETE ON "public"."ticketgrantingticket"
FOR EACH ROW
EXECUTE PROCEDURE "public"."lo_manage"('descendant_tickets');
CREATE TRIGGER "t_exp_policy" BEFORE UPDATE OR DELETE ON "public"."ticketgrantingticket"
FOR EACH ROW
EXECUTE PROCEDURE "public"."lo_manage"('expiration_policy');
CREATE TRIGGER "t_proxied_by" BEFORE UPDATE OR DELETE ON "public"."ticketgrantingticket"
FOR EACH ROW
EXECUTE PROCEDURE "public"."lo_manage"('proxied_by');
CREATE TRIGGER "t_proxy_granting_tickets" BEFORE UPDATE OR DELETE ON "public"."ticketgrantingticket"
FOR EACH ROW
EXECUTE PROCEDURE "public"."lo_manage"('proxy_granting_tickets');
CREATE TRIGGER "t_services_granted" BEFORE UPDATE OR DELETE ON "public"."ticketgrantingticket"
FOR EACH ROW
EXECUTE PROCEDURE "public"."lo_manage"('services_granted_access_to');
