【gloops Advent Calendar 12/1】gloopsで検知しているDBアンチパターンまとめ

はじめまして。
社内での月間目標の取り組みとして「Advent Calendar 2016」に社内エンジニアみんなで参加しよう！
ということで開始しました。
これから約1か月の間、gloopsをよろしくお願いします！

【gloops Advent Calendar 2016】

「株式会社gloops」では、RDBMSとしてMicrosoft SQL Serverを採用しています。

今回は、Advent Calendar第一弾ということで、gloopsで検知しているDBデータのアンチパターン検出方法をまとめてみました。

アンチパターンを検出する

大きく分けて2種類の方法で実施しています。

①トリガーで検知

データに法則性があるものについて、それ以外のデータが入った場合、エラーとして検出する。
（マスタデータの作成時チェック等に使用しています）
【メリット】
・INSERT,UPDATEのタイミングで動くので、検知が早い。
【デメリット】
・テーブル単位に設定するので、メンテナンスが面倒

例）日付の逆転が無いかをチェックする

CREATE TRIGGER [dbo].[tri_hoge] 
ON [dbo].[hoge_master] FOR INSERT, UPDATE 
NOT FOR REPLICATION 
AS 
BEGIN 
  IF (SELECT COUNT(*) FROM inserted) > 0 
  BEGIN 
    IF (	
	(select 
		COUNT(*)
	from 
		inserted
	where 
		(from_date > to_date))
	 > 0
	 ) 
    BEGIN 
	select 'to_dateがfrom_dateよりも過去日になっています！'
	raiserror
	( 'to_dateがfrom_dateよりも過去日になっています！' , 16 , 1 ) 
	ROLLBACK TRANSACTION;
    END
  END
END

エラー時出力

to_dateがfrom_dateよりも過去日になっています！
メッセージ 50000、レベル 16、状態 1

②定期チェックでメール送信

プロジェクトをまたいで横断チェックするもの等に使用しています。
エラー検出以外にも不具合のレポートであったり、各KPIデータ等もこの機能を使って配信するようにしています。

例) メールを送信する

CREATE TABLE #tmp_hoge_table
(
--アンチパターンや集計データをいれるテーブルを定義
)

--複数のテータベースに一括で実行する為、sp_MSforeachdbで実行
exec sp_MSForEachDB    '

USE [?] 
IF DB_ID() > 4 and DB_NAME() not like ''hoge%'' --対象のスキーマが決まってれば設定
   BEGIN
DECLARE @table_name varchar(200)
DECLARE @column_name varchar(200)

DECLARE TABLE_LIST CURSOR STATIC FOR
SELECT
''[''+OBJECT_NAME(object_id)+'']'',''[''+name+'']''
from sys.columns where name =''hoge_column'' and OBJECT_NAME(object_id)!=''hoge_table'' --カラムやテーブルがあれば指定

--カーソルをオープンし、取得したテーブル全てについてチェック
OPEN TABLE_LIST

FETCH NEXT FROM TABLE_LIST INTO @table_name, @column_name

WHILE @@FETCH_STATUS = 0
BEGIN

--送信するデータを作成して一時テーブルにinsertする
EXEC(''insert into #tmp_hoge_table select・・・'')

FETCH NEXT FROM TABLE_LIST INTO @table_name, @column_name
END

CLOSE TABLE_LIST
DEALLOCATE TABLE_LIST


END'

DECLARE @tableHTML  NVARCHAR(MAX);
DECLARE @count  int;

--テーブルに入った内容をメールで送信(HTML形式)
SET @tableHTML =
    N'<H1>タイトル</H1>' +
    N'<table border="1">' +
    N'<tr style="background-color: #336699; color: #FFFFFF;">'+
    N'<th>カラム1</th><th>カラム2</th><th>カラム3</th><th>カラム4</th></tr>' +
    CAST ( ( SELECT 
                    td = column1, '',
                    td = column2, '',
                    td = column3, '',
                    td = column4, ''
              FROM #tmp_hoge_table with(nolock)
              ORDER BY column1,column2 DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX)) +
    N'</table>' ;

SELECT @count = COUNT(*) FROM #tmp_hoge_table
IF @count>0
BEGIN
	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'mail',
		@recipients = '[e-mail]',
		@from_address = '[e-mail]',
		@subject = 'メールタイトル',
		@body = @tableHTML,
		@body_format = 'HTML';
END

DROP TABLE #tmp_hoge_table

送信されるメール

後は①,②を量産！！機械でできることはどんどん任せます。
以上のような方法で、gloopsではSQL Serverを用いて機械チェックや日々の業務を行っています。