public override string DropIndex(DatabaseTable databaseTable, DatabaseIndex index)
{
return string.Format(CultureInfo.InvariantCulture,
"DROP INDEX {0}{1};",
SchemaPrefix(index.SchemaOwner),
Escape(index.Name));
}
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);
}
/// <summary>
/// Initializes a new instance of the <see cref="ProcedureGenerator"/> class.
/// </summary>
/// <param name="table">The table.</param>
public ProcedureGenerator(DatabaseTable table)
: base(table)
{
SqlWriter = new SqlWriter(table, SqlType.Oracle);
SqlWriter.InStoredProcedure = true;
PackageName = "PACK_" + TableName;
}
protected TableGeneratorBase(DatabaseTable table)
{
Table = table;
TableName = table.Name;
IncludeSchema = true;
IncludeDefaultValues = true;
}
public DatabaseTable Load(string tableName, CancellationToken ct)
{
if (ct.IsCancellationRequested) return new DatabaseTable();
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.Tables, tableName, null, null);
var schemaOwner = _schemaReader.Owner;
DatabaseTable table;
using (var ds = _schemaReader.Table(tableName))
{
if (ds == null) return null;
if (ds.Tables.Count == 0) return null;
ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.Tables, tableName, null, null);
table = _databaseSchema.FindTableByName(tableName, schemaOwner);
if (table == null)
{
table = new DatabaseTable();
_databaseSchema.Tables.Add(table);
}
table.Name = tableName;
table.SchemaOwner = schemaOwner;
//columns must be done first as it is updated by the others
schemaOwner = AddColumns(schemaOwner, tableName, table, ds);
AddConstraints(ds, table);
AddOthers(schemaOwner, tableName, table, ds);
_schemaReader.PostProcessing(table);
}
return table;
}
public override string AlterColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn, DatabaseColumn originalColumn)
{
var sb = new StringBuilder();
var defaultName = "DF_" + databaseTable.Name + "_" + databaseColumn.Name;
if (originalColumn != null)
{
if (originalColumn.DefaultValue != null)
{
//have to drop default contraint
var df = FindDefaultConstraint(databaseTable, databaseColumn.Name);
if (df != null)
{
defaultName = df.Name;
sb.AppendLine("ALTER TABLE " + TableName(databaseTable)
+ " DROP CONSTRAINT " + Escape(defaultName) + ";");
}
}
}
//we could check if any of the properties are changed here
sb.AppendLine(base.AlterColumn(databaseTable, databaseColumn, originalColumn));
if (databaseColumn.DefaultValue != null)
{
//add default contraint
sb.AppendLine("ALTER TABLE " + TableName(databaseTable) +
" ADD CONSTRAINT " + Escape(defaultName) +
" DEFAULT " + databaseColumn.DefaultValue +
" FOR " + Escape(databaseColumn.Name) + ";");
}
return sb.ToString();
}
public void Execute(DatabaseTable databaseTable, DatabaseTable compareTable)
{
var firstTriggers = databaseTable.Triggers;
var secondTriggers = compareTable.Triggers;
foreach (var trigger in firstTriggers)
{
var indexName = trigger.Name;
var match = secondTriggers.FirstOrDefault(c => c.Name == indexName);
if (match == null)
{
CreateResult(ResultType.Delete, databaseTable, indexName,
_writer.DropTrigger(databaseTable, trigger));
continue;
}
if (trigger.TriggerBody != match.TriggerBody ||
trigger.TriggerType != match.TriggerType ||
trigger.TriggerEvent != match.TriggerEvent)
{
CreateResult(ResultType.Change, databaseTable, indexName,
_writer.DropTrigger(databaseTable, trigger) + Environment.NewLine +
_writer.AddTrigger(databaseTable, match));
}
}
foreach (var trigger in secondTriggers)
{
var indexName = trigger.Name;
var firstConstraint = firstTriggers.FirstOrDefault(c => c.Name == indexName);
if (firstConstraint == null)
{
CreateResult(ResultType.Add, databaseTable, indexName,
_writer.AddTrigger(databaseTable, trigger));
}
}
}
public void WithBidirectionalDepndencyTopologicalSort()
{
//arrange
var schema = new DatabaseSchema(null, null);
var orders = new DatabaseTable();
orders.Name = "countries";
var productsFk = new DatabaseConstraint();
productsFk.ConstraintType = ConstraintType.ForeignKey;
productsFk.RefersToTable = "capitalcities";
orders.AddConstraint(productsFk);
schema.Tables.Add(orders);
var products = new DatabaseTable();
products.Name = "capitalcities";
var categoriesFk = new DatabaseConstraint();
categoriesFk.ConstraintType = ConstraintType.ForeignKey;
categoriesFk.RefersToTable = "countries";
products.AddConstraint(categoriesFk);
schema.Tables.Add(products);
//a country has one capital city
//a capital city is in one country
//But bidirectional foreign keys is terrible database design - you really only need one direction.
//(you have to save the country with a null capital, then the capital, then update the country again).
//Topological sorts don't support cycles, so we should just get back the original list
//act
var sortedTables = SchemaTablesSorter.TopologicalSort(schema);
//assert
Assert.AreEqual(2, sortedTables.Count());
//non-deterministic order
}
/// <summary>
/// Finds the trigger that uses a sequence for autonumbering. May return NULL.
/// </summary>
/// <param name="databaseTable">The database table.</param>
/// <returns></returns>
public static OracleSequenceTrigger FindTrigger(DatabaseTable databaseTable)
{
var triggers = databaseTable.Triggers;
if (triggers.Count == 0) return null;
var pk = databaseTable.PrimaryKeyColumn ?? databaseTable.Columns.Find(x => x.IsAutoNumber);
if (pk == null) return null;
//the trigger body will look something like "SELECT MYSEQ.NEXTVAL INTO :NEW.ID FROM DUAL;"
var pattern = ".NEXTVAL\\s+?INTO\\s+?:NEW.\"?" + pk.Name;
var regex = new Regex(pattern, RegexOptions.IgnoreCase);
foreach (var databaseTrigger in databaseTable.Triggers)
{
var body = databaseTrigger.TriggerBody;
var match = regex.Match(body);
if (!match.Success) continue;
var result = new OracleSequenceTrigger(databaseTrigger);
//let's write the sequence if we can find it
var seqName = ParseSequenceName(body, match.Index);
if (seqName == null) return result;
result.SequenceName = seqName;
if (databaseTable.DatabaseSchema != null)
{
result.DatabaseSequence = databaseTable.DatabaseSchema.Sequences
.FirstOrDefault(x => seqName.Equals(x.Name, StringComparison.OrdinalIgnoreCase));
}
return result;
}
return null;
}
public bool RunSprocs(DirectoryInfo directory, SqlType dialect, DatabaseTable table)
{
if (table == null)
{
Message = "No table";
return false;
}
var gen = new DdlGeneratorFactory(dialect).ProcedureGenerator(table);
if (gen == null)
{
//there is no sproc provider (SQLite)
Message = @"There is no sproc generator";
return false;
}
var path = Path.Combine(directory.FullName, table.Name + "_sprocs.sql");
try
{
gen.WriteToScript(path);
Message = @"Wrote " + path;
return true;
}
catch (Exception exception)
{
Message =
@"An error occurred while creating the script.\n" + exception.Message;
}
return false;
}
private static IList<DatabaseTable> Convert(DataTable dataTable)
{
var list = new List<DatabaseTable>();
if ((dataTable == null) || (dataTable.Columns.Count == 0) || (dataTable.Rows.Count == 0))
{
return list;
}
const string schemaKey = "SchemaOwner";
const string tableKey = "TableName";
const string descKey = "ColumnDescription";
const string columnKey = "ColumnName";
foreach (DataRow row in dataTable.Rows)
{
var schema = row[schemaKey].ToString();
var name = row[tableKey].ToString();
var col = row[columnKey].ToString();
var desc = row[descKey].ToString();
var table = list.FirstOrDefault(t => t.SchemaOwner == schema && t.Name == name);
if (table == null)
{
table = new DatabaseTable();
table.Name = name;
table.SchemaOwner = schema;
list.Add(table);
}
table.AddColumn(col).Description = desc;
}
return list;
}
public virtual string AddColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn)
{
var tableGenerator = CreateTableGenerator(databaseTable);
var addColumn = tableGenerator.WriteColumn(databaseColumn).Trim();
if (string.IsNullOrEmpty(databaseColumn.DefaultValue) && !databaseColumn.Nullable)
{
var dt = databaseColumn.DataType;
if (dt == null || dt.IsString)
{
addColumn += " DEFAULT ''"; //empty string
}
else if (dt.IsNumeric)
{
addColumn += " DEFAULT 0";
}
else if (dt.IsDateTime)
{
addColumn += " DEFAULT CURRENT_TIMESTAMP";
}
//make sure the NOT NULL is AFTER the default
addColumn = addColumn.Replace(" NOT NULL ", " ") + " NOT NULL";
}
return string.Format(CultureInfo.InvariantCulture,
"ALTER TABLE {0} ADD {1}",
TableName(databaseTable),
addColumn) + LineEnding();
}
public string Write()
{
WriteUsings();
MappingClassName = _mappingNamer.NameMappingClass(_table.NetName);
_inheritanceTable = _table.FindInheritanceTable();
using (_cb.BeginNest("namespace " + _codeWriterSettings.Namespace + ".Mapping"))
{
using (_cb.BeginNest("public class " + MappingClassName + " : EntityTypeConfiguration<" + _table.NetName + ">", "Class mapping to " + _table.Name + " table"))
{
using (_cb.BeginNest("public " + MappingClassName + "()", "Constructor"))
{
MapTableName();
AddPrimaryKey();
_cb.AppendLine("// Properties");
WriteColumns();
WriteForeignKeys();
WriteNavigationProperties();
}
}
}
return _cb.ToString();
}
public OverrideWriter(ClassBuilder classBuilder, DatabaseTable table, INamer namer)
{
_cb = classBuilder;
_table = table;
_namer = namer;
NetName = table.NetName;
}
public override string DropColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn)
{
return string.Format(CultureInfo.InvariantCulture,
"ALTER TABLE {0} DROP COLUMN {1} CASCADE;",
TableName(databaseTable),
Escape(databaseColumn.Name));
}
private void AddConstraints(DataSet ds, DatabaseTable table)
{
if (ds.Tables.Contains(_schemaReader.PrimaryKeysCollectionName))
{
var converter = new SchemaConstraintConverter(ds.Tables[_schemaReader.PrimaryKeysCollectionName],
ConstraintType.PrimaryKey);
var pkConstraints = converter.Constraints();
PrimaryKeyLogic.AddPrimaryKey(table, pkConstraints);
}
if (ds.Tables.Contains(_schemaReader.ForeignKeysCollectionName))
{
var converter = new SchemaConstraintConverter(ds.Tables[_schemaReader.ForeignKeysCollectionName],
ConstraintType.ForeignKey);
table.AddConstraints(converter.Constraints());
}
if (ds.Tables.Contains(_schemaReader.ForeignKeyColumnsCollectionName))
{
var fkConverter = new ForeignKeyColumnConverter(ds.Tables[_schemaReader.ForeignKeyColumnsCollectionName]);
fkConverter.AddForeignKeyColumns(table.ForeignKeys);
}
if (ds.Tables.Contains(_schemaReader.UniqueKeysCollectionName))
{
var converter = new SchemaConstraintConverter(ds.Tables[_schemaReader.UniqueKeysCollectionName],
ConstraintType.UniqueKey);
table.AddConstraints(converter.Constraints());
}
}
private void ComparePrimaryKey(DatabaseTable databaseTable, DatabaseTable match)
{
if (databaseTable.PrimaryKey == null && match.PrimaryKey == null)
{
//no primary key before or after. Oh dear.
Trace.TraceWarning("-- NB: " + databaseTable.Name + " has no primary key!");
return;
}
if (databaseTable.PrimaryKey == null)
{
//forgot to put pk on it
CreateResult(ResultType.Add, databaseTable, match.PrimaryKey.Name,
_writer.AddConstraint(databaseTable, match.PrimaryKey));
}
else if (match.PrimaryKey == null)
{
//why oh why would you want to drop the primary key?
CreateResult(ResultType.Change, databaseTable, databaseTable.PrimaryKey.Name,
_writer.DropConstraint(databaseTable, databaseTable.PrimaryKey) + Environment.NewLine +
"-- NB: " + databaseTable.Name + " has no primary key!");
}
else if (!ConstraintColumnsEqual(databaseTable.PrimaryKey, match.PrimaryKey))
{
CreateResult(ResultType.Change, databaseTable, databaseTable.PrimaryKey.Name,
_writer.DropConstraint(databaseTable, databaseTable.PrimaryKey) + Environment.NewLine +
_writer.AddConstraint(match, match.PrimaryKey));
}
}
private static IList<DatabaseTable> Convert(DataTable dataTable)
{
var list = new List<DatabaseTable>();
if ((dataTable == null) || (dataTable.Columns.Count == 0) || (dataTable.Rows.Count == 0))
{
return list;
}
const string schemaKey = "SchemaOwner";
const string tableKey = "TableName";
const string descKey = "TableDescription";
foreach (DataRow row in dataTable.Rows)
{
var schema = row[schemaKey].ToString();
var name = row[tableKey].ToString();
var desc = row[descKey].ToString();
var table = new DatabaseTable();
table.Name = name;
table.SchemaOwner = schema;
table.Description = desc;
list.Add(table);
}
return list;
}
/// <summary>
/// Converts the "Tables" DataTable into <see cref="DatabaseTable"/> objects.
/// </summary>
/// <remarks>
/// Note the SqlServer DataTable includes views, which we explicitly remove.
/// </remarks>
public static List<DatabaseTable> Tables(DataTable dt)
{
List<DatabaseTable> list = new List<DatabaseTable>();
TableKeyMap keyMap = new TableKeyMap(dt);
foreach (DataRow row in dt.Rows)
{
string type = row[keyMap.TypeKey].ToString();
//Sql server has base tables and views. Oracle has system and user
if (IsNotTable(type)) continue;
DatabaseTable t = new DatabaseTable();
t.Name = row[keyMap.TableName].ToString();
//exclude Oracle bin tables
if (t.Name.StartsWith("BIN$", StringComparison.OrdinalIgnoreCase)) continue;
if (!string.IsNullOrEmpty(keyMap.OwnerKey))
{
t.SchemaOwner = row[keyMap.OwnerKey].ToString();
//Db2 system tables creeping in
if (keyMap.IsDb2 && t.SchemaOwner.Equals("SYSTOOLS", StringComparison.OrdinalIgnoreCase)) continue;
}
list.Add(t);
}
return list;
}
public void Execute(DatabaseTable baseTable, DatabaseTable compareTable)
{
ComparePrimaryKey(baseTable, compareTable);
Compare(baseTable, baseTable.UniqueKeys, compareTable.UniqueKeys);
Compare(baseTable, baseTable.CheckConstraints, compareTable.CheckConstraints);
Compare(baseTable, baseTable.ForeignKeys, compareTable.ForeignKeys);
}
public SqlWriterCommonTest(SqlType sqlType, DatabaseTable table, DbProviderFactory factory, string connectionString)
{
_connectionString = connectionString;
_factory = factory;
_table = table;
_sqlType = sqlType;
}
public DatabaseInserter CreateDatabaseInserter(DbConnection connection, string insertSql, DatabaseTable databaseTable)
{
if (_useSqlServerCe)
{
return new SqlServerInserter(connection, insertSql, databaseTable);
}
return new DatabaseInserter(connection, insertSql);
}
public override string DropIndex(DatabaseTable databaseTable, DatabaseIndex index)
{
//no "ON table" syntax
return string.Format(CultureInfo.InvariantCulture,
"DROP INDEX {0}.{1}",
databaseTable.Name,
Escape(index.Name));
}
public ProcedureGenerator(DatabaseTable table)
: base(table)
{
SqlWriter = new SqlWriter(table, SqlType.Db2);
SqlWriter.InStoredProcedure = true;
SqlWriter.FormatParameter = x => { return "p_" + x; };
FormatParameter = SqlWriter.FormatParameter;
}
//private CodeInserter _codeInserter;
/// <summary>
/// Initializes a new instance of the <see cref="ClassWriter"/> class.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="codeWriterSettings">The code writer settings.</param>
public ClassWriter(DatabaseTable table, CodeWriterSettings codeWriterSettings)
{
_codeWriterSettings = codeWriterSettings;
_table = table;
_cb = new ClassBuilder();
//_codeInserter = codeWriterSettings.CodeInserter;
//if (_codeInserter == null) _codeInserter = new CodeInserter();
}
public override string AddTrigger(DatabaseTable databaseTable, DatabaseTrigger trigger)
{
if (string.IsNullOrEmpty(trigger.TriggerBody))
return "-- add trigger " + trigger.Name;
//db2 returns the entire "Create trigger" statement, so this is very easy
return trigger.TriggerBody + ";";
}
public TableGenerator(DatabaseTable table)
: base(table)
{
SqlType? originSqlType = null;
if (table.DatabaseSchema != null)
originSqlType = ProviderToSqlType.Convert(table.DatabaseSchema.Provider);
DataTypeWriter = new DataTypeWriter(originSqlType);
}
/// <summary>
/// Initializes a new instance of the <see cref="ProcedureGenerator"/> class.
/// </summary>
/// <param name="table">The table.</param>
public ProcedureGenerator(DatabaseTable table)
: base(table)
{
SqlWriter = new SqlWriter(table, SqlType.SqlServer);
SqlType? originSqlType = null;
if (table.DatabaseSchema != null)
originSqlType = ProviderToSqlType.Convert(table.DatabaseSchema.Provider);
_dataTypeWriter = new DataTypeWriter(originSqlType);
}
/// <summary>
/// Returns the name of an inverse foreign key property. Uses <see cref="NameCollection"/>
/// For single fks, it's a collection using the name of the fk table.
/// For multiple fks, it's a collection using the name of the fk columns
/// </summary>
/// <param name="targetTable">The target table.</param>
/// <param name="table">The table.</param>
/// <param name="foreignKey">The foreign key.</param>
/// <returns>
/// Eg OrderLine has fk to Order. Order will have an ICollection<OrderLine> called "OrderLineCollection".
/// Multiple fk eg Order has Delivery Address and Billing Address.
/// Address will have an ICollection<Order> called "DeliveryAddressCollection",
/// and another ICollection<Order> called "BillingAddressCollection"
/// </returns>
public virtual string ForeignKeyCollectionName(string targetTable, DatabaseTable table, DatabaseConstraint foreignKey)
{
var fksToTarget = table.ForeignKeys.Where(x => x.RefersToTable == targetTable).ToList();
string name = table.NetName;
if (fksToTarget.Count > 1)
name = string.Join("", foreignKey.Columns.Select(x => table.FindColumn(x).NetName).ToArray());
return NameCollection(name);
}
private static void AssertEmployeeTableValid(DatabaseTable employees)
{
Assert.AreEqual(11, employees.Columns.Count, "The employee table contains 11 columns.");
Assert.AreEqual(2, employees.ForeignKeys.Count, "The employee table contains 2 foreign keys.");
var integ28 = employees.ForeignKeys.First(f => f.Name == "INTEG_28");
Assert.AreEqual(1, integ28.Columns.Count, "The INTEG_28 fk on employee table references with 1 key.");
var integ29 = employees.ForeignKeys.First(f => f.Name == "INTEG_29");
Assert.AreEqual(3, integ29.Columns.Count, "The INTEG_28 fk on employee table references with 3 key.");
}
