diff options
Diffstat (limited to 'Emby.Server.Implementations/Data/SqliteExtensions.cs')
| -rw-r--r-- | Emby.Server.Implementations/Data/SqliteExtensions.cs | 360 |
1 files changed, 128 insertions, 232 deletions
diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 4055b0ba1..9e5cb1702 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -1,11 +1,12 @@ -#nullable disable +#nullable enable #pragma warning disable CS1591 using System; using System.Collections.Generic; +using System.Data; using System.Diagnostics; using System.Globalization; -using SQLitePCL.pretty; +using Microsoft.Data.Sqlite; namespace Emby.Server.Implementations.Data { @@ -52,7 +53,68 @@ namespace Emby.Server.Implementations.Data "yy-MM-dd" }; - public static void RunQueries(this SQLiteDatabaseConnection connection, string[] queries) + private static void EnsureOpen(this SqliteConnection sqliteConnection) + { + if (sqliteConnection.State == ConnectionState.Closed) + { + sqliteConnection.Open(); + } + } + + public static IEnumerable<SqliteDataReader> Query(this SqliteConnection sqliteConnection, string commandText) + { + if (sqliteConnection.State != ConnectionState.Open) + { + sqliteConnection.Open(); + } + + var command = sqliteConnection.CreateCommand(); + command.CommandText = commandText; + using (var reader = command.ExecuteReader()) + { + while (reader.Read()) + { + yield return reader; + } + } + } + + public static void Execute(this SqliteConnection sqliteConnection, string commandText) + { + sqliteConnection.EnsureOpen(); + var command = sqliteConnection.CreateCommand(); + command.CommandText = commandText; + command.ExecuteNonQuery(); + } + + public static void RunInTransaction(this SqliteConnection sqliteConnection, Action<SqliteConnection> action) + { + sqliteConnection.EnsureOpen(); + + using var transaction = sqliteConnection.BeginTransaction(); + action(sqliteConnection); + transaction.Commit(); + } + + public static bool RunInTransaction(this SqliteConnection sqliteConnection, Func<SqliteConnection, bool> action) + { + sqliteConnection.EnsureOpen(); + using var transaction = sqliteConnection.BeginTransaction(); + var result = action(sqliteConnection); + transaction.Commit(); + return result; + } + + public static void ExecuteAll(this SqliteConnection sqliteConnection, string commandText) + { + sqliteConnection.EnsureOpen(); + + var command = sqliteConnection.CreateCommand(); + command.CommandText = commandText; + command.ExecuteNonQuery(); + } + + public static void RunQueries(this SqliteConnection connection, string[] queries) { ArgumentNullException.ThrowIfNull(queries); @@ -62,11 +124,6 @@ namespace Emby.Server.Implementations.Data }); } - public static Guid ReadGuidFromBlob(this ResultSetValue result) - { - return new Guid(result.ToBlob()); - } - public static string ToDateTimeParamValue(this DateTime dateValue) { var kind = DateTimeKind.Utc; @@ -83,27 +140,26 @@ namespace Emby.Server.Implementations.Data private static string GetDateTimeKindFormat(DateTimeKind kind) => (kind == DateTimeKind.Utc) ? DatetimeFormatUtc : DatetimeFormatLocal; - public static DateTime ReadDateTime(this ResultSetValue result) + public static DateTime ReadDateTime(this SqliteDataReader result) { var dateText = result.ToString(); return DateTime.ParseExact( - dateText, + dateText!, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AdjustToUniversal); } - public static bool TryReadDateTime(this IReadOnlyList<ResultSetValue> reader, int index, out DateTime result) + public static bool TryReadDateTime(this SqliteDataReader reader, int index, out DateTime result) { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - var dateText = item.ToString(); + var dateText = reader.GetString(index); if (DateTime.TryParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AdjustToUniversal, out var dateTimeResult)) { @@ -115,335 +171,175 @@ namespace Emby.Server.Implementations.Data return false; } - public static bool TryGetGuid(this IReadOnlyList<ResultSetValue> reader, int index, out Guid result) + public static bool TryGetGuid(this SqliteDataReader reader, int index, out Guid result) { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ReadGuidFromBlob(); + result = reader.GetGuid(index); return true; } - public static bool IsDbNull(this ResultSetValue result) + public static bool TryGetString(this SqliteDataReader reader, int index, out string result) { - return result.SQLiteType == SQLiteType.Null; - } - - public static string GetString(this IReadOnlyList<ResultSetValue> result, int index) - { - return result[index].ToString(); - } + result = string.Empty; - public static bool TryGetString(this IReadOnlyList<ResultSetValue> reader, int index, out string result) - { - result = null; - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { return false; } - result = item.ToString(); + result = reader.GetString(index); return true; } - public static bool GetBoolean(this IReadOnlyList<ResultSetValue> result, int index) + public static bool TryGetBoolean(this SqliteDataReader reader, int index, out bool result) { - return result[index].ToBool(); - } - - public static bool TryGetBoolean(this IReadOnlyList<ResultSetValue> reader, int index, out bool result) - { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ToBool(); + result = reader.GetBoolean(index); return true; } - public static bool TryGetInt32(this IReadOnlyList<ResultSetValue> reader, int index, out int result) + public static bool TryGetInt32(this SqliteDataReader reader, int index, out int result) { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ToInt(); + result = reader.GetInt32(index); return true; } - public static long GetInt64(this IReadOnlyList<ResultSetValue> result, int index) + public static bool TryGetInt64(this SqliteDataReader reader, int index, out long result) { - return result[index].ToInt64(); - } - - public static bool TryGetInt64(this IReadOnlyList<ResultSetValue> reader, int index, out long result) - { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ToInt64(); + result = reader.GetInt64(index); return true; } - public static bool TryGetSingle(this IReadOnlyList<ResultSetValue> reader, int index, out float result) + public static bool TryGetSingle(this SqliteDataReader reader, int index, out float result) { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ToFloat(); + result = reader.GetFloat(index); return true; } - public static bool TryGetDouble(this IReadOnlyList<ResultSetValue> reader, int index, out double result) + public static bool TryGetDouble(this SqliteDataReader reader, int index, out double result) { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ToDouble(); + result = reader.GetDouble(index); return true; } - public static Guid GetGuid(this IReadOnlyList<ResultSetValue> result, int index) - { - return result[index].ReadGuidFromBlob(); - } - [Conditional("DEBUG")] private static void CheckName(string name) { throw new ArgumentException("Invalid param name: " + name, nameof(name)); } - public static void TryBind(this IStatement statement, string name, double value) + public static void TryBind(this SqliteCommand statement, string name, Guid value) { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) + if (statement.Parameters.Contains(name)) { - bindParam.Bind(value); + statement.Parameters[name].Value = value; } else { - CheckName(name); + statement.Parameters.Add(new SqliteParameter(name, SqliteType.Blob) { Value = value }); } } - public static void TryBind(this IStatement statement, string name, string value) + public static void TryBind(this SqliteCommand statement, string name, object? value) { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) + var preparedValue = value ?? DBNull.Value; + if (statement.Parameters.Contains(name)) { - if (value is null) - { - bindParam.BindNull(); - } - else - { - bindParam.Bind(value); - } + statement.Parameters[name].Value = preparedValue; } else { - CheckName(name); + statement.Parameters.AddWithValue(name, preparedValue); } } - public static void TryBind(this IStatement statement, string name, bool value) + public static void TryBind(this SqliteCommand statement, string name, byte[] value) { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) + if (statement.Parameters.Contains(name)) { - bindParam.Bind(value); + statement.Parameters[name].Value = value; } else { - CheckName(name); + statement.Parameters.Add(new SqliteParameter(name, SqliteType.Blob, value.Length) { Value = value }); } } - public static void TryBind(this IStatement statement, string name, float value) + public static void TryBindNull(this SqliteCommand statement, string name) { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - bindParam.Bind(value); - } - else - { - CheckName(name); - } - } - - public static void TryBind(this IStatement statement, string name, int value) - { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - bindParam.Bind(value); - } - else - { - CheckName(name); - } - } - - public static void TryBind(this IStatement statement, string name, Guid value) - { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - Span<byte> byteValue = stackalloc byte[16]; - value.TryWriteBytes(byteValue); - bindParam.Bind(byteValue); - } - else - { - CheckName(name); - } + statement.TryBind(name, DBNull.Value); } - public static void TryBind(this IStatement statement, string name, DateTime value) + public static IEnumerable<SqliteDataReader> ExecuteQuery(this SqliteCommand command) { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) + using (var reader = command.ExecuteReader()) { - bindParam.Bind(value.ToDateTimeParamValue()); - } - else - { - CheckName(name); - } - } - - public static void TryBind(this IStatement statement, string name, long value) - { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - bindParam.Bind(value); - } - else - { - CheckName(name); - } - } - - public static void TryBind(this IStatement statement, string name, ReadOnlySpan<byte> value) - { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - bindParam.Bind(value); - } - else - { - CheckName(name); - } - } - - public static void TryBindNull(this IStatement statement, string name) - { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - bindParam.BindNull(); - } - else - { - CheckName(name); - } - } - - public static void TryBind(this IStatement statement, string name, DateTime? value) - { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); + while (reader.Read()) + { + yield return reader; + } } } - public static void TryBind(this IStatement statement, string name, Guid? value) + public static int SelectScalarInt(this SqliteCommand command) { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); - } + var result = command.ExecuteScalar(); + return Convert.ToInt32(result!, CultureInfo.InvariantCulture); } - public static void TryBind(this IStatement statement, string name, double? value) + public static SqliteCommand PrepareStatement(this SqliteConnection sqliteConnection, string sql) { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); - } + sqliteConnection.EnsureOpen(); + var command = sqliteConnection.CreateCommand(); + command.CommandText = sql; + return command; } - public static void TryBind(this IStatement statement, string name, int? value) + // Hacky + public static void MoveNext(this SqliteCommand sqliteCommand) { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); - } + sqliteCommand.Prepare(); + var result = sqliteCommand.ExecuteNonQuery(); } - public static void TryBind(this IStatement statement, string name, float? value) + public static byte[] GetBlob(this SqliteDataReader reader, int index) { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); - } - } - - public static void TryBind(this IStatement statement, string name, bool? value) - { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); - } - } - - public static IEnumerable<IReadOnlyList<ResultSetValue>> ExecuteQuery(this IStatement statement) - { - while (statement.MoveNext()) - { - yield return statement.Current; - } + // Have to reset to casting as there isn't a publicly available GetBlob method + return (byte[])reader.GetValue(index); } } } |