Since we have presented a way to review the history of committed changes using Git, Team Foundation Server and Subversion, let’s check how the same can be achieved when working with the Mercurial source control system.
We’ll be using Tortoise Hg, free Mercurial client that comes with Windows shell integration. For the purpose of this article we’ll use a sample database called StoresDB whose objects are scripted in separate files and saved in a local folder called MercLocal. The same folder is initialized to be a local Mercurial repository. I won’t include details about initializing the repository and committing changes. The goal of the article is to cover the following: revision history of all committed changes, comparing two versions of the same object committed in two different changesets and getting specific version of an object from the commit history.
As a starting point, let’s assume that the following tasks were performed against previously scripted database object files:
Initial commit of all database objects
Committing a new table called Balances using the following SQL script:
12345678CREATE TABLE [dbo].[Balances]([BalanceID] [varchar](15) PRIMARY KEY NOT NULL,[Description] [varchar](255) NULL,[DocID] INT IDENTITY(1,1) NOT NULL)GO
In the next changeset the following changes are committed:
New column called BalanceType is added to the Balances table using the following SQL script:
123ALTER TABLE dbo.Balances ADD BalanceType NVARCHAR(50)
New stored procedure called BalancesList is created using the following script:
12345678CREATE PROCEDURE dbo.BalancesListASBEGINSELECT BalanceID, Description, DocID FROM dbo.BalancesENDGO
New column added to the ProductCategory table:
12345ALTER TABLE Production.ProductCategoryADD CategoryDescription NVARCHAR (255)
The next changeset holds just a single edit. The previously created BalanceType column in the Balances table is renamed using the following script:
123EXEC sp_rename 'dbo.Balances.BalanceType', 'BType', 'COLUMN'The last change we committed is deleting the Sales.CreditCard table:
123DROP TABLE Sales.ShoppingCartItem;
Revision history
In order to review the entire history of committed changes, start the TortoiseHg Workbench application. It can be started from the right click menu inside the folder initialized as a local Mercurial repository (in this case the MercLocal folder):
Double-click the appropriate repository (in this case MercLocal) in the repository tree panel to the left, and the complete history of committed changes will load on the right panel:
As shown in the above image, the history view presents the task workflow introduced in the article. The first commit (at the bottom of the list) shows initial commit of all database objects, followed by commits where the rest of changes are committed.
Each commit in the list contains the revision ID (the Rev column), branch where it is committed (currently all changes are committed to default branch), description – which is actually the commit message, the author of the commit (all commits are performed by the same user – ApexSQL Test). The Age column stores the time that passed after the commit is performed. The Changes column at the end, shows the exact number of changes included in the specific changeset, along with the appropriate colors that represents the actions performed against committed files:
For instance, 131 highlighted in green for the first commit (Rev 0) indicates that 131 new objects are committed to the repository. The third changeset (Rev 2) that has 1 highlighted in green and 2 highlighted in orange indicates that one new file (object) is added, and two existing objects were updated/edited. The most recent commit shown on top of the list (Rev 4) where 1 is highlighted in orange indicates that one object is deleted from the repository which corresponds to deleting the ShoppingCartItem table in the last commit.
Revision history for the single object
To review the history of the specific object only, navigate to the object in the local folder which is initialized as a Mercurial repository (in this case this is the MercLocal folder) and right click the specific object that you want to compare across revisions (in this case, we’ll review the revision history for the dbo.Balances table), and select the Revision History option:
This initiates the Log file viewer form, that shows the list of changesets which contain the selected file. For the highlighted revision (in this case Rev 1) the version of an object committed in that revision will be shown in the section below:
Selecting the next revision (Rev 2) from the list, shows that a new column (BalanceType) is added, which is indicated with the green highlighted line (line 18):
Selecting the last revision (Rev 3) where the BalanceType column is renamed to BType will be highlighted in different color (line 18 where the actual change is shown will be highlighted in purple):
Compare between two revisions
To compare between two revisions, select the first one from the commit history, press and hold the CTRL key, select the second revision, and right click any of them. From the right click menu, select the Visual Diff option. In this case, we’ll compare Rev 3 with Rev 1:
This initiates a new form that lists all the differences between selected revisions. In order to show differences for the specific object, double-click that object from the list. We’ll do that for the dbo.Balances table:
Compare between versions of the specific object across revisions
The above described comparison between revisions gives a list of all objects that are compared, so the user can select which one to inspect in details, as we did for the dbo.Balances table. In case we need to inspect differences for the specific object across revision history, there is no need to compare all objects from one changeset with all objects from another changeset. To narrow down the comparison to a single object, select it from the list of objects in any changeset, and from the right click menu select the Compare File Revisions option. We’ll perform that against the dbo.Balances table from the Rev 2 revision:
This initiates the Log file viewer form for the selected object. Both sides, left and right, shows the same list of revisions related to specific object (in this case the Balances table). To compare between revisions, simply select one on the left side, and another one on the right (we’ll compare the version of the balances table from Rev 1 with the version from Rev 3):
The above image shows that the BType we have introduced in Rev3 didn’t exist in Rev 1 when the Balances table was committed initially.
Revert to the specific revision
In case the entire revision needs to be reverted, right click on it from the revision history and select the Revert All Files option:
Reverting to Rev 1 discards all changes committed after the Rev 1. This means that BalancesList stored procedure will be deleted, as well as BType column that was committed in the later revision. Checking the Balances table in the local repository gives the following result:
This confirms that the BalanceType column introduced in the next revision, and renamed to BType later, does not exist.
Get specific version of an object from the revision history
In case only specific object(s) need to be reverted, instead of the entire revision, navigate to the specific revision, select one (or multiple files using the CTRL key), and from the right click menu select the Revert to Revision option:
The above image represents reverting the version of the Balances table from Rev 2, without reverting other changes from the same revision. Selecting the Revert to Revision option from the right click menu gives a confirmation dialog, where optionally all object from the selected revision can be reverted (by checking the Revert all files to this revision option). In order to demonstrate reverting of a single object, this option will be unchecked:
After clicking the OK button, to confirm reverting just the Balances table, let’s inspect the local repository.
The Balances table is shown as changed, and reviewing the actual script shows that BType column committed in Rev 4 is reverted back to BalanceType committed in Rev 2:
Also, BalancesList stored procedure still exists in the local repository since we have reverted the Balances table only.
Using this approach, any revision can be reverted, which will actually revert all files from the revision, or selecting specific object(s) from the revision can be reverted without affecting the rest of changes from the revision.
To see the full version history of a SQL Server database object under source control, you can try ApexSQL Source Control, an SSMS add-in that allows you to put a database under version control, commit all changes to the repository and easily revert any committed change from the history.
He uses his spare time to play guitar, ride a bike and hang out with his friends. During winter, he likes skiing the most, but all other snow activities, too.
He is also author of various SQL Shack articles about SSIS packages and knowledgebase articles about ApexSQL Doc.
View all posts by Marko Radakovic