timabell · July 23, 2012 15:40
diff --git a/README.md b/README.md
diff --git a/app_offline.htm b/app_offline.htm
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
 <html xmlns="http://www.w3.org/1999/xhtml" >
 <head>
    <title>Application Offline</title>
    <style  type="text/css">

    div {
        background-color:#ffffcc;
        padding-top:10px;
        padding-bottom:10px;
        padding-left:10px;
        padding-right:10px;    
        border-style:solid;
        border-color:Black;
        border-width:1px;
    }

    </style>
 </head>
 <body>
    <div>
        This application is currently offline.  Site update in progress.
    </div>
 </body>
 </html>
diff --git a/config-transform.xml b/config-transform.xml
 <!-- http://stackoverflow.com/questions/2905151/msbuild-script-and-vs2010-publish-apply-web-config-transform -->
 <!-- this is an msbuild configuration for applying web config transforms -->
 <Project ToolsVersion="4.0"
         DefaultTargets="Transform"
         xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

    <UsingTask TaskName="TransformXml"
               AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\Web\Microsoft.Web.Publishing.Tasks.dll"/>

    <PropertyGroup>
        <ProjectPath>..\Shell.DocumentGenerator.Mvc</ProjectPath>
        <TransformInputFile>$(ProjectPath)\Web.config</TransformInputFile>
        <TransformFile>$(ProjectPath)\Web.$(Configuration).config</TransformFile>
        <TransformOutputFile>$(DeployPath)\Web.config</TransformOutputFile>
        <StackTraceEnabled>False</StackTraceEnabled>
    </PropertyGroup>

    <Target Name="Transform">
        <TransformXml Source="$(TransformInputFile)"
                      Transform="$(TransformFile)"
                      Destination="$(TransformOutputFile)"
                      StackTrace="$(StackTraceEnabled)" />
    </Target>
 </Project>
diff --git a/create-snapshot.sql b/create-snapshot.sql
 /* script for taking a snapshot of a test db for testing scripts */
 -- This is manually run for setting a point for the reset scripts to go back to before re-applying all outstanding changes. Replace ##database## with the name of the database you are creating

 -- ref http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

 -- create snapshot of CI db for testing scripts
 create database ##database##_snapshot
 	on (name = 'DevDb', -- name of source file
 		filename = 'C:\sqlserver_databases\snapshots\##database##_snapshot.ss1')
 	as snapshot of ##database##

 -- Tim Abell
 -- 3 May 2012
diff --git a/global-config.sql b/global-config.sql
 -- Set up config stored in database for local testing.

 /*
 Note that $(mailbox) is a variable set for sqlcmd.
 ref: http://msdn.microsoft.com/en-us/library/ms188714.aspx
 As the exchange server is part of our domain the logged in user will
 have permissions to send as the matching email address.
 */

 update globalConfig
 	set value = '$(mailbox)'
 	where name like '%EMAIL_ADDRESS';

 update globalConfig 
 	set value = REPLACE(value, 'http://www.example.com', '$(siteUrl)')
 	where value like 'http://www.example.com%';
diff --git a/rebuild-QA.bat b/rebuild-QA.bat
 @echo off
 Setlocal EnableDelayedExpansion

 REM set variables for building QA
 set server=staging
 set database=QA
 if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!; Manual run. Changeset Cxxxx
 set publishPath=\\staging\web\QA
 set [email protected]
 set siteUrl=http://staging/qa
 set resetType=snapshot
 set config=Debug (QA)

 call rebuild.bat
diff --git a/rebuild.bat b/rebuild.bat
 @echo off
 REM =============================================================================================
 REM                           !! DESTRUCTIVE !!
 REM  Resets a test database, runs upgrades, adds test data, adds test files.

 REM Must be run in visual studio command prompt to allow build.
 REM Requires SqlHawk, msbuild, and msdeploy (part of webdeploy) to be on the path.
 REM Environment variables can be overridden by caller / calling scripts.

 REM It is recommended that you set a buffer of 3000 lines on your command window to avoid losing output.
 REM =============================================================================================

 REM allow use of !var! everywhere, the normal !var! version doesn't work with if / for etc.
 REM and avoid messing with variables in the command window scope:
 Setlocal EnableDelayedExpansion

 set localPublishPath=..\WebProject

 REM set variable defaults if not already set by caller
 if "!server!"=="" set server=localhost
 if "!database!"=="" set database=DevDb
 if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!; Manual run
 if "!publishPath!"=="" set publishPath=!localPublishPath!
 if "!mailbox!"=="" set [email protected]
 if "!siteUrl!"=="" set siteUrl=http://localhost:7031
 if "!resetType!"=="" set resetType=backup
 if "!config!"=="" set config=Debug

 echo   Server      : !server!
 echo   Database    : !database!
 echo   Batch       : !batch!
 echo   Publish path: !publishPath!
 echo   Mailbox     : !mailbox!
 echo   Site url    : !siteUrl!
 echo   Config      : !config!

 echo.
 echo !date! !time! Starting rebuild...

 if not "!publishPath!"=="!localPublishPath!" (
 	echo.
 	@echo Taking app offline...
 	copy app_offline.htm "!publishPath!"
 	if !errorlevel! neq 0 exit /b !errorlevel!
 )

 if not "!skipDb!"=="" (
 	echo.
 	@echo Skipping db rebuild.
 ) else (
 	echo.
 	@echo Reverting to clean backup of live db...
 	@echo Do not interrupt this step as a broken connection can wedge the database. Ref http://stackoverflow.com/questions/520967
 	sqlcmd -b -S !server! -E -d master -i reset-to-!resetType!.sql -v database=!database!
 	if !errorlevel! neq 0 exit /b !errorlevel!
 )

 REM this is the bit that will be done on the final deployment to live:
 echo.
 @echo Running upgrade...
 call upgrade.bat
 if !errorlevel! neq 0 exit /b !errorlevel!


 if "!skipDb!"=="" (
 	echo.
 	@echo Applying test data...
 	pushd .
 	cd test-data\sql\
 	call test-data.bat
 	if !errorlevel! neq 0 exit /b !errorlevel!
 	popd

 	echo.
 	@echo Configuring global config...
 	sqlcmd -b -S !server! -E -d !database! -i global-config.sql -v mailbox="!mailbox!" siteUrl="!siteUrl!"
 )

 echo.
 @echo Copying test files into publish directory...
 xcopy /S /Y test-data\upload !publishPath!\data\upload\
 if !errorlevel! neq 0 exit /b !errorlevel!

 if not "!publishPath!"=="!localPublishPath!" (
 	echo.
 	@echo Putting app back online...
 	del !publishPath!\app_offline.htm
 )

 echo.
 @echo !date! !time! Done.
diff --git a/reset-to-backup.sql b/reset-to-backup.sql
 --       !! DESTRUCTIVE !!
 -- Restore a local database from backup.
 -- (sqlexpress doesn't support snapshots)
 -- To be used in preparation for re-testing scripted changes with sqlhawk
 -- This script is not intended to be run manually. See ../README.txt for the (re)build process.

 -- kill active connections
 -- ref http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn
 ALTER DATABASE [DevDb]
 SET OFFLINE WITH ROLLBACK IMMEDIATE
 ALTER DATABASE [DevDb]
 SET ONLINE

 RESTORE DATABASE [DevDb] 
 FROM  DISK = 
 N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\DevDb.bak' 
 WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
 GO
 use [DevDb]
 GO
 -- fix login mapping after databse restore
 alter user WebsiteUser with login = WebsiteUser, default_schema = dbo

 -- Tim Abell
 -- 23 May 2012
diff --git a/reset-to-snapshot.sql b/reset-to-snapshot.sql
 --       !! DESTRUCTIVE !!
 -- Restore a local database back to the last snapshot.
 -- To be used in preparation for re-testing scripted changes with sqlhawk
 -- This script is not intended to be run manually. See ../README.txt for the (re)build process.

 -- ref http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

 /*
 Note that $(database) is a variable set for sqlcmd.
 ref: http://msdn.microsoft.com/en-us/library/ms188714.aspx
 */

 -- kill active connections
 -- ref http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn
 ALTER DATABASE $(database)
 SET OFFLINE WITH ROLLBACK IMMEDIATE
 ALTER DATABASE $(database)
 SET ONLINE

 -- reset the snapshot
 restore database $(database)
 	from database_snapshot = '$(database)_snapshot';


 -- fix login mapping after databse restore
 use $(database)
 alter user WebsiteUser with login = WebsiteUser, default_schema = dbo
diff --git a/test-data.bat b/test-data.bat
 @echo off
 REM this is a generic script for running all the files matching pattern test-data_*.sql in a folder
 rem set DelayedExpansion to be able to catch errorlevel inside a loop
 setlocal EnableDelayedExpansion
 for /R %%I IN (test-data_*.sql) DO (
 	@echo %%I
 	sqlcmd -b -S !server! -E -d !database! -i "%%I"
 	if !errorlevel! neq 0 exit /b !errorlevel!
 )
diff --git a/upgrade-live.bat b/upgrade-live.bat
 @echo off
 Setlocal EnableDelayedExpansion

 REM TODO: Fill this in with the correct values for live

 set server=liveSql
 set database=liveDb
 if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!;
 set publishPath=\\web01\web\live
 set config=Release (Production)

 echo   Server      : !server!
 echo   Database    : !database!
 echo   Batch       : !batch!
 echo   Publish path: !publishPath!
 echo   Config      : !config!
 echo.

 REM saftey buffer, remove this if we move to a proper build server based deployment
 @echo Are you sure you want to upgrade the live db?
 @echo * Have you made a backup first?
 @echo * Have you filled in the parameters of this script?
 @echo.
 @echo Press Ctrl+C to cancel, any other key to continue.
 pause >nul

 @echo Taking app offline
 copy app_offline.htm !publishPath!

 echo.
 @echo Running upgrade...
 call upgrade.bat
 if !errorlevel! neq 0 exit /b !errorlevel!

 @echo Putting app back online...
 del !publishPath!\app_offline.htm

 @echo Done.
diff --git a/upgrade.bat b/upgrade.bat
 @echo off
 Setlocal EnableDelayedExpansion
 REM This script takes the live database or a clone of it, and upgrades it to the latest version based on the stored procedures and scripts in source control.

 REM The current live database (and therefore the backups and snapshots) have never had SqlHawk run, thus the tracking table needs to be created. Once live has the tracking table the initialize-tracking step must be discarded.

 echo.
 echo !date! !time! Starting upgrade...

 if "!skipDb!"=="" (
 	echo.
 	@echo Initializing upgrade log table...
 	java -jar "C:\Program Files\SqlHawk\sqlhawk.jar"                                 ^
 		--initialize-tracking -t mssql05-jtds -h !server! --sso -d !database! -s dbo ^
 		--driver-path "C:\Program Files\jtds-1.2.5\jtds-1.2.5.jar"
 	if !errorlevel! neq 0 exit /b !errorlevel!


 	echo.
 	@echo Upgrading database...
 	java -jar "C:\Program Files\SqlHawk\sqlhawk.jar"                             ^
 		--scm-input --target-path ..\database\                                   ^
 		--database-output -t mssql05-jtds -h !server! --sso -d !database! -s dbo ^
 		--driver-path "C:\Program Files\jtds-1.2.5\jtds-1.2.5.jar"               ^
 		--upgrade-batch "!batch!"
 	if !errorlevel! neq 0 exit /b !errorlevel!

 	REM "sleep" for windows. hack. http://stackoverflow.com/questions/7958265/sleep-command-in-batch-file
 	REM this is because on a local SQLExpress the next sql command fails with spurious login failure if it is run too soon.
 	@echo Waiting for database to settle...
 	ping -n 10 127.0.0.1 > NUL
 	if !errorlevel! neq 0 exit /b !errorlevel!
 )

 REM Only publish if not working locally.
 if not "!publishPath!"=="!localPublishPath!" (
 	REM move to sln directory ready for build and publish
 	pushd .
 	cd ..\
 	
 	set packageTemp=obj\PackageTemp

 	echo.
 	@echo Building and packaging the code...
 	REM delete old copy
 	rd /S /Q "WebProject\!packageTemp!"
 	msbuild /p:DeployOnBuild=true;DeployTarget=PipelinePreDeployCopyAllFilesToOneFolder;PackageTempRootDir="!packageTemp!";AutoParameterizationWebConfigConnectionStrings=false;Configuration="!config!"
 	if !errorlevel! neq 0 exit /b !errorlevel!

 	echo.
 	@echo Publishing code ...
 	REM Cleanup
 	REM Delete outdated folders
 	for /d %%a in ("!publishPath!\*") do (
 		rd /S /Q %%~Fa
 		if !errorlevel! neq 0 exit /b !errorlevel!
 	)
 	REM delete outdated files except the offline file (ignore case on comparison as windows corrects the path)
 	for %%a in ("!publishPath!\*") do (
 		if /i not "%%~fa" == "!publishPath!\app_offline.htm" (
 			del /Q "%%~fa"
 			if !errorlevel! neq 0 exit /b !errorlevel!
 		)
 	)
 	REM deploy updated copy
 	xcopy /S /Y "WebProject\!packageTemp!\PackageTmp\*" "!publishPath!"
 	if !errorlevel! neq 0 exit /b !errorlevel!

 	REM delete temp files
 	rd /S /Q "WebProject\!packageTemp!"

 	popd

 	echo.
 	@echo Publishing Web.config for configuration...
 	msbuild config-transform.xml /p:Configuration="!config!";DeployPath="!publishPath!"
 	if !errorlevel! neq 0 exit /b !errorlevel!
 )

 echo.
 echo !date! !time! Upgrade complete.
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml" >
	<head>
	<title>Application Offline</title>
	<style type="text/css">

	div {
	background-color:#ffffcc;
	padding-top:10px;
	padding-bottom:10px;
	padding-left:10px;
	padding-right:10px;
	border-style:solid;
	border-color:Black;
	border-width:1px;
	}

	</style>
	</head>
	<body>
	<div>
	This application is currently offline. Site update in progress.
	</div>
	</body>
	</html>
	<!-- http://stackoverflow.com/questions/2905151/msbuild-script-and-vs2010-publish-apply-web-config-transform -->
	<!-- this is an msbuild configuration for applying web config transforms -->
	<Project ToolsVersion="4.0"
	DefaultTargets="Transform"
	xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

	<UsingTask TaskName="TransformXml"
	AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\Web\Microsoft.Web.Publishing.Tasks.dll"/>

	<PropertyGroup>
	<ProjectPath>..\Shell.DocumentGenerator.Mvc</ProjectPath>
	<TransformInputFile>$(ProjectPath)\Web.config</TransformInputFile>
	<TransformFile>$(ProjectPath)\Web.$(Configuration).config</TransformFile>
	<TransformOutputFile>$(DeployPath)\Web.config</TransformOutputFile>
	<StackTraceEnabled>False</StackTraceEnabled>
	</PropertyGroup>

	<Target Name="Transform">
	<TransformXml Source="$(TransformInputFile)"
	Transform="$(TransformFile)"
	Destination="$(TransformOutputFile)"
	StackTrace="$(StackTraceEnabled)" />
	</Target>
	</Project>
	/* script for taking a snapshot of a test db for testing scripts */
	-- This is manually run for setting a point for the reset scripts to go back to before re-applying all outstanding changes. Replace ##database## with the name of the database you are creating

	-- ref http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

	-- create snapshot of CI db for testing scripts
	create database ##database##_snapshot
	on (name = 'DevDb', -- name of source file
	filename = 'C:\sqlserver_databases\snapshots\##database##_snapshot.ss1')
	as snapshot of ##database##

	-- Tim Abell
	-- 3 May 2012
	-- Set up config stored in database for local testing.

	/*
	Note that $(mailbox) is a variable set for sqlcmd.
	ref: http://msdn.microsoft.com/en-us/library/ms188714.aspx
	As the exchange server is part of our domain the logged in user will
	have permissions to send as the matching email address.
	*/

	update globalConfig
	set value = '$(mailbox)'
	where name like '%EMAIL_ADDRESS';

	update globalConfig
	set value = REPLACE(value, 'http://www.example.com', '$(siteUrl)')
	where value like 'http://www.example.com%';
	@echo off
	Setlocal EnableDelayedExpansion

	REM set variables for building QA
	set server=staging
	set database=QA
	if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!; Manual run. Changeset Cxxxx
	set publishPath=\\staging\web\QA
	set [email protected]
	set siteUrl=http://staging/qa
	set resetType=snapshot
	set config=Debug (QA)

	call rebuild.bat
	@echo off
	REM =============================================================================================
	REM !! DESTRUCTIVE !!
	REM Resets a test database, runs upgrades, adds test data, adds test files.

	REM Must be run in visual studio command prompt to allow build.
	REM Requires SqlHawk, msbuild, and msdeploy (part of webdeploy) to be on the path.
	REM Environment variables can be overridden by caller / calling scripts.

	REM It is recommended that you set a buffer of 3000 lines on your command window to avoid losing output.
	REM =============================================================================================

	REM allow use of !var! everywhere, the normal !var! version doesn't work with if / for etc.
	REM and avoid messing with variables in the command window scope:
	Setlocal EnableDelayedExpansion

	set localPublishPath=..\WebProject

	REM set variable defaults if not already set by caller
	if "!server!"=="" set server=localhost
	if "!database!"=="" set database=DevDb
	if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!; Manual run
	if "!publishPath!"=="" set publishPath=!localPublishPath!
	if "!mailbox!"=="" set [email protected]
	if "!siteUrl!"=="" set siteUrl=http://localhost:7031
	if "!resetType!"=="" set resetType=backup
	if "!config!"=="" set config=Debug

	echo Server : !server!
	echo Database : !database!
	echo Batch : !batch!
	echo Publish path: !publishPath!
	echo Mailbox : !mailbox!
	echo Site url : !siteUrl!
	echo Config : !config!

	echo.
	echo !date! !time! Starting rebuild...

	if not "!publishPath!"=="!localPublishPath!" (
	echo.
	@echo Taking app offline...
	copy app_offline.htm "!publishPath!"
	if !errorlevel! neq 0 exit /b !errorlevel!
	)

	if not "!skipDb!"=="" (
	echo.
	@echo Skipping db rebuild.
	) else (
	echo.
	@echo Reverting to clean backup of live db...
	@echo Do not interrupt this step as a broken connection can wedge the database. Ref http://stackoverflow.com/questions/520967
	sqlcmd -b -S !server! -E -d master -i reset-to-!resetType!.sql -v database=!database!
	if !errorlevel! neq 0 exit /b !errorlevel!
	)

	REM this is the bit that will be done on the final deployment to live:
	echo.
	@echo Running upgrade...
	call upgrade.bat
	if !errorlevel! neq 0 exit /b !errorlevel!


	if "!skipDb!"=="" (
	echo.
	@echo Applying test data...
	pushd .
	cd test-data\sql\
	call test-data.bat
	if !errorlevel! neq 0 exit /b !errorlevel!
	popd

	echo.
	@echo Configuring global config...
	sqlcmd -b -S !server! -E -d !database! -i global-config.sql -v mailbox="!mailbox!" siteUrl="!siteUrl!"
	)

	echo.
	@echo Copying test files into publish directory...
	xcopy /S /Y test-data\upload !publishPath!\data\upload\
	if !errorlevel! neq 0 exit /b !errorlevel!

	if not "!publishPath!"=="!localPublishPath!" (
	echo.
	@echo Putting app back online...
	del !publishPath!\app_offline.htm
	)

	echo.
	@echo !date! !time! Done.
	-- !! DESTRUCTIVE !!
	-- Restore a local database from backup.
	-- (sqlexpress doesn't support snapshots)
	-- To be used in preparation for re-testing scripted changes with sqlhawk
	-- This script is not intended to be run manually. See ../README.txt for the (re)build process.

	-- kill active connections
	-- ref http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn
	ALTER DATABASE [DevDb]
	SET OFFLINE WITH ROLLBACK IMMEDIATE
	ALTER DATABASE [DevDb]
	SET ONLINE

	RESTORE DATABASE [DevDb]
	FROM DISK =
	N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\DevDb.bak'
	WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
	GO
	use [DevDb]
	GO
	-- fix login mapping after databse restore
	alter user WebsiteUser with login = WebsiteUser, default_schema = dbo

	-- Tim Abell
	-- 23 May 2012
	-- !! DESTRUCTIVE !!
	-- Restore a local database back to the last snapshot.
	-- To be used in preparation for re-testing scripted changes with sqlhawk
	-- This script is not intended to be run manually. See ../README.txt for the (re)build process.

	-- ref http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

	/*
	Note that $(database) is a variable set for sqlcmd.
	ref: http://msdn.microsoft.com/en-us/library/ms188714.aspx
	*/

	-- kill active connections
	-- ref http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn
	ALTER DATABASE $(database)
	SET OFFLINE WITH ROLLBACK IMMEDIATE
	ALTER DATABASE $(database)
	SET ONLINE

	-- reset the snapshot
	restore database $(database)
	from database_snapshot = '$(database)_snapshot';


	-- fix login mapping after databse restore
	use $(database)
	alter user WebsiteUser with login = WebsiteUser, default_schema = dbo
	@echo off
	REM this is a generic script for running all the files matching pattern test-data_*.sql in a folder
	rem set DelayedExpansion to be able to catch errorlevel inside a loop
	setlocal EnableDelayedExpansion
	for /R %%I IN (test-data_*.sql) DO (
	@echo %%I
	sqlcmd -b -S !server! -E -d !database! -i "%%I"
	if !errorlevel! neq 0 exit /b !errorlevel!
	)
	@echo off
	Setlocal EnableDelayedExpansion

	REM TODO: Fill this in with the correct values for live

	set server=liveSql
	set database=liveDb
	if "!batch!"=="" set batch=!USERNAME! on !COMPUTERNAME!;
	set publishPath=\\web01\web\live
	set config=Release (Production)

	echo Server : !server!
	echo Database : !database!
	echo Batch : !batch!
	echo Publish path: !publishPath!
	echo Config : !config!
	echo.

	REM saftey buffer, remove this if we move to a proper build server based deployment
	@echo Are you sure you want to upgrade the live db?
	@echo * Have you made a backup first?
	@echo * Have you filled in the parameters of this script?
	@echo.
	@echo Press Ctrl+C to cancel, any other key to continue.
	pause >nul

	@echo Taking app offline
	copy app_offline.htm !publishPath!

	echo.
	@echo Running upgrade...
	call upgrade.bat
	if !errorlevel! neq 0 exit /b !errorlevel!

	@echo Putting app back online...
	del !publishPath!\app_offline.htm

	@echo Done.