Attachment Archive and Expire

Overview

Several QBO modules leverage the Attachment module, including qbo.Import, qbo.Contact, qbo.Debt and qbo.Score. Over the course of time, it the number of attachment accumulated by these modules can cause the Attachment module's performance to decrease, and incur unwanted storage expenses. To manage such issues, we can leverage archiving and expiration.

Attachment Templates offer fields that determine when to automatically archive (move to cheap storage) or expire (delete), based on the age of the attachment. Modules that use attachments "under the hood" can be configured to automatically use configured attachment templates to drive archiving and expiration. To configure:

• 1. From Design > Documents, create a template configured with the archive and expiration fields set to whatever values you deem appropriate

     • in the examples below, the template created is called "SystemAttachmentTemplate"

  2. From Design > Configuration > Queuing, create a maintenance queue to ensure archiving and expiration jobs don't interfere with other processing

  3. Consider updating existing Attachment rows to populate the AttachmentTemplateID, ExpirationDate and ArchiveDate fields

  4. From Design > Configuration > {Module} > Settings, set the Default*AttachmentTemplate values to the template configured in step 1

     • Modules that leverage such settings include: ImportFile, ServiceRequest, ProfessionalLicense, CreditReport, Bucket, BucketItem, Score

Settings related to attachment templates:

• • qbo.Import

    • DefaultPreTransformAttachmentTemplate

    • DefaultPostTransformAttachmentTemplate

    • DefaultServiceRequestAttachmentTemplate

    • DefaultImportResultAttachmentTemplate

    • DefaultBucketAttachmentTemplate

    • DefaultBucketItemAttachmentTemplate

  • qbo.Contact

    • DefaultProfessionalLicenseAttachmentTemplate

  • qbo.Credit

    • DefaultCreditReportAttachmentTemplate

  • qbo.Score

    • DefaultScoreAttachmentTemplate

Empty Folder Cleanup

A few new settings have been introduced to the UNC File Object:

• • DeleteParentDirectory - bool (defaults to false). If set to true and the folder this attachment is in is empty, the folder will be deleted (Assuming it's not the file object assembly's root folder and not a protected folder name)

  • DeleteParentDirectoryDepth - int (defaults to 1). This will move up the directory tree deleting all empty folders until it finds a non-empty, non-protected, non-root folder.

  • DeleteParentProtectedFolderNames - string (defaults to empty). Forces protection of any folders with these names from ever being deleted. These are | delimited. E.G. "IN|OUT"

If you use the suggested FileNameFormat (/{Year}/{Month}/{Day}/{Object}/{ObjectID}/{Guid}.xml), it is recommended to have a DeleteParentDirectoryDepth of 6.

Sample File Object:

<ConfigurationEntryItem>

    <ConfigurationEntry>Standard BLOB Storage</ConfigurationEntry>

    <Source>FileObject.config</Source>

    <ConfigurationType>qbo.Attachment.Configuration.FileObjectCollection</ConfigurationType>

    <ConfigurationKey>Standard BLOB Storage</ConfigurationKey>

    <SubscriberID>qbs.quandis.net-FileObject-Standard BLOB Storage</SubscriberID>

    <ConfigurationXml>

      <FileObject Name="Standard BLOB Storage" 

                  Type="qbo.Attachment.FileObjects.UNCFile, qbo.Attachment"      

                  Uri="//HDCShared/UATFileStore/devqbs.quandis.net/" 

                  Settings="DeleteParentDirectory=true&amp;DeleteParentDirectoryDepth=6&amp;DeleteParentProtectedFolderNames=IN|OUT"

                  Compression="false"/>

    </ConfigurationXml>

 </ConfigurationEntryItem>

Data Listeners

The following sample XML will create jobs (data listeners) to archive and expire attachments based on the template settings. These data listeners are part of source-controlled Attachment.config, so this is for illustration purposes or overrides only.

Archiving

<ConfigurationEntryItem>

  <ConfigurationEntry>Attachment/ArchiveAttachments</ConfigurationEntry>

  <Source>Attachment.config</Source>

  <ConfigurationType>qbo.Application.Configuration.DataListenerCollection</ConfigurationType>

  <ConfigurationKey>ArchiveAttachments</ConfigurationKey>

  <ConfigurationXml>

    <Listener Name="ArchiveAttachments" Statement="ArchiveFileSearch" Class="Attachment" Operation="Archive" Parameters="AttachmentID={AttachmentID}&amp;UpdatedDate={UpdatedDate}" Async="False" StopOnError="False" Status="" />

  </ConfigurationXml>

</ConfigurationEntryItem>

Expiration

<ConfigurationEntryItem>

  <ConfigurationEntry>Attachment/ExpireAttachments</ConfigurationEntry>

  <Source>Attachment.config</Source>

  <ConfigurationType>qbo.Application.Configuration.DataListenerCollection</ConfigurationType>

  <ConfigurationKey>ExpireAttachments</ConfigurationKey>

  <ConfigurationXml>

    <Listener Name="ExpireAttachments" Statement="ExpireFileSearch" Class="Attachment" Operation="Expire" Parameters="AttachmentID={AttachmentID}&amp;UpdatedDate={UpdatedDate}" Async="False" StopOnError="False" Status="" />

  </ConfigurationXml>

</ConfigurationEntryItem>

Queue

The following sample XML will create a system maintenance queue to run the jobs above against.

<ConfigurationEntryItem>

  <ConfigurationEntry>SystemMaintenance</ConfigurationEntry>

  <Source>Queuing.config</Source>

  <ConfigurationType>qbo.Application.Configuration.QueueCollection</ConfigurationType>

  <ConfigurationKey>SystemMaintenanceQueue</ConfigurationKey>

  <ConfigurationXml>

    <Queue Name="SystemMaintenance"

           Type="qbo.Queue.MSSQL.ServiceBroker, qbo.Queue.MSSQL, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"

           TypeLabel="qbo.Queue.MSSQL.ServiceBroker"

           Uri="SystemMaintenance"

           Threads="1"

           RetryCount="1"

           RetryIntervalType="None"

           ThrottleIntervalType="None"

           Schedule="Queue - Business Hours"

           Status="Started"

           SuccessLog="QueueLog"

           FailureLog="QueueLog"

           Description="System Maintenance Queue"

           ImpersonateType="QueuedUser" />

  </ConfigurationXml>

</ConfigurationEntryItem>

System Attachment Template

The following sample XML will create a template that can be used by the application settings in other modules. Note that you should review and alter the ArchiveFileObjectAssembly value to a valid entry for your system.

<AttachmentTemplateItem>

  <AttachmentTemplate>SystemAttachmentTemplate</AttachmentTemplate>

  <Repeatable>Many</Repeatable>

  <ExpirationScale>0</ExpirationScale>

  <Expiration>14</Expiration>

  <FileObjectAssembly>Standard BLOB Storage</FileObjectAssembly>

  <FileNameFormat>/{Year}/{Month}/{Day}/{Object}/{ObjectID}/{Guid}.xml</FileNameFormat>

  <ArchiveScale>2</ArchiveScale>

  <Archive>1</Archive>

  <ArchiveObjectAssembly>AmazonS3</ArchiveObjectAssembly>

  <ExtranetEnabled>None</ExtranetEnabled>

  <CustomStatusList>False</CustomStatusList>

  <CustomTypeList>False</CustomTypeList>

</AttachmentTemplateItem>

DB Statements

These statemnents are part of source-controlled Attachment.config, so this is for illustration purposes or overrides only.

<ConfigurationEntryItem>

  <ConfigurationEntry>Attachment/ArchiveFileSearch</ConfigurationEntry>

  <Source>Attachment.config</Source>

  <ConfigurationType>qbo.Application.Configuration.DbStatementCollection</ConfigurationType>

  <ConfigurationKey>ArchiveFileSearch</ConfigurationKey>

  <ConfigurationXml>

    <Statement Name="ArchiveFileSearch" ReturnType="DataReader" Permission="{Table}ArchiveFileSearch" Timeout="1200" AllowInheritance="true" Query="

                 SELECT TOP {Param.Batch} AttachmentID, Attachment.ArchiveScheduled UpdatedDate FROM Attachment

                  INNER JOIN AttachmentTemplate

                   ON Attachment.AttachmentTemplateID = AttachmentTemplate.AttachmentTemplateID

                  WHERE ArchiveScheduled &lt; GETDATE()

                   AND Attachment.FileObjectAssembly &lt;&gt; AttachmentTemplate.ArchiveObjectAssembly

                   AND ArchivalDate IS NULL

                   AND Attachment.ArchiveScheduled &gt;= '{Param.FromDate}'

                   AND ISNULL(Attachment.Status, '') &lt;&gt; 'Error'

                  ORDER BY Attachment.ArchiveScheduled

                 ">

      <Parameters>

        <Parameter Name="FromDate" DbType="DateTime" />

        <Parameter Name="Batch" DbType="Int32" Value="500" />

      </Parameters>

    </Statement>

  </ConfigurationXml>

</ConfigurationEntryItem>

<ConfigurationEntryItem>

  <ConfigurationEntry>Attachment/ExpireFileSearch</ConfigurationEntry>

  <Source>Attachment.config</Source>

  <ConfigurationType>qbo.Application.Configuration.DbStatementCollection</ConfigurationType>

  <ConfigurationKey>ExpireFileSearch</ConfigurationKey>

  <ConfigurationXml>

    <Statement Name="ExpireFileSearch" ReturnType="DataReader" Permission="{Table}ExpireFileSearch" Timeout="1200" AllowInheritance="true" Query="

                 SELECT TOP {Param.Batch} AttachmentID, Attachment.ExpirationScheduled UpdatedDate FROM Attachment

                  WHERE ExpirationScheduled &lt; GETDATE()

                   AND ExpirationDate IS NULL

                   AND Attachment.ExpirationScheduled &gt;= '{Param.FromDate}'

                   AND ISNULL(Attachment.Status, '') &lt;&gt; 'Error'

                  ORDER BY Attachment.ExpirationScheduled

                 ">

      <Parameters>

        <Parameter Name="FromDate" DbType="DateTime" />

        <Parameter Name="Batch" DbType="Int32" Value="500" />

      </Parameters>

    </Statement>

  </ConfigurationXml>

</ConfigurationEntryItem>

Schedule Your Jobs

Note the {ScheduleID} is the value of ScheduleName in the parameters (not the actual schedule name).

http://localhost/Attachment/Attachment.ashx/Schedule/Listen?Listeners=ArchiveAttachments&ScheduleName={ScheduleID}&QueueName=SystemMaintenance

http://localhost/Attachment/Attachment.ashx/Schedule/Listen?Listeners=ExpireAttachments&ScheduleName={ScheduleID}&QueueName=SystemMaintenance