示例#1
0
        /// <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());
            }
        }