For a number of weeks, we have been regularly releasing code to our UAT database. The release process of database code differs slightly from releasing binary code because the release has to take into account existing data and schema in the database. With binary code (as in C# application) usually old version is just overwritten with new version. For database releases, change script has to be implemented that contains relevant changes to schema and data in the database. Developers who work on QC items prepare partial scripts that cover only issues discovered during QA.
The fixes and new features may be implemented in many SQL scripts. If there are a few teams working on the system, the number of files can grow significantly. There is always a question of sequence the scripts have to be executed in. Obviously, alter script adding a column to a table has to be executed before the script that alters view to use this column for example. The order of the scripts is not always obvious and during testing of the release it may happen that exceptions are thrown because of invalid schema for example.
Trying to simplify testing and the actual release, we decided to release only one script that contains all partial updates. I thought - wouldn't it be nice to be able to drag files to an app and
then arrange them appropriately, then save the resulting script? I looked for a simple utility that will concatenate scripts into one file that would be easier to manage. I found no such utilities (maybe I wasn't looking well enough - if you know about an app like this, let me know!). I considered using a batch file with copy command to stick the scripts together, but this wasn't a 'clean' solution. So I decided to write such tool.
The principle is very simple: you drag the files to the application, order them the right way and you can save the output. This output file contains all partial scripts concatenated and framed with logging messages. These messages allow you to observe progress of the release and locate scripts that cause issues more easily.
The picture shows the screen of the application:
On the left side there is list of files that are part of the release. On the right side, resulting script is being shown. Clicking on a file in the left listbox navigates to the beginning of this file in the right window. As you can see, the file is preceeded with raiserror message logging the name of the file. Additionally, full file path is commented into the release script. The logging messages have by default "---#" prefix, which allows them to be easily distinguished from other logging that may take place during execution of the release script.
You can save both output of the "compilation" and the project, which contains list of partial scripts in the order they are displayed in the listbox. Having a project has the advantage of being able to regenerate the same output if the partial files change. You can refresh the list of files with command File/Refresh. This command reloads files listed in the listbox.
There is configuration file associated with the application. It contains several simple settings:
<configuration>
<appSettings>
<add key="AddFileName" value="true"/>
<add key="AddFileNameFormat" value="\r\n/******************** {FileName} **********************/\r\n"/>
<add key="ItemSeparator" value="\r\nGO\r\n/* END END ******************* {FileName} ********************* END END */\r\n\r\n"/>
<add key="AllowedExtensions" value=".sql;.prc;.tab"/>
<add key="OutputFileName" value="true"/><!--should filename be output to resulting script-->
</appSettings>
</configuration>
These settings are as follows:
- AddFileName: If true, AddFileNameFormat setting is processed.
- AddFileNameFormat: This string is inserted into the release script at the beginning of the partial script. the {FileName} variable is replaced with the
partial script name
- ItemSeparator: This string is inserted into the release script at the end of the partial script. the {FileName} variable is replaced with the partial script name
- AllowedExtensions: Defines extensions of files that can be dropped on the application.
- OutputFileName: If set to true, names of executing files are printed to output when the release script is run
Even though this application lacks bells and whistles, it saved us already a ton of time. I hope you will find it usefull too!
For your convenience, I attach two files: one is compressed solution for Visual Studio 2008, the other one, for these of you who are not interested in C# and compiling the code, msi installer that will install the application. You will find it in the Programs menu, in main folder.
ReleaseBuilderSolution.zip
ReleaseBuilderSetup.zip
Note: The utility is provided as is and you use it at your responsibility. You may copy and modify the application, but not sell it.
Let me know what you think of this tool.