Repository navigation
Expand file tree
/
Copy pathSQLiteProviderServices.cs
More file actions
408 lines (338 loc) · 13.3 KB
/
Copy pathSQLiteProviderServices.cs
File metadata and controls
408 lines (338 loc) · 13.3 KB
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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
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
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
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
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
/********************************************************
* ADO.NET 2.0 Data Provider for SQLite Version 3.X
* Written by Robert Simpson (robert@blackcastlesoft.com)
*
* Released to the public domain, use at your own risk!
********************************************************/
#if USE_ENTITY_FRAMEWORK_6
namespace System.Data.SQLite.EF6
#else
namespace System.Data.SQLite.Linq
#endif
{
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Diagnostics;
using System.Globalization;
using System.Text;
#if USE_ENTITY_FRAMEWORK_6
using System.Data.Entity.Core.Common;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Core.Common.CommandTrees;
#else
using System.Data.Metadata.Edm;
using System.Data.Common.CommandTrees;
#endif
internal sealed class SQLiteProviderServices : DbProviderServices, ISQLiteSchemaExtensions
{
internal static readonly SQLiteProviderServices Instance = new SQLiteProviderServices();
protected override DbCommandDefinition CreateDbCommandDefinition(DbProviderManifest manifest, DbCommandTree commandTree)
{
DbCommand prototype = CreateCommand(manifest, commandTree);
DbCommandDefinition result = this.CreateCommandDefinition(prototype);
return result;
}
private DbCommand CreateCommand(DbProviderManifest manifest, DbCommandTree commandTree)
{
if (manifest == null)
throw new ArgumentNullException("manifest");
if (commandTree == null)
throw new ArgumentNullException("commandTree");
SQLiteCommand command = new SQLiteCommand();
try
{
List<DbParameter> parameters;
CommandType commandType;
command.CommandText = SqlGenerator.GenerateSql((SQLiteProviderManifest)manifest, commandTree, out parameters, out commandType);
command.CommandType = commandType;
// Get the function (if any) implemented by the command tree since this influences our interpretation of parameters
EdmFunction function = null;
if (commandTree is DbFunctionCommandTree)
{
function = ((DbFunctionCommandTree)commandTree).EdmFunction;
}
// Now make sure we populate the command's parameters from the CQT's parameters:
foreach (KeyValuePair<string, TypeUsage> queryParameter in commandTree.Parameters)
{
SQLiteParameter parameter;
// Use the corresponding function parameter TypeUsage where available (currently, the SSDL facets and
// type trump user-defined facets and type in the EntityCommand).
FunctionParameter functionParameter;
if (null != function && function.Parameters.TryGetValue(queryParameter.Key, false, out functionParameter))
{
parameter = CreateSqlParameter(functionParameter.Name, functionParameter.TypeUsage, functionParameter.Mode, DBNull.Value);
}
else
{
parameter = CreateSqlParameter(queryParameter.Key, queryParameter.Value, ParameterMode.In, DBNull.Value);
}
command.Parameters.Add(parameter);
}
// Now add parameters added as part of SQL gen (note: this feature is only safe for DML SQL gen which
// does not support user parameters, where there is no risk of name collision)
if (null != parameters && 0 < parameters.Count)
{
if (!(commandTree is DbInsertCommandTree) &&
!(commandTree is DbUpdateCommandTree) &&
!(commandTree is DbDeleteCommandTree))
{
throw new InvalidOperationException("SqlGenParametersNotPermitted");
}
foreach (DbParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return command;
}
catch
{
command.Dispose();
throw;
}
}
protected override string GetDbProviderManifestToken(DbConnection connection)
{
if (connection == null)
throw new ArgumentNullException("connection");
if (String.IsNullOrEmpty(connection.ConnectionString))
throw new ArgumentNullException("ConnectionString");
return connection.ConnectionString;
}
protected override DbProviderManifest GetDbProviderManifest(string versionHint)
{
return new SQLiteProviderManifest(versionHint);
}
/// <summary>
/// Creates a SQLiteParameter given a name, type, and direction
/// </summary>
internal static SQLiteParameter CreateSqlParameter(string name, TypeUsage type, ParameterMode mode, object value)
{
int? size;
SQLiteParameter result = new SQLiteParameter(name, value);
// .Direction
ParameterDirection direction = MetadataHelpers.ParameterModeToParameterDirection(mode);
if (result.Direction != direction)
{
result.Direction = direction;
}
// .Size and .DbType
// output parameters are handled differently (we need to ensure there is space for return
// values where the user has not given a specific Size/MaxLength)
bool isOutParam = mode != ParameterMode.In;
DbType sqlDbType = GetSqlDbType(type, isOutParam, out size);
if (result.DbType != sqlDbType)
{
result.DbType = sqlDbType;
}
// Note that we overwrite 'facet' parameters where either the value is different or
// there is an output parameter.
if (size.HasValue && (isOutParam || result.Size != size.Value))
{
result.Size = size.Value;
}
// .IsNullable
bool isNullable = MetadataHelpers.IsNullable(type);
if (isOutParam || isNullable != result.IsNullable)
{
result.IsNullable = isNullable;
}
return result;
}
/// <summary>
/// Determines DbType for the given primitive type. Extracts facet
/// information as well.
/// </summary>
private static DbType GetSqlDbType(TypeUsage type, bool isOutParam, out int? size)
{
// only supported for primitive type
PrimitiveTypeKind primitiveTypeKind = MetadataHelpers.GetPrimitiveTypeKind(type);
size = default(int?);
switch (primitiveTypeKind)
{
case PrimitiveTypeKind.Binary:
// for output parameters, ensure there is space...
size = GetParameterSize(type, isOutParam);
return GetBinaryDbType(type);
case PrimitiveTypeKind.Boolean:
return DbType.Boolean;
case PrimitiveTypeKind.Byte:
return DbType.Byte;
case PrimitiveTypeKind.Time:
return DbType.Time;
case PrimitiveTypeKind.DateTimeOffset:
return DbType.DateTimeOffset;
case PrimitiveTypeKind.DateTime:
return DbType.DateTime;
case PrimitiveTypeKind.Decimal:
return DbType.Decimal;
case PrimitiveTypeKind.Double:
return DbType.Double;
case PrimitiveTypeKind.Guid:
return DbType.Guid;
case PrimitiveTypeKind.Int16:
return DbType.Int16;
case PrimitiveTypeKind.Int32:
return DbType.Int32;
case PrimitiveTypeKind.Int64:
return DbType.Int64;
case PrimitiveTypeKind.SByte:
return DbType.SByte;
case PrimitiveTypeKind.Single:
return DbType.Single;
case PrimitiveTypeKind.String:
size = GetParameterSize(type, isOutParam);
return GetStringDbType(type);
default:
Debug.Fail("unknown PrimitiveTypeKind " + primitiveTypeKind);
return DbType.Object;
}
}
/// <summary>
/// Determines preferred value for SqlParameter.Size. Returns null
/// where there is no preference.
/// </summary>
private static int? GetParameterSize(TypeUsage type, bool isOutParam)
{
int maxLength;
if (MetadataHelpers.TryGetMaxLength(type, out maxLength))
{
// if the MaxLength facet has a specific value use it
return maxLength;
}
else if (isOutParam)
{
// if the parameter is a return/out/inout parameter, ensure there
// is space for any value
return int.MaxValue;
}
else
{
// no value
return default(int?);
}
}
/// <summary>
/// Chooses the appropriate DbType for the given string type.
/// </summary>
private static DbType GetStringDbType(TypeUsage type)
{
Debug.Assert(type.EdmType.BuiltInTypeKind == BuiltInTypeKind.PrimitiveType &&
PrimitiveTypeKind.String == ((PrimitiveType)type.EdmType).PrimitiveTypeKind, "only valid for string type");
DbType dbType;
// Specific type depends on whether the string is a unicode string and whether it is a fixed length string.
// By default, assume widest type (unicode) and most common type (variable length)
bool unicode;
bool fixedLength;
if (!MetadataHelpers.TryGetIsFixedLength(type, out fixedLength))
{
fixedLength = false;
}
if (!MetadataHelpers.TryGetIsUnicode(type, out unicode))
{
unicode = true;
}
if (fixedLength)
{
dbType = (unicode ? DbType.StringFixedLength : DbType.AnsiStringFixedLength);
}
else
{
dbType = (unicode ? DbType.String : DbType.AnsiString);
}
return dbType;
}
/// <summary>
/// Chooses the appropriate DbType for the given binary type.
/// </summary>
private static DbType GetBinaryDbType(TypeUsage type)
{
Debug.Assert(type.EdmType.BuiltInTypeKind == BuiltInTypeKind.PrimitiveType &&
PrimitiveTypeKind.Binary == ((PrimitiveType)type.EdmType).PrimitiveTypeKind, "only valid for binary type");
// Specific type depends on whether the binary value is fixed length. By default, assume variable length.
bool fixedLength;
if (!MetadataHelpers.TryGetIsFixedLength(type, out fixedLength))
{
fixedLength = false;
}
return DbType.Binary;
// return fixedLength ? DbType.Binary : DbType.VarBinary;
}
#region ISQLiteSchemaExtensions Members
/// <summary>
/// Creates temporary tables on the connection so schema information can be queried
/// </summary>
/// <remarks>
/// There's a lot of work involved in getting schema information out of SQLite, but LINQ expects to
/// be able to query on schema tables. Therefore we need to "fake" it by generating temporary tables
/// filled with the schema of the current connection. We get away with making this information static
/// because schema information seems to always be queried on a new connection object, so the schema is
/// always fresh.
/// </remarks>
/// <param name="cnn">The connection upon which to build the schema tables.</param>
void ISQLiteSchemaExtensions.BuildTempSchema(SQLiteConnection cnn)
{
string[] arr = new string[] { "TABLES", "COLUMNS", "VIEWS", "VIEWCOLUMNS", "INDEXES", "INDEXCOLUMNS", "FOREIGNKEYS", "CATALOGS" };
using (DataTable table = cnn.GetSchema("Tables", new string[] { "temp", null, String.Format("SCHEMA{0}", arr[0]) }))
{
if (table.Rows.Count > 0) return;
}
for (int n = 0; n < arr.Length; n++)
{
using (DataTable table = cnn.GetSchema(arr[n]))
{
DataTableToTable(cnn, table, String.Format("SCHEMA{0}", arr[n]));
}
}
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = Properties.Resources.SQL_CONSTRAINTS;
cmd.ExecuteNonQuery();
cmd.CommandText = Properties.Resources.SQL_CONSTRAINTCOLUMNS;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Turn a datatable into a table in the temporary database for the connection
/// </summary>
/// <param name="cnn">The connection to make the temporary table in</param>
/// <param name="table">The table to write out</param>
/// <param name="dest">The temporary table name to write to</param>
private void DataTableToTable(SQLiteConnection cnn, DataTable table, string dest)
{
StringBuilder sql = new StringBuilder();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
using (SQLiteCommand cmd = cnn.CreateCommand())
using (DataTable source = new DataTable())
{
sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest));
string separator = String.Empty;
SQLiteConnectionFlags flags = cnn.Flags;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType);
string typeName = SQLiteConvert.DbTypeToTypeName(cnn, dbtypeName, flags);
sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator);
separator = ", ";
}
sql.Append(")");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest));
using (SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd))
{
builder.DataAdapter = adp;
adp.Fill(source);
foreach (DataRow row in table.Rows)
{
object[] arr = row.ItemArray;
source.Rows.Add(arr);
}
adp.Update(source);
}
}
}
#endregion
}
}