public void ReturnCorrectDefinitionWithN(int n)
{
DataType dataType = DataType.Char(n);
string expectedDefinition = n == 1 ? "char" : $"char({n})";
Assert.Equal(expectedDefinition, dataType.Definition);
}
public void AddCollationCorrectly()
{
string tableName = nameof(AddCollationCorrectly);
string columnName = nameof(AddCollationCorrectly) + "Column";
string expectedCollation = "Latin1_General_BIN2";
Column column = new Column(columnName, DataType.Char())
{
Collation = expectedCollation
};
Table table = new Table(tableName);
table.Columns.AddAll(column);
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
Assert.False(table.IsTablePresentInDatabase(sqlConnection), "Table should not exist in the database.");
table.Create(sqlConnection);
Assert.True(table.IsTablePresentInDatabase(sqlConnection), "Table should exist in the database.");
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string sql = $"Select collation_name from sys.columns where name = '{columnName}'";
sqlCommand.CommandText = sql;
string actualCollation = (string)sqlCommand.ExecuteScalar();
Assert.Equal(expectedCollation, actualCollation);
}
table.Drop(sqlConnection);
}
}
public void CreateTableWithMultipleConstraints()
{
const string PrimaryKeyColumnName = "PK";
const string UniqueColumnName1 = "UQ1";
const string UniqueColumnName2 = "UQ2";
string tableName = nameof(CreateTableWithMultipleConstraints);
Table table = new Table(tableName);
Column column1 = new Column(PrimaryKeyColumnName, DataType.Int());
Column column2 = new Column(UniqueColumnName1, DataType.Char(10));
Column column3 = new Column(UniqueColumnName2, DataType.Money());
table.Columns.AddAll(column1, column2, column3);
PrimaryKeyConstraint primaryKeyConstraint = new PrimaryKeyConstraint();
primaryKeyConstraint.IndexType = IndexType.NONCLUSTERED;
primaryKeyConstraint.AddColumn(column1);
UniqueConstraint uniqueConstraint = new UniqueConstraint();
uniqueConstraint.IndexType = IndexType.CLUSTERED;
uniqueConstraint.AddColumns(Tuple.Create(column2, ColumnSort.DESC), Tuple.Create(column3, ColumnSort.ASC));
table.Constraints.AddAll(primaryKeyConstraint, uniqueConstraint);
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
Assert.False(table.IsTablePresentInDatabase(sqlConnection));
table.Create(sqlConnection);
Assert.True(table.IsTablePresentInDatabase(sqlConnection));
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string sql = $@"
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = '{tableName}'";
sqlCommand.CommandText = sql;
string primaryKeycolumnNameResult = (string)sqlCommand.ExecuteScalar();
Assert.Equal(PrimaryKeyColumnName, primaryKeycolumnNameResult);
sql = $"SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsUniqueCnst') = 1 AND TABLE_NAME = '{tableName}'";
sqlCommand.CommandText = sql;
int columnCount = (int)sqlCommand.ExecuteScalar();
Assert.Equal(2, columnCount);
}
table.Drop(sqlConnection);
Assert.False(table.IsTablePresentInDatabase(sqlConnection));
}
}
private static Table CreateTableWithEveryDataType(string tableName)
{
Table table = new Table(tableName);
table.Columns.AddAll(
new Column("ID", DataType.BigInt()),
new Column("Amount", DataType.Int()),
new Column("Maximum", DataType.TinyInt()),
new Column("Minimum", DataType.SmallInt()),
new Column("Payment", DataType.Money()),
new Column("Change", DataType.SmallMoney()),
new Column("Decimal", DataType.Decimal()),
new Column("Decimal-1", DataType.Decimal(10)),
new Column("Decimal-2", DataType.Decimal(20, 5)),
new Column("Numeric", DataType.Numeric()),
new Column("Numeric-1", DataType.Numeric(15)),
new Column("Numeric-2", DataType.Numeric(22, 11)),
new Column("VarChar", DataType.VarChar()),
new Column("VarChar-128", DataType.VarChar(128)),
new Column("VarChar-MAX", DataType.VarChar(DataType.MAX)),
new Column("Char", DataType.Char()),
new Column("Char-256", DataType.Char(256)),
new Column("VarBinary", DataType.VarBinary()),
new Column("VarBinary-512", DataType.VarBinary(512)),
new Column("VarBinary-MAX", DataType.VarBinary(DataType.MAX)),
new Column("Binary", DataType.Binary()),
new Column("Binary-1024", DataType.Binary(1024)),
new Column("NVarChar", DataType.NVarChar()),
new Column("NVarChar-248", DataType.NVarChar(248)),
new Column("NVarChar-MAX", DataType.NVarChar(DataType.MAX)),
new Column("NChar", DataType.NChar()),
new Column("NChar-400", DataType.NChar(400)),
new Column("Date", DataType.Date()),
new Column("DateTime", DataType.DateTime()),
new Column("DateTime2", DataType.DateTime2()),
new Column("DateTime2-2", DataType.DateTime2(2)),
new Column("Time", DataType.Time()),
new Column("Time-1", DataType.Time(1)),
new Column("UniqueIdentifier", DataType.UniqueIdentifier())
);
return(table);
}
private static void CreateCustomerTable(Database database)
{
Table customersTable = new Table(database, "Customers");
customersTable.Schema = "dbo";
Column idColumn = new Column(customersTable, "Id", DataType.Int);
idColumn.Identity = true;
idColumn.IdentityIncrement = 1;
idColumn.IdentitySeed = 1;
Column nameColumn = new Column(customersTable, "Name", DataType.VarChar(200));
nameColumn.Nullable = false;
Column addressColumn = new Column(customersTable, "Address", DataType.VarChar(4000));
Column cityColumn = new Column(customersTable, "City", DataType.VarChar(50));
Column stateColumn = new Column(customersTable, "State", DataType.Char(2));
Column zipColumn = new Column(customersTable, "Zip", DataType.VarChar(9));
customersTable.Columns.Add(idColumn);
customersTable.Columns.Add(nameColumn);
customersTable.Columns.Add(addressColumn);
customersTable.Columns.Add(cityColumn);
customersTable.Columns.Add(stateColumn);
customersTable.Columns.Add(zipColumn);
Index index = new Index(customersTable, "PK_Customers");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
IndexedColumn idIndexedColumn = new IndexedColumn(index, "Id");
index.IndexedColumns.Add(idIndexedColumn);
customersTable.Indexes.Add(index);
customersTable.Create();
}
public void ThrowInvalidCharacterSetLength(int n)
{
Assert.Throws <InvalidCharacterSetLengthException>(() => DataType.Char(n));
}
public void ReturnCorrectSizeWithN(int n)
{
DataType dataType = DataType.Char(n);
Assert.Equal(n, dataType.Size);
}
public void ReturnCorrectSizeWithoutN()
{
DataType dataType = DataType.Char();
Assert.Equal(1, dataType.Size);
}
public void ReturnCorrectDefinitionWithoutN()
{
DataType dataType = DataType.Char();
Assert.Equal("char", dataType.Definition);
}
public void returnCharType()
{
DataType dataType = DataType.Char();
Assert.Equal("char", dataType.Definition);
}
public void Test_AdditionalOperator_Constraint()
{
try
{
_connection.Execute(Db <DBForCreateTest> .Sql(db => DropTable(db.table1)));
}
catch { }
var sql = Db <DBForCreateTest> .Sql(db =>
CreateTable(db.table1,
new Column(db.table1.id, DataType.Int(), Default(10), NotNull(), PrimaryKey()),
new Column(db.table1.val1, DataType.Int()),
new Column(db.table1.val2, DataType.Char(10), Default("abc"), NotNull()),
Constraint("xxx") + Check(db.table1.id < 100),
Unique(db.table1.val2)
));
_connection.Execute(sql);
AssertEx.AreEqual(sql, _connection,
@"CREATE TABLE table1(
id INT DEFAULT 10 NOT NULL PRIMARY KEY,
val1 INT,
val2 CHAR(10) DEFAULT 'abc' NOT NULL,
CONSTRAINT xxx
CHECK(id < 100),
UNIQUE(val2))" );
}
public void AddColumnEncryptionCorrectly()
{
string tableName = nameof(AddColumnEncryptionCorrectly);
string columnName1 = tableName + "Column1";
string columnName2 = tableName + "Column2";
string columnMasterKeyName = tableName + "_CMK";
string columnEncryptionName = tableName + "_CEK";
ColumnMasterKey columnMasterKey = new ColumnMasterKey(columnMasterKeyName, KeyStoreProvider.AzureKeyVaultProvider, "Test/Path");
ColumnEncryptionKey columnEncryptionKey = new ColumnEncryptionKey(columnEncryptionName, columnMasterKey.Name, "0x555");
ColumnEncryption columnEncryption1 = new ColumnEncryption(columnEncryptionKey, ColumnEncryptionType.Deterministic);
Column column1 = new Column(columnName1, DataType.Char())
{
ColumnEncryption = columnEncryption1,
Collation = "Latin1_General_BIN2"
};
ColumnEncryption columnEncryption2 = new ColumnEncryption(columnEncryptionKey, ColumnEncryptionType.Randomized);
Column column2 = new Column(columnName2, DataType.NVarChar())
{
ColumnEncryption = columnEncryption2
};
Table table = new Table(tableName);
table.Columns.AddAll(column1, column2);
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
Assert.False(columnMasterKey.IsColumnMasterKeyPresentInDatabase(sqlConnection), "ColumnMasterKey should not exist in the database.");
columnMasterKey.Create(sqlConnection);
Assert.True(columnMasterKey.IsColumnMasterKeyPresentInDatabase(sqlConnection), "ColumnMasterKey should exist in the database.");
Assert.False(columnEncryptionKey.IsColumnEncryptionKeyPresentInDatabase(sqlConnection), "ColumnEncryptionKey should not exist in the database.");
columnEncryptionKey.Create(sqlConnection);
Assert.True(columnEncryptionKey.IsColumnEncryptionKeyPresentInDatabase(sqlConnection), "ColumnEncryptionKey should exist in the database.");
Assert.False(table.IsTablePresentInDatabase(sqlConnection), "Table should not exist in the database.");
table.Create(sqlConnection);
Assert.True(table.IsTablePresentInDatabase(sqlConnection), "Table should exist in the database.");
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
foreach (Column column in table.Columns)
{
string sql = $@"
Select c.encryption_type_desc, k.name
FROM sys.columns c JOIN sys.column_encryption_keys k ON (c.column_encryption_key_id = k.column_encryption_key_id)
WHERE c.name = '{column.Name}'";
sqlCommand.CommandText = sql;
using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
Assert.Equal(column.ColumnEncryption.ColumnEncryptionType.GetStringValue(), reader.GetString(0));
Assert.Equal(column.ColumnEncryption.ColumnEncryptionKeyName, reader.GetString(1));
}
}
}
}
table.Drop(sqlConnection);
columnEncryptionKey.Drop(sqlConnection);
columnMasterKey.Drop(sqlConnection);
}
}
public void Test_CreateTable_Oracle()
{
CleanUpCreateDropTestTable();
var sql = Db <DBForCreateTest> .Sql(db =>
CreateTable(db.table3,
new Column(db.table3.obj1, DataType.VarChar2(1)),
new Column(db.table3.obj2, DataType.NVarChar2(1)),
new Column(db.table3.obj3, DataType.Char(1)),
new Column(db.table3.obj4, DataType.NChar(1)),
new Column(db.table3.obj5, DataType.Number(1, 2)),
new Column(db.table3.obj6, DataType.Binary_Float()),
new Column(db.table3.obj7, DataType.Binary_Double()),
new Column(db.table3.obj8, DataType.Date()),
new Column(db.table3.obj9, DataType.TimeStamp(1)),
// new Column(db.table3.obj10, DataType.Raw()),
// new Column(db.table3.obj11, DataType.LongRaw()),
new Column(db.table3.obj12, DataType.BFile()),
new Column(db.table3.obj13, DataType.Blob()),
new Column(db.table3.obj14, DataType.Long()),
new Column(db.table3.obj15, DataType.Clob()),
new Column(db.table3.obj16, DataType.NClob()),
new Column(db.table3.obj17, DataType.XmlType()),
new Column(db.table3.obj18, DataType.TimeStampWithTimeZone()),
new Column(db.table3.obj19, DataType.TimeStampWithTimeZone(1))
));
_connection.Execute(sql);
}
public void Test_CreateTable_SqlServer()
{
CleanUpCreateDropTestTable();
var sql = Db <DBForCreateTest> .Sql(db =>
CreateTable(db.table3,
new Column(db.table3.obj1, DataType.Bit()),
new Column(db.table3.obj2, DataType.Date()),
new Column(db.table3.obj3, DataType.DateTime()),
new Column(db.table3.obj4, DataType.DateTime2()),
new Column(db.table3.obj5, DataType.DateTime2(2)),
new Column(db.table3.obj6, DataType.DateTimeOffset()),
new Column(db.table3.obj7, DataType.DateTimeOffset(3)),
new Column(db.table3.obj8, DataType.SmallDateTime()),
new Column(db.table3.obj9, DataType.Time()),
new Column(db.table3.obj10, DataType.Time(4)),
new Column(db.table3.obj11, DataType.Decimal()),
new Column(db.table3.obj12, DataType.Decimal(5)),
new Column(db.table3.obj13, DataType.Decimal(6, 6)),
new Column(db.table3.obj14, DataType.Numeric()),
new Column(db.table3.obj15, DataType.Numeric(8)),
new Column(db.table3.obj16, DataType.Numeric(9, 6)),
new Column(db.table3.obj17, DataType.Float()),
new Column(db.table3.obj18, DataType.Float(5)),
new Column(db.table3.obj19, DataType.Real()),
new Column(db.table3.obj20, DataType.BigInt()),
new Column(db.table3.obj21, DataType.Int()),
new Column(db.table3.obj22, DataType.SmallInt()),
new Column(db.table3.obj23, DataType.TinyInt()),
new Column(db.table3.obj24, DataType.Money()),
new Column(db.table3.obj25, DataType.SmallMoney()),
new Column(db.table3.obj26, DataType.Binary()),
new Column(db.table3.obj27, DataType.Binary(8)),
new Column(db.table3.obj28, DataType.VarBinary()),
new Column(db.table3.obj29, DataType.VarBinary(6)),
new Column(db.table3.obj30, DataType.Char()),
new Column(db.table3.obj31, DataType.Char(4)),
new Column(db.table3.obj32, DataType.VarChar()),
new Column(db.table3.obj33, DataType.VarChar(7)),
new Column(db.table3.obj34, DataType.NChar()),
new Column(db.table3.obj35, DataType.NChar(9)),
new Column(db.table3.obj36, DataType.NVarChar()),
new Column(db.table3.obj37, DataType.NVarChar(3)),
new Column(db.table3.obj38, DataType.NText()),
new Column(db.table3.obj39, DataType.Text()),
new Column(db.table3.obj40, DataType.Image()))
);
_connection.Execute(sql);
AssertEx.AreEqual(sql, _connection,
@"CREATE TABLE table3(
obj1 BIT,
obj2 DATE,
obj3 DATETIME,
obj4 DATETIME2,
obj5 DATETIME2(2),
obj6 DATETIMEOFFSET,
obj7 DATETIMEOFFSET(3),
obj8 SMALLDATETIME,
obj9 TIME,
obj10 TIME(4),
obj11 DECIMAL,
obj12 DECIMAL(5),
obj13 DECIMAL(6, 6),
obj14 NUMERIC,
obj15 NUMERIC(8),
obj16 NUMERIC(9, 6),
obj17 FLOAT,
obj18 FLOAT(5),
obj19 REAL,
obj20 BIGINT,
obj21 INT,
obj22 SMALLINT,
obj23 TINYINT,
obj24 MONEY,
obj25 SMALLMONEY,
obj26 BINARY,
obj27 BINARY(8),
obj28 VARBINARY,
obj29 VARBINARY(6),
obj30 CHAR,
obj31 CHAR(4),
obj32 VARCHAR,
obj33 VARCHAR(7),
obj34 NCHAR,
obj35 NCHAR(9),
obj36 NVARCHAR,
obj37 NVARCHAR(3),
obj38 NTEXT,
obj39 TEXT,
obj40 IMAGE)" );
}
public void Test_CreateTableIfNotExists()
{
CleanUpCreateDropTestTable();
var sql = Db <DBForCreateTest> .Sql(db =>
CreateTableIfNotExists(db.table1,
new Column(db.table1.id, DataType.Int()),
new Column(db.table1.val1, DataType.Int()),
new Column(db.table1.val2, DataType.Char(10))
));
_connection.Execute(sql);
AssertEx.AreEqual(sql, _connection,
@"CREATE TABLE IF NOT EXISTS table1(
id INT,
val1 INT,
val2 CHAR(10))" );
}
public void Test_CreateTable_MySQL()
{
CleanUpCreateDropTestTable();
var sql = Db <DBForCreateTest> .Sql(db =>
CreateTable(db.table3,
new Column(db.table3.obj1, DataType.TinyInt()),
new Column(db.table3.obj2, DataType.SmallInt()),
new Column(db.table3.obj3, DataType.MediumInt()),
new Column(db.table3.obj4, DataType.Int()),
new Column(db.table3.obj5, DataType.BigInt()),
new Column(db.table3.obj6, DataType.Float()),
new Column(db.table3.obj7, DataType.Double()),
new Column(db.table3.obj8, DataType.Decimal()),
new Column(db.table3.obj9, DataType.Char(1)),
new Column(db.table3.obj10, DataType.VarChar(1)),
new Column(db.table3.obj11, DataType.TinyBlob()),
new Column(db.table3.obj12, DataType.Blob()),
new Column(db.table3.obj13, DataType.MediumBlob()),
new Column(db.table3.obj14, DataType.LongBlob()),
new Column(db.table3.obj15, DataType.TinyText()),
new Column(db.table3.obj16, DataType.Text()),
new Column(db.table3.obj17, DataType.MediumText()),
new Column(db.table3.obj18, DataType.LongText()),
new Column(db.table3.obj19, DataType.Enum("a", "b")),
new Column(db.table3.obj20, DataType.Set("a", "b")),
new Column(db.table3.obj21, DataType.Date()),
new Column(db.table3.obj22, DataType.Time()),
new Column(db.table3.obj23, DataType.DateTime()),
new Column(db.table3.obj24, DataType.TimeStamp()),
new Column(db.table3.obj25, DataType.Year())
));
_connection.Execute(sql);
}
public void Test_CreateTable_DB2()
{
CleanUpCreateDropTestTable();
var sql = Db <DBForCreateTest> .Sql(db =>
CreateTable(db.table3,
new Column(db.table3.obj1, DataType.BigInt()),
new Column(db.table3.obj2, DataType.Binary()),
new Column(db.table3.obj3, DataType.Blob()),
new Column(db.table3.obj4, DataType.Char(1)),
new Column(db.table3.obj5, DataType.Clob()),
// new Column(db.table3.obj6, DataType.Currency()),
new Column(db.table3.obj7, DataType.Date()),
// new Column(db.table3.obj8, DataType.DateTime()),
new Column(db.table3.obj9, DataType.DbClob()),
new Column(db.table3.obj10, DataType.Double()),
new Column(db.table3.obj11, DataType.Float()),
new Column(db.table3.obj12, DataType.Graphic()),
new Column(db.table3.obj13, DataType.Int()),
new Column(db.table3.obj14, DataType.Integer()),
// new Column(db.table3.obj15, DataType.LongVarchar(1)),
// new Column(db.table3.obj16, DataType.LongVarGraphic()),
new Column(db.table3.obj17, DataType.Numeric()),
new Column(db.table3.obj18, DataType.Real()),
new Column(db.table3.obj19, DataType.SmallInt()),
// new Column(db.table3.obj20, DataType.Text()),
new Column(db.table3.obj21, DataType.Time()),
new Column(db.table3.obj22, DataType.TimeStamp()),
new Column(db.table3.obj23, DataType.VarChar(1))
// new Column(db.table3.obj24, DataType.VarGraphic())
));
_connection.Execute(sql);
}