From a4d1c9e6e48f63121cc51abda61ed46d7f6a72cf Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Sun, 1 May 2016 17:48:37 -0400 Subject: update sqlite --- .../Activity/ActivityRepository.cs | 4 +- .../MediaBrowser.Server.Implementations.csproj | 6 +- .../Notifications/SqliteNotificationsRepository.cs | 4 +- .../Persistence/DataExtensions.cs | 182 +++++++++++++++++ .../Persistence/IDbConnector.cs | 10 + .../SqliteDisplayPreferencesRepository.cs | 4 +- .../Persistence/SqliteExtensions.cs | 218 --------------------- .../SqliteFileOrganizationRepository.cs | 4 +- .../Persistence/SqliteProviderInfoRepository.cs | 4 +- .../Persistence/SqliteUserDataRepository.cs | 4 +- .../Persistence/SqliteUserRepository.cs | 6 +- .../Security/AuthenticationRepository.cs | 4 +- .../Social/SharingRepository.cs | 4 +- .../Sync/SyncRepository.cs | 4 +- 14 files changed, 215 insertions(+), 243 deletions(-) create mode 100644 MediaBrowser.Server.Implementations/Persistence/DataExtensions.cs create mode 100644 MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs delete mode 100644 MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs (limited to 'MediaBrowser.Server.Implementations') diff --git a/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs b/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs index 85ab761823..b0e05a5bc8 100644 --- a/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs +++ b/MediaBrowser.Server.Implementations/Activity/ActivityRepository.cs @@ -27,11 +27,11 @@ namespace MediaBrowser.Server.Implementations.Activity _appPaths = appPaths; } - public async Task Initialize() + public async Task Initialize(IDbConnector dbConnector) { var dbFile = Path.Combine(_appPaths.DataPath, "activitylog.db"); - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); string[] queries = { diff --git a/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj b/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj index 43f5d741b4..aff3a5e160 100644 --- a/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj +++ b/MediaBrowser.Server.Implementations/MediaBrowser.Server.Implementations.csproj @@ -78,9 +78,6 @@ - - ..\ThirdParty\System.Data.SQLite.ManagedOnly\1.0.94.0\System.Data.SQLite.dll - @@ -261,6 +258,8 @@ + + @@ -275,7 +274,6 @@ - diff --git a/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs b/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs index 7302431e17..cecf03ddfe 100644 --- a/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs +++ b/MediaBrowser.Server.Implementations/Notifications/SqliteNotificationsRepository.cs @@ -32,11 +32,11 @@ namespace MediaBrowser.Server.Implementations.Notifications _appPaths = appPaths; } - public async Task Initialize() + public async Task Initialize(IDbConnector dbConnector) { var dbFile = Path.Combine(_appPaths.DataPath, "notifications.db"); - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); string[] queries = { diff --git a/MediaBrowser.Server.Implementations/Persistence/DataExtensions.cs b/MediaBrowser.Server.Implementations/Persistence/DataExtensions.cs new file mode 100644 index 0000000000..103b75f840 --- /dev/null +++ b/MediaBrowser.Server.Implementations/Persistence/DataExtensions.cs @@ -0,0 +1,182 @@ +using System.Text; +using MediaBrowser.Model.Logging; +using MediaBrowser.Model.Serialization; +using System; +using System.Data; +using System.IO; +using System.Threading.Tasks; + +namespace MediaBrowser.Server.Implementations.Persistence +{ + static class DataExtensions + { + /// + /// Determines whether the specified conn is open. + /// + /// The conn. + /// true if the specified conn is open; otherwise, false. + public static bool IsOpen(this IDbConnection conn) + { + return conn.State == ConnectionState.Open; + } + + public static IDataParameter GetParameter(this IDbCommand cmd, int index) + { + return (IDataParameter)cmd.Parameters[index]; + } + + public static IDataParameter Add(this IDataParameterCollection paramCollection, IDbCommand cmd, string name, DbType type) + { + var param = cmd.CreateParameter(); + + param.ParameterName = name; + param.DbType = type; + + paramCollection.Add(param); + + return param; + } + + public static IDataParameter Add(this IDataParameterCollection paramCollection, IDbCommand cmd, string name) + { + var param = cmd.CreateParameter(); + + param.ParameterName = name; + + paramCollection.Add(param); + + return param; + } + + + /// + /// Gets a stream from a DataReader at a given ordinal + /// + /// The reader. + /// The ordinal. + /// Stream. + /// reader + public static Stream GetMemoryStream(this IDataReader reader, int ordinal) + { + if (reader == null) + { + throw new ArgumentNullException("reader"); + } + + var memoryStream = new MemoryStream(); + var num = 0L; + var array = new byte[4096]; + long bytes; + do + { + bytes = reader.GetBytes(ordinal, num, array, 0, array.Length); + memoryStream.Write(array, 0, (int)bytes); + num += bytes; + } + while (bytes > 0L); + memoryStream.Position = 0; + return memoryStream; + } + + /// + /// Runs the queries. + /// + /// The connection. + /// The queries. + /// The logger. + /// true if XXXX, false otherwise + /// queries + public static void RunQueries(this IDbConnection connection, string[] queries, ILogger logger) + { + if (queries == null) + { + throw new ArgumentNullException("queries"); + } + + using (var tran = connection.BeginTransaction()) + { + try + { + using (var cmd = connection.CreateCommand()) + { + foreach (var query in queries) + { + cmd.Transaction = tran; + cmd.CommandText = query; + cmd.ExecuteNonQuery(); + } + } + + tran.Commit(); + } + catch (Exception e) + { + logger.ErrorException("Error running queries", e); + tran.Rollback(); + throw; + } + } + } + + public static void Attach(IDbConnection db, string path, string alias) + { + using (var cmd = db.CreateCommand()) + { + cmd.CommandText = string.Format("attach '{0}' as {1};", path, alias); + cmd.ExecuteNonQuery(); + } + } + + /// + /// Serializes to bytes. + /// + /// The json. + /// The obj. + /// System.Byte[][]. + /// obj + public static byte[] SerializeToBytes(this IJsonSerializer json, object obj) + { + if (obj == null) + { + throw new ArgumentNullException("obj"); + } + + using (var stream = new MemoryStream()) + { + json.SerializeToStream(obj, stream); + return stream.ToArray(); + } + } + + public static void AddColumn(this IDbConnection connection, ILogger logger, string table, string columnName, string type) + { + using (var cmd = connection.CreateCommand()) + { + cmd.CommandText = "PRAGMA table_info(" + table + ")"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + if (!reader.IsDBNull(1)) + { + var name = reader.GetString(1); + + if (string.Equals(name, columnName, StringComparison.OrdinalIgnoreCase)) + { + return; + } + } + } + } + } + + var builder = new StringBuilder(); + + builder.AppendLine("alter table " + table); + builder.AppendLine("add column " + columnName + " " + type); + + connection.RunQueries(new[] { builder.ToString() }, logger); + } + } +} \ No newline at end of file diff --git a/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs b/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs new file mode 100644 index 0000000000..cac9fe9835 --- /dev/null +++ b/MediaBrowser.Server.Implementations/Persistence/IDbConnector.cs @@ -0,0 +1,10 @@ +using System.Data; +using System.Threading.Tasks; + +namespace MediaBrowser.Server.Implementations.Persistence +{ + public interface IDbConnector + { + Task Connect(string dbPath); + } +} diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs index 45e0304c1c..6077cfdba4 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteDisplayPreferencesRepository.cs @@ -52,11 +52,11 @@ namespace MediaBrowser.Server.Implementations.Persistence /// Opens the connection to the database /// /// Task. - public async Task Initialize() + public async Task Initialize(IDbConnector dbConnector) { var dbFile = Path.Combine(_appPaths.DataPath, "displaypreferences.db"); - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); string[] queries = { diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs deleted file mode 100644 index 4fb1e07dd7..0000000000 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteExtensions.cs +++ /dev/null @@ -1,218 +0,0 @@ -using System.Text; -using MediaBrowser.Model.Logging; -using MediaBrowser.Model.Serialization; -using System; -using System.Data; -using System.Data.SQLite; -using System.IO; -using System.Threading.Tasks; - -namespace MediaBrowser.Server.Implementations.Persistence -{ - /// - /// Class SQLiteExtensions - /// - static class SqliteExtensions - { - /// - /// Determines whether the specified conn is open. - /// - /// The conn. - /// true if the specified conn is open; otherwise, false. - public static bool IsOpen(this IDbConnection conn) - { - return conn.State == ConnectionState.Open; - } - - public static IDataParameter GetParameter(this IDbCommand cmd, int index) - { - return (IDataParameter)cmd.Parameters[index]; - } - - public static IDataParameter Add(this IDataParameterCollection paramCollection, IDbCommand cmd, string name, DbType type) - { - var param = cmd.CreateParameter(); - - param.ParameterName = name; - param.DbType = type; - - paramCollection.Add(param); - - return param; - } - - public static IDataParameter Add(this IDataParameterCollection paramCollection, IDbCommand cmd, string name) - { - var param = cmd.CreateParameter(); - - param.ParameterName = name; - - paramCollection.Add(param); - - return param; - } - - - /// - /// Gets a stream from a DataReader at a given ordinal - /// - /// The reader. - /// The ordinal. - /// Stream. - /// reader - public static Stream GetMemoryStream(this IDataReader reader, int ordinal) - { - if (reader == null) - { - throw new ArgumentNullException("reader"); - } - - var memoryStream = new MemoryStream(); - var num = 0L; - var array = new byte[4096]; - long bytes; - do - { - bytes = reader.GetBytes(ordinal, num, array, 0, array.Length); - memoryStream.Write(array, 0, (int)bytes); - num += bytes; - } - while (bytes > 0L); - memoryStream.Position = 0; - return memoryStream; - } - - /// - /// Runs the queries. - /// - /// The connection. - /// The queries. - /// The logger. - /// true if XXXX, false otherwise - /// queries - public static void RunQueries(this IDbConnection connection, string[] queries, ILogger logger) - { - if (queries == null) - { - throw new ArgumentNullException("queries"); - } - - using (var tran = connection.BeginTransaction()) - { - try - { - using (var cmd = connection.CreateCommand()) - { - foreach (var query in queries) - { - cmd.Transaction = tran; - cmd.CommandText = query; - cmd.ExecuteNonQuery(); - } - } - - tran.Commit(); - } - catch (Exception e) - { - logger.ErrorException("Error running queries", e); - tran.Rollback(); - throw; - } - } - } - - /// - /// Connects to db. - /// - /// The db path. - /// The logger. - /// Task{IDbConnection}. - /// dbPath - public static async Task ConnectToDb(string dbPath, ILogger logger) - { - if (string.IsNullOrEmpty(dbPath)) - { - throw new ArgumentNullException("dbPath"); - } - - logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, dbPath); - - var connectionstr = new SQLiteConnectionStringBuilder - { - PageSize = 4096, - CacheSize = 2000, - SyncMode = SynchronizationModes.Full, - DataSource = dbPath, - JournalMode = SQLiteJournalModeEnum.Wal - }; - - var connection = new SQLiteConnection(connectionstr.ConnectionString); - - await connection.OpenAsync().ConfigureAwait(false); - - return connection; - } - - public static void Attach(IDbConnection db, string path, string alias) - { - using (var cmd = db.CreateCommand()) - { - cmd.CommandText = string.Format("attach '{0}' as {1};", path, alias); - cmd.ExecuteNonQuery(); - } - } - - /// - /// Serializes to bytes. - /// - /// The json. - /// The obj. - /// System.Byte[][]. - /// obj - public static byte[] SerializeToBytes(this IJsonSerializer json, object obj) - { - if (obj == null) - { - throw new ArgumentNullException("obj"); - } - - using (var stream = new MemoryStream()) - { - json.SerializeToStream(obj, stream); - return stream.ToArray(); - } - } - - public static void AddColumn(this IDbConnection connection, ILogger logger, string table, string columnName, string type) - { - using (var cmd = connection.CreateCommand()) - { - cmd.CommandText = "PRAGMA table_info(" + table + ")"; - - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) - { - while (reader.Read()) - { - if (!reader.IsDBNull(1)) - { - var name = reader.GetString(1); - - if (string.Equals(name, columnName, StringComparison.OrdinalIgnoreCase)) - { - return; - } - } - } - } - } - - var builder = new StringBuilder(); - - builder.AppendLine("alter table " + table); - builder.AppendLine("add column " + columnName + " " + type); - - connection.RunQueries(new[] { builder.ToString() }, logger); - } - } -} \ No newline at end of file diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs index 2d5aad04df..037776997e 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteFileOrganizationRepository.cs @@ -35,11 +35,11 @@ namespace MediaBrowser.Server.Implementations.Persistence /// Opens the connection to the database /// /// Task. - public async Task Initialize() + public async Task Initialize(IDbConnector dbConnector) { var dbFile = Path.Combine(_appPaths.DataPath, "fileorganization.db"); - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); string[] queries = { diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteProviderInfoRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteProviderInfoRepository.cs index dbceda727b..40d5c9586e 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteProviderInfoRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteProviderInfoRepository.cs @@ -39,11 +39,11 @@ namespace MediaBrowser.Server.Implementations.Persistence /// Opens the connection to the database /// /// Task. - public async Task Initialize() + public async Task Initialize(IDbConnector dbConnector) { var dbFile = Path.Combine(_appPaths.DataPath, "refreshinfo.db"); - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); string[] queries = { diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs index 63c41c71fe..33a2b11877 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs @@ -37,11 +37,11 @@ namespace MediaBrowser.Server.Implementations.Persistence /// Opens the connection to the database /// /// Task. - public async Task Initialize() + public async Task Initialize(IDbConnector dbConnector) { var dbFile = Path.Combine(_appPaths.DataPath, "userdata_v2.db"); - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); string[] queries = { diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs index 9bd7e47f39..f7ca39a548 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserRepository.cs @@ -43,12 +43,12 @@ namespace MediaBrowser.Server.Implementations.Persistence /// Opens the connection to the database /// /// Task. - public async Task Initialize() + public async Task Initialize(IDbConnector dbConnector) { var dbFile = Path.Combine(_appPaths.DataPath, "users.db"); - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); - + _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); + string[] queries = { "create table if not exists users (guid GUID primary key, data BLOB)", diff --git a/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs b/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs index b932f0cac4..e8d9814ecb 100644 --- a/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs +++ b/MediaBrowser.Server.Implementations/Security/AuthenticationRepository.cs @@ -27,11 +27,11 @@ namespace MediaBrowser.Server.Implementations.Security _appPaths = appPaths; } - public async Task Initialize() + public async Task Initialize(IDbConnector dbConnector) { var dbFile = Path.Combine(_appPaths.DataPath, "authentication.db"); - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); string[] queries = { diff --git a/MediaBrowser.Server.Implementations/Social/SharingRepository.cs b/MediaBrowser.Server.Implementations/Social/SharingRepository.cs index d6d7f021a3..317743eb1b 100644 --- a/MediaBrowser.Server.Implementations/Social/SharingRepository.cs +++ b/MediaBrowser.Server.Implementations/Social/SharingRepository.cs @@ -26,11 +26,11 @@ namespace MediaBrowser.Server.Implementations.Social /// Opens the connection to the database /// /// Task. - public async Task Initialize() + public async Task Initialize(IDbConnector dbConnector) { var dbFile = Path.Combine(_appPaths.DataPath, "shares.db"); - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); string[] queries = { diff --git a/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs b/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs index 965c081ebd..6d31663b97 100644 --- a/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs +++ b/MediaBrowser.Server.Implementations/Sync/SyncRepository.cs @@ -39,11 +39,11 @@ namespace MediaBrowser.Server.Implementations.Sync _appPaths = appPaths; } - public async Task Initialize() + public async Task Initialize(IDbConnector dbConnector) { var dbFile = Path.Combine(_appPaths.DataPath, "sync14.db"); - _connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false); + _connection = await dbConnector.Connect(dbFile).ConfigureAwait(false); string[] queries = { -- cgit v1.2.3