#make sure no errors slip through
set-psdebug -strict; $ErrorActionPreference = "stop"

#--------------------These below need to be filled in! -----------------------------
#parameters you need to fill in
$pathToTest = "$env:USERPROFILE\Testrun"
$databasename = "AdventureWorks2012" #the database we want
$ServersToAvoid= @()
$ExcludeFiles = @() #any sql files you want to exclude
$ExcludeDirectories = @() #any subdirectories you want to exclude
#--------------------These above needed to be filled in! ---------------------------

if (-not $pathToTest.EndsWith('\')) { $pathToTest = $pathToTest.TrimEnd() + '\' }
#now we try to get the XMLDiff package
try { Add-Type -path "${env:ProgramFiles(x86)}\XmlDiffPatch\Bin\xmldiffpatch.dll" } #load xmldiffpatch to test results
catch #oops, he hasn't installed it yet
{
    write-warning @'
This routine currently compares results to make sure that the results
are what they should be. It uses XMLDiff, a NET tool. It can be downloaded
from here. 
It only does so if you leave a file with the CorrectResult suffix in
the filename. If you don't want this facility, remove it!  
'@;
    exit;
}
$xmlDiff = New-Object Microsoft.XmlDiffPatch.XmlDiff;
# Create the XmlDiff object
$xmlDiff = New-Object Microsoft.XmlDiffPatch.XmlDiff([Microsoft.XmlDiffPatch.XmlDiffOptions]::IgnoreChildOrder);
#poised to test results against what they should be
#here is the SQL batch for testing. It a real routine this would be pulled off disk
# but we need to keep this test simple

try { Import-Module sqlps -DisableNameChecking } #load the SQLPS functionality for getting the registered servers
catch #oops, he hasn't installed it yet
{
    write-warning @'
This routine uses SQLPS to access the local registered servers to use for these tests. Please install it!
'@;
    exit;
}

$Server = $null
#create the directory if it doesn't already exist.
if (-not (Test-Path $pathToTest)) #if the directory isn't there make it
{ $null = New-Item -ItemType Directory -Force -path $pathToTest }
#now fetch the list of all our registered servers
get-childitem 'SQLSERVER:\sqlregistration\' -recurse | where { $_.GetType() -notlike '*ServerGroup*' } |
Select servername, connectionstring | where-object  { $ServersToAvoid-NotContains $_.servername } |
sort-object -property servername -unique |
foreach-object {
    $Server = $_
    #Add the database name to the connection string that we got from the local registration
    $ConnectionStringBuilder = new-Object  System.Data.SqlClient.SqlConnectionStringBuilder($Server.connectionstring)
    $ConnectionStringBuilder['Database'] = $DatabaseName # there is a whole lot you can add
    $InstanceName = split-path $server.ServerName -leaf
    $message = [string]''; # for messages (e.g. print statements)
    $Name = '';#start with a blank name for the test
    $broken = $false; #we press on with the next database if we can't access this one
    $LastMessage = '';
    $SQLError = ''; #the current SQL Error if there was one
    $previousName = ''; #so we can invent one if necessary.
    $SavingResult = $true; #are we currently saving the response (Default to 'yes')
    $ThereWasASQLError = $false;
    try #to make the connection
    {
        $conn = new-Object System.Data.SqlClient.SqlConnection($ConnectionStringBuilder.ConnectionString)
        $conn.Open()
    }
    catch #can't make that connection
    {
        write-warning @" 
Sorry, but I can't reach $($ConnectionStringBuilder.database) on the server instance $instanceName. 
Maybe it is spelled wrong, credentials are wrong or the VPN link is broken.
I can't therefore run the test.
"@;
        $broken = $true;
    }
    if (-not $broken)
    {
        # This is the beating heart of the routine. It is called on receipt of every
        # message or error
        $conn.add_InfoMessage({#this is called on every print statement or message 
                param ($sender, #The source of the event
                    $event) #the errors, message and source
                if ($event.Errors.count -gt 0) #there may be an error
                {
                    $global:SQLError = "$($event.Errors)"; #remember the errors
                    $global:ThereWasASQLError = ($global:SQLError -cmatch '(?im)\.SqlError: *\w')
                    #you may think that if there is an error in the array... but no there are false alarms
                };
                $global:LastMessage = $event.Message; #save the message
                $global:message = "$($message)`n $($global:LastMessage)";#just add it
                switch -regex ($global:LastMessage) #check print statements for a switch
                {
                    '(?im)\((.{2,25})\)' #was it the name of the query?
                    {
                        $global:Name = $matches[1] -replace '[\n\\\/\:\.]', '-'; #get the name in the brackets
                        $null > "$pathToTest$($name).io"; #and clear out the io record for the query
                        break;
                    }
                    '(?im)-NoMoreResult' { $global:SavingResult = $false; break; } #prevent saving of result
                    '(?im)-SaveResult' { $global:SavingResult = $true; break; } #switch on saving of result
                    default
                    { #if we have some other message, then record the messge to a file
                        if ($name -ne '') { "$($event.Message)" >> "$pathToWriteTo$($name).io"; }
                    }
                }
            }
            ); #end of putting the listener into the event
            $conn.FireInfoMessageEventOnUserErrors = $true; #collect even the errors as messages.
            #now we do the server settings to get IO and CPU from the server. 
            # We do them as separate batches just to play nice
            @('Set statistics io on;Set statistics time on;', 'SET statistics XML ON;') |
            %{ $Result = (new-Object System.Data.SqlClient.SqlCommand($_, $conn)).ExecuteNonQuery(); }
            Foreach ($folder in (Get-ChildItem $pathToTest -Recurse -Filter '*.sql' | Where-Object { $_.Extension -eq '.sql' }))
            {
                if (($ExcludeFiles -notin $Folder.Name) -and ($excludeDirectories -notContains $folder.Directory.name))
                {
                    write-verbose "In folder '$($folder.FullName)'"
                    $PathToWriteTo = "$($folder.DirectoryName)\$($InstanceName -replace '[\n\\\/\:\.]', '-')\";
                    If (-not (Test-Path $PathToWriteTo))
                    {
                        $null = New-Item -ItemType Directory -Force -path $PathToWriteTo
                    }
                    
                    $Statements = [io.file]::ReadAllText($folder.FullName) -split '(?im)^\s*GO\s*$'
                    foreach ($statement in $statements)
                    {
                        #and we execute everything at once, recording how long it all took
                        try #executing the sql
                        {
                            $timeTaken = measure-command { #measure the end-to-end time
                                $rdr = (new-Object System.Data.SqlClient.SqlCommand($statement, $conn)).ExecuteReader();
                            }
                        }
                        catch
                        {
                            write-warning @" 
Sorry, but there was an error with executing the batch against  $databasename on the server instance $InstanceName. 
I can't therefore run the test.
"@;
                            return;
                        }
                        if ($ThereWasASQLError -eq $true)
                        {
                            write-warning @" 
Sorry, but there was an error '$SQLError' with executing the batch against  $databasename on the server instance $InstanceName. 
I can't therefore run the test.
"@;
                        }
                        
                        #now we save each query, along with the query plans
                        do #a loop
                        {
                            if ($name -eq $previousName) #if we have no name then generate one that's legal
                            {
                                $Name = ([System.IO.Path]::GetRandomFileName() -split '\.')[0]
                            }#why would we want the file-type?
                            #the first result will be the data so save it
                            $datatable = new-object System.Data.DataTable
                            $datatable.TableName = $name
                            $datatable.Load($rdr)#pop it in a datatable
                            if ($SavingResult) { $datatable.WriteXml("$pathToWriteTo$($name).xml"); }
                            #and write it out as XML so we can compare it easily
                            else #if we aren't saving the result delete any previous tests
                            {
                                If (Test-Path "$pathToWriteTo$($name).xml")
                                {
                                    Remove-Item "$pathToWriteTo$($name).xml"
                                }
                            }
                            $datatable.close; #and close the datatable
                            if ($rdr.GetName(0) -like '*showplan')#ah we have a showplan!!
                            {
                                while ($rdr.Read())#so read it all out quickly in one gulp
                                {
                                    [system.io.file]::WriteAllText("$pathToWriteTo$($name).sqlplan", $rdr.GetString(0));
                                }
                            }
                            $previousName = $name #and remember the name to avoid duplicates
                            #now we wonder if the DBA has left an XML file with the correct result?     
                            if (test-path "$($folder.FullName)\$($name)CorrectResult.xml")
                            { #there is a correct result to compare with!
                                $CorrectResult = [xml][IO.File]::ReadAllText("$($folder.FullName)\$($name)CorrectResult.xml")
                                $TestResult = [xml][IO.File]::ReadAllText("$PathToWriteTo$($name).xml")
                                if (-not $xmlDiff.Compare($CorrectResult, $TestResult))#if there were differences....
                                { #do the difference report
                                    $XmlWriter = New-Object System.XMl.XmlTextWriter("$pathToWriteTo$($name).differences", $Null)
                                    $xmlDiff.Compare($CorrectResult, $TestResult, $XmlWriter)
                                    $xmlWriter.Close();
                                    $message = "$message`nDifferences found to result of query '$name'"
                                }
                                else #remove any difference reports with the same name
                                {
                                    If (Test-Path "$pathToWriteTo$($name).differences")
                                    {
                                        Remove-Item "$pathToWriteTo$($name).differences"
                                    }
                                }
                                
                            }
                        }
                        while ($rdr.NextResult())# and get the next if there is one
                        $rdr.Close()
                        #now save all the messages for th batch including the errors.
                        $message > "$($pathToWriteTo)all.messages"
                        #and add the end-to-end timing.
                        "End-To-End time was $($timeTaken.Milliseconds) Ms" >> "$($pathToWriteTo)all.messages"
                    }
                }
            }
        }
    }