T-SQL の Parse と Generate

Presentation on theme: "T-SQL の Parse と Generate"— Presentation transcript:

1 T-SQL の Parse と Generate
2013/03/23 SQLWorld大阪#12 SQLWorld お だ

2 自己紹介 織田 信亮 大阪で開発者しています SQLWorld の代表です
織田　信亮 大阪で開発者しています SQLWorld の代表です

3 アジェンダ Parse と Generate って？ どうやるの？ 何に使える？ 使ってみた！ まとめ

4 このセッションの注意事項 全ての機能は紹介出来ません！ 紹介するライブラリでは、1000近くの Class, 200近くの Enum がある
簡単な Parse と Generate に絞ってます。

5 Parse と Generate って？ どうやるの？ 何に使える？ 使ってみた！ まとめ

6 Parse と Generate Parse (パース) Generate (ジェネレート)
SQL 文 => 構文毎に分解したデータ構造の集まり Generate (ジェネレート) 構文毎のデータ構造の集まり => SQL 文

7 Parse クエリ SELECT 句 列1 列2 FROM 句 テーブル SELECT 列1, 列2 FROM テーブル

8 Generate クエリ SELECT 句 列1 列2 FROM 句 テーブル SELECT 列1, 列2 FROM テーブル

9 Parse と Generate Parse (パース) Generate (ジェネレート) Parser (パーサー)
SQL 文 => 構文毎に分解したデータ構造の集まり Generate (ジェネレート) 構文毎のデータ構造の集まり => SQL 文 Parser (パーサー) Parse してくれる便利なやつ Generator (ジェネレーター) Generate してくれる良いやつ

10 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 は不要

11 Parse と Generate って？ どうやるの？ 何に使える？ 使ってみた！ まとめ

12 インストール Microsoft SQL Server 2012 Feature Pack

13 準備 参照の追加 Microsoft.SqlServer.TransactSql.ScriptDom

14 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); }

15 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); }

16 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);

17 もうちょっと細かいとこまで Parser – バージョン毎に用意されてる TSql80Parser - SQL Server 2000用
バージョンが違うとエラーになる構文も…

18 もうちょっと細かいとこまで Parse 結果 TSqlFragment – 基底クラス
TSqlScript, TSqlBatch, TSqlStatement, SelectElement, FromClause, WhereClause, Identifier, CreateTableStatement, 等々 800個近い継承したクラスがある DML に限らず、DDL や DBCC 等の全ての T-SQL に対応している「はず！」 Visitor パターンになっているので、Visitor を実装すれば色々出来る

19 もうちょっと細かいとこまで Visitor どちらかのクラスを継承し、目的の Visitor メソッドを override する
TSqlFragmentVisitor 呼び出される Visitor メソッドのパラメータは継承した物も含む TSqlConcreteFragmentVisitor Visitor メソッドのパラメータ型は厳密

20 もうちょっと細かいとこまで 例：SELECT で指定している項目の数を数える
= = B.Name FROM ( SELECT * FROM Table1 WHERE Id = 1) A INNER JOIN Table2 B ON ( A.USERID = B.ID )

21 もうちょっと細かいとこまで TSqlFragmentVisitor SELECT で指定している項目全ての件数を数える Visitor
public class SelectElementVisitor : TSqlFragmentVisitor { public int Count { get; set; } public override void Visit(SelectElement node) { Count++; base.Visit(node); }

22 もうちょっと細かいとこまで TSqlConcreteFragmentVisitor
SELECT で指定している “*” の件数を数える Visitor public class SelectStarVisitor : TSqlFragmentVisitor { public int Count { get; set; } public override void Visit(SelectStarExpression node) { Count++; base.Visit(node); }

23 もうちょっと細かいとこまで カスタム Visitor を利用する
var = = 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

24 もうちょっと細かいとこまで Generator – バージョン毎に用意されてる
Sql80ScriptGenerator - SQL Server 2000用 Sql90ScriptGenerator - SQL Server 2005用 Sql100ScriptGenerator - SQL Server 2008用 Sql110ScriptGenerator - SQL Server 2012用 イマイチ違いが判らず…

25 「もうちょっと」 で済まないくらい大変！！
もうちょっと細かいとこまで TSqlFragment を組み立てて、クエリを生成する 「もうちょっと」 で済まないくらい大変！！ お勧めはしない

26 裏では何使ってるの？ antlr を使ってると思います

27 Parse と Generate って？ どうやるの？ 何に使える？ 使ってみた！ まとめ

28 MSDN に掲載されているサンプル チュートリアル: SQL 用のカスタムの静的コード分析規則アセンブリを作成する
Visual Studio 2008/2010 でのコード解析 で 「WAITFOR DELAY」 が使用されているか検出するチュートリアル

29 何に使えるの？ アイデア募集中！

30 何に使えるの？ クエリの検証 クエリの書式設定/統一 クエリの部分抽出 動的なクエリ生成 構文エラーの検出 コーディング規約のチェック
クエリの改造

31 クエリの検証 構文エラーの検出 実DB が無い環境でも SQL の構文が正しいか判定出来る
注意点としてオブジェクトの存在チェックは出来ない！ SQL Server のバージョン毎に構文チェックが可能なので、移行検証時に使えるかも SQL Server の Ver UP や 他社DB (Oracle 等)からの移行

32 クエリの検証 コーディング規約のチェック 例えば… 漏れやすい項目の検証 DELETE は使わない(論理削除)
マスタテーブル(MST_～)は inner join とか 漏れやすい項目の検証 SELECT 文に ORDER BY が存在していないクエリの検出 スキーマ指定漏れ 定型的な条件の漏れ (DELETE_FLG = 0 とか) COLLATE 指定

33 クエリの書式設定/統一 クエリのフォーマット キーワードの大文字/小文字化 識別子の [] 囲み インデント 改行

34 クエリの部分抽出 サブクエリだけ抜き出す SELECT の結果カラムだけ抜き出す
INSERT – SELECT から SELECT だけ抜き出す INSERT 実行前に更新対象を確認(クエリ自体は1つで可能)

35 動的なクエリ生成 クエリの改造 ER => DDL (CREATE TABLE) で共通的なカラムの追加
SELECT で共通的なカラムの追加 COUNT(*) OVER() AS [全件数] INSERT/UPDATE で共通的なカラムの追加 更新日/更新者 等

36 Parse と Generate って？ どうやるの？ 何に使える？ 使ってみた！ まとめ

37 ScriptDom のサンプルサイト ScriptDom Sample
クエリの書式設定/統一 クエリの部分抽出 クエリの改造

38 ビルド時の SQL 検証 MSBuild のタスクとして作成する
プロジェクト内に存在する .sql ファイルで SELECT 文の物を対象に ORDER BY が存在しなかったらエラーとする

39 まとめ T-SQL には、MS 公式の Parser/Generator がある
SQL Server 2000, 2005, 2008, 2012 の4バージョン 面白そうではあるけど、どこで/何に使うのかはアイデアが要るかも

40 参考資料 Microsoft SQL Server 2012 Feature Pack
Microsoft.SqlServer.TransactSql.ScriptDom 名前空間 Visual Studio のデータベース機能の API リファレンス ANTLR

41 参考資料 (ScriptDom を使ってる)
チュートリアル: SQL 用のカスタムの静的コード分析規則アセンブリを作成する SQLPSX (PowerShell2 系/1世代前の ScriptDom)