diff options
Diffstat (limited to 'MediaBrowser.Server.Sqlite')
| -rw-r--r-- | MediaBrowser.Server.Sqlite/MediaBrowser.Server.Sqlite.csproj | 99 | ||||
| -rw-r--r-- | MediaBrowser.Server.Sqlite/Properties/AssemblyInfo.cs | 36 | ||||
| -rw-r--r-- | MediaBrowser.Server.Sqlite/SQLiteDisplayPreferencesRepository.cs | 141 | ||||
| -rw-r--r-- | MediaBrowser.Server.Sqlite/SQLiteExtensions.cs | 61 | ||||
| -rw-r--r-- | MediaBrowser.Server.Sqlite/SQLiteItemRepository.cs | 270 | ||||
| -rw-r--r-- | MediaBrowser.Server.Sqlite/SQLiteRepository.cs | 301 | ||||
| -rw-r--r-- | MediaBrowser.Server.Sqlite/SQLiteUserDataRepository.cs | 140 | ||||
| -rw-r--r-- | MediaBrowser.Server.Sqlite/SQLiteUserRepository.cs | 149 | ||||
| -rw-r--r-- | MediaBrowser.Server.Sqlite/packages.config | 4 |
9 files changed, 1201 insertions, 0 deletions
diff --git a/MediaBrowser.Server.Sqlite/MediaBrowser.Server.Sqlite.csproj b/MediaBrowser.Server.Sqlite/MediaBrowser.Server.Sqlite.csproj new file mode 100644 index 0000000000..b4e8dba897 --- /dev/null +++ b/MediaBrowser.Server.Sqlite/MediaBrowser.Server.Sqlite.csproj @@ -0,0 +1,99 @@ +<?xml version="1.0" encoding="utf-8"?> +<Project ToolsVersion="4.0" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> + <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" /> + <PropertyGroup> + <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration> + <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform> + <ProjectGuid>{8649ED6B-8504-4D00-BFA5-B8C73CC744DB}</ProjectGuid> + <OutputType>Library</OutputType> + <AppDesignerFolder>Properties</AppDesignerFolder> + <RootNamespace>MediaBrowser.Server.Sqlite</RootNamespace> + <AssemblyName>MediaBrowser.Server.Sqlite</AssemblyName> + <TargetFrameworkVersion>v4.5</TargetFrameworkVersion> + <FileAlignment>512</FileAlignment> + <SolutionDir Condition="$(SolutionDir) == '' Or $(SolutionDir) == '*Undefined*'">..\</SolutionDir> + <RestorePackages>true</RestorePackages> + </PropertyGroup> + <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' "> + <DebugSymbols>true</DebugSymbols> + <DebugType>full</DebugType> + <Optimize>false</Optimize> + <OutputPath>bin\Debug\</OutputPath> + <DefineConstants>DEBUG;TRACE</DefineConstants> + <ErrorReport>prompt</ErrorReport> + <WarningLevel>4</WarningLevel> + </PropertyGroup> + <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' "> + <DebugType>pdbonly</DebugType> + <Optimize>true</Optimize> + <OutputPath>bin\Release\</OutputPath> + <DefineConstants>TRACE</DefineConstants> + <ErrorReport>prompt</ErrorReport> + <WarningLevel>4</WarningLevel> + </PropertyGroup> + <PropertyGroup> + <RunPostBuildEvent>Always</RunPostBuildEvent> + </PropertyGroup> + <ItemGroup> + <Reference Include="System" /> + <Reference Include="System.ComponentModel.Composition" /> + <Reference Include="System.Core" /> + <Reference Include="System.Data.SQLite"> + <HintPath>..\packages\System.Data.SQLite.1.0.84.0\lib\net45\System.Data.SQLite.dll</HintPath> + </Reference> + <Reference Include="System.Data.SQLite.Linq"> + <HintPath>..\packages\System.Data.SQLite.1.0.84.0\lib\net45\System.Data.SQLite.Linq.dll</HintPath> + </Reference> + <Reference Include="System.Xml.Linq" /> + <Reference Include="System.Data.DataSetExtensions" /> + <Reference Include="Microsoft.CSharp" /> + <Reference Include="System.Data" /> + <Reference Include="System.Xml" /> + </ItemGroup> + <ItemGroup> + <Compile Include="Properties\AssemblyInfo.cs" /> + <Compile Include="SQLiteDisplayPreferencesRepository.cs" /> + <Compile Include="SQLiteExtensions.cs" /> + <Compile Include="SQLiteItemRepository.cs" /> + <Compile Include="SQLiteRepository.cs" /> + <Compile Include="SQLiteUserDataRepository.cs" /> + <Compile Include="SQLiteUserRepository.cs" /> + </ItemGroup> + <ItemGroup> + <Content Include="x64\SQLite.Interop.dll"> + <CopyToOutputDirectory>Always</CopyToOutputDirectory> + </Content> + <Content Include="x86\SQLite.Interop.dll"> + <CopyToOutputDirectory>Always</CopyToOutputDirectory> + </Content> + </ItemGroup> + <ItemGroup> + <None Include="packages.config" /> + </ItemGroup> + <ItemGroup> + <ProjectReference Include="..\MediaBrowser.Common\MediaBrowser.Common.csproj"> + <Project>{9142eefa-7570-41e1-bfcc-468bb571af2f}</Project> + <Name>MediaBrowser.Common</Name> + </ProjectReference> + <ProjectReference Include="..\MediaBrowser.Controller\MediaBrowser.Controller.csproj"> + <Project>{17e1f4e6-8abd-4fe5-9ecf-43d4b6087ba2}</Project> + <Name>MediaBrowser.Controller</Name> + </ProjectReference> + <ProjectReference Include="..\MediaBrowser.Model\MediaBrowser.Model.csproj"> + <Project>{7eeeb4bb-f3e8-48fc-b4c5-70f0fff8329b}</Project> + <Name>MediaBrowser.Model</Name> + </ProjectReference> + </ItemGroup> + <Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" /> + <Import Project="$(SolutionDir)\.nuget\nuget.targets" /> + <PropertyGroup> + <PostBuildEvent>xcopy "$(TargetPath)" "$(SolutionDir)\MediaBrowser.ServerApplication\CorePlugins\" /y</PostBuildEvent> + </PropertyGroup> + <!-- To modify your build process, add your task inside one of the targets below and uncomment it. + Other similar extension points exist, see Microsoft.Common.targets. + <Target Name="BeforeBuild"> + </Target> + <Target Name="AfterBuild"> + </Target> + --> +</Project>
\ No newline at end of file diff --git a/MediaBrowser.Server.Sqlite/Properties/AssemblyInfo.cs b/MediaBrowser.Server.Sqlite/Properties/AssemblyInfo.cs new file mode 100644 index 0000000000..905af0e8c2 --- /dev/null +++ b/MediaBrowser.Server.Sqlite/Properties/AssemblyInfo.cs @@ -0,0 +1,36 @@ +using System.Reflection; +using System.Runtime.CompilerServices; +using System.Runtime.InteropServices; + +// General Information about an assembly is controlled through the following +// set of attributes. Change these attribute values to modify the information +// associated with an assembly. +[assembly: AssemblyTitle("MediaBrowser.Server.Sqlite")] +[assembly: AssemblyDescription("")] +[assembly: AssemblyConfiguration("")] +[assembly: AssemblyCompany("")] +[assembly: AssemblyProduct("MediaBrowser.Server.Sqlite")] +[assembly: AssemblyCopyright("Copyright © 2013")] +[assembly: AssemblyTrademark("")] +[assembly: AssemblyCulture("")] + +// Setting ComVisible to false makes the types in this assembly not visible +// to COM components. If you need to access a type in this assembly from +// COM, set the ComVisible attribute to true on that type. +[assembly: ComVisible(false)] + +// The following GUID is for the ID of the typelib if this project is exposed to COM +[assembly: Guid("f46c9f4b-24ed-49e1-be19-4b6242dd8382")] + +// Version information for an assembly consists of the following four values: +// +// Major Version +// Minor Version +// Build Number +// Revision +// +// You can specify all the values or you can default the Build and Revision Numbers +// by using the '*' as shown below: +// [assembly: AssemblyVersion("1.0.*")] +[assembly: AssemblyVersion("1.0.0.0")] +[assembly: AssemblyFileVersion("1.0.0.0")] diff --git a/MediaBrowser.Server.Sqlite/SQLiteDisplayPreferencesRepository.cs b/MediaBrowser.Server.Sqlite/SQLiteDisplayPreferencesRepository.cs new file mode 100644 index 0000000000..be45a82dda --- /dev/null +++ b/MediaBrowser.Server.Sqlite/SQLiteDisplayPreferencesRepository.cs @@ -0,0 +1,141 @@ +using MediaBrowser.Controller; +using MediaBrowser.Controller.Entities; +using MediaBrowser.Controller.Persistence; +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.Server.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.Server.Sqlite/SQLiteExtensions.cs b/MediaBrowser.Server.Sqlite/SQLiteExtensions.cs new file mode 100644 index 0000000000..f9f79a8b75 --- /dev/null +++ b/MediaBrowser.Server.Sqlite/SQLiteExtensions.cs @@ -0,0 +1,61 @@ +using System; +using System.Data; +using System.Data.SQLite; + +namespace MediaBrowser.Server.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.Server.Sqlite/SQLiteItemRepository.cs b/MediaBrowser.Server.Sqlite/SQLiteItemRepository.cs new file mode 100644 index 0000000000..c381de85b4 --- /dev/null +++ b/MediaBrowser.Server.Sqlite/SQLiteItemRepository.cs @@ -0,0 +1,270 @@ +using MediaBrowser.Common.Serialization; +using MediaBrowser.Controller; +using MediaBrowser.Controller.Entities; +using MediaBrowser.Controller.Persistence; +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.Server.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.Server.Sqlite/SQLiteRepository.cs b/MediaBrowser.Server.Sqlite/SQLiteRepository.cs new file mode 100644 index 0000000000..b84b336dc1 --- /dev/null +++ b/MediaBrowser.Server.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.Server.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.Server.Sqlite/SQLiteUserDataRepository.cs b/MediaBrowser.Server.Sqlite/SQLiteUserDataRepository.cs new file mode 100644 index 0000000000..c9d4db3ba7 --- /dev/null +++ b/MediaBrowser.Server.Sqlite/SQLiteUserDataRepository.cs @@ -0,0 +1,140 @@ +using MediaBrowser.Controller; +using MediaBrowser.Controller.Entities; +using MediaBrowser.Controller.Persistence; +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.Server.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.Server.Sqlite/SQLiteUserRepository.cs b/MediaBrowser.Server.Sqlite/SQLiteUserRepository.cs new file mode 100644 index 0000000000..f300f51775 --- /dev/null +++ b/MediaBrowser.Server.Sqlite/SQLiteUserRepository.cs @@ -0,0 +1,149 @@ +using MediaBrowser.Common.Serialization; +using MediaBrowser.Controller; +using MediaBrowser.Controller.Entities; +using MediaBrowser.Controller.Persistence; +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.Server.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); + }); + } + } +} diff --git a/MediaBrowser.Server.Sqlite/packages.config b/MediaBrowser.Server.Sqlite/packages.config new file mode 100644 index 0000000000..1066188147 --- /dev/null +++ b/MediaBrowser.Server.Sqlite/packages.config @@ -0,0 +1,4 @@ +<?xml version="1.0" encoding="utf-8"?> +<packages> + <package id="System.Data.SQLite" version="1.0.84.0" targetFramework="net45" /> +</packages>
\ No newline at end of file |