/// <summary>
/// 比较字段是否一致
/// </summary>
private void CompareColumns(string bllTableName, string archiveTableName)
{
List <TableCharacter> bllCharacter = GetTableCharacters(bllTableName);
List <TableCharacter> archiveCharacter = GetTableCharacters(archiveTableName);
List <TableCharacter> tableCharacters = bllCharacter.Where(x => !archiveCharacter.Exists(y => x.Field.Equals(y.Field))).ToList();
foreach (TableCharacter tableCharacter in tableCharacters)
{
StringBuilder builder = new StringBuilder();
builder.Append($"ALTER TABLE `{archiveTableName}` Add COLUMN `{tableCharacter.Field}` {tableCharacter.Type}");
if (!tableCharacter.IsNull)
{
builder.Append(" NOT NULL");
}
builder.Append(" COLLATE utf8_unicode_ci");
if (!string.IsNullOrEmpty(tableCharacter.Default))
{
builder.Append($" DEFAULT '{tableCharacter.Default}'");
}
MySqlHelperEx.ExecuteNonQueryEx(EnvironmentInfo.ConnectionString, builder.ToString());
}
}
private string CreateTable(Table table)
{
StringBuilder script = new StringBuilder();
if (table.Type == 1)
{
script.Append($"CREATE TABLE `{table.TableName}` (");
//字段
foreach (var field in table.ColumnList)
{
#region int bigint
if (field.Type.StartsWith("int") || field.Type.StartsWith("bigint"))
{
if (field.IsKey && field.Extra.Equals("auto_increment"))
{
script.Append($"`{field.Field}` {field.Type} NOT NULL AUTO_INCREMENT,").Append(Environment.NewLine);
}
else
{
if (field.IsNull)
{
if (string.IsNullOrEmpty(field.Default))
{
script.Append($"`{field.Field}` {field.Type} DEFAULT '0',").Append(Environment.NewLine);
}
else
{
script.Append($"`{field.Field}` {field.Type} DEFAULT '{field.Default}',").Append(Environment.NewLine);
}
}
else
{
if (string.IsNullOrEmpty(field.Default))
{
script.Append($"`{field.Field}` {field.Type} NOT NULL DEFAULT '0',").Append(Environment.NewLine);
}
else
{
script.Append($"`{field.Field}` {field.Type} NOT NULL DEFAULT '{field.Default}',").Append(Environment.NewLine);
}
}
}
}
#endregion
#region varchar char
if (field.Type.StartsWith("varchar") || field.Type.StartsWith("char"))
{
if (field.IsNull)
{
if (string.IsNullOrEmpty(field.Default))
{
script.Append($"`{field.Field}` {field.Type} COLLATE utf8_unicode_ci DEFAULT NULL,").Append(Environment.NewLine);
}
else
{
script.Append($"`{field.Field}` {field.Type} COLLATE utf8_unicode_ci DEFAULT '{field.Default}',").Append(Environment.NewLine);
}
}
else
{
if (string.IsNullOrEmpty(field.Default))
{
script.Append($"`{field.Field}` {field.Type} COLLATE utf8_unicode_ci NOT NULL,").Append(Environment.NewLine);
}
else
{
script.Append($"`{field.Field}` {field.Type} COLLATE utf8_unicode_ci NOT NULL DEFAULT '{field.Default}',").Append(Environment.NewLine);
}
}
}
#endregion
#region decimal
if (field.Type.StartsWith("decimal"))
{
if (field.IsNull)
{
if (string.IsNullOrEmpty(field.Default))
{
script.Append($"`{field.Field}` {field.Type} DEFAULT '0.00',").Append(Environment.NewLine);
}
else
{
script.Append($"`{field.Field}` {field.Type} DEFAULT '{field.Default}',").Append(Environment.NewLine);
}
}
else
{
if (string.IsNullOrEmpty(field.Default))
{
script.Append($"`{field.Field}` {field.Type} NOT NULL DEFAULT '0.00',").Append(Environment.NewLine);
}
else
{
script.Append($"`{field.Field}` {field.Type} NOT NULL DEFAULT '{field.Default}',").Append(Environment.NewLine);
}
}
}
#endregion
#region float
if (field.Type.StartsWith("float"))
{
if (field.IsNull)
{
if (string.IsNullOrEmpty(field.Default))
{
script.Append($"`{field.Field}` {field.Type} DEFAULT '0',").Append(Environment.NewLine);
}
else
{
script.Append($"`{field.Field}` {field.Type} DEFAULT '{field.Default}',").Append(Environment.NewLine);
}
}
else
{
if (string.IsNullOrEmpty(field.Default))
{
script.Append($"`{field.Field}` {field.Type} NOT NULL DEFAULT '0',").Append(Environment.NewLine);
}
else
{
script.Append($"`{field.Field}` {field.Type} NOT NULL DEFAULT '{field.Default}',").Append(Environment.NewLine);
}
}
}
#endregion
#region datetime
if (field.Type.StartsWith("datetime") || field.Type.StartsWith("timestamp"))
{
if (field.IsNull)
{
if (string.IsNullOrEmpty(field.Default))
{
script.Append($"`{field.Field}` {field.Type} DEFAULT NULL,").Append(Environment.NewLine);
}
else
{
if (field.Default.Equals("CURRENT_TIMESTAMP"))
{
script.Append($"`{field.Field}` {field.Type} DEFAULT {field.Default},").Append(Environment.NewLine);
}
else
{
script.Append($"`{field.Field}` {field.Type} DEFAULT '{field.Default}',").Append(Environment.NewLine);
}
}
}
else
{
if (string.IsNullOrEmpty(field.Default))
{
script.Append($"`{field.Field}` {field.Type} NOT NULL,").Append(Environment.NewLine);
}
else
{
if (field.Default.Equals("CURRENT_TIMESTAMP"))
{
script.Append($"`{field.Field}` {field.Type} NOT NULL DEFAULT {field.Default},").Append(Environment.NewLine);
}
else
{
script.Append($"`{field.Field}` {field.Type} NOT NULL DEFAULT '{field.Default}',").Append(Environment.NewLine);
}
}
}
}
#endregion
}
int primaryKeyCount = table.IndexList.Count(x => x.KeyName == "PRIMARY");
if (primaryKeyCount > 1)//存在联合主键
{
var primaryKey = table.IndexList.Where(x => x.KeyName == "PRIMARY");
script.Append($"PRIMARY KEY (");
string pky = "";
foreach (var index in primaryKey)
{
pky += "`" + index.ColumnName + "`,";
}
script.Append(pky.Trim(','));
script.Append($"),").Append(Environment.NewLine);
var otherKey = table.IndexList.Where(x => x.KeyName != "PRIMARY");
//索引
foreach (var index in otherKey)
{
#region 创建索引
if (index.NonUnique == 0)
{
script.Append($"UNIQUE KEY `{index.KeyName}` (`{index.ColumnName}`) USING BTREE,").Append(Environment.NewLine);
}
else if (index.NonUnique == 1)
{
var groupByKeyName = table.IndexList.Where(x => x.NonUnique == 1)
.GroupBy(x => x.KeyName).Select(x => x.Key).Distinct();
foreach (var keyName in groupByKeyName)
{
int nonUniqueKeyCount = table.IndexList.Count(x => x.KeyName == keyName);
var uniqueKey = table.IndexList.Where(x => x.KeyName == keyName);
if (nonUniqueKeyCount > 1)
{
script.Append($"KEY `{keyName}` (");
string uky = "";
foreach (var key in uniqueKey)
{
uky += "`" + key.ColumnName + "`,";
}
script.Append(uky.Trim(','));
script.Append($") USING BTREE,").Append(Environment.NewLine);
}
else
{
Index uniqueIndex = uniqueKey.FirstOrDefault();
script.Append($"KEY `{uniqueIndex.KeyName}` (`{uniqueIndex.ColumnName}`) USING BTREE,").Append(Environment.NewLine);
}
}
}
#endregion
}
}
else
{
var otherKey = table.IndexList.Where(x => x.NonUnique == 0);
foreach (var index in otherKey)
{
if (index.KeyName == "PRIMARY")
{
script.Append($"PRIMARY KEY (`{index.ColumnName}`),").Append(Environment.NewLine);
}
else
{
script.Append($"UNIQUE KEY `{index.KeyName}` (`{index.ColumnName}`) USING BTREE,").Append(Environment.NewLine);
}
}
#region 创建索引
var groupByKeyName = table.IndexList.Where(x => x.NonUnique == 1)
.GroupBy(x => x.KeyName).Select(x => x.Key).Distinct();
foreach (var keyName in groupByKeyName)
{
int nonUniqueKeyCount = table.IndexList.Count(x => x.KeyName == keyName);
var uniqueKey = table.IndexList.Where(x => x.KeyName == keyName);
if (nonUniqueKeyCount > 1)//联合索引
{
script.Append($"KEY `{keyName}` (");
string uky = "";
foreach (var key in uniqueKey)
{
uky += "`" + key.ColumnName + "`,";
}
script.Append(uky.Trim(','));
script.Append($") USING BTREE,").Append(Environment.NewLine);
}
else
{
Index index = uniqueKey.FirstOrDefault();
script.Append($"KEY `{index.KeyName}` (`{index.ColumnName}`) USING BTREE,").Append(Environment.NewLine);
}
}
#endregion
}
string ddlScript = script.ToString().TrimEnd(Environment.NewLine.ToCharArray()).TrimEnd(',') + Environment.NewLine;
ddlScript += $") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
try
{
LogHelper.CommLogger.Info(ddlScript);
MySqlHelperEx.ExecuteNonQueryEx(EnvironmentInfo.ConnectionString, ddlScript);
}
catch (Exception)
{
StringBuilder ddlStringBuilder = new StringBuilder();
ddlStringBuilder.Append($"-- 表{table.TableName}创建失败！").Append(Environment.NewLine);
ddlStringBuilder.Append(ddlScript);
return(ddlStringBuilder.ToString());
}
}
return("");
}
private void CheckTables(string jsonText)
{
if (string.IsNullOrEmpty(jsonText))
{
ShowMessage($"当前版本的初始化json文件不存在，不执行数据库校验...");
ShowMessage("升级已经完成，请留意观察JieLink中心使用是否正常...");
return;
}
StringBuilder exceptMessage = new StringBuilder();
DBVersionScript script = JsonHelper.DeserializeObject <DBVersionScript>(jsonText);
foreach (var table in script.TableList)
{
if (table.Type == 1)
{
ShowMessage($"正在校验{table.TableName}表...");
if (TableIsExists(table.TableName))
{
Table dbTable = GetTable(table.TableName);
List <Column> columns = table.ColumnList.Where(x => !dbTable.ColumnList.Exists(y => x.Field.Equals(y.Field))).ToList();
foreach (Column column in columns)
{
StringBuilder builder = new StringBuilder();
builder.Append($"ALTER TABLE `{table.TableName}` Add COLUMN `{column.Field}` {column.Type}");
if (!column.IsNull)
{
builder.Append(" NOT NULL");
}
if (!string.IsNullOrEmpty(column.Default))
{
builder.Append($" DEFAULT '{column.Default}'");
}
builder.Append(" COLLATE utf8_unicode_ci;");
ShowMessage($"添加{column.Field}字段...");
try
{
MySqlHelperEx.ExecuteNonQueryEx(EnvironmentInfo.ConnectionString, builder.ToString());
}
catch (Exception)
{
exceptMessage.Append($"-- {table.TableName}表添加{column.Field}字段失败...").Append(Environment.NewLine);
exceptMessage.Append(builder.ToString()).Append(Environment.NewLine);
}
}
}
else
{
ShowMessage($"创建{table.TableName}表...");
string result = CreateTable(table);
if (!string.IsNullOrEmpty(result))
{
exceptMessage.Append(result).Append(Environment.NewLine);
}
}
}
}
ShowMessage("数据库校验完成...");
if (string.IsNullOrEmpty(exceptMessage.ToString()))
{
ShowMessage("升级已经完成，请留意观察JieLink中心使用是否正常...");
}
else
{
ShowMessage("升级已经完成，以下脚本执行异常，需要人工处理：");
ShowMessage(exceptMessage.ToString());
}
}