-
Notifications
You must be signed in to change notification settings - Fork 454
/
Mssql.ts
73 lines (69 loc) · 2.24 KB
/
Mssql.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
import { MssqlConnectionParams } from "../../types/integrations/mssql";
import { decryptDataSourceParams } from "../services/datasource";
import { FormatDialect } from "../util/sql";
import { findOrCreateConnection } from "../util/mssqlPoolManager";
import SqlIntegration from "./SqlIntegration";
export default class Mssql extends SqlIntegration {
// eslint-disable-next-line @typescript-eslint/ban-ts-comment
// @ts-expect-error
params: MssqlConnectionParams;
requiresSchema = false;
setParams(encryptedParams: string) {
this.params = decryptDataSourceParams<MssqlConnectionParams>(
encryptedParams
);
}
getFormatDialect(): FormatDialect {
return "tsql";
}
getSensitiveParamKeys(): string[] {
return ["password"];
}
async runQuery(sqlStr: string) {
const conn = await findOrCreateConnection(this.datasource, {
server: this.params.server,
port: parseInt(this.params.port + "", 10),
user: this.params.user,
password: this.params.password,
database: this.params.database,
options: this.params.options,
});
const results = await conn.request().query(sqlStr);
return results.recordset;
}
// MS SQL Server doesn't support the LIMIT keyword, so we have to use the TOP or OFFSET and FETCH keywords instead.
// (and OFFSET/FETCH only work when there is an ORDER BY clause)
selectSampleRows(table: string, limit: number): string {
return `SELECT TOP ${limit} * FROM ${table}`;
}
addTime(
col: string,
unit: "hour" | "minute",
sign: "+" | "-",
amount: number
): string {
return `DATEADD(${unit}, ${sign === "-" ? "-" : ""}${amount}, ${col})`;
}
dateTrunc(col: string) {
//return `DATETRUNC(day, ${col})`; <- this is only supported in SQL Server 2022 preview.
return `cast(${col} as DATE)`;
}
stddev(col: string) {
return `STDEV(${col})`;
}
ensureFloat(col: string): string {
return `CAST(${col} as FLOAT)`;
}
formatDate(col: string): string {
return `FORMAT(${col}, 'yyyy-MM-dd')`;
}
castToString(col: string): string {
return `cast(${col} as varchar(256))`;
}
formatDateTimeString(col: string): string {
return `CONVERT(VARCHAR(25), ${col}, 121)`;
}
getDefaultDatabase() {
return this.params.database;
}
}