/********************************************************
* 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!
********************************************************/
namespace System.Data.SQLite
{
using System;
using System.Runtime.InteropServices;
using System.Collections.Generic;
///
/// SQLite has very limited types, and is inherently text-based. The first 5 types below represent the sum of all types SQLite
/// understands. The DateTime extension to the spec is for internal use only.
///
public enum TypeAffinity
{
///
/// All integers in SQLite default to Int64
///
Int64 = 1,
///
/// All floating point numbers in SQLite default to double
///
Double = 2,
///
/// The default data type of SQLite is text
///
Text = 3,
///
/// Typically blob types are only seen when returned from a function
///
Blob = 4,
///
/// Null types can be returned from functions
///
Null = 5,
///
/// Used internally by this provider
///
DateTime = 128,
///
/// Used internally by this provider
///
None=256,
}
///
/// This implementation of SQLite for ADO.NET can process date/time fields in databases in only one of two formats. Ticks and ISO8601.
/// Ticks is inherently more accurate, but less compatible with 3rd party tools that query the database, and renders the DateTime field
/// unreadable without post-processing.
/// ISO8601 is more compatible, readable, fully-processable, but less accurate as it doesn't provide time down to fractions of a second.
///
public enum DateTimeFormat
{
///
/// Using ticks is more accurate but less compatible with other viewers and utilities that access your database.
///
Ticks = 0,
///
/// The default format for this provider. More compatible with SQLite's intended usage of datetimes, but overall less accurate than Ticks as it doesn't
/// natively support times down to fractions of a second.
///
ISO8601 = 1,
}
internal struct SQLiteType
{
internal DbType Type;
internal TypeAffinity Affinity;
}
///
/// This base class provides datatype conversion services for the SQLite provider.
///
public abstract class SQLiteConvert
{
///
/// An array of ISO8601 datetime formats we support conversion from
///
private static string[] _datetimeFormats;
///
/// An UTF-8 Encoding instance, so we can convert strings to and from UTF8
///
private Text.UTF8Encoding _utf8;
///
/// The default DateTime format for this instance
///
private DateTimeFormat _datetimeFormat;
///
/// Static constructor, initializes the supported ISO8601 date time formats
///
static SQLiteConvert()
{
_datetimeFormats = new string[] {"yyyy-MM-dd HH:mm:ss",
"yyyyMMddHHmmss",
"yyyyMMddTHHmmssfffffff",
"yyyy-MM-dd",
"yy-MM-dd",
"yyyyMMdd",
"HH:mm:ss",
"THHmmss"
};
}
internal SQLiteConvert(DateTimeFormat fmt)
{
_datetimeFormat = fmt;
_utf8 = new System.Text.UTF8Encoding();
}
#region UTF-8 Conversion Functions
///
/// Converts a string to a UTF-8 encoded byte array sized to include a null-terminating character.
///
/// The string to convert to UTF-8
/// A byte array containing the converted string plus an extra 0 terminating byte at the end of the array.
public byte[] ToUTF8(string strSrc)
{
Byte[] b;
int nlen = _utf8.GetByteCount(strSrc) + 1;
b = new byte[nlen];
nlen = _utf8.GetBytes(strSrc, 0, strSrc.Length, b, 0);
b[nlen] = 0;
return b;
}
///
/// Convert a DateTime to a UTF-8 encoded, zero-terminated byte array.
///
///
/// This function is a convenience function, which first calls ToString() on the DateTime, and then calls ToUTF8() with the
/// string result.
///
/// The DateTime to convert.
/// The UTF-8 encoded string, including a 0 terminating byte at the end of the array.
public byte[] ToUTF8(DateTime dtSrc)
{
return ToUTF8(ToString(dtSrc));
}
///
/// Converts a UTF-8 encoded IntPtr of the specified length into a .NET string
///
/// The pointer to the memory where the UTF-8 string is encoded
/// The number of bytes to decode
/// A string containing the translated character(s)
public virtual string ToString(IntPtr b, int nlen)
{
if (nlen == 0) return "";
byte[] byt;
byt = new byte[nlen];
Marshal.Copy(b, byt, 0, nlen);
return _utf8.GetString(byt, 0, nlen);
}
#endregion
#region DateTime Conversion Functions
///
/// Converts a string into a DateTime, using the current DateTimeFormat specified for the connection when it was opened.
///
///
/// Acceptable ISO8601 DateTime formats are:
/// yyyy-MM-dd HH:mm:ss
/// yyyyMMddHHmmss
/// yyyyMMddTHHmmssfffffff
/// yyyy-MM-dd
/// yy-MM-dd
/// yyyyMMdd
/// HH:mm:ss
/// THHmmss
///
/// The string containing either a Tick value or an ISO8601-format string
/// A DateTime value
public DateTime ToDateTime(string strSrc)
{
switch (_datetimeFormat)
{
case DateTimeFormat.Ticks:
return new DateTime(Convert.ToInt64(strSrc));
default:
return DateTime.ParseExact(strSrc, _datetimeFormats, System.Globalization.DateTimeFormatInfo.InvariantInfo, System.Globalization.DateTimeStyles.None);
}
}
///
/// Attempt to convert the specified string to a datetime value.
///
/// The string to parse into a datetime
/// If successful, a valid datetime structure
/// Returns true if the string was a valid ISO8601 datetime, false otherwise.
public bool TryToDateTime(string strSrc, out DateTime result)
{
switch (_datetimeFormat)
{
case DateTimeFormat.ISO8601:
return DateTime.TryParseExact(strSrc, _datetimeFormats, System.Globalization.DateTimeFormatInfo.InvariantInfo, System.Globalization.DateTimeStyles.None, out result);
case DateTimeFormat.Ticks:
{
long n;
if (long.TryParse(strSrc, out n) == true)
{
result = new DateTime(n);
return true;
}
}
break;
}
result = DateTime.Now;
return false;
}
///
/// Converts a DateTime to a string value, using the current DateTimeFormat specified for the connection when it was opened.
///
/// The DateTime value to convert
/// Either a string consisting of the tick count for DateTimeFormat.Ticks, or a date/time in ISO8601 format.
public string ToString(DateTime dtSrc)
{
switch (_datetimeFormat)
{
case DateTimeFormat.Ticks:
return dtSrc.Ticks.ToString();
default:
return dtSrc.ToString(_datetimeFormats[0]);
}
}
///
/// Internal function to convert a UTF-8 encoded IntPtr of the specified length to a DateTime.
///
///
/// This is a convenience function, which first calls ToString() on the IntPtr to convert it to a string, then calls
/// ToDateTime() on the string to return a DateTime.
///
/// A pointer to the UTF-8 encoded string
/// The length in bytes of the string
/// The parsed DateTime value
internal DateTime ToDateTime(IntPtr ptr, int len)
{
return ToDateTime(ToString(ptr, len));
}
#endregion
///
/// Smart method of splitting a string. Skips quoted elements, removes the quotes.
///
///
/// This split function works somewhat like the String.Split() function in that it breaks apart a string into
/// pieces and returns the pieces as an array. The primary differences are:
///
/// - Only one character can be provided as a separator character
/// - Quoted text inside the string is skipped over when searching for the separator, and the quotes are removed.
///
/// Thus, if splitting the following string looking for a comma:
/// One,Two, "Three, Four", Five
///
/// The resulting array would contain
/// [0] One
/// [1] Two
/// [2] Three, Four
/// [3] Five
///
/// Note that the leading and trailing spaces were removed from each item during the split.
///
/// Source string to split apart
/// Separator character
/// A string array of the split up elements
public static string[] Split(string src, char sep)
{
char[] toks = new char[2] { '\"', sep };
char[] quot = new char[1] { '\"' };
int n = 0;
List ls = new List();
string s;
while (src.Length > 0)
{
n = src.IndexOfAny(toks, n);
if (n == -1) break;
if (src[n] == toks[0])
{
src = src.Remove(n, 1);
n = src.IndexOfAny(quot, n);
if (n == -1)
{
src = "\"" + src;
break;
}
src = src.Remove(n, 1);
}
else
{
s = src.Substring(0, n).Trim();
src = src.Substring(n + 1).Trim();
if (s.Length > 0) ls.Add(s);
n = 0;
}
}
if (src.Length > 0) ls.Add(src);
string[] ar = new string[ls.Count];
ls.CopyTo(ar, 0);
return ar;
}
#region Type Conversions
internal static SQLiteType ColumnToType(SQLiteStatement stmt, int ordinal)
{
SQLiteType typ;
typ.Type = TypeNameToDbType(stmt._sql.ColumnType(stmt, ordinal, out typ.Affinity));
return typ;
}
internal static Type SQLiteTypeToType(SQLiteType t)
{
if (t.Type != DbType.Object)
return SQLiteConvert.DbTypeToType(t.Type);
switch (t.Affinity)
{
case TypeAffinity.Null:
return typeof(DBNull);
case TypeAffinity.Int64:
return typeof(Int64);
case TypeAffinity.Double:
return typeof(Double);
case TypeAffinity.Blob:
return typeof(byte[]);
default:
return typeof(string);
}
}
///
/// For a given intrinsic type, return a DbType
///
/// The native type to convert
/// The corresponding (closest match) DbType
internal static DbType TypeToDbType(Type typ)
{
switch (Type.GetTypeCode(typ))
{
case TypeCode.Int16:
return DbType.Int16;
case TypeCode.Int32:
return DbType.Int32;
case TypeCode.Int64:
return DbType.Int64;
case TypeCode.UInt16:
return DbType.UInt16;
case TypeCode.UInt32:
return DbType.UInt32;
case TypeCode.UInt64:
return DbType.UInt64;
case TypeCode.Double:
return DbType.Double;
case TypeCode.Single:
return DbType.Single;
case TypeCode.Decimal:
return DbType.Decimal;
case TypeCode.Boolean:
return DbType.Boolean;
case TypeCode.SByte:
case TypeCode.Char:
return DbType.SByte;
case TypeCode.DateTime:
return DbType.DateTime;
case TypeCode.String:
return DbType.String;
case TypeCode.Object:
if (typ == typeof(byte[])) return DbType.Binary;
if (typ == typeof(Guid)) return DbType.Guid;
return DbType.String;
}
return DbType.String;
}
///
/// Convert a DbType to a Type
///
/// The DbType to convert from
/// The closest-match .NET type
internal static Type DbTypeToType(DbType typ)
{
switch (typ)
{
case DbType.Binary:
return typeof(byte[]);
case DbType.Boolean:
return typeof(bool);
case DbType.Byte:
return typeof(byte);
case DbType.Currency:
case DbType.Decimal:
return typeof(decimal);
case DbType.DateTime:
return typeof(DateTime);
case DbType.Double:
return typeof(double);
case DbType.Guid:
return typeof(Guid);
case DbType.Int16:
case DbType.UInt16:
return typeof(Int16);
case DbType.Int32:
case DbType.UInt32:
return typeof(Int32);
case DbType.Int64:
case DbType.UInt64:
return typeof(Int64);
case DbType.String:
return typeof(string);
case DbType.SByte:
return typeof(char);
case DbType.Single:
return typeof(float);
}
return typeof(string);
}
///
/// For a given type, return the closest-match SQLite TypeAffinity, which only understands a very limited subset of types.
///
/// The type to evaluate
/// The SQLite type affinity for that type.
internal static TypeAffinity TypeToAffinity(Type typ)
{
switch (Type.GetTypeCode(typ))
{
case TypeCode.DBNull:
return TypeAffinity.Null;
case TypeCode.String:
return TypeAffinity.Text;
case TypeCode.DateTime:
return TypeAffinity.DateTime;
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
case TypeCode.Char:
case TypeCode.SByte:
case TypeCode.Byte:
case TypeCode.Boolean:
return TypeAffinity.Int64;
case TypeCode.Double:
case TypeCode.Single:
case TypeCode.Decimal:
return TypeAffinity.Double;
case TypeCode.Object:
if (typ == typeof(byte[])) return TypeAffinity.Blob;
else return TypeAffinity.Text;
}
return TypeAffinity.Text;
}
///
/// For a given type name, return a closest-match .NET type
///
/// The name of the type to match
/// The .NET DBType the text evaluates to.
internal static DbType TypeNameToDbType(string Name)
{
if (Name == null) return DbType.Object;
Name = Name.ToUpper();
if (Name.IndexOf("LONGTEXT") > -1) return DbType.String;
if (Name.IndexOf("LONGCHAR") > -1) return DbType.String;
if (Name.IndexOf("SMALLINT") > -1) return DbType.Int16;
if (Name.IndexOf("BIGINT") > -1) return DbType.Int64;
if (Name.IndexOf("COUNTER") > -1) return DbType.Int64;
if (Name.IndexOf("AUTOINCREMENT") > -1) return DbType.Int64;
if (Name.IndexOf("IDENTITY") > -1) return DbType.Int64;
if (Name.IndexOf("LONG") > -1) return DbType.Int64;
if (Name.IndexOf("TINYINT") > -1) return DbType.Byte;
if (Name.IndexOf("INTEGER") > -1) return DbType.Int64;
if (Name.IndexOf("INT") > -1) return DbType.Int32;
if (Name.IndexOf("TEXT") > -1) return DbType.String;
if (Name.IndexOf("DOUBLE") > -1) return DbType.Double;
if (Name.IndexOf("FLOAT") > -1) return DbType.Double;
if (Name.IndexOf("REAL") > -1) return DbType.Single;
if (Name.IndexOf("BIT") > -1) return DbType.Boolean;
if (Name.IndexOf("YESNO") > -1) return DbType.Boolean;
if (Name.IndexOf("LOGICAL") > -1) return DbType.Boolean;
if (Name.IndexOf("BOOL") > -1) return DbType.Boolean;
if (Name.IndexOf("NUMERIC") > -1) return DbType.Decimal;
if (Name.IndexOf("DECIMAL") > -1) return DbType.Decimal;
if (Name.IndexOf("MONEY") > -1) return DbType.Decimal;
if (Name.IndexOf("CURRENCY") > -1) return DbType.Decimal;
if (Name.IndexOf("TIME") > -1) return DbType.DateTime;
if (Name.IndexOf("DATE") > -1) return DbType.DateTime;
if (Name.IndexOf("BLOB") > -1) return DbType.Binary;
if (Name.IndexOf("BINARY") > -1) return DbType.Binary;
if (Name.IndexOf("IMAGE") > -1) return DbType.Binary;
if (Name.IndexOf("GENERAL") > -1) return DbType.Binary;
if (Name.IndexOf("OLEOBJECT") > -1) return DbType.Binary;
if (Name.IndexOf("GUID") > -1) return DbType.Guid;
if (Name.IndexOf("UNIQUEIDENTIFIER") > -1) return DbType.Guid;
if (Name.IndexOf("MEMO") > -1) return DbType.String;
if (Name.IndexOf("NOTE") > -1) return DbType.String;
if (Name.IndexOf("CHAR") > -1) return DbType.String;
return DbType.Object;
}
#endregion
}
}