SqliteConnectionTest.cs

// Licensed to the .NET Foundation under one or more agreements.
// The .NET Foundation licenses this file to you under the MIT license.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using Microsoft.Data.Sqlite.Properties;
using Xunit;
using static SQLitePCL.raw;

namespace Microsoft.Data.Sqlite
{
    public class SqliteConnectionTest
    {
        [Fact]
        public void Ctor_sets_connection_string()
        {
            var connectionString = "Data Source=test.db";

            var connection = new SqliteConnection(connectionString);

            Assert.Equal(connectionString, connection.ConnectionString);
        }

        [Fact]
        public void ConnectionString_defaults_to_empty()
        {
            var connection = new SqliteConnection();

            Assert.Empty(connection.ConnectionString);
        }

        [Fact]
        public void ConnectionString_coalesces_to_empty()
        {
            var connection = new SqliteConnection
            {
                ConnectionString = null
            };

            Assert.NotNull(connection.ConnectionString);
            Assert.Empty(connection.ConnectionString);
        }

        [Fact]
        public void ConnectionString_setter_throws_when_open()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            var ex = Assert.Throws<InvalidOperationException>(() => connection.ConnectionString = "Data Source=test.db");

            Assert.Equal(Resources.ConnectionStringRequiresClosedConnection, ex.Message);
        }

        [Fact]
        public void ConnectionString_gets_and_sets_value()
        {
            var connection = new SqliteConnection();
            var connectionString = "Data Source=test.db";

            connection.ConnectionString = connectionString;

            Assert.Equal(connectionString, connection.ConnectionString);
        }

        [Fact]
        public void Database_returns_value()
        {
            var connection = new SqliteConnection();

            Assert.Equal("main", connection.Database);
        }

        [Fact]
        public void DataSource_returns_connection_string_data_source_when_closed()
        {
            var connection = new SqliteConnection("Data Source=test.db");

            Assert.Equal("test.db", connection.DataSource);
        }

        [Fact]
        public void DataSource_returns_actual_filename_when_open()
        {
            using var connection = new SqliteConnection("Data Source=test.db");
            connection.Open();

            var result = connection.DataSource;

            Assert.True(Path.IsPathRooted(result));
            Assert.Equal("test.db", Path.GetFileName(result));
        }

        [Fact]
        public void DefaultTimeout_defaults_to_30()
        {
            var connection = new SqliteConnection();

            Assert.Equal(30, connection.DefaultTimeout);
        }

        [Fact]
        public void DefaultTimeout_defaults_to_connection_string()
        {
            var connection = new SqliteConnection("Default Timeout=1");

            Assert.Equal(1, connection.DefaultTimeout);
        }

        [Fact]
        public void DefaultTimeout_works()
        {
            var connection = new SqliteConnection("Default Timeout=1")
            {
                DefaultTimeout = 2
            };

            Assert.Equal(2, connection.DefaultTimeout);
        }

        [Fact]
        public void ServerVersion_returns_value()
        {
            var connection = new SqliteConnection();

            var version = connection.ServerVersion;

            Assert.StartsWith("3.", version);
        }

        [Fact]
        public void State_closed_by_default()
        {
            var connection = new SqliteConnection();

            Assert.Equal(ConnectionState.Closed, connection.State);
        }

        [Fact]
        public void Open_works_when_no_connection_string()
        {
            using var connection = new SqliteConnection();

            connection.Open();
        }

        [Fact]
        public void Open_adjusts_data_directory_path()
        {
            var dataSubDirectory = Path.Combine(AppContext.BaseDirectory, "DataFolder");

            if (!Directory.Exists(dataSubDirectory))
                Directory.CreateDirectory(dataSubDirectory);

            AppDomain.CurrentDomain.SetData("DataDirectory", dataSubDirectory);

            try
            {
                using var connection = new SqliteConnection("Data Source=|DataDirectory|local.db");
                connection.Open();

                Assert.Equal(Path.Combine(dataSubDirectory, "local.db"), connection.DataSource);
            }
            finally
            {
                AppDomain.CurrentDomain.SetData("DataDirectory", null);
            }
        }

        [Fact]
        public void Open_adjusts_relative_path()
        {
            using var connection = new SqliteConnection("Data Source=local.db");
            connection.Open();

            Assert.Equal(Path.Combine(AppContext.BaseDirectory, "local.db"), connection.DataSource);
        }

        [Fact]
        public void Open_throws_when_error()
        {
            using var connection = new SqliteConnection("Data Source=file:data.db?mode=invalidmode");
            var ex = Assert.Throws<SqliteException>(() => connection.Open());

            Assert.Equal(SQLITE_ERROR, ex.SqliteErrorCode);
        }

        [Fact]
        public void Open_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");

            var raised = false;
            StateChangeEventHandler handler = (sender, e) =>
            {
                raised = true;

                Assert.Equal(connection, sender);
                Assert.Equal(ConnectionState.Closed, e.OriginalState);
                Assert.Equal(ConnectionState.Open, e.CurrentState);
            };

            connection.StateChange += handler;
            try
            {
                connection.Open();

                Assert.True(raised);
                Assert.Equal(ConnectionState.Open, connection.State);
            }
            finally
            {
                connection.StateChange -= handler;
            }
        }

        [Fact]
        public void Open_works_when_readonly()
        {
            using (var connection = new SqliteConnection("Data Source=readonly.db"))
            {
                connection.Open();

                connection.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS Idomic (Word TEXT);");
            }

            using (var connection = new SqliteConnection("Data Source=readonly.db;Mode=ReadOnly"))
            {
                connection.Open();

                var ex = Assert.Throws<SqliteException>(
                    () => connection.ExecuteNonQuery("INSERT INTO Idomic VALUES ('arimfexendrapuse');"));

                Assert.Equal(SQLITE_READONLY, ex.SqliteErrorCode);
            }
        }

        [Fact]
        public void Open_works_when_readwrite()
        {
            using var connection = new SqliteConnection("Data Source=readwrite.db;Mode=ReadWrite");
            var ex = Assert.Throws<SqliteException>(() => connection.Open());

            Assert.Equal(SQLITE_CANTOPEN, ex.SqliteErrorCode);
        }

        [Fact]
        public void Open_works_when_memory_shared()
        {
            var connectionString = "Data Source=people;Mode=Memory;Cache=Shared";

            using var connection1 = new SqliteConnection(connectionString);
            connection1.Open();

            connection1.ExecuteNonQuery(
                "CREATE TABLE Person (Name TEXT);" + "INSERT INTO Person VALUES ('Waldo');");

            using var connection2 = new SqliteConnection(connectionString);
            connection2.Open();

            var name = connection2.ExecuteScalar<string>("SELECT Name FROM Person;");
            Assert.Equal("Waldo", name);
        }

        [Fact]
        public void Open_works_when_password()
        {
#if E_SQLITE3 || WINSQLITE3
            Open_works_when_password_unsupported();
#elif E_SQLCIPHER || SQLCIPHER
            Open_works_when_password_supported();
#elif SQLITE3
            Open_works_when_password_might_be_supported();
#else
#error Unexpected native library
#endif
        }

        private void Open_works_when_password_unsupported()
        {
            using var connection = new SqliteConnection("Data Source=encrypted.db;Password=password");
            var stateChangeRaised = false;
            connection.StateChange += (sender, e) => stateChangeRaised = true;

            var ex = Assert.Throws<InvalidOperationException>(() => connection.Open());

            Assert.Equal(Resources.EncryptionNotSupported(GetNativeLibraryName()), ex.Message);
            Assert.False(stateChangeRaised);
            Assert.Equal(ConnectionState.Closed, connection.State);
        }

        private void Open_works_when_password_supported()
        {
            using var connection1 = new SqliteConnection("Data Source=encrypted.db;Password=password");
            connection1.Open();

            // NB: The file is only encrypted after writing
            connection1.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS dual (dummy)");

            using var connection2 = new SqliteConnection("Data Source=encrypted.db;Password=wrong");
            var stateChangeRaised = false;
            connection2.StateChange += (sender, e) => stateChangeRaised = true;

            var ex = Assert.Throws<SqliteException>(() => connection2.Open());

            Assert.Equal(SQLITE_NOTADB, ex.SqliteErrorCode);
            Assert.False(stateChangeRaised);
            Assert.Equal(ConnectionState.Closed, connection2.State);
        }

        private void Open_works_when_password_might_be_supported()
        {
            using var connection = new SqliteConnection("Data Source=encrypted.db;Password=password");
            connection.Open();
        }

#if E_SQLCIPHER || SQLCIPHER
        [Fact]
        public void Open_decrypts_lazily_when_no_password()
        {
            try
            {
                using var connection1 = new SqliteConnection("Data Source=encrypted2.db;Password=password");
                connection1.Open();

                // NB: The file is only encrypted after writing
                connection1.ExecuteNonQuery(
                    "CREATE TABLE IF NOT EXISTS data (value); INSERT INTO data (value) VALUES (1);");

                using var connection2 = new SqliteConnection("Data Source=encrypted2.db");
                connection2.Open();
                connection2.ExecuteNonQuery("PRAGMA key = 'password';");

                var value = connection2.ExecuteScalar<long>("SELECT value FROM data;");

                Assert.Equal(1L, value);
            }
            finally
            {
                SqliteConnection.ClearPool(new SqliteConnection("Data Source=encrypted2.db;Password=password"));
                SqliteConnection.ClearPool(new SqliteConnection("Data Source=encrypted2.db"));
                File.Delete("encrypted2.db");
            }
        }
#endif

        [Theory]
        [InlineData("True", 1L)]
        [InlineData("False", 0L)]
        public void Open_works_when_foreign_keys(string foreignKeys, long expected)
        {
            using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=" + foreignKeys);
            connection.Open();

            Assert.Equal(expected, connection.ExecuteScalar<long>("PRAGMA foreign_keys;"));
        }

        [Fact]
        public void Open_works_when_recursive_triggers()
        {
            using var connection = new SqliteConnection("Data Source=:memory:;Recursive Triggers=True");
            connection.Open();

            Assert.Equal(1L, connection.ExecuteScalar<long>("PRAGMA recursive_triggers;"));
        }

        [Fact]
        public void BackupDatabase_works()
        {
            using var connection1 = new SqliteConnection("Data Source=:memory:");
            connection1.Open();

            connection1.ExecuteNonQuery(
                "CREATE TABLE Person (Name TEXT);" + "INSERT INTO Person VALUES ('Waldo');");

            using var connection2 = new SqliteConnection("Data Source=:memory:");
            connection2.Open();
            connection1.BackupDatabase(connection2);

            var name = connection2.ExecuteScalar<string>("SELECT Name FROM Person;");
            Assert.Equal("Waldo", name);
        }

        [Fact]
        public void BackupDatabase_works_when_destination_closed()
        {
            using var source = new SqliteConnection("Data Source=:memory:");
            using var destination = new SqliteConnection("Data Source=:memory:");
            source.Open();
            source.ExecuteNonQuery("CREATE TABLE Data (Value); INSERT INTO Data VALUES (0);");

            source.BackupDatabase(destination);
        }

        [Fact]
        public void BackupDatabase_throws_when_closed()
        {
            var source = new SqliteConnection();
            var destination = new SqliteConnection();

            var ex = Assert.Throws<InvalidOperationException>(() => source.BackupDatabase(destination));

            Assert.Equal(Resources.CallRequiresOpenConnection("BackupDatabase"), ex.Message);
        }

        [Fact]
        public void BackupDatabase_throws_when_destination_null()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            var ex = Assert.Throws<ArgumentNullException>(() => connection.BackupDatabase(null!));

            Assert.Equal("destination", ex.ParamName);
        }

        [Fact]
        public void BackupDatabase_throws_with_correct_message()
        {
            using var source = new SqliteConnection("Data Source=:memory:");
            using var destination = new SqliteConnection("Data Source=:memory:");
            source.Open();
            source.ExecuteNonQuery("CREATE TABLE Data (Value); INSERT INTO Data VALUES (0);");

            using (source.BeginTransaction())
            {
                source.ExecuteNonQuery("UPDATE Data SET Value = 1;");

                var ex = Assert.Throws<SqliteException>(() => source.BackupDatabase(destination));
                Assert.Equal(SQLITE_BUSY, ex.SqliteErrorCode);
            }
        }

        [Fact]
        public void Open_works_when_uri()
        {
            using var connection = new SqliteConnection("Data Source=file:readwrite.db?mode=rw");
            var ex = Assert.Throws<SqliteException>(() => connection.Open());

            Assert.Equal(SQLITE_CANTOPEN, ex.SqliteErrorCode);
        }

        [Fact]
        public void Close_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            var raised = false;
            StateChangeEventHandler handler = (sender, e) =>
            {
                raised = true;

                Assert.Equal(connection, sender);
                Assert.Equal(ConnectionState.Open, e.OriginalState);
                Assert.Equal(ConnectionState.Closed, e.CurrentState);
            };

            connection.StateChange += handler;
            try
            {
                connection.Close();

                Assert.True(raised);
                Assert.Equal(ConnectionState.Closed, connection.State);
            }
            finally
            {
                connection.StateChange -= handler;
            }
        }

        [Fact]
        public void Close_can_be_called_before_open()
        {
            var connection = new SqliteConnection();

            connection.Close();
        }

        [Fact]
        public void Close_can_be_called_more_than_once()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            connection.Close();
            connection.Close();
        }

        [Fact]
        public void Dispose_closes_connection()
        {
            var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            connection.Dispose();

            Assert.Equal(ConnectionState.Closed, connection.State);
        }

        [Fact]
        public void Dispose_can_be_called_more_than_once()
        {
            var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            connection.Dispose();
            connection.Dispose();
        }

        [Fact]
        public void CreateCommand_returns_command()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            using var transaction = connection.BeginTransaction();
            var command = connection.CreateCommand();

            Assert.NotNull(command);
            Assert.Same(connection, command.Connection);
            Assert.Same(transaction, command.Transaction);
        }

        [Fact]
        public void CreateCollation_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateCollation("MY_NOCASE", (s1, s2) => string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase));

            Assert.Equal(1L, connection.ExecuteScalar<long>("SELECT 'Νικοσ' = 'ΝΙΚΟΣ' COLLATE MY_NOCASE;"));
        }

        [Fact]
        public void CreateCollation_with_null_comparer_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateCollation("MY_NOCASE", (s1, s2) => string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase));
            connection.CreateCollation("MY_NOCASE", null);

            var ex = Assert.Throws<SqliteException>(
                () => connection.ExecuteScalar<long>("SELECT 'Νικοσ' = 'ΝΙΚΟΣ' COLLATE MY_NOCASE;"));

            Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "no such collation sequence: MY_NOCASE"), ex.Message);
        }

        [Fact]
        public void CreateCollation_works_when_closed()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.CreateCollation("MY_NOCASE", (s1, s2) => string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase));
            connection.Open();

            Assert.Equal(1L, connection.ExecuteScalar<long>("SELECT 'Νικοσ' = 'ΝΙΚΟΣ' COLLATE MY_NOCASE;"));
        }

        [Fact]
        public void CreateCollation_throws_with_empty_name()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            var ex = Assert.Throws<ArgumentNullException>(() => connection.CreateCollation(null!, null));

            Assert.Equal("name", ex.ParamName);
        }

        [Fact]
        public void CreateCollation_works_with_state()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            var list = new List<string>();
            connection.CreateCollation(
                "MY_NOCASE",
                list,
                (l, s1, s2) =>
                {
                    l.Add("Invoked");
                    return string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase);
                });

            Assert.Equal(1L, connection.ExecuteScalar<long>("SELECT 'Νικοσ' = 'ΝΙΚΟΣ' COLLATE MY_NOCASE;"));
            var item = Assert.Single(list);
            Assert.Equal("Invoked", item);
        }

        [Fact]
        public void CreateFunction_works_when_closed()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.CreateFunction("test", 1L, (long state, long x, int y) => $"{state} {x} {y}");
            connection.Open();

            var result = connection.ExecuteScalar<string>("SELECT test(2, 3);");

            Assert.Equal("1 2 3", result);
        }

        [Fact]
        public void CreateFunction_throws_when_no_name()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            var ex = Assert.Throws<ArgumentNullException>(() => connection.CreateFunction(null!, () => 1L));

            Assert.Equal("name", ex.ParamName);
        }

        [Fact]
        public void CreateFunction_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction("test", 1L, (long state, long x, int y) => $"{state} {x} {y}");

            var result = connection.ExecuteScalar<string>("SELECT test(2, 3);");

            Assert.Equal("1 2 3", result);
        }

        [Fact]
        public void CreateFunction_works_when_params()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction(
                "test",
                args => string.Join(", ", args.Select(a => a?.GetType().FullName ?? "(null)")));

            var result = connection.ExecuteScalar<string>("SELECT test(1, 3.1, 'A', X'7E57', NULL);");

            Assert.Equal("System.Int64, System.Double, System.String, System.Byte[], (null)", result);
        }

        [Fact]
        public void CreateFunction_works_when_exception()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction<long>("test", () => throw new Exception("Test"));

            var ex = Assert.Throws<SqliteException>(() => connection.ExecuteScalar<long>("SELECT test();"));

            Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "Test"), ex.Message);
        }

        [Fact]
        public void CreateFunction_works_when_sqlite_exception()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction<long>("test", () => throw new SqliteException("Test", 200));

            var ex = Assert.Throws<SqliteException>(() => connection.ExecuteScalar<long>("SELECT test();"));

            Assert.Equal(Resources.SqliteNativeError(200, "Test"), ex.Message);
        }

        [Fact]
        public void CreateFunction_works_when_null()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction("test", () => 1L);
            connection.CreateFunction("test", default(Func<long>));

            var ex = Assert.Throws<SqliteException>(() => connection.ExecuteScalar<long>("SELECT test();"));

            Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "no such function: test"), ex.Message);
        }

        [Fact]
        public void CreateFunction_works_when_result_null()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction<object?>("test", () => null);

            var result = connection.ExecuteScalar<object>("SELECT test();");

            Assert.Equal(DBNull.Value, result);
        }

        [Fact]
        public void CreateFunction_works_when_result_DBNull()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction<object>("test", () => DBNull.Value);

            var result = connection.ExecuteScalar<object>("SELECT test();");

            Assert.Equal(DBNull.Value, result);
        }

        [Fact]
        public void CreateFunction_works_when_parameter_null_and_type_nullable()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction("test", (long? x) => x == null);

            var result = connection.ExecuteScalar<long>("SELECT test(NULL);");

            Assert.Equal(1L, result);
        }

        [Fact]
        public void CreateFunction_works_when_parameter_null_but_type_long()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction("test", (long x) => x);

            var ex = Assert.Throws<SqliteException>(() => connection.ExecuteScalar<long>("SELECT test(NULL);"));

            Assert.Equal(
                Resources.SqliteNativeError(SQLITE_ERROR, Resources.UDFCalledWithNull("test", 0)),
                ex.Message);
        }

        [Fact]
        public void CreateFunction_works_when_parameter_null_but_type_double()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction("test", (double x) => x);

            var ex = Assert.Throws<SqliteException>(() => connection.ExecuteScalar<double>("SELECT test(NULL);"));

            Assert.Equal(
                Resources.SqliteNativeError(SQLITE_ERROR, Resources.UDFCalledWithNull("test", 0)),
                ex.Message);
            Assert.Equal(SQLITE_ERROR, ex.SqliteErrorCode);
        }

        [Fact]
        public void CreateFunction_works_when_parameter_null_and_type_string()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction("test", (string? x) => x == null);

            var result = connection.ExecuteScalar<long>("SELECT test(NULL);");

            Assert.Equal(1L, result);
        }

        [Fact]
        public void CreateFunction_works_when_parameter_null_and_type_byteArray()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction("test", (byte[] x) => x == null);

            var result = connection.ExecuteScalar<long>("SELECT test(NULL);");

            Assert.Equal(1L, result);
        }

        [Fact]
        public void CreateFunction_works_when_parameter_empty_blob()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.CreateFunction("test", (byte[] x) => x?.Length == 0);

            var result = connection.ExecuteScalar<long>("SELECT test(X'');");

            Assert.Equal(1L, result);
        }

        [Fact]
        public void CreateFunction_is_non_deterministic()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            connection.ExecuteNonQuery("CREATE TABLE Data (Value); INSERT INTO Data VALUES (0);");
            connection.CreateFunction("test", (double x) => x);

            var ex = Assert.Throws<SqliteException>(
                () => connection.ExecuteNonQuery("CREATE INDEX InvalidIndex ON Data (Value) WHERE test(Value) = 0;"));

            Assert.Equal(
                Resources.SqliteNativeError(SQLITE_ERROR, "non-deterministic functions prohibited in partial index WHERE clauses"),
                ex.Message);
        }

        [Fact]
        public void CreateFunction_deterministic_param_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            connection.ExecuteNonQuery("CREATE TABLE Data (Value); INSERT INTO Data VALUES (0);");
            connection.CreateFunction("test", (double x) => x, true);

            Assert.Equal(1, connection.ExecuteNonQuery("CREATE INDEX InvalidIndex ON Data (Value) WHERE test(Value) = 0;"));
        }

        [Fact]
        public void CreateAggregate_works_when_closed()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.CreateAggregate(
                "test",
                "A",
                (string a, string x, int y) => a + x + y,
                a => a + "Z");
            connection.Open();
            connection.ExecuteNonQuery("CREATE TABLE dual2 (dummy1, dummy2); INSERT INTO dual2 (dummy1, dummy2) VALUES ('X', 1);");

            var result = connection.ExecuteScalar<string>("SELECT test(dummy1, dummy2) FROM dual2;");

            Assert.Equal("AX1Z", result);
        }

        [Fact]
        public void CreateAggregate_throws_when_no_name()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            var ex = Assert.Throws<ArgumentNullException>(() => connection.CreateAggregate(null!, (string? _) => "A"));

            Assert.Equal("name", ex.ParamName);
        }

        [Fact]
        public void CreateAggregate_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.ExecuteNonQuery("CREATE TABLE dual2 (dummy1, dummy2); INSERT INTO dual2 (dummy1, dummy2) VALUES ('X', 1);");
            connection.CreateAggregate(
                "test",
                "A",
                (string a, string x, int y) => a + x + y,
                a => a + "Z");

            var result = connection.ExecuteScalar<string>("SELECT test(dummy1, dummy2) FROM dual2;");

            Assert.Equal("AX1Z", result);
        }

        [Fact]
        public void CreateAggregate_works_called_twice()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.ExecuteNonQuery("CREATE TABLE dual2 (dummy1, dummy2); INSERT INTO dual2 (dummy1, dummy2) VALUES ('X', 'Y');");
            connection.CreateAggregate(
                "test",
                "A",
                (string a, string x, string y) => a + x + y,
                a => a + "Z");

            var result = connection.ExecuteScalar<string>("SELECT test(dummy1, dummy2) FROM dual2;");
            Assert.Equal("AXYZ", result);

            result = connection.ExecuteScalar<string>("SELECT test(dummy1, dummy2) FROM dual2;");
            Assert.Equal("AXYZ", result);
        }

        [Fact]
        public void CreateAggregate_works_called_twice_in_same_query()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.ExecuteNonQuery("CREATE TABLE dual2 (dummy1, dummy2); INSERT INTO dual2 (dummy1, dummy2) VALUES ('X', 'Y');");
            connection.CreateAggregate(
                "test",
                "A",
                (string a, string x, string y) => a + x + y,
                a => a + "Z");

            using (var reader = connection.ExecuteReader("SELECT test(dummy1, dummy2), test(dummy2, dummy1) FROM dual2;"))
            {
                Assert.True(reader.Read());

                Assert.Equal("AXYZ", reader.GetString(0));
                Assert.Equal("AYXZ", reader.GetString(1));

                Assert.False(reader.Read());
            }
        }

        [Fact]
        public void CreateAggregate_works_when_no_rows()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.ExecuteNonQuery("CREATE TABLE dual2 (dummy1, dummy2);");
            connection.CreateAggregate(
                "test",
                "A",
                (string a, string x, string y) => a + x + y,
                a => a + "Z");

            var result = connection.ExecuteScalar<string>("SELECT test(dummy1, dummy2) FROM dual2;");

            Assert.Equal("AZ", result);
        }

        [Fact]
        public void CreateAggregate_works_when_params()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
            connection.CreateAggregate("test", (string? a, object?[] args) => a + string.Join(", ", args) + "; ");

            var result = connection.ExecuteScalar<string>("SELECT test(dummy) FROM dual;");

            Assert.Equal("X; ", result);
        }

        [Fact]
        public void CreateAggregate_works_when_exception_during_step()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
            connection.CreateAggregate("test", (string? _) => throw new Exception("Test"));

            var ex = Assert.Throws<SqliteException>(
                () => connection.ExecuteScalar<string>("SELECT test() FROM dual;"));

            Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "Test"), ex.Message);
        }

        [Fact]
        public void CreateAggregate_works_when_exception_during_final()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
            connection.CreateAggregate<string, string>("test", "A", _ => "B", a => throw new Exception("Test"));

            var ex = Assert.Throws<SqliteException>(
                () => connection.ExecuteScalar<string>("SELECT test() FROM dual;"));

            Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "Test"), ex.Message);
        }

        [Fact]
        public void CreateAggregate_works_when_sqlite_exception()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
            connection.CreateAggregate("test", (string? _) => throw new SqliteException("Test", 200));

            var ex = Assert.Throws<SqliteException>(
                () => connection.ExecuteScalar<string>("SELECT test() FROM dual;"));

            Assert.Equal(Resources.SqliteNativeError(200, "Test"), ex.Message);
        }

        [Fact]
        public void CreateAggregate_works_when_null()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
            connection.CreateAggregate("test", (string? _) => "A");
            connection.CreateAggregate("test", default(Func<string?, string>));

            var ex = Assert.Throws<SqliteException>(
                () => connection.ExecuteScalar<long>("SELECT test() FROM dual;"));

            Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "no such function: test"), ex.Message);
        }

        [Fact]
        public void BeginTransaction_throws_when_closed()
        {
            var connection = new SqliteConnection();

            var ex = Assert.Throws<InvalidOperationException>(() => connection.BeginTransaction());

            Assert.Equal(Resources.CallRequiresOpenConnection("BeginTransaction"), ex.Message);
        }

        [Fact]
        public void BeginTransaction_throws_when_parallel_transaction()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            using (connection.BeginTransaction())
            {
                var ex = Assert.Throws<InvalidOperationException>(() => connection.BeginTransaction());

                Assert.Equal(Resources.ParallelTransactionsNotSupported, ex.Message);
            }
        }

        [Fact]
        public void BeginTransaction_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            using var transaction = connection.BeginTransaction(IsolationLevel.Serializable);
            Assert.NotNull(transaction);
            Assert.Equal(connection, transaction.Connection);
            Assert.Equal(IsolationLevel.Serializable, transaction.IsolationLevel);
        }

        [Fact]
        public void ChangeDatabase_not_supported()
        {
            using var connection = new SqliteConnection();
            Assert.Throws<NotSupportedException>(() => connection.ChangeDatabase("new"));
        }

        [Fact]
        public void Mars_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            var command1 = connection.CreateCommand();
            command1.CommandText = "SELECT '1A' UNION SELECT '1B';";

            using var reader1 = command1.ExecuteReader();
            reader1.Read();
            Assert.Equal("1A", reader1.GetString(0));

            var command2 = connection.CreateCommand();
            command2.CommandText = "SELECT '2A' UNION SELECT '2B';";

            using var reader2 = command2.ExecuteReader();
            reader2.Read();
            Assert.Equal("2A", reader2.GetString(0));

            reader1.Read();
            Assert.Equal("1B", reader1.GetString(0));

            reader2.Read();
            Assert.Equal("2B", reader2.GetString(0));
        }

        [Fact]
        public void EnableExtensions_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            var loadExtensionOmitted = connection.ExecuteScalar<long>(
                "SELECT COUNT(*) FROM pragma_compile_options WHERE compile_options = 'OMIT_LOAD_EXTENSION';");
            if (loadExtensionOmitted != 0L)
            {
                return;
            }

            var sql = "SELECT load_extension('unknown');";

            var ex = Assert.Throws<SqliteException>(() => connection.ExecuteNonQuery(sql));
            var originalError = ex.Message;

            connection.EnableExtensions();

            ex = Assert.Throws<SqliteException>(() => connection.ExecuteNonQuery(sql));
            var enabledError = ex.Message;

            connection.EnableExtensions(enable: false);

            ex = Assert.Throws<SqliteException>(() => connection.ExecuteNonQuery(sql));
            var disabledError = ex.Message;

            Assert.NotEqual(originalError, enabledError);
            Assert.Equal(originalError, disabledError);
        }

        [Fact]
        public void EnableExtensions_works_when_closed()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            var loadExtensionOmitted = connection.ExecuteScalar<long>(
                "SELECT COUNT(*) FROM pragma_compile_options WHERE compile_options = 'OMIT_LOAD_EXTENSION';");
            if (loadExtensionOmitted != 0L)
            {
                return;
            }

            var sql = "SELECT load_extension('unknown');";

            var ex = Assert.Throws<SqliteException>(() => connection.ExecuteNonQuery(sql));
            var originalError = ex.Message;

            connection.Close();
            connection.EnableExtensions();
            connection.Open();

            ex = Assert.Throws<SqliteException>(() => connection.ExecuteNonQuery(sql));
            var enabledError = ex.Message;

            Assert.NotEqual(originalError, enabledError);
        }

        [Fact]
        public void LoadExtension_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            var loadExtensionOmitted = connection.ExecuteScalar<long>(
                "SELECT COUNT(*) FROM pragma_compile_options WHERE compile_options = 'OMIT_LOAD_EXTENSION';");
            if (loadExtensionOmitted != 0L)
            {
                return;
            }

            connection.Close();
            connection.EnableExtensions(false);
            connection.Open();

            var ex = Assert.Throws<SqliteException>(
                () => connection.ExecuteNonQuery("SELECT load_extension('unknown');"));
            var extensionsDisabledError = ex.Message;

            ex = Assert.Throws<SqliteException>(() => connection.LoadExtension("unknown"));

            Assert.NotEqual(extensionsDisabledError, ex.Message);
        }

        [Fact]
        public void LoadExtension_works_when_closed()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();

            var loadExtensionOmitted = connection.ExecuteScalar<long>(
                "SELECT COUNT(*) FROM pragma_compile_options WHERE compile_options = 'OMIT_LOAD_EXTENSION';");
            if (loadExtensionOmitted != 0L)
            {
                return;
            }

            connection.Close();
            connection.EnableExtensions(false);
            connection.Open();

            var ex = Assert.Throws<SqliteException>(
                () => connection.ExecuteNonQuery("SELECT load_extension('unknown');"));
            var extensionsDisabledError = ex.Message;

            connection.Close();

            connection.LoadExtension("unknown");

            ex = Assert.Throws<SqliteException>(() => connection.Open());

            Assert.NotEqual(extensionsDisabledError, ex.Message);
        }

        [Fact]
        public void DbProviderFactory_works()
        {
            var connection = new SqliteConnection();

            var result = DbProviderFactories.GetFactory(connection);

            Assert.Same(SqliteFactory.Instance, result);
        }

        [Fact]
        public void GetSchema_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");

            var dataTable = connection.GetSchema();

            Assert.Equal(DbMetaDataCollectionNames.MetaDataCollections, dataTable.TableName);
            Assert.Collection(
                dataTable.Columns.Cast<DataColumn>(),
                c => Assert.Equal(DbMetaDataColumnNames.CollectionName, c.ColumnName),
                c => Assert.Equal(DbMetaDataColumnNames.NumberOfRestrictions, c.ColumnName),
                c => Assert.Equal(DbMetaDataColumnNames.NumberOfIdentifierParts, c.ColumnName));
            Assert.Collection(
                dataTable.Rows.Cast<DataRow>().Select(r => r.ItemArray),
                r => Assert.Equal(new object[] { DbMetaDataCollectionNames.MetaDataCollections, 0, 0 }, r),
                r => Assert.Equal(new object[] { DbMetaDataCollectionNames.ReservedWords, 0, 0 }, r));
        }

        [Fact]
        public void GetSchema_works_when_no_args()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");

            var dataTable = connection.GetSchema();

            Assert.Equal(DbMetaDataCollectionNames.MetaDataCollections, dataTable.TableName);
        }

        [Theory]
        [InlineData(null)]
        [InlineData("")]
        [InlineData(" ")]
        [InlineData("Unknown")]
        public void GetSchema_throws_when_unknown_collection(string collectionName)
        {
            using var connection = new SqliteConnection("Data Source=:memory:");

            var ex = Assert.Throws<ArgumentException>(() => connection.GetSchema(collectionName));

            Assert.Equal(Resources.UnknownCollection(collectionName), ex.Message);
        }

        [Fact]
        public void GetSchema_ignores_case()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");

            var dataTable = connection.GetSchema(DbMetaDataCollectionNames.MetaDataCollections.ToUpper());

            Assert.Equal(DbMetaDataCollectionNames.MetaDataCollections, dataTable.TableName);
        }

        [Theory]
        [InlineData(nameof(DbMetaDataCollectionNames.MetaDataCollections), 0)]
        [InlineData(nameof(DbMetaDataCollectionNames.ReservedWords), 0)]
        public void GetSchema_throws_when_unknown_restrictions(string collectionName, int maxRestrictions)
        {
            using var connection = new SqliteConnection("Data Source=:memory:");

            var ex = Assert.Throws<ArgumentException>(
                () => connection.GetSchema(
                    collectionName,
                    Enumerable.Repeat<string?>(null, maxRestrictions + 1).ToArray()));

            Assert.Equal(Resources.TooManyRestrictions(collectionName), ex.Message);
        }

        [Fact]
        public void GetSchema_allows_null()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");

            var dataTable = connection.GetSchema(DbMetaDataCollectionNames.MetaDataCollections, null!);

            Assert.Equal(DbMetaDataCollectionNames.MetaDataCollections, dataTable.TableName);
        }

        [Fact]
        public void GetSchema_ReservedWords_works()
        {
            using var connection = new SqliteConnection("Data Source=:memory:");
            if (new Version(connection.ServerVersion) < new Version(3, 24, 0))
            {
                // Skip. Native functions not available
                return;
            }

            var dataTable = connection.GetSchema(DbMetaDataCollectionNames.ReservedWords);

            Assert.Equal(DbMetaDataCollectionNames.ReservedWords, dataTable.TableName);
            Assert.Single(dataTable.Columns);
            Assert.Contains(
                dataTable.Rows.Cast<DataRow>(),
                r => (string)r[DbMetaDataColumnNames.ReservedWord] == "SELECT");
        }
    }
}