Add-SQLTable.ps1
|
function Add-SqlTable { <# .Synopsis Adds a SQL Table .Description Creates a new Table in SQL .Example Add-SqlTable -TableName PatchyComputers -KeyType Sequential -Column MachineName, GroupName, PatchStatus, PatchWindowStart, PatchStartedAt, LastPatchedAt -DataType 'varchar(100)', 'varchar(100)', 'varchar(20)', 'datetime', 'datetime', 'datetime' -OutputSql -ConnectionStringOrSetting SqlAzureConnectionString .Link Select-SQL .Link Update-SQL #> [CmdletBinding(DefaultParameterSetName='SqlServer')] [OutputType([Nullable])] param( # The name of the SQL table [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)] [string]$TableName, # The columns to create within the table [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)] [string[]]$Column, # The keytype to use [ValidateSet('Guid', 'Hex', 'SmallHex', 'Sequential', 'Named', 'Parameter')] [string]$KeyType = 'Guid', # The name of the column to use as a key. [string] $RowKey = "RowKey", # The data types of each column [Parameter(ValueFromPipelineByPropertyName=$true)] [string[]]$DataType, # A connection string or a setting containing a connection string. [Alias('ConnectionString', 'ConnectionSetting')] [string]$ConnectionStringOrSetting, # If set, outputs the SQL, and doesn't execute it [Switch] $OutputSQL, # If set, will use SQL server compact edition [Parameter(Mandatory=$true,ParameterSetName='SqlCompact')] [Switch] $UseSQLCompact, # If set, will use MySql to connect to the database [Parameter(Mandatory=$true,ParameterSetName='MySql')] [Switch] $UseMySql, # The path to MySql's .NET connector. If not provided, MySql will be loaded from Program Files [Parameter(ParameterSetName='MySql')] [string] $MySqlPath, # The path to SQL Compact. If not provided, SQL compact will be loaded from the GAC [Parameter(ParameterSetName='SqlCompact')] [string] $SqlCompactPath, # If set, will use SQL lite [Parameter(Mandatory=$true,ParameterSetName='Sqlite')] [Alias('UseSqlLite')] [switch] $UseSQLite, # The path to SQL Lite. If not provided, SQL compact will be loaded from Program Files [Parameter(ParameterSetName='Sqlite')] [string] $SqlitePath, # The path to a SQL compact or SQL lite database [Parameter(Mandatory=$true,ParameterSetName='SqlCompact')] [Parameter(Mandatory=$true,ParameterSetName='Sqlite')] [Alias('DBPath')] [string] $DatabasePath, # Foreign keys in the table. [Parameter(ParameterSetName='SqlServer')] [Hashtable] $ForeignKey = @{}, # The size of a string key. By default, 100 characters [Uint32] $StringKeyLength = 100 ) begin { #region Resolve Connection String if ($PSBoundParameters.ConnectionStringOrSetting) { if ($ConnectionStringOrSetting -notlike "*;*") { $ConnectionString = Get-SecureSetting -Name $ConnectionStringOrSetting -ValueOnly } else { $ConnectionString = $ConnectionStringOrSetting } $script:CachedConnectionString = $ConnectionString } elseif ($psBoundParameters.Server -and $psBoundParameters.Database) { $ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" $script:CachedConnectionString = $ConnectionString } elseif ($script:CachedConnectionString){ $ConnectionString = $script:CachedConnectionString } else { $ConnectionString = "" } #endregion Resolve Connection String # Exit if we don't have a connection string, # and are not using SQLite or SQLCompact (which don't need one) if (-not $ConnectionString -and -not ($UseSQLite -or $UseSQLCompact)) { throw "No Connection String" return } #region If we're not just going to output SQL, we might as well connect if (-not $OutputSQL) { if ($UseSQLCompact) { # If we're using SQL compact, make sure it's loaded if (-not ('Data.SqlServerCE.SqlCeConnection' -as [type])) { if ($SqlCompactPath) { $resolvedCompactPath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($SqlCompactPath) $asm = [reflection.assembly]::LoadFrom($resolvedCompactPath) } else { $asm = [reflection.assembly]::LoadWithPartialName("System.Data.SqlServerCe") } } # Find the absolute path $resolvedDatabasePath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($DatabasePath) # Craft a connection string $sqlConnection = New-Object Data.SqlServerCE.SqlCeConnection "Data Source=$resolvedDatabasePath" # Open the DB $sqlConnection.Open() } elseif ($UseSqlite) { # If we're using SQLite, make sure it's loaded if (-not ('Data.Sqlite.SqliteConnection' -as [type])) { if ($sqlitePath) { $resolvedLitePath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($sqlitePath) $asm = [reflection.assembly]::LoadFrom($resolvedLitePath) } else { $asm = [Reflection.Assembly]::LoadFrom("$env:ProgramFiles\System.Data.SQLite\2010\bin\System.Data.SQLite.dll") } } # Find the absolute path $resolvedDatabasePath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($DatabasePath) # Craft a connection string $sqlConnection = New-Object Data.Sqlite.SqliteConnection "Data Source=$resolvedDatabasePath" # Open the DB $sqlConnection.Open() } elseif ($useMySql) { if (-not ('MySql.Data.MySqlClient.MySqlConnection' -as [type])) { if (-not $mySqlPath) { $programDir = if (${env:ProgramFiles(x86)}) { ${env:ProgramFiles(x86)} } else { ${env:ProgramFiles} } $mySqlPath = Get-ChildItem "$programDir\MySQL\Connector NET 6.7.4\Assemblies\"| Where-Object { $_.Name -like "*v*" } | Sort-Object { $_.Name.Replace("v", "") -as [Version] } -Descending | Select-object -First 1 | Get-ChildItem -filter "MySql.Data.dll" | Select-Object -ExpandProperty Fullname } $asm = [Reflection.Assembly]::LoadFrom($MySqlPath) $null = $asm } $sqlConnection = New-Object MySql.Data.MySqlClient.MySqlConnection "$ConnectionString" $sqlConnection.Open() } else { # We're using SQL server (or SQL Azure), just use the connection string we've got $sqlConnection = New-Object Data.SqlClient.SqlConnection "$connectionString" # Open the DB $sqlConnection.Open() } } #endregion If we're not just going to output SQL, we might as well connect #region Determine the "real" type of foreign keys $ForeignDataTypes = @{} if ($ForeignKey.Count) { foreach ($kv in $ForeignKey.GetEnumerator()) { $v = $kv.Value $chunks = @($v -split "[\(\)]") $foreignTable = $chunks[0] $foreignRef = $chunks[1] $foreignColumn = Select-SQL "select * from information_schema.columns where column_Name = '$ForeignRef' and table_name = '$foreignTable'" if ($foreignColumn.Data_Type -ne 'char') { $ForeignDataTypes[$kv.Key] = $foreignColumn.Data_Type } else { $ForeignDataTypes[$kv.Key] = "char($($foreignColumn.CHARACTER_MAXIMUM_LENGTH))" } $null = $null } } #endregion Determine the "real" type of foreign keys } process { $columnsAndTypes = New-Object Collections.ArrayList $rowKeySqlType = if ($KeyType -ne 'Sequential') { if ($useSqlLite) { "nchar($StringKeyLength)" } elseif ($useSqlCompact) { "nchar($StringKeyLength)" } else { "char($StringKeyLength)" } } else { if ($UseSQLite) { "integer" } else { "bigint" } } $autoIncrement = $(if ($KeyType -eq 'Sequential') { if ($UseSQLite) { "PRIMARY KEY" } else { "PRIMARY KEY IDENTITY" } } else { "" }) if ($UseMySql) { $null = $columnsAndTypes.Add("$RowKey $rowKeySqlType NOT NULL $(if ($KeyType -eq 'Sequential') { 'AUTO_INCREMENT' })") } else { $null = $columnsAndTypes.Add("$RowKey $rowKeySqlType NOT NULL $autoIncrement $(if (-not $autoIncrement) { "PRIMARY KEY"})") } $null = $columnsAndTypes.AddRange(@( for($i =0; $i -lt $Column.Count; $i++) { $columnDataType = if ($dataType -and $DataType[$i]) { $datatype[$i] } else { if ($UseSQLite) { "text" } elseif ($useSqlCompact) { "ntext" } elseif ($useMySql) { "longtext" } else { "varchar(max)" } } if ($UseMySql) { "$($Column[$i]) $columnDataType" } else { if ($ForeignKey[$column[$i]]) { "`"$($Column[$i])`" $($ForeignDataTypes[$Column[$i]]) FOREIGN KEY References $($ForeignKey[$Column[$i]])" $null = $null } else { "`"$($Column[$i])`" $columnDataType" } } })) if ($UseMySql) { $null = $columnsAndTypes.Add("PRIMARY KEY($RowKey)") } $createstatement = "CREATE TABLE $tableName ( $($ColumnsAndTypes -join (',' + [Environment]::NewLine + " ")) )" $sqlStatement = $createstatement if ($outputSql) { # If we're outputting SQL, just output it and be done $sqlStatement } elseif (-not $outputSql -and $psCmdlet.ShouldProcess($sqlStatement)) { # If we're not, be so nice as to use ShouldProcess first to confirm Write-Verbose "$sqlStatement" #region Execute SQL Statement if ($UseSQLCompact) { $sqlAdapter = New-Object "Data.SqlServerCE.SqlCeDataAdapter" $sqlStatement, $sqlConnection $dataSet = New-Object Data.DataSet $rowCount = $sqlAdapter.Fill($dataSet) } elseif ($UseSQLite) { $sqliteCmd = New-Object Data.Sqlite.SqliteCommand $sqlStatement, $sqlConnection $rowCount = $sqliteCmd.ExecuteNonQuery() } elseif ($usemySql) { $sqlAdapter= New-Object "MySql.Data.MySqlClient.MySqlDataAdapter" ($sqlStatement, $sqlConnection) $sqlAdapter.SelectCommand.CommandTimeout = 0 $dataSet = New-Object Data.DataSet $rowCount = $sqlAdapter.Fill($dataSet) } else { $sqlAdapter= New-Object "Data.SqlClient.SqlDataAdapter" ($sqlStatement, $sqlConnection) $sqlAdapter.SelectCommand.CommandTimeout = 0 $dataSet = New-Object Data.DataSet $rowCount = $sqlAdapter.Fill($dataSet) } #endregion Execute SQL Statement } } end { #region If a SQL connection exists, close it and Dispose of it if ($sqlConnection) { $sqlConnection.Close() $sqlConnection.Dispose() } #endregion If a SQL connection exists, close it and Dispose of it } } |