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 /// The value for the Unix epoch (e.g. January 1, 1970 at midnight, in UTC).
\r
30 protected static readonly DateTime UnixEpoch =
\r
31 new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
\r
33 /// An array of ISO8601 datetime formats we support conversion from
\r
35 private static string[] _datetimeFormats = new string[] {
\r
43 "yyyy-MM-dd HH:mm:ss.FFFFFFF",
\r
44 "yyyy-MM-dd HH:mm:ss",
\r
45 "yyyy-MM-dd HH:mm",
\r
46 "yyyy-MM-ddTHH:mm:ss.FFFFFFF",
\r
48 "yyyy-MM-ddTHH:mm:ss",
\r
51 "yyyyMMddTHHmmssFFFFFFF",
\r
56 /// An UTF-8 Encoding instance, so we can convert strings to and from UTF-8
\r
58 private static Encoding _utf8 = new UTF8Encoding();
\r
60 /// The default DateTime format for this instance
\r
62 internal SQLiteDateFormats _datetimeFormat;
\r
64 /// Initializes the conversion class
\r
66 /// <param name="fmt">The default date/time format to use for this instance</param>
\r
67 internal SqliteConvert(SQLiteDateFormats fmt)
\r
69 _datetimeFormat = fmt;
\r
72 #region UTF-8 Conversion Functions
\r
74 /// Converts a string to a UTF-8 encoded byte array sized to include a null-terminating character.
\r
76 /// <param name="sourceText">The string to convert to UTF-8</param>
\r
77 /// <returns>A byte array containing the converted string plus an extra 0 terminating byte at the end of the array.</returns>
\r
78 public static byte[] ToUTF8(string sourceText)
\r
81 int nlen = _utf8.GetByteCount(sourceText) + 1;
\r
83 byteArray = new byte[nlen];
\r
84 nlen = _utf8.GetBytes(sourceText, 0, sourceText.Length, byteArray, 0);
\r
85 byteArray[nlen] = 0;
\r
91 /// Convert a DateTime to a UTF-8 encoded, zero-terminated byte array.
\r
94 /// This function is a convenience function, which first calls ToString() on the DateTime, and then calls ToUTF8() with the
\r
97 /// <param name="dateTimeValue">The DateTime to convert.</param>
\r
98 /// <returns>The UTF-8 encoded string, including a 0 terminating byte at the end of the array.</returns>
\r
99 public byte[] ToUTF8(DateTime dateTimeValue)
\r
101 return ToUTF8(ToString(dateTimeValue));
\r
105 /// Converts a UTF-8 encoded IntPtr of the specified length into a .NET string
\r
107 /// <param name="nativestring">The pointer to the memory where the UTF-8 string is encoded</param>
\r
108 /// <param name="nativestringlen">The number of bytes to decode</param>
\r
109 /// <returns>A string containing the translated character(s)</returns>
\r
110 public virtual string ToString(IntPtr nativestring, int nativestringlen)
\r
112 return UTF8ToString(nativestring, nativestringlen);
\r
116 /// Converts a UTF-8 encoded IntPtr of the specified length into a .NET string
\r
118 /// <param name="nativestring">The pointer to the memory where the UTF-8 string is encoded</param>
\r
119 /// <param name="nativestringlen">The number of bytes to decode</param>
\r
120 /// <returns>A string containing the translated character(s)</returns>
\r
121 public static string UTF8ToString(IntPtr nativestring, int nativestringlen)
\r
123 if (nativestringlen == 0 || nativestring == IntPtr.Zero) return "";
\r
124 if (nativestringlen == -1)
\r
129 } while (Marshal.ReadByte(nativestring, nativestringlen) != 0);
\r
132 byte[] byteArray = new byte[nativestringlen];
\r
134 Marshal.Copy(nativestring, byteArray, 0, nativestringlen);
\r
136 return _utf8.GetString(byteArray, 0, nativestringlen);
\r
142 #region DateTime Conversion Functions
\r
144 /// Converts a string into a DateTime, using the current DateTimeFormat specified for the connection when it was opened.
\r
147 /// Acceptable ISO8601 DateTime formats are:
\r
148 /// yyyy-MM-dd HH:mm:ss
\r
150 /// yyyyMMddTHHmmssfffffff
\r
157 /// <param name="dateText">The string containing either a Tick value, a JulianDay double, or an ISO8601-format string</param>
\r
158 /// <returns>A DateTime value</returns>
\r
159 public DateTime ToDateTime(string dateText)
\r
161 switch (_datetimeFormat)
\r
163 case SQLiteDateFormats.Ticks:
\r
164 return new DateTime(Convert.ToInt64(dateText, CultureInfo.InvariantCulture));
\r
165 case SQLiteDateFormats.JulianDay:
\r
166 return ToDateTime(Convert.ToDouble(dateText, CultureInfo.InvariantCulture));
\r
167 case SQLiteDateFormats.UnixEpoch:
\r
168 return UnixEpoch.AddSeconds(Convert.ToInt32(dateText, CultureInfo.InvariantCulture));
\r
170 return DateTime.ParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None);
\r
175 /// Converts a julianday value into a DateTime
\r
177 /// <param name="julianDay">The value to convert</param>
\r
178 /// <returns>A .NET DateTime</returns>
\r
179 public DateTime ToDateTime(double julianDay)
\r
181 return DateTime.FromOADate(julianDay - 2415018.5);
\r
185 /// Converts a DateTime struct to a JulianDay double
\r
187 /// <param name="value">The DateTime to convert</param>
\r
188 /// <returns>The JulianDay value the Datetime represents</returns>
\r
189 public double ToJulianDay(DateTime value)
\r
191 return value.ToOADate() + 2415018.5;
\r
195 /// Converts a DateTime to a string value, using the current DateTimeFormat specified for the connection when it was opened.
\r
197 /// <param name="dateValue">The DateTime value to convert</param>
\r
198 /// <returns>Either a string consisting of the tick count for DateTimeFormat.Ticks, a JulianDay double, or a date/time in ISO8601 format.</returns>
\r
199 public string ToString(DateTime dateValue)
\r
201 switch (_datetimeFormat)
\r
203 case SQLiteDateFormats.Ticks:
\r
204 return dateValue.Ticks.ToString(CultureInfo.InvariantCulture);
\r
205 case SQLiteDateFormats.JulianDay:
\r
206 return ToJulianDay(dateValue).ToString(CultureInfo.InvariantCulture);
\r
207 case SQLiteDateFormats.UnixEpoch:
\r
208 return ((long)(dateValue.Subtract(UnixEpoch).Ticks / TimeSpan.TicksPerSecond)).ToString();
\r
210 return dateValue.ToString(_datetimeFormats[7], CultureInfo.InvariantCulture);
\r
215 /// Internal function to convert a UTF-8 encoded IntPtr of the specified length to a DateTime.
\r
218 /// This is a convenience function, which first calls ToString() on the IntPtr to convert it to a string, then calls
\r
219 /// ToDateTime() on the string to return a DateTime.
\r
221 /// <param name="ptr">A pointer to the UTF-8 encoded string</param>
\r
222 /// <param name="len">The length in bytes of the string</param>
\r
223 /// <returns>The parsed DateTime value</returns>
\r
224 internal DateTime ToDateTime(IntPtr ptr, int len)
\r
226 return ToDateTime(ToString(ptr, len));
\r
232 /// Smart method of splitting a string. Skips quoted elements, removes the quotes.
\r
235 /// This split function works somewhat like the String.Split() function in that it breaks apart a string into
\r
236 /// pieces and returns the pieces as an array. The primary differences are:
\r
237 /// <list type="bullet">
\r
238 /// <item><description>Only one character can be provided as a separator character</description></item>
\r
239 /// <item><description>Quoted text inside the string is skipped over when searching for the separator, and the quotes are removed.</description></item>
\r
241 /// Thus, if splitting the following string looking for a comma:<br/>
\r
242 /// One,Two, "Three, Four", Five<br/>
\r
244 /// The resulting array would contain<br/>
\r
247 /// [2] Three, Four<br/>
\r
250 /// Note that the leading and trailing spaces were removed from each item during the split.
\r
252 /// <param name="source">Source string to split apart</param>
\r
253 /// <param name="separator">Separator character</param>
\r
254 /// <returns>A string array of the split up elements</returns>
\r
255 public static string[] Split(string source, char separator)
\r
257 char[] toks = new char[2] { '\"', separator };
\r
258 char[] quot = new char[1] { '\"' };
\r
260 List<string> ls = new List<string>();
\r
263 while (source.Length > 0)
\r
265 n = source.IndexOfAny(toks, n);
\r
266 if (n == -1) break;
\r
267 if (source[n] == toks[0])
\r
269 //source = source.Remove(n, 1);
\r
270 n = source.IndexOfAny(quot, n + 1);
\r
273 //source = "\"" + source;
\r
277 //source = source.Remove(n, 1);
\r
281 s = source.Substring(0, n).Trim();
\r
282 if (s.Length > 1 && s[0] == quot[0] && s[s.Length - 1] == s[0])
\r
283 s = s.Substring(1, s.Length - 2);
\r
285 source = source.Substring(n + 1).Trim();
\r
286 if (s.Length > 0) ls.Add(s);
\r
290 if (source.Length > 0)
\r
293 if (s.Length > 1 && s[0] == quot[0] && s[s.Length - 1] == s[0])
\r
294 s = s.Substring(1, s.Length - 2);
\r
298 string[] ar = new string[ls.Count];
\r
305 /// Convert a value to true or false.
\r
307 /// <param name="source">A string or number representing true or false</param>
\r
308 /// <returns></returns>
\r
309 public static bool ToBoolean(object source)
\r
311 if (source is bool) return (bool)source;
\r
313 return ToBoolean(source.ToString());
\r
317 /// Convert a string to true or false.
\r
319 /// <param name="source">A string representing true or false</param>
\r
320 /// <returns></returns>
\r
322 /// "yes", "no", "y", "n", "0", "1", "on", "off" as well as Boolean.FalseString and Boolean.TrueString will all be
\r
323 /// converted to a proper boolean value.
\r
325 public static bool ToBoolean(string source)
\r
327 if (String.Compare(source, bool.TrueString, StringComparison.OrdinalIgnoreCase) == 0) return true;
\r
328 else if (String.Compare(source, bool.FalseString, StringComparison.OrdinalIgnoreCase) == 0) return false;
\r
330 switch(source.ToLower())
\r
343 throw new ArgumentException("source");
\r
347 #region Type Conversions
\r
349 /// Determines the data type of a column in a statement
\r
351 /// <param name="stmt">The statement to retrieve information for</param>
\r
352 /// <param name="i">The column to retrieve type information on</param>
\r
353 /// <param name="typ">The SQLiteType to receive the affinity for the given column</param>
\r
354 internal static void ColumnToType(SqliteStatement stmt, int i, SQLiteType typ)
\r
356 typ.Type = TypeNameToDbType(stmt._sql.ColumnType(stmt, i, out typ.Affinity));
\r
360 /// Converts a SQLiteType to a .NET Type object
\r
362 /// <param name="t">The SQLiteType to convert</param>
\r
363 /// <returns>Returns a .NET Type object</returns>
\r
364 internal static Type SQLiteTypeToType(SQLiteType t)
\r
366 if (t.Type == DbType.Object)
\r
367 return _affinitytotype[(int)t.Affinity];
\r
369 return SqliteConvert.DbTypeToType(t.Type);
\r
372 private static Type[] _affinitytotype = {
\r
384 /// For a given intrinsic type, return a DbType
\r
386 /// <param name="typ">The native type to convert</param>
\r
387 /// <returns>The corresponding (closest match) DbType</returns>
\r
388 internal static DbType TypeToDbType(Type typ)
\r
390 TypeCode tc = Type.GetTypeCode(typ);
\r
391 if (tc == TypeCode.Object)
\r
393 if (typ == typeof(byte[])) return DbType.Binary;
\r
394 if (typ == typeof(Guid)) return DbType.Guid;
\r
395 return DbType.String;
\r
397 return _typetodbtype[(int)tc];
\r
400 private static DbType[] _typetodbtype = {
\r
412 DbType.Int64, // 11
\r
423 /// Returns the ColumnSize for the given DbType
\r
425 /// <param name="typ">The DbType to get the size of</param>
\r
426 /// <returns></returns>
\r
427 internal static int DbTypeToColumnSize(DbType typ)
\r
429 return _dbtypetocolumnsize[(int)typ];
\r
432 private static int[] _dbtypetocolumnsize = {
\r
458 2147483647, // 25 (Xml)
\r
461 internal static object DbTypeToNumericPrecision(DbType typ)
\r
463 return _dbtypetonumericprecision[(int)typ];
\r
466 private static object[] _dbtypetonumericprecision = {
\r
494 internal static object DbTypeToNumericScale(DbType typ)
\r
496 return _dbtypetonumericscale[(int)typ];
\r
499 private static object[] _dbtypetonumericscale = {
\r
527 internal static string DbTypeToTypeName(DbType typ)
\r
529 for (int n = 0; n < _dbtypeNames.Length; n++)
\r
531 if (_dbtypeNames[n].dataType == typ)
\r
532 return _dbtypeNames[n].typeName;
\r
535 return String.Empty;
\r
538 private static SQLiteTypeNames[] _dbtypeNames = {
\r
539 new SQLiteTypeNames("INTEGER", DbType.Int64),
\r
540 new SQLiteTypeNames("TINYINT", DbType.Byte),
\r
541 new SQLiteTypeNames("INT", DbType.Int32),
\r
542 new SQLiteTypeNames("VARCHAR", DbType.AnsiString),
\r
543 new SQLiteTypeNames("NVARCHAR", DbType.String),
\r
544 new SQLiteTypeNames("CHAR", DbType.AnsiStringFixedLength),
\r
545 new SQLiteTypeNames("NCHAR", DbType.StringFixedLength),
\r
546 new SQLiteTypeNames("FLOAT", DbType.Double),
\r
547 new SQLiteTypeNames("REAL", DbType.Single),
\r
548 new SQLiteTypeNames("BIT", DbType.Boolean),
\r
549 new SQLiteTypeNames("DECIMAL", DbType.Decimal),
\r
550 new SQLiteTypeNames("DATETIME", DbType.DateTime),
\r
551 new SQLiteTypeNames("BLOB", DbType.Binary),
\r
552 new SQLiteTypeNames("UNIQUEIDENTIFIER", DbType.Guid),
\r
553 new SQLiteTypeNames("SMALLINT", DbType.Int16),
\r
556 /// Convert a DbType to a Type
\r
558 /// <param name="typ">The DbType to convert from</param>
\r
559 /// <returns>The closest-match .NET type</returns>
\r
560 internal static Type DbTypeToType(DbType typ)
\r
562 return _dbtypeToType[(int)typ];
\r
565 private static Type[] _dbtypeToType = {
\r
566 typeof(string), // 0
\r
567 typeof(byte[]), // 1
\r
570 typeof(decimal), // 4
\r
571 typeof(DateTime), // 5
\r
572 typeof(DateTime), // 6
\r
573 typeof(decimal), // 7
\r
574 typeof(double), // 8
\r
591 typeof(string), // 25 (Xml)
\r
595 /// For a given type, return the closest-match SQLite TypeAffinity, which only understands a very limited subset of types.
\r
597 /// <param name="typ">The type to evaluate</param>
\r
598 /// <returns>The SQLite type affinity for that type.</returns>
\r
599 internal static TypeAffinity TypeToAffinity(Type typ)
\r
601 TypeCode tc = Type.GetTypeCode(typ);
\r
602 if (tc == TypeCode.Object)
\r
604 if (typ == typeof(byte[]) || typ == typeof(Guid))
\r
605 return TypeAffinity.Blob;
\r
607 return TypeAffinity.Text;
\r
609 return _typecodeAffinities[(int)tc];
\r
612 private static TypeAffinity[] _typecodeAffinities = {
\r
616 TypeAffinity.Int64,
\r
617 TypeAffinity.Int64,
\r
618 TypeAffinity.Int64,
\r
619 TypeAffinity.Int64,
\r
620 TypeAffinity.Int64, // 7
\r
621 TypeAffinity.Int64,
\r
622 TypeAffinity.Int64,
\r
623 TypeAffinity.Int64,
\r
624 TypeAffinity.Int64, // 11
\r
625 TypeAffinity.Int64,
\r
626 TypeAffinity.Double,
\r
627 TypeAffinity.Double,
\r
628 TypeAffinity.Double,
\r
629 TypeAffinity.DateTime,
\r
635 /// For a given type name, return a closest-match .NET type
\r
637 /// <param name="Name">The name of the type to match</param>
\r
638 /// <returns>The .NET DBType the text evaluates to.</returns>
\r
639 internal static DbType TypeNameToDbType(string Name)
\r
641 if (String.IsNullOrEmpty(Name)) return DbType.Object;
\r
643 int x = _typeNames.Length;
\r
644 for (int n = 0; n < x; n++)
\r
646 if (String.Compare(Name, _typeNames[n].typeName, true, CultureInfo.InvariantCulture) == 0)
\r
647 return _typeNames[n].dataType;
\r
649 return DbType.Object;
\r
653 private static SQLiteTypeNames[] _typeNames = {
\r
654 new SQLiteTypeNames("COUNTER", DbType.Int64),
\r
655 new SQLiteTypeNames("AUTOINCREMENT", DbType.Int64),
\r
656 new SQLiteTypeNames("IDENTITY", DbType.Int64),
\r
657 new SQLiteTypeNames("LONGTEXT", DbType.String),
\r
658 new SQLiteTypeNames("LONGCHAR", DbType.String),
\r
659 new SQLiteTypeNames("LONGVARCHAR", DbType.String),
\r
660 new SQLiteTypeNames("LONG", DbType.Int64),
\r
661 new SQLiteTypeNames("TINYINT", DbType.Byte),
\r
662 new SQLiteTypeNames("INTEGER", DbType.Int64),
\r
663 new SQLiteTypeNames("INT", DbType.Int32),
\r
664 new SQLiteTypeNames("VARCHAR", DbType.String),
\r
665 new SQLiteTypeNames("NVARCHAR", DbType.String),
\r
666 new SQLiteTypeNames("CHAR", DbType.String),
\r
667 new SQLiteTypeNames("NCHAR", DbType.String),
\r
668 new SQLiteTypeNames("TEXT", DbType.String),
\r
669 new SQLiteTypeNames("NTEXT", DbType.String),
\r
670 new SQLiteTypeNames("STRING", DbType.String),
\r
671 new SQLiteTypeNames("DOUBLE", DbType.Double),
\r
672 new SQLiteTypeNames("FLOAT", DbType.Double),
\r
673 new SQLiteTypeNames("REAL", DbType.Single),
\r
674 new SQLiteTypeNames("BIT", DbType.Boolean),
\r
675 new SQLiteTypeNames("YESNO", DbType.Boolean),
\r
676 new SQLiteTypeNames("LOGICAL", DbType.Boolean),
\r
677 new SQLiteTypeNames("BOOL", DbType.Boolean),
\r
678 new SQLiteTypeNames("BOOLEAN", DbType.Boolean),
\r
679 new SQLiteTypeNames("NUMERIC", DbType.Decimal),
\r
680 new SQLiteTypeNames("DECIMAL", DbType.Decimal),
\r
681 new SQLiteTypeNames("MONEY", DbType.Decimal),
\r
682 new SQLiteTypeNames("CURRENCY", DbType.Decimal),
\r
683 new SQLiteTypeNames("TIME", DbType.DateTime),
\r
684 new SQLiteTypeNames("DATE", DbType.DateTime),
\r
685 new SQLiteTypeNames("SMALLDATE", DbType.DateTime),
\r
686 new SQLiteTypeNames("BLOB", DbType.Binary),
\r
687 new SQLiteTypeNames("BINARY", DbType.Binary),
\r
688 new SQLiteTypeNames("VARBINARY", DbType.Binary),
\r
689 new SQLiteTypeNames("IMAGE", DbType.Binary),
\r
690 new SQLiteTypeNames("GENERAL", DbType.Binary),
\r
691 new SQLiteTypeNames("OLEOBJECT", DbType.Binary),
\r
692 new SQLiteTypeNames("GUID", DbType.Guid),
\r
693 new SQLiteTypeNames("GUIDBLOB", DbType.Guid),
\r
694 new SQLiteTypeNames("UNIQUEIDENTIFIER", DbType.Guid),
\r
695 new SQLiteTypeNames("MEMO", DbType.String),
\r
696 new SQLiteTypeNames("NOTE", DbType.String),
\r
697 new SQLiteTypeNames("SMALLINT", DbType.Int16),
\r
698 new SQLiteTypeNames("BIGINT", DbType.Int64),
\r
699 new SQLiteTypeNames("TIMESTAMP", DbType.DateTime),
\r
700 new SQLiteTypeNames("DATETIME", DbType.DateTime),
\r
705 /// SQLite has very limited types, and is inherently text-based. The first 5 types below represent the sum of all types SQLite
\r
706 /// understands. The DateTime extension to the spec is for internal use only.
\r
708 public enum TypeAffinity
\r
715 /// All integers in SQLite default to Int64
\r
719 /// All floating point numbers in SQLite default to double
\r
723 /// The default data type of SQLite is text
\r
727 /// Typically blob types are only seen when returned from a function
\r
731 /// Null types can be returned from functions
\r
735 /// Used internally by this provider
\r
739 /// Used internally
\r
745 /// This implementation of SQLite for ADO.NET can process date/time fields in databases in only one of three formats. Ticks, ISO8601
\r
749 /// ISO8601 is more compatible, readable, fully-processable, but less accurate as it doesn't provide time down to fractions of a second.
\r
750 /// JulianDay is the numeric format the SQLite uses internally and is arguably the most compatible with 3rd party tools. It is
\r
751 /// not readable as text without post-processing.
\r
752 /// Ticks less compatible with 3rd party tools that query the database, and renders the DateTime field unreadable as text without post-processing.
\r
754 /// The preferred order of choosing a datetime format is JulianDay, ISO8601, and then Ticks. Ticks is mainly present for legacy
\r
757 public enum SQLiteDateFormats
\r
760 /// Using ticks is not recommended and is not well supported with LINQ.
\r
764 /// The default format for this provider.
\r
768 /// JulianDay format, which is what SQLite uses internally
\r
772 /// The whole number of seconds since the Unix epoch (January 1, 1970).
\r
778 /// This enum determines how SQLite treats its journal file.
\r
781 /// By default SQLite will create and delete the journal file when needed during a transaction.
\r
782 /// However, for some computers running certain filesystem monitoring tools, the rapid
\r
783 /// creation and deletion of the journal file can cause those programs to fail, or to interfere with SQLite.
\r
785 /// If a program or virus scanner is interfering with SQLite's journal file, you may receive errors like "unable to open database file"
\r
786 /// when starting a transaction. If this is happening, you may want to change the default journal mode to Persist.
\r
788 public enum SQLiteJournalModeEnum
\r
791 /// The default mode, this causes SQLite to create and destroy the journal file as-needed.
\r
795 /// When this is set, SQLite will keep the journal file even after a transaction has completed. It's contents will be erased,
\r
796 /// and the journal re-used as often as needed. If it is deleted, it will be recreated the next time it is needed.
\r
800 /// This option disables the rollback journal entirely. Interrupted transactions or a program crash can cause database
\r
801 /// corruption in this mode!
\r
807 /// Struct used internally to determine the datatype of a column in a resultset
\r
809 internal class SQLiteType
\r
812 /// The DbType of the column, or DbType.Object if it cannot be determined
\r
814 internal DbType Type;
\r
816 /// The affinity of a column, used for expressions or when Type is DbType.Object
\r
818 internal TypeAffinity Affinity;
\r
821 internal struct SQLiteTypeNames
\r
823 internal SQLiteTypeNames(string newtypeName, DbType newdataType)
\r
825 typeName = newtypeName;
\r
826 dataType = newdataType;
\r
829 internal string typeName;
\r
830 internal DbType dataType;
\r