/********************************************************
* 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((SQLiteProviderManifest)manifest, functionParameter.Name, functionParameter.TypeUsage, functionParameter.Mode, DBNull.Value);
}
else
{
parameter = CreateSqlParameter((SQLiteProviderManifest)manifest, 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(SQLiteProviderManifest manifest, string name, TypeUsage type, ParameterMode mode, object value)
{
int? size;
//
// NOTE: Adjust the parameter type so that it will work with textual
// GUIDs. Please see ticket [a4d9c7ee94] for more details.
//
if ((manifest != null) && !manifest._binaryGuid &&
(MetadataHelpers.GetPrimitiveTypeKind(type) == PrimitiveTypeKind.Guid))
{
type = TypeUsage.CreateStringTypeUsage(
PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String),
false, true);
}
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
}
}
