プリザンターに設定した日本語の表示名でデータベースのビューを作成したい

2021年4月21日2024年12月4日

回答¶

SitesテーブルのSiteSettings列の内容からテーブル名および表示名を抽出し、ビューを作成します。

概要¶

プリザンターに保存された「サイト設定」の情報を元に日本語の項目名でSQL Serverのビューを作成するスクリプトについて説明します。プリザンター上でテーブル名や表示名を変更した後に再実行すると、新しい設定でビューを再作成します。

注意事項¶

データベースの操作を行いますので事前にデータベースのバックアップを取得してください。

制限事項¶

本サンプルコードはSQL Serverの環境でのみ動作します。
項目名が重複している場合にはビューの作成が失敗します。
テーブルのタイトルが重複している場合にはビューを削除して再作成します。

操作方法¶

サーバにリモートデスクトップ等で接続してください。
下記のサンプルコードを「PleasanterViewCreator.ps1」等の拡張子ps1で作成し保存てください。
PowerShellでスクリプトを実行してください。

サンプルコード¶

PowerShell¶

[object]$ConnectionString = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder
[object]$ConnectionString['Data Source'] = "(local)"
[object]$ConnectionString['Initial Catalog'] = "Implem.Pleasanter"
[object]$ConnectionString['UID'] = "sa"
[object]$ConnectionString['PWD'] = "SetSaPWD"
[object]$ColumnNames = @{
    "IssueId" = "ID"
    "ResultId" = "ID"
    "Ver" = "バージョン"
    "Title" = "タイトル"
    "Body" = "内容"
    "StartTime" = "開始"
    "CompletionTime" = "完了"
    "WorkValue" = "作業量"
    "ProgressRate" = "進捗率"
    "Status" = "状況"
    "Manager" = "管理者"
    "Owner" = "担当者"
    "Comments" = "コメント"
    "CreatedTime" = "作成日時"
    "UpdatedTime" = "更新日時"
    "Creator" = "作成者"
    "Updator" = "更新者"
}
[object]$ExcludeNames = @(
    "RemainingWorkValue"
)

function Get-Site()
{
    [string]$SQLQuery = "select * from [Sites] where [ReferenceType] in ('Issues', 'Results');"
    [object]$ResultsDataTable = New-Object System.Data.DataTable
    [object]$SqlConnection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
    [object]$SqlCommand = New-Object System.Data.SQLClient.SQLCommand($SQLQuery, $SqlConnection)
    [object]$SqlConnection.Open()
    [object]$ResultsDataTable.Load($SqlCommand.ExecuteReader())
    [object]$SqlConnection.Close()
    return $ResultsDataTable
}

function Mainte-View($Row)
{
    [object]$Ss = ConvertFrom-Json ([string]$row.SiteSettings)
    [object]$SqlColumns = New-Object System.Collections.Generic.List[string]
    [object]$EditorColumns = $ss.EditorColumns
    if ($EditorColumns.Count -eq 0)
    {
        $EditorColumns = New-Object System.Collections.ArrayList
        $EditorColumnHash = (ConvertConvertFrom-JsonPSCustomObjectToHash $ss.EditorColumnHash)
        foreach ($Key in $EditorColumnHash.Keys)
        {
            foreach ($Value in $EditorColumnHash[$Key])
            {
                [int]$Ret = $EditorColumns.Add($Value)
            }
        }
    }
    foreach ($ColumnName in $EditorColumns)
    {
        [string]$Alias = ""
        foreach ($Column in $Ss.Columns)
        {
            if ($Column.ColumnName -eq $ColumnName)
            {
                $Alias = $Column.LabelText
            }
        }
        if ($Alias -eq "")
        {
            $Alias = $ColumnNames[$ColumnName]
        }
        if ($Alias -eq "")
        {
            $Alias = $ColumnName
        }
        if ($ExcludeNames -notcontains $ColumnName)
        {
            $SqlColumns.Add("[$ColumnName] as [$Alias]")
        }
    }
    [string]$JoinedSqlColumns = $SqlColumns -join ","
    Delete-View $Row
    Create-View $Row $JoinedSqlColumns
}

function Create-View($Row, $JoinedSqlColumns)
{
    "ビュー作成：" + $Row.Title
    [string]$SQLQuery = @"
        create view [$([string]$Row.Title)]
        as
            select $JoinedSqlColumns
            from [$([string]$Row.ReferenceType)]
            where [SiteId]=$([long]$row.SiteId);
"@
    [object]$SqlConnection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
    [object]$SqlCommand = New-Object System.Data.SQLClient.SQLCommand($SQLQuery, $SqlConnection)
    [object]$SqlConnection.Open()
    [int]$Ret = $SqlCommand.ExecuteNonQuery()
    [object]$SqlConnection.Close()
}

function Delete-View($Row)
{
    [string]$SQLQuery = @"
        if exists (select 1 from sysobjects where id = object_id('$([string]$row.Title)'))
        drop view [$([string]$row.Title)];
"@
    [object]$SqlConnection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
    [object]$SqlCommand = New-Object System.Data.SQLClient.SQLCommand($SQLQuery, $SqlConnection)
    [object]$SqlConnection.Open()
    [int]$Ret = $SqlCommand.ExecuteNonQuery()
    [object]$SqlConnection.Close()
}

Function ConvertConvertFrom-JsonPSCustomObjectToHash($obj)
{
    $hash = @{}
    $obj | Get-Member -MemberType Properties | SELECT -exp "Name" | % {
        $hash[$_] = ($obj | SELECT -exp $_)
    }
    $hash
}

[object]$Table = Get-Site
foreach ($Row in $Table)
{
    Mainte-View $Row
}