Xamarin problem

[MattiaDurli]

Hello, I'm trying to use sqlite sink in a Xamarin.Forms app, here's the code:

string fileLogText = DependencyService.Get<IFileHelper>().GetExtStoragePath("", 1) + "/logs/logs.txt";
string fileLogSQLite = DependencyService.Get<IFileHelper>().GetExtStoragePath("", 1) + "/logs
string fileLogLiteDB = DependencyService.Get<IFileHelper>().GetExtStoragePath("", 1) + "/logs/logs_lite.db";

Serilog.Log.Logger = new LoggerConfiguration()
                .MinimumLevel.Debug()
                .WriteTo.File(fileLogText, rollingInterval: RollingInterval.Day)
                //.WriteTo.SQLite(fileLogSQLite)
                .WriteTo.LiteDB(fileLogLiteDB)
                .CreateLogger();

Serilog.Log.Debug("Test");

This code works, paths are correct and files gets created, but if I uncomment the SQLite line in the configuration I get this error:

Object reference not set to an instance of an object.
System.NullReferenceException: Object reference not set to an instance of an object.
at Mono.Debugging.Soft.SoftDebuggerSession.HandleBreakEventSet(Event[] es, Boolean dequeuing) in E:\A_work\2239\s\external\debugger-libs\Mono.Debugging.Soft\SoftDebuggerSession.cs:line 1799
at Mono.Debugging.Soft.SoftDebuggerSession.HandleEventSet(EventSet es) in E:\A_work\2239\s\external\debugger-libs\Mono.Debugging.Soft\SoftDebuggerSession.cs:line 1589
at Mono.Debugging.Soft.SoftDebuggerSession.EventHandler() in E:\A_work\2239\s\external\debugger-libs\Mono.Debugging.Soft\SoftDebuggerSession.cs:line 1489

I'm using Xamarin 4.3, VStudio 16.3.8, Serilog 2.9.0

[alexd-uss]

SQLite.Interop.dll assembly:<unknown assembly> type:<unknown type> member:(null)

I'm getting this exception on iOS simulator

[alexd-uss]

Seems like this library relies on System.Data.Sqlite according to this line

serilog-sinks-sqlite/src/Serilog.Sinks.SQLite/Sinks/SQLite/SQLiteSink.cs

Line 18 in 3e94df6

using System.Data.SQLite;

Which seems to be incompatible with xamarin according to this discussion:
https://forums.xamarin.com/discussion/153170/system-data-sqlite

P.S. Have not found any better or more "official" proofs

[alexd-uss]

Xamarin.iOS 8.10 adds support for System.Data, including the Mono.Data.Sqlite.dll ADO.NET provider. Support includes the addition of the following assemblies:

System.Data.dll
System.Data.Service.Client.dll
System.Transactions.dll
Mono.Data.Tds.dll
Mono.Data.Sqlite.dll

https://docs.microsoft.com/en-us/xamarin/ios/data-cloud/system.data

Mono.Data.Sqlite.dll != System.Data.Sqlite.dll which seems to be the root cause

[alexd-uss]

Installing Microsoft.Data.SQLite manually has not helped me:
https://www.nuget.org/packages/Microsoft.Data.SQLite

Still some people on stackoverflow claim that it has solved a similar SQLite.Interop.dll related failure:
https://stackoverflow.com/questions/55510552/xamarin-forms-system-data-sqlite

[alexd-uss]

SQLitePCL.Batteries_V2.Init(); ==> no luck either

https://forums.xamarin.com/discussion/129066/is-the-microsoft-data-sqlite-package-the-correct-sqlite-dal-library-to-use-for-new-projects

[dahovey]

I am using Microsoft Universal Platform (Windows 10) app and using SQLite.Interop.dll fails Microsoft's validation when built for their store. Validation message: File SQLite.Interop.dll has failed the AppContainerCheck check.

According to Microsoft options are using Microsoft.Data.SQLite, here

[mishrapw]

You can change SQLite to "sqlite-net-pcl", that works perfectly in Xamarin.
and change API syntax, rest all works fine.

[alexd-uss]

@mishrapw the sqlite xamarin package does work well indeed.
However, serilog seems to be using a "server side" sqlite package which makes no sense on mobile.

So, @mishrapw could you please share a minimal mobile app capable of sqlite logging with serilog, please.
** as long as you've figured out and the use case mentioned here "works perfectly" for you.

I'm also struggling with this issue just like @MattiaDurli (who created this ticket), so your help would be valuable.

[mishrapw]

// Copyright 2016 Serilog Contributors
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
// Below code has been modified as per application need.
// Used "sqlite-net-pcl" (instead of "System.Data.SQLite") which is cross platform support.
// Removed some parameters which are not used in application.

using System;
using System.Collections.Generic;
using System.IO;
using SQLite;
using System.Threading;
using System.Threading.Tasks;
using Serilog.Core;
using Serilog.Debugging;
using Serilog.Events;

namespace Serilog.SQLite.Logging
{
internal class SQLiteSink : BatchProvider, ILogEventSink
{
private readonly string _databasePath;
private readonly bool _storeTimestampInUtc;
private readonly bool _rollOver;
private readonly string _tableName;
private static SemaphoreSlim semaphoreSlim = new SemaphoreSlim(1, 1);

    public SQLiteSink(
        string sqlLiteDbPath,
        string tableName,
        bool storeTimestampInUtc,
        uint batchSize = 100,
        bool rollOver = true) : base(batchSize: (int) batchSize, maxBufferSize: 100_000)
    {
        _databasePath = sqlLiteDbPath;
        _tableName = tableName;
        _storeTimestampInUtc = storeTimestampInUtc;
        _rollOver = rollOver;
        
        InitializeDatabase();
    }

    #region ILogEvent implementation

    public void Emit(LogEvent logEvent)
    {
        PushEvent(logEvent);
    }

    #endregion

    private void InitializeDatabase()
    {
        var conn = GetSqLiteAsyncConnection();
        CreateSqlTable(conn);
    }

    private SQLiteAsyncConnection GetSqLiteAsyncConnection()
    {
        var sqlConString = new SQLiteConnectionString(_databasePath, true);
        var sqLiteConnection = new SQLiteAsyncConnection(sqlConString);
        return sqLiteConnection;
    }

    private void CreateSqlTable(SQLiteAsyncConnection sqlConnection)
    {
        var colDefs = "id INTEGER PRIMARY KEY AUTOINCREMENT,";
        colDefs += "Timestamp TEXT,";
        colDefs += "Level VARCHAR(10),";
        colDefs += "Exception TEXT,";
        colDefs += "RenderedMessage TEXT,";
        colDefs += "Properties TEXT";

        var sqlCreateText = $"CREATE TABLE IF NOT EXISTS {_tableName} ({colDefs})";

        sqlConnection.ExecuteAsync(sqlCreateText).ConfigureAwait(false);
    }

    private void TruncateLog(SQLiteAsyncConnection sqlConnection)
    {
        sqlConnection.ExecuteAsync($"DELETE FROM {_tableName}")
            .ConfigureAwait(false);
    }

 
    protected override async Task<bool> WriteLogEventAsync(ICollection<LogEvent> logEventsBatch)
    {
        if ((logEventsBatch == null) || (logEventsBatch.Count == 0))
            return true;
        await semaphoreSlim.WaitAsync().ConfigureAwait(false);
        try
        {
            var sqlConnection = GetSqLiteAsyncConnection();

            try
            {
                await WriteToDatabaseAsync(logEventsBatch, sqlConnection).ConfigureAwait(false);
                return true;
            }
            catch (SQLiteException e)
            {
                SelfLog.WriteLine(e.Message);

                if (e.Result != SQLite3.Result.Full)
                {
                    return false;
                }

                if (!_rollOver)
                {
                    SelfLog.WriteLine("Discarding log excessive of max database");
                    return true;
                }

                var dbExtension = Path.GetExtension(_databasePath);

                var newFilePath = Path.Combine(Path.GetDirectoryName(_databasePath) ?? "Logs",
                    $"{Path.GetFileNameWithoutExtension(_databasePath)}-{DateTime.Now:yyyyMMdd_hhmmss.ff}{dbExtension}");

                File.Copy(_databasePath, newFilePath, true);

                TruncateLog(sqlConnection);
                await WriteToDatabaseAsync(logEventsBatch, sqlConnection).ConfigureAwait(false);

                SelfLog.WriteLine($"Rolling database to {newFilePath}");
                return true;
            }
            catch (Exception e)
            {
                SelfLog.WriteLine(e.Message);
                return false;
            }
        }
        finally
        {
            semaphoreSlim.Release();
        }
    }

    private async Task WriteToDatabaseAsync(ICollection<LogEvent> logEventsBatch,
        SQLiteAsyncConnection sqlConnection)
    {
        var sqlInsertText = "INSERT INTO {0} (Timestamp, Level, Exception, RenderedMessage, Properties)";
        sqlInsertText += " VALUES (@timeStamp, @level, @exception, @renderedMessage, @properties)";
        sqlInsertText = string.Format(sqlInsertText, _tableName);

        foreach (var logEvent in logEventsBatch)
        {
            var logTimeStamp = _storeTimestampInUtc
                ? logEvent.Timestamp.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss.fff")
                : logEvent.Timestamp.ToString("yyyy-MM-ddTHH:mm:ss.fff");
            var logLevel = logEvent.Level.ToString();
            var exception = logEvent.Exception?.ToString() ?? string.Empty;
            var message = logEvent.MessageTemplate.Text;
            var properties = logEvent.Properties.Count > 0 ? logEvent.Properties.Json() : string.Empty;

            await sqlConnection.ExecuteAsync(sqlInsertText, new object[]
            {
                logTimeStamp,
                logLevel,
                exception,
                message,
                properties
            }).ConfigureAwait(false);
        }
    }
}

}

[mishrapw]

@alexd-uss you can change SQL sink code as per above rest module are Xamarin compatible (You can create .Net Standard lib for this), later on i will extend this repository for Xamarin support.

[alexd-uss]

@mishrapw thank you for the code. I'll try it as soon as I can.
P.S. This subclassing thing was not obvious for a new serilog user like myself. So thanks again.

[jaandk]

Have created a fork of this project and changed it to support Sqlite-PCL. With the help from the commet from @mishrapw :)
https://github.com/jaandk/serilog-sinks-sqlite-Net-PCL
https://www.nuget.org/packages/Serilog.Sinks.SQLite-Net-PCL