SQLiteTransformationProvider_AddTableTests.cs

using System;
using System.Data.SQLite;
using System.Linq;
using System.Threading.Tasks;
using DotNetProjects.Migrator.Framework;
using DotNetProjects.Migrator.Providers.Impl.SQLite;
using Migrator.Tests.Providers.Generic;
using NUnit.Framework;

namespace Migrator.Tests.Providers.SQLite;

[TestFixture]
[Category("SQLite")]
public class SQLiteTransformationProvider_AddTableTests : Generic_AddTableTestsBase
{
    [SetUp]
    public async Task SetUpAsync()
    {
        await BeginSQLiteTransactionAsync();
    }

    [Test]
    public void AddTable_UniqueOnlyOnColumnLevel_Obsolete_UniquesListIsEmpty()
    {
        const string tableName = "MyTableName";
        const string columnName = "MyColumnName";

        // Arrange/Act
        Provider.AddTable(tableName, new Column(columnName, System.Data.DbType.Int32, ColumnProperty.Unique));

        // Assert
        var createScript = ((SQLiteTransformationProvider)Provider).GetSqlCreateTableScript(tableName);
        Assert.That(createScript, Is.EqualTo("CREATE TABLE MyTableName (MyColumnName INTEGER NULL UNIQUE)"));

        var sqliteInfo = ((SQLiteTransformationProvider)Provider).GetSQLiteTableInfo(tableName);

        // It is no named unique so it is not listed in the Uniques list. Unique on column level is marked as obsolete.
        Assert.That(sqliteInfo.Uniques, Is.Empty);
    }

    [Test]
    public void AddTable_CompositePrimaryKey_ContainsNull()
    {
        const string tableName = "MyTableName";
        const string columnName1 = "Column1";
        const string columnName2 = "Column2";

        // Arrange/Act
        Provider.AddTable(tableName,
            new Column(columnName1, System.Data.DbType.Int32, ColumnProperty.PrimaryKey),
            new Column(columnName2, System.Data.DbType.Int32, ColumnProperty.PrimaryKey | ColumnProperty.NotNull)
        );

        Provider.Insert(tableName, [columnName1, columnName2], [1, 1]);
        var ex = Assert.Throws<SQLiteException>(() => Provider.Insert(tableName, [columnName1, columnName2], [1, 1]));

        // Assert
        var createScript = ((SQLiteTransformationProvider)Provider).GetSqlCreateTableScript(tableName);
        Assert.That(createScript, Is.EqualTo("CREATE TABLE MyTableName (Column1 INTEGER NULL, Column2 INTEGER NOT NULL, PRIMARY KEY (Column1, Column2))"));

        var pragmaTableInfos = ((SQLiteTransformationProvider)Provider).GetPragmaTableInfoItems(tableName);
        Assert.That(pragmaTableInfos.Single(x => x.Name == columnName1).NotNull, Is.False);
        Assert.That(pragmaTableInfos.Single(x => x.Name == columnName2).NotNull, Is.True);

        var sqliteInfo = ((SQLiteTransformationProvider)Provider).GetSQLiteTableInfo(tableName);
        Assert.That(sqliteInfo.Columns.First().Name, Is.EqualTo(columnName1));
        Assert.That(sqliteInfo.Columns[1].Name, Is.EqualTo(columnName2));

        // 19 = UNIQUE constraint failed
        Assert.That(ex.ErrorCode, Is.EqualTo(19));
    }

    [Test]
    public void AddTable_SinglePrimaryKey_ContainsNull()
    {
        const string tableName = "MyTableName";
        const string columnName1 = "Column1";
        const string columnName2 = "Column2";

        // Arrange/Act
        Provider.AddTable(tableName,
            new Column(columnName1, System.Data.DbType.Int32, ColumnProperty.PrimaryKey),
            new Column(columnName2, System.Data.DbType.Int32, ColumnProperty.NotNull)
        );

        Provider.Insert(tableName, [columnName1, columnName2], [1, 1]);
        Assert.Throws<SQLiteException>(() => Provider.Insert(tableName, [columnName1, columnName2], [1, 2]));

        // Assert
        var createScript = ((SQLiteTransformationProvider)Provider).GetSqlCreateTableScript(tableName);

        // In SQLite an INTEGER PRIMARY KEY column is NOT NULL implicitly (see insert asserts above)
        Assert.That(createScript, Is.EqualTo("CREATE TABLE MyTableName (Column1 INTEGER NOT NULL PRIMARY KEY, Column2 INTEGER NOT NULL)"));

        var sqliteInfo = ((SQLiteTransformationProvider)Provider).GetSQLiteTableInfo(tableName);
        Assert.That(sqliteInfo.Columns.First().Name, Is.EqualTo(columnName1));
        Assert.That(sqliteInfo.Columns[1].Name, Is.EqualTo(columnName2));
    }

    [Test]
    public void AddTable_MiscellaneousColumns_Succeeds()
    {
        const string tableName = "MyTableName";
        const string columnName1 = "Column1";
        const string columnName2 = "Column2";

        // Arrange/Act
        Provider.AddTable(tableName,
            new Column(columnName1, System.Data.DbType.Int32, ColumnProperty.NotNull | ColumnProperty.Identity | ColumnProperty.PrimaryKey),
            new Column(columnName2, System.Data.DbType.Int32, ColumnProperty.Null | ColumnProperty.Unique)
        );

        Provider.Insert(tableName, [columnName1, columnName2], [1, 1]);
        Assert.Throws<SQLiteException>(() => Provider.Insert(tableName, [columnName1, columnName2], [1, 1]));

        // Assert
        var createScript = ((SQLiteTransformationProvider)Provider).GetSqlCreateTableScript(tableName);
        Assert.That(createScript, Is.EqualTo("CREATE TABLE MyTableName (Column1 INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Column2 INTEGER NULL UNIQUE)"));

        var pragmaTableInfos = ((SQLiteTransformationProvider)Provider).GetPragmaTableInfoItems(tableName);
        Assert.That(pragmaTableInfos.First().NotNull, Is.True);
        Assert.That(pragmaTableInfos[1].NotNull, Is.False);

        var sqliteInfo = ((SQLiteTransformationProvider)Provider).GetSQLiteTableInfo(tableName);
        Assert.That(sqliteInfo.Columns.First().Name, Is.EqualTo(columnName1));
        Assert.That(sqliteInfo.Columns[1].Name, Is.EqualTo(columnName2));
    }

    /// <summary>
    /// NOT NULL is implicitly set by SQLite
    /// </summary>
    [Test]
    public void AddTable_GuidPrimaryKeyOneColumnPKImplicitlyUsingNotNull_ThrowsOnNullAndOnDuplicates()
    {
        const string tableName = "MyTableName";
        const string columnName1 = "Column1";
        var guid = Guid.NewGuid();

        // Arrange/Act
        Provider.AddTable(tableName,
            new Column(columnName1, System.Data.DbType.Guid, ColumnProperty.PrimaryKey)
        );

        Provider.Insert(tableName, [columnName1], [guid]);
        Assert.Throws<SQLiteException>(() => Provider.Insert(tableName, [columnName1], [guid]));

        // The migrator sets NotNull on PrimaryKey (non composite) so this line throws.
        Assert.Throws<SQLiteException>(() => Provider.Insert(tableName, [columnName1], [null]));
    }

    /// <summary>
    /// Composite PK with Guids
    /// </summary>
    [Test]
    public void AddTable_GuidPrimaryKeyCompositeWithGuid_DoesNotThrowOnDuplicateNULLEntries()
    {
        const string tableName = "MyTableName";
        const string columnName1 = "Column1";
        const string columnName2 = "Column2";
        var guid = Guid.NewGuid();
        var guid2 = Guid.NewGuid();

        // Arrange/Act
        Provider.AddTable(tableName,
            new Column(columnName1, System.Data.DbType.Guid, ColumnProperty.PrimaryKey),
            new Column(columnName2, System.Data.DbType.Guid, ColumnProperty.PrimaryKey)
        );

        // This is a normal SQLite behavior! 
        // NULL != NULL
        // (A, NULL) != (A, NULL)
        // Duplicates! You need to set NotNull if you want to prevent it!
        Provider.Insert(tableName, [columnName1, columnName2], [guid, null]);
        Provider.Insert(tableName, [columnName1, columnName2], [guid, null]);

        Provider.Insert(tableName, [columnName1, columnName2], [null, guid]);
        Provider.Insert(tableName, [columnName1, columnName2], [null, guid]);

        Provider.Insert(tableName, [columnName1, columnName2], [guid2, guid2]);
        Assert.Throws<SQLiteException>(() => Provider.Insert(tableName, [columnName1, columnName2], [guid2, guid2]));
    }
}