konnosDashboard
index-advisor.utils.ts192 lines · main
1import { joinSqlFragments, safeSql, type SafeSqlFragment } from '@supabase/pg-meta'
2import { toast } from 'sonner'
3
4import { DatabaseExtension } from '@/data/database-extensions/database-extensions-query'
5import { GetIndexAdvisorResultResponse } from '@/data/database/retrieve-index-advisor-result-query'
6import { executeSql } from '@/data/sql/execute-sql-query'
7import { INTERNAL_SCHEMAS } from '@/hooks/useProtectedSchemas'
8
9/**
10 * Gets the required extensions for index advisor
11 * @param extensions Array of database extensions
12 * @returns Object containing hypopg, index_advisor, and test_extension extensions if they exist
13 */
14export function getIndexAdvisorExtensions(extensions: DatabaseExtension[] = []) {
15 const hypopg = extensions.find((ext) => ext.name === 'hypopg')
16 const indexAdvisor = extensions.find((ext) => ext.name === 'index_advisor')
17 return { hypopg, indexAdvisor }
18}
19
20/**
21 * Calculates the percentage improvement between before and after costs
22 *
23 * @param costBefore Cost before optimization
24 * @param costAfter Cost after optimization
25 * @returns Percentage improvement, or 0 if inputs are invalid
26 */
27export function calculateImprovement(
28 costBefore: number | undefined,
29 costAfter: number | undefined
30): number {
31 if (costBefore === undefined || costAfter === undefined) {
32 return 0
33 }
34
35 const before = Number(costBefore)
36 const after = Number(costAfter)
37
38 if (before <= 0 || before <= after) {
39 return 0
40 }
41
42 return ((before - after) / before) * 100
43}
44
45interface CreateIndexParams {
46 projectRef?: string
47 connectionString?: string | null
48 indexStatements: SafeSqlFragment[]
49 onSuccess?: () => void
50 onError?: (error: any) => void
51}
52
53/**
54 * Creates database indexes using the provided SQL statements
55 *
56 * @param params Parameters for index creation
57 * @returns Promise that resolves when the index creation completes
58 */
59export async function createIndexes({
60 projectRef,
61 connectionString,
62 indexStatements,
63 onSuccess,
64 onError,
65}: CreateIndexParams): Promise<void> {
66 if (!projectRef) {
67 const error = new Error('Project ref is required')
68 if (onError) onError(error)
69 return Promise.reject(error)
70 }
71
72 if (indexStatements.length === 0) {
73 const error = new Error('No index statements provided')
74 if (onError) onError(error)
75 return Promise.reject(error)
76 }
77
78 try {
79 await executeSql({
80 projectRef,
81 connectionString,
82 sql: safeSql`${joinSqlFragments(indexStatements, ';\n')};`,
83 })
84
85 toast.success('Successfully created index')
86 if (onSuccess) onSuccess()
87 return Promise.resolve()
88 } catch (error: any) {
89 toast.error(`Failed to create index: ${error.message}`)
90 if (onError) onError(error)
91 return Promise.reject(error)
92 }
93}
94
95/**
96 * Checks if the index advisor result contains recommendations
97 *
98 * @param result The index advisor result object
99 * @param isSuccess Whether the query was successful
100 * @returns Whether there are index recommendations available
101 */
102export function hasIndexRecommendations(
103 result: GetIndexAdvisorResultResponse | undefined | null,
104 isSuccess: boolean
105): boolean {
106 return Boolean(isSuccess && result?.index_statements && result.index_statements.length > 0)
107}
108
109/**
110 * Filters out index statements that reference protected schemas
111 * Index statements are typically in the format: "CREATE INDEX ON schema.table USING ..."
112 *
113 * @param indexStatements Array of index statement strings
114 * @returns Filtered array excluding statements referencing protected schemas
115 */
116export function filterProtectedSchemaIndexStatements(
117 indexStatements: SafeSqlFragment[]
118): SafeSqlFragment[] {
119 if (!indexStatements || indexStatements.length === 0) {
120 return []
121 }
122
123 return indexStatements.filter((statement) => {
124 // Match patterns like "CREATE INDEX ON schema.table" or "CREATE INDEX ON "schema"."table""
125 // Handle both quoted and unquoted schema names
126 const schemaMatch = statement.match(/ON\s+(?:"?(\w+)"?\.|(\w+)\.)/i)
127
128 if (!schemaMatch) {
129 // If we can't parse the schema, keep it (safer to show than hide)
130 return true
131 }
132
133 // Extract schema name (handle both quoted and unquoted)
134 const schemaName = schemaMatch[1] || schemaMatch[2]
135
136 if (!schemaName) {
137 return true
138 }
139
140 // Check if schema is in the protected schemas list
141 return !INTERNAL_SCHEMAS.includes(schemaName.toLowerCase())
142 })
143}
144
145/**
146 * Filters an index advisor result to remove recommendations for protected schemas
147 *
148 * @param result The index advisor result object
149 * @returns Filtered result with protected schema recommendations removed
150 */
151export function filterProtectedSchemaIndexAdvisorResult(
152 result: GetIndexAdvisorResultResponse | null | undefined
153): GetIndexAdvisorResultResponse | null {
154 if (!result || !result.index_statements) {
155 return result ?? null
156 }
157
158 const filteredStatements = filterProtectedSchemaIndexStatements(result.index_statements)
159
160 // If all statements were filtered out, return null
161 if (filteredStatements.length === 0) {
162 return null
163 }
164
165 return {
166 ...result,
167 index_statements: filteredStatements,
168 }
169}
170
171/**
172 * Checks if a query involves protected schemas by examining the SQL query text
173 *
174 * @param query The SQL query string
175 * @returns Whether the query involves protected schemas
176 */
177export function queryInvolvesProtectedSchemas(query: string | undefined | null): boolean {
178 if (!query) return false
179
180 const queryLower = query.toLowerCase()
181
182 // Check if the query references any protected schemas
183 // Match patterns like "schema.table", "FROM schema.table", "JOIN schema.table", etc.
184 return INTERNAL_SCHEMAS.some((schema) => {
185 // Match schema.table patterns (with or without quotes)
186 const schemaPattern = new RegExp(
187 `(?:from|join|update|insert\\s+into|delete\\s+from)\\s+(?:${schema}\\.|"${schema}"\\.)`,
188 'i'
189 )
190 return schemaPattern.test(queryLower)
191 })
192}