2 // Mono.Data.Sqlite.SQLiteConvert.cs
5 // Robert Simpson (robert@blackcastlesoft.com)
7 // Adapted and modified for the Mono Project by
8 // Marek Habersack (grendello@gmail.com)
11 // Copyright (C) 2006 Novell, Inc (http://www.novell.com)
12 // Copyright (C) 2007 Marek Habersack
14 // Permission is hereby granted, free of charge, to any person obtaining
15 // a copy of this software and associated documentation files (the
16 // "Software"), to deal in the Software without restriction, including
17 // without limitation the rights to use, copy, modify, merge, publish,
18 // distribute, sublicense, and/or sell copies of the Software, and to
19 // permit persons to whom the Software is furnished to do so, subject to
20 // the following conditions:
22 // The above copyright notice and this permission notice shall be
23 // included in all copies or substantial portions of the Software.
25 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
26 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
27 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
28 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
29 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
30 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
31 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
34 /********************************************************
35 * ADO.NET 2.0 Data Provider for Sqlite Version 3.X
36 * Written by Robert Simpson (robert@blackcastlesoft.com)
38 * Released to the public domain, use at your own risk!
39 ********************************************************/
41 namespace Mono.Data.Sqlite
45 using System.Runtime.InteropServices;
46 using System.Collections.Generic;
47 using System.ComponentModel;
48 using System.Globalization;
51 #if !PLATFORM_COMPACTFRAMEWORK
52 using System.ComponentModel.Design;
56 /// Sqlite has very limited types, and is inherently text-based. The first 5 types below represent the sum of all types Sqlite
57 /// understands. The DateTime extension to the spec is for internal use only.
59 public enum TypeAffinity
66 /// All integers in Sqlite default to Int64
70 /// All floating point numbers in Sqlite default to double
74 /// The default data type of Sqlite is text
78 /// Typically blob types are only seen when returned from a function
82 /// Null types can be returned from functions
86 /// Used internally by this provider
96 /// This implementation of Sqlite for ADO.NET can process date/time fields in databases in only one of two formats. Ticks and ISO8601.
97 /// Ticks is inherently more accurate, but less compatible with 3rd party tools that query the database, and renders the DateTime field
98 /// unreadable without post-processing.
99 /// ISO8601 is more compatible, readable, fully-processable, but less accurate as it doesn't provide time down to fractions of a second.
101 public enum SqliteDateFormats
104 /// Using ticks is more accurate but less compatible with other viewers and utilities that access your database.
108 /// The default format for this provider.
114 /// Struct used internally to determine the datatype of a column in a resultset
116 internal struct SqliteType
119 /// The DbType of the column, or DbType.Object if it cannot be determined
121 internal DbType Type;
123 /// The affinity of a column, used for expressions or when Type is DbType.Object
125 internal TypeAffinity Affinity;
128 internal struct SqliteTypeNames
130 internal SqliteTypeNames(string newtypeName, DbType newdataType)
132 typeName = newtypeName;
133 dataType = newdataType;
136 internal string typeName;
137 internal DbType dataType;
141 /// This base class provides datatype conversion services for the Sqlite provider.
143 public abstract class SqliteConvert
146 /// An array of ISO8601 datetime formats we support conversion from
148 private static string[] _datetimeFormats = new string[] {
149 "yyyy-MM-dd HH:mm:ss.fffffff",
150 "yyyy-MM-dd HH:mm:ss",
154 "yyyyMMddTHHmmssfffffff",
162 "yyyy-MM-dd HH:mm:ss.fff",
164 "yyyy-MM-ddTHH:mm:ss",
165 "yyyy-MM-ddTHH:mm:ss.fff",
166 "yyyy-MM-ddTHH:mm:ss.ffffff",
171 /// An UTF-8 Encoding instance, so we can convert strings to and from UTF-8
173 private Encoding _utf8 = new UTF8Encoding();
175 /// The default DateTime format for this instance
177 internal SqliteDateFormats _datetimeFormat;
179 /// Initializes the conversion class
181 /// <param name="fmt">The default date/time format to use for this instance</param>
182 internal SqliteConvert(SqliteDateFormats fmt)
184 _datetimeFormat = fmt;
187 #region UTF-8 Conversion Functions
189 /// Converts a string to a UTF-8 encoded byte array sized to include a null-terminating character.
191 /// <param name="sourceText">The string to convert to UTF-8</param>
192 /// <returns>A byte array containing the converted string plus an extra 0 terminating byte at the end of the array.</returns>
193 public byte[] ToUTF8(string sourceText)
196 int nlen = _utf8.GetByteCount(sourceText) + 1;
198 byteArray = new byte[nlen];
199 nlen = _utf8.GetBytes(sourceText, 0, sourceText.Length, byteArray, 0);
206 /// Convert a DateTime to a UTF-8 encoded, zero-terminated byte array.
209 /// This function is a convenience function, which first calls ToString() on the DateTime, and then calls ToUTF8() with the
212 /// <param name="dateTimeValue">The DateTime to convert.</param>
213 /// <returns>The UTF-8 encoded string, including a 0 terminating byte at the end of the array.</returns>
214 public byte[] ToUTF8(DateTime dateTimeValue)
216 return ToUTF8(ToString(dateTimeValue));
220 /// Converts a UTF-8 encoded IntPtr of the specified length into a .NET string
222 /// <param name="nativestring">The pointer to the memory where the UTF-8 string is encoded</param>
223 /// <param name="nativestringlen">The number of bytes to decode</param>
224 /// <returns>A string containing the translated character(s)</returns>
225 public virtual string ToString(IntPtr nativestring)
227 return UTF8ToString(nativestring);
231 /// Converts a UTF-8 encoded IntPtr of the specified length into a .NET string
233 /// <param name="nativestring">The pointer to the memory where the UTF-8 string is encoded</param>
234 /// <param name="nativestringlen">The number of bytes to decode</param>
235 /// <returns>A string containing the translated character(s)</returns>
236 public virtual string UTF8ToString(IntPtr nativestring)
238 return Marshal.PtrToStringAuto (nativestring);
244 #region DateTime Conversion Functions
246 /// Converts a string into a DateTime, using the current DateTimeFormat specified for the connection when it was opened.
249 /// Acceptable ISO8601 DateTime formats are:
250 /// yyyy-MM-dd HH:mm:ss
252 /// yyyyMMddTHHmmssfffffff
259 /// <param name="dateText">The string containing either a Tick value or an ISO8601-format string</param>
260 /// <returns>A DateTime value</returns>
261 public DateTime ToDateTime(string dateText)
263 switch (_datetimeFormat)
265 case SqliteDateFormats.Ticks:
266 return new DateTime(Convert.ToInt64(dateText, CultureInfo.InvariantCulture));
268 return DateTime.ParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None);
273 /// Converts a DateTime to a string value, using the current DateTimeFormat specified for the connection when it was opened.
275 /// <param name="dateValue">The DateTime value to convert</param>
276 /// <returns>Either a string consisting of the tick count for DateTimeFormat.Ticks, or a date/time in ISO8601 format.</returns>
277 public string ToString(DateTime dateValue)
279 switch (_datetimeFormat)
281 case SqliteDateFormats.Ticks:
282 return dateValue.Ticks.ToString(CultureInfo.InvariantCulture);
284 return dateValue.ToString(_datetimeFormats[0], CultureInfo.InvariantCulture);
289 /// Internal function to convert a UTF-8 encoded IntPtr of the specified length to a DateTime.
292 /// This is a convenience function, which first calls ToString() on the IntPtr to convert it to a string, then calls
293 /// ToDateTime() on the string to return a DateTime.
295 /// <param name="ptr">A pointer to the UTF-8 encoded string</param>
296 /// <param name="len">The length in bytes of the string</param>
297 /// <returns>The parsed DateTime value</returns>
298 internal DateTime ToDateTime(IntPtr ptr)
300 return ToDateTime(ToString(ptr));
305 /// Smart method of splitting a string. Skips quoted elements, removes the quotes.
308 /// This split function works somewhat like the String.Split() function in that it breaks apart a string into
309 /// pieces and returns the pieces as an array. The primary differences are:
310 /// <list type="bullet">
311 /// <item><description>Only one character can be provided as a separator character</description></item>
312 /// <item><description>Quoted text inside the string is skipped over when searching for the separator, and the quotes are removed.</description></item>
314 /// Thus, if splitting the following string looking for a comma:<br/>
315 /// One,Two, "Three, Four", Five<br/>
317 /// The resulting array would contain<br/>
320 /// [2] Three, Four<br/>
323 /// Note that the leading and trailing spaces were removed from each item during the split.
325 /// <param name="source">Source string to split apart</param>
326 /// <param name="separator">Separator character</param>
327 /// <returns>A string array of the split up elements</returns>
328 public static string[] Split(string source, char separator)
330 char[] toks = new char[2] { '\"', separator };
331 char[] quot = new char[1] { '\"' };
333 List<string> ls = new List<string>();
336 while (source.Length > 0)
338 n = source.IndexOfAny(toks, n);
340 if (source[n] == toks[0])
342 source = source.Remove(n, 1);
343 n = source.IndexOfAny(quot, n);
346 source = "\"" + source;
349 source = source.Remove(n, 1);
353 s = source.Substring(0, n).Trim();
354 source = source.Substring(n + 1).Trim();
355 if (s.Length > 0) ls.Add(s);
359 if (source.Length > 0) ls.Add(source);
361 string[] ar = new string[ls.Count];
367 #region Type Conversions
369 /// Determines the data type of a column in a statement
371 /// <param name="stmt">The statement to retrieve information for</param>
372 /// <param name="i">The column to retrieve type information on</param>
373 /// <returns>Returns a SqliteType struct</returns>
374 internal static SqliteType ColumnToType(SqliteStatement stmt, int i)
378 typ.Type = TypeNameToDbType(stmt._sql.ColumnType(stmt, i, out typ.Affinity));
384 /// Converts a SqliteType to a .NET Type object
386 /// <param name="t">The SqliteType to convert</param>
387 /// <returns>Returns a .NET Type object</returns>
388 internal static Type SqliteTypeToType(SqliteType t)
390 if (t.Type != DbType.Object)
391 return SqliteConvert.DbTypeToType(t.Type);
393 return _typeaffinities[(int)t.Affinity];
396 static Type[] _typeaffinities = {
412 /// For a given intrinsic type, return a DbType
414 /// <param name="typ">The native type to convert</param>
415 /// <returns>The corresponding (closest match) DbType</returns>
416 internal static DbType TypeToDbType(Type typ)
418 TypeCode tc = Type.GetTypeCode(typ);
419 if (tc == TypeCode.Object)
421 if (typ == typeof(byte[])) return DbType.Binary;
422 if (typ == typeof(Guid)) return DbType.Guid;
423 return DbType.String;
425 return _typetodbtype[(int)tc];
428 private static DbType[] _typetodbtype = {
451 /// Returns the ColumnSize for the given DbType
453 /// <param name="typ">The DbType to get the size of</param>
454 /// <returns></returns>
455 internal static int DbTypeToColumnSize(DbType typ)
457 return _dbtypetocolumnsize[(int)typ];
460 private static int[] _dbtypetocolumnsize = {
486 2147483647, // 25 (Xml)
490 /// Convert a DbType to a Type
492 /// <param name="typ">The DbType to convert from</param>
493 /// <returns>The closest-match .NET type</returns>
494 internal static Type DbTypeToType(DbType typ)
496 return _dbtypeToType[(int)typ];
499 private static Type[] _dbtypeToType = {
504 typeof(decimal), // 4
505 typeof(DateTime), // 5
506 typeof(DateTime), // 6
507 typeof(decimal), // 7
525 typeof(string), // 25 (Xml)
529 /// For a given type, return the closest-match Sqlite TypeAffinity, which only understands a very limited subset of types.
531 /// <param name="typ">The type to evaluate</param>
532 /// <returns>The Sqlite type affinity for that type.</returns>
533 internal static TypeAffinity TypeToAffinity(Type typ)
535 TypeCode tc = Type.GetTypeCode(typ);
536 if (tc == TypeCode.Object)
538 if (typ == typeof(byte[]) || typ == typeof(Guid))
539 return TypeAffinity.Blob;
541 return TypeAffinity.Text;
543 return _typecodeAffinities[(int)tc];
546 private static TypeAffinity[] _typecodeAffinities = {
554 TypeAffinity.Int64, // 7
558 TypeAffinity.Int64, // 11
563 TypeAffinity.DateTime,
569 /// For a given type name, return a closest-match .NET type
571 /// <param name="Name">The name of the type to match</param>
572 /// <returns>The .NET DBType the text evaluates to.</returns>
573 internal static DbType TypeNameToDbType(string Name)
575 if (String.IsNullOrEmpty(Name)) return DbType.Object;
577 int x = _typeNames.Length;
578 for (int n = 0; n < x; n++)
580 if (String.Compare(Name, 0, _typeNames[n].typeName, 0, _typeNames[n].typeName.Length, true, CultureInfo.InvariantCulture) == 0)
581 return _typeNames[n].dataType;
583 return DbType.Object;
587 private static SqliteTypeNames[] _typeNames = {
588 new SqliteTypeNames("COUNTER", DbType.Int64),
589 new SqliteTypeNames("AUTOINCREMENT", DbType.Int64),
590 new SqliteTypeNames("IDENTITY", DbType.Int64),
591 new SqliteTypeNames("LONGTEXT", DbType.String),
592 new SqliteTypeNames("LONGCHAR", DbType.String),
593 new SqliteTypeNames("LONGVARCHAR", DbType.String),
594 new SqliteTypeNames("LONG", DbType.Int64),
595 new SqliteTypeNames("TINYINT", DbType.Byte),
596 new SqliteTypeNames("INTEGER", DbType.Int64),
597 new SqliteTypeNames("INT", DbType.Int32),
598 new SqliteTypeNames("VARCHAR", DbType.String),
599 new SqliteTypeNames("NVARCHAR", DbType.String),
600 new SqliteTypeNames("CHAR", DbType.String),
601 new SqliteTypeNames("NCHAR", DbType.String),
602 new SqliteTypeNames("TEXT", DbType.String),
603 new SqliteTypeNames("NTEXT", DbType.String),
604 new SqliteTypeNames("STRING", DbType.String),
605 new SqliteTypeNames("DOUBLE", DbType.Double),
606 new SqliteTypeNames("FLOAT", DbType.Double),
607 new SqliteTypeNames("REAL", DbType.Single),
608 new SqliteTypeNames("BIT", DbType.Boolean),
609 new SqliteTypeNames("YESNO", DbType.Boolean),
610 new SqliteTypeNames("LOGICAL", DbType.Boolean),
611 new SqliteTypeNames("BOOL", DbType.Boolean),
612 new SqliteTypeNames("NUMERIC", DbType.Decimal),
613 new SqliteTypeNames("DECIMAL", DbType.Decimal),
614 new SqliteTypeNames("MONEY", DbType.Decimal),
615 new SqliteTypeNames("CURRENCY", DbType.Decimal),
616 new SqliteTypeNames("TIME", DbType.DateTime),
617 new SqliteTypeNames("DATE", DbType.DateTime),
618 new SqliteTypeNames("SMALLDATE", DbType.DateTime),
619 new SqliteTypeNames("BLOB", DbType.Binary),
620 new SqliteTypeNames("BINARY", DbType.Binary),
621 new SqliteTypeNames("VARBINARY", DbType.Binary),
622 new SqliteTypeNames("IMAGE", DbType.Binary),
623 new SqliteTypeNames("GENERAL", DbType.Binary),
624 new SqliteTypeNames("OLEOBJECT", DbType.Binary),
625 new SqliteTypeNames("GUID", DbType.Guid),
626 new SqliteTypeNames("UNIQUEIDENTIFIER", DbType.Guid),
627 new SqliteTypeNames("MEMO", DbType.String),
628 new SqliteTypeNames("NOTE", DbType.String),
629 new SqliteTypeNames("SMALLINT", DbType.Int16),
630 new SqliteTypeNames("BIGINT", DbType.Int64),