public string GenerateSqlToCreate()
{
var sb = new SqlBuilder();
if (Table != null)
{
sb.AppendFormat(
_unique ? "CREATE UNIQUE NONCLUSTERED INDEX {0} ON {1}" : "CREATE NONCLUSTERED INDEX {0} ON {1}",
DatabaseUtils.EscapeDbObject(Name),
DatabaseUtils.GetQualifiedTableName(Table.SchemaName, Table.Name));
sb.Append("(");
for (int n = 0; n < Key.Count; ++n)
{
if (n > 0)
{
sb.Append(",");
}
var part = Key[n];
sb.AppendFormat(
" {0} {1}",
DatabaseUtils.EscapeDbObject(part.ColumnName),
Utils.ColumnOrderToSqlString(part.ColumnOrder));
}
sb.Append(");");
}
return(sb.ToString());
}
string IDatabaseObject.GenerateSqlToCreate()
{
var sb = new SqlBuilder();
if (Table != null)
{
var keyName = Name ?? string.Concat(Prefix, Table.Name.ToUpper(), "_", _primaryKeyTable.Name.ToUpper());
sb.AppendFormat(
"ALTER TABLE {0} ADD CONSTRAINT {1}",
DatabaseUtils.GetQualifiedTableName(Table.SchemaName, Table.Name),
DatabaseUtils.EscapeDbObject(keyName));
sb.AppendFormat("FOREIGN KEY ({0})", ColsAsCsv(_foreignKeyColumns));
sb.AppendFormat(
"REFERENCES {0} ({1})",
DatabaseUtils.GetQualifiedTableName(_primaryKeyTable.SchemaName, _primaryKeyTable.Name),
ColsAsCsv(_primaryKeyColumns));
if (_rules.Any())
{
foreach (var rule in _rules)
{
sb.Append(rule);
}
}
sb.Append(";");
}
return(sb.ToString());
}
public TableDiffer(
string connectionString,
int commandTimeoutSecs,
string oldTable,
string newTable,
int primaryKeyColCount = 1)
: base(
connectionString,
commandTimeoutSecs,
$"select * from {DatabaseUtils.EscapeDbObject(oldTable)}",
$"select * from {DatabaseUtils.EscapeDbObject(newTable)}",
primaryKeyColCount)
{
}
public StageTableDiffer(
string connectionString,
int commandTimeoutSecs,
string oldTable,
string newTable,
int timetableId,
int primaryKeyColCount = 1)
: base(
connectionString,
commandTimeoutSecs,
$"select * from {DatabaseUtils.EscapeDbObject(oldTable)} where src_timetable_id = {timetableId}",
$"select * from {DatabaseUtils.EscapeDbObject(newTable)} where src_timetable_id = {timetableId}",
primaryKeyColCount)
{
}
private string ColsAsCsv(IReadOnlyList <string> colNames)
{
var sb = new StringBuilder();
for (int n = 0; n < colNames.Count; ++n)
{
if (n > 0)
{
sb.Append(", ");
}
sb.AppendFormat("{0}", DatabaseUtils.EscapeDbObject(colNames[n]));
}
return(sb.ToString());
}
public string GenerateSqlToCreate()
{
var sb = new SqlBuilder();
if (Table != null)
{
sb.AppendFormat(
"ALTER TABLE {0} ADD CONSTRAINT {1} CHECK ({2})",
DatabaseUtils.GetQualifiedTableName(Table.SchemaName, Table.Name),
DatabaseUtils.EscapeDbObject(Name),
_body);
sb.Append(";");
}
return(sb.ToString());
}
public string GenerateSqlToCreate()
{
var sb = new SqlBuilder();
if (Table != null)
{
string name = Name ?? string.Concat(Prefix, Table.Name, "_", _colName.ToUpper());
sb.AppendFormat(
"ALTER TABLE {0} ADD CONSTRAINT {1} DEFAULT {2} FOR {3}",
DatabaseUtils.GetQualifiedTableName(Table.SchemaName, Table.Name),
DatabaseUtils.EscapeDbObject(name),
_defaultValue,
_colName);
sb.Append(";");
}
return(sb.ToString());
}
string IDatabaseObject.GenerateSqlToCreate()
{
var sb = new SqlBuilder();
if (Table != null)
{
string keyName = Name ?? string.Concat(PREFIX, Table.Name.ToUpper());
sb.AppendFormat(
"ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY",
DatabaseUtils.GetQualifiedTableName(Table.SchemaName, Table.Name),
DatabaseUtils.EscapeDbObject(keyName));
if (Clustered)
{
sb.Append("CLUSTERED");
}
sb.Append("(");
for (int n = 0; n < Key.Count; ++n)
{
if (n > 0)
{
sb.Append(",");
}
var part = Key[n];
sb.AppendFormat(
" {0} {1}",
DatabaseUtils.EscapeDbObject(part.ColumnName),
Utils.ColumnOrderToSqlString(part.ColumnOrder));
}
sb.Append(");");
}
return(sb.ToString());
}
