DB.Changelog: SQL トリガーと CodeMode = objectgenerator を使ってデータベースの変更を追跡する |

記事

Toshihiko Minamoto · 17 hr 前 9m read

お客様のプロジェクトにおいて、どのようにすればいつ何時に誰がデータベースを変更したかを追跡できるか問われました。 SQL とオブジェクトへの両方のアクセスで Insert、Update、および Delete を追跡することが目的です。

以下は、変更ログを維持するために作成したテーブルです。

/// Changelog, keep track of changes to any table
Class ChangeLog.DB.ChangeLog Extends (%Persistent, %JSON.Adaptor)
{

/// Action 
Property Action As %String(%JSONFIELDNAME = "action", DISPLAYLIST = ",Create,Update,Delete", MAXLEN = 1, VALUELIST = ",0,1,2");

/// Classname of the %Persistent class
Property ClassName As %String(%JSONFIELDNAME = "table", MAXLEN = "") [ SqlFieldName = TableName ];

/// ID of the record
Property DRecordId As %String(%JSONFIELDNAME = "id") [ SqlFieldName = RecordId ];

/// Name of the user that made the change
Property DUsername As %String(%JSONFIELDNAME = "user") [ SqlFieldName = Username ];

/// ISO 8601 formatted UTC timestamp e.g 2023-03-20T15:14:45.7384083Z
Property ETimestamp As %String(%JSONFIELDNAME = "timestamp", MAXLEN = 28) [ SqlFieldName = Timestamp ];

/// Changed Data (only there for Action < 2)
Property NewData As %String(%JSONFIELDNAME = "changed-data", MAXLEN = "");

/// Old Data (only there for Action > 0)
Property OldData As %String(%JSONFIELDNAME = "old-data", MAXLEN = "");

}

変更を追跡するためのテーブルは単純な Name-Value タイプでした。

Class ChangeLog.DB.NameValues Extends %Persistent
{

/// Name
Property name As %String;

Index nameIndex On name [ Unique ];

/// Value
Property value As %String(MAXLEN = "");

/// CreateOrUpdate
ClassMethod CreateOrUpdate(name As %String, value As %String = "") As %Status
{
    if ..nameIndexExists(name)
    {
        if (value = "")
        {
            return ..nameIndexDelete(name)
        }

        set record = ..nameIndexOpen(name)
    }
    else
    {
        set record = ..%New()
        set record.name = name
    }

    if (value = "") // Do not store!
    {
        return $$$OK        
    }

    set record.value = value

    return record.%Save()
}

}

まず、%OnAfterSave() メソッドを使用することを試してみました。これは非常に簡単でしたた、SQL を介して更新が起こったときに呼び出されませんでした。
そこで、代わりにトリガーメソッドを記述しなければならないことがわかりました。https://docs.intersystems.com/healthconnectlatest/csp/docbook/DocBook.UI... をご覧ください。

適用する特定の構文ルールに慣れてきたら、トリガーメソッドを書くのは比較的簡潔な作業であったため、NameValues クラスに以下のトリガーを追加しました。

/// Write the DB changelog
Trigger LogUpdate [ Event = INSERT/UPDATE, Foreach = row/object, Time = AFTER ]
{
    New changelog
    set changelog = ##class(ChangeLog.DB.ChangeLog).%New()
    set changelog.ClassName = $CLASSNAME()
    set changelog.DRecordId = {ID}
    set changelog.Action = (%oper = "UPDATE")
    set changelog.DUsername = $SYSTEM.Process.UserName()
    set changelog.ETimestamp = $ZDATETIME($ZTIMESTAMP, 3, 7, 7)
    if (%oper = "UPDATE") // also add old data
    {
        set changelog.OldData = { "name": ({name*O}), "value": ({value*O}) }.%ToJSON()
    }
    set changelog.NewData = { "name": ({name*N}), "value": ({value*N}) }.%ToJSON()
    do changelog.%Save()
}

/// Write delete to changelog
Trigger LogDelete [ Event = DELETE, Foreach = row/object ]
{
    New changelog
    set changelog = ##class(ChangeLog.DB.ChangeLog).%New()
    set changelog.ClassName = $CLASSNAME()
    set changelog.DRecordId = {ID}
    set changelog.Action = 2
    set changelog.DUsername = $SYSTEM.Process.UserName()
    set changelog.ETimestamp = $ZDATETIME($ZTIMESTAMP, 3, 7, 7)
    set changelog.OldData = { "name": ({name*O}), "value": ({value*O}) }.%ToJSON()
    do changelog.%Save()
}

上記のコードは Name-Values テーブルように特別に書かれたもので、ここでは
name プロパティの古い値に使用する {nameO} と、value プロパティの新しい値を表す {valueN} のように、古いプロパティ値と新しいプロパティ値にアクセスする特別な構文を使用しています。さらに、
value プロパティが特定の更新中に実際に変更したかを確認する {value*C} も追加することが可能でした。

特定のテーブルに対するトリガーを作成できたので、トリガーの構文が特定のプロパティ名をサポートしてもワイルドカードは使用しないことを考えると、同じように動作しても完全に汎用になるように変更するにはどうすればよいか考えました。

最近の記事（https://community.intersystems.com/post/how-add-webterminal-when-you-hav...）では [ CodeMode = objectgenerator ] を使用したため、ここでもそれを使用できないか考えました。

興味深いことに、https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls... のセクションから、オブジェクト生成中にプロパティをリスト表示する方法がすぐにわかりました。さらに、CodeMode = objectgenerator はトリガーでもサポートされているのです！

そういった背景において、トリガーを完全に汎用化し始め、以下のコードが出来上がりました。

/// %Persistent type with a changelog
Class ChangeLog.DB.PersistentWithChangeLog Extends %Persistent
{
/// Write the DB changelog
Trigger LogUpdate [ CodeMode = objectgenerator, Event = INSERT/UPDATE, Foreach = row/object, Time = AFTER ]
{
    do %code.WriteLine(" new changelog")
    do %code.WriteLine(" set changelog = ##class(ChangeLog.DB.ChangeLog).%New()")
    do %code.WriteLine(" set changelog.ClassName = ..%ClassName()")
    do %code.WriteLine(" set changelog.DRecordId = {ID}")
    do %code.WriteLine(" set changelog.Action = (%oper = ""UPDATE"")")
    do %code.WriteLine(" set changelog.DUsername = $UserName")
    do %code.WriteLine(" set changelog.ETimestamp = $ZDATETIME($ZTIMESTAMP, 3, 7, 7)")
    do %code.WriteLine(" if (%oper = ""UPDATE"") {")
    do %code.WriteLine("     new data")
    do %code.WriteLine("     set data = {}")

    for i = 1:1:%compiledclass.Properties.Count()
    {
        set prop = %compiledclass.Properties.GetAt(i)
        set propName = prop.Parameters.GetAt("%JSONFIELDNAME")

        if (propName = "")
        {
            set propName = prop.Name
        }

        if (prop.Name '[ "%") && 'prop.Transient && (prop.Type ? 1"%Library"0.E)
        {
            do %code.WriteLine("     do data.%Set(""" _ propName _ """, {"_ prop.Name _ "*O})")
        }
    }

    do %code.WriteLine("     set changelog.OldData = data.%ToJSON()")
    do %code.WriteLine(" }")
    do %code.WriteLine(" set data = {}")

    for i = 1:1:%compiledclass.Properties.Count()
    {
        set prop = %compiledclass.Properties.GetAt(i)
        set propName = prop.Parameters.GetAt("%JSONFIELDNAME")

        if (propName = "")
        {
            set propName = prop.Name
        }

        if (prop.Name '[ "%") && 'prop.Transient && (prop.Type ? 1"%Library"0.E)
        {
            do %code.WriteLine(" if {"_ prop.Name _ "*C} && '$ISOBJECT({"_ prop.Name _ "*N}) {")
            do %code.WriteLine("     do data.%Set(""" _ propName _ """, {"_ prop.Name _ "*N})")
            do %code.WriteLine(" }")
        }
    }
    do %code.WriteLine(" set changelog.NewData = data.%ToJSON()")
    do %code.WriteLine(" do changelog.%Save()")
    return $$$OK
}

/// Write delete to changelog
Trigger LogDelete [ CodeMode = objectgenerator, Event = DELETE, Foreach = row/object ]
{
    do %code.WriteLine(" new changelog")
    do %code.WriteLine(" set changelog = ##class(ChangeLog.DB.ChangeLog).%New()")
    do %code.WriteLine(" set changelog.ClassName = ..%ClassName()")
    do %code.WriteLine(" set changelog.DRecordId = {ID}")
    do %code.WriteLine(" set changelog.Action = 2")
    do %code.WriteLine(" set changelog.DUsername = $UserName")
    do %code.WriteLine(" set changelog.ETimestamp = $ZDATETIME($ZTIMESTAMP, 3, 7, 7)")
    do %code.WriteLine(" new data")
    do %code.WriteLine(" set data = {}")

    for i = 1:1:%compiledclass.Properties.Count()
    {
        set prop = %compiledclass.Properties.GetAt(i)
        set propName = prop.Parameters.GetAt("%JSONFIELDNAME")

        if (propName = "")
        {
            set propName = prop.Name
        }

        if (prop.Name '[ "%") && 'prop.Transient && (prop.Type ? 1"%Library"0.E)
        {
            do %code.WriteLine(" do data.%Set(""" _ propName _ """, {"_ prop.Name _ "*O})")
        }
    }

    do %code.WriteLine(" set changelog.OldData = data.%ToJSON()")
    do %code.WriteLine(" do changelog.%Save()")
    return $$$OK
}

もちろんこれを Name-Values クラスでテストしました。

/// Test table with name values
/// Each change must be recorded in the ChangeLog Table
Class ChangeLog.DB.NameValues Extends ChangeLog.DB.PersistentWithChangeLog
{

/// Name
Property name As %String;

Index nameIndex On name [ Unique ];

/// Value
Property value As %String(MAXLEN = "");

/// CreateOrUpdate
ClassMethod CreateOrUpdate(name As %String, value As %String = "") As %Status
{
    if ..nameIndexExists(name)
    {
        if (value = "")
        {
            return ..nameIndexDelete(name)
        }

        set record = ..nameIndexOpen(name)
    }
    else
    {
        set record = ..%New()
        set record.name = name
    }

    if (value = "") // Do not store!
    {
        return $$$OK        
    }

    set record.value = value

    return record.%Save()
}

}

上手くいきました！次の 3 つのコマンドを実行すると...
1. 以下を使用して、レコードを NameValues に挿入する

w ##class(ChangeLog.DB.NameValues).CreateOrUpdate("name", "value1")

以下を使用してそのインスタンスを更新する

w ##class(ChangeLog.DB.NameValues).CreateOrUpdate("name", "value2")

以下を使用してすべてのレコードを削除する

delete FROM ChangeLog_DB.NameValues

変更ログは以下のようになりました。

ID	Action	TableName	RecordId	Username	Timestamp	NewData	OldData
1	Create	NameValues	1	_SYSTEM	2023-11-27T12:52:05.8768627Z	{"name":"name","value":"value1"}
2	Update	NameValues	1	_SYSTEM	2023-11-27T12:52:09.7573293Z	{"value":"value2"}	{"name":"name","value":"value1"}
3	Delete	NameValues	1	_SYSTEM	2023-11-27T12:53:15.2558132Z		{"name":"name","value":"value2"}

次に、顧客プロジェクトに定義される %Persistent を拡張するすべての 12 個のクラスを、代わりに ChangeLog.DB.PersistentWithChangeLog を拡張するように変更しました。これにより、いくつかの変更が発生しました（上記のコードにすでに存在します）。
1. デフォルトでクラスの一部であるプロパティ%%OID と %Concurrency を除外することにしました。
2. 一時的なプロパティは、SQL プロパティとして存在しないため、除外する必要があります。
3. 古いデータと新しいデータを JSON としてログしているため、定義されるときにプロパティ名として "%JSONFIELDNAME" パラメーターを使用するのが合理的でした。

"UniqueIndex" という一意のインデックスを使うクラスが %Persistent を拡張する際にコンパイルされるが、ChangeLog.DB.PersistentWithChangeLog を拡張する際にはコンパイルされないという説明のつかない問題に 1 つ遭遇しました。この問題は、インデックス名を UniqueIndex2 に変更すると解消されました。

最後になりましたが、[ CodeMode = objectgenerator ] の能力には本当にワクワクさせられています。この記事があなたの役に立てられることを願っています！

@Theo Stolkerさんが書いた元の記事へ