TeamAAS-2.5/TeamAAS.Base/TeamAAS.Database/Providers/SqliteDatabase.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Threading.Tasks;
using TeamAAS.Database.Attributes;
using TeamAAS.Database.Interfaces;
using TeamAAS.Database.Models;

namespace TeamAAS.Database.Providers
{
    /// <summary>
    /// SQLite 数据库提供者实现
    /// </summary>
    [DatabaseProvider("sqlite", DisplayName = "SQLite", RequiresServer = false,
        Description = "SQLite 文件型数据库，基于 System.Data.SQLite")]
    public class SqliteDatabase : IDatabase
    {
        private SQLiteConnection _connection;
        private DatabaseConfig _config;
        private readonly object _sync = new object();

        public Guid Id { get; private set; }
        public string Name { get; set; }
        public string ProviderType => "sqlite";
        public bool IsConnected => _connection != null && _connection.State == ConnectionState.Open;

        public string ConnectionString
        {
            get
            {
                if (_config == null) return string.Empty;
                if (!string.IsNullOrWhiteSpace(_config.ConnectionString))
                    return _config.ConnectionString;
                return $"Data Source={GetDbFilePath()};Version=3;";
            }
        }

        private string GetDbFilePath()
        {
            // SQLite: Server 字段存文件路径，或者 DatabaseName 存文件名
            if (!string.IsNullOrWhiteSpace(_config.Server) && _config.Server.Contains("\\"))
                return _config.Server;
            if (!string.IsNullOrWhiteSpace(_config.DatabaseName) && _config.DatabaseName.Contains("\\"))
                return _config.DatabaseName;

            var fileName = string.IsNullOrWhiteSpace(_config.DatabaseName)
                ? _config.Server
                : _config.DatabaseName;

            if (string.IsNullOrWhiteSpace(fileName))
                fileName = "database.db";

            if (!Path.IsPathRooted(fileName))
                fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, fileName);

            return fileName;
        }

        private string GetRawConnectionString()
        {
            if (!string.IsNullOrWhiteSpace(_config?.ConnectionString))
                return _config.ConnectionString;

            if (_config == null)
                throw new InvalidOperationException("数据库未配置");

            var filePath = GetDbFilePath();
            var builder = new SQLiteConnectionStringBuilder
            {
                DataSource = filePath,
                Version = 3,
                FailIfMissing = false,
                JournalMode = SQLiteJournalModeEnum.Wal
            };

            if (_config.ConnectionTimeout > 0)
                builder.DefaultTimeout = _config.ConnectionTimeout;

            if (_config.ExtraParams != null)
            {
                foreach (var kv in _config.ExtraParams)
                {
                    try
                    {
                        var prop = typeof(SQLiteConnectionStringBuilder).GetProperty(kv.Key);
                        if (prop != null && prop.CanWrite)
                            prop.SetValue(builder, Convert.ChangeType(kv.Value, prop.PropertyType));
                    }
                    catch { /* 忽略无效参数 */ }
                }
            }

            return builder.ConnectionString;
        }

        public void Configure(DatabaseConfig config)
        {
            _config = config ?? throw new ArgumentNullException(nameof(config));
            Id = config.Id == Guid.Empty ? Guid.NewGuid() : config.Id;
            Name = config.Name ?? $"SQLite_{Path.GetFileName(GetDbFilePath())}";
        }

        public bool Open()
        {
            lock (_sync)
            {
                try
                {
                    if (_connection == null)
                    {
                        var filePath = GetDbFilePath();
                        var dir = Path.GetDirectoryName(filePath);
                        if (!string.IsNullOrWhiteSpace(dir) && !Directory.Exists(dir))
                            Directory.CreateDirectory(dir);

                        _connection = new SQLiteConnection(GetRawConnectionString());
                    }

                    if (_connection.State == ConnectionState.Open)
                        return true;

                    _connection.Open();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        public Task<bool> OpenAsync()
        {
            return Task.Run(() => Open());
        }

        public void Close()
        {
            lock (_sync)
            {
                if (_connection != null)
                {
                    try { _connection.Close(); } catch { }
                }
            }
        }

        public Task CloseAsync()
        {
            return Task.Run(() => Close());
        }

        public (bool Success, string Message) TestConnection()
        {
            try
            {
                using (var conn = new SQLiteConnection(GetRawConnectionString()))
                {
                    conn.Open();
                    return (true, "连接成功");
                }
            }
            catch (Exception ex)
            {
                return (false, ex.Message);
            }
        }

        public Task<(bool Success, string Message)> TestConnectionAsync()
        {
            return Task.Run(() => TestConnection());
        }

        public int ExecuteNonQuery(string sql, IDictionary<string, object> parameters = null)
        {
            using (var cmd = CreateCommand(sql, parameters))
            {
                EnsureOpen();
                return cmd.ExecuteNonQuery();
            }
        }

        public Task<int> ExecuteNonQueryAsync(string sql, IDictionary<string, object> parameters = null)
        {
            return Task.Run(() => ExecuteNonQuery(sql, parameters));
        }

        public DataTable ExecuteQuery(string sql, IDictionary<string, object> parameters = null)
        {
            using (var cmd = CreateCommand(sql, parameters))
            {
                EnsureOpen();
                var dt = new DataTable();
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
                return dt;
            }
        }

        public Task<DataTable> ExecuteQueryAsync(string sql, IDictionary<string, object> parameters = null)
        {
            return Task.Run(() => ExecuteQuery(sql, parameters));
        }

        public object ExecuteScalar(string sql, IDictionary<string, object> parameters = null)
        {
            using (var cmd = CreateCommand(sql, parameters))
            {
                EnsureOpen();
                return cmd.ExecuteScalar();
            }
        }

        public Task<object> ExecuteScalarAsync(string sql, IDictionary<string, object> parameters = null)
        {
            return Task.Run(() => ExecuteScalar(sql, parameters));
        }

        public bool ExecuteTransaction(IEnumerable<string> sqlCommands)
        {
            if (sqlCommands == null) return true;

            EnsureOpen();
            using (var transaction = _connection.BeginTransaction())
            {
                try
                {
                    foreach (var sql in sqlCommands)
                    {
                        if (string.IsNullOrWhiteSpace(sql)) continue;
                        using (var cmd = _connection.CreateCommand())
                        {
                            cmd.Transaction = transaction;
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    try { transaction.Rollback(); } catch { }
                    return false;
                }
            }
        }

        public Task<bool> ExecuteTransactionAsync(IEnumerable<string> sqlCommands)
        {
            return Task.Run(() => ExecuteTransaction(sqlCommands));
        }

        public List<string> GetTableNames()
        {
            var tables = new List<string>();
            var sql = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name";

            using (var cmd = CreateCommand(sql, null))
            {
                EnsureOpen();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        tables.Add(reader.GetString(0));
                    }
                }
            }
            return tables;
        }

        public Task<List<string>> GetTableNamesAsync()
        {
            return Task.Run(() => GetTableNames());
        }

        public List<ColumnInfo> GetTableSchema(string tableName)
        {
            var columns = new List<ColumnInfo>();
            var sql = $"PRAGMA table_info([{tableName}])";

            using (var cmd = CreateCommand(sql, null))
            {
                EnsureOpen();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // cid, name, type, notnull, dflt_value, pk
                        var colName = reader["name"].ToString();
                        columns.Add(new ColumnInfo
                        {
                            ColumnName = colName,
                            DataType = reader["type"].ToString(),
                            IsNullable = Convert.ToInt32(reader["notnull"]) == 0,
                            IsPrimaryKey = Convert.ToInt32(reader["pk"]) > 0,
                            DefaultValue = reader["dflt_value"] == DBNull.Value ? null : reader["dflt_value"].ToString(),
                            MaxLength = 0,
                            Description = null
                        });
                    }
                }
            }
            return columns;
        }

        public Task<List<ColumnInfo>> GetTableSchemaAsync(string tableName)
        {
            return Task.Run(() => GetTableSchema(tableName));
        }

        #region 私有方法

        private SQLiteCommand CreateCommand(string sql, IDictionary<string, object> parameters)
        {
            var cmd = _connection.CreateCommand();
            cmd.CommandText = sql;

            if (parameters != null)
            {
                foreach (var kv in parameters)
                {
                    var paramName = kv.Key.StartsWith("@") ? kv.Key : "@" + kv.Key;
                    cmd.Parameters.AddWithValue(paramName, kv.Value ?? DBNull.Value);
                }
            }

            return cmd;
        }

        private void EnsureOpen()
        {
            if (_connection == null || _connection.State != ConnectionState.Open)
            {
                if (!Open())
                    throw new InvalidOperationException("数据库连接失败");
            }
        }

        #endregion

        #region IDisposable

        private bool _disposed;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (_disposed) return;

            if (disposing)
            {
                Close();
                _connection?.Dispose();
                _connection = null;
            }

            _disposed = true;
        }

        ~SqliteDatabase()
        {
            Dispose(false);
        }

        #endregion
    }
}