Clever Geek Handbook

SQL ServerからMariaDB / MySQLへの移行。 XMLをどうするか

同僚、大規模なアプリケーションをSQL ServerからMariaDB / MySQLに移行するプロジェクトを支援したソリューションの説明を提供したいと思います。



おそらく、これは同様のプロジェクトの誰かを助けるでしょう。



しなかったこと



MariaDb / MySQLの機能は、たとえば... SQL Server未満ですが、このプロジェクトでは次のような側面を扱う必要はありませんでした。



私たちは何をしましたか







パフォーマンスの問題を解決し、デッドロックの原因を探す必要がありましたが、 Percona Toolkitがそれを助けました。 また、DAC（データアクセスレイヤー）を変更して、アプリケーションがSQL ServerおよびMariaDbと連携するようにします。



主な問題



最大の問題は、プロシージャの変換が多かったという理由だけでした。 NHybernateまたは別のORMに切り替える可能性を真剣に考えた人はいませんでした。手順にロジックはなく、ビジネスレイヤーに転送する人もいませんでした（アプリケーションが.NET上にあると正しく推測しました）。



良かった



プロジェクトの主な実現可能性は、T-SQLの構文とMariaDb / MySQLプロシージャの構文が類似しており、プロシージャを変換するための理論的な自動化の可能性があるという事実に基づいていました。 少なくとも些細なCRUDの場合。



MariaDb / MySQLおよびT-SQLのプロシージャからいくつかの結果セットを返すことができるという事実が特に気に入っています。 OracleやPostgreSQLの開発者がそのような単純な機能を提供しなかった理由には常に悩まされていました。 つまり、プロシージャの最後にある単純なSELECT * FROMユーザー式により、クライアント側のDACで結果が読み取られます。 さらに、テーブル（INSERT INTO ... SELECT）または変数（SELECT ... INTO ...）にリダイレクトされず、プロシージャの最後でなくてもかまいません。



もちろん、以前に書かれた統合テストは役に立ちませんでした。 それらがない場合は、プロジェクト中にそれらを書くことを考える理由があります。 DAC機能テストの対象範囲が広いほど、プロジェクトが成功する可能性が高くなります。



ちなみに、MySQLとMariaDbはWindowsにインストールできますが、Linuxですぐに開発を開始することをお勧めします。 Linuxでは、ツール環境が優れており（同じPercona Toolkitなど）、識別子、より正確にはテーブル名の正しい衝突の問題がすぐに発生します。 たとえば、Linuxでは、クエリSELECT * FROM usersとSELECT * FROM Usersはまったく同じものではありません。 テーブル内のデータはファイル内にあり、Unix / Linuxでは、ファイルシステムは大文字と小文字を区別します。 それでも、当然のことながら、Linuxウイルス内のMariaDbは、「ネイティブ」なWindowsサービスとしてのWindowホストよりも高速です。 どうやら、MySQLはこの優れたデスクトップオペレーティングシステムとは関係ありません。



最も単純なものから始めましょう-XMLをどうするか？



XMLアプリケーションは次のように使用されました。



最初の問題は簡単に解決されますが、XMLデータ型がない場合に聴衆がテンプレートを中断しないようにするために言及する価値があります。 残りの部分については、さらに詳しく説明する価値があります。



XMLパラメーターのデータコレクション



一般に、MariaDb / MySQLには、パラメーターでBLOBとして渡されたXMLドキュメントに対してXPathを実行し、結果をテキストとして返すExtractValue関数があります。 

  SELECT ExtractValue（ '<a> <b>ブラウンシール</ b> </a>'、 '/ a / b / text（）'）


別の質問、値とサイズの特定のコレクションを含むXMLドキュメントが渡された場合、どうすればいいかわかりませんか？ コレクションに含まれるアイテムの数を確認するにはどうすればよいですか？ そして、コレクションの特定の要素を取り出す方法は？



XPathの要素をカウントするために、特定のXPath式に対応するXML要素の数を返すことができるcount（）関数があります。 （http://www.w3schools.com/xpath/xpath_functions.asp） 

  SELECT ExtractValue（ '<a> <b>ブラウン</ b> <b>シール</ b> </a>'、 'count（/ a / b）'）


コレクション内の要素の数を特定したら、XPathを記述して特定の要素を取得できます 

  SELECT ExtractValue（ '<a> <b>ブラウン</ b> <b>シール</ b> </a>'、 '/ a / b [1] / text（）'）


コレクションのすべての要素を取得するループ。 

 CREATE PROCEDURE `sproc1`（p1 LONGTEXT）
開始
 ...
 SET vCount = ExtractValue（p1、 'count（/ ids / id）'）;
 vCount> 0 DOの場合
  INSERT INTO __temptable1__（att1）
         VALUES（ExtractValue（p1、CONCAT（ '/ ids / id ['、CAST（vCount AS CHAR）、 '] / @ att1'）））;
  SET vCount = vCount-1;
終了中;
 ...


ExtractValueを呼び出すたびにXMLドキュメントが解析されるわけではないため、期待に反して非常に迅速に機能します。 この例では、id要素のXML属性を1つだけ取得しますが、もちろん、これらの属性はできるだけ多く使用できます。

XMLを使用して各パラメーターの各プロシージャで何度もWHILEサイクルを記述しないようにするには、一時テーブルを作成し、XMLからデータを入力するプロシージャを記述できます。 ちょうどそれをやった。



このソリューションのマイナス面には、XMLでのデータコレクションのシリアル化/非シリアル化のオーバーヘッドが含まれます。 プロシージャを呼び出す前に一時テーブルにデータを準備し、それを単にプロシージャで使用できます。 ただし、プロシージャコードを見ると、この一時テーブルまたはその一時テーブルがどこから来たのかは明らかではありません。 可読性が悪化します。



SELECT ... FOR XML



T-SQLでこのような構文を置き換える方法は？ 

 SELECT [PropertyName] AS [@Name]、[PropertyValue] AS [@Value] 
 FROM [dbo] .props1 AS [プロパティ] 
どこで...
 FOR XML PATH（N'Property '）、ROOT（N'Properties'）


（https://msdn.microsoft.com/en-us/library/ms178107.aspx）

クエリの結果、次の形式のテキストを含む1つのXMLドキュメントが作成されます。 

  <Properties> <Property Name =” abc” Value =” def” /> ... </ Properties>


つまり、結果セットの集約と、1つの構造化文書への変換です。

MariaDb / MySQLでこれを繰り返すことはできますか？

でき、必要なのは集約関数GROUP_CONCATだけです。 （https://mariadb.com/kb/en/mariadb/group_concat/）

結果セットの文字列を連結できます。 このようなもの： 

  SELECT GROUP_CONCAT（student_name）FROM学生。


結果は、フォームの1行です 
  「Vasya Petya Kolya ...」


しかし、値だけでなく、各行に対して計算された形式の式も連結できます。 
  CONCAT（ '<ユーザー名= "'、u.User、 '" host = "'、u.Host、 '" />'）


例： 

 SELECT CONCAT（ '<ユーザー>'、
	 GROUP_CONCAT（
		 CONCAT（ '<ユーザー名= "'、u.User、 '" host = "'、u.Host、 '" />'）
			 SEPARATOR ''）、 '</ users>'）FROMユーザーu


つまり、結果として何が得られたのでしょうか？ 正しい-XMLドキュメント。

しかし、他の場所と同様に、もちろん、いくつかのニュアンスがあります。





データベース内のXMLドキュメントの変更



データベースのテーブルのフィールドに格納されているXMLドキュメントを変更することです。



通常、この必要性は、データモデルの設計のどこかで間違いを犯し、非自己含有データをXMLに追加した場合に発生します。その後、格納されたXMLドキュメントを調べて変更する必要が定期的に発生します。



しかし、間違いが起こったため、問題は原則として、すべてをやり直すには（正確にするために）費用がかかり、定期的にXMLをバイパスして特定の規則に従ってXMLを変更するには費用がかかるという事実に基づいています。

SQL Serverでは、次のように実行できます。 

 UPDATE user_profile up
 SET Fields.modify（ '（/ fields / field [@ key = sql：variable（ "@ Name"）] / text（））[1] with sql：variable（ "@ Value"）'）
 WHERE Fields.value（ 'data（（/ fields / field [@ key = sql：variable（ "@ Name"）] / text（））[1]）'、 'nvarchar（256）'）= @OldValue


奇妙なことに、迅速ではありませんが、それでもMariaDbで実行できます。 これにはUpdateXml関数があります。

さらに、テーブル内の目的の行を見つけるためにExtractValue関数が必要です。 これを許容可能な時間内で動作させるには、まずそれらを見つけて一時テーブルに入れる必要があります。 

 INSERT INTO __ProfilesToUpdate__（id）
 user_profileからIDを選択
	 WHERE ExtractValue（up.`Fields`、vValueXPath）= vOldValue;
	
 SET vValueXPath = CONCAT（ '/ fields / field [@key = "'、XML_ESCAPE（vName）、 '"] [1] / text（）'）;




そして更新します： 

 UPDATE user_profile up
	 SET up.`Fields` = UpdateXML（up.`Fields`、vReplacementXPath、vReplacementXml）
		 WHERE id IN（SELECT Profile FROM __ProfilesToUpdate__）;
		
ここで、SET vReplacementXPath = CONCAT（ '/ fields / field [@key = "'、XML_ESCAPE（vFieldName）、 '"] [1]'）;
およびSET vReplacementXml = CONCAT（ '<field key = "'、XML_ESCAPE（vFieldName）、 '">'、XML_ESCAPE（pValue）、 '</ field>'）;




全体として、MariaDBのXMLがすべてです。



この記事が役に立つと思われる場合は、SQL ServerからMariaDbへの移行に関連する他の問題と解決策の説明を続けることができます。


More articles:


All Articles