Snapshot Data Storage Structure
CopyStorm stores all snapshot data in the following database tables.
| Table Name | Description |
|---|---|
| CopyForceArchiveToken | Mapping from string names to integer tokens. The table is used to minimize the size of other tables. |
| CopyForceArchiveFilter | List of filter types that are used to determine if a record belongs in a snapshot. |
| CopyForceArchivePolicy | This the root table for each individual snapshot. |
| CopyForceArchivePolicyStat | Statistics about the data stored in each snapshot. |
| CopyForceArchiveRetentionRule | Rules which describe data retention rules for groups of tables within a snapshot. This table allows a single snapshot to support different retention policies for various tables. |
| CopyForceArchiveFieldSet | Description of the fields defined in Salesforce at the point a record was snapshot. |
| CopyForceArchiveRecord | Snapshot records. This is the primary storage table for each snapshot. |
| CopyForceArchiveRecordRelat | Relationships between record based on Salesforce references fields. The purpose of this table is to facilitate fast record relationship traversal without scanning the complete text of a snapshot record in the CopyForceArchiveRecord table. |
The following diagram illustrates the relationships between the various tables.
Note: All physical database tables are prefixed with “CopyForce”.
CopyForceArchiveToken
| Column | Data Type | Description |
|---|---|---|
| Id | Integer | Unique Id assigned to the Name. |
| Name | String | Name of a table, field, etc. in Salesforce. |
CopyForceArchiveFilter
A CopyForceArchiveFilter describes the implementation of Java class which can determine if records should be snapshot. These classes are built into the CopyStorm Snapshot subsystem and include:
- Deleted Records — determine if a record has been deleted in Salesforce.
- Column Expression — evaluation of a user supplied expression to determine if a record matches.
- All Changes — indicate if a record matches if it has any changes.
- SQL Predicate — use a bit of user supplied SQL to determine if a record matches.
| Column | Data Type | Description |
|---|---|---|
| Id | Integer | Unique internal id for the filter. |
| Name | String | Name of the filter as presented to a user. |
| Description | String | Description of the filter. |
| ClassName | String | Name of a Java class that implements the filter. These classes are typically developed by Capstorm rather than customers. |
| CreatedDate | Timestamp | Timestamp of when the record was created. |
| ModifiedDate | Timestamp | Timestamp of the most recent modification to the record. |
CopyForceArchivePolicy
A CopyForceArchivePolicy record holds the definition for a CopyStorm Snapshot — if a record matches the rules for a CopyStormArchivePolicy then it will be written to the the policy’s associated database storage.
| Column | Data Type | Description |
|---|---|---|
| Id | Integer | Unique internal id for the filter. |
| Name | String | Name of the filter as presented to a user. |
| Description | String | Description of the policy. |
| FilterId | Integer | Id of the CopyForceArchiveFilter that will be used to determine if a record matches the policy. |
| Active | Boolean | True if the policy should be automatically evaluated during a CopyStorm backup. |
| IncludeFormulaFields | Boolean | True if formula field values should be stored in an archived record. |
| IncludeNullFields | Boolean | True if field with an empty (null) value should be explicitly stored in an archive record. |
| IncludedTables | Boolean | Comma separated list of table names and/or regular expressions for which this policy is applicable. |
| ExcludedTables | String | Comma separated list of tables and/or regular expressions that should be ignored by the policy. If the IncludedTables value is not blank then the ExcludedTables value is ignored. |
| ExcludedReferenceFields | String | Comma separated list of Salesforce reference field names that should be excluded from the CopyForceArchiveRecordRelat table. The primary reason to exclude a reference field is to save space. |
| Argument | String | Argument that will be passed to the corresponding ArchiveFilterId logic. For example, the Column Expression filter has a argument the represents the expression. |
| ArchiveAfterInsert | Boolean | If True and the policy is active then evaluate a record for potential snapshot by the policy immediately after it is inserted into CopyStorm. |
| ArchiveBeforeUpdate | Boolean | If True and the policy is active then evaluate a record for potential snapshot by the policy just before it is updated in CopyStorm. |
| ArchiveAfterUpdate | Boolean | If True and the policy is active then evaluate a record for potential snapshot by the policy just after it is updated in CopyStorm. |
| RetentionDays | Integer | Default number of days a record will be retained in the snapshot before it is deleted. If blank then records will be kept forever. |
| RetentionVersions | Integer | Maximum number of versions to keep for a record even when the default retention days has not been met. |
| CreatedDate | Timestamp | Timestamp of when the record was created. |
| ModifiedDate | Timestamp | Timestamp of the most recent modification to the record. |
CopyForceArchivePolicyStat
The CopyForceArchivePolicyStat contains table level statistics about a CopyStorm Snapshot. Statistics about the tables contained in a CopyStorm Snapshot are maintained automatically while the record count statistics require a scheduled job.
| Column | Data Type | Description |
|---|---|---|
| PolicyId | Integer | Snapshot policy that owns the statistic |
| TableNameToken | Integer | Table for which the statistic applies. |
| NumRecords | Integer | Number of table records in the snapshot (updated on demand). |
| FirstArchiveDate | Timestamp | Timestamp of the earliest record in the snapshot (updated on demand). |
| LastArchiveDate | Timestamp | Timestamp of the oldest record in the snapshot (updated on demand). |
| LastRetentionRunDate | Timestamp | Most recent date on which the policy’s record retention date rules were run. |
| NumRetentionDeleted | Integer | Number of records deleted from the snapshot during the most recent record retention policy run. |
CopyForceArchiveRetentionRule
The CopyForceArchiveRetentionRule table contains rules that override the default data retention rules for an archive for specific tables. For example:
- The default retention rule for an archive is set to 30 days.
- For the Account,Contact, and Case table use a override retention rule to keep them in the archive for 180 days.
The algorithm which determines the policy for a specific table always selects the matching rule with the longest number of days.
| Column | Data Type | Description |
|---|---|---|
| Id | Integer | Unique id for the rule. |
| PolicyId | Integer | The archive policy that owns the rule. |
| RetentionDays | Integer | Number to retain records before they are deleted from the archive. |
| IncludedTables | String | Comma separated list of table names and/or regular expressions for which the retention rule applies. |
CopyForceArchiveFieldSet
The CopyForceArchiveFieldSet table contains a unique configuration of the fields for a Saleforce table. When a record is snapshot:
- If the current table field definitions have never been snapshot then create a record in the CopyForceArchiveFieldSet table.
- Associate a CopyForceArchiveFieldSet with each snapshot record. This supports recovering both the schema and data for a snapshot record.
| Column | Data Type | Description |
|---|---|---|
| Id | Integer | Unique id for the field set. |
| TableName | String | API Name of the Salesforce table used to define the field set. |
| TableLabel | String | Label of the Salesforce table used to define the field set. |
| Fingerprint | String | Unique fingerprint for the field set. Used for rapid searching and duplicate detection. |
| Fields | XML | Simple XML document describing the fields in a table. |
| CreatedDate | Timestamp | Date on which the field set was created. |
| ModifiedDate | Timestamp | Date on which the field set was last updated. |
The fields in this type of record are represented as XML in the form below. Naturally, a complete record will contains many more fields and this is only a subset.
<ArchiveFields table="Account" version="24MAY18"> <Field name="AccountNumber" label="Account Number" type="string" length="40" ndigits="0" scale="0" nameField="false" calculated="false" encrypted="false" custom="false"/> <Field name="AccountSource" label="Account Source" type="picklist" length="40" ndigits="0" scale="0" nameField="false" calculated="false" encrypted="false" custom="false"/> <Field name="Active__c" label="Active" type="boolean" length="0" ndigits="0" scale="0" nameField="false" calculated="false" encrypted="false" custom="true"/> <Field name="CLS_Account_Reconcile__c" label="Account Reconcile" type="string" length="1300" ndigits="0" scale="0" nameField="false" calculated="true" formula="IF( RecordType.DeveloperName = 'Procurement_Supplier', 'ESP', 
IF( RecordType.DeveloperName = 'CLS_Customer_Account', 'CLS', 
IF( RecordType.DeveloperName = 'Global_Account', 'GLOBAL', 
IF( RecordType.DeveloperName = 'Supplier', 'SRM', 
'' 
))))" encrypted="false" custom="true"/> </ArchiveFields>
CopyForceArchiveRecord
The CopyForceArchiveRecord table contains all snapshot records. Since it can grow quite large sites may consideration partitioning the table based on the policyId column.
| Column | Data Type | Description |
|---|---|---|
| Id | Integer | Unique id for the snapshot record. |
| SalesforceId | String | Salesforce Id of the snapshot record. |
| Name | String | Value of the Salesforce Name field for the record. If a record does not contain a Name field then the table name is used. |
| TableNameToken | Integer | Internal Id used to lookup the name of the Salesforce table. |
| PolicyId | Integer | Internal Id of the snapshot policy which created the record. |
| FieldSetId | Integer | Internal Id of the field set definitions in place when the record was snapshot. |
| Fingerprint | String | Unique string used to rapidly avoid redundant snapshot records. |
| FieldData | XML | Simple XML document describing data in the record. |
| SystemModStamp | Timestamp | The Salesforce SystemModStamp when the record was snapshot. In the rare case where a record does not have a SystemModStamp column a different column with the same semantics is selected. |
| CreatedDate | Timestamp | The Salesforce CreatedDate when the record was snapshot. In the rare case where a record does not have a CreatedDate column a different column with the same semantics is selected. |
| ModifiedDate | Timestamp | The Salesforce ModifiedDate when the record was snapshot. In the rare case where a record does not have a ModifiedDate column a different column with the same semantics is selected. |
| ArchivedDate | Timestamp | The timestamp when the record was written to the snapshot (UTC). |
CopyForceArchiveRecord field data can be read directly using the XML extensions for your database though most people will use SQL generated from the CopyStorm Snapshot Explorer tool rather than handcraft XML. However, if you are a master of XML and database then this section illustrates the format of a snapshot record. Note:
- In the example code formula column values were excluded.
- In the example fields with empty values were excluded.
<FieldData> <Active__c>false</Active__c> <BillingCity>Orlando</BillingCity> <BillingCountry>Florida</BillingCountry> <BillingPostalCode>L4B 1Y3</BillingPostalCode> <BillingState>Orlando</BillingState> <BillingStreet>Walt Disney Street</BillingStreet> <CreatedById>0051a000000EVk1AAG</CreatedById> <CreatedDate>2015-04-23T15:06:27.000Z</CreatedDate> <Description>Walt Disney World is a leading entertainment destination.</Description> <Id>0011a000003B7M2AAK</Id> <Industry>Media</Industry> <IsCustomerPortal>false</IsCustomerPortal> <IsDeleted>false</IsDeleted> <LastActivityDate>2015-04-22</LastActivityDate> <LastModifiedById>0051a000000EVk1AAG</LastModifiedById> <LastModifiedDate>2015-04-23T15:06:27.000Z</LastModifiedDate> <Name>Walt Disney</Name> <NumberOfEmployees>14668</NumberOfEmployees> <OwnerId>0051a000000EVk1AAG</OwnerId> <Phone>(905) 232-8888</Phone> <SystemModstamp>2016-07-29T10:57:56.000Z</SystemModstamp> <Type>Prospect</Type> </FieldData>
CopyForceArchiveRecordRelat
The purpose of the CopyForceArchiveRecordRelat table is to support rapid traversal of relationships between snapshot records by avoiding scanning the XML to find relationships.
| Column | Data Type | Description |
|---|---|---|
| ArchiveRecordId | Integer | Id of the CopyForceArchiveRecord entry that contains the relationship. |
| ParentTableToken | String | Salesforce tokenized name of the table that owns the relationship. Example: Contact |
| ParentFieldToken | String | Salesforce tokenized name of the table column that owns the relationship. Example: AccountId (for relationship Contact.ParentId) |
| ParentId | String | Salesforce Id of the record that owns the relationships. |
| ReferenceTableToken | Integer | Salesforce tokenized name of the table pointed to by the relationship. Example: Account. |
| ReferenceId | Integer | Salesforce Id of the record the relationship is pointing to. |
| ArchivedDate | Timestamp | The timestamp when the record was written to the snapshot (UTC). |