private bool EqualsColumn(COLUMN source, COLUMN target, ref bool needUpdateNull)
{
needUpdateNull = false;
bool result;
if (source.COLUMN_TYPE != target.COLUMN_TYPE)
{
result = false;
}
else if (source.IS_NULLABLE != target.IS_NULLABLE)
{
result = false;
if (source.IS_NULLABLE == IS_NULLABLE.NO)
{
needUpdateNull = true;
}
}
else if (source.EXTRA != target.EXTRA)
{
result = false;
}
else
{
if (source.COLUMN_DEFAULT != DBNull.Value && target.COLUMN_DEFAULT != DBNull.Value)
{
if (source.COLUMN_DEFAULT.ToString() != target.COLUMN_DEFAULT.ToString())
{
result = false;
return(result);
}
}
result = ((source.COLUMN_DEFAULT != DBNull.Value || target.COLUMN_DEFAULT == DBNull.Value) && (source.COLUMN_DEFAULT == DBNull.Value || target.COLUMN_DEFAULT != DBNull.Value));
}
return(result);
}
private string GetColumnInfo(COLUMN col)
{
string fields = "";
string text = fields;
fields = string.Concat(new string[]
{
text,
"`",
col.COLUMN_NAME,
"` ",
col.COLUMN_TYPE,
" ",
(col.IS_NULLABLE == IS_NULLABLE.NO) ? "NOT NULL" : "NULL"
});
if (col.COLUMN_DEFAULT != DBNull.Value)
{
fields = fields + " DEFAULT " + ((col.COLUMN_DEFAULT.ToString() == "") ? "''" : col.DATA_TYPE.IsChar() ? "'" + col.COLUMN_DEFAULT.ToString() + "'" : col.COLUMN_DEFAULT.ToString());
}
if (col.EXTRA != "")
{
fields += " " + col.EXTRA.ToUpper();
}
return(fields);
}
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);
}