| | 1 | | #nullable disable |
| | 2 | |
|
| | 3 | | #pragma warning disable CS1591 |
| | 4 | |
|
| | 5 | | using System; |
| | 6 | | using System.Collections.Generic; |
| | 7 | | using System.Threading; |
| | 8 | | using Jellyfin.Extensions; |
| | 9 | | using Microsoft.Data.Sqlite; |
| | 10 | | using Microsoft.Extensions.Logging; |
| | 11 | |
|
| | 12 | | namespace Emby.Server.Implementations.Data |
| | 13 | | { |
| | 14 | | public abstract class BaseSqliteRepository : IDisposable |
| | 15 | | { |
| | 16 | | private bool _disposed = false; |
| 68 | 17 | | private SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1); |
| | 18 | | private SqliteConnection _writeConnection; |
| | 19 | |
|
| | 20 | | /// <summary> |
| | 21 | | /// Initializes a new instance of the <see cref="BaseSqliteRepository"/> class. |
| | 22 | | /// </summary> |
| | 23 | | /// <param name="logger">The logger.</param> |
| | 24 | | protected BaseSqliteRepository(ILogger<BaseSqliteRepository> logger) |
| | 25 | | { |
| | 26 | | Logger = logger; |
| 68 | 27 | | } |
| | 28 | |
|
| | 29 | | /// <summary> |
| | 30 | | /// Gets or sets the path to the DB file. |
| | 31 | | /// </summary> |
| | 32 | | protected string DbFilePath { get; set; } |
| | 33 | |
|
| | 34 | | /// <summary> |
| | 35 | | /// Gets the logger. |
| | 36 | | /// </summary> |
| | 37 | | /// <value>The logger.</value> |
| | 38 | | protected ILogger<BaseSqliteRepository> Logger { get; } |
| | 39 | |
|
| | 40 | | /// <summary> |
| | 41 | | /// Gets the cache size. |
| | 42 | | /// </summary> |
| | 43 | | /// <value>The cache size or null.</value> |
| 24 | 44 | | protected virtual int? CacheSize => null; |
| | 45 | |
|
| | 46 | | /// <summary> |
| | 47 | | /// Gets the locking mode. <see href="https://www.sqlite.org/pragma.html#pragma_locking_mode" />. |
| | 48 | | /// </summary> |
| 1396 | 49 | | protected virtual string LockingMode => "NORMAL"; |
| | 50 | |
|
| | 51 | | /// <summary> |
| | 52 | | /// Gets the journal mode. <see href="https://www.sqlite.org/pragma.html#pragma_journal_mode" />. |
| | 53 | | /// </summary> |
| | 54 | | /// <value>The journal mode.</value> |
| 1396 | 55 | | protected virtual string JournalMode => "WAL"; |
| | 56 | |
|
| | 57 | | /// <summary> |
| | 58 | | /// Gets the journal size limit. <see href="https://www.sqlite.org/pragma.html#pragma_journal_size_limit" />. |
| | 59 | | /// The default (-1) is overridden to prevent unconstrained WAL size, as reported by users. |
| | 60 | | /// </summary> |
| | 61 | | /// <value>The journal size limit.</value> |
| 1396 | 62 | | protected virtual int? JournalSizeLimit => 134_217_728; // 128MiB |
| | 63 | |
|
| | 64 | | /// <summary> |
| | 65 | | /// Gets the page size. |
| | 66 | | /// </summary> |
| | 67 | | /// <value>The page size or null.</value> |
| 698 | 68 | | protected virtual int? PageSize => null; |
| | 69 | |
|
| | 70 | | /// <summary> |
| | 71 | | /// Gets the temp store mode. |
| | 72 | | /// </summary> |
| | 73 | | /// <value>The temp store mode.</value> |
| | 74 | | /// <see cref="TempStoreMode"/> |
| 24 | 75 | | protected virtual TempStoreMode TempStore => TempStoreMode.Memory; |
| | 76 | |
|
| | 77 | | /// <summary> |
| | 78 | | /// Gets the synchronous mode. |
| | 79 | | /// </summary> |
| | 80 | | /// <value>The synchronous mode or null.</value> |
| | 81 | | /// <see cref="SynchronousMode"/> |
| 1396 | 82 | | protected virtual SynchronousMode? Synchronous => SynchronousMode.Normal; |
| | 83 | |
|
| | 84 | | public virtual void Initialize() |
| | 85 | | { |
| | 86 | | // Configuration and pragmas can affect VACUUM so it needs to be last. |
| 44 | 87 | | using (var connection = GetConnection()) |
| | 88 | | { |
| 44 | 89 | | connection.Execute("VACUUM"); |
| 44 | 90 | | } |
| 44 | 91 | | } |
| | 92 | |
|
| | 93 | | protected ManagedConnection GetConnection(bool readOnly = false) |
| | 94 | | { |
| 822 | 95 | | if (!readOnly) |
| | 96 | | { |
| 168 | 97 | | _writeLock.Wait(); |
| 168 | 98 | | if (_writeConnection is not null) |
| | 99 | | { |
| 124 | 100 | | return new ManagedConnection(_writeConnection, _writeLock); |
| | 101 | | } |
| | 102 | |
|
| 44 | 103 | | var writeConnection = new SqliteConnection($"Filename={DbFilePath};Pooling=False"); |
| 44 | 104 | | writeConnection.Open(); |
| | 105 | |
|
| 44 | 106 | | if (CacheSize.HasValue) |
| | 107 | | { |
| 22 | 108 | | writeConnection.Execute("PRAGMA cache_size=" + CacheSize.Value); |
| | 109 | | } |
| | 110 | |
|
| 44 | 111 | | if (!string.IsNullOrWhiteSpace(LockingMode)) |
| | 112 | | { |
| 44 | 113 | | writeConnection.Execute("PRAGMA locking_mode=" + LockingMode); |
| | 114 | | } |
| | 115 | |
|
| 44 | 116 | | if (!string.IsNullOrWhiteSpace(JournalMode)) |
| | 117 | | { |
| 44 | 118 | | writeConnection.Execute("PRAGMA journal_mode=" + JournalMode); |
| | 119 | | } |
| | 120 | |
|
| 44 | 121 | | if (JournalSizeLimit.HasValue) |
| | 122 | | { |
| 44 | 123 | | writeConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); |
| | 124 | | } |
| | 125 | |
|
| 44 | 126 | | if (Synchronous.HasValue) |
| | 127 | | { |
| 44 | 128 | | writeConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); |
| | 129 | | } |
| | 130 | |
|
| 44 | 131 | | if (PageSize.HasValue) |
| | 132 | | { |
| 0 | 133 | | writeConnection.Execute("PRAGMA page_size=" + PageSize.Value); |
| | 134 | | } |
| | 135 | |
|
| 44 | 136 | | writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore); |
| | 137 | |
|
| 44 | 138 | | return new ManagedConnection(_writeConnection = writeConnection, _writeLock); |
| | 139 | | } |
| | 140 | |
|
| 654 | 141 | | var connection = new SqliteConnection($"Filename={DbFilePath};Mode=ReadOnly"); |
| 654 | 142 | | connection.Open(); |
| | 143 | |
|
| 654 | 144 | | if (CacheSize.HasValue) |
| | 145 | | { |
| 652 | 146 | | connection.Execute("PRAGMA cache_size=" + CacheSize.Value); |
| | 147 | | } |
| | 148 | |
|
| 654 | 149 | | if (!string.IsNullOrWhiteSpace(LockingMode)) |
| | 150 | | { |
| 654 | 151 | | connection.Execute("PRAGMA locking_mode=" + LockingMode); |
| | 152 | | } |
| | 153 | |
|
| 654 | 154 | | if (!string.IsNullOrWhiteSpace(JournalMode)) |
| | 155 | | { |
| 654 | 156 | | connection.Execute("PRAGMA journal_mode=" + JournalMode); |
| | 157 | | } |
| | 158 | |
|
| 654 | 159 | | if (JournalSizeLimit.HasValue) |
| | 160 | | { |
| 654 | 161 | | connection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); |
| | 162 | | } |
| | 163 | |
|
| 654 | 164 | | if (Synchronous.HasValue) |
| | 165 | | { |
| 654 | 166 | | connection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); |
| | 167 | | } |
| | 168 | |
|
| 654 | 169 | | if (PageSize.HasValue) |
| | 170 | | { |
| 0 | 171 | | connection.Execute("PRAGMA page_size=" + PageSize.Value); |
| | 172 | | } |
| | 173 | |
|
| 654 | 174 | | connection.Execute("PRAGMA temp_store=" + (int)TempStore); |
| | 175 | |
|
| 654 | 176 | | return new ManagedConnection(connection, null); |
| | 177 | | } |
| | 178 | |
|
| | 179 | | public SqliteCommand PrepareStatement(ManagedConnection connection, string sql) |
| | 180 | | { |
| 828 | 181 | | var command = connection.CreateCommand(); |
| 828 | 182 | | command.CommandText = sql; |
| 828 | 183 | | return command; |
| | 184 | | } |
| | 185 | |
|
| | 186 | | protected bool TableExists(ManagedConnection connection, string name) |
| | 187 | | { |
| 44 | 188 | | using var statement = PrepareStatement(connection, "select DISTINCT tbl_name from sqlite_master"); |
| 704 | 189 | | foreach (var row in statement.ExecuteQuery()) |
| | 190 | | { |
| 308 | 191 | | if (string.Equals(name, row.GetString(0), StringComparison.OrdinalIgnoreCase)) |
| | 192 | | { |
| 0 | 193 | | return true; |
| | 194 | | } |
| | 195 | | } |
| | 196 | |
|
| 44 | 197 | | return false; |
| 44 | 198 | | } |
| | 199 | |
|
| | 200 | | protected List<string> GetColumnNames(ManagedConnection connection, string table) |
| | 201 | | { |
| 110 | 202 | | var columnNames = new List<string>(); |
| | 203 | |
|
| 2948 | 204 | | foreach (var row in connection.Query("PRAGMA table_info(" + table + ")")) |
| | 205 | | { |
| 1364 | 206 | | if (row.TryGetString(1, out var columnName)) |
| | 207 | | { |
| 1364 | 208 | | columnNames.Add(columnName); |
| | 209 | | } |
| | 210 | | } |
| | 211 | |
|
| 110 | 212 | | return columnNames; |
| | 213 | | } |
| | 214 | |
|
| | 215 | | protected void AddColumn(ManagedConnection connection, string table, string columnName, string type, List<string |
| | 216 | | { |
| 2222 | 217 | | if (existingColumnNames.Contains(columnName, StringComparison.OrdinalIgnoreCase)) |
| | 218 | | { |
| 616 | 219 | | return; |
| | 220 | | } |
| | 221 | |
|
| 1606 | 222 | | connection.Execute("alter table " + table + " add column " + columnName + " " + type + " NULL"); |
| 1606 | 223 | | } |
| | 224 | |
|
| | 225 | | protected void CheckDisposed() |
| | 226 | | { |
| 832 | 227 | | ObjectDisposedException.ThrowIf(_disposed, this); |
| 832 | 228 | | } |
| | 229 | |
|
| | 230 | | /// <inheritdoc /> |
| | 231 | | public void Dispose() |
| | 232 | | { |
| 44 | 233 | | Dispose(true); |
| 44 | 234 | | GC.SuppressFinalize(this); |
| 44 | 235 | | } |
| | 236 | |
|
| | 237 | | /// <summary> |
| | 238 | | /// Releases unmanaged and - optionally - managed resources. |
| | 239 | | /// </summary> |
| | 240 | | /// <param name="dispose"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release o |
| | 241 | | protected virtual void Dispose(bool dispose) |
| | 242 | | { |
| 44 | 243 | | if (_disposed) |
| | 244 | | { |
| 0 | 245 | | return; |
| | 246 | | } |
| | 247 | |
|
| 44 | 248 | | if (dispose) |
| | 249 | | { |
| 44 | 250 | | _writeLock.Wait(); |
| | 251 | | try |
| | 252 | | { |
| 44 | 253 | | _writeConnection.Dispose(); |
| 44 | 254 | | } |
| | 255 | | finally |
| | 256 | | { |
| 44 | 257 | | _writeLock.Release(); |
| 44 | 258 | | } |
| | 259 | |
|
| 44 | 260 | | _writeLock.Dispose(); |
| | 261 | | } |
| | 262 | |
|
| 44 | 263 | | _writeConnection = null; |
| 44 | 264 | | _writeLock = null; |
| | 265 | |
|
| 44 | 266 | | _disposed = true; |
| 44 | 267 | | } |
| | 268 | | } |
| | 269 | | } |