# encoding: ascii
# api: powershell
# title: ScriptSVN.ps1
# description: Script from SVN Utility
# version: 1.0
# type: script
# license: CC0
# function: split-directive
# x-poshcode-id: 927
# x-archived: 2009-03-15T01:17:17
#
# This script processes template files and produces and output file.
# Its a simple macro processor really.
# We use it to extract the latest version of our database procedurrads and functions
# from our source code control server to create deployment scripts.
# This will assume you have svn command line client in your path.
# This currently cannot authenticate with svn to retrieve data. So just simple svn: supported.
# This is written and tested with Microsoft Windows Powershell v1.0.
#
#
# Script from SVN Utility
# ~~~~~~~~~~~~~~~~~~~~~~~
# This script processes template files and produces and output file.
# Its a simple macro processor really.
# We use it to extract the latest version of our database procedurrads and functions
# from our source code control server to create deployment scripts.
#
# This will assume you have svn command line client in your path.
# This currently cannot authenticate with svn to retrieve data. So just simple svn: supported.
# This is written and tested with Microsoft Windows Powershell v1.0.
#
# When run it will look for all files with ".dro" extension in the current
# directory and process them. If the ".dro" file does not specify the
# OuputFile directive then the output file will have the same name as the
# input file with ".dro" replaced by ".out"
#
# GETTING STARTED
# ~~~~~~~~~~~~~~~
# Go to a directory containing a .dro file and execute "ScriptSVN.ps1" It will process all .dro files it finds.
# Look at $exampleInputFile below to see a sample input file content.
#
# This example will fail to get this example to work you will need
# 1. SVN Server called sccserver.
# 2. Files under the following path on SVN server
# svn://sccserver/trunk/database/maindb/Procedure/ProcedureA.sql
# svn://sccserver/trunk/database/maindb/Procedure/ProcedureB.sql
# svn://sccserver/trunk/database/maindb/Procedure/ProcedureD.sql
# 3. The ProcedureD file must have a revision 43 available.
#
# File Format SYNTAX
# ~~~~~~~~~~~~~~~~~~
# 1. '#' marks start of script comment
# Comments characters are converted to $comment on output to match output format.
# Currently understands Single Quote strings and will mostly not identify a # in a string as a comment.
# 2. '{' '}' mark start and end of a script directive.
# Only recognised if the '{' is first non white space on a line. [simplifies search match a lot]
# Only recognised if both start and end on same line.
# The result of the directive is output to the file.
# The output result is bracketed before and after by comments identifying the directive and parameters.
# White space preceding a directive is prepended to each line output from directive [maintains indenting].
# Extra data following a directive is completely ignored at the moment.
# 3. Other lines are output as they are
#
# A directive may have parameters.
# Directive parameters must use double quotes to quote things with spaces.
# For simplicity will always end up with a newline as last char in file.
# Simple understanding of single quoted strings wont find comment characters inside single quoted strings.
# Single quoted strings are supported because this was primarilly setup for SQL rollout scripts.
# Double quoted strings are not supported anywhere but inside directives.
#
# DIRECTIVES
# ~~~~~~~~~~
# OutputFile
# set the name of file to write output.
# No lines that produce output in file must precede the line its on [so make it first]
# Or only after SourceControl line.
# SourceControl
# Set the source control access string base path.
# Must have trailing slash [ no it does not check it has it ]
# eg. svn://scc/project/trunk/Database/DBName/ for DBName procedures
# Reference
# Extract a file from SourceControl with given path. eg. path "Procedures/spnEmailMessageInsert.sql"
# First parameter is path to file to fetch from source control.
# Second parameter is an optional revision to retrieve of that file
# Third paremter is an optional string to output to file as well [not sure how useful this is]
# Inside this third parameter the string is processed for some special characters
# "\n" represents a new line. useful for "GO\n".
# "\t" represents a tab.
# The conversion only occurs for output data not for the trace data output to file.
#
# Example. Build.cmd file that converts the .dro file(s) into our file
# "powershell C:\Scripts\rollscript.ps1"
#
# HISTORY
# 2008/12/11 rluiten Created.
# 2008/12/15 rluiten Setup a v3.2 folder to test and fixed a bug or three.
# Useful now maybe.
# 2008/12/17 rluiten Added explicit revision retrieval to Reference.
# Reference now looks up the revision to be able to output to file.
# 2009/02/23 rluiten Corrected end of line outputs to CRLF.
#
# SOME EXTRA NOTES
# ~~~~~~~~~~~~~~~~
# We export our procedures and functions as a unit that drop themseles if they exist
# create themselves and then set permissions. This is the functional block of code we manage
# and track in our source code control server. This allows us to export the code from SQL again
# with another small script and do a file comparison to check we havnt missed a change or
# something hasnt snuck out to production without being in source control.
#
# Dont put identation in front of a {Reference} directive to a procedure as it will add indenting
# to the start of every line and your exported code if you wish to diff your database back to
# your source code control server will cause every line to be different.
#
# This utility is general and could be used for other stuff than SQL.
# However the single quote processing of strings not inside directives may limit some scenarios.
# This is the first script I have written so probably lots of things I can do better.
# Happy to have feedback.
# Hope someone finds this useful saves us here a lot of time keeping our deployment scripts
# correct as we work.
#
$exampleInputFile = @"
{SourceControl "svn://sccserver/trunk/database/maindb/"}
{OutputFile "Test2.sql"}
#{hello world}
{Reference "Procedure/ProcedureA.sql" }
{Reference "Procedure/ProcedureB.sql" "" "print 'dont forget to do job 222'\n"}
#{Reference "Procedure/ProcedureC.sql" } -- commented out for now
{Reference "Procedure/ProcedureD.sql" "43"} -- extract specific revision file
select 'This is a test'
select 'What a nice day.'
#{Me to}
# not here
-- Zzzz ' this and # that ' # and here
-- Aaaa# ' foiousf ds' # and here
-- Bbbb # ' foiousf ds # and here
--select 'Yyyy'
"@
$commentStart = "#"
$comment = "--"
$version = "3"
# directives must be first non white space on line and not be empty.
# doesnt matter what follows a directive
# the content of the directive will always be group 3 of regex.
# group 4 is data following directive
$matchDirective=[Regex]"^(\s*)({([^{]+)})(.*)$"
# matching only single quote strings for now - and assume a string goes from first quote on line to last quote on line.
# groups 0 whole match, 1 - before string, 2 - string, 3 - after string
$matchSingleQuoteString=[Regex]@"
^([^']*)(['].*?['])([^']*)$
"@
# returns array.
# array 0 - prefix string to directive. [will always be whitespace]
# array 1 - directive text inside brackets
# array 2 - post fix data after directive
function split-directive([string]$str)
{
$myMatches = $matchDirective.match($str)
if ($myMatches.Success)
{
return $myMatches.Groups[1], $myMatches.Groups[3], $myMatches.Groups[4]
}
}
# return the index of the comment character on the line. -1 if no comment on line
function get-commentindex([string]$str)
{
$startCommentIndex = -1
$myMatches = $matchSingleQuoteString.match($str)
# check for comment char before and after string in group 1 and 3.
if ($myMatches.Success) # found a string in our line so look around it for comments
{
$commentIndex = ([string]$myMatches.Groups[1]).IndexOf($commentStart)
if ($commentIndex -gt -1)
{ # comment in group 1
$startCommentIndex = $myMatches.Groups[1].Index + $commentIndex
}
else
{
$commentIndex = ([string]$myMatches.Groups[3]).IndexOf($commentStart)
if ($commentIndex -gt -1)
{
$startCommentIndex = $myMatches.Groups[3].Index + $commentIndex
}
}
}
else # no string so just first index of comment
{
$startCommentIndex = $str.IndexOf($commentStart)
}
return $startCommentIndex
}
# returns array
# array 0 - non comment part of line
# array 1 - comment part of line including comment char
function split-comment([string]$str)
{
$startCommentIndex = get-commentindex $str
if ($startCommentIndex -gt -1)
{
return $str.Substring(0, $startCommentIndex), $str.Substring($startCommentIndex)
}
else
{
return $str
}
}
## got from http://poshcode.org/496 thank you Jaykul :)
################################################################################
## Convert-Delimiter - A function to convert between different delimiters.
## E.g.: commas to tabs, tabs to spaces, spaces to commas, etc.
################################################################################
## Written primarily as a way of enabling the use of Import-CSV when
## the source file was a columnar text file with data like services.txt:
## ip service port
## 13.13.13.1 http 8000
## 13.13.13.2 https 8001
## 13.13.13.1 irc 6665-6669
##
## Sample Use:
## Get-Content services.txt | Convert-Delimiter " +" "," | Set-Content services.csv
## would convert the file above into something that could passed to:
## Import-Csv services.csv
##
## Get-Content Delimited.csv | Convert-Delimiter "," "`t" | Set-Content Delimited.tab
## would convert a simple comma-separated-values file to a tab-delimited file
################################################################################
## Version History
## Version 1.0
## First working version
## Version 2.0
## Fixed the quoting so it adds quotes in case they're neeeded
## Version 2.1
## Remove quotes which aren't needed
## Version 2.2
## Trim spaces off the ends, they confuse things
## Version 2.3
## Allow empty columns: as in: there,are,six,"comma, delimited",,columns
## Version 2.3
## Replaced Trim() with regex to do similar job.
## if a parameter is "" <-- empty string this captures the "Quotes" as its value not empty string ???
Function Convert-Delimiter([regex]$from,[string]$to)
{
begin
{
$z = [char](222)
}
process
{
#$_ = $_.Trim() # converted Trim into regex replace as powershell 1 does not have it ?
$_ = $_ -replace "^\s+", "" -replace "\s+$", ""
$_ = $_ -replace "(?:`"((?:(?:[^`"]|`"`"))+)(?:`"$from|`"`$))|(?:$from)|(?:((?:.(?!$from))*.)(?:$from|`$))","$z`$1`$2$z$to"
$_ = $_ -replace "$z(?:$to|$z)?`$","$z"
$_ = $_ -replace "`"`"","`"" -replace "`"","`"`""
$_ = $_ -replace "$z((?:[^$z`"](?!$to))+)$z($to|`$)","`$1`$2"
$_ = $_ -replace "$z","`"" -replace "$z","`""
$_
}
}
# Use source code control client [svn] to retrieve referenced file.
Function get-reference([string]$scc, [string]$filePath, [string]$rev, [string]$post, [string]$whiteSpacePre)
{
$svn = 'svn.exe'
if ($rev -eq $null -or $rev -eq "")
{
# figure out the revision of head if we dont have it.
$result = @(& $svn info $scc$filePath) # ensure its an array even if only one
$revResult = @($result -like "Revision: *")
if ($revResult.Length -eq 1)
{
$tmp = $revResult[0]
$regexRevision = [regex]"^Revision: (\d+)$"
$myMatches = $regexRevision.match($tmp)
if ($myMatches.Success)
{
$rev = $myMatches.Groups[1]
}
}
else
{
write-error "Cannot find revision for $scc$filePath."
exit
}
}
# example of svn 'svn -r 1234 cat svn://scc/project/trunk/database/DBName/Procedure/procedure.sql'
write-host "Reference [$rev] $filePath"
#write-host "$svn -r $rev cat $scc$filePath"
$result = & $svn -r $rev cat $scc$filePath
# prefix each line
for ($i = 0; $i -lt $result.Length; $i++)
{
$result[$i] = $whiteSpacePre + $result[$i]
}
$fileContent = [string]::join("`r`n", $result)
append-outputString "$comment ** Start Reference [$scc] [$filePath] [$rev] [$post]`r`n";
append-outputString "$fileContent`r`n" # additional newline after data
if ($post -ne $null -and $post -ne "")
{
$convertedPost = $post -replace "\\n", "`r`n" -replace "\\t", "`t"
append-outputString $convertedPost
}
append-outputString "$comment ** End Reference [$scc] [$filePath] [$rev] [$post]`r`n";
}
# procedures to wrap up global string buffer for processed output.
function init-outputString()
{
$global:outputString = ""
$global:sourceControl = ""
$global:outputFile = ""
}
function append-outputString([string]$str)
{
$global:outputString += $str
}
function write-outputString([string]$file)
{
write-output $global:outputString | out-file -filePath $file -encoding oem
}
function execute-directive($splitDirective, [string]$whiteSpacePre)
{
$keyword = $splitDirective[0]
switch ($keyword)
{
"SourceControl"
{
write-host "$comment $keyword `"$($splitDirective[1])`""
append-outputString "$whiteSpacePre$comment $keyword `"$($splitDirective[1])`""
$global:sourceControl = $splitDirective[1]
}
"OutputFile"
{
append-outputString "$whiteSpacePre$comment $keyword `"$($splitDirective[1])`""
$global:outputFile = $splitDirective[1]
}
"Reference"
{
get-reference $global:sourceControl $splitDirective[1] $splitDirective[2] $splitDirective[3] $whiteSpacePre
}
default
{
write-error "Unknown directive `"$keyword`" exiting..."
exit
}
}
}
function process-directive([string]$directive, [string]$whiteSpacePre)
{
$reDelimit = $directive | Convert-Delimiter " " "!"
$splitDirective = $reDelimit.Split("!")
for ($i = 0; $i -lt $splitDirective.Length; $i++)
{
$tmp = $splitDirective[$i]
if ($tmp -eq "`"`"`"`"") # convert """" to empty string -- side effect of Convert-Delimiter
{
$splitDirective[$i] = ""; # empty string
}
}
execute-directive $splitDirective $whiteSpacePre
}
function process-file([string]$inputFile, [string]$outputFile)
{
init-outputString
$global:outputFile = $outputFile
if (!(test-path -pathType Leaf $inputFile))
{
write-error "Cannot open input file `"$inputFile`""
exit
}
$outmsg = "$comment ScriptSVN $version processing file `"$inputFile`""
write-host $outmsg
append-outputString "$outmsg`r`n"
$inputLines = @(get-content -path $inputFile) # read file @ to ensure we get an array
foreach ($line in $inputLines)
{
$activeStr, $commentStr = split-comment $line
$whiteSpacePre, $directive, $poststr = split-directive $activeStr
if ($directive -eq $null -or $directive.Length -eq 0)
{
append-outputString "$activeStr" # no directive to just output the line
}
else
{
process-directive $directive $whiteSpacePre
}
# output comment
if ($commentStr.Length -gt 0)
{
# convert input comment format to output format
$afterComment = $commentStr.Substring($commentStart.Length)
append-outputString "$comment$afterComment`r`n"
}
else
{
append-outputString "`r`n" # just new line
}
}
write-host "$comment OutputFile `"$($global:outputFile)`""
write-outputString $global:outputFile
}
# by default process all file ending in file extension in current directory
$files = @(get-childitem "*.dro")
if ($files.Length -eq 0)
{
write-host "No files found for processing."
write-host "This utility processes files ending in `".dro`" "
}
foreach ($f in $files)
{
$outFile = $f -replace ".dro",".out"
process-file $f $outFile
}