if exists(select * from systable where table_name = 'FADbStatus') then 
    if exists (select * from "ADMIN"."FADbStatus" where major1 = 7 and major2 = 50 and minor1 = 0 and minor2 = 0) then
        RAISERROR 23000 'This FormAnalyzer database patch has allready been applied!'
    end if;
end if;

-------------------------------------------------
--   Create tables
-------------------------------------------------

CREATE TABLE "ADMIN"."Cleanup" (
	"id" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
	"status" INTEGER NOT NULL,
	"creator" CHAR(128) NOT NULL DEFAULT CURRENT USER,
	"created" TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
	"executed" TIMESTAMP NULL,
	CONSTRAINT "CLEANUP_PK" PRIMARY KEY ( "id" ASC )
)
go

GRANT SELECT ON "ADMIN"."Cleanup" TO "PUBLIC" 
go

CREATE TABLE "ADMIN"."ClpOptions" (
	"id" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
	"edoc" LONG VARCHAR NOT NULL,
	"editor" CHAR(128) NOT NULL DEFAULT CURRENT USER,
	"edited" TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
	CONSTRAINT "CLPOPTIONS_PK" PRIMARY KEY ( "id" )
)
go

GRANT SELECT ON "ADMIN"."ClpOptions" TO "PUBLIC" 
go

CREATE TABLE "ADMIN"."ClpFolder" (
	"id" INTEGER NOT NULL,
	CONSTRAINT "CLPFOLDER_PK" PRIMARY KEY ( "id" )
)
go

GRANT SELECT ON "ADMIN"."ClpFolder" TO "PUBLIC" 
go


-------------------------------------------------
--   Create indexes
-------------------------------------------------

CREATE INDEX "CLP_STATUS_IDX" ON "ADMIN"."Cleanup" ( "status" )
go

ALTER TABLE "ADMIN"."ClpFolder"
    ADD CONSTRAINT "CLP_FOLDER_FK" NOT NULL FOREIGN KEY ( "id" ) 
    REFERENCES "ADMIN"."Folder" ( "id" ) ON DELETE CASCADE
go

------------------------------------------------
--   Create views
-------------------------------------------------

CREATE VIEW "ADMIN"."ClpFolders" AS
WITH RECURSIVE Child(id, parent, name, level, path) AS
(
    SELECT F.id, F.parent, F.name, 0 AS level, FolderPath(F.id)
        FROM ADMIN.Folder F KEY JOIN ADMIN.ClpFolder
UNION ALL
    SELECT F.id, F.parent, F.name, level + 1, FolderPath(F.id)
        FROM ADMIN.Folder F INNER JOIN Child C ON F.parent = C.id WHERE F.id <> F.parent
)

SELECT * FROM Child
go

GRANT SELECT ON "ADMIN"."ClpFolders" TO "PUBLIC" 
go


-------------------------------------------------
--   Create triggers
-------------------------------------------------

CREATE TRIGGER "OnUpdate" BEFORE UPDATE ORDER 1 ON "ADMIN"."ClpOptions"
REFERENCING NEW AS rnew
FOR EACH ROW
BEGIN
    SET rnew.editor = current user;
    SET rnew.edited = current timestamp
END
go


-------------------------------------------------
--   Create events
-------------------------------------------------

CREATE EVENT "ADMIN"."AutoDelete"
DISABLE
HANDLER
BEGIN 
    INSERT INTO ADMIN.Cleanup(status) VALUES(0);
END
go


INSERT INTO "ADMIN"."FADbStatus"(id, major1, major2, minor1, minor2, "version", note) 
    VALUES(default, 7, 50, 0, 0, '7.50.0.0', 'FormAnalyzer Database Cleanup')
go

commit work
go
