-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."oledocument"
-- Table comment: Pointers to documents belonging to People & Companies. Stored in the Blobstore (Class O)
-- Statement count: 13
CREATE TABLE "pears"."oledocument" (
"oledocumentid" CHAR(20) NOT NULL
,"description" CHAR(50) NULL
,"ownertype" CHAR(2) NULL
,"ownerid" CHAR(20) NULL
,"RawFile" tinyint NULL
,"FileExtension" CHAR(5) NULL
,"DocumentTypeID" CHAR(20) NULL
,"ExternalFilePath" long VARCHAR NULL
,"LinkCreatedBy" CHAR(25) NULL
,"LinkCreatedDateTime" TIMESTAMP NULL
,"WhenArchived" TIMESTAMP NULL
,"WhoArchived" CHAR(20) NULL
,"WhenRedacted" TIMESTAMP NULL
,"WhoRedacted" CHAR(20) NULL
,"GlobalDoc" SMALLINT NULL
,"Defunct" tinyint NULL DEFAULT 0
,"deletiondate" DATE NULL
,"Signed" SMALLINT NULL
,"ExpiryDate" DATE NULL
,PRIMARY KEY ("oledocumentid" ASC)
)
GO
COMMENT ON COLUMN "pears"."oledocument"."RawFile" IS
'Set if storing the original file NOT in an OLE wrapper'
GO
COMMENT ON COLUMN "pears"."oledocument"."FileExtension" IS
'If RawFile'
GO
COMMENT ON TABLE "pears"."oledocument" IS
'Pointers to documents belonging to People & Companies. Stored in the Blobstore (Class O)'
GO
ALTER TABLE "pears"."oledocument"
ADD FOREIGN KEY "DocumentType" ("DocumentTypeID" ASC)
REFERENCES "pears"."DocumentType" ("DocumentTypeID")
ON DELETE SET NULL
GO
CREATE INDEX "oledoc_owner" ON "pears"."oledocument"
( "ownertype","ownerid" )
GO
CREATE INDEX "oledocumentOwneridDocumentTypeID" ON "pears"."oledocument"
( "ownertype","ownerid","DocumentTypeID" )
GO
CREATE TRIGGER "oledocumentauditinsdel" BEFORE INSERT,DELETE ORDER 1 ON
"pears"."oledocument"
REFERENCING OLD AS "old_d" NEW AS "new_d"
FOR each ROW
WHEN(EXISTS(SELECT * FROM "AuditItems" WHERE "AreaName" = 'Document' AND "AuditFlag" = 1))
BEGIN
DECLARE "OwnerType" CHAR(20);
DECLARE "OwnerName" CHAR(100);
IF inserting AND EXISTS(SELECT * FROM "AuditItems" WHERE "AreaName" = 'Document' AND "AuditFlag" = 1 AND "ItemName" = 'Add') THEN
SELECT CASE "new_d"."ownertype" WHEN 'P' THEN 'Person' WHEN 'C' THEN 'Company' WHEN 'L' THEN 'Placement' WHEN 'V' THEN 'Vacancy' END INTO "OwnerType";
SELECT CASE "new_d"."ownertype"
WHEN 'P' THEN(SELECT "name" FROM "person" WHERE "personid" = "new_d"."ownerid")
WHEN 'C' THEN(SELECT "name" FROM "company" WHERE "companyid" = "new_d"."ownerid")
WHEN 'L' THEN(SELECT "refcode" FROM "placement" WHERE "placementid" = "new_d"."ownerid")
WHEN 'V' THEN(SELECT "string"("Name",' - ',"v"."position",'(',"v"."RefCode",')') FROM "Company" KEY JOIN "Employment" KEY JOIN "vacancy" AS "v" WHERE "vacancyID" = "new_d"."ownerid") END
INTO "OwnerName";
CALL "AuditLog"('DOCUMENT',"new_d"."ownerid","string"("OwnerType",' ',"OwnerName",' Document Inserted.'),'',"new_d"."description")
END IF;
IF deleting AND EXISTS(SELECT * FROM "AuditItems" WHERE "AreaName" = 'Document' AND "AuditFlag" = 1 AND "ItemName" = 'Remove') THEN
SELECT CASE "old_d"."ownertype" WHEN 'P' THEN 'Person' WHEN 'C' THEN 'Company' WHEN 'L' THEN 'Placement' WHEN 'V' THEN 'Vacancy' END INTO "OwnerType";
SELECT CASE "old_d"."ownertype"
WHEN 'P' THEN(SELECT "name" FROM "person" WHERE "personid" = "old_d"."ownerid")
WHEN 'C' THEN(SELECT "name" FROM "company" WHERE "companyid" = "old_d"."ownerid")
WHEN 'L' THEN(SELECT "refcode" FROM "placement" WHERE "placementid" = "old_d"."ownerid")
WHEN 'V' THEN(SELECT "string"("Name",' - ',"v"."position",'(',"v"."RefCode",')') FROM "Company" KEY JOIN "Employment" KEY JOIN "vacancy" AS "v" WHERE "vacancyID" = "old_d"."ownerid") END
INTO "OwnerName";
CALL "AuditLog"('DOCUMENT',"old_d"."ownerid","string"("OwnerType",' ',"OwnerName",' Document Deleted. '),"old_d"."description",'')
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."oledocument"."oledocumentauditinsdel" IS
{CREATE TRIGGER oledocumentauditinsdel
BEFORE INSERT,DELETE ORDER 1 ON
pears.oledocument
REFERENCING OLD AS old_d NEW AS new_d
FOR each ROW
WHEN(EXISTS(SELECT * FROM AuditItems WHERE AreaName = 'Document' AND AuditFlag = 1))
BEGIN
DECLARE OwnerType CHAR(20);
DECLARE OwnerName CHAR(100);
IF inserting AND EXISTS(SELECT * FROM AuditItems WHERE AreaName = 'Document' AND AuditFlag = 1 AND ItemName = 'Add') THEN
SELECT CASE new_d.ownertype WHEN 'P' THEN 'Person' WHEN 'C' THEN 'Company' WHEN 'L' THEN 'Placement' WHEN 'V' THEN 'Vacancy' END INTO OwnerType;
SELECT CASE new_d.ownertype
WHEN 'P' THEN (SELECT name FROM person WHERE personid = new_d.ownerid)
WHEN 'C' THEN (SELECT name FROM company WHERE companyid = new_d.ownerid)
WHEN 'L' THEN (SELECT refcode FROM placement WHERE placementid = new_d.ownerid)
WHEN 'V' THEN (SELECT string(Name,' - ',v.position,'(',v.RefCode,')') FROM Company KEY JOIN Employment KEY JOIN vacancy v WHERE vacancyID = new_d.ownerid)
END INTO OwnerName;
CALL AuditLog('DOCUMENT',new_d.ownerid,string(OwnerType,' ',OwnerName,' Document Inserted.'),'',new_d.description)
END IF;
IF deleting AND EXISTS(SELECT * FROM AuditItems WHERE AreaName = 'Document' AND AuditFlag = 1 AND ItemName = 'Remove') THEN
SELECT CASE old_d.ownertype WHEN 'P' THEN 'Person' WHEN 'C' THEN 'Company' WHEN 'L' THEN 'Placement' WHEN 'V' THEN 'Vacancy' END INTO OwnerType;
SELECT CASE old_d.ownertype
WHEN 'P' THEN (SELECT name FROM person WHERE personid = old_d.ownerid)
WHEN 'C' THEN (SELECT name FROM company WHERE companyid = old_d.ownerid)
WHEN 'L' THEN (SELECT refcode FROM placement WHERE placementid = old_d.ownerid)
WHEN 'V' THEN (SELECT string(Name,' - ',v.position,'(',v.RefCode,')') FROM Company KEY JOIN Employment KEY JOIN vacancy v WHERE vacancyID = old_d.ownerid)
END INTO OwnerName;
CALL AuditLog('DOCUMENT',old_d.ownerid,string(OwnerType,' ',OwnerName,' Document Deleted. '),old_d.description,'')
END IF
END
}
GO
CREATE TRIGGER "oledocumentautoarchive" after INSERT ORDER 2 ON
"pears"."oledocument"
REFERENCING NEW AS "new_d"
FOR each ROW
BEGIN
DECLARE "staffid" CHAR(20);
IF "varexists"('userstaffid') <> 0 THEN
SET "staffid" = "userstaffid"
ELSE SET "staffid" = NULL
END IF;
IF EXISTS(SELECT * FROM "documenttype" WHERE "isnull"("iscompliance",0) = 1 AND "documenttypeid" = "new_d"."documenttypeid") THEN
IF "new_d"."whenredacted" IS NOT NULL THEN
UPDATE "oledocument" SET "whenarchived" = CURRENT TIMESTAMP,"whoarchived" = "staffid" WHERE "whenredacted" IS NOT NULL AND "whenarchived" IS NULL AND "documenttypeid" = "new_d"."documenttypeid"
AND "oledocumentid" <> "new_d"."oledocumentid" AND "ownerid" = "new_d"."ownerid" AND "ownertype" = "new_d"."ownertype"
END IF;
IF "new_d"."whenredacted" IS NULL THEN
UPDATE "oledocument" SET "whenarchived" = CURRENT TIMESTAMP,"whoarchived" = "staffid" WHERE "whenredacted" IS NULL AND "whenarchived" IS NULL AND "documenttypeid" = "new_d"."documenttypeid"
AND "oledocumentid" <> "new_d"."oledocumentid" AND "ownerid" = "new_d"."ownerid" AND "ownertype" = "new_d"."ownertype"
END IF
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."oledocument"."oledocumentautoarchive" IS
{CREATE TRIGGER oledocumentautoarchive
after INSERT ORDER 2 ON
pears.oledocument
REFERENCING NEW AS new_d
FOR each ROW
BEGIN
DECLARE staffid CHAR(20);
IF varexists('userstaffid') <> 0 THEN
SET staffid=userstaffid
ELSE SET staffid=NULL
END IF;
IF EXISTS(SELECT * FROM documenttype WHERE isnull(iscompliance,0) = 1 AND documenttypeid = new_d.documenttypeid) THEN
IF new_d.whenredacted IS NOT NULL THEN
UPDATE oledocument SET whenarchived = CURRENT TIMESTAMP,whoarchived = staffid WHERE whenredacted IS NOT NULL AND whenarchived IS NULL AND documenttypeid = new_d.documenttypeid
AND oledocumentid <> new_d.oledocumentid AND ownerid = new_d.ownerid AND ownertype=new_d.ownertype
END IF;
IF new_d.whenredacted IS NULL THEN
UPDATE oledocument SET whenarchived = CURRENT TIMESTAMP,whoarchived = staffid WHERE whenredacted IS NULL AND whenarchived IS NULL AND documenttypeid = new_d.documenttypeid
AND oledocumentid <> new_d.oledocumentid AND ownerid = new_d.ownerid AND ownertype=new_d.ownertype
END IF
END IF
END
}
GO
CREATE TRIGGER "oledocument_updateaudit" after UPDATE OF "deletiondate"
ORDER 3 ON "pears"."oledocument"
REFERENCING OLD AS "old_d" NEW AS "new_d"
FOR each ROW
WHEN(EXISTS(SELECT * FROM "AuditItems" WHERE "AreaName" = 'Document' AND "AuditFlag" = 1))
BEGIN
DECLARE "OwnerType" CHAR(20);
DECLARE "OwnerName" CHAR(100);
IF EXISTS(SELECT * FROM "AuditItems" WHERE "AreaName" = 'Document' AND "AuditFlag" = 1 AND "ItemName" = 'Deletion Date') THEN
SELECT CASE "old_d"."ownertype" WHEN 'P' THEN 'Person' WHEN 'C' THEN 'Company' WHEN 'L' THEN 'Placement' WHEN 'V' THEN 'Vacancy' END INTO "OwnerType";
SELECT CASE "old_d"."ownertype"
WHEN 'P' THEN(SELECT "name" FROM "person" WHERE "personid" = "old_d"."ownerid")
WHEN 'C' THEN(SELECT "name" FROM "company" WHERE "companyid" = "old_d"."ownerid")
WHEN 'L' THEN(SELECT "refcode" FROM "placement" WHERE "placementid" = "old_d"."ownerid")
WHEN 'V' THEN(SELECT "string"("Name",' - ',"v"."position",'(',"v"."RefCode",')') FROM "Company" KEY JOIN "Employment" KEY JOIN "vacancy" AS "v" WHERE "vacancyID" = "old_d"."ownerid") END
INTO "OwnerName";
CALL "AuditLog"('DOCUMENT',"old_d"."ownerid","string"("OwnerType",' ',"OwnerName",' - ',"old_d"."description",' Deletion Date Changed. '),"old_d"."deletiondate","new_d"."deletiondate")
END IF
END
GO
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."oledocument"."oledocument_updateaudit" IS
{CREATE TRIGGER oledocument_updateaudit
after UPDATE OF deletiondate
ORDER 3 ON pears.oledocument
REFERENCING OLD AS old_d NEW AS new_d
FOR each ROW
WHEN(EXISTS(SELECT * FROM AuditItems WHERE AreaName = 'Document' AND AuditFlag = 1))
BEGIN
DECLARE OwnerType CHAR(20);
DECLARE OwnerName CHAR(100);
IF EXISTS(SELECT * FROM AuditItems WHERE AreaName = 'Document' AND AuditFlag = 1 AND ItemName = 'Deletion Date') THEN
SELECT CASE old_d.ownertype WHEN 'P' THEN 'Person' WHEN 'C' THEN 'Company' WHEN 'L' THEN 'Placement' WHEN 'V' THEN 'Vacancy' END INTO OwnerType;
SELECT CASE old_d.ownertype
WHEN 'P' THEN (SELECT name FROM person WHERE personid = old_d.ownerid)
WHEN 'C' THEN (SELECT name FROM company WHERE companyid = old_d.ownerid)
WHEN 'L' THEN (SELECT refcode FROM placement WHERE placementid = old_d.ownerid)
WHEN 'V' THEN (SELECT string(Name,' - ',v.position,'(',v.RefCode,')') FROM Company KEY JOIN Employment KEY JOIN vacancy v WHERE vacancyID = old_d.ownerid)
END INTO OwnerName;
CALL AuditLog('DOCUMENT',old_d.ownerid,string(OwnerType,' ',OwnerName,' - ',old_d.description, ' Deletion Date Changed. '),old_d.deletiondate,new_d.deletiondate)
END IF
END
}
GO
127.0.0.1