Migrator.NET/src/Migrator.Tests/Providers/SQLite/SQLiteTransformationProvider_AddColumnTests.cs at 635536e93a0242252c96e50223896f059a0fdfaf · dotnetprojects/Migrator.NET · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
using System.Data;
using System.Linq;
using DotNetProjects.Migrator.Framework;
using DotNetProjects.Migrator.Providers.Impl.SQLite;
using Migrator.Tests.Providers.SQLite.Base;
using NUnit.Framework;
using NUnit.Framework.Legacy;

namespace Migrator.Tests.Providers.SQLite;

[TestFixture]
[Category("SQLite")]
public class SQLiteTransformationProvider_AddColumnTests : SQLiteTransformationProviderTestBase
{
    /// <summary>
    /// We use a NULL column as new column here. NOT NULL will fail as expected. The user should handle that on his own.
    /// </summary>
    [Test]
    public void AddColumn_HavingColumnPropertyUniqueAndIndex_RebuildSucceeds()
    {
        // Arrange
        const string testTableName = "MyDefaultTestTable";
        const string propertyName1 = "Color1";
        const string propertyName2 = "Color2";
        const string newColumn = "NewColumn";
        const string indexName = "MyIndexName";

        Provider.AddTable(testTableName,
            new Column(propertyName1, DbType.Int32, ColumnProperty.PrimaryKey),
            new Column(propertyName2, DbType.Int32, ColumnProperty.Unique)
        );

        Provider.AddIndex(indexName, testTableName, [propertyName1, propertyName2]);
        var tableInfoBefore = ((SQLiteTransformationProvider)Provider).GetSQLiteTableInfo(testTableName);

        Provider.ExecuteNonQuery($"INSERT INTO {testTableName} ({propertyName1}, {propertyName2}) VALUES (1, 2)");

        // Act
        Provider.AddColumn(table: testTableName, new Column(newColumn, DbType.String, ColumnProperty.Null));
        Provider.ExecuteNonQuery($"INSERT INTO {testTableName} ({propertyName1}, {propertyName2}, {newColumn}) VALUES (2, 3, 'Hello')");

        // Assert
        using var command = Provider.GetCommand();
        using var reader = Provider.ExecuteQuery(command, $"SELECT COUNT(*) as Count from {testTableName}");
        reader.Read();
        var count = reader.GetInt32(reader.GetOrdinal("Count"));
        Assert.That(count, Is.EqualTo(2));

        var tableInfoAfter = ((SQLiteTransformationProvider)Provider).GetSQLiteTableInfo(testTableName);

        Assert.That(tableInfoBefore.Columns.Single(x => x.Name == propertyName1).ColumnProperty.HasFlag(ColumnProperty.PrimaryKey), Is.True);
        Assert.That(tableInfoBefore.Columns.Single(x => x.Name == propertyName2).ColumnProperty.HasFlag(ColumnProperty.Unique), Is.True);

        Assert.That(tableInfoAfter.Columns.Single(x => x.Name == propertyName1).ColumnProperty.HasFlag(ColumnProperty.PrimaryKey), Is.True);
        Assert.That(tableInfoAfter.Columns.Single(x => x.Name == propertyName2).ColumnProperty.HasFlag(ColumnProperty.Unique), Is.True);

        var indexAfter = tableInfoAfter.Indexes.Single();
        Assert.That(indexAfter.Name, Is.EqualTo(indexName));
        CollectionAssert.AreEquivalent(indexAfter.KeyColumns, new string[] { propertyName1, propertyName2 });
    }

    [Test]
    public void AddColumn_HavingNullInPrimaryKey_HasNULLAfterAddAnotherColumn()
    {
        // Arrange/Act
        Provider.ExecuteNonQuery("CREATE TABLE Common_Language (LanguageID TEXT PRIMARY KEY)");

        Provider.AddColumn("Common_Language", "Enabled", DbType.Boolean);

        var tableInfo = ((SQLiteTransformationProvider)Provider).GetSQLiteTableInfo("Common_Language");
        var script = ((SQLiteTransformationProvider)Provider).GetSqlCreateTableScript("Common_Language");

        var columnProperty = tableInfo.Columns.Single(x => x.Name == "LanguageID").ColumnProperty;

        // Assert
        Assert.That(script, Does.Contain("LanguageID TEXT NULL PRIMARY KEY"));
    }

    [Test]
    public void AddColumn_HavingNullInPrimaryKey_HasNOTNULLAfterAddAnotherColumn()
    {
        // Arrange/Act
        Provider.ExecuteNonQuery("CREATE TABLE Common_Language (LanguageID TEXT NOT NULL PRIMARY KEY)");

        Provider.AddColumn("Common_Language", "Enabled", DbType.Boolean);

        var tableInfo = ((SQLiteTransformationProvider)Provider).GetSQLiteTableInfo("Common_Language");
        var script = ((SQLiteTransformationProvider)Provider).GetSqlCreateTableScript("Common_Language");

        var columnProperty = tableInfo.Columns.Single(x => x.Name == "LanguageID").ColumnProperty;

        // Assert
        Assert.That(script, Does.Contain("LanguageID TEXT NOT NULL PRIMARY KEY"));
    }

    [Test]
    public void AddColumn_HavingNotNullInPrimaryKey_Succeds()
    {
        // Arrange/Act
        Provider.ExecuteNonQuery("CREATE TABLE Common_Language (LanguageID INT NOT NULL PRIMARY KEY)");

        Provider.AddColumn("Common_Language", "Enabled", DbType.Boolean);

        var tableInfo = ((SQLiteTransformationProvider)Provider).GetSQLiteTableInfo("Common_Language");
        var script = ((SQLiteTransformationProvider)Provider).GetSqlCreateTableScript("Common_Language");

        var columnProperty = tableInfo.Columns.Single(x => x.Name == "LanguageID").ColumnProperty;
        var hasNull = columnProperty.IsSet(ColumnProperty.Null);

        // Assert
        Assert.That(script, Does.Contain("LanguageID INTEGER NOT NULL PRIMARY KEY"));
        Assert.That(hasNull, Is.False);
    }
}