SQL Features
Content Management System Migration
Often times products will build content management systems into themselves to store visual pages, documents, or domain-specific langauge files that
drive how the product functions.While the custom content management system may have fulfilled your organization's needs when the product was starting out, needs may have grown and
started requiring some of those artifacts to be managed along side your code in your source control system.
The Timely Migration SQL interface consists of a handful of stored procedures that you will need to write to allow Timely Migration to retrieve high level revision information,
item level actions, and item contents. Timely Migration will call those stored procedures and use the common Timely Migration plumbing to consolidate and filter / reorganize the contents
based on what configured for the session.
Since the interface is implemented through stored procedures, you can also use SQL permissions to create a user account for Timely Migration that only has permissions to execute
those stored procedures to alleviate any security concerns.
Content mirroring
If you are not ready to migrate off your content management system yet, maintenance concerns such as tracking when changes were made, by who, and what exactly they consisted of can be cumbersome to build into the product.
Timely Migration can be used to create a mirror of the content in your source control system to allow your developers to manage those concerns with the tools they are familiar with.
Change Consolidation
During the analysis process, Timely Migration will group individual file revisions by author, comments and time into change groups which are then migrated to Git commits or TFS (TFVC) changesets. This brings one of the benefits of destination to the existing source tree as consolidation provides a more coherent view of the changes that occurred and what they were related to. This is especially useful when looking at source history to research issues in the current product.
Sample Database
To provide a concrete example of the stored procedures that needs to be implemented, the configuration UI has the ability to create a sample database that can be used to perform a test migration. SQL Server Management Studio can then be used to the script the stored procedures as templates you can adapt to your database structure.
SQL Interface
Descriptions of the stored procedures that make up the SQL interface and the expected result columns are below. The execution statements and SQL Server Management Studio result examples are based on the sample database created when you use the "Configuration Sample Session" and "Create Sample Database" buttons on the "Source" tab.
Project Retrieval
exec spTimelyMigration_GetProjects
| Column | Data Type | Description | |
| Results: | Id | bigint | A unique id for the project. |
| Name | nvarchar(max) | The name of the project. |
Branch Retrieval
exec spTimelyMigration_GetBranches @ProjectName = 'TimelyMigration'
| Column | Data Type | Description | |
| Inputs: | ProjectName | nvarchar(max) | The name of the project you want to retrieve folders for. If the interface does not require projects, this parameter will be an empty string. |
| Results: | Id | bigint | A unique id for the branch. |
| Name | nvarchar(max) | The name of the branch. | |
| ParentBranchId | bigint (null) | A reference to a parent branch the branch was created from. |
Folder Retrieval
exec spTimelyMigration_GetFolders @ProjectName = 'TimelyMigration',
@BranchName = 'Main'
| Column | Data Type | Description | |
| Inputs: | ProjectName | nvarchar(max) | The name of the project you want to retrieve folders for. If the interface does not require projects, this parameter will be an empty string. |
| BranchName | nvarchar(max) | The name of the branch you want to retrieve folders for. If the interface does not require branches, this parameter will be an empty string. | |
| Results: | Name | nvarchar(max) | The name of the folder. |
Revision Retrieval
exec spTimelyMigration_GetRevisions @ProjectName = 'TimelyMigration',
@StartingRevisionId = 1, @EndingRevisionId = default
| Column | Data Type | Description | |
| Inputs: | ProjectName | nvarchar(max) | The name of the project you want to retrieve revisions for. If the interface does not require projects, this parameter will be an empty string. |
| StartingRevisionId | bigint null | The starting revision that results should be returned for. When a session is resumed startingRevision will be set to the revision after the last revision that was analyzed. The StartingRevision setting can also be used to set a minimum starting revision. | |
| EndingRevisionId | bigint null | The ending revision that results should be returned for. This will be set when the EndingRevision setting is used to set a maximum revision to process. | |
| Results: | Id | bigint | A unique id for the revision. |
| Time | datetime2(7) | The time of the change. | |
| Author | nvarchar(128) | The name of the user that made the change. | |
| Comment | nvarchar(max) | The description of the change. |
Revision Item Retrieval
exec spTimelyMigration_GetRevisionItems @ProjectName = 'TimelyMigration',
@StartingRevisionId = 1, @EndingRevisionId = default
| Column | Data Type | Description | |
| Inputs: | ProjectName | nvarchar(max) | The name of the project you want to retrieve revisions for. If the interface does not require projects, this parameter will be an empty string. |
| StartingRevisionId | bigint null | The starting revision that results should be returned for. When a session is resumed startingRevision will be set to the revision after the last revision that was analyzed. The StartingRevision setting can also be used to set a minimum starting revision. | |
| EndingRevisionId | bigint null | The ending revision that results should be returned for. This will be set when the EndingRevision setting is used to set a maximum revision to process. | |
| Results: | Id | bigint | A unique id for the revision. |
| BranchId | bigint | The branch the revision item was changed in. | |
| ItemName | nvarchar(max) | The name of the item that was changed. | |
| ItemType | int | 0 if the item is a file, 1 if the item is a folder. | |
| Actions | nvarchar(max) |
A comma separated list of changes that were made to the item. Supported types are: Add, Edit, Branch, Delete, Undelete, Rename. For Branch actions, PreviousBranchId, PreviousItemName, and PreviousRevisionId should be set. For Rename actions PreviousItemName should be set. |
|
| PreviousRevisionId | bigint | For Branch actions, this is the revision id the current item was branched from. | |
| PreviousBranchId | bigint | For Branch and Rename actions, this is the id of the branch that the current item is based on. | |
| PreviousItemName | nvarchar(max) | For Branch and Rename actions, this is the name of the item that the current item is based on. |
Item Content Retrieval
exec spTimelyMigration_GetItemContent @ProjectName = 'TimelyMigration',
@BranchName = 'Main', @RevisionId = 12, @ItemName = '/Source/Version.txt'
| Column | Data Type | Description | |
| Inputs: | ProjectName | nvarchar(max) | The name of the project you want to retrieve item content for. If the interface does not require projects, this parameter will be an empty string. |
| BranchName | nvarchar(max) | The branch that item content is being requested from. | |
| RevisionId | bigint null | The revision that content is being requested for. | |
| ItemName | nvarchar(max) | The name of the item to retrieve content for. | |
| Results: | StringContent | nvarchar(max) | File contents if the file is a text file. Either StringContent or BinaryContent should be set. |
| BinaryContent | varbinary(max) | File contents if the file is stored as binary file. Either StringContent or BinaryContent should be set. |
