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

自己紹介 織田 信亮 大阪で開発者しています SQLWorld の代表です 織田　信亮 大阪で開発者しています SQLWorld の代表です http://d.hatena.ne.jp/odashinsuke/ Twitter:@shinsukeoda

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

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

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

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

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

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

Parse と Generate Parse (パース) Generate (ジェネレート) Parser (パーサー) 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/ja-jp/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用 バージョンが違うとエラーになる構文も…

もうちょっと細かいとこまで 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/ja-jp/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/ja-jp/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/