防禦式 SQL 更新 - PowerShell 版

2020-03-04 08:43 PM

先前談過防禦式 SQL 更新 - 用更嚴謹的態度執行正式資料庫更新作業，滿足保留資料更新前後對照、過程與預期不同自動煞車、方便檢閱複核... 等要求。純 SQL 語法簡單明瞭，但施行有些前題：操作環境必須有安裝 GUI 或 Command Line 的 SQL Client 工具、操作人員必須具備 SQL 工具基本技巧。此外，若系統有自己的連線字串保存機制，帳號密碼僅限程式存取不該透露給操作人員，但缺少帳號密碼又無法操作 SQL Client 工具，就會形成矛盾。

我想到的解法是為該次資料更新作業撰寫一段專屬程式，如此可從自訂機制取連線字串不經過人手，程式能跑各式複雜 SQL 更新邏輯，察覺有異自動煞車，甚至留下是否要 Commit 的人工發射鈕(延伸閱讀：小心駛得萬年船 - SQL指令保險栓)，彈性遠勝純 SQL。對我來說，寫程式用 C# 寫是首選，但先前說過，PowerShell 免編譯免部署又以明碼示人便於人工審核，是能取代 C# 在艱苦環境野外求生的利器，所以就用 PowerShell 來寫吧!

以下範例使用 PowerShell 搭 Managed ODP.NET 更新一筆資料，如要更新 MSSQL 原理相似，亦可照方煎藥。

Add-Type -Path .\CnStrManager.dll #某種自訂連線字串保管機制
Add-Type -Path .\Oracle.ManagedDataAccess.dll
try {
    $cs = [TheNamespace.CnStrManager.Helper]::GetConnString("MYDB")
    $cn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($cs)
    $cn.Open()
    $trn = $cn.BeginTransaction()
    $cmd = $cn.CreateCommand()
    $cmd.Transaction = $trn
    $cmd.CommandText = "select n from t where i = 1"
    $dr = $cmd.ExecuteReader()
    if (!$dr.Read()) { throw "更新前查無資料" }
    $origChk = $dr.Item('n')
    Write-Host "更新前檢查 = $($origChk)" -ForegroundColor Yellow
    if ($origChk -ne "Jeffrey") {
        throw "執行前檢查不符: $($origChk) vs Jeffrey" 
    }

    #執行更新
    $cmd.CommandText = "update t set n = 'Updated' where i = 1";
    $rowCountAffected = $cmd.ExecuteNonQuery()

    #檢查筆數
    if ($rowCountAffected -ne 1) {
        throw "執行筆數不為 1 : $($rowCountAffected)"
    }

    #檢查更新後結果
    $cmd.CommandText = "select n from t where i = 1"
    $dr = $cmd.ExecuteReader()
    if (!$dr.Read()) { throw "更新後查無資料" }
    $updChk = $dr.Item('n')
    Write-Host "更新後檢查 = $($updChk)" -ForegroundColor Yellow
    if ($updChk -ne "Updated") {
        throw "執行後檢查不符: $($updChk) vs Updated"
    }
    Write-Host "檢查結果相符: $($updChk) vs Updated" -ForegroundColor Green
    $confirm = Read-Host "是否確認 Commit? Y/N"
    if ($confirm.ToUpper() -eq 'Y') {
        $trn.Commit()
        Write-Host "資料庫更新動作完成" -ForegroundColor Cyan
    }
    else {
        throw "操作人員放棄更新"
    }
}
catch {
    Write-Host '執行失敗' -ForegroundColor Red
    Write-Host $_ -ForegroundColor Red
    if ($trn) { 
        $trn.Rollback() 
    }  
}
finally {
    if ($cn) { $cn.Close() }
}

在程式裡我加了四道保險：

更新前檢查資料內容是否符合預期，並留下證據
更新時比對異動筆數是否符合預期
更新後檢查資料是否如預期改成新值，並留下證據
是否 Commit Transaction 由人工做最後判別

以上四者任一關卡未通過就 Transaction.Rollback 放棄更新。若執行成功，擷取畫面可當成呈堂證供。

實測範例如下，示範 1) 修改前內容不符 2) 找不到要更新資料 3) 操作人員放棄 4) 成功更新四種情境。

只需把握「時時檢核、保留證據、出錯還原」原則，程式可依需求自行發揮調整，滿足大部分需求。

同場加映：執行多個 SQL 指令的寫法。

方法一，以 begin ... end; 包含多組更新指令，透過 Output Parameter 取回影響筆數：

    #執行多行更新
    $cmd.CommandText = @"
begin    
    update t set n = 'Updated' where i = 1;
    :rowNum := sql%rowcount;
    update t set n = 'Updated' where i = 2;
    :rowNum := :rowNum + sql%rowcount;
end;
"@
    $pRowNum = $cmd.Parameters.Add("rowNum", [Oracle.ManagedDataAccess.Client.OracleDbType]::Int32, [System.Data.ParameterDirection]::Output)
    $cmd.ExecuteNonQuery() | Out-Null
    $rowCountAffected = $pRowNum.Value
    $cmd.Parameters.Clear()

缺點是每執行一個指令要取一次 sql%rowcount 累加統計，有點麻煩。

方法二，跑迴圈執行：

    #執行多行更新
    $cmdLines = @"
update t set n = 'Updated' where i = 1
update t set n = 'Updated' where i = 2
"@.Split("`r")
    foreach ($cmdLine in $cmdLines) {
        $cmd.CommandText = $cmdLine
        $rowCountAffected = $cmd.ExecuteNonQuery()
        #檢查筆數
        if ($rowCountAffected -ne 1) {
            throw "執行筆數不為 1 : $($rowCountAffected) @ $($cmdLine)"
        }
    }

邏輯單純好寫易讀，指令亦可考慮另存成獨立檔案，但迴圈數多時要留意效能。

防禦式 SQL 更新 - PowerShell 版

Comments

Post a comment