プリザンターでWikiをリンク項目マスタにしたときに項目をSQLで取得する方法

はじめに

プリザンターのリンク項目、皆さん使っていますか？実はWikiをリンク項目のマスタにすることできます。
ただ、Wikiにリンク項目を定義してしまうと、データをSQLで直接抽出したい時にちょっと不便・・・。ということで、SQLで直接扱えるようにバラすクエリを書いてみました。

クエリ

当社はSQL Serverでプリザンターを使うことが多いので、SQL Serverでの使用を前提にクエリを書いてみました。
フィールド名については、プリザンター本体側のコードを参考に決定しています。

WITH [Masters] AS ( 
    SELECT
        [Row].[RowOrdinal], 
        ordinal AS [ColOrdinal], 
        VALUE   AS [ColValue] 
    FROM
        ( 
            SELECT
                VALUE   AS [RowValue], 
                ordinal AS [RowOrdinal] 
            FROM
                [dbo].[Wikis] 
                CROSS APPLY STRING_SPLIT([Body], CHAR (10), 1) 
            WHERE
                [SiteId] = @SiteId
        ) AS [Row] 
        CROSS APPLY STRING_SPLIT([RowValue], ',', 1)
) 
SELECT
    MAX( 
        CASE [Masters].[ColOrdinal] 
            WHEN 1 THEN [Masters].[ColValue] 
            END
    )          AS [Value], 
    MAX( 
        CASE [Masters].[ColOrdinal] 
            WHEN 2 THEN [Masters].[ColValue] 
            END
    )          AS [Text], 
    MAX( 
        CASE [Masters].[ColOrdinal] 
            WHEN 3 THEN [Masters].[ColValue] 
            END
    )          AS [TextMini], 
    MAX( 
        CASE [Masters].[ColOrdinal] 
            WHEN 4 THEN [Masters].[ColValue] 
            END
    )          AS [CssClass],
	MAX( 
        CASE [Masters].[ColOrdinal] 
            WHEN 5 THEN [Masters].[ColValue] 
            END
    )          AS [Style] 
FROM
    [Masters] 
GROUP BY
    [Masters].[RowOrdinal]

実際に使うときは@SiteIdを変数定義するか、直接サイトIDに書き換えて使ってください。
PostgreSQLの場合はREGEXP_SPLIT_TO_TABLEを、MySQLの場合はSUBSTRING_INDEXを使えば同じようなことができます。

動かしてみた

こんな内容のWikiがあるとします。

100,保留,保留,status-new
130,伝票作成,伝票作成,status-preparation
140,伝票確認,伝票確認,status-preparation
150,発注依頼,発注依頼,status-preparation
900,部材発注済,部材発注済,status-rejected

これを上記のクエリで取得するとこんな内容で取得出来ます。

まとめ

Wikiをクエリを使ってバラすことで、SQLで直接扱えるようになりました。これで記録テーブルや期限付きテーブルを使わずに、Wikiを使ってシンプルなSQLで結合可能なリンク項目マスタを作ることが可能になりました。
Wikiをマスタ項目につかうと、キーにReferenceId以外のものが使えたり、項目が少ない場合は表示などのパフォーマンスがいいなどのメリットがあります。データの二次活用を考えたとき、マスタにWikiを使うのは使い勝手が悪いというデメリットがありますが、こうしてクエリを1本作ることで、そのデメリットを解消することが出来ます。是非活用してみてください。