1073 lines
40 KiB
C#
1073 lines
40 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Security.Cryptography;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using Erp.SqlQueryManager.Application.Contracts;
|
|
using Erp.SqlQueryManager.Domain.Entities;
|
|
using Erp.SqlQueryManager.Domain.Services;
|
|
using Erp.SqlQueryManager.Domain.Shared;
|
|
using Microsoft.AspNetCore.Authorization;
|
|
using Microsoft.AspNetCore.Http;
|
|
using Volo.Abp.Application.Services;
|
|
using Volo.Abp.Domain.Repositories;
|
|
using Volo.Abp.MultiTenancy;
|
|
|
|
namespace Erp.SqlQueryManager.Application;
|
|
|
|
/// <summary>
|
|
/// Unified service for SQL Object Explorer
|
|
/// Combines all SQL objects into a single endpoint
|
|
/// </summary>
|
|
[Authorize("App.SqlQueryManager")]
|
|
public class SqlObjectManagerAppService : ApplicationService, ISqlObjectManagerAppService
|
|
{
|
|
private readonly IRepository<SqlQuery, Guid> _queryRepository;
|
|
private readonly IRepository<SqlStoredProcedure, Guid> _procedureRepository;
|
|
private readonly IRepository<SqlView, Guid> _viewRepository;
|
|
private readonly IRepository<SqlFunction, Guid> _functionRepository;
|
|
private readonly ISqlExecutorService _sqlExecutorService;
|
|
private readonly ISqlTemplateProvider _templateProvider;
|
|
private readonly ICurrentTenant _currentTenant;
|
|
private readonly IHttpContextAccessor _httpContextAccessor;
|
|
|
|
public SqlObjectManagerAppService(
|
|
IRepository<SqlQuery, Guid> queryRepository,
|
|
IRepository<SqlStoredProcedure, Guid> procedureRepository,
|
|
IRepository<SqlView, Guid> viewRepository,
|
|
IRepository<SqlFunction, Guid> functionRepository,
|
|
ISqlExecutorService sqlExecutorService,
|
|
ISqlTemplateProvider templateProvider,
|
|
ICurrentTenant currentTenant,
|
|
IHttpContextAccessor httpContextAccessor
|
|
)
|
|
{
|
|
_queryRepository = queryRepository;
|
|
_procedureRepository = procedureRepository;
|
|
_viewRepository = viewRepository;
|
|
_functionRepository = functionRepository;
|
|
_sqlExecutorService = sqlExecutorService;
|
|
_templateProvider = templateProvider;
|
|
_currentTenant = currentTenant;
|
|
_httpContextAccessor = httpContextAccessor;
|
|
}
|
|
|
|
private string GetTenantFromHeader()
|
|
{
|
|
return _httpContextAccessor.HttpContext?
|
|
.Request?
|
|
.Headers["__tenant"]
|
|
.FirstOrDefault();
|
|
}
|
|
|
|
private void ValidateTenantAccess()
|
|
{
|
|
var headerTenant = GetTenantFromHeader();
|
|
var currentTenantName = _currentTenant.Name;
|
|
|
|
if (_currentTenant.IsAvailable)
|
|
{
|
|
if (headerTenant != currentTenantName)
|
|
{
|
|
throw new Volo.Abp.UserFriendlyException($"Tenant mismatch. Header tenant '{headerTenant}' does not match current tenant '{currentTenantName}'.");
|
|
}
|
|
}
|
|
}
|
|
|
|
public async Task<SqlObjectExplorerDto> GetAllObjectsAsync(string dataSourceCode)
|
|
{
|
|
ValidateTenantAccess();
|
|
var result = new SqlObjectExplorerDto();
|
|
|
|
// Get all queries for this data source
|
|
var queries = await _queryRepository.GetListAsync();
|
|
result.Queries = queries
|
|
.Where(q => q.DataSourceCode == dataSourceCode)
|
|
.Select(q => new SqlQueryDto
|
|
{
|
|
Id = q.Id,
|
|
Code = q.Code,
|
|
Name = q.Name,
|
|
Description = q.Description,
|
|
QueryText = q.QueryText,
|
|
DataSourceCode = q.DataSourceCode,
|
|
Status = q.Status,
|
|
Category = q.Category,
|
|
Tags = q.Tags,
|
|
IsModifyingData = q.IsModifyingData,
|
|
Parameters = q.Parameters,
|
|
ExecutionCount = q.ExecutionCount,
|
|
LastExecutedAt = q.LastExecutedAt
|
|
})
|
|
.ToList();
|
|
|
|
// Get all stored procedures for this data source (custom + native merged)
|
|
result.StoredProcedures = await GetMergedStoredProceduresAsync(dataSourceCode);
|
|
|
|
// Get all views for this data source (custom + native merged)
|
|
result.Views = await GetMergedViewsAsync(dataSourceCode);
|
|
|
|
// Get all functions for this data source (custom + native merged)
|
|
result.Functions = await GetMergedFunctionsAsync(dataSourceCode);
|
|
|
|
// Get all database tables
|
|
result.Tables = await GetTablesAsync(dataSourceCode);
|
|
|
|
// Get all templates
|
|
result.Templates = _templateProvider.GetAvailableQueryTemplates()
|
|
.Select(t => new SqlTemplateDto
|
|
{
|
|
Type = t.Type,
|
|
Name = t.Name,
|
|
Description = t.Description,
|
|
Template = _templateProvider.GetQueryTemplate(t.Type)
|
|
})
|
|
.ToList();
|
|
|
|
return result;
|
|
}
|
|
|
|
private async Task<List<DatabaseTableDto>> GetTablesAsync(string dataSourceCode)
|
|
{
|
|
var query = @"
|
|
SELECT
|
|
SCHEMA_NAME(t.schema_id) AS SchemaName,
|
|
t.name AS TableName
|
|
FROM
|
|
sys.tables t
|
|
WHERE
|
|
t.is_ms_shipped = 0
|
|
ORDER BY
|
|
SCHEMA_NAME(t.schema_id), t.name";
|
|
|
|
var result = await _sqlExecutorService.ExecuteQueryAsync(query, dataSourceCode);
|
|
|
|
var tables = new List<DatabaseTableDto>();
|
|
if (result.Success && result.Data != null)
|
|
{
|
|
foreach (var row in result.Data)
|
|
{
|
|
var dict = row as System.Collections.Generic.IDictionary<string, object>;
|
|
if (dict != null)
|
|
{
|
|
tables.Add(new DatabaseTableDto
|
|
{
|
|
SchemaName = dict["SchemaName"]?.ToString() ?? "dbo",
|
|
TableName = dict["TableName"]?.ToString() ?? ""
|
|
});
|
|
}
|
|
}
|
|
}
|
|
|
|
return tables;
|
|
}
|
|
|
|
private async Task<List<SqlStoredProcedureDto>> GetMergedStoredProceduresAsync(string dataSourceCode)
|
|
{
|
|
// Get custom stored procedures from database
|
|
var customProcedures = await _procedureRepository.GetListAsync();
|
|
var customList = customProcedures
|
|
.Where(p => p.DataSourceCode == dataSourceCode)
|
|
.Select(p => new SqlStoredProcedureDto
|
|
{
|
|
Id = p.Id,
|
|
ProcedureName = p.ProcedureName,
|
|
SchemaName = p.SchemaName,
|
|
DisplayName = p.DisplayName,
|
|
Description = p.Description,
|
|
ProcedureBody = p.ProcedureBody,
|
|
DataSourceCode = p.DataSourceCode,
|
|
Category = p.Category,
|
|
Parameters = p.Parameters,
|
|
IsDeployed = p.IsDeployed,
|
|
LastDeployedAt = p.LastDeployedAt,
|
|
IsCustom = true
|
|
})
|
|
.ToList();
|
|
|
|
// Get native stored procedures from SQL Server
|
|
var nativeQuery = @"
|
|
SELECT
|
|
SCHEMA_NAME(p.schema_id) AS SchemaName,
|
|
p.name AS ProcedureName,
|
|
p.create_date AS CreatedDate,
|
|
p.modify_date AS ModifiedDate
|
|
FROM
|
|
sys.procedures p
|
|
WHERE
|
|
p.is_ms_shipped = 0
|
|
ORDER BY
|
|
SCHEMA_NAME(p.schema_id), p.name";
|
|
|
|
var result = await _sqlExecutorService.ExecuteQueryAsync(nativeQuery, dataSourceCode);
|
|
var nativeList = new List<SqlStoredProcedureDto>();
|
|
|
|
if (result.Success && result.Data != null)
|
|
{
|
|
foreach (var row in result.Data)
|
|
{
|
|
var dict = row as System.Collections.Generic.IDictionary<string, object>;
|
|
if (dict != null)
|
|
{
|
|
var schemaName = dict["SchemaName"]?.ToString() ?? "dbo";
|
|
var procName = dict["ProcedureName"]?.ToString() ?? "";
|
|
|
|
// Skip if already exists in custom list
|
|
if (!customList.Any(c => c.SchemaName == schemaName && c.ProcedureName == procName))
|
|
{
|
|
// Generate deterministic GUID from schema and name to ensure uniqueness
|
|
var uniqueId = GenerateDeterministicGuid($"SP_{dataSourceCode}_{schemaName}_{procName}");
|
|
|
|
nativeList.Add(new SqlStoredProcedureDto
|
|
{
|
|
Id = uniqueId,
|
|
SchemaName = schemaName,
|
|
ProcedureName = procName,
|
|
DisplayName = $"[{schemaName}].[{procName}]",
|
|
DataSourceCode = dataSourceCode,
|
|
IsCustom = false,
|
|
IsDeployed = true // Native objects are already deployed
|
|
});
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// Merge and return
|
|
return customList.Concat(nativeList).ToList();
|
|
}
|
|
|
|
private async Task<List<SqlViewDto>> GetMergedViewsAsync(string dataSourceCode)
|
|
{
|
|
// Get custom views from database
|
|
var customViews = await _viewRepository.GetListAsync();
|
|
var customList = customViews
|
|
.Where(v => v.DataSourceCode == dataSourceCode)
|
|
.Select(v => new SqlViewDto
|
|
{
|
|
Id = v.Id,
|
|
ViewName = v.ViewName,
|
|
SchemaName = v.SchemaName,
|
|
DisplayName = v.DisplayName,
|
|
Description = v.Description,
|
|
ViewDefinition = v.ViewDefinition,
|
|
DataSourceCode = v.DataSourceCode,
|
|
Category = v.Category,
|
|
WithSchemaBinding = v.WithSchemaBinding,
|
|
IsDeployed = v.IsDeployed,
|
|
LastDeployedAt = v.LastDeployedAt,
|
|
IsCustom = true
|
|
})
|
|
.ToList();
|
|
|
|
// Get native views from SQL Server
|
|
var nativeQuery = @"
|
|
SELECT
|
|
SCHEMA_NAME(v.schema_id) AS SchemaName,
|
|
v.name AS ViewName,
|
|
v.create_date AS CreatedDate,
|
|
v.modify_date AS ModifiedDate
|
|
FROM
|
|
sys.views v
|
|
WHERE
|
|
v.is_ms_shipped = 0
|
|
ORDER BY
|
|
SCHEMA_NAME(v.schema_id), v.name";
|
|
|
|
var result = await _sqlExecutorService.ExecuteQueryAsync(nativeQuery, dataSourceCode);
|
|
var nativeList = new List<SqlViewDto>();
|
|
|
|
if (result.Success && result.Data != null)
|
|
{
|
|
foreach (var row in result.Data)
|
|
{
|
|
var dict = row as System.Collections.Generic.IDictionary<string, object>;
|
|
if (dict != null)
|
|
{
|
|
var schemaName = dict["SchemaName"]?.ToString() ?? "dbo";
|
|
var viewName = dict["ViewName"]?.ToString() ?? "";
|
|
|
|
// Skip if already exists in custom list
|
|
if (!customList.Any(c => c.SchemaName == schemaName && c.ViewName == viewName))
|
|
{
|
|
// Generate deterministic GUID from schema and name to ensure uniqueness
|
|
var uniqueId = GenerateDeterministicGuid($"VIEW_{dataSourceCode}_{schemaName}_{viewName}");
|
|
|
|
nativeList.Add(new SqlViewDto
|
|
{
|
|
Id = uniqueId,
|
|
SchemaName = schemaName,
|
|
ViewName = viewName,
|
|
DisplayName = $"[{schemaName}].[{viewName}]",
|
|
DataSourceCode = dataSourceCode,
|
|
IsCustom = false,
|
|
IsDeployed = true
|
|
});
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return customList.Concat(nativeList).ToList();
|
|
}
|
|
|
|
private async Task<List<SqlFunctionDto>> GetMergedFunctionsAsync(string dataSourceCode)
|
|
{
|
|
// Get custom functions from database
|
|
var customFunctions = await _functionRepository.GetListAsync();
|
|
var customList = customFunctions
|
|
.Where(f => f.DataSourceCode == dataSourceCode)
|
|
.Select(f => new SqlFunctionDto
|
|
{
|
|
Id = f.Id,
|
|
FunctionName = f.FunctionName,
|
|
SchemaName = f.SchemaName,
|
|
DisplayName = f.DisplayName,
|
|
Description = f.Description,
|
|
FunctionType = f.FunctionType,
|
|
FunctionBody = f.FunctionBody,
|
|
ReturnType = f.ReturnType,
|
|
DataSourceCode = f.DataSourceCode,
|
|
Category = f.Category,
|
|
Parameters = f.Parameters,
|
|
IsDeployed = f.IsDeployed,
|
|
LastDeployedAt = f.LastDeployedAt,
|
|
IsCustom = true
|
|
})
|
|
.ToList();
|
|
|
|
// Get native functions from SQL Server
|
|
var nativeQuery = @"
|
|
SELECT
|
|
SCHEMA_NAME(o.schema_id) AS SchemaName,
|
|
o.name AS FunctionName,
|
|
o.create_date AS CreatedDate,
|
|
o.modify_date AS ModifiedDate,
|
|
CASE o.type
|
|
WHEN 'FN' THEN 'Scalar'
|
|
WHEN 'IF' THEN 'InlineTableValued'
|
|
WHEN 'TF' THEN 'TableValued'
|
|
ELSE 'Unknown'
|
|
END AS FunctionType
|
|
FROM
|
|
sys.objects o
|
|
WHERE
|
|
o.type IN ('FN', 'IF', 'TF')
|
|
AND o.is_ms_shipped = 0
|
|
ORDER BY
|
|
SCHEMA_NAME(o.schema_id), o.name";
|
|
|
|
var result = await _sqlExecutorService.ExecuteQueryAsync(nativeQuery, dataSourceCode);
|
|
var nativeList = new List<SqlFunctionDto>();
|
|
|
|
if (result.Success && result.Data != null)
|
|
{
|
|
foreach (var row in result.Data)
|
|
{
|
|
var dict = row as System.Collections.Generic.IDictionary<string, object>;
|
|
if (dict != null)
|
|
{
|
|
var schemaName = dict["SchemaName"]?.ToString() ?? "dbo";
|
|
var funcName = dict["FunctionName"]?.ToString() ?? "";
|
|
|
|
// Skip if already exists in custom list
|
|
if (!customList.Any(c => c.SchemaName == schemaName && c.FunctionName == funcName))
|
|
{
|
|
var funcTypeStr = dict["FunctionType"]?.ToString() ?? "Scalar";
|
|
var funcType = funcTypeStr == "Scalar" ? SqlFunctionType.ScalarFunction :
|
|
funcTypeStr == "InlineTableValued" ? SqlFunctionType.InlineTableValuedFunction :
|
|
SqlFunctionType.TableValuedFunction;
|
|
|
|
// Generate deterministic GUID from schema and name to ensure uniqueness
|
|
var uniqueId = GenerateDeterministicGuid($"FUNC_{dataSourceCode}_{schemaName}_{funcName}");
|
|
|
|
nativeList.Add(new SqlFunctionDto
|
|
{
|
|
Id = uniqueId,
|
|
SchemaName = schemaName,
|
|
FunctionName = funcName,
|
|
DisplayName = $"[{schemaName}].[{funcName}]",
|
|
DataSourceCode = dataSourceCode,
|
|
FunctionType = funcType,
|
|
IsCustom = false,
|
|
IsDeployed = true
|
|
});
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return customList.Concat(nativeList).ToList();
|
|
}
|
|
|
|
#region Query Operations
|
|
|
|
public async Task<SqlQueryDto> CreateQueryAsync(CreateSqlQueryDto input)
|
|
{
|
|
ValidateTenantAccess();
|
|
|
|
var query = ObjectMapper.Map<CreateSqlQueryDto, SqlQuery>(input);
|
|
query.Status = SqlQueryStatus.Draft;
|
|
|
|
var created = await _queryRepository.InsertAsync(query, autoSave: true);
|
|
return ObjectMapper.Map<SqlQuery, SqlQueryDto>(created);
|
|
}
|
|
|
|
public async Task<SqlQueryDto> UpdateQueryAsync(Guid id, UpdateSqlQueryDto input)
|
|
{
|
|
ValidateTenantAccess();
|
|
var query = await _queryRepository.GetAsync(id);
|
|
|
|
query.Name = input.Name;
|
|
query.Description = input.Description;
|
|
query.QueryText = input.QueryText;
|
|
query.Category = input.Category;
|
|
query.Tags = input.Tags;
|
|
|
|
var updated = await _queryRepository.UpdateAsync(query, autoSave: true);
|
|
return ObjectMapper.Map<SqlQuery, SqlQueryDto>(updated);
|
|
}
|
|
|
|
public async Task DeleteQueryAsync(Guid id)
|
|
{
|
|
ValidateTenantAccess();
|
|
await _queryRepository.DeleteAsync(id);
|
|
}
|
|
|
|
public async Task<SqlQueryExecutionResultDto> ExecuteQueryAsync(ExecuteSqlQueryDto input)
|
|
{
|
|
ValidateTenantAccess();
|
|
var sqlText = input.QueryText.Trim();
|
|
var sqlUpper = sqlText.ToUpperInvariant();
|
|
|
|
// Check if this is a DDL command (CREATE/ALTER/DROP for VIEW/PROCEDURE/FUNCTION)
|
|
bool isDDLCommand =
|
|
sqlUpper.Contains("CREATE VIEW") || sqlUpper.Contains("ALTER VIEW") ||
|
|
sqlUpper.Contains("CREATE PROCEDURE") || sqlUpper.Contains("CREATE PROC") ||
|
|
sqlUpper.Contains("ALTER PROCEDURE") || sqlUpper.Contains("ALTER PROC") ||
|
|
sqlUpper.Contains("CREATE FUNCTION") || sqlUpper.Contains("ALTER FUNCTION") ||
|
|
sqlUpper.Contains("DROP VIEW") || sqlUpper.Contains("DROP PROCEDURE") ||
|
|
sqlUpper.Contains("DROP PROC") || sqlUpper.Contains("DROP FUNCTION");
|
|
|
|
if (isDDLCommand)
|
|
{
|
|
// For DDL commands, only validate syntax without executing
|
|
try
|
|
{
|
|
// Try to parse/validate the SQL using SET PARSEONLY
|
|
var validationSql = $"SET PARSEONLY ON;\n{sqlText}\nSET PARSEONLY OFF;";
|
|
await _sqlExecutorService.ExecuteNonQueryAsync(validationSql, input.DataSourceCode);
|
|
|
|
return new SqlQueryExecutionResultDto
|
|
{
|
|
Success = true,
|
|
Message = "SQL syntax is valid. Use Save button to save and Deploy button to create in SQL Server.",
|
|
Data = new List<object>(),
|
|
RowsAffected = 0,
|
|
ExecutionTimeMs = 0
|
|
};
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new SqlQueryExecutionResultDto
|
|
{
|
|
Success = false,
|
|
Message = $"SQL syntax error: {ex.Message}",
|
|
Data = new List<object>(),
|
|
RowsAffected = 0,
|
|
ExecutionTimeMs = 0
|
|
};
|
|
}
|
|
}
|
|
|
|
// For DML commands (SELECT, INSERT, UPDATE, DELETE), execute normally
|
|
var result = await _sqlExecutorService.ExecuteQueryAsync(input.QueryText, input.DataSourceCode);
|
|
return MapExecutionResult(result);
|
|
}
|
|
|
|
public async Task<SqlQueryExecutionResultDto> ExecuteSavedQueryAsync(Guid id)
|
|
{
|
|
ValidateTenantAccess();
|
|
var query = await _queryRepository.GetAsync(id);
|
|
var result = await _sqlExecutorService.ExecuteQueryAsync(query.QueryText, query.DataSourceCode);
|
|
|
|
// Update execution statistics
|
|
query.ExecutionCount++;
|
|
query.LastExecutedAt = DateTime.UtcNow;
|
|
await _queryRepository.UpdateAsync(query, autoSave: true);
|
|
|
|
return MapExecutionResult(result);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Stored Procedure Operations
|
|
|
|
public async Task<SqlStoredProcedureDto> UpdateStoredProcedureAsync(Guid id, UpdateSqlStoredProcedureDto input)
|
|
{
|
|
ValidateTenantAccess();
|
|
var procedure = await _procedureRepository.GetAsync(id);
|
|
|
|
procedure.DisplayName = input.DisplayName;
|
|
procedure.Description = input.Description;
|
|
procedure.ProcedureBody = input.ProcedureBody;
|
|
procedure.Category = input.Category;
|
|
procedure.IsDeployed = false;
|
|
procedure.LastDeployedAt = null;
|
|
|
|
var updated = await _procedureRepository.UpdateAsync(procedure, autoSave: true);
|
|
return ObjectMapper.Map<SqlStoredProcedure, SqlStoredProcedureDto>(updated);
|
|
}
|
|
|
|
public async Task DeleteStoredProcedureAsync(Guid id)
|
|
{
|
|
ValidateTenantAccess();
|
|
var procedure = await _procedureRepository.GetAsync(id);
|
|
|
|
// Drop stored procedure from SQL Server (always try, regardless of IsDeployed flag)
|
|
try
|
|
{
|
|
var dropSql = $"IF OBJECT_ID('[{procedure.SchemaName}].[{procedure.ProcedureName}]', 'P') IS NOT NULL DROP PROCEDURE [{procedure.SchemaName}].[{procedure.ProcedureName}]";
|
|
await _sqlExecutorService.ExecuteNonQueryAsync(dropSql, procedure.DataSourceCode);
|
|
}
|
|
catch
|
|
{
|
|
// Ignore errors if object doesn't exist in database
|
|
}
|
|
|
|
await _procedureRepository.DeleteAsync(id);
|
|
}
|
|
|
|
public async Task<SqlQueryExecutionResultDto> DeployStoredProcedureAsync(DeployStoredProcedureDto input)
|
|
{
|
|
ValidateTenantAccess();
|
|
var procedure = await _procedureRepository.GetAsync(input.Id);
|
|
|
|
try
|
|
{
|
|
// Önce DROP işlemi yap (varsa)
|
|
var dropSql = $"IF OBJECT_ID('[{procedure.SchemaName}].[{procedure.ProcedureName}]', 'P') IS NOT NULL DROP PROCEDURE [{procedure.SchemaName}].[{procedure.ProcedureName}]";
|
|
await _sqlExecutorService.ExecuteNonQueryAsync(dropSql, procedure.DataSourceCode);
|
|
|
|
// Sonra CREATE işlemi yap
|
|
var result = await _sqlExecutorService.DeployStoredProcedureAsync(
|
|
procedure.ProcedureBody,
|
|
procedure.DataSourceCode
|
|
);
|
|
|
|
if (result.Success)
|
|
{
|
|
procedure.IsDeployed = true;
|
|
procedure.LastDeployedAt = DateTime.UtcNow;
|
|
await _procedureRepository.UpdateAsync(procedure, autoSave: true);
|
|
}
|
|
|
|
return MapExecutionResult(result);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new SqlQueryExecutionResultDto
|
|
{
|
|
Success = false,
|
|
Message = $"Deploy failed: {ex.Message}",
|
|
Data = new List<object>(),
|
|
RowsAffected = 0,
|
|
ExecutionTimeMs = 0
|
|
};
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region View Operations
|
|
|
|
public async Task<SqlViewDto> UpdateViewAsync(Guid id, UpdateSqlViewDto input)
|
|
{
|
|
ValidateTenantAccess();
|
|
var view = await _viewRepository.GetAsync(id);
|
|
|
|
view.DisplayName = input.DisplayName;
|
|
view.Description = input.Description;
|
|
view.ViewDefinition = input.ViewDefinition;
|
|
view.Category = input.Category;
|
|
view.IsDeployed = false;
|
|
view.LastDeployedAt = null;
|
|
|
|
var updated = await _viewRepository.UpdateAsync(view, autoSave: true);
|
|
return ObjectMapper.Map<SqlView, SqlViewDto>(updated);
|
|
}
|
|
|
|
public async Task DeleteViewAsync(Guid id)
|
|
{
|
|
ValidateTenantAccess();
|
|
var view = await _viewRepository.GetAsync(id);
|
|
|
|
// Drop view from SQL Server (always try, regardless of IsDeployed flag)
|
|
try
|
|
{
|
|
var dropSql = $"IF OBJECT_ID('[{view.SchemaName}].[{view.ViewName}]', 'V') IS NOT NULL DROP VIEW [{view.SchemaName}].[{view.ViewName}]";
|
|
await _sqlExecutorService.ExecuteNonQueryAsync(dropSql, view.DataSourceCode);
|
|
}
|
|
catch
|
|
{
|
|
// Ignore errors if object doesn't exist in database
|
|
}
|
|
|
|
await _viewRepository.DeleteAsync(id);
|
|
}
|
|
|
|
public async Task<SqlQueryExecutionResultDto> DeployViewAsync(DeployViewDto input)
|
|
{
|
|
ValidateTenantAccess();
|
|
var view = await _viewRepository.GetAsync(input.Id);
|
|
|
|
try
|
|
{
|
|
// Önce DROP işlemi yap (varsa)
|
|
var dropSql = $"IF OBJECT_ID('[{view.SchemaName}].[{view.ViewName}]', 'V') IS NOT NULL DROP VIEW [{view.SchemaName}].[{view.ViewName}]";
|
|
await _sqlExecutorService.ExecuteNonQueryAsync(dropSql, view.DataSourceCode);
|
|
|
|
// Sonra CREATE işlemi yap
|
|
var result = await _sqlExecutorService.DeployViewAsync(
|
|
view.ViewDefinition,
|
|
view.DataSourceCode
|
|
);
|
|
|
|
if (result.Success)
|
|
{
|
|
view.IsDeployed = true;
|
|
view.LastDeployedAt = DateTime.UtcNow;
|
|
await _viewRepository.UpdateAsync(view, autoSave: true);
|
|
}
|
|
|
|
return MapExecutionResult(result);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new SqlQueryExecutionResultDto
|
|
{
|
|
Success = false,
|
|
Message = $"Deploy failed: {ex.Message}",
|
|
Data = new List<object>(),
|
|
RowsAffected = 0,
|
|
ExecutionTimeMs = 0
|
|
};
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Function Operations
|
|
|
|
public async Task<SqlFunctionDto> UpdateFunctionAsync(Guid id, UpdateSqlFunctionDto input)
|
|
{
|
|
ValidateTenantAccess();
|
|
var function = await _functionRepository.GetAsync(id);
|
|
|
|
function.DisplayName = input.DisplayName;
|
|
function.Description = input.Description;
|
|
function.FunctionBody = input.FunctionBody;
|
|
function.Category = input.Category;
|
|
function.IsDeployed = false;
|
|
function.LastDeployedAt = null;
|
|
|
|
var updated = await _functionRepository.UpdateAsync(function, autoSave: true);
|
|
return ObjectMapper.Map<SqlFunction, SqlFunctionDto>(updated);
|
|
}
|
|
|
|
public async Task DeleteFunctionAsync(Guid id)
|
|
{
|
|
ValidateTenantAccess();
|
|
var function = await _functionRepository.GetAsync(id);
|
|
|
|
// Drop function from SQL Server (always try, regardless of IsDeployed flag)
|
|
try
|
|
{
|
|
var dropSql = $"IF OBJECT_ID('[{function.SchemaName}].[{function.FunctionName}]', 'FN') IS NOT NULL DROP FUNCTION [{function.SchemaName}].[{function.FunctionName}]";
|
|
await _sqlExecutorService.ExecuteNonQueryAsync(dropSql, function.DataSourceCode);
|
|
}
|
|
catch
|
|
{
|
|
// Ignore errors if object doesn't exist in database
|
|
}
|
|
|
|
await _functionRepository.DeleteAsync(id);
|
|
}
|
|
|
|
public async Task<SqlQueryExecutionResultDto> DeployFunctionAsync(DeployFunctionDto input)
|
|
{
|
|
ValidateTenantAccess();
|
|
var function = await _functionRepository.GetAsync(input.Id);
|
|
|
|
try
|
|
{
|
|
// Önce DROP işlemi yap (varsa)
|
|
var dropSql = $"IF OBJECT_ID('[{function.SchemaName}].[{function.FunctionName}]', 'FN') IS NOT NULL DROP FUNCTION [{function.SchemaName}].[{function.FunctionName}]";
|
|
await _sqlExecutorService.ExecuteNonQueryAsync(dropSql, function.DataSourceCode);
|
|
|
|
// Sonra CREATE işlemi yap
|
|
var result = await _sqlExecutorService.DeployFunctionAsync(
|
|
function.FunctionBody,
|
|
function.DataSourceCode
|
|
);
|
|
|
|
if (result.Success)
|
|
{
|
|
function.IsDeployed = true;
|
|
function.LastDeployedAt = DateTime.UtcNow;
|
|
await _functionRepository.UpdateAsync(function, autoSave: true);
|
|
}
|
|
|
|
return MapExecutionResult(result);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new SqlQueryExecutionResultDto
|
|
{
|
|
Success = false,
|
|
Message = $"Deploy failed: {ex.Message}",
|
|
Data = new List<object>(),
|
|
RowsAffected = 0,
|
|
ExecutionTimeMs = 0
|
|
};
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Database Metadata Operations
|
|
|
|
public async Task<string> GetNativeObjectDefinitionAsync(string dataSourceCode, string schemaName, string objectName)
|
|
{
|
|
ValidateTenantAccess();
|
|
var query = @"
|
|
SELECT OBJECT_DEFINITION(OBJECT_ID(@ObjectName)) AS Definition";
|
|
|
|
var fullObjectName = $"[{schemaName}].[{objectName}]";
|
|
var result = await _sqlExecutorService.ExecuteQueryAsync(
|
|
query.Replace("@ObjectName", $"'{fullObjectName}'"),
|
|
dataSourceCode
|
|
);
|
|
|
|
if (result.Success && result.Data != null)
|
|
{
|
|
var dataList = result.Data.ToList();
|
|
if (dataList.Count > 0)
|
|
{
|
|
var row = dataList[0] as System.Collections.Generic.IDictionary<string, object>;
|
|
if (row != null && row.ContainsKey("Definition"))
|
|
{
|
|
return row["Definition"]?.ToString() ?? string.Empty;
|
|
}
|
|
}
|
|
}
|
|
|
|
return string.Empty;
|
|
}
|
|
|
|
public async Task<List<DatabaseColumnDto>> GetTableColumnsAsync(string dataSourceCode, string schemaName, string tableName)
|
|
{
|
|
ValidateTenantAccess();
|
|
var query = $@"
|
|
SELECT
|
|
c.name AS ColumnName,
|
|
TYPE_NAME(c.user_type_id) AS DataType,
|
|
c.is_nullable AS IsNullable,
|
|
c.max_length AS MaxLength
|
|
FROM
|
|
sys.columns c
|
|
INNER JOIN sys.tables t ON c.object_id = t.object_id
|
|
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
|
|
WHERE
|
|
s.name = '{schemaName}'
|
|
AND t.name = '{tableName}'
|
|
ORDER BY
|
|
c.column_id";
|
|
|
|
var result = await _sqlExecutorService.ExecuteQueryAsync(query, dataSourceCode);
|
|
|
|
var columns = new List<DatabaseColumnDto>();
|
|
if (result.Success && result.Data != null)
|
|
{
|
|
foreach (var row in result.Data)
|
|
{
|
|
var dict = row as System.Collections.Generic.IDictionary<string, object>;
|
|
if (dict != null)
|
|
{
|
|
columns.Add(new DatabaseColumnDto
|
|
{
|
|
ColumnName = dict["ColumnName"]?.ToString() ?? "",
|
|
DataType = dict["DataType"]?.ToString() ?? "",
|
|
IsNullable = dict["IsNullable"] is bool b && b,
|
|
MaxLength = dict["MaxLength"] != null ? int.Parse(dict["MaxLength"].ToString()) : null
|
|
});
|
|
}
|
|
}
|
|
}
|
|
|
|
return columns;
|
|
}
|
|
|
|
#endregion
|
|
|
|
public async Task<SmartSaveResultDto> SmartSaveAsync(SmartSaveInputDto input)
|
|
{
|
|
ValidateTenantAccess();
|
|
var result = new SmartSaveResultDto();
|
|
var sqlText = input.SqlText.Trim();
|
|
var sqlUpper = sqlText.ToUpperInvariant();
|
|
|
|
try
|
|
{
|
|
// Analyze SQL to determine object type
|
|
if (sqlUpper.Contains("CREATE VIEW") || sqlUpper.Contains("ALTER VIEW"))
|
|
{
|
|
// Extract view name
|
|
var viewName = input.Name;
|
|
var schemaName = "dbo";
|
|
var displayName = input.Name;
|
|
|
|
// Check if view already exists
|
|
var existingView = (await _viewRepository.GetListAsync())
|
|
.FirstOrDefault(v => v.ViewName == viewName && v.DataSourceCode == input.DataSourceCode);
|
|
|
|
SqlView view;
|
|
if (existingView != null)
|
|
{
|
|
// Update existing view
|
|
existingView.DisplayName = displayName;
|
|
existingView.ViewDefinition = sqlText;
|
|
existingView.IsDeployed = false;
|
|
existingView.LastDeployedAt = null;
|
|
|
|
if (!string.IsNullOrEmpty(input.Description))
|
|
{
|
|
existingView.Description = input.Description;
|
|
}
|
|
view = await _viewRepository.UpdateAsync(existingView, autoSave: true);
|
|
result.Message = $"View '{viewName}' updated successfully. Use Deploy button to deploy changes to SQL Server.";
|
|
}
|
|
else
|
|
{
|
|
// Create new view
|
|
view = new SqlView(
|
|
GuidGenerator.Create(),
|
|
viewName, // ViewName from SQL
|
|
schemaName,
|
|
displayName, // DisplayName from user input
|
|
sqlText,
|
|
input.DataSourceCode
|
|
);
|
|
|
|
if (!string.IsNullOrEmpty(input.Description))
|
|
{
|
|
view.Description = input.Description;
|
|
}
|
|
|
|
await _viewRepository.InsertAsync(view, autoSave: true);
|
|
result.Message = $"View '{viewName}' saved successfully. Use Deploy button to deploy to SQL Server.";
|
|
}
|
|
|
|
result.ObjectType = "View";
|
|
result.ObjectId = view.Id;
|
|
result.Deployed = view.IsDeployed;
|
|
}
|
|
else if (sqlUpper.Contains("CREATE PROCEDURE") || sqlUpper.Contains("CREATE PROC") ||
|
|
sqlUpper.Contains("ALTER PROCEDURE") || sqlUpper.Contains("ALTER PROC"))
|
|
{
|
|
// Extract procedure name
|
|
var procName = input.Name;
|
|
var schemaName = "dbo";
|
|
var displayName = input.Name;
|
|
|
|
// Check if procedure already exists
|
|
var existingProcedure = (await _procedureRepository.GetListAsync())
|
|
.FirstOrDefault(p => p.ProcedureName == procName && p.DataSourceCode == input.DataSourceCode);
|
|
|
|
SqlStoredProcedure procedure;
|
|
if (existingProcedure != null)
|
|
{
|
|
// Update existing procedure
|
|
existingProcedure.DisplayName = displayName;
|
|
existingProcedure.ProcedureBody = sqlText;
|
|
existingProcedure.IsDeployed = false;
|
|
existingProcedure.LastDeployedAt = null;
|
|
|
|
if (!string.IsNullOrEmpty(input.Description))
|
|
{
|
|
existingProcedure.Description = input.Description;
|
|
}
|
|
procedure = await _procedureRepository.UpdateAsync(existingProcedure, autoSave: true);
|
|
result.Message = $"Stored Procedure '{procName}' updated successfully. Use Deploy button to deploy changes to SQL Server.";
|
|
}
|
|
else
|
|
{
|
|
// Create new procedure
|
|
procedure = new SqlStoredProcedure(
|
|
GuidGenerator.Create(),
|
|
procName, // ProcedureName from SQL
|
|
schemaName,
|
|
displayName, // DisplayName from user input
|
|
sqlText,
|
|
input.DataSourceCode
|
|
);
|
|
|
|
if (!string.IsNullOrEmpty(input.Description))
|
|
{
|
|
procedure.Description = input.Description;
|
|
}
|
|
|
|
await _procedureRepository.InsertAsync(procedure, autoSave: true);
|
|
result.Message = $"Stored Procedure '{procName}' saved successfully. Use Deploy button to deploy to SQL Server.";
|
|
}
|
|
|
|
result.ObjectType = "StoredProcedure";
|
|
result.ObjectId = procedure.Id;
|
|
result.Deployed = procedure.IsDeployed;
|
|
}
|
|
else if (sqlUpper.Contains("CREATE FUNCTION") || sqlUpper.Contains("ALTER FUNCTION"))
|
|
{
|
|
// Extract function name
|
|
var funcName = input.Name;
|
|
var schemaName = "dbo";
|
|
var returnType = "NVARCHAR(MAX)"; // Default, can be extracted from SQL
|
|
var displayName = input.Name;
|
|
|
|
// Check if function already exists
|
|
var existingFunction = (await _functionRepository.GetListAsync())
|
|
.FirstOrDefault(f => f.FunctionName == funcName && f.DataSourceCode == input.DataSourceCode);
|
|
|
|
SqlFunction function;
|
|
if (existingFunction != null)
|
|
{
|
|
// Update existing function
|
|
existingFunction.DisplayName = displayName;
|
|
existingFunction.FunctionBody = sqlText;
|
|
existingFunction.IsDeployed = false;
|
|
existingFunction.LastDeployedAt = null;
|
|
|
|
if (!string.IsNullOrEmpty(input.Description))
|
|
{
|
|
existingFunction.Description = input.Description;
|
|
}
|
|
function = await _functionRepository.UpdateAsync(existingFunction, autoSave: true);
|
|
result.Message = $"Function '{funcName}' updated successfully. Use Deploy button to deploy changes to SQL Server.";
|
|
}
|
|
else
|
|
{
|
|
// Create new function
|
|
function = new SqlFunction(
|
|
GuidGenerator.Create(),
|
|
funcName, // FunctionName from SQL
|
|
schemaName,
|
|
displayName, // DisplayName from user input
|
|
SqlFunctionType.ScalarFunction,
|
|
sqlText,
|
|
returnType,
|
|
input.DataSourceCode
|
|
);
|
|
|
|
if (!string.IsNullOrEmpty(input.Description))
|
|
{
|
|
function.Description = input.Description;
|
|
}
|
|
|
|
await _functionRepository.InsertAsync(function, autoSave: true);
|
|
result.Message = $"Function '{funcName}' saved successfully. Use Deploy button to deploy to SQL Server.";
|
|
}
|
|
|
|
result.ObjectType = "Function";
|
|
result.ObjectId = function.Id;
|
|
result.Deployed = function.IsDeployed;
|
|
}
|
|
else
|
|
{
|
|
// Default to Query (SELECT, INSERT, UPDATE, DELETE, etc.)
|
|
var queryName = input.Name ?? $"Query_{DateTime.Now:yyyyMMddHHmmss}";
|
|
var queryCode = queryName.Replace(" ", "_");
|
|
|
|
// Check if query already exists
|
|
var existingQuery = (await _queryRepository.GetListAsync())
|
|
.FirstOrDefault(q => q.Code == queryCode && q.DataSourceCode == input.DataSourceCode);
|
|
|
|
SqlQuery query;
|
|
if (existingQuery != null)
|
|
{
|
|
// Update existing query
|
|
existingQuery.Name = queryName;
|
|
existingQuery.QueryText = sqlText;
|
|
if (!string.IsNullOrEmpty(input.Description))
|
|
{
|
|
existingQuery.Description = input.Description;
|
|
}
|
|
query = await _queryRepository.UpdateAsync(existingQuery, autoSave: true);
|
|
result.Message = $"Query '{queryName}' updated successfully";
|
|
}
|
|
else
|
|
{
|
|
// Create new query
|
|
query = new SqlQuery(
|
|
GuidGenerator.Create(),
|
|
queryCode,
|
|
queryName,
|
|
sqlText,
|
|
input.DataSourceCode
|
|
);
|
|
|
|
if (!string.IsNullOrEmpty(input.Description))
|
|
{
|
|
query.Description = input.Description;
|
|
}
|
|
|
|
await _queryRepository.InsertAsync(query, autoSave: true);
|
|
result.Message = $"Query '{queryName}' saved successfully";
|
|
}
|
|
|
|
result.ObjectType = "Query";
|
|
result.ObjectId = query.Id;
|
|
result.Deployed = false; // Queries are not deployed;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Volo.Abp.UserFriendlyException($"Failed to save SQL object: {ex.Message}");
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
#region Helper Methods
|
|
|
|
private SqlQueryExecutionResultDto MapExecutionResult(SqlExecutionResult result)
|
|
{
|
|
return new SqlQueryExecutionResultDto
|
|
{
|
|
Success = result.Success,
|
|
Message = result.Message,
|
|
Data = result.Data,
|
|
RowsAffected = result.RowsAffected,
|
|
ExecutionTimeMs = result.ExecutionTimeMs,
|
|
Metadata = result.Metadata
|
|
};
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Helper Methods
|
|
|
|
/// <summary>
|
|
/// Generates a deterministic GUID from a string input using MD5 hash
|
|
/// This ensures same input always produces same GUID
|
|
/// </summary>
|
|
private static Guid GenerateDeterministicGuid(string input)
|
|
{
|
|
using (var md5 = MD5.Create())
|
|
{
|
|
var hash = md5.ComputeHash(Encoding.UTF8.GetBytes(input));
|
|
return new Guid(hash);
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
}
|