SQLiteManger 第一部分

SQLiteManager相比于SQLiteHelper更像是一个帮手类，实现了一些常用的操作和查询，并且提供了不少快捷菜单方法（在编辑器中不用播放游戏即可执行测试）

SQLiteManager只提供了测试连接、创建数据库和显示所有表名的快捷方法，TableTool脚本提供的多一些

// Mono.Data.Sqlite请自行百度解决

// SQLiteHelper合集里有

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Reflection;
using UnityEngine;
using Mono.Data.Sqlite;
using MyHelper;

namespace SaveAndLoader
{
    public class SQLiteManager : MonoBehaviour
    {
        [Header(@"省略直到“StreamingAssets\”为止的路径字符")]
        public string DBPath;
        [Header(@"不要省略数据库的副文件名(如果有的话)")]
        public string DBName;

        SqliteConnection connection;
        SqliteCommand command;
        string connectionString;
        public SqliteDataReader reader;
        // ==================== ==================== ==================== ====================
        private void Start()
        {
            RefreshConnectionString();
        }
        void RefreshConnectionString()
        {
            connectionString = Application.streamingAssetsPath.Replace('/', '\\') + "\\";

            if (string.IsNullOrEmpty(DBPath))
                connectionString += DBName;
            else
                connectionString += DBPath.Replace('/', '\\') + "\\" + DBName;
        }
        // ==================== ==================== ==================== ====================
        // Connect将先尝试断开再连接，如果需要保持连接时不要欣然使用
        public bool Connect()
        {
            if (connection != null)
                DisconnectAndClear();

            RefreshConnectionString();
            return SQLiteHelper.Connect(out connection, out command, connectionString);
        }
        public void DisconnectAndClear()
        {
            if (reader != null && !reader.IsClosed)
                reader.Close();
            SQLiteHelper.DisconnectAndClear(ref connection, ref command);
        }
        public void ExecuteQuery(string queryString)
        {
            if (reader != null && !reader.IsClosed)
                reader.Close();
            reader = SQLiteHelper.ExecuteQuery(command, queryString);
        }
        public void VACUUM()
        {
            Connect();
            ExecuteQuery("VACUUM");
            DisconnectAndClear();
        }
        // ==================== ==================== ==================== ====================
        public void GetAllTablesName()
        {
            FileInfo DBFileInfo = new FileInfo(connectionString);
            if (File.Exists(connectionString) && DBFileInfo.Length == 0)
            {
                if (reader != null && !reader.IsClosed)
                { reader.Close(); reader = null; return; }
            }

            string queryString = "SELECT name FROM sqlite_master where type='table' order by name";
            ExecuteQuery(queryString);
        }
        public List<string> GetAllTablesNameToList()
        {
            GetAllTablesName();
            if (reader == null)
                return new List<string>();

            List<string> returnList = new List<string>();
            while (reader.Read())
            {
                returnList.Add(reader["name"].ToString());
            }
            reader.Close();
            return returnList;
        }
        public bool IsTableExist(string tableName)
        {
            return GetAllTablesNameToList().Contains(tableName);
        }
        public void CreateTable(string tableName, List<TableColInfo> list_ColInfos)
        {
            if (!CheckList_TableColInfos(list_ColInfos))
            { Debug.LogError("ColInfo列表未通过检查！"); return; }
            if (IsTableExist(tableName))
            { Debug.LogError("表名已经被占用！"); return; }
            // ---------------- ---------------- ----------------
            string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + " ( ";
            int n = 0;
            foreach (var col in list_ColInfos)
            {
                queryString += col.name + " " + col.type.ToString();

                // 添加主键信息
                if (col.isPK)
                    queryString += " PRIMARY KEY";
                // 添加NOTNULL信息
                if (col.isNOTNULL)
                    queryString += " NOT NULL";
                // 添加默认值信息
                if (!string.IsNullOrEmpty(col.dflt_value))
                    queryString += " DEFAULT " + col.dflt_value;

                if (n < list_ColInfos.Count - 1)
                    queryString += ",";

                n++;
            }
            queryString += ")";
            ExecuteQuery(queryString);
            reader.Close();
        }
        public void RenameTable(string tableName, string newTableName)
        {
            string queryString = "ALTER TABLE " + tableName + " RENAME TO " + newTableName;
            ExecuteQuery(queryString);
            reader.Close();
        }
        public void DeleteTable(string tableName)
        {
            string queryString = "DROP TABLE " + tableName;
            ExecuteQuery(queryString);
            reader.Close();
        }
        // ==================== ==================== ==================== ====================
        public bool CheckList_TableColInfos(List<TableColInfo> cols)
        {
            if (cols == null || cols.Count == 0) return false;
            //---------------- ---------------- ----------------
            List<string> existingColNames = new List<string>();
            int PKCount = 0;
            foreach (var col in cols)
            {
                // 检查：所有的列名都不可为null或""
                if (string.IsNullOrEmpty(col.name))
                    return false;

                // 检查：name不可重复
                if (existingColNames.Contains(col.name))
                    return false;
                else
                    existingColNames.Add(col.name);

                // 检查：数据类型不包含NULL型
                if (col.type == SQLiteDataType.NULL) return false;

                // 检查：主键只能有1个
                if (col.isPK)
                {
                    PKCount++;
                    if (PKCount > 1) return false;
                }
            }
            return true;
        }
        // ==================== ==================== ==================== ====================
        [ContextMenu("TestConnect")]
        public void TestConnect()
        {
            RefreshConnectionString();
            if (Connect())
                Debug.Log($"连接成功!(已断开)——{connectionString}");
            else
                Debug.LogError($"连接失败!——{connectionString}");

            DisconnectAndClear();
        }

        [ContextMenu("CreateNewDB")]
        public void CreateNewDB()
        {
            string path = Application.streamingAssetsPath.Replace('/', '\\');
            if (!string.IsNullOrEmpty(DBPath))
                path += "\\" + DBPath.Replace('/', '\\');

            Debug.Log($"正在尝试创建{path}" + "\\" + DBName);
            Debug.LogWarning($"Project窗口中的显示可能稍微滞后，可尝试刷新以加快显示。");
            SQLiteHelper.CreateDB(path, DBName);
        }

        [ContextMenu("PrintAllTabelsName")]
        public void PrintAllTablesName()
        {
            RefreshConnectionString();

            List<string> names;
            if (Connect())
                names = GetAllTablesNameToList();
            else
            {
                Debug.LogError("连接数据库失败。");
                DisconnectAndClear();
                return;
            }

            if (names.Count == 0)
                Debug.Log("数据库中没有表。");
            else
            {
                Debug.Log($"数据库中共有{names.Count}张表：");
                foreach (var n in names)
                    Debug.Log(n);
            }
            DisconnectAndClear();
        }
    }
}