StarryEyes-improve_connection.patch

From b6f08957c1605b441385e6b671f9fdd7b6df9cf4 Mon Sep 17 00:00:00 2001
From: Takeshi KIRIYA <takeshik@xspect.org>
Date: Mon, 14 Jul 2014 09:24:07 +0900
Subject: [PATCH] improve connection

---
 StarryEyes.Casket/Cruds/ListUserCrud.cs         |  6 +-
 StarryEyes.Casket/Cruds/ManagementCrud.cs       |  6 +-
 StarryEyes.Casket/Cruds/Scaffolding/CrudBase.cs | 74 ++-----------------------
 StarryEyes.Casket/Cruds/UserCrud.cs             |  2 +-
 StarryEyes.Casket/Database.cs                   | 71 ++++++++++++++++++++++++
 StarryEyes/App.xaml.cs                          | 11 +++-
 6 files changed, 92 insertions(+), 78 deletions(-)

diff --git a/StarryEyes.Casket/Cruds/ListUserCrud.cs b/StarryEyes.Casket/Cruds/ListUserCrud.cs
index c03d5ab..8ad1610 100644
--- a/StarryEyes.Casket/Cruds/ListUserCrud.cs
+++ b/StarryEyes.Casket/Cruds/ListUserCrud.cs
@@ -37,12 +37,12 @@ namespace StarryEyes.Casket.Cruds
                 try
                 {
                     ReaderWriterLock.EnterWriteLock();
-                    using (var conn = DangerousOpenConnection())
-                    using (var tran = conn.BeginTransaction(DefaultIsolationLevel))
+                    var con = Database.Connection;
+                    using (var tran = con.BeginTransaction(DefaultIsolationLevel))
                     {
                         foreach (var userId in userIds)
                         {
-                            conn.Execute(TableInserter, new DatabaseListUser(listId, userId));
+                            con.Execute(TableInserter, new DatabaseListUser(listId, userId));
                         }
                         tran.Commit();
                     }
diff --git a/StarryEyes.Casket/Cruds/ManagementCrud.cs b/StarryEyes.Casket/Cruds/ManagementCrud.cs
index 78d0756..fee3a72 100644
--- a/StarryEyes.Casket/Cruds/ManagementCrud.cs
+++ b/StarryEyes.Casket/Cruds/ManagementCrud.cs
@@ -31,8 +31,8 @@ namespace StarryEyes.Casket.Cruds
             var sql = this.CreateSql("Id = @Id");
             try
             {
+                var con = Database.Connection;
                 using (AcquireReadLock())
-                using (var con = DangerousOpenConnection())
                 {
                     return con.Query<DatabaseManagement>(sql, new { Id = id })
                               .SingleOrDefault();
@@ -53,8 +53,8 @@ namespace StarryEyes.Casket.Cruds
         {
             try
             {
+                var con = Database.Connection;
                 using (AcquireWriteLock())
-                using (var con = DangerousOpenConnection())
                 using (var tr = con.BeginTransaction(DefaultIsolationLevel))
                 {
                     con.Execute(this.TableInserter, mgmt);
@@ -76,8 +76,8 @@ namespace StarryEyes.Casket.Cruds
             {
                 try
                 {
+                    var con = Database.Connection;
                     using (AcquireWriteLock())
-                    using (var con = DangerousOpenConnection())
                     {
                         con.Execute("VACUUM;");
                     }
diff --git a/StarryEyes.Casket/Cruds/Scaffolding/CrudBase.cs b/StarryEyes.Casket/Cruds/Scaffolding/CrudBase.cs
index a9a4c93..a0a7447 100644
--- a/StarryEyes.Casket/Cruds/Scaffolding/CrudBase.cs
+++ b/StarryEyes.Casket/Cruds/Scaffolding/CrudBase.cs
@@ -24,37 +24,6 @@ namespace StarryEyes.Casket.Cruds.Scaffolding
 
         #endregion
 
-        #region connection string builder
-
-        private static readonly string _baseConStr = CreateBaseConStr();
-
-        private static string CreateBaseConStr()
-        {
-            var dic = new Dictionary<string, string>
-            {
-                {"Version", "3"},
-                {"Cache Size", "8000"},
-                // This option would cause damage to database image.
-                // {"Synchronous", "Off"},
-                {"Synchronous", "Normal"},
-                {"Default Timeout", "3"},
-                {"Default IsolationLevel", "Serializable"},
-                {"Journal Mode", "WAL"},
-                {"Page Size", "2048"},
-                {"Pooling", "True"},
-                {"Max Pool Size", "200"},
-            };
-            return dic.Select(kvp => kvp.Key + "=" + kvp.Value)
-                      .JoinString(";");
-        }
-
-        private static string CreateConStr(string dbfilepath)
-        {
-            return "Data Source=" + dbfilepath + ";" + _baseConStr;
-        }
-
-        #endregion
-
         #region threading
 
         private static readonly TaskFactory _readTaskFactory = LimitedTaskScheduler.GetTaskFactory(8);
@@ -82,37 +51,6 @@ namespace StarryEyes.Casket.Cruds.Scaffolding
             return Disposable.Create(() => _rwlock.ExitReadLock());
         }
 
-        protected static SQLiteConnection DangerousOpenConnection()
-        {
-#if DEBUG
-            if (!ReaderWriterLock.IsReadLockHeld &&
-                !ReaderWriterLock.IsUpgradeableReadLockHeld &&
-                !ReaderWriterLock.IsWriteLockHeld)
-            {
-                throw new InvalidOperationException("This thread does not have any locks!");
-            }
-#endif
-            SQLiteConnection con = null;
-            try
-            {
-                con = new SQLiteConnection(CreateConStr(Database.DbFilePath));
-                con.Open();
-                con.Execute("PRAGMA case_sensitive_like=1");
-                return con;
-            }
-            catch (Exception)
-            {
-                if (con != null)
-                {
-                    try { con.Dispose(); }
-                    // ReSharper disable EmptyGeneralCatchClause
-                    catch { }
-                    // ReSharper restore EmptyGeneralCatchClause
-                }
-                throw;
-            }
-        }
-
         internal static Task<int> ExecuteAsync(string query)
         {
             return _writeTaskFactory.StartNew(() =>
@@ -121,7 +59,7 @@ namespace StarryEyes.Casket.Cruds.Scaffolding
                 try
                 {
                     ReaderWriterLock.EnterWriteLock();
-                    using (var con = DangerousOpenConnection())
+                    var con = Database.Connection;
                     using (var tr = con.BeginTransaction(DefaultIsolationLevel))
                     {
                         var result = con.Execute(query, transaction: tr);
@@ -148,7 +86,7 @@ namespace StarryEyes.Casket.Cruds.Scaffolding
                 {
                     _rwlock.EnterWriteLock();
                     // System.Diagnostics.Debug.WriteLine("EXECUTE: " + query);
-                    using (var con = DangerousOpenConnection())
+                    var con = Database.Connection;
                     using (var tr = con.BeginTransaction(DefaultIsolationLevel))
                     {
                         var result = (int)SqlMapper.Execute(con, query, param, tr);
@@ -175,7 +113,7 @@ namespace StarryEyes.Casket.Cruds.Scaffolding
                 try
                 {
                     ReaderWriterLock.EnterWriteLock();
-                    using (var con = DangerousOpenConnection())
+                    var con = Database.Connection;
                     using (var tr = con.BeginTransaction(DefaultIsolationLevel))
                     {
                         foreach (var qap in qnp)
@@ -206,10 +144,8 @@ namespace StarryEyes.Casket.Cruds.Scaffolding
                 try
                 {
                     ReaderWriterLock.EnterReadLock();
-                    using (var con = DangerousOpenConnection())
-                    {
-                        return con.Query<T>(query, param);
-                    }
+                    var con = Database.Connection;
+                    return con.Query<T>(query, param);
                 }
                 catch (Exception ex)
                 {
diff --git a/StarryEyes.Casket/Cruds/UserCrud.cs b/StarryEyes.Casket/Cruds/UserCrud.cs
index aa60441..dfac511 100644
--- a/StarryEyes.Casket/Cruds/UserCrud.cs
+++ b/StarryEyes.Casket/Cruds/UserCrud.cs
@@ -34,8 +34,8 @@ namespace StarryEyes.Casket.Cruds
             var sql = "select Id from " + TableName + " where LOWER(ScreenName) = @ScreenName limit 1;";
             try
             {
+                var con = Database.Connection;
                 using (AcquireReadLock())
-                using (var con = DangerousOpenConnection())
                 {
                     return con.Query<long>(sql, new { ScreenName = screenName.ToLower() })
                               .SingleOrDefault();
diff --git a/StarryEyes.Casket/Database.cs b/StarryEyes.Casket/Database.cs
index 7c1b82b..108984f 100644
--- a/StarryEyes.Casket/Database.cs
+++ b/StarryEyes.Casket/Database.cs
@@ -4,6 +4,7 @@ using System.Data.SQLite;
 using System.Linq;
 using System.Reflection;
 using System.Threading.Tasks;
+using Dapper;
 using StarryEyes.Casket.Cruds;
 using StarryEyes.Casket.Cruds.Scaffolding;
 using StarryEyes.Casket.DatabaseModels;
@@ -25,9 +26,12 @@ namespace StarryEyes.Casket
         private static readonly RelationCrud _relationCrud = new RelationCrud();
         private static readonly ManagementCrud _managementCrud = new ManagementCrud();
 
+        private static SQLiteConnection _connection;
         private static string _dbFilePath;
         private static bool _isInitialized;
 
+        public static SQLiteConnection Connection { get { return _connection; } }
+
         public static string DbFilePath { get { return _dbFilePath; } }
 
         public static AccountInfoCrud AccountInfoCrud
@@ -107,6 +111,8 @@ namespace StarryEyes.Casket
 
             // initialize tables
             _dbFilePath = dbFilePath;
+            _connection = OpenConnection();
+
             var tasks = new Task[] { };
             Task.WaitAll(Task.Factory.StartNew(() =>
             {
@@ -129,11 +135,76 @@ namespace StarryEyes.Casket
             Task.WaitAll(tasks);
         }
 
+        public static void Shutdown()
+        {
+            if (_connection != null)
+            {
+                _connection.Dispose();
+            }
+        }
+
         public static async Task ReInitializeAsync<T>(CrudBase<T> crudBase) where T : class
         {
             await crudBase.InitializeAsync();
         }
 
+        internal static SQLiteConnection OpenConnection()
+        {
+#if DEBUG
+            if (!ReaderWriterLock.IsReadLockHeld &&
+                !ReaderWriterLock.IsUpgradeableReadLockHeld &&
+                !ReaderWriterLock.IsWriteLockHeld)
+            {
+                throw new InvalidOperationException("This thread does not have any locks!");
+            }
+#endif
+            SQLiteConnection con = null;
+            try
+            {
+                con = new SQLiteConnection(CreateConStr(DbFilePath));
+                con.Open();
+                con.Execute("PRAGMA case_sensitive_like=1");
+                return con;
+            }
+            catch (Exception)
+            {
+                if (con != null)
+                {
+                    try { con.Dispose(); }
+                    // ReSharper disable EmptyGeneralCatchClause
+                    catch { }
+                    // ReSharper restore EmptyGeneralCatchClause
+                }
+                throw;
+            }
+        }
+
+        private static string CreateBaseConStr()
+        {
+            var dic = new Dictionary<string, string>
+            {
+                {"Version", "3"},
+                {"Cache Size", "8000"},
+                // This option would cause damage to database image.
+                // {"Synchronous", "Off"},
+                {"Synchronous", "Normal"},
+                {"Default Timeout", "3"},
+                {"Default IsolationLevel", "Serializable"},
+                {"Journal Mode", "WAL"},
+                {"Page Size", "2048"},
+                {"Pooling", "True"},
+                {"Max Pool Size", "200"},
+            };
+            return dic.Select(kvp => kvp.Key + "=" + kvp.Value)
+                      .JoinString(";");
+        }
+
+        private static string CreateConStr(string dbfilepath)
+        {
+            return (dbfilepath.StartsWith("file:") ? "Uri=" : "Data Source=")
+                + dbfilepath + ";" + CreateBaseConStr();
+        }
+
         #region store in one transaction
 
         private static readonly string _statusInserter =
diff --git a/StarryEyes/App.xaml.cs b/StarryEyes/App.xaml.cs
index 1b9754d..bad0fc5 100644
--- a/StarryEyes/App.xaml.cs
+++ b/StarryEyes/App.xaml.cs
@@ -65,6 +65,8 @@ namespace StarryEyes
 
             // initialize database
             Database.Initialize(DatabaseFilePath);
+            ApplicationFinalize += Database.Shutdown;
+
             if (!this.CheckDatabase())
             {
                 // db migration failed
@@ -278,7 +280,7 @@ namespace StarryEyes
                         return ConfigurationDirectoryPath;
                     }
                     // make sure to exist database directory
-                    if (!Directory.Exists(path))
+                    if (!(path.StartsWith("file:") || Directory.Exists(path)))
                     {
                         Directory.CreateDirectory(path);
                     }
@@ -356,7 +358,12 @@ namespace StarryEyes
         [NotNull]
         public static string DatabaseFilePath
         {
-            get { return Path.Combine(DatabaseDirectoryPath, DatabaseFileName); }
+            get
+            {
+                return DatabaseDirectoryPath.StartsWith("file:")
+                    ? DatabaseDirectoryPath
+                    : Path.Combine(DatabaseDirectoryPath, DatabaseFileName);
+            }
         }
 
         [NotNull]
-- 
1.8.4.msysgit.0