PageRenderTime 34ms CodeModel.GetById 9ms RepoModel.GetById 1ms app.codeStats 0ms

/BE2012/SQLiteAsync.cs

https://bitbucket.org/damirarh/bleedingedge2012
C# | 486 lines | 390 code | 57 blank | 39 comment | 1 complexity | c5d90d6da149267a18c8fa24d8902f81 MD5 | raw file
  1. //
  2. // Copyright (c) 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. using System;
  23. using System.Collections;
  24. using System.Collections.Generic;
  25. using System.Linq;
  26. using System.Linq.Expressions;
  27. using System.Threading;
  28. using System.Threading.Tasks;
  29. namespace SQLite
  30. {
  31. public class SQLiteAsyncConnection
  32. {
  33. SQLiteConnectionString _connectionString;
  34. public SQLiteAsyncConnection (string databasePath, bool storeDateTimeAsTicks = false)
  35. {
  36. _connectionString = new SQLiteConnectionString (databasePath, storeDateTimeAsTicks);
  37. }
  38. SQLiteConnectionWithLock GetConnection ()
  39. {
  40. return SQLiteConnectionPool.Shared.GetConnection (_connectionString);
  41. }
  42. public Task<CreateTablesResult> CreateTableAsync<T> ()
  43. where T : new ()
  44. {
  45. return CreateTablesAsync (typeof (T));
  46. }
  47. public Task<CreateTablesResult> CreateTablesAsync<T, T2> ()
  48. where T : new ()
  49. where T2 : new ()
  50. {
  51. return CreateTablesAsync (typeof (T), typeof (T2));
  52. }
  53. public Task<CreateTablesResult> CreateTablesAsync<T, T2, T3> ()
  54. where T : new ()
  55. where T2 : new ()
  56. where T3 : new ()
  57. {
  58. return CreateTablesAsync (typeof (T), typeof (T2), typeof (T3));
  59. }
  60. public Task<CreateTablesResult> CreateTablesAsync<T, T2, T3, T4> ()
  61. where T : new ()
  62. where T2 : new ()
  63. where T3 : new ()
  64. where T4 : new ()
  65. {
  66. return CreateTablesAsync (typeof (T), typeof (T2), typeof (T3), typeof (T4));
  67. }
  68. public Task<CreateTablesResult> CreateTablesAsync<T, T2, T3, T4, T5> ()
  69. where T : new ()
  70. where T2 : new ()
  71. where T3 : new ()
  72. where T4 : new ()
  73. where T5 : new ()
  74. {
  75. return CreateTablesAsync (typeof (T), typeof (T2), typeof (T3), typeof (T4), typeof (T5));
  76. }
  77. public Task<CreateTablesResult> CreateTablesAsync (params Type[] types)
  78. {
  79. return Task.Factory.StartNew (() => {
  80. CreateTablesResult result = new CreateTablesResult ();
  81. var conn = GetConnection ();
  82. using (conn.Lock ()) {
  83. foreach (Type type in types) {
  84. int aResult = conn.CreateTable (type);
  85. result.Results[type] = aResult;
  86. }
  87. }
  88. return result;
  89. });
  90. }
  91. public Task<int> DropTableAsync<T> ()
  92. where T : new ()
  93. {
  94. return Task.Factory.StartNew (() => {
  95. var conn = GetConnection ();
  96. using (conn.Lock ()) {
  97. return conn.DropTable<T> ();
  98. }
  99. });
  100. }
  101. public Task<int> InsertAsync (object item)
  102. {
  103. return Task.Factory.StartNew (() => {
  104. var conn = GetConnection ();
  105. using (conn.Lock ()) {
  106. return conn.Insert (item);
  107. }
  108. });
  109. }
  110. public Task<int> UpdateAsync (object item)
  111. {
  112. return Task.Factory.StartNew (() => {
  113. var conn = GetConnection ();
  114. using (conn.Lock ()) {
  115. return conn.Update (item);
  116. }
  117. });
  118. }
  119. public Task<int> DeleteAsync (object item)
  120. {
  121. return Task.Factory.StartNew (() => {
  122. var conn = GetConnection ();
  123. using (conn.Lock ()) {
  124. return conn.Delete (item);
  125. }
  126. });
  127. }
  128. public Task<T> GetAsync<T>(object pk)
  129. where T : new()
  130. {
  131. return Task.Factory.StartNew(() =>
  132. {
  133. var conn = GetConnection();
  134. using (conn.Lock())
  135. {
  136. return conn.Get<T>(pk);
  137. }
  138. });
  139. }
  140. public Task<T> FindAsync<T> (object pk)
  141. where T : new ()
  142. {
  143. return Task.Factory.StartNew (() => {
  144. var conn = GetConnection ();
  145. using (conn.Lock ()) {
  146. return conn.Find<T> (pk);
  147. }
  148. });
  149. }
  150. public Task<T> GetAsync<T> (Expression<Func<T, bool>> predicate)
  151. where T : new()
  152. {
  153. return Task.Factory.StartNew(() =>
  154. {
  155. var conn = GetConnection();
  156. using (conn.Lock())
  157. {
  158. return conn.Get<T> (predicate);
  159. }
  160. });
  161. }
  162. public Task<T> FindAsync<T> (Expression<Func<T, bool>> predicate)
  163. where T : new ()
  164. {
  165. return Task.Factory.StartNew (() => {
  166. var conn = GetConnection ();
  167. using (conn.Lock ()) {
  168. return conn.Find<T> (predicate);
  169. }
  170. });
  171. }
  172. public Task<int> ExecuteAsync (string query, params object[] args)
  173. {
  174. return Task<int>.Factory.StartNew (() => {
  175. var conn = GetConnection ();
  176. using (conn.Lock ()) {
  177. return conn.Execute (query, args);
  178. }
  179. });
  180. }
  181. public Task<int> InsertAllAsync (IEnumerable items)
  182. {
  183. return Task.Factory.StartNew (() => {
  184. var conn = GetConnection ();
  185. using (conn.Lock ()) {
  186. return conn.InsertAll (items);
  187. }
  188. });
  189. }
  190. [Obsolete("Will cause a deadlock if any call in action ends up in a different thread. Use RunInTransactionAsync(Action<SQLiteConnection>) instead.")]
  191. public Task RunInTransactionAsync (Action<SQLiteAsyncConnection> action)
  192. {
  193. return Task.Factory.StartNew (() => {
  194. var conn = this.GetConnection ();
  195. using (conn.Lock ()) {
  196. conn.BeginTransaction ();
  197. try {
  198. action (this);
  199. conn.Commit ();
  200. }
  201. catch (Exception) {
  202. conn.Rollback ();
  203. throw;
  204. }
  205. }
  206. });
  207. }
  208. public Task RunInTransactionAsync(Action<SQLiteConnection> action)
  209. {
  210. return Task.Factory.StartNew(() =>
  211. {
  212. var conn = this.GetConnection();
  213. using (conn.Lock())
  214. {
  215. conn.BeginTransaction();
  216. try
  217. {
  218. action(conn);
  219. conn.Commit();
  220. }
  221. catch (Exception)
  222. {
  223. conn.Rollback();
  224. throw;
  225. }
  226. }
  227. });
  228. }
  229. public AsyncTableQuery<T> Table<T> ()
  230. where T : new ()
  231. {
  232. //
  233. // This isn't async as the underlying connection doesn't go out to the database
  234. // until the query is performed. The Async methods are on the query iteself.
  235. //
  236. var conn = GetConnection ();
  237. return new AsyncTableQuery<T> (conn.Table<T> ());
  238. }
  239. public Task<T> ExecuteScalarAsync<T> (string sql, params object[] args)
  240. {
  241. return Task<T>.Factory.StartNew (() => {
  242. var conn = GetConnection ();
  243. using (conn.Lock ()) {
  244. var command = conn.CreateCommand (sql, args);
  245. return command.ExecuteScalar<T> ();
  246. }
  247. });
  248. }
  249. public Task<List<T>> QueryAsync<T> (string sql, params object[] args)
  250. where T : new ()
  251. {
  252. return Task<List<T>>.Factory.StartNew (() => {
  253. var conn = GetConnection ();
  254. using (conn.Lock ()) {
  255. return conn.Query<T> (sql, args);
  256. }
  257. });
  258. }
  259. }
  260. //
  261. // TODO: Bind to AsyncConnection.GetConnection instead so that delayed
  262. // execution can still work after a Pool.Reset.
  263. //
  264. public class AsyncTableQuery<T>
  265. where T : new ()
  266. {
  267. TableQuery<T> _innerQuery;
  268. public AsyncTableQuery (TableQuery<T> innerQuery)
  269. {
  270. _innerQuery = innerQuery;
  271. }
  272. public AsyncTableQuery<T> Where (Expression<Func<T, bool>> predExpr)
  273. {
  274. return new AsyncTableQuery<T> (_innerQuery.Where (predExpr));
  275. }
  276. public AsyncTableQuery<T> Skip (int n)
  277. {
  278. return new AsyncTableQuery<T> (_innerQuery.Skip (n));
  279. }
  280. public AsyncTableQuery<T> Take (int n)
  281. {
  282. return new AsyncTableQuery<T> (_innerQuery.Take (n));
  283. }
  284. public AsyncTableQuery<T> OrderBy<U> (Expression<Func<T, U>> orderExpr)
  285. {
  286. return new AsyncTableQuery<T> (_innerQuery.OrderBy<U> (orderExpr));
  287. }
  288. public AsyncTableQuery<T> OrderByDescending<U> (Expression<Func<T, U>> orderExpr)
  289. {
  290. return new AsyncTableQuery<T> (_innerQuery.OrderByDescending<U> (orderExpr));
  291. }
  292. public Task<List<T>> ToListAsync ()
  293. {
  294. return Task.Factory.StartNew (() => {
  295. using (((SQLiteConnectionWithLock)_innerQuery.Connection).Lock ()) {
  296. return _innerQuery.ToList ();
  297. }
  298. });
  299. }
  300. public Task<int> CountAsync ()
  301. {
  302. return Task.Factory.StartNew (() => {
  303. using (((SQLiteConnectionWithLock)_innerQuery.Connection).Lock ()) {
  304. return _innerQuery.Count ();
  305. }
  306. });
  307. }
  308. public Task<T> ElementAtAsync (int index)
  309. {
  310. return Task.Factory.StartNew (() => {
  311. using (((SQLiteConnectionWithLock)_innerQuery.Connection).Lock ()) {
  312. return _innerQuery.ElementAt (index);
  313. }
  314. });
  315. }
  316. public Task<T> FirstAsync ()
  317. {
  318. return Task<T>.Factory.StartNew(() => {
  319. using (((SQLiteConnectionWithLock)_innerQuery.Connection).Lock ()) {
  320. return _innerQuery.First ();
  321. }
  322. });
  323. }
  324. public Task<T> FirstOrDefaultAsync ()
  325. {
  326. return Task<T>.Factory.StartNew(() => {
  327. using (((SQLiteConnectionWithLock)_innerQuery.Connection).Lock ()) {
  328. return _innerQuery.FirstOrDefault ();
  329. }
  330. });
  331. }
  332. }
  333. public class CreateTablesResult
  334. {
  335. public Dictionary<Type, int> Results { get; private set; }
  336. internal CreateTablesResult ()
  337. {
  338. this.Results = new Dictionary<Type, int> ();
  339. }
  340. }
  341. class SQLiteConnectionPool
  342. {
  343. class Entry
  344. {
  345. public SQLiteConnectionString ConnectionString { get; private set; }
  346. public SQLiteConnectionWithLock Connection { get; private set; }
  347. public Entry (SQLiteConnectionString connectionString)
  348. {
  349. ConnectionString = connectionString;
  350. Connection = new SQLiteConnectionWithLock (connectionString);
  351. }
  352. public void OnApplicationSuspended ()
  353. {
  354. Connection.Dispose ();
  355. Connection = null;
  356. }
  357. }
  358. readonly Dictionary<string, Entry> _entries = new Dictionary<string, Entry> ();
  359. readonly object _entriesLock = new object ();
  360. static readonly SQLiteConnectionPool _shared = new SQLiteConnectionPool ();
  361. /// <summary>
  362. /// Gets the singleton instance of the connection tool.
  363. /// </summary>
  364. public static SQLiteConnectionPool Shared
  365. {
  366. get
  367. {
  368. return _shared;
  369. }
  370. }
  371. public SQLiteConnectionWithLock GetConnection (SQLiteConnectionString connectionString)
  372. {
  373. lock (_entriesLock) {
  374. Entry entry;
  375. string key = connectionString.ConnectionString;
  376. if (!_entries.TryGetValue (key, out entry)) {
  377. entry = new Entry (connectionString);
  378. _entries[key] = entry;
  379. }
  380. return entry.Connection;
  381. }
  382. }
  383. /// <summary>
  384. /// Closes all connections managed by this pool.
  385. /// </summary>
  386. public void Reset ()
  387. {
  388. lock (_entriesLock) {
  389. foreach (var entry in _entries.Values) {
  390. entry.OnApplicationSuspended ();
  391. }
  392. _entries.Clear ();
  393. }
  394. }
  395. /// <summary>
  396. /// Call this method when the application is suspended.
  397. /// </summary>
  398. /// <remarks>Behaviour here is to close any open connections.</remarks>
  399. public void ApplicationSuspended ()
  400. {
  401. Reset ();
  402. }
  403. }
  404. class SQLiteConnectionWithLock : SQLiteConnection
  405. {
  406. readonly object _lockPoint = new object ();
  407. public SQLiteConnectionWithLock (SQLiteConnectionString connectionString)
  408. : base (connectionString.DatabasePath, connectionString.StoreDateTimeAsTicks)
  409. {
  410. }
  411. public IDisposable Lock ()
  412. {
  413. return new LockWrapper (_lockPoint);
  414. }
  415. private class LockWrapper : IDisposable
  416. {
  417. object _lockPoint;
  418. public LockWrapper (object lockPoint)
  419. {
  420. _lockPoint = lockPoint;
  421. Monitor.Enter (_lockPoint);
  422. }
  423. public void Dispose ()
  424. {
  425. Monitor.Exit (_lockPoint);
  426. }
  427. }
  428. }
  429. }