public void TriggerWithNoQuotes()
{
//arrange
var osr = new OracleSchemaReader(ConnectionStrings.OracleHr, "System.Data.OracleClient");
var dt = new DatabaseTable();
dt
.AddColumn("ID")
.AddPrimaryKey()
.AddColumn("NAME");
dt.Triggers.Add(new DatabaseTrigger
{
//with spaces, line breaks
TriggerBody = @"BEGIN
SELECT MY_SEQ.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;"
});
//act
osr.PostProcessing(dt);
//assert
Assert.IsTrue(dt.HasAutoNumberColumn);
}
public void TestPostgreSqlColumnDefaults()
{
//MigrationGenerator does not output default values for columns
//https://github.com/martinjw/dbschemareader/issues/11
//arrange
var gen = new DdlGeneratorFactory(SqlType.PostgreSql).MigrationGenerator();
gen.IncludeSchema = false;
var pkSeqName = "Seq_PK_Generator";
var pkSeq = new DatabaseSequence()
{
Name = pkSeqName,
MinimumValue = 1,
IncrementBy = 1,
};
//gen.AddSequence(pkSeq).Replace(";", " CACHE;");
var newTable = new DatabaseTable { Name = "TestTable" };
var idColumn = newTable.AddColumn("Id", DbType.Int64);
idColumn.AddPrimaryKey("PK_TestTable");
idColumn.DefaultValue = $"nextval('{pkSeqName}')";
var summaryColumn = newTable.AddColumn("Summary", DbType.String);
summaryColumn.Length = 100;
//act
var ddl = gen.AddTable(newTable);
//assert
//expected
/*
CREATE SEQUENCE "Seq_PK_Generator" INCREMENT BY 1 MINVALUE 1 CACHE;
CREATE TABLE "TestTable"
(
"Id" BIGINT NOT NULL, --default value missing
"Summary" VARCHAR (100) NOT NULL
);
ALTER TABLE "TestTable" ADD CONSTRAINT "PK_TestTable" PRIMARY KEY ("Id");
*/
Assert.IsTrue(ddl.IndexOf("BIGINT NOT NULL DEFAULT nextval('Seq_PK_Generator'),", StringComparison.OrdinalIgnoreCase) != -1, "default value should be included");
}
private static DatabaseTable CreateTable()
{
var table = new DatabaseTable();
table.Name = "Test";
table.AddColumn("A", DbType.Int32).AddPrimaryKey("PK_TEST")
.AddColumn("B", DbType.Int32)
.AddColumn("C", DbType.String).AddLength(10).AddNullable();
return table;
}
/// <summary>
/// Adds the column.
/// </summary>
/// <param name="databasetable">The database table.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="netType">A CLR type (will attempt to translate to database type)</param>
/// <returns></returns>
/// <exception cref="ArgumentException">Cannot map the CLR type to a database type</exception>
public static DatabaseColumn AddColumn(this DatabaseTable databasetable, string columnName, Type netType)
{
if (databasetable == null)
{
throw new ArgumentNullException("databasetable", "databasetable must not be null");
}
var dataType = TypeToString(databasetable, netType);
if (string.IsNullOrEmpty(dataType))
{
throw new ArgumentException("Cannot map .net type to a database type", "netType");
}
return(databasetable.AddColumn(columnName, dataType));
}
/// <summary>
/// Adds the column.
/// </summary>
/// <typeparam name="T">The specified .Net type (string, int, decimal)</typeparam>
/// <param name="databasetable">The database table.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns></returns>
/// <exception cref="ArgumentException">Cannot map the CLR type to a database type</exception>
public static DatabaseColumn AddColumn <T>(this DatabaseTable databasetable, string columnName)
{
if (databasetable == null)
{
throw new ArgumentNullException("databasetable", "databasetable must not be null");
}
//we can't use a generic type constraint because we want primitive structs and strings.
var dataType = TypeToString(databasetable, typeof(T));
if (string.IsNullOrEmpty(dataType))
{
throw new ArgumentException("Cannot map .net type to a database type");
}
return(databasetable.AddColumn(columnName, dataType));
}
public void NoTriggers()
{
//arrange
var osr = new OracleSchemaReader(ConnectionStrings.OracleHr, "System.Data.OracleClient");
var dt = new DatabaseTable();
dt
.AddColumn("ID")
.AddPrimaryKey()
.AddColumn("NAME");
//act
osr.PostProcessing(dt);
//assert
Assert.IsFalse(dt.HasAutoNumberColumn);
}
public void TestIndex()
{
//arrange
var settings = new CodeWriterSettings();
settings.CodeTarget = CodeTarget.PocoEntityCodeFirst;
settings.WriteCodeFirstIndexAttribute = true;
var classBuilder = new ClassBuilder();
var table = new DatabaseTable { Name = "Test" };
var nameColumn = table.AddColumn<int>("Id").AddPrimaryKey()
.AddColumn<string>("Name").AddNullable().AddIndex("IX_NAME");
var target = new DataAnnotationWriter(true, settings);
//act
target.Write(classBuilder, nameColumn);
var result = classBuilder.ToString().Trim(); //ignore lines
//assert
Assert.AreEqual("[Index(\"IX_NAME\")]", result);
}
public void TriggerWithQuotes()
{
//arrange
var osr = new OracleSchemaReader(ConnectionStrings.OracleHr, "System.Data.OracleClient");
var dt = new DatabaseTable();
dt
.AddColumn("ID")
.AddPrimaryKey()
.AddColumn("NAME");
dt.Triggers.Add(new DatabaseTrigger
{
//generated by SqlDeveloper
TriggerBody = @"CREATE OR REPLACE TRIGGER ""DB"".""MYTRIGGER"" before insert on ""DB"".""TABLE1"" for each row begin if inserting then if :NEW.""ID"" is null then select MY_SEQ.nextval into :NEW.""ID"" from dual; end if; end if; end;"
});
//act
osr.PostProcessing(dt);
//assert
Assert.IsTrue(dt.HasAutoNumberColumn);
}
private void ReadProperty(XElement property, DatabaseTable table)
{
var name = (string)property.Attribute("Name");
var type = (string)property.Attribute("Type");
var nullable = (bool?)property.Attribute("Nullable") ?? true;
var maxLength = (int?)property.Attribute("MaxLength");
var precision = (int?)property.Attribute("Precision");
var scale = (int?)property.Attribute("Scale");
var defaultValue = (string)property.Attribute("DefaultValue");
var storeGeneratedPattern = (string)property.Attribute("StoreGeneratedPattern");
if (type.EndsWith("(max)", StringComparison.OrdinalIgnoreCase))
{
type = type.Substring(0, type.Length - 5);
maxLength = -1;
}
//timestamp is a varbinary(8) computed
if (type == "binary" && maxLength == 8 && (string)property.Attribute("StoreGeneratedPattern") == "Computed")
{
type = "timestamp";
maxLength = null;
}
var column = table.AddColumn(name, type);
column.DefaultValue = defaultValue;
column.Length = maxLength;
column.Precision = precision;
column.Scale = scale;
column.Nullable = nullable;
if (storeGeneratedPattern == "Identity") column.IsAutoNumber = true;
FixColumn(column);
}
public void TestIndexNotNeededForPrimaryKey()
{
//arrange
var settings = new CodeWriterSettings();
settings.CodeTarget = CodeTarget.PocoEntityCodeFirst;
settings.WriteCodeFirstIndexAttribute = true;
var classBuilder = new ClassBuilder();
var table = new DatabaseTable { Name = "Test" };
table.AddColumn<int>("Id").AddPrimaryKey().AddIndex("PK_TEST")
.AddColumn<string>("Category").AddNullable()
.AddColumn<string>("Name").AddNullable().AddIndex("IX_NAME")
;
var idColumn = table.PrimaryKeyColumn;
var target = new DataAnnotationWriter(true, settings);
//act
target.Write(classBuilder, idColumn);
var result = classBuilder.ToString().Trim(); //ignore lines
//assert
Assert.IsTrue(result.IndexOf("[Index", StringComparison.OrdinalIgnoreCase) == -1, "Should be just[Key]");
}
/// <summary>
/// Adds a column.
/// </summary>
/// <param name="databaseTable">The database table.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="dbType"><see cref="DbType"/>.</param>
/// <param name="columnInitialization">The column initialization.</param>
/// <returns></returns>
public static DatabaseColumn AddColumn(this DatabaseTable databaseTable, string columnName, DbType dbType, Action <DatabaseColumn> columnInitialization)
{
var dataType = DataTypeMappingFactory.DataTypeMapper(databaseTable).Map(dbType);
return(databaseTable.AddColumn(columnName, dataType, columnInitialization));
}