public static int GetDiffColumnsCount(this TableSchema table1, TableSchema table2)
{
if (table1 == null)
throw new ArgumentNullException("table1");
if (table2 == null)
throw new ArgumentNullException("table2");
return table1.Columns.Count(column => !table2.IsColumnExist(column.Name));
}
protected string MakeDdlKeyDrop(KeySchema key, TableSchema table)
{
return string.Format(@"ALTER TABLE {0} DROP CONSTRAINT {1}",
MakeDdlElementName(table.Name), MakeDdlElementName(key.Name));
}
protected string MakeDdlKeyCreateByAlter(KeySchema key, TableSchema table)
{
return string.Format(@"ALTER TABLE {0} ADD CONSTRAINT {1} {2}",
MakeDdlElementName(table.Name), MakeDdlElementName(key.Name),
ParseKey(key));
}
protected string MakeDdlDefaultCreate(KeySchema key, TableSchema table)
{
return string.Format(@"ALTER TABLE {0} ADD CONSTRAINT {1} DEFAULT {2} FOR {3}",
MakeDdlElementName(table.Name), MakeDdlElementName(key.Name),
key.Source, key.Columns);
}
protected virtual string MakeDdlColumnDrop(TableColumnSchema column, TableSchema table)
{
return string.Format(@"ALTER TABLE {0} DROP COLUMN {1}",
MakeDdlElementName(table.Name), MakeDdlElementName(column.Name));
}
protected virtual string MakeDdlColumnAlter(TableColumnSchema mColumn, TableColumnSchema eColumn,
TableSchema table)
{
return string.Format(@"ALTER TABLE {0} ALTER COLUMN {1}",
MakeDdlElementName(table.Name), ParseColumnAlter(mColumn, eColumn));
}
private string MakeDdlColumnCreate(TableColumnSchema column, TableSchema table)
{
return string.Format(@"ALTER TABLE {0} ADD {1}",
MakeDdlElementName(table.Name), ParseColumn(column));
}
private void HelperTableCreate(TableSchema mTable, int i)
{
AddDdlCommand(i,
mTable.Name,
"Table",
MakeDdlTableCreate(mTable, false));
foreach (var mIndex in mTable.Indexes)
AddDdlCommand(i,
mTable.Name + @"." + mIndex.Name,
"Index",
MakeDdlIndexCreate(mIndex, mTable));
foreach (var mKey in mTable.Keys)
AddDdlCommand(i,
mTable.Name + @"." + mKey.Name,
mKey.KeyType.ToString(),
MakeDdlKeyCreateByAlter(mKey, mTable));
}
protected string MakeDdlTableRename(TableSchema table, string newName)
{
return string.Format(@"ALTER TABLE {0} RENAME TO {1}",
MakeDdlElementName(table.Name), newName);
}
protected string MakeDdlTableDrop(TableSchema table)
{
return string.Format(@"DROP TABLE {0}",
MakeDdlElementName(table.Name));
}
protected virtual string MakeDdlTableCreate(TableSchema table, bool withConstraint)
{
var stat = new StringBuilder();
var firstColumn = true;
foreach (var column in table.Columns)
{
if (!firstColumn)
stat.Append(",\n\t");
stat.Append(ParseColumn(column));
firstColumn = false;
}
if (withConstraint)
{
if (!firstColumn)
stat.Append(",\n\t");
foreach (var key in table.Keys)
stat.Append(@" " + ParseKey(key));
}
return string.Format(@"CREATE TABLE {0} ({1})",
MakeDdlElementName(table.Name), stat);
}
public virtual void EndTableLoad(IDbConnection connection, TableSchema table)
{}
public virtual string MakeInsert(TableSchema table)
{
var columns = Array.ConvertAll(table.Columns, column => MakeDdlElementName(column.Name));
var parameters = Array.ConvertAll(table.Columns, MakeParameterName);
var query =
string.Format("INSERT INTO\n\t{0}\n\t({1})\nVALUES\n\t({2})",
MakeDdlElementName(table.Name),
string.Join(",\n\t", columns),
string.Join(",\n\t", parameters));
return query;
}
public virtual string MakeSelect(TableSchema table, bool orderedByPK)
{
var columns = Array.ConvertAll(table.Columns, column => MakeDdlElementName(column.Name));
var query = string.Format("SELECT\n\t{0}\nFROM\n\t{1}",
string.Join(",\n\t", columns), MakeDdlElementName(table.Name));
if (orderedByPK && table.KeyPrimary() != null
&& !string.IsNullOrEmpty(table.KeyPrimary().Columns))
{
var keyColumns = table.KeyPrimary().Columns.Split(new[] {',', ' '},
StringSplitOptions.RemoveEmptyEntries);
keyColumns = Array.ConvertAll(keyColumns, MakeDdlElementName);
query += "\nORDER BY\n\t" + string.Join(",\n\t", keyColumns);
}
return query;
}
private void HelperTableDrop(TableSchema eTable, IEnumerable<TableSchema> eTables, int i)
{
foreach (var table in eTables)
foreach (var t in table.Keys)
if (t.KeyType == ConstraintType.KeyForeign &&
t.RelTable == eTable.Name)
AddDdlCommand(i,
table.Name + @"." + t.Name,
t.KeyType.ToString(),
MakeDdlKeyDrop(t, table));
AddDdlCommand(i,
eTable.Name,
"Table",
MakeDdlTableDrop(eTable));
}
protected abstract string MakeDdlIndexCreate(IndexSchema index, TableSchema table);
protected virtual string MakeDdlIndexDrop(IndexSchema index, TableSchema table)
{
return string.Format(@"DROP INDEX {0} ON {1}",
MakeDdlElementName(index.Name), MakeDdlElementName(table.Name));
}
protected string MakeDdlTableCopy(TableSchema toTable, TableSchema fromTable)
{
return string.Format(@"INSERT INTO {0}({1}) SELECT {3} FROM {2}",
MakeDdlElementName(toTable.Name), toTable.ColumnsList(MakeDdlElementName),
MakeDdlElementName(fromTable.Name), fromTable.ColumnsList(MakeDdlElementName));
}
