In previous articles, I have already covered the revision history for Git and Team Foundation Server. Similarly, this article covers the revision history of committed changesets using Subversion as the source control system.
For the purpose of the article, we’ll use a sample SQL database called MyDatabase whose objects are scripted and committed to the SVN repository. In order to work with Subversion, we’ll use Tortoise SVN, which is a Windows shell extension used to work on a local copy of a database and to communicate (commit changes to, and get changes from) the remote repository. For expediency, I will not go into details about initializing the repository, committing changes, or any other operation. The goal of the article is to cover the following: revision history of all committed changes, comparing two versions of the same object and get a specific version of the object from the revision history.
The following are changes that are committed to the repository, in order to have a history of committed changesets to review:
- Initial commit of all database objects
-
Added a new table dbo.Resellers using the following script:
12345678910111213141516171819USE [MyDatabase]GOCREATE TABLE [dbo].[Resellers]([ResellerID] [int] IDENTITY(1,1) NOT NULL,[ResellerName] [nvarchar](50) NULL,[AddressLine1] [nvarchar](50) NULL,[AddressLine2] [nvarchar](50) NULL,[City] [nvarchar](50) NULL,[ZipCode] [int] NULL,[DateOfContract] [date] NULL,[Active] [bit] NULL,CONSTRAINT [PK_Resellers] PRIMARY KEY CLUSTERED([ResellerID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO Modified the AddressLine1 column from the previously created dbo.Resellers table, by renaming it to StreetAddress. In the same changeset the AddressLine2 column is dropped in favor of creating the ApptNo column. The following scripts are used:
123456EXEC sp_rename 'dbo.Resellers.AddressLine1', 'StreetAddress', 'COLUMN';GOALTER TABLE dbo.Resellers ADD ApptNo int;ALTER TABLE dbo.Resellers DROP COLUMN AddressLine2;Creating the usp_ResellersInfo stored procedure
12345678910USE [MyDatabase]GOCREATE PROCEDURE dbo.usp_ResellersInfoASSET NOCOUNT ON;SELECT ResellerID, ResellerName, City, ZipCodeFROM dbo.Resellers;GO
Revision history
Once we have all of the above committed, let’s inspect the revision history. Since TotroiseSVN is a shell extension, all available options can be found in the right-click context menu of the working copy folder. To review the entire history of committed changesets, right-click the folder that represents the working copy of a remote repository and select the SVN Show log option. The same option is available on right-click menu inside the folder:
This initiates the History form:
All committed changes will be listed in the upper section, showing the exact revision ID (in this case from 1 to 4), the appropriate actions (added, modified, deleted), the user who performed the commit (in the Author column), timestamp of the commit, and the commit message.
By highlighting any of the changesets from the list, the full commit message appears in the section below. In this case, for the selected changeset 4, the commit message says that the usp_ResellersInfo stored procedure is created/committed in this changeset. The last section shows the list of files committed in the selected changeset. For instance, we have committed only one file in Changeset 4, and that is a SQL script of the mentioned stored procedure.
The history form contains all the information about the general overview of committed changes, such as who committed what and when. However, when it comes to reverting from the history, such changes need to be compared with the working copy (or even with other changesets) before applying.
Compare between revisions
In order to inspect specific change in details and compare between revisions, right click any of the changeset and click the Compare with previous revision option. Specifically, we’ll compare between Changeset 3 (where the dbo.Resellers table is created) and Changeset 4 (where we made some modifications in the dbo.Resellers table, and therefore it is expected to have some differences when comparing):
In case there are differences between the selected changeset (in this case Changeset 3 and the previous one (Changeset 2), the list of files where differences are detected will be shown. Since we have committed a single file (the Resellers table), it is the only one listed below:
The Changed Files form, shown in the above image, gives only the list of files where differences are detected. At this point, you can specify any other changeset as a source or the target for the comparison. This can be achieved by clicking any of the revisions, and specifying another revision for comparison:
To review the exact differences, right click on the file from the list, and from the context menu, choose the Compare revisions option:
This initiates the form where the exact differences are available for review:
Specifically, the comparison between the version of the Resellers table from Changeset 3 and Changeset 2 gives the expected result. The AddressLine1 column was renamed to StreetAddress (line 7), the AddressLine2 column was dropped (line 8), and a new column ApptNo was created (line 12). These changes are committed in Changeset 3.
In addition to this, differences between two versions of the same object can be shown within a single form instead of showing the two tabs in parallel. To review differences in a single tab, right-click the file in the Changed Files form, and from the context menu, select the Show difference as unified diff option:
This will combine object differences in a single form highlighting added lines in green, and removed ones in red. In this particular case, differences between the version of the Resellers table across two changesets will be as follows:
Revision history of the specific object
In addition to reviewing the entire history, there is an option to review the history for a single object, ignoring any other objects and changesets that do not contain the specific object. In the example included in this article, there are 4 commits only, so it is not that hard to review the history. However, in case of having numerous commits from multiple users, where each commit contains a set of files, it is necessary to have a mechanism to show the history for a single file, isolated from the rest of the history. In order to achieve this, navigate to a file inside any changeset and from the right-click context menu, select the Show log option. In this case, we have selected the Resellers table inside the Changeset 3:
The complete history of a single object is shown, no matter in which changeset is selected (if the object is selected in the Changeset 5 for example, the Show log option will give the complete history for the selected objects, and not just up to the selected changeset). The form that the Show log option initiates is the same as the form for the history of all objects, but with the difference being that changesets containing the selected objects are the only ones that will be shown:
In this case, only Changeset 2 (where the Resellers table is committed initially) and the Changeset 3 (where it is modified) will be shown.
Get a specific version of an object from history
To get a specific version of an object from history and apply it on a working copy of a database, navigate to the specific object in the changeset that contains the version of the object to be applied. In this case, we’ll apply the initial version of the dbo.Resellers table initially committed in Changeset 2. Right-click on the object under the list of files from the single changeset, and from the context menu, select the Revert changes from this revision option:
Confirm reverting in the next dialog, and the summary of the operation will appear as follows:
Checking the working copy shows that reverting was finished successfully, as the current version of the object is with the originally created columns AddresLine1, and AddressLine2, without the ApptNo column that was created in the next changeset:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE [MyDatabase] GO CREATE TABLE [dbo].[Resellers]( [ResellerID] [int] IDENTITY(1,1) NOT NULL, [ResellerName] [nvarchar](50) NULL, [AddressLine1] [nvarchar](50) NULL, [AddressLine2] [nvarchar](50) NULL, [City] [nvarchar](50) NULL, [ZipCode] [int] NULL, [DateOfContract] [date] NULL, [Active] [bit] NULL, |
By following these steps, any specific version of an object can be retrieved from the history and made the current version.
Useful links:
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