明日ですが、
devblogラジオ vol.9 - connpass
に参加します。
ムッシュ と 兄貴 とお話しするの久々なのでめっちゃ楽しみ!
夜ですけどお時間あるかたは是非ご参加を~。
だいぶ昔に書いた
SQL Server のバージョン毎の TSqlParser - お だ のスペース
にコメントが付いてたので雑にサンプル。
yutap01 だいぶ昔の投稿についての質問になります。 よろしければお知恵を拝借したいのですが、 TSqlParserを使って、複雑なTSQLを複数のSQLに分解したいと考えています。 具体的には、SQL文から自動的にサブクエリをwith句として外に抽出するという処理なのですが、具体的な実装方法についてご教授いただけないでしょうか?
from で join とかするサブクエリは、
QueryDerivedTable Class (Microsoft.SqlServer.TransactSql.ScriptDom) | Microsoft Docs
っぽいです。
= や in のサブクエリは
ScalarSubquery Class (Microsoft.SqlServer.TransactSql.ScriptDom) | Microsoft Docs
なのかな?
CTE 作るとこまではサクッと出来たけど、元の From のサブクエリを消すのは面倒そうなので、手抜き。
サブクエリの中のサブクエリとかはゴリゴリやらないとダメかもね。
※エイリアスが被る可能性がありそう
select * from (select s.Id from (select Id from Table1) s) sub1 inner join (select s.Id from (select Id from Table2) s) sub2 on sub1.Id = sub2.Id
雑なサンプルなので
使ってます。
using Microsoft.SqlServer.TransactSql.ScriptDom; using System; using System.Collections.Generic; using System.IO; namespace TSqlParser { class Program { static void Main(string[] args) { var baseQuery = @"select [SubQuery1].[Id] , [SubQuery1].[Name] , [Table1].[Id] , [Table1].[Name] from ( select [Id] , [Name] from [Table2] ) SubQuery1 inner join [Table1] on [SubQuery1].[Id] = [Table1].[Id] inner join [Table4] on [Table1].[Id] = [Table4].[Id] inner join ( select [Id] , [DivId] from [Table5] where [EntryDate] >= '2021-01-01' ) [SubQuery2] on [Table1].[Id] = [SubQuery2].[Id] where [Table4].[Name] like '%hoge%' and [SubQuery2].[DivId] > 2 order by [SubQuery1].[Id]"; var parser = new TSql150Parser(false); IList<ParseError> errors; var parsed = parser.Parse(new StringReader(baseQuery), out errors); var v = new SubQueryVisitor(); parsed.Accept(v); var cte = v.SelectStatement.WithCtesAndXmlNamespaces ?? new WithCtesAndXmlNamespaces(); foreach (var sub in v.SubQueries) { cte.CommonTableExpressions.Add(new CommonTableExpression() { QueryExpression = sub.QueryExpression, ExpressionName = sub.Alias }); sub.QueryExpression = null; } v.SelectStatement.WithCtesAndXmlNamespaces = cte; var generator = new Sql150ScriptGenerator(); generator.GenerateScript(parsed, out var s); foreach (var sub in v.SubQueries) { s = s.Replace($"() AS [{sub.Alias.Value}]", $"[{sub.Alias.Value}]"); } Console.WriteLine(s); parser.Parse(new StringReader(s), out errors); Console.WriteLine("-- エラー件数 --"); Console.WriteLine(errors.Count); Console.ReadKey(); } } public class SubQueryVisitor : TSqlFragmentVisitor { public List<QueryDerivedTable> SubQueries { get; set; } = new List<QueryDerivedTable>(); public SelectStatement SelectStatement { get; set; } public override void ExplicitVisit(SelectStatement node) { SelectStatement = node; base.ExplicitVisit(node); } public override void ExplicitVisit(QueryDerivedTable node) { SubQueries.Add(node); // サブクエリのエイリアスを [] で囲ってない場合も [] で囲むようにする。(手抜きの Replace用に) node.Alias.QuoteType = QuoteType.SquareBracket; base.ExplicitVisit(node); } } }
40系はデフォだと ProblemDetails Class (Microsoft.AspNetCore.Mvc) | Microsoft Docs になってしまうので、
ProducesErrorResponseTypeAttribute Class (Microsoft.AspNetCore.Mvc) | Microsoft Docs でデフォルトの type を変える。
例:403 は void で 400 は IdentityError[] の時
[Produces("application/json")] [Route("api/[controller]")] [ApiController] public class AccountController : ControllerBase { [HttpPost("register")] [ProducesErrorResponseType(typeof(void))] // これが無いと 403 が ProblemDetails になる [ProducesResponseType(403)] // [ProducesResponseType(typeof(void), 403)] にしても ProducesErrorResponseType が無いと ProblemDetails になる [ProducesResponseType(typeof(IdentityError[]), 400)] [ProducesResponseType(typeof(string), 200)] public async Task<object> Hoge([FromBody] HogeModel model) { // ... }
このご時世なんで オンラインの ZOOM ですが、お時間があればぜひ~。
SQL DB をソースに Azure Cognitive Search を使うときのメモ - お だ のスペース
や関係ない質問でも答えれる限り答えま~。
座談会っぽい感じみたいなんで楽しくやりましょう!
Azure SQL Database のデータを Azure Cognitive Search で全文検索するときに見る資料とかメモ
兼 meetup app vol.5? の資料。
Cognitive Search
料金 - Search | Microsoft Azure
インデックス作るのに AI 使うと追加でお金掛かりそう。
AI エンリッチメントの概念 - Azure Cognitive Search | Microsoft Docs
Cognitive Services をスキルセットにアタッチする - Azure Cognitive Search | Microsoft Docs
Azure SQL データにインデックスを付ける C# チュートリアル - Azure Cognitive Search | Microsoft Docs
Azure SQL データを検索する - Azure Cognitive Search | Microsoft Docs
変更追跡したい場合は、Change Tracking を使った SQL 統合変更追跡ポリシー
変更の追跡について - SQL Server | Microsoft Docs
要件として使えない場合は、rowversion を使った高基準値変更検出ポリシー
rowversion (Transact-SQL) - SQL Server | Microsoft Docs
高基準値変更検出ポリシーの
"highWaterMarkColumnName" : "[a rowversion or last_updated column name]"
論理削除列削除検出ポリシーの
"softDeleteMarkerValue" : "[the value that indicates that a row is deleted]"
は "["、"]" で列名囲ってるように見えるけど囲うと例外でてやられる。。
'Error with data source: Unclosed quotation mark after the character string '[ColName] SET NO_BROWSETABLE OFF;'.
— oda shinsuke (@shinsukeoda) 2021年1月4日
Incorrect syntax near '[ColName] SET NO_BROWSETABLE OFF;'. Please adjust your data source definition in order to proceed.
Status: 400 (Bad Request)
フィールドに属性付けないと使えない
インデックスを作成する - Azure Cognitive Search | Microsoft Docs
カスタムアナライザーでやる。
文字列フィールドにカスタム アナライザーを追加する - Azure Cognitive Search | Microsoft Docs
インデックス作るサンプルコード
search-dotnet-getting-started/DotNetHowToIndexers at master · Azure-Samples/search-dotnet-getting-started · GitHub
を参考に該当する箇所変えてってー。
定義
// 継承する必要ないけど、INDEX の定義と // Analyser / Tokenizer の追加のとこの2か所で NAME 使うので継承してる public class PrefixCustomAnalyzer : CustomAnalyzer { public const string NAME = "Prefix-Analyzer"; public PrefixCustomAnalyzer() : this(NAME, PrefixCustomTokenizer.NAME) { } public PrefixCustomAnalyzer(string name, LexicalTokenizerName tokenizerName) : base(name, tokenizerName) { // 小文字でも検索できるように TokenFilters.Add(TokenFilterName.Lowercase); } } public class PrefixCustomTokenizer : EdgeNGramTokenizer { public const string NAME = "Prefix-Tokenizer"; public PrefixCustomTokenizer() : this(NAME) { } public PrefixCustomTokenizer(string name) : base(name) { MinGram = 1; MaxGram = 30; // とりま30文字までで。 max 300文字まで出来る } } // サンプルでは Hotel だけどここでは変えた public class PrefixSeachableDocument { [SimpleField(IsFilterable = true, IsKey = true)] public string Id { get; set; } [SearchableField(IsFilterable = true, AnalyzerName = LexicalAnalyzerName.Values.JaLucene)] public string Title { get; set; } // Title は前方一致もしたいー [SearchableField(IsFilterable = true, AnalyzerName = PrefixCustomAnalyzer.NAME)] public string PrefixTitle { get; set; } }
インデックス作成の箇所
var searchIndex = new SearchIndex( "hotels-sql-idx", searchFields); // 作ったAnalyzer と Tokenizer を登録する。 // 継承したクラスじゃなくても、 // ここでプロパティを適当に設定するでもOK。 // Name さえ定義のクラスとあってれば searchIndex.Analyzers.Add(new PrefixCustomAnalyzer()); searchIndex.Tokenizers.Add(new PrefixCustomTokenizer());
複合データ型をモデル化する方法 - Azure Cognitive Search | Microsoft Docs
インポートおよびインデックス作成用に SQL リレーショナル データをモデル化する - Azure Cognitive Search | Microsoft Docs
View でもいいけど、1列に Json で関係しているデータ放り込む。
対応してる型
サポートされているデータ型 (Azure Cognitive Search REST API) | Microsoft Docs
テキストの分析 (Azure Cognitive Search REST API) | Microsoft Docs
OData の filter と Lucene の search
完全一致や範囲検索は filter、全文検索は search
ドキュメントの検索 (Azure Cognitive Search REST API) | Microsoft Docs
search と filter は同時に指定も可能。
OData 言語の概要 - Azure Cognitive Search | Microsoft Docs
'
はエスケープ必要
メモ:コレクション内の検索
OData コレクション演算子のリファレンス - Azure Cognitive Search | Microsoft Docs
Lucene クエリ構文 - Azure Cognitive Search | Microsoft Docs
+ - & | ! ( ) { } [ ] ^ " ~ * ? : \ /
はエスケープ必要
単一項目に対しての条件は、fieldName:searchExpression
でフィールド指定で検索する。
複数項目またがって検索する場合は、ドキュメントの検索のパラメータ searchFields
を指定し、search
はフィールド指定いない。
混合も可能。
例:Col1、Col2、Col3 を おだ、Col4 は SQLWorld で検索
{ "count": true, "queryType": "full", "searchFields": "Col1, Col2, Col3", "search": "おだ AND Col4:SQLWorld" }
highlight
、highlightPreTag
、highlightPostTag
で結果に highlights が取れる。
タグ はデフォルトだと、<em></em>
request
{ "count": true, "highlight": "Col1、Col2、Col3、Col4", "highlightPreTag": "[hoge]", "highlightPostTag": "[/hoge]", "queryType": "full", "searchFields": "Col1, Col2, Col3", "search": "Blog AND Col4:SQLWorld" }
response (手で作ってるから細部は適当)
{ "@odata.context": "https://xxxxx.search.windows.net/indexes('index-name')/$metadata#docs(*)", "@odata.count": 1, "value": [ { "@search.score": 1, "@search.highlights": { "Col1": [ "この [hoge]Blog[/hoge] は おだのスペース" ], "Col4": [ "[hoge]SQLWorld[/hoge] is Worldwide!" ] }, "Id": "5", "Col1": "この Blog は おだのスペース", "Col2": ”さfさdふぁsだsdfさd”, "Col3": "あかかかっかあsdfdさ", "Col4": "SQLWorld is Worldwide!", "Col5": null, "Col6": [ 12, 26 ], "Col7": [ "てすとあああ", "てすとかかか" ], } ] }
ハイライトは HTML にレンダリングするなら、タグにせずに別なのにして HTML エンコードしてからタグに置換した方が良さげ。
メモ:T-SQL 単一列の結果表をシンプルな JsonArray にしたい - お だ のスペース
で、XML にしたいわけじゃないのに FOR XML 使ってますが、こういう時に XML でエスケープ必要な文字があった場合困ることが起きます。
前回のデータを変えて試してみます。
create table [データ] ( [Id] bigint not null identity(1, 1) primary key , [Name] nvarchar(20) not null ) go insert into [データ] ([Name]) values (N'あいう&えお') , (N'かき<div>くけこ</div>')
select concat(',', '"', [Name], '"') from [データ] for xml path('')
,"あいう&えお","かき<div>くけこ</div>"
これをエスケープしてない文字列としてとる方法があります。
まず FOR XML クエリの TYPE ディレクティブ - SQL Server | Microsoft Docs を使って XML 型として返します。
select concat(',', '"', [Name], '"') from [データ] for xml path(''), type
,"あいう&えお","かき<div>くけこ</div>"
結果は変わりませんが、XML 型 になっています。
これで XML 型のメソッドが使えます。
xml データ型のメソッド - SQL Server | Microsoft Docs
今回は value() メソッド (xml データ型) - SQL Server | Microsoft Docs を使って値を全部取り出します。
select ( select concat(',', '"', [Name], '"') from [データ] for xml path(''), type ).value('.', 'nvarchar(max)')
,"あいう&えお","かき<div>くけこ</div>"
はい、エスケープされてない生文字列が取れました。
create table [データ] ( [Id] bigint not null identity(1, 1) primary key , [Name] nvarchar(20) not null ) go insert into [データ] ([Name]) values (N'あいうえお') , (N'かきくけこ')
から
["あいうえお", "かきくけこ"]
にしたい場合のクエリ。
参考 SQL to JSON - array of objects to array of values in SQL 2016 - Stack Overflow
単純に for json だとダメ。
select [Name] from [データ] for json path
[{"Name":"あいうえお"},{"Name":"かきくけこ"}]
結果セットの行が配列の要素(object)になってしまう。
ojbect じゃなくて良ーんだよってことで、色々やります。
まず、'"' で囲って、 区切りの','付けて for xml で1行にまとめてしまう。
select concat(',', '"', [Name], '"') from [データ] for xml path('')
,"あいうえお","かきくけこ"
で、STUFF (Transact-SQL) - SQL Server | Microsoft Docs で 先頭の ',' を取る。
select stuff( (select concat(',', '"', [Name], '"') from [データ] for xml path('')) , 1, 1, '')
"あいうえお","かきくけこ"
後は、前後に '['、']' 付けたらOK!
select concat('[', stuff( (select concat(',', '"', [Name], '"') from [データ] for xml path('')) , 1, 1, ''), ']')
["あいうえお","かきくけこ"]
SQL Server 標準の JSON 機能使ってない?
では、正しい JSON かどうかだけチェックしときます。
JSON_QUERY (Transact-SQL) - SQL Server | Microsoft Docs
select json_query((select concat('[', stuff( (select concat(',', '"', [Name], '"') from [データ] for xml path('')) , 1, 1, ''), ']')))
["あいうえお","かきくけこ"]
JSON 機能素晴らしい?!