# 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 }