SQLiteでメールのDateヘッダーを変換

SQLite3では「Tue, 3 Aug 1999 23:10:00 +0900」とか対応日付関数も正規表現置換も出来なくて厄介…

「過去のメールからメアドをGREPし、同時にDateヘッダーからやり取りした日付も取得する」という作業をしていました。メールデータはプレーンテキスト、抽出にはとりあえず適当なGREPを使い【ファイルパス、Dateヘッダー、From|To|Cc|BCCヘッダー】を抽出します。*1
抽出済みのデータがこんな感じです。

SELECT file, date_, mail FROM den8wk1_view;
file date_ mail
Y:\Sample\Denshin8\IN\0000.TXT Tue, 3 Aug 1999 23:10:00 +0900 From: mag2
Y:\Sample\Denshin8\IN\0000.TXT Tue, 3 Aug 1999 23:10:00 +0900 To: pochi-p@example.com

目的はメアドの使用順の調査なので、ここから日付順にソートしたりしたい訳です。しかしRFC822で定義されている書式はソート処理に不向きな形式の文字列です。

From:フィールドのフォーマットは,RFC822で定義されている次のフォーマットに従います。

[daytime,] d mon yy hh:mm[:ss] zone
[daytime,] dd mon yy hh:mm[:ss] zone
[daytime,] d mon yyyy hh:mm[:ss] zone
[daytime,] dd mon yyyy hh:mm[:ss] zone

http://itdoc.hitachi.co.jp/manuals/3020/30203D1390/0173.htm

(……FromではなくDateの気がしますが、まあtypoは無視して続けます。)


最終的にソート&閲覧しやすくする為に下記の結果が欲しいです。

file date_ mail yyyy mm dd
Y:\Sample\Denshin8\IN\0000.TXT Tue, 3 Aug 1999 23:10:00 +0900 From: mag2 1999 08 03
Y:\Sample\Denshin8\IN\0000.TXT Tue, 3 Aug 1999 23:10:00 +0900 To: pochi-p@example.com 1999 08 03


DBによっては変換関数があるかもしれませんが、今回使用するのはSQLite3です。標準で実装されている日付関数strftimeでは上記形式に対応していないようです。
正規表現で適当に加工しようにも、SQLite3には正規表現の検索/置換機能は現在実装されていない様です。
という事はSQLite3では自作の関数を別途作る必要があるのでしょうか…。正直面倒過ぎるのでもっと楽に解決したいです。


という訳で、それを力業で何とかするのが今回の課題。さっそく実際のSQLを見ていきましょう。

(1)CASEとLIKEで何とかする

偉い人も言ってます。「正規表現が使えないならLIKEを使えば良いじゃない」と。まあLIKEだけでは置換出来ないのでCASEを使って条件分けしてしまいましょう。力業 is Power!

SELECT
  file
  , date_
  , mail 
  , CASE 
    WHEN date_ LIKE '% 2000 %' THEN '2000'
    WHEN date_ LIKE '% 2001 %' THEN '2001'
    WHEN date_ LIKE '% 2002 %' THEN '2002'
    WHEN date_ LIKE '% 2003 %' THEN '2003'
    WHEN date_ LIKE '% 2004 %' THEN '2004'
    WHEN date_ LIKE '% 2005 %' THEN '2005'
    WHEN date_ LIKE '% 2006 %' THEN '2006'
    WHEN date_ LIKE '% 2007 %' THEN '2007'
    WHEN date_ LIKE '% 2008 %' THEN '2008'
    WHEN date_ LIKE '% 2009 %' THEN '2009'
    WHEN date_ LIKE '% 2010 %' THEN '2010'
    WHEN date_ LIKE '% 2011 %' THEN '2011'
    WHEN date_ LIKE '% 2012 %' THEN '2012'
    WHEN date_ LIKE '% 2013 %' THEN '2013'
    WHEN date_ LIKE '% 2014 %' THEN '2014'
    WHEN date_ LIKE '% 2015 %' THEN '2015'
    WHEN date_ LIKE '% 2016 %' THEN '2016'
    WHEN date_ LIKE '% 2017 %' THEN '2017'
    WHEN date_ LIKE '% 00 __:%' THEN 2000
    WHEN date_ LIKE '% 01 __:%' THEN 2001
    WHEN date_ LIKE '% 02 __:%' THEN 2002
    WHEN date_ LIKE '% 03 __:%' THEN 2003
    WHEN date_ LIKE '% 04 __:%' THEN 2004
    WHEN date_ LIKE '% 05 __:%' THEN 2005
    WHEN date_ LIKE '% 06 __:%' THEN 2006
    WHEN date_ LIKE '% 07 __:%' THEN 2007
    WHEN date_ LIKE '% 08 __:%' THEN 2008
    WHEN date_ LIKE '% 09 __:%' THEN 2009
    WHEN date_ LIKE '% 10 __:%' THEN 2010
    WHEN date_ LIKE '% 11 __:%' THEN 2011
    WHEN date_ LIKE '% 12 __:%' THEN 2012
    WHEN date_ LIKE '% 13 __:%' THEN 2013
    WHEN date_ LIKE '% 14 __:%' THEN 2014
    WHEN date_ LIKE '% 15 __:%' THEN 2015
    WHEN date_ LIKE '% 16 __:%' THEN 2016
    WHEN date_ LIKE '% 17 __:%' THEN 2017
    END yyyy
  , CASE 
    WHEN date_ LIKE '%Jan%' THEN '01'
    WHEN date_ LIKE '%Feb%' THEN '02'
    WHEN date_ LIKE '%Mar%' THEN '03'
    WHEN date_ LIKE '%Apr%' THEN '04'
    WHEN date_ LIKE '%May%' THEN '05'
    WHEN date_ LIKE '%Jun%' THEN '06'
    WHEN date_ LIKE '%Jul%' THEN '07'
    WHEN date_ LIKE '%Aug%' THEN '08'
    WHEN date_ LIKE '%Sep%' THEN '09'
    WHEN date_ LIKE '%Oct%' THEN '10'
    WHEN date_ LIKE '%Nov%' THEN '11'
    WHEN date_ LIKE '%Dec%' THEN '12'
    END mm
  , CASE 
    WHEN date_ LIKE '%, 1 %' THEN '01' 
    WHEN date_ LIKE '%, 2 %' THEN '02' 
    WHEN date_ LIKE '%, 3 %' THEN '03' 
    WHEN date_ LIKE '%, 4 %' THEN '04' 
    WHEN date_ LIKE '%, 5 %' THEN '05' 
    WHEN date_ LIKE '%, 6 %' THEN '06' 
    WHEN date_ LIKE '%, 7 %' THEN '07' 
    WHEN date_ LIKE '%, 8 %' THEN '08' 
    WHEN date_ LIKE '%, 9 %' THEN '09' 
    WHEN date_ LIKE '%, 01 %' THEN '01' 
    WHEN date_ LIKE '%, 02 %' THEN '02' 
    WHEN date_ LIKE '%, 03 %' THEN '03' 
    WHEN date_ LIKE '%, 04 %' THEN '04' 
    WHEN date_ LIKE '%, 05 %' THEN '05' 
    WHEN date_ LIKE '%, 06 %' THEN '06' 
    WHEN date_ LIKE '%, 07 %' THEN '07' 
    WHEN date_ LIKE '%, 08 %' THEN '08' 
    WHEN date_ LIKE '%, 09 %' THEN '09' 
    WHEN date_ LIKE '%, 10 %' THEN '10' 
    WHEN date_ LIKE '%, 11 %' THEN '11' 
    WHEN date_ LIKE '%, 12 %' THEN '12' 
    WHEN date_ LIKE '%, 13 %' THEN '13' 
    WHEN date_ LIKE '%, 14 %' THEN '14' 
    WHEN date_ LIKE '%, 15 %' THEN '15' 
    WHEN date_ LIKE '%, 16 %' THEN '16' 
    WHEN date_ LIKE '%, 17 %' THEN '17' 
    WHEN date_ LIKE '%, 18 %' THEN '18' 
    WHEN date_ LIKE '%, 19 %' THEN '19' 
    WHEN date_ LIKE '%, 20 %' THEN '20' 
    WHEN date_ LIKE '%, 21 %' THEN '21' 
    WHEN date_ LIKE '%, 22 %' THEN '22' 
    WHEN date_ LIKE '%, 23 %' THEN '23' 
    WHEN date_ LIKE '%, 24 %' THEN '24' 
    WHEN date_ LIKE '%, 25 %' THEN '25' 
    WHEN date_ LIKE '%, 26 %' THEN '26' 
    WHEN date_ LIKE '%, 27 %' THEN '27' 
    WHEN date_ LIKE '%, 28 %' THEN '28' 
    WHEN date_ LIKE '%, 29 %' THEN '29' 
    WHEN date_ LIKE '%, 30 %' THEN '30' 
    WHEN date_ LIKE '%, 31 %' THEN '31' 
    WHEN date_ LIKE '1 %' THEN '01' 
    WHEN date_ LIKE '2 %' THEN '02' 
    WHEN date_ LIKE '3 %' THEN '03' 
    WHEN date_ LIKE '4 %' THEN '04' 
    WHEN date_ LIKE '5 %' THEN '05' 
    WHEN date_ LIKE '6 %' THEN '06' 
    WHEN date_ LIKE '7 %' THEN '07' 
    WHEN date_ LIKE '8 %' THEN '08' 
    WHEN date_ LIKE '9 %' THEN '09' 
    WHEN date_ LIKE '01 %' THEN '01' 
    WHEN date_ LIKE '02 %' THEN '02' 
    WHEN date_ LIKE '03 %' THEN '03' 
    WHEN date_ LIKE '04 %' THEN '04' 
    WHEN date_ LIKE '05 %' THEN '05' 
    WHEN date_ LIKE '06 %' THEN '06' 
    WHEN date_ LIKE '07 %' THEN '07' 
    WHEN date_ LIKE '08 %' THEN '08' 
    WHEN date_ LIKE '09 %' THEN '09' 
    WHEN date_ LIKE '10 %' THEN '10' 
    WHEN date_ LIKE '11 %' THEN '11' 
    WHEN date_ LIKE '12 %' THEN '12' 
    WHEN date_ LIKE '13 %' THEN '13' 
    WHEN date_ LIKE '14 %' THEN '14' 
    WHEN date_ LIKE '15 %' THEN '15' 
    WHEN date_ LIKE '16 %' THEN '16' 
    WHEN date_ LIKE '17 %' THEN '17' 
    WHEN date_ LIKE '18 %' THEN '18' 
    WHEN date_ LIKE '19 %' THEN '19' 
    WHEN date_ LIKE '20 %' THEN '20' 
    WHEN date_ LIKE '21 %' THEN '21' 
    WHEN date_ LIKE '22 %' THEN '22' 
    WHEN date_ LIKE '23 %' THEN '23' 
    WHEN date_ LIKE '24 %' THEN '24' 
    WHEN date_ LIKE '25 %' THEN '25' 
    WHEN date_ LIKE '26 %' THEN '26' 
    WHEN date_ LIKE '27 %' THEN '27' 
    WHEN date_ LIKE '28 %' THEN '28' 
    WHEN date_ LIKE '29 %' THEN '29' 
    WHEN date_ LIKE '30 %' THEN '30' 
    WHEN date_ LIKE '31 %' THEN '31' 
    END dd
FROM
  den8wk1_view 

とりあえず上記で2000年以降2017年までの日付に関しては対応可能になりました。
年部分は4桁であれば

    WHEN date_ LIKE '% 2000 %' THEN '2000'

…で簡単に変換出来ます。列挙が面倒ですけど。年2桁は位置特定を後続の時刻内区切り文字":"を利用して列挙すればなんとかなります。

    WHEN date_ LIKE '% 50 __:%' THEN 1950
--省略--
    WHEN date_ LIKE '% 99 __:%' THEN 1999
    WHEN date_ LIKE '% 00 __:%' THEN 2000
--省略--
    WHEN date_ LIKE '% 49 __:%' THEN 2049

月に関しては月を表す3文字を12種類列挙するだけですね。簡単です。

    WHEN date_ LIKE '%Jan%' THEN '01'

日付に関してはこちらも頭ゼロ埋め2桁ゼロ埋め無しかの問題と、年の方が2桁の場合の対処が必要です。位置特定する為に曜日直後のカンマか、曜日がない前提で位置指定をして何とかしましょう。

    WHEN date_ LIKE '%, 1 %' THEN '01' 
--省略--
    WHEN date_ LIKE '%, 9 %' THEN '09' 
    WHEN date_ LIKE '%, 01 %' THEN '01' 
--省略--
    WHEN date_ LIKE '%, 31 %' THEN '31' 
    WHEN date_ LIKE '1 %' THEN '01' 
--省略--
    WHEN date_ LIKE '9 %' THEN '09' 
    WHEN date_ LIKE '01 %' THEN '01' 
--省略--
    WHEN date_ LIKE '31 %' THEN '31' 

この方法でやればSQLite3以外でも基本的なCASEやLIKEを使えるDBならほぼ実現可能です。
ただしパフォーマンスは保証出来かねます。LIKEだしSQL長過ぎだし遅くなる要素満載です。
そして残念ながらSQLite3でも件数が多いと「メモリが足りません」とエラーになるので、残念ながら条件を絞る等の工夫が必要になってしまいます。
数百件程度は大丈夫でしたが92911件のデータでは使っていたツール*2を巻き込んで不安定になってしまいました。

(2)汎用的な形にする(条件と値の変換テーブルを用意しJOIN)

件数制限の問題と、SQL文が長くなる問題を何とかする為改造します。
結局のところWHENで描いてるのは「判断条件と取得する値の組み合わせ」だけなので、それを変換用テーブルとして登録しちゃいましょう。
テーブル定義して

create table rfc822date( 
  type varcahr(15)
  , liketext varchar (30)
  , valtext varchar (15)
) 
type liketext valtext
type liketext valtext
yyyy % 1950 % 1950
省略 省略 省略
yyyy % 1999 % 1999
yyyy % 2000 % 2000
省略 省略 省略
yyyy % 2049 % 2049
yyyy % 50 __:% 1950
省略 省略 省略
yyyy % 99 __:% 1999
yyyy % 00 __:% 2000
省略 省略 省略
yyyy % 49 __:% 2049
mm %Jan% 01
省略 省略 省略
mm %Dec% 12
dd %, 1 % 01
省略 省略 省略
dd %, 9 % 09
dd %, 01 % 01
省略 省略 省略
dd %, 31 % 31
dd 1 % 01
省略 省略 省略
dd 9 % 09
dd 01 % 01
省略 省略 省略
dd 31 % 31

な感じでデータ登録します。省略箇所はただの連続データなので説明要りませんよね? あとはSQLをJOINの形式に置き換えです。

SELECT
  file
  , date_
  , mail
  , y.valtext yyyy
  , m.valtext mm
  , d.valtext dd 
FROM
  den8wk1_view s 
  LEFT OUTER JOIN rfc822date y 
    ON s.date_ LIKE y.liketext 
    AND y.type = 'yyyy' 
  LEFT OUTER JOIN rfc822date m 
    ON s.date_ LIKE m.liketext 
    AND m.type = 'mm' 
  LEFT OUTER JOIN rfc822date d 
    ON s.date_ LIKE d.liketext 
    AND d.type = 'dd'

こちらの方式であれば、92911件のデータであっても問題なく取得できました。検索時間は結構かかってしまいますけどね。*3
毎回実行は遅くなりますので、あらかじめ変換誤の値を格納する列を設けたテーブルに用意し、1度SELECT INTO等で変換値を保存してやる事で実用上問題無くなると思います。


以上、CASEとLIKEとJOINで遊んでみるコーナーでした。

*1:複数件宛先のあるメールも存在しましたが、その辺は事前に加工や情報絞り込みで排除しています。

*2:みんな大好きA5:SQL Mk-2! http://a5m2.mmatsubara.com/

*3:DBによっては前述のCASEの方が早かったりするかもしれません。その辺は実装次第ですね。