Migrator.NET/src/Migrator.Tests/Providers/SQLite/SQLiteTransformationProvider_AddTableTests.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
115
116
117
118
119
120
121
122
123
124
125
126
127
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 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));
    }
}