private string MakeNewTable(TABLE sourceTable)
{
string sql = "CREATE TABLE `{$TableName}` (\r\n {$Fields}\r\n {$PRIMARY} {$INDEX})ENGINE = {$ENGINE} ROW_FORMAT = {$ROW_FORMAT};\r\n ";
string fields = "";
string primary = "";
string index = "";
foreach (COLUMN item in sourceTable.COLUMNS)
{
fields = fields + this.GetColumnInfo(item) + " ,";
if (item.COLUMN_KEY == COLUMN_KEY.PRI)
{
primary = primary + "`" + item.COLUMN_NAME + "`,";
}
}
if (fields.Length > 0)
{
fields = fields.Remove(fields.Length - 1);
}
if (primary.Length > 0)
{
primary = primary.Remove(primary.Length - 1);
primary = ",PRIMARY KEY (" + primary + ")";
}
foreach (INDEX item2 in sourceTable.INDEXS)
{
index += string.Format(",{0} INDEX `{1}` ({2})", (item2.NON_UNIQUE == 0) ? " UNIQUE" : "", item2.INDEX_NAME, item2.COLUMN_NAME);
}
return(sql.Replace("{$TableName}", sourceTable.TABLE_NAME).Replace("{$Fields}", fields).Replace("{$PRIMARY}", primary).Replace("{$ENGINE}", sourceTable.ENGINE.ToString()).Replace("{$ROW_FORMAT}", sourceTable.ROW_FORMAT).Replace("{$INDEX}", index));
}
private string MakeENGINE(TABLE sourceTable, TABLE targetTable)
{
string result;
if (sourceTable.ENGINE != targetTable.ENGINE)
{
result = string.Concat(new object[]
{
"ALTER TABLE `",
targetTable.TABLE_NAME,
"` ENGINE = ",
sourceTable.ENGINE,
" ;"
});
}
else
{
result = "";
}
return(result);
}
private string MakeRowFormat(TABLE sourceTable, TABLE targetTable)
{
string result;
if (sourceTable.ROW_FORMAT != targetTable.ROW_FORMAT)
{
result = string.Concat(new string[]
{
"ALTER TABLE `",
targetTable.TABLE_NAME,
"` ROW_FORMAT = ",
sourceTable.ROW_FORMAT,
" ;"
});
}
else
{
result = "";
}
return(result);
}
public List <string> MakeScript()
{
List <string> list = new List <string>();
foreach (var sourceTable in m_Source.TABLES)
{
TABLE targetTable = this.m_Target.TABLES.Find((TABLE p) => p.TABLE_NAME == sourceTable.TABLE_NAME);
if (targetTable == null)
{
list.Add(this.MakeNewTable(sourceTable));
}
else
{
string engine = this.MakeENGINE(sourceTable, targetTable);
if (engine != "")
{
list.Add(engine);
}
string rowformat = this.MakeRowFormat(sourceTable, targetTable);
if (rowformat != "")
{
list.Add(rowformat);
}
string temp = this.MakeChange(sourceTable, targetTable, list);
if (temp != "")
{
list.Add(temp);
}
string index = this.MakeIndex(sourceTable, targetTable);
if (index != "")
{
list.Add(index);
}
}
}
return(list);
}
private string MakeIndex(TABLE sourceTable, TABLE targetTable)
{
StringBuilder sb = new StringBuilder();
foreach (INDEX t in targetTable.INDEXS)
{
bool isexsit = false;
foreach (INDEX s in sourceTable.INDEXS)
{
if (t.INDEX_NAME == s.INDEX_NAME && t.NON_UNIQUE == s.NON_UNIQUE && t.COLUMN_NAME == s.COLUMN_NAME)
{
isexsit = true;
break;
}
}
if (!isexsit)
{
sb.Append("DROP INDEX " + t.INDEX_NAME + " ,");
}
}
foreach (INDEX s in sourceTable.INDEXS)
{
bool isexsit = false;
foreach (INDEX t in targetTable.INDEXS)
{
if (t.INDEX_NAME == s.INDEX_NAME && t.NON_UNIQUE == s.NON_UNIQUE && t.COLUMN_NAME == s.COLUMN_NAME)
{
isexsit = true;
break;
}
}
if (!isexsit)
{
sb.AppendFormat(string.Concat(new string[]
{
"ADD {0} INDEX ",
s.INDEX_NAME,
"(",
s.COLUMN_NAME,
") ,"
}), (s.NON_UNIQUE == 0) ? " UNIQUE" : "");
}
}
if (sb.ToString().EndsWith(","))
{
sb.Remove(sb.Length - 1, 1);
}
string result;
if (sb.Length == 0)
{
result = "";
}
else
{
result = string.Concat(new string[]
{
"ALTER TABLE ",
sourceTable.TABLE_NAME,
" \r\n",
sb.ToString(),
";"
});
}
return(result);
}
private string MakeChange(TABLE sourceTable, TABLE targetTable, List <string> extendSqls = null)
{
if (extendSqls == null)
{
extendSqls = new List <string>();
}
string change = "";
string changeprimary = "";
string prev = "";
bool isChangeKey = false;
bool isExsitKey = false;
string primary = "";
foreach (var sourceColumn in sourceTable.COLUMNS)
{
bool updatenull = false;
COLUMN targetColumn2 = targetTable.COLUMNS.Find((COLUMN p) => p.COLUMN_NAME == sourceColumn.COLUMN_NAME);
if (targetColumn2 == null)
{
if (sourceColumn.COLUMN_KEY == COLUMN_KEY.PRI)
{
isChangeKey = true;
}
string text = change;
change = string.Concat(new string[]
{
text,
"ADD COLUMN ",
this.GetColumnInfo(sourceColumn),
" ",
(prev == "") ? " FIRST" : ("AFTER `" + prev + "`"),
" ,"
});
}
else if (!this.EqualsColumn(sourceColumn, targetColumn2, ref updatenull))
{
string text = change;
change = string.Concat(new string[]
{
text,
"CHANGE COLUMN `",
sourceColumn.COLUMN_NAME,
"` ",
this.GetColumnInfo(sourceColumn),
" ,"
});
if (updatenull)
{
string update = "update {0} set {1} = {2} where {1} is null;";
string upvalue = (sourceColumn.COLUMN_DEFAULT == DBNull.Value) ?
sourceColumn.DATA_TYPE.GetDefault() :
sourceColumn.DATA_TYPE.IsChar() ? "'" + sourceColumn.COLUMN_DEFAULT.ToString() + "'" :
sourceColumn.COLUMN_DEFAULT.ToString();
update = string.Format(update, sourceColumn.TABLE_NAME, sourceColumn.COLUMN_NAME, upvalue);
extendSqls.Add(update);
}
}
if (targetColumn2 != null && sourceColumn.COLUMN_KEY == COLUMN_KEY.PRI && sourceColumn.COLUMN_KEY != targetColumn2.COLUMN_KEY)
{
isChangeKey = true;
}
if (sourceColumn.COLUMN_KEY == COLUMN_KEY.PRI)
{
primary = primary + "`" + sourceColumn.COLUMN_NAME + "`,";
}
prev = sourceColumn.COLUMN_NAME;
}
foreach (var targetColumn in targetTable.COLUMNS)
{
if (targetColumn.COLUMN_KEY == COLUMN_KEY.PRI)
{
isExsitKey = true;
}
COLUMN sourceColumn2 = sourceTable.COLUMNS.Find((COLUMN p) => p.COLUMN_NAME == targetColumn.COLUMN_NAME);
if (sourceColumn2 == null)
{
change = change + " DROP COLUMN `" + targetColumn.COLUMN_NAME + "`,";
}
}
if (change.Length > 0)
{
change = change.Remove(change.Length - 1);
}
if (primary.Length > 0)
{
primary = primary.Remove(primary.Length - 1);
}
if (isChangeKey)
{
if (isExsitKey)
{
changeprimary = " DROP PRIMARY KEY , ADD PRIMARY KEY (" + primary + ") ;";
}
else
{
changeprimary = " ADD PRIMARY KEY (" + primary + ") ;";
}
if (change.Length > 0)
{
changeprimary = "," + changeprimary;
}
}
string sql = "ALTER TABLE {$TableName} {$Change} {$Primary}";
string result;
if (change == "" && changeprimary == "")
{
result = "";
}
else
{
result = sql.Replace("{$TableName}", sourceTable.TABLE_NAME).Replace("{$Change}", change).Replace("{$Primary}", changeprimary);
}
return(result);
}
public static SCHEMA GetSCHEMA(string conn, string dbname)
{
TableMySQL sqltable = new TableMySQL(conn);
SCHEMA schema = new SCHEMA(dbname);
DataTable dt = sqltable.Get("select * from information_schema.tables where TABLE_TYPE='BASE TABLE' AND Table_SCHEMA ='" + dbname + "'");
foreach (DataRow dr in dt.Rows)
{
TABLE table = new TABLE();
table.TABLE_SCHEMA = ((dr["TABLE_SCHEMA"] == DBNull.Value) ? "" : dr["TABLE_SCHEMA"].ToString());
table.TABLE_NAME = ((dr["TABLE_NAME"] == DBNull.Value) ? "" : dr["TABLE_NAME"].ToString());
table.TABLE_TYPE = MySqlCore.GetTABLE_TYPE(dr["TABLE_TYPE"].ToString());
table.ENGINE = MySqlCore.GetENGINE(dr["ENGINE"].ToString());
table.TABLE_ROWS = ((dr["TABLE_ROWS"] == DBNull.Value) ? 0uL : ((ulong)dr["TABLE_ROWS"]));
table.DATA_LENGTH = ((dr["DATA_LENGTH"] == DBNull.Value) ? 0uL : ((ulong)dr["DATA_LENGTH"]));
table.MAX_DATA_LENGTH = ((dr["MAX_DATA_LENGTH"] == DBNull.Value) ? 0uL : ((ulong)dr["MAX_DATA_LENGTH"]));
table.INDEX_LENGTH = ((dr["INDEX_LENGTH"] == DBNull.Value) ? 0uL : ((ulong)dr["INDEX_LENGTH"]));
table.CREATE_TIME = ((dr["CREATE_TIME"] == DBNull.Value) ? DateTime.MinValue : ((DateTime)dr["CREATE_TIME"]));
table.UPDATE_TIME = ((dr["UPDATE_TIME"] == DBNull.Value) ? DateTime.MinValue : ((DateTime)dr["UPDATE_TIME"]));
table.TABLE_COMMENT = ((dr["TABLE_COMMENT"] == DBNull.Value) ? "" : dr["TABLE_COMMENT"].ToString());
table.ROW_FORMAT = ((dr["ROW_FORMAT"] == DBNull.Value) ? "" : dr["ROW_FORMAT"].ToString());
schema.TABLES.Add(table);
DataTable dtcol = sqltable.Get(string.Concat(new string[]
{
"select * from COLUMNs where table_schema='",
table.TABLE_SCHEMA,
"' and table_name='",
table.TABLE_NAME,
"';"
}));
foreach (DataRow drcol in dtcol.Rows)
{
COLUMN column = new COLUMN();
column.TABLE_SCHEMA = dbname;
column.TABLE_NAME = table.TABLE_NAME;
column.COLUMN_NAME = drcol["COLUMN_NAME"].ToString();
column.ORDINAL_POSITION = (drcol["ORDINAL_POSITION"] == DBNull.Value) ? 0uL : Convert.ToUInt64(drcol["ORDINAL_POSITION"]);
column.COLUMN_DEFAULT = drcol["COLUMN_DEFAULT"];
column.IS_NULLABLE = MySqlCore.GetIS_NULLABLE(drcol["IS_NULLABLE"].ToString());
column.DATA_TYPE = MySqlCore.GetDATA_TYPE(drcol["DATA_TYPE"].ToString());
column.CHARACTER_MAXIMUM_LENGTH = (drcol["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value) ? 0uL : Convert.ToUInt64(drcol["CHARACTER_MAXIMUM_LENGTH"]);
column.CHARACTER_OCTET_LENGTH = (drcol["CHARACTER_OCTET_LENGTH"] == DBNull.Value) ? 0uL : Convert.ToUInt64(drcol["CHARACTER_OCTET_LENGTH"]);
column.NUMERIC_PRECISION = (drcol["NUMERIC_PRECISION"] == DBNull.Value) ? 0uL : Convert.ToUInt64(drcol["NUMERIC_PRECISION"]);
column.NUMERIC_SCALE = (drcol["NUMERIC_SCALE"] == DBNull.Value) ? 0uL : Convert.ToUInt64(drcol["NUMERIC_SCALE"]);
column.COLUMN_TYPE = ((drcol["COLUMN_TYPE"] == DBNull.Value) ? "" : drcol["COLUMN_TYPE"].ToString());
column.COLUMN_KEY = MySqlCore.GetCOLUMN_KEY(drcol["COLUMN_KEY"].ToString());
column.COLUMN_COMMENT = drcol["COLUMN_COMMENT"].ToString();
column.EXTRA = ((drcol["EXTRA"] == DBNull.Value) ? "" : drcol["EXTRA"].ToString());
table.COLUMNS.Add(column);
}
DataTable dtindex = sqltable.Get(string.Concat(new string[]
{
"select * from information_schema.statistics where table_schema='",
table.TABLE_SCHEMA,
"' and table_name='",
table.TABLE_NAME,
"';"
}));
foreach (DataRow dri in dtindex.Rows)
{
string indexname = dri["INDEX_NAME"].ToString();
if (!(indexname == "PRIMARY"))
{
INDEX model = table.INDEXS.Find((INDEX p) => p.INDEX_NAME == indexname);
if (model == null)
{
model = new INDEX();
model.INDEX_NAME = indexname;
model.NON_UNIQUE = Convert.ToInt32(dri["NON_UNIQUE"]);
model.COLUMN_NAME = string.Format("`{0}` ASC", dri["COLUMN_NAME"]);
table.INDEXS.Add(model);
}
else
{
model.COLUMN_NAME += string.Format(",`{0}` ASC", dri["COLUMN_NAME"]);
}
}
}
}
return(schema);
}
