1 /********************************************************
\r
2 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
\r
3 * Written by Robert Simpson (robert@blackcastlesoft.com)
\r
5 * Released to the public domain, use at your own risk!
\r
6 ********************************************************/
\r
8 namespace Mono.Data.Sqlite
\r
12 using System.Data.Common;
\r
13 using System.Collections.Generic;
\r
14 using System.Globalization;
\r
15 using System.ComponentModel;
\r
17 using System.Runtime.InteropServices;
\r
21 /// SQLite implentation of DbConnection.
\r
24 /// The <see cref="ConnectionString">ConnectionString</see> property of the SqliteConnection class can contain the following parameter(s), delimited with a semi-colon:
\r
25 /// <list type="table">
\r
27 /// <term>Parameter</term>
\r
28 /// <term>Values</term>
\r
29 /// <term>Required</term>
\r
30 /// <term>Default</term>
\r
33 /// <description>Data Source</description>
\r
34 /// <description>{filename}</description>
\r
35 /// <description>Y</description>
\r
36 /// <description></description>
\r
39 /// <description>Version</description>
\r
40 /// <description>3</description>
\r
41 /// <description>N</description>
\r
42 /// <description>3</description>
\r
45 /// <description>UseUTF16Encoding</description>
\r
46 /// <description><b>True</b><br/><b>False</b></description>
\r
47 /// <description>N</description>
\r
48 /// <description>False</description>
\r
51 /// <description>DateTimeFormat</description>
\r
52 /// <description><b>Ticks</b> - Use DateTime.Ticks<br/><b>ISO8601</b> - Use ISO8601 DateTime format</description>
\r
53 /// <description>N</description>
\r
54 /// <description>ISO8601</description>
\r
57 /// <description>BinaryGUID</description>
\r
58 /// <description><b>True</b> - Store GUID columns in binary form<br/><b>False</b> - Store GUID columns as text</description>
\r
59 /// <description>N</description>
\r
60 /// <description>True</description>
\r
63 /// <description>Cache Size</description>
\r
64 /// <description>{size in bytes}</description>
\r
65 /// <description>N</description>
\r
66 /// <description>2000</description>
\r
69 /// <description>Synchronous</description>
\r
70 /// <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>
\r
71 /// <description>N</description>
\r
72 /// <description>Normal</description>
\r
75 /// <description>Page Size</description>
\r
76 /// <description>{size in bytes}</description>
\r
77 /// <description>N</description>
\r
78 /// <description>1024</description>
\r
81 /// <description>Password</description>
\r
82 /// <description>{password}</description>
\r
83 /// <description>N</description>
\r
84 /// <description></description>
\r
87 /// <description>Enlist</description>
\r
88 /// <description><b>Y</b> - Automatically enlist in distributed transactions<br/><b>N</b> - No automatic enlistment</description>
\r
89 /// <description>N</description>
\r
90 /// <description>Y</description>
\r
93 /// <description>Pooling</description>
\r
94 /// <description><b>True</b> - Use connection pooling<br/><b>False</b> - Do not use connection pooling</description>
\r
95 /// <description>N</description>
\r
96 /// <description>False</description>
\r
99 /// <description>FailIfMissing</description>
\r
100 /// <description><b>True</b> - Don't create the database if it does not exist, throw an error instead<br/><b>False</b> - Automatically create the database if it does not exist</description>
\r
101 /// <description>N</description>
\r
102 /// <description>False</description>
\r
105 /// <description>Max Page Count</description>
\r
106 /// <description>{size in pages} - Limits the maximum number of pages (limits the size) of the database</description>
\r
107 /// <description>N</description>
\r
108 /// <description>0</description>
\r
111 /// <description>Legacy Format</description>
\r
112 /// <description><b>True</b> - Use the more compatible legacy 3.x database format<br/><b>False</b> - Use the newer 3.3x database format which compresses numbers more effectively</description>
\r
113 /// <description>N</description>
\r
114 /// <description>False</description>
\r
117 /// <description>Default Timeout</description>
\r
118 /// <description>{time in seconds}<br/>The default command timeout</description>
\r
119 /// <description>N</description>
\r
120 /// <description>30</description>
\r
123 /// <description>Journal Mode</description>
\r
124 /// <description><b>Delete</b> - Delete the journal file after a commit<br/><b>Persist</b> - Zero out and leave the journal file on disk after a commit<br/><b>Off</b> - Disable the rollback journal entirely</description>
\r
125 /// <description>N</description>
\r
126 /// <description>Delete</description>
\r
129 /// <description>Read Only</description>
\r
130 /// <description><b>True</b> - Open the database for read only access<br/><b>False</b> - Open the database for normal read/write access</description>
\r
131 /// <description>N</description>
\r
132 /// <description>False</description>
\r
135 /// <description>Max Pool Size</description>
\r
136 /// <description>The maximum number of connections for the given connection string that can be in the connection pool</description>
\r
137 /// <description>N</description>
\r
138 /// <description>100</description>
\r
141 /// <description>Default IsolationLevel</description>
\r
142 /// <description>The default transaciton isolation level</description>
\r
143 /// <description>N</description>
\r
144 /// <description>Serializable</description>
\r
148 public sealed partial class SqliteConnection : DbConnection, ICloneable
\r
150 private const string _dataDirectory = "|DataDirectory|";
\r
151 private const string _masterdb = "sqlite_master";
\r
152 private const string _tempmasterdb = "sqlite_temp_master";
\r
155 /// State of the current connection
\r
157 private ConnectionState _connectionState;
\r
159 /// The connection string
\r
161 private string _connectionString;
\r
163 /// Nesting level of the transactions open on the connection
\r
165 internal int _transactionLevel;
\r
168 /// The default isolation level for new transactions
\r
170 private IsolationLevel _defaultIsolation;
\r
172 #if !PLATFORM_COMPACTFRAMEWORK
\r
174 /// Whether or not the connection is enlisted in a distrubuted transaction
\r
176 internal SQLiteEnlistment _enlistment;
\r
179 /// The base SQLite object to interop with
\r
181 internal SQLiteBase _sql;
\r
183 /// The database filename minus path and extension
\r
185 private string _dataSource;
\r
187 /// Temporary password storage, emptied after the database has been opened
\r
189 private byte[] _password;
\r
192 /// Default command timeout
\r
194 private int _defaultTimeout = 30;
\r
196 internal bool _binaryGuid;
\r
198 internal long _version;
\r
200 private event SQLiteUpdateEventHandler _updateHandler;
\r
201 private event SQLiteCommitHandler _commitHandler;
\r
202 private event EventHandler _rollbackHandler;
\r
204 private SQLiteUpdateCallback _updateCallback;
\r
205 private SQLiteCommitCallback _commitCallback;
\r
206 private SQLiteRollbackCallback _rollbackCallback;
\r
209 /// This event is raised whenever the database is opened or closed.
\r
211 public override event StateChangeEventHandler StateChange;
\r
214 /// Constructs a new SqliteConnection object
\r
217 /// Default constructor
\r
219 public SqliteConnection()
\r
225 /// Initializes the connection with the specified connection string
\r
227 /// <param name="connectionString">The connection string to use on the connection</param>
\r
228 public SqliteConnection(string connectionString)
\r
231 _connectionState = ConnectionState.Closed;
\r
232 _connectionString = "";
\r
233 _transactionLevel = 0;
\r
235 //_commandList = new List<WeakReference>();
\r
237 if (connectionString != null)
\r
238 ConnectionString = connectionString;
\r
242 /// Clones the settings and connection string from an existing connection. If the existing connection is already open, this
\r
243 /// function will open its own connection, enumerate any attached databases of the original connection, and automatically
\r
244 /// attach to them.
\r
246 /// <param name="connection"></param>
\r
247 public SqliteConnection(SqliteConnection connection)
\r
248 : this(connection.ConnectionString)
\r
252 if (connection.State == ConnectionState.Open)
\r
256 // Reattach all attached databases from the existing connection
\r
257 using (DataTable tbl = connection.GetSchema("Catalogs"))
\r
259 foreach (DataRow row in tbl.Rows)
\r
261 str = row[0].ToString();
\r
262 if (String.Compare(str, "main", true, CultureInfo.InvariantCulture) != 0
\r
263 && String.Compare(str, "temp", true, CultureInfo.InvariantCulture) != 0)
\r
265 using (SqliteCommand cmd = CreateCommand())
\r
267 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "ATTACH DATABASE '{0}' AS [{1}]", row[1], row[0]);
\r
268 cmd.ExecuteNonQuery();
\r
276 #if PLATFORM_COMPACTFRAMEWORK
\r
280 public override int ConnectionTimeout
\r
290 /// Creates a clone of the connection. All attached databases and user-defined functions are cloned. If the existing connection is open, the cloned connection
\r
291 /// will also be opened.
\r
293 /// <returns></returns>
\r
294 public object Clone()
\r
296 return new SqliteConnection(this);
\r
300 /// Disposes of the SqliteConnection, closing it if it is active.
\r
302 /// <param name="disposing">True if the connection is being explicitly closed.</param>
\r
303 protected override void Dispose(bool disposing)
\r
305 base.Dispose(disposing);
\r
315 /// Creates a database file. This just creates a zero-byte file which SQLite
\r
316 /// will turn into a database when the file is opened properly.
\r
318 /// <param name="databaseFileName">The file to create</param>
\r
319 static public void CreateFile(string databaseFileName)
\r
321 FileStream fs = File.Create(databaseFileName);
\r
325 #if !SQLITE_STANDARD
\r
327 /// On NTFS volumes, this function turns on the compression attribute for the given file.
\r
328 /// It must not be open or referenced at the time of the function call.
\r
330 /// <param name="databaseFileName">The file to compress</param>
\r
331 [Obsolete("This functionality is being removed from a future version of the SQLite provider")]
\r
332 static public void CompressFile(string databaseFileName)
\r
334 UnsafeNativeMethods.sqlite3_compressfile(databaseFileName);
\r
338 #if !SQLITE_STANDARD
\r
340 /// On NTFS volumes, this function removes the compression attribute for the given file.
\r
341 /// It must not be open or referenced at the time of the function call.
\r
343 /// <param name="databaseFileName">The file to decompress</param>
\r
344 [Obsolete("This functionality is being removed from a future version of the SQLite provider")]
\r
345 static public void DecompressFile(string databaseFileName)
\r
347 UnsafeNativeMethods.sqlite3_decompressfile(databaseFileName);
\r
352 /// Raises the state change event when the state of the connection changes
\r
354 /// <param name="newState">The new state. If it is different from the previous state, an event is raised.</param>
\r
355 internal void OnStateChange(ConnectionState newState)
\r
357 ConnectionState oldState = _connectionState;
\r
358 _connectionState = newState;
\r
360 if (StateChange != null && oldState != newState)
\r
362 StateChangeEventArgs e = new StateChangeEventArgs(oldState, newState);
\r
363 StateChange(this, e);
\r
368 /// OBSOLETE. Creates a new SqliteTransaction if one isn't already active on the connection.
\r
370 /// <param name="isolationLevel">This parameter is ignored.</param>
\r
371 /// <param name="deferredLock">When TRUE, SQLite defers obtaining a write lock until a write operation is requested.
\r
372 /// When FALSE, a writelock is obtained immediately. The default is TRUE, but in a multi-threaded multi-writer
\r
373 /// environment, one may instead choose to lock the database immediately to avoid any possible writer deadlock.</param>
\r
374 /// <returns>Returns a SqliteTransaction object.</returns>
\r
375 [Obsolete("Use one of the standard BeginTransaction methods, this one will be removed soon")]
\r
376 public SqliteTransaction BeginTransaction(IsolationLevel isolationLevel, bool deferredLock)
\r
378 return (SqliteTransaction)BeginDbTransaction(deferredLock == false ? IsolationLevel.Serializable : IsolationLevel.ReadCommitted);
\r
382 /// OBSOLETE. Creates a new SqliteTransaction if one isn't already active on the connection.
\r
384 /// <param name="deferredLock">When TRUE, SQLite defers obtaining a write lock until a write operation is requested.
\r
385 /// When FALSE, a writelock is obtained immediately. The default is false, but in a multi-threaded multi-writer
\r
386 /// environment, one may instead choose to lock the database immediately to avoid any possible writer deadlock.</param>
\r
387 /// <returns>Returns a SqliteTransaction object.</returns>
\r
388 [Obsolete("Use one of the standard BeginTransaction methods, this one will be removed soon")]
\r
389 public SqliteTransaction BeginTransaction(bool deferredLock)
\r
391 return (SqliteTransaction)BeginDbTransaction(deferredLock == false ? IsolationLevel.Serializable : IsolationLevel.ReadCommitted);
\r
395 /// Creates a new SqliteTransaction if one isn't already active on the connection.
\r
397 /// <param name="isolationLevel">Supported isolation levels are Serializable, ReadCommitted and Unspecified.</param>
\r
399 /// Unspecified will use the default isolation level specified in the connection string. If no isolation level is specified in the
\r
400 /// connection string, Serializable is used.
\r
401 /// Serializable transactions are the default. In this mode, the engine gets an immediate lock on the database, and no other threads
\r
402 /// may begin a transaction. Other threads may read from the database, but not write.
\r
403 /// With a ReadCommitted isolation level, locks are deferred and elevated as needed. It is possible for multiple threads to start
\r
404 /// a transaction in ReadCommitted mode, but if a thread attempts to commit a transaction while another thread
\r
405 /// has a ReadCommitted lock, it may timeout or cause a deadlock on both threads until both threads' CommandTimeout's are reached.
\r
407 /// <returns>Returns a SqliteTransaction object.</returns>
\r
408 public new SqliteTransaction BeginTransaction(IsolationLevel isolationLevel)
\r
410 return (SqliteTransaction)BeginDbTransaction(isolationLevel);
\r
414 /// Creates a new SqliteTransaction if one isn't already active on the connection.
\r
416 /// <returns>Returns a SqliteTransaction object.</returns>
\r
417 public new SqliteTransaction BeginTransaction()
\r
419 return (SqliteTransaction)BeginDbTransaction(_defaultIsolation);
\r
423 /// Forwards to the local BeginTransaction() function
\r
425 /// <param name="isolationLevel">Supported isolation levels are Unspecified, Serializable, and ReadCommitted</param>
\r
426 /// <returns></returns>
\r
427 protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
\r
429 if (_connectionState != ConnectionState.Open)
\r
430 throw new InvalidOperationException();
\r
432 if (isolationLevel == IsolationLevel.Unspecified) isolationLevel = _defaultIsolation;
\r
434 if (isolationLevel != IsolationLevel.Serializable && isolationLevel != IsolationLevel.ReadCommitted)
\r
435 throw new ArgumentException("isolationLevel");
\r
437 return new SqliteTransaction(this, isolationLevel != IsolationLevel.Serializable);
\r
441 /// Not implemented
\r
443 /// <param name="databaseName"></param>
\r
444 public override void ChangeDatabase(string databaseName)
\r
446 throw new NotImplementedException();
\r
450 /// When the database connection is closed, all commands linked to this connection are automatically reset.
\r
452 public override void Close()
\r
456 #if !PLATFORM_COMPACTFRAMEWORK
\r
457 if (_enlistment != null)
\r
459 // If the connection is enlisted in a transaction scope and the scope is still active,
\r
460 // we cannot truly shut down this connection until the scope has completed. Therefore make a
\r
461 // hidden connection temporarily to hold open the connection until the scope has completed.
\r
462 SqliteConnection cnn = new SqliteConnection();
\r
464 cnn._transactionLevel = _transactionLevel;
\r
465 cnn._enlistment = _enlistment;
\r
466 cnn._connectionState = _connectionState;
\r
467 cnn._version = _version;
\r
469 cnn._enlistment._transaction._cnn = cnn;
\r
470 cnn._enlistment._disposeConnection = true;
\r
472 _enlistment = null;
\r
480 _transactionLevel = 0;
\r
482 OnStateChange(ConnectionState.Closed);
\r
486 /// Clears the connection pool associated with the connection. Any other active connections using the same database file
\r
487 /// will be discarded instead of returned to the pool when they are closed.
\r
489 /// <param name="connection"></param>
\r
490 public static void ClearPool(SqliteConnection connection)
\r
492 if (connection._sql == null) return;
\r
493 connection._sql.ClearPool();
\r
497 /// Clears all connection pools. Any active connections will be discarded instead of sent to the pool when they are closed.
\r
499 public static void ClearAllPools()
\r
501 SqliteConnectionPool.ClearAllPools();
\r
505 /// The connection string containing the parameters for the connection
\r
508 /// <list type="table">
\r
510 /// <term>Parameter</term>
\r
511 /// <term>Values</term>
\r
512 /// <term>Required</term>
\r
513 /// <term>Default</term>
\r
516 /// <description>Data Source</description>
\r
517 /// <description>{filename}</description>
\r
518 /// <description>Y</description>
\r
519 /// <description></description>
\r
522 /// <description>Version</description>
\r
523 /// <description>3</description>
\r
524 /// <description>N</description>
\r
525 /// <description>3</description>
\r
528 /// <description>UseUTF16Encoding</description>
\r
529 /// <description><b>True</b><br/><b>False</b></description>
\r
530 /// <description>N</description>
\r
531 /// <description>False</description>
\r
534 /// <description>DateTimeFormat</description>
\r
535 /// <description><b>Ticks</b> - Use DateTime.Ticks<br/><b>ISO8601</b> - Use ISO8601 DateTime format<br/><b>JulianDay</b> - Use JulianDay format</description>
\r
536 /// <description>N</description>
\r
537 /// <description>ISO8601</description>
\r
540 /// <description>BinaryGUID</description>
\r
541 /// <description><b>Yes/On/1</b> - Store GUID columns in binary form<br/><b>No/Off/0</b> - Store GUID columns as text</description>
\r
542 /// <description>N</description>
\r
543 /// <description>On</description>
\r
546 /// <description>Cache Size</description>
\r
547 /// <description>{size in bytes}</description>
\r
548 /// <description>N</description>
\r
549 /// <description>2000</description>
\r
552 /// <description>Synchronous</description>
\r
553 /// <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>
\r
554 /// <description>N</description>
\r
555 /// <description>Normal</description>
\r
558 /// <description>Page Size</description>
\r
559 /// <description>{size in bytes}</description>
\r
560 /// <description>N</description>
\r
561 /// <description>1024</description>
\r
564 /// <description>Password</description>
\r
565 /// <description>{password}</description>
\r
566 /// <description>N</description>
\r
567 /// <description></description>
\r
570 /// <description>Enlist</description>
\r
571 /// <description><B>Y</B> - Automatically enlist in distributed transactions<br/><b>N</b> - No automatic enlistment</description>
\r
572 /// <description>N</description>
\r
573 /// <description>Y</description>
\r
576 /// <description>Pooling</description>
\r
577 /// <description><b>True</b> - Use connection pooling<br/><b>False</b> - Do not use connection pooling</description>
\r
578 /// <description>N</description>
\r
579 /// <description>False</description>
\r
582 /// <description>FailIfMissing</description>
\r
583 /// <description><b>True</b> - Don't create the database if it does not exist, throw an error instead<br/><b>False</b> - Automatically create the database if it does not exist</description>
\r
584 /// <description>N</description>
\r
585 /// <description>False</description>
\r
588 /// <description>Max Page Count</description>
\r
589 /// <description>{size in pages} - Limits the maximum number of pages (limits the size) of the database</description>
\r
590 /// <description>N</description>
\r
591 /// <description>0</description>
\r
594 /// <description>Legacy Format</description>
\r
595 /// <description><b>True</b> - Use the more compatible legacy 3.x database format<br/><b>False</b> - Use the newer 3.3x database format which compresses numbers more effectively</description>
\r
596 /// <description>N</description>
\r
597 /// <description>False</description>
\r
600 /// <description>Default Timeout</description>
\r
601 /// <description>{time in seconds}<br/>The default command timeout</description>
\r
602 /// <description>N</description>
\r
603 /// <description>30</description>
\r
606 /// <description>Journal Mode</description>
\r
607 /// <description><b>Delete</b> - Delete the journal file after a commit<br/><b>Persist</b> - Zero out and leave the journal file on disk after a commit<br/><b>Off</b> - Disable the rollback journal entirely</description>
\r
608 /// <description>N</description>
\r
609 /// <description>Delete</description>
\r
612 /// <description>Read Only</description>
\r
613 /// <description><b>True</b> - Open the database for read only access<br/><b>False</b> - Open the database for normal read/write access</description>
\r
614 /// <description>N</description>
\r
615 /// <description>False</description>
\r
618 /// <description>Max Pool Size</description>
\r
619 /// <description>The maximum number of connections for the given connection string that can be in the connection pool</description>
\r
620 /// <description>N</description>
\r
621 /// <description>100</description>
\r
624 /// <description>Default IsolationLevel</description>
\r
625 /// <description>The default transaciton isolation level</description>
\r
626 /// <description>N</description>
\r
627 /// <description>Serializable</description>
\r
631 #if !PLATFORM_COMPACTFRAMEWORK
\r
632 [RefreshProperties(RefreshProperties.All), DefaultValue("")]
\r
633 [Editor("SQLite.Designer.SqliteConnectionStringEditor, SQLite.Designer, Version=1.0.36.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139", "System.Drawing.Design.UITypeEditor, System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")]
\r
635 public override string ConnectionString
\r
639 return _connectionString;
\r
644 throw new ArgumentNullException();
\r
646 else if (_connectionState != ConnectionState.Closed)
\r
647 throw new InvalidOperationException();
\r
649 _connectionString = value;
\r
654 /// Create a new SqliteCommand and associate it with this connection.
\r
656 /// <returns>Returns an instantiated SqliteCommand object already assigned to this connection.</returns>
\r
657 public new SqliteCommand CreateCommand()
\r
659 return new SqliteCommand(this);
\r
663 /// Forwards to the local CreateCommand() function
\r
665 /// <returns></returns>
\r
666 protected override DbCommand CreateDbCommand()
\r
668 return CreateCommand();
\r
672 /// Returns the filename without extension or path
\r
674 #if !PLATFORM_COMPACTFRAMEWORK
\r
675 [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
\r
677 public override string DataSource
\r
681 return _dataSource;
\r
686 /// Returns an empty string
\r
688 #if !PLATFORM_COMPACTFRAMEWORK
\r
689 [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
\r
691 public override string Database
\r
700 /// Maps mono-specific connection string keywords to the standard ones
\r
702 /// <returns>The mapped keyword name</returns>
\r
703 internal static void MapMonoKeyword (string[] arPiece, SortedList<string, string> ls)
\r
705 string keyword, value;
\r
707 switch (arPiece[0].ToLower (CultureInfo.InvariantCulture)) {
\r
709 keyword = "Data Source";
\r
710 value = MapMonoUriPath (arPiece[1]);
\r
714 keyword = arPiece[0];
\r
715 value = arPiece[1];
\r
719 ls.Add(keyword, value);
\r
722 internal static string MapMonoUriPath (string path)
\r
724 if (path.StartsWith ("file://")) {
\r
725 return path.Substring (7);
\r
726 } else if (path.StartsWith ("file:")) {
\r
727 return path.Substring (5);
\r
728 } else if (path.StartsWith ("/")) {
\r
731 throw new InvalidOperationException ("Invalid connection string: invalid URI");
\r
735 internal static string MapUriPath(string path)
\r
737 if (path.StartsWith ("file://"))
\r
738 return path.Substring (7);
\r
739 else if (path.StartsWith ("file:"))
\r
740 return path.Substring (5);
\r
741 else if (path.StartsWith ("/"))
\r
744 throw new InvalidOperationException ("Invalid connection string: invalid URI");
\r
748 /// Parses the connection string into component parts
\r
750 /// <param name="connectionString">The connection string to parse</param>
\r
751 /// <returns>An array of key-value pairs representing each parameter of the connection string</returns>
\r
752 internal static SortedList<string, string> ParseConnectionString(string connectionString)
\r
754 string s = connectionString.Replace (',', ';'); // Mono compatibility
\r
756 SortedList<string, string> ls = new SortedList<string, string>(StringComparer.OrdinalIgnoreCase);
\r
758 // First split into semi-colon delimited values. The Split() function of SQLiteBase accounts for and properly
\r
759 // skips semi-colons in quoted strings
\r
760 string[] arParts = SqliteConvert.Split(s, ';');
\r
763 int x = arParts.Length;
\r
764 // For each semi-colon piece, split into key and value pairs by the presence of the = sign
\r
765 for (n = 0; n < x; n++)
\r
767 arPiece = SqliteConvert.Split(arParts[n], '=');
\r
768 if (arPiece.Length == 2)
\r
770 MapMonoKeyword (arPiece, ls);
\r
772 else throw new ArgumentException(String.Format(CultureInfo.CurrentCulture, "Invalid ConnectionString format for parameter \"{0}\"", (arPiece.Length > 0) ? arPiece[0] : "null"));
\r
777 #if !PLATFORM_COMPACTFRAMEWORK
\r
779 /// Manual distributed transaction enlistment support
\r
781 /// <param name="transaction">The distributed transaction to enlist in</param>
\r
782 public override void EnlistTransaction(System.Transactions.Transaction transaction)
\r
784 if (_transactionLevel > 0 && transaction != null)
\r
785 throw new ArgumentException("Unable to enlist in transaction, a local transaction already exists");
\r
787 if (_enlistment != null && transaction != _enlistment._scope)
\r
788 throw new ArgumentException("Already enlisted in a transaction");
\r
790 _enlistment = new SQLiteEnlistment(this, transaction);
\r
795 /// Looks for a key in the array of key/values of the parameter string. If not found, return the specified default value
\r
797 /// <param name="items">The list to look in</param>
\r
798 /// <param name="key">The key to find</param>
\r
799 /// <param name="defValue">The default value to return if the key is not found</param>
\r
800 /// <returns>The value corresponding to the specified key, or the default value if not found.</returns>
\r
801 static internal string FindKey(SortedList<string, string> items, string key, string defValue)
\r
805 if (items.TryGetValue(key, out ret)) return ret;
\r
811 /// Opens the connection using the parameters found in the <see cref="ConnectionString">ConnectionString</see>
\r
813 public override void Open()
\r
815 if (_connectionState != ConnectionState.Closed)
\r
816 throw new InvalidOperationException();
\r
820 SortedList<string, string> opts = ParseConnectionString(_connectionString);
\r
823 if (Convert.ToInt32(FindKey(opts, "Version", "3"), CultureInfo.InvariantCulture) != 3)
\r
824 throw new NotSupportedException("Only SQLite Version 3 is supported at this time");
\r
826 fileName = FindKey(opts, "Data Source", "");
\r
828 if (String.IsNullOrEmpty(fileName))
\r
830 fileName = FindKey(opts, "Uri", "");
\r
831 if (String.IsNullOrEmpty(fileName))
\r
832 throw new ArgumentException("Data Source cannot be empty. Use :memory: to open an in-memory database");
\r
834 fileName = MapUriPath(fileName);
\r
837 if (String.Compare(fileName, ":MEMORY:", true, CultureInfo.InvariantCulture) == 0)
\r
838 fileName = ":memory:";
\r
841 #if PLATFORM_COMPACTFRAMEWORK
\r
842 if (fileName.StartsWith(".\\"))
\r
843 fileName = Path.GetDirectoryName(System.Reflection.Assembly.GetCallingAssembly().GetName().CodeBase) + fileName.Substring(1);
\r
845 fileName = ExpandFileName(fileName);
\r
849 bool usePooling = (SqliteConvert.ToBoolean(FindKey(opts, "Pooling", Boolean.FalseString)) == true);
\r
850 bool bUTF16 = (SqliteConvert.ToBoolean(FindKey(opts, "UseUTF16Encoding", Boolean.FalseString)) == true);
\r
851 int maxPoolSize = Convert.ToInt32(FindKey(opts, "Max Pool Size", "100"));
\r
853 _defaultTimeout = Convert.ToInt32(FindKey(opts, "Default Timeout", "30"), CultureInfo.CurrentCulture);
\r
855 _defaultIsolation = (IsolationLevel)Enum.Parse(typeof(IsolationLevel), FindKey(opts, "Default IsolationLevel", "Serializable"), true);
\r
856 if (_defaultIsolation != IsolationLevel.Serializable && _defaultIsolation != IsolationLevel.ReadCommitted)
\r
857 throw new NotSupportedException("Invalid Default IsolationLevel specified");
\r
859 SQLiteDateFormats dateFormat = (SQLiteDateFormats)Enum.Parse(typeof(SQLiteDateFormats), FindKey(opts, "DateTimeFormat", "ISO8601"), true);
\r
860 //string temp = FindKey(opts, "DateTimeFormat", "ISO8601");
\r
861 //if (String.Compare(temp, "ticks", true, CultureInfo.InvariantCulture) == 0) dateFormat = SQLiteDateFormats.Ticks;
\r
862 //else if (String.Compare(temp, "julianday", true, CultureInfo.InvariantCulture) == 0) dateFormat = SQLiteDateFormats.JulianDay;
\r
864 if (bUTF16) // SQLite automatically sets the encoding of the database to UTF16 if called from sqlite3_open16()
\r
865 _sql = new SQLite3_UTF16(dateFormat);
\r
867 _sql = new SQLite3(dateFormat);
\r
869 SQLiteOpenFlagsEnum flags = SQLiteOpenFlagsEnum.None;
\r
871 if (SqliteConvert.ToBoolean(FindKey(opts, "Read Only", Boolean.FalseString)) == true)
\r
872 flags |= SQLiteOpenFlagsEnum.ReadOnly;
\r
874 flags |= SQLiteOpenFlagsEnum.ReadWrite;
\r
875 if (SqliteConvert.ToBoolean(FindKey(opts, "FailIfMissing", Boolean.FalseString)) == false)
\r
876 flags |= SQLiteOpenFlagsEnum.Create;
\r
878 if (SqliteConvert.ToBoolean (FindKey (opts, "FileProtectionComplete", Boolean.FalseString)))
\r
879 flags |= SQLiteOpenFlagsEnum.FileProtectionComplete;
\r
880 if (SqliteConvert.ToBoolean (FindKey (opts, "FileProtectionCompleteUnlessOpen", Boolean.FalseString)))
\r
881 flags |= SQLiteOpenFlagsEnum.FileProtectionCompleteUnlessOpen;
\r
882 if (SqliteConvert.ToBoolean (FindKey (opts, "FileProtectionCompleteUntilFirstUserAuthentication", Boolean.FalseString)))
\r
883 flags |= SQLiteOpenFlagsEnum.FileProtectionCompleteUntilFirstUserAuthentication;
\r
884 if (SqliteConvert.ToBoolean (FindKey (opts, "FileProtectionNone", Boolean.FalseString)))
\r
885 flags |= SQLiteOpenFlagsEnum.FileProtectionNone;
\r
888 _sql.Open(fileName, flags, maxPoolSize, usePooling);
\r
890 _binaryGuid = (SqliteConvert.ToBoolean(FindKey(opts, "BinaryGUID", Boolean.TrueString)) == true);
\r
892 string password = FindKey(opts, "Password", null);
\r
894 if (String.IsNullOrEmpty(password) == false)
\r
895 _sql.SetPassword(System.Text.UTF8Encoding.UTF8.GetBytes(password));
\r
896 else if (_password != null)
\r
897 _sql.SetPassword(_password);
\r
900 _dataSource = Path.GetFileNameWithoutExtension(fileName);
\r
902 OnStateChange(ConnectionState.Open);
\r
905 using (SqliteCommand cmd = CreateCommand())
\r
909 if (fileName != ":memory:")
\r
911 defValue = FindKey(opts, "Page Size", "1024");
\r
912 if (Convert.ToInt32(defValue, CultureInfo.InvariantCulture) != 1024)
\r
914 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA page_size={0}", defValue);
\r
915 cmd.ExecuteNonQuery();
\r
919 defValue = FindKey(opts, "Max Page Count", "0");
\r
920 if (Convert.ToInt32(defValue, CultureInfo.InvariantCulture) != 0)
\r
922 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA max_page_count={0}", defValue);
\r
923 cmd.ExecuteNonQuery();
\r
926 defValue = FindKey(opts, "Legacy Format", Boolean.FalseString);
\r
927 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA legacy_file_format={0}", SqliteConvert.ToBoolean(defValue) == true ? "ON" : "OFF");
\r
928 cmd.ExecuteNonQuery();
\r
930 defValue = FindKey(opts, "Synchronous", "Normal");
\r
931 if (String.Compare(defValue, "Full", StringComparison.OrdinalIgnoreCase) != 0)
\r
933 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA synchronous={0}", defValue);
\r
934 cmd.ExecuteNonQuery();
\r
937 defValue = FindKey(opts, "Cache Size", "2000");
\r
938 if (Convert.ToInt32(defValue, CultureInfo.InvariantCulture) != 2000)
\r
940 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA cache_size={0}", defValue);
\r
941 cmd.ExecuteNonQuery();
\r
944 defValue = FindKey(opts, "Journal Mode", "Delete");
\r
945 if (String.Compare(defValue, "Default", StringComparison.OrdinalIgnoreCase) != 0)
\r
947 cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "PRAGMA journal_mode={0}", defValue);
\r
948 cmd.ExecuteNonQuery();
\r
952 if (_commitHandler != null)
\r
953 _sql.SetCommitHook(_commitCallback);
\r
955 if (_updateHandler != null)
\r
956 _sql.SetUpdateHook(_updateCallback);
\r
958 if (_rollbackHandler != null)
\r
959 _sql.SetRollbackHook(_rollbackCallback);
\r
961 #if !PLATFORM_COMPACTFRAMEWORK
\r
962 if (global::System.Transactions.Transaction.Current != null && SqliteConvert.ToBoolean(FindKey(opts, "Enlist", Boolean.TrueString)) == true)
\r
963 EnlistTransaction(global::System.Transactions.Transaction.Current);
\r
966 catch (SqliteException)
\r
974 /// Gets/sets the default command timeout for newly-created commands. This is especially useful for
\r
975 /// commands used internally such as inside a SqliteTransaction, where setting the timeout is not possible.
\r
976 /// This can also be set in the ConnectionString with "Default Timeout"
\r
978 public int DefaultTimeout
\r
980 get { return _defaultTimeout; }
\r
981 set { _defaultTimeout = value; }
\r
985 /// Returns the version of the underlying SQLite database engine
\r
987 #if !PLATFORM_COMPACTFRAMEWORK
\r
988 [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
\r
990 public override string ServerVersion
\r
994 if (_connectionState != ConnectionState.Open)
\r
995 throw new InvalidOperationException();
\r
997 return _sql.Version;
\r
1002 /// Returns the version of the underlying SQLite database engine
\r
1004 public static string SQLiteVersion
\r
1006 get { return SQLite3.SQLiteVersion; }
\r
1010 /// Returns the state of the connection.
\r
1012 #if !PLATFORM_COMPACTFRAMEWORK
\r
1013 [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
\r
1015 public override ConnectionState State
\r
1019 return _connectionState;
\r
1024 /// Change the password (or assign a password) to an open database.
\r
1027 /// No readers or writers may be active for this process. The database must already be open
\r
1028 /// and if it already was password protected, the existing password must already have been supplied.
\r
1030 /// <param name="newPassword">The new password to assign to the database</param>
\r
1031 public void ChangePassword(string newPassword)
\r
1033 ChangePassword(String.IsNullOrEmpty(newPassword) ? null : System.Text.UTF8Encoding.UTF8.GetBytes(newPassword));
\r
1037 /// Change the password (or assign a password) to an open database.
\r
1040 /// No readers or writers may be active for this process. The database must already be open
\r
1041 /// and if it already was password protected, the existing password must already have been supplied.
\r
1043 /// <param name="newPassword">The new password to assign to the database</param>
\r
1044 public void ChangePassword(byte[] newPassword)
\r
1046 if (_connectionState != ConnectionState.Open)
\r
1047 throw new InvalidOperationException("Database must be opened before changing the password.");
\r
1049 _sql.ChangePassword(newPassword);
\r
1053 /// Sets the password for a password-protected database. A password-protected database is
\r
1054 /// unusable for any operation until the password has been set.
\r
1056 /// <param name="databasePassword">The password for the database</param>
\r
1057 public void SetPassword(string databasePassword)
\r
1059 SetPassword(String.IsNullOrEmpty(databasePassword) ? null : System.Text.UTF8Encoding.UTF8.GetBytes(databasePassword));
\r
1063 /// Sets the password for a password-protected database. A password-protected database is
\r
1064 /// unusable for any operation until the password has been set.
\r
1066 /// <param name="databasePassword">The password for the database</param>
\r
1067 public void SetPassword(byte[] databasePassword)
\r
1069 if (_connectionState != ConnectionState.Closed)
\r
1070 throw new InvalidOperationException("Password can only be set before the database is opened.");
\r
1072 if (databasePassword != null)
\r
1073 if (databasePassword.Length == 0) databasePassword = null;
\r
1075 _password = databasePassword;
\r
1079 /// Expand the filename of the data source, resolving the |DataDirectory| macro as appropriate.
\r
1081 /// <param name="sourceFile">The database filename to expand</param>
\r
1082 /// <returns>The expanded path and filename of the filename</returns>
\r
1083 private string ExpandFileName(string sourceFile)
\r
1085 if (String.IsNullOrEmpty(sourceFile)) return sourceFile;
\r
1087 if (sourceFile.StartsWith(_dataDirectory, StringComparison.OrdinalIgnoreCase))
\r
1089 string dataDirectory;
\r
1091 #if PLATFORM_COMPACTFRAMEWORK
\r
1092 dataDirectory = Path.GetDirectoryName(System.Reflection.Assembly.GetCallingAssembly().GetName().CodeBase);
\r
1094 dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory") as string;
\r
1095 if (String.IsNullOrEmpty(dataDirectory))
\r
1096 dataDirectory = AppDomain.CurrentDomain.BaseDirectory;
\r
1099 if (sourceFile.Length > _dataDirectory.Length)
\r
1101 if (sourceFile[_dataDirectory.Length] == Path.DirectorySeparatorChar ||
\r
1102 sourceFile[_dataDirectory.Length] == Path.AltDirectorySeparatorChar)
\r
1103 sourceFile = sourceFile.Remove(_dataDirectory.Length, 1);
\r
1105 sourceFile = Path.Combine(dataDirectory, sourceFile.Substring(_dataDirectory.Length));
\r
1108 #if !PLATFORM_COMPACTFRAMEWORK
\r
1109 sourceFile = Path.GetFullPath(sourceFile);
\r
1112 return sourceFile;
\r
1116 /// The following commands are used to extract schema information out of the database. Valid schema types are:
\r
1117 /// <list type="bullet">
\r
1119 /// <description>MetaDataCollections</description>
\r
1122 /// <description>DataSourceInformation</description>
\r
1125 /// <description>Catalogs</description>
\r
1128 /// <description>Columns</description>
\r
1131 /// <description>ForeignKeys</description>
\r
1134 /// <description>Indexes</description>
\r
1137 /// <description>IndexColumns</description>
\r
1140 /// <description>Tables</description>
\r
1143 /// <description>Views</description>
\r
1146 /// <description>ViewColumns</description>
\r
1151 /// Returns the MetaDataCollections schema
\r
1153 /// <returns>A DataTable of the MetaDataCollections schema</returns>
\r
1154 public override DataTable GetSchema()
\r
1156 return GetSchema("MetaDataCollections", null);
\r
1160 /// Returns schema information of the specified collection
\r
1162 /// <param name="collectionName">The schema collection to retrieve</param>
\r
1163 /// <returns>A DataTable of the specified collection</returns>
\r
1164 public override DataTable GetSchema(string collectionName)
\r
1166 return GetSchema(collectionName, new string[0]);
\r
1170 /// Retrieves schema information using the specified constraint(s) for the specified collection
\r
1172 /// <param name="collectionName">The collection to retrieve</param>
\r
1173 /// <param name="restrictionValues">The restrictions to impose</param>
\r
1174 /// <returns>A DataTable of the specified collection</returns>
\r
1175 public override DataTable GetSchema(string collectionName, string[] restrictionValues)
\r
1177 if (_connectionState != ConnectionState.Open)
\r
1178 throw new InvalidOperationException();
\r
1180 string[] parms = new string[5];
\r
1182 if (restrictionValues == null) restrictionValues = new string[0];
\r
1183 restrictionValues.CopyTo(parms, 0);
\r
1185 switch (collectionName.ToUpper(CultureInfo.InvariantCulture))
\r
1187 case "METADATACOLLECTIONS":
\r
1188 return Schema_MetaDataCollections();
\r
1189 case "DATASOURCEINFORMATION":
\r
1190 return Schema_DataSourceInformation();
\r
1192 return Schema_DataTypes();
\r
1194 case "TABLECOLUMNS":
\r
1195 return Schema_Columns(parms[0], parms[2], parms[3]);
\r
1197 return Schema_Indexes(parms[0], parms[2], parms[3]);
\r
1199 return Schema_Triggers(parms[0], parms[2], parms[3]);
\r
1200 case "INDEXCOLUMNS":
\r
1201 return Schema_IndexColumns(parms[0], parms[2], parms[3], parms[4]);
\r
1203 return Schema_Tables(parms[0], parms[2], parms[3]);
\r
1205 return Schema_Views(parms[0], parms[2]);
\r
1206 case "VIEWCOLUMNS":
\r
1207 return Schema_ViewColumns(parms[0], parms[2], parms[3]);
\r
1208 case "FOREIGNKEYS":
\r
1209 return Schema_ForeignKeys(parms[0], parms[2], parms[3]);
\r
1211 return Schema_Catalogs(parms[0]);
\r
1212 case "RESERVEDWORDS":
\r
1213 return Schema_ReservedWords();
\r
1215 throw new NotSupportedException();
\r
1218 private static DataTable Schema_ReservedWords()
\r
1220 DataTable tbl = new DataTable("MetaDataCollections");
\r
1222 tbl.Locale = CultureInfo.InvariantCulture;
\r
1223 tbl.Columns.Add("ReservedWord", typeof(string));
\r
1224 tbl.Columns.Add("MaximumVersion", typeof(string));
\r
1225 tbl.Columns.Add("MinimumVersion", typeof(string));
\r
1227 tbl.BeginLoadData();
\r
1229 foreach (string word in SR.Keywords.Split(new char[] { ',' }))
\r
1231 row = tbl.NewRow();
\r
1233 tbl.Rows.Add(row);
\r
1236 tbl.AcceptChanges();
\r
1237 tbl.EndLoadData();
\r
1243 /// Builds a MetaDataCollections schema datatable
\r
1245 /// <returns>DataTable</returns>
\r
1246 private static DataTable Schema_MetaDataCollections()
\r
1248 DataTable tbl = new DataTable("MetaDataCollections");
\r
1250 tbl.Locale = CultureInfo.InvariantCulture;
\r
1251 tbl.Columns.Add("CollectionName", typeof(string));
\r
1252 tbl.Columns.Add("NumberOfRestrictions", typeof(int));
\r
1253 tbl.Columns.Add("NumberOfIdentifierParts", typeof(int));
\r
1255 tbl.BeginLoadData();
\r
1257 StringReader reader = new StringReader(SR.MetaDataCollections);
\r
1258 tbl.ReadXml(reader);
\r
1261 tbl.AcceptChanges();
\r
1262 tbl.EndLoadData();
\r
1268 /// Builds a DataSourceInformation datatable
\r
1270 /// <returns>DataTable</returns>
\r
1271 private DataTable Schema_DataSourceInformation()
\r
1273 DataTable tbl = new DataTable("DataSourceInformation");
\r
1276 tbl.Locale = CultureInfo.InvariantCulture;
\r
1277 tbl.Columns.Add(DbMetaDataColumnNames.CompositeIdentifierSeparatorPattern, typeof(string));
\r
1278 tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductName, typeof(string));
\r
1279 tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductVersion, typeof(string));
\r
1280 tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductVersionNormalized, typeof(string));
\r
1281 tbl.Columns.Add(DbMetaDataColumnNames.GroupByBehavior, typeof(int));
\r
1282 tbl.Columns.Add(DbMetaDataColumnNames.IdentifierPattern, typeof(string));
\r
1283 tbl.Columns.Add(DbMetaDataColumnNames.IdentifierCase, typeof(int));
\r
1284 tbl.Columns.Add(DbMetaDataColumnNames.OrderByColumnsInSelect, typeof(bool));
\r
1285 tbl.Columns.Add(DbMetaDataColumnNames.ParameterMarkerFormat, typeof(string));
\r
1286 tbl.Columns.Add(DbMetaDataColumnNames.ParameterMarkerPattern, typeof(string));
\r
1287 tbl.Columns.Add(DbMetaDataColumnNames.ParameterNameMaxLength, typeof(int));
\r
1288 tbl.Columns.Add(DbMetaDataColumnNames.ParameterNamePattern, typeof(string));
\r
1289 tbl.Columns.Add(DbMetaDataColumnNames.QuotedIdentifierPattern, typeof(string));
\r
1290 tbl.Columns.Add(DbMetaDataColumnNames.QuotedIdentifierCase, typeof(int));
\r
1291 tbl.Columns.Add(DbMetaDataColumnNames.StatementSeparatorPattern, typeof(string));
\r
1292 tbl.Columns.Add(DbMetaDataColumnNames.StringLiteralPattern, typeof(string));
\r
1293 tbl.Columns.Add(DbMetaDataColumnNames.SupportedJoinOperators, typeof(int));
\r
1295 tbl.BeginLoadData();
\r
1297 row = tbl.NewRow();
\r
1298 row.ItemArray = new object[] {
\r
1304 @"(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\""[^\""\0]|\""\""+\""$)",
\r
1308 @"@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)",
\r
1310 @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)",
\r
1311 @"(([^\[]|\]\])*)",
\r
1314 @"'(([^']|'')*)'",
\r
1317 tbl.Rows.Add(row);
\r
1319 tbl.AcceptChanges();
\r
1320 tbl.EndLoadData();
\r
1326 /// Build a Columns schema
\r
1328 /// <param name="strCatalog">The catalog (attached database) to query, can be null</param>
\r
1329 /// <param name="strTable">The table to retrieve schema information for, must not be null</param>
\r
1330 /// <param name="strColumn">The column to retrieve schema information for, can be null</param>
\r
1331 /// <returns>DataTable</returns>
\r
1332 private DataTable Schema_Columns(string strCatalog, string strTable, string strColumn)
\r
1334 DataTable tbl = new DataTable("Columns");
\r
1337 tbl.Locale = CultureInfo.InvariantCulture;
\r
1338 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
\r
1339 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
\r
1340 tbl.Columns.Add("TABLE_NAME", typeof(string));
\r
1341 tbl.Columns.Add("COLUMN_NAME", typeof(string));
\r
1342 tbl.Columns.Add("COLUMN_GUID", typeof(Guid));
\r
1343 tbl.Columns.Add("COLUMN_PROPID", typeof(long));
\r
1344 tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
\r
1345 tbl.Columns.Add("COLUMN_HASDEFAULT", typeof(bool));
\r
1346 tbl.Columns.Add("COLUMN_DEFAULT", typeof(string));
\r
1347 tbl.Columns.Add("COLUMN_FLAGS", typeof(long));
\r
1348 tbl.Columns.Add("IS_NULLABLE", typeof(bool));
\r
1349 tbl.Columns.Add("DATA_TYPE", typeof(string));
\r
1350 tbl.Columns.Add("TYPE_GUID", typeof(Guid));
\r
1351 tbl.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int));
\r
1352 tbl.Columns.Add("CHARACTER_OCTET_LENGTH", typeof(int));
\r
1353 tbl.Columns.Add("NUMERIC_PRECISION", typeof(int));
\r
1354 tbl.Columns.Add("NUMERIC_SCALE", typeof(int));
\r
1355 tbl.Columns.Add("DATETIME_PRECISION", typeof(long));
\r
1356 tbl.Columns.Add("CHARACTER_SET_CATALOG", typeof(string));
\r
1357 tbl.Columns.Add("CHARACTER_SET_SCHEMA", typeof(string));
\r
1358 tbl.Columns.Add("CHARACTER_SET_NAME", typeof(string));
\r
1359 tbl.Columns.Add("COLLATION_CATALOG", typeof(string));
\r
1360 tbl.Columns.Add("COLLATION_SCHEMA", typeof(string));
\r
1361 tbl.Columns.Add("COLLATION_NAME", typeof(string));
\r
1362 tbl.Columns.Add("DOMAIN_CATALOG", typeof(string));
\r
1363 tbl.Columns.Add("DOMAIN_NAME", typeof(string));
\r
1364 tbl.Columns.Add("DESCRIPTION", typeof(string));
\r
1365 tbl.Columns.Add("PRIMARY_KEY", typeof(bool));
\r
1366 tbl.Columns.Add("EDM_TYPE", typeof(string));
\r
1367 tbl.Columns.Add("AUTOINCREMENT", typeof(bool));
\r
1368 tbl.Columns.Add("UNIQUE", typeof(bool));
\r
1370 tbl.BeginLoadData();
\r
1372 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
\r
1374 string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
\r
1376 using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table' OR [type] LIKE 'view'", strCatalog, master), this))
\r
1377 using (SqliteDataReader rdTables = cmdTables.ExecuteReader())
\r
1379 while (rdTables.Read())
\r
1381 if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), true, CultureInfo.InvariantCulture) == 0)
\r
1385 using (SqliteCommand cmd = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
\r
1386 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader(CommandBehavior.SchemaOnly))
\r
1387 using (DataTable tblSchema = rd.GetSchemaTable(true, true))
\r
1389 foreach (DataRow schemaRow in tblSchema.Rows)
\r
1391 if (String.Compare(schemaRow[SchemaTableColumn.ColumnName].ToString(), strColumn, true, CultureInfo.InvariantCulture) == 0
\r
1392 || strColumn == null)
\r
1394 row = tbl.NewRow();
\r
1396 row["NUMERIC_PRECISION"] = schemaRow[SchemaTableColumn.NumericPrecision];
\r
1397 row["NUMERIC_SCALE"] = schemaRow[SchemaTableColumn.NumericScale];
\r
1398 row["TABLE_NAME"] = rdTables.GetString(2);
\r
1399 row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.ColumnName];
\r
1400 row["TABLE_CATALOG"] = strCatalog;
\r
1401 row["ORDINAL_POSITION"] = schemaRow[SchemaTableColumn.ColumnOrdinal];
\r
1402 row["COLUMN_HASDEFAULT"] = (schemaRow[SchemaTableOptionalColumn.DefaultValue] != DBNull.Value);
\r
1403 row["COLUMN_DEFAULT"] = schemaRow[SchemaTableOptionalColumn.DefaultValue];
\r
1404 row["IS_NULLABLE"] = schemaRow[SchemaTableColumn.AllowDBNull];
\r
1405 row["DATA_TYPE"] = schemaRow["DataTypeName"].ToString().ToLower(CultureInfo.InvariantCulture);
\r
1406 row["EDM_TYPE"] = SqliteConvert.DbTypeToTypeName((DbType)schemaRow[SchemaTableColumn.ProviderType]).ToString().ToLower(CultureInfo.InvariantCulture);
\r
1407 row["CHARACTER_MAXIMUM_LENGTH"] = schemaRow[SchemaTableColumn.ColumnSize];
\r
1408 row["TABLE_SCHEMA"] = schemaRow[SchemaTableColumn.BaseSchemaName];
\r
1409 row["PRIMARY_KEY"] = schemaRow[SchemaTableColumn.IsKey];
\r
1410 row["AUTOINCREMENT"] = schemaRow[SchemaTableOptionalColumn.IsAutoIncrement];
\r
1411 row["COLLATION_NAME"] = schemaRow["CollationType"];
\r
1412 row["UNIQUE"] = schemaRow[SchemaTableColumn.IsUnique];
\r
1413 tbl.Rows.Add(row);
\r
1418 catch(SqliteException)
\r
1425 tbl.AcceptChanges();
\r
1426 tbl.EndLoadData();
\r
1432 /// Returns index information for the given database and catalog
\r
1434 /// <param name="strCatalog">The catalog (attached database) to query, can be null</param>
\r
1435 /// <param name="strIndex">The name of the index to retrieve information for, can be null</param>
\r
1436 /// <param name="strTable">The table to retrieve index information for, can be null</param>
\r
1437 /// <returns>DataTable</returns>
\r
1438 private DataTable Schema_Indexes(string strCatalog, string strTable, string strIndex)
\r
1440 DataTable tbl = new DataTable("Indexes");
\r
1442 List<int> primaryKeys = new List<int>();
\r
1445 tbl.Locale = CultureInfo.InvariantCulture;
\r
1446 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
\r
1447 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
\r
1448 tbl.Columns.Add("TABLE_NAME", typeof(string));
\r
1449 tbl.Columns.Add("INDEX_CATALOG", typeof(string));
\r
1450 tbl.Columns.Add("INDEX_SCHEMA", typeof(string));
\r
1451 tbl.Columns.Add("INDEX_NAME", typeof(string));
\r
1452 tbl.Columns.Add("PRIMARY_KEY", typeof(bool));
\r
1453 tbl.Columns.Add("UNIQUE", typeof(bool));
\r
1454 tbl.Columns.Add("CLUSTERED", typeof(bool));
\r
1455 tbl.Columns.Add("TYPE", typeof(int));
\r
1456 tbl.Columns.Add("FILL_FACTOR", typeof(int));
\r
1457 tbl.Columns.Add("INITIAL_SIZE", typeof(int));
\r
1458 tbl.Columns.Add("NULLS", typeof(int));
\r
1459 tbl.Columns.Add("SORT_BOOKMARKS", typeof(bool));
\r
1460 tbl.Columns.Add("AUTO_UPDATE", typeof(bool));
\r
1461 tbl.Columns.Add("NULL_COLLATION", typeof(int));
\r
1462 tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
\r
1463 tbl.Columns.Add("COLUMN_NAME", typeof(string));
\r
1464 tbl.Columns.Add("COLUMN_GUID", typeof(Guid));
\r
1465 tbl.Columns.Add("COLUMN_PROPID", typeof(long));
\r
1466 tbl.Columns.Add("COLLATION", typeof(short));
\r
1467 tbl.Columns.Add("CARDINALITY", typeof(Decimal));
\r
1468 tbl.Columns.Add("PAGES", typeof(int));
\r
1469 tbl.Columns.Add("FILTER_CONDITION", typeof(string));
\r
1470 tbl.Columns.Add("INTEGRATED", typeof(bool));
\r
1471 tbl.Columns.Add("INDEX_DEFINITION", typeof(string));
\r
1473 tbl.BeginLoadData();
\r
1475 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
\r
1477 string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
\r
1479 using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this))
\r
1480 using (SqliteDataReader rdTables = cmdTables.ExecuteReader())
\r
1482 while (rdTables.Read())
\r
1484 maybeRowId = false;
\r
1485 primaryKeys.Clear();
\r
1486 if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0)
\r
1488 // First, look for any rowid indexes -- which sqlite defines are INTEGER PRIMARY KEY columns.
\r
1489 // Such indexes are not listed in the indexes list but count as indexes just the same.
\r
1492 using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].table_info([{1}])", strCatalog, rdTables.GetString(2)), this))
\r
1493 using (SqliteDataReader rdTable = cmdTable.ExecuteReader())
\r
1495 while (rdTable.Read())
\r
1497 if (rdTable.GetInt32(5) == 1)
\r
1499 primaryKeys.Add(rdTable.GetInt32(0));
\r
1501 // If the primary key is of type INTEGER, then its a rowid and we need to make a fake index entry for it.
\r
1502 if (String.Compare(rdTable.GetString(2), "INTEGER", true, CultureInfo.InvariantCulture) == 0)
\r
1503 maybeRowId = true;
\r
1508 catch (SqliteException)
\r
1511 if (primaryKeys.Count == 1 && maybeRowId == true)
\r
1513 row = tbl.NewRow();
\r
1515 row["TABLE_CATALOG"] = strCatalog;
\r
1516 row["TABLE_NAME"] = rdTables.GetString(2);
\r
1517 row["INDEX_CATALOG"] = strCatalog;
\r
1518 row["PRIMARY_KEY"] = true;
\r
1519 row["INDEX_NAME"] = String.Format(CultureInfo.InvariantCulture, "{1}_PK_{0}", rdTables.GetString(2), master);
\r
1520 row["UNIQUE"] = true;
\r
1522 if (String.Compare((string)row["INDEX_NAME"], strIndex, true, CultureInfo.InvariantCulture) == 0
\r
1523 || strIndex == null)
\r
1525 tbl.Rows.Add(row);
\r
1528 primaryKeys.Clear();
\r
1531 // Now fetch all the rest of the indexes.
\r
1534 using (SqliteCommand cmd = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_list([{1}])", strCatalog, rdTables.GetString(2)), this))
\r
1535 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader())
\r
1539 if (String.Compare(rd.GetString(1), strIndex, true, CultureInfo.InvariantCulture) == 0
\r
1540 || strIndex == null)
\r
1542 row = tbl.NewRow();
\r
1544 row["TABLE_CATALOG"] = strCatalog;
\r
1545 row["TABLE_NAME"] = rdTables.GetString(2);
\r
1546 row["INDEX_CATALOG"] = strCatalog;
\r
1547 row["INDEX_NAME"] = rd.GetString(1);
\r
1548 row["UNIQUE"] = rd.GetBoolean(2);
\r
1549 row["PRIMARY_KEY"] = false;
\r
1551 // get the index definition
\r
1552 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))
\r
1553 using (SqliteDataReader rdIndexes = cmdIndexes.ExecuteReader())
\r
1555 while (rdIndexes.Read())
\r
1557 if (rdIndexes.IsDBNull(4) == false)
\r
1559 row["INDEX_DEFINITION"] = rdIndexes.GetString(4);
\r
1565 // Now for the really hard work. Figure out which index is the primary key index.
\r
1566 // The only way to figure it out is to check if the index was an autoindex and if we have a non-rowid
\r
1567 // primary key, and all the columns in the given index match the primary key columns
\r
1568 if (primaryKeys.Count > 0 && rd.GetString(1).StartsWith("sqlite_autoindex_" + rdTables.GetString(2), StringComparison.InvariantCultureIgnoreCase) == true)
\r
1570 using (SqliteCommand cmdDetails = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rd.GetString(1)), this))
\r
1571 using (SqliteDataReader rdDetails = cmdDetails.ExecuteReader())
\r
1574 while (rdDetails.Read())
\r
1576 if (primaryKeys.Contains(rdDetails.GetInt32(1)) == false)
\r
1583 if (nMatches == primaryKeys.Count)
\r
1585 row["PRIMARY_KEY"] = true;
\r
1586 primaryKeys.Clear();
\r
1591 tbl.Rows.Add(row);
\r
1596 catch (SqliteException)
\r
1603 tbl.AcceptChanges();
\r
1604 tbl.EndLoadData();
\r
1609 private DataTable Schema_Triggers(string catalog, string table, string triggerName)
\r
1611 DataTable tbl = new DataTable("Triggers");
\r
1614 tbl.Locale = CultureInfo.InvariantCulture;
\r
1615 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
\r
1616 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
\r
1617 tbl.Columns.Add("TABLE_NAME", typeof(string));
\r
1618 tbl.Columns.Add("TRIGGER_NAME", typeof(string));
\r
1619 tbl.Columns.Add("TRIGGER_DEFINITION", typeof(string));
\r
1621 tbl.BeginLoadData();
\r
1623 if (String.IsNullOrEmpty(table)) table = null;
\r
1624 if (String.IsNullOrEmpty(catalog)) catalog = "main";
\r
1625 string master = (String.Compare(catalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
\r
1627 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))
\r
1628 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader())
\r
1632 if (String.Compare(rd.GetString(1), triggerName, true, CultureInfo.InvariantCulture) == 0
\r
1633 || triggerName == null)
\r
1635 if (table == null || String.Compare(table, rd.GetString(2), true, CultureInfo.InvariantCulture) == 0)
\r
1637 row = tbl.NewRow();
\r
1639 row["TABLE_CATALOG"] = catalog;
\r
1640 row["TABLE_NAME"] = rd.GetString(2);
\r
1641 row["TRIGGER_NAME"] = rd.GetString(1);
\r
1642 row["TRIGGER_DEFINITION"] = rd.GetString(4);
\r
1644 tbl.Rows.Add(row);
\r
1649 tbl.AcceptChanges();
\r
1650 tbl.EndLoadData();
\r
1656 /// Retrieves table schema information for the database and catalog
\r
1658 /// <param name="strCatalog">The catalog (attached database) to retrieve tables on</param>
\r
1659 /// <param name="strTable">The table to retrieve, can be null</param>
\r
1660 /// <param name="strType">The table type, can be null</param>
\r
1661 /// <returns>DataTable</returns>
\r
1662 private DataTable Schema_Tables(string strCatalog, string strTable, string strType)
\r
1664 DataTable tbl = new DataTable("Tables");
\r
1668 tbl.Locale = CultureInfo.InvariantCulture;
\r
1669 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
\r
1670 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
\r
1671 tbl.Columns.Add("TABLE_NAME", typeof(string));
\r
1672 tbl.Columns.Add("TABLE_TYPE", typeof(string));
\r
1673 tbl.Columns.Add("TABLE_ID", typeof(long));
\r
1674 tbl.Columns.Add("TABLE_ROOTPAGE", typeof(int));
\r
1675 tbl.Columns.Add("TABLE_DEFINITION", typeof(string));
\r
1676 tbl.BeginLoadData();
\r
1678 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
\r
1680 string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
\r
1682 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))
\r
1683 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader())
\r
1687 strItem = rd.GetString(0);
\r
1688 if (String.Compare(rd.GetString(2), 0, "SQLITE_", 0, 7, true, CultureInfo.InvariantCulture) == 0)
\r
1689 strItem = "SYSTEM_TABLE";
\r
1691 if (String.Compare(strType, strItem, true, CultureInfo.InvariantCulture) == 0
\r
1692 || strType == null)
\r
1694 if (String.Compare(rd.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0
\r
1695 || strTable == null)
\r
1697 row = tbl.NewRow();
\r
1699 row["TABLE_CATALOG"] = strCatalog;
\r
1700 row["TABLE_NAME"] = rd.GetString(2);
\r
1701 row["TABLE_TYPE"] = strItem;
\r
1702 row["TABLE_ID"] = rd.GetInt64(5);
\r
1703 row["TABLE_ROOTPAGE"] = rd.GetInt32(3);
\r
1704 row["TABLE_DEFINITION"] = rd.GetString(4);
\r
1706 tbl.Rows.Add(row);
\r
1712 tbl.AcceptChanges();
\r
1713 tbl.EndLoadData();
\r
1719 /// Retrieves view schema information for the database
\r
1721 /// <param name="strCatalog">The catalog (attached database) to retrieve views on</param>
\r
1722 /// <param name="strView">The view name, can be null</param>
\r
1723 /// <returns>DataTable</returns>
\r
1724 private DataTable Schema_Views(string strCatalog, string strView)
\r
1726 DataTable tbl = new DataTable("Views");
\r
1731 tbl.Locale = CultureInfo.InvariantCulture;
\r
1732 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
\r
1733 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
\r
1734 tbl.Columns.Add("TABLE_NAME", typeof(string));
\r
1735 tbl.Columns.Add("VIEW_DEFINITION", typeof(string));
\r
1736 tbl.Columns.Add("CHECK_OPTION", typeof(bool));
\r
1737 tbl.Columns.Add("IS_UPDATABLE", typeof(bool));
\r
1738 tbl.Columns.Add("DESCRIPTION", typeof(string));
\r
1739 tbl.Columns.Add("DATE_CREATED", typeof(DateTime));
\r
1740 tbl.Columns.Add("DATE_MODIFIED", typeof(DateTime));
\r
1742 tbl.BeginLoadData();
\r
1744 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
\r
1746 string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
\r
1748 using (SqliteCommand cmd = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'view'", strCatalog, master), this))
\r
1749 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader())
\r
1753 if (String.Compare(rd.GetString(1), strView, true, CultureInfo.InvariantCulture) == 0
\r
1754 || String.IsNullOrEmpty(strView))
\r
1756 strItem = rd.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ');
\r
1757 nPos = CultureInfo.InvariantCulture.CompareInfo.IndexOf(strItem, " AS ", CompareOptions.IgnoreCase);
\r
1760 strItem = strItem.Substring(nPos + 4).Trim();
\r
1761 row = tbl.NewRow();
\r
1763 row["TABLE_CATALOG"] = strCatalog;
\r
1764 row["TABLE_NAME"] = rd.GetString(2);
\r
1765 row["IS_UPDATABLE"] = false;
\r
1766 row["VIEW_DEFINITION"] = strItem;
\r
1768 tbl.Rows.Add(row);
\r
1774 tbl.AcceptChanges();
\r
1775 tbl.EndLoadData();
\r
1781 /// Retrieves catalog (attached databases) schema information for the database
\r
1783 /// <param name="strCatalog">The catalog to retrieve, can be null</param>
\r
1784 /// <returns>DataTable</returns>
\r
1785 private DataTable Schema_Catalogs(string strCatalog)
\r
1787 DataTable tbl = new DataTable("Catalogs");
\r
1790 tbl.Locale = CultureInfo.InvariantCulture;
\r
1791 tbl.Columns.Add("CATALOG_NAME", typeof(string));
\r
1792 tbl.Columns.Add("DESCRIPTION", typeof(string));
\r
1793 tbl.Columns.Add("ID", typeof(long));
\r
1795 tbl.BeginLoadData();
\r
1797 using (SqliteCommand cmd = new SqliteCommand("PRAGMA database_list", this))
\r
1798 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader())
\r
1802 if (String.Compare(rd.GetString(1), strCatalog, true, CultureInfo.InvariantCulture) == 0
\r
1803 || strCatalog == null)
\r
1805 row = tbl.NewRow();
\r
1807 row["CATALOG_NAME"] = rd.GetString(1);
\r
1808 row["DESCRIPTION"] = rd.GetString(2);
\r
1809 row["ID"] = rd.GetInt64(0);
\r
1811 tbl.Rows.Add(row);
\r
1816 tbl.AcceptChanges();
\r
1817 tbl.EndLoadData();
\r
1822 private DataTable Schema_DataTypes()
\r
1824 DataTable tbl = new DataTable("DataTypes");
\r
1826 tbl.Locale = CultureInfo.InvariantCulture;
\r
1827 tbl.Columns.Add("TypeName", typeof(String));
\r
1828 tbl.Columns.Add("ProviderDbType", typeof(int));
\r
1829 tbl.Columns.Add("ColumnSize", typeof(long));
\r
1830 tbl.Columns.Add("CreateFormat", typeof(String));
\r
1831 tbl.Columns.Add("CreateParameters", typeof(String));
\r
1832 tbl.Columns.Add("DataType", typeof(String));
\r
1833 tbl.Columns.Add("IsAutoIncrementable", typeof(bool));
\r
1834 tbl.Columns.Add("IsBestMatch", typeof(bool));
\r
1835 tbl.Columns.Add("IsCaseSensitive", typeof(bool));
\r
1836 tbl.Columns.Add("IsFixedLength", typeof(bool));
\r
1837 tbl.Columns.Add("IsFixedPrecisionScale", typeof(bool));
\r
1838 tbl.Columns.Add("IsLong", typeof(bool));
\r
1839 tbl.Columns.Add("IsNullable", typeof(bool));
\r
1840 tbl.Columns.Add("IsSearchable", typeof(bool));
\r
1841 tbl.Columns.Add("IsSearchableWithLike", typeof(bool));
\r
1842 tbl.Columns.Add("IsLiteralSupported", typeof(bool));
\r
1843 tbl.Columns.Add("LiteralPrefix", typeof(String));
\r
1844 tbl.Columns.Add("LiteralSuffix", typeof(String));
\r
1845 tbl.Columns.Add("IsUnsigned", typeof(bool));
\r
1846 tbl.Columns.Add("MaximumScale", typeof(short));
\r
1847 tbl.Columns.Add("MinimumScale", typeof(short));
\r
1848 tbl.Columns.Add("IsConcurrencyType", typeof(bool));
\r
1850 tbl.BeginLoadData();
\r
1852 StringReader reader = new StringReader(SR.DataTypes);
\r
1853 tbl.ReadXml(reader);
\r
1856 tbl.AcceptChanges();
\r
1857 tbl.EndLoadData();
\r
1863 /// Returns the base column information for indexes in a database
\r
1865 /// <param name="strCatalog">The catalog to retrieve indexes for (can be null)</param>
\r
1866 /// <param name="strTable">The table to restrict index information by (can be null)</param>
\r
1867 /// <param name="strIndex">The index to restrict index information by (can be null)</param>
\r
1868 /// <param name="strColumn">The source column to restrict index information by (can be null)</param>
\r
1869 /// <returns>A DataTable containing the results</returns>
\r
1870 private DataTable Schema_IndexColumns(string strCatalog, string strTable, string strIndex, string strColumn)
\r
1872 DataTable tbl = new DataTable("IndexColumns");
\r
1874 List<KeyValuePair<int, string>> primaryKeys = new List<KeyValuePair<int, string>>();
\r
1877 tbl.Locale = CultureInfo.InvariantCulture;
\r
1878 tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string));
\r
1879 tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string));
\r
1880 tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
\r
1881 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
\r
1882 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
\r
1883 tbl.Columns.Add("TABLE_NAME", typeof(string));
\r
1884 tbl.Columns.Add("COLUMN_NAME", typeof(string));
\r
1885 tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
\r
1886 tbl.Columns.Add("INDEX_NAME", typeof(string));
\r
1887 tbl.Columns.Add("COLLATION_NAME", typeof(string));
\r
1888 tbl.Columns.Add("SORT_MODE", typeof(string));
\r
1889 tbl.Columns.Add("CONFLICT_OPTION", typeof(int));
\r
1891 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
\r
1893 string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
\r
1895 tbl.BeginLoadData();
\r
1897 using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this))
\r
1898 using (SqliteDataReader rdTables = cmdTables.ExecuteReader())
\r
1900 while (rdTables.Read())
\r
1902 maybeRowId = false;
\r
1903 primaryKeys.Clear();
\r
1904 if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0)
\r
1908 using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].table_info([{1}])", strCatalog, rdTables.GetString(2)), this))
\r
1909 using (SqliteDataReader rdTable = cmdTable.ExecuteReader())
\r
1911 while (rdTable.Read())
\r
1913 if (rdTable.GetInt32(5) == 1) // is a primary key
\r
1915 primaryKeys.Add(new KeyValuePair<int, string>(rdTable.GetInt32(0), rdTable.GetString(1)));
\r
1916 // Is an integer -- could be a rowid if no other primary keys exist in the table
\r
1917 if (String.Compare(rdTable.GetString(2), "INTEGER", true, CultureInfo.InvariantCulture) == 0)
\r
1918 maybeRowId = true;
\r
1923 catch (SqliteException)
\r
1926 // This is a rowid row
\r
1927 if (primaryKeys.Count == 1 && maybeRowId == true)
\r
1929 row = tbl.NewRow();
\r
1930 row["CONSTRAINT_CATALOG"] = strCatalog;
\r
1931 row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "{1}_PK_{0}", rdTables.GetString(2), master);
\r
1932 row["TABLE_CATALOG"] = strCatalog;
\r
1933 row["TABLE_NAME"] = rdTables.GetString(2);
\r
1934 row["COLUMN_NAME"] = primaryKeys[0].Value;
\r
1935 row["INDEX_NAME"] = row["CONSTRAINT_NAME"];
\r
1936 row["ORDINAL_POSITION"] = 0; // primaryKeys[0].Key;
\r
1937 row["COLLATION_NAME"] = "BINARY";
\r
1938 row["SORT_MODE"] = "ASC";
\r
1939 row["CONFLICT_OPTION"] = 2;
\r
1941 if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, (string)row["INDEX_NAME"], true, CultureInfo.InvariantCulture) == 0)
\r
1942 tbl.Rows.Add(row);
\r
1945 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))
\r
1946 using (SqliteDataReader rdIndexes = cmdIndexes.ExecuteReader())
\r
1948 while (rdIndexes.Read())
\r
1951 if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, rdIndexes.GetString(1), true, CultureInfo.InvariantCulture) == 0)
\r
1955 using (SqliteCommand cmdIndex = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rdIndexes.GetString(1)), this))
\r
1956 using (SqliteDataReader rdIndex = cmdIndex.ExecuteReader())
\r
1958 while (rdIndex.Read())
\r
1960 row = tbl.NewRow();
\r
1961 row["CONSTRAINT_CATALOG"] = strCatalog;
\r
1962 row["CONSTRAINT_NAME"] = rdIndexes.GetString(1);
\r
1963 row["TABLE_CATALOG"] = strCatalog;
\r
1964 row["TABLE_NAME"] = rdIndexes.GetString(2);
\r
1965 row["COLUMN_NAME"] = rdIndex.GetString(2);
\r
1966 row["INDEX_NAME"] = rdIndexes.GetString(1);
\r
1967 row["ORDINAL_POSITION"] = ordinal; // rdIndex.GetInt32(1);
\r
1969 string collationSequence;
\r
1972 _sql.GetIndexColumnExtendedInfo(strCatalog, rdIndexes.GetString(1), rdIndex.GetString(2), out sortMode, out onError, out collationSequence);
\r
1974 if (String.IsNullOrEmpty(collationSequence) == false)
\r
1975 row["COLLATION_NAME"] = collationSequence;
\r
1977 row["SORT_MODE"] = (sortMode == 0) ? "ASC" : "DESC";
\r
1978 row["CONFLICT_OPTION"] = onError;
\r
1982 if (String.IsNullOrEmpty(strColumn) || String.Compare(strColumn, row["COLUMN_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0)
\r
1983 tbl.Rows.Add(row);
\r
1987 catch (SqliteException)
\r
1997 tbl.EndLoadData();
\r
1998 tbl.AcceptChanges();
\r
2004 /// Returns detailed column information for a specified view
\r
2006 /// <param name="strCatalog">The catalog to retrieve columns for (can be null)</param>
\r
2007 /// <param name="strView">The view to restrict column information by (can be null)</param>
\r
2008 /// <param name="strColumn">The source column to restrict column information by (can be null)</param>
\r
2009 /// <returns>A DataTable containing the results</returns>
\r
2010 private DataTable Schema_ViewColumns(string strCatalog, string strView, string strColumn)
\r
2012 DataTable tbl = new DataTable("ViewColumns");
\r
2016 DataRow schemaRow;
\r
2019 tbl.Locale = CultureInfo.InvariantCulture;
\r
2020 tbl.Columns.Add("VIEW_CATALOG", typeof(string));
\r
2021 tbl.Columns.Add("VIEW_SCHEMA", typeof(string));
\r
2022 tbl.Columns.Add("VIEW_NAME", typeof(string));
\r
2023 tbl.Columns.Add("VIEW_COLUMN_NAME", typeof(String));
\r
2024 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
\r
2025 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
\r
2026 tbl.Columns.Add("TABLE_NAME", typeof(string));
\r
2027 tbl.Columns.Add("COLUMN_NAME", typeof(string));
\r
2028 tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
\r
2029 tbl.Columns.Add("COLUMN_HASDEFAULT", typeof(bool));
\r
2030 tbl.Columns.Add("COLUMN_DEFAULT", typeof(string));
\r
2031 tbl.Columns.Add("COLUMN_FLAGS", typeof(long));
\r
2032 tbl.Columns.Add("IS_NULLABLE", typeof(bool));
\r
2033 tbl.Columns.Add("DATA_TYPE", typeof(string));
\r
2034 tbl.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int));
\r
2035 tbl.Columns.Add("NUMERIC_PRECISION", typeof(int));
\r
2036 tbl.Columns.Add("NUMERIC_SCALE", typeof(int));
\r
2037 tbl.Columns.Add("DATETIME_PRECISION", typeof(long));
\r
2038 tbl.Columns.Add("CHARACTER_SET_CATALOG", typeof(string));
\r
2039 tbl.Columns.Add("CHARACTER_SET_SCHEMA", typeof(string));
\r
2040 tbl.Columns.Add("CHARACTER_SET_NAME", typeof(string));
\r
2041 tbl.Columns.Add("COLLATION_CATALOG", typeof(string));
\r
2042 tbl.Columns.Add("COLLATION_SCHEMA", typeof(string));
\r
2043 tbl.Columns.Add("COLLATION_NAME", typeof(string));
\r
2044 tbl.Columns.Add("PRIMARY_KEY", typeof(bool));
\r
2045 tbl.Columns.Add("EDM_TYPE", typeof(string));
\r
2046 tbl.Columns.Add("AUTOINCREMENT", typeof(bool));
\r
2047 tbl.Columns.Add("UNIQUE", typeof(bool));
\r
2049 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
\r
2051 string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
\r
2053 tbl.BeginLoadData();
\r
2055 using (SqliteCommand cmdViews = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'view'", strCatalog, master), this))
\r
2056 using (SqliteDataReader rdViews = cmdViews.ExecuteReader())
\r
2058 while (rdViews.Read())
\r
2060 if (String.IsNullOrEmpty(strView) || String.Compare(strView, rdViews.GetString(2), true, CultureInfo.InvariantCulture) == 0)
\r
2062 using (SqliteCommand cmdViewSelect = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdViews.GetString(2)), this))
\r
2064 strSql = rdViews.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ');
\r
2065 n = CultureInfo.InvariantCulture.CompareInfo.IndexOf(strSql, " AS ", CompareOptions.IgnoreCase);
\r
2069 strSql = strSql.Substring(n + 4);
\r
2071 using (SqliteCommand cmd = new SqliteCommand(strSql, this))
\r
2072 using (SqliteDataReader rdViewSelect = cmdViewSelect.ExecuteReader(CommandBehavior.SchemaOnly))
\r
2073 using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader(CommandBehavior.SchemaOnly))
\r
2074 using (DataTable tblSchemaView = rdViewSelect.GetSchemaTable(false, false))
\r
2075 using (DataTable tblSchema = rd.GetSchemaTable(false, false))
\r
2077 for (n = 0; n < tblSchema.Rows.Count; n++)
\r
2079 viewRow = tblSchemaView.Rows[n];
\r
2080 schemaRow = tblSchema.Rows[n];
\r
2082 if (String.Compare(viewRow[SchemaTableColumn.ColumnName].ToString(), strColumn, true, CultureInfo.InvariantCulture) == 0
\r
2083 || strColumn == null)
\r
2085 row = tbl.NewRow();
\r
2087 row["VIEW_CATALOG"] = strCatalog;
\r
2088 row["VIEW_NAME"] = rdViews.GetString(2);
\r
2089 row["TABLE_CATALOG"] = strCatalog;
\r
2090 row["TABLE_SCHEMA"] = schemaRow[SchemaTableColumn.BaseSchemaName];
\r
2091 row["TABLE_NAME"] = schemaRow[SchemaTableColumn.BaseTableName];
\r
2092 row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.BaseColumnName];
\r
2093 row["VIEW_COLUMN_NAME"] = viewRow[SchemaTableColumn.ColumnName];
\r
2094 row["COLUMN_HASDEFAULT"] = (viewRow[SchemaTableOptionalColumn.DefaultValue] != DBNull.Value);
\r
2095 row["COLUMN_DEFAULT"] = viewRow[SchemaTableOptionalColumn.DefaultValue];
\r
2096 row["ORDINAL_POSITION"] = viewRow[SchemaTableColumn.ColumnOrdinal];
\r
2097 row["IS_NULLABLE"] = viewRow[SchemaTableColumn.AllowDBNull];
\r
2098 row["DATA_TYPE"] = viewRow["DataTypeName"]; // SqliteConvert.DbTypeToType((DbType)viewRow[SchemaTableColumn.ProviderType]).ToString();
\r
2099 row["EDM_TYPE"] = SqliteConvert.DbTypeToTypeName((DbType)viewRow[SchemaTableColumn.ProviderType]).ToString().ToLower(CultureInfo.InvariantCulture);
\r
2100 row["CHARACTER_MAXIMUM_LENGTH"] = viewRow[SchemaTableColumn.ColumnSize];
\r
2101 row["TABLE_SCHEMA"] = viewRow[SchemaTableColumn.BaseSchemaName];
\r
2102 row["PRIMARY_KEY"] = viewRow[SchemaTableColumn.IsKey];
\r
2103 row["AUTOINCREMENT"] = viewRow[SchemaTableOptionalColumn.IsAutoIncrement];
\r
2104 row["COLLATION_NAME"] = viewRow["CollationType"];
\r
2105 row["UNIQUE"] = viewRow[SchemaTableColumn.IsUnique];
\r
2106 tbl.Rows.Add(row);
\r
2115 tbl.EndLoadData();
\r
2116 tbl.AcceptChanges();
\r
2122 /// Retrieves foreign key information from the specified set of filters
\r
2124 /// <param name="strCatalog">An optional catalog to restrict results on</param>
\r
2125 /// <param name="strTable">An optional table to restrict results on</param>
\r
2126 /// <param name="strKeyName">An optional foreign key name to restrict results on</param>
\r
2127 /// <returns>A DataTable with the results of the query</returns>
\r
2128 private DataTable Schema_ForeignKeys(string strCatalog, string strTable, string strKeyName)
\r
2130 DataTable tbl = new DataTable("ForeignKeys");
\r
2133 tbl.Locale = CultureInfo.InvariantCulture;
\r
2134 tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string));
\r
2135 tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string));
\r
2136 tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
\r
2137 tbl.Columns.Add("TABLE_CATALOG", typeof(string));
\r
2138 tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
\r
2139 tbl.Columns.Add("TABLE_NAME", typeof(string));
\r
2140 tbl.Columns.Add("CONSTRAINT_TYPE", typeof(string));
\r
2141 tbl.Columns.Add("IS_DEFERRABLE", typeof(bool));
\r
2142 tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool));
\r
2143 tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string));
\r
2144 tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int));
\r
2145 tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string));
\r
2146 tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string));
\r
2147 tbl.Columns.Add("FKEY_TO_TABLE", typeof(string));
\r
2148 tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string));
\r
2150 if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
\r
2152 string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
\r
2154 tbl.BeginLoadData();
\r
2156 using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this))
\r
2157 using (SqliteDataReader rdTables = cmdTables.ExecuteReader())
\r
2159 while (rdTables.Read())
\r
2161 if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), true, CultureInfo.InvariantCulture) == 0)
\r
2165 using (SqliteCommandBuilder builder = new SqliteCommandBuilder())
\r
2166 //using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
\r
2167 //using (SqliteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
\r
2168 using (SqliteCommand cmdKey = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this))
\r
2169 using (SqliteDataReader rdKey = cmdKey.ExecuteReader())
\r
2171 while (rdKey.Read())
\r
2173 row = tbl.NewRow();
\r
2174 row["CONSTRAINT_CATALOG"] = strCatalog;
\r
2175 row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}", rdTables[2], rdKey.GetInt32(0));
\r
2176 row["TABLE_CATALOG"] = strCatalog;
\r
2177 row["TABLE_NAME"] = builder.UnquoteIdentifier(rdTables.GetString(2));
\r
2178 row["CONSTRAINT_TYPE"] = "FOREIGN KEY";
\r
2179 row["IS_DEFERRABLE"] = false;
\r
2180 row["INITIALLY_DEFERRED"] = false;
\r
2181 row["FKEY_FROM_COLUMN"] = builder.UnquoteIdentifier(rdKey[3].ToString());
\r
2182 row["FKEY_TO_CATALOG"] = strCatalog;
\r
2183 row["FKEY_TO_TABLE"] = builder.UnquoteIdentifier(rdKey[2].ToString());
\r
2184 row["FKEY_TO_COLUMN"] = builder.UnquoteIdentifier(rdKey[4].ToString());
\r
2185 row["FKEY_FROM_ORDINAL_POSITION"] = rdKey[1];
\r
2187 if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0)
\r
2188 tbl.Rows.Add(row);
\r
2192 catch (SqliteException)
\r
2199 tbl.EndLoadData();
\r
2200 tbl.AcceptChanges();
\r
2206 /// This event is raised whenever SQLite makes an update/delete/insert into the database on
\r
2207 /// this connection. It only applies to the given connection.
\r
2209 public event SQLiteUpdateEventHandler Update
\r
2213 if (_updateHandler == null)
\r
2215 _updateCallback = new SQLiteUpdateCallback(UpdateCallback);
\r
2216 if (_sql != null) _sql.SetUpdateHook(_updateCallback);
\r
2218 _updateHandler += value;
\r
2222 _updateHandler -= value;
\r
2223 if (_updateHandler == null)
\r
2225 if (_sql != null) _sql.SetUpdateHook(null);
\r
2226 _updateCallback = null;
\r
2231 private void UpdateCallback(IntPtr puser, int type, IntPtr database, IntPtr table, Int64 rowid)
\r
2233 _updateHandler(this, new UpdateEventArgs(
\r
2234 SQLiteBase.UTF8ToString(database, -1),
\r
2235 SQLiteBase.UTF8ToString(table, -1),
\r
2236 (UpdateEventType)type,
\r
2241 /// This event is raised whenever SQLite is committing a transaction.
\r
2242 /// Return non-zero to trigger a rollback
\r
2244 public event SQLiteCommitHandler Commit
\r
2248 if (_commitHandler == null)
\r
2250 _commitCallback = new SQLiteCommitCallback(CommitCallback);
\r
2251 if (_sql != null) _sql.SetCommitHook(_commitCallback);
\r
2253 _commitHandler += value;
\r
2257 _commitHandler -= value;
\r
2258 if (_commitHandler == null)
\r
2260 if (_sql != null) _sql.SetCommitHook(null);
\r
2261 _commitCallback = null;
\r
2267 /// This event is raised whenever SQLite is committing a transaction.
\r
2268 /// Return non-zero to trigger a rollback
\r
2270 public event EventHandler RollBack
\r
2274 if (_rollbackHandler == null)
\r
2276 _rollbackCallback = new SQLiteRollbackCallback(RollbackCallback);
\r
2277 if (_sql != null) _sql.SetRollbackHook(_rollbackCallback);
\r
2279 _rollbackHandler += value;
\r
2283 _rollbackHandler -= value;
\r
2284 if (_rollbackHandler == null)
\r
2286 if (_sql != null) _sql.SetRollbackHook(null);
\r
2287 _rollbackCallback = null;
\r
2293 private int CommitCallback(IntPtr parg)
\r
2295 CommitEventArgs e = new CommitEventArgs();
\r
2296 _commitHandler(this, e);
\r
2297 return (e.AbortTransaction == true) ? 1 : 0;
\r
2300 private void RollbackCallback(IntPtr parg)
\r
2302 _rollbackHandler(this, EventArgs.Empty);
\r
2305 // http://www.sqlite.org/c3ref/config.html
\r
2306 public static void SetConfig (SQLiteConfig config)
\r
2308 int n = UnsafeNativeMethods.sqlite3_config (config);
\r
2309 if (n > 0) throw new SqliteException (n, null);
\r
2314 /// The I/O file cache flushing behavior for the connection
\r
2316 public enum SynchronizationModes
\r
2319 /// Normal file flushing at critical sections of the code
\r
2323 /// Full file flushing after every write operation
\r
2327 /// Use the default operating system's file flushing, SQLite does not explicitly flush the file buffers after writing
\r
2332 #if !PLATFORM_COMPACTFRAMEWORK
\r
2333 [UnmanagedFunctionPointer(CallingConvention.Cdecl)]
\r
2335 internal delegate void SQLiteUpdateCallback(IntPtr puser, int type, IntPtr database, IntPtr table, Int64 rowid);
\r
2336 #if !PLATFORM_COMPACTFRAMEWORK
\r
2337 [UnmanagedFunctionPointer(CallingConvention.Cdecl)]
\r
2339 internal delegate int SQLiteCommitCallback(IntPtr puser);
\r
2340 #if !PLATFORM_COMPACTFRAMEWORK
\r
2341 [UnmanagedFunctionPointer(CallingConvention.Cdecl)]
\r
2343 internal delegate void SQLiteRollbackCallback(IntPtr puser);
\r
2346 /// Raised when a transaction is about to be committed. To roll back a transaction, set the
\r
2347 /// rollbackTrans boolean value to true.
\r
2349 /// <param name="sender">The connection committing the transaction</param>
\r
2350 /// <param name="e">Event arguments on the transaction</param>
\r
2351 public delegate void SQLiteCommitHandler(object sender, CommitEventArgs e);
\r
2354 /// Raised when data is inserted, updated and deleted on a given connection
\r
2356 /// <param name="sender">The connection committing the transaction</param>
\r
2357 /// <param name="e">The event parameters which triggered the event</param>
\r
2358 public delegate void SQLiteUpdateEventHandler(object sender, UpdateEventArgs e);
\r
2361 /// Whenever an update event is triggered on a connection, this enum will indicate
\r
2362 /// exactly what type of operation is being performed.
\r
2364 public enum UpdateEventType
\r
2367 /// A row is being deleted from the given database and table
\r
2371 /// A row is being inserted into the table.
\r
2375 /// A row is being updated in the table.
\r
2381 /// Passed during an Update callback, these event arguments detail the type of update operation being performed
\r
2382 /// on the given connection.
\r
2384 public class UpdateEventArgs : EventArgs
\r
2387 /// The name of the database being updated (usually "main" but can be any attached or temporary database)
\r
2389 public readonly string Database;
\r
2392 /// The name of the table being updated
\r
2394 public readonly string Table;
\r
2397 /// The type of update being performed (insert/update/delete)
\r
2399 public readonly UpdateEventType Event;
\r
2402 /// The RowId affected by this update.
\r
2404 public readonly Int64 RowId;
\r
2406 internal UpdateEventArgs(string database, string table, UpdateEventType eventType, Int64 rowid)
\r
2408 Database = database;
\r
2410 Event = eventType;
\r
2416 /// Event arguments raised when a transaction is being committed
\r
2418 public class CommitEventArgs : EventArgs
\r
2420 internal CommitEventArgs()
\r
2425 /// Set to true to abort the transaction and trigger a rollback
\r
2427 public bool AbortTransaction;
\r