2 // Mono.Data.Sqlite.SQLiteConnection.cs
5 // Robert Simpson (robert@blackcastlesoft.com)
7 // Adapted and modified for the Mono Project by
8 // Marek Habersack (grendello@gmail.com)
11 // Copyright (C) 2006 Novell, Inc (http://www.novell.com)
12 // Copyright (C) 2007 Marek Habersack
14 // Permission is hereby granted, free of charge, to any person obtaining
15 // a copy of this software and associated documentation files (the
16 // "Software"), to deal in the Software without restriction, including
17 // without limitation the rights to use, copy, modify, merge, publish,
18 // distribute, sublicense, and/or sell copies of the Software, and to
19 // permit persons to whom the Software is furnished to do so, subject to
20 // the following conditions:
22 // The above copyright notice and this permission notice shall be
23 // included in all copies or substantial portions of the Software.
25 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
26 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
27 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
28 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
29 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
30 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
31 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
34 /********************************************************
35 * ADO.NET 2.0 Data Provider for Sqlite Version 3.X
36 * Written by Robert Simpson (robert@blackcastlesoft.com)
38 * Released to the public domain, use at your own risk!
39 ********************************************************/
41 namespace Mono.Data.Sqlite
45 using System.Data.Common;
46 using System.Collections.Generic;
47 using System.Globalization;
48 using System.ComponentModel;
51 /// Sqlite implentation of DbConnection.
54 /// The <see cref="ConnectionString">ConnectionString</see> property of the SqliteConnection class can contain the following parameter(s), delimited with a semi-colon:
55 /// <list type="table">
57 /// <term>Parameter</term>
58 /// <term>Values</term>
59 /// <term>Required</term>
60 /// <term>Default</term>
63 /// <description>Data Source</description>
64 /// <description>{filename}</description>
65 /// <description>Y</description>
66 /// <description></description>
69 /// <description>Version</description>
70 /// <description>3</description>
71 /// <description>N</description>
72 /// <description>3</description>
75 /// <description>UseUTF16Encoding</description>
76 /// <description><b>True</b><br/><b>False</b></description>
77 /// <description>N</description>
78 /// <description>False</description>
81 /// <description>DateTimeFormat</description>
82 /// <description><b>Ticks</b> - Use DateTime.Ticks<br/><b>ISO8601</b> - Use ISO8601 DateTime format</description>
83 /// <description>N</description>
84 /// <description>ISO8601</description>
87 /// <description>BinaryGUID</description>
88 /// <description><b>True</b> - Store GUID columns in binary form<br/><b>False</b> - Store GUID columns as text</description>
89 /// <description>N</description>
90 /// <description>True</description>
93 /// <description>Cache Size</description>
94 /// <description>{size in bytes}</description>
95 /// <description>N</description>
96 /// <description>2000</description>
99 /// <description>Synchronous</description>
100 /// <description><b>Normal</b> - Normal file flushing behavior<br/><b>Full</b> - Full flushing after all writes<br/><b>Off</b> - Underlying OS flushes I/O's</description>
101 /// <description>N</description>
102 /// <description>Normal</description>
105 /// <description>Page Size</description>
106 /// <description>{size in bytes}</description>
107 /// <description>N</description>
108 /// <description>1024</description>
111 /// <description>Password</description>
112 /// <description>{password}</description>
113 /// <description>N</description>
114 /// <description></description>
117 /// <description>Enlist</description>
118 /// <description><B>Y</B> - Automatically enlist in distributed transactions<br/><b>N</b> - No automatic enlistment</description>
119 /// <description>N</description>
120 /// <description>Y</description>
124 public sealed class SqliteConnection : DbConnection, ICloneable
126 private const string _dataDirectory = "|DataDirectory|";
129 /// State of the current connection
131 private ConnectionState _connectionState;
133 /// The connection string
135 private string _connectionString;
137 /// Nesting level of the transactions open on the connection
139 internal int _transactionLevel;
141 /// Busy command timeout value. Defaults to 30
143 internal int _busyTimeout;
145 #if !PLATFORM_COMPACTFRAMEWORK
147 /// Whether or not the connection is enlisted in a distrubuted transaction
149 internal SqliteEnlistment _enlistment;
152 /// The base Sqlite object to interop with
154 internal SqliteBase _sql;
156 /// Commands associated with this connection
158 internal List<SqliteCommand> _commandList;
160 /// The database filename minus path and extension
162 private string _dataSource;
163 #if MONO_SUPPORT_PASSWORDS
165 /// Temporary password storage, emptied after the database has been opened
167 private byte[] _password;
170 internal bool _binaryGuid;
172 internal long _version;
174 private event SqliteUpdateEventHandler _updateHandler;
175 private event SqliteCommitHandler _commitHandler;
176 private event EventHandler _rollbackHandler;
178 private SqliteUpdateCallback _updateCallback;
179 private SqliteCommitCallback _commitCallback;
180 private SqliteRollbackCallback _rollbackCallback;
183 /// This event is raised whenever the database is opened or closed.
185 //public override event StateChangeEventHandler StateChange;
188 /// This event is raised whenever Sqlite makes an update/delete/insert into the database on
189 /// this connection. It only applies to the given connection.
191 public event SqliteUpdateEventHandler Update
195 if (_updateHandler == null)
197 _updateCallback = new SqliteUpdateCallback(UpdateCallback);
198 _sql.SetUpdateHook(_updateCallback);
200 _updateHandler += value;
204 _updateHandler -= value;
205 if (_updateHandler == null)
207 _sql.SetUpdateHook(null);
208 _updateCallback = null;
213 private void UpdateCallback(int type, IntPtr database, int databaseLen, IntPtr table, int tableLen, Int64 rowid)
215 _updateHandler(this, new UpdateEventArgs(
216 _sql.UTF8ToString(database),
217 _sql.UTF8ToString(table),
218 (UpdateEventType)type,
223 /// This event is raised whenever Sqlite is committing a transaction.
224 /// Return non-zero to trigger a rollback
226 public event SqliteCommitHandler Commit
230 if (_commitHandler == null)
232 _commitCallback = new SqliteCommitCallback(CommitCallback);
233 _sql.SetCommitHook(_commitCallback);
235 _commitHandler += value;
239 _commitHandler -= value;
240 if (_commitHandler == null)
242 _sql.SetCommitHook(null);
243 _commitCallback = null;
249 /// This event is raised whenever Sqlite is committing a transaction.
250 /// Return non-zero to trigger a rollback
252 public event EventHandler RollBack
256 if (_rollbackHandler == null)
258 _rollbackCallback = new SqliteRollbackCallback(RollbackCallback);
259 _sql.SetRollbackHook(_rollbackCallback);
261 _rollbackHandler += value;
265 _rollbackHandler -= value;
266 if (_rollbackHandler == null)
268 _sql.SetRollbackHook(null);
269 _rollbackCallback = null;
275 private int CommitCallback()
277 CommitEventArgs e = new CommitEventArgs();
278 _commitHandler(this, e);
279 return (e.AbortTransaction == true) ? 1 : 0;
282 private void RollbackCallback()
284 _rollbackHandler(this, EventArgs.Empty);
288 /// Constructs a new SqliteConnection object
291 /// Default constructor
293 public SqliteConnection() : this("")
298 /// Initializes the connection with the specified connection string
300 /// <param name="connectionString">The connection string to use on the connection</param>
301 public SqliteConnection(string connectionString)
304 _connectionState = ConnectionState.Closed;
305 _connectionString = "";
306 _transactionLevel = 0;
309 _commandList = new List<SqliteCommand>();
311 if (connectionString != null)
312 ConnectionString = connectionString;
316 /// Clones the settings and connection string from an existing connection. If the existing connection is already open, this
317 /// function will open its own connection, enumerate any attached databases of the original connection, and automatically
320 /// <param name="connection"></param>
321 public SqliteConnection(SqliteConnection connection) : this(connection.ConnectionString)
325 if (connection.State == ConnectionState.Open)
329 // Reattach all attached databases from the existing connection
330 using (DataTable tbl = connection.GetSchema("Catalogs"))
332 foreach (DataRow row in tbl.Rows)
334 str = row[0].ToString();
335 if (String.Compare(str, "main", true, CultureInfo.InvariantCulture) != 0
336 && String.Compare(str, "temp", true, CultureInfo.InvariantCulture) != 0)
338 using (SqliteCommand cmd = CreateCommand())
340 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "ATTACH DATABASE '{0}' AS [{1}]", row[1], row[0]);
341 cmd.ExecuteNonQuery();
350 /// Creates a clone of the connection. All attached databases and user-defined functions are cloned. If the existing connection is open, the cloned connection
351 /// will also be opened.
353 /// <returns></returns>
354 public object Clone()
356 return new SqliteConnection(this);
360 /// Disposes of the SqliteConnection, closing it if it is active.
362 /// <param name="disposing">True if the connection is being explicitly closed.</param>
363 protected override void Dispose(bool disposing)
365 base.Dispose(disposing);
370 /// Creates a database file. This just creates a zero-byte file which Sqlite
371 /// will turn into a database when the file is opened properly.
373 /// <param name="databaseFileName">The file to create</param>
374 static public void CreateFile(string databaseFileName)
376 System.IO.FileStream fs = System.IO.File.Create(databaseFileName);
381 /// On NTFS volumes, this function turns on the compression attribute for the given file.
382 /// It must not be open or referenced at the time of the function call.
384 /// <param name="databaseFileName">The file to compress</param>
385 static public void CompressFile(string databaseFileName)
387 UnsafeNativeMethods.sqlite3_compressfile(databaseFileName);
391 /// On NTFS volumes, this function removes the compression attribute for the given file.
392 /// It must not be open or referenced at the time of the function call.
394 /// <param name="databaseFileName">The file to decompress</param>
395 static public void DecompressFile(string databaseFileName)
397 UnsafeNativeMethods.sqlite3_decompressfile(databaseFileName);
401 /// Raises the state change event when the state of the connection changes
403 /// <param name="newState">The new state. If it is different from the previous state, an event is raised.</param>
404 internal void OnStateChange(ConnectionState newState)
406 // FIXME: breaks when the commented out code is used
407 ConnectionState oldState = _connectionState;
408 _connectionState = newState;
410 // if (StateChange != null && oldState != newState)
411 if (oldState != newState)
413 StateChangeEventArgs e = new StateChangeEventArgs(oldState, newState);
414 //StateChange(this, e);
415 base.OnStateChange (e);
420 /// Creates a new SqliteTransaction if one isn't already active on the connection.
422 /// <param name="isolationLevel">Sqlite doesn't support varying isolation levels, so this parameter is ignored.</param>
423 /// <param name="deferredLock">When TRUE, Sqlite defers obtaining a write lock until a write operation is requested.
424 /// When FALSE, a writelock is obtained immediately. The default is TRUE, but in a multi-threaded multi-writer
425 /// environment, one may instead choose to lock the database immediately to avoid any possible writer deadlock.</param>
426 /// <returns>Returns a SqliteTransaction object.</returns>
427 public SqliteTransaction BeginTransaction(System.Data.IsolationLevel isolationLevel, bool deferredLock)
429 return BeginTransaction(deferredLock);
433 /// Creates a new SqliteTransaction if one isn't already active on the connection.
435 /// <param name="deferredLock">When TRUE, Sqlite defers obtaining a write lock until a write operation is requested.
436 /// When FALSE, a writelock is obtained immediately. The default is TRUE, but in a multi-threaded multi-writer
437 /// environment, one may instead choose to lock the database immediately to avoid any possible writer deadlock.</param>
438 /// <returns>Returns a SqliteTransaction object.</returns>
439 public SqliteTransaction BeginTransaction(bool deferredLock)
441 if (_connectionState != ConnectionState.Open)
442 throw new InvalidOperationException();
444 return new SqliteTransaction(this, deferredLock);
448 /// Creates a new SqliteTransaction if one isn't already active on the connection.
450 /// <param name="isolationLevel">Sqlite supports only serializable transactions.</param>
451 /// <returns>Returns a SqliteTransaction object.</returns>
452 public new SqliteTransaction BeginTransaction(System.Data.IsolationLevel isolationLevel)
454 return BeginTransaction(false);
458 /// Creates a new SqliteTransaction if one isn't already active on the connection.
460 /// <returns>Returns a SqliteTransaction object.</returns>
461 public new SqliteTransaction BeginTransaction()
463 return BeginTransaction(false);
467 /// Forwards to the local BeginTransaction() function
469 /// <param name="isolationLevel"></param>
470 /// <returns></returns>
471 protected override DbTransaction BeginDbTransaction(System.Data.IsolationLevel isolationLevel)
473 return BeginTransaction(false);
479 /// <param name="databaseName"></param>
480 public override void ChangeDatabase(string databaseName)
482 throw new NotImplementedException();
486 /// When the database connection is closed, all commands linked to this connection are automatically reset.
488 public override void Close()
492 // Force any commands associated with this connection to release their unmanaged
493 // resources. The commands are still valid and will automatically re-acquire the
494 // unmanaged resources the next time they are run -- provided this connection is
495 // re-opened before then.
498 foreach (SqliteCommand cmd in _commandList)
502 #if !PLATFORM_COMPACTFRAMEWORK
503 if (_enlistment != null)
505 // If the connection is enlisted in a transaction scope and the scope is still active,
506 // we cannot truly shut down this connection until the scope has completed. Therefore make a
507 // hidden connection temporarily to hold open the connection until the scope has completed.
508 SqliteConnection cnn = new SqliteConnection();
510 cnn._transactionLevel = _transactionLevel;
511 cnn._enlistment = _enlistment;
512 cnn._connectionState = _connectionState;
513 cnn._version = _version;
515 cnn._enlistment._transaction._cnn = cnn;
516 cnn._enlistment._disposeConnection = true;
527 _transactionLevel = 0;
530 OnStateChange(ConnectionState.Closed);
534 /// The connection string containing the parameters for the connection
537 /// <list type="table">
539 /// <term>Parameter</term>
540 /// <term>Values</term>
541 /// <term>Required</term>
542 /// <term>Default</term>
545 /// <description>Data Source</description>
546 /// <description>{filename}</description>
547 /// <description>Y</description>
548 /// <description></description>
551 /// <description>Version</description>
552 /// <description>3</description>
553 /// <description>N</description>
554 /// <description>3</description>
557 /// <description>UseUTF16Encoding</description>
558 /// <description><b>True</b><br/><b>False</b></description>
559 /// <description>N</description>
560 /// <description>False</description>
563 /// <description>DateTimeFormat</description>
564 /// <description><b>Ticks</b> - Use DateTime.Ticks<br/><b>ISO8601</b> - Use ISO8601 DateTime format</description>
565 /// <description>N</description>
566 /// <description>ISO8601</description>
569 /// <description>BinaryGUID</description>
570 /// <description><b>Yes/On/1</b> - Store GUID columns in binary form<br/><b>No/Off/0</b> - Store GUID columns as text</description>
571 /// <description>N</description>
572 /// <description>On</description>
575 /// <description>Cache Size</description>
576 /// <description>{size in bytes}</description>
577 /// <description>N</description>
578 /// <description>2000</description>
581 /// <description>Synchronous</description>
582 /// <description><b>Normal</b> - Normal file flushing behavior<br/><b>Full</b> - Full flushing after all writes<br/><b>Off</b> - Underlying OS flushes I/O's</description>
583 /// <description>N</description>
584 /// <description>Normal</description>
587 /// <description>Page Size</description>
588 /// <description>{size in bytes}</description>
589 /// <description>N</description>
590 /// <description>1024</description>
593 /// <description>Password</description>
594 /// <description>{password}</description>
595 /// <description>N</description>
596 /// <description></description>
599 /// <description>Enlist</description>
600 /// <description><B>Y</B> - Automatically enlist in distributed transactions<br/><b>N</b> - No automatic enlistment</description>
601 /// <description>N</description>
602 /// <description>Y</description>
606 #if !PLATFORM_COMPACTFRAMEWORK
607 [RefreshProperties(RefreshProperties.All), DefaultValue("")]
608 [Editor("Sqlite.Designer.SqliteConnectionStringEditor, Sqlite.Designer, Version=1.0.31.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139", "System.Drawing.Design.UITypeEditor, System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")]
610 public override string ConnectionString
614 return _connectionString;
619 throw new ArgumentNullException();
621 else if (_connectionState != ConnectionState.Closed)
622 throw new InvalidOperationException();
624 _connectionString = value;
629 /// Create a new SqliteCommand and associate it with this connection.
631 /// <returns>Returns an instantiated SqliteCommand object already assigned to this connection.</returns>
632 public new SqliteCommand CreateCommand()
634 return new SqliteCommand(this);
638 /// Forwards to the local CreateCommand() function
640 /// <returns></returns>
641 protected override DbCommand CreateDbCommand()
643 return CreateCommand();
647 /// Returns the filename without extension or path
649 #if !PLATFORM_COMPACTFRAMEWORK
650 [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
652 public override string DataSource
663 #if !PLATFORM_COMPACTFRAMEWORK
664 [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
666 public override string Database
675 /// Maps mono-specific connection string keywords to the standard ones
677 /// <returns>The mapped keyword name</returns>
678 internal void MapMonoKeyword (string[] arPiece, List<KeyValuePair<string, string>> ls)
680 string keyword, value;
682 switch (arPiece[0].ToLower (CultureInfo.InvariantCulture)) {
684 keyword = "Data Source";
685 value = MapMonoUriPath (arPiece[1]);
689 keyword = arPiece[0];
694 ls.Add(new KeyValuePair<string, string>(keyword, value));
697 internal string MapMonoUriPath (string path)
699 if (path.StartsWith ("file://")) {
700 return path.Substring (7);
701 } else if (path.StartsWith ("file:")) {
702 return path.Substring (5);
703 } else if (path.StartsWith ("/")) {
706 throw new InvalidOperationException ("Invalid connection string: invalid URI");
711 /// Parses the connection string into component parts
713 /// <returns>An array of key-value pairs representing each parameter of the connection string</returns>
714 internal KeyValuePair<string, string>[] ParseConnectionString()
716 string s = _connectionString.Replace (',', ';'); // Mono compatibility
718 List<KeyValuePair<string, string>> ls = new List<KeyValuePair<string, string>>();
720 // First split into semi-colon delimited values. The Split() function of SqliteBase accounts for and properly
721 // skips semi-colons in quoted strings
722 string[] arParts = SqliteConvert.Split(s, ';');
725 int x = arParts.Length;
726 // For each semi-colon piece, split into key and value pairs by the presence of the = sign
727 for (n = 0; n < x; n++)
729 arPiece = SqliteConvert.Split(arParts[n], '=');
730 if (arPiece.Length == 2)
731 MapMonoKeyword (arPiece, ls);
732 else throw new ArgumentException(String.Format(CultureInfo.CurrentCulture, "Invalid ConnectionString format for parameter \"{0}\"", (arPiece.Length > 0) ? arPiece[0] : "null"));
734 KeyValuePair<string, string>[] ar = new KeyValuePair<string, string>[ls.Count];
737 // Return the array of key-value pairs
741 #if !PLATFORM_COMPACTFRAMEWORK
743 /// Manual distributed transaction enlistment support
745 /// <param name="transaction">The distributed transaction to enlist in</param>
746 public override void EnlistTransaction(System.Transactions.Transaction transaction)
748 if (_transactionLevel > 0 && transaction != null)
749 throw new ArgumentException("Unable to enlist in transaction, a local transaction already exists");
751 if (_enlistment != null && transaction != _enlistment._scope)
752 throw new ArgumentException("Already enlisted in a transaction");
754 _enlistment = new SqliteEnlistment(this, transaction);
759 /// Looks for a key in the array of key/values of the parameter string. If not found, return the specified default value
761 /// <param name="opts">The Key/Value pair array to look in</param>
762 /// <param name="key">The key to find</param>
763 /// <param name="defValue">The default value to return if the key is not found</param>
764 /// <returns>The value corresponding to the specified key, or the default value if not found.</returns>
765 static internal string FindKey(KeyValuePair<string, string>[] opts, string key, string defValue)
768 for (int n = 0; n < x; n++)
770 if (String.Compare(opts[n].Key, key, true, CultureInfo.InvariantCulture) == 0)
772 return opts[n].Value;
779 /// Opens the connection using the parameters found in the <see cref="ConnectionString">ConnectionString</see>
781 public override void Open()
783 if (_connectionState != ConnectionState.Closed)
784 throw new InvalidOperationException();
788 KeyValuePair<string, string>[] opts = ParseConnectionString();
791 if (Convert.ToInt32(FindKey(opts, "Version", "3"), CultureInfo.InvariantCulture) != 3)
792 throw new NotSupportedException("Only Sqlite Version 3 is supported at this time");
794 fileName = FindKey(opts, "Data Source", "");
796 if (String.IsNullOrEmpty(fileName))
797 throw new ArgumentException("Data Source cannot be empty. Use :memory: to open an in-memory database");
799 if (String.Compare(fileName, ":MEMORY:", true, CultureInfo.InvariantCulture) == 0)
800 fileName = ":memory:";
801 #if PLATFORM_COMPACTFRAMEWORK
802 else if (fileName.StartsWith(".\\"))
803 fileName = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetCallingAssembly().GetName().CodeBase) + fileName.Substring(1);
805 string bt = FindKey (opts, "busy_timeout", "30");
807 _busyTimeout = Int32.Parse (bt);
808 } catch (Exception) {
814 bool bUTF16 = (Convert.ToBoolean(FindKey(opts, "UseUTF16Encoding", "False"), CultureInfo.InvariantCulture) == true);
815 SqliteDateFormats dateFormat = String.Compare(FindKey(opts, "DateTimeFormat", "ISO8601"), "ticks", true, CultureInfo.InvariantCulture) == 0 ? SqliteDateFormats.Ticks : SqliteDateFormats.ISO8601;
817 if (bUTF16) // Sqlite automatically sets the encoding of the database to UTF16 if called from sqlite3_open16()
818 _sql = new Sqlite3_UTF16(dateFormat);
820 _sql = new Sqlite3(dateFormat);
822 fileName = ExpandFileName(fileName);
826 if (System.IO.File.Exists(fileName) == false)
827 throw new System.IO.FileNotFoundException(String.Format(CultureInfo.CurrentCulture, "Unable to locate file \"{0}\", creating new database.", fileName));
835 _binaryGuid = (Convert.ToBoolean(FindKey(opts, "BinaryGUID", "True"), CultureInfo.InvariantCulture) == true);
837 #if MONO_SUPPORT_PASSWORDS
838 // Not used under mono now
839 string password = FindKey(opts, "Password", null);
841 if (String.IsNullOrEmpty(password) == false)
842 _sql.SetPassword(System.Text.UTF8Encoding.UTF8.GetBytes(password));
843 else if (_password != null)
844 _sql.SetPassword(_password);
847 _dataSource = System.IO.Path.GetFileNameWithoutExtension(fileName);
849 OnStateChange(ConnectionState.Open);
852 using (SqliteCommand cmd = CreateCommand())
856 defValue = FindKey(opts, "Synchronous", "Normal");
857 if (String.Compare(defValue, "Normal", true, CultureInfo.InvariantCulture) != 0)
859 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA Synchronous={0}", defValue);
860 cmd.ExecuteNonQuery();
863 defValue = FindKey(opts, "Cache Size", "2000");
864 if (Convert.ToInt32(defValue) != 2000)
866 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA Cache_Size={0}", defValue);
867 cmd.ExecuteNonQuery();
870 if (fileName != ":memory:")
872 defValue = FindKey(opts, "Page Size", "1024");
873 if (Convert.ToInt32(defValue) != 1024)
875 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA Page_Size={0}", defValue);
876 cmd.ExecuteNonQuery();
881 #if !PLATFORM_COMPACTFRAMEWORK
882 if (FindKey(opts, "Enlist", "Y").ToUpper()[0] == 'Y' && System.Transactions.Transaction.Current != null)
883 EnlistTransaction(System.Transactions.Transaction.Current);
886 catch (SqliteException)
888 OnStateChange(ConnectionState.Broken);
894 /// Returns the version of the underlying Sqlite database engine
896 #if !PLATFORM_COMPACTFRAMEWORK
897 [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
899 public override string ServerVersion
903 if (_connectionState != ConnectionState.Open)
904 throw new InvalidOperationException();
911 /// Returns the state of the connection.
913 #if !PLATFORM_COMPACTFRAMEWORK
914 [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
916 public override ConnectionState State
920 return _connectionState;
924 #if MONO_SUPPORT_PASSWORDS // Not used on mono now
926 /// Change the password (or assign a password) to an open database.
929 /// No readers or writers may be active for this process. The database must already be open
930 /// and if it already was password protected, the existing password must already have been supplied.
932 /// <param name="newPassword">The new password to assign to the database</param>
933 public void ChangePassword(string newPassword)
935 ChangePassword(String.IsNullOrEmpty(newPassword) ? null : System.Text.UTF8Encoding.UTF8.GetBytes(newPassword));
939 /// Change the password (or assign a password) to an open database.
942 /// No readers or writers may be active for this process. The database must already be open
943 /// and if it already was password protected, the existing password must already have been supplied.
945 /// <param name="newPassword">The new password to assign to the database</param>
946 public void ChangePassword(byte[] newPassword)
948 if (_connectionState != ConnectionState.Open)
949 throw new InvalidOperationException("Database must be opened before changing the password.");
951 _sql.ChangePassword(newPassword);
955 /// Sets the password for a password-protected database. A password-protected database is
956 /// unusable for any operation until the password has been set.
958 /// <param name="databasePassword">The password for the database</param>
959 public void SetPassword(string databasePassword)
961 SetPassword(String.IsNullOrEmpty(databasePassword) ? null : System.Text.UTF8Encoding.UTF8.GetBytes(databasePassword));
965 /// Sets the password for a password-protected database. A password-protected database is
966 /// unusable for any operation until the password has been set.
968 /// <param name="databasePassword">The password for the database</param>
969 public void SetPassword(byte[] databasePassword)
971 if (_connectionState != ConnectionState.Closed)
972 throw new InvalidOperationException("Password can only be set before the database is opened.");
974 if (databasePassword != null)
975 if (databasePassword.Length == 0) databasePassword = null;
977 _password = databasePassword;
982 /// Expand the filename of the data source, resolving the |DataDirectory| macro as appropriate.
984 /// <param name="sourceFile">The database filename to expand</param>
985 /// <returns>The expanded path and filename of the filename</returns>
986 private string ExpandFileName(string sourceFile)
988 if (String.IsNullOrEmpty(sourceFile)) return sourceFile;
990 if (sourceFile.StartsWith(_dataDirectory, StringComparison.OrdinalIgnoreCase))
992 string dataDirectory;
994 #if PLATFORM_COMPACTFRAMEWORK
995 dataDirectory = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetCallingAssembly().GetName().CodeBase);
997 dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory") as string;
998 if (String.IsNullOrEmpty(dataDirectory))
999 dataDirectory = AppDomain.CurrentDomain.BaseDirectory;
1002 if (sourceFile.Length > _dataDirectory.Length)
1004 if (sourceFile[_dataDirectory.Length] == System.IO.Path.DirectorySeparatorChar ||
1005 sourceFile[_dataDirectory.Length] == System.IO.Path.AltDirectorySeparatorChar)
1006 sourceFile = sourceFile.Remove(_dataDirectory.Length, 1);
1008 sourceFile = System.IO.Path.Combine(dataDirectory, sourceFile.Substring(_dataDirectory.Length));
1014 /// The following commands are used to extract schema information out of the database. Valid schema types are:
1015 /// <list type="bullet">
1017 /// <description>MetaDataCollections</description>
1020 /// <description>DataSourceInformation</description>
1023 /// <description>Catalogs</description>
1026 /// <description>Columns</description>
1029 /// <description>ForeignKeys</description>
1032 /// <description>Indexes</description>
1035 /// <description>IndexColumns</description>
1038 /// <description>Tables</description>
1041 /// <description>Views</description>
1044 /// <description>ViewColumns</description>
1049 /// Returns the MetaDataCollections schema
1051 /// <returns>A DataTable of the MetaDataCollections schema</returns>
1052 public override DataTable GetSchema()
1054 return GetSchema("MetaDataCollections", null);
1058 /// Returns schema information of the specified collection
1060 /// <param name="collectionName">The schema collection to retrieve</param>
1061 /// <returns>A DataTable of the specified collection</returns>
1062 public override DataTable GetSchema(string collectionName)
1064 return GetSchema(collectionName, new string[0]);
1068 /// Retrieves schema information using the specified constraint(s) for the specified collection
1070 /// <param name="collectionName">The collection to retrieve</param>
1071 /// <param name="restrictionValues">The restrictions to impose</param>
1072 /// <returns>A DataTable of the specified collection</returns>
1073 public override DataTable GetSchema(string collectionName, string[] restrictionValues)
1075 if (_connectionState != ConnectionState.Open)
1076 throw new InvalidOperationException();
1078 string[] parms = new string[5];
1080 if (restrictionValues == null) restrictionValues = new string[0];
1081 restrictionValues.CopyTo(parms, 0);
1083 switch (collectionName.ToUpper(CultureInfo.InvariantCulture))
1085 case "METADATACOLLECTIONS":
1086 return Schema_MetaDataCollections();
1087 case "DATASOURCEINFORMATION":
1088 return Schema_DataSourceInformation();
1090 return Schema_DataTypes();
1092 return Schema_Columns(parms[0], parms[2], parms[3]);
1094 return Schema_Indexes(parms[0], parms[2], parms[4]);
1095 case "INDEXCOLUMNS":
1096 return Schema_IndexColumns(parms[0], parms[2], parms[3], parms[4]);
1098 return Schema_Tables(parms[0], parms[2], parms[3]);
1100 return Schema_Views(parms[0], parms[2]);
1102 return Schema_ViewColumns(parms[0], parms[2], parms[3]);
1104 return Schema_ForeignKeys(parms[0], parms[2], parms[3]);
1106 return Schema_Catalogs(parms[0]);
1107 case "RESERVEDWORDS":
1108 return Schema_ReservedWords();
1110 throw new NotSupportedException();
1113 private static DataTable Schema_ReservedWords()
1115 DataTable tbl = new DataTable("MetaDataCollections");
1117 tbl.Locale = CultureInfo.InvariantCulture;
1118 tbl.Columns.Add("ReservedWord", typeof(string));
1119 tbl.Columns.Add("MaximumVersion", typeof(string));
1120 tbl.Columns.Add("MinimumVersion", typeof(string));
1122 tbl.BeginLoadData();
1124 foreach (string word in SR.Keywords.Split(new char[] { ',' }))
1131 tbl.AcceptChanges();
1138 /// Builds a MetaDataCollections schema datatable
1140 /// <returns>DataTable</returns>
1141 private static DataTable Schema_MetaDataCollections()
1143 DataTable tbl = new DataTable("MetaDataCollections");
1145 tbl.Locale = CultureInfo.InvariantCulture;
1146 tbl.Columns.Add("CollectionName", typeof(string));
1147 tbl.Columns.Add("NumberOfRestrictions", typeof(int));
1148 tbl.Columns.Add("NumberOfIdentifierParts", typeof(int));
1150 tbl.BeginLoadData();
1152 System.IO.StringReader reader = new System.IO.StringReader(SR.MetaDataCollections);
1153 tbl.ReadXml(reader);
1156 tbl.AcceptChanges();
1163 /// Builds a DataSourceInformation datatable
1165 /// <returns>DataTable</returns>
1166 private DataTable Schema_DataSourceInformation()
1168 DataTable tbl = new DataTable("DataSourceInformation");
1171 tbl.Locale = CultureInfo.InvariantCulture;
1172 tbl.Columns.Add(DbMetaDataColumnNames.CompositeIdentifierSeparatorPattern, typeof(string));
1173 tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductName, typeof(string));
1174 tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductVersion, typeof(string));
1175 tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductVersionNormalized, typeof(string));
1176 tbl.Columns.Add(DbMetaDataColumnNames.GroupByBehavior, typeof(int));
1177 tbl.Columns.Add(DbMetaDataColumnNames.IdentifierPattern, typeof(string));
1178 tbl.Columns.Add(DbMetaDataColumnNames.IdentifierCase, typeof(int));
1179 tbl.Columns.Add(DbMetaDataColumnNames.OrderByColumnsInSelect, typeof(bool));
1180 tbl.Columns.Add(DbMetaDataColumnNames.ParameterMarkerFormat, typeof(string));
1181 tbl.Columns.Add(DbMetaDataColumnNames.ParameterMarkerPattern, typeof(string));
1182 tbl.Columns.Add(DbMetaDataColumnNames.ParameterNameMaxLength, typeof(int));
1183 tbl.Columns.Add(DbMetaDataColumnNames.ParameterNamePattern, typeof(string));
1184 tbl.Columns.Add(DbMetaDataColumnNames.QuotedIdentifierPattern, typeof(string));
1185 tbl.Columns.Add(DbMetaDataColumnNames.QuotedIdentifierCase, typeof(int));
1186 tbl.Columns.Add(DbMetaDataColumnNames.StatementSeparatorPattern, typeof(string));
1187 tbl.Columns.Add(DbMetaDataColumnNames.StringLiteralPattern, typeof(string));
1188 tbl.Columns.Add(DbMetaDataColumnNames.SupportedJoinOperators, typeof(int));
1190 tbl.BeginLoadData();
1193 row.ItemArray = new object[] {
1199 @"(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\""[^\""\0]|\""\""+\""$)",
1203 @"@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)",
1205 @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)",
1209 @"'(([^']|'')*)'", // ' a bug in c-sharp mode for emacs
1214 tbl.AcceptChanges();
1221 /// Build a Columns schema
1223 /// <param name="strCatalog">The catalog (attached database) to query, can be null</param>
1224 /// <param name="strTable">The table to retrieve schema information for, must not be null</param>
1225 /// <param name="strColumn">The column to retrieve schema information for, can be null</param>
1226 /// <returns>DataTable</returns>
1227 private DataTable Schema_Columns(string strCatalog, string strTable, string strColumn)
1229 DataTable tbl = new DataTable("Columns");
1232 tbl.Locale = CultureInfo.InvariantCulture;
1233 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
1234 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
1235 tbl.Columns.Add("TABLE_NAME", typeof(string));
1236 tbl.Columns.Add("COLUMN_NAME", typeof(string));
1237 tbl.Columns.Add("COLUMN_GUID", typeof(Guid));
1238 tbl.Columns.Add("COLUMN_PROPID", typeof(long));
1239 tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
1240 tbl.Columns.Add("COLUMN_HASDEFAULT", typeof(bool));
1241 tbl.Columns.Add("COLUMN_DEFAULT", typeof(string));
1242 tbl.Columns.Add("COLUMN_FLAGS", typeof(long));
1243 tbl.Columns.Add("IS_NULLABLE", typeof(bool));
1244 tbl.Columns.Add("DATA_TYPE", typeof(string));
1245 tbl.Columns.Add("TYPE_GUID", typeof(Guid));
1246 tbl.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int));
1247 tbl.Columns.Add("CHARACTER_OCTET_LENGTH", typeof(int));
1248 tbl.Columns.Add("NUMERIC_PRECISION", typeof(int));
1249 tbl.Columns.Add("NUMERIC_SCALE", typeof(int));
1250 tbl.Columns.Add("DATETIME_PRECISION", typeof(long));
1251 tbl.Columns.Add("CHARACTER_SET_CATALOG", typeof(string));
1252 tbl.Columns.Add("CHARACTER_SET_SCHEMA", typeof(string));
1253 tbl.Columns.Add("CHARACTER_SET_NAME", typeof(string));
1254 tbl.Columns.Add("COLLATION_CATALOG", typeof(string));
1255 tbl.Columns.Add("COLLATION_SCHEMA", typeof(string));
1256 tbl.Columns.Add("COLLATION_NAME", typeof(string));
1257 tbl.Columns.Add("DOMAIN_CATALOG", typeof(string));
1258 tbl.Columns.Add("DOMAIN_NAME", typeof(string));
1259 tbl.Columns.Add("DESCRIPTION", typeof(string));
1260 tbl.Columns.Add("PRIMARY_KEY", typeof(bool));
1262 tbl.BeginLoadData();
1264 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
1266 using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table' OR [type] LIKE 'view'", strCatalog), this))
1267 using (SqliteDataReader rdTables = cmdTables.ExecuteReader())
1269 while (rdTables.Read())
1271 if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), true, CultureInfo.InvariantCulture) == 0)
1273 using (SqliteCommand cmd = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
1274 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader(CommandBehavior.SchemaOnly))
1275 using (DataTable tblSchema = rd.GetSchemaTable(false, true))
1277 foreach (DataRow schemaRow in tblSchema.Rows)
1279 if (String.Compare(schemaRow[SchemaTableColumn.ColumnName].ToString(), strColumn, true, CultureInfo.InvariantCulture) == 0
1280 || strColumn == null)
1284 row["TABLE_NAME"] = rdTables.GetString(2);
1285 row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.ColumnName];
1286 row["TABLE_CATALOG"] = strCatalog;
1287 row["ORDINAL_POSITION"] = schemaRow[SchemaTableColumn.ColumnOrdinal];
1288 row["COLUMN_HASDEFAULT"] = (schemaRow[SchemaTableOptionalColumn.DefaultValue] != DBNull.Value);
1289 row["COLUMN_DEFAULT"] = schemaRow[SchemaTableOptionalColumn.DefaultValue];
1290 row["IS_NULLABLE"] = schemaRow[SchemaTableColumn.AllowDBNull];
1291 row["DATA_TYPE"] = schemaRow["DataTypeName"]; // SqliteConvert.DbTypeToType((DbType)schemaRow[SchemaTableColumn.ProviderType]).ToString();
1292 row["CHARACTER_MAXIMUM_LENGTH"] = schemaRow[SchemaTableColumn.ColumnSize];
1293 row["TABLE_SCHEMA"] = schemaRow[SchemaTableColumn.BaseSchemaName];
1294 row["PRIMARY_KEY"] = schemaRow[SchemaTableColumn.IsKey];
1304 tbl.AcceptChanges();
1311 /// Returns index information for the given database and catalog
1313 /// <param name="strCatalog">The catalog (attached database) to query, can be null</param>
1314 /// <param name="strIndex">The name of the index to retrieve information for, can be null</param>
1315 /// <param name="strTable">The table to retrieve index information for, can be null</param>
1316 /// <returns>DataTable</returns>
1317 private DataTable Schema_Indexes(string strCatalog, string strTable, string strIndex)
1319 DataTable tbl = new DataTable("Indexes");
1321 System.Collections.Generic.List<int> primaryKeys = new List<int>();
1324 tbl.Locale = CultureInfo.InvariantCulture;
1325 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
1326 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
1327 tbl.Columns.Add("TABLE_NAME", typeof(string));
1328 tbl.Columns.Add("INDEX_CATALOG", typeof(string));
1329 tbl.Columns.Add("INDEX_SCHEMA", typeof(string));
1330 tbl.Columns.Add("INDEX_NAME", typeof(string));
1331 tbl.Columns.Add("PRIMARY_KEY", typeof(bool));
1332 tbl.Columns.Add("UNIQUE", typeof(bool));
1333 tbl.Columns.Add("CLUSTERED", typeof(bool));
1334 tbl.Columns.Add("TYPE", typeof(int));
1335 tbl.Columns.Add("FILL_FACTOR", typeof(int));
1336 tbl.Columns.Add("INITIAL_SIZE", typeof(int));
1337 tbl.Columns.Add("NULLS", typeof(int));
1338 tbl.Columns.Add("SORT_BOOKMARKS", typeof(bool));
1339 tbl.Columns.Add("AUTO_UPDATE", typeof(bool));
1340 tbl.Columns.Add("NULL_COLLATION", typeof(int));
1341 tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
1342 tbl.Columns.Add("COLUMN_NAME", typeof(string));
1343 tbl.Columns.Add("COLUMN_GUID", typeof(Guid));
1344 tbl.Columns.Add("COLUMN_PROPID", typeof(long));
1345 tbl.Columns.Add("COLLATION", typeof(short));
1346 tbl.Columns.Add("CARDINALITY", typeof(Decimal));
1347 tbl.Columns.Add("PAGES", typeof(int));
1348 tbl.Columns.Add("FILTER_CONDITION", typeof(string));
1349 tbl.Columns.Add("INTEGRATED", typeof(bool));
1351 tbl.BeginLoadData();
1353 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
1355 using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table'", strCatalog), this))
1356 using (SqliteDataReader rdTables = cmdTables.ExecuteReader())
1358 while (rdTables.Read())
1361 primaryKeys.Clear();
1362 if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0)
1364 // First, look for any rowid indexes -- which sqlite defines are INTEGER PRIMARY KEY columns.
1365 // Such indexes are not listed in the indexes list but count as indexes just the same.
1366 using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].table_info([{1}])", strCatalog, rdTables.GetString(2)), this))
1367 using (SqliteDataReader rdTable = cmdTable.ExecuteReader())
1369 while (rdTable.Read())
1371 if (rdTable.GetInt32(5) == 1)
1373 primaryKeys.Add(rdTable.GetInt32(0));
1375 // If the primary key is of type INTEGER, then its a rowid and we need to make a fake index entry for it.
1376 if (String.Compare(rdTable.GetString(2), "INTEGER", true, CultureInfo.InvariantCulture) == 0)
1382 if (primaryKeys.Count == 1 && maybeRowId == true)
1386 row["TABLE_CATALOG"] = strCatalog;
1387 row["TABLE_NAME"] = rdTables.GetString(2);
1388 row["INDEX_CATALOG"] = strCatalog;
1389 row["PRIMARY_KEY"] = true;
1390 row["INDEX_NAME"] = String.Format(CultureInfo.InvariantCulture, "sqlite_master_PK_{0}", rdTables.GetString(2));
1391 row["UNIQUE"] = true;
1393 if (String.Compare((string)row["INDEX_NAME"], strIndex, true, CultureInfo.InvariantCulture) == 0
1394 || strIndex == null)
1399 primaryKeys.Clear();
1402 // Now fetch all the rest of the indexes.
1403 using (SqliteCommand cmd = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_list([{1}])", strCatalog, rdTables.GetString(2)), this))
1404 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader())
1408 if (String.Compare(rd.GetString(1), strIndex, true, CultureInfo.InvariantCulture) == 0
1409 || strIndex == null)
1413 row["TABLE_CATALOG"] = strCatalog;
1414 row["TABLE_NAME"] = rdTables.GetString(2);
1415 row["INDEX_CATALOG"] = strCatalog;
1416 row["INDEX_NAME"] = rd.GetString(1);
1417 row["UNIQUE"] = rd.GetBoolean(2);
1418 row["PRIMARY_KEY"] = false;
1420 // Now for the really hard work. Figure out which index is the primary key index.
1421 // The only way to figure it out is to check if the index was an autoindex and if we have a non-rowid
1422 // primary key, and all the columns in the given index match the primary key columns
1423 if (primaryKeys.Count > 0 && rd.GetString(1).StartsWith("sqlite_autoindex_" + rdTables.GetString(2), StringComparison.InvariantCultureIgnoreCase) == true)
1425 using (SqliteCommand cmdDetails = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rd.GetString(1)), this))
1426 using (SqliteDataReader rdDetails = cmdDetails.ExecuteReader())
1429 while (rdDetails.Read())
1431 if (primaryKeys.Contains(rdDetails.GetInt32(1)) == false)
1438 if (nMatches == primaryKeys.Count)
1440 row["PRIMARY_KEY"] = true;
1441 primaryKeys.Clear();
1454 tbl.AcceptChanges();
1461 /// Retrieves table schema information for the database and catalog
1463 /// <param name="strCatalog">The catalog (attached database) to retrieve tables on</param>
1464 /// <param name="strTable">The table to retrieve, can be null</param>
1465 /// <param name="strType">The table type, can be null</param>
1466 /// <returns>DataTable</returns>
1467 private DataTable Schema_Tables(string strCatalog, string strTable, string strType)
1469 DataTable tbl = new DataTable("Tables");
1473 tbl.Locale = CultureInfo.InvariantCulture;
1474 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
1475 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
1476 tbl.Columns.Add("TABLE_NAME", typeof(string));
1477 tbl.Columns.Add("TABLE_TYPE", typeof(string));
1478 tbl.Columns.Add("TABLE_ID", typeof(long));
1479 tbl.Columns.Add("TABLE_ROOTPAGE", typeof(int));
1481 tbl.BeginLoadData();
1483 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
1485 using (SqliteCommand cmd = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT [type], [name], [tbl_name], [rootpage], [sql], [rowid] FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table'", strCatalog), this))
1486 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader())
1490 strItem = rd.GetString(0);
1491 if (String.Compare(rd.GetString(2), 0, "SQLITE_", 0, 7, true, CultureInfo.InvariantCulture) == 0)
1492 strItem = "SYSTEM_TABLE";
1494 if (String.Compare(strType, strItem, true, CultureInfo.InvariantCulture) == 0
1497 if (String.Compare(rd.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0
1498 || strTable == null)
1502 row["TABLE_CATALOG"] = strCatalog;
1503 row["TABLE_NAME"] = rd.GetString(2);
1504 row["TABLE_TYPE"] = strItem;
1505 row["TABLE_ID"] = rd.GetInt64(5);
1506 row["TABLE_ROOTPAGE"] = rd.GetInt32(3);
1514 tbl.AcceptChanges();
1521 /// Retrieves view schema information for the database
1523 /// <param name="strCatalog">The catalog (attached database) to retrieve views on</param>
1524 /// <param name="strView">The view name, can be null</param>
1525 /// <returns>DataTable</returns>
1526 private DataTable Schema_Views(string strCatalog, string strView)
1528 DataTable tbl = new DataTable("Views");
1533 tbl.Locale = CultureInfo.InvariantCulture;
1534 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
1535 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
1536 tbl.Columns.Add("TABLE_NAME", typeof(string));
1537 tbl.Columns.Add("VIEW_DEFINITION", typeof(string));
1538 tbl.Columns.Add("CHECK_OPTION", typeof(bool));
1539 tbl.Columns.Add("IS_UPDATABLE", typeof(bool));
1540 tbl.Columns.Add("DESCRIPTION", typeof(string));
1541 tbl.Columns.Add("DATE_CREATED", typeof(DateTime));
1542 tbl.Columns.Add("DATE_MODIFIED", typeof(DateTime));
1544 tbl.BeginLoadData();
1546 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
1548 using (SqliteCommand cmd = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'view'", strCatalog), this))
1549 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader())
1553 if (String.Compare(rd.GetString(1), strView, true, CultureInfo.InvariantCulture) == 0
1554 || String.IsNullOrEmpty(strView))
1556 strItem = rd.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ');
1557 nPos = System.Globalization.CultureInfo.InvariantCulture.CompareInfo.IndexOf(strItem, " AS ", CompareOptions.IgnoreCase);
1560 strItem = strItem.Substring(nPos + 4).Trim();
1563 row["TABLE_CATALOG"] = strCatalog;
1564 row["TABLE_NAME"] = rd.GetString(2);
1565 row["IS_UPDATABLE"] = false;
1566 row["VIEW_DEFINITION"] = strItem;
1574 tbl.AcceptChanges();
1581 /// Retrieves catalog (attached databases) schema information for the database
1583 /// <param name="strCatalog">The catalog to retrieve, can be null</param>
1584 /// <returns>DataTable</returns>
1585 private DataTable Schema_Catalogs(string strCatalog)
1587 DataTable tbl = new DataTable("Catalogs");
1590 tbl.Locale = CultureInfo.InvariantCulture;
1591 tbl.Columns.Add("CATALOG_NAME", typeof(string));
1592 tbl.Columns.Add("DESCRIPTION", typeof(string));
1593 tbl.Columns.Add("ID", typeof(long));
1595 tbl.BeginLoadData();
1597 using (SqliteCommand cmd = new SqliteCommand("PRAGMA database_list", this))
1598 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader())
1602 if (String.Compare(rd.GetString(1), strCatalog, true, CultureInfo.InvariantCulture) == 0
1603 || strCatalog == null)
1607 row["CATALOG_NAME"] = rd.GetString(1);
1608 row["DESCRIPTION"] = rd.GetString(2);
1609 row["ID"] = rd.GetInt64(0);
1616 tbl.AcceptChanges();
1622 private DataTable Schema_DataTypes()
1624 DataTable tbl = new DataTable("DataTypes");
1626 tbl.Locale = CultureInfo.InvariantCulture;
1627 tbl.Columns.Add("TypeName", typeof(String));
1628 tbl.Columns.Add("ProviderDbType", typeof(int));
1629 tbl.Columns.Add("ColumnSize", typeof(long));
1630 tbl.Columns.Add("CreateFormat", typeof(String));
1631 tbl.Columns.Add("CreateParameters", typeof(String));
1632 tbl.Columns.Add("DataType", typeof(String));
1633 tbl.Columns.Add("IsAutoIncrementable", typeof(bool));
1634 tbl.Columns.Add("IsBestMatch", typeof(bool));
1635 tbl.Columns.Add("IsCaseSensitive", typeof(bool));
1636 tbl.Columns.Add("IsFixedLength", typeof(bool));
1637 tbl.Columns.Add("IsFixedPrecisionScale", typeof(bool));
1638 tbl.Columns.Add("IsLong", typeof(bool));
1639 tbl.Columns.Add("IsNullable", typeof(bool));
1640 tbl.Columns.Add("IsSearchable", typeof(bool));
1641 tbl.Columns.Add("IsSearchableWithLike", typeof(bool));
1642 tbl.Columns.Add("IsLiteralSupported", typeof(bool));
1643 tbl.Columns.Add("LiteralPrefix", typeof(String));
1644 tbl.Columns.Add("LiteralSuffix", typeof(String));
1645 tbl.Columns.Add("IsUnsigned", typeof(bool));
1646 tbl.Columns.Add("MaximumScale", typeof(short));
1647 tbl.Columns.Add("MinimumScale", typeof(short));
1648 tbl.Columns.Add("IsConcurrencyType", typeof(bool));
1650 tbl.BeginLoadData();
1652 System.IO.StringReader reader = new System.IO.StringReader(SR.DataTypes);
1653 tbl.ReadXml(reader);
1656 tbl.AcceptChanges();
1663 /// Returns the base column information for indexes in a database
1665 /// <param name="strCatalog">The catalog to retrieve indexes for (can be null)</param>
1666 /// <param name="strTable">The table to restrict index information by (can be null)</param>
1667 /// <param name="strIndex">The index to restrict index information by (can be null)</param>
1668 /// <param name="strColumn">The source column to restrict index information by (can be null)</param>
1669 /// <returns>A DataTable containing the results</returns>
1670 private DataTable Schema_IndexColumns(string strCatalog, string strTable, string strIndex, string strColumn)
1672 DataTable tbl = new DataTable("IndexColumns");
1674 List<KeyValuePair<int, string>> primaryKeys = new List<KeyValuePair<int, string>>();
1677 tbl.Locale = CultureInfo.InvariantCulture;
1678 tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string));
1679 tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string));
1680 tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
1681 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
1682 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
1683 tbl.Columns.Add("TABLE_NAME", typeof(string));
1684 tbl.Columns.Add("COLUMN_NAME", typeof(string));
1685 tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
1686 tbl.Columns.Add("INDEX_NAME", typeof(string));
1688 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
1690 tbl.BeginLoadData();
1692 using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table'", strCatalog), this))
1693 using (SqliteDataReader rdTables = cmdTables.ExecuteReader())
1695 while (rdTables.Read())
1698 primaryKeys.Clear();
1699 if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0)
1701 using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].table_info([{1}])", strCatalog, rdTables.GetString(2)), this))
1702 using (SqliteDataReader rdTable = cmdTable.ExecuteReader())
1704 while (rdTable.Read())
1706 if (rdTable.GetInt32(5) == 1) // is a primary key
1708 primaryKeys.Add(new KeyValuePair<int, string>(rdTable.GetInt32(0), rdTable.GetString(1)));
1709 // Is an integer -- could be a rowid if no other primary keys exist in the table
1710 if (String.Compare(rdTable.GetString(2), "INTEGER", true, CultureInfo.InvariantCulture) == 0)
1715 if (primaryKeys.Count == 1 && maybeRowId == true)
1718 row["CONSTRAINT_CATALOG"] = strCatalog;
1719 row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "sqlite_master_PK_{0}", rdTables.GetString(2));
1720 row["TABLE_CATALOG"] = strCatalog;
1721 row["TABLE_NAME"] = rdTables.GetString(2);
1722 row["COLUMN_NAME"] = primaryKeys[0].Value;
1723 row["INDEX_NAME"] = row["CONSTRAINT_NAME"];
1724 row["ORDINAL_POSITION"] = primaryKeys[0].Key;
1726 if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, (string)row["INDEX_NAME"], true, CultureInfo.InvariantCulture) == 0)
1730 using (SqliteCommand cmdIndexes = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'index' AND [tbl_name] LIKE '{1}'", strCatalog, rdTables.GetString(2).Replace("'", "''")), this))
1731 using (SqliteDataReader rdIndexes = cmdIndexes.ExecuteReader())
1733 while (rdIndexes.Read())
1735 if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, rdIndexes.GetString(1), true, CultureInfo.InvariantCulture) == 0)
1737 using (SqliteCommand cmdIndex = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rdIndexes.GetString(1)), this))
1738 using (SqliteDataReader rdIndex = cmdIndex.ExecuteReader())
1740 while (rdIndex.Read())
1743 row["CONSTRAINT_CATALOG"] = strCatalog;
1744 row["CONSTRAINT_NAME"] = rdIndexes.GetString(1);
1745 row["TABLE_CATALOG"] = strCatalog;
1746 row["TABLE_NAME"] = rdIndexes.GetString(2);
1747 row["COLUMN_NAME"] = rdIndex.GetString(2);
1748 row["INDEX_NAME"] = rdIndexes.GetString(1);
1749 row["ORDINAL_POSITION"] = rdIndex.GetInt32(1);
1751 if (String.IsNullOrEmpty(strColumn) || String.Compare(strColumn, row["COLUMN_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0)
1763 tbl.AcceptChanges();
1769 /// Returns detailed column information for a specified view
1771 /// <param name="strCatalog">The catalog to retrieve columns for (can be null)</param>
1772 /// <param name="strView">The view to restrict column information by (can be null)</param>
1773 /// <param name="strColumn">The source column to restrict column information by (can be null)</param>
1774 /// <returns>A DataTable containing the results</returns>
1775 private DataTable Schema_ViewColumns(string strCatalog, string strView, string strColumn)
1777 DataTable tbl = new DataTable("ViewColumns");
1784 tbl.Locale = CultureInfo.InvariantCulture;
1785 tbl.Columns.Add("VIEW_CATALOG", typeof(string));
1786 tbl.Columns.Add("VIEW_SCHEMA", typeof(string));
1787 tbl.Columns.Add("VIEW_NAME", typeof(string));
1788 tbl.Columns.Add("VIEW_COLUMN_NAME", typeof(String));
1789 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
1790 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
1791 tbl.Columns.Add("TABLE_NAME", typeof(string));
1792 tbl.Columns.Add("COLUMN_NAME", typeof(string));
1794 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
1796 tbl.BeginLoadData();
1798 using (SqliteCommand cmdViews = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'view'", strCatalog), this))
1799 using (SqliteDataReader rdViews = cmdViews.ExecuteReader())
1801 while (rdViews.Read())
1803 if (String.IsNullOrEmpty(strView) || String.Compare(strView, rdViews.GetString(2), true, CultureInfo.InvariantCulture) == 0)
1805 using (SqliteCommand cmdViewSelect = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdViews.GetString(2)), this))
1807 strSql = rdViews.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ');
1808 n = CultureInfo.InvariantCulture.CompareInfo.IndexOf(strSql, " AS ", CompareOptions.IgnoreCase);
1812 strSql = strSql.Substring(n + 4);
1814 using (SqliteCommand cmd = new SqliteCommand(strSql, this))
1815 using (SqliteDataReader rdViewSelect = cmdViewSelect.ExecuteReader(CommandBehavior.SchemaOnly))
1816 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader(CommandBehavior.SchemaOnly))
1817 using (DataTable tblSchemaView = rdViewSelect.GetSchemaTable(false, false))
1818 using (DataTable tblSchema = rd.GetSchemaTable(false, false))
1820 for (n = 0; n < tblSchema.Rows.Count; n++)
1822 viewRow = tblSchemaView.Rows[n];
1823 schemaRow = tblSchema.Rows[n];
1825 if (String.Compare(viewRow[SchemaTableColumn.ColumnName].ToString(), strColumn, true, CultureInfo.InvariantCulture) == 0
1826 || strColumn == null)
1830 row["VIEW_CATALOG"] = strCatalog;
1831 row["VIEW_NAME"] = rdViews.GetString(2);
1832 row["TABLE_CATALOG"] = strCatalog;
1833 row["TABLE_SCHEMA"] = schemaRow[SchemaTableColumn.BaseSchemaName];
1834 row["TABLE_NAME"] = schemaRow[SchemaTableColumn.BaseTableName];
1835 row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.ColumnName];
1836 row["VIEW_COLUMN_NAME"] = viewRow[SchemaTableColumn.ColumnName];
1848 tbl.AcceptChanges();
1854 /// Retrieves foreign key information from the specified set of filters
1856 /// <param name="strCatalog">An optional catalog to restrict results on</param>
1857 /// <param name="strTable">An optional table to restrict results on</param>
1858 /// <param name="strKeyName">An optional foreign key name to restrict results on</param>
1859 /// <returns>A DataTable with the results of the query</returns>
1860 private DataTable Schema_ForeignKeys(string strCatalog, string strTable, string strKeyName)
1862 DataTable tbl = new DataTable("ForeignKeys");
1865 tbl.Locale = CultureInfo.InvariantCulture;
1866 tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string));
1867 tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string));
1868 tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
1869 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
1870 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
1871 tbl.Columns.Add("TABLE_NAME", typeof(string));
1872 tbl.Columns.Add("CONSTRAINT_TYPE", typeof(string));
1873 tbl.Columns.Add("IS_DEFERRABLE", typeof(bool));
1874 tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool));
1875 tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string));
1876 tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int));
1877 tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string));
1878 tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string));
1879 tbl.Columns.Add("FKEY_TO_TABLE", typeof(string));
1880 tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string));
1882 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
1884 tbl.BeginLoadData();
1886 using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table'", strCatalog), this))
1887 using (SqliteDataReader rdTables = cmdTables.ExecuteReader())
1889 while (rdTables.Read())
1891 if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), true, CultureInfo.InvariantCulture) == 0)
1893 using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
1894 using (SqliteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
1895 using (SqliteCommand cmdKey = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this))
1896 using (SqliteDataReader rdKey = cmdKey.ExecuteReader())
1898 while (rdKey.Read())
1901 row["CONSTRAINT_CATALOG"] = strCatalog;
1902 row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}_{2}", rdTables.GetString(2), rdKey.GetString(3), rdKey.GetString(4));
1903 row["TABLE_CATALOG"] = strCatalog;
1904 row["TABLE_NAME"] = rdTables.GetString(2);
1905 row["CONSTRAINT_TYPE"] = "FOREIGN KEY";
1906 row["IS_DEFERRABLE"] = false;
1907 row["INITIALLY_DEFERRED"] = false;
1908 row["FKEY_FROM_COLUMN"] = rdKey.GetString(3);
1909 row["FKEY_FROM_ORDINAL_POSITION"] = rdTable.GetOrdinal(row["FKEY_FROM_COLUMN"].ToString());
1910 row["FKEY_TO_CATALOG"] = strCatalog;
1911 row["FKEY_TO_TABLE"] = rdKey.GetString(2);
1912 row["FKEY_TO_COLUMN"] = rdKey.GetString(4);
1914 if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0)
1923 tbl.AcceptChanges();
1928 internal void AddCommand(SqliteCommand cmd)
1932 _commandList.Add(cmd);
1936 internal void RemoveCommand(SqliteCommand cmd)
1940 _commandList.Remove(cmd);
1944 #if MONO_BACKWARD_COMPAT
1948 public override int ConnectionTimeout
1956 public int Version {
1960 public int LastInsertRowId {
1961 get { return _sql.GetLastInsertRowId (); }
1964 public int BusyTimeout {
1965 get { return _busyTimeout; }
1971 /// The I/O file cache flushing behavior for the connection
1973 public enum SynchronizationModes
1976 /// Normal file flushing at critical sections of the code
1980 /// Full file flushing after every write operation
1984 /// Use the default operating system's file flushing, Sqlite does not explicitly flush the file buffers after writing
1989 internal delegate void SqliteUpdateCallback(int type, IntPtr database, int databaseLen, IntPtr table, int tableLen, Int64 rowid);
1990 internal delegate int SqliteCommitCallback();
1991 internal delegate void SqliteRollbackCallback();
1994 /// Raised when a transaction is about to be committed. To roll back a transaction, set the
1995 /// rollbackTrans boolean value to true.
1997 /// <param name="sender">The connection committing the transaction</param>
1998 /// <param name="e">Event arguments on the transaction</param>
1999 public delegate void SqliteCommitHandler(object sender, CommitEventArgs e);
2002 /// Raised when data is inserted, updated and deleted on a given connection
2004 /// <param name="sender">The connection committing the transaction</param>
2005 /// <param name="e">The event parameters which triggered the event</param>
2006 public delegate void SqliteUpdateEventHandler(object sender, UpdateEventArgs e);
2009 /// Whenever an update event is triggered on a connection, this enum will indicate
2010 /// exactly what type of operation is being performed.
2012 public enum UpdateEventType
2015 /// A row is being deleted from the given database and table
2019 /// A row is being inserted into the table.
2023 /// A row is being updated in the table.
2029 /// Passed during an Update callback, these event arguments detail the type of update operation being performed
2030 /// on the given connection.
2032 public class UpdateEventArgs : EventArgs
2035 /// The name of the database being updated (usually "main" but can be any attached or temporary database)
2037 public readonly string Database;
2040 /// The name of the table being updated
2042 public readonly string Table;
2045 /// The type of update being performed (insert/update/delete)
2047 public readonly UpdateEventType Event;
2050 /// The RowId affected by this update.
2052 public readonly Int64 RowId;
2054 internal UpdateEventArgs(string database, string table, UpdateEventType eventType, Int64 rowid)
2056 Database = database;
2064 /// Event arguments raised when a transaction is being committed
2066 public class CommitEventArgs : EventArgs
2068 internal CommitEventArgs()
2073 /// Set to true to abort the transaction and trigger a rollback
2075 public bool AbortTransaction;