System.Data.SQLite

Check-in [01a3da88e7]
Login

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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 01a3da88e7ecad81042b876f0a50e97cae4c9457
User & Date: mistachkin 2015-06-23 00:56:44.662
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
Side-by-Side Diff Show Whitespace Changes Patch
Changes to Doc/Extra/Provider/version.html.
︙
43
44
45
46
47
48
49

50
51
52
53
54
55
56

43
44
45
46
47
48
49
50
51
52
53
54
55
56
57








+








    <div id="mainSection">
    <div id="mainBody">
    <h1 class="heading">Version History</h1>
    <p><b>1.0.98.0 - August XX, 2015 <font color="red">(release scheduled)</font></b></p>
    <ul>
      <li>Updated to <a href="https://www.sqlite.org/draft/releaselog/3_8_11.html">SQLite 3.8.11</a>.</li>
      <li>Implement the Substring method for LINQ using the &quot;substr&quot; core SQL function.&nbsp;<b>** Potentially Incompatible Change **</b></li>
      <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>
      <li>Add VfsName connection string property to allow a non-default VFS to be used by the SQLite core library.</li>
      <li>Add BusyTimeout connection string property to set the busy timeout to be used by the SQLite core library.</li>
      <li>Enable integration with the <a href="http://www.hwaci.com/sw/sqlite/zipvfs.html">ZipVFS</a> extension.</li>
    </ul>
    <p><b>1.0.97.0 - May 26, 2015</b></p>
    <ul>
      <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
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241

242
243
244

245
246
247
248
249
250
251

223
224
225
226
227
228
229

230
231
232
233
234
235
236
237
238
239

240
241
242
243
244
245
246
247
248
249
250
251








-










-
+



+








        // retrieve member value sql. the translator remembers member values
        // as it constructs the DML statement (which precedes the "returning"
        // SQL)
        DbParameter value;
        if (translator.MemberValues.TryGetValue(keyMember, out value))
        {
          commandText.Append(value.ParameterName);
          commandText.AppendLine(";");
        }
        else
        {
          // if no value is registered for the key member, it means it is an identity
          // which can be retrieved using the scope_identity() function
          if (identity)
          {
            // there can be only one server generated key
            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));
          }
          commandText.AppendLine("last_insert_rowid();");
          commandText.AppendLine("last_insert_rowid()");
          identity = true;
        }
      }
      commandText.AppendLine(";");
    }

    /// <summary>
    /// Lightweight expression translator for DML expression trees, which have constrained
    /// scope and support.
    /// </summary>
    private class ExpressionTranslator : DbExpressionVisitor
︙
Added Tests/tkt-9d353b0bd8.eagle. 

































































1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+

###############################################################################
#
# tkt-9d353b0bd8.eagle --
#
# Written by Joe Mistachkin.
# Released to the public domain, use at your own risk!
#
###############################################################################

package require Eagle
package require Eagle.Library
package require Eagle.Test

runTestPrologue

###############################################################################

package require System.Data.SQLite.Test
runSQLiteTestPrologue
runSQLiteTestFilesPrologue

###############################################################################

runTest {test tkt-9d353b0bd8-1.1 {DbModificationCommandTree w/INSERT} -body {
  #
  # NOTE: Re-copy the reference database file used for this unit test to the
  #       build directory in case it has been changed by a previous test run.
  #
  file copy -force $northwindEfDbFile \
      [file join [getBuildDirectory] [file tail $northwindEfDbFile]]

  set result [list]
  set output ""

  set code [catch {
    testClrExec $testLinqExeFile [list -eventflags Wait -directory \
        [file dirname $testLinqExeFile] -nocarriagereturns -stdout output \
        -success 0] -insert
  } error]

  tlog "---- BEGIN STDOUT OUTPUT\n"
  tlog $output
  tlog "\n---- END STDOUT OUTPUT\n"

  lappend result $code

  if {$code == 0} then {
    lappend result [string trim $output]
  } else {
    lappend result [string trim $error]
  }

  set result
} -cleanup {
  unset -nocomplain code output error result
} -constraints {eagle monoToDo SQLite file_System.Data.SQLite.dll testExec\
file_System.Data.SQLite.Linq.dll file_testlinq.exe file_northwindEF.db} \
-result {0 {inserted 1}}}

###############################################################################

runSQLiteTestFilesEpilogue
runSQLiteTestEpilogue
runTestEpilogue






Changes to readme.htm.






︙


210
211
212
213
214
215
216

217
218
219
220
221
222
223

210
211
212
213
214
215
216
217
218
219
220
221
222
223
224








+









<p>
    <b>1.0.98.0 - August XX, 2015 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>Updated to <a href="https://www.sqlite.org/draft/releaselog/3_8_11.html">SQLite 3.8.11</a>.</li>
    <li>Implement the Substring method for LINQ using the &quot;substr&quot; core SQL function.&nbsp;<b>** Potentially Incompatible Change **</b></li>
    <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>
    <li>Add VfsName connection string property to allow a non-default VFS to be used by the SQLite core library.</li>
    <li>Add BusyTimeout connection string property to set the busy timeout to be used by the SQLite core library.</li>
    <li>Enable integration with the <a href="http://www.hwaci.com/sw/sqlite/zipvfs.html">ZipVFS</a> extension.</li>
</ul>
<p>
    <b>1.0.97.0 - May 26, 2015</b>
</p>




︙





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






︙


133
134
135
136
137
138
139






140

141
142
143
144
145
146
147

133
134
135
136
137
138
139
140
141
142
143
144
145

146
147
148
149
150
151
152
153








+
+
+
+
+
+
-
+








            <PropertyRef Name="OrderID" />
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" />
          <Property Name="ProductID" Type="integer" Nullable="false" />
          <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
          <Property Name="Quantity" Type="smallint" Nullable="false" />
          <!--
              NOTE: The "Discount" column is not actually generated by the store;
                    however, it has a default value and can be utilized to test
                    generating an DbInsertCommandTree with a Returning property
                    value that is not null.
          -->
          <Property Name="Discount" Type="real" Nullable="false" />
          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
        </EntityType>
        <EntityType Name="Orders">
          <Key>
            <PropertyRef Name="OrderID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />




︙





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






︙


133
134
135
136
137
138
139






140

141
142
143
144
145
146
147

133
134
135
136
137
138
139
140
141
142
143
144
145

146
147
148
149
150
151
152
153








+
+
+
+
+
+
-
+








            <PropertyRef Name="OrderID" />
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" />
          <Property Name="ProductID" Type="integer" Nullable="false" />
          <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
          <Property Name="Quantity" Type="smallint" Nullable="false" />
          <!--
              NOTE: The "Discount" column is not actually generated by the store;
                    however, it has a default value and can be utilized to test
                    generating an DbInsertCommandTree with a Returning property
                    value that is not null.
          -->
          <Property Name="Discount" Type="real" Nullable="false" />
          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
        </EntityType>
        <EntityType Name="Orders">
          <Key>
            <PropertyRef Name="OrderID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />




︙





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






︙


133
134
135
136
137
138
139






140

141
142
143
144
145
146
147

133
134
135
136
137
138
139
140
141
142
143
144
145

146
147
148
149
150
151
152
153








+
+
+
+
+
+
-
+








            <PropertyRef Name="OrderID" />
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" />
          <Property Name="ProductID" Type="integer" Nullable="false" />
          <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
          <Property Name="Quantity" Type="smallint" Nullable="false" />
          <!--
              NOTE: The "Discount" column is not actually generated by the store;
                    however, it has a default value and can be utilized to test
                    generating an DbInsertCommandTree with a Returning property
                    value that is not null.
          -->
          <Property Name="Discount" Type="real" Nullable="false" />
          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
        </EntityType>
        <EntityType Name="Orders">
          <Key>
            <PropertyRef Name="OrderID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />




︙





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






︙


133
134
135
136
137
138
139






140

141
142
143
144
145
146
147

133
134
135
136
137
138
139
140
141
142
143
144
145

146
147
148
149
150
151
152
153








+
+
+
+
+
+
-
+








            <PropertyRef Name="OrderID" />
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" />
          <Property Name="ProductID" Type="integer" Nullable="false" />
          <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
          <Property Name="Quantity" Type="smallint" Nullable="false" />
          <!--
              NOTE: The "Discount" column is not actually generated by the store;
                    however, it has a default value and can be utilized to test
                    generating an DbInsertCommandTree with a Returning property
                    value that is not null.
          -->
          <Property Name="Discount" Type="real" Nullable="false" />
          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
        </EntityType>
        <EntityType Name="Orders">
          <Key>
            <PropertyRef Name="OrderID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />




︙





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






︙


133
134
135
136
137
138
139






140

141
142
143
144
145
146
147

133
134
135
136
137
138
139
140
141
142
143
144
145

146
147
148
149
150
151
152
153








+
+
+
+
+
+
-
+








            <PropertyRef Name="OrderID" />
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" />
          <Property Name="ProductID" Type="integer" Nullable="false" />
          <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
          <Property Name="Quantity" Type="smallint" Nullable="false" />
          <!--
              NOTE: The "Discount" column is not actually generated by the store;
                    however, it has a default value and can be utilized to test
                    generating an DbInsertCommandTree with a Returning property
                    value that is not null.
          -->
          <Property Name="Discount" Type="real" Nullable="false" />
          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
        </EntityType>
        <EntityType Name="Orders">
          <Key>
            <PropertyRef Name="OrderID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />




︙





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






︙


133
134
135
136
137
138
139






140

141
142
143
144
145
146
147

133
134
135
136
137
138
139
140
141
142
143
144
145

146
147
148
149
150
151
152
153








+
+
+
+
+
+
-
+








            <PropertyRef Name="OrderID" />
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" />
          <Property Name="ProductID" Type="integer" Nullable="false" />
          <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
          <Property Name="Quantity" Type="smallint" Nullable="false" />
          <!--
              NOTE: The "Discount" column is not actually generated by the store;
                    however, it has a default value and can be utilized to test
                    generating an DbInsertCommandTree with a Returning property
                    value that is not null.
          -->
          <Property Name="Discount" Type="real" Nullable="false" />
          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
        </EntityType>
        <EntityType Name="Orders">
          <Key>
            <PropertyRef Name="OrderID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />




︙





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






︙


133
134
135
136
137
138
139






140

141
142
143
144
145
146
147

133
134
135
136
137
138
139
140
141
142
143
144
145

146
147
148
149
150
151
152
153








+
+
+
+
+
+
-
+








            <PropertyRef Name="OrderID" />
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" />
          <Property Name="ProductID" Type="integer" Nullable="false" />
          <Property Name="UnitPrice" Type="decimal" Nullable="false" Precision="53" />
          <Property Name="Quantity" Type="smallint" Nullable="false" />
          <!--
              NOTE: The "Discount" column is not actually generated by the store;
                    however, it has a default value and can be utilized to test
                    generating an DbInsertCommandTree with a Returning property
                    value that is not null.
          -->
          <Property Name="Discount" Type="real" Nullable="false" />
          <Property Name="Discount" Type="real" Nullable="false" StoreGeneratedPattern="Computed" />
        </EntityType>
        <EntityType Name="Orders">
          <Key>
            <PropertyRef Name="OrderID" />
          </Key>
          <Property Name="OrderID" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="CustomerID" Type="nvarchar" MaxLength="5" />




︙





Changes to testlinq/Program.cs.






︙


140
141
142
143
144
145
146




147
148
149
150
151
152
153

140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157








+
+
+
+









                              return 1;
                          }
                      }

                      return EFTransactionTest(value);
                  }
              case "insert":
                  {
                      return InsertTest();
                  }
              case "update":
                  {
                      return UpdateTest();
                  }
              case "binaryguid":
                  {
                      bool value = false;




︙


534
535
536
537
538
539
540























































541
542
543
544
545
546
547

538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606








+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+








                      Console.Write(territories.TerritoryID);

                      once = true;
                  }
#endif
              }
          }

          return 0;
      }

      //
      // NOTE: Used to test the INSERT fix (i.e. an extra semi-colon in
      //       the SQL statement after the actual INSERT statement in
      //       the follow-up SELECT statement).
      //
      private static int InsertTest()
      {
          long[] orderIds = new long[] {
              0
          };

          using (northwindEFEntities db = new northwindEFEntities())
          {
              int[] counts = { 0 };

              //
              // NOTE: *REQUIRED* This is required so that the
              //       Entity Framework is prevented from opening
              //       multiple connections to the underlying SQLite
              //       database (i.e. which would result in multiple
              //       IMMEDIATE transactions, thereby failing [later
              //       on] with locking errors).
              //
              db.Connection.Open();

              OrderDetails newOrderDetails = new OrderDetails();

              newOrderDetails.OrderID = 10248;
              newOrderDetails.ProductID = 1;
              newOrderDetails.UnitPrice = (decimal)1.23;
              newOrderDetails.Quantity = 1;
              newOrderDetails.Discount = 0.0f;

              db.AddObject("OrderDetails", newOrderDetails);

              try
              {
                  db.SaveChanges();
                  counts[0]++;
              }
              catch (Exception e)
              {
                  Console.WriteLine(e);
              }
              finally
              {
                  db.AcceptAllChanges();
              }

              Console.WriteLine("inserted {0}", counts[0]);
          }

          return 0;
      }

      //
      // NOTE: Used to test the UPDATE fix (i.e. the missing semi-colon
      //       in the SQL statement between the actual UPDATE statement




︙





Changes to www/news.wiki.





1
2
3
4
5
6
7
8
9
10

11
12
13
14
15
16
17

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18











+








<title>News</title>

<b>Version History</b>

<p>
    <b>1.0.98.0 - August XX, 2015 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>Updated to [https://www.sqlite.org/draft/releaselog/3_8_11.html|SQLite 3.8.11].</li>
    <li>Implement the Substring method for LINQ using the &quot;substr&quot; core SQL function.&nbsp;<b>** Potentially Incompatible Change **</b></li>
    <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>
    <li>Add VfsName connection string property to allow a non-default VFS to be used by the SQLite core library.</li>
    <li>Add BusyTimeout connection string property to set the busy timeout to be used by the SQLite core library.</li>
    <li>Enable integration with the [http://www.hwaci.com/sw/sqlite/zipvfs.html|ZipVFS] extension.</li>
</ul>
<p>
    <b>1.0.97.0 - May 26, 2015</b>
</p>




︙