How to use SQLite in Plugin

[bakes82 167]

What version of SQLite is being used; also what pathing would be needed for the new db file so its stored in configuration directory?

The file 'System.Data.SQLite, Version=1.0.119.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139' cound not be found.

 

[Amything 122]

Regarding the path, below is example from @softworkzdemo project. I haven't implemented SQLite yet though. 

 

 using System;
 using System.IO;

 using Emby.Web.GenericEdit;

 using MediaBrowser.Common;
 using MediaBrowser.Common.Configuration;
 using MediaBrowser.Model.IO;
 using MediaBrowser.Model.Logging;
 using MediaBrowser.Model.Serialization;

 public class SimpleFileStore<TOptionType> : SimpleContentStore<TOptionType>
     where TOptionType : EditableOptionsBase, new()
 {
     private readonly ILogger logger;
     private readonly string pluginFullName;
     private readonly object lockObj = new object();
     private readonly IJsonSerializer jsonSerializer;
     private readonly IFileSystem fileSystem;
     private readonly string pluginconfigPath;

     private TOptionType options;

     public SimpleFileStore(IApplicationHost applicationHost, ILogger logger, string pluginFullName)
     {
         this.logger = logger;
         this.pluginFullName = pluginFullName;
         jsonSerializer = applicationHost.Resolve<IJsonSerializer>();
         fileSystem = applicationHost.Resolve<IFileSystem>();

         var applicationPaths = applicationHost.Resolve<IApplicationPaths>();
         pluginconfigPath = applicationPaths.PluginConfigurationsPath;

         if (!fileSystem.DirectoryExists(pluginconfigPath))
         {
             fileSystem.CreateDirectory(pluginconfigPath);
         }

         OptionsFileName = string.Format("{0}.json", pluginFullName);
     }

 

[Luke 42077]

The best example for sqlite is auto organize: https://github.com/MediaBrowser/Emby.AutoOrganize

[bakes82 167]

@LukeThe file 'SQLitePCLRaw.core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1488e028ca7ab535' cound not be found.

 

<ItemGroup>
    <PackageReference Include="MediaBrowser.Common" Version="4.9.0.33-beta"/>
    <PackageReference Include="MediaBrowser.Server.Core" Version="4.9.0.33-beta"/>
    <PackageReference Include="SQLitePCL.pretty.core" Version="1.2.2" />
</ItemGroup>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLitePCL;

public class SQLiteService : IDisposable
{
    private readonly sqlite3 _db;
    private bool _disposed;

    public SQLiteService(string? dbPath)
    {
        if (string.IsNullOrEmpty(dbPath))
        {
            dbPath = "SharedPluginDatabase.db";
        }
        
        var result = raw.sqlite3_open(dbPath, out _db);
        if (result != raw.SQLITE_OK)
        {
            throw new SQLiteException($"Failed to open database: {raw.sqlite3_errmsg(_db)}");
        }
    }

    public void CreateTable<T>(string? overrideTableName = null) where T : class
    {
        var type = typeof(T);
        var properties = type.GetProperties();
        var tableName = overrideTableName ?? type.Name;

        var query = new StringBuilder($"CREATE TABLE IF NOT EXISTS {tableName} (");
        var columns = new List<string>();

        foreach (var prop in properties)
        {
            var columnType = GetSQLiteType(prop.PropertyType);
            var columnDef = $"{prop.Name} {columnType}";

            if (prop.Name.Equals("Id", StringComparison.OrdinalIgnoreCase))
            {
                columnDef += " PRIMARY KEY AUTOINCREMENT";
            }

            columns.Add(columnDef);
        }

        query.Append(string.Join(", ", columns));
        query.Append(")");

        ExecuteNonQuery(query.ToString());
    }

    public void Insert<T>(T entity, string? overrideTableName = null) where T : class
    {
        var type = typeof(T);
        var properties = type.GetProperties()
            .Where(p => !p.Name.Equals("Id", StringComparison.OrdinalIgnoreCase));
        var tableName = overrideTableName ?? type.Name;

        var propertyInfos = properties.ToList();
        var columns = string.Join(", ", propertyInfos.Select(p => p.Name));
        var parameters = string.Join(", ", propertyInfos.Select((_, i) => $"?{i + 1}"));

        var query = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";
        
        using var stmt = PrepareStatement(query);
        int paramIndex = 1;
        
        foreach (var prop in propertyInfos)
        {
            BindParameter(stmt, paramIndex++, prop.GetValue(entity));
        }

        var result = raw.sqlite3_step(stmt);
        if (result != raw.SQLITE_DONE)
        {
            throw new SQLiteException($"Error inserting data: {raw.sqlite3_errmsg(_db)}");
        }
    }

    public void Update<T>(T entity, string? overrideTableName = null) where T : class
    {
        var type = typeof(T);
        var properties = type.GetProperties();
        var tableName = overrideTableName ?? type.Name;

        var idProperty = properties.FirstOrDefault(p => p.Name.Equals("Id", StringComparison.OrdinalIgnoreCase));
        if (idProperty == null)
        {
            throw new ArgumentException("Entity must have an Id property");
        }

        var updateProps = properties.Where(p => !p.Name.Equals("Id", StringComparison.OrdinalIgnoreCase));
        var setClause = string.Join(", ", updateProps.Select((p, i) => $"{p.Name} = ?{i + 1}"));

        var query = $"UPDATE {tableName} SET {setClause} WHERE Id = ?{updateProps.Count() + 1}";

        using var stmt = PrepareStatement(query);
        int paramIndex = 1;

        foreach (var prop in updateProps)
        {
            BindParameter(stmt, paramIndex++, prop.GetValue(entity));
        }

        // Bind the ID parameter last
        BindParameter(stmt, paramIndex, idProperty.GetValue(entity));

        var result = raw.sqlite3_step(stmt);
        if (result != raw.SQLITE_DONE)
        {
            throw new SQLiteException($"Error updating data: {raw.sqlite3_errmsg(_db)}");
        }
    }

    public List<T> Query<T>(string whereClause = "", string? overrideTableName = null, Dictionary<string, object>? parameters = null) 
        where T : class, new()
    {
        var type = typeof(T);
        var tableName = overrideTableName ?? type.Name;
        var query = $"SELECT * FROM {tableName}";

        if (!string.IsNullOrEmpty(whereClause))
        {
            query += $" WHERE {whereClause}";
        }

        using var stmt = PrepareStatement(query);
        
        if (parameters != null)
        {
            int paramIndex = 1;
            foreach (var param in parameters)
            {
                BindParameter(stmt, paramIndex++, param.Value);
            }
        }

        var results = new List<T>();
        while (raw.sqlite3_step(stmt) == raw.SQLITE_ROW)
        {
            var item = new T();
            for (int i = 0; i < raw.sqlite3_column_count(stmt); i++)
            {
                var columnName = raw.sqlite3_column_name(stmt, i);
                var prop = type.GetProperty(columnName);
                
                if (prop != null && !raw.sqlite3_column_type(stmt, i).Equals(raw.SQLITE_NULL))
                {
                    var value = GetColumnValue(stmt, i, prop.PropertyType);
                    prop.SetValue(item, value);
                }
            }
            results.Add(item);
        }

        return results;
    }

    public T? QueryByProperty<T>(string propertyName, object value, string? overrideTableName = null)
        where T : class, new()
    {
        var parameters = new Dictionary<string, object>
        {
            { "@value", value }
        };

        return Query<T>($"{propertyName} = @value", overrideTableName, parameters).FirstOrDefault();
    }

    public List<T> QueryWhere<T>(Func<T, bool> predicate, string? overrideTableName = null) where T : class, new()
    {
        return Query<T>(overrideTableName: overrideTableName)
            .Where(predicate)
            .ToList();
    }

    private void ExecuteNonQuery(string query)
    {
        using var stmt = PrepareStatement(query);
        var result = raw.sqlite3_step(stmt);
        if (result != raw.SQLITE_DONE)
        {
            throw new SQLiteException($"Error executing query: {raw.sqlite3_errmsg(_db)}");
        }
    }

    private sqlite3_stmt PrepareStatement(string query)
    {
        var result = raw.sqlite3_prepare_v2(_db, query, out sqlite3_stmt stmt);
        if (result != raw.SQLITE_OK)
        {
            throw new SQLiteException($"Error preparing statement: {raw.sqlite3_errmsg(_db)}");
        }
        return stmt;
    }

    private void BindParameter(sqlite3_stmt stmt, int index, object? value)
    {
        if (value == null || value == DBNull.Value)
        {
            raw.sqlite3_bind_null(stmt, index);
            return;
        }

        switch (value)
        {
            case int intValue:
                raw.sqlite3_bind_int(stmt, index, intValue);
                break;
            case long longValue:
                raw.sqlite3_bind_int64(stmt, index, longValue);
                break;
            case double doubleValue:
                raw.sqlite3_bind_double(stmt, index, doubleValue);
                break;
            case string stringValue:
                raw.sqlite3_bind_text(stmt, index, stringValue);
                break;
            case byte[] blobValue:
                raw.sqlite3_bind_blob(stmt, index, blobValue);
                break;
            case bool boolValue:
                raw.sqlite3_bind_int(stmt, index, boolValue ? 1 : 0);
                break;
            case DateTime dateTimeValue:
                raw.sqlite3_bind_text(stmt, index, dateTimeValue.ToString("O"));
                break;
            case Guid guidValue:
                raw.sqlite3_bind_text(stmt, index, guidValue.ToString());
                break;
            default:
                raw.sqlite3_bind_text(stmt, index, value.ToString());
                break;
        }
    }

    private object? GetColumnValue(sqlite3_stmt stmt, int index, Type propertyType)
    {
        var columnType = raw.sqlite3_column_type(stmt, index);
        
        if (columnType == raw.SQLITE_NULL)
            return null;

        if (propertyType == typeof(int))
            return raw.sqlite3_column_int(stmt, index);
        
        if (propertyType == typeof(long))
            return raw.sqlite3_column_int64(stmt, index);
        
        if (propertyType == typeof(double))
            return raw.sqlite3_column_double(stmt, index);
        
        if (propertyType == typeof(string))
            return raw.sqlite3_column_text(stmt, index);
        
        if (propertyType == typeof(byte[]))
        {
            var blob = raw.sqlite3_column_blob(stmt, index);
            return blob.ToArray();
        }
        
        if (propertyType == typeof(bool))
            return raw.sqlite3_column_int(stmt, index) != 0;
        
        if (propertyType == typeof(DateTime))
            return DateTime.Parse(raw.sqlite3_column_text(stmt, index));
        
        if (propertyType == typeof(Guid))
            return Guid.Parse(raw.sqlite3_column_text(stmt, index));

        var stringValue = raw.sqlite3_column_text(stmt, index);
        return Convert.ChangeType(stringValue, propertyType);
    }

    private string GetSQLiteType(Type type)
    {
        if (type == typeof(int) || type == typeof(long)) return "INTEGER";
        if (type == typeof(string)) return "TEXT";
        if (type == typeof(DateTime)) return "DATETIME";
        if (type == typeof(bool)) return "BOOLEAN";
        if (type == typeof(decimal) || type == typeof(double) || type == typeof(float)) return "REAL";
        if (type == typeof(byte[])) return "BLOB";
        if (type == typeof(Guid)) return "TEXT";

        return "TEXT";
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (_disposed)
            return;

        if (disposing)
        {
            _db.Dispose();
        }

        _disposed = true;
    }

    ~SQLiteService()
    {
        Dispose(false);
    }
}

public class SQLiteException : Exception
{
    public SQLiteException(string message) : base(message)
    {
    }
}

 

[Luke 42077]

I wouldn't be able to find the answer to your problem without digging through your entire project. What I can tell you though is that auto-organize is working just fine, so if it can do it, so can your plugin.

[benjaminroesdal 0]

Is it correctly understood though Luke that it is very hard without extra steps to reference fx Microsoft.Data.Sqlite in the plugin or similar external packages? I have been trying for a while now and had no luck. Like you're saying in this thread i am able to use SQLitePCL.pretty.core but i would rather not @Luke

[Luke 42077]

7 hours ago, benjaminroesdal said:

Is it correctly understood though Luke that it is very hard without extra steps to reference fx Microsoft.Data.Sqlite in the plugin or similar external packages? I have been trying for a while now and had no luck. Like you're saying in this thread i am able to use SQLitePCL.pretty.core but i would rather not @Luke

Plugins can’t pull in external packages unless you get lucky and it’s something that is already shipped with Emby server. If you want to embed a small third party lib, the easiest thing to do is embed the source code for it in your plugin. Obviously that won’t help with Microsoft.datasqlite.

[bakes82 167]

11 hours ago, benjaminroesdal said:

Is it correctly understood though Luke that it is very hard without extra steps to reference fx Microsoft.Data.Sqlite in the plugin or similar external packages? I have been trying for a while now and had no luck. Like you're saying in this thread i am able to use SQLitePCL.pretty.core but i would rather not @Luke

Correct you have to use whatever their custom implementation is, its pretty bad compared to what you get natively out of the data.sqlite package.  IIRC you can pull their sqliterepo and extension files from github they listed above, feed it in to claude/gpt and have it create you a new service. But yes its painful, not intuitive, and missing all the features that make data.sqlite 1000% better to use.