1027 lines
37 KiB
PHP
1027 lines
37 KiB
PHP
<?php
|
|
|
|
namespace App\Controllers\Query;
|
|
|
|
use App\Controllers\BaseController;
|
|
use App\Libraries\ApiConnector;
|
|
use App\Libraries\QueryBuilder;
|
|
use App\Libraries\QueryExecutor;
|
|
use App\Libraries\QueryVariableParser;
|
|
use App\Libraries\QueryVariableResolver;
|
|
use App\Models\DataSourceModel;
|
|
use App\Models\QueryVariableModel;
|
|
use App\Models\SavedQueryModel;
|
|
use Throwable;
|
|
|
|
class QueryController extends BaseController
|
|
{
|
|
public function index()
|
|
{
|
|
$workspaceId = (int) $this->session->get('active_workspace_id');
|
|
$queries = (new SavedQueryModel())->forWorkspace($workspaceId);
|
|
|
|
return view('query/index', [
|
|
'title' => 'Queries | Chart-Board',
|
|
'queries' => $queries,
|
|
]);
|
|
}
|
|
|
|
public function create()
|
|
{
|
|
$workspaceId = (int) $this->session->get('active_workspace_id');
|
|
$dataSources = (new DataSourceModel())->forWorkspace($workspaceId);
|
|
|
|
return view('query/create', [
|
|
'title' => 'Create Query | Chart-Board',
|
|
'dataSources' => $dataSources,
|
|
'initialVariables' => [],
|
|
]);
|
|
}
|
|
|
|
public function show(int $id)
|
|
{
|
|
$workspaceId = (int) $this->session->get('active_workspace_id');
|
|
$savedQueryModel = new SavedQueryModel();
|
|
$savedQuery = $savedQueryModel->where('workspace_id', $workspaceId)->find($id);
|
|
|
|
if (! $savedQuery) {
|
|
return redirect()->to('/query')->with('error', 'Query not found.');
|
|
}
|
|
|
|
$dataSource = (new DataSourceModel())
|
|
->where('workspace_id', $workspaceId)
|
|
->find((int) $savedQuery['data_source_id']);
|
|
|
|
$queryVariables = (new QueryVariableModel())
|
|
->where('saved_query_id', $id)
|
|
->orderBy('sort_order', 'ASC')
|
|
->findAll();
|
|
|
|
return view('query/show', [
|
|
'title' => (string) $savedQuery['name'] . ' | Chart-Board',
|
|
'savedQuery' => $savedQuery,
|
|
'dataSourceName' => $dataSource['name'] ?? null,
|
|
'queryVariables' => $queryVariables,
|
|
]);
|
|
}
|
|
|
|
public function edit(int $id)
|
|
{
|
|
$workspaceId = (int) $this->session->get('active_workspace_id');
|
|
$savedQueryModel = new SavedQueryModel();
|
|
$savedQuery = $savedQueryModel->where('workspace_id', $workspaceId)->find($id);
|
|
|
|
if (! $savedQuery) {
|
|
return redirect()->to('/query')->with('error', 'Query not found.');
|
|
}
|
|
|
|
$dataSources = (new DataSourceModel())->forWorkspace($workspaceId);
|
|
$visualConfig = $this->decodeJsonField($savedQuery['visual_config'] ?? null);
|
|
$fieldMapRows = $this->fieldMapToRows($savedQuery['field_map'] ?? null);
|
|
$apiHeaderRows = $this->apiParamsToHeaderRows($savedQuery['api_params'] ?? null);
|
|
|
|
return view('query/edit', [
|
|
'title' => 'Edit Query | Chart-Board',
|
|
'dataSources' => $dataSources,
|
|
'savedQuery' => $savedQuery,
|
|
'visualConfig' => $visualConfig,
|
|
'fieldMapRows' => $fieldMapRows,
|
|
'apiHeaderRows' => $apiHeaderRows,
|
|
'initialVariables' => $this->buildInitialVariablesForScripts($id),
|
|
]);
|
|
}
|
|
|
|
public function update(int $id)
|
|
{
|
|
$workspaceId = (int) $this->session->get('active_workspace_id');
|
|
$savedQueryModel = new SavedQueryModel();
|
|
$existing = $savedQueryModel->where('workspace_id', $workspaceId)->find($id);
|
|
|
|
if (! $existing) {
|
|
return redirect()->to('/query')->with('error', 'Query not found.');
|
|
}
|
|
|
|
$rules = [
|
|
'name' => 'required|min_length[3]|max_length[150]',
|
|
'data_source_id' => 'required|integer',
|
|
'query_type' => 'required|in_list[visual,raw_sql,api]',
|
|
'raw_sql' => 'permit_empty',
|
|
'description' => 'permit_empty|max_length[5000]',
|
|
];
|
|
|
|
if (! $this->validate($rules)) {
|
|
return redirect()->back()->withInput()->with('errors', $this->validator->getErrors());
|
|
}
|
|
|
|
$dataSourceId = (int) $this->request->getPost('data_source_id');
|
|
$dataSource = (new DataSourceModel())
|
|
->where('workspace_id', $workspaceId)
|
|
->find($dataSourceId);
|
|
|
|
if (! $dataSource) {
|
|
return redirect()->back()->withInput()->with('error', 'Selected data source was not found in this workspace.');
|
|
}
|
|
|
|
$queryType = (string) $this->request->getPost('query_type');
|
|
$rawSql = trim((string) $this->request->getPost('raw_sql'));
|
|
$apiEndpoint = trim((string) $this->request->getPost('api_endpoint'));
|
|
$responsePath = trim((string) $this->request->getPost('response_path'));
|
|
$cacheTtl = max(0, (int) $this->request->getPost('cache_ttl'));
|
|
|
|
if ($queryType === 'raw_sql' && $rawSql === '') {
|
|
return redirect()->back()->withInput()->with('error', 'Raw SQL query is required for raw_sql mode.');
|
|
}
|
|
if ($queryType === 'api' && $apiEndpoint === '') {
|
|
return redirect()->back()->withInput()->with('error', 'API endpoint is required for API mode.');
|
|
}
|
|
|
|
$visualConfig = null;
|
|
if ($queryType === 'visual') {
|
|
try {
|
|
$visualConfig = $this->buildVisualConfigFromRequest();
|
|
$resolverVals = $this->buildResolverInputValues($workspaceId, $id);
|
|
$visualResolved = $this->resolveVisualConfigPlaceholders($visualConfig, $resolverVals);
|
|
$built = (new QueryBuilder())->toSQL($visualResolved);
|
|
$rawSql = (string) $built['sql'];
|
|
} catch (Throwable $e) {
|
|
return redirect()->back()->withInput()->with('error', $e->getMessage());
|
|
}
|
|
}
|
|
|
|
if ($rawSql !== '' && $this->containsBlockedKeyword($rawSql)) {
|
|
return redirect()->back()->withInput()->with('error', 'Unsafe query detected. Only read-only queries are allowed.');
|
|
}
|
|
|
|
$payload = [
|
|
'workspace_id' => $workspaceId,
|
|
'data_source_id' => $dataSourceId,
|
|
'name' => strip_tags((string) $this->request->getPost('name')),
|
|
'description' => trim((string) $this->request->getPost('description')) ?: null,
|
|
'query_type' => $queryType,
|
|
'raw_sql' => $rawSql !== '' ? $rawSql : null,
|
|
'visual_config' => $visualConfig !== null ? json_encode($visualConfig, JSON_UNESCAPED_UNICODE) : null,
|
|
'api_endpoint' => $apiEndpoint !== '' ? $apiEndpoint : null,
|
|
'api_params' => $queryType === 'api' ? $this->buildApiParamsJson() : null,
|
|
'response_path' => $responsePath !== '' ? $responsePath : null,
|
|
'field_map' => $this->buildFieldMapJson(),
|
|
'cache_ttl' => $cacheTtl,
|
|
];
|
|
|
|
$savedQueryModel->update($id, $payload);
|
|
$parser = new QueryVariableParser();
|
|
$detectedNames = $this->collectDetectedVariableNames($queryType, $rawSql, $apiEndpoint, $visualConfig);
|
|
(new QueryVariableModel())->replaceForQuery($id, $workspaceId, $detectedNames, $this->collectVariableConfig());
|
|
|
|
$message = 'Query updated successfully.';
|
|
if ($detectedNames !== []) {
|
|
$message .= ' ' . count($detectedNames) . ' variable(s) detected and stored.';
|
|
}
|
|
|
|
return redirect()->to('/query/edit/' . $id)->with('success', $message);
|
|
}
|
|
|
|
public function store()
|
|
{
|
|
$workspaceId = (int) $this->session->get('active_workspace_id');
|
|
$rules = [
|
|
'name' => 'required|min_length[3]|max_length[150]',
|
|
'data_source_id' => 'required|integer',
|
|
'query_type' => 'required|in_list[visual,raw_sql,api]',
|
|
'raw_sql' => 'permit_empty',
|
|
'description' => 'permit_empty|max_length[5000]',
|
|
];
|
|
|
|
if (! $this->validate($rules)) {
|
|
return redirect()->back()->withInput()->with('errors', $this->validator->getErrors());
|
|
}
|
|
|
|
$dataSourceId = (int) $this->request->getPost('data_source_id');
|
|
$dataSource = (new DataSourceModel())
|
|
->where('workspace_id', $workspaceId)
|
|
->find($dataSourceId);
|
|
|
|
if (! $dataSource) {
|
|
return redirect()->back()->withInput()->with('error', 'Selected data source was not found in this workspace.');
|
|
}
|
|
|
|
$queryType = (string) $this->request->getPost('query_type');
|
|
$rawSql = trim((string) $this->request->getPost('raw_sql'));
|
|
$apiEndpoint = trim((string) $this->request->getPost('api_endpoint'));
|
|
$responsePath = trim((string) $this->request->getPost('response_path'));
|
|
$cacheTtl = max(0, (int) $this->request->getPost('cache_ttl'));
|
|
|
|
if ($queryType === 'raw_sql' && $rawSql === '') {
|
|
return redirect()->back()->withInput()->with('error', 'Raw SQL query is required for raw_sql mode.');
|
|
}
|
|
if ($queryType === 'api' && $apiEndpoint === '') {
|
|
return redirect()->back()->withInput()->with('error', 'API endpoint is required for API mode.');
|
|
}
|
|
|
|
$visualConfig = null;
|
|
if ($queryType === 'visual') {
|
|
try {
|
|
$visualConfig = $this->buildVisualConfigFromRequest();
|
|
$resolverVals = $this->buildResolverInputValues($workspaceId, 0);
|
|
$visualResolved = $this->resolveVisualConfigPlaceholders($visualConfig, $resolverVals);
|
|
$built = (new QueryBuilder())->toSQL($visualResolved);
|
|
$rawSql = (string) $built['sql'];
|
|
} catch (Throwable $e) {
|
|
return redirect()->back()->withInput()->with('error', $e->getMessage());
|
|
}
|
|
}
|
|
|
|
if ($rawSql !== '' && $this->containsBlockedKeyword($rawSql)) {
|
|
return redirect()->back()->withInput()->with('error', 'Unsafe query detected. Only read-only queries are allowed.');
|
|
}
|
|
|
|
$savedQueryModel = new SavedQueryModel();
|
|
$payload = [
|
|
'workspace_id' => $workspaceId,
|
|
'data_source_id' => $dataSourceId,
|
|
'name' => strip_tags((string) $this->request->getPost('name')),
|
|
'description' => trim((string) $this->request->getPost('description')) ?: null,
|
|
'query_type' => $queryType,
|
|
'raw_sql' => $rawSql !== '' ? $rawSql : null,
|
|
'visual_config' => $visualConfig !== null ? json_encode($visualConfig, JSON_UNESCAPED_UNICODE) : null,
|
|
'api_endpoint' => $apiEndpoint !== '' ? $apiEndpoint : null,
|
|
'api_params' => $queryType === 'api' ? $this->buildApiParamsJson() : null,
|
|
'response_path' => $responsePath !== '' ? $responsePath : null,
|
|
'field_map' => $this->buildFieldMapJson(),
|
|
'cache_ttl' => $cacheTtl,
|
|
'created_by' => (int) $this->session->get('user_id'),
|
|
];
|
|
|
|
$savedQueryId = (int) $savedQueryModel->insert($payload, true);
|
|
$detectedNames = $this->collectDetectedVariableNames($queryType, $rawSql, $apiEndpoint, $visualConfig);
|
|
(new QueryVariableModel())->replaceForQuery($savedQueryId, $workspaceId, $detectedNames, $this->collectVariableConfig());
|
|
|
|
$message = 'Query saved successfully.';
|
|
if ($detectedNames !== []) {
|
|
$message .= ' ' . count($detectedNames) . ' variable(s) detected and stored for configuration.';
|
|
}
|
|
|
|
return redirect()->to('/query/edit/' . $savedQueryId)->with('success', $message);
|
|
}
|
|
|
|
public function detectVariables()
|
|
{
|
|
$query = (string) $this->request->getPost('query');
|
|
$variables = (new QueryVariableParser())->parse($query);
|
|
|
|
return $this->response->setJSON([
|
|
'success' => true,
|
|
'variables' => $variables,
|
|
]);
|
|
}
|
|
|
|
public function execute()
|
|
{
|
|
$workspaceId = (int) $this->session->get('active_workspace_id');
|
|
$dataSourceId = (int) $this->request->getPost('data_source_id');
|
|
$queryType = (string) $this->request->getPost('query_type');
|
|
$cacheTtl = max(0, (int) $this->request->getPost('cache_ttl'));
|
|
|
|
$dataSource = (new DataSourceModel())
|
|
->where('workspace_id', $workspaceId)
|
|
->find($dataSourceId);
|
|
|
|
if (! $dataSource) {
|
|
return $this->response->setJSON([
|
|
'success' => false,
|
|
'message' => 'Data source not found in this workspace.',
|
|
])->setStatusCode(404);
|
|
}
|
|
|
|
try {
|
|
if ($queryType === 'api') {
|
|
$rows = $this->executeApiPreview();
|
|
return $this->response->setJSON([
|
|
'success' => true,
|
|
'message' => 'API query executed.',
|
|
'data' => $this->buildPreviewPayload($rows, 0.0, false),
|
|
]);
|
|
}
|
|
|
|
$savedQueryId = (int) $this->request->getPost('saved_query_id');
|
|
$resolverValues = $this->buildResolverInputValues($workspaceId, $savedQueryId);
|
|
|
|
$rawSqlTemplate = trim((string) $this->request->getPost('raw_sql'));
|
|
$sql = $rawSqlTemplate;
|
|
$bindings = [];
|
|
$warningVarNames = [];
|
|
|
|
if ($queryType === 'visual') {
|
|
$visualConfig = $this->buildVisualConfigFromRequest();
|
|
$warningVarNames = $this->parseVariableNamesFromVisualConfig($visualConfig);
|
|
$visualResolved = $this->resolveVisualConfigPlaceholders($visualConfig, $resolverValues);
|
|
$built = (new QueryBuilder())->toSQL($visualResolved);
|
|
$sql = (string) $built['sql'];
|
|
$bindings = $built['bindings'];
|
|
$rawSqlTemplate = '';
|
|
} else {
|
|
$warningVarNames = (new QueryVariableParser())->parse($rawSqlTemplate);
|
|
}
|
|
|
|
if ($sql === '') {
|
|
throw new \RuntimeException('Query cannot be empty.');
|
|
}
|
|
if ($this->containsBlockedKeyword($sql)) {
|
|
throw new \RuntimeException('Unsafe query detected. Only read-only queries are allowed.');
|
|
}
|
|
|
|
$sql = $this->stripTrailingSemicolon($sql);
|
|
$resolver = new QueryVariableResolver();
|
|
$resolved = $resolver->resolve($sql, $resolverValues);
|
|
|
|
$allBindings = array_merge($bindings, $resolved['bindings']);
|
|
$warnings = $this->buildVariableWarningsForNames($warningVarNames, $resolverValues, $resolver);
|
|
|
|
$result = (new QueryExecutor())->executeSql(
|
|
$dataSource,
|
|
(string) $resolved['sql'],
|
|
$allBindings,
|
|
$cacheTtl
|
|
);
|
|
|
|
return $this->response->setJSON([
|
|
'success' => true,
|
|
'message' => $warnings === []
|
|
? 'Query executed successfully.'
|
|
: 'Query executed successfully. Check warnings below.',
|
|
'data' => $this->buildPreviewPayload(
|
|
$result['rows'],
|
|
(float) $result['execution_ms'],
|
|
(bool) $result['cache_hit'],
|
|
[
|
|
'sql_parameterized' => (string) $resolved['sql'],
|
|
'bindings' => $this->serializeBindingsForDebug($allBindings),
|
|
'sql_interpolated' => $this->interpolateSqlForDebug((string) $resolved['sql'], $allBindings),
|
|
'warnings' => $warnings,
|
|
]
|
|
),
|
|
]);
|
|
} catch (Throwable $e) {
|
|
return $this->response->setJSON([
|
|
'success' => false,
|
|
'message' => $e->getMessage(),
|
|
])->setStatusCode(422);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function decodeJsonField(mixed $value): array
|
|
{
|
|
if ($value === null || $value === '') {
|
|
return [];
|
|
}
|
|
if (is_array($value)) {
|
|
return $value;
|
|
}
|
|
$decoded = json_decode((string) $value, true);
|
|
|
|
return is_array($decoded) ? $decoded : [];
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array{key: string, alias: string}>
|
|
*/
|
|
private function fieldMapToRows(mixed $fieldMap): array
|
|
{
|
|
$rows = [];
|
|
if ($fieldMap === null || $fieldMap === '') {
|
|
return $rows;
|
|
}
|
|
$fm = is_array($fieldMap) ? $fieldMap : json_decode((string) $fieldMap, true);
|
|
if (! is_array($fm)) {
|
|
return $rows;
|
|
}
|
|
foreach ($fm as $k => $v) {
|
|
$rows[] = ['key' => (string) $k, 'alias' => (string) $v];
|
|
}
|
|
|
|
return $rows;
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function buildInitialVariablesForScripts(int $savedQueryId): array
|
|
{
|
|
$rows = (new QueryVariableModel())
|
|
->where('saved_query_id', $savedQueryId)
|
|
->orderBy('sort_order', 'ASC')
|
|
->findAll();
|
|
|
|
$out = [];
|
|
foreach ($rows as $row) {
|
|
$opts = $row['options_json'] ?? null;
|
|
$optionsCsv = '';
|
|
if ($opts !== null && $opts !== '') {
|
|
$decoded = is_string($opts) ? json_decode($opts, true) : $opts;
|
|
if (is_array($decoded)) {
|
|
$optionsCsv = implode(', ', $decoded);
|
|
}
|
|
}
|
|
$out[] = [
|
|
'name' => (string) $row['name'],
|
|
'label' => (string) ($row['label'] ?? ''),
|
|
'type' => (string) ($row['type'] ?? 'text'),
|
|
'default_value' => (string) ($row['default_value'] ?? ''),
|
|
'options_csv' => $optionsCsv,
|
|
'is_required' => ! empty($row['is_required']),
|
|
'test_value' => (string) ($row['default_value'] ?? ''),
|
|
];
|
|
}
|
|
|
|
return $out;
|
|
}
|
|
|
|
private function containsBlockedKeyword(string $sql): bool
|
|
{
|
|
return (bool) preg_match('/\b(drop|delete|update|insert|truncate|alter|create|grant|revoke)\b/i', $sql);
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function buildVisualConfigFromRequest(): array
|
|
{
|
|
$table = trim((string) $this->request->getPost('visual_table'));
|
|
$columnsRaw = trim((string) $this->request->getPost('visual_columns'));
|
|
$columns = array_values(array_filter(array_map('trim', explode(',', $columnsRaw))));
|
|
|
|
$filters = [];
|
|
$fields = $this->request->getPost('filter_field');
|
|
$operators = $this->request->getPost('filter_operator');
|
|
$values = $this->request->getPost('filter_value');
|
|
if (is_array($fields) && is_array($operators) && is_array($values)) {
|
|
foreach ($fields as $idx => $field) {
|
|
$field = trim((string) $field);
|
|
if ($field === '') {
|
|
continue;
|
|
}
|
|
$filters[] = [
|
|
'field' => $field,
|
|
'operator' => trim((string) ($operators[$idx] ?? '=')),
|
|
'value' => $values[$idx] ?? null,
|
|
];
|
|
}
|
|
}
|
|
|
|
$orderBy = [];
|
|
$orderCols = $this->request->getPost('visual_order_column');
|
|
$orderDirs = $this->request->getPost('visual_order_direction');
|
|
if (is_array($orderCols) && is_array($orderDirs)) {
|
|
foreach ($orderCols as $idx => $col) {
|
|
$col = trim((string) $col);
|
|
if ($col === '') {
|
|
continue;
|
|
}
|
|
$dir = strtoupper(trim((string) ($orderDirs[$idx] ?? 'ASC')));
|
|
$orderBy[] = [
|
|
'column' => $col,
|
|
'direction' => $dir === 'DESC' ? 'DESC' : 'ASC',
|
|
];
|
|
}
|
|
}
|
|
|
|
$limitRaw = trim((string) $this->request->getPost('visual_limit'));
|
|
|
|
return [
|
|
'table' => $table,
|
|
'columns' => $columns !== [] ? $columns : ['*'],
|
|
'filters' => $filters,
|
|
'group_by' => [],
|
|
'order_by' => $orderBy,
|
|
'limit' => $limitRaw !== '' ? $limitRaw : '500',
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function buildResolverInputValues(int $workspaceId, int $savedQueryId): array
|
|
{
|
|
$resolverValues = json_decode((string) $this->request->getPost('variables_json'), true);
|
|
if (! is_array($resolverValues)) {
|
|
$resolverValues = [];
|
|
}
|
|
$resolverValues = $this->applyVariableDefaultsFromForm($resolverValues);
|
|
if ($savedQueryId > 0) {
|
|
$resolverValues = $this->mergeVariableDefaultsFromDatabase($workspaceId, $savedQueryId, $resolverValues);
|
|
}
|
|
|
|
return $resolverValues;
|
|
}
|
|
|
|
/**
|
|
* @param array<string, mixed> $config
|
|
* @param array<string, mixed> $resolverValues
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function resolveVisualConfigPlaceholders(array $config, array $resolverValues): array
|
|
{
|
|
$resolver = new QueryVariableResolver();
|
|
$out = $config;
|
|
|
|
$out['table'] = $resolver->resolveTemplateString(trim((string) ($out['table'] ?? '')), $resolverValues);
|
|
|
|
$cols = $out['columns'] ?? ['*'];
|
|
if (is_array($cols)) {
|
|
$newCols = [];
|
|
foreach ($cols as $c) {
|
|
$r = $resolver->resolveTemplateString(trim((string) $c), $resolverValues);
|
|
if ($r !== '') {
|
|
$newCols[] = $r;
|
|
}
|
|
}
|
|
$out['columns'] = $newCols !== [] ? $newCols : ['*'];
|
|
}
|
|
|
|
$filters = $out['filters'] ?? [];
|
|
if (is_array($filters)) {
|
|
$newF = [];
|
|
foreach ($filters as $f) {
|
|
if (! is_array($f)) {
|
|
continue;
|
|
}
|
|
$field = $resolver->resolveTemplateString(trim((string) ($f['field'] ?? '')), $resolverValues);
|
|
if ($field === '') {
|
|
continue;
|
|
}
|
|
$op = trim((string) ($f['operator'] ?? '='));
|
|
$val = $f['value'] ?? null;
|
|
if ($val !== null && $val !== '') {
|
|
$val = $resolver->resolveTemplateString((string) $val, $resolverValues);
|
|
}
|
|
$newF[] = [
|
|
'field' => $field,
|
|
'operator' => $op,
|
|
'value' => $val,
|
|
];
|
|
}
|
|
$out['filters'] = $newF;
|
|
}
|
|
|
|
$orderBy = $out['order_by'] ?? [];
|
|
if (is_array($orderBy) && array_key_exists('column', $orderBy)) {
|
|
$orderBy = [$orderBy];
|
|
}
|
|
if (is_array($orderBy)) {
|
|
$newOb = [];
|
|
foreach ($orderBy as $clause) {
|
|
if (! is_array($clause)) {
|
|
continue;
|
|
}
|
|
$col = $resolver->resolveTemplateString(trim((string) ($clause['column'] ?? '')), $resolverValues);
|
|
if ($col === '') {
|
|
continue;
|
|
}
|
|
$dir = strtoupper(trim((string) ($clause['direction'] ?? 'ASC')));
|
|
$newOb[] = [
|
|
'column' => $col,
|
|
'direction' => $dir === 'DESC' ? 'DESC' : 'ASC',
|
|
];
|
|
}
|
|
$out['order_by'] = $newOb;
|
|
}
|
|
|
|
$limStr = $resolver->resolveTemplateString((string) ($out['limit'] ?? '500'), $resolverValues);
|
|
$lim = (int) $limStr;
|
|
$out['limit'] = $lim > 0 ? $lim : 500;
|
|
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* @param array<string, mixed> $visualConfig
|
|
* @return array<int, string>
|
|
*/
|
|
private function parseVariableNamesFromVisualConfig(array $visualConfig): array
|
|
{
|
|
$parser = new QueryVariableParser();
|
|
$chunks = [];
|
|
$chunks[] = (string) ($visualConfig['table'] ?? '');
|
|
if (isset($visualConfig['columns']) && is_array($visualConfig['columns'])) {
|
|
foreach ($visualConfig['columns'] as $c) {
|
|
$chunks[] = (string) $c;
|
|
}
|
|
}
|
|
$chunks[] = (string) ($visualConfig['limit'] ?? '');
|
|
if (isset($visualConfig['filters']) && is_array($visualConfig['filters'])) {
|
|
foreach ($visualConfig['filters'] as $f) {
|
|
if (! is_array($f)) {
|
|
continue;
|
|
}
|
|
$chunks[] = (string) ($f['field'] ?? '');
|
|
$chunks[] = (string) ($f['value'] ?? '');
|
|
}
|
|
}
|
|
$ob = $visualConfig['order_by'] ?? [];
|
|
if (is_array($ob) && array_key_exists('column', $ob)) {
|
|
$chunks[] = (string) ($ob['column'] ?? '');
|
|
} elseif (is_array($ob)) {
|
|
foreach ($ob as $clause) {
|
|
if (is_array($clause)) {
|
|
$chunks[] = (string) ($clause['column'] ?? '');
|
|
}
|
|
}
|
|
}
|
|
|
|
return $parser->parse(implode("\n", $chunks));
|
|
}
|
|
|
|
/**
|
|
* @param array<string, mixed>|null $visualConfig Unresolved config (placeholders preserved)
|
|
* @return array<int, string>
|
|
*/
|
|
private function collectDetectedVariableNames(string $queryType, string $rawSql, string $apiEndpoint, ?array $visualConfig): array
|
|
{
|
|
$parser = new QueryVariableParser();
|
|
$detectedNames = [];
|
|
|
|
if ($queryType === 'visual' && $visualConfig !== null) {
|
|
$detectedNames = $this->parseVariableNamesFromVisualConfig($visualConfig);
|
|
} elseif ($queryType === 'raw_sql') {
|
|
$detectedNames = $parser->parse($rawSql);
|
|
}
|
|
|
|
if ($queryType === 'api') {
|
|
$detectedNames = array_values(array_unique(array_merge($detectedNames, $parser->parse($apiEndpoint))));
|
|
}
|
|
|
|
return array_values(array_unique($detectedNames));
|
|
}
|
|
|
|
private function buildFieldMapJson(): ?string
|
|
{
|
|
$keys = $this->request->getPost('field_map_key');
|
|
$aliases = $this->request->getPost('field_map_alias');
|
|
if (! is_array($keys) || ! is_array($aliases)) {
|
|
return null;
|
|
}
|
|
|
|
$map = [];
|
|
foreach ($keys as $i => $key) {
|
|
$k = trim((string) $key);
|
|
$v = trim((string) ($aliases[$i] ?? ''));
|
|
if ($k === '' || $v === '') {
|
|
continue;
|
|
}
|
|
$map[$k] = $v;
|
|
}
|
|
|
|
return $map === [] ? null : json_encode($map, JSON_UNESCAPED_UNICODE);
|
|
}
|
|
|
|
/**
|
|
* Persist API query headers in api_params JSON.
|
|
*/
|
|
private function buildApiParamsJson(): ?string
|
|
{
|
|
$headerKeys = $this->request->getPost('api_header_key');
|
|
$headerValues = $this->request->getPost('api_header_value');
|
|
$headers = [];
|
|
if (is_array($headerKeys) && is_array($headerValues)) {
|
|
foreach ($headerKeys as $idx => $key) {
|
|
$k = trim((string) $key);
|
|
if ($k === '') {
|
|
continue;
|
|
}
|
|
$headers[] = [
|
|
'key' => $k,
|
|
'value' => trim((string) ($headerValues[$idx] ?? '')),
|
|
];
|
|
}
|
|
}
|
|
|
|
if ($headers === []) {
|
|
return null;
|
|
}
|
|
|
|
return json_encode(['headers' => $headers], JSON_UNESCAPED_UNICODE);
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array{key: string, value: string}>
|
|
*/
|
|
private function apiParamsToHeaderRows(mixed $apiParams): array
|
|
{
|
|
$decoded = $this->decodeJsonField($apiParams);
|
|
$headers = $decoded['headers'] ?? null;
|
|
if (! is_array($headers)) {
|
|
return [['key' => '', 'value' => '']];
|
|
}
|
|
$rows = [];
|
|
foreach ($headers as $h) {
|
|
if (! is_array($h)) {
|
|
continue;
|
|
}
|
|
$rows[] = [
|
|
'key' => (string) ($h['key'] ?? ''),
|
|
'value' => (string) ($h['value'] ?? ''),
|
|
];
|
|
}
|
|
|
|
return $rows !== [] ? $rows : [['key' => '', 'value' => '']];
|
|
}
|
|
|
|
/**
|
|
* @return array<string, array<string, mixed>>
|
|
*/
|
|
private function collectVariableConfig(): array
|
|
{
|
|
$config = [
|
|
'label' => [],
|
|
'type' => [],
|
|
'default' => [],
|
|
'options' => [],
|
|
'required' => [],
|
|
];
|
|
|
|
$labels = $this->request->getPost('variable_label');
|
|
if (is_array($labels)) {
|
|
foreach ($labels as $name => $val) {
|
|
$key = strtolower(trim((string) $name));
|
|
if ($key === '') {
|
|
continue;
|
|
}
|
|
$config['label'][$key] = trim((string) $val);
|
|
}
|
|
}
|
|
|
|
$types = $this->request->getPost('variable_type');
|
|
if (is_array($types)) {
|
|
foreach ($types as $name => $val) {
|
|
$key = strtolower(trim((string) $name));
|
|
if ($key === '') {
|
|
continue;
|
|
}
|
|
$config['type'][$key] = trim((string) $val);
|
|
}
|
|
}
|
|
|
|
$defaults = $this->request->getPost('variable_default');
|
|
if (is_array($defaults)) {
|
|
foreach ($defaults as $name => $val) {
|
|
$key = strtolower(trim((string) $name));
|
|
if ($key === '') {
|
|
continue;
|
|
}
|
|
$config['default'][$key] = trim((string) $val);
|
|
}
|
|
}
|
|
|
|
$options = $this->request->getPost('variable_options');
|
|
if (is_array($options)) {
|
|
foreach ($options as $name => $val) {
|
|
$key = strtolower(trim((string) $name));
|
|
if ($key === '') {
|
|
continue;
|
|
}
|
|
$opts = trim((string) $val);
|
|
if ($opts !== '') {
|
|
$optValues = array_values(array_filter(array_map('trim', explode(',', $opts))));
|
|
$config['options'][$key] = json_encode($optValues, JSON_UNESCAPED_UNICODE);
|
|
}
|
|
}
|
|
}
|
|
|
|
$required = $this->request->getPost('variable_required');
|
|
if (is_array($required)) {
|
|
foreach ($required as $name => $val) {
|
|
$key = strtolower(trim((string) $name));
|
|
if ($key === '') {
|
|
continue;
|
|
}
|
|
$config['required'][$key] = (string) $val === '1';
|
|
}
|
|
}
|
|
|
|
return $config;
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array<string, mixed>>
|
|
*/
|
|
private function executeApiPreview(): array
|
|
{
|
|
$endpoint = trim((string) $this->request->getPost('api_endpoint'));
|
|
if ($endpoint === '') {
|
|
throw new \RuntimeException('API endpoint is required for API mode.');
|
|
}
|
|
|
|
$queryResolver = new QueryVariableResolver();
|
|
$vars = json_decode((string) $this->request->getPost('variables_json'), true);
|
|
if (! is_array($vars)) {
|
|
$vars = [];
|
|
}
|
|
|
|
$resolvedUrl = preg_replace_callback(
|
|
'/\{\{\s*([a-zA-Z_][a-zA-Z0-9_]*)\s*\}\}/',
|
|
static function (array $match) use ($queryResolver, $vars): string {
|
|
$resolved = $queryResolver->resolve('{{' . $match[1] . '}}', $vars);
|
|
return urlencode((string) ($resolved['bindings'][0] ?? ''));
|
|
},
|
|
$endpoint
|
|
);
|
|
|
|
$headers = [];
|
|
$headerKeys = $this->request->getPost('api_header_key');
|
|
$headerValues = $this->request->getPost('api_header_value');
|
|
if (is_array($headerKeys) && is_array($headerValues)) {
|
|
foreach ($headerKeys as $idx => $key) {
|
|
$k = trim((string) $key);
|
|
if ($k === '') {
|
|
continue;
|
|
}
|
|
$headers[$k] = trim((string) ($headerValues[$idx] ?? ''));
|
|
}
|
|
}
|
|
|
|
$jsonPath = trim((string) $this->request->getPost('response_path'));
|
|
$rows = (new ApiConnector())->fetch((string) $resolvedUrl, $headers, $jsonPath !== '' ? $jsonPath : null);
|
|
|
|
$fieldMapRaw = $this->buildFieldMapJson();
|
|
if ($fieldMapRaw !== null) {
|
|
$fieldMap = json_decode($fieldMapRaw, true);
|
|
if (is_array($fieldMap) && $fieldMap !== []) {
|
|
$rows = array_map(static function (array $row) use ($fieldMap): array {
|
|
$mapped = [];
|
|
foreach ($fieldMap as $source => $alias) {
|
|
$mapped[(string) $alias] = $row[(string) $source] ?? null;
|
|
}
|
|
return $mapped;
|
|
}, $rows);
|
|
}
|
|
}
|
|
|
|
return $rows;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, array<string, mixed>> $rows
|
|
* @param array<string, mixed>|null $debug
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function buildPreviewPayload(array $rows, float $executionMs, bool $cacheHit, ?array $debug = null): array
|
|
{
|
|
$previewRows = array_slice($rows, 0, 100);
|
|
$columns = [];
|
|
if ($previewRows !== []) {
|
|
$columns = array_keys($previewRows[0]);
|
|
}
|
|
|
|
$payload = [
|
|
'columns' => $columns,
|
|
'rows' => $previewRows,
|
|
'row_count' => count($rows),
|
|
'execution_ms' => $executionMs,
|
|
'truncated' => count($rows) > 100,
|
|
'cache_hit' => $cacheHit,
|
|
];
|
|
if ($debug !== null) {
|
|
$payload['debug'] = $debug;
|
|
}
|
|
|
|
return $payload;
|
|
}
|
|
|
|
/**
|
|
* Uses `variable_default[varname]` from the same request (keyed fields) when test value is empty.
|
|
*
|
|
* @param array<string, mixed> $input
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function applyVariableDefaultsFromForm(array $input): array
|
|
{
|
|
$defaults = $this->request->getPost('variable_default');
|
|
if (! is_array($defaults)) {
|
|
return $input;
|
|
}
|
|
|
|
$out = $input;
|
|
foreach ($defaults as $rawName => $def) {
|
|
$key = strtolower(trim((string) $rawName));
|
|
if ($key === '') {
|
|
continue;
|
|
}
|
|
$def = trim((string) $def);
|
|
$current = $out[$key] ?? null;
|
|
if (($current === null || $current === '') && $def !== '') {
|
|
$out[$key] = $def;
|
|
}
|
|
}
|
|
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* Fills empty values from saved `query_variables` rows.
|
|
*
|
|
* @param array<string, mixed> $input
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function mergeVariableDefaultsFromDatabase(int $workspaceId, int $savedQueryId, array $input): array
|
|
{
|
|
if ($savedQueryId <= 0) {
|
|
return $input;
|
|
}
|
|
|
|
$rows = (new QueryVariableModel())
|
|
->where('workspace_id', $workspaceId)
|
|
->where('saved_query_id', $savedQueryId)
|
|
->findAll();
|
|
|
|
$out = $input;
|
|
foreach ($rows as $row) {
|
|
$name = strtolower((string) $row['name']);
|
|
$current = $out[$name] ?? null;
|
|
if ($current === null || $current === '') {
|
|
$def = trim((string) ($row['default_value'] ?? ''));
|
|
if ($def !== '') {
|
|
$out[$name] = $def;
|
|
}
|
|
}
|
|
}
|
|
|
|
return $out;
|
|
}
|
|
|
|
private function stripTrailingSemicolon(string $sql): string
|
|
{
|
|
return rtrim(preg_replace('/\s*;\s*$/', '', trim($sql)));
|
|
}
|
|
|
|
/**
|
|
* @param array<int, string> $names
|
|
* @return array<int, string>
|
|
*/
|
|
private function buildVariableWarningsForNames(array $names, array $mergedValues, QueryVariableResolver $resolver): array
|
|
{
|
|
if ($names === []) {
|
|
return [];
|
|
}
|
|
|
|
$warnings = [];
|
|
foreach ($names as $name) {
|
|
$name = strtolower($name);
|
|
if ($resolver->isSystemName($name)) {
|
|
continue;
|
|
}
|
|
$v = $mergedValues[$name] ?? null;
|
|
if (is_array($v)) {
|
|
$flat = array_values(array_filter($v, static fn ($x) => $x !== '' && $x !== null));
|
|
$v = $flat === [] ? null : $flat[0];
|
|
}
|
|
if ($v === null || $v === '') {
|
|
$warnings[] = 'Variable {{' . $name . '}} has no test value and no saved default — it resolved to empty and may break identifiers or filters. Enter a Test value or a Default, then click Update Query to save.';
|
|
}
|
|
}
|
|
|
|
return $warnings;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, mixed> $bindings
|
|
* @return array<int, mixed>
|
|
*/
|
|
private function serializeBindingsForDebug(array $bindings): array
|
|
{
|
|
$out = [];
|
|
foreach ($bindings as $b) {
|
|
if ($b === null) {
|
|
$out[] = null;
|
|
continue;
|
|
}
|
|
$out[] = $b;
|
|
}
|
|
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, mixed> $bindings
|
|
*/
|
|
private function interpolateSqlForDebug(string $sql, array $bindings): string
|
|
{
|
|
$out = $sql;
|
|
foreach ($bindings as $b) {
|
|
if ($b === null) {
|
|
$rep = 'NULL';
|
|
} elseif (is_bool($b)) {
|
|
$rep = $b ? '1' : '0';
|
|
} elseif (is_int($b) || is_float($b)) {
|
|
$rep = (string) $b;
|
|
} else {
|
|
$rep = "'" . str_replace(["\\", "'"], ["\\\\", "\\'"], (string) $b) . "'";
|
|
}
|
|
$out = preg_replace('/\?/', $rep, $out, 1);
|
|
}
|
|
|
|
return $out;
|
|
}
|
|
}
|