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

-------------------------------------------------
--   Alter tables
-------------------------------------------------

ALTER TABLE "ADMIN"."Document" add fsource char(255);
ALTER TABLE "ADMIN"."Document" add fext char(16);

commit work
go

ALTER TABLE "ADMIN"."ExportImage" drop foreign key "EXPORTIMAGE_IMAGEEXTENSION_FK"
ALTER TABLE "ADMIN"."ExportImage" rename extid to fext
ALTER TABLE "ADMIN"."ExportImage" alter fext char(16) NOT NULL

commit work
go

ALTER TABLE "ADMIN"."Recycle" drop extid;
ALTER TABLE "ADMIN"."Recycle" add fsource bit;
ALTER TABLE "ADMIN"."Recycle" add fext char(16);

commit work
go

------------------------------------------------
--   Alter views
-------------------------------------------------

ALTER VIEW "ADMIN"."Content"
  as select ifnull(D.docid,1,2) as type,
    B.obid as id,
    B.parent,
    ifnull(D.docid,B.name,'ID: ' || D.docid) as name,
    D.fext,
    B.creator,
    B.created,
    B.volid,
    B.hold,
    B.expid,
    B.archid,
    B.status,
    B.error,
    B.cfgid,
    B.doctempl,
    B.imgctrl,
    B.imgctrled,
    B.imgctrlspan,
    B.classifier,
    B.classified,
    B.clsspan,
    B.verifier1,
    B.verified1,
    B.ver1span,
    B.ver1quality,
    B.verifier2,
    B.verified2,
    B.ver2span,
    B.ver2quality,
    B.arbiter,
    B.arbited,
    B.arbspan,
    B.arbquality,
    B.certifier,
    B.certified,
    B.cerspan,
    B.cerquality,
    B.controller,
    B.controlled,
    B.conspan,
    P.endorser
    from(ADMIN.Boxes as B left outer join ADMIN.Document as D on B.obid = D.obid)
      left outer join ADMIN.Page as P on D.obid = P.obid and P.number = 1

commit work
go

ALTER VIEW "ADMIN"."Documents"
  as select ADMIN.Document.obid,
    ADMIN.Document.docid,
    ADMIN.Document.boxid,
    ADMIN.Document.number,
    ADMIN.Document.errauto,
    ADMIN.Document.fext,
    ADMIN.Object.status,
    ADMIN.Object.error,
    ADMIN.Object.creator,
    ADMIN.Object.created,
    ADMIN.Object.cfgid,
    ADMIN.Object.doctempl,
    H1.creator as verifier1,
    H1.created as verified1,
    H1.span as ver1span,
    H1.quality as ver1quality,
    H2.creator as verifier2,
    H2.created as verified2,
    H2.span as ver2span,
    H2.quality as ver2quality,
    H3.creator as arbiter,
    H3.created as arbited,
    H3.span as arbspan,
    H3.quality as arbquality,
    H4.creator as certifier,
    H4.created as certified,
    H4.span as cerspan,
    H4.quality as cerquality,
    H5.creator as controller,
    H5.created as controlled,
    H5.span as conspan
    from(((((ADMIN.Document key join ADMIN.Object)
      left outer join ADMIN.History as H1 on ADMIN.Object.verifier1id = H1.id)
      left outer join ADMIN.History as H2 on ADMIN.Object.verifier2id = H2.id)
      left outer join ADMIN.History as H3 on ADMIN.Object.arbiterid = H3.id)
      left outer join ADMIN.History as H4 on ADMIN.Object.certifierid = H4.id)
      left outer join ADMIN.History as H5 on ADMIN.Object.controllerid = H5.id

commit work
go

ALTER VIEW "ADMIN"."RecycleFiles"
  as select R.pageid, V.path, R.fsource, isnull(R.fext, 'TIF') as extension
    from ADMIN.Recycle as R join ADMIN.Volume as V on R.volid = V.id
      where(R.spid = @@spid)

commit work
go

-------------------------------------------------
--   Alter triggers
-------------------------------------------------

ALTER TRIGGER "OnRecycle" before delete order 1 on ADMIN.Box
referencing old as rold
for each row begin
  insert into ADMIN.Recycle(
    select @@spid,rold.volid,id,0,null from ADMIN.Page
      where obid = any(select obid from ADMIN.Document where boxid = rold.obid));

  insert into ADMIN.Recycle(
    select @@spid,rold.volid,docid,1,fext from ADMIN.Document 
      where boxid = rold.obid and fext is not null);
  
  insert into ADMIN.Recycle(
    select @@spid,rold.volid,id,0,'INT' from ADMIN.PageTemp
      where obid = rold.obid)
end;

commit work
go

ALTER TRIGGER "OnDelete" before delete order 1 on ADMIN.Document
referencing old as rold
for each row begin
  insert into ADMIN.Recycle(
    select @@spid,B.volid,P.id,0,null from ADMIN.Page as P,ADMIN.Box as B
      where P.obid = rold.obid and B.obid = rold.boxid);
  delete from ADMIN.Object where id = rold.obid
end;

commit work
go

ALTER TRIGGER "OnDelete" after delete order 1 on
ADMIN.ExportImage
referencing old as rold
for each row begin
  insert into ADMIN.Recycle values(@@spid,rold.volid,rold.id,0,rold.fext) 
end;

commit work
go



INSERT INTO "ADMIN"."FADbStatus"(id, major1, major2, minor1, minor2, "version", note) 
    VALUES(default, 7, 53, 0, 0, '7.53.0.0', 'Eksport podpisanych cyfrowo oryginałów dokumentów')
go

commit work
go
