2021-03-03

再帰クエリで循環参照した時にどうなるのか？(SQL Server, PostgreSQL)

PostgreSQL で循環参照したらどうなるん？って聞かれたので。

PostgreSQL

with recursive d as (
select 1 as id, 4 as parentid
union 
select 2 as id, 1 as parentid
union 
select 3 as id, 2 as parentid
union 
select 4 as id, 3 as parentid
), cte as (
  select id, parentid, 1 as level from d where id = 1
  union all
  select d.id, d.parentid, cte.level + 1 as level
  from cte inner join d on cte.parentid = d.id
  -- where cte.level < 100 これないと無限に再帰する
)
select * from cte
where cte.level < 100 -- ここで条件書いても無限再帰は止まらない！！
 --limit 100 limit 付けてると無限に再帰するやつでも先頭 n 件だけで返ってくる

特に上限回数みたいなのは無いっぽい、リソースが尽きるまで回り続けるんかな？

7.6. LIMITとOFFSET
7.8. WITH問い合わせ（共通テーブル式）

ループするかどうか確信が持てない問い合わせをテストする有益な秘訣として、親問い合わせにLIMITを配置します。例えば、以下の問い合わせはLIMITがないと永久にループします。

WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

これが動作するのは、PostgreSQLの実装が、実際に親問い合わせで取り出されるのと同じ数のWITH問い合わせの行のみを評価するからです。この秘訣を実稼動環境で使用することは勧められません。他のシステムでは異なった動作をする可能性があるからです。同時に、もし外部問い合わせを再帰的問い合わせの結果を並べ替えたり、またはそれらを他のテーブルと結合するような書き方をした場合、動作しません。このような場合、外部問い合わせは通常、WITH問い合わせの出力をとにかくすべて取り込もうとするからです。

SQL Server

ついでに SQL Server も。

with d as (
select 1 as id, 4 as parentid
union 
select 2 as id, 1 as parentid
union 
select 3 as id, 2 as parentid
union 
select 4 as id, 3 as parentid
), cte as (
  select id, parentid, 1 as level from d where id = 1
  union all
  select d.id, d.parentid, cte.level + 1 as level
  from cte inner join d on cte.parentid = d.id
  -- where cte.level < 100 これないと無限に再帰する
)
select /*top(100)*/ * from cte -- top が無いと再帰回数上限エラーが出る
where cte.level < 100 -- ここで条件書いても無限再帰は止まらない！！

SQL Server は再帰の上限回数が既定では 100 になってるので、100 回再帰した時点でエラー。
再帰回数の上限や上限変更方法は、
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs *1
見てね。

再帰回数を1000回にする例だと…

with d as (
select 1 as id, 4 as parentid
union 
select 2 as id, 1 as parentid
union 
select 3 as id, 2 as parentid
union 
select 4 as id, 3 as parentid
), cte as (
  select id, parentid, 1 as level from d where id = 1
  union all
  select d.id, d.parentid, cte.level + 1 as level
  from cte inner join d on cte.parentid = d.id
)
select top(1000) * from cte
option (maxrecursion 1000)

後は、TOP (Transact-SQL) - SQL Server | Microsoft Docs と
ORDER BY 句 (Transact-SQL) - SQL Server | Microsoft Docs
は似たような動きするけど、このケースでは offset fetch はダメ。
offset fetch は order by 必須なので、並び替え中に再帰の上限に達してエラーになる。

SQL Server の Top は PostgreSQL の limit と同じで order by 無いと全データ見ないで返す感じになってるみたい。

まとめ

再帰クエリの外で条件書いても再帰の上限は変わらない
- 再帰クエリ(共通テーブル式)の中で再帰の条件書いとこう！
どうしても条件書けないときは limit / top で件数絞る！
- order by はダメ。

*1:日本語は訳が酷いので英語の方が良さげ

2021-02-22

メモ：Azure Cognitive Search デフォルトのアナライザーのストップワード(1文字限定) を調べる

Microsoft Azure

カスタムアナライザー使うのに ja.microsoft がどんなストップワード設定してるか確認用。
※複数文字は日本語なので取り合えずいいや。

雑いけど、ひらがな1文字ずつに分かれるテキスト渡して消えたやつがストップワードだ！って感じで。
テキストの分析 (Azure Cognitive Search REST API) | Microsoft Docs

{
  "text": "あ い う え お か き く け こ さ し す せ そ た ち つ て と な に ぬ ね の は ひ ふ へ ほ ま み む め も や ゆ よ ら り る れ ろ わ を ん が ぎ ぐ げ ご ざ じ ず ぜ ぞ だ ぢ づ で ど ば び ぶ べ ぼ ぱ ぴ ぷ ぺ ぽ",
  "analyzer": "ja.microsoft"
}

2021-02-04

2/5(金) devblogラジオ vol.9 で DB の雑談します

SQL Server 勉強会

明日ですが、
devblogラジオ vol.9 - connpass
に参加します。

ムッシュと兄貴とお話しするの久々なのでめっちゃ楽しみ！

夜ですけどお時間あるかたは是非ご参加を～。

2021-01-30

ScriptDom でサブクエリを CTE に書き換えるサンプル(叩き台)

.NET SQL Server

だいぶ昔に書いた
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);
        }
    }
}

2021-01-28

メモ：Swashbuckle Swagger で 40系の ProducesResponseType(typeof(void), ...) が ProblemDetails に変わる回避策

.NET

ProducesResponseType(typeof(void), ...) causes ApiExplorer to indicate the return type is ProblemDetails · Issue #7874 · dotnet/aspnetcore · GitHub

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)
        {
            // ...
        }

2021-01-27

1/30(土) meetup app osaka@5 に参加します #meetupapp

SQL Server 勉強会 Microsoft Azure

meetup app osaka@5 - connpass

このご時世なんでオンラインの ZOOM ですが、お時間があればぜひ～。
SQL DB をソースに Azure Cognitive Search を使うときのメモ - おだのスペース
や関係ない質問でも答えれる限り答えま～。

座談会っぽい感じみたいなんで楽しくやりましょう！

2021-01-09

SQL DB をソースに Azure Cognitive Search を使うときのメモ

SQL Server Microsoft Azure 勉強会

Azure SQL Database のデータを Azure Cognitive Search で全文検索するときに見る資料とかメモ
兼 meetup app vol.5？の資料。

変更追跡したい場合は、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;'.
Incorrect syntax near '[ColName] SET NO_BROWSETABLE OFF;'. Please adjust your data source definition in order to proceed.
Status: 400 (Bad Request)
— oda shinsuke (@shinsukeoda) 2021年1月4日

フィールドに属性付けないと使えない
インデックスを作成する - Azure Cognitive Search | Microsoft Docs

searchable : search (Lucene) で検索可能
filterable：filter (OData) で検索可能
sortable : order by 指定可能
retrievable : これないと検索結果として取得できない

前方一致したい

カスタムアナライザーでやる。
文字列フィールドにカスタムアナライザーを追加する - 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 は同時に指定も可能。

filter

OData 言語の概要 - Azure Cognitive Search | Microsoft Docs
' はエスケープ必要

メモ：コレクション内の検索
OData コレクション演算子のリファレンス - Azure Cognitive Search | Microsoft Docs

search でのメモ

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": ”さｆさｄふぁｓだｓｄｆさｄ”,
            "Col3": "あかかかっかあｓｄｆｄさ",
            "Col4": "SQLWorld is Worldwide!",
            "Col5": null,
            "Col6": [
                12,
                26
            ],
            "Col7": [
                "てすとあああ",
                "てすとかかか"
            ],
        }
    ]
}

ハイライトは HTML にレンダリングするなら、タグにせずに別なのにして HTML エンコードしてからタグに置換した方が良さげ。

まとめ