Alex-Yates

# Function to create a string of SQL Compare switches from a SQL Server JDBC connection string
# $sourcetarget should be a 1 or 2 to determine whether the string should be the source or target db
function getSqlCompareArgs ($connectionsString, $sourcetarget){
    $sSwitch = "/s$sourcetarget" + ": "
    $dbSwitch = "/db$sourcetarget" + ": "
    $uSwitch = "/u$sourcetarget" + ": "
    $pSwitch = "/p$sourcetarget" + ": "
    $s = ""
    $db = ""
    $u = ""

Alex-Yates

/*
Run this script on an empty database called SimpleTalk or SimpleTalk_Dev etc

Script created by SQL Compare version 11.5.2 from Red Gate Software Ltd at 01/07/2016 15:50:08
*/

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

Alex-Yates

/*
Run this script on the standard SimpleTalk demo database to create a new NOT NULL column but grab the data from a different column

(SSDT can't do this in an automated way)
*/
PRINT N'Adding [Person].[Address].[CreatedDate] column (empty)'
GO
ALTER TABLE [dbo].[Contacts]
    ADD [CreatedDate] [datetime] NULL
GO

Alex-Yates

/*
Run this script on the standard SimpleTalk demo database to populate it with data
Script created by SQL Data Compare version 11.5.2 from Red Gate Software Ltd at 01/07/2016 15:58:36
*/
		
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD

Alex-Yates

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
  <metadata>
    <id>MySchema</id>
    <version>0.1</version>
    <authors>Alex Yates</authors>
    <owners>Alex Yates</owners>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <licenseUrl>https://creativecommons.org/licenses/by/4.0/</licenseUrl>
    <projectUrl>http://www.dlmconsultants.com/</projectUrl>

Alex-Yates

"C:\Program Files (x86)\NuGet\nuget.exe" pack "%WORKSPACE%\RelativePath\MySchema.nuspec" /version 0.%BUILD_NUMBER%

Alex-Yates

echo PIPELINE_NUMBER = %BUILD_NUMBER% > env:properties

Alex-Yates

Write-Output "***************************"
Write-Output "***** EXTRACT PACKAGE *****"
Write-Output "***************************"

$pipeline = ${env:PIPELINE_NUMBER}.trim()
$nuget = "C:\RelativePath\JenkinsBuildArtifacts\MySchema.0.${pipeline}.nupkg"
$destination = "$env:WORKSPACE\extractedNuGet"

Write-Output "Using the following variables:"
Write-Output "pipeline: $pipeline"

Alex-Yates

Copy-Item "$env:WORKSPACE\crm.0.$env:BUILD_NUMBER.nupkg" -Destination "C:\Relative\Path\JenkinsBuildArtifacts\MySchema.0.$env:BUILD_NUMBER.nupkg"

Alex-Yates

Write-Output "***************************"
Write-Output "***** DEPLOY DATABASE *****"
Write-Output "***************************"

# Configuration
$pipeline = ${env:PIPELINE_NUMBER}.trim()
$schemaComparePath = 'C:\\Program Files\\Red Gate\\Schema Compare for Oracle 3\\SCO.exe'
$targetSchemaName = "MySchema_INTEGRATION"
$schemaRelativePath = ""
$tns = "MyTNS"