主キーのないテーブルの比較(vbaで)

Question

SQLのDML文(INSERT、DELETE、 UPDATE)を検知するツールをVBAで現在作成しています。
検知の方法なのですが、最初に対象のテーブルに
SELECT * FROM テーブル名;
を投げその結果を、シートをに格納。
そして、もう一度
SELECT * FROM テーブル名;
を投げ上記とは別にシートに結果を格納。
二つのシートを、主キーを元に文字列比較し、
DML文の検知を行います。

ですが、上記の方法だと主キーのないテーブルには使えません。

そもそも主キーのないテーブルの行を一位に識別するためには
行の値すべて見る必要があるので行全体を比較すれば
テーブルの値の違いだけでも検知できるかもしれませんが、
あまりいいやり方ではない気がします。

VBAに限らず、主キーのないテーブル同士の比較で
似たような事例を知っている方がいれば、ご教授願いたいです。

Answer 1

元のテーブルと編集後のテーブルを比較して差分を取り、どのような操作が行われたかを検知したいということでしょうか。
(以下、イメージです。主キーが無いテーブルを想定しています。)

主キー無しテーブルの場合、UpdateはDelete+Insertでも等価な処理ができてしまうので、テーブルの比較だけでどちらが行われたかは厳密には判定できません。

従って、以下のように完全一致以外のものを全て出力させるのが現実的かと思います。

テーブル構成によっては、特定の複数列を主キーと見なしてしまう手もありますが、汎用性は無くなります。

行全体を比較すれば
テーブルの値の違いだけでも検知できるかもしれませんが、
あまりいいやり方ではない気がします。

たしかにスマートではないですが、主キーが無い以上、他に方法は無いですね。
セルを一つずつ取得して比較するのは速度面からあまりよくないですが、一旦範囲全体をVariant型変数に代入してしまえば2次元配列になるので、そこからは高速に処理できます。

上の表を前提にコードサンプルを書いてみました。
※Microsoft Scripting Runtimeへの参照設定が必要です。

Sub Sample()
    Dim レコード As String, 行 As Long, 列 As Long
    
    '高速化の為、元データを配列に転記
    Dim 元データ As Variant
    元データ = ThisWorkbook.Sheets(1).Range("B4:E8").Value
    
    '面倒な配列から、操作しやすいDictionary型に変換
    Dim 元データディクショナリ As New Dictionary
    For 行 = LBound(元データ, 1) To UBound(元データ, 1)
        レコード = ""
        For 列 = LBound(元データ, 2) To UBound(元データ, 2)
            レコード = レコード & CStr(元データ(行, 列))
        Next 列
        元データディクショナリ.Add 行, レコード
    Next 行
    
    '高速化の為、編集後データを配列に転記
    Dim 編集後 As Variant
    編集後 = ThisWorkbook.Sheets(1).Range("G4:J10").Value
    
    '面倒な配列から、操作しやすいDictionary型に変換
    Dim 編集後ディクショナリ As New Dictionary
    For 行 = LBound(編集後, 1) To UBound(編集後, 1)
        レコード = ""
        For 列 = LBound(編集後, 2) To UBound(編集後, 2)
            レコード = レコード & CStr(編集後(行, 列))
        Next 列
        編集後ディクショナリ.Add 行, レコード
    Next 行

    'ディクショナリ同士の比較でマッチしたものをRemove
    Dim k1, k2
    For Each k1 In 元データディクショナリ.Keys
        For Each k2 In 編集後ディクショナリ.Keys
            If 元データディクショナリ(k1) = 編集後ディクショナリ(k2) Then
                元データディクショナリ.Remove (k1)
                編集後ディクショナリ.Remove (k2)
            End If
        Next
    Next
    
    'アンマッチデータの出力
    Debug.Print "元データのアンマッチ"
    For Each k1 In 元データディクショナリ.Keys
        If 元データディクショナリ(k1) <> "" Then Debug.Print k1; "行目"
    Next
    Debug.Print "編集後のアンマッチ"
    For Each k2 In 編集後ディクショナリ.Keys
        If 編集後ディクショナリ(k2) <> "" Then Debug.Print k2; "行目"
    Next
End Sub

出力結果はこうなります。

元データのアンマッチ
 2 行目
 5 行目
編集後のアンマッチ
 1 行目
 3 行目
 4 行目
 5 行目

注意点として、レコードにデリミタを付けてないので、例えば1,12と11,2が文字列結合されるとどちらも112になってしまいます。そこらへんは、ご自身で工夫してください。

Answer 2

他の方が書かれていないようなので、一点だけ。

主キーがないテーブルに主キーを追加してはいかがですか、他で使わない項目追加は技術的には問題無いです。（”不要なデータを登録して、ディスクを圧迫するな”（実際の増加量が数Mｂとしても）など政治的・感情的に反対がおこる可能性はあります。）

オートナンバー型などで項目を追加すればいいです。または、毎秒ごとに行の挿入されるテーブルでなければ、デフォルト値が現在日時の”作成日時”という項目を追加してはいかがでしょう。ミリセカンドまで登録されていればバッティングの可能性はほぼ０です。

これらの方法だと、Insert文を変更する必要がないので、影響はほとんど無いと言っていいです。

Answer 3

DBMS次第ですが、「DMLを検知する」のが目的ならトレースログや監査ログなどを見るのが良いと思います。

Comments

[退会済みユーザー]

あまりトレースログ、監査ログの知識があるわけではないのですが、トレースログ等を取得するためにはDB上の設定を変える必要がありますよね？
今回作成するツールは顧客本番環境に対して使用する想定なのであまり環境はいじりたくない。またあまり知識のない方でも簡単に使用できるようにユーザーライクなものを作りたいと考えています。

Answer 4

一つの質問文の中にお聞きになりたいことが複数ある様ですね。
整理すると以下でしょうか？
1)なにか(SQLテキストファイル？)からDMLに該当するものを検知する方法
2)キーのないテーブル同士のマッチング方法

1については、
直接、DMLに当たるものを見つけ出すのではだめなのでしょう？
自分もVB.netソースからSQL(除くLINQ)を検出して
PrgID(ソースファイル名)毎×テーブル毎にCRUDを書き出すVBAを組んだりしましたが…

２については、
Dictionary Objectを拡張し、Itemに複数(配列やオブジェクト)を格納できるクラスを作成して、
・複合キー(複数の項目を連結したり）で比較
・項目ごとに重要度などで点数付けし、点数合計を比較に使用
などをしました。
テーブル全体の比較であれば、並び替えてからの比較も有効な気がします。

参考になりますでしょうか？