現在システム間連携を行うシステム（GWシステム）の開発に携わっており、受信電文および送信電文をログファイルに出力するようにしています。電文ログは、利用実績のレポートや障害発生時の解析などで利用します。

アプリケーションとしては・・・ロガー経由(具体的にはSLF4JのAPI経由)でログファイルにTSV形式で出力しておいて、PostgreSQLのCOPYコマンドを使って定期的またはオンデマンドでDBへ反映するような仕組みにしようかと思っています（＝リアルタイム性は求められていないので）。

ログファイル

電文はバイナリ(intなど)を含むものがあるのでバイト配列をHEX文字列化し、出力項目はタブで区切る形式（TSV形式）にしようかと思っています。実際のログファイルとは異なりますが、以下のような感じです。

2019-10-05 23:00:00.001 XML  UTF-8 3C6D65737361676520747970653D22393939223E0A20203C646174613E666F6F3C2F646174613E0A3C2F6D6573736167653E
2019-10-05 23:00:00.002 JSON UTF-8 7B0A2020227479706522203A2022393939222C0A2020226461746122203A2022666F6F220A7D
2019-10-05 23:00:00.003 TEXT SJIS  30303130393939666F6F
2019-10-05 23:00:00.004 BIN  SJIS  0000000903E7666F6F

NOTE:

HEX化が必須なのはバイナリデータ(例、intなど)が含まれる場合のみななのですが、XMLやJSON形式の電文には改行が含まれる可能性があるため何らかの対処が必要になります。これは項目を囲い文字（デフォルトだと「"」）で囲めば良いのですが・・・項目囲い文字のエスケープを行わないと正しくデータをインポートすることができません。そのため・・・テキストデータのみで構成される電文もHEX化してしまうことでエスケープ問題を解決しようかと思っています。

なお、ローカル環境やCI環境など電文ログをDBへ登録しない環境では、テキストデータのみで構成される電文はHEX化せずに出力するようにするつもりです。

１行目：XML電文

テキスト表現だと以下のようなXMLになります。

<message type="999">
  <data>foo</data>
</message>

type : 電文種別

２行目：JSON電文

テキスト表現だと以下のようなJSONになります。

{
  "type" : "999",
  "data" : "foo"
}

3行目：固定項目長(テキスト)電文

テキスト表現だと以下のような文字列になります。

0010999foo
↑   ↑  ↑
1   2  3

総電文長: 10
電文タイプ: 999
データ: foo

4行目：固定項目長(バイナリ＋テキスト)電文

テキスト表現だと以下のような文字列になります。なお{0x..}と表記している部分はバイナリデータ部になります。

{0x00000009}{0x03E7}foo
↑           ↑       ↑
1           2       3

総電文長(int = 4バイト): 9
電文タイプ(short = 2バイト): 999
データ: foo

テーブル作成

電文ログをインポートするためのテーブルを用意します。


create table messages (
  id bigserial primary key
  , datetime timestamp not null
  , format varchar(10) not null
  , encoding varchar(20) not null
  , hex_message text not null
);

ビュー作成

実システムでビューを使うかはわかりませんが、本エントリーではHEX文字列から生メッセージ(bytea型)とテキストメッセージ(text型)を参照できるようにするためのビューを作ります。


create view v_messages as 
  select
    id
    ,datetime
    ,format
    ,encoding
    ,hex_message
    ,decode(hex_message,'hex') as raw_message
    ,case when format <> 'BIN' then convert_from(decode(hex_message,'hex'),encoding) else null end as text_message
  from messages;

decode関数を利用するとHEX文字列をbyteaデータへ変換することができ、さらにconvert_from関数を利用するとbytea型の値をテキストデータへ戻すことができます。

インポート

TSVファイルからデータをインポートします。

dev=# \copy messages(datetime,format,encoding,hex_message) from 'messages.tsv' csv delimiter E'\t' header;

検索

電文形式毎に条件を指定して電文を検索します。

select id, text_message, hex_message from v_messages where 
  (
    format = 'JSON' and
    json_extract_path_text(text_message::json,'type') = '999' and 
    json_extract_path_text(text_message::json,'data') = 'foo'
  )
  or 
  (
    format = 'XML' and
    xpath('/message[@type="999"]/data/text()', text_message::xml)::text[] = '{foo}'
  )
  or
  (
    format = 'TEXT' and
    substring(raw_message, 5, 3) = '999' and
    substring(text_message, 8, 3) = 'foo'
  )
  or
  (
    format = 'BIN' and
    substring(raw_message, 5, 2) = '\x03e7' and
    substring(raw_message, 7, 3) = 'foo'
  )
;

検索結果は以下のようになります。

 id |     text_message     |                                             hex_message                                              
----+----------------------+------------------------------------------------------------------------------------------------------
  1 | <message type="999">+| 3C6D65737361676520747970653D22393939223E0A20203C646174613E666F6F3C2F646174613E0A3C2F6D6573736167653E
    |   <data>foo</data>  +| 
    | </message>           | 
  2 | {                   +| 7B0A2020227479706522203A2022393939222C0A2020226461746122203A2022666F6F220A7D
    |   "type" : "999",   +| 
    |   "data" : "foo"    +| 
    | }                    | 
  3 | 0010999foo           | 30303130393939666F6F
  4 |                      | 0000000903E7666F6F
(4 rows)

最後に

実システムではもうちょっと考慮すべきことはあると思いますが、やりたいことはできそうです。
ちなみに・・・当初はアプリケーションからリアルタイムでDBに登録する案もあがったのですが、DBが落ちていたらファイルへ出力してほしい！という要望があがったので・・・、それなら最初からファイルに出力しておいて、集計時や解析時にDBに入れればいんじゃない？ということになりました。

HEX文字列でPostgreSQLへデータを登録してSQLで検索してみる