AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/DataPlatform/MdpDbSourceMetadataService.cs

using Admin.NET.Plugin.AiDOP.Entity.DataPlatform;
using Microsoft.Extensions.Logging;

namespace Admin.NET.Plugin.AiDOP.DataPlatform;

[ApiDescriptionSettings(Order = 323, Description = "数据中台数据库元数据")]
[Route("api/DataPlatform")]
[NonUnify]
public class MdpDbSourceMetadataService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _user;
    private readonly MdpSourceScopeFactory _scopes;
    private readonly ILogger<MdpDbSourceMetadataService> _logger;

    public MdpDbSourceMetadataService(
        ISqlSugarClient db,
        UserManager user,
        MdpSourceScopeFactory scopes,
        ILogger<MdpDbSourceMetadataService> logger)
    {
        _db = db;
        _user = user;
        _scopes = scopes;
        _logger = logger;
    }

    [DisplayName("读取数据库表和视图")]
    [HttpGet("sources/{sourceCode}/db/tables")]
    public async Task<object> Tables(string sourceCode)
    {
        await EnsureMenuAsync(MdpDbSyncRules.SourceMenuId);
        var source = await FindDbSourceAsync(sourceCode);
        try
        {
            var scope = await OpenAsync(source);
            var isSqlServer = IsSqlServer(source);
            var sql = isSqlServer
                ? """
              SELECT TABLE_SCHEMA AS schemaName, TABLE_NAME AS name, TABLE_TYPE AS type
              FROM INFORMATION_SCHEMA.TABLES
              WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW')
              ORDER BY TABLE_SCHEMA, TABLE_NAME
              """
                : """
              SELECT TABLE_SCHEMA AS databaseName, TABLE_NAME AS name, TABLE_TYPE AS type
              FROM information_schema.TABLES
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_TYPE IN ('BASE TABLE', 'VIEW')
              ORDER BY TABLE_NAME
              """;
            var rows = await scope.Ado.SqlQueryAsync<dynamic>(sql);
            return new { list = rows };
        }
        catch (Exception ex)
        {
            throw SafeMetadataError(source, ex);
        }
    }

    [DisplayName("读取数据库字段")]
    [HttpGet("sources/{sourceCode}/db/columns")]
    public async Task<object> Columns(string sourceCode, [FromQuery] string table)
    {
        await EnsureMenuAsync(MdpDbSyncRules.SourceMenuId);
        var source = await FindDbSourceAsync(sourceCode);
        var safeTable = MdpDbSyncRules.RequireSourceTable(table);
        try
        {
            var scope = await OpenAsync(source);
            var isSqlServer = IsSqlServer(source);
            var simple = SimpleName(safeTable);
            MdpDbSyncRules.RequireIdentifier(simple, "table");
            var sql = isSqlServer
                ? """
              SELECT c.name AS name, ty.name AS dataType,
                     CASE WHEN c.is_nullable = 1 THEN 1 ELSE 0 END AS nullable,
                     CASE WHEN pk.column_id IS NULL THEN 0 ELSE 1 END AS primaryKey
              FROM sys.columns c
              INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
              LEFT JOIN (
                  SELECT ic.object_id, ic.column_id
                  FROM sys.indexes i
                  INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
                  WHERE i.is_primary_key = 1
              ) pk ON pk.object_id = c.object_id AND pk.column_id = c.column_id
              WHERE c.object_id = OBJECT_ID(@table)
              ORDER BY c.column_id
              """
                : """
              SELECT COLUMN_NAME AS name, DATA_TYPE AS dataType,
                     CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END AS nullable,
                     CASE WHEN COLUMN_KEY = 'PRI' THEN 1 ELSE 0 END AS primaryKey
              FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @table
              ORDER BY ORDINAL_POSITION
              """;
            var rows = await scope.Ado.SqlQueryAsync<dynamic>(sql, new SugarParameter("@table", simple));
            return new { table = safeTable, list = rows };
        }
        catch (Exception ex)
        {
            throw SafeMetadataError(source, ex);
        }
    }

    private async Task<MdpSource> FindDbSourceAsync(string sourceCode)
    {
        var code = (sourceCode ?? "").Trim();
        var source = await _db.Queryable<MdpSource>()
            .Where(x => x.SourceCode == code && x.TenantId == _user.TenantId && x.Status == 1)
            .FirstAsync()
            ?? throw Oops.Oh("数据源不存在或未启用");
        if (!string.Equals(source.SourceType, "DB", StringComparison.OrdinalIgnoreCase))
            throw Oops.Oh("只有数据库来源可以读取表结构");
        if (!MdpDbSyncRules.IsSupportedDbType(source.DbType))
            throw Oops.Oh("数据库类型只支持 SQLSERVER 或 MYSQL");
        return source;
    }

    private async Task<ISqlSugarClient> OpenAsync(MdpSource source)
    {
        var scope = await _scopes.GetScopeAsync(source);
        scope.Ado.CommandTimeOut = 15;
        return scope;
    }

    private Exception SafeMetadataError(MdpSource source, Exception exception)
    {
        _logger.LogWarning(
            exception,
            "读取数据源 {SourceCode} 结构失败，tenant={TenantId}",
            source.SourceCode,
            source.TenantId);
        var error = MdpDbConnectionError.Describe(exception);
        return Oops.Oh(error.Message);
    }

    private async Task EnsureMenuAsync(long menuId)
    {
        if (_user.SuperAdmin || _user.SysAdmin)
            return;
        var roleIds = await _db.Queryable<SysUserRole>()
            .Where(x => x.UserId == _user.UserId)
            .Select(x => x.RoleId)
            .ToListAsync();
        var allowed = roleIds.Count > 0 && await _db.Queryable<SysRoleMenu>()
            .Where(x => roleIds.Contains(x.RoleId) && x.MenuId == menuId)
            .AnyAsync();
        if (!allowed)
            throw Oops.Oh("没有数据源管理权限");
    }

    private static bool IsSqlServer(MdpSource source) =>
        string.Equals(source.DbType, "SQLSERVER", StringComparison.OrdinalIgnoreCase)
        || string.Equals(source.DbType, "MSSQL", StringComparison.OrdinalIgnoreCase);

    private static string SimpleName(string table)
    {
        var text = table.Trim().Trim('[', ']');
        var parts = text.Split('.', StringSplitOptions.RemoveEmptyEntries);
        return parts[^1].Trim('[', ']');
    }
}