System.Data.SQLite
Check-in [f74a1ac3f1]
Not logged in

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

Overview
Comment:Add static ToUnixEpoch method to the SQLiteConvert class, update docs and tests.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f74a1ac3f1561796829d6d3b7ab40ed05bf5055f
User & Date: mistachkin 2012-02-20 06:17:08
Context
2012-02-20
13:17
Add experimental support to the new design-time component installer for Visual Studio 2005. check-in: 6efd2f01bb user: mistachkin tags: trunk
06:17
Add static ToUnixEpoch method to the SQLiteConvert class, update docs and tests. check-in: f74a1ac3f1 user: mistachkin tags: trunk
2012-02-19
13:42
More doc updates for release 1.0.80.0. check-in: 9a01e7715d user: mistachkin tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to Doc/Extra/version.html.

44
45
46
47
48
49
50

51
52
53
54
55
56
57
    <div id="mainBody">
    <h1 class="heading">Version History</h1>
    <p><b>1.0.80.0 - April XX, 2012</b></p>
    <ul>
      <li>In the SQLiteFunction class, when calling user-provided methods from a delegate called by native code, avoid throwing exceptions, optionally tracing the caught exceptions. Fix for <a href="http://system.data.sqlite.org/index.html/info/8a426d12eb">[8a426d12eb]</a>.</li>
      <li>Support tracing of all parameter binding activity and use the connection flags to control what is traced.</li>
      <li>When converting a DateTime instance of an &quot;Unspecified&quot; kind to a string, use the same kind as the connection, if available.</li>

      <li>In the implicit conversion operators (to IntPtr) for both the SQLiteConnectionHandle and SQLiteStatementHandle classes, return IntPtr.Zero if the instance being converted is null.</li>
      <li>Write warning message to the active trace listners (for the Debug build configuration only) if a column type or type name cannot be mapped properly. See <a href="http://system.data.sqlite.org/index.html/info/4bbf851fa5">[4bbf851fa5]</a>.</li>
      <li>When tracing SQL statements to be prepared, bypass the internal length limit of the sqlite3_log function by using the SQLiteLog class directly instead. Also, detect null and/or empty strings and emit a special message in that case.</li>
      <li>For the setup, the Visual Studio task should only be initially checked if the GAC task is available and vice-versa.</li>
      <li>Improve compatibility with custom command processors by using __ECHO instead of _ECHO in batch tools.</li>
      <li>Add OpenAndReturn method to the SQLiteConnection class to open a connection and return it.</li>
      <li>Add missing CheckDisposed calls to the SQLiteConnection class.</li>







>







44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
    <div id="mainBody">
    <h1 class="heading">Version History</h1>
    <p><b>1.0.80.0 - April XX, 2012</b></p>
    <ul>
      <li>In the SQLiteFunction class, when calling user-provided methods from a delegate called by native code, avoid throwing exceptions, optionally tracing the caught exceptions. Fix for <a href="http://system.data.sqlite.org/index.html/info/8a426d12eb">[8a426d12eb]</a>.</li>
      <li>Support tracing of all parameter binding activity and use the connection flags to control what is traced.</li>
      <li>When converting a DateTime instance of an &quot;Unspecified&quot; kind to a string, use the same kind as the connection, if available.</li>
      <li>Add static ToUnixEpoch method to the SQLiteConvert class to convert a DateTime value to the number of whole seconds since the Unix epoch.</li>
      <li>In the implicit conversion operators (to IntPtr) for both the SQLiteConnectionHandle and SQLiteStatementHandle classes, return IntPtr.Zero if the instance being converted is null.</li>
      <li>Write warning message to the active trace listners (for the Debug build configuration only) if a column type or type name cannot be mapped properly. See <a href="http://system.data.sqlite.org/index.html/info/4bbf851fa5">[4bbf851fa5]</a>.</li>
      <li>When tracing SQL statements to be prepared, bypass the internal length limit of the sqlite3_log function by using the SQLiteLog class directly instead. Also, detect null and/or empty strings and emit a special message in that case.</li>
      <li>For the setup, the Visual Studio task should only be initially checked if the GAC task is available and vice-versa.</li>
      <li>Improve compatibility with custom command processors by using __ECHO instead of _ECHO in batch tools.</li>
      <li>Add OpenAndReturn method to the SQLiteConnection class to open a connection and return it.</li>
      <li>Add missing CheckDisposed calls to the SQLiteConnection class.</li>

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

357
358
359
360
361
362
363











364
365
366
367
368
369
370
    /// </summary>
    /// <param name="value">The DateTime to convert</param>
    /// <returns>The JulianDay value the Datetime represents</returns>
    public static double ToJulianDay(DateTime value)
    {
      return value.ToOADate() + OleAutomationEpochAsJulianDay;
    }












    /// <summary>
    /// Returns the default DateTime format string to use for the specified
    /// DateTimeKind.
    /// </summary>
    /// <param name="kind">The DateTimeKind to use.</param>
    /// <returns>







>
>
>
>
>
>
>
>
>
>
>







357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
    /// </summary>
    /// <param name="value">The DateTime to convert</param>
    /// <returns>The JulianDay value the Datetime represents</returns>
    public static double ToJulianDay(DateTime value)
    {
      return value.ToOADate() + OleAutomationEpochAsJulianDay;
    }

    /// <summary>
    /// Converts a DateTime struct to the whole number of seconds since the
    /// Unix epoch.
    /// </summary>
    /// <param name="value">The DateTime to convert</param>
    /// <returns>The whole number of seconds since the Unix epoch</returns>
    public static long ToUnixEpoch(DateTime value)
    {
        return (value.Subtract(UnixEpoch).Ticks / TimeSpan.TicksPerSecond);
    }

    /// <summary>
    /// Returns the default DateTime format string to use for the specified
    /// DateTimeKind.
    /// </summary>
    /// <param name="kind">The DateTimeKind to use.</param>
    /// <returns>

Changes to Tests/basic.eagle.

1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
....
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183








































































1184
1185
1186
1187
1188
1189
runTest {test data-1.18 {SQLiteConvert ToJulianDay} -body {
  expr {round([object invoke System.Data.SQLite.SQLiteConvert ToJulianDay \
      "2012-01-01 12:00:00Z"])}
} -constraints {eagle System.Data.SQLite} -result {2455928}}

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

runTest {test data-1.19 {LINQ SQL_CONSTRAINTCOLUMNS resource} -body {
  object invoke -flags +NonPublic System.Data.SQLite.Properties.Resources \
      SQL_CONSTRAINTCOLUMNS
} -constraints {eagle System.Data.SQLite System.Data.SQLite.Linq} -result {
      CREATE TEMP VIEW SCHEMACONSTRAINTCOLUMNS AS
        SELECT CONSTRAINT_CATALOG,
               NULL AS CONSTRAINT_SCHEMA,
               CONSTRAINT_NAME,
               TABLE_CATALOG,
               NULL AS TABLE_SCHEMA,
               TABLE_NAME,
               COLUMN_NAME
        FROM TEMP.SCHEMAINDEXCOLUMNS
        UNION
        SELECT CONSTRAINT_CATALOG,
               NULL,
               CONSTRAINT_NAME,
               TABLE_CATALOG,
               NULL,
               TABLE_NAME,
               FKEY_FROM_COLUMN
        FROM TEMP.SCHEMAFOREIGNKEYS;
    }}

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

runTest {test data-1.20 {LINQ SQL_CONSTRAINTS resource} -body {
  object invoke -flags +NonPublic System.Data.SQLite.Properties.Resources \
      SQL_CONSTRAINTS
} -constraints {eagle System.Data.SQLite System.Data.SQLite.Linq} -result {
      CREATE TEMP VIEW SCHEMACONSTRAINTS AS
        SELECT INDEX_CATALOG AS CONSTRAINT_CATALOG,
               NULL AS CONSTRAINT_SCHEMA,
               INDEX_NAME AS CONSTRAINT_NAME,
               TABLE_CATALOG,
               NULL AS TABLE_SCHEMA,
               TABLE_NAME,
               'PRIMARY KEY' AS CONSTRAINT_TYPE,
               0 AS IS_DEFERRABLE,
               0 AS INITIALLY_DEFERRED,
               NULL AS CHECK_CLAUSE
        FROM TEMP.SCHEMAINDEXES
        WHERE PRIMARY_KEY = 1
        UNION
        SELECT INDEX_CATALOG,
               NULL,
               INDEX_NAME,
               TABLE_CATALOG,
               NULL,
               TABLE_NAME,
               'UNIQUE',
               0,
               0,
               NULL
        FROM TEMP.SCHEMAINDEXES
        WHERE PRIMARY_KEY = 0 AND [UNIQUE] = 1
        UNION
        SELECT CONSTRAINT_CATALOG,
               NULL,
               CONSTRAINT_NAME,
               TABLE_CATALOG,
               NULL,
               TABLE_NAME,
               CONSTRAINT_TYPE,
               IS_DEFERRABLE,
               INITIALLY_DEFERRED,
               NULL
        FROM TEMP.SCHEMAFOREIGNKEYS;
    }}

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

runTest {test data-1.21 {SQLiteTransaction disposal} -setup {
  setupDb [set fileName data-1.21.db]
} -body {
  sql execute $db "CREATE TABLE t1(x TEXT);"
................................................................................
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result results errors code sql dataSource id db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -match \
regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\}$}}

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









































































unset -nocomplain testExeFile testLinqExeFile northwindEfDbFile testLinqOutFile

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

runSQLiteTestEpilogue
runTestEpilogue







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 










>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>






1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070




























1071
1072
1073
1074
1075
1076
1077
....
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
runTest {test data-1.18 {SQLiteConvert ToJulianDay} -body {
  expr {round([object invoke System.Data.SQLite.SQLiteConvert ToJulianDay \
      "2012-01-01 12:00:00Z"])}
} -constraints {eagle System.Data.SQLite} -result {2455928}}

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

runTest {test data-1.19 {SQLiteConvert ToUnixEpoch (Utc)} -body {
  #
  # NOTE: At first, the test result here may not seem correct; however, the
  #       same result can be seen by compiling and running the following C#
  #       code fragment together with the ToUnixEpoch method:
  #
  #           DateTime dateTime;
  #           Console.WriteLine("dateTime = {0}, unixTime = {1}",
  #               dateTime = DateTime.Parse("2012-01-01 12:00:00Z"),
  #               ToUnixEpoch(dateTime));
  #
  #       The basic problem here is that the Parse [and TryParse] methods of
  #       the DateTime structure seem to always return local time, even when
  #       string value clearly indicates otherwise (i.e. the trailing "Z",
  #       indicating UTC).
  #
  expr {round([object invoke System.Data.SQLite.SQLiteConvert ToUnixEpoch \
      "2012-01-01 12:00:00Z"])}
} -constraints {eagle System.Data.SQLite} -result {1325390400}}

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

runTest {test data-1.20 {SQLiteConvert ToUnixEpoch (Local)} -body {
  #
  # NOTE: At first, the test result here may not seem correct; however, the
  #       same result can be seen by compiling and running the following C#
  #       code fragment together with the ToUnixEpoch method:
  #
  #           DateTime dateTime;
  #           Console.WriteLine("dateTime = {0}, unixTime = {1}",
  #               dateTime = DateTime.Parse("2012-01-01 12:00:00"),
  #               ToUnixEpoch(dateTime));
  #
  #       The basic problem here is that the Parse [and TryParse] methods of
  #       the DateTime structure seem to always return local time, even when
  #       string value clearly indicates otherwise (i.e. the trailing "Z",
  #       indicating UTC).
  #
  expr {round([object invoke System.Data.SQLite.SQLiteConvert ToUnixEpoch \
      "2012-01-01 12:00:00"])}
} -constraints {eagle System.Data.SQLite} -result {1325419200}}





























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

runTest {test data-1.21 {SQLiteTransaction disposal} -setup {
  setupDb [set fileName data-1.21.db]
} -body {
  sql execute $db "CREATE TABLE t1(x TEXT);"
................................................................................
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result results errors code sql dataSource id db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -match \
regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\}$}}

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

runTest {test data-1.22 {LINQ SQL_CONSTRAINTCOLUMNS resource} -body {
  object invoke -flags +NonPublic System.Data.SQLite.Properties.Resources \
      SQL_CONSTRAINTCOLUMNS
} -constraints {eagle System.Data.SQLite System.Data.SQLite.Linq} -result {
      CREATE TEMP VIEW SCHEMACONSTRAINTCOLUMNS AS
        SELECT CONSTRAINT_CATALOG,
               NULL AS CONSTRAINT_SCHEMA,
               CONSTRAINT_NAME,
               TABLE_CATALOG,
               NULL AS TABLE_SCHEMA,
               TABLE_NAME,
               COLUMN_NAME
        FROM TEMP.SCHEMAINDEXCOLUMNS
        UNION
        SELECT CONSTRAINT_CATALOG,
               NULL,
               CONSTRAINT_NAME,
               TABLE_CATALOG,
               NULL,
               TABLE_NAME,
               FKEY_FROM_COLUMN
        FROM TEMP.SCHEMAFOREIGNKEYS;
    }}

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

runTest {test data-1.23 {LINQ SQL_CONSTRAINTS resource} -body {
  object invoke -flags +NonPublic System.Data.SQLite.Properties.Resources \
      SQL_CONSTRAINTS
} -constraints {eagle System.Data.SQLite System.Data.SQLite.Linq} -result {
      CREATE TEMP VIEW SCHEMACONSTRAINTS AS
        SELECT INDEX_CATALOG AS CONSTRAINT_CATALOG,
               NULL AS CONSTRAINT_SCHEMA,
               INDEX_NAME AS CONSTRAINT_NAME,
               TABLE_CATALOG,
               NULL AS TABLE_SCHEMA,
               TABLE_NAME,
               'PRIMARY KEY' AS CONSTRAINT_TYPE,
               0 AS IS_DEFERRABLE,
               0 AS INITIALLY_DEFERRED,
               NULL AS CHECK_CLAUSE
        FROM TEMP.SCHEMAINDEXES
        WHERE PRIMARY_KEY = 1
        UNION
        SELECT INDEX_CATALOG,
               NULL,
               INDEX_NAME,
               TABLE_CATALOG,
               NULL,
               TABLE_NAME,
               'UNIQUE',
               0,
               0,
               NULL
        FROM TEMP.SCHEMAINDEXES
        WHERE PRIMARY_KEY = 0 AND [UNIQUE] = 1
        UNION
        SELECT CONSTRAINT_CATALOG,
               NULL,
               CONSTRAINT_NAME,
               TABLE_CATALOG,
               NULL,
               TABLE_NAME,
               CONSTRAINT_TYPE,
               IS_DEFERRABLE,
               INITIALLY_DEFERRED,
               NULL
        FROM TEMP.SCHEMAFOREIGNKEYS;
    }}

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

unset -nocomplain testExeFile testLinqExeFile northwindEfDbFile testLinqOutFile

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

runSQLiteTestEpilogue
runTestEpilogue

Changes to readme.htm.

189
190
191
192
193
194
195

196
197
198
199
200
201
202
<p>
    <b>1.0.80.0 - April XX, 2012</b>
</p>
<ul>
    <li>In the SQLiteFunction class, when calling user-provided methods from a delegate called by native code, avoid throwing exceptions, optionally tracing the caught exceptions. Fix for [8a426d12eb].</li>
    <li>Support tracing of all parameter binding activity and use the connection flags to control what is traced.</li>
    <li>When converting a DateTime instance of an &quot;Unspecified&quot; kind to a string, use the same kind as the connection, if available.</li>

    <li>In the implicit conversion operators (to IntPtr) for both the SQLiteConnectionHandle and SQLiteStatementHandle classes, return IntPtr.Zero if the instance being converted is null.</li>
    <li>Write warning message to the active trace listners (for the Debug build configuration only) if a column type or type name cannot be mapped properly. See [4bbf851fa5].</li>
    <li>When tracing SQL statements to be prepared, bypass the internal length limit of the sqlite3_log function by using the SQLiteLog class directly instead. Also, detect null and/or empty strings and emit a special message in that case.</li>
    <li>For the setup, the Visual Studio task should only be initially checked if the GAC task is available and vice-versa.</li>
    <li>Improve compatibility with custom command processors by using __ECHO instead of _ECHO in batch tools.</li>
    <li>Add OpenAndReturn method to the SQLiteConnection class to open a connection and return it.</li>
    <li>Add missing CheckDisposed calls to the SQLiteConnection class.</li>







>







189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
<p>
    <b>1.0.80.0 - April XX, 2012</b>
</p>
<ul>
    <li>In the SQLiteFunction class, when calling user-provided methods from a delegate called by native code, avoid throwing exceptions, optionally tracing the caught exceptions. Fix for [8a426d12eb].</li>
    <li>Support tracing of all parameter binding activity and use the connection flags to control what is traced.</li>
    <li>When converting a DateTime instance of an &quot;Unspecified&quot; kind to a string, use the same kind as the connection, if available.</li>
    <li>Add static ToUnixEpoch method to the SQLiteConvert class to convert a DateTime value to the number of whole seconds since the Unix epoch.</li>
    <li>In the implicit conversion operators (to IntPtr) for both the SQLiteConnectionHandle and SQLiteStatementHandle classes, return IntPtr.Zero if the instance being converted is null.</li>
    <li>Write warning message to the active trace listners (for the Debug build configuration only) if a column type or type name cannot be mapped properly. See [4bbf851fa5].</li>
    <li>When tracing SQL statements to be prepared, bypass the internal length limit of the sqlite3_log function by using the SQLiteLog class directly instead. Also, detect null and/or empty strings and emit a special message in that case.</li>
    <li>For the setup, the Visual Studio task should only be initially checked if the GAC task is available and vice-versa.</li>
    <li>Improve compatibility with custom command processors by using __ECHO instead of _ECHO in batch tools.</li>
    <li>Add OpenAndReturn method to the SQLiteConnection class to open a connection and return it.</li>
    <li>Add missing CheckDisposed calls to the SQLiteConnection class.</li>

Changes to www/news.wiki.

5
6
7
8
9
10
11

12
13
14
15
16
17
18
<p>
    <b>1.0.80.0 - April XX, 2012 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>In the SQLiteFunction class, when calling user-provided methods from a delegate called by native code, avoid throwing exceptions, optionally tracing the caught exceptions. Fix for [8a426d12eb].</li>
    <li>Support tracing of all parameter binding activity and use the connection flags to control what is traced.</li>
    <li>When converting a DateTime instance of an &quot;Unspecified&quot; kind to a string, use the same kind as the connection, if available.</li>

    <li>In the implicit conversion operators (to IntPtr) for both the SQLiteConnectionHandle and SQLiteStatementHandle classes, return IntPtr.Zero if the instance being converted is null.</li>
    <li>Write warning message to the active trace listners (for the Debug build configuration only) if a column type or type name cannot be mapped properly. See [4bbf851fa5].</li>
    <li>When tracing SQL statements to be prepared, bypass the internal length limit of the sqlite3_log function by using the SQLiteLog class directly instead. Also, detect null and/or empty strings and emit a special message in that case.</li>
    <li>For the setup, the Visual Studio task should only be initially checked if the GAC task is available and vice-versa.</li>
    <li>Improve compatibility with custom command processors by using __ECHO instead of _ECHO in batch tools.</li>
    <li>Add OpenAndReturn method to the SQLiteConnection class to open a connection and return it.</li>
    <li>Add missing CheckDisposed calls to the SQLiteConnection class.</li>







>







5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<p>
    <b>1.0.80.0 - April XX, 2012 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>In the SQLiteFunction class, when calling user-provided methods from a delegate called by native code, avoid throwing exceptions, optionally tracing the caught exceptions. Fix for [8a426d12eb].</li>
    <li>Support tracing of all parameter binding activity and use the connection flags to control what is traced.</li>
    <li>When converting a DateTime instance of an &quot;Unspecified&quot; kind to a string, use the same kind as the connection, if available.</li>
    <li>Add static ToUnixEpoch method to the SQLiteConvert class to convert a DateTime value to the number of whole seconds since the Unix epoch.</li>
    <li>In the implicit conversion operators (to IntPtr) for both the SQLiteConnectionHandle and SQLiteStatementHandle classes, return IntPtr.Zero if the instance being converted is null.</li>
    <li>Write warning message to the active trace listners (for the Debug build configuration only) if a column type or type name cannot be mapped properly. See [4bbf851fa5].</li>
    <li>When tracing SQL statements to be prepared, bypass the internal length limit of the sqlite3_log function by using the SQLiteLog class directly instead. Also, detect null and/or empty strings and emit a special message in that case.</li>
    <li>For the setup, the Visual Studio task should only be initially checked if the GAC task is available and vice-versa.</li>
    <li>Improve compatibility with custom command processors by using __ECHO instead of _ECHO in batch tools.</li>
    <li>Add OpenAndReturn method to the SQLiteConnection class to open a connection and return it.</li>
    <li>Add missing CheckDisposed calls to the SQLiteConnection class.</li>