/******************************************************** * 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.Runtime.InteropServices; using System.IO; ///////////////////////////////////////////////////////////////////////////////////////////////// /// /// 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; /// /// 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 command or message text, if any. /// The extra data, if any. internal ConnectionEventArgs( SQLiteConnectionEventType eventType, StateChangeEventArgs eventArgs, IDbTransaction transaction, IDbCommand command, string text, object data ) { EventType = eventType; EventArgs = eventArgs; Transaction = transaction; Command = command; 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). /// Y /// /// /// /// Version /// 3 /// N /// 3 /// /// /// UseUTF16Encoding /// True
False /// N /// False /// /// /// 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 /// /// /// 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 /// {size in bytes} /// 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 /// 1024 /// /// /// Password /// {password} - Using this parameter requires that the CryptoAPI based codec 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 /// 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 /// /// /// 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 /// 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 /// /// /// public sealed partial class SQLiteConnection : DbConnection, ICloneable { #region Private Constants /// /// 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:"; private const SQLiteConnectionFlags DefaultFlags = SQLiteConnectionFlags.Default; private const SQLiteSynchronousEnum DefaultSynchronous = SQLiteSynchronousEnum.Default; private const SQLiteJournalModeEnum DefaultJournalMode = SQLiteJournalModeEnum.Default; private const IsolationLevel DefaultIsolationLevel = IsolationLevel.Serializable; private const SQLiteDateFormats DefaultDateTimeFormat = SQLiteDateFormats.ISO8601; private const DateTimeKind DefaultDateTimeKind = DateTimeKind.Unspecified; private const string DefaultDataSource = null; private const string DefaultUri = null; private const string DefaultFullUri = null; private const string DefaultPassword = null; private const int DefaultVersion = 3; private const int DefaultPageSize = 1024; private const int DefaultMaxPageCount = 0; private const int DefaultCacheSize = 2000; private const int DefaultMaxPoolSize = 100; private const int DefaultConnectionTimeout = 30; private const bool DefaultFailIfMissing = false; private const bool DefaultReadOnly = false; private const bool DefaultBinaryGUID = true; private const bool DefaultUseUTF16Encoding = false; private const bool DefaultToFullPath = true; private const bool DefaultPooling = false; private const bool DefaultLegacyFormat = false; private const bool DefaultForeignKeys = false; private const bool DefaultEnlist = true; private const bool DefaultSetDefaults = true; private const int SQLITE_FCNTL_WIN32_AV_RETRY = 9; private const string _dataDirectory = "|DataDirectory|"; private const string _masterdb = "sqlite_master"; private const string _tempmasterdb = "sqlite_temp_master"; #endregion /////////////////////////////////////////////////////////////////////////////////////////////// #region Private Static Data /// /// Object used to synchronize access to the static instance data /// for this class. /// private static object _syncRoot = new object(); /// /// Static variable to store the connection event handlers to call. /// private static event SQLiteConnectionEventHandler _handlers; #endregion /////////////////////////////////////////////////////////////////////////////////////////////// #region Private Data /// /// State of the current connection /// private ConnectionState _connectionState; /// /// The connection string /// private string _connectionString; /// /// Nesting level of the transactions open on the connection /// internal int _transactionLevel; /// /// The default isolation level for new transactions /// private IsolationLevel _defaultIsolation; #if !PLATFORM_COMPACTFRAMEWORK /// /// Whether or not the connection is enlisted in a distrubuted transaction /// internal SQLiteEnlistment _enlistment; #endif /// /// The base SQLite object to interop with /// internal SQLiteBase _sql; /// /// The database filename minus path and extension /// private string _dataSource; #if INTEROP_CODEC /// /// 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; /// /// Default command timeout /// private int _defaultTimeout = 30; internal bool _binaryGuid; internal long _version; private event SQLiteUpdateEventHandler _updateHandler; private event SQLiteCommitHandler _commitHandler; private event SQLiteTraceEventHandler _traceHandler; private event EventHandler _rollbackHandler; private SQLiteUpdateCallback _updateCallback; private SQLiteCommitCallback _commitCallback; private SQLiteTraceCallback _traceCallback; private SQLiteRollbackCallback _rollbackCallback; #endregion /////////////////////////////////////////////////////////////////////////////////////////////// /// /// 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("") { } /// /// Initializes the connection with the specified connection string /// /// The connection string to use on the connection public SQLiteConnection(string connectionString) { #if (SQLITE_STANDARD || USE_INTEROP_DLL || PLATFORM_COMPACTFRAMEWORK) && PRELOAD_NATIVE_LIBRARY UnsafeNativeMethods.Initialize(); #endif #if !PLATFORM_COMPACTFRAMEWORK SQLiteLog.Initialize(); #endif _flags = SQLiteConnectionFlags.Default; _connectionState = ConnectionState.Closed; _connectionString = ""; //_commandList = new List(); 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. /// /// public SQLiteConnection(SQLiteConnection connection) : this(connection.ConnectionString) { string str; 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) { str = row[0].ToString(); if (String.Compare(str, "main", StringComparison.OrdinalIgnoreCase) != 0 && String.Compare(str, "temp", StringComparison.OrdinalIgnoreCase) != 0) { using (SQLiteCommand cmd = CreateCommand()) { cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "ATTACH DATABASE '{0}' AS [{1}]", row[1], row[0]); cmd.ExecuteNonQuery(); } } } } } } /////////////////////////////////////////////////////////////////////////////////////////////// /// /// Raises the event. /// /// /// The connection associated with this event. /// /// /// A that contains the event data. /// internal static void OnChanged( SQLiteConnection connection, ConnectionEventArgs e ) { if (connection == null) return; #if !PLATFORM_COMPACTFRAMEWORK if (!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; } } } /////////////////////////////////////////////////////////////////////////////////////////////// #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 or negative to copy all remaining pages. /// /// /// The method to invoke between each step of the backup process. This /// parameter may be null (i.e. no callbacks will be performed). /// /// /// 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; while (sqliteBase.StepBackup(backup, pages, out 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)) System.Threading.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; } } #if !PLATFORM_COMPACTFRAMEWORK catch (Exception e) { if ((_flags & SQLiteConnectionFlags.LogBackup) == SQLiteConnectionFlags.LogBackup) { SQLiteLog.LogMessage(String.Format( CultureInfo.CurrentCulture, "Caught exception while backing up database: {0}", e)); } throw; } #endif finally { if (backup != null) sqliteBase.FinishBackup(backup); /* throw */ } } #endregion /////////////////////////////////////////////////////////////////////////////////////////////// [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."); } /////////////////////////////////////////////////////////////////////////////////////////////// #region IDisposable "Pattern" Members private bool disposed; private void CheckDisposed() /* throw */ { #if THROW_ON_DISPOSED if (disposed) throw new ObjectDisposedException(typeof(SQLiteConnection).Name); #endif } /////////////////////////////////////////////////////////////////////////////////////////////// protected override void Dispose(bool disposing) { try { if (!disposed) { //if (disposing) //{ // //////////////////////////////////// // // dispose managed resources here... // //////////////////////////////////// //} ////////////////////////////////////// // release unmanaged resources here... ////////////////////////////////////// Close(); disposed = true; } } finally { base.Dispose(disposing); } } #endregion /////////////////////////////////////////////////////////////////////////////////////////////// #if PLATFORM_COMPACTFRAMEWORK /// /// Obsolete /// public override int ConnectionTimeout { get { CheckDisposed(); return 30; } } #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; } } /// /// 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 ? IsolationLevel.Serializable : IsolationLevel.ReadCommitted); } /// /// 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 ? IsolationLevel.Serializable : IsolationLevel.ReadCommitted); } /// /// 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; if (isolationLevel != IsolationLevel.Serializable && isolationLevel != IsolationLevel.ReadCommitted) throw new ArgumentException("isolationLevel"); SQLiteTransaction transaction = new SQLiteTransaction(this, isolationLevel != IsolationLevel.Serializable); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.NewTransaction, null, transaction, 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, 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)); if (_sql != null) { #if !PLATFORM_COMPACTFRAMEWORK 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(); cnn._sql = _sql; cnn._transactionLevel = _transactionLevel; cnn._enlistment = _enlistment; cnn._connectionState = _connectionState; cnn._version = _version; cnn._enlistment._transaction._cnn = cnn; cnn._enlistment._disposeConnection = true; _sql = null; _enlistment = null; } #endif if (_sql != null) { _sql.Close(); _sql = null; } _transactionLevel = 0; } StateChangeEventArgs eventArgs = null; OnStateChange(ConnectionState.Closed, ref eventArgs); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.Closed, eventArgs, 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 /// /// /// /// /// 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). /// Y /// /// /// /// Version /// 3 /// N /// 3 /// /// /// UseUTF16Encoding /// True
False /// N /// False /// /// /// 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 /// /// /// 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 /// {size in bytes} /// 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 /// 1024 /// /// /// Password /// {password} - Using this parameter requires that the CryptoAPI based codec 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 /// 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 /// /// /// 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 /// 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 /// /// /// #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(); } /// /// 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 string "main". /// #if !PLATFORM_COMPACTFRAMEWORK [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public override string Database { get { CheckDisposed(); return "main"; } } 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"); } /// /// Parses the connection string into component parts /// /// The connection string to parse /// An array of key-value pairs representing each parameter of the connection string internal static SortedList ParseConnectionString(string connectionString) { string s = connectionString; int n; SortedList ls = new SortedList(StringComparer.OrdinalIgnoreCase); // First split into semi-colon delimited values. The Split() function of SQLiteBase accounts for and properly // skips semi-colons in quoted strings string[] arParts = SQLiteConvert.Split(s, ';'); int x = arParts.Length; // For each semi-colon piece, split into key and value pairs by the presence of the = sign for (n = 0; n < x; n++) { int indexOf = arParts[n].IndexOf('='); if (indexOf != -1) ls.Add(arParts[n].Substring(0, indexOf), arParts[n].Substring(indexOf + 1)); else throw new ArgumentException(String.Format(CultureInfo.CurrentCulture, "Invalid ConnectionString format for part \"{0}\"", arParts[n])); } return ls; } #if !PLATFORM_COMPACTFRAMEWORK /// /// Manual distributed transaction enlistment support /// /// The distributed transaction to enlist in public override void EnlistTransaction(System.Transactions.Transaction transaction) { CheckDisposed(); 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"); _enlistment = new SQLiteEnlistment(this, transaction); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.EnlistTransaction, null, null, null, null, _enlistment)); } #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 (items.TryGetValue(key, 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. private static object TryParseEnum( Type type, string value, bool ignoreCase ) { try { return Enum.Parse(type, value, ignoreCase); } catch { // do nothing. } return null; } /// /// Enables or disabled extension loading. /// /// /// True to enable loading of extensions, false to disable. /// public void EnableExtensions( bool enable ) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException(String.Format( "Database connection not valid for {0} extensions.", enable ? "enabling" : "disabling")); _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."); _sql.LoadExtension(fileName, procName); } /// /// Opens the connection using the parameters found in the . /// public override void Open() { CheckDisposed(); OnChanged(this, new ConnectionEventArgs( SQLiteConnectionEventType.Opening, null, null, null, null, null)); if (_connectionState != ConnectionState.Closed) throw new InvalidOperationException(); Close(); SortedList opts = ParseConnectionString(_connectionString); object enumValue; enumValue = TryParseEnum(typeof(SQLiteConnectionFlags), FindKey(opts, "Flags", DefaultFlags.ToString()), true); _flags = (enumValue is SQLiteConnectionFlags) ? (SQLiteConnectionFlags)enumValue : DefaultFlags; bool fullUri = false; string fileName; if (Convert.ToInt32(FindKey(opts, "Version", DefaultVersion.ToString()), CultureInfo.InvariantCulture) != DefaultVersion) throw new NotSupportedException(String.Format(CultureInfo.CurrentCulture, "Only SQLite Version {0} is supported at this time", DefaultVersion)); fileName = FindKey(opts, "Data Source", DefaultDataSource); fileName = UnwrapFileName(fileName); 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(String.Format(CultureInfo.CurrentCulture, "Data Source cannot be empty. Use {0} to open an in-memory database", MemoryFileName)); else fullUri = true; } else fileName = MapUriPath(fileName); } bool isMemory = (String.Compare(fileName, MemoryFileName, StringComparison.OrdinalIgnoreCase) == 0); if (!fullUri) { if (isMemory) fileName = MemoryFileName; else { #if PLATFORM_COMPACTFRAMEWORK if (fileName.StartsWith("./") || fileName.StartsWith(".\\")) fileName = Path.GetDirectoryName(System.Reflection.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", DefaultPooling.ToString())); int maxPoolSize = Convert.ToInt32(FindKey(opts, "Max Pool Size", DefaultMaxPoolSize.ToString()), CultureInfo.InvariantCulture); _defaultTimeout = Convert.ToInt32(FindKey(opts, "Default Timeout", DefaultConnectionTimeout.ToString()), CultureInfo.InvariantCulture); enumValue = TryParseEnum(typeof(IsolationLevel), FindKey(opts, "Default IsolationLevel", DefaultIsolationLevel.ToString()), true); _defaultIsolation = (enumValue is IsolationLevel) ? (IsolationLevel)enumValue : DefaultIsolationLevel; if (_defaultIsolation != IsolationLevel.Serializable && _defaultIsolation != IsolationLevel.ReadCommitted) throw new NotSupportedException("Invalid Default IsolationLevel specified"); _baseSchemaName = FindKey(opts, "BaseSchemaName", DefaultBaseSchemaName); if (_sql == null) { 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; // // 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); } else { _sql = new SQLite3(dateFormat, kind); } } 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, _flags, flags, maxPoolSize, usePooling); _binaryGuid = SQLiteConvert.ToBoolean(FindKey(opts, "BinaryGUID", DefaultBinaryGUID.ToString())); #if INTEROP_CODEC string password = FindKey(opts, "Password", DefaultPassword); if (!String.IsNullOrEmpty(password)) _sql.SetPassword(System.Text.UTF8Encoding.UTF8.GetBytes(password)); else if (_password != null) _sql.SetPassword(_password); _password = null; #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()) { int intValue; if (!fullUri && !isMemory) { strValue = FindKey(opts, "Page Size", DefaultPageSize.ToString()); intValue = Convert.ToInt32(strValue, CultureInfo.InvariantCulture); if (intValue != DefaultPageSize) { cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA page_size={0}", intValue); cmd.ExecuteNonQuery(); } } strValue = FindKey(opts, "Max Page Count", DefaultMaxPageCount.ToString()); intValue = Convert.ToInt32(strValue, CultureInfo.InvariantCulture); if (intValue != DefaultMaxPageCount) { cmd.CommandText = String.Format(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 = String.Format(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 = String.Format(CultureInfo.InvariantCulture, "PRAGMA synchronous={0}", strValue); cmd.ExecuteNonQuery(); } strValue = FindKey(opts, "Cache Size", DefaultCacheSize.ToString()); intValue = Convert.ToInt32(strValue, CultureInfo.InvariantCulture); if (intValue != DefaultCacheSize) { cmd.CommandText = String.Format(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)) { cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA journal_mode={0}", strValue); cmd.ExecuteNonQuery(); } strValue = FindKey(opts, "Foreign Keys", DefaultForeignKeys.ToString()); boolValue = Convert.ToBoolean(strValue, CultureInfo.InvariantCulture); if (boolValue != DefaultForeignKeys) { cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA foreign_keys={0}", boolValue ? "ON" : "OFF"); cmd.ExecuteNonQuery(); } } } 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)); } 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 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; } } /// /// 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; } } /// /// 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; } } /// /// 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; } } /// /// 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 database engine /// 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 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 off to SQLite. public SQLiteErrorCode Shutdown() { CheckDisposed(); // make sure we have an instance of the base class if (_sql == null) { SortedList opts = ParseConnectionString(_connectionString); 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; // // 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); } else { _sql = new SQLite3(dateFormat, kind); } } if (_sql != null) return _sql.Shutdown(); throw new InvalidOperationException("Database connection not active."); } /// Enables or disabled extended result codes returned by SQLite public void SetExtendedResultCodes(bool bOnOff) { CheckDisposed(); if (_sql != null) _sql.SetExtendedResultCodes(bOnOff); } /// Enables or disabled 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 disabled 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(int iErrCode, string zMessage) { CheckDisposed(); if (_sql == null) throw new InvalidOperationException("Database connection not valid for logging message."); _sql.LogMessage(iErrCode, zMessage); } #if INTEROP_CODEC /// /// 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(); ChangePassword(String.IsNullOrEmpty(newPassword) ? null : System.Text.UTF8Encoding.UTF8.GetBytes(newPassword)); } /// /// 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(); SetPassword(String.IsNullOrEmpty(databasePassword) ? null : System.Text.UTF8Encoding.UTF8.GetBytes(databasePassword)); } /// /// 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; _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 == 0) { count = Marshal.ReadInt32(pArg, 0); interval = Marshal.ReadInt32(pArg, sizeof(int)); } } finally { if (pArg != IntPtr.Zero) Marshal.FreeHGlobal(pArg); } return rc; } /// /// Removes one set of surrounding single -OR- double quotes from the file /// name and returns the resulting file name. If the string is null, empty, /// or contains quotes that are not balanced, nothing is done and the original /// string will be returned. /// /// The database file name to process. /// The modified database file name. private string UnwrapFileName(string sourceFile) { if (String.IsNullOrEmpty(sourceFile)) { // // NOTE: The string is null or empty, return it verbatim. // return sourceFile; } int length = sourceFile.Length; if (((sourceFile[0] == '\'') && (sourceFile[length - 1] == '\'')) || ((sourceFile[0] == '"') && (sourceFile[length - 1] == '"'))) { // // NOTE: Remove the first and last character. // return sourceFile.Substring(1, length - 2); } // // NOTE: No match, return the input string verbatim. // return sourceFile; } /// /// 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 string ExpandFileName(string sourceFile, bool toFullPath) { if (String.IsNullOrEmpty(sourceFile)) return sourceFile; if (sourceFile.StartsWith(_dataDirectory, StringComparison.OrdinalIgnoreCase)) { string dataDirectory; #if PLATFORM_COMPACTFRAMEWORK dataDirectory = Path.GetDirectoryName(System.Reflection.Assembly.GetCallingAssembly().GetName().CodeBase); #else dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory") as string; if (String.IsNullOrEmpty(dataDirectory)) dataDirectory = AppDomain.CurrentDomain.BaseDirectory; #endif 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 /// 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("MetaDataCollections"); 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, must not 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 = "main"; string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb; using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(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(String.Format(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((DbType)schemaRow[SchemaTableColumn.ProviderType]).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 = "main"; string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb; using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(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(String.Format(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) { 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"] = String.Format(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(String.Format(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"] = rd.GetBoolean(2); row["PRIMARY_KEY"] = false; // get the index definition using (SQLiteCommand cmdIndexes = new SQLiteCommand(String.Format(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(String.Format(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 = "main"; string master = (String.Compare(catalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb; using (SQLiteCommand cmd = new SQLiteCommand(String.Format(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 = "main"; string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb; using (SQLiteCommand cmd = new SQLiteCommand(String.Format(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 = "main"; string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb; using (SQLiteCommand cmd = new SQLiteCommand(String.Format(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 = "main"; string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb; tbl.BeginLoadData(); using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(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(String.Format(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"] = String.Format(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(String.Format(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(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rdIndexes.GetString(1)), this)) using (SQLiteDataReader rdIndex = cmdIndex.ExecuteReader()) { while (rdIndex.Read()) { 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"] = rdIndex.GetString(2); row["INDEX_NAME"] = rdIndexes.GetString(1); row["ORDINAL_POSITION"] = ordinal; // rdIndex.GetInt32(1); string collationSequence; int sortMode; int onError; _sql.GetIndexColumnExtendedInfo(strCatalog, rdIndexes.GetString(1), rdIndex.GetString(2), out sortMode, out onError, out collationSequence); if (String.IsNullOrEmpty(collationSequence) == false) row["COLLATION_NAME"] = collationSequence; row["SORT_MODE"] = (sortMode == 0) ? "ASC" : "DESC"; row["CONFLICT_OPTION"] = onError; ordinal++; if (String.IsNullOrEmpty(strColumn) || String.Compare(strColumn, row["COLUMN_NAME"].ToString(), 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 = "main"; string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb; tbl.BeginLoadData(); using (SQLiteCommand cmdViews = new SQLiteCommand(String.Format(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(String.Format(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((DbType)viewRow[SchemaTableColumn.ProviderType]).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 = "main"; string master = (String.Compare(strCatalog, "temp", StringComparison.OrdinalIgnoreCase) == 0) ? _tempmasterdb : _masterdb; tbl.BeginLoadData(); using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(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(String.Format(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"] = String.Format(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 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 void UpdateCallback(IntPtr puser, int type, IntPtr database, IntPtr table, Int64 rowid) { _updateHandler(this, new UpdateEventArgs( SQLiteBase.UTF8ToString(database, -1), SQLiteBase.UTF8ToString(table, -1), (UpdateEventType)type, rowid)); } /// /// 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, IntPtr statement) { _traceHandler(this, new TraceEventArgs( SQLiteBase.UTF8ToString(statement, -1))); } /// /// 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) { CommitEventArgs e = new CommitEventArgs(); _commitHandler(this, e); return (e.AbortTransaction == true) ? 1 : 0; } private void RollbackCallback(IntPtr parg) { _rollbackHandler(this, EventArgs.Empty); } } /// /// 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 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 SQLiteRollbackCallback(IntPtr puser); /// /// 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 /////////////////////////////////////////////////////////////////////////////////////////////// /// /// 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; } } }