pears.BlobstoreProcessDeleteQueue

Process external blob files awaiting deletion. Called by a database event.

CREATE PROCEDURE "pears"."BlobstoreProcessDeleteQueue"() no RESULT SET
/* Application Maintained Function / Procedure - DO NOT EDIT*/
BEGIN
  -- Get list of files not yet deleted
  FOR "FileLoop" AS "FileCursor" no scroll cursor FOR
    SELECT "BlobStoreDeleteQueueID" AS "deleteID","ExternalFilePath" AS "ExtFilePath" FROM "BlobStoreDeleteQueue" WHERE "WhenDeleted" IS NULL ORDER BY "BlobStoreDeleteQueueID" ASC FOR READ ONLY
  do
    -- Delete waiting files if not referenced by other BlobStore Records
    IF NOT EXISTS(SELECT * FROM "BlobStore" WHERE "BlobStore"."ExternalFilePath" = "ExtFilePath") THEN
      CALL "BlobstoreDeleteExternal"("ExtFilePath")
    END IF;
    UPDATE "BlobStoreDeleteQueue" SET "WhenDeleted" = CURRENT TIMESTAMP WHERE "BlobStoreDeleteQueue"."BlobStoreDeleteQueueID" = "deleteID"
  END FOR;
  -- Clean out processed queue items more than 14 days old
  DELETE FROM "BlobStoreDeleteQueue" WHERE "WhenDeleted" < "dateadd"("day",-14,CURRENT TIMESTAMP)
END
GO
 
COMMENT ON PROCEDURE "pears"."BlobstoreProcessDeleteQueue" IS 
	'Process external blob files awaiting deletion. Called by a database event.'
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."BlobstoreProcessDeleteQueue" IS 
{CREATE PROCEDURE BlobstoreProcessDeleteQueue 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
()
NO RESULT SET
BEGIN
-- Get list of files not yet deleted
FOR FileLoop AS FileCursor NO SCROLL CURSOR 
 FOR SELECT BlobStoreDeleteQueueID AS deleteID, ExternalFilePath AS ExtFilePath FROM BlobStoreDeleteQueue WHERE WhenDeleted IS NULL ORDER BY BlobStoreDeleteQueueID ASC
 FOR READ ONLY DO
 -- Delete waiting files if not referenced by other BlobStore Records
 IF NOT EXISTS (SELECT * FROM BlobStore WHERE BlobStore.ExternalFilePath = ExtFilePath)THEN
   CALL BlobstoreDeleteExternal(ExtFilePath);
 END IF;
 UPDATE BlobStoreDeleteQueue SET WhenDeleted = CURRENT TIMESTAMP WHERE BlobStoreDeleteQueue.BlobStoreDeleteQueueID = deleteID;
END FOR;
-- Clean out processed queue items more than 14 days old
DELETE FROM BlobStoreDeleteQueue WHERE WhenDeleted < dateadd(DAY,-14,CURRENT TIMESTAMP);
END
}