mcs/class/System.Data.Linq/src/DbLinq/Data/Linq/Sugar/Implementation/QueryBuilder.Upsert.cs

   1 #region MIT license\r
   2 // \r
   3 // MIT license\r
   4 //\r
   5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne\r
   6 // \r
   7 // Permission is hereby granted, free of charge, to any person obtaining a copy\r
   8 // of this software and associated documentation files (the "Software"), to deal\r
   9 // in the Software without restriction, including without limitation the rights\r
  10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell\r
  11 // copies of the Software, and to permit persons to whom the Software is\r
  12 // furnished to do so, subject to the following conditions:\r
  13 // \r
  14 // The above copyright notice and this permission notice shall be included in\r
  15 // all copies or substantial portions of the Software.\r
  16 // \r
  17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\r
  18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\r
  19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\r
  20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\r
  21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\r
  22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN\r
  23 // THE SOFTWARE.\r
  24 // \r
  25 #endregion\r
  26 \r
  27 using System;\r
  28 using System.Collections.Generic;\r
  29 using System.Data.Linq.Mapping;\r
  30 using System.Linq.Expressions;\r
  31 using System.Reflection;\r
  32 \r
  33 #if MONO_STRICT\r
  34 using System.Data.Linq.Sql;\r
  35 using System.Data.Linq.Sugar.Expressions;\r
  36 #else\r
  37 using DbLinq.Data.Linq.Sql;\r
  38 using DbLinq.Data.Linq.Sugar.Expressions;\r
  39 #endif\r
  40 \r
  41 using DbLinq.Util;\r
  42 \r
  43 #if MONO_STRICT\r
  44 namespace System.Data.Linq.Sugar.Implementation\r
  45 #else\r
  46 namespace DbLinq.Data.Linq.Sugar.Implementation\r
  47 #endif\r
  48 {\r
  49     partial class QueryBuilder\r
  50     {\r
  51         protected class UpsertParameters\r
  52         {\r
  53             public MetaTable Table;\r
  54             public readonly IList<ObjectInputParameterExpression> InputParameters = new List<ObjectInputParameterExpression>();\r
  55             public readonly IList<ObjectOutputParameterExpression> OutputParameters = new List<ObjectOutputParameterExpression>();\r
  56             public readonly IList<SqlStatement> InputColumns = new List<SqlStatement>();\r
  57             public readonly IList<SqlStatement> InputValues = new List<SqlStatement>();\r
  58             public readonly IList<SqlStatement> OutputValues = new List<SqlStatement>();\r
  59             public readonly IList<SqlStatement> OutputExpressions = new List<SqlStatement>();\r
  60             public readonly IList<SqlStatement> InputPKColumns = new List<SqlStatement>();\r
  61             public readonly IList<SqlStatement> InputPKValues = new List<SqlStatement>();\r
  62         }\r
  63 \r
  64         // SQLite:\r
  65         // IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false, Expression = null\r
  66         // INSERT INTO main.Products (CategoryID, Discontinued, ProductName, QuantityPerUnit) \r
  67         //                  VALUES (@P1, @P2, @P3, @P4) ;SELECT last_insert_rowid()\r
  68         //\r
  69         // Ingres:\r
  70         // IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false, \r
  71         //                       Expression = "next value for \"linquser\".\"products_seq\"")]\r
  72         // INSERT INTO linquser.products (categoryid, discontinued, productid, productname, quantityperunit) \r
  73         //                  VALUES ($param_000001_param$, $param_000002_param$, \r
  74         //                          next value for "linquser"."products_seq", $param_000004_param$, $param_000005_param$) \r
  75         //\r
  76         // Oracle:\r
  77         // IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false, Expression = null\r
  78         // BEGIN \r
  79         // INSERT INTO NORTHWIND."Products" ("CategoryID", "Discontinued", "ProductID", "ProductName", "QuantityPerUnit") \r
  80         //                  VALUES (:P1, :P2, NORTHWIND."Products_SEQ".NextVal, :P4, :P5)\r
  81         //               ;SELECT NORTHWIND."Products_SEQ".CurrVal INTO :P3 FROM DUAL; END;\r
  82         //\r
  83         // PostgreSQL:\r
  84         // IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false, Expression = "nextval('\"Products_ProductID_seq\"')"\r
  85         // INSERT INTO public."Products" ("CategoryID", "Discontinued", "ProductName", "QuantityPerUnit") \r
  86         //                  VALUES (:P1, :P2, :P3, :P4) \r
  87         //               ;SELECT currval('"Products_ProductID_seq"')\r
  88         //\r
  89         // SQL Server (bogus):\r
  90         // IsPrimaryKey = true, IsDbGenerated = true\r
  91         // INSERT INTO [dbo].[Products] (, , , ) VALUES (@P1, @P2, @P3, @P4) \r
  92         //                  ; SELECT @@IDENTITY\r
  93         //\r
  94         // Column:               default --> use value\r
  95         //          PK: Expression !null --> use parameter (Oracle is wrong here)\r
  96         //              Expression  null --> ignore\r
  97         // SQL: wrap clause with PK information\r
  98 \r
  99 \r
 100         /// <summary>\r
 101         /// Creates a query for insertion\r
 102         /// </summary>\r
 103         /// <param name="objectToInsert"></param>\r
 104         /// <param name="queryContext"></param>\r
 105         /// <returns></returns>\r
 106         public UpsertQuery GetInsertQuery(object objectToInsert, QueryContext queryContext)\r
 107         {\r
 108             // TODO: cache\r
 109             var upsertParameters = GetUpsertParameters(objectToInsert, false, null, queryContext);\r
 110             var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;\r
 111             var insertSql = sqlProvider.GetInsert(\r
 112                 sqlProvider.GetTable(upsertParameters.Table.TableName),\r
 113                 upsertParameters.InputColumns,\r
 114                 upsertParameters.InputValues);\r
 115             var insertIdSql = sqlProvider.GetInsertIds(\r
 116                 upsertParameters.OutputValues,\r
 117                 upsertParameters.OutputExpressions);\r
 118             return new UpsertQuery(queryContext.DataContext, insertSql, insertIdSql, upsertParameters.InputParameters, upsertParameters.OutputParameters);\r
 119         }\r
 120 \r
 121         protected enum ParameterType\r
 122         {\r
 123             Input,\r
 124             InputPK,\r
 125             Output\r
 126         }\r
 127 \r
 128         /// <summary>\r
 129         /// Gets values for insert/update\r
 130         /// </summary>\r
 131         /// <param name="objectToUpsert"></param>\r
 132         /// <param name="queryContext"></param>\r
 133         /// <param name="update"></param>\r
 134         /// <param name="modifiedMembers"></param>\r
 135         /// <returns></returns>\r
 136         protected virtual UpsertParameters GetUpsertParameters(object objectToUpsert, bool update, IList<MemberInfo> modifiedMembers, QueryContext queryContext)\r
 137         {\r
 138             var rowType = objectToUpsert.GetType();\r
 139             var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;\r
 140             var upsertParameters = new UpsertParameters\r
 141                                        {\r
 142                                            Table = queryContext.DataContext.Mapping.GetTable(rowType)\r
 143                                        };\r
 144             foreach (var dataMember in upsertParameters.Table.RowType.PersistentDataMembers)\r
 145             {\r
 146                 var column = sqlProvider.GetColumn(dataMember.MappedName);\r
 147                 ParameterType type = GetParameterType(objectToUpsert, dataMember, update);\r
 148                 var memberInfo = dataMember.Member;\r
 149                 // if the column is generated AND not specified, we may have:\r
 150                 // - an explicit generation (Expression property is not null, so we add the column)\r
 151                 // - an implicit generation (Expression property is null\r
 152                 // in all cases, we want to get the value back\r
 153                 if (type == ParameterType.Output)\r
 154                 {\r
 155                     if (dataMember.Expression != null)\r
 156                     {\r
 157                         upsertParameters.InputColumns.Add(column);\r
 158                         upsertParameters.InputValues.Add(dataMember.Expression);\r
 159                     }\r
 160                     var setter = (Expression<Action<object, object>>)((o, v) => memberInfo.SetMemberValue(o, v));\r
 161                     var outputParameter = new ObjectOutputParameterExpression(setter,\r
 162                                                                               memberInfo.GetMemberType(),\r
 163                                                                               dataMember.Name);\r
 164                     upsertParameters.OutputParameters.Add(outputParameter);\r
 165                     upsertParameters.OutputValues.Add(sqlProvider.GetParameterName(outputParameter.Alias));\r
 166                     upsertParameters.OutputExpressions.Add(dataMember.Expression);\r
 167                 }\r
 168                 else // standard column\r
 169                 {\r
 170                     var getter = (Expression<Func<object, object>>)(o => memberInfo.GetMemberValue(o));\r
 171                     var inputParameter = new ObjectInputParameterExpression(\r
 172                         getter,\r
 173                         memberInfo.GetMemberType(), dataMember.Name);\r
 174                     if (type == ParameterType.InputPK)\r
 175                     {\r
 176                         upsertParameters.InputPKColumns.Add(column);\r
 177                         upsertParameters.InputPKValues.Add(sqlProvider.GetParameterName(inputParameter.Alias));\r
 178                         upsertParameters.InputParameters.Add(inputParameter);\r
 179                     }\r
 180                     // for a standard column, we keep it only if modifiedMembers contains the specified memberInfo\r
 181                     // caution: this makes the cache harder to maintain\r
 182                     else if (modifiedMembers == null || modifiedMembers.Contains(memberInfo))\r
 183                     {\r
 184                         upsertParameters.InputColumns.Add(column);\r
 185                         upsertParameters.InputValues.Add(sqlProvider.GetParameterName(inputParameter.Alias));\r
 186                         upsertParameters.InputParameters.Add(inputParameter);\r
 187                     }\r
 188                 }\r
 189             }\r
 190             return upsertParameters;\r
 191         }\r
 192 \r
 193         /// <summary>\r
 194         /// Provides the parameter type for a given data member\r
 195         /// </summary>\r
 196         /// <param name="objectToUpsert"></param>\r
 197         /// <param name="dataMember"></param>\r
 198         /// <param name="update"></param>\r
 199         /// <returns></returns>\r
 200         protected virtual ParameterType GetParameterType(object objectToUpsert, MetaDataMember dataMember, bool update)\r
 201         {\r
 202             var memberInfo = dataMember.Member;\r
 203             // the deal with columns is:\r
 204             // PK only:  explicit for INSERT, criterion for UPDATE\r
 205             // PK+GEN:   implicit/explicit for INSERT, criterion for UPDATE\r
 206             // GEN only: implicit for both\r
 207             // -:        explicit for both\r
 208             //\r
 209             // explicit is input,\r
 210             // implicit is output, \r
 211             // criterion is input PK\r
 212             ParameterType type;\r
 213             if (dataMember.IsPrimaryKey)\r
 214             {\r
 215                 if (update)\r
 216                     type = ParameterType.InputPK;\r
 217                 else\r
 218                 {\r
 219                     if (dataMember.IsDbGenerated)\r
 220                     {\r
 221                         if (IsSpecified(objectToUpsert, memberInfo))\r
 222                             type = ParameterType.Input;\r
 223                         else\r
 224                             type = ParameterType.Output;\r
 225                     }\r
 226                     else\r
 227                         type = ParameterType.Input;\r
 228                 }\r
 229             }\r
 230             else\r
 231             {\r
 232                 if (dataMember.IsDbGenerated)\r
 233                     type = ParameterType.Output;\r
 234                 else\r
 235                     type = ParameterType.Input;\r
 236             }\r
 237             return type;\r
 238         }\r
 239 \r
 240         /// <summary>\r
 241         /// Determines if a property is different from its default value\r
 242         /// </summary>\r
 243         /// <param name="target"></param>\r
 244         /// <param name="memberInfo"></param>\r
 245         /// <returns></returns>\r
 246         protected virtual bool IsSpecified(object target, MemberInfo memberInfo)\r
 247         {\r
 248             object value = memberInfo.GetMemberValue(target);\r
 249             if (value == null)\r
 250                 return false;\r
 251             if (Equals(value, TypeConvert.GetDefault(memberInfo.GetMemberType())))\r
 252                 return false;\r
 253             return true;\r
 254         }\r
 255 \r
 256         /// <summary>\r
 257         /// Creates or gets an UPDATE query\r
 258         /// </summary>\r
 259         /// <param name="objectToUpdate"></param>\r
 260         /// <param name="modifiedMembers">List of modified members, or NULL</param>\r
 261         /// <param name="queryContext"></param>\r
 262         /// <returns></returns>\r
 263         public UpsertQuery GetUpdateQuery(object objectToUpdate, IList<MemberInfo> modifiedMembers, QueryContext queryContext)\r
 264         {\r
 265             var upsertParameters = GetUpsertParameters(objectToUpdate, true, modifiedMembers, queryContext);\r
 266             var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;\r
 267             var updateSql = sqlProvider.GetUpdate(sqlProvider.GetTable(upsertParameters.Table.TableName),\r
 268                 upsertParameters.InputColumns, upsertParameters.InputValues,\r
 269                 upsertParameters.OutputValues, upsertParameters.OutputExpressions,\r
 270                 upsertParameters.InputPKColumns, upsertParameters.InputPKValues\r
 271                 );\r
 272             return new UpsertQuery(queryContext.DataContext, updateSql, "", upsertParameters.InputParameters, upsertParameters.OutputParameters);\r
 273         }\r
 274 \r
 275         /// <summary>\r
 276         /// Creates or gets a DELETE query\r
 277         /// </summary>\r
 278         /// <param name="objectToDelete"></param>\r
 279         /// <param name="queryContext"></param>\r
 280         /// <returns></returns>\r
 281         public DeleteQuery GetDeleteQuery(object objectToDelete, QueryContext queryContext)\r
 282         {\r
 283             var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;\r
 284             var rowType = objectToDelete.GetType();\r
 285             var table = queryContext.DataContext.Mapping.GetTable(rowType);\r
 286             var deleteParameters = new List<ObjectInputParameterExpression>();\r
 287             var pkColumns = new List<SqlStatement>();\r
 288             var pkValues = new List<SqlStatement>();\r
 289             foreach (var pkMember in table.RowType.IdentityMembers)\r
 290             {\r
 291                 var memberInfo = pkMember.Member;\r
 292                 var getter = (Expression<Func<object, object>>)(o => memberInfo.GetMemberValue(o));\r
 293                 var inputParameter = new ObjectInputParameterExpression(\r
 294                     getter,\r
 295                     memberInfo.GetMemberType(), pkMember.Name);\r
 296                 var column = sqlProvider.GetColumn(pkMember.MappedName);\r
 297                 pkColumns.Add(column);\r
 298                 pkValues.Add(sqlProvider.GetParameterName(inputParameter.Alias));\r
 299                 deleteParameters.Add(inputParameter);\r
 300             }\r
 301             var deleteSql = sqlProvider.GetDelete(sqlProvider.GetTable(table.TableName), pkColumns, pkValues);\r
 302             return new DeleteQuery(queryContext.DataContext, deleteSql, deleteParameters);\r
 303         }\r
 304     }\r
 305 }\r