1import { TemplateSrv } from 'app/features/templating/template_srv'; 2import { QueryEditorExpressionType } from '../expressions'; 3import { SQLExpression } from '../types'; 4import { 5 aggregationvariable, 6 labelsVariable, 7 metricVariable, 8 namespaceVariable, 9} from '../__mocks__/CloudWatchDataSource'; 10import { 11 createFunctionWithParameter, 12 createArray, 13 createOperator, 14 createGroupBy, 15 createFunction, 16 createProperty, 17} from '../__mocks__/sqlUtils'; 18import SQLGenerator from './SQLGenerator'; 19 20describe('SQLGenerator', () => { 21 let baseQuery: SQLExpression = { 22 select: createFunctionWithParameter('SUM', ['CPUUtilization']), 23 from: createFunctionWithParameter('SCHEMA', ['AWS/EC2']), 24 orderByDirection: 'DESC', 25 }; 26 27 describe('mandatory fields check', () => { 28 it('should return undefined if metric and aggregation is missing', () => { 29 expect( 30 new SQLGenerator().expressionToSqlQuery({ 31 from: createFunctionWithParameter('SCHEMA', ['AWS/EC2']), 32 }) 33 ).toBeUndefined(); 34 }); 35 36 it('should return undefined if aggregation is missing', () => { 37 expect( 38 new SQLGenerator().expressionToSqlQuery({ 39 from: createFunctionWithParameter('SCHEMA', []), 40 }) 41 ).toBeUndefined(); 42 }); 43 }); 44 45 it('should return query if mandatory fields are provided', () => { 46 expect(new SQLGenerator().expressionToSqlQuery(baseQuery)).not.toBeUndefined(); 47 }); 48 49 describe('select', () => { 50 it('should use statistic and metric name', () => { 51 const select = createFunctionWithParameter('COUNT', ['BytesPerSecond']); 52 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery, select })).toEqual( 53 `SELECT COUNT(BytesPerSecond) FROM SCHEMA("AWS/EC2")` 54 ); 55 }); 56 57 it('should wrap in double quotes if metric name contains illegal characters ', () => { 58 const select = createFunctionWithParameter('COUNT', ['Bytes-Per-Second']); 59 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery, select })).toEqual( 60 `SELECT COUNT("Bytes-Per-Second") FROM SCHEMA("AWS/EC2")` 61 ); 62 }); 63 }); 64 65 describe('from', () => { 66 describe('with schema contraint', () => { 67 it('should handle schema without dimensions', () => { 68 const from = createFunctionWithParameter('SCHEMA', ['AWS/MQ']); 69 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery, from })).toEqual( 70 `SELECT SUM(CPUUtilization) FROM SCHEMA("AWS/MQ")` 71 ); 72 }); 73 74 it('should handle schema with dimensions', () => { 75 const from = createFunctionWithParameter('SCHEMA', ['AWS/MQ', 'InstanceId', 'InstanceType']); 76 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery, from })).toEqual( 77 `SELECT SUM(CPUUtilization) FROM SCHEMA("AWS/MQ", InstanceId, InstanceType)` 78 ); 79 }); 80 81 it('should handle schema with dimensions that has special characters', () => { 82 const from = createFunctionWithParameter('SCHEMA', [ 83 'AWS/MQ', 84 'Instance Id', 85 'Instance.Type', 86 'Instance-Group', 87 ]); 88 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery, from })).toEqual( 89 `SELECT SUM(CPUUtilization) FROM SCHEMA("AWS/MQ", "Instance Id", "Instance.Type", "Instance-Group")` 90 ); 91 }); 92 }); 93 94 describe('without schema', () => { 95 it('should use the specified namespace', () => { 96 const from = createProperty('AWS/MQ'); 97 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery, from })).toEqual( 98 `SELECT SUM(CPUUtilization) FROM "AWS/MQ"` 99 ); 100 }); 101 }); 102 }); 103 104 function assertQueryEndsWith(rest: Partial<SQLExpression>, expectedFilter: string) { 105 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery, ...rest })).toEqual( 106 `SELECT SUM(CPUUtilization) FROM SCHEMA("AWS/EC2") ${expectedFilter}` 107 ); 108 } 109 110 describe('filter', () => { 111 it('should not add WHERE clause in case its empty', () => { 112 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery })).not.toContain('WHERE'); 113 }); 114 115 it('should not add WHERE clause when there is no filter conditions', () => { 116 const where = createArray([]); 117 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery, where })).not.toContain('WHERE'); 118 }); 119 120 // TODO: We should handle this scenario 121 it.skip('should not add WHERE clause when the operator is incomplete', () => { 122 const where = createArray([createOperator('Instance-Id', '=')]); 123 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery, where })).not.toContain('WHERE'); 124 }); 125 126 it('should handle one top level filter with AND', () => { 127 const where = createArray([createOperator('Instance-Id', '=', 'I-123')]); 128 assertQueryEndsWith({ where }, `WHERE "Instance-Id" = 'I-123'`); 129 }); 130 131 it('should handle one top level filter with OR', () => { 132 assertQueryEndsWith( 133 { where: createArray([createOperator('InstanceId', '=', 'I-123')]) }, 134 `WHERE InstanceId = 'I-123'` 135 ); 136 }); 137 138 it('should handle multiple top level filters combined with AND', () => { 139 const filter = createArray( 140 [createOperator('InstanceId', '=', 'I-123'), createOperator('Instance-Id', '!=', 'I-456')], 141 QueryEditorExpressionType.And 142 ); 143 assertQueryEndsWith({ where: filter }, `WHERE InstanceId = 'I-123' AND "Instance-Id" != 'I-456'`); 144 }); 145 146 it('should handle multiple top level filters combined with OR', () => { 147 const filter = createArray( 148 [createOperator('InstanceId', '=', 'I-123'), createOperator('InstanceId', '!=', 'I-456')], 149 QueryEditorExpressionType.Or 150 ); 151 assertQueryEndsWith({ where: filter }, `WHERE InstanceId = 'I-123' OR InstanceId != 'I-456'`); 152 }); 153 154 it('should handle one top level filters with one nested filter', () => { 155 const filter = createArray( 156 [ 157 createOperator('InstanceId', '=', 'I-123'), 158 createArray([createOperator('InstanceId', '!=', 'I-456')], QueryEditorExpressionType.And), 159 ], 160 QueryEditorExpressionType.And 161 ); 162 assertQueryEndsWith({ where: filter }, `WHERE InstanceId = 'I-123' AND InstanceId != 'I-456'`); 163 }); 164 165 it('should handle one top level filter with two nested filters combined with AND', () => { 166 const filter = createArray( 167 [ 168 createOperator('Instance.Type', '=', 'I-123'), 169 createArray( 170 [createOperator('InstanceId', '!=', 'I-456'), createOperator('Type', '!=', 'some-type')], 171 QueryEditorExpressionType.And 172 ), 173 ], 174 QueryEditorExpressionType.And 175 ); 176 // In this scenario, the parenthesis are redundant. However, they're not doing any harm and it would be really complicated to remove them 177 assertQueryEndsWith( 178 { where: filter }, 179 `WHERE "Instance.Type" = 'I-123' AND (InstanceId != 'I-456' AND Type != 'some-type')` 180 ); 181 }); 182 183 it('should handle one top level filter with two nested filters combined with OR', () => { 184 const filter = createArray( 185 [ 186 createOperator('InstanceId', '=', 'I-123'), 187 createArray( 188 [createOperator('InstanceId', '!=', 'I-456'), createOperator('Type', '!=', 'some-type')], 189 QueryEditorExpressionType.Or 190 ), 191 ], 192 QueryEditorExpressionType.And 193 ); 194 assertQueryEndsWith( 195 { where: filter }, 196 `WHERE InstanceId = 'I-123' AND (InstanceId != 'I-456' OR Type != 'some-type')` 197 ); 198 }); 199 200 it('should handle two top level filters with two nested filters combined with AND', () => { 201 const filter = createArray( 202 [ 203 createArray( 204 [createOperator('InstanceId', '=', 'I-123'), createOperator('Type', '!=', 'some-type')], 205 QueryEditorExpressionType.And 206 ), 207 createArray( 208 [createOperator('InstanceId', '!=', 'I-456'), createOperator('Type', '!=', 'some-type')], 209 QueryEditorExpressionType.Or 210 ), 211 ], 212 QueryEditorExpressionType.And 213 ); 214 215 assertQueryEndsWith( 216 { where: filter }, 217 `WHERE (InstanceId = 'I-123' AND Type != 'some-type') AND (InstanceId != 'I-456' OR Type != 'some-type')` 218 ); 219 }); 220 221 it('should handle two top level filters with two nested filters combined with OR', () => { 222 const filter = createArray( 223 [ 224 createArray( 225 [createOperator('InstanceId', '=', 'I-123'), createOperator('Type', '!=', 'some-type')], 226 QueryEditorExpressionType.Or 227 ), 228 createArray( 229 [createOperator('InstanceId', '!=', 'I-456'), createOperator('Type', '!=', 'some-type')], 230 QueryEditorExpressionType.Or 231 ), 232 ], 233 QueryEditorExpressionType.Or 234 ); 235 assertQueryEndsWith( 236 { where: filter }, 237 `WHERE (InstanceId = 'I-123' OR Type != 'some-type') OR (InstanceId != 'I-456' OR Type != 'some-type')` 238 ); 239 }); 240 241 it('should handle three top level filters with one nested filters combined with OR', () => { 242 const filter = createArray( 243 [ 244 createArray([createOperator('InstanceId', '=', 'I-123')], QueryEditorExpressionType.Or), 245 createArray([createOperator('Type', '!=', 'some-type')], QueryEditorExpressionType.Or), 246 createArray([createOperator('InstanceId', '!=', 'I-456')], QueryEditorExpressionType.Or), 247 ], 248 QueryEditorExpressionType.Or 249 ); 250 assertQueryEndsWith( 251 { where: filter }, 252 `WHERE InstanceId = 'I-123' OR Type != 'some-type' OR InstanceId != 'I-456'` 253 ); 254 }); 255 256 it('should handle three top level filters with one nested filters combined with AND', () => { 257 const filter = createArray( 258 [ 259 createArray([createOperator('InstanceId', '=', 'I-123')], QueryEditorExpressionType.Or), 260 createArray([createOperator('Type', '!=', 'some-type')], QueryEditorExpressionType.Or), 261 createArray([createOperator('InstanceId', '!=', 'I-456')], QueryEditorExpressionType.Or), 262 ], 263 QueryEditorExpressionType.And 264 ); 265 assertQueryEndsWith( 266 { where: filter }, 267 `WHERE InstanceId = 'I-123' AND Type != 'some-type' AND InstanceId != 'I-456'` 268 ); 269 }); 270 }); 271 272 describe('group by', () => { 273 it('should not add GROUP BY clause in case its empty', () => { 274 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery })).not.toContain('GROUP BY'); 275 }); 276 it('should handle single label', () => { 277 const groupBy = createArray([createGroupBy('InstanceId')], QueryEditorExpressionType.And); 278 assertQueryEndsWith({ groupBy }, `GROUP BY InstanceId`); 279 }); 280 it('should handle multiple label', () => { 281 const groupBy = createArray( 282 [createGroupBy('InstanceId'), createGroupBy('Type'), createGroupBy('Group')], 283 QueryEditorExpressionType.And 284 ); 285 assertQueryEndsWith({ groupBy }, `GROUP BY InstanceId, Type, Group`); 286 }); 287 }); 288 289 describe('order by', () => { 290 it('should not add ORDER BY clause in case its empty', () => { 291 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery })).not.toContain('ORDER BY'); 292 }); 293 it('should handle SUM ASC', () => { 294 const orderBy = createFunction('SUM'); 295 assertQueryEndsWith({ orderBy, orderByDirection: 'ASC' }, `ORDER BY SUM() ASC`); 296 }); 297 298 it('should handle SUM ASC', () => { 299 const orderBy = createFunction('SUM'); 300 assertQueryEndsWith({ orderBy, orderByDirection: 'ASC' }, `ORDER BY SUM() ASC`); 301 }); 302 it('should handle COUNT DESC', () => { 303 const orderBy = createFunction('COUNT'); 304 assertQueryEndsWith({ orderBy, orderByDirection: 'DESC' }, `ORDER BY COUNT() DESC`); 305 }); 306 }); 307 describe('limit', () => { 308 it('should not add LIMIT clause in case its empty', () => { 309 expect(new SQLGenerator().expressionToSqlQuery({ ...baseQuery })).not.toContain('LIMIT'); 310 }); 311 312 it('should be added in case its specified', () => { 313 assertQueryEndsWith({ limit: 10 }, `LIMIT 10`); 314 }); 315 }); 316 317 describe('full query', () => { 318 it('should not add LIMIT clause in case its empty', () => { 319 let query: SQLExpression = { 320 select: createFunctionWithParameter('COUNT', ['DroppedBytes']), 321 from: createFunctionWithParameter('SCHEMA', ['AWS/MQ', 'InstanceId', 'Instance-Group']), 322 where: createArray( 323 [ 324 createArray( 325 [createOperator('InstanceId', '=', 'I-123'), createOperator('Type', '!=', 'some-type')], 326 QueryEditorExpressionType.Or 327 ), 328 createArray( 329 [createOperator('InstanceId', '!=', 'I-456'), createOperator('Type', '!=', 'some-type')], 330 QueryEditorExpressionType.Or 331 ), 332 ], 333 QueryEditorExpressionType.And 334 ), 335 groupBy: createArray([createGroupBy('InstanceId'), createGroupBy('InstanceType')]), 336 orderBy: createFunction('COUNT'), 337 orderByDirection: 'DESC', 338 limit: 100, 339 }; 340 expect(new SQLGenerator().expressionToSqlQuery(query)).toEqual( 341 `SELECT COUNT(DroppedBytes) FROM SCHEMA("AWS/MQ", InstanceId, "Instance-Group") WHERE (InstanceId = 'I-123' OR Type != 'some-type') AND (InstanceId != 'I-456' OR Type != 'some-type') GROUP BY InstanceId, InstanceType ORDER BY COUNT() DESC LIMIT 100` 342 ); 343 }); 344 }); 345 346 describe('using variables', () => { 347 const templateService = new TemplateSrv(); 348 templateService.init([metricVariable, namespaceVariable, labelsVariable, aggregationvariable]); 349 350 it('should interpolate variables correctly', () => { 351 let query: SQLExpression = { 352 select: createFunctionWithParameter('$aggregation', ['$metric']), 353 from: createFunctionWithParameter('SCHEMA', ['$namespace', '$labels']), 354 where: createArray( 355 [ 356 createArray( 357 [createOperator('InstanceId', '=', 'I-123'), createOperator('Type', '!=', 'some-type')], 358 QueryEditorExpressionType.Or 359 ), 360 createArray( 361 [createOperator('InstanceId', '!=', 'I-456'), createOperator('Type', '!=', 'some-type')], 362 QueryEditorExpressionType.Or 363 ), 364 ], 365 QueryEditorExpressionType.And 366 ), 367 groupBy: createArray([createGroupBy('$labels')]), 368 orderBy: createFunction('$aggregation'), 369 orderByDirection: 'DESC', 370 limit: 100, 371 }; 372 expect(new SQLGenerator(templateService).expressionToSqlQuery(query)).toEqual( 373 `SELECT $aggregation($metric) FROM SCHEMA(\"$namespace\", $labels) WHERE (InstanceId = 'I-123' OR Type != 'some-type') AND (InstanceId != 'I-456' OR Type != 'some-type') GROUP BY $labels ORDER BY $aggregation() DESC LIMIT 100` 374 ); 375 }); 376 }); 377}); 378