System.Data.SQLite
Check-in [01a3da88e7]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Remove errant semi-colons from the SQL used by LINQ to INSERT and then SELECT rows with composite primary keys. Fix for [9d353b0bd8].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 01a3da88e7ecad81042b876f0a50e97cae4c9457
User & Date: mistachkin 2015-06-23 00:56:44
References
2015-06-23
00:57 Closed ticket [9d353b0bd8]: EF-INSERT fails for multi-column-primary-key tables plus 5 other changes artifact: 96ebacc5e0 user: mistachkin
Context
2015-06-24
00:13
Minor enhancement to the batch build tool. check-in: 2cccb64c09 user: mistachkin tags: trunk
2015-06-23
00:56
Remove errant semi-colons from the SQL used by LINQ to INSERT and then SELECT rows with composite primary keys. Fix for [9d353b0bd8]. check-in: 01a3da88e7 user: mistachkin tags: trunk
00:13
Reorganize the solution files. check-in: e0e67614a2 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Doc/Extra/Provider/version.html.

    43     43       <div id="mainSection">
    44     44       <div id="mainBody">
    45     45       <h1 class="heading">Version History</h1>
    46     46       <p><b>1.0.98.0 - August XX, 2015 <font color="red">(release scheduled)</font></b></p>
    47     47       <ul>
    48     48         <li>Updated to <a href="https://www.sqlite.org/draft/releaselog/3_8_11.html">SQLite 3.8.11</a>.</li>
    49     49         <li>Implement the Substring method for LINQ using the &quot;substr&quot; core SQL function.&nbsp;<b>** Potentially Incompatible Change **</b></li>
           50  +      <li>Remove errant semi-colons from the SQL used by LINQ to INSERT and then SELECT rows with composite primary keys. Fix for <a href="https://system.data.sqlite.org/index.html/info/9d353b0bd8">[9d353b0bd8]</a>.</li>
    50     51         <li>Add VfsName connection string property to allow a non-default VFS to be used by the SQLite core library.</li>
    51     52         <li>Add BusyTimeout connection string property to set the busy timeout to be used by the SQLite core library.</li>
    52     53         <li>Enable integration with the <a href="http://www.hwaci.com/sw/sqlite/zipvfs.html">ZipVFS</a> extension.</li>
    53     54       </ul>
    54     55       <p><b>1.0.97.0 - May 26, 2015</b></p>
    55     56       <ul>
    56     57         <li>Updated to <a href="https://www.sqlite.org/releaselog/3_8_10_2.html">SQLite 3.8.10.2</a>.</li>

Changes to System.Data.SQLite.Linq/SQL Generation/DmlSqlGenerator.cs.

   223    223           // retrieve member value sql. the translator remembers member values
   224    224           // as it constructs the DML statement (which precedes the "returning"
   225    225           // SQL)
   226    226           DbParameter value;
   227    227           if (translator.MemberValues.TryGetValue(keyMember, out value))
   228    228           {
   229    229             commandText.Append(value.ParameterName);
   230         -          commandText.AppendLine(";");
   231    230           }
   232    231           else
   233    232           {
   234    233             // if no value is registered for the key member, it means it is an identity
   235    234             // which can be retrieved using the scope_identity() function
   236    235             if (identity)
   237    236             {
   238    237               // there can be only one server generated key
   239    238               throw new NotSupportedException(string.Format("Server generated keys are only supported for identity columns. More than one key column is marked as server generated in table '{0}'.", table.Name));
   240    239             }
   241         -          commandText.AppendLine("last_insert_rowid();");
          240  +          commandText.AppendLine("last_insert_rowid()");
   242    241             identity = true;
   243    242           }
   244    243         }
          244  +      commandText.AppendLine(";");
   245    245       }
   246    246   
   247    247       /// <summary>
   248    248       /// Lightweight expression translator for DML expression trees, which have constrained
   249    249       /// scope and support.
   250    250       /// </summary>
   251    251       private class ExpressionTranslator : DbExpressionVisitor

Added Tests/tkt-9d353b0bd8.eagle.

            1  +###############################################################################
            2  +#
            3  +# tkt-9d353b0bd8.eagle --
            4  +#
            5  +# Written by Joe Mistachkin.
            6  +# Released to the public domain, use at your own risk!
            7  +#
            8  +###############################################################################
            9  +
           10  +package require Eagle
           11  +package require Eagle.Library
           12  +package require Eagle.Test
           13  +
           14  +runTestPrologue
           15  +
           16  +###############################################################################
           17  +
           18  +package require System.Data.SQLite.Test
           19  +runSQLiteTestPrologue
           20  +runSQLiteTestFilesPrologue
           21  +
           22  +###############################################################################
           23  +
           24  +runTest {test tkt-9d353b0bd8-1.1 {DbModificationCommandTree w/INSERT} -body {
           25  +  #
           26  +  # NOTE: Re-copy the reference database file used for this unit test to the
           27  +  #       build directory in case it has been changed by a previous test run.
           28  +  #
           29  +  file copy -force $northwindEfDbFile \
           30  +      [file join [getBuildDirectory] [file tail $northwindEfDbFile]]
           31  +
           32  +  set result [list]
           33  +  set output ""
           34  +
           35  +  set code [catch {
           36  +    testClrExec $testLinqExeFile [list -eventflags Wait -directory \
           37  +        [file dirname $testLinqExeFile] -nocarriagereturns -stdout output \
           38  +        -success 0] -insert
           39  +  } error]
           40  +
           41  +  tlog "---- BEGIN STDOUT OUTPUT\n"
           42  +  tlog $output
           43  +  tlog "\n---- END STDOUT OUTPUT\n"
           44  +
           45  +  lappend result $code
           46  +
           47  +  if {$code == 0} then {
           48  +    lappend result [string trim $output]
           49  +  } else {
           50  +    lappend result [string trim $error]
           51  +  }
           52  +
           53  +  set result
           54  +} -cleanup {
           55  +  unset -nocomplain code output error result
           56  +} -constraints {eagle monoToDo SQLite file_System.Data.SQLite.dll testExec\
           57  +file_System.Data.SQLite.Linq.dll file_testlinq.exe file_northwindEF.db} \
           58  +-result {0 {inserted 1}}}
           59  +
           60  +###############################################################################
           61  +
           62  +runSQLiteTestFilesEpilogue
           63  +runSQLiteTestEpilogue
           64  +runTestEpilogue

Changes to readme.htm.

   210    210   
   211    211   <p>
   212    212       <b>1.0.98.0 - August XX, 2015 <font color="red">(release scheduled)</font></b>
   213    213   </p>
   214    214   <ul>
   215    215       <li>Updated to <a href="https://www.sqlite.org/draft/releaselog/3_8_11.html">SQLite 3.8.11</a>.</li>
   216    216       <li>Implement the Substring method for LINQ using the &quot;substr&quot; core SQL function.&nbsp;<b>** Potentially Incompatible Change **</b></li>
          217  +    <li>Remove errant semi-colons from the SQL used by LINQ to INSERT and then SELECT rows with composite primary keys. Fix for [9d353b0bd8].</li>
   217    218       <li>Add VfsName connection string property to allow a non-default VFS to be used by the SQLite core library.</li>
   218    219       <li>Add BusyTimeout connection string property to set the busy timeout to be used by the SQLite core library.</li>
   219    220       <li>Enable integration with the <a href="http://www.hwaci.com/sw/sqlite/zipvfs.html">ZipVFS</a> extension.</li>
   220    221   </ul>
   221    222   <p>
   222    223       <b>1.0.97.0 - May 26, 2015</b>
   223    224   </p>

Changes to testlinq/NorthwindModel.EF6.2010.edmx.

   133    133               <PropertyRef Name="OrderID" />
   134    134               <PropertyRef Name="ProductID" />
   135    135             </Key>
   136    136             <Property Name="OrderID" Type="integer" Nullable="false" />
   137    137             <Property Name="ProductID" Type="integer" Nullable="false" />
   138    138             <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
   139    139             <Property Name="Quantity" Type="smallint" Nullable="false" />
   140         -          <Property Name="Discount" Type="real" Nullable="false" />
          140  +          <!--
          141  +              NOTE: The "Discount" column is not actually generated by the store;
          142  +                    however, it has a default value and can be utilized to test
          143  +                    generating an DbInsertCommandTree with a Returning property
          144  +                    value that is not null.
          145  +          -->
          146  +          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
   141    147           </EntityType>
   142    148           <EntityType Name="Orders">
   143    149             <Key>
   144    150               <PropertyRef Name="OrderID" />
   145    151             </Key>
   146    152             <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
   147    153             <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />

Changes to testlinq/NorthwindModel.EF6.2012.edmx.

   133    133               <PropertyRef Name="OrderID" />
   134    134               <PropertyRef Name="ProductID" />
   135    135             </Key>
   136    136             <Property Name="OrderID" Type="integer" Nullable="false" />
   137    137             <Property Name="ProductID" Type="integer" Nullable="false" />
   138    138             <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
   139    139             <Property Name="Quantity" Type="smallint" Nullable="false" />
   140         -          <Property Name="Discount" Type="real" Nullable="false" />
          140  +          <!--
          141  +              NOTE: The "Discount" column is not actually generated by the store;
          142  +                    however, it has a default value and can be utilized to test
          143  +                    generating an DbInsertCommandTree with a Returning property
          144  +                    value that is not null.
          145  +          -->
          146  +          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
   141    147           </EntityType>
   142    148           <EntityType Name="Orders">
   143    149             <Key>
   144    150               <PropertyRef Name="OrderID" />
   145    151             </Key>
   146    152             <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
   147    153             <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />

Changes to testlinq/NorthwindModel.EF6.2013.edmx.

   133    133               <PropertyRef Name="OrderID" />
   134    134               <PropertyRef Name="ProductID" />
   135    135             </Key>
   136    136             <Property Name="OrderID" Type="integer" Nullable="false" />
   137    137             <Property Name="ProductID" Type="integer" Nullable="false" />
   138    138             <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
   139    139             <Property Name="Quantity" Type="smallint" Nullable="false" />
   140         -          <Property Name="Discount" Type="real" Nullable="false" />
          140  +          <!--
          141  +              NOTE: The "Discount" column is not actually generated by the store;
          142  +                    however, it has a default value and can be utilized to test
          143  +                    generating an DbInsertCommandTree with a Returning property
          144  +                    value that is not null.
          145  +          -->
          146  +          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
   141    147           </EntityType>
   142    148           <EntityType Name="Orders">
   143    149             <Key>
   144    150               <PropertyRef Name="OrderID" />
   145    151             </Key>
   146    152             <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
   147    153             <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />

Changes to testlinq/NorthwindModel.Linq.2008.edmx.

   133    133               <PropertyRef Name="OrderID" />
   134    134               <PropertyRef Name="ProductID" />
   135    135             </Key>
   136    136             <Property Name="OrderID" Type="integer" Nullable="false" />
   137    137             <Property Name="ProductID" Type="integer" Nullable="false" />
   138    138             <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
   139    139             <Property Name="Quantity" Type="smallint" Nullable="false" />
   140         -          <Property Name="Discount" Type="real" Nullable="false" />
          140  +          <!--
          141  +              NOTE: The "Discount" column is not actually generated by the store;
          142  +                    however, it has a default value and can be utilized to test
          143  +                    generating an DbInsertCommandTree with a Returning property
          144  +                    value that is not null.
          145  +          -->
          146  +          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
   141    147           </EntityType>
   142    148           <EntityType Name="Orders">
   143    149             <Key>
   144    150               <PropertyRef Name="OrderID" />
   145    151             </Key>
   146    152             <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
   147    153             <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />

Changes to testlinq/NorthwindModel.Linq.2010.edmx.

   133    133               <PropertyRef Name="OrderID" />
   134    134               <PropertyRef Name="ProductID" />
   135    135             </Key>
   136    136             <Property Name="OrderID" Type="integer" Nullable="false" />
   137    137             <Property Name="ProductID" Type="integer" Nullable="false" />
   138    138             <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
   139    139             <Property Name="Quantity" Type="smallint" Nullable="false" />
   140         -          <Property Name="Discount" Type="real" Nullable="false" />
          140  +          <!--
          141  +              NOTE: The "Discount" column is not actually generated by the store;
          142  +                    however, it has a default value and can be utilized to test
          143  +                    generating an DbInsertCommandTree with a Returning property
          144  +                    value that is not null.
          145  +          -->
          146  +          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
   141    147           </EntityType>
   142    148           <EntityType Name="Orders">
   143    149             <Key>
   144    150               <PropertyRef Name="OrderID" />
   145    151             </Key>
   146    152             <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
   147    153             <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />

Changes to testlinq/NorthwindModel.Linq.2012.edmx.

   133    133               <PropertyRef Name="OrderID" />
   134    134               <PropertyRef Name="ProductID" />
   135    135             </Key>
   136    136             <Property Name="OrderID" Type="integer" Nullable="false" />
   137    137             <Property Name="ProductID" Type="integer" Nullable="false" />
   138    138             <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
   139    139             <Property Name="Quantity" Type="smallint" Nullable="false" />
   140         -          <Property Name="Discount" Type="real" Nullable="false" />
          140  +          <!--
          141  +              NOTE: The "Discount" column is not actually generated by the store;
          142  +                    however, it has a default value and can be utilized to test
          143  +                    generating an DbInsertCommandTree with a Returning property
          144  +                    value that is not null.
          145  +          -->
          146  +          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
   141    147           </EntityType>
   142    148           <EntityType Name="Orders">
   143    149             <Key>
   144    150               <PropertyRef Name="OrderID" />
   145    151             </Key>
   146    152             <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
   147    153             <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />

Changes to testlinq/NorthwindModel.Linq.2013.edmx.

   133    133               <PropertyRef Name="OrderID" />
   134    134               <PropertyRef Name="ProductID" />
   135    135             </Key>
   136    136             <Property Name="OrderID" Type="integer" Nullable="false" />
   137    137             <Property Name="ProductID" Type="integer" Nullable="false" />
   138    138             <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
   139    139             <Property Name="Quantity" Type="smallint" Nullable="false" />
   140         -          <Property Name="Discount" Type="real" Nullable="false" />
          140  +          <!--
          141  +              NOTE: The "Discount" column is not actually generated by the store;
          142  +                    however, it has a default value and can be utilized to test
          143  +                    generating an DbInsertCommandTree with a Returning property
          144  +                    value that is not null.
          145  +          -->
          146  +          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
   141    147           </EntityType>
   142    148           <EntityType Name="Orders">
   143    149             <Key>
   144    150               <PropertyRef Name="OrderID" />
   145    151             </Key>
   146    152             <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
   147    153             <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />

Changes to testlinq/Program.cs.

   140    140   
   141    141                                 return 1;
   142    142                             }
   143    143                         }
   144    144   
   145    145                         return EFTransactionTest(value);
   146    146                     }
          147  +              case "insert":
          148  +                  {
          149  +                      return InsertTest();
          150  +                  }
   147    151                 case "update":
   148    152                     {
   149    153                         return UpdateTest();
   150    154                     }
   151    155                 case "binaryguid":
   152    156                     {
   153    157                         bool value = false;
................................................................................
   534    538                         Console.Write(territories.TerritoryID);
   535    539   
   536    540                         once = true;
   537    541                     }
   538    542   #endif
   539    543                 }
   540    544             }
          545  +
          546  +          return 0;
          547  +      }
          548  +
          549  +      //
          550  +      // NOTE: Used to test the INSERT fix (i.e. an extra semi-colon in
          551  +      //       the SQL statement after the actual INSERT statement in
          552  +      //       the follow-up SELECT statement).
          553  +      //
          554  +      private static int InsertTest()
          555  +      {
          556  +          long[] orderIds = new long[] {
          557  +              0
          558  +          };
          559  +
          560  +          using (northwindEFEntities db = new northwindEFEntities())
          561  +          {
          562  +              int[] counts = { 0 };
          563  +
          564  +              //
          565  +              // NOTE: *REQUIRED* This is required so that the
          566  +              //       Entity Framework is prevented from opening
          567  +              //       multiple connections to the underlying SQLite
          568  +              //       database (i.e. which would result in multiple
          569  +              //       IMMEDIATE transactions, thereby failing [later
          570  +              //       on] with locking errors).
          571  +              //
          572  +              db.Connection.Open();
          573  +
          574  +              OrderDetails newOrderDetails = new OrderDetails();
          575  +
          576  +              newOrderDetails.OrderID = 10248;
          577  +              newOrderDetails.ProductID = 1;
          578  +              newOrderDetails.UnitPrice = (decimal)1.23;
          579  +              newOrderDetails.Quantity = 1;
          580  +              newOrderDetails.Discount = 0.0f;
          581  +
          582  +              db.AddObject("OrderDetails", newOrderDetails);
          583  +
          584  +              try
          585  +              {
          586  +                  db.SaveChanges();
          587  +                  counts[0]++;
          588  +              }
          589  +              catch (Exception e)
          590  +              {
          591  +                  Console.WriteLine(e);
          592  +              }
          593  +              finally
          594  +              {
          595  +                  db.AcceptAllChanges();
          596  +              }
          597  +
          598  +              Console.WriteLine("inserted {0}", counts[0]);
          599  +          }
   541    600   
   542    601             return 0;
   543    602         }
   544    603   
   545    604         //
   546    605         // NOTE: Used to test the UPDATE fix (i.e. the missing semi-colon
   547    606         //       in the SQL statement between the actual UPDATE statement

Changes to www/news.wiki.

     4      4   
     5      5   <p>
     6      6       <b>1.0.98.0 - August XX, 2015 <font color="red">(release scheduled)</font></b>
     7      7   </p>
     8      8   <ul>
     9      9       <li>Updated to [https://www.sqlite.org/draft/releaselog/3_8_11.html|SQLite 3.8.11].</li>
    10     10       <li>Implement the Substring method for LINQ using the &quot;substr&quot; core SQL function.&nbsp;<b>** Potentially Incompatible Change **</b></li>
           11  +    <li>Remove errant semi-colons from the SQL used by LINQ to INSERT and then SELECT rows with composite primary keys. Fix for [9d353b0bd8].</li>
    11     12       <li>Add VfsName connection string property to allow a non-default VFS to be used by the SQLite core library.</li>
    12     13       <li>Add BusyTimeout connection string property to set the busy timeout to be used by the SQLite core library.</li>
    13     14       <li>Enable integration with the [http://www.hwaci.com/sw/sqlite/zipvfs.html|ZipVFS] extension.</li>
    14     15   </ul>
    15     16   <p>
    16     17       <b>1.0.97.0 - May 26, 2015</b>
    17     18   </p>