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: |
01a3da88e7ecad81042b876f0a50e97c |
| 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
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 "substr" core SQL function. <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 "substr" core SQL function. <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 "substr" core SQL function. <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>