Select Git revision
query-service.ts
Martin Weise authored
query-service.ts 11.52 KiB
import {format} from 'sql-formatter'
import type {AxiosRequestConfig} from 'axios'
import {axiosErrorToApiError} from '@/utils'
export const useQueryService = (): any => {
async function findAll(databaseId: number, persisted: boolean): Promise<QueryDto[]> {
const axios = useAxiosInstance()
console.debug('find queries')
return new Promise<QueryDto[]>((resolve, reject) => {
axios.get<QueryDto[]>(`/api/database/${databaseId}/subset`, {params: (persisted && {persisted})})
.then((response) => {
console.info(`Found ${response.data.length} query(s)`)
resolve(response.data)
})
.catch((error) => {
if (error.response.status === 403) {
/* ignore */
resolve([])
}
console.error('Failed to find queries', error)
reject(axiosErrorToApiError(error))
})
})
}
async function findOne(databaseId: number, queryId: number): Promise<QueryDto> {
const axios = useAxiosInstance()
console.debug('find query with id', queryId, 'in database with id', databaseId)
return new Promise<QueryDto>((resolve, reject) => {
axios.get<QueryDto>(`/api/database/${databaseId}/subset/${queryId}`)
.then((response) => {
console.info('Found query with id', queryId, 'in database with id', databaseId)
resolve(response.data)
})
.catch((error) => {
console.error('Failed to find query', error)
reject(axiosErrorToApiError(error))
})
})
}
async function update(databaseId: number, queryId: number, data: QueryPersistDto): Promise<QueryDto> {
const axios = useAxiosInstance()
console.debug('update query with id', queryId, 'in database with id', databaseId)
return new Promise<QueryDto>((resolve, reject) => {
axios.put<QueryDto>(`/api/database/${databaseId}/subset/${queryId}`, data)
.then((response) => {
console.info('Updated query with id', queryId, 'in database with id', databaseId)
resolve(response.data)
})
.catch((error) => {
console.error('Failed to update query', error)
reject(axiosErrorToApiError(error))
})
})
}
async function exportCsv(databaseId: number, queryId: number): Promise<any> {
const axios = useAxiosInstance()
const config: AxiosRequestConfig = {
responseType: 'blob',
headers: {
Accept: 'text/csv'
}
}
console.debug('export query with id', queryId, 'in database with id', databaseId)
return new Promise<any>((resolve, reject) => {
axios.get<any>(`/api/database/${databaseId}/subset/${queryId}`, config)
.then((response) => {
console.info('Exported query with id', queryId, 'in database with id', databaseId) resolve(response.data)
})
.catch((error) => {
console.error('Failed to export query', error)
reject(axiosErrorToApiError(error))
})
})
}
async function execute(databaseId: number, data: ExecuteStatementDto, timestamp: Date | null, page: number, size: number): Promise<QueryResultDto> {
const axios = useAxiosInstance()
console.debug('execute query in database with id', databaseId)
return new Promise<QueryResultDto>((resolve, reject) => {
axios.post<QueryResultDto>(`/api/database/${databaseId}/subset`, data, {params: mapFilter(timestamp, page, size), timeout: 600_000})
.then((response) => {
console.info('Executed query with id', response.data.id, ' in database with id', databaseId)
resolve(response.data)
})
.catch((error) => {
console.error('Failed to execute query', error)
reject(axiosErrorToApiError(error))
})
})
}
async function reExecuteData(databaseId: number, queryId: number, page: number, size: number): Promise<QueryResultDto> {
const axios = useAxiosInstance()
console.debug('re-execute query in database with id', databaseId)
return new Promise<QueryResultDto>((resolve, reject) => {
axios.get<QueryResultDto>(`/api/database/${databaseId}/subset/${queryId}/data`, { params: mapFilter(null, page, size) })
.then((response) => {
console.info('Re-executed query in database with id', databaseId)
resolve(response.data)
})
.catch((error) => {
console.error('Failed to re-execute query', error)
reject(axiosErrorToApiError(error))
})
})
}
async function reExecuteCount(databaseId: number, queryId: number): Promise<number> {
const axios = useAxiosInstance()
console.debug('re-execute query in database with id', databaseId)
return new Promise<number>((resolve, reject) => {
axios.head<void>(`/api/database/${databaseId}/subset/${queryId}/data`)
.then((response) => {
const count: number = Number(response.headers['x-count'])
console.info('Found', count, 'tuples for query', queryId, 'in database with id', databaseId)
resolve(count)
})
.catch((error) => {
console.error('Failed to re-execute query', error)
reject(axiosErrorToApiError(error))
})
})
}
function build(table: TableDto, columns: ColumnDto[], clauses: any[]): QueryBuildResultDto {
var sql = 'SELECT'
for (let i = 0; i < columns.length; i++) {
sql += `${i > 0 ? ',' : ''} \`${columns[i].internal_name}\``
}
sql += ` FROM \`${table}\``
if (clauses.length > 0) {
sql += ' WHERE'
for (let i = 0; i < clauses.length; i++) {
const clause = clauses[i]
if (clause.type === 'and' || clause.type === 'or') {
sql += ` ${clause.type.toUpperCase()} ` continue
}
const fCol = columns.filter(c => c.internal_name === clause.params[0])
if (fCol.length === 0) {
return {
error: true,
reason: 'column.exists',
column: clause.params[0],
raw: null,
formatted: null
}
}
sql += ` \`${clause.params[0]}\` ${clause.params[1]} `
const fCon = mySql8DataTypes().filter(t => t.value === fCol[0].column_type)
if (fCol.length === 0) {
return {
error: true,
reason: 'type.exists',
column: fCol[0].column_type,
raw: null,
formatted: null
}
}
if (!fCon[0].isBuildable) {
return {
error: true,
reason: 'type.build',
column: fCol[0].column_type,
raw: null,
formatted: null
}
}
if (fCon[0].quoted) {
sql += `'${clause.params[2]}'`
} else {
sql += `${clause.params[2]}`
}
}
}
return {
error: false,
reason: null,
column: null,
raw: sql,
formatted: format(sql, {
language: 'mysql',
keywordCase: 'upper'
})
}
}
function mapFilter(timestamp: Date | null, page: number, size: number) {
if (!timestamp) {
return {page, size}
}
return {timestamp, page, size}
}
function mySql8DataTypes(): MySql8DataType[] {
return [
{value: 'bigint', text: 'BIGINT(size)', defaultSize: null, defaultD: null, signed: null, zerofill: false, quoted: false, isBuildable: true, hint: null},
{value: 'binary', text: 'BINARY(size)', minSize: 0, maxSize: 255, defaultSize: 255, defaultD: null, quoted: false, isBuildable: false, hint: 'size in Bytes'},
{value: 'bit', text: 'BIT(size)', minSize: 1, maxSize: 64, defaultSize: null, defaultD: null, quoted: false, isBuildable: true, hint: null},
{value: 'blob', text: 'BLOB(size)', minSize: 0, maxSize: 65535, defaultSize: null, defaultD: null, quoted: false, isBuildable: false, hint: 'size in Bytes'},
{value: 'bool', text: 'BOOL', defaultSize: null, defaultD: null, quoted: false, isBuildable: true},
{value: 'char', text: 'CHAR(size)', minSize: 0, maxSize: 255, defaultSize: 255, defaultD: null, quoted: true, isBuildable: true},
{value: 'date', text: 'DATE', defaultSize: null, defaultD: null, quoted: true, isBuildable: true, hint: 'min. 1000-01-01, max. 9999-12-31'},
{value: 'datetime', text: 'DATETIME(fsp)', minSize: 0, maxSize: 6, defaultSize: null, defaultD: null, quoted: true, isBuildable: true, hint: 'fsp=microsecond precision, min. 1000-01-01 00:00:00.0, max. 9999-12-31 23:59:59.9'},
{value: 'decimal', text: 'DECIMAL(size, d)', minSize: 0, maxSize: 65, defaultSize: null, defaultD: null, minD: 0, maxD: 38, signed: null, quoted: false, isBuildable: true},
{value: 'double', text: 'DOUBLE(size, d)', defaultSize: null, defaultD: null, signed: null, quoted: false, isBuildable: true}, {value: 'enum', text: 'ENUM(val1,val2,...)', defaultSize: null, defaultD: null, quoted: true, isBuildable: true},
{value: 'float', text: 'FLOAT(p)', defaultSize: null, defaultD: null, signed: null, quoted: false, isBuildable: true},
{value: 'int', text: 'INT(size)', defaultSize: null, defaultD: null, signed: null, zerofill: false, quoted: false, isBuildable: true, hint: 'size in Bytes'},
{value: 'longblob', text: 'LONGBLOB', defaultSize: null, defaultD: null, quoted: false, isBuildable: false, hint: 'max. 3.999 GiB'},
{value: 'longtext', text: 'LONGTEXT', defaultSize: null, defaultD: null, quoted: true, isBuildable: true, hint: 'max. 3.999 GiB'},
{value: 'mediumblob', text: 'MEDIUMBLOB', defaultSize: null, defaultD: null, quoted: false, isBuildable: false, hint: 'max. 15.999 MiB'},
{value: 'mediumint', text: 'MEDIUMINT(size)', defaultSize: null, defaultD: null, signed: null, zerofill: false, quoted: false, isBuildable: true, hint: 'size in Bytes'},
{value: 'mediumtext', text: 'MEDIUMTEXT', defaultSize: null, defaultD: null, quoted: true, isBuildable: true},
{value: 'set', text: 'SET(val1,val2,...)', defaultSize: null, defaultD: null, quoted: true, isBuildable: true},
{value: 'smallint', text: 'SMALLINT(size)', defaultSize: null, defaultD: null, signed: null, zerofill: false, quoted: false, isBuildable: true, hint: 'size in Bytes'},
{value: 'text', text: 'TEXT(size)', minSize: 0, defaultSize: null, defaultD: null, quoted: true, isBuildable: true, hint: 'size in #characters'},
{value: 'time', text: 'TIME(fsp)', minSize: 0, maxSize: 6, defaultSize: 0, defaultD: null, quoted: true, isBuildable: true, hint: 'fsp=microsecond precision, min. 0, max. 6'},
{value: 'timestamp', text: 'TIMESTAMP(fsp)', minSize: 0, maxSize: 6, defaultSize: 0, defaultD: null, quoted: true, isBuildable: true, hint: 'fsp=microsecond precision, min. 0, max. 6'},
{value: 'tinyblob', text: 'TINYBLOB', defaultSize: null, defaultD: null, quoted: false, isBuildable: false},
{value: 'tinyint', text: 'TINYINT(size)', defaultSize: null, defaultD: null, quoted: false, isBuildable: true, hint: 'size in Bytes'},
{value: 'tinytext', text: 'TINYTEXT', defaultSize: null, defaultD: null, quoted: true, isBuildable: true, hint: 'max. 255 characters'},
{value: 'year', text: 'YEAR', minSize: 2, maxSize: 4, sizeSteps: 2, defaultSize: null, defaultD: null, quoted: true, isBuildable: true, hint: 'min. 1901, max. 2155'},
{value: 'varbinary', text: 'VARBINARY(size)', defaultSize: null, defaultD: null, quoted: false, isBuildable: false},
{value: 'varchar', text: 'VARCHAR(size)', minSize: 0, maxSize: 65532, defaultSize: 255, defaultD: null, quoted: true, isBuildable: true, hint: 'max. characters depends on the encoding'}
]
}
return {findAll, findOne, update, exportCsv, execute, reExecuteData, reExecuteCount, build, mySql8DataTypes}
}