konnosDashboard
useQueryPerformanceQuery.test.ts221 lines · main
1import { describe, expect, it } from 'vitest'
2
3import { QueryPerformanceSort } from './QueryPerformance.types'
4import { generateQueryPerformanceSql } from './useQueryPerformanceQuery'
5
6describe('generateQueryPerformanceSql', () => {
7 it('generates sql with no filters', () => {
8 const result = generateQueryPerformanceSql({ preset: 'mostFrequentlyInvoked' })
9 expect(result.sql).toBeDefined()
10 expect(result.whereSql).toBe('')
11 expect(result.orderBySql).toBeUndefined()
12 })
13
14 it('generates ORDER BY clause', () => {
15 const result = generateQueryPerformanceSql({
16 preset: 'mostFrequentlyInvoked',
17 orderBy: { column: 'calls', order: 'desc' },
18 })
19 expect(result.orderBySql).toBe('ORDER BY calls desc')
20 })
21
22 it('filters by roles', () => {
23 const result = generateQueryPerformanceSql({
24 preset: 'mostFrequentlyInvoked',
25 roles: ['postgres', 'anon'],
26 })
27 expect(result.whereSql).toContain("auth.rolname in ('postgres', 'anon')")
28 })
29
30 it('filters by search query', () => {
31 const result = generateQueryPerformanceSql({
32 preset: 'mostFrequentlyInvoked',
33 searchQuery: 'SELECT',
34 })
35 expect(result.whereSql).toContain("statements.query ~* 'SELECT'")
36 })
37
38 it('filters by dashboard source only', () => {
39 const result = generateQueryPerformanceSql({
40 preset: 'mostFrequentlyInvoked',
41 sources: ['dashboard'],
42 })
43 expect(result.whereSql).toContain("statements.query ~* 'source: dashboard'")
44 })
45
46 it('filters by non-dashboard source only', () => {
47 const result = generateQueryPerformanceSql({
48 preset: 'mostFrequentlyInvoked',
49 sources: ['non-dashboard'],
50 })
51 expect(result.whereSql).toContain("statements.query !~* 'source: dashboard'")
52 })
53
54 it('does not add source filter when both sources are selected', () => {
55 const result = generateQueryPerformanceSql({
56 preset: 'mostFrequentlyInvoked',
57 sources: ['dashboard', 'non-dashboard'],
58 })
59 expect(result.whereSql).not.toContain('source: dashboard')
60 })
61
62 it('filters by minimum calls', () => {
63 const result = generateQueryPerformanceSql({
64 preset: 'mostFrequentlyInvoked',
65 minCalls: 10,
66 })
67 expect(result.whereSql).toContain('statements.calls >= 10')
68 })
69
70 it('does not filter by minCalls when 0', () => {
71 const result = generateQueryPerformanceSql({
72 preset: 'mostFrequentlyInvoked',
73 minCalls: 0,
74 })
75 expect(result.whereSql).not.toContain('statements.calls')
76 })
77
78 it('filters by minimum total time', () => {
79 const result = generateQueryPerformanceSql({
80 preset: 'mostFrequentlyInvoked',
81 minTotalTime: 500,
82 })
83 expect(result.whereSql).toContain(
84 '(statements.total_exec_time + statements.total_plan_time) >= 500'
85 )
86 })
87
88 it('combines multiple WHERE conditions with AND', () => {
89 const result = generateQueryPerformanceSql({
90 preset: 'mostFrequentlyInvoked',
91 roles: ['postgres'],
92 searchQuery: 'SELECT',
93 minCalls: 5,
94 })
95 expect(result.whereSql).toContain("auth.rolname in ('postgres')")
96 expect(result.whereSql).toContain("statements.query ~* 'SELECT'")
97 expect(result.whereSql).toContain('statements.calls >= 5')
98 expect(result.whereSql.split(' AND ').length).toBe(3)
99 })
100
101 it('passes WHERE clause to base sql function', () => {
102 const result = generateQueryPerformanceSql({
103 preset: 'mostFrequentlyInvoked',
104 roles: ['postgres'],
105 })
106 expect(result.sql).toContain('WHERE')
107 })
108
109 it('does not include user-defined WHERE conditions when no filters set', () => {
110 const result = generateQueryPerformanceSql({ preset: 'mostFrequentlyInvoked' })
111 expect(result.whereSql).toBe('')
112 // The base SQL may still contain its own WHERE clause (e.g. statements.calls > 0)
113 // but the user-defined whereSql should be empty
114 })
115
116 it('passes runIndexAdvisor and filterIndexAdvisor flags', () => {
117 const resultWithAdvisor = generateQueryPerformanceSql({
118 preset: 'mostFrequentlyInvoked',
119 runIndexAdvisor: true,
120 filterIndexAdvisor: true,
121 })
122 expect(resultWithAdvisor.sql).toBeDefined()
123 })
124
125 it('works with different presets', () => {
126 const presets = [
127 'mostFrequentlyInvoked',
128 'mostTimeConsuming',
129 'slowestExecutionTime',
130 'queryHitRate',
131 'unified',
132 ] as const
133
134 for (const preset of presets) {
135 const result = generateQueryPerformanceSql({ preset })
136 expect(result.sql).toBeDefined()
137 }
138 })
139
140 it('clamps page=0 to page=1 (no negative offset)', () => {
141 const result = generateQueryPerformanceSql({ preset: 'unified', page: 0, pageSize: 20 })
142 expect(result.sql).toContain('offset 0')
143 expect(result.sql).not.toMatch(/offset -\d/)
144 })
145
146 it('clamps negative page to page=1', () => {
147 const result = generateQueryPerformanceSql({ preset: 'unified', page: -5, pageSize: 20 })
148 expect(result.sql).toContain('offset 0')
149 })
150
151 it('clamps pageSize above 100 to 100', () => {
152 const result = generateQueryPerformanceSql({ preset: 'unified', page: 1, pageSize: 9999 })
153 expect(result.sql).toContain('limit 100')
154 expect(result.sql).not.toContain('limit 9999')
155 })
156
157 it('applies LIMIT and OFFSET for page 2', () => {
158 const result = generateQueryPerformanceSql({ preset: 'unified', page: 2, pageSize: 20 })
159 expect(result.sql).toContain('limit 20 offset 20')
160 })
161
162 it('does not produce NaN in SQL when page is NaN', () => {
163 const result = generateQueryPerformanceSql({ preset: 'unified', page: NaN, pageSize: 20 })
164 expect(result.sql).not.toContain('NaN')
165 expect(result.sql).toContain('offset 0')
166 })
167
168 it('does not produce NaN in SQL when pageSize is NaN', () => {
169 const result = generateQueryPerformanceSql({ preset: 'unified', page: 1, pageSize: NaN })
170 expect(result.sql).not.toContain('NaN')
171 expect(result.sql).toContain('limit 20')
172 })
173})
174
175describe('generateQueryPerformanceSql - ORDER BY column validation', () => {
176 it('rejects invalid orderBy column containing colon (old URL format)', () => {
177 const result = generateQueryPerformanceSql({
178 preset: 'mostFrequentlyInvoked',
179 orderBy: { column: 'created_at:asc' as any, order: 'desc' },
180 })
181 expect(result.orderBySql).toBeUndefined()
182 })
183
184 it('rejects SQL injection in orderBy column', () => {
185 const result = generateQueryPerformanceSql({
186 preset: 'mostFrequentlyInvoked',
187 orderBy: { column: 'calls; DROP TABLE users--' as any, order: 'asc' },
188 })
189 expect(result.orderBySql).toBeUndefined()
190 expect(result.sql).not.toContain('DROP TABLE')
191 })
192
193 it('falls back to default ORDER BY when column is invalid', () => {
194 const result = generateQueryPerformanceSql({
195 preset: 'mostFrequentlyInvoked',
196 orderBy: { column: 'created_at:asc' as any, order: 'asc' },
197 })
198 expect(result.sql).toContain('order by statements.calls desc')
199 })
200
201 it('accepts all valid sort columns', () => {
202 const columns: QueryPerformanceSort['column'][] = [
203 'query',
204 'rolname',
205 'total_time',
206 'prop_total_time',
207 'calls',
208 'avg_rows',
209 'max_time',
210 'mean_time',
211 'min_time',
212 ]
213 for (const column of columns) {
214 const result = generateQueryPerformanceSql({
215 preset: 'mostFrequentlyInvoked',
216 orderBy: { column, order: 'asc' },
217 })
218 expect(result.orderBySql).toBe(`ORDER BY ${column} asc`)
219 }
220 })
221})