· 6 years ago · Apr 29, 2019, 12:24 AM
1//
2// Copyright (c) 2009-2012 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//
22#if WINDOWS_PHONE && !USE_WP8_NATIVE_SQLITE
23#define USE_CSHARP_SQLITE
24#endif
25
26using System;
27using System.Diagnostics;
28using System.Runtime.InteropServices;
29using System.Collections.Generic;
30using System.Reflection;
31using System.Linq;
32using System.Linq.Expressions;
33using System.Threading;
34
35#if USE_CSHARP_SQLITE
36using Sqlite3 = Community.CsharpSqlite.Sqlite3;
37using Sqlite3DatabaseHandle = Community.CsharpSqlite.Sqlite3.sqlite3;
38using Sqlite3Statement = Community.CsharpSqlite.Sqlite3.Vdbe;
39#elif USE_WP8_NATIVE_SQLITE
40using Sqlite3 = Sqlite.Sqlite3;
41using Sqlite3DatabaseHandle = Sqlite.Database;
42using Sqlite3Statement = Sqlite.Statement;
43#else
44using Sqlite3DatabaseHandle = System.IntPtr;
45using Sqlite3Statement = System.IntPtr;
46#endif
47
48namespace SQLite4Unity3d
49{
50 public class SQLiteException : Exception
51 {
52 public SQLite3.Result Result { get; private set; }
53
54 protected SQLiteException(SQLite3.Result r, string message) : base(message)
55 {
56 Result = r;
57 }
58
59 public static SQLiteException New(SQLite3.Result r, string message)
60 {
61 return new SQLiteException(r, message);
62 }
63 }
64
65 public class NotNullConstraintViolationException : SQLiteException
66 {
67 public IEnumerable<TableMapping.Column> Columns { get; protected set; }
68
69 protected NotNullConstraintViolationException(SQLite3.Result r, string message)
70 : this(r, message, null, null)
71 {
72
73 }
74
75 protected NotNullConstraintViolationException(SQLite3.Result r, string message, TableMapping mapping, object obj)
76 : base(r, message)
77 {
78 if (mapping != null && obj != null)
79 {
80 this.Columns = from c in mapping.Columns
81 where c.IsNullable == false && c.GetValue(obj) == null
82 select c;
83 }
84 }
85
86 public static new NotNullConstraintViolationException New(SQLite3.Result r, string message)
87 {
88 return new NotNullConstraintViolationException(r, message);
89 }
90
91 public static NotNullConstraintViolationException New(SQLite3.Result r, string message, TableMapping mapping, object obj)
92 {
93 return new NotNullConstraintViolationException(r, message, mapping, obj);
94 }
95
96 public static NotNullConstraintViolationException New(SQLiteException exception, TableMapping mapping, object obj)
97 {
98 return new NotNullConstraintViolationException(exception.Result, exception.Message, mapping, obj);
99 }
100 }
101
102 [Flags]
103 public enum SQLiteOpenFlags
104 {
105 ReadOnly = 1, ReadWrite = 2, Create = 4,
106 NoMutex = 0x8000, FullMutex = 0x10000,
107 SharedCache = 0x20000, PrivateCache = 0x40000,
108 ProtectionComplete = 0x00100000,
109 ProtectionCompleteUnlessOpen = 0x00200000,
110 ProtectionCompleteUntilFirstUserAuthentication = 0x00300000,
111 ProtectionNone = 0x00400000
112 }
113
114 [Flags]
115 public enum CreateFlags
116 {
117 None = 0,
118 ImplicitPK = 1, // create a primary key for field called 'Id' (Orm.ImplicitPkName)
119 ImplicitIndex = 2, // create an index for fields ending in 'Id' (Orm.ImplicitIndexSuffix)
120 AllImplicit = 3, // do both above
121
122 AutoIncPK = 4 // force PK field to be auto inc
123 }
124
125 /// <summary>
126 /// Represents an open connection to a SQLite database.
127 /// </summary>
128 public partial class SQLiteConnection : IDisposable
129 {
130 private bool _open;
131 private TimeSpan _busyTimeout;
132 private Dictionary<string, TableMapping> _mappings = null;
133 private Dictionary<string, TableMapping> _tables = null;
134 private System.Diagnostics.Stopwatch _sw;
135 private long _elapsedMilliseconds = 0;
136
137 private int _transactionDepth = 0;
138 private Random _rand = new Random();
139
140 public Sqlite3DatabaseHandle Handle { get; private set; }
141 internal static readonly Sqlite3DatabaseHandle NullHandle = default(Sqlite3DatabaseHandle);
142
143 public string DatabasePath { get; private set; }
144
145 // Dictionary of synchronization objects.
146 //
147 // To prevent database disruption, a database file must be accessed *synchronously*.
148 // For the purpose we create synchronous objects for each database file and store in the
149 // static dictionary to share it among all connections.
150 // The key of the dictionary is database file path and its value is an object to be used
151 // by lock() statement.
152 //
153 // Use case:
154 // - database file lock is done implicitly and automatically.
155 // - To prepend deadlock, application may lock a database file explicity by either way:
156 // - RunInTransaction(Action) locks the database during the transaction (for insert/update)
157 // - RunInDatabaseLock(Action) similarly locks the database but no transaction (for query)
158 private static Dictionary<string, object> syncObjects = new Dictionary<string, object>();
159
160 public bool TimeExecution { get; set; }
161
162 #region debug tracing
163
164 public bool Trace { get; set; }
165
166 public delegate void TraceHandler(string message);
167 public event TraceHandler TraceEvent;
168
169 internal void InvokeTrace(string message)
170 {
171 if (TraceEvent != null)
172 {
173 TraceEvent(message);
174 }
175 }
176
177 #endregion
178
179 public bool StoreDateTimeAsTicks { get; private set; }
180
181 /// <summary>
182 /// Constructs a new SQLiteConnection and opens a SQLite database specified by databasePath.
183 /// </summary>
184 /// <param name="databasePath">
185 /// Specifies the path to the database file.
186 /// </param>
187 /// <param name="storeDateTimeAsTicks">
188 /// Specifies whether to store DateTime properties as ticks (true) or strings (false). You
189 /// absolutely do want to store them as Ticks in all new projects. The default of false is
190 /// only here for backwards compatibility. There is a *significant* speed advantage, with no
191 /// down sides, when setting storeDateTimeAsTicks = true.
192 /// </param>
193 public SQLiteConnection(string databasePath, bool storeDateTimeAsTicks = false)
194 : this(databasePath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create, storeDateTimeAsTicks)
195 {
196 }
197
198 /// <summary>
199 /// Constructs a new SQLiteConnection and opens a SQLite database specified by databasePath.
200 /// </summary>
201 /// <param name="databasePath">
202 /// Specifies the path to the database file.
203 /// </param>
204 /// <param name="storeDateTimeAsTicks">
205 /// Specifies whether to store DateTime properties as ticks (true) or strings (false). You
206 /// absolutely do want to store them as Ticks in all new projects. The default of false is
207 /// only here for backwards compatibility. There is a *significant* speed advantage, with no
208 /// down sides, when setting storeDateTimeAsTicks = true.
209 /// </param>
210 public SQLiteConnection(string databasePath, SQLiteOpenFlags openFlags, bool storeDateTimeAsTicks = false)
211 {
212 if (string.IsNullOrEmpty(databasePath))
213 throw new ArgumentException("Must be specified", "databasePath");
214
215 DatabasePath = databasePath;
216 mayCreateSyncObject(databasePath);
217
218#if NETFX_CORE
219 SQLite3.SetDirectory(/*temp directory type*/2, Windows.Storage.ApplicationData.Current.TemporaryFolder.Path);
220#endif
221
222 Sqlite3DatabaseHandle handle;
223
224#if SILVERLIGHT || USE_CSHARP_SQLITE
225 var r = SQLite3.Open (databasePath, out handle, (int)openFlags, IntPtr.Zero);
226#else
227 // open using the byte[]
228 // in the case where the path may include Unicode
229 // force open to using UTF-8 using sqlite3_open_v2
230 var databasePathAsBytes = GetNullTerminatedUtf8(DatabasePath);
231 var r = SQLite3.Open(databasePathAsBytes, out handle, (int)openFlags, IntPtr.Zero);
232#endif
233
234 Handle = handle;
235 if (r != SQLite3.Result.OK)
236 {
237 throw SQLiteException.New(r, String.Format("Could not open database file: {0} ({1})", DatabasePath, r));
238 }
239 _open = true;
240
241 StoreDateTimeAsTicks = storeDateTimeAsTicks;
242
243 BusyTimeout = TimeSpan.FromSeconds(0.1);
244 }
245
246 static SQLiteConnection()
247 {
248 if (_preserveDuringLinkMagic)
249 {
250 var ti = new ColumnInfo();
251 ti.Name = "magic";
252 }
253 }
254
255 void mayCreateSyncObject(string databasePath)
256 {
257 if (!syncObjects.ContainsKey(databasePath))
258 {
259 syncObjects[databasePath] = new object();
260 }
261 }
262
263 /// <summary>
264 /// Gets the synchronous object, to be lock the database file for updating.
265 /// </summary>
266 /// <value>The sync object.</value>
267 public object SyncObject { get { return syncObjects[DatabasePath]; } }
268
269 public void EnableLoadExtension(int onoff)
270 {
271 SQLite3.Result r = SQLite3.EnableLoadExtension(Handle, onoff);
272 if (r != SQLite3.Result.OK)
273 {
274 string msg = SQLite3.GetErrmsg(Handle);
275 throw SQLiteException.New(r, msg);
276 }
277 }
278
279 static byte[] GetNullTerminatedUtf8(string s)
280 {
281 var utf8Length = System.Text.Encoding.UTF8.GetByteCount(s);
282 var bytes = new byte[utf8Length + 1];
283 utf8Length = System.Text.Encoding.UTF8.GetBytes(s, 0, s.Length, bytes, 0);
284 return bytes;
285 }
286
287 /// <summary>
288 /// Used to list some code that we want the MonoTouch linker
289 /// to see, but that we never want to actually execute.
290 /// </summary>
291#pragma warning disable 649
292 static bool _preserveDuringLinkMagic;
293#pragma warning restore 649
294
295 /// <summary>
296 /// Sets a busy handler to sleep the specified amount of time when a table is locked.
297 /// The handler will sleep multiple times until a total time of <see cref="BusyTimeout"/> has accumulated.
298 /// </summary>
299 public TimeSpan BusyTimeout
300 {
301 get { return _busyTimeout; }
302 set
303 {
304 _busyTimeout = value;
305 if (Handle != NullHandle)
306 {
307 SQLite3.BusyTimeout(Handle, (int)_busyTimeout.TotalMilliseconds);
308 }
309 }
310 }
311
312 /// <summary>
313 /// Returns the mappings from types to tables that the connection
314 /// currently understands.
315 /// </summary>
316 public IEnumerable<TableMapping> TableMappings
317 {
318 get
319 {
320 return _tables != null ? _tables.Values : Enumerable.Empty<TableMapping>();
321 }
322 }
323
324 /// <summary>
325 /// Retrieves the mapping that is automatically generated for the given type.
326 /// </summary>
327 /// <param name="type">
328 /// The type whose mapping to the database is returned.
329 /// </param>
330 /// <param name="createFlags">
331 /// Optional flags allowing implicit PK and indexes based on naming conventions
332 /// </param>
333 /// <returns>
334 /// The mapping represents the schema of the columns of the database and contains
335 /// methods to set and get properties of objects.
336 /// </returns>
337 public TableMapping GetMapping(Type type, CreateFlags createFlags = CreateFlags.None)
338 {
339 if (_mappings == null)
340 {
341 _mappings = new Dictionary<string, TableMapping>();
342 }
343 TableMapping map;
344 if (!_mappings.TryGetValue(type.FullName, out map))
345 {
346 map = new TableMapping(type, createFlags);
347 _mappings[type.FullName] = map;
348 }
349 return map;
350 }
351
352 /// <summary>
353 /// Retrieves the mapping that is automatically generated for the given type.
354 /// </summary>
355 /// <returns>
356 /// The mapping represents the schema of the columns of the database and contains
357 /// methods to set and get properties of objects.
358 /// </returns>
359 public TableMapping GetMapping<T>()
360 {
361 return GetMapping(typeof(T));
362 }
363
364 private struct IndexedColumn
365 {
366 public int Order;
367 public string ColumnName;
368 }
369
370 private struct IndexInfo
371 {
372 public string IndexName;
373 public string TableName;
374 public bool Unique;
375 public List<IndexedColumn> Columns;
376 }
377
378 /// <summary>
379 /// Executes a "drop table" on the database. This is non-recoverable.
380 /// </summary>
381 public int DropTable<T>()
382 {
383 var map = GetMapping(typeof(T));
384
385 var query = string.Format("drop table if exists \"{0}\"", map.TableName);
386
387 return Execute(query);
388 }
389
390 /// <summary>
391 /// Executes a "create table if not exists" on the database. It also
392 /// creates any specified indexes on the columns of the table. It uses
393 /// a schema automatically generated from the specified type. You can
394 /// later access this schema by calling GetMapping.
395 /// </summary>
396 /// <returns>
397 /// The number of entries added to the database schema.
398 /// </returns>
399 public int CreateTable<T>(CreateFlags createFlags = CreateFlags.None)
400 {
401 return CreateTable(typeof(T), createFlags);
402 }
403
404 /// <summary>
405 /// Executes a "create table if not exists" on the database. It also
406 /// creates any specified indexes on the columns of the table. It uses
407 /// a schema automatically generated from the specified type. You can
408 /// later access this schema by calling GetMapping.
409 /// </summary>
410 /// <param name="ty">Type to reflect to a database table.</param>
411 /// <param name="createFlags">Optional flags allowing implicit PK and indexes based on naming conventions.</param>
412 /// <returns>
413 /// The number of entries added to the database schema.
414 /// </returns>
415 public int CreateTable(Type ty, CreateFlags createFlags = CreateFlags.None)
416 {
417 if (_tables == null)
418 {
419 _tables = new Dictionary<string, TableMapping>();
420 }
421 TableMapping map;
422 if (!_tables.TryGetValue(ty.FullName, out map))
423 {
424 map = GetMapping(ty, createFlags);
425 _tables.Add(ty.FullName, map);
426 }
427 var query = "create table if not exists \"" + map.TableName + "\"(\n";
428
429 var decls = map.Columns.Select(p => Orm.SqlDecl(p, StoreDateTimeAsTicks));
430 var decl = string.Join(",\n", decls.ToArray());
431 query += decl;
432 query += ")";
433
434 var count = Execute(query);
435
436 if (count == 0)
437 { //Possible bug: This always seems to return 0?
438 // Table already exists, migrate it
439 MigrateTable(map);
440 }
441
442 var indexes = new Dictionary<string, IndexInfo>();
443 foreach (var c in map.Columns)
444 {
445 foreach (var i in c.Indices)
446 {
447 var iname = i.Name ?? map.TableName + "_" + c.Name;
448 IndexInfo iinfo;
449 if (!indexes.TryGetValue(iname, out iinfo))
450 {
451 iinfo = new IndexInfo
452 {
453 IndexName = iname,
454 TableName = map.TableName,
455 Unique = i.Unique,
456 Columns = new List<IndexedColumn>()
457 };
458 indexes.Add(iname, iinfo);
459 }
460
461 if (i.Unique != iinfo.Unique)
462 throw new Exception("All the columns in an index must have the same value for their Unique property");
463
464 iinfo.Columns.Add(new IndexedColumn
465 {
466 Order = i.Order,
467 ColumnName = c.Name
468 });
469 }
470 }
471
472 foreach (var indexName in indexes.Keys)
473 {
474 var index = indexes[indexName];
475 string[] columnNames = new string[index.Columns.Count];
476 if (index.Columns.Count == 1)
477 {
478 columnNames[0] = index.Columns[0].ColumnName;
479 }
480 else
481 {
482 index.Columns.Sort((lhs, rhs) => {
483 return lhs.Order - rhs.Order;
484 });
485 for (int i = 0, end = index.Columns.Count; i < end; ++i)
486 {
487 columnNames[i] = index.Columns[i].ColumnName;
488 }
489 }
490 count += CreateIndex(indexName, index.TableName, columnNames, index.Unique);
491 }
492
493 return count;
494 }
495
496 /// <summary>
497 /// Creates an index for the specified table and columns.
498 /// </summary>
499 /// <param name="indexName">Name of the index to create</param>
500 /// <param name="tableName">Name of the database table</param>
501 /// <param name="columnNames">An array of column names to index</param>
502 /// <param name="unique">Whether the index should be unique</param>
503 public int CreateIndex(string indexName, string tableName, string[] columnNames, bool unique = false)
504 {
505 const string sqlFormat = "create {2} index if not exists \"{3}\" on \"{0}\"(\"{1}\")";
506 var sql = String.Format(sqlFormat, tableName, string.Join("\", \"", columnNames), unique ? "unique" : "", indexName);
507 return Execute(sql);
508 }
509
510 /// <summary>
511 /// Creates an index for the specified table and column.
512 /// </summary>
513 /// <param name="indexName">Name of the index to create</param>
514 /// <param name="tableName">Name of the database table</param>
515 /// <param name="columnName">Name of the column to index</param>
516 /// <param name="unique">Whether the index should be unique</param>
517 public int CreateIndex(string indexName, string tableName, string columnName, bool unique = false)
518 {
519 return CreateIndex(indexName, tableName, new string[] { columnName }, unique);
520 }
521
522 /// <summary>
523 /// Creates an index for the specified table and column.
524 /// </summary>
525 /// <param name="tableName">Name of the database table</param>
526 /// <param name="columnName">Name of the column to index</param>
527 /// <param name="unique">Whether the index should be unique</param>
528 public int CreateIndex(string tableName, string columnName, bool unique = false)
529 {
530 return CreateIndex(tableName + "_" + columnName, tableName, columnName, unique);
531 }
532
533 /// <summary>
534 /// Creates an index for the specified table and columns.
535 /// </summary>
536 /// <param name="tableName">Name of the database table</param>
537 /// <param name="columnNames">An array of column names to index</param>
538 /// <param name="unique">Whether the index should be unique</param>
539 public int CreateIndex(string tableName, string[] columnNames, bool unique = false)
540 {
541 return CreateIndex(tableName + "_" + string.Join("_", columnNames), tableName, columnNames, unique);
542 }
543
544 /// <summary>
545 /// Creates an index for the specified object property.
546 /// e.g. CreateIndex<Client>(c => c.Name);
547 /// </summary>
548 /// <typeparam name="T">Type to reflect to a database table.</typeparam>
549 /// <param name="property">Property to index</param>
550 /// <param name="unique">Whether the index should be unique</param>
551 public void CreateIndex<T>(Expression<Func<T, object>> property, bool unique = false)
552 {
553 MemberExpression mx;
554 if (property.Body.NodeType == ExpressionType.Convert)
555 {
556 mx = ((UnaryExpression)property.Body).Operand as MemberExpression;
557 }
558 else
559 {
560 mx = (property.Body as MemberExpression);
561 }
562 var propertyInfo = mx.Member as PropertyInfo;
563 if (propertyInfo == null)
564 {
565 throw new ArgumentException("The lambda expression 'property' should point to a valid Property");
566 }
567
568 var propName = propertyInfo.Name;
569
570 var map = GetMapping<T>();
571 var colName = map.FindColumnWithPropertyName(propName).Name;
572
573 CreateIndex(map.TableName, colName, unique);
574 }
575
576 public class ColumnInfo
577 {
578 // public int cid { get; set; }
579
580 [Column("name")]
581 public string Name { get; set; }
582
583 // [Column ("type")]
584 // public string ColumnType { get; set; }
585
586 public int notnull { get; set; }
587
588 // public string dflt_value { get; set; }
589
590 // public int pk { get; set; }
591
592 public override string ToString()
593 {
594 return Name;
595 }
596 }
597
598 public List<ColumnInfo> GetTableInfo(string tableName)
599 {
600 var query = "pragma table_info(\"" + tableName + "\")";
601 return Query<ColumnInfo>(query);
602 }
603
604 void MigrateTable(TableMapping map)
605 {
606 var existingCols = GetTableInfo(map.TableName);
607
608 var toBeAdded = new List<TableMapping.Column>();
609
610 foreach (var p in map.Columns)
611 {
612 var found = false;
613 foreach (var c in existingCols)
614 {
615 found = (string.Compare(p.Name, c.Name, StringComparison.OrdinalIgnoreCase) == 0);
616 if (found)
617 break;
618 }
619 if (!found)
620 {
621 toBeAdded.Add(p);
622 }
623 }
624
625 foreach (var p in toBeAdded)
626 {
627 var addCol = "alter table \"" + map.TableName + "\" add column " + Orm.SqlDecl(p, StoreDateTimeAsTicks);
628 Execute(addCol);
629 }
630 }
631
632 /// <summary>
633 /// Creates a new SQLiteCommand. Can be overridden to provide a sub-class.
634 /// </summary>
635 /// <seealso cref="SQLiteCommand.OnInstanceCreated"/>
636 protected virtual SQLiteCommand NewCommand()
637 {
638 return new SQLiteCommand(this);
639 }
640
641 /// <summary>
642 /// Creates a new SQLiteCommand given the command text with arguments. Place a '?'
643 /// in the command text for each of the arguments.
644 /// </summary>
645 /// <param name="cmdText">
646 /// The fully escaped SQL.
647 /// </param>
648 /// <param name="args">
649 /// Arguments to substitute for the occurences of '?' in the command text.
650 /// </param>
651 /// <returns>
652 /// A <see cref="SQLiteCommand"/>
653 /// </returns>
654 public SQLiteCommand CreateCommand(string cmdText, params object[] ps)
655 {
656 if (!_open)
657 throw SQLiteException.New(SQLite3.Result.Error, "Cannot create commands from unopened database");
658
659 var cmd = NewCommand();
660 cmd.CommandText = cmdText;
661 foreach (var o in ps)
662 {
663 cmd.Bind(o);
664 }
665 return cmd;
666 }
667
668 /// <summary>
669 /// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
670 /// in the command text for each of the arguments and then executes that command.
671 /// Use this method instead of Query when you don't expect rows back. Such cases include
672 /// INSERTs, UPDATEs, and DELETEs.
673 /// You can set the Trace or TimeExecution properties of the connection
674 /// to profile execution.
675 /// </summary>
676 /// <param name="query">
677 /// The fully escaped SQL.
678 /// </param>
679 /// <param name="args">
680 /// Arguments to substitute for the occurences of '?' in the query.
681 /// </param>
682 /// <returns>
683 /// The number of rows modified in the database as a result of this execution.
684 /// </returns>
685 public int Execute(string query, params object[] args)
686 {
687 var cmd = CreateCommand(query, args);
688
689 if (TimeExecution)
690 {
691 if (_sw == null)
692 {
693 _sw = new Stopwatch();
694 }
695 _sw.Reset();
696 _sw.Start();
697 }
698
699 var r = cmd.ExecuteNonQuery();
700
701 if (TimeExecution)
702 {
703 _sw.Stop();
704 _elapsedMilliseconds += _sw.ElapsedMilliseconds;
705 Debug.WriteLine(string.Format("Finished in {0} ms ({1:0.0} s total)", _sw.ElapsedMilliseconds, _elapsedMilliseconds / 1000.0));
706 }
707
708 return r;
709 }
710
711 public T ExecuteScalar<T>(string query, params object[] args)
712 {
713 var cmd = CreateCommand(query, args);
714
715 if (TimeExecution)
716 {
717 if (_sw == null)
718 {
719 _sw = new Stopwatch();
720 }
721 _sw.Reset();
722 _sw.Start();
723 }
724
725 var r = cmd.ExecuteScalar<T>();
726
727 if (TimeExecution)
728 {
729 _sw.Stop();
730 _elapsedMilliseconds += _sw.ElapsedMilliseconds;
731 Debug.WriteLine(string.Format("Finished in {0} ms ({1:0.0} s total)", _sw.ElapsedMilliseconds, _elapsedMilliseconds / 1000.0));
732 }
733
734 return r;
735 }
736
737 /// <summary>
738 /// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
739 /// in the command text for each of the arguments and then executes that command.
740 /// It returns each row of the result using the mapping automatically generated for
741 /// the given type.
742 /// </summary>
743 /// <param name="query">
744 /// The fully escaped SQL.
745 /// </param>
746 /// <param name="args">
747 /// Arguments to substitute for the occurences of '?' in the query.
748 /// </param>
749 /// <returns>
750 /// An enumerable with one result for each row returned by the query.
751 /// </returns>
752 public List<T> Query<T>(string query, params object[] args) where T : new()
753 {
754 var cmd = CreateCommand(query, args);
755 return cmd.ExecuteQuery<T>();
756 }
757
758 /// <summary>
759 /// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
760 /// in the command text for each of the arguments and then executes that command.
761 /// It returns each row of the result using the mapping automatically generated for
762 /// the given type.
763 /// </summary>
764 /// <param name="query">
765 /// The fully escaped SQL.
766 /// </param>
767 /// <param name="args">
768 /// Arguments to substitute for the occurences of '?' in the query.
769 /// </param>
770 /// <returns>
771 /// An enumerable with one result for each row returned by the query.
772 /// The enumerator will call sqlite3_step on each call to MoveNext, so the database
773 /// connection must remain open for the lifetime of the enumerator.
774 /// </returns>
775 public IEnumerable<T> DeferredQuery<T>(string query, params object[] args) where T : new()
776 {
777 var cmd = CreateCommand(query, args);
778 return cmd.ExecuteDeferredQuery<T>();
779 }
780
781 /// <summary>
782 /// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
783 /// in the command text for each of the arguments and then executes that command.
784 /// It returns each row of the result using the specified mapping. This function is
785 /// only used by libraries in order to query the database via introspection. It is
786 /// normally not used.
787 /// </summary>
788 /// <param name="map">
789 /// A <see cref="TableMapping"/> to use to convert the resulting rows
790 /// into objects.
791 /// </param>
792 /// <param name="query">
793 /// The fully escaped SQL.
794 /// </param>
795 /// <param name="args">
796 /// Arguments to substitute for the occurences of '?' in the query.
797 /// </param>
798 /// <returns>
799 /// An enumerable with one result for each row returned by the query.
800 /// </returns>
801 public List<object> Query(TableMapping map, string query, params object[] args)
802 {
803 var cmd = CreateCommand(query, args);
804 return cmd.ExecuteQuery<object>(map);
805 }
806
807 /// <summary>
808 /// Creates a SQLiteCommand given the command text (SQL) with arguments. Place a '?'
809 /// in the command text for each of the arguments and then executes that command.
810 /// It returns each row of the result using the specified mapping. This function is
811 /// only used by libraries in order to query the database via introspection. It is
812 /// normally not used.
813 /// </summary>
814 /// <param name="map">
815 /// A <see cref="TableMapping"/> to use to convert the resulting rows
816 /// into objects.
817 /// </param>
818 /// <param name="query">
819 /// The fully escaped SQL.
820 /// </param>
821 /// <param name="args">
822 /// Arguments to substitute for the occurences of '?' in the query.
823 /// </param>
824 /// <returns>
825 /// An enumerable with one result for each row returned by the query.
826 /// The enumerator will call sqlite3_step on each call to MoveNext, so the database
827 /// connection must remain open for the lifetime of the enumerator.
828 /// </returns>
829 public IEnumerable<object> DeferredQuery(TableMapping map, string query, params object[] args)
830 {
831 var cmd = CreateCommand(query, args);
832 return cmd.ExecuteDeferredQuery<object>(map);
833 }
834
835 /// <summary>
836 /// Returns a queryable interface to the table represented by the given type.
837 /// </summary>
838 /// <returns>
839 /// A queryable object that is able to translate Where, OrderBy, and Take
840 /// queries into native SQL.
841 /// </returns>
842 public TableQuery<T> Table<T>() where T : new()
843 {
844 return new TableQuery<T>(this);
845 }
846
847 /// <summary>
848 /// Attempts to retrieve an object with the given primary key from the table
849 /// associated with the specified type. Use of this method requires that
850 /// the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).
851 /// </summary>
852 /// <param name="pk">
853 /// The primary key.
854 /// </param>
855 /// <returns>
856 /// The object with the given primary key. Throws a not found exception
857 /// if the object is not found.
858 /// </returns>
859 public T Get<T>(object pk) where T : new()
860 {
861 var map = GetMapping(typeof(T));
862 return Query<T>(map.GetByPrimaryKeySql, pk).First();
863 }
864
865 /// <summary>
866 /// Attempts to retrieve the first object that matches the predicate from the table
867 /// associated with the specified type.
868 /// </summary>
869 /// <param name="predicate">
870 /// A predicate for which object to find.
871 /// </param>
872 /// <returns>
873 /// The object that matches the given predicate. Throws a not found exception
874 /// if the object is not found.
875 /// </returns>
876 public T Get<T>(Expression<Func<T, bool>> predicate) where T : new()
877 {
878 return Table<T>().Where(predicate).First();
879 }
880
881 /// <summary>
882 /// Attempts to retrieve an object with the given primary key from the table
883 /// associated with the specified type. Use of this method requires that
884 /// the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).
885 /// </summary>
886 /// <param name="pk">
887 /// The primary key.
888 /// </param>
889 /// <returns>
890 /// The object with the given primary key or null
891 /// if the object is not found.
892 /// </returns>
893 public T Find<T>(object pk) where T : new()
894 {
895 var map = GetMapping(typeof(T));
896 return Query<T>(map.GetByPrimaryKeySql, pk).FirstOrDefault();
897 }
898
899 /// <summary>
900 /// Attempts to retrieve an object with the given primary key from the table
901 /// associated with the specified type. Use of this method requires that
902 /// the given type have a designated PrimaryKey (using the PrimaryKeyAttribute).
903 /// </summary>
904 /// <param name="pk">
905 /// The primary key.
906 /// </param>
907 /// <param name="map">
908 /// The TableMapping used to identify the object type.
909 /// </param>
910 /// <returns>
911 /// The object with the given primary key or null
912 /// if the object is not found.
913 /// </returns>
914 public object Find(object pk, TableMapping map)
915 {
916 return Query(map, map.GetByPrimaryKeySql, pk).FirstOrDefault();
917 }
918
919 /// <summary>
920 /// Attempts to retrieve the first object that matches the predicate from the table
921 /// associated with the specified type.
922 /// </summary>
923 /// <param name="predicate">
924 /// A predicate for which object to find.
925 /// </param>
926 /// <returns>
927 /// The object that matches the given predicate or null
928 /// if the object is not found.
929 /// </returns>
930 public T Find<T>(Expression<Func<T, bool>> predicate) where T : new()
931 {
932 return Table<T>().Where(predicate).FirstOrDefault();
933 }
934
935 /// <summary>
936 /// Whether <see cref="BeginTransaction"/> has been called and the database is waiting for a <see cref="Commit"/>.
937 /// </summary>
938 public bool IsInTransaction
939 {
940 get { return _transactionDepth > 0; }
941 }
942
943 /// <summary>
944 /// Begins a new transaction. Call <see cref="Commit"/> to end the transaction.
945 /// </summary>
946 /// <example cref="System.InvalidOperationException">Throws if a transaction has already begun.</example>
947 public void BeginTransaction()
948 {
949 // The BEGIN command only works if the transaction stack is empty,
950 // or in other words if there are no pending transactions.
951 // If the transaction stack is not empty when the BEGIN command is invoked,
952 // then the command fails with an error.
953 // Rather than crash with an error, we will just ignore calls to BeginTransaction
954 // that would result in an error.
955 if (Interlocked.CompareExchange(ref _transactionDepth, 1, 0) == 0)
956 {
957 try
958 {
959 Execute("begin transaction");
960 }
961 catch (Exception ex)
962 {
963 var sqlExp = ex as SQLiteException;
964 if (sqlExp != null)
965 {
966 // It is recommended that applications respond to the errors listed below
967 // by explicitly issuing a ROLLBACK command.
968 // TODO: This rollback failsafe should be localized to all throw sites.
969 switch (sqlExp.Result)
970 {
971 case SQLite3.Result.IOError:
972 case SQLite3.Result.Full:
973 case SQLite3.Result.Busy:
974 case SQLite3.Result.NoMem:
975 case SQLite3.Result.Interrupt:
976 RollbackTo(null, true);
977 break;
978 }
979 }
980 else
981 {
982 // Call decrement and not VolatileWrite in case we've already
983 // created a transaction point in SaveTransactionPoint since the catch.
984 Interlocked.Decrement(ref _transactionDepth);
985 }
986
987 throw;
988 }
989 }
990 else
991 {
992 // Calling BeginTransaction on an already open transaction is invalid
993 throw new InvalidOperationException("Cannot begin a transaction while already in a transaction.");
994 }
995 }
996
997 /// <summary>
998 /// Creates a savepoint in the database at the current point in the transaction timeline.
999 /// Begins a new transaction if one is not in progress.
1000 ///
1001 /// Call <see cref="RollbackTo"/> to undo transactions since the returned savepoint.
1002 /// Call <see cref="Release"/> to commit transactions after the savepoint returned here.
1003 /// Call <see cref="Commit"/> to end the transaction, committing all changes.
1004 /// </summary>
1005 /// <returns>A string naming the savepoint.</returns>
1006 public string SaveTransactionPoint()
1007 {
1008 int depth = Interlocked.Increment(ref _transactionDepth) - 1;
1009 string retVal = "S" + _rand.Next(short.MaxValue) + "D" + depth;
1010
1011 try
1012 {
1013 Execute("savepoint " + retVal);
1014 }
1015 catch (Exception ex)
1016 {
1017 var sqlExp = ex as SQLiteException;
1018 if (sqlExp != null)
1019 {
1020 // It is recommended that applications respond to the errors listed below
1021 // by explicitly issuing a ROLLBACK command.
1022 // TODO: This rollback failsafe should be localized to all throw sites.
1023 switch (sqlExp.Result)
1024 {
1025 case SQLite3.Result.IOError:
1026 case SQLite3.Result.Full:
1027 case SQLite3.Result.Busy:
1028 case SQLite3.Result.NoMem:
1029 case SQLite3.Result.Interrupt:
1030 RollbackTo(null, true);
1031 break;
1032 }
1033 }
1034 else
1035 {
1036 Interlocked.Decrement(ref _transactionDepth);
1037 }
1038
1039 throw;
1040 }
1041
1042 return retVal;
1043 }
1044
1045 /// <summary>
1046 /// Rolls back the transaction that was begun by <see cref="BeginTransaction"/> or <see cref="SaveTransactionPoint"/>.
1047 /// </summary>
1048 public void Rollback()
1049 {
1050 RollbackTo(null, false);
1051 }
1052
1053 /// <summary>
1054 /// Rolls back the savepoint created by <see cref="BeginTransaction"/> or SaveTransactionPoint.
1055 /// </summary>
1056 /// <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>
1057 public void RollbackTo(string savepoint)
1058 {
1059 RollbackTo(savepoint, false);
1060 }
1061
1062 /// <summary>
1063 /// Rolls back the transaction that was begun by <see cref="BeginTransaction"/>.
1064 /// </summary>
1065 /// <param name="noThrow">true to avoid throwing exceptions, false otherwise</param>
1066 void RollbackTo(string savepoint, bool noThrow)
1067 {
1068 // Rolling back without a TO clause rolls backs all transactions
1069 // and leaves the transaction stack empty.
1070 try
1071 {
1072 if (String.IsNullOrEmpty(savepoint))
1073 {
1074 if (Interlocked.Exchange(ref _transactionDepth, 0) > 0)
1075 {
1076 Execute("rollback");
1077 }
1078 }
1079 else
1080 {
1081 DoSavePointExecute(savepoint, "rollback to ");
1082 }
1083 }
1084 catch (SQLiteException)
1085 {
1086 if (!noThrow)
1087 throw;
1088
1089 }
1090 // No need to rollback if there are no transactions open.
1091 }
1092
1093 /// <summary>
1094 /// Releases a savepoint returned from <see cref="SaveTransactionPoint"/>. Releasing a savepoint
1095 /// makes changes since that savepoint permanent if the savepoint began the transaction,
1096 /// or otherwise the changes are permanent pending a call to <see cref="Commit"/>.
1097 ///
1098 /// The RELEASE command is like a COMMIT for a SAVEPOINT.
1099 /// </summary>
1100 /// <param name="savepoint">The name of the savepoint to release. The string should be the result of a call to <see cref="SaveTransactionPoint"/></param>
1101 public void Release(string savepoint)
1102 {
1103 DoSavePointExecute(savepoint, "release ");
1104 }
1105
1106 void DoSavePointExecute(string savepoint, string cmd)
1107 {
1108 // Validate the savepoint
1109 int firstLen = savepoint.IndexOf('D');
1110 if (firstLen >= 2 && savepoint.Length > firstLen + 1)
1111 {
1112 int depth;
1113 if (Int32.TryParse(savepoint.Substring(firstLen + 1), out depth))
1114 {
1115 // TODO: Mild race here, but inescapable without locking almost everywhere.
1116 if (0 <= depth && depth < _transactionDepth)
1117 {
1118#if NETFX_CORE
1119 Volatile.Write (ref _transactionDepth, depth);
1120#elif SILVERLIGHT
1121 _transactionDepth = depth;
1122#else
1123 Thread.VolatileWrite(ref _transactionDepth, depth);
1124#endif
1125 Execute(cmd + savepoint);
1126 return;
1127 }
1128 }
1129 }
1130
1131 throw new ArgumentException("savePoint is not valid, and should be the result of a call to SaveTransactionPoint.", "savePoint");
1132 }
1133
1134 /// <summary>
1135 /// Commits the transaction that was begun by <see cref="BeginTransaction"/>.
1136 /// </summary>
1137 public void Commit()
1138 {
1139 if (Interlocked.Exchange(ref _transactionDepth, 0) != 0)
1140 {
1141 Execute("commit");
1142 }
1143 // Do nothing on a commit with no open transaction
1144 }
1145
1146 /// <summary>
1147 /// Executes <param name="action"> within a (possibly nested) transaction by wrapping it in a SAVEPOINT. If an
1148 /// exception occurs the whole transaction is rolled back, not just the current savepoint. The exception
1149 /// is rethrown.
1150 /// </summary>
1151 /// <param name="action">
1152 /// The <see cref="Action"/> to perform within a transaction. <param name="action"> can contain any number
1153 /// of operations on the connection but should never call <see cref="BeginTransaction"/> or
1154 /// <see cref="Commit"/>.
1155 /// </param>
1156 public void RunInTransaction(Action action)
1157 {
1158 try
1159 {
1160 lock (syncObjects[DatabasePath])
1161 {
1162 var savePoint = SaveTransactionPoint();
1163 action();
1164 Release(savePoint);
1165 }
1166 }
1167 catch (Exception)
1168 {
1169 Rollback();
1170 throw;
1171 }
1172 }
1173
1174 /// <summary>
1175 /// Executes <param name="action"> while blocking other threads to access the same database.
1176 /// </summary>
1177 /// <param name="action">
1178 /// The <see cref="Action"/> to perform within a lock.
1179 /// </param>
1180 public void RunInDatabaseLock(Action action)
1181 {
1182 lock (syncObjects[DatabasePath])
1183 {
1184 action();
1185 }
1186 }
1187
1188 /// <summary>
1189 /// Inserts all specified objects.
1190 /// </summary>
1191 /// <param name="objects">
1192 /// An <see cref="IEnumerable"/> of the objects to insert.
1193 /// </param>
1194 /// <returns>
1195 /// The number of rows added to the table.
1196 /// </returns>
1197 public int InsertAll(System.Collections.IEnumerable objects)
1198 {
1199 var c = 0;
1200 RunInTransaction(() => {
1201 foreach (var r in objects)
1202 {
1203 c += Insert(r);
1204 }
1205 });
1206 return c;
1207 }
1208
1209 /// <summary>
1210 /// Inserts all specified objects.
1211 /// </summary>
1212 /// <param name="objects">
1213 /// An <see cref="IEnumerable"/> of the objects to insert.
1214 /// </param>
1215 /// <param name="extra">
1216 /// Literal SQL code that gets placed into the command. INSERT {extra} INTO ...
1217 /// </param>
1218 /// <returns>
1219 /// The number of rows added to the table.
1220 /// </returns>
1221 public int InsertAll(System.Collections.IEnumerable objects, string extra)
1222 {
1223 var c = 0;
1224 RunInTransaction(() => {
1225 foreach (var r in objects)
1226 {
1227 c += Insert(r, extra);
1228 }
1229 });
1230 return c;
1231 }
1232
1233 /// <summary>
1234 /// Inserts all specified objects.
1235 /// </summary>
1236 /// <param name="objects">
1237 /// An <see cref="IEnumerable"/> of the objects to insert.
1238 /// </param>
1239 /// <param name="objType">
1240 /// The type of object to insert.
1241 /// </param>
1242 /// <returns>
1243 /// The number of rows added to the table.
1244 /// </returns>
1245 public int InsertAll(System.Collections.IEnumerable objects, Type objType)
1246 {
1247 var c = 0;
1248 RunInTransaction(() => {
1249 foreach (var r in objects)
1250 {
1251 c += Insert(r, objType);
1252 }
1253 });
1254 return c;
1255 }
1256
1257 /// <summary>
1258 /// Inserts the given object and retrieves its
1259 /// auto incremented primary key if it has one.
1260 /// </summary>
1261 /// <param name="obj">
1262 /// The object to insert.
1263 /// </param>
1264 /// <returns>
1265 /// The number of rows added to the table.
1266 /// </returns>
1267 public int Insert(object obj)
1268 {
1269 if (obj == null)
1270 {
1271 return 0;
1272 }
1273 return Insert(obj, "", obj.GetType());
1274 }
1275
1276 /// <summary>
1277 /// Inserts the given object and retrieves its
1278 /// auto incremented primary key if it has one.
1279 /// If a UNIQUE constraint violation occurs with
1280 /// some pre-existing object, this function deletes
1281 /// the old object.
1282 /// </summary>
1283 /// <param name="obj">
1284 /// The object to insert.
1285 /// </param>
1286 /// <returns>
1287 /// The number of rows modified.
1288 /// </returns>
1289 public int InsertOrReplace(object obj)
1290 {
1291 if (obj == null)
1292 {
1293 return 0;
1294 }
1295 return Insert(obj, "OR REPLACE", obj.GetType());
1296 }
1297
1298 /// <summary>
1299 /// Inserts the given object and retrieves its
1300 /// auto incremented primary key if it has one.
1301 /// </summary>
1302 /// <param name="obj">
1303 /// The object to insert.
1304 /// </param>
1305 /// <param name="objType">
1306 /// The type of object to insert.
1307 /// </param>
1308 /// <returns>
1309 /// The number of rows added to the table.
1310 /// </returns>
1311 public int Insert(object obj, Type objType)
1312 {
1313 return Insert(obj, "", objType);
1314 }
1315
1316 /// <summary>
1317 /// Inserts the given object and retrieves its
1318 /// auto incremented primary key if it has one.
1319 /// If a UNIQUE constraint violation occurs with
1320 /// some pre-existing object, this function deletes
1321 /// the old object.
1322 /// </summary>
1323 /// <param name="obj">
1324 /// The object to insert.
1325 /// </param>
1326 /// <param name="objType">
1327 /// The type of object to insert.
1328 /// </param>
1329 /// <returns>
1330 /// The number of rows modified.
1331 /// </returns>
1332 public int InsertOrReplace(object obj, Type objType)
1333 {
1334 return Insert(obj, "OR REPLACE", objType);
1335 }
1336
1337 /// <summary>
1338 /// Inserts the given object and retrieves its
1339 /// auto incremented primary key if it has one.
1340 /// </summary>
1341 /// <param name="obj">
1342 /// The object to insert.
1343 /// </param>
1344 /// <param name="extra">
1345 /// Literal SQL code that gets placed into the command. INSERT {extra} INTO ...
1346 /// </param>
1347 /// <returns>
1348 /// The number of rows added to the table.
1349 /// </returns>
1350 public int Insert(object obj, string extra)
1351 {
1352 if (obj == null)
1353 {
1354 return 0;
1355 }
1356 return Insert(obj, extra, obj.GetType());
1357 }
1358
1359 /// <summary>
1360 /// Inserts the given object and retrieves its
1361 /// auto incremented primary key if it has one.
1362 /// </summary>
1363 /// <param name="obj">
1364 /// The object to insert.
1365 /// </param>
1366 /// <param name="extra">
1367 /// Literal SQL code that gets placed into the command. INSERT {extra} INTO ...
1368 /// </param>
1369 /// <param name="objType">
1370 /// The type of object to insert.
1371 /// </param>
1372 /// <returns>
1373 /// The number of rows added to the table.
1374 /// </returns>
1375 public int Insert(object obj, string extra, Type objType)
1376 {
1377 if (obj == null || objType == null)
1378 {
1379 return 0;
1380 }
1381
1382
1383 var map = GetMapping(objType);
1384
1385#if NETFX_CORE
1386 if (map.PK != null && map.PK.IsAutoGuid)
1387 {
1388 // no GetProperty so search our way up the inheritance chain till we find it
1389 PropertyInfo prop;
1390 while (objType != null)
1391 {
1392 var info = objType.GetTypeInfo();
1393 prop = info.GetDeclaredProperty(map.PK.PropertyName);
1394 if (prop != null)
1395 {
1396 if (prop.GetValue(obj, null).Equals(Guid.Empty))
1397 {
1398 prop.SetValue(obj, Guid.NewGuid(), null);
1399 }
1400 break;
1401 }
1402
1403 objType = info.BaseType;
1404 }
1405 }
1406#else
1407 if (map.PK != null && map.PK.IsAutoGuid)
1408 {
1409 var prop = objType.GetProperty(map.PK.PropertyName);
1410 if (prop != null)
1411 {
1412 //if (prop.GetValue(obj, null).Equals(Guid.Empty)) {
1413 if (prop.GetGetMethod().Invoke(obj, null).Equals(Guid.Empty))
1414 {
1415 prop.SetValue(obj, Guid.NewGuid(), null);
1416 }
1417 }
1418 }
1419#endif
1420
1421
1422 var replacing = string.Compare(extra, "OR REPLACE", StringComparison.OrdinalIgnoreCase) == 0;
1423
1424 var cols = replacing ? map.InsertOrReplaceColumns : map.InsertColumns;
1425 var vals = new object[cols.Length];
1426 for (var i = 0; i < vals.Length; i++)
1427 {
1428 vals[i] = cols[i].GetValue(obj);
1429 }
1430
1431 var insertCmd = map.GetInsertCommand(this, extra);
1432 int count;
1433
1434 try
1435 {
1436 count = insertCmd.ExecuteNonQuery(vals);
1437 }
1438 catch (SQLiteException ex)
1439 {
1440
1441 if (SQLite3.ExtendedErrCode(this.Handle) == SQLite3.ExtendedResult.ConstraintNotNull)
1442 {
1443 throw NotNullConstraintViolationException.New(ex.Result, ex.Message, map, obj);
1444 }
1445 throw;
1446 }
1447
1448 if (map.HasAutoIncPK)
1449 {
1450 var id = SQLite3.LastInsertRowid(Handle);
1451 map.SetAutoIncPK(obj, id);
1452 }
1453
1454 return count;
1455 }
1456
1457 /// <summary>
1458 /// Updates all of the columns of a table using the specified object
1459 /// except for its primary key.
1460 /// The object is required to have a primary key.
1461 /// </summary>
1462 /// <param name="obj">
1463 /// The object to update. It must have a primary key designated using the PrimaryKeyAttribute.
1464 /// </param>
1465 /// <returns>
1466 /// The number of rows updated.
1467 /// </returns>
1468 public int Update(object obj)
1469 {
1470 if (obj == null)
1471 {
1472 return 0;
1473 }
1474 return Update(obj, obj.GetType());
1475 }
1476
1477 /// <summary>
1478 /// Updates all of the columns of a table using the specified object
1479 /// except for its primary key.
1480 /// The object is required to have a primary key.
1481 /// </summary>
1482 /// <param name="obj">
1483 /// The object to update. It must have a primary key designated using the PrimaryKeyAttribute.
1484 /// </param>
1485 /// <param name="objType">
1486 /// The type of object to insert.
1487 /// </param>
1488 /// <returns>
1489 /// The number of rows updated.
1490 /// </returns>
1491 public int Update(object obj, Type objType)
1492 {
1493 int rowsAffected = 0;
1494 if (obj == null || objType == null)
1495 {
1496 return 0;
1497 }
1498
1499 var map = GetMapping(objType);
1500
1501 var pk = map.PK;
1502
1503 if (pk == null)
1504 {
1505 throw new NotSupportedException("Cannot update " + map.TableName + ": it has no PK");
1506 }
1507
1508 var cols = from p in map.Columns
1509 where p != pk
1510 select p;
1511 var vals = from c in cols
1512 select c.GetValue(obj);
1513 var ps = new List<object>(vals);
1514 ps.Add(pk.GetValue(obj));
1515 var q = string.Format("update \"{0}\" set {1} where {2} = ? ", map.TableName, string.Join(",", (from c in cols
1516 select "\"" + c.Name + "\" = ? ").ToArray()), pk.Name);
1517
1518 try
1519 {
1520 rowsAffected = Execute(q, ps.ToArray());
1521 }
1522 catch (SQLiteException ex)
1523 {
1524
1525 if (ex.Result == SQLite3.Result.Constraint && SQLite3.ExtendedErrCode(this.Handle) == SQLite3.ExtendedResult.ConstraintNotNull)
1526 {
1527 throw NotNullConstraintViolationException.New(ex, map, obj);
1528 }
1529
1530 throw ex;
1531 }
1532
1533 return rowsAffected;
1534 }
1535
1536 /// <summary>
1537 /// Updates all specified objects.
1538 /// </summary>
1539 /// <param name="objects">
1540 /// An <see cref="IEnumerable"/> of the objects to insert.
1541 /// </param>
1542 /// <returns>
1543 /// The number of rows modified.
1544 /// </returns>
1545 public int UpdateAll(System.Collections.IEnumerable objects)
1546 {
1547 var c = 0;
1548 RunInTransaction(() => {
1549 foreach (var r in objects)
1550 {
1551 c += Update(r);
1552 }
1553 });
1554 return c;
1555 }
1556
1557 /// <summary>
1558 /// Deletes the given object from the database using its primary key.
1559 /// </summary>
1560 /// <param name="objectToDelete">
1561 /// The object to delete. It must have a primary key designated using the PrimaryKeyAttribute.
1562 /// </param>
1563 /// <returns>
1564 /// The number of rows deleted.
1565 /// </returns>
1566 public int Delete(object objectToDelete)
1567 {
1568 var map = GetMapping(objectToDelete.GetType());
1569 var pk = map.PK;
1570 if (pk == null)
1571 {
1572 throw new NotSupportedException("Cannot delete " + map.TableName + ": it has no PK");
1573 }
1574 var q = string.Format("delete from \"{0}\" where \"{1}\" = ?", map.TableName, pk.Name);
1575 return Execute(q, pk.GetValue(objectToDelete));
1576 }
1577
1578 /// <summary>
1579 /// Deletes the object with the specified primary key.
1580 /// </summary>
1581 /// <param name="primaryKey">
1582 /// The primary key of the object to delete.
1583 /// </param>
1584 /// <returns>
1585 /// The number of objects deleted.
1586 /// </returns>
1587 /// <typeparam name='T'>
1588 /// The type of object.
1589 /// </typeparam>
1590 public int Delete<T>(object primaryKey)
1591 {
1592 var map = GetMapping(typeof(T));
1593 var pk = map.PK;
1594 if (pk == null)
1595 {
1596 throw new NotSupportedException("Cannot delete " + map.TableName + ": it has no PK");
1597 }
1598 var q = string.Format("delete from \"{0}\" where \"{1}\" = ?", map.TableName, pk.Name);
1599 return Execute(q, primaryKey);
1600 }
1601
1602 /// <summary>
1603 /// Deletes all the objects from the specified table.
1604 /// WARNING WARNING: Let me repeat. It deletes ALL the objects from the
1605 /// specified table. Do you really want to do that?
1606 /// </summary>
1607 /// <returns>
1608 /// The number of objects deleted.
1609 /// </returns>
1610 /// <typeparam name='T'>
1611 /// The type of objects to delete.
1612 /// </typeparam>
1613 public int DeleteAll<T>()
1614 {
1615 var map = GetMapping(typeof(T));
1616 var query = string.Format("delete from \"{0}\"", map.TableName);
1617 return Execute(query);
1618 }
1619
1620 ~SQLiteConnection()
1621 {
1622 Dispose(false);
1623 }
1624
1625 public void Dispose()
1626 {
1627 Dispose(true);
1628 GC.SuppressFinalize(this);
1629 }
1630
1631 protected virtual void Dispose(bool disposing)
1632 {
1633 Close();
1634 }
1635
1636 public void Close()
1637 {
1638 if (_open && Handle != NullHandle)
1639 {
1640 try
1641 {
1642 if (_mappings != null)
1643 {
1644 foreach (var sqlInsertCommand in _mappings.Values)
1645 {
1646 sqlInsertCommand.Dispose();
1647 }
1648 }
1649 var r = SQLite3.Close(Handle);
1650 if (r != SQLite3.Result.OK)
1651 {
1652 string msg = SQLite3.GetErrmsg(Handle);
1653 throw SQLiteException.New(r, msg);
1654 }
1655 }
1656 finally
1657 {
1658 Handle = NullHandle;
1659 _open = false;
1660 }
1661 }
1662 }
1663 }
1664
1665 /// <summary>
1666 /// Represents a parsed connection string.
1667 /// </summary>
1668 class SQLiteConnectionString
1669 {
1670 public string ConnectionString { get; private set; }
1671 public string DatabasePath { get; private set; }
1672 public bool StoreDateTimeAsTicks { get; private set; }
1673
1674#if NETFX_CORE
1675 static readonly string MetroStyleDataPath = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
1676#endif
1677
1678 public SQLiteConnectionString(string databasePath, bool storeDateTimeAsTicks)
1679 {
1680 ConnectionString = databasePath;
1681 StoreDateTimeAsTicks = storeDateTimeAsTicks;
1682
1683#if NETFX_CORE
1684 DatabasePath = System.IO.Path.Combine (MetroStyleDataPath, databasePath);
1685#else
1686 DatabasePath = databasePath;
1687#endif
1688 }
1689 }
1690
1691 [AttributeUsage(AttributeTargets.Class)]
1692 public class TableAttribute : Attribute
1693 {
1694 public string Name { get; set; }
1695
1696 public TableAttribute(string name)
1697 {
1698 Name = name;
1699 }
1700 }
1701
1702 [AttributeUsage(AttributeTargets.Property)]
1703 public class ColumnAttribute : Attribute
1704 {
1705 public string Name { get; set; }
1706
1707 public ColumnAttribute(string name)
1708 {
1709 Name = name;
1710 }
1711 }
1712
1713 [AttributeUsage(AttributeTargets.Property)]
1714 public class PrimaryKeyAttribute : Attribute
1715 {
1716 }
1717
1718 [AttributeUsage(AttributeTargets.Property)]
1719 public class AutoIncrementAttribute : Attribute
1720 {
1721 }
1722
1723 [AttributeUsage(AttributeTargets.Property)]
1724 public class IndexedAttribute : Attribute
1725 {
1726 public string Name { get; set; }
1727 public int Order { get; set; }
1728 public virtual bool Unique { get; set; }
1729
1730 public IndexedAttribute()
1731 {
1732 }
1733
1734 public IndexedAttribute(string name, int order)
1735 {
1736 Name = name;
1737 Order = order;
1738 }
1739 }
1740
1741 [AttributeUsage(AttributeTargets.Property)]
1742 public class IgnoreAttribute : Attribute
1743 {
1744 }
1745
1746 [AttributeUsage(AttributeTargets.Property)]
1747 public class UniqueAttribute : IndexedAttribute
1748 {
1749 public override bool Unique
1750 {
1751 get { return true; }
1752 set { /* throw? */ }
1753 }
1754 }
1755
1756 [AttributeUsage(AttributeTargets.Property)]
1757 public class MaxLengthAttribute : Attribute
1758 {
1759 public int Value { get; private set; }
1760
1761 public MaxLengthAttribute(int length)
1762 {
1763 Value = length;
1764 }
1765 }
1766
1767 [AttributeUsage(AttributeTargets.Property)]
1768 public class CollationAttribute : Attribute
1769 {
1770 public string Value { get; private set; }
1771
1772 public CollationAttribute(string collation)
1773 {
1774 Value = collation;
1775 }
1776 }
1777
1778 [AttributeUsage(AttributeTargets.Property)]
1779 public class NotNullAttribute : Attribute
1780 {
1781 }
1782
1783 public class TableMapping
1784 {
1785 public Type MappedType { get; private set; }
1786
1787 public string TableName { get; private set; }
1788
1789 public Column[] Columns { get; private set; }
1790
1791 public Column PK { get; private set; }
1792
1793 public string GetByPrimaryKeySql { get; private set; }
1794
1795 Column _autoPk;
1796 Column[] _insertColumns;
1797 Column[] _insertOrReplaceColumns;
1798
1799 public TableMapping(Type type, CreateFlags createFlags = CreateFlags.None)
1800 {
1801 MappedType = type;
1802
1803#if NETFX_CORE
1804 var tableAttr = (TableAttribute)System.Reflection.CustomAttributeExtensions
1805 .GetCustomAttribute(type.GetTypeInfo(), typeof(TableAttribute), true);
1806#else
1807 var tableAttr = (TableAttribute)type.GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault();
1808#endif
1809
1810 TableName = tableAttr != null ? tableAttr.Name : MappedType.Name;
1811
1812#if !NETFX_CORE
1813 var props = MappedType.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty);
1814#else
1815 var props = from p in MappedType.GetRuntimeProperties()
1816 where ((p.GetMethod != null && p.GetMethod.IsPublic) || (p.SetMethod != null && p.SetMethod.IsPublic) || (p.GetMethod != null && p.GetMethod.IsStatic) || (p.SetMethod != null && p.SetMethod.IsStatic))
1817 select p;
1818#endif
1819 var cols = new List<Column>();
1820 foreach (var p in props)
1821 {
1822#if !NETFX_CORE
1823 var ignore = p.GetCustomAttributes(typeof(IgnoreAttribute), true).Length > 0;
1824#else
1825 var ignore = p.GetCustomAttributes (typeof(IgnoreAttribute), true).Count() > 0;
1826#endif
1827 if (p.CanWrite && !ignore)
1828 {
1829 cols.Add(new Column(p, createFlags));
1830 }
1831 }
1832 Columns = cols.ToArray();
1833 foreach (var c in Columns)
1834 {
1835 if (c.IsAutoInc && c.IsPK)
1836 {
1837 _autoPk = c;
1838 }
1839 if (c.IsPK)
1840 {
1841 PK = c;
1842 }
1843 }
1844
1845 HasAutoIncPK = _autoPk != null;
1846
1847 if (PK != null)
1848 {
1849 GetByPrimaryKeySql = string.Format("select * from \"{0}\" where \"{1}\" = ?", TableName, PK.Name);
1850 }
1851 else
1852 {
1853 // People should not be calling Get/Find without a PK
1854 GetByPrimaryKeySql = string.Format("select * from \"{0}\" limit 1", TableName);
1855 }
1856 }
1857
1858 public bool HasAutoIncPK { get; private set; }
1859
1860 public void SetAutoIncPK(object obj, long id)
1861 {
1862 if (_autoPk != null)
1863 {
1864 _autoPk.SetValue(obj, Convert.ChangeType(id, _autoPk.ColumnType, null));
1865 }
1866 }
1867
1868 public Column[] InsertColumns
1869 {
1870 get
1871 {
1872 if (_insertColumns == null)
1873 {
1874 _insertColumns = Columns.Where(c => !c.IsAutoInc).ToArray();
1875 }
1876 return _insertColumns;
1877 }
1878 }
1879
1880 public Column[] InsertOrReplaceColumns
1881 {
1882 get
1883 {
1884 if (_insertOrReplaceColumns == null)
1885 {
1886 _insertOrReplaceColumns = Columns.ToArray();
1887 }
1888 return _insertOrReplaceColumns;
1889 }
1890 }
1891
1892 public Column FindColumnWithPropertyName(string propertyName)
1893 {
1894 var exact = Columns.FirstOrDefault(c => c.PropertyName == propertyName);
1895 return exact;
1896 }
1897
1898 public Column FindColumn(string columnName)
1899 {
1900 var exact = Columns.FirstOrDefault(c => c.Name == columnName);
1901 return exact;
1902 }
1903
1904 PreparedSqlLiteInsertCommand _insertCommand;
1905 string _insertCommandExtra;
1906
1907 public PreparedSqlLiteInsertCommand GetInsertCommand(SQLiteConnection conn, string extra)
1908 {
1909 if (_insertCommand == null)
1910 {
1911 _insertCommand = CreateInsertCommand(conn, extra);
1912 _insertCommandExtra = extra;
1913 }
1914 else if (_insertCommandExtra != extra)
1915 {
1916 _insertCommand.Dispose();
1917 _insertCommand = CreateInsertCommand(conn, extra);
1918 _insertCommandExtra = extra;
1919 }
1920 return _insertCommand;
1921 }
1922
1923 PreparedSqlLiteInsertCommand CreateInsertCommand(SQLiteConnection conn, string extra)
1924 {
1925 var cols = InsertColumns;
1926 string insertSql;
1927 if (!cols.Any() && Columns.Count() == 1 && Columns[0].IsAutoInc)
1928 {
1929 insertSql = string.Format("insert {1} into \"{0}\" default values", TableName, extra);
1930 }
1931 else
1932 {
1933 var replacing = string.Compare(extra, "OR REPLACE", StringComparison.OrdinalIgnoreCase) == 0;
1934
1935 if (replacing)
1936 {
1937 cols = InsertOrReplaceColumns;
1938 }
1939
1940 insertSql = string.Format("insert {3} into \"{0}\"({1}) values ({2})", TableName,
1941 string.Join(",", (from c in cols
1942 select "\"" + c.Name + "\"").ToArray()),
1943 string.Join(",", (from c in cols
1944 select "?").ToArray()), extra);
1945
1946 }
1947
1948 var insertCommand = new PreparedSqlLiteInsertCommand(conn);
1949 insertCommand.CommandText = insertSql;
1950 return insertCommand;
1951 }
1952
1953 protected internal void Dispose()
1954 {
1955 if (_insertCommand != null)
1956 {
1957 _insertCommand.Dispose();
1958 _insertCommand = null;
1959 }
1960 }
1961
1962 public class Column
1963 {
1964 PropertyInfo _prop;
1965
1966 public string Name { get; private set; }
1967
1968 public string PropertyName { get { return _prop.Name; } }
1969
1970 public Type ColumnType { get; private set; }
1971
1972 public string Collation { get; private set; }
1973
1974 public bool IsAutoInc { get; private set; }
1975 public bool IsAutoGuid { get; private set; }
1976
1977 public bool IsPK { get; private set; }
1978
1979 public IEnumerable<IndexedAttribute> Indices { get; set; }
1980
1981 public bool IsNullable { get; private set; }
1982
1983 public int? MaxStringLength { get; private set; }
1984
1985 public Column(PropertyInfo prop, CreateFlags createFlags = CreateFlags.None)
1986 {
1987 var colAttr = (ColumnAttribute)prop.GetCustomAttributes(typeof(ColumnAttribute), true).FirstOrDefault();
1988
1989 _prop = prop;
1990 Name = colAttr == null ? prop.Name : colAttr.Name;
1991 //If this type is Nullable<T> then Nullable.GetUnderlyingType returns the T, otherwise it returns null, so get the actual type instead
1992 ColumnType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
1993 Collation = Orm.Collation(prop);
1994
1995 IsPK = Orm.IsPK(prop) ||
1996 (((createFlags & CreateFlags.ImplicitPK) == CreateFlags.ImplicitPK) &&
1997 string.Compare(prop.Name, Orm.ImplicitPkName, StringComparison.OrdinalIgnoreCase) == 0);
1998
1999 var isAuto = Orm.IsAutoInc(prop) || (IsPK && ((createFlags & CreateFlags.AutoIncPK) == CreateFlags.AutoIncPK));
2000 IsAutoGuid = isAuto && ColumnType == typeof(Guid);
2001 IsAutoInc = isAuto && !IsAutoGuid;
2002
2003 Indices = Orm.GetIndices(prop);
2004 if (!Indices.Any()
2005 && !IsPK
2006 && ((createFlags & CreateFlags.ImplicitIndex) == CreateFlags.ImplicitIndex)
2007 && Name.EndsWith(Orm.ImplicitIndexSuffix, StringComparison.OrdinalIgnoreCase)
2008 )
2009 {
2010 Indices = new IndexedAttribute[] { new IndexedAttribute() };
2011 }
2012 IsNullable = !(IsPK || Orm.IsMarkedNotNull(prop));
2013 MaxStringLength = Orm.MaxStringLength(prop);
2014 }
2015
2016 public void SetValue(object obj, object val)
2017 {
2018 _prop.SetValue(obj, val, null);
2019 }
2020
2021 public object GetValue(object obj)
2022 {
2023 return _prop.GetGetMethod().Invoke(obj, null);
2024 }
2025 }
2026 }
2027
2028 public static class Orm
2029 {
2030 public const int DefaultMaxStringLength = 140;
2031 public const string ImplicitPkName = "Id";
2032 public const string ImplicitIndexSuffix = "Id";
2033
2034 public static string SqlDecl(TableMapping.Column p, bool storeDateTimeAsTicks)
2035 {
2036 string decl = "\"" + p.Name + "\" " + SqlType(p, storeDateTimeAsTicks) + " ";
2037
2038 if (p.IsPK)
2039 {
2040 decl += "primary key ";
2041 }
2042 if (p.IsAutoInc)
2043 {
2044 decl += "autoincrement ";
2045 }
2046 if (!p.IsNullable)
2047 {
2048 decl += "not null ";
2049 }
2050 if (!string.IsNullOrEmpty(p.Collation))
2051 {
2052 decl += "collate " + p.Collation + " ";
2053 }
2054
2055 return decl;
2056 }
2057
2058 public static string SqlType(TableMapping.Column p, bool storeDateTimeAsTicks)
2059 {
2060 var clrType = p.ColumnType;
2061 if (clrType == typeof(Boolean) || clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32))
2062 {
2063 return "integer";
2064 }
2065 else if (clrType == typeof(UInt32) || clrType == typeof(Int64))
2066 {
2067 return "bigint";
2068 }
2069 else if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal))
2070 {
2071 return "float";
2072 }
2073 else if (clrType == typeof(String))
2074 {
2075 int? len = p.MaxStringLength;
2076
2077 if (len.HasValue)
2078 return "varchar(" + len.Value + ")";
2079
2080 return "varchar";
2081 }
2082 else if (clrType == typeof(TimeSpan))
2083 {
2084 return "bigint";
2085 }
2086 else if (clrType == typeof(DateTime))
2087 {
2088 return storeDateTimeAsTicks ? "bigint" : "datetime";
2089 }
2090 else if (clrType == typeof(DateTimeOffset))
2091 {
2092 return "bigint";
2093#if !NETFX_CORE
2094 }
2095 else if (clrType.IsEnum)
2096 {
2097#else
2098 } else if (clrType.GetTypeInfo().IsEnum) {
2099#endif
2100 return "integer";
2101 }
2102 else if (clrType == typeof(byte[]))
2103 {
2104 return "blob";
2105 }
2106 else if (clrType == typeof(Guid))
2107 {
2108 return "varchar(36)";
2109 }
2110 else
2111 {
2112 throw new NotSupportedException("Don't know about " + clrType);
2113 }
2114 }
2115
2116 public static bool IsPK(MemberInfo p)
2117 {
2118 var attrs = p.GetCustomAttributes(typeof(PrimaryKeyAttribute), true);
2119#if !NETFX_CORE
2120 return attrs.Length > 0;
2121#else
2122 return attrs.Count() > 0;
2123#endif
2124 }
2125
2126 public static string Collation(MemberInfo p)
2127 {
2128 var attrs = p.GetCustomAttributes(typeof(CollationAttribute), true);
2129#if !NETFX_CORE
2130 if (attrs.Length > 0)
2131 {
2132 return ((CollationAttribute)attrs[0]).Value;
2133#else
2134 if (attrs.Count() > 0) {
2135 return ((CollationAttribute)attrs.First()).Value;
2136#endif
2137 }
2138 else
2139 {
2140 return string.Empty;
2141 }
2142 }
2143
2144 public static bool IsAutoInc(MemberInfo p)
2145 {
2146 var attrs = p.GetCustomAttributes(typeof(AutoIncrementAttribute), true);
2147#if !NETFX_CORE
2148 return attrs.Length > 0;
2149#else
2150 return attrs.Count() > 0;
2151#endif
2152 }
2153
2154 public static IEnumerable<IndexedAttribute> GetIndices(MemberInfo p)
2155 {
2156 var attrs = p.GetCustomAttributes(typeof(IndexedAttribute), true);
2157 return attrs.Cast<IndexedAttribute>();
2158 }
2159
2160 public static int? MaxStringLength(PropertyInfo p)
2161 {
2162 var attrs = p.GetCustomAttributes(typeof(MaxLengthAttribute), true);
2163#if !NETFX_CORE
2164 if (attrs.Length > 0)
2165 return ((MaxLengthAttribute)attrs[0]).Value;
2166#else
2167 if (attrs.Count() > 0)
2168 return ((MaxLengthAttribute)attrs.First()).Value;
2169#endif
2170
2171 return null;
2172 }
2173
2174 public static bool IsMarkedNotNull(MemberInfo p)
2175 {
2176 var attrs = p.GetCustomAttributes(typeof(NotNullAttribute), true);
2177#if !NETFX_CORE
2178 return attrs.Length > 0;
2179#else
2180 return attrs.Count() > 0;
2181#endif
2182 }
2183 }
2184
2185 public partial class SQLiteCommand
2186 {
2187 SQLiteConnection _conn;
2188 private List<Binding> _bindings;
2189
2190 public string CommandText { get; set; }
2191
2192 internal SQLiteCommand(SQLiteConnection conn)
2193 {
2194 _conn = conn;
2195 _bindings = new List<Binding>();
2196 CommandText = "";
2197 }
2198
2199 public int ExecuteNonQuery()
2200 {
2201 if (_conn.Trace)
2202 {
2203 _conn.InvokeTrace("Executing: " + this);
2204 }
2205
2206 var r = SQLite3.Result.OK;
2207 lock (_conn.SyncObject)
2208 {
2209 var stmt = Prepare();
2210 r = SQLite3.Step(stmt);
2211 Finalize(stmt);
2212 }
2213
2214 if (r == SQLite3.Result.Done)
2215 {
2216 int rowsAffected = SQLite3.Changes(_conn.Handle);
2217 return rowsAffected;
2218 }
2219 else if (r == SQLite3.Result.Error)
2220 {
2221 string msg = SQLite3.GetErrmsg(_conn.Handle);
2222 throw SQLiteException.New(r, msg);
2223 }
2224 else if (r == SQLite3.Result.Constraint)
2225 {
2226 if (SQLite3.ExtendedErrCode(_conn.Handle) == SQLite3.ExtendedResult.ConstraintNotNull)
2227 {
2228 throw NotNullConstraintViolationException.New(r, SQLite3.GetErrmsg(_conn.Handle));
2229 }
2230 }
2231
2232 throw SQLiteException.New(r, r.ToString());
2233 }
2234
2235 public IEnumerable<T> ExecuteDeferredQuery<T>()
2236 {
2237 return ExecuteDeferredQuery<T>(_conn.GetMapping(typeof(T)));
2238 }
2239
2240 public List<T> ExecuteQuery<T>()
2241 {
2242 return ExecuteDeferredQuery<T>(_conn.GetMapping(typeof(T))).ToList();
2243 }
2244
2245 public List<T> ExecuteQuery<T>(TableMapping map)
2246 {
2247 return ExecuteDeferredQuery<T>(map).ToList();
2248 }
2249
2250 /// <summary>
2251 /// Invoked every time an instance is loaded from the database.
2252 /// </summary>
2253 /// <param name='obj'>
2254 /// The newly created object.
2255 /// </param>
2256 /// <remarks>
2257 /// This can be overridden in combination with the <see cref="SQLiteConnection.NewCommand"/>
2258 /// method to hook into the life-cycle of objects.
2259 ///
2260 /// Type safety is not possible because MonoTouch does not support virtual generic methods.
2261 /// </remarks>
2262 protected virtual void OnInstanceCreated(object obj)
2263 {
2264 // Can be overridden.
2265 }
2266
2267 public IEnumerable<T> ExecuteDeferredQuery<T>(TableMapping map)
2268 {
2269 if (_conn.Trace)
2270 {
2271 _conn.InvokeTrace("Executing Query: " + this);
2272 }
2273
2274 lock (_conn.SyncObject)
2275 {
2276 var stmt = Prepare();
2277 try
2278 {
2279 var cols = new TableMapping.Column[SQLite3.ColumnCount(stmt)];
2280
2281 for (int i = 0; i < cols.Length; i++)
2282 {
2283 var name = SQLite3.ColumnName16(stmt, i);
2284 cols[i] = map.FindColumn(name);
2285 }
2286
2287 while (SQLite3.Step(stmt) == SQLite3.Result.Row)
2288 {
2289 var obj = Activator.CreateInstance(map.MappedType);
2290 for (int i = 0; i < cols.Length; i++)
2291 {
2292 if (cols[i] == null)
2293 continue;
2294 var colType = SQLite3.ColumnType(stmt, i);
2295 var val = ReadCol(stmt, i, colType, cols[i].ColumnType);
2296 cols[i].SetValue(obj, val);
2297 }
2298 OnInstanceCreated(obj);
2299 yield return (T)obj;
2300 }
2301 }
2302 finally
2303 {
2304 SQLite3.Finalize(stmt);
2305 }
2306 }
2307 }
2308
2309 public T ExecuteScalar<T>()
2310 {
2311 if (_conn.Trace)
2312 {
2313 _conn.InvokeTrace("Executing Query: " + this);
2314 }
2315
2316 T val = default(T);
2317
2318 lock (_conn.SyncObject)
2319 {
2320 var stmt = Prepare();
2321
2322 try
2323 {
2324 var r = SQLite3.Step(stmt);
2325 if (r == SQLite3.Result.Row)
2326 {
2327 var colType = SQLite3.ColumnType(stmt, 0);
2328 val = (T)ReadCol(stmt, 0, colType, typeof(T));
2329 }
2330 else if (r == SQLite3.Result.Done)
2331 {
2332 }
2333 else
2334 {
2335 throw SQLiteException.New(r, SQLite3.GetErrmsg(_conn.Handle));
2336 }
2337 }
2338 finally
2339 {
2340 Finalize(stmt);
2341 }
2342 }
2343
2344 return val;
2345 }
2346
2347 public void Bind(string name, object val)
2348 {
2349 _bindings.Add(new Binding
2350 {
2351 Name = name,
2352 Value = val
2353 });
2354 }
2355
2356 public void Bind(object val)
2357 {
2358 Bind(null, val);
2359 }
2360
2361 public override string ToString()
2362 {
2363 var parts = new string[1 + _bindings.Count];
2364 parts[0] = CommandText;
2365 var i = 1;
2366 foreach (var b in _bindings)
2367 {
2368 parts[i] = string.Format(" {0}: {1}", i - 1, b.Value);
2369 i++;
2370 }
2371 return string.Join(Environment.NewLine, parts);
2372 }
2373
2374 Sqlite3Statement Prepare()
2375 {
2376 var stmt = SQLite3.Prepare2(_conn.Handle, CommandText);
2377 BindAll(stmt);
2378 return stmt;
2379 }
2380
2381 void Finalize(Sqlite3Statement stmt)
2382 {
2383 SQLite3.Finalize(stmt);
2384 }
2385
2386 void BindAll(Sqlite3Statement stmt)
2387 {
2388 int nextIdx = 1;
2389 foreach (var b in _bindings)
2390 {
2391 if (b.Name != null)
2392 {
2393 b.Index = SQLite3.BindParameterIndex(stmt, b.Name);
2394 }
2395 else
2396 {
2397 b.Index = nextIdx++;
2398 }
2399
2400 BindParameter(stmt, b.Index, b.Value, _conn.StoreDateTimeAsTicks);
2401 }
2402 }
2403
2404 internal static IntPtr NegativePointer = new IntPtr(-1);
2405
2406 internal static void BindParameter(Sqlite3Statement stmt, int index, object value, bool storeDateTimeAsTicks)
2407 {
2408 if (value == null)
2409 {
2410 SQLite3.BindNull(stmt, index);
2411 }
2412 else
2413 {
2414 if (value is Int32)
2415 {
2416 SQLite3.BindInt(stmt, index, (int)value);
2417 }
2418 else if (value is String)
2419 {
2420 SQLite3.BindText(stmt, index, (string)value, -1, NegativePointer);
2421 }
2422 else if (value is Byte || value is UInt16 || value is SByte || value is Int16)
2423 {
2424 SQLite3.BindInt(stmt, index, Convert.ToInt32(value));
2425 }
2426 else if (value is Boolean)
2427 {
2428 SQLite3.BindInt(stmt, index, (bool)value ? 1 : 0);
2429 }
2430 else if (value is UInt32 || value is Int64)
2431 {
2432 SQLite3.BindInt64(stmt, index, Convert.ToInt64(value));
2433 }
2434 else if (value is Single || value is Double || value is Decimal)
2435 {
2436 SQLite3.BindDouble(stmt, index, Convert.ToDouble(value));
2437 }
2438 else if (value is TimeSpan)
2439 {
2440 SQLite3.BindInt64(stmt, index, ((TimeSpan)value).Ticks);
2441 }
2442 else if (value is DateTime)
2443 {
2444 if (storeDateTimeAsTicks)
2445 {
2446 SQLite3.BindInt64(stmt, index, ((DateTime)value).Ticks);
2447 }
2448 else
2449 {
2450 SQLite3.BindText(stmt, index, ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"), -1, NegativePointer);
2451 }
2452 }
2453 else if (value is DateTimeOffset)
2454 {
2455 SQLite3.BindInt64(stmt, index, ((DateTimeOffset)value).UtcTicks);
2456#if !NETFX_CORE
2457 }
2458 else if (value.GetType().IsEnum)
2459 {
2460#else
2461 } else if (value.GetType().GetTypeInfo().IsEnum) {
2462#endif
2463 SQLite3.BindInt(stmt, index, Convert.ToInt32(value));
2464 }
2465 else if (value is byte[])
2466 {
2467 SQLite3.BindBlob(stmt, index, (byte[])value, ((byte[])value).Length, NegativePointer);
2468 }
2469 else if (value is Guid)
2470 {
2471 SQLite3.BindText(stmt, index, ((Guid)value).ToString(), 72, NegativePointer);
2472 }
2473 else
2474 {
2475 throw new NotSupportedException("Cannot store type: " + value.GetType());
2476 }
2477 }
2478 }
2479
2480 class Binding
2481 {
2482 public string Name { get; set; }
2483
2484 public object Value { get; set; }
2485
2486 public int Index { get; set; }
2487 }
2488
2489 object ReadCol(Sqlite3Statement stmt, int index, SQLite3.ColType type, Type clrType)
2490 {
2491 if (type == SQLite3.ColType.Null)
2492 {
2493 return null;
2494 }
2495 else
2496 {
2497 if (clrType == typeof(String))
2498 {
2499 return SQLite3.ColumnString(stmt, index);
2500 }
2501 else if (clrType == typeof(Int32))
2502 {
2503 return (int)SQLite3.ColumnInt(stmt, index);
2504 }
2505 else if (clrType == typeof(Boolean))
2506 {
2507 return SQLite3.ColumnInt(stmt, index) == 1;
2508 }
2509 else if (clrType == typeof(double))
2510 {
2511 return SQLite3.ColumnDouble(stmt, index);
2512 }
2513 else if (clrType == typeof(float))
2514 {
2515 return (float)SQLite3.ColumnDouble(stmt, index);
2516 }
2517 else if (clrType == typeof(TimeSpan))
2518 {
2519 return new TimeSpan(SQLite3.ColumnInt64(stmt, index));
2520 }
2521 else if (clrType == typeof(DateTime))
2522 {
2523 if (_conn.StoreDateTimeAsTicks)
2524 {
2525 return new DateTime(SQLite3.ColumnInt64(stmt, index));
2526 }
2527 else
2528 {
2529 var text = SQLite3.ColumnString(stmt, index);
2530 return DateTime.Parse(text);
2531 }
2532 }
2533 else if (clrType == typeof(DateTimeOffset))
2534 {
2535 return new DateTimeOffset(SQLite3.ColumnInt64(stmt, index), TimeSpan.Zero);
2536#if !NETFX_CORE
2537 }
2538 else if (clrType.IsEnum)
2539 {
2540#else
2541 } else if (clrType.GetTypeInfo().IsEnum) {
2542#endif
2543 return SQLite3.ColumnInt(stmt, index);
2544 }
2545 else if (clrType == typeof(Int64))
2546 {
2547 return SQLite3.ColumnInt64(stmt, index);
2548 }
2549 else if (clrType == typeof(UInt32))
2550 {
2551 return (uint)SQLite3.ColumnInt64(stmt, index);
2552 }
2553 else if (clrType == typeof(decimal))
2554 {
2555 return (decimal)SQLite3.ColumnDouble(stmt, index);
2556 }
2557 else if (clrType == typeof(Byte))
2558 {
2559 return (byte)SQLite3.ColumnInt(stmt, index);
2560 }
2561 else if (clrType == typeof(UInt16))
2562 {
2563 return (ushort)SQLite3.ColumnInt(stmt, index);
2564 }
2565 else if (clrType == typeof(Int16))
2566 {
2567 return (short)SQLite3.ColumnInt(stmt, index);
2568 }
2569 else if (clrType == typeof(sbyte))
2570 {
2571 return (sbyte)SQLite3.ColumnInt(stmt, index);
2572 }
2573 else if (clrType == typeof(byte[]))
2574 {
2575 return SQLite3.ColumnByteArray(stmt, index);
2576 }
2577 else if (clrType == typeof(Guid))
2578 {
2579 var text = SQLite3.ColumnString(stmt, index);
2580 return new Guid(text);
2581 }
2582 else
2583 {
2584 throw new NotSupportedException("Don't know how to read " + clrType);
2585 }
2586 }
2587 }
2588 }
2589
2590 /// <summary>
2591 /// Since the insert never changed, we only need to prepare once.
2592 /// </summary>
2593 public class PreparedSqlLiteInsertCommand : IDisposable
2594 {
2595 public bool Initialized { get; set; }
2596
2597 protected SQLiteConnection Connection { get; set; }
2598
2599 public string CommandText { get; set; }
2600
2601 protected Sqlite3Statement Statement { get; set; }
2602 internal static readonly Sqlite3Statement NullStatement = default(Sqlite3Statement);
2603
2604 internal PreparedSqlLiteInsertCommand(SQLiteConnection conn)
2605 {
2606 Connection = conn;
2607 }
2608
2609 public int ExecuteNonQuery(object[] source)
2610 {
2611 if (Connection.Trace)
2612 {
2613 Connection.InvokeTrace("Executing: " + CommandText);
2614 }
2615
2616 var r = SQLite3.Result.OK;
2617
2618 if (!Initialized)
2619 {
2620 Statement = Prepare();
2621 Initialized = true;
2622 }
2623
2624 //bind the values.
2625 if (source != null)
2626 {
2627 for (int i = 0; i < source.Length; i++)
2628 {
2629 SQLiteCommand.BindParameter(Statement, i + 1, source[i], Connection.StoreDateTimeAsTicks);
2630 }
2631 }
2632 r = SQLite3.Step(Statement);
2633
2634 if (r == SQLite3.Result.Done)
2635 {
2636 int rowsAffected = SQLite3.Changes(Connection.Handle);
2637 SQLite3.Reset(Statement);
2638 return rowsAffected;
2639 }
2640 else if (r == SQLite3.Result.Error)
2641 {
2642 string msg = SQLite3.GetErrmsg(Connection.Handle);
2643 SQLite3.Reset(Statement);
2644 throw SQLiteException.New(r, msg);
2645 }
2646 else if (r == SQLite3.Result.Constraint && SQLite3.ExtendedErrCode(Connection.Handle) == SQLite3.ExtendedResult.ConstraintNotNull)
2647 {
2648 SQLite3.Reset(Statement);
2649 throw NotNullConstraintViolationException.New(r, SQLite3.GetErrmsg(Connection.Handle));
2650 }
2651 else
2652 {
2653 SQLite3.Reset(Statement);
2654 throw SQLiteException.New(r, r.ToString());
2655 }
2656 }
2657
2658 protected virtual Sqlite3Statement Prepare()
2659 {
2660 var stmt = SQLite3.Prepare2(Connection.Handle, CommandText);
2661 return stmt;
2662 }
2663
2664 public void Dispose()
2665 {
2666 Dispose(true);
2667 GC.SuppressFinalize(this);
2668 }
2669
2670 private void Dispose(bool disposing)
2671 {
2672 if (Statement != NullStatement)
2673 {
2674 try
2675 {
2676 SQLite3.Finalize(Statement);
2677 }
2678 finally
2679 {
2680 Statement = NullStatement;
2681 Connection = null;
2682 }
2683 }
2684 }
2685
2686 ~PreparedSqlLiteInsertCommand()
2687 {
2688 Dispose(false);
2689 }
2690 }
2691
2692 public abstract class BaseTableQuery
2693 {
2694 protected class Ordering
2695 {
2696 public string ColumnName { get; set; }
2697 public bool Ascending { get; set; }
2698 }
2699 }
2700
2701 public class TableQuery<T> : BaseTableQuery, IEnumerable<T>
2702 {
2703 public SQLiteConnection Connection { get; private set; }
2704
2705 public TableMapping Table { get; private set; }
2706
2707 Expression _where;
2708 List<Ordering> _orderBys;
2709 int? _limit;
2710 int? _offset;
2711
2712 BaseTableQuery _joinInner;
2713 Expression _joinInnerKeySelector;
2714 BaseTableQuery _joinOuter;
2715 Expression _joinOuterKeySelector;
2716 Expression _joinSelector;
2717
2718 Expression _selector;
2719
2720 TableQuery(SQLiteConnection conn, TableMapping table)
2721 {
2722 Connection = conn;
2723 Table = table;
2724 }
2725
2726 public TableQuery(SQLiteConnection conn)
2727 {
2728 Connection = conn;
2729 Table = Connection.GetMapping(typeof(T));
2730 }
2731
2732 public TableQuery<U> Clone<U>()
2733 {
2734 var q = new TableQuery<U>(Connection, Table);
2735 q._where = _where;
2736 q._deferred = _deferred;
2737 if (_orderBys != null)
2738 {
2739 q._orderBys = new List<Ordering>(_orderBys);
2740 }
2741 q._limit = _limit;
2742 q._offset = _offset;
2743 q._joinInner = _joinInner;
2744 q._joinInnerKeySelector = _joinInnerKeySelector;
2745 q._joinOuter = _joinOuter;
2746 q._joinOuterKeySelector = _joinOuterKeySelector;
2747 q._joinSelector = _joinSelector;
2748 q._selector = _selector;
2749 return q;
2750 }
2751
2752 public TableQuery<T> Where(Expression<Func<T, bool>> predExpr)
2753 {
2754 if (predExpr.NodeType == ExpressionType.Lambda)
2755 {
2756 var lambda = (LambdaExpression)predExpr;
2757 var pred = lambda.Body;
2758 var q = Clone<T>();
2759 q.AddWhere(pred);
2760 return q;
2761 }
2762 else
2763 {
2764 throw new NotSupportedException("Must be a predicate");
2765 }
2766 }
2767
2768 public TableQuery<T> Take(int n)
2769 {
2770 var q = Clone<T>();
2771 q._limit = n;
2772 return q;
2773 }
2774
2775 public TableQuery<T> Skip(int n)
2776 {
2777 var q = Clone<T>();
2778 q._offset = n;
2779 return q;
2780 }
2781
2782 public T ElementAt(int index)
2783 {
2784 return Skip(index).Take(1).First();
2785 }
2786
2787 bool _deferred;
2788 public TableQuery<T> Deferred()
2789 {
2790 var q = Clone<T>();
2791 q._deferred = true;
2792 return q;
2793 }
2794
2795 public TableQuery<T> OrderBy<U>(Expression<Func<T, U>> orderExpr)
2796 {
2797 return AddOrderBy<U>(orderExpr, true);
2798 }
2799
2800 public TableQuery<T> OrderByDescending<U>(Expression<Func<T, U>> orderExpr)
2801 {
2802 return AddOrderBy<U>(orderExpr, false);
2803 }
2804
2805 public TableQuery<T> ThenBy<U>(Expression<Func<T, U>> orderExpr)
2806 {
2807 return AddOrderBy<U>(orderExpr, true);
2808 }
2809
2810 public TableQuery<T> ThenByDescending<U>(Expression<Func<T, U>> orderExpr)
2811 {
2812 return AddOrderBy<U>(orderExpr, false);
2813 }
2814
2815 private TableQuery<T> AddOrderBy<U>(Expression<Func<T, U>> orderExpr, bool asc)
2816 {
2817 if (orderExpr.NodeType == ExpressionType.Lambda)
2818 {
2819 var lambda = (LambdaExpression)orderExpr;
2820
2821 MemberExpression mem = null;
2822
2823 var unary = lambda.Body as UnaryExpression;
2824 if (unary != null && unary.NodeType == ExpressionType.Convert)
2825 {
2826 mem = unary.Operand as MemberExpression;
2827 }
2828 else
2829 {
2830 mem = lambda.Body as MemberExpression;
2831 }
2832
2833 if (mem != null && (mem.Expression.NodeType == ExpressionType.Parameter))
2834 {
2835 var q = Clone<T>();
2836 if (q._orderBys == null)
2837 {
2838 q._orderBys = new List<Ordering>();
2839 }
2840 q._orderBys.Add(new Ordering
2841 {
2842 ColumnName = Table.FindColumnWithPropertyName(mem.Member.Name).Name,
2843 Ascending = asc
2844 });
2845 return q;
2846 }
2847 else
2848 {
2849 throw new NotSupportedException("Order By does not support: " + orderExpr);
2850 }
2851 }
2852 else
2853 {
2854 throw new NotSupportedException("Must be a predicate");
2855 }
2856 }
2857
2858 private void AddWhere(Expression pred)
2859 {
2860 if (_where == null)
2861 {
2862 _where = pred;
2863 }
2864 else
2865 {
2866 _where = Expression.AndAlso(_where, pred);
2867 }
2868 }
2869
2870 public TableQuery<TResult> Join<TInner, TKey, TResult>(
2871 TableQuery<TInner> inner,
2872 Expression<Func<T, TKey>> outerKeySelector,
2873 Expression<Func<TInner, TKey>> innerKeySelector,
2874 Expression<Func<T, TInner, TResult>> resultSelector)
2875 {
2876 var q = new TableQuery<TResult>(Connection, Connection.GetMapping(typeof(TResult)))
2877 {
2878 _joinOuter = this,
2879 _joinOuterKeySelector = outerKeySelector,
2880 _joinInner = inner,
2881 _joinInnerKeySelector = innerKeySelector,
2882 _joinSelector = resultSelector,
2883 };
2884 return q;
2885 }
2886
2887 public TableQuery<TResult> Select<TResult>(Expression<Func<T, TResult>> selector)
2888 {
2889 var q = Clone<TResult>();
2890 q._selector = selector;
2891 return q;
2892 }
2893
2894 private SQLiteCommand GenerateCommand(string selectionList)
2895 {
2896 if (_joinInner != null && _joinOuter != null)
2897 {
2898 throw new NotSupportedException("Joins are not supported.");
2899 }
2900 else
2901 {
2902 var cmdText = "select " + selectionList + " from \"" + Table.TableName + "\"";
2903 var args = new List<object>();
2904 if (_where != null)
2905 {
2906 var w = CompileExpr(_where, args);
2907 cmdText += " where " + w.CommandText;
2908 }
2909 if ((_orderBys != null) && (_orderBys.Count > 0))
2910 {
2911 var t = string.Join(", ", _orderBys.Select(o => "\"" + o.ColumnName + "\"" + (o.Ascending ? "" : " desc")).ToArray());
2912 cmdText += " order by " + t;
2913 }
2914 if (_limit.HasValue)
2915 {
2916 cmdText += " limit " + _limit.Value;
2917 }
2918 if (_offset.HasValue)
2919 {
2920 if (!_limit.HasValue)
2921 {
2922 cmdText += " limit -1 ";
2923 }
2924 cmdText += " offset " + _offset.Value;
2925 }
2926 return Connection.CreateCommand(cmdText, args.ToArray());
2927 }
2928 }
2929
2930 class CompileResult
2931 {
2932 public string CommandText { get; set; }
2933
2934 public object Value { get; set; }
2935 }
2936
2937 private CompileResult CompileExpr(Expression expr, List<object> queryArgs)
2938 {
2939 if (expr == null)
2940 {
2941 throw new NotSupportedException("Expression is NULL");
2942 }
2943 else if (expr is BinaryExpression)
2944 {
2945 var bin = (BinaryExpression)expr;
2946
2947 var leftr = CompileExpr(bin.Left, queryArgs);
2948 var rightr = CompileExpr(bin.Right, queryArgs);
2949
2950 //If either side is a parameter and is null, then handle the other side specially (for "is null"/"is not null")
2951 string text;
2952 if (leftr.CommandText == "?" && leftr.Value == null)
2953 text = CompileNullBinaryExpression(bin, rightr);
2954 else if (rightr.CommandText == "?" && rightr.Value == null)
2955 text = CompileNullBinaryExpression(bin, leftr);
2956 else
2957 text = "(" + leftr.CommandText + " " + GetSqlName(bin) + " " + rightr.CommandText + ")";
2958 return new CompileResult { CommandText = text };
2959 }
2960 else if (expr.NodeType == ExpressionType.Call)
2961 {
2962
2963 var call = (MethodCallExpression)expr;
2964 var args = new CompileResult[call.Arguments.Count];
2965 var obj = call.Object != null ? CompileExpr(call.Object, queryArgs) : null;
2966
2967 for (var i = 0; i < args.Length; i++)
2968 {
2969 args[i] = CompileExpr(call.Arguments[i], queryArgs);
2970 }
2971
2972 var sqlCall = "";
2973
2974 if (call.Method.Name == "Like" && args.Length == 2)
2975 {
2976 sqlCall = "(" + args[0].CommandText + " like " + args[1].CommandText + ")";
2977 }
2978 else if (call.Method.Name == "Contains" && args.Length == 2)
2979 {
2980 sqlCall = "(" + args[1].CommandText + " in " + args[0].CommandText + ")";
2981 }
2982 else if (call.Method.Name == "Contains" && args.Length == 1)
2983 {
2984 if (call.Object != null && call.Object.Type == typeof(string))
2985 {
2986 sqlCall = "(" + obj.CommandText + " like ('%' || " + args[0].CommandText + " || '%'))";
2987 }
2988 else
2989 {
2990 sqlCall = "(" + args[0].CommandText + " in " + obj.CommandText + ")";
2991 }
2992 }
2993 else if (call.Method.Name == "StartsWith" && args.Length == 1)
2994 {
2995 sqlCall = "(" + obj.CommandText + " like (" + args[0].CommandText + " || '%'))";
2996 }
2997 else if (call.Method.Name == "EndsWith" && args.Length == 1)
2998 {
2999 sqlCall = "(" + obj.CommandText + " like ('%' || " + args[0].CommandText + "))";
3000 }
3001 else if (call.Method.Name == "Equals" && args.Length == 1)
3002 {
3003 sqlCall = "(" + obj.CommandText + " = (" + args[0].CommandText + "))";
3004 }
3005 else if (call.Method.Name == "ToLower")
3006 {
3007 sqlCall = "(lower(" + obj.CommandText + "))";
3008 }
3009 else if (call.Method.Name == "ToUpper")
3010 {
3011 sqlCall = "(upper(" + obj.CommandText + "))";
3012 }
3013 else
3014 {
3015 sqlCall = call.Method.Name.ToLower() + "(" + string.Join(",", args.Select(a => a.CommandText).ToArray()) + ")";
3016 }
3017 return new CompileResult { CommandText = sqlCall };
3018
3019 }
3020 else if (expr.NodeType == ExpressionType.Constant)
3021 {
3022 var c = (ConstantExpression)expr;
3023 queryArgs.Add(c.Value);
3024 return new CompileResult
3025 {
3026 CommandText = "?",
3027 Value = c.Value
3028 };
3029 }
3030 else if (expr.NodeType == ExpressionType.Convert)
3031 {
3032 var u = (UnaryExpression)expr;
3033 var ty = u.Type;
3034 var valr = CompileExpr(u.Operand, queryArgs);
3035 return new CompileResult
3036 {
3037 CommandText = valr.CommandText,
3038 Value = valr.Value != null ? ConvertTo(valr.Value, ty) : null
3039 };
3040 }
3041 else if (expr.NodeType == ExpressionType.MemberAccess)
3042 {
3043 var mem = (MemberExpression)expr;
3044
3045 if (mem.Expression != null && mem.Expression.NodeType == ExpressionType.Parameter)
3046 {
3047 //
3048 // This is a column of our table, output just the column name
3049 // Need to translate it if that column name is mapped
3050 //
3051 var columnName = Table.FindColumnWithPropertyName(mem.Member.Name).Name;
3052 return new CompileResult { CommandText = "\"" + columnName + "\"" };
3053 }
3054 else
3055 {
3056 object obj = null;
3057 if (mem.Expression != null)
3058 {
3059 var r = CompileExpr(mem.Expression, queryArgs);
3060 if (r.Value == null)
3061 {
3062 throw new NotSupportedException("Member access failed to compile expression");
3063 }
3064 if (r.CommandText == "?")
3065 {
3066 queryArgs.RemoveAt(queryArgs.Count - 1);
3067 }
3068 obj = r.Value;
3069 }
3070
3071 //
3072 // Get the member value
3073 //
3074 object val = null;
3075
3076#if !NETFX_CORE
3077 if (mem.Member.MemberType == MemberTypes.Property)
3078 {
3079#else
3080 if (mem.Member is PropertyInfo) {
3081#endif
3082 var m = (PropertyInfo)mem.Member;
3083 //val = m.GetValue (obj, null);
3084 val = m.GetGetMethod().Invoke(obj, null);
3085#if !NETFX_CORE
3086 }
3087 else if (mem.Member.MemberType == MemberTypes.Field)
3088 {
3089#else
3090 } else if (mem.Member is FieldInfo) {
3091#endif
3092#if SILVERLIGHT
3093 val = Expression.Lambda (expr).Compile ().DynamicInvoke ();
3094#else
3095 var m = (FieldInfo)mem.Member;
3096 val = m.GetValue(obj);
3097#endif
3098 }
3099 else
3100 {
3101#if !NETFX_CORE
3102 throw new NotSupportedException("MemberExpr: " + mem.Member.MemberType);
3103#else
3104 throw new NotSupportedException ("MemberExpr: " + mem.Member.DeclaringType);
3105#endif
3106 }
3107
3108 //
3109 // Work special magic for enumerables
3110 //
3111 if (val != null && val is System.Collections.IEnumerable && !(val is string) && !(val is System.Collections.Generic.IEnumerable<byte>))
3112 {
3113 var sb = new System.Text.StringBuilder();
3114 sb.Append("(");
3115 var head = "";
3116 foreach (var a in (System.Collections.IEnumerable)val)
3117 {
3118 queryArgs.Add(a);
3119 sb.Append(head);
3120 sb.Append("?");
3121 head = ",";
3122 }
3123 sb.Append(")");
3124 return new CompileResult
3125 {
3126 CommandText = sb.ToString(),
3127 Value = val
3128 };
3129 }
3130 else
3131 {
3132 queryArgs.Add(val);
3133 return new CompileResult
3134 {
3135 CommandText = "?",
3136 Value = val
3137 };
3138 }
3139 }
3140 }
3141 throw new NotSupportedException("Cannot compile: " + expr.NodeType.ToString());
3142 }
3143
3144 static object ConvertTo(object obj, Type t)
3145 {
3146 Type nut = Nullable.GetUnderlyingType(t);
3147
3148 if (nut != null)
3149 {
3150 if (obj == null) return null;
3151 return Convert.ChangeType(obj, nut);
3152 }
3153 else
3154 {
3155 return Convert.ChangeType(obj, t);
3156 }
3157 }
3158
3159 /// <summary>
3160 /// Compiles a BinaryExpression where one of the parameters is null.
3161 /// </summary>
3162 /// <param name="parameter">The non-null parameter</param>
3163 private string CompileNullBinaryExpression(BinaryExpression expression, CompileResult parameter)
3164 {
3165 if (expression.NodeType == ExpressionType.Equal)
3166 return "(" + parameter.CommandText + " is ?)";
3167 else if (expression.NodeType == ExpressionType.NotEqual)
3168 return "(" + parameter.CommandText + " is not ?)";
3169 else
3170 throw new NotSupportedException("Cannot compile Null-BinaryExpression with type " + expression.NodeType.ToString());
3171 }
3172
3173 string GetSqlName(Expression expr)
3174 {
3175 var n = expr.NodeType;
3176 if (n == ExpressionType.GreaterThan)
3177 return ">";
3178 else if (n == ExpressionType.GreaterThanOrEqual)
3179 {
3180 return ">=";
3181 }
3182 else if (n == ExpressionType.LessThan)
3183 {
3184 return "<";
3185 }
3186 else if (n == ExpressionType.LessThanOrEqual)
3187 {
3188 return "<=";
3189 }
3190 else if (n == ExpressionType.And)
3191 {
3192 return "&";
3193 }
3194 else if (n == ExpressionType.AndAlso)
3195 {
3196 return "and";
3197 }
3198 else if (n == ExpressionType.Or)
3199 {
3200 return "|";
3201 }
3202 else if (n == ExpressionType.OrElse)
3203 {
3204 return "or";
3205 }
3206 else if (n == ExpressionType.Equal)
3207 {
3208 return "=";
3209 }
3210 else if (n == ExpressionType.NotEqual)
3211 {
3212 return "!=";
3213 }
3214 else
3215 {
3216 throw new NotSupportedException("Cannot get SQL for: " + n);
3217 }
3218 }
3219
3220 public int Count()
3221 {
3222 return GenerateCommand("count(*)").ExecuteScalar<int>();
3223 }
3224
3225 public int Count(Expression<Func<T, bool>> predExpr)
3226 {
3227 return Where(predExpr).Count();
3228 }
3229
3230 public IEnumerator<T> GetEnumerator()
3231 {
3232 if (!_deferred)
3233 return GenerateCommand("*").ExecuteQuery<T>().GetEnumerator();
3234
3235 return GenerateCommand("*").ExecuteDeferredQuery<T>().GetEnumerator();
3236 }
3237
3238 System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
3239 {
3240 return GetEnumerator();
3241 }
3242
3243 public T First()
3244 {
3245 var query = Take(1);
3246 return query.ToList<T>().First();
3247 }
3248
3249 public T FirstOrDefault()
3250 {
3251 var query = Take(1);
3252 return query.ToList<T>().FirstOrDefault();
3253 }
3254 }
3255
3256 public static class SQLite3
3257 {
3258 public enum Result : int
3259 {
3260 OK = 0,
3261 Error = 1,
3262 Internal = 2,
3263 Perm = 3,
3264 Abort = 4,
3265 Busy = 5,
3266 Locked = 6,
3267 NoMem = 7,
3268 ReadOnly = 8,
3269 Interrupt = 9,
3270 IOError = 10,
3271 Corrupt = 11,
3272 NotFound = 12,
3273 Full = 13,
3274 CannotOpen = 14,
3275 LockErr = 15,
3276 Empty = 16,
3277 SchemaChngd = 17,
3278 TooBig = 18,
3279 Constraint = 19,
3280 Mismatch = 20,
3281 Misuse = 21,
3282 NotImplementedLFS = 22,
3283 AccessDenied = 23,
3284 Format = 24,
3285 Range = 25,
3286 NonDBFile = 26,
3287 Notice = 27,
3288 Warning = 28,
3289 Row = 100,
3290 Done = 101
3291 }
3292
3293 public enum ExtendedResult : int
3294 {
3295 IOErrorRead = (Result.IOError | (1 << 8)),
3296 IOErrorShortRead = (Result.IOError | (2 << 8)),
3297 IOErrorWrite = (Result.IOError | (3 << 8)),
3298 IOErrorFsync = (Result.IOError | (4 << 8)),
3299 IOErrorDirFSync = (Result.IOError | (5 << 8)),
3300 IOErrorTruncate = (Result.IOError | (6 << 8)),
3301 IOErrorFStat = (Result.IOError | (7 << 8)),
3302 IOErrorUnlock = (Result.IOError | (8 << 8)),
3303 IOErrorRdlock = (Result.IOError | (9 << 8)),
3304 IOErrorDelete = (Result.IOError | (10 << 8)),
3305 IOErrorBlocked = (Result.IOError | (11 << 8)),
3306 IOErrorNoMem = (Result.IOError | (12 << 8)),
3307 IOErrorAccess = (Result.IOError | (13 << 8)),
3308 IOErrorCheckReservedLock = (Result.IOError | (14 << 8)),
3309 IOErrorLock = (Result.IOError | (15 << 8)),
3310 IOErrorClose = (Result.IOError | (16 << 8)),
3311 IOErrorDirClose = (Result.IOError | (17 << 8)),
3312 IOErrorSHMOpen = (Result.IOError | (18 << 8)),
3313 IOErrorSHMSize = (Result.IOError | (19 << 8)),
3314 IOErrorSHMLock = (Result.IOError | (20 << 8)),
3315 IOErrorSHMMap = (Result.IOError | (21 << 8)),
3316 IOErrorSeek = (Result.IOError | (22 << 8)),
3317 IOErrorDeleteNoEnt = (Result.IOError | (23 << 8)),
3318 IOErrorMMap = (Result.IOError | (24 << 8)),
3319 LockedSharedcache = (Result.Locked | (1 << 8)),
3320 BusyRecovery = (Result.Busy | (1 << 8)),
3321 CannottOpenNoTempDir = (Result.CannotOpen | (1 << 8)),
3322 CannotOpenIsDir = (Result.CannotOpen | (2 << 8)),
3323 CannotOpenFullPath = (Result.CannotOpen | (3 << 8)),
3324 CorruptVTab = (Result.Corrupt | (1 << 8)),
3325 ReadonlyRecovery = (Result.ReadOnly | (1 << 8)),
3326 ReadonlyCannotLock = (Result.ReadOnly | (2 << 8)),
3327 ReadonlyRollback = (Result.ReadOnly | (3 << 8)),
3328 AbortRollback = (Result.Abort | (2 << 8)),
3329 ConstraintCheck = (Result.Constraint | (1 << 8)),
3330 ConstraintCommitHook = (Result.Constraint | (2 << 8)),
3331 ConstraintForeignKey = (Result.Constraint | (3 << 8)),
3332 ConstraintFunction = (Result.Constraint | (4 << 8)),
3333 ConstraintNotNull = (Result.Constraint | (5 << 8)),
3334 ConstraintPrimaryKey = (Result.Constraint | (6 << 8)),
3335 ConstraintTrigger = (Result.Constraint | (7 << 8)),
3336 ConstraintUnique = (Result.Constraint | (8 << 8)),
3337 ConstraintVTab = (Result.Constraint | (9 << 8)),
3338 NoticeRecoverWAL = (Result.Notice | (1 << 8)),
3339 NoticeRecoverRollback = (Result.Notice | (2 << 8))
3340 }
3341
3342
3343 public enum ConfigOption : int
3344 {
3345 SingleThread = 1,
3346 MultiThread = 2,
3347 Serialized = 3
3348 }
3349
3350#if !USE_CSHARP_SQLITE && !USE_WP8_NATIVE_SQLITE
3351 [DllImport("sqlite3", EntryPoint = "sqlite3_open", CallingConvention = CallingConvention.Cdecl)]
3352 public static extern Result Open([MarshalAs(UnmanagedType.LPStr)] string filename, out IntPtr db);
3353
3354 [DllImport("sqlite3", EntryPoint = "sqlite3_open_v2", CallingConvention = CallingConvention.Cdecl)]
3355 public static extern Result Open([MarshalAs(UnmanagedType.LPStr)] string filename, out IntPtr db, int flags, IntPtr zvfs);
3356
3357 [DllImport("sqlite3", EntryPoint = "sqlite3_open_v2", CallingConvention = CallingConvention.Cdecl)]
3358 public static extern Result Open(byte[] filename, out IntPtr db, int flags, IntPtr zvfs);
3359
3360 [DllImport("sqlite3", EntryPoint = "sqlite3_open16", CallingConvention = CallingConvention.Cdecl)]
3361 public static extern Result Open16([MarshalAs(UnmanagedType.LPWStr)] string filename, out IntPtr db);
3362
3363 [DllImport("sqlite3", EntryPoint = "sqlite3_enable_load_extension", CallingConvention = CallingConvention.Cdecl)]
3364 public static extern Result EnableLoadExtension(IntPtr db, int onoff);
3365
3366 [DllImport("sqlite3", EntryPoint = "sqlite3_close", CallingConvention = CallingConvention.Cdecl)]
3367 public static extern Result Close(IntPtr db);
3368
3369 [DllImport("sqlite3", EntryPoint = "sqlite3_initialize", CallingConvention = CallingConvention.Cdecl)]
3370 public static extern Result Initialize();
3371
3372 [DllImport("sqlite3", EntryPoint = "sqlite3_shutdown", CallingConvention = CallingConvention.Cdecl)]
3373 public static extern Result Shutdown();
3374
3375 [DllImport("sqlite3", EntryPoint = "sqlite3_config", CallingConvention = CallingConvention.Cdecl)]
3376 public static extern Result Config(ConfigOption option);
3377
3378 [DllImport("sqlite3", EntryPoint = "sqlite3_win32_set_directory", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Unicode)]
3379 public static extern int SetDirectory(uint directoryType, string directoryPath);
3380
3381 [DllImport("sqlite3", EntryPoint = "sqlite3_busy_timeout", CallingConvention = CallingConvention.Cdecl)]
3382 public static extern Result BusyTimeout(IntPtr db, int milliseconds);
3383
3384 [DllImport("sqlite3", EntryPoint = "sqlite3_changes", CallingConvention = CallingConvention.Cdecl)]
3385 public static extern int Changes(IntPtr db);
3386
3387 [DllImport("sqlite3", EntryPoint = "sqlite3_prepare_v2", CallingConvention = CallingConvention.Cdecl)]
3388 public static extern Result Prepare2(IntPtr db, [MarshalAs(UnmanagedType.LPStr)] string sql, int numBytes, out IntPtr stmt, IntPtr pzTail);
3389
3390#if NETFX_CORE
3391 [DllImport ("sqlite3", EntryPoint = "sqlite3_prepare_v2", CallingConvention = CallingConvention.Cdecl)]
3392 public static extern Result Prepare2 (IntPtr db, byte[] queryBytes, int numBytes, out IntPtr stmt, IntPtr pzTail);
3393#endif
3394
3395 public static IntPtr Prepare2(IntPtr db, string query)
3396 {
3397 IntPtr stmt;
3398#if NETFX_CORE
3399 byte[] queryBytes = System.Text.UTF8Encoding.UTF8.GetBytes (query);
3400 var r = Prepare2 (db, queryBytes, queryBytes.Length, out stmt, IntPtr.Zero);
3401#else
3402 var r = Prepare2(db, query, System.Text.UTF8Encoding.UTF8.GetByteCount(query), out stmt, IntPtr.Zero);
3403#endif
3404 if (r != Result.OK)
3405 {
3406 throw SQLiteException.New(r,GetErrmsg(db));
3407 }
3408 return stmt;
3409 }
3410
3411 [DllImport("sqlite3", EntryPoint = "sqlite3_step", CallingConvention = CallingConvention.Cdecl)]
3412 public static extern Result Step(IntPtr stmt);
3413
3414 [DllImport("sqlite3", EntryPoint = "sqlite3_reset", CallingConvention = CallingConvention.Cdecl)]
3415 public static extern Result Reset(IntPtr stmt);
3416
3417 [DllImport("sqlite3", EntryPoint = "sqlite3_finalize", CallingConvention = CallingConvention.Cdecl)]
3418 public static extern Result Finalize(IntPtr stmt);
3419
3420 [DllImport("sqlite3", EntryPoint = "sqlite3_last_insert_rowid", CallingConvention = CallingConvention.Cdecl)]
3421 public static extern long LastInsertRowid(IntPtr db);
3422
3423 [DllImport("sqlite3", EntryPoint = "sqlite3_errmsg16", CallingConvention = CallingConvention.Cdecl)]
3424 public static extern IntPtr Errmsg(IntPtr db);
3425
3426 public static string GetErrmsg(IntPtr db)
3427 {
3428 return Marshal.PtrToStringUni(Errmsg(db));
3429 }
3430
3431 [DllImport("sqlite3", EntryPoint = "sqlite3_bind_parameter_index", CallingConvention = CallingConvention.Cdecl)]
3432 public static extern int BindParameterIndex(IntPtr stmt, [MarshalAs(UnmanagedType.LPStr)] string name);
3433
3434 [DllImport("sqlite3", EntryPoint = "sqlite3_bind_null", CallingConvention = CallingConvention.Cdecl)]
3435 public static extern int BindNull(IntPtr stmt, int index);
3436
3437 [DllImport("sqlite3", EntryPoint = "sqlite3_bind_int", CallingConvention = CallingConvention.Cdecl)]
3438 public static extern int BindInt(IntPtr stmt, int index, int val);
3439
3440 [DllImport("sqlite3", EntryPoint = "sqlite3_bind_int64", CallingConvention = CallingConvention.Cdecl)]
3441 public static extern int BindInt64(IntPtr stmt, int index, long val);
3442
3443 [DllImport("sqlite3", EntryPoint = "sqlite3_bind_double", CallingConvention = CallingConvention.Cdecl)]
3444 public static extern int BindDouble(IntPtr stmt, int index, double val);
3445
3446 [DllImport("sqlite3", EntryPoint = "sqlite3_bind_text16", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Unicode)]
3447 public static extern int BindText(IntPtr stmt, int index, [MarshalAs(UnmanagedType.LPWStr)] string val, int n, IntPtr free);
3448
3449 [DllImport("sqlite3", EntryPoint = "sqlite3_bind_blob", CallingConvention = CallingConvention.Cdecl)]
3450 public static extern int BindBlob(IntPtr stmt, int index, byte[] val, int n, IntPtr free);
3451
3452 [DllImport("sqlite3", EntryPoint = "sqlite3_column_count", CallingConvention = CallingConvention.Cdecl)]
3453 public static extern int ColumnCount(IntPtr stmt);
3454
3455 [DllImport("sqlite3", EntryPoint = "sqlite3_column_name", CallingConvention = CallingConvention.Cdecl)]
3456 public static extern IntPtr ColumnName(IntPtr stmt, int index);
3457
3458 [DllImport("sqlite3", EntryPoint = "sqlite3_column_name16", CallingConvention = CallingConvention.Cdecl)]
3459 static extern IntPtr ColumnName16Internal(IntPtr stmt, int index);
3460 public static string ColumnName16(IntPtr stmt, int index)
3461 {
3462 return Marshal.PtrToStringUni(ColumnName16Internal(stmt, index));
3463 }
3464
3465 [DllImport("sqlite3", EntryPoint = "sqlite3_column_type", CallingConvention = CallingConvention.Cdecl)]
3466 public static extern ColType ColumnType(IntPtr stmt, int index);
3467
3468 [DllImport("sqlite3", EntryPoint = "sqlite3_column_int", CallingConvention = CallingConvention.Cdecl)]
3469 public static extern int ColumnInt(IntPtr stmt, int index);
3470
3471 [DllImport("sqlite3", EntryPoint = "sqlite3_column_int64", CallingConvention = CallingConvention.Cdecl)]
3472 public static extern long ColumnInt64(IntPtr stmt, int index);
3473
3474 [DllImport("sqlite3", EntryPoint = "sqlite3_column_double", CallingConvention = CallingConvention.Cdecl)]
3475 public static extern double ColumnDouble(IntPtr stmt, int index);
3476
3477 [DllImport("sqlite3", EntryPoint = "sqlite3_column_text", CallingConvention = CallingConvention.Cdecl)]
3478 public static extern IntPtr ColumnText(IntPtr stmt, int index);
3479
3480 [DllImport("sqlite3", EntryPoint = "sqlite3_column_text16", CallingConvention = CallingConvention.Cdecl)]
3481 public static extern IntPtr ColumnText16(IntPtr stmt, int index);
3482
3483 [DllImport("sqlite3", EntryPoint = "sqlite3_column_blob", CallingConvention = CallingConvention.Cdecl)]
3484 public static extern IntPtr ColumnBlob(IntPtr stmt, int index);
3485
3486 [DllImport("sqlite3", EntryPoint = "sqlite3_column_bytes", CallingConvention = CallingConvention.Cdecl)]
3487 public static extern int ColumnBytes(IntPtr stmt, int index);
3488
3489 public static string ColumnString(IntPtr stmt, int index)
3490 {
3491 return Marshal.PtrToStringUni(SQLite3.ColumnText16(stmt, index));
3492 }
3493
3494 public static byte[] ColumnByteArray(IntPtr stmt, int index)
3495 {
3496 int length = ColumnBytes(stmt, index);
3497 var result = new byte[length];
3498 if (length > 0)
3499 Marshal.Copy(ColumnBlob(stmt, index), result, 0, length);
3500 return result;
3501 }
3502
3503 [DllImport("sqlite3", EntryPoint = "sqlite3_extended_errcode", CallingConvention = CallingConvention.Cdecl)]
3504 public static extern ExtendedResult ExtendedErrCode(IntPtr db);
3505
3506 [DllImport("sqlite3", EntryPoint = "sqlite3_libversion_number", CallingConvention = CallingConvention.Cdecl)]
3507 public static extern int LibVersionNumber();
3508#else
3509 public static Result Open(string filename, out Sqlite3DatabaseHandle db)
3510 {
3511 return (Result) Sqlite3.sqlite3_open(filename, out db);
3512 }
3513
3514 public static Result Open(string filename, out Sqlite3DatabaseHandle db, int flags, IntPtr zVfs)
3515 {
3516#if USE_WP8_NATIVE_SQLITE
3517 return (Result)Sqlite3.sqlite3_open_v2(filename, out db, flags, "");
3518#else
3519 return (Result)Sqlite3.sqlite3_open_v2(filename, out db, flags, null);
3520#endif
3521 }
3522
3523 public static Result Close(Sqlite3DatabaseHandle db)
3524 {
3525 return (Result)Sqlite3.sqlite3_close(db);
3526 }
3527
3528 public static Result BusyTimeout(Sqlite3DatabaseHandle db, int milliseconds)
3529 {
3530 return (Result)Sqlite3.sqlite3_busy_timeout(db, milliseconds);
3531 }
3532
3533 public static int Changes(Sqlite3DatabaseHandle db)
3534 {
3535 return Sqlite3.sqlite3_changes(db);
3536 }
3537
3538 public static Sqlite3Statement Prepare2(Sqlite3DatabaseHandle db, string query)
3539 {
3540 Sqlite3Statement stmt = default(Sqlite3Statement);
3541#if USE_WP8_NATIVE_SQLITE
3542 var r = Sqlite3.sqlite3_prepare_v2(db, query, out stmt);
3543#else
3544 stmt = new Sqlite3Statement();
3545 var r = Sqlite3.sqlite3_prepare_v2(db, query, -1, ref stmt, 0);
3546#endif
3547 if (r != 0)
3548 {
3549 throw SQLiteException.New((Result)r, GetErrmsg(db));
3550 }
3551 return stmt;
3552 }
3553
3554 public static Result Step(Sqlite3Statement stmt)
3555 {
3556 return (Result)Sqlite3.sqlite3_step(stmt);
3557 }
3558
3559 public static Result Reset(Sqlite3Statement stmt)
3560 {
3561 return (Result)Sqlite3.sqlite3_reset(stmt);
3562 }
3563
3564 public static Result Finalize(Sqlite3Statement stmt)
3565 {
3566 return (Result)Sqlite3.sqlite3_finalize(stmt);
3567 }
3568
3569 public static long LastInsertRowid(Sqlite3DatabaseHandle db)
3570 {
3571 return Sqlite3.sqlite3_last_insert_rowid(db);
3572 }
3573
3574 public static string GetErrmsg(Sqlite3DatabaseHandle db)
3575 {
3576 return Sqlite3.sqlite3_errmsg(db);
3577 }
3578
3579 public static int BindParameterIndex(Sqlite3Statement stmt, string name)
3580 {
3581 return Sqlite3.sqlite3_bind_parameter_index(stmt, name);
3582 }
3583
3584 public static int BindNull(Sqlite3Statement stmt, int index)
3585 {
3586 return Sqlite3.sqlite3_bind_null(stmt, index);
3587 }
3588
3589 public static int BindInt(Sqlite3Statement stmt, int index, int val)
3590 {
3591 return Sqlite3.sqlite3_bind_int(stmt, index, val);
3592 }
3593
3594 public static int BindInt64(Sqlite3Statement stmt, int index, long val)
3595 {
3596 return Sqlite3.sqlite3_bind_int64(stmt, index, val);
3597 }
3598
3599 public static int BindDouble(Sqlite3Statement stmt, int index, double val)
3600 {
3601 return Sqlite3.sqlite3_bind_double(stmt, index, val);
3602 }
3603
3604 public static int BindText(Sqlite3Statement stmt, int index, string val, int n, IntPtr free)
3605 {
3606#if USE_WP8_NATIVE_SQLITE
3607 return Sqlite3.sqlite3_bind_text(stmt, index, val, n);
3608#else
3609 return Sqlite3.sqlite3_bind_text(stmt, index, val, n, null);
3610#endif
3611 }
3612
3613 public static int BindBlob(Sqlite3Statement stmt, int index, byte[] val, int n, IntPtr free)
3614 {
3615#if USE_WP8_NATIVE_SQLITE
3616 return Sqlite3.sqlite3_bind_blob(stmt, index, val, n);
3617#else
3618 return Sqlite3.sqlite3_bind_blob(stmt, index, val, n, null);
3619#endif
3620 }
3621
3622 public static int ColumnCount(Sqlite3Statement stmt)
3623 {
3624 return Sqlite3.sqlite3_column_count(stmt);
3625 }
3626
3627 public static string ColumnName(Sqlite3Statement stmt, int index)
3628 {
3629 return Sqlite3.sqlite3_column_name(stmt, index);
3630 }
3631
3632 public static string ColumnName16(Sqlite3Statement stmt, int index)
3633 {
3634 return Sqlite3.sqlite3_column_name(stmt, index);
3635 }
3636
3637 public static ColType ColumnType(Sqlite3Statement stmt, int index)
3638 {
3639 return (ColType)Sqlite3.sqlite3_column_type(stmt, index);
3640 }
3641
3642 public static int ColumnInt(Sqlite3Statement stmt, int index)
3643 {
3644 return Sqlite3.sqlite3_column_int(stmt, index);
3645 }
3646
3647 public static long ColumnInt64(Sqlite3Statement stmt, int index)
3648 {
3649 return Sqlite3.sqlite3_column_int64(stmt, index);
3650 }
3651
3652 public static double ColumnDouble(Sqlite3Statement stmt, int index)
3653 {
3654 return Sqlite3.sqlite3_column_double(stmt, index);
3655 }
3656
3657 public static string ColumnText(Sqlite3Statement stmt, int index)
3658 {
3659 return Sqlite3.sqlite3_column_text(stmt, index);
3660 }
3661
3662 public static string ColumnText16(Sqlite3Statement stmt, int index)
3663 {
3664 return Sqlite3.sqlite3_column_text(stmt, index);
3665 }
3666
3667 public static byte[] ColumnBlob(Sqlite3Statement stmt, int index)
3668 {
3669 return Sqlite3.sqlite3_column_blob(stmt, index);
3670 }
3671
3672 public static int ColumnBytes(Sqlite3Statement stmt, int index)
3673 {
3674 return Sqlite3.sqlite3_column_bytes(stmt, index);
3675 }
3676
3677 public static string ColumnString(Sqlite3Statement stmt, int index)
3678 {
3679 return Sqlite3.sqlite3_column_text(stmt, index);
3680 }
3681
3682 public static byte[] ColumnByteArray(Sqlite3Statement stmt, int index)
3683 {
3684 return ColumnBlob(stmt, index);
3685 }
3686
3687 public static Result EnableLoadExtension(Sqlite3DatabaseHandle db, int onoff)
3688 {
3689 return (Result)Sqlite3.sqlite3_enable_load_extension(db, onoff);
3690 }
3691
3692 public static ExtendedResult ExtendedErrCode(Sqlite3DatabaseHandle db)
3693 {
3694 return (ExtendedResult)Sqlite3.sqlite3_extended_errcode(db);
3695 }
3696#endif
3697
3698 public enum ColType : int
3699 {
3700 Integer = 1,
3701 Float = 2,
3702 Text = 3,
3703 Blob = 4,
3704 Null = 5
3705 }
3706 }
3707}