Are Sqlite Transactions supported?

Question

Are Sqlite Transactions supported?

Closed this issue 7 years ago · 3 comments

Good morning,

Reading through issue #294 I am not sure what the result of the discussion was. I see in asp.net for ef core that was added, but I am not sure about this provider. I am working on a .net core 2.0 application and I am using Sqlite as the database for it. I would like to do something like the following:

        //If any of the tables that are expected to exist do not, create them
        if (commandsToExecute.Any())
        {
            using (var transaction = _holdingSqliteConnection.BeginTransaction())
            {
                foreach (var currentCommandToExecute in commandsToExecute)
                {
                    using (var command = transaction.Connection.CreateCommand())
                    {
                        command.CommandText = currentCommandToExecute;

                        command.ExecuteNonQuery();
                    }
                }

                transaction.Commit();
            }
        }

Stepping through the debugger it would seem that they should be supported, but each time I attempt to execute this, whether it has just one element or multiple, I get the following error:
Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'cannot commit - no transaction is active'.'

I see the transaction being set, but I am at a loss for what it could be. Any assistance with this would be most appreciated.

Thanks,
Adam Brousseau

Answer 1 · 2018-02-12T16:49:28.000Z

Yes, SqliteConnection.BeginTransaction() should work fine. Do any of those commands contain BEGIN or END statements? If so, that is unsupported in ADO.NET.

Answer 2 · 2018-02-12T17:18:09.000Z

Opening a single connection I do the following:

Execute the following PRAGMA statements in the same command in order to set the sqlite parametres
that are not available to set via the SqliteConnectionStringBuilder
a. PRAGMA synchronous=OFF; PRAGMA journal_mode=Memory; PRAGMA temp_store=Memory;
PRAGMA page_size={0}; PRAGMA cache_size={1}; (Where 0 and 1 are read in args)
Execute the loading of an extension, mod spatialite (new command):
a. Select load_extension('mod_spatialite');
Check to see if the tables I need in the database are there (new command):
a. Select name From sqlite_master Where type='table' and name In({0}); (Where {0} is the string join of
the tables I am looking for)
If I don't find any of the tables I am looking for then I create the transaction to create the multiple
tables that are required (see above code).

I am not sure if there is anything wrong with what I am doing, at least I am not getting an error from any of the other commands. The only dependencies this project has are Gdal.Core 1.0.0, Microsoft.Data.Sqlite 2.0.0, and Microsoft.Extensions.Configuration[Abstractions, FileExtensions, Json]. I have attached the database class I am using as well.

public static class Db
{
    static Db()
    {
        Gdal.AllRegister();
        Ogr.RegisterAll();
    }

    private static SqliteConnection _holdingSqliteConnection;

    public static SqliteCacheMode CacheMode { get; set; } = SqliteCacheMode.Shared;

    public static int CacheSize { get; set; } = 2000;

    public static string DbName { get; set; } = "sample.db";

    public static void InitialiseDatabase()
    {
        BuildHolderConnection();

        if (!(_holdingSqliteConnection is null))
        {
            SetConfigurationElements();

            //Get the Sql Statements Section
            var sqlStatements = AppConfig.Configuration.GetSection("Sql Statements");

            LoadExtensions(sqlStatements);

            //If this is an existing database then check to see if the tables that need to be there exist
            var existingTables = !NewDatabase
                ? TablesExists(new List<string> {"'geometry_columns'", "'table1'", "'table2'"},
                    _holdingSqliteConnection)
                : new List<string>();

            LoadInitialTables(sqlStatements, existingTables);
        }
    }

    public static SqliteOpenMode OpenMode { get; set; } = SqliteOpenMode.Memory;

    public static bool NewDatabase { get; set; } = true;

    public static int PageSize { get; set; } = 4096;

    private static void BuildHolderConnection()
    {
        if (_holdingSqliteConnection is null)
        {
            _holdingSqliteConnection = new SqliteConnection(new SqliteConnectionStringBuilder
            {
                DataSource = Db.DbName,
                Cache = Db.CacheMode,
                Mode = Db.OpenMode
            }.ToString());

            _holdingSqliteConnection.Open();
        }
    }

    private static void LoadExtensions(IConfigurationSection sqlStatements)
    {
        if (!(_holdingSqliteConnection is null))
        {
            //Enable loading extensions
            _holdingSqliteConnection.EnableExtensions(true);

            //Initialise the database with Mod_Spatialite
            using (var command = _holdingSqliteConnection.CreateCommand())
            {
                command.CommandText = string.Format(sqlStatements["Load Mod_Spatialite"]);

                command.ExecuteNonQuery();
            }
        }
    }

    private static void LoadInitialTables(IConfigurationSection sqlStatements, List<string> existingTablesToCheck)
    {
        var commandsToExecute = new List<string>();

        if (!existingTablesToCheck.Contains("geometry_columns") || NewDatabase)
        {
            commandsToExecute.Add(sqlStatements["Initialise Geometry_Columns"]);
        }

        if (!existingTablesToCheck.Contains("table2") || NewDatabase)
        {
            commandsToExecute.AddRange(sqlStatements["Initialise Table2"].Split('%').ToList());
        }

        if (!existingTablesToCheck.Contains("table3") || NewDatabase)
        {
            commandsToExecute.AddRange(sqlStatements["Initialise Table3"].Split('%').ToList());
        }

        //If any of the tables that are expected to exist do not, create them
        if (commandsToExecute.Any())
        {
            using (var transaction = _holdingSqliteConnection.BeginTransaction())
            {
                foreach (var currentCommandToExecute in commandsToExecute)
                {
                    using (var command = transaction.Connection.CreateCommand())
                    {
                        command.CommandText = currentCommandToExecute;

                        command.ExecuteNonQuery();
                    }
                }

                transaction.Commit();
            }
        }
    }

    private static void SetConfigurationElements()
    {
        using (var command = _holdingSqliteConnection.CreateCommand())
        {
            command.CommandText = Db.OpenMode.Equals(SqliteOpenMode.Memory)
                ? string.Format("PRAGMA synchronous=OFF; PRAGMA journal_mode=Memory; PRAGMA temp_store=Memory; PRAGMA page_size={0}; PRAGMA cache_size={1};",
                    PageSize, CacheSize)
                : string.Format("PRAGMA synchronous=OFF; PRAGMA journal_mode=Truncate; PRAGMA temp_store=File; PRAGMA page_size={0}; PRAGMA cache_size={1};",
                    PageSize, CacheSize);

            command.ExecuteNonQuery();
        }
    }

    private static List<string> TablesExists(IEnumerable<string> tablesToCheckForExistence, DbConnection connection)
    {
        var tablesExists = new List<string>();

        using (var command = connection.CreateCommand())
        {
            command.CommandText =
                string.Format(AppConfig.Configuration.GetSection("Sql Statements")["Check If Table[s] Exists"], string.Join(",", tablesToCheckForExistence));

            object[] resultHolder = null;

            using (var dbReader = command.ExecuteReader())
            {
                if (dbReader.HasRows)
                {
                    resultHolder = new object[dbReader.FieldCount];
                    dbReader.GetValues(resultHolder);
                }
            }

            tablesExists = resultHolder.Select(currentTable => currentTable.ToString()).ToList();
        }

        return tablesExists;
    }
}

Answer 3 · 2018-02-15T13:31:52.000Z

I found out what the issue was. It would seem that one of the commands should have thrown back an error, but it wasn't so by the time the transaction attempted to commit it no longer existed. One of the queries contained a \t instead of the expected spacing.