XMLDBの問題 (Dev docs)

提供:MoodleDocs
移動先:案内検索

XMLDBドキュメンテーション > XMLDBの問題


このページの各セクションでは、1.7で予定されている新しいデータベース層の採用に際しての重要な問題を紹介します。各セクションでは、問題点、その影響、そして可能であればその解決策を定義します。各セクションには、そのステータス ('Open', 'Decided', 'Work in progress', 'Finished', 'Tested', 'Closed') を含む 1 行が含まれます。

もちろん、Bug Tracker を使って、各セクションの中に適切なバグを示すリンクを1つ追加することで、これらの問題の日々の進化を自由に追跡することができます。

ステータスを 'Decided' から 'Work in progress' に変更する前に、問題を解決するために必要なすべてのアクションやステップを含む ロードマップ の該当ページを変更しなければなりません。当然ながら、最終的には各セクションのステータスは 'Closed' であることが望ましいです。

正規表現

(ステータス: Open - 重大性: Not Critical - バグ: not defined)

Oracle (10gまで使えない) と MSSQL (使えない) の問題。Oracle 10g ではこれらを直接使って実装されており、古くからあるパッケージ (Oracle 8i?) で対応可能 (owa_pattern) です。MSSQLは、いくつかの ストアドプロシージャ をインストールすることで、それらを実行することができます。一部の(いくつかの)クエリに問題が...。

SQLリミットのパフォーマンス

(ステータス: Open - 重大性: Not critical - バグ: not defined)

SQL*Server や Oracle で SelectLimit() を呼び出した場合の影響を分析してください。オフセットパラメータが大きくなると、ADOdb は望ましい ウィンドウ を得るためにより多くのレコードを繰り返し処理しなければなりませんが、これは長いレコードセットの下では問題になるかもしれません!

命名規則

(ステータス: Closed - 重大性: Critical - バグ: not defined)

以前に作成したDBオブジェクト(インデックス、ユニークインデックス、シーケンス...)の命名スキーマがADOdbによって実装されたものと一致しない場合、それらをどうすればよいでしょうか。すべて削除・再作成?変更せずにそのまま?各DBで異なる命名規則が使えるか?PostgreSQL の方が正式なようですが、Oracle や SQL*Server もそうなのでしょうか。また、すべてのインデックスを削除して、XMLDBの命名ページ で指定されている正確な命名規則に従って再生成できるように、Health Center にいくつかのオプションを追加することを提案します。

命名規則 II

(ステータス: Closed - 重大性: Critical - バグ: [https://tracker.moodle.org/browse/MDL-6230 MDL-6230])

OracleはDBオブジェクトの命名に30ccの制限(テーブル、フィールド、インデックス...)を課しており、現在この制限を超えるオブジェクトが存在します: mdl_glossary_entries_categories と mdl_question_dataset_definitions があります。早急に他の名前に変更する必要があります。代替案は存在しません。また、一旦名前を変更したら、望ましくない状況を避けるために、少なくとも新規インストール時には $CFG->prefix の最大長を5ccに制限し、テーブル名を25ccに制限すべきです(prefixを含まない場合)。7.4以前の PostgreSQL の制限もかなり近いようです(32cc)。

解決方法: テーブル/フィールド/インデックス/キー/シーケンス名を30ccに制限し、Oracle インストレーションでは2ccを超える接頭辞の使用を拒否します。XMLDBのコンストラクタがDB内の残りのオブジェクトにどのように名前を付けるかについては、キーおよびインデックスの命名を参照してください。

DEFAULT '' 節を使用した NOT NULL フィールド

(ステータス: Open - 重大性: Critical - バグ: [https://tracker.moodle.org/browse/MDL-6218 MDL-6218])

Moodle 1.6 では、NOT NULL として定義され、値 '' (空文字列) を持つ1つの DEFAULT 句を含む、大量の char/varchar/text/blob カラムが存在します。

この種の定義には、2つの重要な問題があります:

  1. デザイン的にもあまり意味がありません。あるフィールドが NOT NULL と定義されている場合、空の値を含んではいけません (しかし、NULL != everything が空であることには同意しますが、論理的な世界では、フィールドを空の値で埋める理由は見つかりません)。
  2. Oracle は空の文字列を NULL 値と見なし、その RDBMS では DEFAULT アプリケーションがクラッシュします。 (http://www.techonthenet.com/oracle/questions/empty_null.php)

この状況を解決する適切な方法は、DBスキーマ全体からそれらの矛盾/間違った組み合わせをすべて消滅させること (NOT NULL + DEFAULT '') ですが、現在 (Moodle 1.6-1.7) それを行うのは本当に困難でリスクが高い場合があります。そこで、解決策を提案します:

  1. 1.6を一切修正しないでください。すべて正常に動作し続けるはずです。
  2. XMLDBファイルでは、1.7では正しいアプローチを使用します。つまり、これらのフィールドはXML構造ではデフォルト値を持ちませんが、そこではNOT NULLとして継続されます。
  3. MySQL、PostgreSQL、MSSQL の XMLDBジェネレーター(テーブル作成)で、XML構造でDEFAULTが定義されていないすべての文字列/テキスト列に対してDEFAULT句を追加します。
  4. インストールスクリプトに、問題のあるMySQLモードを探すためのチェックを追加してください。

このアプローチでは、1.7 で MySQL と PostgreSQL サーバがそのまま動作し、MSSQL も同じように動作し、Oracle バージョンも、多くの場所で動作しないことは確かですが、DBの観点からはより正しいものになるでしょう。また、Oracle が全く使えない場合は、それらのフィールドを全て NULL として直接宣言するか (それに対して +1)、1つの空白のようなダミーのデフォルト値を追加するかの両方で、少しごまかすことが可能です。これらは完璧な解決策ではありませんが、Oracle の体験を少し改善するはずです。ただし、1.7 ではアルファベータ品質にとどまるでしょう (NULL/NOT NULL/DEFAULT 問題を解決するには、すべてのコードであまりにも多くの変更が必要で、1.7の時間は本当に短くなっています)。

1.7以降では、MySQL、PostgreSQL、MSSQL の実装を、同じ (正しい) フィールド定義 (nullable カラム) を使用するように変更し、すべての変更/テスト/修正に時間をかけ、NOT NULLのものを、本当に nullable なら NULL へ徐々に移行し、空のデフォルトをすべて削除することを始めるべきです。

更新: 1.7 では、ロジックであるためデータを含まないことができる TEXT フィールドの NOT NULL から NULL への移行を開始しましたが、VARCHAR フィールドは上記の通り維持します。これにより、Moodleのコード全体におけるこのような変更の影響を実際に知ることができ、また、より良い (実際の) DBスキーマを作成することができます。1.7以降では、残りのすべての VARCHAR フィールドも同じ処理を行い、アプリケーションロジックが内容を持たないと判断した場合は NOT NULL から NULL に変換される必要があります。そうすると、空の () デフォルトがすべて出てきます。間違いなく!Oracle では、すべての NOT NULL、デフォルトの フィールドに 1 つの空白を挿入/更新し、すべての get_xxx() DML 関数でそれらを元に戻しています (これはコード内で Oracle dirty hack と呼ばれているものです)。うまくいけば、いくつかのリリースで出てくるかもしれません。

NOT NULL から NULL に変換された TEXT フィールドのリストです (1.6 --> 1.7):

  • course->modinfo
  • course_categories->description
  • course_sections->summary
  • course_sections->sequence
  • data->XXXtemplate
  • data_fields->paramXXX
  • data_content->contentXXX
  • user->description

NOT NULL から NULL に変換された TEXT フィールドのリストです (1.7 --> 1.8):

TEXT カラムのインデックス

(ステータス: Closed - 重大性: Not Critical - バグ: not defined)

各 RDBMS 実装は、TEXT 列に対して FULLTEXT インデックスを作成するために、全く異なるメカニズムを持っています。また、そのような列に対して検索を実行するための SQL コマンドもかなり異なっています (互換性がありません)。このため、今のところTEXT列のインデックス作成は避けざるを得ません。XMLDB スキーマへの移行に伴い、このようなインデックス作成を避けるために変更したフィールドのリストを以下に示します:

  • hotpot_questions->name
  • hotpot_strings->string

また、FULLTEXT インデックス (あらゆるテキストコンテンツを自由にインデックス化し検索する) と FUNCTION インデックス (ある関数の実行結果のみをインデックス化する) を区別することも重要です。この最後のインデックスは TEXT インデックスの適切な代替物ではなく、インデックスの定義に使用された 'EXACT 関数呼び出しが SQL クエリで使用されない場合は、全く使用されません。

長期的には、XMLDB のスキーマを拡張して、この種のインデックスをサポートし、より優れた全文検索を実現する必要があります。これを書いている時点では、グローバル検索プロジェクトという学生プロジェクトの一部で行われている取り組みがあります。

最後に、多くの RDBMS は、すべての FULLTEXT インデックスをオンラインで更新し続けることができないので、"外部" エージェント (ストアドプロシージャ、コマンドライン実行ファイル、cronタスクなど) によって継続的に同期/最適化する必要があることに注意してください。

更新: 現在 Moodle の下に存在するすべてのインデックスは、HotPot モジュールに存在していました。作者 (Gordon Bateson) とのいくつかの興味深いメールの後、彼はこの問題を救うためにモジュールに回避策を実装するつもりでいるようです。これには、元のテキストからいくつかの md5() ハッシュを持つ新しいカラム (VARCHAR 型、index-able) を作成することが含まれます。このアプローチにより、モジュールはインデックスされたフィールド (ハッシュそのもの) を使ってハッシュの比較を行うことができるようになります。この方法は、最適化された方法で検索を実行するには適していないことに注意してください。そのためには、上で説明したように、FULLTEXT インデックスが必要です。

FULLTEXT インデックス/検索/維持に関する興味深いリンクがいくつかあります:

ORDER BY テキストカラム

(ステータス: Closed - 重大性: Not Critical - バグ: not defined)

すべての RDBMS は、任意の TEXT フィールドを持つ ORDER BY 句を使用して SELECT 文をサポートしていません。この問題を解決するために、次の方法を使用します:

  1. TEXT カラムによるクエリの順序付けは避けてください。
  2. もし、前のものが不可能で、任意のテキストカラムで並べる必要がある場合は、sql_order_by_text($columname) を使用します。クエリを実行し、結果を取得するために必要な SQL コードを作成します。

MSSQL, PHP, UTF-8 および UCS-2

(ステータス: Closed - 重大性: Critical - バグ: [https://tracker.moodle.org/browse/MDL-6877 MDL-6877])

最近の RDBMS は、複数のエンコーディングをサポートしており、それらのエンコーディングで情報を保存したり、クライアントとサーバ間でデータを自動変換して処理したりすることができます。より正確には、これらすべてが、実質的にすべてのウェブアプリケーション、モバイルウィジェット、およびあらゆる種類のデバイスで現在使用されている UTF-8 エンコーディングをネイティブでサポートしています。

しかし、SQL*Server には 注目すべき例外が一つ あります。SQL*Server は、UCS-2 エンコーディング (別の Unicode 形式) を排他的にサポートし、特別な nchar カラムに対して使用することができます。さらに悪いことに、UCS-2 と希望のエンコーディング(Moodle では UTF-8)の変換を透過的に行うための接続ソリューション(ODBC、OLE、COM...)の中で、マイクロソフトは第3者のクライアントに対して信頼できるソリューションを提供しません

マイクロソフトが 提案した 解決策はこちらで見ることができます: http://support.microsoft.com/kb/232580/EN-US/ :-P

その記事で提供された解決策は、一部の人々には役立つかもしれませんが、Moodle には適していません。私たちは PHP を使用し、UTF-8 データをサーバと送受信し、サーバ側で適切に (UCS-2で) 保存し、適切に処理できるようにする必要があります。

ネットで ググった 結果、問題の根源は DBLIB というライブラリ(dll ファイルには発音できない名前がある)にあるようです。このライブラリは、実質的にすべての DB クライアントがそのインターフェイスを構築するために使用しています。PHP などは、SQL*Server データベースにアクセスする独自のモジュールを構築するために、このライブラリに依存しています。 また、このベースライブラリは UTF-8 をサポートしていません(少なくとも UTF-8 との変換を提供していない)。

このような考えから、代替案の研究が始まりました。どのようなソリューションであっても、次のような特徴を備えていなければなりません:

  • UTF-8 (PHP と Moodle) から UCS-2 (SQL*Server) に 透過的に変換 し、それぞれの側ですべてが適切に保存される。
  • PHP 4.3.x 以降で動作すること (Moodle では最小要件) が明らかである。
  • PHP のデータベース抽象化ライブラリである ADOdb に対応している
  • Unix 環境と Windows 環境の 両方に対応 している。
  • 可能であれば、自動変換に対応していない 標準の php_mssql拡張モジュール を置き換えることができるようにする。ADOdb の mssql サポートはかなり良い (COM や OLE などの他の選択肢よりも良い) ので、これは素晴らしいと思います。

そして、ついに、それらは存在するのです!我々は、上記の要件をすべて満たす2つの製品 (もちろん、オープンソース) を発見しました。どちらも UTF-8 と UCS-2 の自動変換を行い、標準の php_mssql ライブラリを置き換えるので、 サポートの充実した mssql ADOdb ドライバを引き続き使用でき、 クロスプラットフォームにも対応しています。それ以上のことはここにあります:

  • FreeTDS: この製品は、SQL*Server と Sybase の両方をサポートする Tabular Data Protocol に基づいている100%クライアント製品です。Linux と MacOS X の両方で問題なくコンパイルして使用しており、最小限の設定をするだけで、標準の php_mssql 拡張をスムーズに置き換えることができます。ドキュメントによると、Win32 システムでも動作するようですが、私たちは試していません。インターネット上では、信頼できるソリューションとして多くの報告がなされています。
  • ODBTP: これも Open Database Transport Protocol というプロトコルで、あらゆる Win32 DB サーバに仮想的に接続することができます。1台のWin32マシンに1つのクライアントインストールと1つのサービス (ODBC 経由でクライアントと DB 間のブリッジとして機能) の両方をインストールする必要があります。クライアント部分は Linux および MacOS X で使用可能で (未テスト)、標準の php_mssql 拡張モジュールを置き換えるものも実装しています。サービス部分は、明らかに Win32 でのみ使用可能です。最小限の設定をすれば完璧に動作し、重大なミッションにも対応できる信頼性の高い製品であると発表しています。

数ヶ月間、両方の選択肢を比較・監視した結果、2番目の選択肢である ODBTP の方が若干信頼性が高いようです。これは主に、ODBTP の方がサポート (フォーラムやバグ) が良いことと、MS が異なるリリース間でTDSサポート (バージョン) を継続的に変えていることが理由です。

PHP を MS SQL に接続する方法の詳細については MSSQL用PHPのインストール を参照してください。

Reopened: TDSODBTP も UCS-2 に自動変換され、テキストフィールドはすべて "Nxxx" と定義されているのに、SQL*Server エンジンは独自に変換してデータを保存しているようです。詳しくは [https://tracker.moodle.org/browse/MDL-6877 MDL-6877] をご覧ください。

Reclosed: ADOdbのmssqlドライバ (mssql_n) を一つ "自作" してみました。これは標準的なものを単純に拡張したもので、DBに送られる前にすべてのクエリを解析し、文中に存在する SQL リテラルの前に悪名高い "N" を付加しようとするものです。よって、他の解決策 (ドライバやDBでのネイティブな UTF-8 サポート、ドライバでの透過的な変換...) が登場するまでは、FreeTDS や ODBTP の上にこの新しい ADOdb ドライバを使うことになるでしょう。次のリリースではどこでも プリペアドステートメント に移行するつもりで、これらが "N" 問題を回避するようであればなおさらです。 また、別の接続の選択肢 (PDO...) が、入ってくるバージョンを分析することになりますが、それはまた別の話です。

Moodle DB の予約語

(ステータス: Done! - 重大性: Critical - バグ: [https://tracker.moodle.org/browse/MDL-6307 MDL-6307])

Moodle 1.6 で使用される DB 名のいくつかは、予約語 (DBオブジェクトの識別子として使用することが禁止されている単語) です。現在検出されている予約語のリストは、以下の通りです:

  • assignment_submissions->comment (oracle) Done! (assignment_submissions->submissioncomment)
  • forum->open (mssql) Done! (this field is out in 1.7)
  • glossary_comments->comment (oracle) Done! (glossary_comments->entrycomment)
  • journal_entries->comment (oracle) Done! (journal_entries->entrycomment)
  • question_dataset_items->number (oracle) Done! (question_dataset_items->itemnumber)
  • question_sessions->comment (oracle) Done! (question_sessions->manualcomment)
  • timezone->rule (mssql) Done! (timezone->tzrule)
  • resource table (oci8po)
  • user table (mssql, oracle, postgres)

(the format of the list is table->field (reserved on DB)

なお、予約語はテーブルに接頭辞を使用している場合は危険ではありません (したがって、Moodle 1.7 で新規インストール時は接頭辞の使用を強制し、Oracle は1〜2cc、それ以外は1〜10ccにすべきです) 。

ADOdb フェッチモード (ASSOC, NUM および BOTH)

(ステータス: Finished - 重大性: Critical - バグ: not defined)

デフォルトでは、ADOdb を FETCH_BOTH モードで使用します。これは、数値キー (0, 1, 2...) とリテラルキー (フィールド名) の両方を持つ 混合 レコードセットを返すものです。そして、そのようなレコードセットから連想配列を取得します (すべての get_records_XXX 関数に対して) 。一つの例を見てみましょう:

3つのカラム (id, name, address) を持つテーブルを "people" と呼び、次のようなデータを持つとします:

   666, 'Bill', 'Medina
   777, 'Steve', 'Palo Alto'

そこで、次のような簡単なクエリを1つ用意します:

 SELECT id, name, address
 FROM people;

この構造を持つレコードセットをPHPで返します:

 array (
     [0] => stdClass Object (
         [0] = 666
         [id] = 666
         [1] = Bill
         [name] = Bill
         [2] = Medina
         [address] = Medina}
     [1] => stdClass Object (
         [0] = 777
         [id] = 777
         [1] = Steve
         [name] = Steve
         [2] = Palo Alto
         [address] = Palo Alto)
 )

そして、連想配列を取得すると、次のように変換されます:

 array (
     [666] => stdClass Object (
         [id] = 666
         [1] = Bill
         [name] = Bill
         [2] = Medina
         [address] = Medina}
     [777] => stdClass Object (
         [id] = 777
         [1] = Steve
         [name] = Steve
         [2] = Palo Alto
         [address] = Palo Alto)
 )

つまり、元のレコードセットの最初の属性が、最終的な配列のキーを作るために使われ、配列自体からは消えてしまうのです。上の例では、レコードセットの最初のカラムはインデックス [0] でした。その値 (666, 777) は、連想配列のキーとして使用されていたため、そこから消えてしまいました。

私たちは、Moodle内部で連想配列を扱うために、このメカニズムを使用しています。キーと [id] 要素の両方が同じ値を指している場合、両方のフィールドを扱うことが可能です。しかし、この仕掛けには2つの大きな欠点があります:

  1. すべてのデータは2回送信され、保存されます。元のレコードセットを見ると、[1], [2]... のインデックスを全く使っていないのに、すべてのフィールドのデータが重複していることがわかります。
  2. 今までは、MySQL でも PostgreSQL でも、数値インデックスを先に取得し、次に名前付きインデックスを取得していましたが、一部のドライバではこの順序を逆転させ、名前付きインデックスを先に取得し、次に数値インデックスを取得するようにしました。そして、これは get_records_XXX() 関数によって返される最終的な構造体に 非常に悪い結果 をもたらし、すべてのMoodleで使用される [id] キーを完全に失いました。

そこで、このような解決策を提案します:

  • Done!: (2) を早急に解決するために、GetAssoc() を呼び出す前に、レコードセットの各レコードの最初のフィールドを複製することにします。これにより、何も失われることなく、連想配列のキーとなるフィールドがレコードに残り続けることが保証されます。
  • Done!: また、ODBTPドライバは、フィールドを正しい順序で返すように "修正" されました。Bob に感謝します。
  • Done!: 1.7以降では、(1) を解決するために、FETCH_BOTH から FETCH_ASSOC にアプローチを変更し、ちょうど半分のデータを取得/処理する必要があります。数字キーを使っている場所はすべて更新されます (実質的には存在しないはずですが) 。先ほどのハックによって、名前付き フィールドのみを扱うことができるようになり、大切な最初のフィールド (id) は連想配列の構築後も維持されるようになりました。
  • Done!: 更新: これは Eloy と Luke によって実行され (コミットされてはいない) 、うまくいっています。さらに、このパッチは小さく、dmlib.php にのみ触れています。私たちは CVS HEAD (for 1.7) で変更を実装し、それは徹底的にテストされます -- 一連の変更の一部として。もし、それが問題であることがわかったら、簡単に元に戻せます。

Oracle, PHP 5.1.x および LOBs

(ステータス: Closed - 重大性: Critical - バグ: not defined)

Oracle で動作している場合、CLOB/BLOB データを持つフィールドを処理することはできないようです。LOB カラムにデータを送ることは可能ですが、そこからデータを取り出すことができません そしてPHPスクリプトはタイムアウトで終了します。

プロセス: ADOdb の内部で時間をかけて問題を追跡した結果、OCI 関数によって生み出されたようです: ocifetchinto()OCI_RETURN_LOBS 設定で使用すると、LOB カラムの内容を 1 回で取得することができます。ociloadlob(), OCI-Lob->read(), OCI-Lob->load() などの代替手段を試しましたが、すべてクラッシュしてしまいました。最終的に 1つのバグを発見 し、PHPでドキュメントを作成しました。つまり、Oracleを使用する場合は、PHP <= 5.1.4 を完全に回避することが解決策となるようです。今試しているところです。

悪いニュースですが、5.1.6 では解決しないようです :-(現在、4.3.11 でテスト中です。また、PHP 5.1.x の解決策を得るために、こちらのPHPのバグ を埋めました。)

その他のニュース: Oracle LOB は PHP 5.2.x (これを書いている時点ではリリース候補) で動作するようになったようです。5.1.x 系にもバックポートしてくれるといいのですが。http://bugs.php.net/bug.php?id=38612 にご期待ください。

解決策: PHP 4.3.x, 4.4.x あるいは PHP 5.2 の標準配布物を使用する、あるいは PHP 5.1.x を oci8-1.2.2 以降 (http://pecl.php.net/package/oci8 からダウンロードするか PEAR/PECL で動的にインストールします) でビルドする。

大文字・小文字を区別しない検索

(ステータス: Open - 重大性: NotCritical - バグ: not defined)

各 RDBMS では、"=" 演算子や "LIKE" 演算子を使った検索方法に重要な違いがあります。MySQLMSSQL は大文字と小文字を区別しない検索を行い、PostgreSQLOracle は大文字と小文字を区別した検索を行います。

PostgreSQL では、"ILIKE" 演算子が使用でき、Moodle では古くから使用されています。 Oracle にとっては、Oracle 10gR2 以前に信頼できるソリューションが利用できないことが大きな問題です。もし、このようなバージョンを Moodle の最低要件とするならば、regexp_like() 関数に i (case-insensitive) スイッチ、または検索を大文字と小文字を区別しないようにする NLS_COMP=LINGUISTIC の設定の両方を使うことができます。

つまり、Oracle の min reqs. を 10gR2 まで上げ、上記の解決策のいずれかを使用することです (2番目の解決策がよりシンプルで透明性が高いです) 。

テーブルとカラムのエイリアス - ASキーワード

(ステータス: Closed - 重大性: Critical - バグ: not defined)

各 DB では、AS キーワードを使用したテーブルやカラムのエイリアスの使用方法について、独自の方法を採用しています。これは要約です:

  • MySQL: テーブルで AS をサポートし、フィールドで AS をサポートします。
  • PostgreSQL: テーブルで AS をサポートし、フィールドでは 必要な AS をサポートします。
  • Oracle: テーブルでの AS を禁止し、フィールドでの AS をサポートします。
  • MSSQL: テーブルで AS をサポートし、フィールドで AS をサポートします。

つまり、この情報では、唯一のクロス DB 戦略は:

  • テーブルエイリアス 全てに AS キーワードを 使わないで ください。
  • AS キーワードをすべての カラムエイリアス使用します

他の解決策としては、いくつかの定数を使用して必要に応じてキーワードを適用することもできますが、そうすると多くの SQL 文がひどい目にあうことになります。他の RDBMS が以前の戦略を壊さない限りは、それが適用されるでしょう。

また、上記の前提を Moodle のコードに細かく適用するために、big search and destroy が必要です。間違った使い方をすることはあまりないはずです。

関連項目