Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix extra single quotes around the formatted DateTime values in the LINQ assembly. Use GetSettingValue to access the new environment variables. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | tkt-8d928c3e88 |
Files: | files | file ages | folders |
SHA1: |
45312da90e224763e718532f9c715a5f |
User & Date: | mistachkin 2015-01-13 04:08:26.828 |
Context
2015-01-14
| ||
21:26 | Address all the LINQ issues mentioned in ticket [8d928c3e88]. Make the storage schema (SSDL) files more consistent with their provider names. check-in: 9ff0f0adf0 user: mistachkin tags: trunk | |
2015-01-13
| ||
04:08 | Fix extra single quotes around the formatted DateTime values in the LINQ assembly. Use GetSettingValue to access the new environment variables. Closed-Leaf check-in: 45312da90e user: mistachkin tags: tkt-8d928c3e88 | |
03:39 | In the LINQ assembly, when surrounding single quotes are required for a formatted DateTime value, be sure to escape contained single quotes. check-in: b999c9c818 user: mistachkin tags: tkt-8d928c3e88 | |
Changes
Changes to System.Data.SQLite.Linq/SQL Generation/SqlGenerator.cs.
︙ | ︙ | |||
116 117 118 119 120 121 122 | /// Renaming issues. /// When rows or columns are renamed, we produce names that are unique globally /// with respect to the query. The names are derived from the original names, /// with an integer as a suffix. e.g. CustomerId will be renamed to CustomerId1, /// CustomerId2 etc. /// /// Since the names generated are globally unique, they will not conflict when the | | | 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | /// Renaming issues. /// When rows or columns are renamed, we produce names that are unique globally /// with respect to the query. The names are derived from the original names, /// with an integer as a suffix. e.g. CustomerId will be renamed to CustomerId1, /// CustomerId2 etc. /// /// Since the names generated are globally unique, they will not conflict when the /// columns of a JOIN SELECT statement are joined with another JOIN. /// /// </para> /// /// <para> /// Record flattening. /// SQL server does not have the concept of records. However, a join statement /// produces records. We have to flatten the record accesses into a simple |
︙ | ︙ | |||
434 435 436 437 438 439 440 | return functionNameToOperatorDictionary; } #endregion #region Constructor /// <summary> | | | 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 | return functionNameToOperatorDictionary; } #endregion #region Constructor /// <summary> /// Basic constructor. /// </summary> private SqlGenerator(SQLiteProviderManifest manifest) { _manifest = manifest; } #endregion |
︙ | ︙ | |||
462 463 464 465 466 467 468 | //Handle Query DbQueryCommandTree queryCommandTree = tree as DbQueryCommandTree; if (queryCommandTree != null) { SqlGenerator sqlGen = new SqlGenerator(manifest); parameters = null; | | | 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 | //Handle Query DbQueryCommandTree queryCommandTree = tree as DbQueryCommandTree; if (queryCommandTree != null) { SqlGenerator sqlGen = new SqlGenerator(manifest); parameters = null; string sql = sqlGen.GenerateSql((DbQueryCommandTree)tree); return sql; } //Handle Function DbFunctionCommandTree DbFunctionCommandTree = tree as DbFunctionCommandTree; |
︙ | ︙ | |||
891 892 893 894 895 896 897 | string dateString = SQLiteConvert.ToString( (System.DateTime)e.Value, _manifest._dateTimeFormat, _manifest._dateTimeKind, _manifest._dateTimeFormatString); if (needQuotes) { | | | < | 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 | string dateString = SQLiteConvert.ToString( (System.DateTime)e.Value, _manifest._dateTimeFormat, _manifest._dateTimeKind, _manifest._dateTimeFormatString); if (needQuotes) { result.Append(EscapeSingleQuote( dateString, false /* IsUnicode */)); } else { result.Append(dateString); } break; |
︙ | ︙ | |||
1143 1144 1145 1146 1147 1148 1149 | /// Lambda functions are not supported. /// The functions supported are: /// <list type="number"> /// <item>Canonical Functions - We recognize these by their dataspace, it is DataSpace.CSpace</item> /// <item>Store Functions - We recognize these by the BuiltInAttribute and not being Canonical</item> /// <item>User-defined Functions - All the rest except for Lambda functions</item> /// </list> | | | 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 | /// Lambda functions are not supported. /// The functions supported are: /// <list type="number"> /// <item>Canonical Functions - We recognize these by their dataspace, it is DataSpace.CSpace</item> /// <item>Store Functions - We recognize these by the BuiltInAttribute and not being Canonical</item> /// <item>User-defined Functions - All the rest except for Lambda functions</item> /// </list> /// We handle Canonical and Store functions the same way: If they are in the list of functions /// that need special handling, we invoke the appropriate handler, otherwise we translate them to /// FunctionName(arg1, arg2, ..., argn). /// We translate user-defined functions to NamespaceName.FunctionName(arg1, arg2, ..., argn). /// </summary> /// <param name="e"></param> /// <returns>A <see cref="SqlBuilder"/></returns> public override ISqlFragment Visit(DbFunctionExpression e) |
︙ | ︙ | |||
1194 1195 1196 1197 1198 1199 1200 | } /// <summary> /// <see cref="Visit(DbFilterExpression)"/> for general details. /// We modify both the GroupBy and the Select fields of the SqlSelectStatement. /// GroupBy gets just the keys without aliases, /// and Select gets the keys and the aggregates with aliases. | | | | | | | | | | | | | | | | | | | 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 1234 1235 1236 1237 1238 1239 | } /// <summary> /// <see cref="Visit(DbFilterExpression)"/> for general details. /// We modify both the GroupBy and the Select fields of the SqlSelectStatement. /// GroupBy gets just the keys without aliases, /// and Select gets the keys and the aggregates with aliases. /// /// Whenever there exists at least one aggregate with an argument that is not is not a simple /// <see cref="DbPropertyExpression"/> over <see cref="DbVariableReferenceExpression"/>, /// we create a nested query in which we alias the arguments to the aggregates. /// That is due to the following two limitations of Sql Server: /// <list type="number"> /// <item>If an expression being aggregated contains an outer reference, then that outer /// reference must be the only column referenced in the expression </item> /// <item>Sql Server cannot perform an aggregate function on an expression containing /// an aggregate or a subquery. </item> /// </list> /// /// The default translation, without inner query is: /// /// SELECT /// kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn, /// aggf1(aexpr1) AS agg1, .. aggfn(aexprn) AS aggn /// FROM input AS a /// GROUP BY kexp1, kexp2, .. kexpn /// /// When we inject an innner query, the equivalent translation is: /// /// SELECT /// key1 AS key1, key2 AS key2, .. keyn AS keys, /// aggf1(agg1) AS agg1, aggfn(aggn) AS aggn /// FROM ( /// SELECT /// kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn, /// aexpr1 AS agg1, .. aexprn AS aggn /// FROM input AS a /// ) as a /// GROUP BY key1, key2, keyn /// /// </summary> /// <param name="e"></param> /// <returns>A <see cref="SqlSelectStatement"/></returns> public override ISqlFragment Visit(DbGroupByExpression e) { Symbol fromSymbol; //SqlSelectStatement result = VisitInputExpression(e.Input.Expression, |
︙ | ︙ | |||
1256 1257 1258 1259 1260 1261 1262 | symbolTable.Add(e.Input.GroupVariableName, fromSymbol); // The enumerator is shared by both the keys and the aggregates, // so, we do not close it in between. RowType groupByType = MetadataHelpers.GetEdmType<RowType>(MetadataHelpers.GetEdmType<CollectionType>(e.ResultType).TypeUsage); | | | 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 | symbolTable.Add(e.Input.GroupVariableName, fromSymbol); // The enumerator is shared by both the keys and the aggregates, // so, we do not close it in between. RowType groupByType = MetadataHelpers.GetEdmType<RowType>(MetadataHelpers.GetEdmType<CollectionType>(e.ResultType).TypeUsage); //Whenever there exists at least one aggregate with an argument that is not simply a PropertyExpression // over a VarRefExpression, we need a nested query in which we alias the arguments to the aggregates. bool needsInnerQuery = NeedsInnerQuery(e.Aggregates); SqlSelectStatement result; if (needsInnerQuery) { //Create the inner query |
︙ | ︙ | |||
1308 1309 1310 1311 1312 1313 1314 | // The inner query contains the default translation Key AS Alias innerQuery.Select.Append(separator); innerQuery.Select.AppendLine(); innerQuery.Select.Append(keySql); innerQuery.Select.Append(" AS "); innerQuery.Select.Append(alias); | | | 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 | // The inner query contains the default translation Key AS Alias innerQuery.Select.Append(separator); innerQuery.Select.AppendLine(); innerQuery.Select.Append(keySql); innerQuery.Select.Append(" AS "); innerQuery.Select.Append(alias); //The outer resulting query projects over the key aliased in the inner query: // fromSymbol.Alias AS Alias result.Select.Append(separator); result.Select.AppendLine(); result.Select.Append(fromSymbol); result.Select.Append("."); result.Select.Append(alias); result.Select.Append(" AS "); |
︙ | ︙ | |||
1482 1483 1484 1485 1486 1487 1488 | public override ISqlFragment Visit(DbLikeExpression e) { SqlBuilder result = new SqlBuilder(); result.Append(e.Argument.Accept(this)); result.Append(" LIKE "); result.Append(e.Pattern.Accept(this)); | | | 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 | public override ISqlFragment Visit(DbLikeExpression e) { SqlBuilder result = new SqlBuilder(); result.Append(e.Argument.Accept(this)); result.Append(" LIKE "); result.Append(e.Pattern.Accept(this)); // if the ESCAPE expression is a DbNullExpression, then that's tantamount to // not having an ESCAPE at all if (e.Escape.ExpressionKind != DbExpressionKind.Null) { result.Append(" ESCAPE "); result.Append(e.Escape.Accept(this)); } |
︙ | ︙ | |||
1832 1833 1834 1835 1836 1837 1838 | throw new NotSupportedException(); } /// <summary> /// For Sql9 it translates to: /// SELECT Y.x1, Y.x2, ..., Y.xn /// FROM ( | | | | | 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 | throw new NotSupportedException(); } /// <summary> /// For Sql9 it translates to: /// SELECT Y.x1, Y.x2, ..., Y.xn /// FROM ( /// SELECT X.x1, X.x2, ..., X.xn, row_number() OVER (ORDER BY sk1, sk2, ...) AS [row_number] /// FROM input as X /// ) as Y /// WHERE Y.[row_number] > count /// ORDER BY sk1, sk2, ... /// </summary> /// <param name="e"></param> /// <returns>A <see cref="SqlBuilder"/></returns> public override ISqlFragment Visit(DbSkipExpression e) { Debug.Assert(e.Count is DbConstantExpression || e.Count is DbParameterReferenceExpression, "DbLimitExpression.Count is of invalid expression type"); |
︙ | ︙ | |||
2635 2636 2637 2638 2639 2640 2641 | throw new InvalidOperationException("Special handling should be called only for functions in the list of special functions"); return handlers[e.Function.Name](this, e); } /// <summary> /// Handles functions that are translated into TSQL operators. | | | | 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 | throw new InvalidOperationException("Special handling should be called only for functions in the list of special functions"); return handlers[e.Function.Name](this, e); } /// <summary> /// Handles functions that are translated into TSQL operators. /// The given function should have one or two arguments. /// Functions with one arguemnt are translated into /// op arg /// Functions with two arguments are translated into /// arg0 op arg1 /// Also, the arguments can be optionaly enclosed in parethesis /// </summary> /// <param name="e"></param> /// <param name="parenthesiseArguments">Whether the arguments should be enclosed in parethesis</param> |
︙ | ︙ | |||
2847 2848 2849 2850 2851 2852 2853 | break; } return result; } /// <summary> | | | 2846 2847 2848 2849 2850 2851 2852 2853 2854 2855 2856 2857 2858 2859 2860 | break; } return result; } /// <summary> /// DateSubtract(datetime1, datetime2) -> DATEDIFF ( seconds , startdate , enddate ) /// </summary> /// <param name="sqlgen"></param> /// <param name="e"></param> /// <returns></returns> private static ISqlFragment HandleCanonicalFunctionDateSubtract(SqlGenerator sqlgen, DbFunctionExpression e) { SqlBuilder result = new SqlBuilder(); |
︙ | ︙ | |||
2871 2872 2873 2874 2875 2876 2877 | break; } return result; } /// <summary> | | | 2870 2871 2872 2873 2874 2875 2876 2877 2878 2879 2880 2881 2882 2883 2884 | break; } return result; } /// <summary> /// Handler for canonical functions for extracting date parts. /// For example: /// Year(date) -> DATEPART( year, date) /// </summary> /// <param name="sqlgen"></param> /// <param name="e"></param> /// <returns></returns> private static ISqlFragment HandleCanonicalFunctionDatepart(SqlGenerator sqlgen, DbFunctionExpression e) |
︙ | ︙ | |||
3330 3331 3332 3333 3334 3335 3336 | { symbolTable.Add(inputVarName, fromSymbol); } } /// <summary> /// Translates a list of SortClauses. | | | 3329 3330 3331 3332 3333 3334 3335 3336 3337 3338 3339 3340 3341 3342 3343 | { symbolTable.Add(inputVarName, fromSymbol); } } /// <summary> /// Translates a list of SortClauses. /// Used in the translation of OrderBy /// </summary> /// <param name="orderByClause">The SqlBuilder to which the sort keys should be appended</param> /// <param name="sortKeys"></param> void AddSortKeys(SqlBuilder orderByClause, IList<DbSortClause> sortKeys) { string separator = ""; foreach (DbSortClause sortClause in sortKeys) |
︙ | ︙ | |||
3474 3475 3476 3477 3478 3479 3480 | /// <returns>The escaped sql string.</returns> private static string EscapeSingleQuote(string s, bool isUnicode) { return "'" + s.Replace("'", "''") + "'"; } /// <summary> | | | | 3473 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 | /// <returns>The escaped sql string.</returns> private static string EscapeSingleQuote(string s, bool isUnicode) { return "'" + s.Replace("'", "''") + "'"; } /// <summary> /// Returns the sql primitive/native type name. /// It will include size, precision or scale depending on type information present in the /// type facets /// </summary> /// <param name="type"></param> /// <returns></returns> private string GetSqlPrimitiveType(TypeUsage type) { PrimitiveType primitiveType = MetadataHelpers.GetEdmType<PrimitiveType>(type); |
︙ | ︙ | |||
3602 3603 3604 3605 3606 3607 3608 | /// <returns></returns> private ISqlFragment HandleCountExpression(DbExpression e) { ISqlFragment result; if (e.ExpressionKind == DbExpressionKind.Constant) { | | | 3601 3602 3603 3604 3605 3606 3607 3608 3609 3610 3611 3612 3613 3614 3615 | /// <returns></returns> private ISqlFragment HandleCountExpression(DbExpression e) { ISqlFragment result; if (e.ExpressionKind == DbExpressionKind.Constant) { //For constant expression we should not cast the value, // thus we don't go throught the default DbConstantExpression handling SqlBuilder sqlBuilder = new SqlBuilder(); sqlBuilder.Append(((DbConstantExpression)e).Value.ToString()); result = sqlBuilder; } else { |
︙ | ︙ | |||
3687 3688 3689 3690 3691 3692 3693 | /// <returns></returns> bool IsCompatible(SqlSelectStatement result, DbExpressionKind expressionKind) { switch (expressionKind) { case DbExpressionKind.Distinct: return result.Top == null | | | 3686 3687 3688 3689 3690 3691 3692 3693 3694 3695 3696 3697 3698 3699 3700 | /// <returns></returns> bool IsCompatible(SqlSelectStatement result, DbExpressionKind expressionKind) { switch (expressionKind) { case DbExpressionKind.Distinct: return result.Top == null // The projection after distinct may not project all // columns used in the Order By && result.OrderBy.IsEmpty; case DbExpressionKind.Filter: return result.Select.IsEmpty && result.Where.IsEmpty && result.GroupBy.IsEmpty |
︙ | ︙ | |||
3810 3811 3812 3813 3814 3815 3816 | /// For the rest, we need to treat them as there was a dummy /// <code> /// -- originally {expression} /// -- change that to /// SELECT * /// FROM {expression} as c /// </code> | | | 3809 3810 3811 3812 3813 3814 3815 3816 3817 3818 3819 3820 3821 3822 3823 | /// For the rest, we need to treat them as there was a dummy /// <code> /// -- originally {expression} /// -- change that to /// SELECT * /// FROM {expression} as c /// </code> /// /// DbLimitExpression needs to start the statement but not add the default columns /// </summary> /// <param name="e"></param> /// <param name="addDefaultColumns"></param> /// <returns></returns> SqlSelectStatement VisitExpressionEnsureSqlStatement(DbExpression e, bool addDefaultColumns) { |
︙ | ︙ | |||
4035 4036 4037 4038 4039 4040 4041 | builder.Append("' "); } /// <summary> /// Helper method for the Group By visitor /// Returns true if at least one of the aggregates in the given list | | | | | 4034 4035 4036 4037 4038 4039 4040 4041 4042 4043 4044 4045 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 4078 4079 4080 4081 4082 4083 4084 4085 4086 4087 4088 | builder.Append("' "); } /// <summary> /// Helper method for the Group By visitor /// Returns true if at least one of the aggregates in the given list /// has an argument that is not a <see cref="DbPropertyExpression"/> /// over <see cref="DbVariableReferenceExpression"/> /// </summary> /// <param name="aggregates"></param> /// <returns></returns> static bool NeedsInnerQuery(IList<DbAggregate> aggregates) { foreach (DbAggregate aggregate in aggregates) { Debug.Assert(aggregate.Arguments.Count == 1); if (!IsPropertyOverVarRef(aggregate.Arguments[0])) { return true; } } return false; } /// <summary> /// Determines whether the given expression is a <see cref="DbPropertyExpression"/> /// over <see cref="DbVariableReferenceExpression"/> /// </summary> /// <param name="expression"></param> /// <returns></returns> static bool IsPropertyOverVarRef(DbExpression expression) { DbPropertyExpression propertyExpression = expression as DbPropertyExpression; if (propertyExpression == null) { return false; } DbVariableReferenceExpression varRefExpression = propertyExpression.Instance as DbVariableReferenceExpression; if (varRefExpression == null) { return false; } return true; } #endregion private class KeyFieldExpressionComparer : IEqualityComparer<DbExpression> { // Fields internal static readonly SqlGenerator.KeyFieldExpressionComparer Singleton = new SqlGenerator.KeyFieldExpressionComparer(); // Methods private KeyFieldExpressionComparer() |
︙ | ︙ |
Changes to System.Data.SQLite.Linq/SQLiteProviderManifest.cs.
︙ | ︙ | |||
79 80 81 82 83 84 85 | /// The effective provider manifest token. /// </returns> private static string GetProviderManifestToken( string manifestToken ) { #if !PLATFORM_COMPACTFRAMEWORK | | | | 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | /// The effective provider manifest token. /// </returns> private static string GetProviderManifestToken( string manifestToken ) { #if !PLATFORM_COMPACTFRAMEWORK string value = UnsafeNativeMethods.GetSettingValue( "AppendManifestToken_SQLiteProviderManifest", null); if (String.IsNullOrEmpty(value)) return manifestToken; int capacity = value.Length; if (manifestToken != null) |
︙ | ︙ |
Changes to System.Data.SQLite/Configurations/System.Data.SQLite.dll.config.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?xml version="1.0"?> <!-- * * System.Data.SQLite.dll.config - * * Written by Joe Mistachkin. * Released to the public domain, use at your own risk! * --> <configuration> <appSettings> <!-- NOTE: If this configuration variable is set [to anything], the SQLite logging subsystem may be initialized in a non-default application domain. By default, this is not allowed due to the potential for application domain unloading issues. --> <!-- | > > > > > > > > > > > > > > | 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 | <?xml version="1.0"?> <!-- * * System.Data.SQLite.dll.config - * * Written by Joe Mistachkin. * Released to the public domain, use at your own risk! * --> <configuration> <appSettings> <!-- NOTE: If this environment variable is set [to anything], it will be used by the System.Data.SQLite.Linq.SQLiteProviderManifest class (and the System.Data.SQLite.EF6.SQLiteProviderManifest class) to modify future provider manifest tokens by appending the value of the environment variable to the existing provider manifest token, if any. Typically, in order for the constructed provider manifest token to be syntactically correct, the environment variable value [to be appended] must begin with a semicolon. --> <!-- <add key="AppendManifestToken_SQLiteProviderManifest" value="" /> --> <!-- NOTE: If this configuration variable is set [to anything], the SQLite logging subsystem may be initialized in a non-default application domain. By default, this is not allowed due to the potential for application domain unloading issues. --> <!-- |
︙ | ︙ | |||
103 104 105 106 107 108 109 110 111 112 113 114 115 116 | architecture of the current process (e.g. a 32-bit x86 application running on a 64-bit x64 operating system should have the value "x86"). --> <!-- <add key="PROCESSOR_ARCHITECTURE" value="%PROCESSOR_ARCHITECTURE%" /> --> <!-- NOTE: If this environment variable is set [to anything], it will be used by the System.Data.SQLite.SQLiteFactory class as the type name containing the System.Data.Common.DbProviderServices implementation that should be used. --> | > > > > > > > > > | 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 | architecture of the current process (e.g. a 32-bit x86 application running on a 64-bit x64 operating system should have the value "x86"). --> <!-- <add key="PROCESSOR_ARCHITECTURE" value="%PROCESSOR_ARCHITECTURE%" /> --> <!-- NOTE: If this environment variable is set [to anything], all calls to prepare a SQL query will be logged, regardless of the flags for the associated connection. --> <!-- <add key="SQLite_ForceLogPrepare" value="1" /> --> <!-- NOTE: If this environment variable is set [to anything], it will be used by the System.Data.SQLite.SQLiteFactory class as the type name containing the System.Data.Common.DbProviderServices implementation that should be used. --> |
︙ | ︙ |
Changes to System.Data.SQLite/SQLite3.cs.
︙ | ︙ | |||
910 911 912 913 914 915 916 | /// </returns> private static bool ForceLogPrepare() { lock (syncRoot) { if (forceLogPrepare == null) { | | | | 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 | /// </returns> private static bool ForceLogPrepare() { lock (syncRoot) { if (forceLogPrepare == null) { if (UnsafeNativeMethods.GetSettingValue( "SQLite_ForceLogPrepare", null) != null) { forceLogPrepare = true; } else { forceLogPrepare = false; } |
︙ | ︙ |