SQL Editor Getting Started

// Editor.tsx
import { SQLEditor } from '@tidbcloud/tisqleditor-react'
import { oneDark } from '@tidbcloud/codemirror-extension-themes'
import { curSqlGutter } from '@tidbcloud/codemirror-extension-cur-sql-gutter'
 
export const Editor = () => {
  return (
    <SQLEditor
      editorId="MySQLEditor"
      doc={"USE game;\n"}
      theme={oneDark}
      basicSetupOptions={{
        autocompletion: true
      }}
      extraExts={[
        curSqlGutter()
        // here you can add some other extensions as you need
      ]}
    />
  )
}

//Editor.tsx
import { useEffect, useRef } from 'react'
import { EditorState, Compartment } from '@codemirror/state'
import { EditorView } from '@codemirror/view'
import { basicSetup } from '@tidbcloud/codemirror-extension-basic-setup'
import { bbedit, oneDark } from '@tidbcloud/codemirror-extension-themes'
import { sql, MySQL } from '@codemirror/lang-sql'
 
const Editor = () => {
  const editorContainerRef = useRef<HTMLDivElement>(null)
  const sqlCompartment = new Compartment()
 
  useEffect(() => {
    const editorInst = new EditorView({
      state: EditorState.create({
        doc: "USE game;\n",
        extensions: [
          basicSetup({
            // set foldGutter: true will add a collapse/expand icon before the multi-line statement 
            foldGutter: false,
            foldKeymap: false,
            searchKeymap: true,
            autocompletion: true
          }),
          sqlCompartment.of(sql({
            dialect: MySQL,
            upperCaseKeywords: true
          })),
          oneDark
          // ------ Or ------
          // bbedit
        ]
      })
    })
 
    editorContainerRef.current?.appendChild(editorInst.dom)
 
    return () => {
      if (editorContainerRef.current && editorInst) {
        editorContainerRef.current.removeChild(editorInst.dom)
      }
    }
  }, [])
 
  return (
    <div ref={editorContainerRef}></div>
  )
}

import { SQLEditor } from '@tidbcloud/tisqleditor-react'
import { oneDark } from '@tidbcloud/codemirror-extension-themes'
import { curSqlGutter } from '@tidbcloud/codemirror-extension-cur-sql-gutter'
import { fullWidthCharLinter } from '@tidbcloud/codemirror-extension-linters'
import { sqlAutoCompletion } from '@tidbcloud/codemirror-extension-sql-autocomplete'
import { onSelectionChange, onDocChange, SelectionRange } from '@tidbcloud/codemirror-extension-events'
import { getNearbyStatement, getSqlStatements } from '@tidbcloud/codemirror-extension-sql-parser'
import { EditorView } from '@codemirror/view'
import { SQLConfig } from '@codemirror/lang-sql'
 
interface SchemaData {
  name: string
  tables: {
    name: string
    columns: {
      col: string
      data_type: string
      nullable?: boolean
    }[]
  }[]
}
 
/**
 * mock schema data
 */
const schema: SchemaData[] = [
  {
    "name": "game",
    "tables": [
      {
        "name": "all_audio_language",
        "columns": [
          {
            "col": "app_id",
            "data_type": "int",
            "nullable": false
          }
        ]
      },
      {
        "name": "category",
        "columns": [
          {
            "col": "category_id",
            "data_type": "int",
            "nullable": false
          }
        ]
      }
    ]            
  },
  {
    "name": "test",
    "tables": []
  }
]
 
/**
 * initial SQL contents
 */
const initialSQL = `USE game;
 
SELECT
  g.*
FROM
  game_genre gg
  LEFT JOIN games g ON g.app_id = gg.app_id
WHERE
  gg.genre_id = 9
ORDER BY
  g.estimated_owners DESC；`
 
interface ColumnItem {
  label: string
  type: string
}
 
/**
convert schema data to SQL config format that the auto-complete list can be displayed
when typing database/table/column name.
*/
const convertSchemaToSQLConfig = (dbList: SchemaData[]): SQLConfig => {
  const schema: {
    [key: string]: ColumnItem[]
  } = {}
  const tables: ColumnItem[] = []
 
  dbList.forEach((d) => {
    const db = d.name
    // allow you autocomplete when you type database name, 
    tables.push({
      label: db,
      type: 'database'
    })
    d.tables.forEach((t) => {
      const table = t.name
      tables.push({ label: table, type: 'table' })
 
      const columns = t.columns.map((c) => ({
        label: c.col,
        type: c.data_type
      }))
      tables.push(...columns)
 
      // allow you autocomplete when you type table, database.table name, 
      schema[`${db}.${table}`] = columns
      schema[table] = columns
    })
  })
 
  return { schema, tables }
}
 
const Editor = () => {
  const sqlConfig = convertSchemaToSQLConfig(schema)
 
  return (
    <SQLEditor
      editorId="mySQLEditor"
      doc={initialSQL}
      sqlConfig={sqlConfig}
      theme={oneDark}
      // ------ Or ------
      // theme={bbedit}
      basicSetupOptions={{
        foldGutter: true
      }}
      extraExts={[
        sqlAutoCompletion(),
        curSqlGutter(),
        fullWidthCharLinter(),
        onDocChange((view: EditorView, doc: string) => {
          // doc is all the current SQL content 
          console.log('doc change', doc)
        }),
        /**
        *  Triggered when your selection or cursor position changes,
        *  you can get the selection data from & to by the selRanges params,
        *  it is the cursor position if from equals to
        */
        onSelectionChange((view: EditorView, selRanges: SelectionRange[]) => {
          console.log('selection change', selRanges)
          console.log(getNearbyStatement(view.state, selRanges[0].from))
          console.log(getSqlStatements(view.state))
        })
      ]}
    />
  )
}