Private/Powershell/PsDbModel.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
<############################################################################
 # Given a connection string to a SQL Server database,
 # check the metadata, and for each table in the database,
 # fill a custom TableInfo object with metadata about those tables and columns.
 #
 # This is not specific to C# or angular, it's just TypeScript objects
 # storing metadata about database tables.
 ############################################################################>

Function New-PsModel([DbInfo]$dbInfo)
{
    Write-Host "### Store metadata of database tables"
    $tableInfos = @{};

    $conn = Get-DbConnection $dbInfo.connStr
    $ds = Invoke-DbQuery $conn "select
            TABLE_NAME,
            iif(TABLE_TYPE = 'BASE TABLE', 0, 1) as IS_VIEW
        from
            INFORMATION_SCHEMA.TABLES
        where
            TABLE_TYPE in ('BASE TABLE', 'VIEW') "
 @{}

    $dt = $ds.Tables[0]

    foreach ($Row in $dt)
    { 
        [string]$tableName = $Row.TABLE_NAME
        [bool]$isView = $Row.IS_VIEW
        [TableInfo]$tableInfo = New-PsTable $dbInfo $tableName $isView
        $tableInfos.Add($tableName, $tableInfo)

    }
    Close-DbConnection $conn

    return $tableInfos
}

<############################################################################
 # Given a tablename and a connection string to a database,
 # make a POTO class where the POTO properties match metadata
 #
 # This is not specific to C# or angular, it's just TypeScript objects
 # storing metadata.
 ############################################################################>

Function New-PsTable([DbInfo]$dbInfo, [string]$tableName, [bool]$isView) 
{
    Write-Host "### Generate internal model for $($tableName)"
    
    $conn = Get-DbConnection $dbInfo.connStr
    # Get a list of all columns in this table
    $ds = Invoke-DbQuery $conn "select
                C.COLUMN_NAME,
                C.IS_NULLABLE,
                C.DATA_TYPE,
                CHARACTER_MAXIMUM_LENGTH,
                iif(
                    (
                        SELECT
                            ku.COLUMN_NAME
                        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
                        where
                            c.TABLE_CATALOG = tc.TABLE_CATALOG
                            AND c.TABLE_SCHEMA = tc.TABLE_SCHEMA
                            AND c.TABLE_NAME = tc.TABLE_NAME
                            AND c.COLUMN_NAME = ku.COLUMN_NAME
                    ) is null, 'NO', 'YES') as PRIMARY_KEY
            from
                INFORMATION_SCHEMA.COLUMNS as C
            where
                TABLE_NAME = @TableName"
 @{ TableName = $tableName }
    $dt = $ds.Tables[0]

    # Generate a TypeScript POTO file that represents this table

    [ColumnInfo[]] $columnInfos = @();
        
    # Loop through all fields
    foreach ($Row in $dt)
    { 
        [string]$fieldName = $row.COLUMN_NAME
        [string]$isNullable = $row.IS_NULLABLE
        [string]$dataType = $row.DATA_TYPE
        [int]$charMaxLen = $null;
        if($row.CHARACTER_MAXIMUM_LENGTH -match '^\d+$') {
            $charMaxLen = $row.CHARACTER_MAXIMUM_LENGTH
        }
        [string]$isPrimaryKey = $row.PRIMARY_KEY

        [ColumnInfo] $columnInfo = [ColumnInfo]::new($fieldName, $dataType, ($isNullable -eq "yes"), $charMaxLen, ($isPrimaryKey -eq "yes"))
        $columnInfos = $columnInfos += $columnInfo
    }
    Close-DbConnection $conn

    [TableInfo] $tableInfo = [TableInfo]::new($tableName, $columnInfos, $isView)
    return $tableInfo
}