Geekly Articles each Day

ストーブを離れずにORMを調理します。 SQLの生成-バイナリ式ツリーに基づいたクエリ

画像

この記事は前半の続きです。 投稿では、式のバイナリツリーの形式の型のオブジェクトモデルと、SQLクエリのパラメーター化とリフレクション最適化の関連トピックを使用して、SQLクエリを構築することを検討します。 この記事のトピックはそれ自体非常に分離されているため、特に最初の部分に注意を払うことなく読むことができます。 繰り返しになりますが、この決定は「夕方に行われました-何もすることはありませんでした」ことであり、工業製品の栄冠を主張していません。

いくつかの歌詞または動的SQLパラメーター化について


一般に、動的SQLは、ストアドプロシージャとして実装されていないDBMSを実行するためにクライアントから送信されるスクリプトです。 DBMSは、EXEC（）およびsp_executesql命令を使用してこのようなスクリプトを実行します。

一部では予期しないように見えるかもしれませんが、6番目のバージョン以降、SQL Serverは動的クエリをキャッシュできます。 ただし、すべてがそれほど単純ではありません。 ストアドプロシージャのキャッシュでの検索中、SQL Serverはその名前をキーとして使用します。動的SQLの場合、名前は使用できないため、SQLは検索キーの形式でパラメーターを含むすべてのクエリテキストを使用します。 はい、絶対にリクエストの全文、カール！ スペースを使用し、大文字と小文字を区別せず、コメント付き。

さらに、サーバーは、その設計に従ってキャッシュ内の要求を検索します。 したがって、スキームに従ってテーブルの完全な名前を指定することが非常に重要です。

パラメータ化されていないリクエスト
//       SQL Server    -  cmd.CommandText = "SELECT mycol FROM product WHERE col = " + value.ToString(); cmd.CommandText = "SELECT mycol FROM Product WHERE col = " + value.ToString();


パラメータ化されたクエリ
 cmd.CommandText = "SELECT mycol FROM dbo.product WHERE col = @value"; cmd.Parameters.Add("@value", SqlDbType.Int); cmd.Parameters["@value"].Value = value;


プロジェクトに多数のパラメータ化されていないクエリがプロジェクト全体に散在している場合、1つの関数へのカプセル化を使用したリファクタリングを検討する必要があります。 ORMを使用すると、このような問題を解決できます（手動SQLを使用するORMの場合、カプセル化を自分で行う必要があります）。

パラメータ化されていないクエリには、SQLインジェクションなどの多くの副作用が伴うことを忘れないでください。 動的SQLキャッシングの問題については、 こちらをご覧ください 。

何が欲しい？


式ツリーに基づいてデータを取得するためのリポジトリメソッドを定義します。 クライアントは次のようになります：

  var repo = new ProfileRepository(); var profiles = repo.Get(x => x.Id == id && x.Rating > rate)

式ツリーベースのSQL生成には、次の利点があります。

  • ユーザーがデータベース内の列の文字列名を思い出す必要がなくなります
  • ユーザーが間違った列名を指定すると、別のレジスタで例外が発生したり、スペースを含む列名が指定されたりするため、SQL Server側でキャッシュの問題が発生する
  • フィルタリング条件はコンポジットにすることができます。これにより、.NET Expressionクラスを実装できます

欠点は、バイナリツリーを再帰的に走査するSQL生成の複雑さとパフォーマンスです。

少し反映する


オブジェクトのマッピング中に、可能であればスローリフレクションメカニズムを避けながら、プロパティと属性を動的に取得する必要があります。 Richterは値の取得とデリゲートに基づいたプロパティの値の設定のパフォーマンスを最適化する方法について詳しく説明しています。ここでは詳しく説明しませんが、すぐにPropertyInfoクラスのラッパーを実装します。

型プロパティを使用した動的な作業には、次のものが必要です。

  • プロパティメソッド
  • プロパティ設定方法
  • 物件名
  • 物件タイプ
  • ビジネスオブジェクトのプロパティをテーブルのフィールドにバインドするための属性

クラスPropWrapper
 public class PropWrapper { private readonly PropertyInfo _property; public Type Type { get { return _property.PropertyType; } } public string Name { get { return _property.Name; } } //  -   - public ICollection<RelatedEntityAttribute> RelatedEntityAttributes { get { return _property.GetCustomAttributes<RelatedEntityAttribute>().ToList(); } } //  -     public ICollection<FieldNameAttribute> FieldNameAttributes { get { return _property.GetCustomAttributes<FieldNameAttribute>().ToList(); } } //   Gettr .  -   public Func<object, object> GetterMethod { get { return GetGetterMethod(); } } //   Settr .  -   public Action<object, object> SetterMethod { get { return GetSetterMethod(); } } public PropWrapper(PropertyInfo prop) { _property = prop; } private Func<object, object> GetGetterMethod() { if (_property == null) throw new ArgumentNullException("property"); var getter = _property.GetGetMethod(); if (getter == null) throw new ArgumentException("The specified property does not have a public accessor."); var genericMethod = typeof (PropMethodsHelper).GetMethod("CreateGetterGeneric"); var r = _property.GetCustomAttributes<FieldNameAttribute>(); MethodInfo genericHelper = genericMethod.MakeGenericMethod(_property.DeclaringType, _property.PropertyType); return (Func<object, object>) genericHelper.Invoke(null, new object[] {getter}); } private static Func<object, object> CreateGetterGeneric<T, R>(MethodInfo getter) where T : class { Func<T, R> getterTypedDelegate = (Func<T, R>) Delegate.CreateDelegate(typeof (Func<T, R>), getter); Func<object, object> getterDelegate = (Func<object, object>) ((object instance) => getterTypedDelegate((T) instance)); return getterDelegate; } private Action<object, object> GetSetterMethod() { if (_property == null) throw new ArgumentNullException("property"); var setter = _property.GetSetMethod(); if (setter == null) throw new ArgumentException("The specified property does not have a public setter."); var genericMethod = typeof (PropMethodsHelper).GetMethod("CreateSetterGeneric"); MethodInfo genericHelper = genericMethod.MakeGenericMethod(_property.DeclaringType, _property.PropertyType); return (Action<object, object>) genericHelper.Invoke(null, new object[] {setter}); } private static Action<object, object> CreateSetterGeneric<T, V>(MethodInfo setter) where T : class { Action<T, V> setterTypedDelegate = (Action<T, V>) Delegate.CreateDelegate(typeof (Action<T, V>), setter); Action<object, object> setterDelegate = (Action<object, object>) ((object instance, object value) => { setterTypedDelegate((T) instance, (V) value); }); return setterDelegate; } }


次に、カプセル化クラス型全体を実装します。 属性、プロパティ名、プロパティタイプなどは、クラスの特定のインスタンスではなく、タイプのみに依存することに注意してください。 したがって、型構造をすぐにキャッシュすると便利です。

CacheTypeReflectionWrapperクラスの実装
 internal static class CacheTypeReflectionWrapper { private static readonly Dictionary<Type, ICollection<PropWrapper>> TypesByProp = new Dictionary<Type, ICollection<PropWrapper>>(); public static ICollection<PropWrapper> GetProps(Type type) { //      if (!TypesByProp.ContainsKey(type)) { var props = type.GetProperties(); var propWrappers = props.Select(propertyInfo => new PropWrapper(propertyInfo)).ToList(); TypesByProp.Add(type, propWrappers); } return TypesByProp[type]; } }



メインコースの調理


最後に、オブジェクトモデルからSQLマッパーの準備を開始できます。 LINQ.NETプロバイダーを使用してソリューションを実装できることにすぐに気付きましたが、まだ実装していません。

オブジェクトのフィールドにより、選択リクエストの本文を計算します。 データベーススキームに従って、フルネームを取得するためにビジネスオブジェクトのタイプとデータベースの名前が必要なのはなぜですか。

CreateBodyメソッド
  private static string CreateBody(string dbName, Type type) { //    ,     - var tableName = CommonCommandBuilder.GetTableName(type); var cmdBulder = new StringBuilder(); //    ,          foreach (var prop in CacheTypeReflectionWrapper.GetProps(type).Where(x => x.FieldNameAttributes.Any())) { var attrs = prop.FieldNameAttributes; //             cmdBulder.Append(string.Format("[{0}].[{1}],", tableName, attrs.First().Value)); } return string.Format("SELECT {0} FROM [{1}].[dbo].[{2}] ", cmdBulder.ToString().Trim(','), dbName, tableName); }


これで楽しい部分が始まります。-単語WHEREの後のSQL条件の生成。 .NETで式ツリーを簡単にたどるために、 ExpressionVisitorクラスがあります。 しかし、自転車を作るなら、最大限に！ そのため、箱から出してすぐに資金なしで行うことができます。
式の分析は、 バイナリ式ツリーに基づいて実行されます 。
バイナリ式ツリーは、式を表すために使用される特定のタイプのバイナリツリーです。 式の二分木は、代数的および論理的な値（単項演算子および二項演算子）にすることができます。 バイナリツリーの各ノード、したがって表現のバイナリツリーには、0、1、または2つの子があります。

式ツリーには、さまざまなタイプの頂点を含めることができます。直接BinaryExpression、
MemberExpression、ConstantExpression、UnaryExpressionなど。

リーフに到達することが重要です。リーフの場合、タイプはMemberExpression、ConstantExpressionです。 MemberExpressionタイプの頂点にはフィールドが含まれており、条件オペランドは頂点のタイプによって取得できます。 ConstantExpression型の頂点には、オペランドの値が直接含まれます。

式の形式を次のようにします。

 repo.Get(x => x.RoleId == 2 && x.UserInfoId > 4 && x.Id < 6)

わかりやすくするために、この場合のバイナリ式ツリーの図を示します。 頂点の値は、トラバーサルアルゴリズム中にデバッガから取得されます。

画像

図では、フィールド{x.UserInfoId}はNULL入力可能なタイプであるため、このような頂点はUnaryExpressionです。 UnaryExpressionなどの頂点には、左と右の2つの子が含まれていません。 この場合、ConstantExpression型への変換により、オペランドの値を取得できます。

式ツリーにSQL条件の構築を実装するための機能コード、詳細なコメント付き：

 //     IDbCommand,       ,    SQL public static string BuildClauseByExpression(IDbCommand command, Type type, BinaryExpression exp) { var strBuilder = new StringBuilder(); //     return BuildClauseByNode(command, type, exp, strBuilder); } //     private static string BuildClauseByNode(IDbCommand command, Type type, BinaryExpression left, StringBuilder strBuilder) { var tableName = GetTableName(type); if (left != null) { var parameter = command.CreateParameter(); var fieldName = string.Empty; var expField = left.Left as MemberExpression; if (expField == null) { if (left.Left is BinaryExpression) { //   Binary -   BuildClauseByNode(command, type, left.Left as BinaryExpression, strBuilder); //ExpressionTypeToDbClause  ,  - ExpressionType,     SQL :_instance[ExpressionType.AndAlso] = " AND " strBuilder.Append(ExpressionTypeToDbClause.Instance[left.NodeType]); } } else { //   Member -       SQL  var name = expField.Member.Name; var prop = CacheTypeReflectionWrapper.GetProps(type) .Where(x => x.FieldNameAttributes.Any()).First(x => x.Name.Equals(name)); var attrs = prop.FieldNameAttributes; fieldName = attrs.First().Value; strBuilder.Append(string.Format("[{0}].[{1}]", tableName, fieldName)); //ExpressionTypeToDbClause  ,  - ExpressionType,     SQL :_instance[ExpressionType.AndAlso] = " AND " var action = ExpressionTypeToDbClause.Instance[left.NodeType]; strBuilder.Append(action); //TypeMap      c#     parameter.DbType = TypeMap[prop.Type]; } var expValue = left.Right as ConstantExpression; if (expValue == null) { var unaryNode = left.Right as UnaryExpression; if (unaryNode != null) { //   UnaryExpression    Operand    //ConstantExpression expValue = unaryNode.Operand as ConstantExpression; if (expValue != null) { //     SQL- InitParams(command, strBuilder, fieldName, parameter, expValue); } } if (expValue == null) { if (left.Right is BinaryExpression) { //   Binary -   BuildClauseByNode(command, type, left.Right as BinaryExpression, strBuilder); } } } else { InitParams(command, strBuilder, fieldName, parameter, expValue); } } return strBuilder.ToString(); } //    SQL- private static void InitParams(IDbCommand command, StringBuilder strBuilder, string fieldName, IDataParameter parameter, ConstantExpression expValue) { var valueFormat = GetParamsFormat(fieldName); strBuilder.Append(valueFormat); parameter.ParameterName = valueFormat; parameter.Value = expValue.Value; if (!command.Parameters.Contains(parameter.ParameterName)) command.Parameters.Add(parameter); } //      SQL- public static string GetParamsFormat(string fieldName) { return string.Format("@{0}", fieldName); }

その結果、身体と魂をクエリ条件と組み合わせて、次の関数を取得します。

  public static string Create<T>(IDbCommand command, BinaryExpression exp) where T : class, IEntity, new() { var type = typeof(T); var selectBody = CreateBody(command.Connection.Database, type); return string.Format("{0} WHERE {1}", selectBody, CommonCommandBuilder.BuildClauseByExpression(command, type, exp)); }

実装の詳細はすべてgithubで確認できます。

Source: https://habr.com/ru/post/J319422/

More articles:


All Articles