DQL (Domino Query Language)

Notes/Domino V10 の新機能である DQL (Domino Query Language) について検証したものです。

2019年2月15日開催の「テクてくLotus技術者夜会」で使用した資料です。

Transcript

1. Domino Query Language (DQL) V10.0.1 でDQLを試す 中野 晴幸 @harunakano harunakano@blogspot.com
2. 免責事項  本資料の記載内容は2019年2月時点のものであり、できる限り正確を期すよう努 めておりますが、その内容の正確性を保証するものではなく、本資料の利用によ り生じたいかなる損害に対して、いかなる責任も負わないものとします。  本資料の情報は使用先の責任において使用されるべきであることをあらかじめご 了承ください。  本資料に記載の内容は不定期に更新されることもあります。他のメディアに無断 で転載することはご遠慮ください。
3. 目次  DQLとは  違い  準備  クエリー実行
4. DQLとは
5. Domino Query Language (DQL) Domino Query Language (DQL) は、Dominoのコアで実行される機能であり、文 書内の情報を検索する際に簡潔な構文を可能にする機能。 アクセスするための詳細なコードを書く必要はない。 DQL は（Notes）Database オブジェクトの LotusScript および Java Query メ ソッド、または domino-db node.js API を介して実行することができる。
6. 簡潔な構文 Term Boolean Term [ Boolean Term ...] [ Identifier ] Operator [ Value ] And Or and not or not Boolean Term
7. 簡潔な構文 Term Boolean Term [ Boolean Term ...] [ Identifier ] Operator [ Value ] And Or and not or not SummaryField ‘View or folder name’.Columnname @function = > >= < <= in [all] ‘ Quoted string value ’ @dt(‘ datetime value ’) Number ( in value list ) Boolean Term [ Identifier ] Operator [ Value ]
8. 簡潔な構文 Term Boolean Term [ Boolean Term ...] [ Identifier ] Operator [ Value ] And Or and not or not SummaryField ‘View or folder name’.Columnname @function = > >= < <= in [all] ‘ Quoted string value ’ @dt(‘ datetime value ’) Number ( in value list ) Boolean Term [ Identifier ] Operator [ Value ]
9. サマリーフィールド (SummaryField)  サマリーフィールドは、Dominoデータベース内の任意の文書の要約部分に格納 されている任意のフィールドの名前です。 要約 フィールド名
10. ‘ビュー名またはフォルダ名’.列名 (‘View or folder name’.Columnname)  ビュー名またはフォルダ名（別名も可）をシングルコーテーションで囲む  列名はプログラム名のこと（列のタイトルではない）  「All」ビューの「Subject」列で 文字列 ‘Notes’ を探す場合 ‘All’.Subject = ‘Notes’
11. @function  @all すべての文書  @Created 文書の作成日時（なんだろうけど….更新日時（初回）の値を返してる気がする）  @ModifiedInThisFile 文書の更新日時（現在のファイル）  @DocumentUniqueID 文書IDを32桁の文字列で指定する
12. 値（Value）のデータ型 テキスト値をシングルコーテーションで囲む •‘テクてくLotus技術者夜会‘ •‘ファイアー 中野’ 文字列 ISO8601 形式の GMT または 国際標準時 を @dt(‘<ISO8601 format>’) と記述する •@dt(‘2019-02-15’) •@dt(’18:00:00.00+0900’) •@dt(‘2019-02-15T18:00:00+09:00’) 日時 値は科学的記数法を含む、小数点の有無にかかわらず浮動小数点数 •123456E-2 •1235 数値
13. In [all] ()  In (a, b)  括弧内で囲まれた値リスト内のいずれかの値と一致する  In all (a, b)  括弧で囲まれた値リスト内のすべての値と一致する ※現状、ビューを使った検索の場合のみ有効？
14. トークン間にはスペースが必要 C:¥IBM¥Domino>domquery -f "DQL.nsf" -q "DocNo=200" -e Query is not understandable - syntax error - MUST have at least one operator (Call hint: OSCalls::OSLocalAllc, Core call #0) DocNo=200 ^......... Query is not understandable C:¥IBM¥Domino>domquery -f "DQL.nsf" -q "DocNo = 200" -e 0.DocNo = 200 View-based equality search estimated cost = 5 [350C:0002-3EE0] Prep 0.240 msecs, Exec 2.5 msecs, ScannedDocs 0, Entries 1, FoundDocs 1 [350C:0002-3EE0] Total Documents found = 1 フィールド名(Identifier)、イコール記号(Operator)、 値(Value)の間にスペースがなければ 文法(Syntax) error となる
15. 違い 全文検索と何が違うの？
16. 何が検索できるの？ DQL  サマリー・フィールド 全文検索  すべてのフィールド  添付ファイル
17. クエリーにワイルドカードが使えるの？ DQL  使えない 全文検索  使える
18. 検索に必要なモノとコト DQL  デザインカタログへの登録・更新 Updall myapp.nsf -e 全文検索  全文索引の作成・更新
19. 最新の文書は見つかるの？ DQL  文書作成直後に見つかる ※ビュー検索の場合、ビューの更新後に見つ かる 全文検索  索引更新後に見つかる
20. 読者フィールドに対応するの？ DQL  閲覧可能な文書だけヒットする 全文検索  閲覧不可の文書もヒットする
21. どこで実行できるの？ DQL  Domino サーバー  Notes クライアント ※現状、サーバー上のデータベースにある エージェントをクライアントで実行できない。 ただしクライアントから RunOnServer メ ソッドを使い実行することは可能 全文検索  Domino サーバー  Notes クライアント
22. 準備
23. 必要なモノとそうでないもの  デザインカタログ  あると良いモノ ➢ ビュー ➢ 制限緩和のための設定 (notes.ini)
24. デザインカタログ  DQL 処理では、ビューノートから抽出され最適化されたデザインデータを使用 して、クエリ計画に関する選択を行い、ビュー名などの構文要素を処理する。こ れを行うには、GQFdsgn.cat という名前の、データディレクトリに保存されて いるカタログ内のデータを作成し、最新の状態に保つ必要がある。  複製されず、実行時に設計データにアクセスするための高速ツールとしてのみ使 用される。  設計とその中のデータは独自のものであり、不安定であるため、ローカルの Domino サーバー以外のエンティティによるアクセスと更新はサポートされてい ない。
25. デザインカタログの作成  GQFdsgn.cat を作成するには、 -e オプションを付けてデータベースの名前で updall を実行する。 (domino コンソールから) load updall myapp.nsf –e (コマンドプロンプトから) nupdall myapp.nsf –e  最初に発行されたときに GQFdsgn.cat はその中にカタログ化された単一のデー タベースで作成される。  DQL を使用してクエリする各データベースを同じ方法でデザインカタログに追 加する必要がある。  ファイル名を省略すると、データディレクトリの下にあるすべてのデータベース がデザインカタログに追加されるが、かなり大きなスーパーセットであるため推 奨されていない。
26. デザインカタログの更新  ビューのデザインを変更したりビューを追加したりした場合、DQLがその変更を 認識するように updall –d を実行する必要がある。
27. ビュー  ビューを使った検索は、使わない検索に比べ「断然早い！」  自動で適切なビューを使う（私が思う最適では無い場合あり..）  DQLで使用されるビューは次の属性が求められる  「ビューの選択」に「SELECT @All」を使用している  「列の値」にフィールド名のみ指定している  列の種類は「カテゴリなし」、かつ「複数の値を別のエントリで表示」が有効である
28. 制限緩和のための設定 (notes.ini)  QUERY_MAX_DOCS_SCANNED = <スキャンされたシステム全体の最大許容NSF 文書>  デフォルトは 200000 (20万文書)  QUERY_MAX_VIEW_ENTRIES_SCANNED = <スキャンされたシステム全体の最大 許容ビューエントリ数>  デフォルトは 200000 (20万ビューエントリ)  QUERY_MAX_MSECS_TOTAL = <システム全体の最大クエリ時間（ミリ秒）>  デフォルトは 120000 (2分)
29. クエリー実行
30. DomQuery コマンドラインユーティリティー  DomQuery コマンドラインユーティリティーは Domino Server に同梱されてお り、クエリの作成や調整に役立ちます。  その EXPLAIN 出力は、DQL構文およびクエリの処理方法を理解し最適化するの に役立ちます。
31. 【参考】 コマンドプロンプトは「管理者として実行」 C:¥IBM¥Domino>domquery.exe -f "DQL.nsf" -q "Subject = '2019/01/26 13:38:08'" -e This database is currently in use by another person or process, and cannot be accessed at this time. In order to share a Notes database, it must be accessed via a Domino Server by all users of the database. C:¥IBM¥Domino> ※「管理者として実行」しない場合、 V10.0.1 では次のように表示されました
32. 【参考】 DomQuery コマンドのオプション -f [DBName] data directory relative path, REQUIRED -q [double quoted string query] query string - either this or -z file REQUIRED -z [QueryFile path] full path to a file containing query syntax queries delimited by #* at preceding line begin -t [File Path] full path to a file with query tree(s) -e Explain the nodes (query tree mode only) -p Parse only (for testing) -g Provide and output overall timing -v [MaxEntries] Maximum view entries to be scanned -c [MaxDocsScanned] Maximum number of documents to be scanned -m [Msecs] Maximum milliseconds to execute -x Exit on error (-z file case) -j No view processing performed (only NSF document scan and FT)
33. DomQuery サーバータスク できることはコマンドラインユーティリティーと同様 Domino サーバーコンソールからコマンド投入例 Load domquery –f “DQL.nsf” –q “Subject = ‘Hello’” -e
34. NotesDominoQuery クラス(LotusScript) V10.0.1 の新機能。 クエリーを使って検索するだけでなく、検索にヒットした文書にアクセスすること が可能になりました。 https://www.ibm.com/support/knowledgecenter/ja/SSVRGU_10.0.1/basic/H_NOTESDOMINOQUERY_CLASS.html
35. プロパティとメソッド(LotusScript)  プロパティ ➢ MaxScanDocs ➢ MaxScanEntries ➢ NoViews ➢ RefreshViews ➢ TimeoutSec  メソッド ➢ Execute ➢ Explain ➢ Parse ➢ ResetNamedVariables ➢ SetNamedVariable
36. MaxScanDocs, MaxScanEntries プロパティ (LotusScript)  クエリによってスキャンされる文書の最大許容数（MaxScanDocs）と、ビュー エントリの最大許容数（MaxScanEntries）を指定します  数値を超えると実行がエラーとなります（とヘルプに記載されていますが…）  デフォルトは文書が50万、ビューエントリが20万です  データ型は Integer （<32768 では少なすぎます！） 設定しても値を超える数がヒットしました。 まだ正しく機能していないようです…
37. Parse, Explain, Execute メソッド (LotusScript)  Parse は、クエリーがパースできる場合、文字列 “Success” を返す  Explain は、クエリーの構文と処理方法、検索の結果を返す  Execute は、クエリーを実行（パースを含む）してヒットした文書のコレクションを 返す ※Execute メソッドや Explain メソッドの前に Parse メソッドの実行は不要
38. SetNamedVariable  クエリー中の変数名を値に置き換える  名前付き置換変数は1～15バイトの英数字および特殊文字のテキスト名 Dim ss As New NotesSession Dim db As NotesDatabase Dim ndq As NOTESDOMINOQUERY Dim query As String Set db = ss.Currentdatabase Set ndq = db.Createdominoquery() query = "DocNo = ?docno" ndq.Setnamedvariable "docno", 210 MsgBox ndq.Explain( query ),,"Explain Result"
39. SQLインジェクション対策 次のように、クエリーの一部を書き換えて生成するコードがある場合、 query = “PhoneNo = ‘” & phoneNo & “’” 変数 phoneNo には、通常 03-1111-2222 といった文字列が入り、変数 query の文字列 は次のようになることが期待されています。 PhoneNo = ‘03-1111-2222’ ここで、変数 phoneNo に 03-1111-2222’ OR NOT FirstName = ‘ をセットし、変数 query の文字列が PhoneNo = ‘03-1111-2222’ OR NOT FirstName = ‘’ となった場合、電話番号に関係なく FirstName がある人はもれなくヒットします。 このように、アプリケーションが想定しないクエリー文を実行させることで、不正に操作 する攻撃方法をSQLインジェクションといいます。
40. SQLインジェクション対策  SetNamedVariable を使用することで、フィールド 名（またはビューの列名）と値とを明確に区別可能 であり、不正なクエリーへの対策として有効です Dim ss As New NotesSession Dim db As NotesDatabase Dim ndq As NOTESDOMINOQUERY Dim query As String Set db = ss.Currentdatabase Set ndq = db.Createdominoquery() query = "Subject = '2019/01/26 13:38:08' OR not DocNO = 0" msgbox ndq.Explain( query ),,“Result #1" Set ndq = Nothing Set ndq = db.Createdominoquery() query = "Subject = ?subject" ndq.Setnamedvariable "subject", "2019/01/26 13:38:08' OR not DocNo = 0" MsgBox ndq.Explain( query ),,"Result #2" ndq.Resetnamedvariables 値を構造化しない クエリーが構造化される
41. DominoQuery クラス (Java) プロパティとメソッド  プロパティ ➢ MaxScanDocs ➢ MaxScanEntries ➢ NoViews ➢ RefreshViews ➢ TimeoutSec  メソッド ➢ GetMaxScanDocs ➢ SetMaxScanDocs ➢ GetMaxScanEntries ➢ SetMaxScanEntries ➢ GetTimeoutSecs ➢ SetTimeoutSecs ➢ IsRefreshViews ➢ SetRefreshViews ➢ IsNoViews ➢ SetNoViews ➢ Execute ➢ Explain ➢ Parse ➢ ResetNamedVariables ➢ SetNamedVariable
42. DQLは 本当に高速なのか 4種類の検索を比較
43. 比較に使用したクエリー 種類 Class.Method 検索結果：少ない 検索結果：多い 検索 NotesDatabase.Search Subject = "2019/01/26 13:38:08" Subject != "2019/01/26 13:38:08" 全文検索 NotesDatabase.FTSearch [Subject] = "2019/01/26 13:38:08" not [Subject] = "2019/01/26 13:38:08" DQL NSF文書検索 NotesDominoQuery.Execute Subject = '2019/01/26 13:38:08' not Subject = '2019/01/26 13:38:08' DQL ビュー列検索 NotesDominoQuery.Execute 'All'.Subject = '2019/01/26 13:38:08' not 'All'.Subject = '2019/01/26 13:38:08' ※ Subject フィールドに文書の作成日時をテキスト型で保持
44. コード抜粋（LotusScript） Dim ss As New NotesSession Dim db As NotesDatabase Dim ndq As NOTESDOMINOQUERY Dim dc As NotesDocumentCollection Dim query As String Set db = ss.Currentdatabase ‘ 検索 query = |Subject = "2019/01/26 13:38:08"| Set dc = db. Search( query, Nothing, 0 ) Set dc = Nothing ‘ 全文検索 query = |[Subject] = "2019/01/26 13:38:08"| Set dc = db. Ftsearch( query, 0 ) Set dc = Nothing ‘ DQL NSF文書検索 Set ndq = db.Createdominoquery() ndq.Noviews = True ‘ビュー列検索しない query = |Subject = '2019/01/26 13:38:08'| Set dc = ndq.Execute( query ) Set dc = Nothing Set ndq = Nothing ‘ DQL ビュー列検索 Set ndq = db.Createdominoquery() query = |'All'.Subject = '2019/01/26 13:38:08'| Set dc = ndq.Execute( query ) Set dc = Nothing Set ndq = Nothing
45. 【参考】 LotusScriptで1秒未満を計る %Include "lsconst.lss" Sub Initialize Dim ss As New NotesSession Dim ndq As NOTESDOMINOQUERY Dim dc As NotesDocumentCollection Dim tick_start As Long, tick_end As Long, seconds As Double Set ndq = ss.Currentdatabase.Createdominoquery() tick_start = GetThreadInfo( LSI_THREAD_TICKS ) Set dc = ndq.Execute(|'All'.Subject = '2019/01/26 13:38:08'| ) tick_end = GetThreadInfo( LSI_THREAD_TICKS ) seconds = ( tick_end - tick_start ) / GetThreadInfo( LSI_THREAD_TICKS_PER_SEC ) Print Format$( seconds, "##0.000" ) End Sub
46. 検証環境の準備  Notesクライアント上のローカルデータベース  100万件の文書を作成  Subject フィールドへ文書の作成日時をテキスト形 式で保存  Subject フィールドでソートしたビューを作成、 ビュー索引を作成  全文索引を作成  デザインカタログ作成（nupdall DQL.nsf -e）  notes.ini へ次の3行を追加 QUERY_MAX_DOCS_SCANNED=1000000 QUERY_MAX_VIEW_ENTRIES_SCANNED=1000000 FT_MAX_SEARCH_RESULTS=1000000
47. 検証環境 IBM Notes Non- Production10.0.1 Windows 10 Pro, Intel i7- 8550U, 16GB, 512GB SSD
48. 実行結果(1) 100万文書中 0.0468% がヒット クラスとメソッド NotesDatabase.Search NotesDatabase.Ftsearch NotesDominoQuery.Execute Noviews = True NotesDominoQuery.Execute ビュー検索 1回目 2.734 0.078 4.172 0.063 2回目 2.719 0.078 4.110 0.062 3回目 2.672 0.078 4.266 0.062 4回目 1.078 0.031 1.625 0.016 5回目 1.015 0.031 1.594 0.031 平均 2.044 0.059 3.153 0.047
49. 実行結果(2) 100万文書中 99.9532% がヒット クラスとメソッド NotesDatabase.Search NotesDatabase.Ftsearch NotesDominoQuery.Execute Noviews = True NotesDominoQuery.Execute ビュー検索 1回目 3.657 2.875 4.515 0.047 2回目 3.531 2.859 4.313 0.047 3回目 3.578 2.859 4.422 0.047 4回目 3.516 2.859 4.281 0.031 5回目 3.532 2.859 4.328 0.047 平均 3.562 2.862 4.372 0.044
50. まとめ DQLのビュー列検索は安定して高速 クラスとメソッド NotesDatabase.Search NotesDatabase.Ftsearch NotesDominoQuery.Execute Noviews = True NotesDominoQuery.Execute ビュー検索 ヒット数 = 小 2.044 0.059 3.153 0.047 ヒット数 = 多 3.562 2.862 4.372 0.044 差 1.158 2.803 1.219 -0.003
51. 失敗例/成功例
52. 【失敗例】 サーバー上のエージェント実行権限がない [2394:008E-2A30] 2019/01/31 14:39:00 Error validating execution rights for agent 'DQL Test#1' in database 'DQL.nsf'. Agent signer 'admin/v10', effective user 'admin/v10'. Examine 'Programmability Restrictions' field in the Server Record. サーバー文書で エージェント実行権限を付与します
53. 【失敗例】 デザインカタログがない [2394:008E-2AA4] 2019/01/31 14:43:23 Agent 'DQL Test#1' error: Domino Query execution error: ** ERROR during View Processing Initializing - possible design catalog corruption ** File does not exist - error during planning and tree generation Error (File does not exist) in getCatalogDB, path = GQFDsgn.cat Please run updall <dbname> -e to create the design catalog (Call hint: NSFCalls::NSFDbOpen, Core call #0) ****************** updall に -e オプションを付けて実行すると作成されます！
54. 【失敗例】 ビュー索引が無い  ビューを作っただけで満足せず、ビューを開くなどして索引を作ります！ Domino Administrator でビュー 索引のサイズがわかります ビュー索引が無くても実行しますが何 もヒットしません
55. 【失敗例】 いまいちなビュー  列を最も左に移動し、昇順でソートする  列式はフィールド名だけとする  列のプログラム名をフィールド名と一致させる  変更した設計をデザインカタログへ反映する > load domquery -f "DQL.nsf" -q "Subject = '2019/01/26 13:38:08'" -e Subject = '2019/01/26 13:38:08' NSF document search estimated cost = 100 Prep 0.137 msecs, Exec 3010.826 msecs, ScannedDocs 1000000, Entries 0, FoundDocs 468 Total Documents found = 468 > load domquery -f "DQL.nsf" -q "Subject = '2019/01/26 13:38:08'" -e 0.Subject = '2019/01/26 13:38:08' View-based equality search estimated cost = 5 Prep 0.372 msecs, Exec 66.305 msecs, ScannedDocs 0, Entries 468, FoundDocs 468 Total Documents found = 468 ビューが使われない例 ビューが使われる例
56. 【失敗例】 ヒットする数 ＞ デフォルト値 [2394:008E-2AC0] 2019/01/31 14:46:53 Agent 'DQL Test#1' error: Domino Query execution error: Maximum number of documents or view entries scanned for query exceeded - timeout or resources exceeded error Too many documents scanned (500001) vs max allowed (500000) (C all hint: NSFCalls::NSFSearchExtended3, Core call #0) ****************** Notes.ini パラメータで制限値を緩和します QUERY_MAX_DOCS_SCANNED QUERY_MAX_VIEW_ENTRIES_SCANNED
57. 【失敗例】 @DocumentUniqueID に叱られる 32桁以外の場合、理解不能と叱られる 32桁の場合、検索してくださる
58. 【失敗例】 @dt に叱られる OKな値 2019-01-26T13:38:09.00Z 2019-01-26T13:38:09+09:00 2019-01-26 13:38:09.0000 13:38:09.0000+0900 13:38:09.0000+00900 13:38:09.0000+0000 13:38:09.0+ 13:38:09.0+0 13:38:09.0+000 13:38:09.0Z 13:38:09.+Z 13:38:09.0+Z NGな値 2019/01/26 2019 01 26 20190126 2019-1-26 19-01-26 13:38:09 T133809 13:38:09. 13:38:09.0 13:38:09Z 13:38:09 Z 13:38:09+ Z 13:38:09+Z クエリーで日時値を日本のタイムゾーンで指定して Explain を実行すると、値がGMTに変換されます。 時刻（+タイムゾーン）のみ指定すると、タイムゾーン が無視されローカルタイムに？！わかりづらい…
59. 【成功例？失敗例？】 クエリーの無駄を Explain で明確にする load domquery -f "DQL.nsf" -q "Subject = '2019/01/26 13:38:08' and DocNo < 200" -e 0. AND (childct 2) (totals when complete:) Prep 0.0 msecs, Exec 83.430 msecs, ScannedDocs 0, Entries 667, FoundDocs 199 1.Subject = '2019/01/26 13:38:08' View-based equality search estimated cost = 5 Prep 0.412 msecs, Exec 70.818 msecs, ScannedDocs 0, Entries 468, FoundDocs 468 1.DocNo < 200 View-based range search estimated cost = 10 Prep 0.209 msecs, Exec 12.597 msecs, ScannedDocs 0, Entries 199, FoundDocs 199 Total Documents found = 199 load domquery -f "DQL.nsf" -q "DocNo < 200" -e 0.DocNo < 200 View-based range search estimated cost = 10 Prep 0.417 msecs, Exec 13.988 msecs, ScannedDocs 0, Entries 199, FoundDocs 199 Total Documents found = 199
60. 【成功例】 多少古いODSバージョンも検索可能 専用拡張子 .NS4 を使用
61. 【禁断の成功例！？】 違和感を感じたらデザインカタログを再作成！？ 遅い？と感じる Query Processed: [Subject = '2019/01/26 13:38:08'] 0.Subject = '2019/01/26 13:38:08' NSF document search estimated cost = 100 Prep 0.62 msecs, Exec 4348.792 msecs, ScannedDocs 1000000, Entries 0, FoundDocs 468 Updall –e で更新後 Query Processed: [Subject = '2019/01/26 13:38:08'] 0.Subject = '2019/01/26 13:38:08' NSF document search estimated cost = 100 Prep 0.105 msecs, Exec 4456.598 msecs, ScannedDocs 1000000, Entries 0, FoundDocs 468 デザインカタログ再作成後 Query Processed: [Subject = '2019/01/26 13:38:08'] 0.Subject = '2019/01/26 13:38:08' NSF document search estimated cost = 100 Prep 0.55 msecs, Exec 2796.996 msecs, ScannedDocs 1000000, Entries 0, FoundDocs 468
62. 改善してほしい点  デザインカタログに追加したデータベース（とビュー？）、それらの最終更新日を知 りたい  現状、追加したビューや設計を変更したビューがデザインカタログに反映されていることを 確認できない  デザインカタログの再作成時に updall を実行するべきものがわからない  クエリーにビューを指定しないがビューを使った検索になる場合、使われたビュー名 が知りたい  日時値をISO8601準拠のフォーマットへ変換する関数がほしい @Text( @Now, “ISODateTime”) ※関数式 Format$( Now, “IsoDateTime” ) ※LotusScript NotesDateTime.ISODateTime ※LotusScript
63. スライドの最後