SQLiteHelper.cs

using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace SQLite
{
    public class SQLiteHelper
    {
        static NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();
        #region 静态操作
        public class SQLiteFieldTypeInfo
        {
            /// <summary>
            /// sqlite field 类型
            /// </summary>
            public string FieldType { get; set; }
            /// <summary>
            /// C# 类型
            /// </summary>
            public Type PropertyType { get; set; }

            /// <summary>
            /// C# 类型转 sqlite 字符串
            /// </summary>
            public Func<object, string> PtoS { get; set; } = DefaultToS;

            public Func<object, object> StoP { get; set; } = DefaultToP;

            public static string DefaultToS(object obj)
            {
                return obj.ToString();
            }

            public static object DefaultToP(object obj)
            {
                return obj;
            }

            public SQLiteFieldTypeInfo(string fieldtype, Type propertytype)
            {
                FieldType = fieldtype;
                PropertyType = propertytype;
            }
            public SQLiteFieldTypeInfo(string fieldtype, Type propertytype, Func<object, string> ptos, Func<object, object> stop)
            {
                FieldType = fieldtype;
                PropertyType = propertytype;
                PtoS = ptos;
                StoP = stop;

            }


        }
        public static List<SQLiteFieldTypeInfo> FieldTypeInfo { get; set; }


        static SQLiteHelper()
        {

            FieldTypeInfo = new List<SQLiteFieldTypeInfo>
            {
                new SQLiteFieldTypeInfo("INTEGER",typeof(int),
                        SQLiteFieldTypeInfo.DefaultToS,
                        (obj)=>{
                            return Convert.ToInt32(obj);
                        }
                    ),
                new SQLiteFieldTypeInfo("INTEGER",typeof(Int64)),
                new SQLiteFieldTypeInfo("BOOLEAN",typeof(bool)),
                new SQLiteFieldTypeInfo("DOUBLE",typeof(double),
                        (obj)=>((double)obj).ToStringOfSQLiteFieldType(),
                        (obj)=>{
                            if(obj == DBNull.Value)
                                return double.NaN;
                            else
                                return obj;
                        }
                     ),
                new SQLiteFieldTypeInfo("TEXT",typeof(string),
                        (obj)=>((string)obj).ToStringOfSQLiteFieldType(),
                        SQLiteFieldTypeInfo.DefaultToP
                     ),
                new SQLiteFieldTypeInfo("DATETIME",typeof(DateTime),
                        (obj)=>((DateTime)obj).ToStringOfSQLiteFieldType(),
                        SQLiteFieldTypeInfo.DefaultToP
                     )
            };
        }

        public static SQLiteFieldTypeInfo GetFieldTypeInfo(string fieldtype)
        {
            var ftis = from fti in FieldTypeInfo where fti.FieldType == fieldtype select fti;
            if (ftis.Count() > 0)
            {
                return ftis.First();
            }
            else
            {
                return null;
            }
        }


        public static string GetTableName(Type type)
        {
            var attributes = type.GetCustomAttributes(typeof(TableAttribute), false);
            if (attributes.Count() > 0)
            {
                return ((TableAttribute)attributes.First()).Name;
            }
            else
            {
                return type.Name;
            }
        }
        static string GetCreateIndexCommandText_fieldText(Type type)
        {

            string total_fieldtext = "";
            List<FieldTextIndex> fieldTexts = new List<FieldTextIndex>();

            PropertyInfo[] propertyInfos = type.GetProperties();
            foreach (var propertyInfo in propertyInfos)
            {
                //忽略
                if (propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).Count() > 0)
                    continue;

                if (!(propertyInfo.GetCustomAttributes(typeof(IndexAttribute), false).Count() > 0))
                    continue;//没有INDEX

                FieldTextIndex fieldText = new FieldTextIndex();
                fieldTexts.Add(fieldText);
                PropertyIndexAttribute propertyIndex = propertyInfo.GetCustomAttribute(typeof(PropertyIndexAttribute)) as PropertyIndexAttribute;
                if (propertyIndex != null)
                    fieldText.index = propertyIndex.Index;//默认index=0

                SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == propertyInfo.PropertyType);
                fieldText.fieldtext = propertyInfo.Name;

            }
            if (fieldTexts.Count() == 0)
                return null;

            //从小到大排序
            fieldTexts.Sort((fieldTextIndex0, fieldTextIndex1) =>
            {
                if (fieldTextIndex0.index < fieldTextIndex1.index)
                    return -1;
                if (fieldTextIndex0.index > fieldTextIndex1.index)
                    return 1;
                else
                    return 0;
            });

            for (int i = 0; i < fieldTexts.Count(); i++)
            {
                var fieldTextIndex = fieldTexts[i];
                if (i != 0)
                    total_fieldtext += ",";
                total_fieldtext += fieldTextIndex.fieldtext;
            }
            return total_fieldtext;
        }
        static string GetCreateTableCommandText_fieldText(Type type)
        {

            string total_fieldtext = "";
            List<FieldTextIndex> fieldTexts = new List<FieldTextIndex>();

            PropertyInfo[] propertyInfos = type.GetProperties();
            foreach (var propertyInfo in propertyInfos)
            {
                //忽略
                if (propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).Count() > 0)
                    continue;

                FieldTextIndex fieldText = new FieldTextIndex();
                fieldTexts.Add(fieldText);
                PropertyIndexAttribute propertyIndex = propertyInfo.GetCustomAttribute(typeof(PropertyIndexAttribute)) as PropertyIndexAttribute;
                if (propertyIndex != null)
                    fieldText.index = propertyIndex.Index;//默认index=0

                SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == propertyInfo.PropertyType);
                string text = "";
                text += string.Format("{0} {1}", propertyInfo.Name, fieldTypeInfo.FieldType);

                //主键
                if (propertyInfo.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0)
                    text += " PRIMARY KEY";
                fieldText.fieldtext = text;
                
            }
            //从小到大排序
            fieldTexts.Sort((fieldTextIndex0, fieldTextIndex1) =>
            {
                if (fieldTextIndex0.index < fieldTextIndex1.index)
                    return -1;
                if (fieldTextIndex0.index > fieldTextIndex1.index)
                    return 1;
                else
                    return 0;
            });

            for (int i = 0; i < fieldTexts.Count(); i++)
            {
                var fieldTextIndex = fieldTexts[i];
                if (i != 0)
                    total_fieldtext += ",";
                total_fieldtext += fieldTextIndex.fieldtext;
            }
            return total_fieldtext;
        }
        public static string GetCreateTableCommandText(Type type)
        {
            //CREATE TABLE table_name(
            //column1 datatype  PRIMARY KEY,
            //column2 datatype,
            //   column3 datatype,
            //   .....
            //   columnN datatype,
            //)
            string tablename = GetTableName(type);
            string fieldtext = GetCreateTableCommandText_fieldText(type);
            string commandText = string.Format("CREATE TABLE {0} ({1})", tablename, fieldtext);
            return commandText;
        }
        public static string GetCreateIndexCommandText(Type type)
        {
            //CREATE INDEX index_name
            //on table_name(column1, column2);
            string tablename = GetTableName(type);

            string fieldtext = GetCreateIndexCommandText_fieldText(type);
            if (string.IsNullOrEmpty(fieldtext))
                return null;
            string commandText = string.Format("CREATE INDEX {0}_INDEX ON {0} ({1})", tablename, fieldtext);
            return commandText;
        }
        class FieldTextIndex
        {
            public int index = 0;
            public string fieldtext = "";
        }
        static string GetInsertCommandText_fieldText(object cell)
        {
            Type type = cell.GetType();
            string total_fieldtext = "";
            List<FieldTextIndex> fieldTexts = new List<FieldTextIndex>();

            PropertyInfo[] propertyInfos = type.GetProperties();
            foreach (var propertyInfo in propertyInfos)
            {
                //忽略
                if (propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).Count() > 0)
                    continue;
                FieldTextIndex fieldText = new FieldTextIndex();
                fieldTexts.Add(fieldText);
                PropertyIndexAttribute propertyIndex = propertyInfo.GetCustomAttribute(typeof(PropertyIndexAttribute)) as PropertyIndexAttribute;
                if (propertyIndex != null)
                    fieldText.index = propertyIndex.Index;//默认index=0

                object o = propertyInfo.GetValue(cell, null);

                SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == propertyInfo.PropertyType);

                fieldText.fieldtext = fieldTypeInfo.PtoS(o);
                
            }

            //从小到大排序
            fieldTexts.Sort((fieldTextIndex0, fieldTextIndex1) =>
            {
                if (fieldTextIndex0.index < fieldTextIndex1.index)
                    return -1;
                if (fieldTextIndex0.index > fieldTextIndex1.index)
                    return 1;
                else
                    return 0;
            });

            for (int i = 0; i < fieldTexts.Count(); i++)
            {
                var fieldTextIndex = fieldTexts[i];
                if (i != 0)
                    total_fieldtext += ",";
                total_fieldtext += fieldTextIndex.fieldtext;
            }
            return total_fieldtext;
        }
        public static string GetInsertCommandText(object cell)
        {
            //不用
            //INSERT INTO TABLE_NAME[(column1, column2, column3,...columnN)]
            //VALUES(value1, value2, value3,...valueN);

            //使用
            //INSERT INTO TABLE_NAME VALUES(value1, value2, value3,...valueN);

            Type type = cell.GetType();
            string tablename = GetTableName(type);
            string fieldtext = GetInsertCommandText_fieldText(cell);
            string commandText = string.Format("INSERT INTO {0} VALUES({1})", tablename, fieldtext);
            return commandText;
        }


        static string GetUpdateCommandText_fieldText(object cell)
        {
            //UPDATE table_name
            //SET column1 = value1, column2 = value2...., columnN = valueN
            //WHERE[condition];

            Type type = cell.GetType();
            string tablename = GetTableName(type);

            string fieldtext = "";
            PropertyInfo[] propertyInfos = type.GetProperties();
            foreach (var propertyInfo in propertyInfos)
            {
                //忽略
                if (propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).Count() > 0)
                    continue;

                if (fieldtext != "")
                {
                    fieldtext += ",";
                }

                object o = propertyInfo.GetValue(cell, null);


                SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == propertyInfo.PropertyType);


                fieldtext += string.Format("{0} = {1}", propertyInfo.Name, fieldTypeInfo.PtoS(o));
                
            }

            return fieldtext;
        }
        /// <summary>
        /// condition 为 "WHERE ......"
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static string GetUpdateCommandText(object cell, string condition)
        {
            //UPDATE table_name
            //SET column1 = value1, column2 = value2...., columnN = valueN
            //WHERE[condition];

            Type type = cell.GetType();
            string tablename = GetTableName(type);
            string fieldtext = GetUpdateCommandText_fieldText(cell);
            string commandText = $"UPDATE {tablename} SET {fieldtext}";

            if (!string.IsNullOrEmpty(condition))
                commandText += $" {condition}";

            return commandText;
        }

        public static List<T> ToObjs<T>(DataTable dataTable)
            where T : new()
        {
            List<T> list = new List<T>();
            foreach (DataRow dataRow in dataTable.Rows)
            {
                list.Add(ToObj<T>(dataRow));
            }
            return list;
        }

        static void SetObj(object t, DataRow dataRow)
        {
            Type type = t.GetType();

            PropertyInfo[] propertyInfos = type.GetProperties();

            foreach (var propertyInfo in propertyInfos)
            {
                //忽略
                if (propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).Count() > 0)
                    continue;

                Type ptype = propertyInfo.PropertyType;

                SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == ptype);
                object o = fieldTypeInfo.StoP(dataRow[propertyInfo.Name]);
                propertyInfo.SetValue(t, o, null);
                
            }
        }
        public static T ToObj<T>(DataRow dataRow)
            where T : new()
        {
            T t = new T();
            SetObj(t, dataRow);
            return t;
        }

        #endregion


        public string ConnectionString;
        /// <summary> 
        /// 查询数据库中的所有数据类型信息。
        /// </summary> 
        /// <returns></returns> 
        /// <exception cref="Exception"></exception>
        public DataTable ExecuteReader(string sql)
        {
            DataTable data;
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    connection.Open();
                    command.CommandText = sql;
                    // 开始读取
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    data = new DataTable();
                    adapter.Fill(data);
                    connection.Close();
                }

            }
            return data;
        }
        /// <summary> 
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句。</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
        /// <returns></returns> 
        /// <exception cref="Exception"></exception>
        public int ExecuteNonQuery(string sql)
        {
            int ret = 0;
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    connection.Open();
                    command.CommandText = sql;
                    ret = command.ExecuteNonQuery();
                    connection.Close();
                }
            }
            return ret;

        }


        /// <summary>
        ///     
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>The first column of the first row of the first resultset from the query.</returns>
        public object ExecuteScalar(string sql)
        {
            object obj = null;
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    connection.Open();
                    command.CommandText = sql;
                    obj = command.ExecuteScalar();
                    connection.Close();
                }
            }
            return obj;
        }
        /// <summary>
        /// 多行执行
        /// </summary>
        /// <param name="queryList"></param>
        /// <returns></returns>
        public bool QueryTran(IEnumerable<string> queryList)
        {
            if (isHoldQueryTran)
            {
                holdQueryList.AddRange(queryList);
                return true;
            }
            else
            {
                using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        connection.Open();

                        SQLiteTransaction tran = connection.BeginTransaction();
                        bool check = false;
                        try
                        {
                            foreach (string item in queryList)
                            {
                                command.CommandText = item;
                                command.ExecuteNonQuery();
                            }
                            tran.Commit();
                            check = true;
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            check = false;
                            logger.Error(ex, Newtonsoft.Json.JsonConvert.SerializeObject(queryList));
                            throw ex;
                        }
                        finally
                        {
                            connection.Close();
                        }
                        return check;
                    }
                }
            }

        }

        /// <summary>
        /// 异步
        /// </summary>
        /// <param name="queryList"></param>
        /// <returns></returns>
        public void QueryTranAsync(IEnumerable<string> queryList)
        {
            new Task((obj) => {
                var _sqls = obj as IEnumerable<string>;
                QueryTran(_sqls);
            }, queryList).Start(Scheduler);
        }
        TaskScheduler Scheduler { get; } = new StepByStepTaskScheduler();

        List<string> holdQueryList = new List<string>();
        bool isHoldQueryTran = false;
        public void HoldQueryTran()
        {
            isHoldQueryTran = true;
        }
        public bool ReleaseQueryTran()
        {
            if (holdQueryList.Count() > 0)
            {
                using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        connection.Open();

                        SQLiteTransaction tran = connection.BeginTransaction();
                        bool check = false;
                        try
                        {
                            foreach (string item in holdQueryList)
                            {
                                command.CommandText = item;
                                command.ExecuteNonQuery();
                            }
                            tran.Commit();
                            check = true;
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            check = false;
                            throw ex;
                        }
                        finally
                        {
                            holdQueryList.Clear();
                            connection.Close();
                            isHoldQueryTran = false;
                        }
                        return check;
                    }
                }
            }
            isHoldQueryTran = false;
            return true;
        }
        /// <summary>
        /// 出错代码
        /// </summary>
        public string ErrorMsg { get; set; }
        /// <summary>
        /// 输入DDLs 判断这些table都是否合法
        /// </summary>
        /// <param name="DDLs">key=tablename, value=DDL</param>
        /// <returns></returns>
        public bool IsTableValid(Dictionary<string, string> DDLs)
        {

            //检测 table 是否合法
            DataTable data = ExecuteReader("SELECT name,sql FROM sqlite_master WHERE type = 'table'");

            //任意一个表不对，或者不存在，都必须重建
            bool isVaild = true;
            foreach (var kv in DDLs)
            {
                string tablename = kv.Key;
                string createtable_sql = kv.Value;

                var sqls = from r in data.AsEnumerable() where (string)r["name"] == tablename select r["sql"];
                if (sqls.Count() == 0)
                {
                    //不存在该表，创建
                    ErrorMsg = $"sqlite_master 不能找到 name = '{tablename}' 的 sql";
                    isVaild = false;
                    break;
                }
                else
                {
                    string sql = (string)sqls.First();
                    if (sql != createtable_sql)
                    {
                        if (!GetTableInfoFromDDL(sql, out SQLiteTableInfo tableInfo0))
                        {
                            ErrorMsg = $"sqlite_master 找到 name = '{tablename}' 的 sql 不能解析";
                            isVaild = false;
                            break;
                        }
                        if (!GetTableInfoFromDDL(createtable_sql, out SQLiteTableInfo tableInfo1))
                        {
                            ErrorMsg = $"程序中 name = '{tablename}' 的 sql 不能解析";
                            isVaild = false;
                            break;
                        }

                        if (!tableInfo0.Equals(tableInfo1))
                        {
                            ErrorMsg = $"sqlite_master 找到 name = '{tablename}' 的 sql 不符合要求, " + tableInfo0.ErrorMsg;
                            isVaild = false;
                            break;
                        }
                    }
                }
            }
            return isVaild;
        }

        public enum IsTableValidResult
        {
            OK,
            NotHere,
            FormatErr
        }
        /// <summary>
        /// 输入DDLs 判断这些table都是否合法
        /// </summary>
        /// <param name="DDLs">key=tablename, value=DDL</param>
        /// <returns></returns>
        public bool IsTableValid(Dictionary<string, string> DDLs,out Dictionary<string,IsTableValidResult> results)
        {
            results = new Dictionary<string, IsTableValidResult>();
            //检测 table 是否合法
            DataTable data = ExecuteReader("SELECT name,sql FROM sqlite_master WHERE type = 'table'");

            //任意一个表不对，或者不存在，都必须重建
            foreach (var kv in DDLs)
            {
                string tablename = kv.Key;
                string createtable_sql = kv.Value;

                var sqls = from r in data.AsEnumerable() where (string)r["name"] == tablename select r["sql"];
                if (sqls.Count() == 0)
                {
                    //不存在该表
                    ErrorMsg = $"sqlite_master 不能找到 name = '{tablename}' 的 sql";
                    results.Add(tablename, IsTableValidResult.NotHere);
                    continue;
                }

                string sql = (string)sqls.First();
                if (sql == createtable_sql)
                {
                    //完全一样
                    results.Add(tablename, IsTableValidResult.OK);
                    continue;
                }

                if (!GetTableInfoFromDDL(sql, out SQLiteTableInfo tableInfo0))
                {
                    ErrorMsg = $"sqlite_master 找到 name = '{tablename}' 的 sql 不能解析";
                    results.Add(tablename, IsTableValidResult.FormatErr);
                    continue;
                }

                if (!GetTableInfoFromDDL(createtable_sql, out SQLiteTableInfo tableInfo1))
                {
                    ErrorMsg = $"程序中 name = '{tablename}' 的 sql 不能解析";
                    results.Add(tablename, IsTableValidResult.FormatErr);
                    continue;
                }

                if (!tableInfo0.Equals(tableInfo1))
                {
                    ErrorMsg = $"sqlite_master 找到 name = '{tablename}' 的 sql 不符合要求, " + tableInfo0.ErrorMsg;
                    results.Add(tablename, IsTableValidResult.FormatErr);
                    continue;
                }

                //虽然不一样，都也是合法的
                results.Add(tablename, IsTableValidResult.OK);
            }

            return results.All(kv => kv.Value == IsTableValidResult.OK);
        }
        public bool GetTableInfoFromDDL(string DDL, out SQLiteTableInfo tableInfo)
        {
            tableInfo = null;
            Regex regex_key = new Regex(@"PRIMARY KEY");
            //CREATE TABLE boltmap(ID INTEGER PRIMARY KEY, MID INTEGER, RBegin INTEGER, REnd INTEGER)
            Regex regex = new Regex(@"CREATE TABLE\s+(\w+)\s+\((.+)\)");
            Match match = regex.Match(DDL);
            if (!match.Success)
            {
                ErrorMsg = "不能匹配 CREATE TABLE 的格式";
                return false;
            }
            tableInfo = new SQLiteTableInfo();
            tableInfo.Name = match.Groups[1].Value;

            string fields_sql = match.Groups[2].Value;
            string[] fields_str = fields_sql.Split(',');
            foreach (string str in fields_str)
            {
                SQLiteFieldInfo fieldInfo = new SQLiteFieldInfo();
                string str1 = str.Trim();
                string[] ss = str1.Split(' ');
                if (ss.Length < 2)
                {
                    ErrorMsg = $"{str1} 格式出错";
                    return false;
                }
                fieldInfo.Name = ss[0];
                fieldInfo.Type = ss[1];
                if (regex_key.IsMatch(str1))
                    fieldInfo.IsKey = true;
                tableInfo.FieldInfos.Add(fieldInfo);
            }
            return true;
        }

        public bool ParseINDEX(string INDEX, out string indexName, out string tableName, out string[] fieldNames)
        {
            indexName = null;
            tableName = null;
            fieldNames = null;
            //CREATE TABLE boltmap(ID INTEGER PRIMARY KEY, MID INTEGER, RBegin INTEGER, REnd INTEGER)
            Regex regex = new Regex(@"CREATE INDEX\s+(\w+)\s+ON\s+(\w+)\s+\((.+)\)");
            Match match = regex.Match(INDEX);
            if (!match.Success)
            {
                ErrorMsg = "不能匹配 CREATE INDEX 的格式";
                return false;
            }
            indexName = match.Groups[1].Value;
            tableName = match.Groups[2].Value;

            string fields_sql = match.Groups[3].Value;
            fieldNames = fields_sql.Split(',');

            return true;
        }

        /// <summary>
        /// 输入INDEXs 判断是否存在
        /// </summary>
        /// <param name="INDEXs">key=tablename, value=INDEX</param>
        /// <returns></returns>
        public bool IsIndexValid(Dictionary<string, string> INDEXs, out Dictionary<string, IsTableValidResult> results)
        {
            results = new Dictionary<string, IsTableValidResult>();
            //检测 table 是否合法
            DataTable data = ExecuteReader("SELECT tbl_name,sql FROM sqlite_master WHERE type = 'index'");

            //任意一个表不对，或者不存在，都必须重建
            foreach (var kv in INDEXs)
            {
                string tableName = kv.Key;
                string createindex_sql = kv.Value;

                var sqls = from r in data.AsEnumerable() where (string)r["tbl_name"] == tableName select r["sql"];
                if (sqls.Count() == 0)
                {
                    //不存在该表
                    ErrorMsg = $"sqlite_master 不能找到 tbl_name = '{tableName}' 的 sql";
                    results.Add(tableName, IsTableValidResult.NotHere);
                    continue;
                }

                string sql = (string)sqls.First();
                if (sql == createindex_sql)
                {
                    //完全一样
                    results.Add(tableName, IsTableValidResult.OK);
                    continue;
                }

                if (!ParseINDEX(createindex_sql, out string indexName0, out string tableName0, out string[] fieldNames0)) 
                {
                    ErrorMsg = $"sqlite_master 找到 tbl_name = '{tableName}' 的 sql 不能解析";
                    results.Add(tableName, IsTableValidResult.FormatErr);
                    continue;
                }

                if (!ParseINDEX(sql, out string indexName2, out string tableName2, out string[] fieldNames2))
                {
                    ErrorMsg = $"程序中 找到 tbl_name = '{tableName}' 的 sql 不能解析";
                    results.Add(tableName, IsTableValidResult.FormatErr);
                    continue;
                }
                
                if (tableName2 != tableName0) {
                    ErrorMsg = $"sqlite_master 找到 tbl_name = '{tableName}' 的 sql 不符合要求";
                    results.Add(tableName, IsTableValidResult.FormatErr);
                    continue;
                }

                //检查 fieldNames0,fieldNames 是否一致
                if (fieldNames0.Except(fieldNames2).Count() != 0) {
                    ErrorMsg = $"sqlite_master 找到 tbl_name = '{tableName}' 的 sql 不符合要求";
                    results.Add(tableName, IsTableValidResult.FormatErr);
                    continue;
                }

                if (fieldNames2.Except(fieldNames0).Count() != 0)
                {
                    ErrorMsg = $"sqlite_master 找到 tbl_name = '{tableName}' 的 sql 不符合要求";
                    results.Add(tableName, IsTableValidResult.FormatErr);
                    continue;
                }

                //虽然不一样，都也是合法的
                results.Add(tableName, IsTableValidResult.OK);
            }

            return results.All(kv => kv.Value == IsTableValidResult.OK);
        }
    }

    public class SQLiteTableInfo
    {
        /// <summary>
        /// 名称
        /// </summary>
        public string Name;
        public List<SQLiteFieldInfo> FieldInfos = new List<SQLiteFieldInfo>();
        public override bool Equals(object obj)
        {
            SQLiteTableInfo tableinfo = obj as SQLiteTableInfo;
            if (tableinfo.Name != Name)
            {
                ErrorMsg = $"名称不相等 期待.{tableinfo.Name}!=数据库.{Name}";
                return false;
            }
            if (tableinfo.FieldInfos.Count() != FieldInfos.Count())
            {
                ErrorMsg = $"field 数量不同 期待.{tableinfo.FieldInfos.Count()}!=数据库.{FieldInfos.Count()}";
                return false;
            }
            for (int i = 0; i < FieldInfos.Count(); i++)
            {
                if (!FieldInfos[i].Equals(tableinfo.FieldInfos[i]))
                {
                    ErrorMsg = $"field 不同 期待.({tableinfo.FieldInfos[i]})!=数据库.({FieldInfos[i]})";
                    return false;
                }
            }
            ErrorMsg = "OK";
            return true;
        }
        public string ErrorMsg { get; set; }
    }
    public class SQLiteFieldInfo
    {
        /// <summary>
        /// 类型
        /// </summary>
        public string Type;
        /// <summary>
        /// 名称
        /// </summary>
        public string Name;
        /// <summary>
        /// 是主键？
        /// </summary>
        public bool IsKey;
        public override bool Equals(object obj)
        {
            SQLiteFieldInfo fieldInfo = obj as SQLiteFieldInfo;
            if (Type != fieldInfo.Type)
                return false;
            if (Name != fieldInfo.Name)
                return false;
            if (IsKey != fieldInfo.IsKey)
                return false;
            return true;
        }
        public override string ToString()
        {
            string s = $"{Name} As {Type}";
            if (IsKey)
                s += " IsKey";
            return s;
        }
    }

    public static class SQLiteFieldTypeExtern
    {
        public static string ToStringOfSQLiteFieldType(this DateTime dt)
        {
            return $"'{dt:yyyy-MM-dd HH:mm:ss.fff}'";
        }
        public static string ToStringOfSQLiteFieldType(this string str)
        {
            return $"'{str}'";
        }
        public static string ToStringOfSQLiteFieldType(this double d)
        {
            if (double.IsNaN(d))
                return "NULL";
            else return d.ToString();
        }
    }


    public class StepByStepTaskScheduler : TaskScheduler
    {
        public static new TaskScheduler Current { get; } = new StepByStepTaskScheduler();
        public static new TaskScheduler Default { get; } = Current;

        public static StepByStepTaskScheduler Instance { get; } = (StepByStepTaskScheduler)Current;
        private readonly BlockingCollection<Task> m_queue = new BlockingCollection<Task>();

        public StepByStepTaskScheduler()
        {
            //Thread thread = new Thread(Run);
            //thread.IsBackground = true;//设为为后台线程，当主线程结束时线程自动结束
            //thread.Start();
            Task.Factory.StartNew(Run);
        }

        private void Run()
        {
            //Console.WriteLine($"MyTaskScheduler, ThreadID: {Thread.CurrentThread.ManagedThreadId}");
            Task t;
            while (m_queue.TryTake(out t, System.Threading.Timeout.Infinite))
            {
                TryExecuteTask(t);//在当前线程执行Task
            }
        }

        protected override IEnumerable<Task> GetScheduledTasks()
        {
            return m_queue;
        }
        public BlockingCollection<Task> ScheduledTasks
        {
            get { return m_queue; }
        }
        protected override void QueueTask(Task task)
        {
            m_queue.Add(task);//t.Start(MyTaskScheduler.Current)时，将Task加入到队列中
        }

        //当执行该函数时，程序正在尝试以同步的方式执行Task代码
        protected override bool TryExecuteTaskInline(Task task, bool taskWasPreviouslyQueued)
        {
            return false;
        }
    }
}