/******************************************************** * 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.Data; using System.Data.Common; using System.Diagnostics; using System.Collections.Generic; using System.Globalization; using System.ComponentModel; using System.Reflection; using System.Runtime.InteropServices; using System.IO; using System.Text; using System.Threading; ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// This class represents a single value to be returned /// from the class via /// its , /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , or /// method. If the value of the /// associated public field of this class is null upon returning from the /// callback, the null value will only be used if the return type for the /// method called is not a value type. /// If the value to be returned from the /// method is unsuitable (e.g. null with a value type), an exception will /// be thrown. /// public sealed class SQLiteDataReaderValue { /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public SQLiteBlob BlobValue; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public bool? BooleanValue; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public byte? ByteValue; /// /// The value to be returned from the /// method. /// public byte[] BytesValue; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public char? CharValue; /// /// The value to be returned from the /// method. /// public char[] CharsValue; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public DateTime? DateTimeValue; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public decimal? DecimalValue; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public double? DoubleValue; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public float? FloatValue; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public Guid? GuidValue; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public short? Int16Value; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public int? Int32Value; /// /// The value to be returned from the /// method -OR- null to /// indicate an error. /// public long? Int64Value; /// /// The value to be returned from the /// method. /// public string StringValue; /// /// The value to be returned from the /// method. /// public object Value; } ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// This class represents the parameters that are provided /// to the methods, with /// the exception of the column index (provided separately). /// public abstract class SQLiteReadEventArgs : EventArgs { // nothing. } ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// This class represents the parameters that are provided to /// the method, with /// the exception of the column index (provided separately). /// public class SQLiteReadBlobEventArgs : SQLiteReadEventArgs { #region Private Data /// /// Provides the underlying storage for the /// property. /// private bool readOnly; #endregion ///////////////////////////////////////////////////////////////////////// #region Private Constructors /// /// Constructs an instance of this class to pass into a user-defined /// callback associated with the /// method. /// /// /// The value that was originally specified for the "readOnly" /// parameter to the method. /// internal SQLiteReadBlobEventArgs( bool readOnly ) { this.readOnly = readOnly; } #endregion ///////////////////////////////////////////////////////////////////////// #region Public Properties /// /// The value that was originally specified for the "readOnly" /// parameter to the method. /// public bool ReadOnly { get { return readOnly; } set { readOnly = value; } } #endregion } ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// This class represents the parameters that are provided /// to the and /// methods, with /// the exception of the column index (provided separately). /// public class SQLiteReadArrayEventArgs : SQLiteReadEventArgs { #region Private Data /// /// Provides the underlying storage for the /// property. /// private long dataOffset; /// /// Provides the underlying storage for the /// property. /// private byte[] byteBuffer; /// /// Provides the underlying storage for the /// property. /// private char[] charBuffer; /// /// Provides the underlying storage for the /// property. /// private int bufferOffset; /// /// Provides the underlying storage for the /// property. /// private int length; #endregion ///////////////////////////////////////////////////////////////////////// #region Private Constructors /// /// Constructs an instance of this class to pass into a user-defined /// callback associated with the /// method. /// /// /// The value that was originally specified for the "dataOffset" /// parameter to the or /// methods. /// /// /// The value that was originally specified for the "buffer" /// parameter to the /// method. /// /// /// The value that was originally specified for the "bufferOffset" /// parameter to the or /// methods. /// /// /// The value that was originally specified for the "length" /// parameter to the or /// methods. /// internal SQLiteReadArrayEventArgs( long dataOffset, byte[] byteBuffer, int bufferOffset, int length ) { this.dataOffset = dataOffset; this.byteBuffer = byteBuffer; this.bufferOffset = bufferOffset; this.length = length; } ///////////////////////////////////////////////////////////////////////// /// /// Constructs an instance of this class to pass into a user-defined /// callback associated with the /// method. /// /// /// The value that was originally specified for the "dataOffset" /// parameter to the or /// methods. /// /// /// The value that was originally specified for the "buffer" /// parameter to the /// method. /// /// /// The value that was originally specified for the "bufferOffset" /// parameter to the or /// methods. /// /// /// The value that was originally specified for the "length" /// parameter to the or /// methods. /// internal SQLiteReadArrayEventArgs( long dataOffset, char[] charBuffer, int bufferOffset, int length ) { this.dataOffset = dataOffset; this.charBuffer = charBuffer; this.bufferOffset = bufferOffset; this.length = length; } #endregion ///////////////////////////////////////////////////////////////////////// #region Public Properties /// /// The value that was originally specified for the "dataOffset" /// parameter to the or /// methods. /// public long DataOffset { get { return dataOffset; } set { dataOffset = value; } } ///////////////////////////////////////////////////////////////////////// /// /// The value that was originally specified for the "buffer" /// parameter to the /// method. /// public byte[] ByteBuffer { get { return byteBuffer; } } ///////////////////////////////////////////////////////////////////////// /// /// The value that was originally specified for the "buffer" /// parameter to the /// method. /// public char[] CharBuffer { get { return charBuffer; } } ///////////////////////////////////////////////////////////////////////// /// /// The value that was originally specified for the "bufferOffset" /// parameter to the or /// methods. /// public int BufferOffset { get { return bufferOffset; } set { bufferOffset = value; } } ///////////////////////////////////////////////////////////////////////// /// /// The value that was originally specified for the "length" /// parameter to the or /// methods. /// public int Length { get { return length; } set { length = value; } } #endregion } ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// This class represents the parameters and return values for the /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , /// , and /// methods. /// public class SQLiteReadValueEventArgs : SQLiteReadEventArgs { #region Private Data /// /// Provides the underlying storage for the /// property. /// private string methodName; /// /// Provides the underlying storage for the /// property. /// private SQLiteReadEventArgs extraEventArgs; /// /// Provides the underlying storage for the /// property. /// private SQLiteDataReaderValue value; #endregion ///////////////////////////////////////////////////////////////////////// #region Private Constructors /// /// Constructs a new instance of this class. Depending on the method /// being called, the and/or /// parameters may be null. /// /// /// The name of the method that was /// responsible for invoking this callback. /// /// /// If the or /// method is being called, /// this object will contain the array related parameters for that /// method. If the method is /// being called, this object will contain the blob related parameters /// for that method. /// /// /// This may be used by the callback to set the return value for the /// called method. /// internal SQLiteReadValueEventArgs( string methodName, SQLiteReadEventArgs extraEventArgs, SQLiteDataReaderValue value ) { this.methodName = methodName; this.extraEventArgs = extraEventArgs; this.value = value; } #endregion ///////////////////////////////////////////////////////////////////////// #region Public Properties /// /// The name of the method that was /// responsible for invoking this callback. /// public string MethodName { get { return methodName; } } ///////////////////////////////////////////////////////////////////////// /// /// If the or /// method is being called, /// this object will contain the array related parameters for that /// method. If the method is /// being called, this object will contain the blob related parameters /// for that method. /// public SQLiteReadEventArgs ExtraEventArgs { get { return extraEventArgs; } } ///////////////////////////////////////////////////////////////////////// /// /// This may be used by the callback to set the return value for the /// called method. /// public SQLiteDataReaderValue Value { get { return value; } } #endregion } ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// This represents a method that will be called in response to a request to /// bind a parameter to a command. If an exception is thrown, it will cause /// the parameter binding operation to fail -AND- it will continue to unwind /// the call stack. /// /// /// The instance in use. /// /// /// The instance in use. /// /// /// The flags associated with the instance /// in use. /// /// /// The instance being bound to the command. /// /// /// The database type name associated with this callback. /// /// /// The ordinal of the parameter being bound to the command. /// /// /// The data originally used when registering this callback. /// /// /// Non-zero if the default handling for the parameter binding call should /// be skipped (i.e. the parameter should not be bound at all). Great care /// should be used when setting this to non-zero. /// public delegate void SQLiteBindValueCallback( SQLiteConvert convert, SQLiteCommand command, SQLiteConnectionFlags flags, SQLiteParameter parameter, string typeName, int index, object userData, out bool complete ); ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// This represents a method that will be called in response to a request /// to read a value from a data reader. If an exception is thrown, it will /// cause the data reader operation to fail -AND- it will continue to unwind /// the call stack. /// /// /// The instance in use. /// /// /// The instance in use. /// /// /// The flags associated with the instance /// in use. /// /// /// The parameter and return type data for the column being read from the /// data reader. /// /// /// The database type name associated with this callback. /// /// /// The zero based index of the column being read from the data reader. /// /// /// The data originally used when registering this callback. /// /// /// Non-zero if the default handling for the data reader call should be /// skipped. If this is set to non-zero and the necessary return value /// is unavailable or unsuitable, an exception will be thrown. /// public delegate void SQLiteReadValueCallback( SQLiteConvert convert, SQLiteDataReader dataReader, SQLiteConnectionFlags flags, SQLiteReadEventArgs eventArgs, string typeName, int index, object userData, out bool complete ); ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// This class represents the custom data type handling callbacks /// for a single type name. /// public sealed class SQLiteTypeCallbacks { #region Private Data /// /// Provides the underlying storage for the /// property. /// private string typeName; /// /// Provides the underlying storage for the /// property. /// private SQLiteBindValueCallback bindValueCallback; /// /// Provides the underlying storage for the /// property. /// private SQLiteReadValueCallback readValueCallback; /// /// Provides the underlying storage for the /// property. /// private object bindValueUserData; /// /// Provides the underlying storage for the /// property. /// private object readValueUserData; #endregion ///////////////////////////////////////////////////////////////////////// #region Private Constructors /// /// Constructs an instance of this class. /// /// /// The custom paramater binding callback. This parameter may be null. /// /// /// The custom data reader value callback. This parameter may be null. /// /// /// The extra data to pass into the parameter binding callback. This /// parameter may be null. /// /// /// The extra data to pass into the data reader value callback. This /// parameter may be null. /// private SQLiteTypeCallbacks( SQLiteBindValueCallback bindValueCallback, SQLiteReadValueCallback readValueCallback, object bindValueUserData, object readValueUserData ) { this.bindValueCallback = bindValueCallback; this.readValueCallback = readValueCallback; this.bindValueUserData = bindValueUserData; this.readValueUserData = readValueUserData; } #endregion ///////////////////////////////////////////////////////////////////////// #region Static "Factory" Methods /// /// Creates an instance of the class. /// /// /// The custom paramater binding callback. This parameter may be null. /// /// /// The custom data reader value callback. This parameter may be null. /// /// /// The extra data to pass into the parameter binding callback. This /// parameter may be null. /// /// /// The extra data to pass into the data reader value callback. This /// parameter may be null. /// public static SQLiteTypeCallbacks Create( SQLiteBindValueCallback bindValueCallback, SQLiteReadValueCallback readValueCallback, object bindValueUserData, object readValueUserData ) { return new SQLiteTypeCallbacks( bindValueCallback, readValueCallback, bindValueUserData, readValueUserData); } #endregion ///////////////////////////////////////////////////////////////////////// #region Public Properties /// /// The database type name that the callbacks contained in this class /// will apply to. This value may not be null. /// public string TypeName { get { return typeName; } internal set { typeName = value; } } ///////////////////////////////////////////////////////////////////////// /// /// The custom paramater binding callback. This value may be null. /// public SQLiteBindValueCallback BindValueCallback { get { return bindValueCallback; } } ///////////////////////////////////////////////////////////////////////// /// /// The custom data reader value callback. This value may be null. /// public SQLiteReadValueCallback ReadValueCallback { get { return readValueCallback; } } ///////////////////////////////////////////////////////////////////////// /// /// The extra data to pass into the parameter binding callback. This /// value may be null. /// public object BindValueUserData { get { return bindValueUserData; } } ///////////////////////////////////////////////////////////////////////// /// /// The extra data to pass into the data reader value callback. This /// value may be null. /// public object ReadValueUserData { get { return readValueUserData; } } #endregion } ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// This class represents the mappings between database type names /// and their associated custom data type handling callbacks. /// internal sealed class SQLiteTypeCallbacksMap : Dictionary { /// /// Constructs an (empty) instance of this class. /// public SQLiteTypeCallbacksMap() : base(new TypeNameStringComparer()) { // do nothing. } } ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// Event data for connection event handlers. /// public class ConnectionEventArgs : EventArgs { /// /// The type of event being raised. /// public readonly SQLiteConnectionEventType EventType; /// /// The associated with this event, if any. /// public readonly StateChangeEventArgs EventArgs; /// /// The transaction associated with this event, if any. /// public readonly IDbTransaction Transaction; /// /// The command associated with this event, if any. /// public readonly IDbCommand Command; /// /// The data reader associated with this event, if any. /// public readonly IDataReader DataReader; /// /// The critical handle associated with this event, if any. /// #if !PLATFORM_COMPACTFRAMEWORK public readonly CriticalHandle CriticalHandle; #else public readonly object CriticalHandle; #endif /// /// Command or message text associated with this event, if any. /// public readonly string Text; /// /// Extra data associated with this event, if any. /// public readonly object Data; /// /// Constructs the object. /// /// The type of event being raised. /// The base associated /// with this event, if any. /// The transaction associated with this event, if any. /// The command associated with this event, if any. /// The data reader associated with this event, if any. /// The critical handle associated with this event, if any. /// The command or message text, if any. /// The extra data, if any. internal ConnectionEventArgs( SQLiteConnectionEventType eventType, StateChangeEventArgs eventArgs, IDbTransaction transaction, IDbCommand command, IDataReader dataReader, #if !PLATFORM_COMPACTFRAMEWORK CriticalHandle criticalHandle, #else object criticalHandle, #endif string text, object data ) { EventType = eventType; EventArgs = eventArgs; Transaction = transaction; Command = command; DataReader = dataReader; CriticalHandle = criticalHandle; Text = text; Data = data; } } ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// Raised when an event pertaining to a connection occurs. /// /// The connection involved. /// Extra information about the event. public delegate void SQLiteConnectionEventHandler(object sender, ConnectionEventArgs e); ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// SQLite implentation of DbConnection. /// /// /// The property can contain the following parameter(s), delimited with a semi-colon: /// /// /// Parameter /// Values /// Required /// Default /// /// /// Data Source /// /// This may be a file name, the string ":memory:", or any supported URI (starting with SQLite 3.7.7). /// Starting with release 1.0.86.0, in order to use more than one consecutive backslash (e.g. for a /// UNC path), each of the adjoining backslash characters must be doubled (e.g. "\\Network\Share\test.db" /// would become "\\\\Network\Share\test.db"). /// /// Y /// /// /// /// Uri /// /// If specified, this must be a file name that starts with "file://", "file:", or "/". Any leading /// "file://" or "file:" prefix will be stripped off and the resulting file name will be used to open /// the database. /// /// N /// null /// /// /// FullUri /// /// If specified, this must be a URI in a format recognized by the SQLite core library (starting with /// SQLite 3.7.7). It will be passed verbatim to the SQLite core library. /// /// N /// null /// /// /// Version /// 3 /// N /// 3 /// /// /// UseUTF16Encoding /// /// True - The UTF-16 encoding should be used. ///
/// False - The UTF-8 encoding should be used. ///
/// N /// False ///
/// /// DefaultDbType /// /// This is the default to use when one cannot be determined based on the /// column metadata and the configured type mappings. /// /// N /// null /// /// /// DefaultTypeName /// /// This is the default type name to use when one cannot be determined based on the column metadata /// and the configured type mappings. /// /// N /// null /// /// /// NoDefaultFlags /// /// True - Do not combine the specified (or existing) connection flags with the value of the /// property. ///
/// False - Combine the specified (or existing) connection flags with the value of the /// property. ///
/// N /// False ///
/// /// NoSharedFlags /// /// True - Do not combine the specified (or existing) connection flags with the value of the /// property. ///
/// False - Combine the specified (or existing) connection flags with the value of the /// property. ///
/// N /// False ///
/// /// VfsName /// /// The name of the VFS to use when opening the database connection. /// If this is not specified, the default VFS will be used. /// /// N /// null /// /// /// ZipVfsVersion /// /// If non-null, this is the "version" of ZipVFS to use. This requires /// the System.Data.SQLite interop assembly -AND- primary managed assembly /// to be compiled with the INTEROP_INCLUDE_ZIPVFS option; otherwise, this /// property does nothing. The valid values are "v2" and "v3". Using /// anyother value will cause an exception to be thrown. Please see the /// ZipVFS documentation for more information on how to use this parameter. /// /// N /// null /// /// /// DateTimeFormat /// /// Ticks - Use the value of DateTime.Ticks.
/// ISO8601 - Use the ISO-8601 format. Uses the "yyyy-MM-dd HH:mm:ss.FFFFFFFK" format for UTC /// DateTime values and "yyyy-MM-dd HH:mm:ss.FFFFFFF" format for local DateTime values).
/// JulianDay - The interval of time in days and fractions of a day since January 1, 4713 BC.
/// UnixEpoch - The whole number of seconds since the Unix epoch (January 1, 1970).
/// InvariantCulture - Any culture-independent string value that the .NET Framework can interpret as a valid DateTime.
/// CurrentCulture - Any string value that the .NET Framework can interpret as a valid DateTime using the current culture.
/// N /// ISO8601 ///
/// /// DateTimeKind /// /// Unspecified - Not specified as either UTC or local time. ///
/// Utc - The time represented is UTC. ///
/// Local - The time represented is local time. ///
/// N /// Unspecified ///
/// /// DateTimeFormatString /// /// The exact DateTime format string to use for all formatting and parsing of all DateTime /// values for this connection. /// /// N /// null /// /// /// BaseSchemaName /// /// Some base data classes in the framework (e.g. those that build SQL queries dynamically) /// assume that an ADO.NET provider cannot support an alternate catalog (i.e. database) without supporting /// alternate schemas as well; however, SQLite does not fit into this model. Therefore, this value is used /// as a placeholder and removed prior to preparing any SQL statements that may contain it. /// /// N /// sqlite_default_schema /// /// /// BinaryGUID /// /// True - Store GUID columns in binary form ///
/// False - Store GUID columns as text ///
/// N /// True ///
/// /// Cache Size /// /// If the argument N is positive then the suggested cache size is set to N. /// If the argument N is negative, then the number of cache pages is adjusted /// to use approximately abs(N*4096) bytes of memory. Backwards compatibility /// note: The behavior of cache_size with a negative N was different in SQLite /// versions prior to 3.7.10. In version 3.7.9 and earlier, the number of /// pages in the cache was set to the absolute value of N. /// /// N /// -2000 /// /// /// Synchronous /// /// Normal - Normal file flushing behavior ///
/// Full - Full flushing after all writes ///
/// Off - Underlying OS flushes I/O's ///
/// N /// Full ///
/// /// Page Size /// {size in bytes} /// N /// 4096 /// /// /// Password /// /// {password} - Using this parameter requires that the legacy CryptoAPI based /// codec (or the SQLite Encryption Extension) be enabled at compile-time for /// both the native interop assembly and the core managed assemblies; otherwise, /// using this parameter may result in an exception being thrown when attempting /// to open the connection. /// /// N /// /// /// /// HexPassword /// /// {hexPassword} - Must contain a sequence of zero or more hexadecimal encoded /// byte values without a leading "0x" prefix. Using this parameter requires /// that the legacy CryptoAPI based codec (or the SQLite Encryption Extension) /// be enabled at compile-time for both the native interop assembly and the /// core managed assemblies; otherwise, using this parameter may result in an /// exception being thrown when attempting to open the connection. /// /// N /// /// /// /// Enlist /// /// Y - Automatically enlist in distributed transactions ///
/// N - No automatic enlistment ///
/// N /// Y ///
/// /// Pooling /// /// True - Use connection pooling.
/// False - Do not use connection pooling.

/// WARNING: When using the default connection pool implementation, /// setting this property to True should be avoided by applications that make /// use of COM (either directly or indirectly) due to possible deadlocks that /// can occur during the finalization of some COM objects. ///
/// N /// False ///
/// /// FailIfMissing /// /// True - Don't create the database if it does not exist, throw an error instead ///
/// False - Automatically create the database if it does not exist ///
/// N /// False ///
/// /// Max Page Count /// {size in pages} - Limits the maximum number of pages (limits the size) of the database /// N /// 0 /// /// /// Legacy Format /// /// True - Use the more compatible legacy 3.x database format ///
/// False - Use the newer 3.3x database format which compresses numbers more effectively ///
/// N /// False ///
/// /// Default Timeout /// {time in seconds}
The default command timeout
/// N /// 30 ///
/// /// BusyTimeout /// {time in milliseconds}
Sets the busy timeout for the core library.
/// N /// 0 ///
/// /// WaitTimeout /// {time in milliseconds}
/// EXPERIMENTAL -- The wait timeout to use with /// method. This is only used when /// waiting for the enlistment to be reset prior to enlisting in a transaction, /// and then only when the appropriate connection flag is set.
/// N /// 30000 ///
/// /// Journal Mode /// /// Delete - Delete the journal file after a commit. ///
/// Persist - Zero out and leave the journal file on disk after a /// commit. ///
/// Off - Disable the rollback journal entirely. This saves disk I/O /// but at the expense of database safety and integrity. If the application /// using SQLite crashes in the middle of a transaction when this journaling /// mode is set, then the database file will very likely go corrupt. ///
/// Truncate - Truncate the journal file to zero-length instead of /// deleting it. ///
/// Memory - Store the journal in volatile RAM. This saves disk I/O /// but at the expense of database safety and integrity. If the application /// using SQLite crashes in the middle of a transaction when this journaling /// mode is set, then the database file will very likely go corrupt. ///
/// Wal - Use a write-ahead log instead of a rollback journal. ///
/// N /// Delete ///
/// /// Read Only /// /// True - Open the database for read only access ///
/// False - Open the database for normal read/write access ///
/// N /// False ///
/// /// Max Pool Size /// The maximum number of connections for the given connection string that can be in the connection pool /// N /// 100 /// /// /// Default IsolationLevel /// The default transaciton isolation level /// N /// Serializable /// /// /// Foreign Keys /// Enable foreign key constraints /// N /// False /// /// /// Flags /// Extra behavioral flags for the connection. See the enumeration for possible values. /// N /// Default /// /// /// SetDefaults /// /// True - Apply the default connection settings to the opened database.
/// False - Skip applying the default connection settings to the opened database. ///
/// N /// True ///
/// /// ToFullPath /// /// True - Attempt to expand the data source file name to a fully qualified path before opening. ///
/// False - Skip attempting to expand the data source file name to a fully qualified path before opening. ///
/// N /// True ///
/// /// PrepareRetries /// /// The maximum number of retries when preparing SQL to be executed. This /// normally only applies to preparation errors resulting from the database /// schema being changed. /// /// N /// 3 /// /// /// ProgressOps /// /// The approximate number of virtual machine instructions between progress /// events. In order for progress events to actually fire, the event handler /// must be added to the event as well. /// /// N /// 0 /// /// /// Recursive Triggers /// /// True - Enable the recursive trigger capability. /// False - Disable the recursive trigger capability. /// /// N /// False /// ///
///
public sealed partial class SQLiteConnection : DbConnection, ICloneable, IDisposable { #region Private Constants /// /// The "invalid value" for the enumeration used /// by the property. This constant is shared /// by this class and the SQLiteConnectionStringBuilder class. /// internal const DbType BadDbType = (DbType)(-1); /// /// The default "stub" (i.e. placeholder) base schema name to use when /// returning column schema information. Used as the initial value of /// the BaseSchemaName property. This should start with "sqlite_*" /// because those names are reserved for use by SQLite (i.e. they cannot /// be confused with the names of user objects). /// internal const string DefaultBaseSchemaName = "sqlite_default_schema"; private const string MemoryFileName = ":memory:"; internal const IsolationLevel DeferredIsolationLevel = IsolationLevel.ReadCommitted; internal const IsolationLevel ImmediateIsolationLevel = IsolationLevel.Serializable; private const SQLiteConnectionFlags FallbackDefaultFlags = SQLiteConnectionFlags.Default; private const SQLiteSynchronousEnum DefaultSynchronous = SQLiteSynchronousEnum.Default; private const SQLiteJournalModeEnum DefaultJournalMode = SQLiteJournalModeEnum.Default; private const IsolationLevel DefaultIsolationLevel = IsolationLevel.Serializable; internal const SQLiteDateFormats DefaultDateTimeFormat = SQLiteDateFormats.Default; internal const DateTimeKind DefaultDateTimeKind = DateTimeKind.Unspecified; internal const string DefaultDateTimeFormatString = null; private const string DefaultDataSource = null; private const string DefaultUri = null; private const string DefaultFullUri = null; private const string DefaultHexPassword = null; private const string DefaultPassword = null; private const int DefaultVersion = 3; private const int DefaultPageSize = 4096; private const int DefaultMaxPageCount = 0; private const int DefaultCacheSize = -2000; private const int DefaultMaxPoolSize = 100; private const int DefaultConnectionTimeout = 30; private const int DefaultBusyTimeout = 0; private const int DefaultWaitTimeout = 30000; private const bool DefaultNoDefaultFlags = false; private const bool DefaultNoSharedFlags = false; private const bool DefaultFailIfMissing = false; private const bool DefaultReadOnly = false; internal const bool DefaultBinaryGUID = true; private const bool DefaultUseUTF16Encoding = false; private const bool DefaultToFullPath = true; private const bool DefaultPooling = false; // TODO: Maybe promote this to static property? private const bool DefaultLegacyFormat = false; private const bool DefaultForeignKeys = false; private const bool DefaultRecursiveTriggers = false; private const bool DefaultEnlist = true; private const bool DefaultSetDefaults = true; internal const int DefaultPrepareRetries = 3; private const string DefaultVfsName = null; private const int DefaultProgressOps = 0; #if INTEROP_INCLUDE_ZIPVFS private const string ZipVfs_Automatic = "automatic"; private const string ZipVfs_V2 = "v2"; private const string ZipVfs_V3 = "v3"; private const string DefaultZipVfsVersion = null; #endif private const int SQLITE_FCNTL_CHUNK_SIZE = 6; private const int SQLITE_FCNTL_WIN32_AV_RETRY = 9; private const string _dataDirectory = "|DataDirectory|"; private static string _defaultCatalogName = "main"; private static string _defaultMasterTableName = "sqlite_master"; private static string _temporaryCatalogName = "temp"; private static string _temporaryMasterTableName = "sqlite_temp_master"; #endregion /////////////////////////////////////////////////////////////////////////////////////////////// #region Private Static Data /// /// The managed assembly containing this type. /// private static readonly Assembly _assembly = typeof(SQLiteConnection).Assembly; /// /// Object used to synchronize access to the static instance data /// for this class. /// private static readonly object _syncRoot = new object(); /// /// Static variable to store the connection event handlers to call. /// private static event SQLiteConnectionEventHandler _handlers; /// /// The extra connection flags to be used for all opened connections. /// private static SQLiteConnectionFlags _sharedFlags; /// /// The instance (for this thread) that /// had the most recent call to . /// #if !PLATFORM_COMPACTFRAMEWORK [ThreadStatic()] #endif private static SQLiteConnection _lastConnectionInOpen; #if SQLITE_STANDARD && !PLATFORM_COMPACTFRAMEWORK /// /// Used to hold the active library version number of SQLite. /// private static int _versionNumber; #endif #endregion /////////////////////////////////////////////////////////////////////////////////////////////// #region Private Data /// /// State of the current connection /// private ConnectionState _connectionState; /// /// The connection string /// private string _connectionString; #if DEBUG /// /// This string will contain enough information to identify this connection, /// e.g. the database file name, original thread, etc. It is not currently /// exposed via the public interface as it is intended for use only when /// debugging this library. /// private string _debugString; #endif /// /// Nesting level of the transactions open on the connection /// internal int _transactionLevel; /// /// Transaction counter for the connection. Currently, this is only used /// to build SAVEPOINT names. /// internal int _transactionSequence; /// /// If this flag is non-zero, the method will have /// no effect; however, the method will continue to /// behave as normal. /// internal bool _noDispose; /// /// If set, then the connection is currently being disposed. /// private bool _disposing; /// /// The default isolation level for new transactions /// private IsolationLevel _defaultIsolation; #if !PLATFORM_COMPACTFRAMEWORK /// /// This object is used with lock statements to synchronize access to the /// field, below. /// internal readonly object _enlistmentSyncRoot = new object(); /// /// Whether or not the connection is enlisted in a distrubuted transaction /// internal SQLiteEnlistment _enlistment; #endif /// /// The per-connection mappings between type names and /// values. These mappings override the corresponding global mappings. /// internal SQLiteDbTypeMap _typeNames; /// /// The per-connection mappings between type names and optional callbacks /// for parameter binding and value reading. /// private SQLiteTypeCallbacksMap _typeCallbacks; /// /// The base SQLite object to interop with /// internal SQLiteBase _sql; /// /// The database filename minus path and extension /// private string _dataSource; #if INTEROP_CODEC || INTEROP_INCLUDE_SEE /// /// Temporary password storage, emptied after the database has been opened /// private byte[] _password; #endif /// /// The "stub" (i.e. placeholder) base schema name to use when returning /// column schema information. /// internal string _baseSchemaName; /// /// The extra behavioral flags for this connection, if any. See the /// enumeration for a list of /// possible values. /// private SQLiteConnectionFlags _flags; /// /// The cached values for all settings that have been fetched on behalf /// of this connection. This cache may be cleared by calling the /// method. /// private Dictionary _cachedSettings; /// /// The default databse type for this connection. This value will only /// be used if the /// flag is set. /// private DbType? _defaultDbType; /// /// The default databse type name for this connection. This value will only /// be used if the /// flag is set. /// private string _defaultTypeName; /// /// The name of the VFS to be used when opening the database connection. /// private string _vfsName; /// /// Default command timeout /// private int _defaultTimeout = DefaultConnectionTimeout; /// /// The default busy timeout to use with the SQLite core library. This is /// only used when opening a connection. /// private int _busyTimeout = DefaultBusyTimeout; #if !PLATFORM_COMPACTFRAMEWORK /// /// The default wait timeout to use with /// method. This is only used when waiting for the enlistment to be reset /// prior to enlisting in a transaction, and then only when the appropriate /// connection flag is set. /// private int _waitTimeout = DefaultWaitTimeout; #endif /// /// The maximum number of retries when preparing SQL to be executed. This /// normally only applies to preparation errors resulting from the database /// schema being changed. /// internal int _prepareRetries = DefaultPrepareRetries; /// /// The approximate number of virtual machine instructions between progress /// events. In order for progress events to actually fire, the event handler /// must be added to the event as /// well. This value will only be used when opening the database. /// private int _progressOps = DefaultProgressOps; /// /// Non-zero if the built-in (i.e. framework provided) connection string /// parser should be used when opening the connection. /// private bool _parseViaFramework; internal bool _binaryGuid; internal int _version; private event SQLiteProgressEventHandler _progressHandler; private event SQLiteAuthorizerEventHandler _authorizerHandler; private event SQLiteUpdateEventHandler _updateHandler; private event SQLiteCommitHandler _commitHandler; private event SQLiteTraceEventHandler _traceHandler; private event EventHandler _rollbackHandler; private SQLiteProgressCallback _progressCallback; private SQLiteAuthorizerCallback _authorizerCallback; private SQLiteUpdateCallback _updateCallback; private SQLiteCommitCallback _commitCallback; private SQLiteTraceCallback _traceCallback; private SQLiteRollbackCallback _rollbackCallback; #endregion /////////////////////////////////////////////////////////////////////////////////////////////// private static string GetDefaultCatalogName() { return _defaultCatalogName; } /////////////////////////////////////////////////////////////////////////////////////////////// private static bool IsDefaultCatalogName( string catalogName ) { return String.Compare(catalogName, GetDefaultCatalogName(), StringComparison.OrdinalIgnoreCase) == 0; } /////////////////////////////////////////////////////////////////////////////////////////////// private static string GetTemporaryCatalogName() { return _temporaryCatalogName; } /////////////////////////////////////////////////////////////////////////////////////////////// private static bool IsTemporaryCatalogName( string catalogName ) { return String.Compare(catalogName, GetTemporaryCatalogName(), StringComparison.OrdinalIgnoreCase) == 0; } /////////////////////////////////////////////////////////////////////////////////////////////// private static string GetMasterTableName( bool temporary ) { return temporary ? _temporaryMasterTableName : _defaultMasterTableName; } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// This event is raised whenever the database is opened or closed. /// public override event StateChangeEventHandler StateChange; /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Constructs a new SQLiteConnection object /// /// /// Default constructor /// public SQLiteConnection() : this((string)null) { } /// /// Initializes the connection with the specified connection string. /// /// The connection string to use. public SQLiteConnection(string connectionString) : this(connectionString, false) { // do nothing. } #if INTEROP_VIRTUAL_TABLE /// /// Initializes the connection with a pre-existing native connection handle. /// This constructor overload is intended to be used only by the private /// method. /// /// /// The native connection handle to use. /// /// /// The file name corresponding to the native connection handle. /// /// /// Non-zero if this instance owns the native connection handle and /// should dispose of it when it is no longer needed. /// internal SQLiteConnection(IntPtr db, string fileName, bool ownHandle) : this() { _sql = new SQLite3( SQLiteDateFormats.Default, DateTimeKind.Unspecified, null, db, fileName, ownHandle); _flags = SQLiteConnectionFlags.None; _connectionState = (db != IntPtr.Zero) ? ConnectionState.Open : ConnectionState.Closed; _connectionString = null; /* unknown */ #if DEBUG _debugString = HelperMethods.StringFormat( CultureInfo.InvariantCulture, "db = {0}, fileName = {1}, ownHandle = {2}", db, fileName, ownHandle); #endif } #endif /// /// Initializes the connection with the specified connection string. /// /// /// The connection string to use. /// /// /// Non-zero to parse the connection string using the built-in (i.e. /// framework provided) parser when opening the connection. /// public SQLiteConnection(string connectionString, bool parseViaFramework) { _noDispose = false; #if (SQLITE_STANDARD || USE_INTEROP_DLL || PLATFORM_COMPACTFRAMEWORK) && PRELOAD_NATIVE_LIBRARY UnsafeNativeMethods.Initialize(); #endif SQLiteLog.Initialize(typeof(SQLiteConnection).Name); #if !PLATFORM_COMPACTFRAMEWORK && !INTEROP_LEGACY_CLOSE && SQLITE_STANDARD // // NOTE: Check if the sqlite3_close_v2() native API should be available // to use. This must be done dynamically because the delegate set // here is used by the SQLiteConnectionHandle class, which is a // CriticalHandle derived class (i.e. protected by a constrained // execution region). Therefore, if the underlying native entry // point is unavailable, an exception will be raised even if it is // never actually called (i.e. because the runtime eagerly prepares // all the methods in the call graph of the constrained execution // region). // lock (_syncRoot) { if (_versionNumber == 0) { _versionNumber = SQLite3.SQLiteVersionNumber; if (_versionNumber >= 3007014) SQLiteConnectionHandle.closeConnection = SQLiteBase.CloseConnectionV2; } } #endif _cachedSettings = new Dictionary( new TypeNameStringComparer()); _typeNames = new SQLiteDbTypeMap(); _typeCallbacks = new SQLiteTypeCallbacksMap(); _parseViaFramework = parseViaFramework; _flags = SQLiteConnectionFlags.None; _defaultDbType = null; _defaultTypeName = null; _vfsName = null; _connectionState = ConnectionState.Closed; _connectionString = null; if (connectionString != null) ConnectionString = connectionString; } /// /// Clones the settings and connection string from an existing connection. If the existing connection is already open, this /// function will open its own connection, enumerate any attached databases of the original connection, and automatically /// attach to them. /// /// The connection to copy the settings from. public SQLiteConnection(SQLiteConnection connection) : this(connection.ConnectionString, connection.ParseViaFramework) { #if DEBUG _debugString = connection._debugString; #endif if (connection.State == ConnectionState.Open) { Open(); // Reattach all attached databases from the existing connection using (DataTable tbl = connection.GetSchema("Catalogs")) { foreach (DataRow row in tbl.Rows) { string str = row[0].ToString(); if (!IsDefaultCatalogName(str) && !IsTemporaryCatalogName(str)) { using (SQLiteCommand cmd = CreateCommand()) { cmd.CommandText = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "ATTACH DATABASE '{0}' AS [{1}]", row[1], row[0]); cmd.ExecuteNonQuery(); } } } } } } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Attempts to lookup the native handle associated with the connection. An exception will /// be thrown if this cannot be accomplished. /// /// /// The connection associated with the desired native handle. /// /// /// The native handle associated with the connection or if it /// cannot be determined. /// private static SQLiteConnectionHandle GetNativeHandle( SQLiteConnection connection ) { if (connection == null) throw new ArgumentNullException("connection"); SQLite3 sqlite3 = connection._sql as SQLite3; if (sqlite3 == null) throw new InvalidOperationException("Connection has no wrapper"); SQLiteConnectionHandle handle = sqlite3._sql; if (handle == null) throw new InvalidOperationException("Connection has an invalid handle."); IntPtr handlePtr = handle; if (handlePtr == IntPtr.Zero) { throw new InvalidOperationException( "Connection has an invalid handle pointer."); } return handle; } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Raises the event. /// /// /// The connection associated with this event. If this parameter is not /// null and the specified connection cannot raise events, then the /// registered event handlers will not be invoked. /// /// /// A that contains the event data. /// internal static void OnChanged( SQLiteConnection connection, ConnectionEventArgs e ) { #if !PLATFORM_COMPACTFRAMEWORK if ((connection != null) && !connection.CanRaiseEvents) return; #endif SQLiteConnectionEventHandler handlers; lock (_syncRoot) { if (_handlers != null) handlers = _handlers.Clone() as SQLiteConnectionEventHandler; else handlers = null; } if (handlers != null) handlers(connection, e); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// This event is raised when events related to the lifecycle of a /// SQLiteConnection object occur. /// public static event SQLiteConnectionEventHandler Changed { add { lock (_syncRoot) { // Remove any copies of this event handler from registered // list. This essentially means that a handler will be // called only once no matter how many times it is added. _handlers -= value; // Add this to the list of event handlers. _handlers += value; } } remove { lock (_syncRoot) { _handlers -= value; } } } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// This property is used to obtain or set the custom connection pool /// implementation to use, if any. Setting this property to null will /// cause the default connection pool implementation to be used. /// public static ISQLiteConnectionPool ConnectionPool { get { return SQLiteConnectionPool.GetConnectionPool(); } set { SQLiteConnectionPool.SetConnectionPool(value); } } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Creates and returns a new managed database connection handle. This /// method is intended to be used by implementations of the /// interface only. In theory, it /// could be used by other classes; however, that usage is not supported. /// /// /// This must be a native database connection handle returned by the /// SQLite core library and it must remain valid and open during the /// entire duration of the calling method. /// /// /// The new managed database connection handle or null if it cannot be /// created. /// public static object CreateHandle( IntPtr nativeHandle ) { SQLiteConnectionHandle result; try { // do nothing. } finally /* NOTE: Thread.Abort() protection. */ { result = (nativeHandle != IntPtr.Zero) ? new SQLiteConnectionHandle(nativeHandle, true) : null; } if (result != null) { SQLiteConnection.OnChanged(null, new ConnectionEventArgs( SQLiteConnectionEventType.NewCriticalHandle, null, null, null, null, result, null, new object[] { typeof(SQLiteConnection), nativeHandle })); } return result; } /////////////////////////////////////////////////////////////////////////////////////////////// #region Backup API Members /// /// Backs up the database, using the specified database connection as the /// destination. /// /// The destination database connection. /// The destination database name. /// The source database name. /// /// The number of pages to copy at a time -OR- a negative value to copy all /// pages. When a negative value is used, the /// may never be invoked. /// /// /// The method to invoke between each step of the backup process. This /// parameter may be null (i.e. no callbacks will be performed). If the /// callback returns false -OR- throws an exception, the backup is canceled. /// /// /// The number of milliseconds to sleep after encountering a locking error /// during the backup process. A value less than zero means that no sleep /// should be performed. /// public void BackupDatabase( SQLiteConnection destination, string destinationName, string sourceName, int pages, SQLiteBackupCallback callback, int retryMilliseconds ) { CheckDisposed(); if (_connectionState != ConnectionState.Open) throw new InvalidOperationException( "Source database is not open."); if (destination == null) throw new ArgumentNullException("destination"); if (destination._connectionState != ConnectionState.Open) throw new ArgumentException( "Destination database is not open.", "destination"); if (destinationName == null) throw new ArgumentNullException("destinationName"); if (sourceName == null) throw new ArgumentNullException("sourceName"); SQLiteBase sqliteBase = _sql; if (sqliteBase == null) throw new InvalidOperationException( "Connection object has an invalid handle."); SQLiteBackup backup = null; try { backup = sqliteBase.InitializeBackup( destination, destinationName, sourceName); /* throw */ bool retry = false; while (sqliteBase.StepBackup(backup, pages, ref retry)) /* throw */ { // // NOTE: If a callback was supplied by our caller, call it. // If it returns false, halt the backup process. // if ((callback != null) && !callback(this, sourceName, destination, destinationName, pages, sqliteBase.RemainingBackup(backup), sqliteBase.PageCountBackup(backup), retry)) { break; } // // NOTE: If we need to retry the previous operation, wait for // the number of milliseconds specified by our caller // unless the caller used a negative number, in that case // skip sleeping at all because we do not want to block // this thread forever. // if (retry && (retryMilliseconds >= 0)) Thread.Sleep(retryMilliseconds); // // NOTE: There is no point in calling the native API to copy // zero pages as it does nothing; therefore, stop now. // if (pages == 0) break; } } catch (Exception e) { if (HelperMethods.LogBackup(_flags)) { SQLiteLog.LogMessage(HelperMethods.StringFormat( CultureInfo.CurrentCulture, "Caught exception while backing up database: {0}", e)); } throw; } finally { if (backup != null) sqliteBase.FinishBackup(backup); /* throw */ } } #endregion /////////////////////////////////////////////////////////////////////////////////////////////// #region Per-Connection Settings /// /// Clears the per-connection cached settings. /// /// /// The total number of per-connection settings cleared. /// public int ClearCachedSettings() { CheckDisposed(); int result = -1; /* NO SETTINGS */ if (_cachedSettings != null) { result = _cachedSettings.Count; _cachedSettings.Clear(); } return result; } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Queries and returns the value of the specified setting, using the /// cached setting names and values for this connection, when available. /// /// /// The name of the setting. /// /// /// The value to be returned if the setting has not been set explicitly /// or cannot be determined. /// /// /// The value of the cached setting is stored here if found; otherwise, /// the value of is stored here. /// /// /// Non-zero if the cached setting was found; otherwise, zero. /// internal bool TryGetCachedSetting( string name, /* in */ object @default, /* in */ out object value /* out */ ) { if ((name == null) || (_cachedSettings == null)) { value = @default; return false; } return _cachedSettings.TryGetValue(name, out value); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Adds or sets the cached setting specified by /// to the value specified by . /// /// /// The name of the cached setting to add or replace. /// /// /// The new value of the cached setting. /// internal void SetCachedSetting( string name, /* in */ object value /* in */ ) { if ((name == null) || (_cachedSettings == null)) return; _cachedSettings[name] = value; } #endregion /////////////////////////////////////////////////////////////////////////////////////////////// #region Per-Connection Type Mappings /// /// Clears the per-connection type mappings. /// /// /// The total number of per-connection type mappings cleared. /// public int ClearTypeMappings() { CheckDisposed(); int result = -1; /* NO MAPPINGS */ if (_typeNames != null) result = _typeNames.Clear(); return result; } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Returns the per-connection type mappings. /// /// /// The per-connection type mappings -OR- null if they are unavailable. /// public Dictionary GetTypeMappings() { CheckDisposed(); Dictionary result = null; if (_typeNames != null) { result = new Dictionary(_typeNames.Count, _typeNames.Comparer); foreach (KeyValuePair pair in _typeNames) { SQLiteDbTypeMapping mapping = pair.Value; object typeName = null; /* System.String */ object dataType = null; /* System.Data.DbType */ object primary = null; /* System.Boolean */ if (mapping != null) { typeName = mapping.typeName; dataType = mapping.dataType; primary = mapping.primary; } result.Add(pair.Key, new object[] { typeName, dataType, primary }); } } return result; } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Adds a per-connection type mapping, possibly replacing one or more /// that already exist. /// /// /// The case-insensitive database type name (e.g. "MYDATE"). The value /// of this parameter cannot be null. Using an empty string value (or /// a string value consisting entirely of whitespace) for this parameter /// is not recommended. /// /// /// The value that should be associated with the /// specified type name. /// /// /// Non-zero if this mapping should be considered to be the primary one /// for the specified . /// /// /// A negative value if nothing was done. Zero if no per-connection type /// mappings were replaced (i.e. it was a pure add operation). More than /// zero if some per-connection type mappings were replaced. /// public int AddTypeMapping( string typeName, DbType dataType, bool primary ) { CheckDisposed(); if (typeName == null) throw new ArgumentNullException("typeName"); int result = -1; /* NO MAPPINGS */ if (_typeNames != null) { result = 0; if (primary && _typeNames.ContainsKey(dataType)) result += _typeNames.Remove(dataType) ? 1 : 0; if (_typeNames.ContainsKey(typeName)) result += _typeNames.Remove(typeName) ? 1 : 0; _typeNames.Add(new SQLiteDbTypeMapping(typeName, dataType, primary)); } return result; } #endregion /////////////////////////////////////////////////////////////////////////////////////////////// #region Per-Connection Type Callbacks /// /// Clears the per-connection type callbacks. /// /// /// The total number of per-connection type callbacks cleared. /// public int ClearTypeCallbacks() { CheckDisposed(); int result = -1; /* NO CALLBACKS */ if (_typeCallbacks != null) { result = _typeCallbacks.Count; _typeCallbacks.Clear(); } return result; } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Attempts to get the per-connection type callbacks for the specified /// database type name. /// /// /// The database type name. /// /// /// Upon success, this parameter will contain the object holding the /// callbacks for the database type name. Upon failure, this parameter /// will be null. /// /// /// Non-zero upon success; otherwise, zero. /// public bool TryGetTypeCallbacks( string typeName, out SQLiteTypeCallbacks callbacks ) { CheckDisposed(); if (typeName == null) throw new ArgumentNullException("typeName"); if (_typeCallbacks == null) { callbacks = null; return false; } return _typeCallbacks.TryGetValue(typeName, out callbacks); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Sets, resets, or clears the per-connection type callbacks for the /// specified database type name. /// /// /// The database type name. /// /// /// The object holding the callbacks for the database type name. If /// this parameter is null, any callbacks for the database type name /// will be removed if they are present. /// /// /// Non-zero if callbacks were set or removed; otherwise, zero. /// public bool SetTypeCallbacks( string typeName, SQLiteTypeCallbacks callbacks ) { CheckDisposed(); if (typeName == null) throw new ArgumentNullException("typeName"); if (_typeCallbacks == null) return false; if (callbacks == null) return _typeCallbacks.Remove(typeName); callbacks.TypeName = typeName; _typeCallbacks[typeName] = callbacks; return true; } #endregion /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Attempts to bind the specified object /// instance to this connection. /// /// /// The object instance containing /// the metadata for the function to be bound. /// /// /// The object instance that implements the /// function to be bound. /// public void BindFunction( SQLiteFunctionAttribute functionAttribute, SQLiteFunction function ) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException( "Database connection not valid for binding functions."); _sql.BindFunction(functionAttribute, function, _flags); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Attempts to bind the specified object /// instance to this connection. /// /// /// The object instance containing /// the metadata for the function to be bound. /// /// /// A object instance that helps implement the /// function to be bound. For scalar functions, this corresponds to the /// type. For aggregate functions, /// this corresponds to the type. For /// collation functions, this corresponds to the /// type. /// /// /// A object instance that helps implement the /// function to be bound. For aggregate functions, this corresponds to the /// type. For other callback types, it /// is not used and must be null. /// public void BindFunction( SQLiteFunctionAttribute functionAttribute, Delegate callback1, Delegate callback2 ) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException( "Database connection not valid for binding functions."); _sql.BindFunction(functionAttribute, new SQLiteDelegateFunction(callback1, callback2), _flags); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Attempts to unbind the specified object /// instance to this connection. /// /// /// The object instance containing /// the metadata for the function to be unbound. /// /// Non-zero if the function was unbound. public bool UnbindFunction( SQLiteFunctionAttribute functionAttribute ) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException( "Database connection not valid for unbinding functions."); return _sql.UnbindFunction(functionAttribute, _flags); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// This method unbinds all registered (known) functions -OR- all previously /// bound user-defined functions from this connection. /// /// /// Non-zero to unbind all registered (known) functions -OR- zero to unbind /// all functions currently bound to the connection. /// /// /// Non-zero if all the specified user-defined functions were unbound. /// public bool UnbindAllFunctions( bool registered ) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException( "Database connection not valid for unbinding functions."); return SQLiteFunction.UnbindAllFunctions(_sql, _flags, registered); } /////////////////////////////////////////////////////////////////////////////////////////////// [Conditional("CHECK_STATE")] internal static void Check(SQLiteConnection connection) { if (connection == null) throw new ArgumentNullException("connection"); connection.CheckDisposed(); if (connection._connectionState != ConnectionState.Open) throw new InvalidOperationException("The connection is not open."); SQLite3 sql = connection._sql as SQLite3; if (sql == null) throw new InvalidOperationException("The connection handle wrapper is null."); SQLiteConnectionHandle handle = sql._sql; if (handle == null) throw new InvalidOperationException("The connection handle is null."); if (handle.IsInvalid) throw new InvalidOperationException("The connection handle is invalid."); if (handle.IsClosed) throw new InvalidOperationException("The connection handle is closed."); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Parses a connection string into component parts using the custom /// connection string parser. An exception may be thrown if the syntax /// of the connection string is incorrect. /// /// /// The connection string to parse. /// /// /// Non-zero to parse the connection string using the algorithm provided /// by the framework itself. This is not applicable when running on the /// .NET Compact Framework. /// /// /// Non-zero if names are allowed without values. /// /// /// The list of key/value pairs corresponding to the parameters specified /// within the connection string. /// internal static SortedList ParseConnectionString( string connectionString, bool parseViaFramework, bool allowNameOnly ) { return ParseConnectionString( null, connectionString, parseViaFramework, allowNameOnly); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Parses a connection string into component parts using the custom /// connection string parser. An exception may be thrown if the syntax /// of the connection string is incorrect. /// /// /// The connection that will be using the parsed connection string. /// /// /// The connection string to parse. /// /// /// Non-zero to parse the connection string using the algorithm provided /// by the framework itself. This is not applicable when running on the /// .NET Compact Framework. /// /// /// Non-zero if names are allowed without values. /// /// /// The list of key/value pairs corresponding to the parameters specified /// within the connection string. /// private static SortedList ParseConnectionString( SQLiteConnection connection, string connectionString, bool parseViaFramework, bool allowNameOnly ) { return parseViaFramework ? ParseConnectionStringViaFramework(connection, connectionString, false) : ParseConnectionString(connection, connectionString, allowNameOnly); } /////////////////////////////////////////////////////////////////////////////////////////////// #if INTEROP_CODEC || INTEROP_INCLUDE_SEE /// /// Attempts to escape the specified connection string property name or /// value in a way that is compatible with the connection string parser. /// /// /// The connection string property name or value to escape. /// /// /// Non-zero if the equals sign is permitted in the string. If this is /// zero and the string contains an equals sign, an exception will be /// thrown. /// /// /// The original string, with all special characters escaped. If the /// original string contains equals signs, they will not be escaped. /// Instead, they will be preserved verbatim. /// private static string EscapeForConnectionString( string value, bool allowEquals ) { if (String.IsNullOrEmpty(value)) return value; if (value.IndexOfAny(SQLiteConvert.SpecialChars) == -1) return value; int length = value.Length; StringBuilder builder = new StringBuilder(length); for (int index = 0; index < length; index++) { char character = value[index]; switch (character) { case SQLiteConvert.QuoteChar: case SQLiteConvert.AltQuoteChar: case SQLiteConvert.PairChar: case SQLiteConvert.EscapeChar: { builder.Append(SQLiteConvert.EscapeChar); builder.Append(character); break; } case SQLiteConvert.ValueChar: { if (allowEquals) { // // HACK: The connection string parser allows // connection string property values // to contain equals signs; however, // they cannot be escaped. // // builder.Append(SQLiteConvert.EscapeChar); builder.Append(character); } else { throw new ArgumentException( "equals sign character is not allowed here"); } break; } default: { builder.Append(character); break; } } } return builder.ToString(); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Builds a connection string from a list of key/value pairs. /// /// /// The list of key/value pairs corresponding to the parameters to be /// specified within the connection string. /// /// /// The connection string. Depending on how the connection string was /// originally parsed, the returned connection string value may not be /// usable in a subsequent call to the method. /// private static string BuildConnectionString( SortedList opts ) { if (opts == null) return null; StringBuilder builder = new StringBuilder(); foreach (KeyValuePair pair in opts) { #if NET_COMPACT_20 builder.Append(HelperMethods.StringFormat( CultureInfo.InvariantCulture, "{0}{1}{2}{3}", EscapeForConnectionString(pair.Key, false), SQLiteConvert.ValueChar, EscapeForConnectionString(pair.Value, true), SQLiteConvert.PairChar)); #else builder.AppendFormat("{0}{1}{2}{3}", EscapeForConnectionString(pair.Key, false), SQLiteConvert.ValueChar, EscapeForConnectionString(pair.Value, true), SQLiteConvert.PairChar); #endif } return builder.ToString(); } #endif /////////////////////////////////////////////////////////////////////////////////////////////// private void SetupSQLiteBase(SortedList opts) { object enumValue; enumValue = TryParseEnum( typeof(SQLiteDateFormats), FindKey(opts, "DateTimeFormat", DefaultDateTimeFormat.ToString()), true); SQLiteDateFormats dateFormat = (enumValue is SQLiteDateFormats) ? (SQLiteDateFormats)enumValue : DefaultDateTimeFormat; enumValue = TryParseEnum( typeof(DateTimeKind), FindKey(opts, "DateTimeKind", DefaultDateTimeKind.ToString()), true); DateTimeKind kind = (enumValue is DateTimeKind) ? (DateTimeKind)enumValue : DefaultDateTimeKind; string dateTimeFormat = FindKey(opts, "DateTimeFormatString", DefaultDateTimeFormatString); // // NOTE: SQLite automatically sets the encoding of the database // to UTF16 if called from sqlite3_open16(). // if (SQLiteConvert.ToBoolean(FindKey(opts, "UseUTF16Encoding", DefaultUseUTF16Encoding.ToString()))) { _sql = new SQLite3_UTF16( dateFormat, kind, dateTimeFormat, IntPtr.Zero, null, false); } else { _sql = new SQLite3( dateFormat, kind, dateTimeFormat, IntPtr.Zero, null, false); } } /////////////////////////////////////////////////////////////////////////////////////////////// #region IDisposable Members /// /// Disposes and finalizes the connection, if applicable. /// public new void Dispose() { if (_noDispose) return; base.Dispose(); } #endregion /////////////////////////////////////////////////////////////////////////////////////////////// #region IDisposable "Pattern" Members private bool disposed; private void CheckDisposed() /* throw */ { #if THROW_ON_DISPOSED if (disposed) throw new ObjectDisposedException(typeof(SQLiteConnection).Name); #endif } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Cleans up resources (native and managed) associated with the current instance. /// /// /// Zero when being disposed via garbage collection; otherwise, non-zero. /// protected override void Dispose(bool disposing) { #if !NET_COMPACT_20 && TRACE_WARNING if (HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.TraceWarning)) { if (_noDispose) { System.Diagnostics.Trace.WriteLine(HelperMethods.StringFormat( CultureInfo.CurrentCulture, "WARNING: Disposing of connection \"{0}\" with the no-dispose flag set.", _connectionString)); } } #endif _disposing = true; try { if (!disposed) { //if (disposing) //{ // //////////////////////////////////// // // dispose managed resources here... // //////////////////////////////////// //} ////////////////////////////////////// // release unmanaged resources here... ////////////////////////////////////// Close(); } } finally { base.Dispose(disposing); // // NOTE: Everything should be fully disposed at this point. // disposed = true; } } #endregion /////////////////////////////////////////////////////////////////////////////////////////////// #if PLATFORM_COMPACTFRAMEWORK /// /// Obsolete /// public override int ConnectionTimeout { get { CheckDisposed(); return DefaultConnectionTimeout; } } #endif /// /// Creates a clone of the connection. All attached databases and user-defined functions are cloned. If the existing connection is open, the cloned connection /// will also be opened. /// /// public object Clone() { CheckDisposed(); return new SQLiteConnection(this); } /// /// Creates a database file. This just creates a zero-byte file which SQLite /// will turn into a database when the file is opened properly. /// /// The file to create static public void CreateFile(string databaseFileName) { FileStream fs = File.Create(databaseFileName); fs.Close(); } /// /// Raises the state change event when the state of the connection changes /// /// The new connection state. If this is different /// from the previous state, the event is /// raised. /// The event data created for the raised event, if /// it was actually raised. internal void OnStateChange( ConnectionState newState, ref StateChangeEventArgs eventArgs ) { ConnectionState oldState = _connectionState; _connectionState = newState; if ((StateChange != null) && (newState != oldState)) { StateChangeEventArgs localEventArgs = new StateChangeEventArgs(oldState, newState); StateChange(this, localEventArgs); eventArgs = localEventArgs; } } /// /// Determines and returns the fallback default isolation level when one cannot be /// obtained from an existing connection instance. /// /// /// The fallback default isolation level for this connection instance -OR- /// if it cannot be determined. /// private static IsolationLevel GetFallbackDefaultIsolationLevel() { return DefaultIsolationLevel; } /// /// Determines and returns the default isolation level for this connection instance. /// /// /// The default isolation level for this connection instance -OR- /// if it cannot be determined. /// internal IsolationLevel GetDefaultIsolationLevel() { return _defaultIsolation; } /// /// OBSOLETE. Creates a new SQLiteTransaction if one isn't already active on the connection. /// /// This parameter is ignored. /// When TRUE, SQLite defers obtaining a write lock until a write operation is requested. /// When FALSE, a writelock is obtained immediately. The default is TRUE, but in a multi-threaded multi-writer /// environment, one may instead choose to lock the database immediately to avoid any possible writer deadlock. /// Returns a SQLiteTransaction object. [Obsolete("Use one of the standard BeginTransaction methods, this one will be removed soon")] public SQLiteTransaction BeginTransaction(IsolationLevel isolationLevel, bool deferredLock) { CheckDisposed(); return (SQLiteTransaction)BeginDbTransaction(deferredLock == false ? ImmediateIsolationLevel : DeferredIsolationLevel); } /// /// OBSOLETE. Creates a new SQLiteTransaction if one isn't already active on the connection. /// /// When TRUE, SQLite defers obtaining a write lock until a write operation is requested. /// When FALSE, a writelock is obtained immediately. The default is false, but in a multi-threaded multi-writer /// environment, one may instead choose to lock the database immediately to avoid any possible writer deadlock. /// Returns a SQLiteTransaction object. [Obsolete("Use one of the standard BeginTransaction methods, this one will be removed soon")] public SQLiteTransaction BeginTransaction(bool deferredLock) { CheckDisposed(); return (SQLiteTransaction)BeginDbTransaction(deferredLock == false ? ImmediateIsolationLevel : DeferredIsolationLevel); } /// /// Creates a new if one isn't already active on the connection. /// /// Supported isolation levels are Serializable, ReadCommitted and Unspecified. /// /// Unspecified will use the default isolation level specified in the connection string. If no isolation level is specified in the /// connection string, Serializable is used. /// Serializable transactions are the default. In this mode, the engine gets an immediate lock on the database, and no other threads /// may begin a transaction. Other threads may read from the database, but not write. /// With a ReadCommitted isolation level, locks are deferred and elevated as needed. It is possible for multiple threads to start /// a transaction in ReadCommitted mode, but if a thread attempts to commit a transaction while another thread /// has a ReadCommitted lock, it may timeout or cause a deadlock on both threads until both threads' CommandTimeout's are reached. /// /// Returns a SQLiteTransaction object. public new SQLiteTransaction BeginTransaction(IsolationLevel isolationLevel) { CheckDisposed(); return (SQLiteTransaction)BeginDbTransaction(isolationLevel); } /// /// Creates a new if one isn't already /// active on the connection. /// /// Returns the new transaction object. public new SQLiteTransaction BeginTransaction() { CheckDisposed(); return (SQLiteTransaction)BeginDbTransaction(_defaultIsolation); } /// /// Forwards to the local function /// /// Supported isolation levels are Unspecified, Serializable, and ReadCommitted /// protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel) { if (_connectionState != ConnectionState.Open) throw new InvalidOperationException(); if (isolationLevel == IsolationLevel.Unspecified) isolationLevel = _defaultIsolation; isolationLevel = GetEffectiveIsolationLevel(isolationLevel); if (isolationLevel != ImmediateIsolationLevel && isolationLevel != DeferredIsolationLevel) throw new ArgumentException("isolationLevel"); SQLiteTransaction transaction; if (HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.AllowNestedTransactions)) { transaction = new SQLiteTransaction2( this, isolationLevel != ImmediateIsolationLevel); } else { transaction = new SQLiteTransaction( this, isolationLevel != ImmediateIsolationLevel); } OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.NewTransaction, null, transaction, null, null, null, null, null)); return transaction; } /// /// This method is not implemented; however, the /// event will still be raised. /// /// public override void ChangeDatabase(string databaseName) { CheckDisposed(); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.ChangeDatabase, null, null, null, null, null, databaseName, null)); throw new NotImplementedException(); // NOTE: For legacy compatibility. } /// /// When the database connection is closed, all commands linked to this connection are automatically reset. /// public override void Close() { CheckDisposed(); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.Closing, null, null, null, null, null, null, null)); if (_sql != null) { #if !PLATFORM_COMPACTFRAMEWORK lock (_enlistmentSyncRoot) /* TRANSACTIONAL */ { SQLiteEnlistment enlistment = _enlistment; _enlistment = null; if (enlistment != null) { // If the connection is enlisted in a transaction scope and the scope is still active, // we cannot truly shut down this connection until the scope has completed. Therefore make a // hidden connection temporarily to hold open the connection until the scope has completed. SQLiteConnection cnn = new SQLiteConnection(); #if DEBUG cnn._debugString = HelperMethods.StringFormat( CultureInfo.InvariantCulture, "closeThreadId = {0}, {1}{2}{2}{3}", HelperMethods.GetThreadId(), _sql, Environment.NewLine, _debugString); #endif cnn._sql = _sql; cnn._transactionLevel = _transactionLevel; cnn._transactionSequence = _transactionSequence; cnn._enlistment = enlistment; cnn._connectionState = _connectionState; cnn._version = _version; SQLiteTransaction transaction = enlistment._transaction; if (transaction != null) transaction._cnn = cnn; enlistment._disposeConnection = true; _sql = null; } } #endif if (_sql != null) { _sql.Close(_disposing); _sql = null; } _transactionLevel = 0; _transactionSequence = 0; } StateChangeEventArgs eventArgs = null; OnStateChange(ConnectionState.Closed, ref eventArgs); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.Closed, eventArgs, null, null, null, null, null, null)); } /// /// Returns the number of pool entries for the file name associated with this connection. /// public int PoolCount { get { if (_sql == null) return 0; return _sql.CountPool(); } } /// /// Clears the connection pool associated with the connection. Any other active connections using the same database file /// will be discarded instead of returned to the pool when they are closed. /// /// public static void ClearPool(SQLiteConnection connection) { if (connection._sql == null) return; connection._sql.ClearPool(); } /// /// Clears all connection pools. Any active connections will be discarded instead of sent to the pool when they are closed. /// public static void ClearAllPools() { SQLiteConnectionPool.ClearAllPools(); } /// /// The connection string containing the parameters for the connection /// /// /// For the complete list of supported connection string properties, /// please see . /// #if !PLATFORM_COMPACTFRAMEWORK [RefreshProperties(RefreshProperties.All), DefaultValue("")] [Editor("SQLite.Designer.SQLiteConnectionStringEditor, SQLite.Designer, Version=" + SQLite3.DesignerVersion + ", Culture=neutral, PublicKeyToken=db937bc2d44ff139", "System.Drawing.Design.UITypeEditor, System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")] #endif public override string ConnectionString { get { CheckDisposed(); return _connectionString; } set { CheckDisposed(); if (value == null) throw new ArgumentNullException(); else if (_connectionState != ConnectionState.Closed) throw new InvalidOperationException(); _connectionString = value; } } /// /// Create a new and associate it with this connection. /// /// Returns a new command object already assigned to this connection. public new SQLiteCommand CreateCommand() { CheckDisposed(); return new SQLiteCommand(this); } /// /// Forwards to the local function. /// /// protected override DbCommand CreateDbCommand() { return CreateCommand(); } #if INTEROP_SESSION_EXTENSION /// /// Attempts to create a new object instance /// using this connection and the specified database name. /// /// /// The name of the database for the newly created session. /// /// /// The newly created session -OR- null if it cannot be created. /// public ISQLiteSession CreateSession( string databaseName ) { CheckDisposed(); return new SQLiteSession(GetNativeHandle(this), _flags, databaseName); } /// /// Attempts to create a new object instance /// using this connection and the specified raw data. /// /// /// The raw data that contains a change set (or patch set). /// /// /// The newly created change set -OR- null if it cannot be created. /// public ISQLiteChangeSet CreateChangeSet( byte[] rawData ) { CheckDisposed(); return new SQLiteMemoryChangeSet(rawData, GetNativeHandle(this), _flags); } /// /// Attempts to create a new object instance /// using this connection and the specified raw data. /// /// /// The raw data that contains a change set (or patch set). /// /// /// The flags used to create the change set iterator. /// /// /// The newly created change set -OR- null if it cannot be created. /// public ISQLiteChangeSet CreateChangeSet( byte[] rawData, SQLiteChangeSetStartFlags flags ) { CheckDisposed(); return new SQLiteMemoryChangeSet(rawData, GetNativeHandle(this), _flags, flags); } /// /// Attempts to create a new object instance /// using this connection and the specified stream. /// /// /// The stream where the raw data that contains a change set (or patch set) /// may be read. /// /// /// The stream where the raw data that contains a change set (or patch set) /// may be written. /// /// /// The newly created change set -OR- null if it cannot be created. /// public ISQLiteChangeSet CreateChangeSet( Stream inputStream, Stream outputStream ) { CheckDisposed(); return new SQLiteStreamChangeSet( inputStream, outputStream, GetNativeHandle(this), _flags); } /// /// Attempts to create a new object instance /// using this connection and the specified stream. /// /// /// The stream where the raw data that contains a change set (or patch set) /// may be read. /// /// /// The stream where the raw data that contains a change set (or patch set) /// may be written. /// /// /// The flags used to create the change set iterator. /// /// /// The newly created change set -OR- null if it cannot be created. /// public ISQLiteChangeSet CreateChangeSet( Stream inputStream, Stream outputStream, SQLiteChangeSetStartFlags flags ) { CheckDisposed(); return new SQLiteStreamChangeSet( inputStream, outputStream, GetNativeHandle(this), _flags, flags); } /// /// Attempts to create a new object /// instance using this connection. /// /// /// The newly created change group -OR- null if it cannot be created. /// public ISQLiteChangeGroup CreateChangeGroup() { CheckDisposed(); return new SQLiteChangeGroup(_flags); } #endif /// /// Returns the data source file name without extension or path. /// #if !PLATFORM_COMPACTFRAMEWORK [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public override string DataSource { get { CheckDisposed(); return _dataSource; } } /// /// Returns the fully qualified path and file name for the currently open /// database, if any. /// #if !PLATFORM_COMPACTFRAMEWORK [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public string FileName { get { CheckDisposed(); if (_sql == null) throw new InvalidOperationException( "Database connection not valid for getting file name."); return _sql.GetFileName(GetDefaultCatalogName()); } } /// /// Returns the string "main". /// #if !PLATFORM_COMPACTFRAMEWORK [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public override string Database { get { CheckDisposed(); return GetDefaultCatalogName(); } } internal static string MapUriPath(string path) { if (path.StartsWith ("file://", StringComparison.OrdinalIgnoreCase)) return path.Substring (7); else if (path.StartsWith ("file:", StringComparison.OrdinalIgnoreCase)) return path.Substring (5); else if (path.StartsWith ("/", StringComparison.OrdinalIgnoreCase)) return path; else throw new InvalidOperationException ("Invalid connection string: invalid URI"); } /// /// Determines if the legacy connection string parser should be used. /// /// /// The connection that will be using the parsed connection string. /// /// /// Non-zero if the legacy connection string parser should be used. /// private static bool ShouldUseLegacyConnectionStringParser( SQLiteConnection connection ) { string name = "No_SQLiteConnectionNewParser"; object value; if ((connection != null) && connection.TryGetCachedSetting(name, null, out value)) { return (value != null); } if ((connection == null) && TryGetLastCachedSetting(name, null, out value)) { return (value != null); } value = UnsafeNativeMethods.GetSettingValue(name, null); if (connection != null) connection.SetCachedSetting(name, value); else SetLastCachedSetting(name, value); return (value != null); } /// /// Parses a connection string into component parts using the custom /// connection string parser. An exception may be thrown if the syntax /// of the connection string is incorrect. /// /// /// The connection string to parse. /// /// /// Non-zero if names are allowed without values. /// /// /// The list of key/value pairs corresponding to the parameters specified /// within the connection string. /// private static SortedList ParseConnectionString( string connectionString, bool allowNameOnly ) { return ParseConnectionString(null, connectionString, allowNameOnly); } /// /// Parses a connection string into component parts using the custom /// connection string parser. An exception may be thrown if the syntax /// of the connection string is incorrect. /// /// /// The connection that will be using the parsed connection string. /// /// /// The connection string to parse. /// /// /// Non-zero if names are allowed without values. /// /// /// The list of key/value pairs corresponding to the parameters specified /// within the connection string. /// private static SortedList ParseConnectionString( SQLiteConnection connection, string connectionString, bool allowNameOnly ) { string s = connectionString; int n; SortedList ls = new SortedList(StringComparer.OrdinalIgnoreCase); // First split into semi-colon delimited values. string error = null; string[] arParts; if (ShouldUseLegacyConnectionStringParser(connection)) arParts = SQLiteConvert.Split(s, SQLiteConvert.PairChar); else arParts = SQLiteConvert.NewSplit(s, SQLiteConvert.PairChar, true, ref error); if (arParts == null) { throw new ArgumentException(HelperMethods.StringFormat( CultureInfo.CurrentCulture, "Invalid ConnectionString format, cannot parse: {0}", (error != null) ? error : "could not split connection string into properties")); } int x = (arParts != null) ? arParts.Length : 0; // For each semi-colon piece, split into key and value pairs by the presence of the = sign for (n = 0; n < x; n++) { if (arParts[n] == null) continue; arParts[n] = arParts[n].Trim(); if (arParts[n].Length == 0) continue; int indexOf = arParts[n].IndexOf(SQLiteConvert.ValueChar); if (indexOf != -1) ls.Add(UnwrapString(arParts[n].Substring(0, indexOf).Trim()), UnwrapString(arParts[n].Substring(indexOf + 1).Trim())); else if (allowNameOnly) ls.Add(UnwrapString(arParts[n].Trim()), String.Empty); else throw new ArgumentException(HelperMethods.StringFormat(CultureInfo.CurrentCulture, "Invalid ConnectionString format for part \"{0}\", no equal sign found", arParts[n])); } return ls; } /// /// Parses a connection string using the built-in (i.e. framework provided) /// connection string parser class and returns the key/value pairs. An /// exception may be thrown if the connection string is invalid or cannot be /// parsed. When compiled for the .NET Compact Framework, the custom /// connection string parser is always used instead because the framework /// provided one is unavailable there. /// /// /// The connection that will be using the parsed connection string. /// /// /// The connection string to parse. /// /// /// Non-zero to throw an exception if any connection string values are not of /// the type. This is not applicable when running on /// the .NET Compact Framework. /// /// The list of key/value pairs. private static SortedList ParseConnectionStringViaFramework( SQLiteConnection connection, string connectionString, bool strict ) { #if !PLATFORM_COMPACTFRAMEWORK DbConnectionStringBuilder connectionStringBuilder = new DbConnectionStringBuilder(); connectionStringBuilder.ConnectionString = connectionString; /* throw */ SortedList result = new SortedList(StringComparer.OrdinalIgnoreCase); foreach (string keyName in connectionStringBuilder.Keys) { object value = connectionStringBuilder[keyName]; string keyValue = null; if (value is string) { keyValue = (string)value; } else if (strict) { throw new ArgumentException( "connection property value is not a string", keyName); } else if (value != null) { keyValue = value.ToString(); } result.Add(keyName, keyValue); } return result; #else // // NOTE: On the .NET Compact Framework, always use our custom connection // string parser as the built-in (i.e. framework provided) one is // unavailable. // return ParseConnectionString(connection, connectionString, false); #endif } #if !PLATFORM_COMPACTFRAMEWORK /// /// Manual distributed transaction enlistment support /// /// The distributed transaction to enlist in public override void EnlistTransaction(System.Transactions.Transaction transaction) { CheckDisposed(); bool waitForEnlistmentReset; int waitTimeout; lock (_enlistmentSyncRoot) /* TRANSACTIONAL */ { waitForEnlistmentReset = HelperMethods.HasFlags( _flags, SQLiteConnectionFlags.WaitForEnlistmentReset); waitTimeout = _waitTimeout; } if (waitForEnlistmentReset) /* IGNORED */ WaitForEnlistmentReset(waitTimeout, null); lock (_enlistmentSyncRoot) /* TRANSACTIONAL */ { if (_enlistment != null && transaction == _enlistment._scope) return; else if (_enlistment != null) throw new ArgumentException("Already enlisted in a transaction"); if (_transactionLevel > 0 && transaction != null) throw new ArgumentException("Unable to enlist in transaction, a local transaction already exists"); else if (transaction == null) throw new ArgumentNullException("Unable to enlist in transaction, it is null"); bool strictEnlistment = HelperMethods.HasFlags( _flags, SQLiteConnectionFlags.StrictEnlistment); _enlistment = new SQLiteEnlistment(this, transaction, GetFallbackDefaultIsolationLevel(), strictEnlistment, strictEnlistment); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.EnlistTransaction, null, null, null, null, null, null, new object[] { _enlistment })); } } #endif #if !PLATFORM_COMPACTFRAMEWORK /// /// EXPERIMENTAL -- /// Waits for the enlistment associated with this connection to be reset. /// This method always throws when /// running on the .NET Compact Framework. /// /// /// The approximate maximum number of milliseconds to wait before timing /// out the wait operation. /// /// /// The return value to use if the connection has been disposed; if this /// value is null, will be raised /// if the connection has been disposed. /// /// /// Non-zero if the enlistment assciated with this connection was reset; /// otherwise, zero. It should be noted that this method returning a /// non-zero value does not necessarily guarantee that the connection /// can enlist in a new transaction (i.e. due to potentical race with /// other threads); therefore, callers should generally use try/catch /// when calling the method. /// #else /// /// EXPERIMENTAL -- /// Waits for the enlistment associated with this connection to be reset. /// This method always throws when /// running on the .NET Compact Framework. /// /// /// The approximate maximum number of milliseconds to wait before timing /// out the wait operation. /// /// /// The return value to use if the connection has been disposed; if this /// value is null, will be raised /// if the connection has been disposed. /// /// /// Non-zero if the enlistment assciated with this connection was reset; /// otherwise, zero. It should be noted that this method returning a /// non-zero value does not necessarily guarantee that the connection /// can enlist in a new transaction (i.e. due to potentical race with /// other threads); therefore, callers should generally use try/catch /// when calling the EnlistTransaction method. /// #endif public bool WaitForEnlistmentReset( int timeoutMilliseconds, bool? returnOnDisposed ) { if (returnOnDisposed == null) CheckDisposed(); else if(disposed) return (bool)returnOnDisposed; #if !PLATFORM_COMPACTFRAMEWORK if (timeoutMilliseconds < 0) throw new ArgumentException("timeout cannot be negative"); const int defaultMilliseconds = 100; int sleepMilliseconds; if (timeoutMilliseconds == 0) { sleepMilliseconds = 0; } else { sleepMilliseconds = Math.Min( timeoutMilliseconds / 10, defaultMilliseconds); if (sleepMilliseconds == 0) sleepMilliseconds = defaultMilliseconds; } DateTime start = DateTime.UtcNow; while (true) { // // NOTE: Attempt to acquire the necessary lock without blocking. // This method will treat a failure to obtain the lock the // same as the enlistment not being reset yet. Both will // advance toward the timeout. // bool locked = Monitor.TryEnter(_enlistmentSyncRoot); try { if (locked) { // // NOTE: Is there still an enlistment? If not, we are // done. There is a potential race condition in // the caller if another thread is able to setup // a new enlistment at any point prior to our // caller fully dealing with the result of this // method. However, that should generally never // happen because this class is not intended to // be used by multiple concurrent threads, with // the notable exception of an active enlistment // being asynchronously committed or rolled back // by the .NET Framework. // if (_enlistment == null) return true; } } finally { if (locked) { Monitor.Exit(_enlistmentSyncRoot); locked = false; } } // // NOTE: A timeout value of zero is special. It means never // sleep. // if (sleepMilliseconds == 0) return false; // // NOTE: How much time has elapsed since we first starting // waiting? // DateTime now = DateTime.UtcNow; TimeSpan elapsed = now.Subtract(start); // // NOTE: Are we done wait? // double totalMilliseconds = elapsed.TotalMilliseconds; if ((totalMilliseconds < 0) || /* Time went backward? */ (totalMilliseconds >= (double)timeoutMilliseconds)) { return false; } // // NOTE: Sleep for a bit and then try again. // Thread.Sleep(sleepMilliseconds); } #else throw new NotImplementedException(); #endif } /// /// Looks for a key in the array of key/values of the parameter string. If not found, return the specified default value /// /// The list to look in /// The key to find /// The default value to return if the key is not found /// The value corresponding to the specified key, or the default value if not found. static internal string FindKey(SortedList items, string key, string defValue) { string ret; if (String.IsNullOrEmpty(key)) return defValue; if (items.TryGetValue(key, out ret)) return ret; if (items.TryGetValue(key.Replace(" ", String.Empty), out ret)) return ret; if (items.TryGetValue(key.Replace(" ", "_"), out ret)) return ret; return defValue; } /// /// Attempts to convert the string value to an enumerated value of the specified type. /// /// The enumerated type to convert the string value to. /// The string value to be converted. /// Non-zero to make the conversion case-insensitive. /// The enumerated value upon success or null upon error. internal static object TryParseEnum( Type type, string value, bool ignoreCase ) { if (!String.IsNullOrEmpty(value)) { try { return Enum.Parse(type, value, ignoreCase); } catch { // do nothing. } } return null; } /// /// Attempts to convert an input string into a byte value. /// /// /// The string value to be converted. /// /// /// The number styles to use for the conversion. /// /// /// Upon sucess, this will contain the parsed byte value. /// Upon failure, the value of this parameter is undefined. /// /// /// Non-zero upon success; zero on failure. /// private static bool TryParseByte( string value, NumberStyles style, out byte result ) { #if !PLATFORM_COMPACTFRAMEWORK return byte.TryParse(value, style, null, out result); #else try { result = byte.Parse(value, style); return true; } catch { result = 0; return false; } #endif } /// /// Change a limit value for the database. /// /// /// The database limit to change. /// /// /// The new value for the specified limit. /// /// /// The old value for the specified limit -OR- negative one if an error /// occurs. /// public int SetLimitOption( SQLiteLimitOpsEnum option, int value ) { CheckDisposed(); if (_sql == null) { throw new InvalidOperationException( "Database connection not valid for changing a limit option."); } return _sql.SetLimitOption(option, value); } /// /// Change a configuration option value for the database. /// /// /// The database configuration option to change. /// /// /// The new value for the specified configuration option. /// public void SetConfigurationOption( SQLiteConfigDbOpsEnum option, object value ) { CheckDisposed(); if (_sql == null) { throw new InvalidOperationException( "Database connection not valid for changing a configuration option."); } if ((option == SQLiteConfigDbOpsEnum.SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION) && HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.NoLoadExtension)) { throw new SQLiteException("Loading extensions is disabled for this database connection."); } SQLiteErrorCode rc = _sql.SetConfigurationOption(option, value); if (rc != SQLiteErrorCode.Ok) throw new SQLiteException(rc, null); } /// /// Enables or disables extension loading. /// /// /// True to enable loading of extensions, false to disable. /// public void EnableExtensions( bool enable ) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException(HelperMethods.StringFormat( CultureInfo.CurrentCulture, "Database connection not valid for {0} extensions.", enable ? "enabling" : "disabling")); if (HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.NoLoadExtension)) throw new SQLiteException("Loading extensions is disabled for this database connection."); _sql.SetLoadExtension(enable); } /// /// Loads a SQLite extension library from the named dynamic link library file. /// /// /// The name of the dynamic link library file containing the extension. /// public void LoadExtension( string fileName ) { CheckDisposed(); LoadExtension(fileName, null); } /// /// Loads a SQLite extension library from the named dynamic link library file. /// /// /// The name of the dynamic link library file containing the extension. /// /// /// The name of the exported function used to initialize the extension. /// If null, the default "sqlite3_extension_init" will be used. /// public void LoadExtension( string fileName, string procName ) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException( "Database connection not valid for loading extensions."); if (HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.NoLoadExtension)) throw new SQLiteException("Loading extensions is disabled for this database connection."); _sql.LoadExtension(fileName, procName); } #if INTEROP_VIRTUAL_TABLE /// /// Creates a disposable module containing the implementation of a virtual /// table. /// /// /// The module object to be used when creating the disposable module. /// public void CreateModule( SQLiteModule module ) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException( "Database connection not valid for creating modules."); if (HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.NoCreateModule)) throw new SQLiteException("Creating modules is disabled for this database connection."); _sql.CreateModule(module, _flags); } #endif /// /// Parses a string containing a sequence of zero or more hexadecimal /// encoded byte values and returns the resulting byte array. The /// "0x" prefix is not allowed on the input string. /// /// /// The input string containing zero or more hexadecimal encoded byte /// values. /// /// /// A byte array containing the parsed byte values or null if an error /// was encountered. /// internal static byte[] FromHexString( string text ) { string error = null; return FromHexString(text, ref error); } /// /// Creates and returns a string containing the hexadecimal encoded byte /// values from the input array. /// /// /// The input array of bytes. /// /// /// The resulting string or null upon failure. /// internal static string ToHexString( byte[] array ) { if (array == null) return null; StringBuilder result = new StringBuilder(); int length = array.Length; for (int index = 0; index < length; index++) #if NET_COMPACT_20 result.Append(HelperMethods.StringFormat( CultureInfo.InvariantCulture, "{0:x2}", array[index])); #else result.AppendFormat("{0:x2}", array[index]); #endif return result.ToString(); } /// /// Parses a string containing a sequence of zero or more hexadecimal /// encoded byte values and returns the resulting byte array. The /// "0x" prefix is not allowed on the input string. /// /// /// The input string containing zero or more hexadecimal encoded byte /// values. /// /// /// Upon failure, this will contain an appropriate error message. /// /// /// A byte array containing the parsed byte values or null if an error /// was encountered. /// private static byte[] FromHexString( string text, ref string error ) { if (text == null) { error = "string is null"; return null; } if (text.Length % 2 != 0) { error = "string contains an odd number of characters"; return null; } byte[] result = new byte[text.Length / 2]; for (int index = 0; index < text.Length; index += 2) { string value = text.Substring(index, 2); if (!TryParseByte(value, NumberStyles.HexNumber, out result[index / 2])) { error = HelperMethods.StringFormat( CultureInfo.CurrentCulture, "string contains \"{0}\", which cannot be converted to a byte value", value); return null; } } return result; } /// /// This method figures out what the default connection pool setting should /// be based on the connection flags. When present, the "Pooling" connection /// string property value always overrides the value returned by this method. /// /// /// Non-zero if the connection pool should be enabled by default; otherwise, /// zero. /// private bool GetDefaultPooling() { bool result = DefaultPooling; if (result) /* NOTE: True branch not reached in the default build. */ { if (HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.NoConnectionPool)) result = false; if (HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.UseConnectionPool)) result = true; } else { if (HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.UseConnectionPool)) result = true; if (HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.NoConnectionPool)) result = false; } return result; } /// /// Determines the transaction isolation level that should be used by /// the caller, primarily based upon the one specified by the caller. /// If mapping of transaction isolation levels is enabled, the returned /// transaction isolation level may be significantly different than the /// originally specified one. /// /// /// The originally specified transaction isolation level. /// /// /// The transaction isolation level that should be used. /// private IsolationLevel GetEffectiveIsolationLevel( IsolationLevel isolationLevel ) { if (!HelperMethods.HasFlags( _flags, SQLiteConnectionFlags.MapIsolationLevels)) { return isolationLevel; } switch (isolationLevel) { case IsolationLevel.Unspecified: case IsolationLevel.Chaos: case IsolationLevel.ReadUncommitted: case IsolationLevel.ReadCommitted: return DeferredIsolationLevel; case IsolationLevel.RepeatableRead: case IsolationLevel.Serializable: case IsolationLevel.Snapshot: return ImmediateIsolationLevel; default: return GetFallbackDefaultIsolationLevel(); } } /// /// Opens the connection using the parameters found in the . /// public override void Open() { CheckDisposed(); _lastConnectionInOpen = this; /* THREAD-SAFE: per-thread datum. */ OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.Opening, null, null, null, null, null, null, null)); if (_connectionState != ConnectionState.Closed) throw new InvalidOperationException(); Close(); SortedList opts = ParseConnectionString( this, _connectionString, _parseViaFramework, false); object enumValue = TryParseEnum(typeof(SQLiteConnectionFlags), FindKey(opts, "Flags", null), true); // // BUGFIX: Always preserve the pre-existing instance flags. This is OK // because when the connection object is initially created, they // are "None"; therefore, OR-ing the connection string property // flags with the instance flags would produce exactly the same // result. If the "Flags" connection string property is absent, // OR-ing the the instance flags with the static DefaultFlags is // done instead. This is OK for the same reason as before: when // the connection object is initially created, they are "None" // by default. If they are different now, they must have been // manually set by the application. // bool noDefaultFlags = SQLiteConvert.ToBoolean(FindKey(opts, "NoDefaultFlags", DefaultNoDefaultFlags.ToString())); if (enumValue is SQLiteConnectionFlags) _flags |= (SQLiteConnectionFlags)enumValue; else if (!noDefaultFlags) _flags |= DefaultFlags; bool noSharedFlags = SQLiteConvert.ToBoolean(FindKey(opts, "NoSharedFlags", DefaultNoSharedFlags.ToString())); if (!noSharedFlags) { lock (_syncRoot) { _flags |= _sharedFlags; } } #if INTEROP_CODEC || INTEROP_INCLUDE_SEE bool hidePassword = HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.HidePassword); #endif SortedList eventArgOpts = opts; string eventArgConnectionString = _connectionString; #if INTEROP_CODEC || INTEROP_INCLUDE_SEE if (hidePassword) { eventArgOpts = new SortedList( StringComparer.OrdinalIgnoreCase); foreach (KeyValuePair pair in opts) { if (String.Equals( pair.Key, "Password", StringComparison.OrdinalIgnoreCase)) { continue; } if (String.Equals( pair.Key, "HexPassword", StringComparison.OrdinalIgnoreCase)) { continue; } eventArgOpts.Add(pair.Key, pair.Value); } eventArgConnectionString = BuildConnectionString( eventArgOpts); } #endif OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.ConnectionString, null, null, null, null, null, eventArgConnectionString, new object[] { eventArgOpts })); enumValue = TryParseEnum(typeof(DbType), FindKey(opts, "DefaultDbType", null), true); _defaultDbType = (enumValue is DbType) ? (DbType)enumValue : (DbType?)null; // // NOTE: Nullable values types are not supported by the .NET Framework // ADO.NET support components that work with the connection string // builder; therefore, translate the "invalid value" used by the // SQLiteConnectionStringBuilder.DefaultDbType property to null // here. // if ((_defaultDbType != null) && ((DbType)_defaultDbType == BadDbType)) _defaultDbType = null; _defaultTypeName = FindKey(opts, "DefaultTypeName", null); _vfsName = FindKey(opts, "VfsName", DefaultVfsName); #if !NET_COMPACT_20 && TRACE_WARNING bool uri = false; #endif bool fullUri = false; string fileName; if (Convert.ToInt32(FindKey(opts, "Version", SQLiteConvert.ToString(DefaultVersion)), CultureInfo.InvariantCulture) != DefaultVersion) throw new NotSupportedException(HelperMethods.StringFormat(CultureInfo.CurrentCulture, "Only SQLite Version {0} is supported at this time", DefaultVersion)); #if INTEROP_INCLUDE_ZIPVFS bool useZipVfs = false; string zipVfsVersion = FindKey(opts, "ZipVfsVersion", DefaultZipVfsVersion); if (zipVfsVersion != null) { if (String.Compare(zipVfsVersion, ZipVfs_Automatic) == 0) { useZipVfs = true; } else if (String.Compare(zipVfsVersion, ZipVfs_V2) == 0) { UnsafeNativeMethods.zipvfsInit_v2(); useZipVfs = true; } else if (String.Compare(zipVfsVersion, ZipVfs_V3) == 0) { UnsafeNativeMethods.zipvfsInit_v3(0); useZipVfs = true; } else { throw new NotSupportedException(HelperMethods.StringFormat( CultureInfo.CurrentCulture, "Only ZipVFS versions {0}, {1}, and {2} are supported at this time", ZipVfs_Automatic, ZipVfs_V2, ZipVfs_V3)); } } #endif fileName = FindKey(opts, "Data Source", DefaultDataSource); if (String.IsNullOrEmpty(fileName)) { fileName = FindKey(opts, "Uri", DefaultUri); if (String.IsNullOrEmpty(fileName)) { fileName = FindKey(opts, "FullUri", DefaultFullUri); if (String.IsNullOrEmpty(fileName)) throw new ArgumentException(HelperMethods.StringFormat(CultureInfo.CurrentCulture, "Data Source cannot be empty. Use {0} to open an in-memory database", MemoryFileName)); else fullUri = true; } else { fileName = MapUriPath(fileName); #if !NET_COMPACT_20 && TRACE_WARNING uri = true; #endif } } bool isMemory = (String.Compare(fileName, MemoryFileName, StringComparison.OrdinalIgnoreCase) == 0); #if !NET_COMPACT_20 && TRACE_WARNING if (HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.TraceWarning)) { if (!uri && !fullUri && !isMemory && !String.IsNullOrEmpty(fileName) && fileName.StartsWith("\\", StringComparison.OrdinalIgnoreCase) && !fileName.StartsWith("\\\\", StringComparison.OrdinalIgnoreCase)) { System.Diagnostics.Trace.WriteLine(HelperMethods.StringFormat( CultureInfo.CurrentCulture, "WARNING: Detected a possibly malformed UNC database file name \"{0}\" that " + "may have originally started with two backslashes; however, four leading " + "backslashes may be required, e.g.: \"Data Source=\\\\\\{0};\"", fileName)); } } #endif if (!fullUri) { if (isMemory) fileName = MemoryFileName; else { #if PLATFORM_COMPACTFRAMEWORK if (fileName.StartsWith("./") || fileName.StartsWith(".\\")) fileName = Path.GetDirectoryName(Assembly.GetCallingAssembly().GetName().CodeBase) + fileName.Substring(1); #endif bool toFullPath = SQLiteConvert.ToBoolean(FindKey(opts, "ToFullPath", DefaultToFullPath.ToString())); fileName = ExpandFileName(fileName, toFullPath); } } try { bool usePooling = SQLiteConvert.ToBoolean(FindKey(opts, "Pooling", GetDefaultPooling().ToString())); int maxPoolSize = Convert.ToInt32(FindKey(opts, "Max Pool Size", SQLiteConvert.ToString(DefaultMaxPoolSize)), CultureInfo.InvariantCulture); _defaultTimeout = Convert.ToInt32(FindKey(opts, "Default Timeout", SQLiteConvert.ToString(DefaultConnectionTimeout)), CultureInfo.InvariantCulture); _busyTimeout = Convert.ToInt32(FindKey(opts, "BusyTimeout", SQLiteConvert.ToString(DefaultBusyTimeout)), CultureInfo.InvariantCulture); #if !PLATFORM_COMPACTFRAMEWORK _waitTimeout = Convert.ToInt32(FindKey(opts, "WaitTimeout", SQLiteConvert.ToString(DefaultWaitTimeout)), CultureInfo.InvariantCulture); #endif _prepareRetries = Convert.ToInt32(FindKey(opts, "PrepareRetries", SQLiteConvert.ToString(DefaultPrepareRetries)), CultureInfo.InvariantCulture); _progressOps = Convert.ToInt32(FindKey(opts, "ProgressOps", SQLiteConvert.ToString(DefaultProgressOps)), CultureInfo.InvariantCulture); enumValue = TryParseEnum(typeof(IsolationLevel), FindKey(opts, "Default IsolationLevel", DefaultIsolationLevel.ToString()), true); _defaultIsolation = (enumValue is IsolationLevel) ? (IsolationLevel)enumValue : DefaultIsolationLevel; _defaultIsolation = GetEffectiveIsolationLevel(_defaultIsolation); if (_defaultIsolation != ImmediateIsolationLevel && _defaultIsolation != DeferredIsolationLevel) throw new NotSupportedException("Invalid Default IsolationLevel specified"); _baseSchemaName = FindKey(opts, "BaseSchemaName", DefaultBaseSchemaName); if (_sql == null) { SetupSQLiteBase(opts); } SQLiteOpenFlagsEnum flags = SQLiteOpenFlagsEnum.None; if (!SQLiteConvert.ToBoolean(FindKey(opts, "FailIfMissing", DefaultFailIfMissing.ToString()))) flags |= SQLiteOpenFlagsEnum.Create; if (SQLiteConvert.ToBoolean(FindKey(opts, "Read Only", DefaultReadOnly.ToString()))) { flags |= SQLiteOpenFlagsEnum.ReadOnly; // SQLite will return SQLITE_MISUSE on ReadOnly and Create flags &= ~SQLiteOpenFlagsEnum.Create; } else { flags |= SQLiteOpenFlagsEnum.ReadWrite; } if (fullUri) flags |= SQLiteOpenFlagsEnum.Uri; _sql.Open(fileName, _vfsName, _flags, flags, maxPoolSize, usePooling); _binaryGuid = SQLiteConvert.ToBoolean(FindKey(opts, "BinaryGUID", DefaultBinaryGUID.ToString())); #if INTEROP_CODEC || INTEROP_INCLUDE_SEE string hexPassword = FindKey(opts, "HexPassword", DefaultHexPassword); if (hexPassword != null) { string error = null; byte[] hexPasswordBytes = FromHexString(hexPassword, ref error); if (hexPasswordBytes == null) { throw new FormatException(HelperMethods.StringFormat( CultureInfo.CurrentCulture, "Cannot parse 'HexPassword' property value into byte values: {0}", error)); } _sql.SetPassword(hexPasswordBytes); } else { string password = FindKey(opts, "Password", DefaultPassword); if (password != null) { byte[] passwordBytes = UTF8Encoding.UTF8.GetBytes( password); /* throw */ _sql.SetPassword(passwordBytes); } else if (_password != null) { _sql.SetPassword(_password); } } hexPassword = null; /* IMMUTABLE */ _password = null; /* IMMUTABLE */ if (hidePassword) { if (opts.ContainsKey("HexPassword")) opts["HexPassword"] = String.Empty; if (opts.ContainsKey("Password")) opts["Password"] = String.Empty; _connectionString = BuildConnectionString(opts); } #else if (FindKey(opts, "HexPassword", DefaultHexPassword) != null) { throw new SQLiteException(SQLiteErrorCode.Error, "Cannot use \"HexPassword\" connection string property: " + "library was not built with encryption support, please " + "see \"https://www.sqlite.org/see\" for more information"); } if (FindKey(opts, "Password", DefaultPassword) != null) { throw new SQLiteException(SQLiteErrorCode.Error, "Cannot use \"Password\" connection string property: " + "library was not built with encryption support, please " + "see \"https://www.sqlite.org/see\" for more information"); } #endif if (!fullUri) _dataSource = Path.GetFileNameWithoutExtension(fileName); else _dataSource = fileName; _version++; ConnectionState oldstate = _connectionState; _connectionState = ConnectionState.Open; try { string strValue; bool boolValue; strValue = FindKey(opts, "SetDefaults", DefaultSetDefaults.ToString()); boolValue = SQLiteConvert.ToBoolean(strValue); if (boolValue) { using (SQLiteCommand cmd = CreateCommand()) { if (_busyTimeout != DefaultBusyTimeout) { cmd.CommandText = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA busy_timeout={0}", _busyTimeout); cmd.ExecuteNonQuery(); } int intValue; if (!fullUri && !isMemory) { strValue = FindKey(opts, "Page Size", SQLiteConvert.ToString(DefaultPageSize)); intValue = Convert.ToInt32(strValue, CultureInfo.InvariantCulture); if (intValue != DefaultPageSize) { cmd.CommandText = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA page_size={0}", intValue); cmd.ExecuteNonQuery(); } } strValue = FindKey(opts, "Max Page Count", SQLiteConvert.ToString(DefaultMaxPageCount)); intValue = Convert.ToInt32(strValue, CultureInfo.InvariantCulture); if (intValue != DefaultMaxPageCount) { cmd.CommandText = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA max_page_count={0}", intValue); cmd.ExecuteNonQuery(); } strValue = FindKey(opts, "Legacy Format", DefaultLegacyFormat.ToString()); boolValue = SQLiteConvert.ToBoolean(strValue); if (boolValue != DefaultLegacyFormat) { cmd.CommandText = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA legacy_file_format={0}", boolValue ? "ON" : "OFF"); cmd.ExecuteNonQuery(); } strValue = FindKey(opts, "Synchronous", DefaultSynchronous.ToString()); enumValue = TryParseEnum(typeof(SQLiteSynchronousEnum), strValue, true); if (!(enumValue is SQLiteSynchronousEnum) || ((SQLiteSynchronousEnum)enumValue != DefaultSynchronous)) { cmd.CommandText = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA synchronous={0}", strValue); cmd.ExecuteNonQuery(); } strValue = FindKey(opts, "Cache Size", SQLiteConvert.ToString(DefaultCacheSize)); intValue = Convert.ToInt32(strValue, CultureInfo.InvariantCulture); if (intValue != DefaultCacheSize) { cmd.CommandText = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA cache_size={0}", intValue); cmd.ExecuteNonQuery(); } strValue = FindKey(opts, "Journal Mode", DefaultJournalMode.ToString()); enumValue = TryParseEnum(typeof(SQLiteJournalModeEnum), strValue, true); if (!(enumValue is SQLiteJournalModeEnum) || ((SQLiteJournalModeEnum)enumValue != DefaultJournalMode)) { string pragmaStr = "PRAGMA journal_mode={0}"; #if INTEROP_INCLUDE_ZIPVFS if (useZipVfs) pragmaStr = "PRAGMA zipvfs_journal_mode={0}"; #endif cmd.CommandText = HelperMethods.StringFormat(CultureInfo.InvariantCulture, pragmaStr, strValue); cmd.ExecuteNonQuery(); } strValue = FindKey(opts, "Foreign Keys", DefaultForeignKeys.ToString()); boolValue = SQLiteConvert.ToBoolean(strValue); if (boolValue != DefaultForeignKeys) { cmd.CommandText = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA foreign_keys={0}", boolValue ? "ON" : "OFF"); cmd.ExecuteNonQuery(); } strValue = FindKey(opts, "Recursive Triggers", DefaultRecursiveTriggers.ToString()); boolValue = SQLiteConvert.ToBoolean(strValue); if (boolValue != DefaultRecursiveTriggers) { cmd.CommandText = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA recursive_triggers={0}", boolValue ? "ON" : "OFF"); cmd.ExecuteNonQuery(); } } } if (_progressHandler != null) _sql.SetProgressHook(_progressOps, _progressCallback); if (_authorizerHandler != null) _sql.SetAuthorizerHook(_authorizerCallback); if (_commitHandler != null) _sql.SetCommitHook(_commitCallback); if (_updateHandler != null) _sql.SetUpdateHook(_updateCallback); if (_rollbackHandler != null) _sql.SetRollbackHook(_rollbackCallback); #if !PLATFORM_COMPACTFRAMEWORK System.Transactions.Transaction transaction = Transactions.Transaction.Current; if (transaction != null && SQLiteConvert.ToBoolean(FindKey(opts, "Enlist", DefaultEnlist.ToString()))) { EnlistTransaction(transaction); } #endif _connectionState = oldstate; StateChangeEventArgs eventArgs = null; OnStateChange(ConnectionState.Open, ref eventArgs); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.Opened, eventArgs, null, null, null, null, eventArgConnectionString, new object[] { eventArgOpts })); #if DEBUG _debugString = HelperMethods.StringFormat( CultureInfo.InvariantCulture, "openThreadId = {0}, connectionString = {1}", HelperMethods.GetThreadId(), eventArgConnectionString); #endif } catch { _connectionState = oldstate; throw; } } catch (SQLiteException) { Close(); throw; } } /// /// Opens the connection using the parameters found in the and then returns it. /// /// The current connection object. public SQLiteConnection OpenAndReturn() { CheckDisposed(); Open(); return this; } /// /// Gets/sets the default command timeout for newly-created commands. This is especially useful for /// commands used internally such as inside a SQLiteTransaction, where setting the timeout is not possible. /// This can also be set in the ConnectionString with "Default Timeout" /// public int DefaultTimeout { get { CheckDisposed(); return _defaultTimeout; } set { CheckDisposed(); _defaultTimeout = value; } } /// /// Gets/sets the default busy timeout to use with the SQLite core library. This is only used when /// opening a connection. /// public int BusyTimeout { get { CheckDisposed(); return _busyTimeout; } set { CheckDisposed(); _busyTimeout = value; } } #if !PLATFORM_COMPACTFRAMEWORK /// /// EXPERIMENTAL -- /// The wait timeout to use with method. /// This is only used when waiting for the enlistment to be reset prior to /// enlisting in a transaction, and then only when the appropriate connection /// flag is set. /// public int WaitTimeout { get { CheckDisposed(); return _waitTimeout; } set { CheckDisposed(); _waitTimeout = value; } } #endif /// /// The maximum number of retries when preparing SQL to be executed. This /// normally only applies to preparation errors resulting from the database /// schema being changed. /// public int PrepareRetries { get { CheckDisposed(); return _prepareRetries; } set { CheckDisposed(); _prepareRetries = value; } } /// /// The approximate number of virtual machine instructions between progress /// events. In order for progress events to actually fire, the event handler /// must be added to the event as /// well. This value will only be used when the underlying native progress /// callback needs to be changed. /// public int ProgressOps { get { CheckDisposed(); return _progressOps; } set { CheckDisposed(); _progressOps = value; } } /// /// Non-zero if the built-in (i.e. framework provided) connection string /// parser should be used when opening the connection. /// public bool ParseViaFramework { get { CheckDisposed(); return _parseViaFramework; } set { CheckDisposed(); _parseViaFramework = value; } } /// /// Gets/sets the extra behavioral flags for this connection. See the /// enumeration for a list of /// possible values. /// public SQLiteConnectionFlags Flags { get { CheckDisposed(); return _flags; } set { CheckDisposed(); _flags = value; } } /// /// Gets/sets the default database type for this connection. This value /// will only be used when not null. /// public DbType? DefaultDbType { get { CheckDisposed(); return _defaultDbType; } set { CheckDisposed(); _defaultDbType = value; } } /// /// Gets/sets the default database type name for this connection. This /// value will only be used when not null. /// public string DefaultTypeName { get { CheckDisposed(); return _defaultTypeName; } set { CheckDisposed(); _defaultTypeName = value; } } /// /// Gets/sets the VFS name for this connection. This value will only be /// used when opening the database. /// public string VfsName { get { CheckDisposed(); return _vfsName; } set { CheckDisposed(); _vfsName = value; } } /// /// Returns non-zero if the underlying native connection handle is /// owned by this instance. /// public bool OwnHandle { get { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for checking handle."); return _sql.OwnHandle; } } /// /// Returns the version of the underlying SQLite database engine /// #if !PLATFORM_COMPACTFRAMEWORK [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public override string ServerVersion { get { CheckDisposed(); return SQLiteVersion; //if (_connectionState != ConnectionState.Open) // throw new InvalidOperationException(); //return _sql.Version; } } /// /// Returns the rowid of the most recent successful INSERT into the database from this connection. /// #if !PLATFORM_COMPACTFRAMEWORK [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public long LastInsertRowId { get { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for getting last insert rowid."); return _sql.LastInsertRowId; } } /// /// This method causes any pending database operation to abort and return at /// its earliest opportunity. This routine is typically called in response /// to a user action such as pressing "Cancel" or Ctrl-C where the user wants /// a long query operation to halt immediately. It is safe to call this /// routine from any thread. However, it is not safe to call this routine /// with a database connection that is closed or might close before this method /// returns. /// public void Cancel() { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for query cancellation."); _sql.Cancel(); /* throw */ } /// /// Returns the number of rows changed by the last INSERT, UPDATE, or DELETE statement executed on /// this connection. /// #if !PLATFORM_COMPACTFRAMEWORK [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public int Changes { get { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for getting number of changes."); return _sql.Changes; } } /// /// Checks if this connection to the specified database should be considered /// read-only. An exception will be thrown if the database name specified /// via cannot be found. /// /// /// The name of a database associated with this connection -OR- null for the /// main database. /// /// /// Non-zero if this connection to the specified database should be considered /// read-only. /// public bool IsReadOnly( string name ) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for checking read-only status."); return _sql.IsReadOnly(name); } /// /// Returns non-zero if the given database connection is in autocommit mode. /// Autocommit mode is on by default. Autocommit mode is disabled by a BEGIN /// statement. Autocommit mode is re-enabled by a COMMIT or ROLLBACK. /// #if !PLATFORM_COMPACTFRAMEWORK [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public bool AutoCommit { get { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for getting autocommit mode."); return _sql.AutoCommit; } } /// /// Returns the amount of memory (in bytes) currently in use by the SQLite core library. /// #if !PLATFORM_COMPACTFRAMEWORK [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public long MemoryUsed { get { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for getting memory used."); return _sql.MemoryUsed; } } /// /// Returns the maximum amount of memory (in bytes) used by the SQLite core library since the high-water mark was last reset. /// #if !PLATFORM_COMPACTFRAMEWORK [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public long MemoryHighwater { get { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for getting maximum memory used."); return _sql.MemoryHighwater; } } /// /// Returns various global memory statistics for the SQLite core library via /// a dictionary of key/value pairs. Currently, only the "MemoryUsed" and /// "MemoryHighwater" keys are returned and they have values that correspond /// to the values that could be obtained via the /// and connection properties. /// /// /// This dictionary will be populated with the global memory statistics. It /// will be created if necessary. /// public static void GetMemoryStatistics( ref IDictionary statistics ) { if (statistics == null) statistics = new Dictionary(); statistics["MemoryUsed"] = SQLite3.StaticMemoryUsed; statistics["MemoryHighwater"] = SQLite3.StaticMemoryHighwater; } /// /// Attempts to free as much heap memory as possible for this database connection. /// public void ReleaseMemory() { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for releasing memory."); SQLiteErrorCode rc = _sql.ReleaseMemory(); if (rc != SQLiteErrorCode.Ok) { throw new SQLiteException(rc, _sql.GetLastError("Could not release connection memory.")); } } /// /// Attempts to free N bytes of heap memory by deallocating non-essential memory /// allocations held by the database library. Memory used to cache database pages /// to improve performance is an example of non-essential memory. This is a no-op /// returning zero if the SQLite core library was not compiled with the compile-time /// option SQLITE_ENABLE_MEMORY_MANAGEMENT. Optionally, attempts to reset and/or /// compact the Win32 native heap, if applicable. /// /// /// The requested number of bytes to free. /// /// /// Non-zero to attempt a heap reset. /// /// /// Non-zero to attempt heap compaction. /// /// /// The number of bytes actually freed. This value may be zero. /// /// /// This value will be non-zero if the heap reset was successful. /// /// /// The size of the largest committed free block in the heap, in bytes. /// This value will be zero unless heap compaction is enabled. /// /// /// A standard SQLite return code (i.e. zero for success and non-zero /// for failure). /// #pragma warning disable 3001 public static SQLiteErrorCode ReleaseMemory( int nBytes, bool reset, bool compact, ref int nFree, ref bool resetOk, ref uint nLargest ) { return SQLite3.StaticReleaseMemory( nBytes, reset, compact, ref nFree, ref resetOk, ref nLargest); } #pragma warning restore 3001 /// /// Sets the status of the memory usage tracking subsystem in the SQLite core library. By default, this is enabled. /// If this is disabled, memory usage tracking will not be performed. This is not really a per-connection value, it is /// global to the process. /// /// Non-zero to enable memory usage tracking, zero otherwise. /// A standard SQLite return code (i.e. zero for success and non-zero for failure). public static SQLiteErrorCode SetMemoryStatus(bool value) { return SQLite3.StaticSetMemoryStatus(value); } /// /// Returns a string containing the define constants (i.e. compile-time /// options) used to compile the core managed assembly, delimited with /// spaces. /// public static string DefineConstants { get { return SQLite3.DefineConstants; } } /// /// Returns the version of the underlying SQLite core library. /// public static string SQLiteVersion { get { return SQLite3.SQLiteVersion; } } /// /// This method returns the string whose value is the same as the /// SQLITE_SOURCE_ID C preprocessor macro used when compiling the /// SQLite core library. /// public static string SQLiteSourceId { get { return SQLite3.SQLiteSourceId; } } /// /// Returns a string containing the compile-time options used to /// compile the SQLite core native library, delimited with spaces. /// public static string SQLiteCompileOptions { get { return SQLite3.SQLiteCompileOptions; } } /// /// This method returns the version of the interop SQLite assembly /// used. If the SQLite interop assembly is not in use or the /// necessary information cannot be obtained for any reason, a null /// value may be returned. /// public static string InteropVersion { get { return SQLite3.InteropVersion; } } /// /// This method returns the string whose value contains the unique /// identifier for the source checkout used to build the interop /// assembly. If the SQLite interop assembly is not in use or the /// necessary information cannot be obtained for any reason, a null /// value may be returned. /// public static string InteropSourceId { get { return SQLite3.InteropSourceId; } } /// /// Returns a string containing the compile-time options used to /// compile the SQLite interop assembly, delimited with spaces. /// public static string InteropCompileOptions { get { return SQLite3.InteropCompileOptions; } } /// /// This method returns the version of the managed components used /// to interact with the SQLite core library. If the necessary /// information cannot be obtained for any reason, a null value may /// be returned. /// public static string ProviderVersion { get { return (_assembly != null) ? _assembly.GetName().Version.ToString() : null; } } /// /// This method returns the string whose value contains the unique /// identifier for the source checkout used to build the managed /// components currently executing. If the necessary information /// cannot be obtained for any reason, a null value may be returned. /// public static string ProviderSourceId { get { if (_assembly == null) return null; string sourceId = null; if (_assembly.IsDefined(typeof(AssemblySourceIdAttribute), false)) { AssemblySourceIdAttribute attribute = (AssemblySourceIdAttribute)_assembly.GetCustomAttributes( typeof(AssemblySourceIdAttribute), false)[0]; sourceId = attribute.SourceId; } string sourceTimeStamp = null; if (_assembly.IsDefined(typeof(AssemblySourceTimeStampAttribute), false)) { AssemblySourceTimeStampAttribute attribute = (AssemblySourceTimeStampAttribute)_assembly.GetCustomAttributes( typeof(AssemblySourceTimeStampAttribute), false)[0]; sourceTimeStamp = attribute.SourceTimeStamp; } if ((sourceId != null) || (sourceTimeStamp != null)) { if (sourceId == null) sourceId = "0000000000000000000000000000000000000000"; if (sourceTimeStamp == null) sourceTimeStamp = "0000-00-00 00:00:00 UTC"; return HelperMethods.StringFormat( CultureInfo.InvariantCulture, "{0} {1}", sourceId, sourceTimeStamp); } else { return null; } } } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Queries and returns the value of the specified setting, using the /// cached setting names and values for the last connection that used /// the method, when available. /// /// /// The name of the setting. /// /// /// The value to be returned if the setting has not been set explicitly /// or cannot be determined. /// /// /// The value of the cached setting is stored here if found; otherwise, /// the value of is stored here. /// /// /// Non-zero if the cached setting was found; otherwise, zero. /// private static bool TryGetLastCachedSetting( string name, object @default, out object value ) { if (_lastConnectionInOpen == null) { value = @default; return false; } return _lastConnectionInOpen.TryGetCachedSetting( name, @default, out value); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Adds or sets the cached setting specified by /// to the value specified by using the cached /// setting names and values for the last connection that used the /// method, when available. /// /// /// The name of the cached setting to add or replace. /// /// /// The new value of the cached setting. /// private static void SetLastCachedSetting( string name, /* in */ object value /* in */ ) { if (_lastConnectionInOpen == null) return; _lastConnectionInOpen.SetCachedSetting(name, value); } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// The default connection flags to be used for all opened connections /// when they are not present in the connection string. /// public static SQLiteConnectionFlags DefaultFlags { get { string name = "DefaultFlags_SQLiteConnection"; object value; if (!TryGetLastCachedSetting(name, null, out value)) { value = UnsafeNativeMethods.GetSettingValue(name, null); SetLastCachedSetting(name, value); } if (value == null) return FallbackDefaultFlags; object enumValue = TryParseEnum( typeof(SQLiteConnectionFlags), value.ToString(), true); if (enumValue is SQLiteConnectionFlags) return (SQLiteConnectionFlags)enumValue; return FallbackDefaultFlags; } } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// The extra connection flags to be used for all opened connections. /// public static SQLiteConnectionFlags SharedFlags { get { lock (_syncRoot) { return _sharedFlags; } } set { lock (_syncRoot) { _sharedFlags = value; } } } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Returns the state of the connection. /// #if !PLATFORM_COMPACTFRAMEWORK [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public override ConnectionState State { get { CheckDisposed(); return _connectionState; } } /// /// Passes a shutdown request to the SQLite core library. Does not throw /// an exception if the shutdown request fails. /// /// /// A standard SQLite return code (i.e. zero for success and non-zero for /// failure). /// public SQLiteErrorCode Shutdown() { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for shutdown."); _sql.Close(false); /* NOTE: MUST be closed before shutdown. */ SQLiteErrorCode rc = _sql.Shutdown(); #if !NET_COMPACT_20 && TRACE_CONNECTION if (rc != SQLiteErrorCode.Ok) System.Diagnostics.Trace.WriteLine(HelperMethods.StringFormat( CultureInfo.CurrentCulture, "Shutdown (Instance) Failed: {0}", rc)); #endif return rc; } /// /// Passes a shutdown request to the SQLite core library. Throws an /// exception if the shutdown request fails and the no-throw parameter /// is non-zero. /// /// /// Non-zero to reset the database and temporary directories to their /// default values, which should be null for both. /// /// /// When non-zero, throw an exception if the shutdown request fails. /// public static void Shutdown( bool directories, bool noThrow ) { SQLiteErrorCode rc = SQLite3.StaticShutdown(directories); if (rc != SQLiteErrorCode.Ok) { #if !NET_COMPACT_20 && TRACE_CONNECTION System.Diagnostics.Trace.WriteLine(HelperMethods.StringFormat( CultureInfo.CurrentCulture, "Shutdown (Static) Failed: {0}", rc)); #endif if (!noThrow) throw new SQLiteException(rc, null); } } /// Enables or disables extended result codes returned by SQLite public void SetExtendedResultCodes(bool bOnOff) { CheckDisposed(); if (_sql != null) _sql.SetExtendedResultCodes(bOnOff); } /// Enables or disables extended result codes returned by SQLite public SQLiteErrorCode ResultCode() { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for getting result code."); return _sql.ResultCode(); } /// Enables or disables extended result codes returned by SQLite public SQLiteErrorCode ExtendedResultCode() { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for getting extended result code."); return _sql.ExtendedResultCode(); } /// Add a log message via the SQLite sqlite3_log interface. public void LogMessage(SQLiteErrorCode iErrCode, string zMessage) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for logging message."); _sql.LogMessage(iErrCode, zMessage); } /// Add a log message via the SQLite sqlite3_log interface. public void LogMessage(int iErrCode, string zMessage) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for logging message."); _sql.LogMessage((SQLiteErrorCode)iErrCode, zMessage); } #if INTEROP_CODEC || INTEROP_INCLUDE_SEE /// /// Change the password (or assign a password) to an open database. /// /// /// No readers or writers may be active for this process. The database must already be open /// and if it already was password protected, the existing password must already have been supplied. /// /// The new password to assign to the database public void ChangePassword(string newPassword) { CheckDisposed(); if (!String.IsNullOrEmpty(newPassword)) { byte[] newPasswordBytes = UTF8Encoding.UTF8.GetBytes( newPassword); /* throw */ ChangePassword(newPasswordBytes); } else { ChangePassword((byte[])null); } } /// /// Change the password (or assign a password) to an open database. /// /// /// No readers or writers may be active for this process. The database must already be open /// and if it already was password protected, the existing password must already have been supplied. /// /// The new password to assign to the database public void ChangePassword(byte[] newPassword) { CheckDisposed(); if (_connectionState != ConnectionState.Open) throw new InvalidOperationException("Database must be opened before changing the password."); _sql.ChangePassword(newPassword); } /// /// Sets the password for a password-protected database. A password-protected database is /// unusable for any operation until the password has been set. /// /// The password for the database public void SetPassword(string databasePassword) { CheckDisposed(); if (!String.IsNullOrEmpty(databasePassword)) { byte[] databasePasswordBytes = UTF8Encoding.UTF8.GetBytes( databasePassword); /* throw */ SetPassword(databasePasswordBytes); } else { SetPassword((byte[])null); } } /// /// Sets the password for a password-protected database. A password-protected database is /// unusable for any operation until the password has been set. /// /// The password for the database public void SetPassword(byte[] databasePassword) { CheckDisposed(); if (_connectionState != ConnectionState.Closed) throw new InvalidOperationException("Password can only be set before the database is opened."); if (databasePassword != null) if (databasePassword.Length == 0) databasePassword = null; if ((databasePassword != null) && HelperMethods.HasFlags(_flags, SQLiteConnectionFlags.HidePassword)) { throw new InvalidOperationException( "With 'HidePassword' enabled, passwords can only be set via the connection string."); } _password = databasePassword; } #endif /// /// Queries or modifies the number of retries or the retry interval (in milliseconds) for /// certain I/O operations that may fail due to anti-virus software. /// /// The number of times to retry the I/O operation. A negative value /// will cause the current count to be queried and replace that negative value. /// The number of milliseconds to wait before retrying the I/O /// operation. This number is multiplied by the number of retry attempts so far to come /// up with the final number of milliseconds to wait. A negative value will cause the /// current interval to be queried and replace that negative value. /// Zero for success, non-zero for error. public SQLiteErrorCode SetAvRetry(ref int count, ref int interval) { CheckDisposed(); if (_connectionState != ConnectionState.Open) throw new InvalidOperationException( "Database must be opened before changing the AV retry parameters."); SQLiteErrorCode rc; IntPtr pArg = IntPtr.Zero; try { pArg = Marshal.AllocHGlobal(sizeof(int) * 2); Marshal.WriteInt32(pArg, 0, count); Marshal.WriteInt32(pArg, sizeof(int), interval); rc = _sql.FileControl(null, SQLITE_FCNTL_WIN32_AV_RETRY, pArg); if (rc == SQLiteErrorCode.Ok) { count = Marshal.ReadInt32(pArg, 0); interval = Marshal.ReadInt32(pArg, sizeof(int)); } } finally { if (pArg != IntPtr.Zero) Marshal.FreeHGlobal(pArg); } return rc; } /// /// Sets the chunk size for the primary file associated with this database /// connection. /// /// /// The new chunk size for the main database, in bytes. /// /// /// Zero for success, non-zero for error. /// public SQLiteErrorCode SetChunkSize(int size) { CheckDisposed(); if (_connectionState != ConnectionState.Open) throw new InvalidOperationException( "Database must be opened before changing the chunk size."); IntPtr pArg = IntPtr.Zero; try { pArg = Marshal.AllocHGlobal(sizeof(int) * 1); Marshal.WriteInt32(pArg, 0, size); return _sql.FileControl(null, SQLITE_FCNTL_CHUNK_SIZE, pArg); } finally { if (pArg != IntPtr.Zero) Marshal.FreeHGlobal(pArg); } } /// /// Removes one set of surrounding single -OR- double quotes from the string /// value and returns the resulting string value. If the string is null, empty, /// or contains quotes that are not balanced, nothing is done and the original /// string value will be returned. /// /// The string value to process. /// /// The string value, modified to remove one set of surrounding single -OR- /// double quotes, if applicable. /// private static string UnwrapString(string value) { if (String.IsNullOrEmpty(value)) { // // NOTE: The string is null or empty, return it verbatim. // return value; } int length = value.Length; if ((value[0] == SQLiteConvert.QuoteChar) && (value[length - 1] == SQLiteConvert.QuoteChar)) { // // NOTE: Remove the first and last character, which are // both double quotes. // return value.Substring(1, length - 2); } if ((value[0] == SQLiteConvert.AltQuoteChar) && (value[length - 1] == SQLiteConvert.AltQuoteChar)) { // // NOTE: Remove the first and last character, which are // both single quotes. // return value.Substring(1, length - 2); } // // NOTE: No match, return the input string verbatim. // return value; } /// /// Determines the directory to be used when dealing with the "|DataDirectory|" /// macro in a database file name. /// /// /// The directory to use in place of the "|DataDirectory|" macro -OR- null if it /// cannot be determined. /// private static string GetDataDirectory() { #if PLATFORM_COMPACTFRAMEWORK string result = Path.GetDirectoryName( Assembly.GetCallingAssembly().GetName().CodeBase); #else string result = AppDomain.CurrentDomain.GetData( "DataDirectory") as string; if (String.IsNullOrEmpty(result)) result = AppDomain.CurrentDomain.BaseDirectory; #endif return result; } /// /// Expand the filename of the data source, resolving the |DataDirectory| /// macro as appropriate. /// /// The database filename to expand /// /// Non-zero if the returned file name should be converted to a full path /// (except when using the .NET Compact Framework). /// /// The expanded path and filename of the filename private static string ExpandFileName(string sourceFile, bool toFullPath) { if (String.IsNullOrEmpty(sourceFile)) return sourceFile; if (sourceFile.StartsWith(_dataDirectory, StringComparison.OrdinalIgnoreCase)) { string dataDirectory = GetDataDirectory(); if (sourceFile.Length > _dataDirectory.Length) { if (sourceFile[_dataDirectory.Length] == Path.DirectorySeparatorChar || sourceFile[_dataDirectory.Length] == Path.AltDirectorySeparatorChar) sourceFile = sourceFile.Remove(_dataDirectory.Length, 1); } sourceFile = Path.Combine(dataDirectory, sourceFile.Substring(_dataDirectory.Length)); } #if !PLATFORM_COMPACTFRAMEWORK if (toFullPath) sourceFile = Path.GetFullPath(sourceFile); #endif return sourceFile; } /// /// The following commands are used to extract schema information out of the database. Valid schema types are: /// /// /// MetaDataCollections /// /// /// DataSourceInformation /// /// /// Catalogs /// /// /// Columns /// /// /// ForeignKeys /// /// /// Indexes /// /// /// IndexColumns /// /// /// Tables /// /// /// Views /// /// /// ViewColumns /// /// /// /// /// Returns the MetaDataCollections schema /// /// A DataTable of the MetaDataCollections schema public override DataTable GetSchema() { CheckDisposed(); return GetSchema("MetaDataCollections", null); } /// /// Returns schema information of the specified collection /// /// The schema collection to retrieve /// A DataTable of the specified collection public override DataTable GetSchema(string collectionName) { CheckDisposed(); return GetSchema(collectionName, new string[0]); } /// /// Retrieves schema information using the specified constraint(s) for the specified collection /// /// The collection to retrieve. /// /// The restrictions to impose. Typically, this may include: /// /// /// restrictionValues element index /// usage /// /// /// 0 /// The database (or catalog) name, if applicable. /// /// /// 1 /// The schema name. This is not used by this provider. /// /// /// 2 /// The table name, if applicable. /// /// /// 3 /// /// Depends on . /// When "IndexColumns", it is the index name; otherwise, it is the column name. /// /// /// /// 4 /// /// Depends on . /// When "IndexColumns", it is the column name; otherwise, it is not used. /// /// /// /// /// A DataTable of the specified collection public override DataTable GetSchema(string collectionName, string[] restrictionValues) { CheckDisposed(); if (_connectionState != ConnectionState.Open) throw new InvalidOperationException(); string[] parms = new string[5]; if (restrictionValues == null) restrictionValues = new string[0]; restrictionValues.CopyTo(parms, 0); switch (collectionName.ToUpper(CultureInfo.InvariantCulture)) { case "METADATACOLLECTIONS": return Schema_MetaDataCollections(); case "DATASOURCEINFORMATION": return Schema_DataSourceInformation(); case "DATATYPES": return Schema_DataTypes(); case "COLUMNS": case "TABLECOLUMNS": return Schema_Columns(parms[0], parms[2], parms[3]); case "INDEXES": return Schema_Indexes(parms[0], parms[2], parms[3]); case "TRIGGERS": return Schema_Triggers(parms[0], parms[2], parms[3]); case "INDEXCOLUMNS": return Schema_IndexColumns(parms[0], parms[2], parms[3], parms[4]); case "TABLES": return Schema_Tables(parms[0], parms[2], parms[3]); case "VIEWS": return Schema_Views(parms[0], parms[2]); case "VIEWCOLUMNS": return Schema_ViewColumns(parms[0], parms[2], parms[3]); case "FOREIGNKEYS": return Schema_ForeignKeys(parms[0], parms[2], parms[3]); case "CATALOGS": return Schema_Catalogs(parms[0]); case "RESERVEDWORDS": return Schema_ReservedWords(); } throw new NotSupportedException(); } private static DataTable Schema_ReservedWords() { DataTable tbl = new DataTable("ReservedWords"); tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("ReservedWord", typeof(string)); tbl.Columns.Add("MaximumVersion", typeof(string)); tbl.Columns.Add("MinimumVersion", typeof(string)); tbl.BeginLoadData(); DataRow row; foreach (string word in SR.Keywords.Split(new char[] { ',' })) { row = tbl.NewRow(); row[0] = word; tbl.Rows.Add(row); } tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; } /// /// Builds a MetaDataCollections schema datatable /// /// DataTable private static DataTable Schema_MetaDataCollections() { DataTable tbl = new DataTable("MetaDataCollections"); tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("CollectionName", typeof(string)); tbl.Columns.Add("NumberOfRestrictions", typeof(int)); tbl.Columns.Add("NumberOfIdentifierParts", typeof(int)); tbl.BeginLoadData(); StringReader reader = new StringReader(SR.MetaDataCollections); tbl.ReadXml(reader); reader.Close(); tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; } /// /// Builds a DataSourceInformation datatable /// /// DataTable private DataTable Schema_DataSourceInformation() { DataTable tbl = new DataTable("DataSourceInformation"); DataRow row; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add(DbMetaDataColumnNames.CompositeIdentifierSeparatorPattern, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductName, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductVersion, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductVersionNormalized, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.GroupByBehavior, typeof(int)); tbl.Columns.Add(DbMetaDataColumnNames.IdentifierPattern, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.IdentifierCase, typeof(int)); tbl.Columns.Add(DbMetaDataColumnNames.OrderByColumnsInSelect, typeof(bool)); tbl.Columns.Add(DbMetaDataColumnNames.ParameterMarkerFormat, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.ParameterMarkerPattern, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.ParameterNameMaxLength, typeof(int)); tbl.Columns.Add(DbMetaDataColumnNames.ParameterNamePattern, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.QuotedIdentifierPattern, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.QuotedIdentifierCase, typeof(int)); tbl.Columns.Add(DbMetaDataColumnNames.StatementSeparatorPattern, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.StringLiteralPattern, typeof(string)); tbl.Columns.Add(DbMetaDataColumnNames.SupportedJoinOperators, typeof(int)); tbl.BeginLoadData(); row = tbl.NewRow(); row.ItemArray = new object[] { null, "SQLite", _sql.Version, _sql.Version, 3, @"(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\""[^\""\0]|\""\""+\""$)", 1, false, "{0}", @"@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)", 255, @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)", @"(([^\[]|\]\])*)", 1, ";", @"'(([^']|'')*)'", 15 }; tbl.Rows.Add(row); tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; } /// /// Build a Columns schema /// /// The catalog (attached database) to query, can be null /// The table to retrieve schema information for, can be null /// The column to retrieve schema information for, can be null /// DataTable private DataTable Schema_Columns(string strCatalog, string strTable, string strColumn) { DataTable tbl = new DataTable("Columns"); DataRow row; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("COLUMN_NAME", typeof(string)); tbl.Columns.Add("COLUMN_GUID", typeof(Guid)); tbl.Columns.Add("COLUMN_PROPID", typeof(long)); tbl.Columns.Add("ORDINAL_POSITION", typeof(int)); tbl.Columns.Add("COLUMN_HASDEFAULT", typeof(bool)); tbl.Columns.Add("COLUMN_DEFAULT", typeof(string)); tbl.Columns.Add("COLUMN_FLAGS", typeof(long)); tbl.Columns.Add("IS_NULLABLE", typeof(bool)); tbl.Columns.Add("DATA_TYPE", typeof(string)); tbl.Columns.Add("TYPE_GUID", typeof(Guid)); tbl.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int)); tbl.Columns.Add("CHARACTER_OCTET_LENGTH", typeof(int)); tbl.Columns.Add("NUMERIC_PRECISION", typeof(int)); tbl.Columns.Add("NUMERIC_SCALE", typeof(int)); tbl.Columns.Add("DATETIME_PRECISION", typeof(long)); tbl.Columns.Add("CHARACTER_SET_CATALOG", typeof(string)); tbl.Columns.Add("CHARACTER_SET_SCHEMA", typeof(string)); tbl.Columns.Add("CHARACTER_SET_NAME", typeof(string)); tbl.Columns.Add("COLLATION_CATALOG", typeof(string)); tbl.Columns.Add("COLLATION_SCHEMA", typeof(string)); tbl.Columns.Add("COLLATION_NAME", typeof(string)); tbl.Columns.Add("DOMAIN_CATALOG", typeof(string)); tbl.Columns.Add("DOMAIN_NAME", typeof(string)); tbl.Columns.Add("DESCRIPTION", typeof(string)); tbl.Columns.Add("PRIMARY_KEY", typeof(bool)); tbl.Columns.Add("EDM_TYPE", typeof(string)); tbl.Columns.Add("AUTOINCREMENT", typeof(bool)); tbl.Columns.Add("UNIQUE", typeof(bool)); tbl.BeginLoadData(); if (String.IsNullOrEmpty(strCatalog)) strCatalog = GetDefaultCatalogName(); string master = GetMasterTableName(IsTemporaryCatalogName(strCatalog)); using (SQLiteCommand cmdTables = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table' OR [type] LIKE 'view'", strCatalog, master), this)) using (SQLiteDataReader rdTables = cmdTables.ExecuteReader()) { while (rdTables.Read()) { if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), StringComparison.OrdinalIgnoreCase) == 0) { try { using (SQLiteCommand cmd = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this)) using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader(CommandBehavior.SchemaOnly)) using (DataTable tblSchema = rd.GetSchemaTable(true, true)) { foreach (DataRow schemaRow in tblSchema.Rows) { if (String.Compare(schemaRow[SchemaTableColumn.ColumnName].ToString(), strColumn, StringComparison.OrdinalIgnoreCase) == 0 || strColumn == null) { row = tbl.NewRow(); row["NUMERIC_PRECISION"] = schemaRow[SchemaTableColumn.NumericPrecision]; row["NUMERIC_SCALE"] = schemaRow[SchemaTableColumn.NumericScale]; row["TABLE_NAME"] = rdTables.GetString(2); row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.ColumnName]; row["TABLE_CATALOG"] = strCatalog; row["ORDINAL_POSITION"] = schemaRow[SchemaTableColumn.ColumnOrdinal]; row["COLUMN_HASDEFAULT"] = (schemaRow[SchemaTableOptionalColumn.DefaultValue] != DBNull.Value); row["COLUMN_DEFAULT"] = schemaRow[SchemaTableOptionalColumn.DefaultValue]; row["IS_NULLABLE"] = schemaRow[SchemaTableColumn.AllowDBNull]; row["DATA_TYPE"] = schemaRow["DataTypeName"].ToString().ToLower(CultureInfo.InvariantCulture); row["EDM_TYPE"] = SQLiteConvert.DbTypeToTypeName(this, (DbType)schemaRow[SchemaTableColumn.ProviderType], _flags).ToString().ToLower(CultureInfo.InvariantCulture); row["CHARACTER_MAXIMUM_LENGTH"] = schemaRow[SchemaTableColumn.ColumnSize]; row["TABLE_SCHEMA"] = schemaRow[SchemaTableColumn.BaseSchemaName]; row["PRIMARY_KEY"] = schemaRow[SchemaTableColumn.IsKey]; row["AUTOINCREMENT"] = schemaRow[SchemaTableOptionalColumn.IsAutoIncrement]; row["COLLATION_NAME"] = schemaRow["CollationType"]; row["UNIQUE"] = schemaRow[SchemaTableColumn.IsUnique]; tbl.Rows.Add(row); } } } } catch(SQLiteException) { } } } } tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; } /// /// Returns index information for the given database and catalog /// /// The catalog (attached database) to query, can be null /// The name of the index to retrieve information for, can be null /// The table to retrieve index information for, can be null /// DataTable private DataTable Schema_Indexes(string strCatalog, string strTable, string strIndex) { DataTable tbl = new DataTable("Indexes"); DataRow row; List primaryKeys = new List(); bool maybeRowId; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("INDEX_CATALOG", typeof(string)); tbl.Columns.Add("INDEX_SCHEMA", typeof(string)); tbl.Columns.Add("INDEX_NAME", typeof(string)); tbl.Columns.Add("PRIMARY_KEY", typeof(bool)); tbl.Columns.Add("UNIQUE", typeof(bool)); tbl.Columns.Add("CLUSTERED", typeof(bool)); tbl.Columns.Add("TYPE", typeof(int)); tbl.Columns.Add("FILL_FACTOR", typeof(int)); tbl.Columns.Add("INITIAL_SIZE", typeof(int)); tbl.Columns.Add("NULLS", typeof(int)); tbl.Columns.Add("SORT_BOOKMARKS", typeof(bool)); tbl.Columns.Add("AUTO_UPDATE", typeof(bool)); tbl.Columns.Add("NULL_COLLATION", typeof(int)); tbl.Columns.Add("ORDINAL_POSITION", typeof(int)); tbl.Columns.Add("COLUMN_NAME", typeof(string)); tbl.Columns.Add("COLUMN_GUID", typeof(Guid)); tbl.Columns.Add("COLUMN_PROPID", typeof(long)); tbl.Columns.Add("COLLATION", typeof(short)); tbl.Columns.Add("CARDINALITY", typeof(Decimal)); tbl.Columns.Add("PAGES", typeof(int)); tbl.Columns.Add("FILTER_CONDITION", typeof(string)); tbl.Columns.Add("INTEGRATED", typeof(bool)); tbl.Columns.Add("INDEX_DEFINITION", typeof(string)); tbl.BeginLoadData(); if (String.IsNullOrEmpty(strCatalog)) strCatalog = GetDefaultCatalogName(); string master = GetMasterTableName(IsTemporaryCatalogName(strCatalog)); using (SQLiteCommand cmdTables = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this)) using (SQLiteDataReader rdTables = cmdTables.ExecuteReader()) { while (rdTables.Read()) { maybeRowId = false; primaryKeys.Clear(); if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, StringComparison.OrdinalIgnoreCase) == 0) { // First, look for any rowid indexes -- which sqlite defines are INTEGER PRIMARY KEY columns. // Such indexes are not listed in the indexes list but count as indexes just the same. try { using (SQLiteCommand cmdTable = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA [{0}].table_info([{1}])", strCatalog, rdTables.GetString(2)), this)) using (SQLiteDataReader rdTable = cmdTable.ExecuteReader()) { while (rdTable.Read()) { if (rdTable.GetInt32(5) != 0) { primaryKeys.Add(rdTable.GetInt32(0)); // If the primary key is of type INTEGER, then its a rowid and we need to make a fake index entry for it. if (String.Compare(rdTable.GetString(2), "INTEGER", StringComparison.OrdinalIgnoreCase) == 0) maybeRowId = true; } } } } catch (SQLiteException) { } if (primaryKeys.Count == 1 && maybeRowId == true) { row = tbl.NewRow(); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = rdTables.GetString(2); row["INDEX_CATALOG"] = strCatalog; row["PRIMARY_KEY"] = true; row["INDEX_NAME"] = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "{1}_PK_{0}", rdTables.GetString(2), master); row["UNIQUE"] = true; if (String.Compare((string)row["INDEX_NAME"], strIndex, StringComparison.OrdinalIgnoreCase) == 0 || strIndex == null) { tbl.Rows.Add(row); } primaryKeys.Clear(); } // Now fetch all the rest of the indexes. try { using (SQLiteCommand cmd = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_list([{1}])", strCatalog, rdTables.GetString(2)), this)) using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader()) { while (rd.Read()) { if (String.Compare(rd.GetString(1), strIndex, StringComparison.OrdinalIgnoreCase) == 0 || strIndex == null) { row = tbl.NewRow(); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = rdTables.GetString(2); row["INDEX_CATALOG"] = strCatalog; row["INDEX_NAME"] = rd.GetString(1); row["UNIQUE"] = SQLiteConvert.ToBoolean(rd.GetValue(2), CultureInfo.InvariantCulture, false); row["PRIMARY_KEY"] = false; // get the index definition using (SQLiteCommand cmdIndexes = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{2}] WHERE [type] LIKE 'index' AND [name] LIKE '{1}'", strCatalog, rd.GetString(1).Replace("'", "''"), master), this)) using (SQLiteDataReader rdIndexes = cmdIndexes.ExecuteReader()) { while (rdIndexes.Read()) { if (rdIndexes.IsDBNull(4) == false) row["INDEX_DEFINITION"] = rdIndexes.GetString(4); break; } } // Now for the really hard work. Figure out which index is the primary key index. // The only way to figure it out is to check if the index was an autoindex and if we have a non-rowid // primary key, and all the columns in the given index match the primary key columns if (primaryKeys.Count > 0 && rd.GetString(1).StartsWith("sqlite_autoindex_" + rdTables.GetString(2), StringComparison.InvariantCultureIgnoreCase) == true) { using (SQLiteCommand cmdDetails = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rd.GetString(1)), this)) using (SQLiteDataReader rdDetails = cmdDetails.ExecuteReader()) { int nMatches = 0; while (rdDetails.Read()) { if (primaryKeys.Contains(rdDetails.GetInt32(1)) == false) { nMatches = 0; break; } nMatches++; } if (nMatches == primaryKeys.Count) { row["PRIMARY_KEY"] = true; primaryKeys.Clear(); } } } tbl.Rows.Add(row); } } } } catch (SQLiteException) { } } } } tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; } private DataTable Schema_Triggers(string catalog, string table, string triggerName) { DataTable tbl = new DataTable("Triggers"); DataRow row; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("TRIGGER_NAME", typeof(string)); tbl.Columns.Add("TRIGGER_DEFINITION", typeof(string)); tbl.BeginLoadData(); if (String.IsNullOrEmpty(table)) table = null; if (String.IsNullOrEmpty(catalog)) catalog = GetDefaultCatalogName(); string master = GetMasterTableName(IsTemporaryCatalogName(catalog)); using (SQLiteCommand cmd = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT [type], [name], [tbl_name], [rootpage], [sql], [rowid] FROM [{0}].[{1}] WHERE [type] LIKE 'trigger'", catalog, master), this)) using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader()) { while (rd.Read()) { if (String.Compare(rd.GetString(1), triggerName, StringComparison.OrdinalIgnoreCase) == 0 || triggerName == null) { if (table == null || String.Compare(table, rd.GetString(2), StringComparison.OrdinalIgnoreCase) == 0) { row = tbl.NewRow(); row["TABLE_CATALOG"] = catalog; row["TABLE_NAME"] = rd.GetString(2); row["TRIGGER_NAME"] = rd.GetString(1); row["TRIGGER_DEFINITION"] = rd.GetString(4); tbl.Rows.Add(row); } } } } tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; } /// /// Retrieves table schema information for the database and catalog /// /// The catalog (attached database) to retrieve tables on /// The table to retrieve, can be null /// The table type, can be null /// DataTable private DataTable Schema_Tables(string strCatalog, string strTable, string strType) { DataTable tbl = new DataTable("Tables"); DataRow row; string strItem; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("TABLE_TYPE", typeof(string)); tbl.Columns.Add("TABLE_ID", typeof(long)); tbl.Columns.Add("TABLE_ROOTPAGE", typeof(int)); tbl.Columns.Add("TABLE_DEFINITION", typeof(string)); tbl.BeginLoadData(); if (String.IsNullOrEmpty(strCatalog)) strCatalog = GetDefaultCatalogName(); string master = GetMasterTableName(IsTemporaryCatalogName(strCatalog)); using (SQLiteCommand cmd = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT [type], [name], [tbl_name], [rootpage], [sql], [rowid] FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this)) using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader()) { while (rd.Read()) { strItem = rd.GetString(0); if (String.Compare(rd.GetString(2), 0, "SQLITE_", 0, 7, StringComparison.OrdinalIgnoreCase) == 0) strItem = "SYSTEM_TABLE"; if (String.Compare(strType, strItem, StringComparison.OrdinalIgnoreCase) == 0 || strType == null) { if (String.Compare(rd.GetString(2), strTable, StringComparison.OrdinalIgnoreCase) == 0 || strTable == null) { row = tbl.NewRow(); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = rd.GetString(2); row["TABLE_TYPE"] = strItem; row["TABLE_ID"] = rd.GetInt64(5); row["TABLE_ROOTPAGE"] = rd.GetInt32(3); row["TABLE_DEFINITION"] = rd.GetString(4); tbl.Rows.Add(row); } } } } tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; } /// /// Retrieves view schema information for the database /// /// The catalog (attached database) to retrieve views on /// The view name, can be null /// DataTable private DataTable Schema_Views(string strCatalog, string strView) { DataTable tbl = new DataTable("Views"); DataRow row; string strItem; int nPos; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("VIEW_DEFINITION", typeof(string)); tbl.Columns.Add("CHECK_OPTION", typeof(bool)); tbl.Columns.Add("IS_UPDATABLE", typeof(bool)); tbl.Columns.Add("DESCRIPTION", typeof(string)); tbl.Columns.Add("DATE_CREATED", typeof(DateTime)); tbl.Columns.Add("DATE_MODIFIED", typeof(DateTime)); tbl.BeginLoadData(); if (String.IsNullOrEmpty(strCatalog)) strCatalog = GetDefaultCatalogName(); string master = GetMasterTableName(IsTemporaryCatalogName(strCatalog)); using (SQLiteCommand cmd = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'view'", strCatalog, master), this)) using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader()) { while (rd.Read()) { if (String.Compare(rd.GetString(1), strView, StringComparison.OrdinalIgnoreCase) == 0 || String.IsNullOrEmpty(strView)) { strItem = rd.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' '); nPos = CultureInfo.InvariantCulture.CompareInfo.IndexOf(strItem, " AS ", CompareOptions.IgnoreCase); if (nPos > -1) { strItem = strItem.Substring(nPos + 4).Trim(); row = tbl.NewRow(); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = rd.GetString(2); row["IS_UPDATABLE"] = false; row["VIEW_DEFINITION"] = strItem; tbl.Rows.Add(row); } } } } tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; } /// /// Retrieves catalog (attached databases) schema information for the database /// /// The catalog to retrieve, can be null /// DataTable private DataTable Schema_Catalogs(string strCatalog) { DataTable tbl = new DataTable("Catalogs"); DataRow row; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("CATALOG_NAME", typeof(string)); tbl.Columns.Add("DESCRIPTION", typeof(string)); tbl.Columns.Add("ID", typeof(long)); tbl.BeginLoadData(); using (SQLiteCommand cmd = new SQLiteCommand("PRAGMA database_list", this)) using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader()) { while (rd.Read()) { if (String.Compare(rd.GetString(1), strCatalog, StringComparison.OrdinalIgnoreCase) == 0 || strCatalog == null) { row = tbl.NewRow(); row["CATALOG_NAME"] = rd.GetString(1); row["DESCRIPTION"] = rd.GetString(2); row["ID"] = rd.GetInt64(0); tbl.Rows.Add(row); } } } tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; } private DataTable Schema_DataTypes() { DataTable tbl = new DataTable("DataTypes"); tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("TypeName", typeof(String)); tbl.Columns.Add("ProviderDbType", typeof(int)); tbl.Columns.Add("ColumnSize", typeof(long)); tbl.Columns.Add("CreateFormat", typeof(String)); tbl.Columns.Add("CreateParameters", typeof(String)); tbl.Columns.Add("DataType", typeof(String)); tbl.Columns.Add("IsAutoIncrementable", typeof(bool)); tbl.Columns.Add("IsBestMatch", typeof(bool)); tbl.Columns.Add("IsCaseSensitive", typeof(bool)); tbl.Columns.Add("IsFixedLength", typeof(bool)); tbl.Columns.Add("IsFixedPrecisionScale", typeof(bool)); tbl.Columns.Add("IsLong", typeof(bool)); tbl.Columns.Add("IsNullable", typeof(bool)); tbl.Columns.Add("IsSearchable", typeof(bool)); tbl.Columns.Add("IsSearchableWithLike", typeof(bool)); tbl.Columns.Add("IsLiteralSupported", typeof(bool)); tbl.Columns.Add("LiteralPrefix", typeof(String)); tbl.Columns.Add("LiteralSuffix", typeof(String)); tbl.Columns.Add("IsUnsigned", typeof(bool)); tbl.Columns.Add("MaximumScale", typeof(short)); tbl.Columns.Add("MinimumScale", typeof(short)); tbl.Columns.Add("IsConcurrencyType", typeof(bool)); tbl.BeginLoadData(); StringReader reader = new StringReader(SR.DataTypes); tbl.ReadXml(reader); reader.Close(); tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; } /// /// Returns the base column information for indexes in a database /// /// The catalog to retrieve indexes for (can be null) /// The table to restrict index information by (can be null) /// The index to restrict index information by (can be null) /// The source column to restrict index information by (can be null) /// A DataTable containing the results private DataTable Schema_IndexColumns(string strCatalog, string strTable, string strIndex, string strColumn) { DataTable tbl = new DataTable("IndexColumns"); DataRow row; List> primaryKeys = new List>(); bool maybeRowId; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string)); tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string)); tbl.Columns.Add("CONSTRAINT_NAME", typeof(string)); tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("COLUMN_NAME", typeof(string)); tbl.Columns.Add("ORDINAL_POSITION", typeof(int)); tbl.Columns.Add("INDEX_NAME", typeof(string)); tbl.Columns.Add("COLLATION_NAME", typeof(string)); tbl.Columns.Add("SORT_MODE", typeof(string)); tbl.Columns.Add("CONFLICT_OPTION", typeof(int)); if (String.IsNullOrEmpty(strCatalog)) strCatalog = GetDefaultCatalogName(); string master = GetMasterTableName(IsTemporaryCatalogName(strCatalog)); tbl.BeginLoadData(); using (SQLiteCommand cmdTables = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this)) using (SQLiteDataReader rdTables = cmdTables.ExecuteReader()) { while (rdTables.Read()) { maybeRowId = false; primaryKeys.Clear(); if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, StringComparison.OrdinalIgnoreCase) == 0) { try { using (SQLiteCommand cmdTable = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA [{0}].table_info([{1}])", strCatalog, rdTables.GetString(2)), this)) using (SQLiteDataReader rdTable = cmdTable.ExecuteReader()) { while (rdTable.Read()) { if (rdTable.GetInt32(5) == 1) // is a primary key { primaryKeys.Add(new KeyValuePair(rdTable.GetInt32(0), rdTable.GetString(1))); // Is an integer -- could be a rowid if no other primary keys exist in the table if (String.Compare(rdTable.GetString(2), "INTEGER", StringComparison.OrdinalIgnoreCase) == 0) maybeRowId = true; } } } } catch (SQLiteException) { } // This is a rowid row if (primaryKeys.Count == 1 && maybeRowId == true) { row = tbl.NewRow(); row["CONSTRAINT_CATALOG"] = strCatalog; row["CONSTRAINT_NAME"] = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "{1}_PK_{0}", rdTables.GetString(2), master); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = rdTables.GetString(2); row["COLUMN_NAME"] = primaryKeys[0].Value; row["INDEX_NAME"] = row["CONSTRAINT_NAME"]; row["ORDINAL_POSITION"] = 0; // primaryKeys[0].Key; row["COLLATION_NAME"] = "BINARY"; row["SORT_MODE"] = "ASC"; row["CONFLICT_OPTION"] = 2; if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, (string)row["INDEX_NAME"], StringComparison.OrdinalIgnoreCase) == 0) tbl.Rows.Add(row); } using (SQLiteCommand cmdIndexes = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{2}] WHERE [type] LIKE 'index' AND [tbl_name] LIKE '{1}'", strCatalog, rdTables.GetString(2).Replace("'", "''"), master), this)) using (SQLiteDataReader rdIndexes = cmdIndexes.ExecuteReader()) { while (rdIndexes.Read()) { int ordinal = 0; if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, rdIndexes.GetString(1), StringComparison.OrdinalIgnoreCase) == 0) { try { using (SQLiteCommand cmdIndex = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rdIndexes.GetString(1)), this)) using (SQLiteDataReader rdIndex = cmdIndex.ExecuteReader()) { while (rdIndex.Read()) { string columnName = rdIndex.IsDBNull(2) ? null : rdIndex.GetString(2); row = tbl.NewRow(); row["CONSTRAINT_CATALOG"] = strCatalog; row["CONSTRAINT_NAME"] = rdIndexes.GetString(1); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = rdIndexes.GetString(2); row["COLUMN_NAME"] = columnName; row["INDEX_NAME"] = rdIndexes.GetString(1); row["ORDINAL_POSITION"] = ordinal; // rdIndex.GetInt32(1); string collationSequence = null; int sortMode = 0; int onError = 0; if (columnName != null) _sql.GetIndexColumnExtendedInfo(strCatalog, rdIndexes.GetString(1), columnName, ref sortMode, ref onError, ref collationSequence); if (String.IsNullOrEmpty(collationSequence) == false) row["COLLATION_NAME"] = collationSequence; row["SORT_MODE"] = (sortMode == 0) ? "ASC" : "DESC"; row["CONFLICT_OPTION"] = onError; ordinal++; if ((strColumn == null) || String.Compare(strColumn, columnName, StringComparison.OrdinalIgnoreCase) == 0) tbl.Rows.Add(row); } } } catch (SQLiteException) { } } } } } } } tbl.EndLoadData(); tbl.AcceptChanges(); return tbl; } /// /// Returns detailed column information for a specified view /// /// The catalog to retrieve columns for (can be null) /// The view to restrict column information by (can be null) /// The source column to restrict column information by (can be null) /// A DataTable containing the results private DataTable Schema_ViewColumns(string strCatalog, string strView, string strColumn) { DataTable tbl = new DataTable("ViewColumns"); DataRow row; string strSql; int n; DataRow schemaRow; DataRow viewRow; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("VIEW_CATALOG", typeof(string)); tbl.Columns.Add("VIEW_SCHEMA", typeof(string)); tbl.Columns.Add("VIEW_NAME", typeof(string)); tbl.Columns.Add("VIEW_COLUMN_NAME", typeof(String)); tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("COLUMN_NAME", typeof(string)); tbl.Columns.Add("ORDINAL_POSITION", typeof(int)); tbl.Columns.Add("COLUMN_HASDEFAULT", typeof(bool)); tbl.Columns.Add("COLUMN_DEFAULT", typeof(string)); tbl.Columns.Add("COLUMN_FLAGS", typeof(long)); tbl.Columns.Add("IS_NULLABLE", typeof(bool)); tbl.Columns.Add("DATA_TYPE", typeof(string)); tbl.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int)); tbl.Columns.Add("NUMERIC_PRECISION", typeof(int)); tbl.Columns.Add("NUMERIC_SCALE", typeof(int)); tbl.Columns.Add("DATETIME_PRECISION", typeof(long)); tbl.Columns.Add("CHARACTER_SET_CATALOG", typeof(string)); tbl.Columns.Add("CHARACTER_SET_SCHEMA", typeof(string)); tbl.Columns.Add("CHARACTER_SET_NAME", typeof(string)); tbl.Columns.Add("COLLATION_CATALOG", typeof(string)); tbl.Columns.Add("COLLATION_SCHEMA", typeof(string)); tbl.Columns.Add("COLLATION_NAME", typeof(string)); tbl.Columns.Add("PRIMARY_KEY", typeof(bool)); tbl.Columns.Add("EDM_TYPE", typeof(string)); tbl.Columns.Add("AUTOINCREMENT", typeof(bool)); tbl.Columns.Add("UNIQUE", typeof(bool)); if (String.IsNullOrEmpty(strCatalog)) strCatalog = GetDefaultCatalogName(); string master = GetMasterTableName(IsTemporaryCatalogName(strCatalog)); tbl.BeginLoadData(); using (SQLiteCommand cmdViews = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'view'", strCatalog, master), this)) using (SQLiteDataReader rdViews = cmdViews.ExecuteReader()) { while (rdViews.Read()) { if (String.IsNullOrEmpty(strView) || String.Compare(strView, rdViews.GetString(2), StringComparison.OrdinalIgnoreCase) == 0) { using (SQLiteCommand cmdViewSelect = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdViews.GetString(2)), this)) { strSql = rdViews.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' '); n = CultureInfo.InvariantCulture.CompareInfo.IndexOf(strSql, " AS ", CompareOptions.IgnoreCase); if (n < 0) continue; strSql = strSql.Substring(n + 4); using (SQLiteCommand cmd = new SQLiteCommand(strSql, this)) using (SQLiteDataReader rdViewSelect = cmdViewSelect.ExecuteReader(CommandBehavior.SchemaOnly)) using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader(CommandBehavior.SchemaOnly)) using (DataTable tblSchemaView = rdViewSelect.GetSchemaTable(false, false)) using (DataTable tblSchema = rd.GetSchemaTable(false, false)) { for (n = 0; n < tblSchema.Rows.Count; n++) { viewRow = tblSchemaView.Rows[n]; schemaRow = tblSchema.Rows[n]; if (String.Compare(viewRow[SchemaTableColumn.ColumnName].ToString(), strColumn, StringComparison.OrdinalIgnoreCase) == 0 || strColumn == null) { row = tbl.NewRow(); row["VIEW_CATALOG"] = strCatalog; row["VIEW_NAME"] = rdViews.GetString(2); row["TABLE_CATALOG"] = strCatalog; row["TABLE_SCHEMA"] = schemaRow[SchemaTableColumn.BaseSchemaName]; row["TABLE_NAME"] = schemaRow[SchemaTableColumn.BaseTableName]; row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.BaseColumnName]; row["VIEW_COLUMN_NAME"] = viewRow[SchemaTableColumn.ColumnName]; row["COLUMN_HASDEFAULT"] = (viewRow[SchemaTableOptionalColumn.DefaultValue] != DBNull.Value); row["COLUMN_DEFAULT"] = viewRow[SchemaTableOptionalColumn.DefaultValue]; row["ORDINAL_POSITION"] = viewRow[SchemaTableColumn.ColumnOrdinal]; row["IS_NULLABLE"] = viewRow[SchemaTableColumn.AllowDBNull]; row["DATA_TYPE"] = viewRow["DataTypeName"]; // SQLiteConvert.DbTypeToType((DbType)viewRow[SchemaTableColumn.ProviderType]).ToString(); row["EDM_TYPE"] = SQLiteConvert.DbTypeToTypeName(this, (DbType)viewRow[SchemaTableColumn.ProviderType], _flags).ToString().ToLower(CultureInfo.InvariantCulture); row["CHARACTER_MAXIMUM_LENGTH"] = viewRow[SchemaTableColumn.ColumnSize]; row["TABLE_SCHEMA"] = viewRow[SchemaTableColumn.BaseSchemaName]; row["PRIMARY_KEY"] = viewRow[SchemaTableColumn.IsKey]; row["AUTOINCREMENT"] = viewRow[SchemaTableOptionalColumn.IsAutoIncrement]; row["COLLATION_NAME"] = viewRow["CollationType"]; row["UNIQUE"] = viewRow[SchemaTableColumn.IsUnique]; tbl.Rows.Add(row); } } } } } } } tbl.EndLoadData(); tbl.AcceptChanges(); return tbl; } /// /// Retrieves foreign key information from the specified set of filters /// /// An optional catalog to restrict results on /// An optional table to restrict results on /// An optional foreign key name to restrict results on /// A DataTable with the results of the query private DataTable Schema_ForeignKeys(string strCatalog, string strTable, string strKeyName) { DataTable tbl = new DataTable("ForeignKeys"); DataRow row; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string)); tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string)); tbl.Columns.Add("CONSTRAINT_NAME", typeof(string)); tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("CONSTRAINT_TYPE", typeof(string)); tbl.Columns.Add("IS_DEFERRABLE", typeof(bool)); tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool)); tbl.Columns.Add("FKEY_ID", typeof(int)); tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string)); tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int)); tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string)); tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string)); tbl.Columns.Add("FKEY_TO_TABLE", typeof(string)); tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string)); tbl.Columns.Add("FKEY_ON_UPDATE", typeof(string)); tbl.Columns.Add("FKEY_ON_DELETE", typeof(string)); tbl.Columns.Add("FKEY_MATCH", typeof(string)); if (String.IsNullOrEmpty(strCatalog)) strCatalog = GetDefaultCatalogName(); string master = GetMasterTableName(IsTemporaryCatalogName(strCatalog)); tbl.BeginLoadData(); using (SQLiteCommand cmdTables = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this)) using (SQLiteDataReader rdTables = cmdTables.ExecuteReader()) { while (rdTables.Read()) { if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), StringComparison.OrdinalIgnoreCase) == 0) { try { using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder()) using (SQLiteCommand cmdKey = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this)) using (SQLiteDataReader rdKey = cmdKey.ExecuteReader()) { while (rdKey.Read()) { row = tbl.NewRow(); row["CONSTRAINT_CATALOG"] = strCatalog; row["CONSTRAINT_NAME"] = HelperMethods.StringFormat(CultureInfo.InvariantCulture, "FK_{0}_{1}_{2}", rdTables[2], rdKey.GetInt32(0), rdKey.GetInt32(1)); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = builder.UnquoteIdentifier(rdTables.GetString(2)); row["CONSTRAINT_TYPE"] = "FOREIGN KEY"; row["IS_DEFERRABLE"] = false; row["INITIALLY_DEFERRED"] = false; row["FKEY_ID"] = rdKey[0]; row["FKEY_FROM_COLUMN"] = builder.UnquoteIdentifier(rdKey[3].ToString()); row["FKEY_TO_CATALOG"] = strCatalog; row["FKEY_TO_TABLE"] = builder.UnquoteIdentifier(rdKey[2].ToString()); row["FKEY_TO_COLUMN"] = builder.UnquoteIdentifier(rdKey[4].ToString()); row["FKEY_FROM_ORDINAL_POSITION"] = rdKey[1]; row["FKEY_ON_UPDATE"] = (rdKey.FieldCount > 5) ? rdKey[5] : String.Empty; row["FKEY_ON_DELETE"] = (rdKey.FieldCount > 6) ? rdKey[6] : String.Empty; row["FKEY_MATCH"] = (rdKey.FieldCount > 7) ? rdKey[7] : String.Empty; if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), StringComparison.OrdinalIgnoreCase) == 0) tbl.Rows.Add(row); } } } catch (SQLiteException) { } } } } tbl.EndLoadData(); tbl.AcceptChanges(); return tbl; } /// /// This event is raised periodically during long running queries. Changing /// the value of the property will /// determine if the operation in progress will continue or be interrupted. /// For the entire duration of the event, the associated connection and /// statement objects must not be modified, either directly or indirectly, by /// the called code. /// public event SQLiteProgressEventHandler Progress { add { CheckDisposed(); if (_progressHandler == null) { _progressCallback = new SQLiteProgressCallback(ProgressCallback); if (_sql != null) _sql.SetProgressHook(_progressOps, _progressCallback); } _progressHandler += value; } remove { CheckDisposed(); _progressHandler -= value; if (_progressHandler == null) { if (_sql != null) _sql.SetProgressHook(0, null); _progressCallback = null; } } } /// /// This event is raised whenever SQLite encounters an action covered by the /// authorizer during query preparation. Changing the value of the /// property will determine if /// the specific action will be allowed, ignored, or denied. For the entire /// duration of the event, the associated connection and statement objects /// must not be modified, either directly or indirectly, by the called code. /// public event SQLiteAuthorizerEventHandler Authorize { add { CheckDisposed(); if (_authorizerHandler == null) { _authorizerCallback = new SQLiteAuthorizerCallback(AuthorizerCallback); if (_sql != null) _sql.SetAuthorizerHook(_authorizerCallback); } _authorizerHandler += value; } remove { CheckDisposed(); _authorizerHandler -= value; if (_authorizerHandler == null) { if (_sql != null) _sql.SetAuthorizerHook(null); _authorizerCallback = null; } } } /// /// This event is raised whenever SQLite makes an update/delete/insert into the database on /// this connection. It only applies to the given connection. /// public event SQLiteUpdateEventHandler Update { add { CheckDisposed(); if (_updateHandler == null) { _updateCallback = new SQLiteUpdateCallback(UpdateCallback); if (_sql != null) _sql.SetUpdateHook(_updateCallback); } _updateHandler += value; } remove { CheckDisposed(); _updateHandler -= value; if (_updateHandler == null) { if (_sql != null) _sql.SetUpdateHook(null); _updateCallback = null; } } } private SQLiteProgressReturnCode ProgressCallback( IntPtr pUserData /* NOT USED: Always IntPtr.Zero. */ ) { try { ProgressEventArgs eventArgs = new ProgressEventArgs( pUserData, SQLiteProgressReturnCode.Continue); if (_progressHandler != null) _progressHandler(this, eventArgs); return eventArgs.ReturnCode; } catch (Exception e) /* NOTE: Must catch ALL. */ { try { if (HelperMethods.LogCallbackExceptions(_flags)) { SQLiteLog.LogMessage(SQLiteBase.COR_E_EXCEPTION, HelperMethods.StringFormat(CultureInfo.CurrentCulture, UnsafeNativeMethods.ExceptionMessageFormat, "Progress", e)); /* throw */ } } catch { // do nothing. } } // // NOTE: Should throwing an exception interrupt the operation? // if (HelperMethods.HasFlags( _flags, SQLiteConnectionFlags.InterruptOnException)) { return SQLiteProgressReturnCode.Interrupt; } else { return SQLiteProgressReturnCode.Continue; } } private SQLiteAuthorizerReturnCode AuthorizerCallback( IntPtr pUserData, /* NOT USED: Always IntPtr.Zero. */ SQLiteAuthorizerActionCode actionCode, IntPtr pArgument1, IntPtr pArgument2, IntPtr pDatabase, IntPtr pAuthContext) { try { AuthorizerEventArgs eventArgs = new AuthorizerEventArgs(pUserData, actionCode, SQLiteBase.UTF8ToString(pArgument1, -1), SQLiteBase.UTF8ToString(pArgument2, -1), SQLiteBase.UTF8ToString(pDatabase, -1), SQLiteBase.UTF8ToString(pAuthContext, -1), SQLiteAuthorizerReturnCode.Ok); if (_authorizerHandler != null) _authorizerHandler(this, eventArgs); return eventArgs.ReturnCode; } catch (Exception e) /* NOTE: Must catch ALL. */ { try { if (HelperMethods.LogCallbackExceptions(_flags)) { SQLiteLog.LogMessage(SQLiteBase.COR_E_EXCEPTION, HelperMethods.StringFormat(CultureInfo.CurrentCulture, UnsafeNativeMethods.ExceptionMessageFormat, "Authorize", e)); /* throw */ } } catch { // do nothing. } } // // NOTE: Should throwing an exception deny the action? // if (HelperMethods.HasFlags( _flags, SQLiteConnectionFlags.DenyOnException)) { return SQLiteAuthorizerReturnCode.Deny; } else { return SQLiteAuthorizerReturnCode.Ok; } } private void UpdateCallback( IntPtr puser, /* NOT USED */ int type, IntPtr database, IntPtr table, Int64 rowid ) { try { _updateHandler(this, new UpdateEventArgs( SQLiteBase.UTF8ToString(database, -1), SQLiteBase.UTF8ToString(table, -1), (UpdateEventType)type, rowid)); } catch (Exception e) /* NOTE: Must catch ALL. */ { try { if (HelperMethods.LogCallbackExceptions(_flags)) { SQLiteLog.LogMessage(SQLiteBase.COR_E_EXCEPTION, HelperMethods.StringFormat(CultureInfo.CurrentCulture, UnsafeNativeMethods.ExceptionMessageFormat, "Update", e)); /* throw */ } } catch { // do nothing. } } } /// /// This event is raised whenever SQLite is committing a transaction. /// Return non-zero to trigger a rollback. /// public event SQLiteCommitHandler Commit { add { CheckDisposed(); if (_commitHandler == null) { _commitCallback = new SQLiteCommitCallback(CommitCallback); if (_sql != null) _sql.SetCommitHook(_commitCallback); } _commitHandler += value; } remove { CheckDisposed(); _commitHandler -= value; if (_commitHandler == null) { if (_sql != null) _sql.SetCommitHook(null); _commitCallback = null; } } } /// /// This event is raised whenever SQLite statement first begins executing on /// this connection. It only applies to the given connection. /// public event SQLiteTraceEventHandler Trace { add { CheckDisposed(); if (_traceHandler == null) { _traceCallback = new SQLiteTraceCallback(TraceCallback); if (_sql != null) _sql.SetTraceCallback(_traceCallback); } _traceHandler += value; } remove { CheckDisposed(); _traceHandler -= value; if (_traceHandler == null) { if (_sql != null) _sql.SetTraceCallback(null); _traceCallback = null; } } } private void TraceCallback( IntPtr puser, /* NOT USED */ IntPtr statement ) { try { if (_traceHandler != null) _traceHandler(this, new TraceEventArgs( SQLiteBase.UTF8ToString(statement, -1))); } catch (Exception e) /* NOTE: Must catch ALL. */ { try { if (HelperMethods.LogCallbackExceptions(_flags)) { SQLiteLog.LogMessage(SQLiteBase.COR_E_EXCEPTION, HelperMethods.StringFormat(CultureInfo.CurrentCulture, UnsafeNativeMethods.ExceptionMessageFormat, "Trace", e)); /* throw */ } } catch { // do nothing. } } } /// /// This event is raised whenever SQLite is rolling back a transaction. /// public event EventHandler RollBack { add { CheckDisposed(); if (_rollbackHandler == null) { _rollbackCallback = new SQLiteRollbackCallback(RollbackCallback); if (_sql != null) _sql.SetRollbackHook(_rollbackCallback); } _rollbackHandler += value; } remove { CheckDisposed(); _rollbackHandler -= value; if (_rollbackHandler == null) { if (_sql != null) _sql.SetRollbackHook(null); _rollbackCallback = null; } } } private int CommitCallback( IntPtr parg /* NOT USED */ ) { try { CommitEventArgs e = new CommitEventArgs(); if (_commitHandler != null) _commitHandler(this, e); return (e.AbortTransaction == true) ? 1 : 0; } catch (Exception e) /* NOTE: Must catch ALL. */ { try { if (HelperMethods.LogCallbackExceptions(_flags)) { SQLiteLog.LogMessage(SQLiteBase.COR_E_EXCEPTION, HelperMethods.StringFormat(CultureInfo.CurrentCulture, UnsafeNativeMethods.ExceptionMessageFormat, "Commit", e)); /* throw */ } } catch { // do nothing. } } // // NOTE: Should throwing an exception rollback the transaction? // if (HelperMethods.HasFlags( _flags, SQLiteConnectionFlags.RollbackOnException)) { return 1; // rollback } else { return 0; // commit } } private void RollbackCallback( IntPtr parg /* NOT USED */ ) { try { if (_rollbackHandler != null) _rollbackHandler(this, EventArgs.Empty); } catch (Exception e) /* NOTE: Must catch ALL. */ { try { if (HelperMethods.LogCallbackExceptions(_flags)) { SQLiteLog.LogMessage(SQLiteBase.COR_E_EXCEPTION, HelperMethods.StringFormat(CultureInfo.CurrentCulture, UnsafeNativeMethods.ExceptionMessageFormat, "Rollback", e)); /* throw */ } } catch { // do nothing. } } } } /// /// The I/O file cache flushing behavior for the connection /// public enum SynchronizationModes { /// /// Normal file flushing at critical sections of the code /// Normal = 0, /// /// Full file flushing after every write operation /// Full = 1, /// /// Use the default operating system's file flushing, SQLite does not explicitly flush the file buffers after writing /// Off = 2, } #if !PLATFORM_COMPACTFRAMEWORK [UnmanagedFunctionPointer(CallingConvention.Cdecl)] #endif internal delegate SQLiteProgressReturnCode SQLiteProgressCallback(IntPtr pUserData); #if !PLATFORM_COMPACTFRAMEWORK [UnmanagedFunctionPointer(CallingConvention.Cdecl)] #endif internal delegate SQLiteAuthorizerReturnCode SQLiteAuthorizerCallback( IntPtr pUserData, SQLiteAuthorizerActionCode actionCode, IntPtr pArgument1, IntPtr pArgument2, IntPtr pDatabase, IntPtr pAuthContext ); #if !PLATFORM_COMPACTFRAMEWORK [UnmanagedFunctionPointer(CallingConvention.Cdecl)] #endif internal delegate void SQLiteUpdateCallback(IntPtr puser, int type, IntPtr database, IntPtr table, Int64 rowid); #if !PLATFORM_COMPACTFRAMEWORK [UnmanagedFunctionPointer(CallingConvention.Cdecl)] #endif internal delegate int SQLiteCommitCallback(IntPtr puser); #if !PLATFORM_COMPACTFRAMEWORK [UnmanagedFunctionPointer(CallingConvention.Cdecl)] #endif internal delegate void SQLiteTraceCallback(IntPtr puser, IntPtr statement); #if !PLATFORM_COMPACTFRAMEWORK [UnmanagedFunctionPointer(CallingConvention.Cdecl)] #endif internal delegate void SQLiteTraceCallback2(SQLiteTraceFlags type, IntPtr puser, IntPtr pCtx1, IntPtr pCtx2); #if !PLATFORM_COMPACTFRAMEWORK [UnmanagedFunctionPointer(CallingConvention.Cdecl)] #endif internal delegate void SQLiteRollbackCallback(IntPtr puser); /// /// Raised each time the number of virtual machine instructions is /// approximately equal to the value of the /// property. /// /// The connection performing the operation. /// A that contains the /// event data. public delegate void SQLiteProgressEventHandler(object sender, ProgressEventArgs e); /// /// Raised when authorization is required to perform an action contained /// within a SQL query. /// /// The connection performing the action. /// A that contains the /// event data. public delegate void SQLiteAuthorizerEventHandler(object sender, AuthorizerEventArgs e); /// /// Raised when a transaction is about to be committed. To roll back a transaction, set the /// rollbackTrans boolean value to true. /// /// The connection committing the transaction /// Event arguments on the transaction public delegate void SQLiteCommitHandler(object sender, CommitEventArgs e); /// /// Raised when data is inserted, updated and deleted on a given connection /// /// The connection committing the transaction /// The event parameters which triggered the event public delegate void SQLiteUpdateEventHandler(object sender, UpdateEventArgs e); /// /// Raised when a statement first begins executing on a given connection /// /// The connection executing the statement /// Event arguments of the trace public delegate void SQLiteTraceEventHandler(object sender, TraceEventArgs e); /////////////////////////////////////////////////////////////////////////////////////////////// #region Backup API Members /// /// Raised between each backup step. /// /// /// The source database connection. /// /// /// The source database name. /// /// /// The destination database connection. /// /// /// The destination database name. /// /// /// The number of pages copied with each step. /// /// /// The number of pages remaining to be copied. /// /// /// The total number of pages in the source database. /// /// /// Set to true if the operation needs to be retried due to database /// locking issues; otherwise, set to false. /// /// /// True to continue with the backup process or false to halt the backup /// process, rolling back any changes that have been made so far. /// public delegate bool SQLiteBackupCallback( SQLiteConnection source, string sourceName, SQLiteConnection destination, string destinationName, int pages, int remainingPages, int totalPages, bool retry ); #endregion /////////////////////////////////////////////////////////////////////////////////////////////// /// /// The event data associated with progress reporting events. /// public class ProgressEventArgs : EventArgs { /// /// The user-defined native data associated with this event. Currently, /// this will always contain the value of . /// public readonly IntPtr UserData; /// /// The return code for the current call into the progress callback. /// public SQLiteProgressReturnCode ReturnCode; /// /// Constructs an instance of this class with default property values. /// private ProgressEventArgs() { this.UserData = IntPtr.Zero; this.ReturnCode = SQLiteProgressReturnCode.Continue; } /// /// Constructs an instance of this class with specific property values. /// /// /// The user-defined native data associated with this event. /// /// /// The progress return code. /// internal ProgressEventArgs( IntPtr pUserData, SQLiteProgressReturnCode returnCode ) : this() { this.UserData = pUserData; this.ReturnCode = returnCode; } } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// The data associated with a call into the authorizer. /// public class AuthorizerEventArgs : EventArgs { /// /// The user-defined native data associated with this event. Currently, /// this will always contain the value of . /// public readonly IntPtr UserData; /// /// The action code responsible for the current call into the authorizer. /// public readonly SQLiteAuthorizerActionCode ActionCode; /// /// The first string argument for the current call into the authorizer. /// The exact value will vary based on the action code, see the /// enumeration for possible /// values. /// public readonly string Argument1; /// /// The second string argument for the current call into the authorizer. /// The exact value will vary based on the action code, see the /// enumeration for possible /// values. /// public readonly string Argument2; /// /// The database name for the current call into the authorizer, if /// applicable. /// public readonly string Database; /// /// The name of the inner-most trigger or view that is responsible for /// the access attempt or a null value if this access attempt is directly /// from top-level SQL code. /// public readonly string Context; /// /// The return code for the current call into the authorizer. /// public SQLiteAuthorizerReturnCode ReturnCode; /////////////////////////////////////////////////////////////////////////////////////////// /// /// Constructs an instance of this class with default property values. /// private AuthorizerEventArgs() { this.UserData = IntPtr.Zero; this.ActionCode = SQLiteAuthorizerActionCode.None; this.Argument1 = null; this.Argument2 = null; this.Database = null; this.Context = null; this.ReturnCode = SQLiteAuthorizerReturnCode.Ok; } /////////////////////////////////////////////////////////////////////////////////////////// /// /// Constructs an instance of this class with specific property values. /// /// /// The user-defined native data associated with this event. /// /// /// The authorizer action code. /// /// /// The first authorizer argument. /// /// /// The second authorizer argument. /// /// /// The database name, if applicable. /// /// /// The name of the inner-most trigger or view that is responsible for /// the access attempt or a null value if this access attempt is directly /// from top-level SQL code. /// /// /// The authorizer return code. /// internal AuthorizerEventArgs( IntPtr pUserData, SQLiteAuthorizerActionCode actionCode, string argument1, string argument2, string database, string context, SQLiteAuthorizerReturnCode returnCode ) : this() { this.UserData = pUserData; this.ActionCode = actionCode; this.Argument1 = argument1; this.Argument2 = argument2; this.Database = database; this.Context = context; this.ReturnCode = returnCode; } } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Whenever an update event is triggered on a connection, this enum will indicate /// exactly what type of operation is being performed. /// public enum UpdateEventType { /// /// A row is being deleted from the given database and table /// Delete = 9, /// /// A row is being inserted into the table. /// Insert = 18, /// /// A row is being updated in the table. /// Update = 23, } /// /// Passed during an Update callback, these event arguments detail the type of update operation being performed /// on the given connection. /// public class UpdateEventArgs : EventArgs { /// /// The name of the database being updated (usually "main" but can be any attached or temporary database) /// public readonly string Database; /// /// The name of the table being updated /// public readonly string Table; /// /// The type of update being performed (insert/update/delete) /// public readonly UpdateEventType Event; /// /// The RowId affected by this update. /// public readonly Int64 RowId; internal UpdateEventArgs(string database, string table, UpdateEventType eventType, Int64 rowid) { Database = database; Table = table; Event = eventType; RowId = rowid; } } /// /// Event arguments raised when a transaction is being committed /// public class CommitEventArgs : EventArgs { internal CommitEventArgs() { } /// /// Set to true to abort the transaction and trigger a rollback /// public bool AbortTransaction; } /// /// Passed during an Trace callback, these event arguments contain the UTF-8 rendering of the SQL statement text /// public class TraceEventArgs : EventArgs { /// /// SQL statement text as the statement first begins executing /// public readonly string Statement; internal TraceEventArgs(string statement) { Statement = statement; } } }