====== pears.blobstore ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Description =====
OLE objects and other blob data eg pictures. If external Blob Store is in use the records are links to the relevant files.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **class** | char(1) | NOT NULL |  |  |
| **id** | char(20) | NOT NULL |  | class of :- J or V: PersonID, O: OleDocumentID, M: MailMergeID, T: TemplateStore, L: ContactEventID |
| blob | long binary | NULL |  |  |
| createdby | char(15) | NULL |  |  |
| createdat | timestamp | NULL |  |  |
| changedby | char(15) | NULL |  |  |
| changedat | timestamp | NULL |  |  |
| accessedby | char(15) | NULL |  |  |
| accessedat | timestamp | NULL |  |  |
| zipped | smallint | NULL | 0 |  |
| PublishToWeb | smallint | NULL | 0 |  |
| AddToInvPDF | smallint | NULL | 0 |  |
| CVSendOutApproved | smallint | NULL | 0 |  |
| ExternalFilePath | char(250) | NULL |  | If null blob is stored internally in the blob column |
| Optimised | timestamp | NULL |  | Date blob was last optimised. Cleared by trigger if CreateDat or ChangeDat are updated. |
| filesize | integer | NULL |  | Kb. Internal and external, disc not unzipped size |

===== Primary Key =====
  * class, id

===== Foreign Keys =====
  * No outgoing foreign keys found.

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| blobcreatedby | Index | createdby |  |
| blobchangedby | Index | changedby |  |
| blobaccessedby | Index | accessedby |  |
| blobaccessedat | Index | accessedat |  |
| BlobExternalFilePath | Index | ExternalFilePath |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| blobstore_delete | before | delete order 1 |
| BlobStore_ClearOptimisedDateTime | before | update of "createdat", "changedat" order 1 |
| blobstore_auditphoto | after | insert,delete,update order 10 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."blobstore"
-- Table comment: OLE objects and other blob data eg pictures. If external Blob Store is in use the records are links to the relevant files.
-- Statement count: 18

CREATE TABLE "pears"."blobstore" (
    "class"                          char(1) NOT NULL
   ,"id"                             char(20) NOT NULL
   ,"blob"                           long binary NULL
   ,"createdby"                      char(15) NULL
   ,"createdat"                      timestamp NULL
   ,"changedby"                      char(15) NULL
   ,"changedat"                      timestamp NULL
   ,"accessedby"                     char(15) NULL
   ,"accessedat"                     timestamp NULL
   ,"zipped"                         smallint NULL DEFAULT 0
   ,"PublishToWeb"                   smallint NULL DEFAULT 0
   ,"AddToInvPDF"                    smallint NULL DEFAULT 0
   ,"CVSendOutApproved"              smallint NULL DEFAULT 0
   ,"ExternalFilePath"               char(250) NULL
   ,"Optimised"                      timestamp NULL
   ,"filesize"                       integer NULL
   ,PRIMARY KEY ("class" ASC,"id" ASC) 
)
go


COMMENT ON COLUMN "pears"."blobstore"."id" IS 
	'class of :- J or V: PersonID, O: OleDocumentID, M: MailMergeID, T: TemplateStore, L: ContactEventID'
go


COMMENT ON COLUMN "pears"."blobstore"."ExternalFilePath" IS 
	'If null blob is stored internally in the blob column'
go


COMMENT ON COLUMN "pears"."blobstore"."Optimised" IS 
	'Date blob was last optimised. Cleared by trigger if CreateDat or ChangeDat are updated.'
go


COMMENT ON COLUMN "pears"."blobstore"."filesize" IS 
	'Kb. Internal and external, disc not unzipped size'
go


COMMENT ON TABLE "pears"."blobstore" IS 
	'OLE objects and other blob data eg pictures. If external Blob Store is in use the records are links to the relevant files.'
go


CREATE INDEX "blobcreatedby" ON "pears"."blobstore"
    ( "createdby" )
go


CREATE INDEX "blobchangedby" ON "pears"."blobstore"
    ( "changedby" )
go


CREATE INDEX "blobaccessedby" ON "pears"."blobstore"
    ( "accessedby" )
go


CREATE INDEX "blobaccessedat" ON "pears"."blobstore"
    ( "accessedat" )
go


CREATE INDEX "BlobExternalFilePath" ON "pears"."blobstore"
    ( "ExternalFilePath" )
go


create trigger "blobstore_delete" before delete order 1 on
"pears"."blobstore"
referencing old as "old_blob"
for each row
when("old_blob"."externalfilepath" is not null)
begin
  declare "CurrentUser" char(25);
  if "VAREXISTS"('UserStaffID') = 1 then
    set "CurrentUser" = "UserStaffID"
  end if;
  insert into "BlobStoreDeleteQueue"( "Class","ID","WhoDeleted","ExternalFilePath" ) values( "old_blob"."Class","old_blob"."ID","CurrentUser","old_blob"."ExternalFilePath" ) 
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."blobstore"."blobstore_delete" IS 
{create trigger blobstore_delete 
 before delete order 1 on
"pears"."blobstore"
referencing old as "old_blob"
for each row
when("old_blob"."externalfilepath" is not null)
begin
  declare CurrentUser char(25);
  IF VAREXISTS('UserStaffID') = 1 THEN
    SET CurrentUser = UserStaffID;
  END IF;
  insert into BlobStoreDeleteQueue(Class, ID, WhoDeleted, ExternalFilePath) values(old_blob.Class, old_blob.ID, CurrentUser, old_blob.ExternalFilePath)
end
}
go


create trigger "BlobStore_ClearOptimisedDateTime" before update of "createdat",
"changedat" order 1 on "pears"."blobstore"
referencing new as "new_Blob"
for each row
begin
  set "new_Blob"."Optimised" = null
end
go


COMMENT ON TRIGGER "pears"."blobstore"."BlobStore_ClearOptimisedDateTime" IS 
	'Clears the Optimised datetime column if either the CreateDat or ChangeDat fields are updated - indicating that the Blob needs to be examined again.'
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."blobstore"."BlobStore_ClearOptimisedDateTime" IS 
{create trigger BlobStore_ClearOptimisedDateTime 
 before update of createdat,
changedat order 1 on pears.blobstore
referencing new as new_Blob
for each row
begin
  set new_Blob.Optimised = null
end
}
go


create trigger "blobstore_auditphoto" after insert,delete,update order 10 on
"pears"."blobstore"
referencing old as "oldrec" new as "newrec"
for each row when("oldrec"."class" = 'J' or "newrec"."class" = 'J')
begin
  if inserting then
    call "AuditLog"('PERSON',"newrec"."ID",'Picture Inserted',null,'New Picture')
  elseif deleting then
    call "AuditLog"('PERSON',"oldrec"."ID",'Picture Deleted','Old Picture',null)
  elseif updating then
    if "isnull"("oldrec"."changedat",current timestamp) <> "newrec"."changedat" then
      call "AuditLog"('PERSON',"oldrec"."ID",'Picture Changed','Old Picture','New Picture')
    end if
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."blobstore"."blobstore_auditphoto" IS 
{create trigger blobstore_auditphoto 
 after insert,delete,update order 10 on
"pears"."blobstore"
referencing old as "oldrec" new as "newrec"
for each row when("oldrec"."class" = 'J' or "newrec"."class" = 'J')
begin
  if inserting then
    call "AuditLog"('PERSON',"newrec"."ID",'Picture Inserted',null,'New Picture')
  elseif deleting then
    call "AuditLog"('PERSON',"oldrec"."ID",'Picture Deleted','Old Picture',null)
  elseif updating then
    if "isnull"("oldrec"."changedat",current timestamp) <> "newrec"."changedat" then
      call "AuditLog"('PERSON',"oldrec"."ID",'Picture Changed','Old Picture','New Picture')
    end if end if
end
}
go
</code>