This repository was archived by the owner on Feb 8, 2025. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 17
Expand file tree
/
Copy pathscriptSqlUtils.js
More file actions
169 lines (145 loc) · 5.54 KB
/
scriptSqlUtils.js
File metadata and controls
169 lines (145 loc) · 5.54 KB
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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
const azdata = require('azdata');
function getColumnInfoQueryMySql(tableCatalog, tableSchema, tableName) {
return `SELECT
C.COLUMN_NAME,
C.DATA_TYPE,
C.CHARACTER_MAXIMUM_LENGTH,
C.NUMERIC_PRECISION,
C.NUMERIC_SCALE,
C.IS_NULLABLE,
(CASE WHEN EXTRA LIKE '%auto_increment%' THEN 'YES' ELSE 'NO' END) AS IS_IDENTITY,
(CASE WHEN EXTRA LIKE '%VIRTUAL GENERATED%' OR EXTRA LIKE '%STORED GENERATED%' THEN 'YES' ELSE 'NO' END) AS IS_COMPUTED,
C.DATETIME_PRECISION
FROM
INFORMATION_SCHEMA.TABLES AS T
INNER JOIN
INFORMATION_SCHEMA.COLUMNS AS C ON C.TABLE_NAME = T.TABLE_NAME AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE
T.TABLE_TYPE = 'BASE TABLE'
AND T.TABLE_SCHEMA = '${tableSchema}' -- Ensure this is the correct catalog name
AND T.TABLE_NAME = '${tableName}'
ORDER BY
C.ORDINAL_POSITION;`;
}
function getColumnInfoQuerySql(tableCatalog, tableSchema, tableName)
{
return `SELECT
COL.COLUMN_NAME,
COL.DATA_TYPE,
COL.CHARACTER_MAXIMUM_LENGTH,
COL.NUMERIC_PRECISION,
COL.NUMERIC_SCALE,
COL.IS_NULLABLE,
SYS_COLS.SUB_ISIDENTITY IS_IDENTITY,
SYS_COLS.SUB_ISCOMPUTED IS_COMPUTED,
COL.DATETIME_PRECISION
FROM [${tableCatalog}].INFORMATION_SCHEMA.TABLES T
INNER JOIN [${tableCatalog}].INFORMATION_SCHEMA.COLUMNS COL ON COL.TABLE_NAME = T.TABLE_NAME AND COL.TABLE_SCHEMA = T.TABLE_SCHEMA
INNER JOIN
(
SELECT
cc.name as SUB_COLNAME
,cc.is_identity as SUB_ISIDENTITY
,cc.is_computed as SUB_ISCOMPUTED
FROM
[${tableCatalog}].SYS.columns CC
inner join [${tableCatalog}].SYS.tables TT
on CC.object_id = TT.object_id
inner join [${tableCatalog}].SYS.schemas SS
on TT.schema_id = SS.schema_id
WHERE SS.name = '${tableSchema}' and TT.name = '${tableName}'
) SYS_COLS ON
COL.COLUMN_NAME = SYS_COLS.SUB_COLNAME
WHERE
T.TABLE_TYPE = 'BASE TABLE'
AND T.TABLE_SCHEMA = '${tableSchema}'
AND T.TABLE_CATALOG = '${tableCatalog}'
AND T.TABLE_NAME = '${tableName}'
ORDER BY COL.ORDINAL_POSITION`;
}
async function getResultsFromQuerySql(connectionProfile, providerText, queryText, tableCatalog)
{
//FIX AZURE DATA PROVIDER, WHEN DEFAULT DATABASE IS NOT SET USE ALWAYS MASTER IN CONNECTION PROFILE OPTIONS
if(tableCatalog
&& connectionProfile.options.database != tableCatalog){
connectionProfile.options.database = tableCatalog;
}
let connectionResult = await azdata.connection.connect(connectionProfile, false, false);
let connectionUri = await azdata.connection.getUriForConnection(connectionResult.connectionId);
let queryProvider = azdata.dataprotocol.getProvider(providerText, azdata.DataProviderType.QueryProvider);
return await queryProvider.runQueryAndReturn(connectionUri, queryText);
}
function getColTypeString (dataType, charMaxLen, numericPrecision, numericScale, isNullable, datetimePrecision)
{
const scaleDataTypes = ["decimal", "numeric"];
const precisionDataTypes = ["time", "datetimeoffset", "datetime2"];
const maxLenDataTypes = ["char", "nchar", "varchar", "nvarchar", "varbinary"];
let typeParts = [];
typeParts.push("<");
typeParts.push(dataType);
if(maxLenDataTypes.includes(dataType))
{
typeParts.push("(");
if(charMaxLen === "-1")
typeParts.push("MAX");
else
typeParts.push(charMaxLen);
typeParts.push(")");
}
if(precisionDataTypes.includes(dataType) || scaleDataTypes.includes(dataType))
{
typeParts.push("(");
if(numericPrecision === "NULL")
typeParts.push(datetimePrecision);
else
typeParts.push(numericPrecision);
if(numericScale !== "NULL" && numericScale === "0"){
typeParts.push(",");
typeParts.push(numericScale);
}
typeParts.push(")");
}
if(isNullable === "YES")
{
typeParts.push(", NULLABLE");
}
typeParts.push(">");
return typeParts.join('');
}
function getRoutineInfoQuerySql(tableCatalog, tableSchema, routineName) {
return `USE [${tableCatalog}]
EXEC sp_helptext '${tableSchema}.${routineName}'`;
}
function getRoutineInfoQueryMySql(tableCatalog, tableSchema, routineName) {
return `SELECT
ROUTINE_DEFINITION,
ROUTINE_TYPE,
DTD_IDENTIFIER,
DEFINER,
IS_DETERMINISTIC,
SQL_DATA_ACCESS,
SECURITY_TYPE,
PARAMETER_STYLE,
CONVERT(ROUTINE_COMMENT USING utf8) AS ROUTINE_COMMENT_TEXT
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = '${tableSchema}'
AND ROUTINE_NAME = '${routineName}';`;
}
function getRoutineParamsQueryMySql(tableCatalog, tableSchema, routineName) {
return `SELECT
PARAMETER_MODE as MODE,
PARAMETER_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = '${tableSchema}'
AND SPECIFIC_NAME = '${routineName}';
ORDER BY ORDINAL_POSITION;`;
}
module.exports.getResultsFromQuerySql = getResultsFromQuerySql;
module.exports.getColumnInfoQueryMySql = getColumnInfoQueryMySql;
module.exports.getColTypeString = getColTypeString;
module.exports.getColumnInfoQuerySql = getColumnInfoQuerySql;
module.exports.getRoutineInfoQuerySql = getRoutineInfoQuerySql;
module.exports.getRoutineInfoQueryMySql = getRoutineInfoQueryMySql;
module.exports.getRoutineParamsQueryMySql = getRoutineParamsQueryMySql;