1 /********************************************************
\r
2 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
\r
3 * Written by Robert Simpson (robert@blackcastlesoft.com)
\r
5 * Released to the public domain, use at your own risk!
\r
6 ********************************************************/
\r
8 namespace Mono.Data.Sqlite
\r
12 using System.Runtime.InteropServices;
\r
13 using System.Collections.Generic;
\r
14 using System.ComponentModel;
\r
15 using System.Globalization;
\r
18 #if !PLATFORM_COMPACTFRAMEWORK
\r
19 using System.ComponentModel.Design;
\r
23 /// This base class provides datatype conversion services for the SQLite provider.
\r
25 public abstract class SqliteConvert
\r
28 /// An array of ISO8601 datetime formats we support conversion from
\r
30 private static string[] _datetimeFormats = new string[] {
\r
38 "yyyy-MM-dd HH:mm:ss.FFFFFFF",
\r
39 "yyyy-MM-dd HH:mm:ss",
\r
40 "yyyy-MM-dd HH:mm",
\r
41 "yyyy-MM-ddTHH:mm:ss.FFFFFFF",
\r
43 "yyyy-MM-ddTHH:mm:ss",
\r
46 "yyyyMMddTHHmmssFFFFFFF",
\r
51 /// An UTF-8 Encoding instance, so we can convert strings to and from UTF-8
\r
53 private static Encoding _utf8 = new UTF8Encoding();
\r
55 /// The default DateTime format for this instance
\r
57 internal SQLiteDateFormats _datetimeFormat;
\r
59 /// Initializes the conversion class
\r
61 /// <param name="fmt">The default date/time format to use for this instance</param>
\r
62 internal SqliteConvert(SQLiteDateFormats fmt)
\r
64 _datetimeFormat = fmt;
\r
67 #region UTF-8 Conversion Functions
\r
69 /// Converts a string to a UTF-8 encoded byte array sized to include a null-terminating character.
\r
71 /// <param name="sourceText">The string to convert to UTF-8</param>
\r
72 /// <returns>A byte array containing the converted string plus an extra 0 terminating byte at the end of the array.</returns>
\r
73 public static byte[] ToUTF8(string sourceText)
\r
76 int nlen = _utf8.GetByteCount(sourceText) + 1;
\r
78 byteArray = new byte[nlen];
\r
79 nlen = _utf8.GetBytes(sourceText, 0, sourceText.Length, byteArray, 0);
\r
80 byteArray[nlen] = 0;
\r
86 /// Convert a DateTime to a UTF-8 encoded, zero-terminated byte array.
\r
89 /// This function is a convenience function, which first calls ToString() on the DateTime, and then calls ToUTF8() with the
\r
92 /// <param name="dateTimeValue">The DateTime to convert.</param>
\r
93 /// <returns>The UTF-8 encoded string, including a 0 terminating byte at the end of the array.</returns>
\r
94 public byte[] ToUTF8(DateTime dateTimeValue)
\r
96 return ToUTF8(ToString(dateTimeValue));
\r
100 /// Converts a UTF-8 encoded IntPtr of the specified length into a .NET string
\r
102 /// <param name="nativestring">The pointer to the memory where the UTF-8 string is encoded</param>
\r
103 /// <param name="nativestringlen">The number of bytes to decode</param>
\r
104 /// <returns>A string containing the translated character(s)</returns>
\r
105 public virtual string ToString(IntPtr nativestring, int nativestringlen)
\r
107 return UTF8ToString(nativestring, nativestringlen);
\r
111 /// Converts a UTF-8 encoded IntPtr of the specified length into a .NET string
\r
113 /// <param name="nativestring">The pointer to the memory where the UTF-8 string is encoded</param>
\r
114 /// <param name="nativestringlen">The number of bytes to decode</param>
\r
115 /// <returns>A string containing the translated character(s)</returns>
\r
116 public static string UTF8ToString(IntPtr nativestring, int nativestringlen)
\r
118 if (nativestringlen == 0 || nativestring == IntPtr.Zero) return "";
\r
119 if (nativestringlen == -1)
\r
124 } while (Marshal.ReadByte(nativestring, nativestringlen) != 0);
\r
127 byte[] byteArray = new byte[nativestringlen];
\r
129 Marshal.Copy(nativestring, byteArray, 0, nativestringlen);
\r
131 return _utf8.GetString(byteArray, 0, nativestringlen);
\r
137 #region DateTime Conversion Functions
\r
139 /// Converts a string into a DateTime, using the current DateTimeFormat specified for the connection when it was opened.
\r
142 /// Acceptable ISO8601 DateTime formats are:
\r
143 /// yyyy-MM-dd HH:mm:ss
\r
145 /// yyyyMMddTHHmmssfffffff
\r
152 /// <param name="dateText">The string containing either a Tick value, a JulianDay double, or an ISO8601-format string</param>
\r
153 /// <returns>A DateTime value</returns>
\r
154 public DateTime ToDateTime(string dateText)
\r
156 switch (_datetimeFormat)
\r
158 case SQLiteDateFormats.Ticks:
\r
159 return new DateTime(Convert.ToInt64(dateText, CultureInfo.InvariantCulture));
\r
160 case SQLiteDateFormats.JulianDay:
\r
161 return ToDateTime(Convert.ToDouble(dateText, CultureInfo.InvariantCulture));
\r
163 return DateTime.ParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None);
\r
168 /// Converts a julianday value into a DateTime
\r
170 /// <param name="julianDay">The value to convert</param>
\r
171 /// <returns>A .NET DateTime</returns>
\r
172 public DateTime ToDateTime(double julianDay)
\r
174 return DateTime.FromOADate(julianDay - 2415018.5);
\r
178 /// Converts a DateTime struct to a JulianDay double
\r
180 /// <param name="value">The DateTime to convert</param>
\r
181 /// <returns>The JulianDay value the Datetime represents</returns>
\r
182 public double ToJulianDay(DateTime value)
\r
184 return value.ToOADate() + 2415018.5;
\r
188 /// Converts a DateTime to a string value, using the current DateTimeFormat specified for the connection when it was opened.
\r
190 /// <param name="dateValue">The DateTime value to convert</param>
\r
191 /// <returns>Either a string consisting of the tick count for DateTimeFormat.Ticks, a JulianDay double, or a date/time in ISO8601 format.</returns>
\r
192 public string ToString(DateTime dateValue)
\r
194 switch (_datetimeFormat)
\r
196 case SQLiteDateFormats.Ticks:
\r
197 return dateValue.Ticks.ToString(CultureInfo.InvariantCulture);
\r
198 case SQLiteDateFormats.JulianDay:
\r
199 return ToJulianDay(dateValue).ToString(CultureInfo.InvariantCulture);
\r
201 return dateValue.ToString(_datetimeFormats[7], CultureInfo.InvariantCulture);
\r
206 /// Internal function to convert a UTF-8 encoded IntPtr of the specified length to a DateTime.
\r
209 /// This is a convenience function, which first calls ToString() on the IntPtr to convert it to a string, then calls
\r
210 /// ToDateTime() on the string to return a DateTime.
\r
212 /// <param name="ptr">A pointer to the UTF-8 encoded string</param>
\r
213 /// <param name="len">The length in bytes of the string</param>
\r
214 /// <returns>The parsed DateTime value</returns>
\r
215 internal DateTime ToDateTime(IntPtr ptr, int len)
\r
217 return ToDateTime(ToString(ptr, len));
\r
223 /// Smart method of splitting a string. Skips quoted elements, removes the quotes.
\r
226 /// This split function works somewhat like the String.Split() function in that it breaks apart a string into
\r
227 /// pieces and returns the pieces as an array. The primary differences are:
\r
228 /// <list type="bullet">
\r
229 /// <item><description>Only one character can be provided as a separator character</description></item>
\r
230 /// <item><description>Quoted text inside the string is skipped over when searching for the separator, and the quotes are removed.</description></item>
\r
232 /// Thus, if splitting the following string looking for a comma:<br/>
\r
233 /// One,Two, "Three, Four", Five<br/>
\r
235 /// The resulting array would contain<br/>
\r
238 /// [2] Three, Four<br/>
\r
241 /// Note that the leading and trailing spaces were removed from each item during the split.
\r
243 /// <param name="source">Source string to split apart</param>
\r
244 /// <param name="separator">Separator character</param>
\r
245 /// <returns>A string array of the split up elements</returns>
\r
246 public static string[] Split(string source, char separator)
\r
248 char[] toks = new char[2] { '\"', separator };
\r
249 char[] quot = new char[1] { '\"' };
\r
251 List<string> ls = new List<string>();
\r
254 while (source.Length > 0)
\r
256 n = source.IndexOfAny(toks, n);
\r
257 if (n == -1) break;
\r
258 if (source[n] == toks[0])
\r
260 //source = source.Remove(n, 1);
\r
261 n = source.IndexOfAny(quot, n + 1);
\r
264 //source = "\"" + source;
\r
268 //source = source.Remove(n, 1);
\r
272 s = source.Substring(0, n).Trim();
\r
273 if (s.Length > 1 && s[0] == quot[0] && s[s.Length - 1] == s[0])
\r
274 s = s.Substring(1, s.Length - 2);
\r
276 source = source.Substring(n + 1).Trim();
\r
277 if (s.Length > 0) ls.Add(s);
\r
281 if (source.Length > 0)
\r
284 if (s.Length > 1 && s[0] == quot[0] && s[s.Length - 1] == s[0])
\r
285 s = s.Substring(1, s.Length - 2);
\r
289 string[] ar = new string[ls.Count];
\r
296 /// Convert a value to true or false.
\r
298 /// <param name="source">A string or number representing true or false</param>
\r
299 /// <returns></returns>
\r
300 public static bool ToBoolean(object source)
\r
302 if (source is bool) return (bool)source;
\r
304 return ToBoolean(source.ToString());
\r
308 /// Convert a string to true or false.
\r
310 /// <param name="source">A string representing true or false</param>
\r
311 /// <returns></returns>
\r
313 /// "yes", "no", "y", "n", "0", "1", "on", "off" as well as Boolean.FalseString and Boolean.TrueString will all be
\r
314 /// converted to a proper boolean value.
\r
316 public static bool ToBoolean(string source)
\r
318 if (String.Compare(source, bool.TrueString, StringComparison.OrdinalIgnoreCase) == 0) return true;
\r
319 else if (String.Compare(source, bool.FalseString, StringComparison.OrdinalIgnoreCase) == 0) return false;
\r
321 switch(source.ToLower())
\r
334 throw new ArgumentException("source");
\r
338 #region Type Conversions
\r
340 /// Determines the data type of a column in a statement
\r
342 /// <param name="stmt">The statement to retrieve information for</param>
\r
343 /// <param name="i">The column to retrieve type information on</param>
\r
344 /// <param name="typ">The SQLiteType to receive the affinity for the given column</param>
\r
345 internal static void ColumnToType(SqliteStatement stmt, int i, SQLiteType typ)
\r
347 typ.Type = TypeNameToDbType(stmt._sql.ColumnType(stmt, i, out typ.Affinity));
\r
351 /// Converts a SQLiteType to a .NET Type object
\r
353 /// <param name="t">The SQLiteType to convert</param>
\r
354 /// <returns>Returns a .NET Type object</returns>
\r
355 internal static Type SQLiteTypeToType(SQLiteType t)
\r
357 if (t.Type == DbType.Object)
\r
358 return _affinitytotype[(int)t.Affinity];
\r
360 return SqliteConvert.DbTypeToType(t.Type);
\r
363 private static Type[] _affinitytotype = {
\r
375 /// For a given intrinsic type, return a DbType
\r
377 /// <param name="typ">The native type to convert</param>
\r
378 /// <returns>The corresponding (closest match) DbType</returns>
\r
379 internal static DbType TypeToDbType(Type typ)
\r
381 TypeCode tc = Type.GetTypeCode(typ);
\r
382 if (tc == TypeCode.Object)
\r
384 if (typ == typeof(byte[])) return DbType.Binary;
\r
385 if (typ == typeof(Guid)) return DbType.Guid;
\r
386 return DbType.String;
\r
388 return _typetodbtype[(int)tc];
\r
391 private static DbType[] _typetodbtype = {
\r
403 DbType.Int64, // 11
\r
414 /// Returns the ColumnSize for the given DbType
\r
416 /// <param name="typ">The DbType to get the size of</param>
\r
417 /// <returns></returns>
\r
418 internal static int DbTypeToColumnSize(DbType typ)
\r
420 return _dbtypetocolumnsize[(int)typ];
\r
423 private static int[] _dbtypetocolumnsize = {
\r
449 2147483647, // 25 (Xml)
\r
452 internal static object DbTypeToNumericPrecision(DbType typ)
\r
454 return _dbtypetonumericprecision[(int)typ];
\r
457 private static object[] _dbtypetonumericprecision = {
\r
485 internal static object DbTypeToNumericScale(DbType typ)
\r
487 return _dbtypetonumericscale[(int)typ];
\r
490 private static object[] _dbtypetonumericscale = {
\r
518 internal static string DbTypeToTypeName(DbType typ)
\r
520 for (int n = 0; n < _dbtypeNames.Length; n++)
\r
522 if (_dbtypeNames[n].dataType == typ)
\r
523 return _dbtypeNames[n].typeName;
\r
526 return String.Empty;
\r
529 private static SQLiteTypeNames[] _dbtypeNames = {
\r
530 new SQLiteTypeNames("INTEGER", DbType.Int64),
\r
531 new SQLiteTypeNames("TINYINT", DbType.Byte),
\r
532 new SQLiteTypeNames("INT", DbType.Int32),
\r
533 new SQLiteTypeNames("VARCHAR", DbType.AnsiString),
\r
534 new SQLiteTypeNames("NVARCHAR", DbType.String),
\r
535 new SQLiteTypeNames("CHAR", DbType.AnsiStringFixedLength),
\r
536 new SQLiteTypeNames("NCHAR", DbType.StringFixedLength),
\r
537 new SQLiteTypeNames("FLOAT", DbType.Double),
\r
538 new SQLiteTypeNames("REAL", DbType.Single),
\r
539 new SQLiteTypeNames("BIT", DbType.Boolean),
\r
540 new SQLiteTypeNames("DECIMAL", DbType.Decimal),
\r
541 new SQLiteTypeNames("DATETIME", DbType.DateTime),
\r
542 new SQLiteTypeNames("BLOB", DbType.Binary),
\r
543 new SQLiteTypeNames("UNIQUEIDENTIFIER", DbType.Guid),
\r
544 new SQLiteTypeNames("SMALLINT", DbType.Int16),
\r
547 /// Convert a DbType to a Type
\r
549 /// <param name="typ">The DbType to convert from</param>
\r
550 /// <returns>The closest-match .NET type</returns>
\r
551 internal static Type DbTypeToType(DbType typ)
\r
553 return _dbtypeToType[(int)typ];
\r
556 private static Type[] _dbtypeToType = {
\r
557 typeof(string), // 0
\r
558 typeof(byte[]), // 1
\r
561 typeof(decimal), // 4
\r
562 typeof(DateTime), // 5
\r
563 typeof(DateTime), // 6
\r
564 typeof(decimal), // 7
\r
565 typeof(double), // 8
\r
582 typeof(string), // 25 (Xml)
\r
586 /// For a given type, return the closest-match SQLite TypeAffinity, which only understands a very limited subset of types.
\r
588 /// <param name="typ">The type to evaluate</param>
\r
589 /// <returns>The SQLite type affinity for that type.</returns>
\r
590 internal static TypeAffinity TypeToAffinity(Type typ)
\r
592 TypeCode tc = Type.GetTypeCode(typ);
\r
593 if (tc == TypeCode.Object)
\r
595 if (typ == typeof(byte[]) || typ == typeof(Guid))
\r
596 return TypeAffinity.Blob;
\r
598 return TypeAffinity.Text;
\r
600 return _typecodeAffinities[(int)tc];
\r
603 private static TypeAffinity[] _typecodeAffinities = {
\r
607 TypeAffinity.Int64,
\r
608 TypeAffinity.Int64,
\r
609 TypeAffinity.Int64,
\r
610 TypeAffinity.Int64,
\r
611 TypeAffinity.Int64, // 7
\r
612 TypeAffinity.Int64,
\r
613 TypeAffinity.Int64,
\r
614 TypeAffinity.Int64,
\r
615 TypeAffinity.Int64, // 11
\r
616 TypeAffinity.Int64,
\r
617 TypeAffinity.Double,
\r
618 TypeAffinity.Double,
\r
619 TypeAffinity.Double,
\r
620 TypeAffinity.DateTime,
\r
626 /// For a given type name, return a closest-match .NET type
\r
628 /// <param name="Name">The name of the type to match</param>
\r
629 /// <returns>The .NET DBType the text evaluates to.</returns>
\r
630 internal static DbType TypeNameToDbType(string Name)
\r
632 if (String.IsNullOrEmpty(Name)) return DbType.Object;
\r
634 int x = _typeNames.Length;
\r
635 for (int n = 0; n < x; n++)
\r
637 if (String.Compare(Name, 0, _typeNames[n].typeName, 0, _typeNames[n].typeName.Length, true, CultureInfo.InvariantCulture) == 0)
\r
638 return _typeNames[n].dataType;
\r
640 return DbType.Object;
\r
644 private static SQLiteTypeNames[] _typeNames = {
\r
645 new SQLiteTypeNames("COUNTER", DbType.Int64),
\r
646 new SQLiteTypeNames("AUTOINCREMENT", DbType.Int64),
\r
647 new SQLiteTypeNames("IDENTITY", DbType.Int64),
\r
648 new SQLiteTypeNames("LONGTEXT", DbType.String),
\r
649 new SQLiteTypeNames("LONGCHAR", DbType.String),
\r
650 new SQLiteTypeNames("LONGVARCHAR", DbType.String),
\r
651 new SQLiteTypeNames("LONG", DbType.Int64),
\r
652 new SQLiteTypeNames("TINYINT", DbType.Byte),
\r
653 new SQLiteTypeNames("INTEGER", DbType.Int64),
\r
654 new SQLiteTypeNames("INT", DbType.Int32),
\r
655 new SQLiteTypeNames("VARCHAR", DbType.String),
\r
656 new SQLiteTypeNames("NVARCHAR", DbType.String),
\r
657 new SQLiteTypeNames("CHAR", DbType.String),
\r
658 new SQLiteTypeNames("NCHAR", DbType.String),
\r
659 new SQLiteTypeNames("TEXT", DbType.String),
\r
660 new SQLiteTypeNames("NTEXT", DbType.String),
\r
661 new SQLiteTypeNames("STRING", DbType.String),
\r
662 new SQLiteTypeNames("DOUBLE", DbType.Double),
\r
663 new SQLiteTypeNames("FLOAT", DbType.Double),
\r
664 new SQLiteTypeNames("REAL", DbType.Single),
\r
665 new SQLiteTypeNames("BIT", DbType.Boolean),
\r
666 new SQLiteTypeNames("YESNO", DbType.Boolean),
\r
667 new SQLiteTypeNames("LOGICAL", DbType.Boolean),
\r
668 new SQLiteTypeNames("BOOL", DbType.Boolean),
\r
669 new SQLiteTypeNames("NUMERIC", DbType.Decimal),
\r
670 new SQLiteTypeNames("DECIMAL", DbType.Decimal),
\r
671 new SQLiteTypeNames("MONEY", DbType.Decimal),
\r
672 new SQLiteTypeNames("CURRENCY", DbType.Decimal),
\r
673 new SQLiteTypeNames("TIME", DbType.DateTime),
\r
674 new SQLiteTypeNames("DATE", DbType.DateTime),
\r
675 new SQLiteTypeNames("SMALLDATE", DbType.DateTime),
\r
676 new SQLiteTypeNames("BLOB", DbType.Binary),
\r
677 new SQLiteTypeNames("BINARY", DbType.Binary),
\r
678 new SQLiteTypeNames("VARBINARY", DbType.Binary),
\r
679 new SQLiteTypeNames("IMAGE", DbType.Binary),
\r
680 new SQLiteTypeNames("GENERAL", DbType.Binary),
\r
681 new SQLiteTypeNames("OLEOBJECT", DbType.Binary),
\r
682 new SQLiteTypeNames("GUID", DbType.Guid),
\r
683 new SQLiteTypeNames("UNIQUEIDENTIFIER", DbType.Guid),
\r
684 new SQLiteTypeNames("MEMO", DbType.String),
\r
685 new SQLiteTypeNames("NOTE", DbType.String),
\r
686 new SQLiteTypeNames("SMALLINT", DbType.Int16),
\r
687 new SQLiteTypeNames("BIGINT", DbType.Int64),
\r
692 /// SQLite has very limited types, and is inherently text-based. The first 5 types below represent the sum of all types SQLite
\r
693 /// understands. The DateTime extension to the spec is for internal use only.
\r
695 public enum TypeAffinity
\r
702 /// All integers in SQLite default to Int64
\r
706 /// All floating point numbers in SQLite default to double
\r
710 /// The default data type of SQLite is text
\r
714 /// Typically blob types are only seen when returned from a function
\r
718 /// Null types can be returned from functions
\r
722 /// Used internally by this provider
\r
726 /// Used internally
\r
732 /// This implementation of SQLite for ADO.NET can process date/time fields in databases in only one of three formats. Ticks, ISO8601
\r
736 /// ISO8601 is more compatible, readable, fully-processable, but less accurate as it doesn't provide time down to fractions of a second.
\r
737 /// JulianDay is the numeric format the SQLite uses internally and is arguably the most compatible with 3rd party tools. It is
\r
738 /// not readable as text without post-processing.
\r
739 /// Ticks less compatible with 3rd party tools that query the database, and renders the DateTime field unreadable as text without post-processing.
\r
741 /// The preferred order of choosing a datetime format is JulianDay, ISO8601, and then Ticks. Ticks is mainly present for legacy
\r
744 public enum SQLiteDateFormats
\r
747 /// Using ticks is not recommended and is not well supported with LINQ.
\r
751 /// The default format for this provider.
\r
755 /// JulianDay format, which is what SQLite uses internally
\r
761 /// This enum determines how SQLite treats its journal file.
\r
764 /// By default SQLite will create and delete the journal file when needed during a transaction.
\r
765 /// However, for some computers running certain filesystem monitoring tools, the rapid
\r
766 /// creation and deletion of the journal file can cause those programs to fail, or to interfere with SQLite.
\r
768 /// If a program or virus scanner is interfering with SQLite's journal file, you may receive errors like "unable to open database file"
\r
769 /// when starting a transaction. If this is happening, you may want to change the default journal mode to Persist.
\r
771 public enum SQLiteJournalModeEnum
\r
774 /// The default mode, this causes SQLite to create and destroy the journal file as-needed.
\r
778 /// When this is set, SQLite will keep the journal file even after a transaction has completed. It's contents will be erased,
\r
779 /// and the journal re-used as often as needed. If it is deleted, it will be recreated the next time it is needed.
\r
783 /// This option disables the rollback journal entirely. Interrupted transactions or a program crash can cause database
\r
784 /// corruption in this mode!
\r
790 /// Struct used internally to determine the datatype of a column in a resultset
\r
792 internal class SQLiteType
\r
795 /// The DbType of the column, or DbType.Object if it cannot be determined
\r
797 internal DbType Type;
\r
799 /// The affinity of a column, used for expressions or when Type is DbType.Object
\r
801 internal TypeAffinity Affinity;
\r
804 internal struct SQLiteTypeNames
\r
806 internal SQLiteTypeNames(string newtypeName, DbType newdataType)
\r
808 typeName = newtypeName;
\r
809 dataType = newdataType;
\r
812 internal string typeName;
\r
813 internal DbType dataType;
\r