diff options
Diffstat (limited to 'MediaBrowser.Controller/Persistence/SQLite')
6 files changed, 1054 insertions, 0 deletions
diff --git a/MediaBrowser.Controller/Persistence/SQLite/SQLiteDisplayPreferencesRepository.cs b/MediaBrowser.Controller/Persistence/SQLite/SQLiteDisplayPreferencesRepository.cs new file mode 100644 index 000000000..db1535b34 --- /dev/null +++ b/MediaBrowser.Controller/Persistence/SQLite/SQLiteDisplayPreferencesRepository.cs @@ -0,0 +1,139 @@ +using MediaBrowser.Controller.Entities; +using MediaBrowser.Model.Entities; +using System; +using System.Collections.Generic; +using System.ComponentModel.Composition; +using System.Data; +using System.IO; +using System.Threading; +using System.Threading.Tasks; + +namespace MediaBrowser.Controller.Persistence.SQLite +{ + /// <summary> + /// Class SQLiteDisplayPreferencesRepository + /// </summary> + [Export(typeof(IDisplayPreferencesRepository))] + class SQLiteDisplayPreferencesRepository : SqliteRepository, IDisplayPreferencesRepository + { + /// <summary> + /// The repository name + /// </summary> + public const string RepositoryName = "SQLite"; + + /// <summary> + /// Gets the name of the repository + /// </summary> + /// <value>The name.</value> + public string Name + { + get + { + return RepositoryName; + } + } + + /// <summary> + /// Opens the connection to the database + /// </summary> + /// <returns>Task.</returns> + public async Task Initialize() + { + var dbFile = Path.Combine(Kernel.Instance.ApplicationPaths.DataPath, "displaypreferences.db"); + + await ConnectToDB(dbFile).ConfigureAwait(false); + + string[] queries = { + + "create table if not exists display_prefs (item_id GUID, user_id GUID, data BLOB)", + "create unique index if not exists idx_display_prefs on display_prefs (item_id, user_id)", + "create table if not exists schema_version (table_name primary key, version)", + //pragmas + "pragma temp_store = memory" + }; + + RunQueries(queries); + } + + /// <summary> + /// Save the display preferences associated with an item in the repo + /// </summary> + /// <param name="item">The item.</param> + /// <param name="cancellationToken">The cancellation token.</param> + /// <returns>Task.</returns> + /// <exception cref="System.ArgumentNullException">item</exception> + public Task SaveDisplayPrefs(Folder item, CancellationToken cancellationToken) + { + if (item == null) + { + throw new ArgumentNullException("item"); + } + + if (cancellationToken == null) + { + throw new ArgumentNullException("cancellationToken"); + } + + cancellationToken.ThrowIfCancellationRequested(); + + return Task.Run(() => + { + var cmd = connection.CreateCommand(); + + cmd.CommandText = "delete from display_prefs where item_id = @guid"; + cmd.AddParam("@guid", item.DisplayPrefsId); + + QueueCommand(cmd); + + if (item.DisplayPrefs != null) + { + foreach (var data in item.DisplayPrefs) + { + cmd = connection.CreateCommand(); + cmd.CommandText = "insert into display_prefs (item_id, user_id, data) values (@1, @2, @3)"; + cmd.AddParam("@1", item.DisplayPrefsId); + cmd.AddParam("@2", data.UserId); + + cmd.AddParam("@3", Kernel.Instance.ProtobufSerializer.SerializeToBytes(data)); + + QueueCommand(cmd); + } + } + }); + } + + /// <summary> + /// Gets display preferences for an item + /// </summary> + /// <param name="item">The item.</param> + /// <returns>IEnumerable{DisplayPreferences}.</returns> + /// <exception cref="System.ArgumentNullException"></exception> + public IEnumerable<DisplayPreferences> RetrieveDisplayPrefs(Folder item) + { + if (item == null) + { + throw new ArgumentNullException("item"); + } + + var cmd = connection.CreateCommand(); + cmd.CommandText = "select data from display_prefs where item_id = @guid"; + var guidParam = cmd.Parameters.Add("@guid", DbType.Guid); + guidParam.Value = item.DisplayPrefsId; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + using (var stream = GetStream(reader, 0)) + { + var data = Kernel.Instance.ProtobufSerializer.DeserializeFromStream<DisplayPreferences>(stream); + if (data != null) + { + yield return data; + } + } + } + } + } + } +} diff --git a/MediaBrowser.Controller/Persistence/SQLite/SQLiteExtensions.cs b/MediaBrowser.Controller/Persistence/SQLite/SQLiteExtensions.cs new file mode 100644 index 000000000..f1ed77492 --- /dev/null +++ b/MediaBrowser.Controller/Persistence/SQLite/SQLiteExtensions.cs @@ -0,0 +1,61 @@ +using System; +using System.Data; +using System.Data.SQLite; + +namespace MediaBrowser.Controller.Persistence.SQLite +{ + /// <summary> + /// Class SQLiteExtensions + /// </summary> + static class SQLiteExtensions + { + /// <summary> + /// Adds the param. + /// </summary> + /// <param name="cmd">The CMD.</param> + /// <param name="param">The param.</param> + /// <returns>SQLiteParameter.</returns> + /// <exception cref="System.ArgumentNullException"></exception> + public static SQLiteParameter AddParam(this SQLiteCommand cmd, string param) + { + if (string.IsNullOrEmpty(param)) + { + throw new ArgumentNullException(); + } + + var sqliteParam = new SQLiteParameter(param); + cmd.Parameters.Add(sqliteParam); + return sqliteParam; + } + + /// <summary> + /// Adds the param. + /// </summary> + /// <param name="cmd">The CMD.</param> + /// <param name="param">The param.</param> + /// <param name="data">The data.</param> + /// <returns>SQLiteParameter.</returns> + /// <exception cref="System.ArgumentNullException"></exception> + public static SQLiteParameter AddParam(this SQLiteCommand cmd, string param, object data) + { + if (string.IsNullOrEmpty(param)) + { + throw new ArgumentNullException(); + } + + var sqliteParam = AddParam(cmd, param); + sqliteParam.Value = data; + return sqliteParam; + } + + /// <summary> + /// Determines whether the specified conn is open. + /// </summary> + /// <param name="conn">The conn.</param> + /// <returns><c>true</c> if the specified conn is open; otherwise, <c>false</c>.</returns> + public static bool IsOpen(this SQLiteConnection conn) + { + return conn.State == ConnectionState.Open; + } + } +} diff --git a/MediaBrowser.Controller/Persistence/SQLite/SQLiteItemRepository.cs b/MediaBrowser.Controller/Persistence/SQLite/SQLiteItemRepository.cs new file mode 100644 index 000000000..08527f9c1 --- /dev/null +++ b/MediaBrowser.Controller/Persistence/SQLite/SQLiteItemRepository.cs @@ -0,0 +1,268 @@ +using MediaBrowser.Common.Serialization; +using MediaBrowser.Controller.Entities; +using System; +using System.Collections.Generic; +using System.ComponentModel.Composition; +using System.Data; +using System.IO; +using System.Threading; +using System.Threading.Tasks; + +namespace MediaBrowser.Controller.Persistence.SQLite +{ + /// <summary> + /// Class SQLiteItemRepository + /// </summary> + [Export(typeof(IItemRepository))] + public class SQLiteItemRepository : SqliteRepository, IItemRepository + { + /// <summary> + /// The _type mapper + /// </summary> + private readonly TypeMapper _typeMapper = new TypeMapper(); + + /// <summary> + /// The repository name + /// </summary> + public const string RepositoryName = "SQLite"; + + /// <summary> + /// Gets the name of the repository + /// </summary> + /// <value>The name.</value> + public string Name + { + get + { + return RepositoryName; + } + } + + /// <summary> + /// Opens the connection to the database + /// </summary> + /// <returns>Task.</returns> + public async Task Initialize() + { + var dbFile = Path.Combine(Kernel.Instance.ApplicationPaths.DataPath, "library.db"); + + await ConnectToDB(dbFile).ConfigureAwait(false); + + string[] queries = { + + "create table if not exists items (guid GUID primary key, obj_type, data BLOB)", + "create index if not exists idx_items on items(guid)", + "create table if not exists children (guid GUID, child GUID)", + "create unique index if not exists idx_children on children(guid, child)", + "create table if not exists schema_version (table_name primary key, version)", + //triggers + TriggerSql, + //pragmas + "pragma temp_store = memory" + }; + + RunQueries(queries); + } + + //cascade delete triggers + /// <summary> + /// The trigger SQL + /// </summary> + protected string TriggerSql = + @"CREATE TRIGGER if not exists delete_item + AFTER DELETE + ON items + FOR EACH ROW + BEGIN + DELETE FROM children WHERE children.guid = old.child; + DELETE FROM children WHERE children.child = old.child; + END"; + + /// <summary> + /// Save a standard item in the repo + /// </summary> + /// <param name="item">The item.</param> + /// <param name="cancellationToken">The cancellation token.</param> + /// <returns>Task.</returns> + /// <exception cref="System.ArgumentNullException">item</exception> + public Task SaveItem(BaseItem item, CancellationToken cancellationToken) + { + if (item == null) + { + throw new ArgumentNullException("item"); + } + + if (cancellationToken == null) + { + throw new ArgumentNullException("cancellationToken"); + } + + cancellationToken.ThrowIfCancellationRequested(); + + return Task.Run(() => + { + var serialized = JsonSerializer.SerializeToBytes(item); + + cancellationToken.ThrowIfCancellationRequested(); + + var cmd = connection.CreateCommand(); + cmd.CommandText = "replace into items (guid, obj_type, data) values (@1, @2, @3)"; + cmd.AddParam("@1", item.Id); + cmd.AddParam("@2", item.GetType().FullName); + cmd.AddParam("@3", serialized); + QueueCommand(cmd); + }); + } + + /// <summary> + /// Retrieve a standard item from the repo + /// </summary> + /// <param name="id">The id.</param> + /// <returns>BaseItem.</returns> + /// <exception cref="System.ArgumentException"></exception> + public BaseItem RetrieveItem(Guid id) + { + if (id == Guid.Empty) + { + throw new ArgumentException(); + } + + return RetrieveItemInternal(id); + } + + /// <summary> + /// Internal retrieve from items or users table + /// </summary> + /// <param name="id">The id.</param> + /// <returns>BaseItem.</returns> + /// <exception cref="System.ArgumentException"></exception> + protected BaseItem RetrieveItemInternal(Guid id) + { + if (id == Guid.Empty) + { + throw new ArgumentException(); + } + + var cmd = connection.CreateCommand(); + cmd.CommandText = "select obj_type,data from items where guid = @guid"; + var guidParam = cmd.Parameters.Add("@guid", DbType.Guid); + guidParam.Value = id; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + { + if (reader.Read()) + { + var type = reader.GetString(0); + using (var stream = GetStream(reader, 1)) + { + var itemType = _typeMapper.GetType(type); + + if (itemType == null) + { + Logger.Error("Cannot find type {0}. Probably belongs to plug-in that is no longer loaded.", type); + return null; + } + + var item = JsonSerializer.DeserializeFromStream(stream, itemType); + return item as BaseItem; + } + } + } + return null; + } + + /// <summary> + /// Retrieve all the children of the given folder + /// </summary> + /// <param name="parent">The parent.</param> + /// <returns>IEnumerable{BaseItem}.</returns> + /// <exception cref="System.ArgumentNullException"></exception> + public IEnumerable<BaseItem> RetrieveChildren(Folder parent) + { + if (parent == null) + { + throw new ArgumentNullException(); + } + + var cmd = connection.CreateCommand(); + cmd.CommandText = "select obj_type,data from items where guid in (select child from children where guid = @guid)"; + var guidParam = cmd.Parameters.Add("@guid", DbType.Guid); + guidParam.Value = parent.Id; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + var type = reader.GetString(0); + + using (var stream = GetStream(reader, 1)) + { + var itemType = _typeMapper.GetType(type); + if (itemType == null) + { + Logger.Error("Cannot find type {0}. Probably belongs to plug-in that is no longer loaded.",type); + continue; + } + var item = JsonSerializer.DeserializeFromStream(stream, itemType) as BaseItem; + if (item != null) + { + item.Parent = parent; + yield return item; + } + } + } + } + } + + /// <summary> + /// Save references to all the children for the given folder + /// (Doesn't actually save the child entities) + /// </summary> + /// <param name="id">The id.</param> + /// <param name="children">The children.</param> + /// <param name="cancellationToken">The cancellation token.</param> + /// <returns>Task.</returns> + /// <exception cref="System.ArgumentNullException">id</exception> + public Task SaveChildren(Guid id, IEnumerable<BaseItem> children, CancellationToken cancellationToken) + { + if (id == Guid.Empty) + { + throw new ArgumentNullException("id"); + } + + if (children == null) + { + throw new ArgumentNullException("children"); + } + + if (cancellationToken == null) + { + throw new ArgumentNullException("cancellationToken"); + } + + cancellationToken.ThrowIfCancellationRequested(); + + return Task.Run(() => + { + var cmd = connection.CreateCommand(); + + cmd.CommandText = "delete from children where guid = @guid"; + cmd.AddParam("@guid", id); + + QueueCommand(cmd); + + foreach (var child in children) + { + var guid = child.Id; + cmd = connection.CreateCommand(); + cmd.AddParam("@guid", id); + cmd.CommandText = "replace into children (guid, child) values (@guid, @child)"; + var childParam = cmd.Parameters.Add("@child", DbType.Guid); + + childParam.Value = guid; + QueueCommand(cmd); + } + }); + } + } +} diff --git a/MediaBrowser.Controller/Persistence/SQLite/SQLiteRepository.cs b/MediaBrowser.Controller/Persistence/SQLite/SQLiteRepository.cs new file mode 100644 index 000000000..5cf57541b --- /dev/null +++ b/MediaBrowser.Controller/Persistence/SQLite/SQLiteRepository.cs @@ -0,0 +1,301 @@ +using MediaBrowser.Common.Logging; +using MediaBrowser.Model.Logging; +using System; +using System.Collections.Concurrent; +using System.Data; +using System.Data.Common; +using System.Data.SQLite; +using System.IO; +using System.Threading; +using System.Threading.Tasks; + +namespace MediaBrowser.Controller.Persistence.SQLite +{ + /// <summary> + /// Class SqliteRepository + /// </summary> + public abstract class SqliteRepository : IDisposable + { + /// <summary> + /// The db file name + /// </summary> + protected string dbFileName; + /// <summary> + /// The connection + /// </summary> + protected SQLiteConnection connection; + /// <summary> + /// The delayed commands + /// </summary> + protected ConcurrentQueue<SQLiteCommand> delayedCommands = new ConcurrentQueue<SQLiteCommand>(); + /// <summary> + /// The flush interval + /// </summary> + private const int FlushInterval = 5000; + + /// <summary> + /// The flush timer + /// </summary> + private Timer FlushTimer; + + protected ILogger Logger { get; private set; } + + /// <summary> + /// Connects to DB. + /// </summary> + /// <param name="dbPath">The db path.</param> + /// <returns>Task{System.Boolean}.</returns> + /// <exception cref="System.ArgumentNullException"></exception> + protected async Task ConnectToDB(string dbPath) + { + if (string.IsNullOrEmpty(dbPath)) + { + throw new ArgumentNullException("dbPath"); + } + + Logger = LogManager.GetLogger(GetType().Name); + + dbFileName = dbPath; + var connectionstr = new SQLiteConnectionStringBuilder + { + PageSize = 4096, + CacheSize = 40960, + SyncMode = SynchronizationModes.Off, + DataSource = dbPath, + JournalMode = SQLiteJournalModeEnum.Memory + }; + + connection = new SQLiteConnection(connectionstr.ConnectionString); + + await connection.OpenAsync().ConfigureAwait(false); + + // Run once + FlushTimer = new Timer(Flush, null, TimeSpan.FromMilliseconds(FlushInterval), TimeSpan.FromMilliseconds(-1)); + } + + /// <summary> + /// Runs the queries. + /// </summary> + /// <param name="queries">The queries.</param> + /// <returns><c>true</c> if XXXX, <c>false</c> otherwise</returns> + /// <exception cref="System.ArgumentNullException"></exception> + protected void RunQueries(string[] queries) + { + if (queries == null) + { + throw new ArgumentNullException("queries"); + } + + using (var tran = connection.BeginTransaction()) + { + try + { + 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; + } + } + } + + /// <summary> + /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. + /// </summary> + public void Dispose() + { + Dispose(true); + GC.SuppressFinalize(this); + } + + /// <summary> + /// Releases unmanaged and - optionally - managed resources. + /// </summary> + /// <param name="dispose"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param> + protected virtual void Dispose(bool dispose) + { + if (dispose) + { + Logger.Info("Disposing " + GetType().Name); + + try + { + // If we're not already flushing, do it now + if (!IsFlushing) + { + Flush(null); + } + + // Don't dispose in the middle of a flush + while (IsFlushing) + { + Thread.Sleep(50); + } + + if (FlushTimer != null) + { + FlushTimer.Dispose(); + FlushTimer = null; + } + + if (connection.IsOpen()) + { + connection.Close(); + } + + connection.Dispose(); + } + catch (Exception ex) + { + Logger.ErrorException("Error disposing database", ex); + } + } + } + + /// <summary> + /// Queues the command. + /// </summary> + /// <param name="cmd">The CMD.</param> + /// <exception cref="System.ArgumentNullException"></exception> + protected void QueueCommand(SQLiteCommand cmd) + { + if (cmd == null) + { + throw new ArgumentNullException("cmd"); + } + + delayedCommands.Enqueue(cmd); + } + + /// <summary> + /// The is flushing + /// </summary> + private bool IsFlushing; + + /// <summary> + /// Flushes the specified sender. + /// </summary> + /// <param name="sender">The sender.</param> + private void Flush(object sender) + { + // Cannot call Count on a ConcurrentQueue since it's an O(n) operation + // Use IsEmpty instead + if (delayedCommands.IsEmpty) + { + FlushTimer.Change(TimeSpan.FromMilliseconds(FlushInterval), TimeSpan.FromMilliseconds(-1)); + return; + } + + if (IsFlushing) + { + return; + } + + IsFlushing = true; + var numCommands = 0; + + using (var tran = connection.BeginTransaction()) + { + try + { + while (!delayedCommands.IsEmpty) + { + SQLiteCommand command; + + delayedCommands.TryDequeue(out command); + + command.Connection = connection; + command.Transaction = tran; + + command.ExecuteNonQuery(); + numCommands++; + } + + tran.Commit(); + } + catch (Exception e) + { + Logger.ErrorException("Failed to commit transaction.", e); + tran.Rollback(); + } + } + + Logger.Info("SQL Delayed writer executed " + numCommands + " commands"); + + FlushTimer.Change(TimeSpan.FromMilliseconds(FlushInterval), TimeSpan.FromMilliseconds(-1)); + IsFlushing = false; + } + + /// <summary> + /// Executes the command. + /// </summary> + /// <param name="cmd">The CMD.</param> + /// <returns>Task.</returns> + /// <exception cref="System.ArgumentNullException"></exception> + public async Task ExecuteCommand(DbCommand cmd) + { + if (cmd == null) + { + throw new ArgumentNullException("cmd"); + } + + using (var tran = connection.BeginTransaction()) + { + try + { + cmd.Connection = connection; + cmd.Transaction = tran; + + await cmd.ExecuteNonQueryAsync().ConfigureAwait(false); + + tran.Commit(); + } + catch (Exception e) + { + Logger.ErrorException("Failed to commit transaction.", e); + tran.Rollback(); + } + } + } + + /// <summary> + /// Gets a stream from a DataReader at a given ordinal + /// </summary> + /// <param name="reader">The reader.</param> + /// <param name="ordinal">The ordinal.</param> + /// <returns>Stream.</returns> + /// <exception cref="System.ArgumentNullException"></exception> + protected static Stream GetStream(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; + } + } +} diff --git a/MediaBrowser.Controller/Persistence/SQLite/SQLiteUserDataRepository.cs b/MediaBrowser.Controller/Persistence/SQLite/SQLiteUserDataRepository.cs new file mode 100644 index 000000000..a027e8475 --- /dev/null +++ b/MediaBrowser.Controller/Persistence/SQLite/SQLiteUserDataRepository.cs @@ -0,0 +1,138 @@ +using MediaBrowser.Controller.Entities; +using System; +using System.Collections.Generic; +using System.ComponentModel.Composition; +using System.Data; +using System.IO; +using System.Threading; +using System.Threading.Tasks; + +namespace MediaBrowser.Controller.Persistence.SQLite +{ + /// <summary> + /// Class SQLiteUserDataRepository + /// </summary> + [Export(typeof(IUserDataRepository))] + public class SQLiteUserDataRepository : SqliteRepository, IUserDataRepository + { + /// <summary> + /// The repository name + /// </summary> + public const string RepositoryName = "SQLite"; + + /// <summary> + /// Gets the name of the repository + /// </summary> + /// <value>The name.</value> + public string Name + { + get + { + return RepositoryName; + } + } + + /// <summary> + /// Opens the connection to the database + /// </summary> + /// <returns>Task.</returns> + public async Task Initialize() + { + var dbFile = Path.Combine(Kernel.Instance.ApplicationPaths.DataPath, "userdata.db"); + + await ConnectToDB(dbFile).ConfigureAwait(false); + + string[] queries = { + + "create table if not exists user_data (item_id GUID, user_id GUID, data BLOB)", + "create unique index if not exists idx_user_data on user_data (item_id, user_id)", + "create table if not exists schema_version (table_name primary key, version)", + //pragmas + "pragma temp_store = memory" + }; + + RunQueries(queries); + } + + /// <summary> + /// Save the user specific data associated with an item in the repo + /// </summary> + /// <param name="item">The item.</param> + /// <param name="cancellationToken">The cancellation token.</param> + /// <returns>Task.</returns> + /// <exception cref="System.ArgumentNullException">item</exception> + public Task SaveUserData(BaseItem item, CancellationToken cancellationToken) + { + if (item == null) + { + throw new ArgumentNullException("item"); + } + + if (cancellationToken == null) + { + throw new ArgumentNullException("cancellationToken"); + } + + return Task.Run(() => + { + cancellationToken.ThrowIfCancellationRequested(); + + var cmd = connection.CreateCommand(); + + cmd.CommandText = "delete from user_data where item_id = @guid"; + cmd.AddParam("@guid", item.UserDataId); + + QueueCommand(cmd); + + if (item.UserData != null) + { + foreach (var data in item.UserData) + { + cmd = connection.CreateCommand(); + cmd.CommandText = "insert into user_data (item_id, user_id, data) values (@1, @2, @3)"; + cmd.AddParam("@1", item.UserDataId); + cmd.AddParam("@2", data.UserId); + + cmd.AddParam("@3", Kernel.Instance.ProtobufSerializer.SerializeToBytes(data)); + + QueueCommand(cmd); + } + } + }); + } + + /// <summary> + /// Gets user data for an item + /// </summary> + /// <param name="item">The item.</param> + /// <returns>IEnumerable{UserItemData}.</returns> + /// <exception cref="System.ArgumentNullException"></exception> + public IEnumerable<UserItemData> RetrieveUserData(BaseItem item) + { + if (item == null) + { + throw new ArgumentNullException("item"); + } + + var cmd = connection.CreateCommand(); + cmd.CommandText = "select data from user_data where item_id = @guid"; + var guidParam = cmd.Parameters.Add("@guid", DbType.Guid); + guidParam.Value = item.UserDataId; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + using (var stream = GetStream(reader, 0)) + { + var data = Kernel.Instance.ProtobufSerializer.DeserializeFromStream<UserItemData>(stream); + if (data != null) + { + yield return data; + } + } + } + } + } + } +} diff --git a/MediaBrowser.Controller/Persistence/SQLite/SQLiteUserRepository.cs b/MediaBrowser.Controller/Persistence/SQLite/SQLiteUserRepository.cs new file mode 100644 index 000000000..9fe5e5624 --- /dev/null +++ b/MediaBrowser.Controller/Persistence/SQLite/SQLiteUserRepository.cs @@ -0,0 +1,147 @@ +using System.Threading; +using MediaBrowser.Common.Serialization; +using MediaBrowser.Controller.Entities; +using System; +using System.Collections.Generic; +using System.ComponentModel.Composition; +using System.Data; +using System.IO; +using System.Threading.Tasks; + +namespace MediaBrowser.Controller.Persistence.SQLite +{ + /// <summary> + /// Class SQLiteUserRepository + /// </summary> + [Export(typeof(IUserRepository))] + public class SQLiteUserRepository : SqliteRepository, IUserRepository + { + /// <summary> + /// The repository name + /// </summary> + public const string RepositoryName = "SQLite"; + + /// <summary> + /// Gets the name of the repository + /// </summary> + /// <value>The name.</value> + public string Name + { + get + { + return RepositoryName; + } + } + + /// <summary> + /// Opens the connection to the database + /// </summary> + /// <returns>Task.</returns> + public async Task Initialize() + { + var dbFile = Path.Combine(Kernel.Instance.ApplicationPaths.DataPath, "users.db"); + + await ConnectToDB(dbFile).ConfigureAwait(false); + + string[] queries = { + + "create table if not exists users (guid GUID primary key, data BLOB)", + "create index if not exists idx_users on users(guid)", + "create table if not exists schema_version (table_name primary key, version)", + //pragmas + "pragma temp_store = memory" + }; + + RunQueries(queries); + } + + /// <summary> + /// Save a user in the repo + /// </summary> + /// <param name="user">The user.</param> + /// <param name="cancellationToken">The cancellation token.</param> + /// <returns>Task.</returns> + /// <exception cref="System.ArgumentNullException">user</exception> + public Task SaveUser(User user, CancellationToken cancellationToken) + { + if (user == null) + { + throw new ArgumentNullException("user"); + } + + if (cancellationToken == null) + { + throw new ArgumentNullException("cancellationToken"); + } + + return Task.Run(() => + { + cancellationToken.ThrowIfCancellationRequested(); + + var serialized = JsonSerializer.SerializeToBytes(user); + + cancellationToken.ThrowIfCancellationRequested(); + + var cmd = connection.CreateCommand(); + cmd.CommandText = "replace into users (guid, data) values (@1, @2)"; + cmd.AddParam("@1", user.Id); + cmd.AddParam("@2", serialized); + QueueCommand(cmd); + }); + } + + /// <summary> + /// Retrieve all users from the database + /// </summary> + /// <returns>IEnumerable{User}.</returns> + public IEnumerable<User> RetrieveAllUsers() + { + var cmd = connection.CreateCommand(); + cmd.CommandText = "select data from users"; + + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) + { + using (var stream = GetStream(reader, 0)) + { + var user = JsonSerializer.DeserializeFromStream<User>(stream); + yield return user; + } + } + } + } + + /// <summary> + /// Deletes the user. + /// </summary> + /// <param name="user">The user.</param> + /// <param name="cancellationToken">The cancellation token.</param> + /// <returns>Task.</returns> + /// <exception cref="System.ArgumentNullException">user</exception> + public Task DeleteUser(User user, CancellationToken cancellationToken) + { + if (user == null) + { + throw new ArgumentNullException("user"); + } + + if (cancellationToken == null) + { + throw new ArgumentNullException("cancellationToken"); + } + + return Task.Run(() => + { + cancellationToken.ThrowIfCancellationRequested(); + + var cmd = connection.CreateCommand(); + cmd.CommandText = "delete from users where guid=@guid"; + var guidParam = cmd.Parameters.Add("@guid", DbType.Guid); + guidParam.Value = user.Id; + + return ExecuteCommand(cmd); + }); + } + } +} |