Directory Structure of Database Files.
This structure displays multiple versions of a Database .
Our senario is to handle the installation of multiple versions incrementally. Each version seeks to upgrade a database. if the DB does not exist then all the versions are executed.If the DB exists then incrementally the upgrade versions are installed. For instance , if version 1.5 was last installed and in course of time 1.6 to 1.10 versions have come up then the installer will install versions from 1.6 to 1.10 or what ever the user wants it to run. i.e user may not want to upgrade beyond 1.8 version,so the installer will run versions 1.6,1.7 and 1,8.The installer will drill into the versioning hierarchy and do the do the needful.
If the Database doesnot exist it creates one.
If the Database exists then upgrades it to a desired version without affecting the existing data.
This we shall be doing with PowerShell.i.e PowerShell script will be executing the relevant .cmd files in the cmd environment and then terminating it once the .cmd file has been executed.The .cmd files will in turn execute the .sql files.
The various informations can be dynamically fetched from an XML file. Here we shall hard code them in the script since the XML part has already been discussed in an earlier post.
$DBFilePath = "D:\MyDB";
$LastDeployVersion =1003;
$TargetDeployVersion = 1.0.05;
$Server = (local);
$User = sa;
$Password = xyz;
$DB = MyDB;
"INSTALLING DB by running sql script "
osql -S $Server -d Master -U $User -P $Password -b -i $DB
"Incrementally Installing the Upgrade Versions"
#Drills down the directory structure
foreach($item in get-childitem $DBFilePath -Recurse)
{
$version=-1
trap{Continue;}
$version =[System.Int32]$item.Name.Replace(".","")
if($version -gt -1 -and $version -gt $LastDeployVersion -and $version -le $TargetDeployVersion)
{
#Searches for the Relevant versions in the hierarchy
$NewPath = get-childitem $item.FullName -Recurse | where { $_.Extension -eq ".cmd"}
$SetPath = $NewPath.Directory.FullName;
$FileName = $NewPath.Name
cd $SetPath
#Executes the .cmd file in cmd environment
cmd.exe /C $FileName $Server $DB $User $Password
cd $SetPath.Substring(0,$SetPath.Indexof("\")+1)
$LastDeployVersion = $version
}
}
PowerShell(Monad) is a Command Line Scripting language that comes with a Object Oriented Framework,the power to leverage the .Net class library and can access the System`s informations easily.
The scope of this post is to throw some light on the basic PowerShell-XML interaction through sample code.The sample code provides an idea about the syntax and the way the job can be done.It intends to address a specific problem.
The Target Audience : those who are fimiliar with XML and have an exposure to PowerShell.
Here we have two XML configuration files.The job is to modify the settings of the Target xml file as per the settings in the Source XML file.Thus one needs to navigate through the XML documents. Identify the desired Elements. Fetch the elements from the Souce XML and modify the Target XML.
This is done with the help of PowerShell script.
$SourceFile = "OriginalSettings.xml"
$TargetFile = "NewSettings.xml"
#Loads the XML document
if( [System.IO.File]::Exists( $SourceFile) )
{
[System.Xml.XmlDocument]$SettingsDoc = new-object System.Xml.XmlDocument;
$SourceDoc.Load($SourceFile);
#Creates he Root Node
$Root =$SourceDoc.get_DocumentElement();
#Gets the list of Child Nodes
$NodeList = $Root.get_ChildNodes();
}
if( [System.IO.File]::Exists( $TargetFile ) )
{
[System.Xml.XmlDocument]$SettingsDoc = new-object System.Xml.XmlDocument;
$TargetDoc.Load($TargetFile);
$TargetRoot =$TargetDoc.get_DocumentElement();
}
#Navigates through the Node tree
foreach( $child in $NodeList)
{
foreach($item in $child.ConnectionStrings)
{
#Sets a New value to the Attribute of an Element
foreach( $element in $TargetRoot.connectionStrings.add )
{
if( $element.name -eq $item.getAttribute("Name"))
{
$element.connectionString = $item.getAttribute("connectionString");
}
}
#Saves the XML file
$TargetDoc.save($File);
"Config Settings of "+$item.getAttribute("Name")+" Updated ";
}
#Gets the contents of a specific Node
$newServices = $Root.Settings.Services.get_InnerXML();
"To Remove the existing node from the Target XML"
#Removes a Node
$TargetRoot.RemoveChild( $TargetRoot.Services)
"Removed Existing............."
#If the Node contains something in the Source XML then create it in the Target XML
if($newServices -ne $null)
{
#Creates a New Node
#Adds it to the Root Node
#Attaches the contents of the Node in the Source XML to the New Node in the Target XML
"Creating New..................."
$NewEle= $TargetDoc.CreateElement("Services")
$TargetRoot.AppendChild($NewEle)
$NewEle.set_InnerXML($newServices);
$TargetDoc.Save($File);
$NewEle.get_Name() +" Updated";
Alternatively a Node can be Imported from the Source XML ino the Target XML
$NewEle= $TargetDoc.ImportNode($child,1)
$TargetRoot..AppendChild($NewEle)
}
}
The code can be written in a Notepad and is saved as .ps1 file.
It can either be executed in a PowerShell window or the .ps1 file can be invoked from a .cmd file.
echo Hi
pause
powershell -nologo -command "& {D:\MyFile\UpdateSettings.PS1}"
pause
PowerShell can be downloaded from :
- http://www.computerperformance.co.uk/powershell/powershell_download.htm
To run a .ps1 file open the Windows PowserShell console from the Start->Programs and set the execution policy : set-executionpolicy remotesigned