System.Data.SQLite
Check-in [59d87c8bce]
Not logged in

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

Overview
Comment:Add GetDecimalAsText connection flag to force Decimal typed columns to be returned as text. Pursuant to [b167206ad3].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 59d87c8bcea9e494568c461ad8609cb0bf4be9a6
User & Date: mistachkin 2017-09-07 20:06:43
References
2017-09-07
20:07 Ticket [b167206ad3] Large decimals gets rounded (123456789123456780 -> 123456789123457000) status still Pending with 3 other changes artifact: 4cb65654ef user: mistachkin
Context
2017-09-07
20:21
Update SQLite core library to the 3.20.1 release. check-in: 6ba379107f user: mistachkin tags: trunk
20:06
Add GetDecimalAsText connection flag to force Decimal typed columns to be returned as text. Pursuant to [b167206ad3]. check-in: 59d87c8bce user: mistachkin tags: trunk
19:56
In the test suite infrastructure, skip over scanning the release directory if it does not exist. check-in: 82655edf58 user: mistachkin tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

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

42
43
44
45
46
47
48

49
50
51
52
53
54
55
    </div>
    <div id="mainSection">
    <div id="mainBody">
    <h1 class="heading">Version History</h1>
    <p><b>1.0.106.0 - September XX, 2017 <font color="red">(release scheduled)</font></b></p>
    <ul>
      <li>Updated to <a href="https://www.sqlite.org/releaselog/3_20_0.html">SQLite 3.20.0</a>.</li>

    </ul>
    <p><b>1.0.105.2 - June 12, 2017</b></p>
    <ul>
      <li>Updated to <a href="https://www.sqlite.org/releaselog/3_19_3.html">SQLite 3.19.3</a>.</li>
      <li>Fix issues that prevented SQLiteBlob creation from succeeding for tables that did not have an integer primary key.</li>
    </ul>
    <p><b>1.0.105.1 - May 15, 2017</b></p>







>







42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
    </div>
    <div id="mainSection">
    <div id="mainBody">
    <h1 class="heading">Version History</h1>
    <p><b>1.0.106.0 - September XX, 2017 <font color="red">(release scheduled)</font></b></p>
    <ul>
      <li>Updated to <a href="https://www.sqlite.org/releaselog/3_20_0.html">SQLite 3.20.0</a>.</li>
      <li>Add GetDecimalAsText connection flag to force Decimal typed columns to be returned as text. Pursuant to <a href="https://system.data.sqlite.org/index.html/info/b167206ad3">[b167206ad3]</a>.</li>
    </ul>
    <p><b>1.0.105.2 - June 12, 2017</b></p>
    <ul>
      <li>Updated to <a href="https://www.sqlite.org/releaselog/3_19_3.html">SQLite 3.19.3</a>.</li>
      <li>Fix issues that prevented SQLiteBlob creation from succeeding for tables that did not have an integer primary key.</li>
    </ul>
    <p><b>1.0.105.1 - May 15, 2017</b></p>

Changes to System.Data.SQLite/SQLite3.cs.

3263
3264
3265
3266
3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277
      TypeAffinity aff = typ.Affinity;
      if (aff == TypeAffinity.Null) return DBNull.Value;
      Type t = null;

      if (typ.Type != DbType.Object)
      {
        t = SQLiteConvert.SQLiteTypeToType(typ);
        aff = TypeToAffinity(t);
      }

      if ((flags & SQLiteConnectionFlags.GetAllAsText) == SQLiteConnectionFlags.GetAllAsText)
          return GetText(stmt, index);

      switch (aff)
      {







|







3263
3264
3265
3266
3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277
      TypeAffinity aff = typ.Affinity;
      if (aff == TypeAffinity.Null) return DBNull.Value;
      Type t = null;

      if (typ.Type != DbType.Object)
      {
        t = SQLiteConvert.SQLiteTypeToType(typ);
        aff = TypeToAffinity(t, flags);
      }

      if ((flags & SQLiteConnectionFlags.GetAllAsText) == SQLiteConnectionFlags.GetAllAsText)
          return GetText(stmt, index);

      switch (aff)
      {

Changes to System.Data.SQLite/SQLiteBase.cs.

1241
1242
1243
1244
1245
1246
1247







1248
1249
1250
1251
1252
1253
1254
      /// Allow transactions to be nested.  The outermost transaction still
      /// controls whether or not any changes are ultimately committed or
      /// rolled back.  All non-outermost transactions are implemented using
      /// the SAVEPOINT construct.
      /// </summary>
      AllowNestedTransactions = 0x8000000000,








      /// <summary>
      /// When binding parameter values or returning column values, always
      /// treat them as though they were plain text (i.e. no numeric,
      /// date/time, or other conversions should be attempted).
      /// </summary>
      BindAndGetAllAsText = BindAllAsText | GetAllAsText,








>
>
>
>
>
>
>







1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
      /// Allow transactions to be nested.  The outermost transaction still
      /// controls whether or not any changes are ultimately committed or
      /// rolled back.  All non-outermost transactions are implemented using
      /// the SAVEPOINT construct.
      /// </summary>
      AllowNestedTransactions = 0x8000000000,

      /// <summary>
      /// When returning column values, always return <see cref="Decimal" />
      /// values as though they were plain text (i.e. not <see cref="Double" />,
      /// which is the legacy behavior).
      /// </summary>
      GetDecimalAsText = 0x10000000000,

      /// <summary>
      /// When binding parameter values or returning column values, always
      /// treat them as though they were plain text (i.e. no numeric,
      /// date/time, or other conversions should be attempted).
      /// </summary>
      BindAndGetAllAsText = BindAllAsText | GetAllAsText,

Changes to System.Data.SQLite/SQLiteConvert.cs.

1621
1622
1623
1624
1625
1626
1627

1628
1629



1630
1631
1632
1633
1634
1635
1636





1637
1638
1639
1640
1641
1642
1643
      typeof(string),   // Xml (25)
    };

    /// <summary>
    /// For a given type, return the closest-match SQLite TypeAffinity, which only understands a very limited subset of types.
    /// </summary>
    /// <param name="typ">The type to evaluate</param>

    /// <returns>The SQLite type affinity for that type.</returns>
    internal static TypeAffinity TypeToAffinity(Type typ)



    {
      TypeCode tc = Type.GetTypeCode(typ);
      if (tc == TypeCode.Object)
      {
        if (typ == typeof(byte[]) || typ == typeof(Guid))
          return TypeAffinity.Blob;
        else





          return TypeAffinity.Text;
      }
      return _typecodeAffinities[(int)tc];
    }

    private static TypeAffinity[] _typecodeAffinities = {
      TypeAffinity.Null,     // Empty (0)







>

|
>
>
>







>
>
>
>
>







1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
      typeof(string),   // Xml (25)
    };

    /// <summary>
    /// For a given type, return the closest-match SQLite TypeAffinity, which only understands a very limited subset of types.
    /// </summary>
    /// <param name="typ">The type to evaluate</param>
    /// <param name="flags">The flags associated with the connection.</param>
    /// <returns>The SQLite type affinity for that type.</returns>
    internal static TypeAffinity TypeToAffinity(
        Type typ,
        SQLiteConnectionFlags flags
        )
    {
      TypeCode tc = Type.GetTypeCode(typ);
      if (tc == TypeCode.Object)
      {
        if (typ == typeof(byte[]) || typ == typeof(Guid))
          return TypeAffinity.Blob;
        else
          return TypeAffinity.Text;
      }
      if ((tc == TypeCode.Decimal) &&
          ((flags & SQLiteConnectionFlags.GetDecimalAsText) == SQLiteConnectionFlags.GetDecimalAsText))
      {
          return TypeAffinity.Text;
      }
      return _typecodeAffinities[(int)tc];
    }

    private static TypeAffinity[] _typecodeAffinities = {
      TypeAffinity.Null,     // Empty (0)

Changes to System.Data.SQLite/SQLiteFunction.cs.

373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
        if (r != null)
        {
          _base.ReturnError(context, r.Message);
          return;
        }
      }

      switch (SQLiteConvert.TypeToAffinity(t))
      {
        case TypeAffinity.Null:
          _base.ReturnNull(context);
          return;
        case TypeAffinity.Int64:
          _base.ReturnInt64(context, Convert.ToInt64(returnValue, CultureInfo.CurrentCulture));
          return;







|







373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
        if (r != null)
        {
          _base.ReturnError(context, r.Message);
          return;
        }
      }

      switch (SQLiteConvert.TypeToAffinity(t, _flags))
      {
        case TypeAffinity.Null:
          _base.ReturnNull(context);
          return;
        case TypeAffinity.Int64:
          _base.ReturnInt64(context, Convert.ToInt64(returnValue, CultureInfo.CurrentCulture));
          return;

Added Tests/tkt-b167206ad3.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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
###############################################################################
#
# tkt-b167206ad3.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

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

runTest {test tkt-b167206ad3-1.1 {type affinity for DECIMAL} -setup {
  setupDb [set fileName tkt-b167206ad3-1.1.db]
} -body {
  sql execute $db {
    CREATE TABLE t1(x INTEGER, y DECIMAL(38,9));
    INSERT INTO t1 (x, y) VALUES(1, 123456789123456780);
    INSERT INTO t1 (x, y) VALUES(2, '123456789123456780');
  }

  set dataReader [sql execute -execute reader -format datareader \
      -alias $db "SELECT x, y FROM t1 ORDER BY x;"]

  set result [list]

  while {[$dataReader Read]} {
    set x [$dataReader -create -alias GetValue 0]
    set y1 [$dataReader -create -alias GetDecimal 1]
    set y2 [$dataReader -create -alias GetValue 1]

    lappend result [list [$x ToString] [$y1 ToString] [$y2 ToString]]
  }

  set result
} -cleanup {
  unset -nocomplain dataReader

  cleanupDb $fileName

  unset -nocomplain y2 y1 x result db fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite} -result {{1 123456789123456780 123456789123457000} {2\
123456789123456780 123456789123457000}}}

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

runTest {test tkt-b167206ad3-1.2 {type affinity for DECIMAL} -setup {
  setupDb [set fileName tkt-b167206ad3-1.2.db] "" "" "" GetDecimalAsText
} -body {
  sql execute $db {
    CREATE TABLE t1(x INTEGER, y DECIMAL(38,9));
    INSERT INTO t1 (x, y) VALUES(1, 123456789123456780);
    INSERT INTO t1 (x, y) VALUES(2, '123456789123456780');
  }

  set dataReader [sql execute -execute reader -format datareader \
      -alias $db "SELECT x, y FROM t1 ORDER BY x;"]

  set result [list]

  while {[$dataReader Read]} {
    set x [$dataReader -create -alias GetValue 0]
    set y1 [$dataReader -create -alias GetDecimal 1]
    set y2 [$dataReader -create -alias GetValue 1]

    lappend result [list [$x ToString] [$y1 ToString] [$y2 ToString]]
  }

  set result
} -cleanup {
  unset -nocomplain dataReader

  cleanupDb $fileName

  unset -nocomplain y2 y1 x result db fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite} -result {{1 123456789123456780 123456789123456780} {2\
123456789123456780 123456789123456780}}}

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

runTest {test tkt-b167206ad3-1.3 {type affinity for NUMERIC} -setup {
  setupDb [set fileName tkt-b167206ad3-1.3.db]
} -body {
  sql execute $db {
    CREATE TABLE t1(x INTEGER, y NUMERIC);
    INSERT INTO t1 (x, y) VALUES(1, 123456789123456780);
    INSERT INTO t1 (x, y) VALUES(2, '123456789123456780');
  }

  set dataReader [sql execute -execute reader -format datareader \
      -alias $db "SELECT x, y FROM t1 ORDER BY x;"]

  set result [list]

  while {[$dataReader Read]} {
    set x [$dataReader -create -alias GetValue 0]
    set y1 [$dataReader -create -alias GetDecimal 1]
    set y2 [$dataReader -create -alias GetValue 1]

    lappend result [list [$x ToString] [$y1 ToString] [$y2 ToString]]
  }

  set result
} -cleanup {
  unset -nocomplain dataReader

  cleanupDb $fileName

  unset -nocomplain y2 y1 x result db fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite} -result {{1 123456789123456780 123456789123457000} {2\
123456789123456780 123456789123457000}}}

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

runTest {test tkt-b167206ad3-1.4 {type affinity for NUMERIC} -setup {
  setupDb [set fileName tkt-b167206ad3-1.4.db] "" "" "" GetDecimalAsText
} -body {
  sql execute $db {
    CREATE TABLE t1(x INTEGER, y NUMERIC);
    INSERT INTO t1 (x, y) VALUES(1, 123456789123456780);
    INSERT INTO t1 (x, y) VALUES(2, '123456789123456780');
  }

  set dataReader [sql execute -execute reader -format datareader \
      -alias $db "SELECT x, y FROM t1 ORDER BY x;"]

  set result [list]

  while {[$dataReader Read]} {
    set x [$dataReader -create -alias GetValue 0]
    set y1 [$dataReader -create -alias GetDecimal 1]
    set y2 [$dataReader -create -alias GetValue 1]

    lappend result [list [$x ToString] [$y1 ToString] [$y2 ToString]]
  }

  set result
} -cleanup {
  unset -nocomplain dataReader

  cleanupDb $fileName

  unset -nocomplain y2 y1 x result db fileName
} -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\
System.Data.SQLite} -result {{1 123456789123456780 123456789123456780} {2\
123456789123456780 123456789123456780}}}

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

runSQLiteTestEpilogue
runTestEpilogue

Changes to readme.htm.

208
209
210
211
212
213
214

215
216
217
218
219
220
221
<h2><b>Version History</b></h2>

<p>
    <b>1.0.106.0 - September XX, 2017 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>Updated to <a href="https://www.sqlite.org/releaselog/3_20_0.html">SQLite 3.20.0</a>.</li>

</ul>
<p>
    <b>1.0.105.2 - June 12, 2017</b>
</p>
<ul>
    <li>Updated to <a href="https://www.sqlite.org/releaselog/3_19_3.html">SQLite 3.19.3</a>.</li>
    <li>Fix issues that prevented SQLiteBlob creation from succeeding for tables that did not have an integer primary key.</li>







>







208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
<h2><b>Version History</b></h2>

<p>
    <b>1.0.106.0 - September XX, 2017 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>Updated to <a href="https://www.sqlite.org/releaselog/3_20_0.html">SQLite 3.20.0</a>.</li>
    <li>Add GetDecimalAsText connection flag to force Decimal typed columns to be returned as text. Pursuant to [b167206ad3].</li>
</ul>
<p>
    <b>1.0.105.2 - June 12, 2017</b>
</p>
<ul>
    <li>Updated to <a href="https://www.sqlite.org/releaselog/3_19_3.html">SQLite 3.19.3</a>.</li>
    <li>Fix issues that prevented SQLiteBlob creation from succeeding for tables that did not have an integer primary key.</li>

Changes to www/news.wiki.

45
46
47
48
49
50
51

52
53
54
55
56
57
58
<div align="center"><h2><b>Version History</b></h2></div>

<p>
    <b>1.0.106.0 - September XX, 2017 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>Updated to [https://www.sqlite.org/releaselog/3_20_0.html|SQLite 3.20.0].</li>

</ul>
<p>
    <b>1.0.105.2 - June 12, 2017</b>
</p>
<ul>
    <li>Updated to [https://www.sqlite.org/releaselog/3_19_3.html|SQLite 3.19.3].</li>
    <li>Fix issues that prevented SQLiteBlob creation from succeeding for tables that did not have an integer primary key.</li>







>







45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
<div align="center"><h2><b>Version History</b></h2></div>

<p>
    <b>1.0.106.0 - September XX, 2017 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>Updated to [https://www.sqlite.org/releaselog/3_20_0.html|SQLite 3.20.0].</li>
    <li>Add GetDecimalAsText connection flag to force Decimal typed columns to be returned as text. Pursuant to [b167206ad3].</li>
</ul>
<p>
    <b>1.0.105.2 - June 12, 2017</b>
</p>
<ul>
    <li>Updated to [https://www.sqlite.org/releaselog/3_19_3.html|SQLite 3.19.3].</li>
    <li>Fix issues that prevented SQLiteBlob creation from succeeding for tables that did not have an integer primary key.</li>