XdCxRhDW/DbMigrate/Form1.cs

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Linq;
using XdCxRhDW.Repostory;
using Dapper;
using XdCxRhDW.Entity;
namespace DbMigrate
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnSelectSqliteFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = "|*.db";
            if (dialog.ShowDialog() != DialogResult.OK) return;
            txtSqliteFile.Text = dialog.FileName;
        }

        private async void txtSqliteFile_TextChanged(object sender, EventArgs e)
        {
            string file = txtSqliteFile.Text;
            if (!File.Exists(file)) return;
            try
            {
                checkedListBox1.Items.Clear();
                using (SQLiteConnection con = new SQLiteConnection($"Data Source={file}"))
                {
                    await con.OpenAsync();
                    var cmd = con.CreateCommand();
                    cmd.CommandText = "select name from sqlite_master where type='table' and  name not like 'sqlite%'";
                    var reader = await cmd.ExecuteReaderAsync();
                    while (await reader.ReadAsync())
                    {
                        var table = reader["name"].ToString();
                        this.checkedListBox1.Items.Add(table);
                    }
                    for (int i = 0; i < checkedListBox1.Items.Count; i++)
                    {
                        checkedListBox1.SetItemChecked(i, true);
                    }
                }
            }
            catch (Exception ex)
            {
                Log($"{file}不是有效的Sqlite库文件");
            }
        }

        private async void btnInitMySql_Click(object sender, EventArgs e)
        {
            try
            {
                this.groupBox1.Enabled = false;
                this.groupBox2.Enabled = false;
                this.panel2.Enabled = false;
                string conStr = txtMySqlCon.Text.Trim();
                if (!conStr.EndsWith(";"))
                    conStr = $"{conStr};";
                //server=192.168.101.3;uid=root;pwd=123456;database=DWPT;
                var arr = conStr.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).Where(p => !p.ToLower().Contains("database")).ToList();
                var createDbCon = string.Join(";", arr) + ";";
                var conWithTimeout = $"{conStr}ConnectionTimeout=5;";
                string dbExistSql = "SELECT count(*) FROM information_schema.SCHEMATA where SCHEMA_NAME='dwpt'";
                string createDbSql = "CREATE DATABASE `dwpt` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */";
                if (!File.Exists("dwpt.sql")) return;
                using (MySqlConnection con = new MySqlConnection(createDbCon))
                {
                    await con.OpenAsync();
                    var count = await con.QueryFirstAsync<int>(dbExistSql);
                    if (count == 0)
                        await con.ExecuteAsync(createDbSql);
                }
                var commands = File.ReadAllText("dwpt.sql").Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                commands.RemoveAll(p => p.All(q => q == '\n' || q == '\r' || q == '\t' || q == ' '));
                using (MySqlConnection db = new MySqlConnection(conWithTimeout))
                {
                    foreach (string cmd in commands)
                    {
                        try
                        {
                            await db.ExecuteAsync(cmd);
                        }
                        catch (Exception ex)
                        {
                            Log($"{ex.Message}");
                        }
                    }
                }
                Log("MySql数据库及表结构创建完成");
            }
            catch (Exception ex)
            {
                Log($"创建MySql数据库及表结构失败.{ex.Message}");
            }
            finally
            {
                this.groupBox1.Enabled = true;
                this.groupBox2.Enabled = true;
                this.panel2.Enabled = true;
            }
        }

        private async void btnMigrate_Click(object sender, EventArgs e)
        {
            this.txtLog.Items.Clear();
            string file = txtSqliteFile.Text;
            if (!File.Exists(file))
            {
                Log($"Sqlite库文件{file}不存在!");
                return;
            }
            var tables = this.checkedListBox1.CheckedItems.Cast<string>().Select(p=>p.ToLower()).ToList();
            tables.Reverse();
            if (!tables.Any())
            {
                Log("请选择要迁移的表");
                return;
            }
            try
            {
                this.groupBox1.Enabled = false;
                this.groupBox2.Enabled = false;
                this.panel2.Enabled = false;
                File.WriteAllText("migrate.txt", $"{txtSqliteFile.Text}\r\n{txtMySqlCon.Text}");
                using (SQLiteConnection con = new SQLiteConnection($"Data Source={file}"))
                {
                    try
                    {
                        await con.OpenAsync();
                    }
                    catch (Exception ex)
                    {
                        Log($"{file}不是有效的Sqlite库文件");
                        return;
                    }
                    MySqlContext ctx = new MySqlContext(txtMySqlCon.Text);
                    string database = ctx.Database.Connection.Database;
                    var t = await ctx.ExecuteSqlCommandAsync("SET FOREIGN_KEY_CHECKS=0;");
                    await ctx.SaveChangesAsync();
                    foreach (var item in tables)
                    {
                        var type = GetEntityType(item);
                        if (type == null)
                        {
                            Log($"跳过表{item}的数据迁移,不是系统需要的表!");
                            continue;
                        }
                        var total = con.Query<long>($"select count(*) from {item}").First();
                        Log($"正在迁移{item}表数据(共{total}条)...0%");
                        long offset = 0;
                        while (true)
                        {
                            var dataItems = await con.QueryAsync(type, $"select * from {item} order by id limit 1000 offset {offset}");
                            if (item == "checkres")
                            {
                                foreach (CheckRes dataItem in dataItems)
                                {
                                    dataItem.SigTime = await con.QueryFirstOrDefaultAsync<DateTime>($"select sigtime from posres where CheckResID={dataItem.ID}");
                                }
                            }
                            if (item== "stationres")
                            {
                                foreach (StationRes dataItem in dataItems)
                                {
                                    dataItem.SigTime = await con.QueryFirstOrDefaultAsync<DateTime>($"select sigtime from posres where StationResID={dataItem.ID}");
                                }
                            }
                            if (dataItems.Any())
                            {
                                ctx.Set(type).AddRange(dataItems);
                                await ctx.SaveChangesAsync();
                                offset += dataItems.Count();
                                UpdateProgress((int)(offset * 100 / total));
                            }
                            else
                            {
                                UpdateProgress(100);
                                break;
                            }
                        }
                    }
                    t = await ctx.ExecuteSqlCommandAsync("SET FOREIGN_KEY_CHECKS=1;");
                    await ctx.SaveChangesAsync();
                    ctx.Dispose();
                    Log($"数据迁移完成");
                }
            }
            catch (Exception ex)
            {
                Log($"数据迁移失败.{ex.Message}");
            }
            finally
            {
                this.groupBox1.Enabled = true;
                this.groupBox2.Enabled = true;
                this.panel2.Enabled = true;
            }
        }
        List<DateTime> GetRangeString(int year)
        {
            List<DateTime> list = new List<DateTime>();
            DateTime now = new DateTime(year, 1, 1, 0, 0, 0);
            while (true)
            {
                var d = Convert.ToInt32(now.DayOfWeek.ToString("d"));
                if (d == 0) d = 7;
                DateTime startWeek = now.AddDays(1 - d); //获取一周的开始日期
                list.Add(startWeek);
                now = now.AddDays(7);
                if (now.Year != year) break;
            }
            return list;
        }
        void Log(string msg)
        {
            txtLog.Items.Add($"{DateTime.Now:HH:mm:ss}--{msg}");
        }
        void UpdateProgress(int newValue)
        {
            int lastIdx = this.txtLog.Items.Count - 1;
            var last = this.txtLog.Items[lastIdx].ToString();
            last = last.Substring(0, last.IndexOf("..."));
            last = $"{last}...{newValue}%";
            this.txtLog.Items.RemoveAt(lastIdx);
            this.txtLog.Items.Add(last);
        }
        Type GetEntityType(string table)
        {
            if (table.ToLower() == nameof(XlInfo).ToLower()) return typeof(XlInfo);
            if (table.ToLower() == nameof(TaskInfo).ToLower()) return typeof(TaskInfo);
            if (table.ToLower() == nameof(TaskSig).ToLower()) return typeof(TaskSig);
            if (table.ToLower() == nameof(TxInfo).ToLower()) return typeof(TxInfo);
            if (table.ToLower() == nameof(SatInfo).ToLower()) return typeof(SatInfo);
            if (table.ToLower() == nameof(FixedStation).ToLower()) return typeof(FixedStation);
            if (table.ToLower() == nameof(SigInfo).ToLower()) return typeof(SigInfo);
            if (table.ToLower() == nameof(SigDelay).ToLower()) return typeof(SigDelay);
            if (table.ToLower() == nameof(TargetInfo).ToLower()) return typeof(TargetInfo);
            if (table.ToLower() == nameof(SysSetings).ToLower()) return typeof(SysSetings);
            if (table.ToLower() == nameof(TaskRunnningInfo).ToLower()) return typeof(TaskRunnningInfo);
            if (table.ToLower() == nameof(LogRes).ToLower()) return typeof(LogRes);
            if (table.ToLower() == nameof(StationRes).ToLower()) return typeof(StationRes);
            if (table.ToLower() == nameof(CxRes).ToLower()) return typeof(CxRes);
            if (table.ToLower() == nameof(CgRes).ToLower()) return typeof(CgRes);
            if (table.ToLower() == nameof(CgXgfRes).ToLower()) return typeof(CgXgfRes);
            if (table.ToLower() == nameof(PosRes).ToLower()) return typeof(PosRes);
            if (table.ToLower() == nameof(CheckRes).ToLower()) return typeof(CheckRes);
            return null;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            if (File.Exists("migrate.txt"))
            {
                try
                {
                    var lines = File.ReadAllLines("migrate.txt");
                    txtSqliteFile.Text = lines[0];
                    txtMySqlCon.Text = lines[1];
                }
                catch { }
            }
        }

        private async void btnCreateRange_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("1.请按照年份从小到大创建分区\r\n2.比如系统一但创建了2025年的分区后无法再创建2024年的分区\r\n3.创建分区时最好不要有太多数据库读写操作", "是否继续?", MessageBoxButtons.YesNo) != DialogResult.Yes) return;
            this.groupBox1.Enabled = false;
            this.groupBox2.Enabled = false;
            this.panel2.Enabled = false;
            try
            {
                txtLog.Items.Clear();
                string conStr = txtMySqlCon.Text.Trim();
                if (!conStr.EndsWith(";"))
                    conStr = $"{conStr};";
                List<string> tables = new List<string>() { "posres", "cgres", "cgxgfres", "cxres", "checkres", "stationres" };
                using (MySqlConnection con = new MySqlConnection(conStr))
                {
                    await con.OpenAsync();
                    foreach (var table in tables)
                    {
                        string sqlExist = $"SELECT count(*) FROM INFORMATION_SCHEMA.PARTITIONS WHERE  TABLE_SCHEMA = 'dwpt' AND TABLE_NAME = '{table}' AND PARTITION_NAME = 'p_future'";
                        var existed = (await con.QueryFirstAsync<int>(sqlExist)) > 0;
                        if (!existed)
                        {
                            string sql = $"ALTER TABLE {table} " +
                                "PARTITION BY RANGE (TO_DAYS(SigTime)) (" +
                                 $"PARTITION p_future VALUES LESS THAN MAXVALUE)";
                            await con.ExecuteAsync(sql);
                        }
                    }

                }
                string str = txtYear.Text.Trim();
                if (str.Length != 4)
                {
                    Log("年份输入错误!");
                    return;
                }
                if (!int.TryParse(str, out int year))
                {
                    Log("年份输入错误!");
                    return;
                }

                var weekTimes = GetRangeString(year);
                using (MySqlConnection con = new MySqlConnection(conStr))
                {
                    await con.OpenAsync();
                    foreach (var table in tables)
                    {
                        foreach (var item in weekTimes)
                        {
                            var sqlExist = $"SELECT count(*) FROM INFORMATION_SCHEMA.PARTITIONS WHERE  TABLE_SCHEMA = 'dwpt' AND TABLE_NAME = '{table}' AND PARTITION_NAME = 'p_{item:yyyyMMdd}'";
                            var sql = $" ALTER TABLE {table} REORGANIZE PARTITION p_future into (PARTITION p_{item:yyyyMMdd} VALUES LESS THAN (TO_DAYS('{item.AddDays(7):yyyy-MM-dd HH:mm:ss}')),PARTITION p_future VALUES LESS THAN MAXVALUE )";
                            var existed = (await con.QueryFirstAsync<int>(sqlExist)) > 0;
                            if (!existed)
                            {
                                await con.ExecuteAsync(sql);
                                Log($"表{table}分区p_{item:yyyyMMdd}创建完成");
                            }
                            else
                            {
                                Log($"表{table}分区p_{item:yyyyMMdd}创建跳过,已经存在此分区");
                            }
                        }

                    }
                }
                Log($"分区创建完成");
            }
            catch (Exception ex)
            {
                Log($"分区创建异常.{ex.Message}");
            }
            finally
            {
                this.groupBox1.Enabled = true;
                this.groupBox2.Enabled = true;
                this.panel2.Enabled = true;
            }
            /*
             *
             */
        }
    }

}