RLA5tVtu

1//
2// Copyright (c) 2009-2010 Krueger Systems, Inc.
3// 
4// Permission is hereby granted, free of charge, to any person obtaining a copy
5// of this software and associated documentation files (the "Software"), to deal
6// in the Software without restriction, including without limitation the rights
7// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8// copies of the Software, and to permit persons to whom the Software is
9// furnished to do so, subject to the following conditions:
10// 
11// The above copyright notice and this permission notice shall be included in
12// all copies or substantial portions of the Software.
13// 
14// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
20// THE SOFTWARE.
21//
22using System;
23using System.Runtime.InteropServices;
24using System.Collections.Generic;
25using System.Reflection;
26using System.Linq;
27using System.Linq.Expressions;
28
29namespace SQLite
30{
31	public class SQLiteException : System.Exception
32	{
33		public SQLite3.Result Result { get;  set; }
34
35		protected SQLiteException (SQLite3.Result r,string message) : base(message)
36		{
37			Result = r;
38		}
39
40		public static SQLiteException New (SQLite3.Result r, string message)
41		{
42			return new SQLiteException (r, message);
43		}
44	}
45
46	/// <summary>
47	/// Represents an open connection to a SQLite database.
48	/// </summary>
49	public class SQLiteConnection : IDisposable
50	{
51		 bool _open;
52		 TimeSpan _busyTimeout;
53		 Dictionary<string, TableMapping> _mappings = null;
54		 Dictionary<string, TableMapping> _tables = null;
55		 System.Diagnostics.Stopwatch _sw;
56		 long _elapsedMilliseconds = 0;
57
58		public IntPtr Handle { get;  set; }
59
60		public string DatabasePath { get;  set; }
61
62		public bool TimeExecution { get; set; }
63
64		public bool Trace { get; set; }
65
66		/// <summary>
67		/// Constructs a new SQLiteConnection and opens a SQLite database specified by databasePath.
68		/// </summary>
69		/// <param name="databasePath">
70		/// Specifies the path to the database file.
71		/// </param>
72		public SQLiteConnection (string databasePath)
73		{
74			DatabasePath = databasePath;
75			IntPtr handle;
76			var r = SQLite3.Open (DatabasePath, out handle);
77			Handle = handle;
78			if (r != SQLite3.Result.OK) {
79				throw SQLiteException.New (r, "Could not open database file: " + DatabasePath);
80			}
81			_open = true;
82			
83			BusyTimeout = TimeSpan.FromSeconds (0.1);
84		}
85
86		static SQLiteConnection ()
87		{
88			if (_preserveDuringLinkMagic) {
89				var ti = new TableInfo ();
90				ti.name = "magic";
91			}
92		}
93
94		/// <summary>
95		/// Used to list some code that we want the MonoTouch linker
96		/// to see, but that we never want to actually execute.
97		/// </summary>
98		static bool _preserveDuringLinkMagic = false;
99
100		/// <summary>
101		/// Sets a busy handler to sleep the specified amount of time when a table is locked.
102		/// The handler will sleep multiple times until a total time of <see cref="BusyTimeout"/> has accumulated.
103		/// </summary>
104		public TimeSpan BusyTimeout {
105			get { return _busyTimeout; }
106			set {
107				_busyTimeout = value;
108				if (Handle != IntPtr.Zero) {
109					SQLite3.BusyTimeout (Handle, (int)_busyTimeout.TotalMilliseconds);
110				}
111			}
112		}
113
114		/// <summary>
115		/// Returns the mappings from types to tables that the connection
116		/// currently understands.
117		/// </summary>
118		public IEnumerable<TableMapping> TableMappings {
119			get {
120				if (_tables == null) {
121					return Enumerable.Empty<TableMapping> ();
122				} else {
123					return _tables.Values;
124				}
125			}
126		}
127
128		/// <summary>
129		/// Retrieves the mapping that is automatically generated for the given type.
130		/// </summary>
131		/// <param name="type">
132		/// The type whose mapping to the database is returned.
133		/// </param>
134		/// <returns>
135		/// The mapping represents the schema of the columns of the database and contains 
136		/// methods to set and get properties of objects.
137		/// </returns>
138		public TableMapping GetMapping (Type type)
139		{
140			if (_mappings == null) {
141				_mappings = new Dictionary<string, TableMapping> ();
142			}
143			TableMapping map;
144			if (!_mappings.TryGetValue (type.FullName, out map)) {
145				map = new TableMapping (type);
146				_mappings [type.FullName] = map;
147			}
148			return map;
149		}
150
151		/// <summary>
152		/// Executes a "create table if not exists" on the database. It also
153		/// creates any specified indexes on the columns of the table. It uses
154		/// a schema automatically generated from the specified type. You can
155		/// later access this schema by calling GetMapping.
156		/// </summary>
157		/// <returns>
158		/// The number of entries added to the database schema.
159		/// </returns>
160		public int CreateTable<T> ()
161		{
162			var ty = typeof(T);
163			
164			if (_tables == null) {
165				_tables = new Dictionary<string, TableMapping> ();
166			}
167			TableMapping map;
168			if (!_tables.TryGetValue (ty.FullName, out map)) {
169				map = GetMapping (ty);
170				_tables.Add (ty.FullName, map);
171			}
172			var query = "create table \"" + map.TableName + "\"(\n";
173			
174			var decls = map.Columns.Select (p => Orm.SqlDecl (p));
175			var decl = string.Join (",\n", decls.ToArray ());
176			query += decl;
177			query += ")";
178			
179			Console.WriteLine(query);
180			Console.WriteLine("");
181			
182			var count = 0;
183
184			try {
185				Execute (query);
186				count = 1;
187			}
188			catch (SQLiteException) {}
189			
190			if (count == 0) {
191				// Table already exists, migrate it
192				MigrateTable (map);
193			}
194
195			foreach (var p in map.Columns.Where (x => x.IsIndexed)) {
196				var indexName = map.TableName + "_" + p.Name;
197				var q = string.Format ("create index if not exists \"{0}\" on \"{1}\"(\"{2}\")", indexName, map.TableName, p.Name);
198				count += Execute (q);
199			}
200			
201			return count;
202		}
203
204		class TableInfo
205		{
206			public int cid { get; set; }
207
208			public string name { get; set; }
209
210			public string type { get; set; }
211
212			public int notnull { get; set; }
213
214			public string dflt_value { get; set; }
215
216			public int pk { get; set; }
217		}
218
219		void MigrateTable (TableMapping map)
220		{
221			var query = "pragma table_info(\"" + map.TableName + "\")";
222			
223			var existingCols = Query<TableInfo> (query);
224
225			var toBeAdded = new List<TableMapping.Column> ();
226			
227			foreach (var p in map.Columns) {
228				var found = false;
229				foreach (var c in existingCols) {
230					found = p.Name == c.name;
231					if (found)
232						break;
233				}
234				if (!found) {
235					toBeAdded.Add (p);
236				}
237			}
238			
239			foreach (var p in toBeAdded) {
240				var addCol = "alter table \"" + map.TableName + "\" add column " + Orm.SqlDecl (p);
241				Execute (addCol);
242			}
243		}
244
245		/// <summary>
246		/// Creates a new SQLiteCommand given the command text with arguments. Place a '?'
247		/// in the command text for each of the arguments.
248		/// </summary>
249		/// <param name="cmdText">
250		/// The fully escaped SQL.
251		/// </param>
252		/// <param name="args">
253		/// Arguments to substitute for the occurences of '?' in the command text.
254		/// </param>
255		/// <returns>
256		/// A <see cref="SQLiteCommand"/>
257		/// </returns>
258		public SQLiteCommand CreateCommand (string cmdText, params object[] ps)
259		{
260			if (!_open) {
261				throw SQLiteException.New (SQLite3.Result.Error, "Cannot create commands from unopened database");
262			} else {
263				var cmd = new SQLiteCommand (this);
264				cmd.CommandText = cmdText;
265				foreach (var o in ps) {
266					cmd.Bind (o);
267				}
268				return cmd;
269			}
270		}
271
272		/// <summary>
273		/// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
274		/// in the command text for each of the arguments and then executes that command.
275		/// Use this method instead of Query when you don't expect rows back. Such cases include
276		/// INSERTs, UPDATEs, and DELETEs.
277		/// You can set the Trace or TimeExecution properties of the connection
278		/// to profile execution.
279		/// </summary>
280		/// <param name="query">
281		/// The fully escaped SQL.
282		/// </param>
283		/// <param name="args">
284		/// Arguments to substitute for the occurences of '?' in the query.
285		/// </param>
286		/// <returns>
287		/// The number of rows modified in the database as a result of this execution.
288		/// </returns>
289		public int Execute (string query, params object[] args)
290		{
291			var cmd = CreateCommand (query, args);
292			
293			if (TimeExecution) {
294				if (_sw == null) {
295					_sw = new System.Diagnostics.Stopwatch ();
296				}
297				_sw.Reset ();
298				_sw.Start ();
299			}
300			
301			int r = cmd.ExecuteNonQuery ();
302			
303			if (TimeExecution) {
304				_sw.Stop ();
305				_elapsedMilliseconds += _sw.ElapsedMilliseconds;
306				Console.WriteLine ("Finished in {0} ms ({1:0.0} s total)", _sw.ElapsedMilliseconds, _elapsedMilliseconds / 1000.0);
307			}
308			
309			return r;
310		}
311
312		/// <summary>
313		/// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
314		/// in the command text for each of the arguments and then executes that command.
315		/// It returns each row of the result using the mapping automatically generated for
316		/// the given type.
317		/// </summary>
318		/// <param name="query">
319		/// The fully escaped SQL.
320		/// </param>
321		/// <param name="args">
322		/// Arguments to substitute for the occurences of '?' in the query.
323		/// </param>
324		/// <returns>
325		/// An enumerable with one result for each row returned by the query.
326		/// </returns>
327		public List<T> Query<T> (string query, params object[] args) where T : new()
328		{
329			var cmd = CreateCommand (query, args);
330			return cmd.ExecuteQuery<T> ();
331		}
332
333		/// <summary>
334		/// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
335		/// in the command text for each of the arguments and then executes that command.
336		/// It returns each row of the result using the specified mapping. This function is
337		/// only used by libraries in order to query the database via introspection. It is
338		/// normally not used.
339		/// </summary>
340		/// <param name="map">
341		/// A <see cref="TableMapping"/> to use to convert the resulting rows
342		/// into objects.
343		/// </param>
344		/// <param name="query">
345		/// The fully escaped SQL.
346		/// </param>
347		/// <param name="args">
348		/// Arguments to substitute for the occurences of '?' in the query.
349		/// </param>
350		/// <returns>
351		/// An enumerable with one result for each row returned by the query.
352		/// </returns>
353		public List<object> Query (TableMapping map, string query, params object[] args)
354		{
355			var cmd = CreateCommand (query, args);
356			return cmd.ExecuteQuery<object> (map);
357		}
358
359		/// <summary>
360		/// Returns a queryable interface to the table represented by the given type.
361		/// </summary>
362		/// <returns>
363		/// A queryable object that is able to translate Where, OrderBy, and Take
364		/// queries into native SQL.
365		/// </returns>
366		public TableQuery<T> Table<T> () where T : new()
367		{
368			return new TableQuery<T> (this);
369		}
370
371		/// <summary>
372		/// Attempts to retrieve an object with the given primary key from the table
373		/// associated with the specified type. Use of this method requires that
374		/// the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).
375		/// </summary>
376		/// <param name="pk">
377		/// The primary key.
378		/// </param>
379		/// <returns>
380		/// The object with the given primary key. Throws a not found exception
381		/// if the object is not found.
382		/// </returns>
383		public T Get<T> (object pk) where T : new()
384		{
385			var map = GetMapping (typeof(T));
386			string query = string.Format ("select * from \"{0}\" where \"{1}\" = ?", map.TableName, map.PK.Name);
387			return Query<T> (query, pk).First ();
388		}
389
390		/// <summary>
391		/// Whether <see cref="BeginTransaction"/> has been called and the database is waiting for a <see cref="Commit"/>.
392		/// </summary>
393		public bool IsInTransaction { get;  set; }
394
395		/// <summary>
396		/// Begins a new transaction. Call <see cref="Commit"/> to end the transaction.
397		/// </summary>
398		public void BeginTransaction ()
399		{
400			if (!IsInTransaction) {
401				Execute ("begin transaction");
402				IsInTransaction = true;
403			}
404		}
405
406		/// <summary>
407		/// Rolls back the transaction that was begun by <see cref="BeginTransaction"/>.
408		/// </summary>
409		public void Rollback ()
410		{
411			if (IsInTransaction) {
412				Execute ("rollback");
413				IsInTransaction = false;
414			}
415		}
416
417		/// <summary>
418		/// Commits the transaction that was begun by <see cref="BeginTransaction"/>.
419		/// </summary>
420		public void Commit ()
421		{
422			if (IsInTransaction) {
423				Execute ("commit");
424				IsInTransaction = false;
425			}
426		}
427
428		/// <summary>
429		/// Executes <param name="action"> within a transaction and automatically rollsback the transaction
430		/// if an exception occurs. The exception is rethrown.
431		/// </summary>
432		/// <param name="action">
433		/// The <see cref="Action"/> to perform within a transaction. <param name="action"> can contain any number
434		/// of operations on the connection but should never call <see cref="BeginTransaction"/>,
435		/// <see cref="Rollback"/>, or <see cref="Commit"/>.
436		/// </param>
437		public void RunInTransaction (Action action)
438		{
439			if (IsInTransaction) {
440				throw new InvalidOperationException ("The connection must not already be in a transaction when RunInTransaction is called");
441			}
442			try {
443				BeginTransaction ();
444				action ();
445				Commit ();
446			} catch (Exception) {
447				Rollback ();
448				throw;
449			}
450		}
451
452		/// <summary>
453		/// Inserts all specified objects.
454		/// </summary>
455		/// <param name="objects">
456		/// An <see cref="IEnumerable"/> of the objects to insert.
457		/// </param>
458		/// <returns>
459		/// The number of rows added to the table.
460		/// </returns>
461		public int InsertAll (System.Collections.IEnumerable objects)
462		{
463			BeginTransaction ();
464			var c = 0;
465			foreach (var r in objects) {
466				c += Insert (r);
467			}
468			Commit ();
469			return c;
470		}
471
472		/// <summary>
473		/// Inserts the given object and retrieves its
474		/// auto incremented primary key if it has one.
475		/// </summary>
476		/// <param name="obj">
477		/// The object to insert.
478		/// </param>
479		/// <returns>
480		/// The number of rows added to the table.
481		/// </returns>
482		public int Insert (object obj)
483		{
484			if (obj == null) {
485				return 0;
486			}
487			return Insert (obj, "", obj.GetType ());
488		}
489
490		public int Insert (object obj, Type objType)
491		{
492			return Insert (obj, "", objType);
493		}
494
495		public int Insert (object obj, string extra)
496		{
497			if (obj == null) {
498				return 0;
499			}
500			return Insert (obj, extra, obj.GetType ());
501		}
502
503		/// <summary>
504		/// Inserts the given object and retrieves its
505		/// auto incremented primary key if it has one.
506		/// </summary>
507		/// <param name="obj">
508		/// The object to insert.
509		/// </param>
510		/// <param name="extra">
511		/// Literal SQL code that gets placed into the command. INSERT {extra} INTO ...
512		/// </param>
513		/// <returns>
514		/// The number of rows added to the table.
515		/// </returns>
516		public int Insert (object obj, string extra, Type objType)
517		{
518			if (obj == null || objType == null) {
519				return 0;
520			}
521			
522			var map = GetMapping (objType);
523			
524			var cols = map.InsertColumns;
525			var vals = new object[cols.Length];
526			for (var i = 0; i < vals.Length; i++) {
527				vals [i] = cols [i].GetValue (obj);
528			}
529			
530			var insertCmd = map.GetInsertCommand (this, extra);
531			var count = insertCmd.ExecuteNonQuery (vals);
532			
533			if (map.HasAutoIncPK) {
534				var id = SQLite3.LastInsertRowid (Handle);
535				map.SetAutoIncPK (obj, id);
536			}
537			
538			return count;
539		}
540
541		/// <summary>
542		/// Updates all of the columns of a table using the specified object
543		/// except for its primary key.
544		/// The object is required to have a primary key.
545		/// </summary>
546		/// <param name="obj">
547		/// The object to update. It must have a primary key designated using the PrimaryKeyAttribute.
548		/// </param>
549		/// <returns>
550		/// The number of rows updated.
551		/// </returns>
552		public int Update (object obj)
553		{
554			if (obj == null) {
555				return 0;
556			}
557			return Update (obj, obj.GetType ());
558		}
559
560		public int Update (object obj, Type objType)
561		{
562			if (obj == null || objType == null) {
563				return 0;
564			}
565			
566			var map = GetMapping (objType);
567			
568			var pk = map.PK;
569			
570			if (pk == null) {
571				throw new NotSupportedException ("Cannot update " + map.TableName + ": it has no PK");
572			}
573			
574			var cols = from p in map.Columns
575				where p != pk
576				select p;
577			var vals = from c in cols
578				select c.GetValue (obj);
579			var ps = new List<object> (vals);
580			ps.Add (pk.GetValue (obj));
581			var q = string.Format ("update \"{0}\" set {1} where {2} = ? ", map.TableName, string.Join (",", (from c in cols
582				select "\"" + c.Name + "\" = ? ").ToArray ()), pk.Name);
583			return Execute (q, ps.ToArray ());
584		}
585
586		/// <summary>
587		/// Deletes the given object from the database using its primary key.
588		/// </summary>
589		/// <param name="obj">
590		/// The object to delete. It must have a primary key designated using the PrimaryKeyAttribute.
591		/// </param>
592		/// <returns>
593		/// The number of rows deleted.
594		/// </returns>
595		public int Delete<T> (T obj)
596		{
597			var map = GetMapping (obj.GetType ());
598			var pk = map.PK;
599			if (pk == null) {
600				throw new NotSupportedException ("Cannot delete " + map.TableName + ": it has no PK");
601			}
602			var q = string.Format ("delete from \"{0}\" where \"{1}\" = ?", map.TableName, pk.Name);
603			return Execute (q, pk.GetValue (obj));
604		}
605
606		public void Dispose ()
607		{
608			Close ();
609		}
610
611		public void Close ()
612		{
613			if (_open && Handle != IntPtr.Zero) {
614				SQLite3.Close (Handle);
615				Handle = IntPtr.Zero;
616				_open = false;
617			}
618		}
619	}
620
621	public class PrimaryKeyAttribute : Attribute
622	{
623	}
624
625	public class AutoIncrementAttribute : Attribute
626	{
627	}
628
629	public class IndexedAttribute : Attribute
630	{
631	}
632
633	public class IgnoreAttribute : Attribute
634	{
635	}
636
637	public class MaxLengthAttribute : Attribute
638	{
639		public int Value { get;  set; }
640
641		public MaxLengthAttribute (int length)
642		{
643			Value = length;
644		}
645	}
646
647	public class CollationAttribute: Attribute
648	{
649		public string Value { get;  set; }
650
651		public CollationAttribute (string collation)
652		{
653			Value = collation;
654		}
655	}
656
657	public class TableMapping
658	{
659		public Type MappedType { get;  set; }
660
661		public string TableName { get;  set; }
662
663		public Column[] Columns { get;  set; }
664
665		public Column PK { get;  set; }
666
667		Column _autoPk = null;
668		Column[] _insertColumns = null;
669		string _insertSql = null;
670
671		public TableMapping (Type type)
672		{
673			MappedType = type;
674			TableName = MappedType.Name;
675			var props = MappedType.GetProperties (BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty);
676			var cols = new List<Column> ();
677			foreach (var p in props) {
678				var ignore = p.GetCustomAttributes (typeof(IgnoreAttribute), true).Length > 0;
679				if (p.CanWrite && !ignore) {
680					cols.Add (new PropColumn (p));
681				}
682			}
683			Columns = cols.ToArray ();
684			foreach (var c in Columns) {
685				if (c.IsAutoInc && c.IsPK) {
686					_autoPk = c;
687				}
688				if (c.IsPK) {
689					PK = c;
690				}
691			}
692			
693			HasAutoIncPK = _autoPk != null;
694		}
695
696		public bool HasAutoIncPK { get;  set; }
697
698		public void SetAutoIncPK (object obj, long id)
699		{
700			if (_autoPk != null) {
701				_autoPk.SetValue (obj, Convert.ChangeType (id, _autoPk.ColumnType));
702			}
703		}
704
705		public Column[] InsertColumns {
706			get {
707				if (_insertColumns == null) {
708					_insertColumns = Columns.Where (c => !c.IsAutoInc).ToArray ();
709				}
710				return _insertColumns;
711			}
712		}
713
714		public Column FindColumn (string name)
715		{
716			var exact = Columns.Where (c => c.Name == name).FirstOrDefault ();
717			return exact;
718		}
719
720		public string InsertSql (string extra)
721		{
722			if (_insertSql == null) {
723				var cols = InsertColumns;
724				_insertSql = string.Format ("insert {3} into \"{0}\"({1}) values ({2})", TableName, string.Join (",", (from c in cols
725					select "\"" + c.Name + "\"").ToArray ()), string.Join (",", (from c in cols
726					select "?").ToArray ()), extra);
727			}
728			return _insertSql;
729		}
730
731		PreparedSqlLiteInsertCommand _insertCommand;
732		string _insertCommandExtra = null;
733
734		public PreparedSqlLiteInsertCommand GetInsertCommand (SQLiteConnection conn, string extra)
735		{
736			if (_insertCommand == null || _insertCommandExtra != extra) {
737				var insertSql = InsertSql (extra);
738				_insertCommand = new PreparedSqlLiteInsertCommand (conn);
739				_insertCommand.CommandText = insertSql;
740				_insertCommandExtra = extra;
741			}
742			return _insertCommand;
743		}
744
745		public abstract class Column
746		{
747			public string Name { get; protected set; }
748
749			public Type ColumnType { get; protected set; }
750
751			public string Collation { get; protected set; }
752
753			public bool IsAutoInc { get; protected set; }
754
755			public bool IsPK { get; protected set; }
756
757			public bool IsIndexed { get; protected set; }
758
759			public bool IsNullable { get; protected set; }
760
761			public int MaxStringLength { get; protected set; }
762
763			public abstract void SetValue (object obj, object val);
764
765			public abstract object GetValue (object obj);
766		}
767
768		public class PropColumn : Column
769		{
770			PropertyInfo _prop;
771
772			public PropColumn (PropertyInfo prop)
773			{
774				_prop = prop;
775				Name = prop.Name;
776				//If this type is Nullable<T> then Nullable.GetUnderlyingType returns the T, otherwise it returns null, so get the the actual type instead
777				ColumnType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
778				Collation = Orm.Collation (prop);
779				IsAutoInc = Orm.IsAutoInc (prop);
780				IsPK = Orm.IsPK (prop);
781				IsIndexed = Orm.IsIndexed (prop);
782				IsNullable = !IsPK;
783				MaxStringLength = Orm.MaxStringLength (prop);
784			}
785
786			public override void SetValue (object obj, object val)
787			{
788				_prop.SetValue (obj, val, null);
789			}
790
791			public override object GetValue (object obj)
792			{
793				return _prop.GetValue (obj, null);
794			}
795		}
796	}
797
798	public static class Orm
799	{
800		public const int DefaultMaxStringLength = 140;
801
802		public static string SqlDecl (TableMapping.Column p)
803		{
804			string decl = "\"" + p.Name + "\" " + SqlType (p) + " ";
805			
806			if (p.IsPK) {
807				decl += "primary key ";
808			}
809			if (p.IsAutoInc) {
810				decl += "autoincrement ";
811			}
812			if (!p.IsNullable) {
813				decl += "not null ";
814			}
815			if (!string.IsNullOrEmpty (p.Collation)) {
816				decl += "collate " + p.Collation + " ";
817			}
818			
819			return decl;
820		}
821
822		public static string SqlType (TableMapping.Column p)
823		{
824			var clrType = p.ColumnType;
825			if (clrType == typeof(Boolean) || clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32)) {
826				return "integer";
827			} else if (clrType == typeof(UInt32) || clrType == typeof(Int64)) {
828				return "bigint";
829			} else if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal)) {
830				return "float";
831			} else if (clrType == typeof(String)) {
832				int len = p.MaxStringLength;
833				return "varchar(" + len + ")";
834			} else if (clrType == typeof(DateTime)) {
835				return "datetime";
836			} else if (clrType.IsEnum) {
837				return "integer";
838			} else if (clrType == typeof(byte[])) {
839				return "blob";
840			} else {
841				throw new NotSupportedException ("Don't know about " + clrType);
842			}
843		}
844
845		public static bool IsPK (MemberInfo p)
846		{
847			var attrs = p.GetCustomAttributes (typeof(PrimaryKeyAttribute), true);
848			return attrs.Length > 0;
849		}
850
851		public static string Collation (MemberInfo p)
852		{
853			var attrs = p.GetCustomAttributes (typeof(CollationAttribute), true);
854			if (attrs.Length > 0) {
855				return ((CollationAttribute)attrs [0]).Value;
856			} else {
857				return string.Empty;
858			}
859		}
860
861		public static bool IsAutoInc (MemberInfo p)
862		{
863			var attrs = p.GetCustomAttributes (typeof(AutoIncrementAttribute), true);
864			return attrs.Length > 0;
865		}
866
867		public static bool IsIndexed (MemberInfo p)
868		{
869			var attrs = p.GetCustomAttributes (typeof(IndexedAttribute), true);
870			return attrs.Length > 0;
871		}
872
873		public static int MaxStringLength (PropertyInfo p)
874		{
875			var attrs = p.GetCustomAttributes (typeof(MaxLengthAttribute), true);
876			if (attrs.Length > 0) {
877				return ((MaxLengthAttribute)attrs [0]).Value;
878			} else {
879				return DefaultMaxStringLength;
880			}
881		}
882	}
883
884	public class SQLiteCommand
885	{
886		SQLiteConnection _conn;
887		 List<Binding> _bindings;
888
889		public string CommandText { get; set; }
890
891		internal SQLiteCommand (SQLiteConnection conn)
892		{
893			_conn = conn;
894			_bindings = new List<Binding> ();
895			CommandText = "";
896		}
897
898		public int ExecuteNonQuery ()
899		{
900			if (_conn.Trace) {
901				Console.WriteLine ("Executing: " + this);
902			}
903			
904			var r = SQLite3.Result.OK;
905			var stmt = Prepare ();
906			r = SQLite3.Step (stmt);
907			Finalize (stmt);
908			if (r == SQLite3.Result.Done) {
909				int rowsAffected = SQLite3.Changes (_conn.Handle);
910				return rowsAffected;
911			} else if (r == SQLite3.Result.Error) {
912				string msg = SQLite3.GetErrmsg (_conn.Handle);
913				throw SQLiteException.New (r, msg);
914			} else {
915				throw SQLiteException.New (r, r.ToString ());
916			}
917		}
918
919		public List<T> ExecuteQuery<T> () where T : new()
920		{
921			return ExecuteQuery<T> (_conn.GetMapping (typeof(T)));
922		}
923
924		public List<T> ExecuteQuery<T> (TableMapping map)
925		{
926			if (_conn.Trace) {
927				Console.WriteLine ("Executing Query: " + this);
928			}
929			
930			var r = new List<T> ();
931			
932			var stmt = Prepare ();
933			
934			var cols = new TableMapping.Column[SQLite3.ColumnCount (stmt)];
935			
936			for (int i = 0; i < cols.Length; i++) {
937				var name = Marshal.PtrToStringUni (SQLite3.ColumnName16 (stmt, i));
938				cols [i] = map.FindColumn (name);
939			}
940			
941			while (SQLite3.Step (stmt) == SQLite3.Result.Row) {
942				var obj = Activator.CreateInstance (map.MappedType);
943				for (int i = 0; i < cols.Length; i++) {
944					if (cols [i] == null)
945						continue;
946					var colType = SQLite3.ColumnType (stmt, i);
947					var val = ReadCol (stmt, i, colType, cols [i].ColumnType);
948					cols [i].SetValue (obj, val);
949				}
950				r.Add ((T)obj);
951			}
952			
953			Finalize (stmt);
954			return r;
955		}
956
957		public T ExecuteScalar<T> ()
958		{
959			if (_conn.Trace) {
960				Console.WriteLine ("Executing Query: " + this);
961			}
962			
963			T val = default(T);
964			
965			var stmt = Prepare ();
966			if (SQLite3.Step (stmt) == SQLite3.Result.Row) {
967				var colType = SQLite3.ColumnType (stmt, 0);
968				val = (T)ReadCol (stmt, 0, colType, typeof(T));
969			}
970			Finalize (stmt);
971			
972			return val;
973		}
974
975		public void Bind (string name, object val)
976		{
977			_bindings.Add (new Binding {
978				Name = name,
979				Value = val
980			});
981		}
982
983		public void Bind (object val)
984		{
985			Bind (null, val);
986		}
987
988		public override string ToString ()
989		{
990			var parts = new string[1 + _bindings.Count];
991			parts [0] = CommandText;
992			var i = 1;
993			foreach (var b in _bindings) {
994				parts [i] = string.Format ("  {0}: {1}", i - 1, b.Value);
995				i++;
996			}
997			return string.Join (Environment.NewLine, parts);
998		}
999
1000		IntPtr Prepare ()
1001		{
1002			var stmt = SQLite3.Prepare2 (_conn.Handle, CommandText);
1003			BindAll (stmt);
1004			return stmt;
1005		}
1006
1007		void Finalize (IntPtr stmt)
1008		{
1009			SQLite3.Finalize (stmt);
1010		}
1011
1012		void BindAll (IntPtr stmt)
1013		{
1014			int nextIdx = 1;
1015			foreach (var b in _bindings) {
1016				if (b.Name != null) {
1017					b.Index = SQLite3.BindParameterIndex (stmt, b.Name);
1018				} else {
1019					b.Index = nextIdx++;
1020				}
1021			}
1022			foreach (var b in _bindings) {
1023				BindParameter (stmt, b.Index, b.Value);
1024			}
1025		}
1026
1027		internal static IntPtr NegativePointer = new IntPtr (-1);
1028
1029		internal static void BindParameter (IntPtr stmt, int index, object value)
1030		{
1031			if (value == null) {
1032				SQLite3.BindNull (stmt, index);
1033			} else {
1034				if (value is Int32) {
1035					SQLite3.BindInt (stmt, index, (int)value);
1036				} else if (value is String) {
1037					SQLite3.BindText (stmt, index, (string)value, -1, NegativePointer);
1038				} else if (value is Byte || value is UInt16 || value is SByte || value is Int16) {
1039					SQLite3.BindInt (stmt, index, Convert.ToInt32 (value));
1040				} else if (value is Boolean) {
1041					SQLite3.BindInt (stmt, index, (bool)value ? 1 : 0);
1042				} else if (value is UInt32 || value is Int64) {
1043					SQLite3.BindInt64 (stmt, index, Convert.ToInt64 (value));
1044				} else if (value is Single || value is Double || value is Decimal) {
1045					SQLite3.BindDouble (stmt, index, Convert.ToDouble (value));
1046				} else if (value is DateTime) {
1047					SQLite3.BindText (stmt, index, ((DateTime)value).ToString ("yyyy-MM-dd HH:mm:ss"), -1, NegativePointer);
1048				} else if (value.GetType ().IsEnum) {
1049					SQLite3.BindInt (stmt, index, Convert.ToInt32 (value));
1050				} else if (value is byte[]) {
1051					SQLite3.BindBlob (stmt, index, (byte[])value, ((byte[])value).Length, NegativePointer);
1052				} else {
1053					throw new NotSupportedException ("Cannot store type: " + value.GetType ());
1054				}
1055			}
1056		}
1057
1058		class Binding
1059		{
1060			public string Name { get; set; }
1061
1062			public object Value { get; set; }
1063
1064			public int Index { get; set; }
1065		}
1066
1067		object ReadCol (IntPtr stmt, int index, SQLite3.ColType type, Type clrType)
1068		{
1069			if (type == SQLite3.ColType.Null) {
1070				return null;
1071			} else {
1072				if (clrType == typeof(String)) {
1073					return SQLite3.ColumnString (stmt, index);
1074				} else if (clrType == typeof(Int32)) {
1075					return (int)SQLite3.ColumnInt (stmt, index);
1076				} else if (clrType == typeof(Boolean)) {
1077					return SQLite3.ColumnInt (stmt, index) == 1;
1078				} else if (clrType == typeof(double)) {
1079					return SQLite3.ColumnDouble (stmt, index);
1080				} else if (clrType == typeof(float)) {
1081					return (float)SQLite3.ColumnDouble (stmt, index);
1082				} else if (clrType == typeof(DateTime)) {
1083					var text = SQLite3.ColumnString (stmt, index);
1084					return DateTime.Parse (text);
1085				} else if (clrType.IsEnum) {
1086					return SQLite3.ColumnInt (stmt, index);
1087				} else if (clrType == typeof(Int64)) {
1088					return SQLite3.ColumnInt64 (stmt, index);
1089				} else if (clrType == typeof(UInt32)) {
1090					return (uint)SQLite3.ColumnInt64 (stmt, index);
1091				} else if (clrType == typeof(decimal)) {
1092					return (decimal)SQLite3.ColumnDouble (stmt, index);
1093				} else if (clrType == typeof(Byte)) {
1094					return (byte)SQLite3.ColumnInt (stmt, index);
1095				} else if (clrType == typeof(UInt16)) {
1096					return (ushort)SQLite3.ColumnInt (stmt, index);
1097				} else if (clrType == typeof(Int16)) {
1098					return (short)SQLite3.ColumnInt (stmt, index);
1099				} else if (clrType == typeof(sbyte)) {
1100					return (sbyte)SQLite3.ColumnInt (stmt, index);
1101				} else if (clrType == typeof(byte[])) {
1102					return SQLite3.ColumnByteArray (stmt, index);
1103				} else {
1104					throw new NotSupportedException ("Don't know how to read " + clrType);
1105				}
1106			}
1107		}
1108	}
1109
1110	/// <summary>
1111	/// Since the insert never changed, we only need to prepare once.
1112	/// </summary>
1113	public class PreparedSqlLiteInsertCommand : IDisposable
1114	{
1115		public bool Initialized { get; set; }
1116
1117		protected SQLiteConnection Connection { get; set; }
1118
1119		public string CommandText { get; set; }
1120
1121		protected IntPtr Statement { get; set; }
1122
1123		internal PreparedSqlLiteInsertCommand (SQLiteConnection conn)
1124		{
1125			Connection = conn;
1126		}
1127
1128		public int ExecuteNonQuery (object[] source)
1129		{
1130			if (Connection.Trace) {
1131				Console.WriteLine ("Executing: " + CommandText);
1132			}
1133
1134			var r = SQLite3.Result.OK;
1135
1136			if (!Initialized) {
1137				Statement = Prepare ();
1138				Initialized = true;
1139			}
1140
1141			//bind the values.
1142			if (source != null) {
1143				for (int i = 0; i < source.Length; i++) {
1144					SQLiteCommand.BindParameter (Statement, i + 1, source [i]);
1145				}
1146			}
1147			r = SQLite3.Step (Statement);
1148
1149			if (r == SQLite3.Result.Done) {
1150				int rowsAffected = SQLite3.Changes (Connection.Handle);
1151				SQLite3.Reset (Statement);
1152				return rowsAffected;
1153			} else if (r == SQLite3.Result.Error) {
1154				string msg = SQLite3.GetErrmsg (Connection.Handle);
1155				SQLite3.Reset (Statement);
1156				throw SQLiteException.New (r, msg);
1157			} else {
1158				SQLite3.Reset (Statement);
1159				throw SQLiteException.New (r, r.ToString ());
1160			}
1161		}
1162
1163		protected virtual IntPtr Prepare ()
1164		{
1165			var stmt = SQLite3.Prepare2 (Connection.Handle, CommandText);
1166			return stmt;
1167		}
1168
1169		public void Dispose ()
1170		{
1171			Dispose (true);
1172			GC.SuppressFinalize (this);
1173		}
1174
1175		 void Dispose (bool disposing)
1176		{
1177			if (Statement != IntPtr.Zero) {
1178				try {
1179					SQLite3.Finalize (Statement);
1180				} finally {
1181					Statement = IntPtr.Zero;
1182					Connection = null;
1183				}
1184			}
1185		}
1186
1187		~PreparedSqlLiteInsertCommand ()
1188		{
1189			Dispose (false);
1190		}
1191	}
1192
1193	public class TableQuery<T> : IEnumerable<T> where T : new()
1194	{
1195		public SQLiteConnection Connection { get;  set; }
1196
1197		public TableMapping Table { get;  set; }
1198
1199		Expression _where;
1200		List<Ordering> _orderBys;
1201		int? _limit;
1202		int? _offset;
1203
1204		class Ordering
1205		{
1206			public string ColumnName { get; set; }
1207
1208			public bool Ascending { get; set; }
1209		}
1210
1211		TableQuery (SQLiteConnection conn, TableMapping table)
1212		{
1213			Connection = conn;
1214			Table = table;
1215		}
1216
1217		public TableQuery (SQLiteConnection conn)
1218		{
1219			Connection = conn;
1220			Table = Connection.GetMapping (typeof(T));
1221		}
1222
1223		public TableQuery<T> Clone ()
1224		{
1225			var q = new TableQuery<T> (Connection, Table);
1226			q._where = _where;
1227			if (_orderBys != null) {
1228				q._orderBys = new List<Ordering> (_orderBys);
1229			}
1230			q._limit = _limit;
1231			q._offset = _offset;
1232			return q;
1233		}
1234
1235		public TableQuery<T> Where (Expression<Func<T, bool>> predExpr)
1236		{
1237			if (predExpr.NodeType == ExpressionType.Lambda) {
1238				var lambda = (LambdaExpression)predExpr;
1239				var pred = lambda.Body;
1240				var q = Clone ();
1241				q.AddWhere (pred);
1242				return q;
1243			} else {
1244				throw new NotSupportedException ("Must be a predicate");
1245			}
1246		}
1247
1248		public TableQuery<T> Take (int n)
1249		{
1250			var q = Clone ();
1251			q._limit = n;
1252			return q;
1253		}
1254
1255		public TableQuery<T> Skip (int n)
1256		{
1257			var q = Clone ();
1258			q._offset = n;
1259			return q;
1260		}
1261
1262		public TableQuery<T> OrderBy<U> (Expression<Func<T, U>> orderExpr)
1263		{
1264			return AddOrderBy<U> (orderExpr, true);
1265		}
1266
1267		public TableQuery<T> OrderByDescending<U> (Expression<Func<T, U>> orderExpr)
1268		{
1269			return AddOrderBy<U> (orderExpr, false);
1270		}
1271
1272		 TableQuery<T> AddOrderBy<U> (Expression<Func<T, U>> orderExpr, bool asc)
1273		{
1274			if (orderExpr.NodeType == ExpressionType.Lambda) {
1275				var lambda = (LambdaExpression)orderExpr;
1276				var mem = lambda.Body as MemberExpression;
1277				if (mem != null && (mem.Expression.NodeType == ExpressionType.Parameter)) {
1278					var q = Clone ();
1279					if (q._orderBys == null) {
1280						q._orderBys = new List<Ordering> ();
1281					}
1282					q._orderBys.Add (new Ordering {
1283						ColumnName = mem.Member.Name,
1284						Ascending = asc
1285					});
1286					return q;
1287				} else {
1288					throw new NotSupportedException ("Order By does not support: " + orderExpr);
1289				}
1290			} else {
1291				throw new NotSupportedException ("Must be a predicate");
1292			}
1293		}
1294
1295		 void AddWhere (Expression pred)
1296		{
1297			if (_where == null) {
1298				_where = pred;
1299			} else {
1300				_where = Expression.AndAlso (_where, pred);
1301			}
1302		}
1303
1304		 SQLiteCommand GenerateCommand (string selectionList)
1305		{
1306			var cmdText = "select " + selectionList + " from \"" + Table.TableName + "\"";
1307			var args = new List<object> ();
1308			if (_where != null) {
1309				var w = CompileExpr (_where, args);
1310				cmdText += " where " + w.CommandText;
1311			}
1312			if ((_orderBys != null) && (_orderBys.Count > 0)) {
1313				var t = string.Join (", ", _orderBys.Select (o => "\"" + o.ColumnName + "\"" + (o.Ascending ? "" : " desc")).ToArray ());
1314				cmdText += " order by " + t;
1315			}
1316			if (_limit.HasValue) {
1317				cmdText += " limit " + _limit.Value;
1318			}
1319			if (_offset.HasValue) {
1320				if (!_limit.HasValue) {
1321					cmdText += " limit -1 ";
1322				}
1323				cmdText += " offset " + _offset.Value;
1324			}
1325			return Connection.CreateCommand (cmdText, args.ToArray ());
1326		}
1327
1328		class CompileResult
1329		{
1330			public string CommandText { get; set; }
1331
1332			public object Value { get; set; }
1333		}
1334
1335		 CompileResult CompileExpr (Expression expr, List<object> queryArgs)
1336		{
1337			if (expr == null) {
1338				throw new NotSupportedException ("Expression is NULL");
1339			} else if (expr is BinaryExpression) {
1340				var bin = (BinaryExpression)expr;
1341				
1342				var leftr = CompileExpr (bin.Left, queryArgs);
1343				var rightr = CompileExpr (bin.Right, queryArgs);
1344
1345				//If either side is a parameter and is null, then handle the other side specially (for "is null"/"is not null")
1346				string text;
1347				if (leftr.CommandText == "?" && leftr.Value == null)
1348					text = CompileNullBinaryExpression(bin, rightr);
1349				else if (rightr.CommandText == "?" && rightr.Value == null)
1350					text = CompileNullBinaryExpression(bin, leftr);
1351				else
1352					text = "(" + leftr.CommandText + " " + GetSqlName(bin) + " " + rightr.CommandText + ")";
1353				return new CompileResult { CommandText = text };
1354			} else if (expr.NodeType == ExpressionType.Call) {
1355				
1356				var call = (MethodCallExpression)expr;
1357				var args = new CompileResult[call.Arguments.Count];
1358				
1359				for (var i = 0; i < args.Length; i++) {
1360					args [i] = CompileExpr (call.Arguments [i], queryArgs);
1361				}
1362				
1363				var sqlCall = "";
1364				
1365				if (call.Method.Name == "Like" && args.Length == 2) {
1366					sqlCall = "(" + args [0].CommandText + " like " + args [1].CommandText + ")";
1367				} else if (call.Method.Name == "Contains" && args.Length == 2) {
1368					sqlCall = "(" + args [1].CommandText + " in " + args [0].CommandText + ")";
1369				} else {
1370					sqlCall = call.Method.Name.ToLower () + "(" + string.Join (",", args.Select (a => a.CommandText).ToArray ()) + ")";
1371				}
1372				return new CompileResult { CommandText = sqlCall };
1373				
1374			} else if (expr.NodeType == ExpressionType.Constant) {
1375				var c = (ConstantExpression)expr;
1376				queryArgs.Add (c.Value);
1377				return new CompileResult {
1378					CommandText = "?",
1379					Value = c.Value
1380				};
1381			} else if (expr.NodeType == ExpressionType.Convert) {
1382				var u = (UnaryExpression)expr;
1383				var ty = u.Type;
1384				var valr = CompileExpr (u.Operand, queryArgs);
1385				return new CompileResult {
1386					CommandText = valr.CommandText,
1387					Value = valr.Value != null ? Convert.ChangeType (valr.Value, ty) : null
1388				};
1389			} else if (expr.NodeType == ExpressionType.MemberAccess) {
1390				var mem = (MemberExpression)expr;
1391				
1392				if (mem.Expression.NodeType == ExpressionType.Parameter) {
1393					//
1394					// This is a column of our table, output just the column name
1395					//
1396					return new CompileResult { CommandText = "\"" + mem.Member.Name + "\"" };
1397				} else {
1398					object obj = null;
1399					if (mem.Expression != null) {
1400						var r = CompileExpr (mem.Expression, queryArgs);
1401						if (r.Value == null) {
1402							throw new NotSupportedException ("Member access failed to compile expression");
1403						}
1404						if (r.CommandText == "?") {
1405							queryArgs.RemoveAt (queryArgs.Count - 1);
1406						}
1407						obj = r.Value;
1408					}
1409					
1410					//
1411					// Get the member value
1412					//
1413					object val = null;
1414					
1415					if (mem.Member.MemberType == MemberTypes.Property) {
1416						var m = (PropertyInfo)mem.Member;
1417						val = m.GetValue (obj, null);						
1418					} else if (mem.Member.MemberType == MemberTypes.Field) {
1419						var m = (FieldInfo)mem.Member;
1420						val = m.GetValue (obj);						
1421					} else {
1422						throw new NotSupportedException ("MemberExpr: " + mem.Member.MemberType.ToString ());
1423					}
1424					
1425					//
1426					// Work special magic for enumerables
1427					//
1428					if (val != null && val is System.Collections.IEnumerable && !(val is string)) {
1429						var sb = new System.Text.StringBuilder();
1430						sb.Append("(");
1431						var head = "";
1432						foreach (var a in (System.Collections.IEnumerable)val) {
1433							queryArgs.Add(a);
1434							sb.Append(head);
1435							sb.Append("?");
1436							head = ",";
1437						}
1438						sb.Append(")");
1439						return new CompileResult {
1440							CommandText = sb.ToString(),
1441							Value = val
1442						};
1443					}
1444					else {
1445						queryArgs.Add (val);
1446						return new CompileResult {
1447							CommandText = "?",
1448							Value = val
1449						};
1450					}
1451				}
1452			}
1453			throw new NotSupportedException ("Cannot compile: " + expr.NodeType.ToString ());
1454		}
1455
1456		/// <summary>
1457		/// Compiles a BinaryExpression where one of the parameters is null.
1458		/// </summary>
1459		/// <param name="parameter">The non-null parameter</param>
1460		 string CompileNullBinaryExpression(BinaryExpression expression, CompileResult parameter)
1461		{
1462			if (expression.NodeType == ExpressionType.Equal)
1463				return "(" + parameter.CommandText + " is ?)";
1464			else if (expression.NodeType == ExpressionType.NotEqual)
1465				return "(" + parameter.CommandText + " is not ?)";
1466			else
1467				throw new NotSupportedException("Cannot compile Null-BinaryExpression with type " + expression.NodeType.ToString());
1468		}
1469
1470		string GetSqlName (Expression expr)
1471		{
1472			var n = expr.NodeType;
1473			if (n == ExpressionType.GreaterThan)
1474				return ">"; else if (n == ExpressionType.GreaterThanOrEqual) {
1475				return ">=";
1476			} else if (n == ExpressionType.LessThan) {
1477				return "<";
1478			} else if (n == ExpressionType.LessThanOrEqual) {
1479				return "<=";
1480			} else if (n == ExpressionType.And) {
1481				return "and";
1482			} else if (n == ExpressionType.AndAlso) {
1483				return "and";
1484			} else if (n == ExpressionType.Or) {
1485				return "or";
1486			} else if (n == ExpressionType.OrElse) {
1487				return "or";
1488			} else if (n == ExpressionType.Equal) {
1489				return "=";
1490			} else if (n == ExpressionType.NotEqual) {
1491				return "!=";
1492			} else {
1493				throw new System.NotSupportedException ("Cannot get SQL for: " + n.ToString ());
1494			}
1495		}
1496		
1497		public int Count ()
1498		{
1499			return GenerateCommand("count(*)").ExecuteScalar<int> ();			
1500		}
1501
1502		public IEnumerator<T> GetEnumerator ()
1503		{
1504			return GenerateCommand ("*").ExecuteQuery<T> ().GetEnumerator ();
1505		}
1506
1507		System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator ()
1508		{
1509			return GetEnumerator ();
1510		}
1511	}
1512
1513	public static class SQLite3
1514	{
1515		public enum Result : int
1516		{
1517			OK = 0,
1518			Error = 1,
1519			Internal = 2,
1520			Perm = 3,
1521			Abort = 4,
1522			Busy = 5,
1523			Locked = 6,
1524			NoMem = 7,
1525			ReadOnly = 8,
1526			Interrupt = 9,
1527			IOError = 10,
1528			Corrupt = 11,
1529			NotFound = 12,
1530			TooBig = 18,
1531			Constraint = 19,
1532			Row = 100,
1533			Done = 101
1534		}
1535
1536		public enum ConfigOption : int
1537		{
1538			SingleThread = 1,
1539			MultiThread = 2,
1540			Serialized = 3
1541		}
1542
1543		[DllImport("sqlite3", EntryPoint = "sqlite3_open")]
1544		public static extern Result Open (string filename, out IntPtr db);
1545
1546		[DllImport("sqlite3", EntryPoint = "sqlite3_close")]
1547		public static extern Result Close (IntPtr db);
1548
1549		[DllImport("sqlite3", EntryPoint = "sqlite3_config")]
1550		public static extern Result Config (ConfigOption option);
1551
1552		[DllImport("sqlite3", EntryPoint = "sqlite3_busy_timeout")]
1553		public static extern Result BusyTimeout (IntPtr db, int milliseconds);
1554
1555		[DllImport("sqlite3", EntryPoint = "sqlite3_changes")]
1556		public static extern int Changes (IntPtr db);
1557
1558		[DllImport("sqlite3", EntryPoint = "sqlite3_prepare_v2")]
1559		public static extern Result Prepare2 (IntPtr db, string sql, int numBytes, out IntPtr stmt, IntPtr pzTail);
1560
1561		public static IntPtr Prepare2 (IntPtr db, string query)
1562		{
1563			IntPtr stmt;
1564			var r = Prepare2 (db, query, query.Length, out stmt, IntPtr.Zero);
1565			if (r != Result.OK) {
1566				throw SQLiteException.New (r, GetErrmsg (db));
1567			}
1568			return stmt;
1569		}
1570
1571		[DllImport("sqlite3", EntryPoint = "sqlite3_step")]
1572		public static extern Result Step (IntPtr stmt);
1573
1574		[DllImport("sqlite3", EntryPoint = "sqlite3_reset")]
1575		public static extern Result Reset (IntPtr stmt);
1576
1577		[DllImport("sqlite3", EntryPoint = "sqlite3_finalize")]
1578		public static extern Result Finalize (IntPtr stmt);
1579
1580		[DllImport("sqlite3", EntryPoint = "sqlite3_last_insert_rowid")]
1581		public static extern long LastInsertRowid (IntPtr db);
1582
1583		[DllImport("sqlite3", EntryPoint = "sqlite3_errmsg16")]
1584		public static extern IntPtr Errmsg (IntPtr db);
1585
1586		public static string GetErrmsg (IntPtr db)
1587		{
1588			return Marshal.PtrToStringUni (Errmsg (db));
1589		}
1590
1591		[DllImport("sqlite3", EntryPoint = "sqlite3_bind_parameter_index")]
1592		public static extern int BindParameterIndex (IntPtr stmt, string name);
1593
1594		[DllImport("sqlite3", EntryPoint = "sqlite3_bind_null")]
1595		public static extern int BindNull (IntPtr stmt, int index);
1596
1597		[DllImport("sqlite3", EntryPoint = "sqlite3_bind_int")]
1598		public static extern int BindInt (IntPtr stmt, int index, int val);
1599
1600		[DllImport("sqlite3", EntryPoint = "sqlite3_bind_int64")]
1601		public static extern int BindInt64 (IntPtr stmt, int index, long val);
1602
1603		[DllImport("sqlite3", EntryPoint = "sqlite3_bind_double")]
1604		public static extern int BindDouble (IntPtr stmt, int index, double val);
1605
1606		[DllImport("sqlite3", EntryPoint = "sqlite3_bind_text")]
1607		public static extern int BindText (IntPtr stmt, int index, string val, int n, IntPtr free);
1608
1609		[DllImport("sqlite3", EntryPoint = "sqlite3_bind_blob")]
1610		public static extern int BindBlob (IntPtr stmt, int index, byte[] val, int n, IntPtr free);
1611
1612		[DllImport("sqlite3", EntryPoint = "sqlite3_column_count")]
1613		public static extern int ColumnCount (IntPtr stmt);
1614
1615		[DllImport("sqlite3", EntryPoint = "sqlite3_column_name")]
1616		public static extern IntPtr ColumnName (IntPtr stmt, int index);
1617
1618		[DllImport("sqlite3", EntryPoint = "sqlite3_column_name16")]
1619		public static extern IntPtr ColumnName16 (IntPtr stmt, int index);
1620
1621		[DllImport("sqlite3", EntryPoint = "sqlite3_column_type")]
1622		public static extern ColType ColumnType (IntPtr stmt, int index);
1623
1624		[DllImport("sqlite3", EntryPoint = "sqlite3_column_int")]
1625		public static extern int ColumnInt (IntPtr stmt, int index);
1626
1627		[DllImport("sqlite3", EntryPoint = "sqlite3_column_int64")]
1628		public static extern long ColumnInt64 (IntPtr stmt, int index);
1629
1630		[DllImport("sqlite3", EntryPoint = "sqlite3_column_double")]
1631		public static extern double ColumnDouble (IntPtr stmt, int index);
1632
1633		[DllImport("sqlite3", EntryPoint = "sqlite3_column_text")]
1634		public static extern IntPtr ColumnText (IntPtr stmt, int index);
1635
1636		[DllImport("sqlite3", EntryPoint = "sqlite3_column_text16")]
1637		public static extern IntPtr ColumnText16 (IntPtr stmt, int index);
1638
1639		[DllImport("sqlite3", EntryPoint = "sqlite3_column_blob")]
1640		public static extern IntPtr ColumnBlob (IntPtr stmt, int index);
1641
1642		[DllImport("sqlite3", EntryPoint = "sqlite3_column_bytes")]
1643		public static extern int ColumnBytes (IntPtr stmt, int index);
1644
1645		public static string ColumnString (IntPtr stmt, int index)
1646		{
1647			return Marshal.PtrToStringUni (SQLite3.ColumnText16 (stmt, index));
1648		}
1649
1650		public static byte[] ColumnByteArray (IntPtr stmt, int index)
1651		{
1652			int length = ColumnBytes (stmt, index);
1653			byte[] result = new byte[length];
1654			if (length > 0)
1655				Marshal.Copy (ColumnBlob (stmt, index), result, 0, length);
1656			return result;
1657		}
1658
1659		public enum ColType : int
1660		{
1661			Integer = 1,
1662			Float = 2,
1663			Text = 3,
1664			Blob = 4,
1665			Null = 5
1666		}
1667	}
1668	
1669}