/
Database Generation Application

Database Generation Application

Owned by Girisha (Deactivated)
Last updated: Aug 13, 2014 by FuseIT OA

The database generation can be configured to either generate for every exposed Salesforce object or an explicit subset. This setting is controlled by specifying the “entities” appSetting in the config file. E.g.

<add key="entities" value="User, Document, Folder"/>

Salesforce schema data

There are two ways to get schema data in database generation:

  1. Salesforce Session for schema data: G4S is used with the "SalesforceConnectionString" to establish a Salesforce session for extracting the schema information through the Partner API. Information includes the available objects, their fields, data types, and labels. Only meta-data that is available to the Salesforce user defined in the connection string will be available in the IDR system. Refer to the G4S documentation for the format of this connection string.
  2. Data Cache for schema data: Salesforce schema data is downloaded while the data export zip files are pulled. It is stored as binary data in the data export folder along with the zip files and is named as 'dataCache'. While database generation this file is parsed to get Salesforce schema data. 

Connection to SQL 2008/2012 server

The generation tool will connect to the configured SQL 2008/2012 server using the configured "SqlAdminConnectionString" connection string. This connection requires sufficient permissions to create and manipulate the database. This is a standard ADO.NET connection string.

Create the target database

The first step the database generation application will perform is to create the target SQL 2008 database. The SQL Server Management Objects (SMO) is used to automate the process from .NET. 

If the target database already exists it will be put into Single user mode and dropped.

Create tables in the database

The FuseIT.SDDMS.GenerateDatabase.DatabaseTables class is used to generate tables in the target database using meta-data from the Salesforce schema.

For each table Extended Properties will be set to include meta-data, such as the label and label plural from Salesforce for the entity.

If the entity is Attachment the table can be optionally configured to use File Streams. This behaviour is set using the useFileStreamForAttachments appSetting. E.g.

<add key="useFileStreamForAttachments" value="true"/>

 

Polymorphic Foreign Keys will be split out into one column per reference type so that the relationships can be enforced in SQL.

The CreatedDate, LastModifiedDate, and SystemModstamp columns will be set to default to the current UTC datetime.

The CreatedById and LastModifiedById columns will be set to default to the System user.

The Salesforce Id column will always be treated as the primary key for the column

SQL Column types will be set to the nearest SQL equivalent to the Salesforce type.

Create Relationships

The FuseIt.SDDMS.GenerateDatabase.DatabaseRelationships class is used to create Foreign Key references for the created tables.

The type of relationship is detected using G4S. E.g. Master-Detail or Lookup.

Polymorphic Key references are split to the corresponding columns.

Cascade delete meta-data will be applied unless it creates a cycle or multiple cascade path which isn’t supported in SQL.

Populate Data Export Data into SQL

Prior to loading any data the relationships in the SQL database will be disabled to avoid issues with the order of the data loading. 

CSV files will be extracted from the Data Export zip files and inserted into the SQL tables using SqlBulkCopy. 

If as CSV file is encountered where no SQL table has been created from the Salesforce schema meta-data a table can be created using the CSV data alone. 

 

<!-- Issue 5076 - If a CSV file in the DataExport doesn't have a corresponding Salesforce object should a table be created and populated in SQL? -->
    <add key="CreateTablesFromCsvAlone" value="false"/>

 

If a column in the CSV doesn’t have a corresponding column in SQL in can be created and populated with the CSV data.

<!-- Issue 5077 - If a column from a CSV file in the DataExport doesn't have a corresponding Salesforce object field should a Column be created and populated in SQL? -->
    <add key="CreateColumnsFromCsvAlone" value="false"/>

 

If the CSV data does not have a value for a non-nullable column it will be populated with a value suitable for its data type. (Task 5078 - CSV does not contain data for a mandatory [non-nullable] schema field).

If the CSV string data exceeds the maximum length of the SQL column it will be truncated. (Task 5075)

After the CSV data has been loaded the binary data for the Attachments and Documents will be extracted from the CSV files and inserted into the SQL tables.

After the data loading is complete the relationships will be reactived.

SQL Triggers

When applicable for the Salesforce Entity an on insert trigger will be created in SQL to set the CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, and SystemModstamp values.

Similarly, another SQL trigger will be created for update to set the LastModifiedDate, LastModifiedById and SystemModstamp.

The System user ID of "005000000000001AAA" will be used from these triggers to indicate that the change occurred in the disaster recovery system.

If a user with ID "005000000000001AAA" is present in database and "Profile" and "UserLicense" tables are present in SQL database then, profile with ID "00e000000000001AAA" and user license with ID "00q000000000001AAA" will also be added to the database.