XiuXianProject/GameClient/Assets/Game/CSShare/GFG/HotUpdate/Sqlite/SQLiteHelper.cs

using System;
using UnityEngine;
#if UNITY_2017_1_OR_NEWER
using Mono.Data.Sqlite;
#else
    using Microsoft.Data.Sqlite;
#endif
using System.Collections.Generic;

namespace GFGGame
{
    public class SQLiteHelper : SingletonBase<SQLiteHelper>
    {
        private int _openCount = 0;

        /// <summary>
        /// 是否开启数据库语句日志
        /// </summary>
        private bool _logable;
        /// <summary>
        /// 数据库文件路径
        /// </summary>
        private string _connectionPath;
        /// <summary>
        /// 数据库连接定义
        /// </summary>
        private SqliteConnection dbConnection;

        /// <summary>
        /// SQL命令定义
        /// </summary>
        private SqliteCommand dbCommand;

        /// <summary>
        /// 数据读取定义
        /// </summary>
        private SqliteDataReader dataReader;

        public void Init(bool logable, string connectionPath)
        {
            _logable = logable;
            _connectionPath = connectionPath;
        }

        /// <summary>
        /// 构造函数    
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public void OpenConnection()
        {
            _openCount++;
            if (_openCount > 1) return;
            //LogHelper.LogEditor("OpenConnection");
            //构造数据库连接
            dbConnection = new SqliteConnection(_connectionPath);
            //打开数据库
            dbConnection.Open();
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <returns>The query.</returns>
        /// <param name="queryString">SQL命令字符串</param>
        private SqliteDataReader ExecuteQuery(string queryString)
        {
            try
            {
                if (_logable)
                {
                    ET.Log.Info(queryString);
                }
                dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = queryString;
                dataReader = dbCommand.ExecuteReader();
            }
            catch (Exception e)
            {
                ET.Log.Error(queryString + "," + e);
                throw;
            }
           
            //dbConnection.EnlistTransaction();
            return dataReader;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseConnection(bool force = false)
        {
            _openCount--;
            if (_openCount > 0 && !force) return;
            _openCount = 0;
               //#if UNITY_2017_1_OR_NEWER
               //if(Application.isPlaying && !force)
               // {
               //     return;
               // }
               //#else

            //#endif

            //LogHelper.LogEditor("CloseConnection");
            //销毁Command
            if (dbCommand != null)
            {
                dbCommand.Cancel();
            }
            dbCommand = null;

            //销毁Reader
            if (dataReader != null)
            {
                dataReader.Close();
            }
            dataReader = null;

            //销毁Connection
            if (dbConnection != null)
            {
                dbConnection.Close();
            }
            dbConnection = null;
        }


        public SqliteDataReader ReadLine(string tableName)
        {
            string queryString = string.Format("select * from " + tableName + " t ");
            return ExecuteQuery(queryString);
        }

        public SqliteDataReader CreateIndex(string tableName, List<string> colNames)
        {
            string[] keys = colNames.ToArray();
            for (int i = 0; i < keys.Length; i++)
            {
                keys[i] = "_" + keys[i];
            }
            string index_name = string.Join("", colNames) + "_index_"+ tableName;
            string colNamesStr = string.Join(",", keys);
            string queryString = $"CREATE INDEX {index_name} ON {tableName} ({colNamesStr});";
            //return null;
            return ExecuteQuery(queryString);
        }

        /// <summary>
        /// 读取整张数据表
        /// </summary>
        /// <returns>The full table.</returns>
        /// <param name="tableName">数据表名称</param>
        public SqliteDataReader ReadFullTable(string tableName)
        {
            string queryString = "SELECT * FROM " + tableName;
            return ExecuteQuery(queryString);
        }

        /// <summary>
        /// Reads the table.
        /// </summary>
        /// <returns>The table.</returns>
        /// <param name="tableName">Table name.</param>
        /// <param name="items">Items.</param>
        /// <param name="colNames">Col names.</param>
        /// <param name="colValues">Col values.</param>
        public SqliteDataReader ReadTable(string tableName, string[] colNames, string[] colValues)
        {
            string queryString = "SELECT * FROM " + tableName + " WHERE ";
            for (int i = 0; i < colNames.Length; i++)
            {
                if (i > 0)
                {
                    queryString += " AND ";
                }
                if (colValues[i] == "0")
                {
                    queryString += "(" + "_" + colNames[i] + " = '" + colValues[i] + "' OR _" + colNames[i] + " = '')";
                }
                else
                {
                    queryString += "_" + colNames[i] + " = '" + colValues[i] + "' ";
                }
            }
            return ExecuteQuery(queryString);
        }

        public SqliteDataReader ReadTableByOneCol(string tableName, string colName, string colValue)
        {
            string queryString = $"select * from {tableName} where _{colName} = '{colValue}'";
            return ExecuteQuery(queryString);
        }
#if UNITY_EDITOR
#region
        /// <summary>
        /// 向指定数据表中插入数据
        /// </summary>
        /// <returns>The values.</returns>
        /// <param name="tableName">数据表名称</param>
        /// <param name="values">插入的数值</param>
        public SqliteDataReader InsertValues(string tableName, string[] values)
        {
            //获取数据表中字段数目
            int fieldCount = ReadFullTable(tableName).FieldCount;
            //当插入的数据长度不等于字段数目时引发异常
            if (values.Length != fieldCount)
            {
                throw new SqliteException("values.Length!=fieldCount");
            }

            string queryString = "INSERT INTO " + tableName + " VALUES ('" + values[0];
            for (int i = 1; i < values.Length; i++)
            {
                queryString += "', '" + values[i];
            }
            queryString += "' )";
            return ExecuteQuery(queryString);
        }

        /// <summary>
        /// 更新指定数据表内的数据
        /// </summary>
        /// <returns>The values.</returns>
        /// <param name="tableName">数据表名称</param>
        /// <param name="colNames">字段名</param>
        /// <param name="colValues">字段名相应的数据</param>
        /// <param name="key">关键字</param>
        /// <param name="value">关键字相应的值</param>
        public SqliteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string value)
        {
            //当字段名称和字段数值不正确应时引发异常
            if (colNames.Length != colValues.Length)
            {
                throw new SqliteException("colNames.Length!=colValues.Length");
            }

            string queryString = "UPDATE " + tableName + " SET _" + colNames[0] + "='" + colValues[0] + "'";
            for (int i = 1; i < colValues.Length; i++)
            {
                queryString += ", _" + colNames[i] + "='" + colValues[i] + "'";
            }
            queryString += " WHERE _" + key + "='" + value + "'";
            return ExecuteQuery(queryString);
        }

        /// <summary>
        /// 创建数据表
        /// </summary> +
        /// <returns>The table.</returns>
        /// <param name="tableName">数据表名</param>
        /// <param name="colNames">字段名</param>
        /// <param name="colTypes">字段名类型</param>
        public SqliteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes)
        {
            string queryString = "CREATE TABLE " + tableName + "( '" + colNames[0] + "' " + colTypes[0];
            for (int i = 1; i < colNames.Length; i++)
            {
                queryString += ", " + colNames[i] + " " + colTypes[i];
            }
            queryString += "  ) ";
            return ExecuteQuery(queryString);
        }
        //清除库中所有表格
        public void ClearAllTables()
        {
            string queryString = "select * from sqlite_master";
            SqliteDataReader reader = ExecuteQuery(queryString);
            List<string> tables = new List<string>();
            while (reader.Read())
            {

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var name = reader.GetName(i);
                    if (name == "tbl_name")
                    {
                        var tbName = reader.GetValue(i).ToString();
                        //LogHelper.LogEditor($" {tbName}");
                        if(!tables.Contains(tbName))
                            tables.Add(tbName);
                    }

                }
            }
            reader.Close();
            foreach (var tbName in tables)
            {
                queryString = $"DROP TABLE {tbName};";
                ExecuteQuery(queryString);
            }
        }
#endregion
#endif
    }
}