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