/// <summary>
/// Gets the rename table SQL.
/// </summary>
/// <param name="dbTable">The database table.</param>
/// <param name="table">The table.</param>
/// <param name="prefix">The prefix.</param>
/// <returns>System.String.</returns>
protected override string GetRenameTableSql(DbTableDefinition dbTable, ITableDefinition table, string prefix = null)
{
return string.Format("EXECUTE sp_rename N'{1}{0}', N'{2}', 'OBJECT'", table.Name, prefix, dbTable.TableName);
}
/// <summary>
/// Gets the data copy SQL.
/// </summary>
/// <param name="dbTable">The database table.</param>
/// <param name="table">The table.</param>
/// <param name="prefix">The prefix.</param>
/// <returns>System.String.</returns>
protected override string GetDataCopySql(DbTableDefinition dbTable, ITableDefinition table, string prefix = null)
{
var result = new StringBuilder();
var insertColumnList = new StringBuilder();
var selectColumnList = new StringBuilder();
// copy all fields
foreach (var field in table.FieldList)
{
if (!field.GenerateDbColumn)
continue;
var dbColumn = dbTable.Columns.FirstOrDefault(x => x.ColumnName == field.ColumnName);
// only if they present in old table
if (dbColumn == null)
continue;
insertColumnList.Append("\"").Append(OracleNamesTranslator.Translate(field.ColumnName)).Append("\",");
var sqlType = GetSqlType(field);
// convert values
var oldType = dbColumn.ColumnType.ToLower();
var newType = GetSqlType(field).ToLower();
if (AreSameDataTypes(oldType, newType) == false)
{
var defaultValue = string.IsNullOrEmpty(field.DefaultValue) ? "NULL" : field.DefaultValue;
if (IsNumericType(newType))
selectColumnList.Append("CASE WHEN REGEXP_LIKE(\"")
.Append(OracleNamesTranslator.Translate(dbColumn.ColumnName))
.Append("\",'^-?[[:digit:],.]*$') THEN ")
.Append("CAST(REPLACE(\"")
.Append(OracleNamesTranslator.Translate(dbColumn.ColumnName))
.Append("\", ',', '.') AS ")
.Append(sqlType)
.Append(") ELSE ")
.Append(defaultValue)
.Append(" END,");
else if (IsDateType(newType))
selectColumnList.Append("CASE WHEN IsDate(\"")
.Append(OracleNamesTranslator.Translate(dbColumn.ColumnName))
.Append("\")=1 THEN ")
.Append("CAST(\"")
.Append(OracleNamesTranslator.Translate(dbColumn.ColumnName))
.Append("\" AS ")
.Append(sqlType)
.Append(") ELSE NULL END,");
else if (IsStringType(newType))
selectColumnList.Append("CAST(\"").Append(OracleNamesTranslator.Translate(dbColumn.ColumnName)).Append("\" AS ").Append(sqlType).Append("),");
else
selectColumnList.Append(defaultValue).Append(",");
}
else
selectColumnList.Append("CAST(\"").Append(OracleNamesTranslator.Translate(dbColumn.ColumnName)).Append("\" AS ").Append(sqlType).Append("),");
}
insertColumnList.Remove(insertColumnList.Length - 1, 1);
selectColumnList.Remove(selectColumnList.Length - 1, 1);
result.Append("INSERT INTO \"")
.Append(OracleNamesTranslator.TranslatePrefix(prefix))
.Append(OracleNamesTranslator.Translate(table.Name))
.Append("\" (")
.Append(insertColumnList)
.AppendLine(")");
result.Append("SELECT ").Append(selectColumnList).Append(" FROM \"").Append(OracleNamesTranslator.Translate(dbTable.TableName)).AppendLine("\";;");
return result.ToString();
}
/// <summary>
/// Gets the fk constraints SQL.
/// </summary>
/// <param name="dbTable">The database table.</param>
/// <param name="fk">The fk.</param>
/// <param name="table">The table.</param>
/// <returns>System.String.</returns>
protected override string GetFKConstraintsSql(DbTableDefinition dbTable, DbForeignKeyDefinition fk, ITableDefinition table)
{
var fKeys = string.Join(",", fk.ForeignKeyColumnsName);
var pKeys = string.Join(",", fk.PrimaryKeyColumnsName);
return string.Format(
@"
IF (EXISTS (SELECT * FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_SCHEMA] = 'dbo' AND [TABLE_NAME] = N'{0}'))
BEGIN
ALTER TABLE [{0}] WITH CHECK ADD CONSTRAINT [{1}] FOREIGN KEY([{2}])
REFERENCES [{3}] ({4})
ALTER TABLE [{0}] CHECK CONSTRAINT [{1}]
END
",
fk.ForeignKeyTableName,
fk.ConstraintName,
fKeys,
fk.PrimaryKeyTableName,
pKeys);
}
/// <summary>
/// Gets the rename table SQL.
/// </summary>
/// <param name="dbTable">The database table.</param>
/// <param name="table">The table.</param>
/// <param name="prefix">The prefix.</param>
/// <returns>System.String.</returns>
protected override string GetRenameTableSql(DbTableDefinition dbTable, ITableDefinition table, string prefix = null)
{
var sb = new StringBuilder();
sb.AppendFormat(@"RENAME ""{1}{0}"" TO ""{2}"";;",
OracleNamesTranslator.Translate(table.Name),
OracleNamesTranslator.TranslatePrefix(prefix),
OracleNamesTranslator.Translate(dbTable.TableName));
var tableFieldDefinition = table.FieldList.FirstOrDefault(x => x.IsIdentity);
if (tableFieldDefinition != null)
sb.AppendFormat(@"ALTER TRIGGER ""{0}"" RENAME TO ""{1}"";;",
OracleNamesTranslator.Translate(OracleNamesTranslator.TranslatePrefix(prefix) + "trg_" + table.Name + "_" + tableFieldDefinition.ColumnName),
OracleNamesTranslator.Translate("trg_" + table.Name + "_" + tableFieldDefinition.ColumnName));
return sb.ToString();
}
/// <summary>
/// Gets the drop pk constraints SQL.
/// </summary>
/// <param name="table">The table.</param>
/// <returns>System.String.</returns>
protected override string GetDropPKConstraintsSql(DbTableDefinition table)
{
return string.Format(@"BEGIN
FOR cc IN (SELECT constraint_name as constraint_exists
FROM user_constraints
WHERE constraint_name = '{1}') LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ""{0}"" DROP CONSTRAINT ""{1}""';
END LOOP;
END; ;;", OracleNamesTranslator.Translate(table.TableName), OracleNamesTranslator.Translate("PK_" + table.TableName));
}
private static bool CompareForeignKeyDefinitions(ITableDefinition table, DbTableDefinition dbTable)
{
foreach (var relationship in table.Relationships.Where(r => r.RelationshipType == RelationshipKinds.OneToMany))
{
if (dbTable.ForeignKeys.All(key => key.ConstraintName != relationship.Name))
return false;
}
return true;
}
/// <summary>
/// Gets the fk constraints SQL.
/// </summary>
/// <param name="dbTable">The database table.</param>
/// <param name="fk">The fk.</param>
/// <param name="table">The table.</param>
/// <returns>System.String.</returns>
protected override string GetFKConstraintsSql(DbTableDefinition dbTable, DbForeignKeyDefinition fk, ITableDefinition table)
{
var fKeys = string.Join(",", from c in fk.ForeignKeyColumnsName select "\"" + OracleNamesTranslator.Translate(c) + "\"");
var pKeys = string.Join(",", from c in fk.PrimaryKeyColumnsName select "\"" + OracleNamesTranslator.Translate(c) + "\"");
return string.Format(
@"ALTER TABLE ""{0}"" ADD CONSTRAINT ""{1}"" FOREIGN KEY({2}) REFERENCES ""{3}""({4});;",
OracleNamesTranslator.Translate(fk.ForeignKeyTableName),
OracleNamesTranslator.Translate(fk.ConstraintName),
fKeys,
OracleNamesTranslator.Translate(fk.PrimaryKeyTableName),
pKeys);
}
protected override string GetFKConstraintsSql(DbTableDefinition dbTable, DbForeignKeyDefinition fk, ITableDefinition table)
{
reportMethodCalled();
return string.Empty;
}
protected override string GetDropPKConstraintsSql(DbTableDefinition table)
{
reportMethodCalled();
return string.Empty;
}
/// <summary> /// Gets the data copy SQL. /// </summary> /// <param name="dbTable">The database table.</param> /// <param name="table">The table.</param> /// <param name="prefix">The prefix.</param> /// <returns>System.String.</returns> protected abstract string GetDataCopySql(DbTableDefinition dbTable, ITableDefinition table, string prefix = null);
public MockDatabaseGenerator(Action<MethodBase> callback, DbTableDefinition tableDefinition)
{
this.callback = callback;
this.tableDefinition = tableDefinition;
}
/// <summary> /// Gets the drop pk constraints SQL. /// </summary> /// <param name="table">The table.</param> /// <returns>System.String.</returns> protected abstract string GetDropPKConstraintsSql(DbTableDefinition table);
/// <summary>
/// Gets the rename table SQL.
/// </summary>
/// <param name="dbTable">The database table.</param>
/// <param name="table">The table.</param>
/// <param name="prefix">The prefix.</param>
/// <returns>System.String.</returns>
protected abstract string GetRenameTableSql(DbTableDefinition dbTable,
ITableDefinition table,
string prefix = null);
/// <summary>
/// Gets the fk constraints SQL.
/// </summary>
/// <param name="dbTable">The database table.</param>
/// <param name="fk">The fk.</param>
/// <param name="table">The table.</param>
/// <returns>System.String.</returns>
protected abstract string GetFKConstraintsSql(DbTableDefinition dbTable,
DbForeignKeyDefinition fk,
ITableDefinition table);
/// <summary>
/// Gets the drop pk constraints SQL.
/// </summary>
/// <param name="table">The table.</param>
/// <returns>System.String.</returns>
protected override string GetDropPKConstraintsSql(DbTableDefinition table)
{
return string.Format(@"
IF EXISTS (SELECT * FROM sys.key_constraints
WHERE type = 'PK'
AND OBJECT_NAME(parent_object_id) = N'{0}'
AND name = N'PK_{0}')
ALTER TABLE [{0}] DROP CONSTRAINT PK_{0}", table.TableName);
}
protected override string GetDataCopySql(DbTableDefinition dbTable, ITableDefinition table, string prefix = null)
{
reportMethodCalled();
return string.Empty;
}
/// <summary>
/// Gets the data copy SQL.
/// </summary>
/// <param name="dbTable">The database table.</param>
/// <param name="table">The table.</param>
/// <param name="prefix">The prefix.</param>
/// <returns>System.String.</returns>
protected override string GetDataCopySql(DbTableDefinition dbTable, ITableDefinition table, string prefix = null)
{
var result = new StringBuilder();
var insertColumnList = new StringBuilder();
var selectColumnList = new StringBuilder();
// copy all fields
foreach (var field in table.FieldList)
{
if (!field.GenerateDbColumn)
continue;
var dbColumn = dbTable.Columns.FirstOrDefault(x => x.ColumnName == field.ColumnName);
// only if they present in old table
if (dbColumn == null)
continue;
insertColumnList.Append("[").Append(field.ColumnName).Append("],");
var sqlType = GetSqlType(field);
// convert values
var oldType = GetSqlType(dbColumn);
var newType = GetSqlType(field).ToLower();
if (AreSameDataTypes(oldType, newType) == false)
{
var defaultValue = string.IsNullOrEmpty(field.DefaultValue) ? "NULL" : field.DefaultValue;
if (IsNumericType(newType))
selectColumnList.Append("CASE WHEN IsNumeric(")
.Append(dbColumn.ColumnName)
.Append(")=1 THEN ")
.Append("CAST(REPLACE([")
.Append(dbColumn.ColumnName)
.Append("], ',', '.') AS ")
.Append(sqlType)
.Append(") ELSE ")
.Append(defaultValue)
.Append(" END,");
else if (IsDateType(newType))
selectColumnList.Append("CASE WHEN IsDate(")
.Append(dbColumn.ColumnName)
.Append(")=1 THEN ")
.Append("CAST([")
.Append(dbColumn.ColumnName)
.Append("] AS ")
.Append(sqlType)
.Append(") ELSE NULL END,");
else if (IsStringType(newType))
selectColumnList.Append("CAST([").Append(dbColumn.ColumnName).Append("] AS ").Append(sqlType).Append("),");
else
selectColumnList.Append(defaultValue).Append(",");
}
else if (dbColumn.ColumnType == "nvarchar" && dbColumn.ColumnSubType == string.Empty && newType != "nvarchar" && newType.Contains("nvarchar"))
{
selectColumnList.Append("CONVERT(")
.Append(newType)
.Append(",")
.Append(dbColumn.ColumnName)
.Append("),");
}
else
selectColumnList.Append("[").Append(dbColumn.ColumnName).Append("],");
}
//if a key field has been changed
var keyFields = table.FieldList.Where(x => x.IsPrimaryKey);
var insertColumnListString = insertColumnList.ToString();
foreach (var field in keyFields)
{
if (!insertColumnListString.Contains(field.ColumnName))
{
Log4NetLogger.Instance.Log(LogSeverity.Error, "SqlServerDatabaseGenerator", string.Format("Failed to save data for the table '{0}'. Missing key column '{1}'.", dbTable.TableName, field.ColumnName));
return string.Empty;
}
}
insertColumnList.Remove(insertColumnList.Length - 1, 1);
selectColumnList.Remove(selectColumnList.Length - 1, 1);
result.Append("IF EXISTS(SELECT * FROM ").Append(dbTable.TableName).AppendLine(")");
result.Append("INSERT INTO [").Append(prefix).Append(table.Name).Append("] (").Append(insertColumnList).AppendLine(")");
result.Append("SELECT ").Append(selectColumnList).Append(" FROM [").Append(dbTable.TableName).AppendLine("] WITH (HOLDLOCK TABLOCKX)");
return result.ToString();
}
/// <summary>
/// Compares the table definitions.
/// </summary>
/// <param name="a1">The a1.</param>
/// <param name="a2">The a2.</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
private static bool CompareTableDefinitions(DbTableDefinition a1, DbTableDefinition a2)
{
return CompareColumnDefinitions(a1.Columns, a2.Columns) && CompareTriggerDefinitions(a1.Triggers, a2.Triggers);
}
