SQL Changeset setup

Comments

• Options

  bhopenw Posts: 39

  June 26, 2009 10:00AM

  Okay,
  
  It looks this is possible and it shouldn't be all that difficult using powershell.
  
  We use SQL Changeset (SQLCS) to connect to TFS 2005 sp1
  
  When SQL Changeset first starts up it adds some elements to the users local Application Data folder (C:\Documents and Settings\bhopenw\Local Settings\Application Data\Red Gate\SQL Changeset). In this directory, there is a xml file named configuration.xml, this is the file use by SQLCS to registery the TFS/SCCI workspaces.
  
  With that fact found it is truly a simple powershell script to get this working. below is the workflow, I have not finished up the script yet but when I do I will post it.
  

  * Ensure SQLCS has been started at least once, need to have local app Data section set, if not start it. * Ensure SQLCS is not running, if it is prompt to stop or kill it * Create workspace with mappings set from parameters/config file * Add <PairOfScProviderOpenProjectInfo> node to configuration.xml * start SQLCS

  
  Where I see this working is we are going to have a common SCP and this script will set up the local environment (workspace) for the developers.
  
  Thanks,
  
  BJHop

  0 · Share on Twitter
• Options

  bhopenw Posts: 39

  June 27, 2009 10:10AM

  As I said
  here is the powershell code I created to get the job done
  It will update sql changeset with a new workspace to poll
  
  I believe it will help us enfore local standards to help work with a local scp file
  
  This script got a bit more complex than I first thought but I think it works well
  
  enjoy
  Please let me know if you have any comments or questions
  

  #Set-SQLChangeset
  #this script set up SQL Changeset for use with a SCP and local workspace
  param([string] $serverPath = $(throw 'The TFVC server path to DB schema is required'),
        [string] $localPath = $(throw 'The local path to workspace is required'),
  	  [string] $tfsUrl = $('http://as73tfs01:8080'))
  
  
  ###MAIN###
  $ErrorActionPreference = "Stop"
  
  write-debug "Starting"
  
  #using 
  [void] [Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.Client")
  [void] [Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.VersionControl.Client")
  
  #set variables 
  $tfs = New-Object Microsoft.TeamFoundation.Client.TeamFoundationServer($tfsUrl)
  $vcs = $tfs.GetService([Microsoft.TeamFoundation.VersionControl.Client.VersionControlServer])
  $wsCurrent = [Microsoft.TeamFoundation.VersionControl.Client.Workstation]::Current
  
  #may push to this to config file 
  [string] $wsName = "SQLChangeset_" + $(Split-Path $localPath -leaf)
  [string] $path2ConfigXml = "C:\Documents and Settings\$($env:USERNAME)\Local Settings\Application Data\Red Gate\SQL Changeset\Configuration.xml"
  [string] $path2sqlCs = "C:\Program Files\Red Gate\SQL Compare 8\SQL Changeset\RedGate.SqlChangeset.exe"
  [string] $user = $Env:USERDOMAIN + "\" + $Env:USERNAME
  
  if (-not (Test-Path $path2ConfigXml))
  {
  	#SQL Changeset has not been started for first time
  	
  	Write-warning "Starting SQL Changeset for first time" 
  	Write-Warning "Please close once started"
  	
  	& $path2sqlCs
  	
  }
  
  #we need to be sure that SQL CS is not running 
  #if it is we need to find out what to do 
  
  while ($(Get-Process | ?{$_.Name -eq 'RedGate.SqlChangeset'})) 
  {
  	Write-Host "SQL Changeset cannot be running while this process is completed"
  	
  	Write-Host "To exit right-click on the SQL ChangeSet icon in the notification"
  	
  	Write-Host "area  in the right hand side of the status bar where the clock usually is"
  		
  	switch -regex (Read-Host -Prompt "[R] Retry, [K] Kill the process, [A] Abort")
  	{
  		"R|r"{}
  		"A|a"
  		{
  			write-host -ForegroundColor Red "Abort: Cannot continue with Red Gate SQL Changeset running"
  			return
  		}
  		"K|k"
  		{
  			Write-Warning "Killing the process may have unkown effects on SQLChangeset"
  			switch -regex (Read-Host -Prompt "Are you sure you want to kill the SQL Changeset process [Y/N]")
  			{
  				"Y|y" 
  				{
  					$sqlcs = $(Get-Process | ?{$_.Name -eq 'RedGate.SqlChangeset'})
  			
  					$sqlcs.Kill()
  			
  					sleep -Seconds 5
  				}
  				
  				"N|n"
  				{}
  			}
  			
  		}
  	}
  }
  
  
  
  if (-not (Test-Path $localPath))
  {
  	New-Item -Path $(Split-Path $localPath -Parent) -Name $(Split-Path $localPath -leaf) -ItemType Directory | Out-Null
  }
  else
  {
  
  	if ($wsCurrent.IsMapped($localPath))
  	{
  		Write-Host -ForegroundColor Red "Error: Local path already mapped; $localPath is already mapped on this workstation" 
  		
  		return
  		
  	}
  	
  	
  }
  
  #check to ensure a workspace with same name not already created 
  if($wsCurrent.GetAllLocalWorkspaceInfo() | ?{$_.Name -match $wsName})
  {
  	Write-Host -ForegroundColor Red "Error: workspace name - $wsName - already in use"
  	Write-Host "Workspace name is taken for the local path directory name"
  	return
  }
  
  ##assume directy name is a good name ie DB name
  $ws = $vcs.createworkspace($wsName)
  
  $ws.map($serverPath, $localPath)
  
  Write-Debug "Workspace created"
  
  [System.IO.FileInfo] $configXml = Get-Item $path2ConfigXml
  
  Copy-Item -Path $configXml.FullName -Destination $($configXml.FullName + ".bak") -Force
  
  [string] $configXmlContents = gc $configXml
  #check to make sure the server path has not already been used 
  Write-Debug "configuration xml backed up"
  
  #search 
  if ($configXmlContents -match "\$serverPath")
  {
  	Write-Host -ForegroundColor Red "Error: $serverPath already in use in SQL Changeset"
  	Write-Host "Workspace will be deleted"
  	$ws.delete()
  	return
  }
  
  [xml] $xml = [xml] (gc $configXml)
  
  $projects = $xml.ConfigurationFile.SelectSingleNode("Projects")
  
  #need to crate xml elements 
  [System.Xml.XmlElement] $pair = $xml.CreateElement("PairOfScProviderOpenProjectInfo")
  [System.Xml.XmlElement] $scp = $xml.CreateElement("ScProvider")
  [System.Xml.XmlElement] $scpC = $xml.CreateElement("ScProvider")
  [System.Xml.XmlElement] $opi = $xml.CreateElement("OpenProjectInfo")
  [System.Xml.XmlElement] $opiC = $xml.CreateElement("OpenProjectInfo")
  
  $scpC.SetAttribute("Name", "Team Foundation Server MSSCCI Provider")
  $scpC.SetAttribute("Registry", "SOFTWARE\Microsoft\Team Foundation Server MSSCCI Provider")
  $scpC.SetAttribute("Dll", "C:\Program Files\Microsoft Team Foundation Server MSSCCI Provider\TfsMsscciProvider.dll")
  
  $scp.AppendChild($scpC)
  
  Write-Debug "Updating configuration xml"
  
  #This is the path SQL CS uses 
  #I think the info it needs to get the workspace object 
  [string] $wsPath = "$tfsUrl/|$wsName|$user"
  
  $opiC.SetAttribute("LocalPath", $localPath)
  $opiC.SetAttribute("Name", $serverPath)
  $opiC.SetAttribute("Path", $wsPath)
  $opiC.SetAttribute("User", $user)
  
  $opi.AppendChild($opiC)
  
  $pair.AppendChild($scp)
  $pair.AppendChild($opi)
  
  $projects.AppendChild($pair)
  
  $xml.Save($configXml.FullName)
  
  Write-Host "SQL Changeset has been successfully updated"
  Write-Host "Starting SQL Changeset"
  & $path2sqlCs
  
  

  0 · Share on Twitter