Commands/Add-EFPoshModelEntity.ps1
|
Function Add-EFPoshModelEntity { <# .SYNOPSIS Used to generate a DB schema - adds an entity to the model. Currently only works with MSSql .DESCRIPTION Used to generate a DB schema - adds an entity to the model .PARAMETER DBSchema Schema the entity is in - defaults to dbo .PARAMETER EntityType Is the entity a Table or View? Only used for narrowing auto-complete of Name. .PARAMETER Name Name of the View/Table .PARAMETER PropertyList List of properties you want modeled - defaults to all .PARAMETER FromSql An optional SQL query to use as a base for querying the entity .NOTES .Author: Ryan Ephgrave #> Param( [Parameter(Mandatory = $false)] [ArgumentCompleter({ Param($CommandName, $ParameterName, $WordToComplete, $CommandAst, $FakeBoundParameters) $SchemaList = ( Get-Module EFPosh ).Invoke( { $Script:ContextFileSettings.Schemas } ) foreach($instance in $SchemaList){ if($instance -like "$($WordToComplete)*"){ New-Object System.Management.Automation.CompletionResult ( $instance, $instance, 'ParameterValue', $instance ) } } })] [string]$DBSchema = 'dbo', [Parameter(Mandatory=$false)] [ValidateSet('Table', 'View', 'Function')] [string]$EntityType, [Parameter(Mandatory = $true)] [ArgumentCompleter({ Param($CommandName, $ParameterName, $WordToComplete, $CommandAst, $FakeBoundParameters) $TableHash = ( Get-Module EFPosh ).Invoke( { $Script:ContextFileSettings.Tables } ) $ViewHash = ( Get-Module EFPosh ).Invoke( { $Script:ContextFileSettings.Views } ) if($FakeBoundParams.Keys -Contains 'DBSchema'){ $Schema = $FakeBoundParameters.DBSchema } else{ $Schema = 'dbo' } if($FakeBoundParams.Keys -Contains 'EntityType'){ $ET = $FakeBoundParameters.EntityType } else{ $ET = $null } $TableList = $TableHash[$Schema] $ViewList = $ViewHash[$schema] if($null -eq $Schema) { $Schema = 'dbo' } if($ET -ne 'View'){ foreach($instance in $TableList){ if($instance -like "*$($WordToComplete)*"){ New-Object System.Management.Automation.CompletionResult ( $instance, $instance, 'ParameterValue', $instance ) } } } if($ET -ne 'Table'){ foreach($instance in $ViewList){ if($instance -like "*$($WordToComplete)*"){ New-Object System.Management.Automation.CompletionResult ( $instance, $instance, 'ParameterValue', $instance ) } } } })] [string]$Name, [Parameter(Mandatory = $false)] [string[]]$PropertyList, [Parameter(Mandatory=$false)] [string[]]$FromSql ) if($null -eq $Script:ContextFileSettings) { throw 'Must first run Start-EFPoshModel to set the DB and file information' return } $Context = $Script:ContextFileSettings.Context if($EntityType -eq 'Function'){ $SchemaInfo = Search-EFPosh -DbContext $Context -Entity 'MSSQLInformationSchemaColumns' -FromSql 'Select * FROM Information_Schema.Routine_Columns' -Expression { $_.TABLE_SCHEMA -eq $DBSchema -and $_.TABLE_NAME -eq $Name } } else{ $SchemaInfo = Search-EFPosh -DbContext $Context -Entity 'MSSQLInformationSchemaColumns' -Expression { $_.TABLE_SCHEMA -eq $DBSchema -and $_.TABLE_NAME -eq $Name } } if($null -eq $PropertyList) { $PropertyList = $SchemaInfo.COLUMN_NAME } $ClassBuilder = @("Class $($Name) {") foreach($prop in $SchemaInfo){ if($PropertyList -contains $prop.COLUMN_NAME){ $type = Get-EFPoshPowerShellType -ColumnType $prop.DATA_TYPE -Nullable:($prop.IS_NULLABLE -ne 'NO') $ClassBuilder += " $type `$$($prop.COLUMN_NAME -replace "\W" )" } } $ClassBuilder += "}" $ClassBuilder += "" $ClassBuilder += "##ClassDefinitions##" $ClassString = $ClassBuilder -join [System.Environment]::NewLine $strPKs = '-Keyless' $foundPrimaryKeys = Search-EFPosh -DbContext $Context -Entity 'MSSQLInformationSchemaPrimaryKeys' -Expression { $_.TABLESCHEMA -eq $DBSchema -and $_.TABLENAME -eq $Name } -FromSql " SELECT KU.table_name as TABLENAME ,column_name as PRIMARYKEYCOLUMN ,KU.TABLE_SCHEMA as TABLESCHEMA ,KU.ORDINAL_POSITION as POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME " if($foundPrimaryKeys.Count -gt 0){ $foundPrimaryKeys = $foundPrimaryKeys | Sort-Object -Property POSITION $strPKs = "-PrimaryKeys @('" foreach($p in $foundPrimaryKeys){ $strPKs += "$($p.PRIMARYKEYCOLUMN)','" } $strPks = $strPKs.TrimEnd(",'") + "')" } $TableArrayBuilder = @() $TableString = "`$Tables += New-EFPoshEntityDefinition -Type '$Name' -TableName '$Name' -Schema '$DBSchema' $strPks" if($FromSql){ $TableString += " -FromSql '$($FromSql)'" } $TableArrayBuilder += $TableString $TableArrayBuilder += "##TableArray##" $TableArrayString = $TableArrayBuilder -join [System.Environment]::NewLine $Content = Get-Content $Script:ContextFileSettings.FilePath -Raw $Content = $Content.Replace('##ClassDefinitions##', $ClassString).Replace("##TableArray##", $TableArrayString).Trim() $Content | Out-File $Script:ContextFileSettings.FilePath -Force -Encoding utf8 } |