T-SQL * Parse * Generator

T-SQL の Parse と Generate
2013/03/23 SQLWorld大阪#12
SQLWorld おだ
自己紹介
織田信亮
大阪で開発者しています
SQLWorld の代表です
http://d.hatena.ne.jp/odashinsuke/
Twitter:@shinsukeoda
アジェンダ
Parse と Generate って？
どうやるの？
何に使える？
使ってみた！
まとめ
このセッションの注意事項
全ての機能は紹介出来ません！
紹介するライブラリでは、1000近くの Class, 200近くの
Enum がある
簡単な Parse と Generate に絞ってます。
Parse と Generate って？
どうやるの？
何に使える？
使ってみた！
まとめ
Parse と Generate
Parse (パース)
SQL 文 => 構文毎に分解したデータ構造の集まり
Generate (ジェネレート)
構文毎のデータ構造の集まり => SQL 文
Parse
クエリ
SELECT 列1, 列2
FROM テーブル
列1
SELECT 句
FROM 句
列2
テーブル
Generate
クエリ
SELECT 句
列1
SELECT 列1, 列2
FROM テーブル
FROM 句
列2
テーブル
Parse と Generate
Parse (パース)
SQL 文 => 構文毎に分解したデータ構造の集まり
Generate (ジェネレート)
構文毎のデータ構造の集まり => SQL 文
Parser (パーサー)
Parse してくれる便利なやつ
Generator (ジェネレーター)
Generate してくれる良いやつ
Parser と Generator
MS から .NET Framework のライブラリとして提供
Microsoft.SqlServer.TransactSql.ScriptDom 名前空間
1世代前は…
Microsoft.Data.Schema.ScriptDom
Microsoft.Data.Schema.ScriptDom.Sql
SQL Server 2012 Feature Pack の「Transact-SQL
ScriptDom」をインストール
SQL Server は不要
Parse と Generate って？
どうやるの？
何に使える？
使ってみた！
まとめ
インストール
Microsoft SQL Server 2012 Feature Pack
http://www.microsoft.com/jajp/download/details.aspx?id=29065
準備
参照の追加
Microsoft.SqlServer.TransactSql.ScriptDom
Parse してみる
TSqlParser クラス
Parse メソッドを使う
using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.Collections.Generic;
using System.IO;
var parser = new TSql110Parser(false);
IList<ParseError> errors;
TSqlFragment parsed;
using (var query = new StringReader("select * from Table1")) {
parsed = parser.Parse(query, out errors);
}
Parse してみる (エラー)
ParseError クラス
Line, Column, Message プロパティから行、文字位置、エ
ラー内容を取れる
if (errors.Count != 0) {
foreach (var error in errors) {
System.Console.WriteLine("{0}行目 {1} 文字目 {2}",
error.Line, error.Column, error.Message);
}
}
Generate してみる
SqlScriptGenerator クラス
GenerateScript メソッドを使う
var options = new SqlScriptGeneratorOptions() {
KeywordCasing = KeywordCasing.Uppercase,
IncludeSemicolons = true,
NewLineBeforeFromClause = true,
NewLineBeforeOrderByClause = true,
NewLineBeforeWhereClause = true
};
var generator = new Sql110ScriptGenerator(options);
string generated;
generator.GenerateScript(parsed, out generated);
もうちょっと細かいとこまで
Parser – バージョン毎に用意されてる
TSql80Parser - SQL Server 2000用
TSql90Parser - SQL Server 2005用
TSql100Parser - SQL Server 2008用
TSql110Parser - SQL Server 2012用
バージョンが違うとエラーになる構文も…
もうちょっと細かいとこまで
Parse 結果
TSqlFragment – 基底クラス
TSqlScript, TSqlBatch, TSqlStatement, SelectElement,
FromClause, WhereClause, Identifier, CreateTableStatement,
等々 800個近い継承したクラスがある
DML に限らず、DDL や DBCC 等の全ての T-SQL に対応している
「はず！」
Visitor パターンになっているので、Visitor を実装すれば
色々出来る
もうちょっと細かいとこまで
Visitor
どちらかのクラスを継承し、目的の Visitor メソッドを
override する
TSqlFragmentVisitor
呼び出される Visitor メソッドのパラメータは継承した物も含む
TSqlConcreteFragmentVisitor
Visitor メソッドのパラメータ型は厳密
もうちょっと細かいとこまで
例：SELECT で指定している項目の数を数える
http://msdn.microsoft.com/jajp/library/microsoft.sqlserver.transactsql.scriptdom.selectelement.aspx
SELECT @Id = A.Id, @Name = B.Name
FROM ( SELECT * FROM Table1 WHERE Id = 1) A
INNER JOIN Table2 B ON ( A.USERID = B.ID )
もうちょっと細かいとこまで
TSqlFragmentVisitor
SELECT で指定している項目全ての件数を数える Visitor
public class SelectElementVisitor : TSqlFragmentVisitor {
public int Count { get; set; }
public override void Visit(SelectElement node) {
Count++;
base.Visit(node);
}
}
もうちょっと細かいとこまで
TSqlConcreteFragmentVisitor
SELECT で指定している “*” の件数を数える Visitor
public class SelectStarVisitor : TSqlFragmentVisitor {
public int Count { get; set; }
public override void Visit(SelectStarExpression node) {
Count++;
base.Visit(node);
}
}
もうちょっと細かいとこまで
カスタム Visitor を利用する
var q = @"SELECT @Id = A.Id, @Name = B.Name
FROM ( SELECT * FROM Table1 WHERE Id = 1) A
INNER JOIN Table2 B ON ( A.USERID = B.ID )";
var f = new TSql110Parser(false)
.Parse(new StringReader(q), out errors);
var v1 = new SelectElementVisitor();
var v2 = new SelectStarVisitor();
f.Accept(v1);
f.Accept(v2);
Console.WriteLine(v1.Count); // 3
Console.WriteLine(v2.Count); // 1
もうちょっと細かいとこまで
Generator – バージョン毎に用意されてる
Sql80ScriptGenerator - SQL Server 2000用
Sql90ScriptGenerator - SQL Server 2005用
Sql100ScriptGenerator - SQL Server 2008用
Sql110ScriptGenerator - SQL Server 2012用
イマイチ違いが判らず…
もうちょっと細かいとこまで
TSqlFragment を組み立てて、クエリを生成する
「もうちょっと」で済まないくらい大変！！
お勧めはしない
http://d.hatena.ne.jp/odashinsuke/20130224/1361714459
裏では何使ってるの？
antlr を使ってると思います
http://www.antlr.org/
Parse と Generate って？
どうやるの？
何に使える？
使ってみた！
まとめ
MSDN に掲載されているサンプル
チュートリアル: SQL 用のカスタムの静的コード分析規
則アセンブリを作成する
http://msdn.microsoft.com/jajp/library/dd172127%28v=vs.100%29.aspx
Visual Studio 2008/2010 でのコード解析で
「WAITFOR DELAY」が使用されているか検出する
チュートリアル
何に使えるの？
アイデア募集中！
何に使えるの？
クエリの検証
構文エラーの検出
コーディング規約のチェック
クエリの書式設定/統一
クエリの部分抽出
動的なクエリ生成
クエリの改造
クエリの検証
構文エラーの検出
実DB が無い環境でも SQL の構文が正しいか判定出来る
注意点としてオブジェクトの存在チェックは出来ない！
SQL Server のバージョン毎に構文チェックが可能なので、
移行検証時に使えるかも
SQL Server の Ver UP や他社DB (Oracle 等)からの移行
クエリの検証
コーディング規約のチェック
例えば…
DELETE は使わない(論理削除)
マスタテーブル(MST_～)は inner join とか
漏れやすい項目の検証
SELECT 文に ORDER BY が存在していないクエリの検出
スキーマ指定漏れ
定型的な条件の漏れ (DELETE_FLG = 0 とか)
COLLATE 指定
クエリの書式設定/統一
クエリのフォーマット
キーワードの大文字/小文字化
識別子の [] 囲み
インデント
改行
クエリの部分抽出
サブクエリだけ抜き出す
SELECT の結果カラムだけ抜き出す
INSERT – SELECT から SELECT だけ抜き出す
INSERT 実行前に更新対象を確認(クエリ自体は1つで可能)
動的なクエリ生成
クエリの改造
ER => DDL (CREATE TABLE) で共通的なカラムの追加
SELECT で共通的なカラムの追加
COUNT(*) OVER() AS [全件数]
INSERT/UPDATE で共通的なカラムの追加
更新日/更新者等
Parse と Generate って？
どうやるの？
何に使える？
使ってみた！
まとめ
ScriptDom のサンプルサイト
ScriptDom Sample
http://scriptdomsample.azurewebsites.net/
クエリの書式設定/統一
クエリの部分抽出
クエリの改造
ビルド時の SQL 検証
MSBuild のタスクとして作成する
プロジェクト内に存在する .sql ファイルで SELECT 文の物
を対象に ORDER BY が存在しなかったらエラーとする
まとめ
T-SQL には、MS 公式の Parser/Generator がある
SQL Server 2000, 2005, 2008, 2012 の4バージョン
面白そうではあるけど、どこで/何に使うのかはアイデア
が要るかも
参考資料
Microsoft SQL Server 2012 Feature Pack
http://www.microsoft.com/ja-jp/download/details.aspx?id=29065
Microsoft.SqlServer.TransactSql.ScriptDom 名前空間
http://msdn.microsoft.com/ja-jp/library/hh215705.aspx
Visual Studio のデータベース機能の API リファレンス
http://msdn.microsoft.com/ja-jp/library/dd193281(v=vs.100).aspx
ANTLR
http://www.antlr.org/
参考資料 (ScriptDom を使ってる)
チュートリアル: SQL 用のカスタムの静的コード分析規則
アセンブリを作成する
http://msdn.microsoft.com/ja-jp/library/dd172127%28v=vs.100%29.aspx
SQLPSX (PowerShell2 系/1世代前の ScriptDom)
http://sqlpsx.codeplex.com/

Download Report