context: //
  // Copyright (c) 2009-2012 Krueger Systems, Inc.
  // 
  // Permission is hereby granted, free of charge, to any person obtaining a copy
  // of this software and associated documentation files (the "Software"), to deal
  // in the Software without restriction, including without limitation the rights
  // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  // copies of the Software, and to permit persons to whom the Software is
  // furnished to do so, subject to the following conditions:
  // 
  // The above copyright notice and this permission notice shall be included in
  // all copies or substantial portions of the Software.
  // 
  // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  // THE SOFTWARE.
  //
  #if WINDOWS_PHONE && !USE_WP8_NATIVE_SQLITE
  #define USE_CSHARP_SQLITE
  #endif
  
  using System;
  using System.Diagnostics;
  using System.Runtime.InteropServices;
  using System.Collections.Generic;
  using System.Reflection;
  using System.Linq;
  using System.Linq.Expressions;
  using System.Threading;
  
  #if USE_CSHARP_SQLITE
  using Sqlite3 = Community.CsharpSqlite.Sqlite3;
  using Sqlite3DatabaseHandle = Community.CsharpSqlite.Sqlite3.sqlite3;
  using Sqlite3Statement = Community.CsharpSqlite.Sqlite3.Vdbe;
  #elif USE_WP8_NATIVE_SQLITE
  using Sqlite3 = Sqlite.Sqlite3;
  using Sqlite3DatabaseHandle = Sqlite.Database;
  using Sqlite3Statement = Sqlite.Statement;
  #else
  using Sqlite3DatabaseHandle = System.IntPtr;
  using Sqlite3Statement = System.IntPtr;
  #endif
  
  namespace SQLite
  {
  	public class SQLiteException : System.Exception
  	{
  		public SQLite3.Result Result { get; private set; }
  
  		protected SQLiteException (SQLite3.Result r,string message) : base(message)
  		{
  			Result = r;
  		}
  
  		public static SQLiteException New (SQLite3.Result r, string message)
  		{
  			return new SQLiteException (r, message);
  		}
  	}
  
  	[Flags]
  	public enum SQLiteOpenFlags {
  		ReadOnly = 1, ReadWrite = 2, Create = 4,
  		NoMutex = 0x8000, FullMutex = 0x10000,
  		SharedCache = 0x20000, PrivateCache = 0x40000,
  		ProtectionComplete = 0x00100000,
  		ProtectionCompleteUnlessOpen = 0x00200000,
  		ProtectionCompleteUntilFirstUserAuthentication = 0x00300000,
  		ProtectionNone = 0x00400000
  	}
  
  	/// <summary>
  	/// Represents an open connection to a SQLite database.
  	/// </summary>
  	public partial class SQLiteConnection : IDisposable
  	{
  		private bool _open;
  		private TimeSpan _busyTimeout;
  		private Dictionary<string, TableMapping> _mappings = null;
  		private Dictionary<string, TableMapping> _tables = null;
  		private System.Diagnostics.Stopwatch _sw;
  		private long _elapsedMilliseconds = 0;
  
  		private int _trasactionDepth = 0;
  		private Random _rand = new Random ();
  
  		public Sqlite3DatabaseHandle Handle { get; private set; }
  		internal static readonly Sqlite3DatabaseHandle NullHandle = default(Sqlite3DatabaseHandle);
  
  		public string DatabasePath { get; private set; }
  
  		public bool TimeExecution { get; set; }
  
  		public bool Trace { get; set; }
  
  		public bool StoreDateTimeAsTicks { get; private set; }
  
  		/// <summary>
  		/// Constructs a new SQLiteConnection and opens a SQLite database specified by databasePath.
  		/// </summary>
  		/// <param name="databasePath">
  		/// Specifies the path to the database file.
  		/// </param>
  		/// <param name="storeDateTimeAsTicks">
  		/// Specifies whether to store DateTime properties as ticks (true) or strings (false). You
  		/// absolutely do want to store them as Ticks in all new projects. The default of false is
  		/// only here for backwards compatibility. There is a *significant* speed advantage, with no
  		/// down sides, when setting storeDateTimeAsTicks = true.
  		/// </param>
  		public SQLiteConnection (string databasePath, bool storeDateTimeAsTicks = false)
  			: this (databasePath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create, storeDateTimeAsTicks)
  		{
  		}
  
  		/// <summary>
  		/// Constructs a new SQLiteConnection and opens a SQLite database specified by databasePath.
  		/// </summary>
  		/// <param name="databasePath">
  		/// Specifies the path to the database file.
  		/// </param>
  		/// <param name="storeDateTimeAsTicks">
  		/// Specifies whether to store DateTime properties as ticks (true) or strings (false). You
  		/// absolutely do want to store them as Ticks in all new projects. The default of false is
  		/// only here for backwards compatibility. There is a *significant* speed advantage, with no
  		/// down sides, when setting storeDateTimeAsTicks = true.
  		/// </param>
  		public SQLiteConnection (string databasePath, SQLiteOpenFlags openFlags, bool storeDateTimeAsTicks = false)
  		{
  			DatabasePath = databasePath;
  
  #if NETFX_CORE
  			SQLite3.SetDirectory(/*temp directory type*/2, Windows.Storage.ApplicationData.Current.TemporaryFolder.Path);
  #endif
  
  			Sqlite3DatabaseHandle handle;
  
  #if SILVERLIGHT || USE_CSHARP_SQLITE
              var r = SQLite3.Open (databasePath, out handle, (int)openFlags, IntPtr.Zero);
  #else
  			// open using the byte[]
  			// in the case where the path may include Unicode
  			// force open to using UTF-8 using sqlite3_open_v2
  			var databasePathAsBytes = GetNullTerminatedUtf8 (DatabasePath);
  			var r = SQLite3.Open (databasePathAsBytes, out handle, (int) openFlags, IntPtr.Zero);
  #endif
  
  			Handle = handle;
  			if (r != SQLite3.Result.OK) {
  				throw SQLiteException.New (r, String.Format ("Could not open database file: {0} ({1})", DatabasePath, r));
  			}
  			_open = true;
  
  			StoreDateTimeAsTicks = storeDateTimeAsTicks;
  			
  			BusyTimeout = TimeSpan.FromSeconds (0.1);
  		}
  		
  		static SQLiteConnection ()
  		{
  			if (_preserveDuringLinkMagic) {
  				var ti = new ColumnInfo ();
  				ti.Name = "magic";
  			}
  		}
  
  		static byte[] GetNullTerminatedUtf8 (string s)
  		{
  			var utf8Length = System.Text.Encoding.UTF8.GetByteCount (s);
  			var bytes = new byte [utf8Length + 1];
  			utf8Length = System.Text.Encoding.UTF8.GetBytes(s, 0, s.Length, bytes, 0);
  			return bytes;
  		}
  		
  		/// <summary>
  		/// Used to list some code that we want the MonoTouch linker
  		/// to see, but that we never want to actually execute.
  		/// </summary>
  		static bool _preserveDuringLinkMagic = false;
  
  		/// <summary>
  		/// Sets a busy handler to sleep the specified amount of time when a table is locked.
  		/// The handler will sleep multiple times until a total time of <see cref="BusyTimeout"/> has accumulated.
  		/// </summary>
  		public TimeSpan BusyTimeout {
  			get { return _busyTimeout; }
  			set {
  				_busyTimeout = value;
  				if (Handle != NullHandle) {
  					SQLite3.BusyTimeout (Handle, (int)_busyTimeout.TotalMilliseconds);
  				}
  			}
  		}
  
  		/// <summary>
  		/// Returns the mappings from types to tables that the connection
  		/// currently understands.
  		/// </summary>
  		public IEnumerable<TableMapping> TableMappings {
  			get {
  				if (_tables == null) {
  					return Enumerable.Empty<TableMapping> ();
  				} else {
  					return _tables.Values;
  				}
  			}
  		}
  
  		/// <summary>
  		/// Retrieves the mapping that is automatically generated for the given type.
  		/// </summary>
  		/// <param name="type">
  		/// The type whose mapping to the database is returned.
  		/// </param>
  		/// <returns>
  		/// The mapping represents the schema of the columns of the database and contains 
  		/// methods to set and get properties of objects.
  		/// </returns>
  		public TableMapping GetMapping (Type type)
  		{
  			if (_mappings == null) {
  				_mappings = new Dictionary<string, TableMapping> ();
  			}
  			TableMapping map;
  			if (!_mappings.TryGetValue (type.FullName, out map)) {
  				map = new TableMapping (type);
  				_mappings [type.FullName] = map;
  			}
  			return map;
  		}
  		
  		/// <summary>
  		/// Retrieves the mapping that is automatically generated for the given type.
  		/// </summary>
  		/// <returns>
  		/// The mapping represents the schema of the columns of the database and contains 
  		/// methods to set and get properties of objects.
  		/// </returns>
  		public TableMapping GetMapping<T> ()
  		{
  			return GetMapping (typeof (T));
  		}
  
  		private struct IndexedColumn
  		{
  			public int Order;
  			public string ColumnName;
  		}
  
  		private struct IndexInfo
  		{
  			public string IndexName;
  			public string TableName;
  			public bool Unique;
  			public List<IndexedColumn> Columns;
  		}
  
  		/// <summary>
  		/// Executes a "drop table" on the database.  This is non-recoverable.
  		/// </summary>
  		public int DropTable<T>()
  		{
  			var map = GetMapping (typeof (T));
  
  			var query = string.Format("drop table if exists \"{0}\"", map.TableName);
  
  			return Execute (query);
  		}
  		
  		/// <summary>
  		/// Executes a "create table if not exists" on the database. It also
  		/// creates any specified indexes on the columns of the table. It uses
  		/// a schema automatically generated from the specified type. You can
  		/// later access this schema by calling GetMapping.
  		/// </summary>
  		/// <returns>
  		/// The number of entries added to the database schema.
  		/// </returns>
  		public int CreateTable<T>()
  		{
  			return CreateTable(typeof (T));
  		}
  
  		/// <summary>
  		/// Executes a "create table if not exists" on the database. It also
  		/// creates any specified indexes on the columns of the table. It uses
  		/// a schema automatically generated from the specified type. You can
  		/// later access this schema by calling GetMapping.
  		/// </summary>
  		/// <param name="ty">Type to reflect to a database table.</param>
  		/// <returns>
  		/// The number of entries added to the database schema.
  		/// </returns>
  		public int CreateTable(Type ty)
  		{
  			if (_tables == null) {
  				_tables = new Dictionary<string, TableMapping> ();
  			}
  			TableMapping map;
  			if (!_tables.TryGetValue (ty.FullName, out map)) {
  				map = GetMapping (ty);
  				_tables.Add (ty.FullName, map);
  			}
  			var query = "create table if not exists \"" + map.TableName + "\"(\n";
  			
  			var decls = map.Columns.Select (p => Orm.SqlDecl (p, StoreDateTimeAsTicks));
  			var decl = string.Join (",\n", decls.ToArray ());
  			query += decl;
  			query += ")";
  			
  			var count = Execute (query);
  			
  			if (count == 0) { //Possible bug: This always seems to return 0?
  				// Table already exists, migrate it
  				MigrateTable (map);
  			}
  
  			var indexes = new Dictionary<string, IndexInfo> ();
  			foreach (var c in map.Columns) {
  				foreach (var i in c.Indices) {
  					var iname = i.Name ?? map.TableName + "_" + c.Name;
  					IndexInfo iinfo;
  					if (!indexes.TryGetValue (iname, out iinfo)) {
  						iinfo = new IndexInfo {
  							IndexName = iname,
  							TableName = map.TableName,
  							Unique = i.Unique,
  							Columns = new List<IndexedColumn> ()
  						};
  						indexes.Add (iname, iinfo);
  					}
  
  					if (i.Unique != iinfo.Unique)
  						throw new Exception ("All the columns in an index must have the same value for their Unique property");
  
  					iinfo.Columns.Add (new IndexedColumn {
  						Order = i.Order,
  						ColumnName = c.Name
  					});
  				}
  			}
  
  			foreach (var indexName in indexes.Keys) {
  				var index = indexes[indexName];
  				const string sqlFormat = "create {3} index if not exists \"{0}\" on \"{1}\"(\"{2}\")";
  				var columns = String.Join("\",\"", index.Columns.OrderBy(i => i.Order).Select(i => i.ColumnName).ToArray());
  				var sql = String.Format (sqlFormat, indexName, index.TableName, columns, index.Unique ? "unique" : "");
  				count += Execute(sql);
  			}
  			
  			return count;
  		}
  
  		public class ColumnInfo
  		{
  //			public int cid { get; set; }
  
  			[Column ("name")]
  			public string Name { get; set; }
  
  //			[Column ("type")]
  //			public string ColumnType { get; set; }
  
  //			public int notnull { get; set; }
  
  //			public string dflt_value { get; set; }
  
  //			public int pk { get; set; }
  
  			public override string ToString ()
  			{
  				return Name;
  			}
  		}
  
  		public IEnumerable<ColumnInfo> GetTableInfo (string tableName)
  		{
  			var query = "pragma table_info(\"" + tableName + "\")";			
  			return Query<ColumnInfo> (query);
  		}
  
  		void MigrateTable (TableMapping map)
  		{
  			var existingCols = GetTableInfo (map.TableName);
  			
  			var toBeAdded = new List<TableMapping.Column> ();
  			
  			foreach (var p in map.Columns) {
  				var found = false;
  				foreach (var c in existingCols) {
  					found = (string.Compare (p.Name, c.Name, StringComparison.OrdinalIgnoreCase) == 0);
  					if (found)
  						break;
  				}
  				if (!found) {
  					toBeAdded.Add (p);
  				}
  			}
  			
  			foreach (var p in toBeAdded) {
  				var addCol = "alter table \"" + map.TableName + "\" add column " + Orm.SqlDecl (p, StoreDateTimeAsTicks);
  				Execute (addCol);
  			}
  		}
  
  		/// <summary>
  		/// Creates a new SQLiteCommand. Can be overridden to provide a sub-class.
  		/// </summary>
  		/// <seealso cref="SQLiteCommand.OnInstanceCreated"/>
  		protected virtual SQLiteCommand NewCommand ()
  		{
  			return new SQLiteCommand (this);
  		}
  
  		/// <summary>
  		/// Creates a new SQLiteCommand given the command text with arguments. Place a '?'
  		/// in the command text for each of the arguments.
  		/// </summary>
  		/// <param name="cmdText">
  		/// The fully escaped SQL.
  		/// </param>
  		/// <param name="args">
  		/// Arguments to substitute for the occurences of '?' in the command text.
  		/// </param>
  		/// <returns>
  		/// A <see cref="SQLiteCommand"/>
  		/// </returns>
  		public SQLiteCommand CreateCommand (string cmdText, params object[] ps)
  		{
  			if (!_open) {
  				throw SQLiteException.New (SQLite3.Result.Error, "Cannot create commands from unopened database");
  			} else {
  				var cmd = NewCommand ();
  				cmd.CommandText = cmdText;
  				foreach (var o in ps) {
  					cmd.Bind (o);
  				}
  				return cmd;
  			}
  		}
  
  		/// <summary>
  		/// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
  		/// in the command text for each of the arguments and then executes that command.
  		/// Use this method instead of Query when you don't expect rows back. Such cases include
  		/// INSERTs, UPDATEs, and DELETEs.
  		/// You can set the Trace or TimeExecution properties of the connection
  		/// to profile execution.
  		/// </summary>
  		/// <param name="query">
  		/// The fully escaped SQL.
  		/// </param>
  		/// <param name="args">
  		/// Arguments to substitute for the occurences of '?' in the query.
  		/// </param>
  		/// <returns>
  		/// The number of rows modified in the database as a result of this execution.
  		/// </returns>
  		public int Execute (string query, params object[] args)
  		{
  			var cmd = CreateCommand (query, args);
  			
  			if (TimeExecution) {
  				if (_sw == null) {
  					_sw = new System.Diagnostics.Stopwatch ();
  				}
  				_sw.Reset ();
  				_sw.Start ();
  			}
  
  			var r = cmd.ExecuteNonQuery ();
  			
  			if (TimeExecution) {
  				_sw.Stop ();
  				_elapsedMilliseconds += _sw.ElapsedMilliseconds;
  				Debug.WriteLine (string.Format ("Finished in {0} ms ({1:0.0} s total)", _sw.ElapsedMilliseconds, _elapsedMilliseconds / 1000.0));
  			}
  			
  			return r;
  		}
  
  		public T ExecuteScalar<T> (string query, params object[] args)
  		{
  			var cmd = CreateCommand (query, args);
  			
  			if (TimeExecution) {
  				if (_sw == null) {
  					_sw = new System.Diagnostics.Stopwatch ();
  				}
  				_sw.Reset ();
  				_sw.Start ();
  			}
  			
  			var r = cmd.ExecuteScalar<T> ();
  			
  			if (TimeExecution) {
  				_sw.Stop ();
  				_elapsedMilliseconds += _sw.ElapsedMilliseconds;
  				Debug.WriteLine (string.Format ("Finished in {0} ms ({1:0.0} s total)", _sw.ElapsedMilliseconds, _elapsedMilliseconds / 1000.0));
  			}
  			
  			return r;
  		}
  
  		/// <summary>
  		/// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
  		/// in the command text for each of the arguments and then executes that command.
  		/// It returns each row of the result using the mapping automatically generated for
  		/// the given type.
  		/// </summary>
  		/// <param name="query">
  		/// The fully escaped SQL.
  		/// </param>
  		/// <param name="args">
  		/// Arguments to substitute for the occurences of '?' in the query.
  		/// </param>
  		/// <returns>
  		/// An enumerable with one result for each row returned by the query.
  		/// </returns>
  		public List<T> Query<T> (string query, params object[] args) where T : new()
  		{
  			var cmd = CreateCommand (query, args);
  			return cmd.ExecuteQuery<T> ();
  		}
  
  		/// <summary>
  		/// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
  		/// in the command text for each of the arguments and then executes that command.
  		/// It returns each row of the result using the mapping automatically generated for
  		/// the given type.
  		/// </summary>
  		/// <param name="query">
  		/// The fully escaped SQL.
  		/// </param>
  		/// <param name="args">
  		/// Arguments to substitute for the occurences of '?' in the query.
  		/// </param>
  		/// <returns>
  		/// An enumerable with one result for each row returned by the query.
  		/// The enumerator will call sqlite3_step on each call to MoveNext, so the database
  		/// connection must remain open for the lifetime of the enumerator.
  		/// </returns>
  		public IEnumerable<T> DeferredQuery<T>(string query, params object[] args) where T : new()
  		{
  			var cmd = CreateCommand(query, args);
  			return cmd.ExecuteDeferredQuery<T>();
  		}
  
  		/// <summary>
  		/// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
  		/// in the command text for each of the arguments and then executes that command.
  		/// It returns each row of the result using the specified mapping. This function is
  		/// only used by libraries in order to query the database via introspection. It is
  		/// normally not used.
  		/// </summary>
  		/// <param name="map">
  		/// A <see cref="TableMapping"/> to use to convert the resulting rows
  		/// into objects.
  		/// </param>
  		/// <param name="query">
  		/// The fully escaped SQL.
  		/// </param>
  		/// <param name="args">
  		/// Arguments to substitute for the occurences of '?' in the query.
  		/// </param>
  		/// <returns>
  		/// An enumerable with one result for each row returned by the query.
  		/// </returns>
  		public List<object> Query (TableMapping map, string query, params object[] args)
  		{
  			var cmd = CreateCommand (query, args);
  			return cmd.ExecuteQuery<object> (map);
  		}
  
  		/// <summary>
  		/// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
  		/// in the command text for each of the arguments and then executes that command.
  		/// It returns each row of the result using the specified mapping. This function is
  		/// only used by libraries in order to query the database via introspection. It is
  		/// normally not used.
  		/// </summary>
  		/// <param name="map">
  		/// A <see cref="TableMapping"/> to use to convert the resulting rows
  		/// into objects.
  		/// </param>
  		/// <param name="query">
  		/// The fully escaped SQL.
  		/// </param>
  		/// <param name="args">
  		/// Arguments to substitute for the occurences of '?' in the query.
  		/// </param>
  		/// <returns>
  		/// An enumerable with one result for each row returned by the query.
  		/// The enumerator will call sqlite3_step on each call to MoveNext, so the database
  		/// connection must remain open for the lifetime of the enumerator.
  		/// </returns>
  		public IEnumerable<object> DeferredQuery(TableMapping map, string query, params object[] args)
  		{
  			var cmd = CreateCommand(query, args);
  			return cmd.ExecuteDeferredQuery<object>(map);
  		}
  
  		/// <summary>
  		/// Returns a queryable interface to the table represented by the given type.
  		/// </summary>
  		/// <returns>
  		/// A queryable object that is able to translate Where, OrderBy, and Take
  		/// queries into native SQL.
  		/// </returns>
  		public TableQuery<T> Table<T> () where T : new()
  		{
  			return new TableQuery<T> (this);
  		}
  
  		/// <summary>
  		/// Attempts to retrieve an object with the given primary key from the table
  		/// associated with the specified type. Use of this method requires that
  		/// the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).
  		/// </summary>
  		/// <param name="pk">
  		/// The primary key.
  		/// </param>
  		/// <returns>
  		/// The object with the given primary key. Throws a not found exception
  		/// if the object is not found.
  		/// </returns>
  		public T Get<T> (object pk) where T : new()
  		{
  			var map = GetMapping (typeof(T));
  			return Query<T> (map.GetByPrimaryKeySql, pk).First ();
  		}
  
          /// <summary>
          /// Attempts to retrieve the first object that matches the predicate from the table
          /// associated with the specified type. 
          /// </summary>
          /// <param name="predicate">
          /// A predicate for which object to find.
          /// </param>
          /// <returns>
          /// The object that matches the given predicate. Throws a not found exception
          /// if the object is not found.
          /// </returns>
          public T Get<T> (Expression<Func<T, bool>> predicate) where T : new()
          {
              return Table<T> ().Where (predicate).First ();
          }
  
  		/// <summary>
  		/// Attempts to retrieve an object with the given primary key from the table
  		/// associated with the specified type. Use of this method requires that
  		/// the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).
  		/// </summary>
  		/// <param name="pk">
  		/// The primary key.
  		/// </param>
  		/// <returns>
  		/// The object with the given primary key or null
  		/// if the object is not found.
  		/// </returns>
  		public T Find<T> (object pk) where T : new ()
  		{
  			var map = GetMapping (typeof (T));
  			return Query<T> (map.GetByPrimaryKeySql, pk).FirstOrDefault ();
  		}
  
  		/// <summary>
  		/// Attempts to retrieve an object with the given primary key from the table
  		/// associated with the specified type. Use of this method requires that
  		/// the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).
  		/// </summary>
  		/// <param name="pk">
  		/// The primary key.
  		/// </param>
  		/// <param name="map">
  		/// The TableMapping used to identify the object type.
  		/// </param>
  		/// <returns>
  		/// The object with the given primary key or null
  		/// if the object is not found.
  		/// </returns>
  		public object Find (object pk, TableMapping map)
  		{
  			return Query (map, map.GetByPrimaryKeySql, pk).FirstOrDefault ();
  		}
  		
  		/// <summary>
          /// Attempts to retrieve the first object that matches the predicate from the table
          /// associated with the specified type. 
          /// </summary>
          /// <param name="predicate">
          /// A predicate for which object to find.
          /// </param>
          /// <returns>
          /// The object that matches the given predicate or null
          /// if the object is not found.
          /// </returns>
          public T Find<T> (Expression<Func<T, bool>> predicate) where T : new()
          {
              return Table<T> ().Where (predicate).FirstOrDefault ();
          }
  
  		/// <summary>
  		/// Whether <see cref="BeginTransaction"/> has been called and the database is waiting for a <see cref="Commit"/>.
  		/// </summary>
  		public bool IsInTransaction {
  			get { return _trasactionDepth > 0; }
  		}
  
  		/// <summary>
  		/// Begins a new transaction. Call <see cref="Commit"/> to end the transaction.
  		/// </summary>
  		/// <example cref="System.InvalidOperationException">Throws if a transaction has already begun.</example>
  		public void BeginTransaction ()
  		{
  			// The BEGIN command only works if the transaction stack is empty, 
  			//    or in other words if there are no pending transactions. 
  			// If the transaction stack is not empty when the BEGIN command is invoked, 
  			//    then the command fails with an error.
  			// Rather than crash with an error, we will just ignore calls to BeginTransaction
  			//    that would result in an error.
  			if (Interlocked.CompareExchange (ref _trasactionDepth, 1, 0) == 0) {
  				try {
  					Execute ("begin transaction");
  				} catch (Exception ex) {
  					var sqlExp = ex as SQLiteException;
  					if (sqlExp != null) {
  						// It is recommended that applications respond to the errors listed below 
  						//    by explicitly issuing a ROLLBACK command.
  						// TODO: This rollback failsafe should be localized to all throw sites.
  						switch (sqlExp.Result) {
  						case SQLite3.Result.IOError:
  						case SQLite3.Result.Full:
  						case SQLite3.Result.Busy:
  						case SQLite3.Result.NoMem:
  						case SQLite3.Result.Interrupt:
  							RollbackTo (null, true);
  							break;
  						}
  					} else {
  						// Call decrement and not VolatileWrite in case we've already 
  						//    created a transaction point in SaveTransactionPoint since the catch.
  						Interlocked.Decrement (ref _trasactionDepth);
  					}
  
  					throw;
  				}
  			} else { 
  				// Calling BeginTransaction on an already open transaction is invalid
  				throw new System.InvalidOperationException ("Cannot begin a transaction while already in a transaction.");
  			}
  		}
  
  		/// <summary>
  		/// Creates a savepoint in the database at the current point in the transaction timeline.
  		/// Begins a new transaction if one is not in progress.
  		/// 
  		/// Call <see cref="RollbackTo"/> to undo transactions since the returned savepoint.
  		/// Call <see cref="Release"/> to commit transactions after the savepoint returned here.
  		/// Call <see cref="Commit"/> to end the transaction, committing all changes.
  		/// </summary>
  		/// <returns>A string naming the savepoint.</returns>
  		public string SaveTransactionPoint ()
  		{
  			int depth = Interlocked.Increment (ref _trasactionDepth) - 1;
  			string retVal = "S" + (short)_rand.Next (short.MaxValue) + "D" + depth;
  
  			try {
  				Execute ("savepoint " + retVal);
  			} catch (Exception ex) {
  				var sqlExp = ex as SQLiteException;
  				if (sqlExp != null) {
  					// It is recommended that applications respond to the errors listed below 
  					//    by explicitly issuing a ROLLBACK command.
  					// TODO: This rollback failsafe should be localized to all throw sites.
  					switch (sqlExp.Result) {
  					case SQLite3.Result.IOError:
  					case SQLite3.Result.Full:
  					case SQLite3.Result.Busy:
  					case SQLite3.Result.NoMem:
  					case SQLite3.Result.Interrupt:
  						RollbackTo (null, true);
  						break;
  					}
  				} else {
  					Interlocked.Decrement (ref _trasactionDepth);
  				}
  
  				throw;
  			}
  
  			return retVal;
  		}
  
  		/// <summary>
  		/// Rolls back the transaction that was begun by <see cref="BeginTransaction"/> or <see cref="SaveTransactionPoint"/>.
  		/// </summary>
  		public void Rollback ()
  		{
  			RollbackTo (null, false);
  		}
  
  		/// <summary>
  		/// Rolls back the savepoint created by <see cref="BeginTransaction"/> or SaveTransactionPoint.
  		/// </summary>
  		/// <param name="savepoint">The name of the savepoint to roll back to, as returned by <see cref="SaveTransactionPoint"/>.  If savepoint is null or empty, this method is equivalent to a call to <see cref="Rollback"/></param>
  		public void RollbackTo (string savepoint)
  		{
  			RollbackTo (savepoint, false);
  		}
  
  		/// <summary>
  		/// Rolls back the transaction that was begun by <see cref="BeginTransaction"/>.
  		/// </summary>
  		/// <param name="noThrow">true to avoid throwing exceptions, false otherwise</param>
  		private void RollbackTo (string savepoint, bool noThrow)
  		{
  			// Rolling back without a TO clause rolls backs all transactions 
  			//    and leaves the transaction stack empty.   
  			try {
  				if (String.IsNullOrEmpty (savepoint)) {
  					if (Interlocked.Exchange (ref _trasactionDepth, 0) > 0) {
  						Execute ("rollback");
  					}
  				} else {
  					DoSavePointExecute (savepoint, "rollback to ");
  				}   
  			} catch (SQLiteException) {
  				if (!noThrow)
  					throw;
              
  			}
  			// No need to rollback if there are no transactions open.
  		}
  
  		/// <summary>
  		/// Releases a savepoint returned from <see cref="SaveTransactionPoint"/>.  Releasing a savepoint 
  		///    makes changes since that savepoint permanent if the savepoint began the transaction,
  		///    or otherwise the changes are permanent pending a call to <see cref="Commit"/>.
  		/// 
  		/// The RELEASE command is like a COMMIT for a SAVEPOINT.
  		/// </summary>
  		/// <param name="savepoint">The name of the savepoint to release.  The string should be the result of a call to <see cref="SaveTransactionPoint"/></param>
  		public void Release (string savepoint)
  		{
  			DoSavePointExecute (savepoint, "release ");
  		}
  
  		private void DoSavePointExecute (string savepoint, string cmd)
  		{
  			// Validate the savepoint
  			int firstLen = savepoint.IndexOf ('D');
  
gt: 			if (firstLen >= 2 && savepoint.Length > firstLen + 1) {