public void FindOracleAutoNumberTrigger()
{
//arrange
var schema = new DatabaseSchema(null, SqlType.Oracle);
var table = schema.AddTable("Test");
var id = table.AddColumn<int>("Id").AddPrimaryKey();
id.IsAutoNumber = true;
table.AddColumn<string>("Name").AddLength(200);
var databaseTrigger = new DatabaseTrigger
{
Name = "Test_INS_TRG",
TriggerEvent = "INSERT",
TriggerBody = @"BEGIN
SELECT ""Test_SEQ"".NEXTVAL INTO :NEW.""Id"" FROM DUAL;
END;",
TriggerType = "BEFORE EACH ROW",
};
table.Triggers.Add(databaseTrigger);
var databaseSequence = new DatabaseSequence { IncrementBy = 1, MinimumValue = 0, Name = "Test_SEQ" };
schema.Sequences.Add(databaseSequence);
//act
var result = OracleSequenceTrigger.FindTrigger(table);
//assert
Assert.IsNotNull(result);
Assert.IsNotNull(result.DatabaseTrigger);
Assert.IsNotNull(result.DatabaseSequence);
Assert.AreEqual(databaseSequence, result.DatabaseSequence);
Assert.AreEqual(databaseTrigger, result.DatabaseTrigger);
}
private void CreateResult(ResultType resultType, DatabaseSequence sequence, string script)
{
var result = new CompareResult
{
SchemaObjectType = SchemaObjectType.Sequence,
ResultType = resultType,
Name = sequence.Name,
SchemaOwner = sequence.SchemaOwner,
Script = script
};
_results.Add(result);
}
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");
}
public static List<DatabaseSequence> Sequences(DataTable dt)
{
List<DatabaseSequence> list = new List<DatabaseSequence>();
var sequenceKeyMap = new SequenceKeyMap(dt);
foreach (DataRow row in dt.Rows)
{
DatabaseSequence seq = new DatabaseSequence();
seq.Name = row[sequenceKeyMap.Key].ToString();
seq.SchemaOwner = row[sequenceKeyMap.OwnerKey].ToString();
if (!string.IsNullOrEmpty(sequenceKeyMap.MinValueKey))
seq.MinimumValue = GetNullableDecimal(row[sequenceKeyMap.MinValueKey]);
if (!string.IsNullOrEmpty(sequenceKeyMap.MaxValueKey))
seq.MaximumValue = GetNullableDecimal(row[sequenceKeyMap.MaxValueKey]);
if (!string.IsNullOrEmpty(sequenceKeyMap.IncrementKey))
seq.IncrementBy = GetNullableInt(row[sequenceKeyMap.IncrementKey]) ?? 1;
list.Add(seq);
}
return list;
}
public string AddSequence(DatabaseSequence sequence)
{
//amazingly SQLServer Denali has the same syntax as Oracle. http://msdn.microsoft.com/en-us/library/ff878091%28v=SQL.110%29.aspx
var sb = new StringBuilder();
sb.Append("CREATE SEQUENCE " + SchemaPrefix(sequence.SchemaOwner) + Escape(sequence.Name) +
" INCREMENT BY " + sequence.IncrementBy);
//min/max are optional- if they look like defaults, no need to write them out
if (sequence.MinimumValue.HasValue && sequence.MinimumValue != 0)
{
sb.Append(" MINVALUE " + sequence.MinimumValue);
}
if (sequence.MaximumValue.HasValue && sequence.MaximumValue != 999999999999999999999999999M)
{
sb.Append(" MAXVALUE " + sequence.MaximumValue);
}
sb.Append(";");
return sb.ToString();
}
public string DropSequence(DatabaseSequence sequence)
{
return "DROP SEQUENCE " + SchemaPrefix(sequence.SchemaOwner) + Escape(sequence.Name) + ";";
}
public string AddSequence(DatabaseSequence sequence)
{
return _migration.AddSequence(sequence);
}
public string DropSequence(DatabaseSequence sequence)
{
return _migration.DropSequence(sequence);
}