Skip to content

The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.

12.7 KB
Raw Blame
import type { PGColumn } from '@supabase/pg-meta'
import { useDebounce, useIntersectionObserver } from '@uidotdev/usehooks'
import { useParams } from 'common'
import { AlertTriangleIcon, CodeIcon, Loader2Icon, Table2Icon } from 'lucide-react'
import { useRouter } from 'next/navigation'
import { useEffect, useRef, useState } from 'react'
import { cn, CommandGroup, CommandItem, CommandList } from 'ui'
import { CodeBlock } from 'ui-patterns/CodeBlock'
import type { CommandOptions } from 'ui-patterns/CommandMenu'
import {
  Breadcrumb,
  CommandHeader,
  CommandMenuInput,
  CommandWrapper,
  escapeAttributeSelector,
  generateCommandClassNames,
  PageType,
  useCommandFilterState,
  useCommandMenuOpen,
  useRegisterCommands,
  useRegisterPage,
  useSetCommandMenuSize,
  useSetPage,
} from 'ui-patterns/CommandMenu'

import { COMMAND_MENU_SECTIONS } from '@/components/interfaces/App/CommandMenu/CommandMenu.utils'
import { orderCommandSectionsByPriority } from '@/components/interfaces/App/CommandMenu/ordering'
import { type SnippetWithContent } from '@/data/content/sql-folders-query'
import { useSqlSnippetsQuery } from '@/data/content/sql-snippets-query'
import {
  useInfiniteTablesQuery,
  usePrefetchTables,
  type TablesData,
} from '@/data/tables/tables-query'
import {
  FGA_PERMISSIONS,
  useAsyncCheckUserContentPermissions,
} from '@/hooks/misc/useCheckPermissionsV2'
import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'

export function useSqlEditorGotoCommands(options?: CommandOptions) {
  let { ref } = useParams()
  ref ||= '_'

  useRegisterCommands(
    COMMAND_MENU_SECTIONS.NAVIGATE,
    [
      {
        id: 'nav-sql-editor',
        name: 'SQL Editor',
        route: `/project/${ref}/sql`,
        defaultHidden: true,
      },
    ],
    { ...options, deps: [ref] }
  )
}

const SNIPPET_PAGE_NAME = 'Snippets'

export function useSnippetCommands() {
  const { data: project } = useSelectedProjectQuery()
  const setPage = useSetPage()

  useRegisterPage(
    SNIPPET_PAGE_NAME,
    {
      type: PageType.Component,
      component: () => <RunSnippetPage />,
    },
    { enabled: !!project }
  )

  useRegisterCommands(
    COMMAND_MENU_SECTIONS.SQL,
    [
      {
        id: 'run-snippet',
        name: 'Run snippet...',
        icon: () => <CodeIcon />,
        action: () => setPage(SNIPPET_PAGE_NAME),
      },
    ],
    {
      enabled: !!project,
      orderSection: orderCommandSectionsByPriority,
      sectionMeta: { priority: 3 },
    }
  )
}

function RunSnippetPage() {
  const { ref } = useParams()
  const {
    data: snippetPages,
    isPending: isLoading,
    isError,
    isSuccess,
  } = useSqlSnippetsQuery({
    projectRef: ref,
  })

  const snippets = snippetPages?.pages.flatMap((page) => page.contents)

  const { can: canCreateSQLSnippet } = useAsyncCheckUserContentPermissions(
    FGA_PERMISSIONS.PROJECT.SNIPPETS_WRITE,
    {
      mode: 'create',
      type: 'sql',
    }
  )

  useSetCommandMenuSize('xlarge')

  return (
    <CommandWrapper>
      <CommandHeader>
        <Breadcrumb />
        <CommandMenuInput autoFocus />
      </CommandHeader>
      {isLoading && <LoadingState />}
      {isError && <ErrorState />}
      {isSuccess && (!snippets || snippets.length === 0) && (
        <EmptyState projectRef={ref} canCreateNew={canCreateSQLSnippet} />
      )}
      {isSuccess && !!snippets && snippets.length > 0 && (
        <SnippetSelector projectRef={ref} canCreateNew={canCreateSQLSnippet} snippets={snippets} />
      )}
    </CommandWrapper>
  )
}

function LoadingState() {
  return (
    <div className="p-6">
      <p className="text-center">
        <Loader2Icon className="inline-block mr-2 animate-spin" />
        Loading...
      </p>
    </div>
  )
}

function ErrorState() {
  return (
    <div className="p-6">
      <p className="text-center">
        <AlertTriangleIcon className="inline-block mr-2" />
        Couldn&apos;t load snippets
      </p>
    </div>
  )
}

function EmptyState({
  projectRef,
  canCreateNew,
}: {
  projectRef: string | undefined
  canCreateNew: boolean
}) {
  const router = useRouter()

  return (
    <div className="p-6">
      <p className="mb-2 text-center">No snippets found.</p>
      <CommandList className="py-2">
        <CommandGroup>
          <CommandItem
            id="create-snippet"
            className={generateCommandClassNames(false)}
            onSelect={() => router.push(`/project/${projectRef ?? '_'}/sql/new`)}
          >
            {canCreateNew ? 'Create new snippet' : 'Run new SQL'}
          </CommandItem>
        </CommandGroup>
      </CommandList>
    </div>
  )
}

function SnippetSelector({
  projectRef,
  snippets,
  canCreateNew,
}: {
  projectRef: string | undefined
  snippets: Array<SnippetWithContent> | undefined
  canCreateNew: boolean
}) {
  const router = useRouter()

  const selectedValue = useCommandFilterState((state) => state.value)
  const selectedSnippet = snippets?.find((snippet) => snippetValue(snippet) === selectedValue)
  const isSQLSnippet = selectedSnippet?.type === 'sql'

  return (
    <div className="w-full grow min-h-0 grid gap-4 md:grid-cols-2">
      <CommandList
        className={cn(
          'h-full! min-h-0 max-h-[unset] py-2 overflow-hidden',
          '*:[[cmdk-list-sizer]]:h-full *:[[cmdk-list-sizer]]:flex *:[[cmdk-list-sizer]]:flex-col'
        )}
      >
        {!!snippets && snippets.length > 0 && (
          <CommandGroup className="grow min-h-0 overflow-auto">
            {snippets.map((snippet) => (
              <CommandItem
                key={snippet.id}
                id={`${snippet.id}-${snippet.name}`}
                className={generateCommandClassNames(false)}
                value={snippetValue(snippet)}
                onSelect={() => void router.push(`/project/${projectRef ?? '_'}/sql/${snippet.id}`)}
              >
                {snippet.name}
              </CommandItem>
            ))}
          </CommandGroup>
        )}
        {canCreateNew && (
          <div className="min-h-fit grow-0">
            <hr className="mt-4 mb-2 mx-2" />
            <CommandGroup forceMount={true}>
              <CommandItem
                id="create-snippet"
                className={generateCommandClassNames(false)}
                onSelect={() => router.push(`/project/${projectRef ?? '_'}/sql/new`)}
                forceMount={true}
              >
                Create new snippet
              </CommandItem>
            </CommandGroup>
          </div>
        )}
      </CommandList>
      <CodeBlock
        language="sql"
        value={isSQLSnippet ? selectedSnippet?.content?.unchecked_sql : ''}
        wrapperClassName="hidden md:block"
        className="w-full h-full border-0 [&>code]:overflow-scroll [&>code]:block [&>code]:w-full [&>code]:h-full"
        hideCopy
      />
    </div>
  )
}

function snippetValue(snippet: SnippetWithContent) {
  if (snippet.type !== 'sql') return ''
  return escapeAttributeSelector(
    `${snippet.id}-${snippet.name}-${snippet?.content?.unchecked_sql.slice(0, 30)}`
  ).toLowerCase()
}

const QUERY_TABLE_PAGE_NAME = 'Query a table'

export function useQueryTableCommands(options?: CommandOptions) {
  const { data: project } = useSelectedProjectQuery()
  const setPage = useSetPage()

  const commandMenuOpen = useCommandMenuOpen()
  // the ref prefetched for this open. unset until that prefetch runs, so a
  // menu that mounts already open still prefetches once the project exists.
  // a new ref prefetches while the menu stays open. cleared on close.
  const prefetchedProjectRef = useRef<string | undefined>(undefined)

  const prefetchTables = usePrefetchTables({
    projectRef: project?.ref,
    connectionString: project?.connectionString,
  })
  useEffect(() => {
    if (!commandMenuOpen) {
      prefetchedProjectRef.current = undefined
      return
    }

    const shouldPrefetch = prefetchedProjectRef.current !== project?.ref && !!project

    if (!shouldPrefetch) {
      return
    }

    prefetchedProjectRef.current = project?.ref
    prefetchTables(undefined, true)
  }, [commandMenuOpen, project, prefetchTables])

  useRegisterPage(
    QUERY_TABLE_PAGE_NAME,
    {
      type: PageType.Component,
      component: TableSelector,
    },
    { enabled: !!project }
  )

  useRegisterCommands(
    COMMAND_MENU_SECTIONS.SQL,
    [
      {
        id: 'query-table',
        name: 'Query a table...',
        icon: () => <Table2Icon />,
        action: () => setPage(QUERY_TABLE_PAGE_NAME),
      },
    ],
    { ...options, enabled: (options?.enabled ?? true) && !!project }
  )
}

function TableSelector() {
  const router = useRouter()
  const { data: project } = useSelectedProjectQuery()

  const [filterString, setFilterString] = useState('')
  const debouncedFilterString = useDebounce(filterString, 300)

  const [sentinelRef, sentinelEntry] = useIntersectionObserver({
    threshold: 0,
    rootMargin: '200px 0px 200px 0px',
  })

  const {
    data: tablesData,
    isError,
    isSuccess,
    isPending: isLoading,
    hasNextPage: hasNextTablesPage,
    isFetchingNextPage: isFetchingNextTablesPage,
    fetchNextPage: fetchNextTablesPage,
  } = useInfiniteTablesQuery({
    projectRef: project?.ref,
    connectionString: project?.connectionString,
    includeColumns: true,
    pageSize: 50,
    nameFilter: debouncedFilterString,
  })
  const tables = tablesData?.pages.flat() ?? []

  useEffect(() => {
    if (
      sentinelEntry?.isIntersecting &&
      hasNextTablesPage &&
      !isFetchingNextTablesPage &&
      isSuccess
    ) {
      fetchNextTablesPage()
    }
  }, [
    isSuccess,
    sentinelEntry?.isIntersecting,
    hasNextTablesPage,
    isFetchingNextTablesPage,
    fetchNextTablesPage,
  ])

  return (
    <CommandWrapper shouldFilter={false}>
      <CommandHeader>
        <Breadcrumb />
        <CommandMenuInput
          autoFocus
          placeholder="Search for schema or a table to query"
          value={filterString}
          onValueChange={setFilterString}
        />
      </CommandHeader>
      <CommandList className="pb-9">
        {isLoading && <LoadingState />}
        {isError && <ErrorState />}
        {isSuccess && (
          <>
            <CommandGroup className="py-2">
              {tables?.map((table) => (
                <CommandItem
                  key={table.id}
                  className={generateCommandClassNames(false)}
                  value={escapeAttributeSelector(`${table.schema}.${table.name}`)}
                  onSelect={() => {
                    router.push(
                      `/project/${project?.ref ?? '_'}/sql/new?content=${encodeURIComponent(generateSelectStatement(table))}`
                    )
                  }}
                >
                  {`${table.schema}.${table.name}`}
                </CommandItem>
              ))}
            </CommandGroup>
            {tables.length === 0 && debouncedFilterString && (
              <p className="text-xs text-center text-foreground-lighter py-3">
                No tables found based on your search
              </p>
            )}
            {tables.length > 0 && (
              <>
                <div ref={sentinelRef} />
                <div className="absolute bottom-0 left-0 right-0 flex items-center justify-between min-h-9 h-9 px-4 border-t bg-surface-200 text-xs text-foreground-light z-10">
                  <div className="flex items-center gap-x-2">
                    {isFetchingNextTablesPage ? (
                      <span className="flex items-center gap-2">
                        <Loader2Icon size={14} className="animate-spin" /> Loading...
                      </span>
                    ) : (
                      <span>
                        Total: {tables.length} {tables.length === 1 ? 'table' : 'tables'}
                        {hasNextTablesPage ? ' loaded' : ''}
                      </span>
                    )}
                  </div>
                </div>
              </>
            )}
          </>
        )}
      </CommandList>
    </CommandWrapper>
  )
}

function generateSelectStatement(table: TablesData[number] & { columns?: Array<PGColumn> }) {
  return `
select ${
    !table.columns
      ? '*'
      : `
${table.columns.map((column) => `\t${column.name}`).join(',\n')}`
  }
from ${formatTableIdentifier(table)}
-- where
-- order by
-- limit
;
  `.trim()
}

// Not a perfectly spec-compliant regex , since Postgres also allows non-Latin
// letters and letters with diacritical marks, but quoting them defensively
// is easier than writing the regex. ¯\_(ツ)_/¯
const VALID_UNQUOTED_IDENTIFIER_REGEX = /^[a-z_][a-z0-9_$]*$/
function formatTableIdentifier(table: TablesData[number]) {
  const schema = VALID_UNQUOTED_IDENTIFIER_REGEX.test(table.schema)
    ? table.schema
    : `"${table.schema}"`
  const tableName = VALID_UNQUOTED_IDENTIFIER_REGEX.test(table.name)
    ? table.name
    : `"${table.name}"`
  return `${schema}.${tableName}`
}