konnosDashboard
SqlEditor.Commands.tsx375 lines · main
1import type { PGColumn } from '@supabase/pg-meta'
2import { PermissionAction } from '@supabase/shared-types/out/constants'
3import { useParams } from 'common'
4import { AlertTriangle, Code, Loader2, Table2 } from 'lucide-react'
5import { useRouter } from 'next/navigation'
6import { useEffect, useMemo, useRef } from 'react'
7import { cn, CommandEmpty, CommandGroup, CommandItem, CommandList } from 'ui'
8import { CodeBlock } from 'ui-patterns/CodeBlock'
9import type { CommandOptions } from 'ui-patterns/CommandMenu'
10import {
11 Breadcrumb,
12 CommandHeader,
13 CommandMenuInput,
14 CommandWrapper,
15 escapeAttributeSelector,
16 generateCommandClassNames,
17 PageType,
18 useCommandFilterState,
19 useCommandMenuOpen,
20 useRegisterCommands,
21 useRegisterPage,
22 useSetCommandMenuSize,
23 useSetPage,
24} from 'ui-patterns/CommandMenu'
25
26import { COMMAND_MENU_SECTIONS } from '@/components/interfaces/App/CommandMenu/CommandMenu.utils'
27import { orderCommandSectionsByPriority } from '@/components/interfaces/App/CommandMenu/ordering'
28import { useSqlSnippetsQuery, type SqlSnippet } from '@/data/content/sql-snippets-query'
29import { usePrefetchTables, useTablesQuery, type TablesData } from '@/data/tables/tables-query'
30import { useAsyncCheckPermissions } from '@/hooks/misc/useCheckPermissions'
31import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'
32import { useProtectedSchemas } from '@/hooks/useProtectedSchemas'
33import { useProfile } from '@/lib/profile'
34
35export function useSqlEditorGotoCommands(options?: CommandOptions) {
36 let { ref } = useParams()
37 ref ||= '_'
38
39 useRegisterCommands(
40 COMMAND_MENU_SECTIONS.NAVIGATE,
41 [
42 {
43 id: 'nav-sql-editor',
44 name: 'SQL Editor',
45 route: `/project/${ref}/sql`,
46 defaultHidden: true,
47 },
48 ],
49 { ...options, deps: [ref] }
50 )
51}
52
53const SNIPPET_PAGE_NAME = 'Snippets'
54
55export function useSnippetCommands() {
56 const { data: project } = useSelectedProjectQuery()
57 const setPage = useSetPage()
58
59 useRegisterPage(
60 SNIPPET_PAGE_NAME,
61 {
62 type: PageType.Component,
63 component: () => <RunSnippetPage />,
64 },
65 { enabled: !!project }
66 )
67
68 useRegisterCommands(
69 COMMAND_MENU_SECTIONS.SQL,
70 [
71 {
72 id: 'run-snippet',
73 name: 'Run snippet...',
74 icon: () => <Code />,
75 action: () => setPage(SNIPPET_PAGE_NAME),
76 },
77 ],
78 {
79 enabled: !!project,
80 orderSection: orderCommandSectionsByPriority,
81 sectionMeta: { priority: 3 },
82 }
83 )
84}
85
86function RunSnippetPage() {
87 const { ref } = useParams()
88 const {
89 data: snippetPages,
90 isPending: isLoading,
91 isError,
92 isSuccess,
93 } = useSqlSnippetsQuery({
94 projectRef: ref,
95 })
96
97 const snippets = snippetPages?.pages.flatMap((page) => page.contents)
98
99 const { profile } = useProfile()
100 const { can: canCreateSQLSnippet } = useAsyncCheckPermissions(
101 PermissionAction.CREATE,
102 'user_content',
103 {
104 resource: { type: 'sql', owner_id: profile?.id },
105 subject: { id: profile?.id },
106 }
107 )
108
109 useSetCommandMenuSize('xlarge')
110
111 return (
112 <CommandWrapper>
113 <CommandHeader>
114 <Breadcrumb />
115 <CommandMenuInput autoFocus />
116 </CommandHeader>
117 {isLoading && <LoadingState />}
118 {isError && <ErrorState />}
119 {isSuccess && (!snippets || snippets.length === 0) && (
120 <EmptyState projectRef={ref} canCreateNew={canCreateSQLSnippet} />
121 )}
122 {isSuccess && !!snippets && snippets.length > 0 && (
123 <SnippetSelector projectRef={ref} canCreateNew={canCreateSQLSnippet} snippets={snippets} />
124 )}
125 </CommandWrapper>
126 )
127}
128
129function LoadingState() {
130 return (
131 <div className="p-6">
132 <p className="text-center">
133 <Loader2 className="inline-block mr-2 animate-spin" />
134 Loading...
135 </p>
136 </div>
137 )
138}
139
140function ErrorState() {
141 return (
142 <div className="p-6">
143 <p className="text-center">
144 <AlertTriangle className="inline-block mr-2" />
145 Couldn&apos;t load snippets
146 </p>
147 </div>
148 )
149}
150
151function EmptyState({
152 projectRef,
153 canCreateNew,
154}: {
155 projectRef: string | undefined
156 canCreateNew: boolean
157}) {
158 const router = useRouter()
159
160 return (
161 <div className="p-6">
162 <p className="mb-2 text-center">No snippets found.</p>
163 <CommandList className="py-2">
164 <CommandGroup>
165 <CommandItem
166 id="create-snippet"
167 className={generateCommandClassNames(false)}
168 onSelect={() => router.push(`/project/${projectRef ?? '_'}/sql/new`)}
169 >
170 {canCreateNew ? 'Create new snippet' : 'Run new SQL'}
171 </CommandItem>
172 </CommandGroup>
173 </CommandList>
174 </div>
175 )
176}
177
178function SnippetSelector({
179 projectRef,
180 snippets,
181 canCreateNew,
182}: {
183 projectRef: string | undefined
184 snippets: Array<SqlSnippet> | undefined
185 canCreateNew: boolean
186}) {
187 const router = useRouter()
188
189 const selectedValue = useCommandFilterState((state) => state.value)
190 const selectedSnippet = snippets?.find((snippet) => snippetValue(snippet) === selectedValue)
191 const isSQLSnippet = selectedSnippet?.type === 'sql'
192
193 return (
194 <div className="w-full grow min-h-0 grid gap-4 md:grid-cols-2">
195 <CommandList
196 className={cn(
197 'h-full! min-h-0 max-h-[unset] py-2 overflow-hidden',
198 '*:[[cmdk-list-sizer]]:h-full *:[[cmdk-list-sizer]]:flex *:[[cmdk-list-sizer]]:flex-col'
199 )}
200 >
201 {!!snippets && snippets.length > 0 && (
202 <CommandGroup className="grow min-h-0 overflow-auto">
203 {snippets.map((snippet) => (
204 <CommandItem
205 key={snippet.id}
206 id={`${snippet.id}-${snippet.name}`}
207 className={generateCommandClassNames(false)}
208 value={snippetValue(snippet)}
209 onSelect={() => void router.push(`/project/${projectRef ?? '_'}/sql/${snippet.id}`)}
210 >
211 {snippet.name}
212 </CommandItem>
213 ))}
214 </CommandGroup>
215 )}
216 {canCreateNew && (
217 <div className="min-h-fit grow-0">
218 <hr className="mt-4 mb-2 mx-2" />
219 <CommandGroup forceMount={true}>
220 <CommandItem
221 id="create-snippet"
222 className={generateCommandClassNames(false)}
223 onSelect={() => router.push(`/project/${projectRef ?? '_'}/sql/new`)}
224 forceMount={true}
225 >
226 Create new snippet
227 </CommandItem>
228 </CommandGroup>
229 </div>
230 )}
231 </CommandList>
232 <CodeBlock
233 language="sql"
234 value={isSQLSnippet ? selectedSnippet?.content?.unchecked_sql : ''}
235 wrapperClassName="hidden md:block"
236 className="w-full h-full border-0 [&>code]:overflow-scroll [&>code]:block [&>code]:w-full [&>code]:h-full"
237 hideCopy
238 />
239 </div>
240 )
241}
242
243function snippetValue(snippet: SqlSnippet) {
244 if (snippet.type !== 'sql') return ''
245 return escapeAttributeSelector(
246 `${snippet.id}-${snippet.name}-${snippet?.content?.unchecked_sql.slice(0, 30)}`
247 ).toLowerCase()
248}
249
250const QUERY_TABLE_PAGE_NAME = 'Query a table'
251
252export function useQueryTableCommands(options?: CommandOptions) {
253 const { data: project } = useSelectedProjectQuery()
254 const setPage = useSetPage()
255
256 const commandMenuOpen = useCommandMenuOpen()
257 const commandMenuPreviouslyOpen = useRef(commandMenuOpen)
258 const commandMenuJustOpened = commandMenuOpen && !commandMenuPreviouslyOpen.current
259 commandMenuPreviouslyOpen.current = commandMenuOpen
260
261 const prefetchTables = usePrefetchTables({
262 projectRef: project?.ref,
263 connectionString: project?.connectionString,
264 })
265 useEffect(() => {
266 if (project && commandMenuJustOpened) {
267 prefetchTables(undefined, true)
268 }
269 }, [project, prefetchTables, commandMenuJustOpened])
270
271 useRegisterPage(
272 QUERY_TABLE_PAGE_NAME,
273 {
274 type: PageType.Component,
275 component: TableSelector,
276 },
277 { enabled: !!project }
278 )
279
280 useRegisterCommands(
281 COMMAND_MENU_SECTIONS.SQL,
282 [
283 {
284 id: 'query-table',
285 name: 'Query a table...',
286 icon: () => <Table2 />,
287 action: () => setPage(QUERY_TABLE_PAGE_NAME),
288 },
289 ],
290 { ...options, enabled: (options?.enabled ?? true) && !!project }
291 )
292}
293
294function TableSelector() {
295 const router = useRouter()
296 const { data: project } = useSelectedProjectQuery()
297 const { data: protectedSchemas } = useProtectedSchemas()
298 const {
299 data: tablesData,
300 isPending: isLoading,
301 isError,
302 isSuccess,
303 } = useTablesQuery({
304 projectRef: project?.ref,
305 connectionString: project?.connectionString,
306 includeColumns: true,
307 })
308 const tables = useMemo(() => {
309 return tablesData?.filter((table) => !protectedSchemas.find((s) => s.name === table.schema))
310 }, [tablesData, protectedSchemas])
311
312 return (
313 <CommandWrapper>
314 <CommandHeader>
315 <Breadcrumb />
316 <CommandMenuInput autoFocus />
317 </CommandHeader>
318 <CommandList>
319 {isLoading && <LoadingState />}
320 {isError && <ErrorState />}
321 {isSuccess && (
322 <>
323 <CommandEmpty />
324 <CommandGroup>
325 {tables?.map((table) => (
326 <CommandItem
327 key={table.id}
328 className={generateCommandClassNames(false)}
329 value={escapeAttributeSelector(`${table.schema}.${table.name}`)}
330 onSelect={() => {
331 router.push(
332 `/project/${project?.ref ?? '_'}/sql/new?content=${encodeURIComponent(generateSelectStatement(table))}`
333 )
334 }}
335 >
336 {`${table.schema}.${table.name}`}
337 </CommandItem>
338 ))}
339 </CommandGroup>
340 </>
341 )}
342 </CommandList>
343 </CommandWrapper>
344 )
345}
346
347function generateSelectStatement(table: TablesData[number] & { columns?: Array<PGColumn> }) {
348 return `
349select ${
350 !table.columns
351 ? '*'
352 : `
353${table.columns.map((column) => `\t${column.name}`).join(',\n')}`
354 }
355from ${formatTableIdentifier(table)}
356-- where
357-- order by
358-- limit
359;
360 `.trim()
361}
362
363// Not a perfectly spec-compliant regex , since Postgres also allows non-Latin
364// letters and letters with diacritical marks, but quoting them defensively
365// is easier than writing the regex. ¯\_(ツ)_/¯
366const VALID_UNQUOTED_IDENTIFIER_REGEX = /^[a-z_][a-z0-9_$]*$/
367function formatTableIdentifier(table: TablesData[number]) {
368 const schema = VALID_UNQUOTED_IDENTIFIER_REGEX.test(table.schema)
369 ? table.schema
370 : `"${table.schema}"`
371 const tableName = VALID_UNQUOTED_IDENTIFIER_REGEX.test(table.name)
372 ? table.name
373 : `"${table.name}"`
374 return `${schema}.${tableName}`
375}