Lieven Cardoen

Database version control MSSQL C#[dot]NET Program

Posted by admin on November 5th, 2009

Some time ago I read an article on the importance of version control for databases by Jeff Atwood. I knew that in our team we had to start using something like this because updating different databases to a new release took a lot of time. We always used sql compare to look for the difference between the two databases and to update the database. Sometimes, with small changes, this wasn’t that bad, but with big differences and with data that also needed to be inserted, it was a nightmare. And if you had done one database, you needed to do it all over on another… very bad, very bad, but hey, we learn, we learn. Now I’ve created a C#.NET Console Application that handles most of the things described in the blog of Jeff Atwood. It’s certainly not done and lots of things can be improved, but due to a lack of time right now, this will currently have to do it. It does its job and that’s all we needed. This is the source code and this is the release build. In order to follow this serie of posts, you’d have to read the article by Jeff Atwood because I will not repeat everything he has said. In the source code zip there’s a folder lib with libraries I used and a folder files with the files I will explain further on.

What’s not implemented?

Setting up a database from scratch using the baseline scripts is not implemented.
Configuring more things like folder structure
Support for databases other than mssql
Views are not implemented as we do not use them yet in our application. Implementing them however is pretty easy and you should be able to do it yourself with the source code.
…

How does it work?

First you have to have a certain folderstructure because I haven’t had time to configure that. Here’s a screenshot of how it should look:

Screenshot Folder Structure

I quickly wrote the program so that we could use Database Version Control as quick as possible. Because of that default it works with this folder structure. Under Program is the Release build of the C# Console Application. Under the root of Scripts are the update scripts along with some files I’ll discuss later. Under the functions and storedprocedures are the create (no alter scripts because the sp’s and functions are removed when running the exe!) scripts for functions and stored procedures.

How does the root of Scripts look like:

ScreenShotRoot Database version control MSSQL C#[dot]NET Program

Screenshot Files under Scripts folder

DropStoredProceduresAndFunctions.sql: Script that will be used to delete all sp’s and functions out of the database. This will be necessary before creating all sp’s and functions.
excludeList.csv: This is a text file with comma seperated values. If one of the values appears in the name of a sp or function, then this sp or function will be ignored (will not be deleted and not be created).
sc.xx.xx.xxxx.sql: The update scripts. The name is important as the exe depends on that.
sc.template.sql: This is a template script that is best used to create an update script

Explanation of scripts

DropStoredProceduresAndFunctions.sql script:

-- variable to object name
DECLARE @name VARCHAR(1000)
-- variable to hold object type
DECLARE @xtype VARCHAR(20)
-- variable to hold sql string
DECLARE @sqlstring nvarchar(4000)
DECLARE SPViews_cursor cursor FOR
SELECT QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)
AS name, ROUTINE_TYPE AS xtype
FROM
INFORMATION_SCHEMA.ROUTINES
UNION
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS
name, 'VIEW' AS xtype
FROM
INFORMATION_SCHEMA.VIEWS
 
OPEN SPViews_cursor
fetch NEXT FROM SPViews_cursor INTO @name, @xtype
while @@fetch_status = 0
BEGIN
-- test object type if it is a stored procedure
IF @xtype = 'PROCEDURE' ###
BEGIN
SET @sqlstring = 'drop procedure ' + @name
EXEC sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- test object type if it is a function
IF @xtype = 'FUNCTION' ###
BEGIN
SET @sqlstring = 'drop FUNCTION ' + @name
EXEC sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- test object type if it is a view
--if @xtype = 'VIEW'
-- begin
-- set @sqlstring = 'drop view ' + @name
-- exec sp_executesql @sqlstring
-- set @sqlstring = ' '
-- end
-- get next record
fetch NEXT FROM SPViews_cursor INTO @name, @xtype
END
close SPViews_cursor
deallocate SPViews_cursor

I’ve got this from here. The ### tokens will be replace by transact-sql by the program to exclude sp’s and funtions with certain keywords (excludeList.csv).

Example of an update script:

/*
Comment
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error INT)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
 
-- sql code
 
GO
 
INSERT INTO [dbo].[SchemaChanges]
([MajorReleaseNumber]
,[MinorReleaseNumber]
,[PointReleaseNumber]
,[ScriptName]
,[DateApplied])
VALUES
('03'
,'01'
,'0011'
,'Comment'
,GETDATE())
 
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT&gt;0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

These sort of scripts will be executed by a SqlCommand (DbCommand from Enterprise Library). We are still experimenting and have seen that the Transactions do not work. Somehow there’s a difference between these kind of scripts being executed in Management Studio and being executed by a SqlCommand. I think we’ll have to use TransactionScrope in the C# Console Application to make the transactions work. These Transactions are important because if only a part of the script would be executed, then the build would be broken. You would then have to restore a previous backup, fix the script and run the exe again against the database. Later on I’ll talk about some best practices to use database version control. The GO statements are also a program and the program filters them out before executing the script (again with some other keywords that cause problems). In a couple of months, when we have been able to work things out a little better, I’ll post a new version. For now, this is what you get .

In the script there’s place to put your sql code and then there’s an important part, updating the SchemaChanges table. Each update script inserts a new record in the table SchemaChanges so that the update gets registered in the database. With this table, the program can check what the last update was and look for new scripts in the Script folder. It’s very important that the values you insert into the SchemaChanges table correspond to the name you gave to the file, otherwise the whole thing will not work correctly.

sc.template.sql script:

/*
Commentaar
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error INT)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
 
-- Plaats SQL Code hier
 
GO
 
INSERT INTO [dbo].[SchemaChanges]
([MajorReleaseNumber]
,[MinorReleaseNumber]
,[PointReleaseNumber]
,[ScriptName]
,[DateApplied])
VALUES
('00'
,'00'
,'0000'
,'Short Description'
,GETDATE(), 'Delete this parameter (is to make sure you change the version numbers')
 
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT&gt;0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

This is the template, which will surely change in the next months. The INSERT query will not work like it is written because there’s a value too much. I’ve done this so that my teammembers wouldn’t forget to edit the release numbers.

Explanation of the program and source code

Let’s start of with explaining how the exe (DatabaseVersionControl.exe) works:

Parameters that are required (one):

-connectionString=”Data Source=OMITTED;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=OMITTED;MultipleActiveResultSets=True”

Parameters that are optional (two):

-include “value1,value2,…” : If one of these values are in the excludeList.csv, then they will be overruled so that they will not be excluded.

-scriptsDirectory=”…” : Path to the directory which contains the scripts. Under this folder the two folders storedprocedures and functions must be existing, otherwise the program will not work. Maybe that in the future this can be configured, but for now it’ll do. If this parameter is left out, then the exe searches for the scripts folder at ../Scripts. Change this if you want in the soruce code.

The source code itself is explained in the code. You’ll need some 4.1 enterprise libraries, but I guess you’ll see that if you try to compile the code. I have put them in a lib folder, but the project uses the dll’s from the registry.

We use cruisecontrol to set up a part of continuous integration. One project of cruisecontrol is listening to the scripts svn repository and executes the exe with the proper connectionstring is something has changed.

Best practices that we use in our team

Everybody that wants to work at the database has a local database.
Before starting to write an update script or change a sp or function, update your svn repository and execute the exe against your local database (and make sure you use the right one if you also have maintenance branches). I create a local bat file for each branch and for the trunk.
If you want to change an sp or function, open the corresponding file, edit it, save it and run the exe. If everything works, commit the changed sp or function.
If you want to create an update script, copy the template and give it the right name. If the last update script is sc.03.01.0017.sql, then the new one should be sc.03.01.0008.sql or sc.03.02.0000.sql or sc.04.00.0000.sql, depending on what kind of change you are going to make. !Do not commit at this point!
Open the change script in an editor or in management studio and put in your scripts. Change the release numbers and put in some comment. Save the script and run the exe. If everything works, commit the change script.

Executing a change script, sp script or function script in management studio is a bad idea. It may work in management studio but not with the exe. In the future we’ll have to check some things that happen with the exe and not with executing them in management studio, but for now we use what we have. If you do want to test changes in management studio you should take a backup before you start doing that. If you think your script is done, then restore your db and run the exe. It’s not fun for other developers if things go wrong on the development database after you committed.

Continuous Integration, Database 3 Comments »

Branch/Tag multiple repos (SubVersion)

Posted by admin on March 9th, 2009

For our product we have several repos that all need to be branched when releasing a stable version. Manually doing this takes a lot of time and because of that I created an ant-script that does this. There are four main public targets and four main private targets:

A public target to create branches and a private to create one branch.
A public target to create tags and a private to create one tag.
A public target to delete branches and a private to delete one branch.
A public target to delete tags and a private to delete one tag.

Important parameters that need to be defined (can be done using ant-form, in a properties file or in the main ant file):

cvsdude.repository.branch.name : Name of the branch to create or delete in each of the repositories.
cvsdude.repository.tag.name : Name of the tag to create or delete in each of the repositories.
cvsdude.repository.user : Username for credentials repository.
cvsdude.repository.passwd : Password for credentials repository.
repos : Comma seperated list of the url’s of repositories.

Create Branches

<!-- Creates a Branch -->
<!-- @param currentUrl Url of repo -->
<!-- @param cvsdude.repository.branch.name Name of the Branch -->
<!-- @param cvsdude.repository.user Username (credentials repository) -->
<!-- @param cvsdude.repository.passwd Password (credentials repository) -->
<target name="create-branch" xmlns:ac="http://antcontrib.sf.net">
	<ac:trycatch>
		<try>
			<echo level="info">Branching ${currentUrl}</echo>
			<svn username="${cvsdude.repository.user}" password="${cvsdude.repository.passwd}">
				<copy srcUrl="${currentUrl}/trunk" revision="HEAD" 
					destUrl="${currentUrl}/branches/${cvsdude.repository.branch.name}" 
					message="Branching Edumatic3 ${timeStamp}"/>
			</svn>
		</try>
		<catch>
			<echo> Failed Deleting Branch ${currentUrl}</echo>
		</catch>
		<finally>
		</finally>
	</ac:trycatch>
</target>
 
<!-- Creates Branches for all repos in ${repos} -->
<!-- @param ${cvsdude.repository.branch.name} Holds name of Branch -->
<!-- @param ${repos} Comma seperated list of repositories -->
<target name="create-branches" xmlns:ac="http://antcontrib.sf.net">
 
	<echo message="create-branches"/>
	<echo message="Name branch: ${cvsdude.repository.branch.name}"/>
	<echo message="repos:${repos}"/>
 
	<ac:for list="${repos}" param="currentRepo" trim="true">
		<sequential>
			<antcall target="create-branch">
				<param name="currentUrl" value="@{currentRepo}"/>
			</antcall>
		</sequential>
	</ac:for>
</target>

Delete Branches

<!-- Deletes a Branch -->
<!-- @param currentUrl Url of repo -->
<!-- @param cvsdude.repository.branch.name Name of the Branch -->
<!-- @param cvsdude.repository.user Username (credentials repository) -->
<!-- @param cvsdude.repository.passwd Password (credentials repository) -->
<target name="delete-branch" xmlns:ac="http://antcontrib.sf.net">
	<ac:trycatch>
		<try>
			<echo level="info">Deleting Branch ${currentUrl}</echo>
			<svn username="${cvsdude.repository.user}" password="${cvsdude.repository.passwd}">
				<delete url="${currentUrl}/branches/${cvsdude.repository.branch.name}"
						message="Deleting Branch ${cvsdude.repository.branch.name}"/>
			</svn>
		</try>
		<catch>
			<echo> Failed Deleting Branch ${currentUrl}</echo>
		</catch>
		<finally>
		</finally>
	</ac:trycatch>
</target>
 
<!-- Deletes Branches for all repos in ${repos} -->
<!-- @param ${cvsdude.repository.branch.name} Holds name of Branch -->
<!-- @param ${repos} Comma seperated list of repositories -->
<target name="delete-branches" xmlns:ac="http://antcontrib.sf.net">
	<echo message="delete-branches"/>
	<echo message="Name branch: ${cvsdude.repository.branch.name}"/>
 
	<ac:for list="${repos}" param="currentRepo" trim="true">
		<sequential>
			<antcall target="delete-branch">
				<param name="currentUrl" value="@{currentRepo}"/>
			</antcall>
		</sequential>
	</ac:for>
</target>

Create Tags

<!-- Creates a Tag -->
<!-- @param currentUrl Url of repo -->
<!-- @param cvsdude.repository.tag.name Name of the Tag -->
<!-- @param cvsdude.repository.user Username (credentials repository) -->
<!-- @param cvsdude.repository.passwd Password (credentials repository) -->
<target name="create-tag" xmlns:ac="http://antcontrib.sf.net">
	<ac:trycatch>
		<try>
			<echo level="info">Tagging ${currentUrl}</echo>
			<svn username="${cvsdude.repository.user}" password="${cvsdude.repository.passwd}">
				<copy srcUrl="${currentUrl}/trunk" revision="HEAD" 
					destUrl="${currentUrl}/tags/${cvsdude.repository.branch.name}" 
					message="Branching Edumatic3 ${timeStamp}"/>
			</svn>
		</try>
		<catch>
			<echo> Failed Deleting Tag ${currentUrl}</echo>
		</catch>
		<finally>
		</finally>
	</ac:trycatch>
</target>
 
<!-- Creates Tags for all repos in ${repos} -->
<!-- @param ${cvsdude.repository.tag.name} Holds name of Tag -->
<!-- @param ${repos} Comma seperated list of repositories -->
<target name="create-tags" xmlns:ac="http://antcontrib.sf.net">
	<echo message="create-tags"/>
	<echo message="Name tag: ${cvsdude.repository.tag.name}"/>
 
	<ac:for list="${repos}" param="currentRepo" trim="true">
		<sequential>
			<antcall target="create-tag">
				<param name="currentUrl" value="@{currentRepo}"/>
			</antcall>
		</sequential>
	</ac:for>
</target>

Delete Tags

<!-- Deletes a Tag -->
<!-- @param currentUrl Url of repo -->
<!-- @param cvsdude.repository.tag.name Name of the Tag -->
<!-- @param cvsdude.repository.user Username (credentials repository) -->
<!-- @param cvsdude.repository.passwd Password (credentials repository) -->
<target name="delete-tag" xmlns:ac="http://antcontrib.sf.net">
	<ac:trycatch>
		<try>
			<echo level="info">Deleting Tag ${currentUrl}</echo>
			<svn username="${cvsdude.repository.user}" password="${cvsdude.repository.passwd}">
				<delete url="${currentUrl}/tags/${cvsdude.repository.branch.name}"
						message="Deleting Tag ${cvsdude.repository.branch.name}"/>
			</svn>
		</try>
		<catch>
			<echo> Failed Deleting Tag ${currentUrl}</echo>
		</catch>
		<finally>
		</finally>
	</ac:trycatch>
</target>
 
<!-- Delete Tags for all repos in ${repos} -->
<!-- ${cvsdude.repository.tag.name} holds name of Tag -->
<!-- @param ${repos} Comma seperated list of repositories -->
<target name="delete-tags" xmlns:ac="http://antcontrib.sf.net">
	<echo message="delete-tags"/>
	<echo message="Name tag: ${cvsdude.repository.tag.name}"/>
 
	<ac:for list="${repos}" param="currentRepo" trim="true">
		<sequential>
			<antcall target="delete-tag">
				<param name="currentUrl" value="@{currentRepo}"/>
			</antcall>
		</sequential>
	</ac:for>
</target>

I guess that some things can be made more generic and put in macros as lot of logic is duplicated…. But this should get you going.

The libraries you need are svnant and ant-contrib.

thx, Lieven Cardoen

Continuous Integration No Comments »

Ant Replace Multiple Lines of Text

Posted by admin on October 25th, 2008

Yesterday I needed to uncomment multiple lines in the applicationcontext of a Prana XML. After some searching I found the Ant Task I needed: replaceregexp.

Threre were four blocks that I needed to uncomment:

...
</value>
<value>
	<object	class="edumatic.backoffice.view.modules.NavigationModuleInfo">
		<property name="url"
			value="edumatic/backoffice/view/modules/support/ManagementNavigationModule.swf" />
		<property name="icon"
			value="edumatic/backoffice/view/modules/support/assets/management.png" />
		<property name="title" value="Management" />
		<property name="pluginID" value="MANAGEMENT" />
	</object>
</value>
<value>
	<object
		class="edumatic.backoffice.view.modules.ContentModuleInfo">
		<property name="url"
			value="edumatic/backoffice/view/modules/support/ManagementContentModule.swf" />
		<property name="pluginID" value="MANAGEMENT" />
	</object>
</value>
<value>
	<object
		class="edumatic.backoffice.view.modules.NavigationModuleInfo">
		<property name="url"
			value="edumatic/backoffice/view/modules/support/MetaDataNavigationModule.swf" />
		<property name="icon"
			value="edumatic/backoffice/view/modules/support/assets/metadata.png" />
		<property name="title" value="Metadata" />
		<property name="pluginID" value="METADATA" />
	</object>
</value>
<value>
	<object
		class="edumatic.backoffice.view.modules.ContentModuleInfo">
		<property name="url"
			value="edumatic/backoffice/view/modules/support/MetaDataContentModule.swf" />
		<property name="pluginID" value="METADATA" />
	</object>
</value>
<value>
...

I couldn’t just search for and replace it by because there were childnodes that had to stay uncommented.

So here are the replaceregexp tasks that made it work:

<replaceregexp file="${selor-salto-test-deploy-folder.dir}/backend/application-context-backoffice.xml"
	byline="false" flags="s">
	<regexp pattern="&lt;value&gt;.{1,250}ManagementNavigationModule"/>
	<substitution expression="&lt;!--value&gt;
				&lt;object	class=&quot;edumatic.backoffice.view.modules.NavigationModuleInfo&quot;&gt;
					&lt;property name=&quot;url&quot;
						value=&quot;edumatic/backoffice/view/modules/support/ManagementNavigationModule"/>
</replaceregexp>
<replaceregexp file="${selor-salto-test-deploy-folder.dir}/backend/application-context-backoffice.xml"
	byline="false" flags="s">
	<regexp pattern="ManagementContentModule.swf.{1,150}&lt;/value&gt;"/>
	<substitution expression="ManagementContentModule.swf&quot; /&gt;
					&lt;property name=&quot;pluginID&quot; value=&quot;MANAGEMENT&quot; /&gt;
				&lt;/object&gt;
			&lt;/value--&gt;"/>
</replaceregexp>
<replaceregexp file="${selor-salto-test-deploy-folder.dir}/backend/application-context-backoffice.xml"
	byline="false" flags="s">
	<regexp pattern="&lt;value&gt;.{1,250}MetaDataNavigationModule"/>
	<substitution expression="&lt;!--value&gt;
				&lt;object	class=&quot;edumatic.backoffice.view.modules.NavigationModuleInfo&quot;&gt;
					&lt;property name=&quot;url&quot;
						value=&quot;edumatic/backoffice/view/modules/support/ManagementNavigationModule"/>
</replaceregexp>
<replaceregexp file="${selor-salto-test-deploy-folder.dir}/backend/application-context-backoffice.xml"
	byline="false" flags="s">
	<regexp pattern="MetaDataContentModule.swf.{1,150}&lt;/value&gt;"/>
	<substitution expression="MetaDataContentModule.swf&quot; /&gt;
					&lt;property name=&quot;pluginID&quot; value=&quot;METADATA&quot; /&gt;
				&lt;/object&gt;
			&lt;/value--&gt;"/>
</replaceregexp>

The thing me and a colleague searched on for a while was the s flag that was needed to make it work. I will try the other flags because I’m really not sure what this s flag means… (trial and error yeah!). I’ve seen that after the task ran the layout was messed up (no newlines anymore in the replaced text).

The .{1,150} means that a maximum of 150 characters may occur between for example MetaDataContentModule.swf and </value>.

Ciao!

Continuous Integration 1 Comment »

SvnAnt

Posted by admin on October 4th, 2008

Yesterday I had some problems installing SvnAnt.

When I used the SvnAnt 1.0.0 (bound against subversion 1.3.1) Release I got this error running a svn ant task:

Part of Ant-script:

<path id="all-java-libs">
	<fileset dir="${lib.dir}">
		<include name="**/*.jar"/>
	</fileset>
</path>
 
<taskdef resource="org/tigris/subversion/svnant/svnantlib.xml" classpathref="all-java-libs"/>
 
<svn>
	<status path="${basedir}" revisionProperty="revision"/> 
</svn>

Error:

144: Problem: failed to create task or type svn
Cause: The name is undefined.
Action: Check the spelling.
Action: Check that any custom tasks/types have been declared.
Action: Check that any declarations have taken place.

After a while I thought trying with a development release SvnAnt 1.2.x (bound against subversion 1.5.0) Release

No error with this release… Strange… Googled for a moment but didn’t really find a solution for the stable release.

Continuous Integration No Comments »

Database version control MSSQL C#[dot]NET Program

Branch/Tag multiple repos (SubVersion)

Ant Replace Multiple Lines of Text

SvnAnt

Music

Categories

Archives

Recent Posts

Recent Comments

Meta

Lieven Cardoen

Database version control MSSQL C#[dot]NET Program

Branch/Tag multiple repos (SubVersion)

Ant Replace Multiple Lines of Text

SvnAnt

Follow Me!

Music

Categories

Tags

Archives

Recent Posts

Recent Comments

Meta