QueryInsightsTable.utils.ts170 lines · main

1	import { formatDuration as formatDurationLong } from '../../QueryPerformance/QueryPerformance.utils'
2
3	export const formatDuration = (ms: number) => {
4	if (ms < 1000) {
5	return `${Math.round(ms)}ms`
6	}
7	return formatDurationLong(ms)
8	}
9
10	export const getQueryType = (query: string | undefined | null): string | null => {
11	if (!query) return null
12	const trimmed = query.trim()
13	const firstWord = trimmed.split(/\s+/)[0]?.toUpperCase()
14
15	const sqlTypes = [
16	'SELECT',
17	'INSERT',
18	'UPDATE',
19	'DELETE',
20	'CREATE',
21	'DROP',
22	'ALTER',
23	'TRUNCATE',
24	'WITH',
25	]
26
27	if (firstWord && sqlTypes.includes(firstWord)) {
28	return firstWord
29	}
30
31	return null
32	}
33
34	const cleanIdentifier = (identifier?: string): string | null => {
35	if (!identifier) return null
36	return (
37	identifier
38	.replace(/["`']/g, '')
39	.replace(/^[\w]+\./, '')
40	.trim() || null
41	)
42	}
43
44	export const getTableName = (query: string | undefined | null): string | null => {
45	if (!query) return null
46	const trimmed = query.trim()
47
48	let match = trimmed.match(
49	/FROM\s+(?:(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"[^"]+"|[\w]+)))/i
50	)
51	if (match?.groups?.table) {
52	return cleanIdentifier(match.groups.table)
53	}
54
55	match = trimmed.match(
56	/INSERT\s+INTO\s+(?:(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"[^"]+"|[\w]+)))/i
57	)
58	if (match?.groups?.table) {
59	return cleanIdentifier(match.groups.table)
60	}
61
62	match = trimmed.match(/UPDATE\s+(?:(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"[^"]+"|[\w]+)))/i)
63	if (match?.groups?.table) {
64	return cleanIdentifier(match.groups.table)
65	}
66
67	match = trimmed.match(
68	/DELETE\s+FROM\s+(?:(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"[^"]+"|[\w]+)))/i
69	)
70	if (match?.groups?.table) {
71	return cleanIdentifier(match.groups.table)
72	}
73
74	match = trimmed.match(
75	/CREATE\s+(?:TEMPORARY\s+|TEMP\s+|UNLOGGED\s+)?TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?:(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"[^"]+"|[\w]+)))/i
76	)
77	if (match?.groups?.table) {
78	return cleanIdentifier(match.groups.table)
79	}
80
81	match = trimmed.match(
82	/ALTER\s+TABLE\s+(?:(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"[^"]+"|[\w]+)))/i
83	)
84	if (match?.groups?.table) {
85	return cleanIdentifier(match.groups.table)
86	}
87
88	match = trimmed.match(
89	/DROP\s+TABLE\s+(?:IF\s+EXISTS\s+)?(?:(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"[^"]+"|[\w]+)))/i
90	)
91	if (match?.groups?.table) {
92	return cleanIdentifier(match.groups.table)
93	}
94
95	match = trimmed.match(
96	/TRUNCATE\s+(?:TABLE\s+)?(?:(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"[^"]+"|[\w]+)))/i
97	)
98	if (match?.groups?.table) {
99	return cleanIdentifier(match.groups.table)
100	}
101
102	if (trimmed.toUpperCase().startsWith('WITH')) {
103	match = trimmed.match(
104	/WITH\s+[\s\S]*?\s+FROM\s+(?:(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"[^"]+"|[\w]+)))/i
105	)
106	if (match?.groups?.table) {
107	return cleanIdentifier(match.groups.table)
108	}
109	}
110
111	return null
112	}
113
114	export const getColumnName = (query: string | undefined | null): string | null => {
115	if (!query) return null
116	const trimmed = query.trim()
117
118	let match = trimmed.match(
119	/WHERE\s+(?:(?<table>(?:"[^"]+"|[\w]+)\.)?(?<column>(?:"[^"]+"|[\w]+)))/i
120	)
121	if (match?.groups?.column) {
122	return cleanIdentifier(match?.groups?.column)
123	}
124
125	match = trimmed.match(
126	/SELECT\s+(?:\*\s+FROM|(?:(?<table>(?:"[^"]+"|[\w]+)\.)?(?<column>(?:"[^"]+"|[\w]+))(?:\s*,\s*[\w.]+)*)\s+FROM)/i
127	)
128	if (match?.groups?.column && match.groups.column.toUpperCase() !== '*') {
129	return cleanIdentifier(match.groups.column)
130	}
131
132	match = trimmed.match(
133	/ORDER\s+BY\s+(?:(?<table>(?:"[^"]+"|[\w]+)\.)?(?<column>(?:"[^"]+"|[\w]+)))/i
134	)
135	if (match?.groups?.column) {
136	return cleanIdentifier(match.groups.column)
137	}
138
139	match = trimmed.match(
140	/GROUP\s+BY\s+(?:(?<table>(?:"[^"]+"|[\w]+)\.)?(?<column>(?:"[^"]+"|[\w]+)))/i
141	)
142	if (match?.groups?.column) {
143	return cleanIdentifier(match.groups.column)
144	}
145
146	match = trimmed.match(
147	/UPDATE\s+[\w.]+\s+SET\s+(?:(?<table>(?:"[^"]+"|[\w]+)\.)?(?<column>(?:"[^"]+"|[\w]+)))/i
148	)
149	if (match?.groups?.column) {
150	return cleanIdentifier(match.groups.column)
151	}
152
153	match = trimmed.match(/INSERT\s+INTO\s+[\w.]+\s*\((?<column>(?:"[^"]+"|[\w]+))/i)
154	if (match?.groups?.column) {
155	return cleanIdentifier(match.groups.column)
156	}
157
158	return null
159	}
160
161	export const formatQueryDisplay = (
162	queryType: string | null,
163	tableName: string | null,
164	columnName: string | null
165	): string => {
166	const type = queryType ?? '–'
167	const table = tableName ?? '–'
168	const column = columnName ?? '–'
169	return `${type} in ${table}, ${column}`
170	}