Repository: getindata/streaming-jupyter-integrations (language: python)

Issue:
Register path provided by jar providers

Release 0.3.0
Bump version and CHANGELOG for next release.


Discussion / hints:

Fix patch:
diff --git a/.eslintignore b/.eslintignore
new file mode 100644
index 0000000..b55afe9
--- /dev/null
+++ b/.eslintignore
@@ -0,0 +1,10 @@
+node_modules
+dist
+coverage
+**/*.d.ts
+tests
+
+**/__tests__
+ui-tests
+
+.eslintrc.js
\ No newline at end of file
diff --git a/.eslintrc.js b/.eslintrc.js
new file mode 100644
index 0000000..665374b
--- /dev/null
+++ b/.eslintrc.js
@@ -0,0 +1,39 @@
+module.exports = {
+  extends: [
+    'eslint:recommended',
+    'plugin:@typescript-eslint/eslint-recommended',
+    'plugin:@typescript-eslint/recommended',
+    'plugin:prettier/recommended'
+  ],
+  parser: '@typescript-eslint/parser',
+  parserOptions: {
+    project: 'tsconfig.json',
+    sourceType: 'module'
+  },
+  plugins: ['@typescript-eslint'],
+  rules: {
+    '@typescript-eslint/naming-convention': [
+      'error',
+      {
+        selector: 'interface',
+        format: ['PascalCase'],
+        custom: {
+          regex: '^I[A-Z]',
+          match: true
+        }
+      }
+    ],
+    '@typescript-eslint/no-unused-vars': ['warn', { args: 'none' }],
+    '@typescript-eslint/no-explicit-any': 'off',
+    '@typescript-eslint/no-namespace': 'off',
+    '@typescript-eslint/no-use-before-define': 'off',
+    '@typescript-eslint/quotes': [
+      'error',
+      'single',
+      { avoidEscape: true, allowTemplateLiterals: false }
+    ],
+    curly: ['error', 'all'],
+    eqeqeq: 'error',
+    'prefer-arrow-callback': 'error'
+  }
+};
diff --git a/.github/workflows/publish.yml b/.github/workflows/publish.yml
index affc121..081ba7c 100644
--- a/.github/workflows/publish.yml
+++ b/.github/workflows/publish.yml
@@ -22,7 +22,8 @@ jobs:
           python-version: ${{ matrix.python-version }}
       - name: Build package dist from source # A better way will be : https://packaging.python.org/guides/publishing-package-distribution-releases-using-github-actions-ci-cd-workflows/ but pep 517 is still marked as experimental
         run: |
-          python setup.py sdist
+          pip install build twine
+          python -m build
       - name:  Merge back to develop  # we have to set the config first on a fresh machine
         run: |
           git config user.name github-actions
diff --git a/.gitignore b/.gitignore
index 8192175..d9c745a 100644
--- a/.gitignore
+++ b/.gitignore
@@ -38,13 +38,19 @@ eggs/
 .eggs/
 lib/
 lib64/
+node-modules/
+node_modules/
 parts/
 sdist/
 var/
 wheels/
 *.egg-info/
+*.bundle.*
+.eslintcache
 .installed.cfg
 *.egg
+*.tsbuildinfo
+streaming_jupyter_integrations/labextension
 MANIFEST
 
 # PyInstaller
diff --git a/.gitlab-ci.yml b/.gitlab-ci.yml
deleted file mode 100644
index 86f4871..0000000
--- a/.gitlab-ci.yml
+++ /dev/null
@@ -1,54 +0,0 @@
-stages:
-  - build
-  - test
-  - ship
-
-image: python:3.8
-
-whl:
-  stage: build
-  before_script:
-    - pip install virtualenv
-    - virtualenv venv
-    - source venv/bin/activate
-    - pip install --upgrade pip
-  script:
-    - python setup.py sdist bdist_wheel
-  artifacts:
-    paths:
-      - dist/*
-
-pytest:
-  stage: test
-  before_script:
-    - pip install virtualenv
-    - virtualenv venv
-    - source venv/bin/activate
-    - pip install .[tests]
-  script:
-    - pre-commit run --all-files
-    - python -m pytest --cov streaming_jupyter_integrations --cov-report xml --cov-report term-missing --ignore=venv
-  cache:
-    paths:
-      - venv/*
-  except:
-    - tags
-
-pypi:
-  stage: ship
-  before_script:
-    - pip install virtualenv
-    - virtualenv venv
-    - source venv/bin/activate
-    - pip install twine
-  script:
-    - python setup.py verify
-    - TWINE_USERNAME="gitlab-ci-token" TWINE_PASSWORD="${CI_JOB_TOKEN}"
-      twine upload --repository-url ${CI_API_V4_URL}/projects/${CI_PROJECT_ID}/packages/pypi dist/*
-  cache:
-    paths:
-      - venv/*
-  needs:
-    - whl
-  rules:
-    - if: "$CI_COMMIT_TAG"
\ No newline at end of file
diff --git a/.pre-commit-config.yaml b/.pre-commit-config.yaml
index e42b5dc..335ea40 100644
--- a/.pre-commit-config.yaml
+++ b/.pre-commit-config.yaml
@@ -18,10 +18,10 @@ repos:
     - id: check-yaml
 
 - repo: https://github.com/pycqa/flake8
-  rev: 4.0.1
+  rev: 5.0.4
   hooks:
     - id: flake8
-      args: [--max-line-length=120, --exclude=streaming_jupyter_integrations/magics.py]
+      args: [--max-line-length=120]
       additional_dependencies: [
         'flake8-blind-except',
         'flake8-comprehensions',
@@ -29,9 +29,22 @@ repos:
       ]
 
 - repo: https://github.com/pre-commit/mirrors-mypy
-  rev: v0.961
+  rev: v0.971
   hooks:
     - id: mypy
       additional_dependencies:
         - 'types-requests'
-        - 'types-PyYAML'
\ No newline at end of file
+        - 'types-PyYAML'
+
+-   repo: https://github.com/pre-commit/mirrors-eslint
+    rev: 'v8.21.0'
+    hooks:
+    -   id: eslint
+        files: \.[jt]sx?$  # *.js, *.jsx, *.ts and *.tsx
+        types: [file]
+        additional_dependencies:
+          - "@typescript-eslint/eslint-plugin@5.32.0"
+          - "@typescript-eslint/parser@5.32.0"
+          - "eslint@7.14.0"
+          - "eslint-config-prettier@6.15.0"
+          - "eslint-plugin-prettier@3.1.4"
diff --git a/.prettierignore b/.prettierignore
new file mode 100644
index 0000000..732a0da
--- /dev/null
+++ b/.prettierignore
@@ -0,0 +1,5 @@
+node_modules
+**/node_modules
+**/lib
+**/package.json
+streaming_jupyter_integrations
diff --git a/.prettierrc b/.prettierrc
new file mode 100644
index 0000000..d0824a6
--- /dev/null
+++ b/.prettierrc
@@ -0,0 +1,6 @@
+{
+  "singleQuote": true,
+  "trailingComma": "none",
+  "arrowParens": "avoid",
+  "endOfLine": "auto"
+}
diff --git a/CHANGELOG.md b/CHANGELOG.md
index c777102..8dbec01 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -2,6 +2,18 @@
 
 ## [Unreleased]
 
+## [0.3.0] - 2022-08-22
+
+### Added
+
+-   Using init.sql as the initialization script.
+-   Use plugin to run jar providers on initialization.
+
+### Fixed
+
+-   Make SQL syntax highlighting available in JupyterLab.
+-   Make Magics recognize _DESCRIBE_ and _SHOW_ keywords as queries.
+
 ## [0.2.1] - 2022-08-02
 
 ### Changed
@@ -28,7 +40,9 @@
 
 -   First release
 
-[Unreleased]: https://github.com/getindata/streaming-jupyter-integrations/compare/0.2.1...HEAD
+[Unreleased]: https://github.com/getindata/streaming-jupyter-integrations/compare/0.3.0...HEAD
+
+[0.3.0]: https://github.com/getindata/streaming-jupyter-integrations/compare/0.2.1...0.3.0
 
 [0.2.1]: https://github.com/getindata/streaming-jupyter-integrations/compare/0.2.0...0.2.1
 
diff --git a/MANIFEST.in b/MANIFEST.in
index f9bd145..5901fad 100644
--- a/MANIFEST.in
+++ b/MANIFEST.in
@@ -1,1 +1,23 @@
+include LICENSE
+include *.md
 include requirements.txt
+
+include package.json
+include install.json
+include ts*.json
+include yarn.lock
+
+graft streaming_jupyter_integrations/labextension
+
+# Javascript files
+graft src
+prune **/node_modules
+prune lib
+prune binder
+
+# Patterns to exclude from any directory
+global-exclude *~
+global-exclude *.pyc
+global-exclude *.pyo
+global-exclude .git
+global-exclude .ipynb_checkpoints
diff --git a/README.md b/README.md
index 5c7b514..d20b080 100644
--- a/README.md
+++ b/README.md
@@ -8,10 +8,10 @@
 
 Streaming Jupyter Integrations project includes a set of magics for interactively running `Flink SQL`  jobs in [Jupyter](https://jupyter.org/) Notebooks
 
-In order to actually use these magics, you must install our PIP package:
+In order to actually use these magics, you must install our PIP package along `jupyterlab-lsp`:
 
 ```shell
-python3 -m pip install streaming-jupyter-integrations
+python3 -m pip install jupyterlab-lsp streaming-jupyter-integrations
 ```
 
 And then register in Jupyter with a running IPython in the first cell:
@@ -22,13 +22,27 @@ And then register in Jupyter with a running IPython in the first cell:
 
 ## Local development
 
-In local development, when streaming-jupyter-integrations repository is checkouted on local machine, you may install the most current version:
-
-```shell
-python3 -m pip install -e .[tests]
+Note: You will need NodeJS to build the extension package.
+
+The `jlpm` command is JupyterLab's pinned version of
+[yarn](https://yarnpkg.com/) that is installed with JupyterLab. You may use
+`yarn` or `npm` in lieu of `jlpm` below. In order to use `jlpm`, you have to
+have `jupyterlab` installed (e.g., by `brew install jupyterlab`, if you use
+Homebrew as your package manager).
+
+```bash
+# Clone the repo to your local environment
+# Change directory to the flink_sql_lsp_extension directory
+# Install package in development mode
+pip install -e .
+# Link your development version of the extension with JupyterLab
+jupyter labextension develop . --overwrite
+# Rebuild extension Typescript source after making changes
+jlpm build
 ```
 
-The project uses [pre-commit](https://pre-commit.com/) hooks to ensure code quality, mostly by linting. To use it, [install pre-commit](https://pre-commit.com/#install) and then run
+The project uses [pre-commit](https://pre-commit.com/) hooks to ensure code quality, mostly by linting.
+To use it, [install pre-commit](https://pre-commit.com/#install) and then run
 ```shell
 pre-commit install --install-hooks
 ```
diff --git a/install.json b/install.json
new file mode 100644
index 0000000..a5394a2
--- /dev/null
+++ b/install.json
@@ -0,0 +1,5 @@
+{
+  "packageManager": "python",
+  "packageName": "streaming_jupyter_integrations",
+  "uninstallInstructions": "Use your Python package manager (pip, conda, etc.) to uninstall the package streaming_jupyter_integrations"
+}
diff --git a/package.json b/package.json
new file mode 100644
index 0000000..c0bcb47
--- /dev/null
+++ b/package.json
@@ -0,0 +1,108 @@
+{
+  "name": "flink_sql_lsp_extension",
+  "version": "0.1.0",
+  "description": "SQL syntax highlighting for Flink SQL.",
+  "keywords": [
+    "jupyter",
+    "jupyterlab",
+    "jupyterlab-extension"
+  ],
+  "homepage": "https://github.com/getindata/streaming-jupyter-integrations",
+  "bugs": {
+    "url": "https://github.com/getindata/streaming-jupyter-integrations/issues"
+  },
+  "license": "BSD-3-Clause",
+  "author": {
+    "name": "GetInData",
+    "email": "office@getindata.com"
+  },
+  "files": [
+    "lib/**/*.{d.ts,eot,gif,html,jpg,js,js.map,json,png,svg,woff2,ttf}"
+  ],
+  "main": "lib/index.js",
+  "types": "lib/index.d.ts",
+  "repository": {
+    "type": "git",
+    "url": "https://github.com/getindata/streaming-jupyter-integrations.git"
+  },
+  "scripts": {
+    "build": "jlpm build:lib && jlpm build:labextension:dev",
+    "build:prod": "jlpm clean && jlpm build:lib && jlpm build:labextension",
+    "build:labextension": "jupyter labextension build .",
+    "build:labextension:dev": "jupyter labextension build --development True .",
+    "build:lib": "tsc",
+    "clean": "jlpm clean:lib",
+    "clean:lib": "rimraf lib tsconfig.tsbuildinfo",
+    "clean:lintcache": "rimraf .eslintcache",
+    "clean:labextension": "rimraf streaming_jupyter_integrations/labextension",
+    "clean:all": "jlpm clean:lib && jlpm clean:labextension && jlpm clean:lintcache",
+    "eslint": "jlpm eslint:check --fix",
+    "eslint:check": "eslint . --cache --ext .ts,.tsx",
+    "install:extension": "jlpm build",
+    "lint": "jlpm prettier && jlpm eslint",
+    "lint:check": "jlpm prettier:check && jlpm eslint:check",
+    "prettier": "jlpm prettier:base --write --list-different",
+    "prettier:base": "prettier \"**/*{.ts,.tsx,.js,.jsx,.css,.json,.md}\"",
+    "prettier:check": "jlpm prettier:base --check",
+    "watch": "run-p watch:src watch:labextension",
+    "watch:src": "tsc -w",
+    "watch:labextension": "jupyter labextension watch ."
+  },
+  "dependencies": {
+    "@jupyterlab/application": "~3.0.0",
+    "@jupyterlab/codemirror-extension": "^3.4.4",
+    "@jupyterlab/docmanager": "^3.0.0",
+    "@jupyterlab/logconsole": "^3.0.0",
+    "@krassowski/jupyterlab-lsp": "^3.7.0",
+    "lsp-ws-connection": "^0.6.0"
+  },
+  "peerDependencies": {
+    "codemirror": "~5.65.7"
+  },
+  "devDependencies": {
+    "@jupyterlab/builder": "^3.0.0",
+    "@types/codemirror": "^5.60.5",
+    "@typescript-eslint/eslint-plugin": "^5.32.0",
+    "@typescript-eslint/parser": "^5.32.0",
+    "codemirror": "~5.65.7",
+    "eslint": "^7.14.0",
+    "eslint-config-prettier": "^6.15.0",
+    "eslint-plugin-prettier": "^3.1.4",
+    "npm-run-all": "^4.1.5",
+    "prettier": "^2.1.1",
+    "rimraf": "^3.0.2",
+    "typescript": "~4.1.3"
+  },
+  "prettier": {
+    "singleQuote": true,
+    "printWidth": 88,
+    "proseWrap": "always"
+  },
+  "publishConfig": {
+    "access": "public"
+  },
+  "jupyterlab": {
+    "extension": true,
+    "outputDir": "streaming_jupyter_integrations/labextension",
+    "sharedPackages": {
+      "@krassowski/jupyterlab-lsp": {
+        "bundled": false,
+        "singleton": true
+      }
+    }
+  },
+  "jupyter-releaser": {
+    "hooks": {
+      "before-build-npm": [
+        "python -m pip install jupyterlab~=3.0",
+        "jlpm"
+      ],
+      "before-build-python": [
+        "jlpm clean:all"
+      ]
+    }
+  },
+  "resolutions": {
+    "vscode-languageserver-protocol": "3.16.0"
+  }
+}
diff --git a/pyproject.toml b/pyproject.toml
new file mode 100644
index 0000000..cc8d287
--- /dev/null
+++ b/pyproject.toml
@@ -0,0 +1,16 @@
+[build-system]
+requires = ["jupyter_packaging~=0.10,<2", "jupyterlab~=3.0"]
+build-backend = "jupyter_packaging.build_api"
+
+[tool.jupyter-packaging.options]
+ensured-targets = ["streaming_jupyter_integrations/labextension/package.json"]
+
+[tool.jupyter-packaging.builder]
+factory = "jupyter_packaging.npm_builder"
+
+[tool.jupyter-packaging.build-args]
+build_cmd = "build:prod"
+npm = ["jlpm"]
+
+[tool.check-manifest]
+ignore = ["streaming_jupyter_integrations/labextension/**", "yarn.lock", ".*", "package-lock.json"]
diff --git a/setup.cfg b/setup.cfg
index 1f0a66d..d58d791 100644
--- a/setup.cfg
+++ b/setup.cfg
@@ -1,5 +1,5 @@
 [bumpversion]
-current_version = 0.2.1
+current_version = 0.3.0
 
 [tox:tox]
 envlist = py38
diff --git a/setup.py b/setup.py
index c948945..e9eb985 100644
--- a/setup.py
+++ b/setup.py
@@ -1,12 +1,30 @@
 """streaming_jupyter_integrations module."""
+import json
 import os
 import sys
+from pathlib import Path
 from typing import List
 
 from setuptools import find_packages, setup
 from setuptools.command.install import install
 
-__version__ = "0.2.1"
+__version__ = "0.3.0"
+
+HERE = Path(__file__).parent.resolve()
+
+# Get the package info from package.json
+pkg_json = json.loads((HERE / "package.json").read_bytes())
+lab_path = (HERE / pkg_json["jupyterlab"]["outputDir"])
+
+# Representative files that should exist after a successful build
+ensured_targets = [str(lab_path / "package.json")]
+
+labext_name = pkg_json["name"]
+
+data_files_spec = [
+    (f"share/jupyter/labextensions/{labext_name}", str(lab_path.relative_to(HERE)), "**"),
+    (f"share/jupyter/labextensions/{labext_name}", str("."), "install.json"),
+]
 
 with open("README.md") as f:
     README = f.read()
@@ -42,31 +60,49 @@ def run(self) -> None:
         "pytest-cov>=2.8.0, <3.0.0",
         "pre-commit==2.15.0",
         "tox==3.21.1",
+        "jupyter-packaging>=0.12.2",
     ]
 }
 
-setup(
-    name="streaming_jupyter_integrations",
-    version=__version__,
-    description="JupyterNotebook Flink magics",
-    long_description=README,
-    long_description_content_type="text/markdown",
-    license="Apache Software License (Apache 2.0)",
-    license_files=("LICENSE",),
-    python_requires=">=3.8",
-    classifiers=[
+setup_args = {
+    'name': "streaming_jupyter_integrations",
+    'version': __version__,
+    'description': "JupyterNotebook Flink magics",
+    'long_description': README,
+    'long_description_content_type': "text/markdown",
+    'license': "Apache Software License (Apache 2.0)",
+    'license_files': ("LICENSE",),
+    'python_requires': ">=3.8",
+    'classifiers': [
         "Programming Language :: Python :: 3.8",
         "Operating System :: OS Independent",
     ],
-    keywords="jupyter flink sql ipython",
-    author=u"GetInData",
-    author_email="office@getindata.com",
-    url="https://github.com/getindata/streaming-jupyter-integrations",
-    packages=find_packages(exclude=["docs", "tests", "examples"]),
-    include_package_data=True,
-    install_requires=get_requirements("requirements.txt"),
-    extras_require=EXTRA_REQUIRE,
-    cmdclass={
+    'keywords': "jupyter flink sql ipython",
+    'author': u"GetInData",
+    'author_email': "office@getindata.com",
+    'url': "https://github.com/getindata/streaming-jupyter-integrations",
+    'packages': find_packages(exclude=["docs", "tests", "examples"]),
+    'include_package_data': True,
+    'install_requires': get_requirements("requirements.txt"),
+    'extras_require': EXTRA_REQUIRE,
+    'cmdclass': {
         'verify': VerifyVersionCommand,
     }
-)
+}
+
+try:
+    from jupyter_packaging import get_data_files, npm_builder, wrap_installers
+    post_develop = npm_builder(
+        build_cmd="install:extension", source_dir="src", build_dir=lab_path
+    )
+    setup_args["cmdclass"] = wrap_installers(post_develop=post_develop, ensured_targets=ensured_targets)
+    setup_args["data_files"] = get_data_files(data_files_spec)
+except ImportError as e:
+    import logging
+    logging.basicConfig(format="%(levelname)s: %(message)s")
+    logging.warning("Build tool `jupyter-packaging` is missing. Install it with pip or conda.")
+    if not ("--name" in sys.argv or "--version" in sys.argv):
+        raise e
+
+if __name__ == "__main__":
+    setup(**setup_args)
diff --git a/src/extractors.ts b/src/extractors.ts
new file mode 100644
index 0000000..9f2298d
--- /dev/null
+++ b/src/extractors.ts
@@ -0,0 +1,44 @@
+import {
+  IForeignCodeExtractorsRegistry,
+  RegExpForeignCodeExtractor,
+} from '@krassowski/jupyterlab-lsp';
+
+export const SQL_URL_PATTERN = '(?:(?:.*?)://(?:.*))';
+// note: -a/--connection_arguments and -f/--file are not supported yet
+const single_argument_options = [
+  '-x',
+  '--close',
+  '-c',
+  '--creator',
+  '-p',
+  '--persist',
+  '--append',
+];
+const zero_argument_options = ['-l', '--connections'];
+
+const COMMAND_PATTERN =
+  '(?:' +
+  (zero_argument_options.join('|') +
+    '|' +
+    single_argument_options.map((command) => command + ' \\w+').join('|')) +
+  ')';
+
+export const graphExtractors: IForeignCodeExtractorsRegistry = {
+  // general note: to match new lines use [^] instead of dot, unless the target is ES2018, then use /s
+  python: [
+    new RegExpForeignCodeExtractor({
+      language: 'sql',
+      pattern: `^%%flink_execute_sql(?: (?:${SQL_URL_PATTERN}|${COMMAND_PATTERN}|(?:\\w+ << )|(?:\\w+@\\w+)))?\n?((?:.+\n)?(?:[^]*))`,
+      foreign_capture_groups: [1],
+      is_standalone: true,
+      file_extension: 'sql',
+    }),
+    new RegExpForeignCodeExtractor({
+      language: 'sql',
+      pattern: `(?:^|\n)%flink_execute_sql (?:${SQL_URL_PATTERN}|${COMMAND_PATTERN}|(.*))\n?`,
+      foreign_capture_groups: [1],
+      is_standalone: false,
+      file_extension: 'sql',
+    }),
+  ],
+};
diff --git a/src/index.ts b/src/index.ts
new file mode 100644
index 0000000..191c3b1
--- /dev/null
+++ b/src/index.ts
@@ -0,0 +1,72 @@
+import { JupyterFrontEnd, JupyterFrontEndPlugin } from '@jupyterlab/application';
+import { ICodeMirror } from '@jupyterlab/codemirror';
+import { ILSPCodeExtractorsManager } from '@krassowski/jupyterlab-lsp';
+import { graphExtractors } from './extractors';
+
+const FLAG_OPTS = [
+  '-c',
+  '--cache', //          Cache dataframe
+  '-e',
+  '--eager', //          Cache dataframe with eager load
+  '-q',
+  '--qgrid', //          Display results in qgrid
+].join('|');
+const SPACE = ' ';
+const OPTION_VALUE = '[0-9a-zA-Z/._]+';
+const SHORT_OPTS = `-[a-z] ${OPTION_VALUE}`;
+const LONG_OPTS = `--[a-zA-Z]+ ${OPTION_VALUE}`;
+const COMMANDS = `(?:${SPACE}|${FLAG_OPTS}|${SHORT_OPTS}|${LONG_OPTS})*`;
+
+// Based on https://github.com/jupyterlab/jupyterlab/blob/1.0.x/packages/codemirror/src/codemirror-ipython.ts
+// and https://github.com/CybercentreCanada/jupyterlab-sql-editor/blob/6aa0e515747dd84425374101bbb0198048664586/src/index.ts
+function codeMirrorWithSqlSyntaxHighlightSupport(c: ICodeMirror) {
+  c.CodeMirror.defineMode(
+    'ipython',
+    (config: CodeMirror.EditorConfiguration, modeOptions?: any) => {
+      const pythonConf: any = {};
+      for (const prop in modeOptions) {
+        if (Object.prototype.hasOwnProperty.call(modeOptions, prop)) {
+          pythonConf[prop] = modeOptions[prop];
+        }
+      }
+      pythonConf.name = 'python';
+      pythonConf.singleOperators = new RegExp('^[\\+\\-\\*/%&|@\\^~<>!\\?]');
+      pythonConf.identifiers = new RegExp(
+        '^[_A-Za-z\u00A1-\uFFFF][_A-Za-z0-9\u00A1-\uFFFF]*'
+      );
+
+      const pythonMode = c.CodeMirror.getMode(config, pythonConf);
+      const sqlMode = c.CodeMirror.getMode(config, 'sql');
+      return c.CodeMirror.multiplexingMode(pythonMode, {
+        open: RegExp(`%%flink_execute_sql${COMMANDS}`) as unknown as string,
+        close: '__A MARKER THAT WILL NEVER BE MATCHED__', // Cell magic: capture chars till the end of the cell
+        parseDelimiters: false,
+        mode: sqlMode,
+      });
+    }
+  );
+}
+
+/**
+ * Initialization data for the flink_sql_lsp_extension extension.
+ */
+const plugin: JupyterFrontEndPlugin<void> = {
+  id: 'flink_sql_lsp_extension:plugin',
+  requires: [ICodeMirror, ILSPCodeExtractorsManager],
+  autoStart: true,
+  activate: (
+    app: JupyterFrontEnd,
+    cm: ICodeMirror,
+    codeExtractors: ILSPCodeExtractorsManager
+  ) => {
+    codeMirrorWithSqlSyntaxHighlightSupport(cm);
+
+    for (const [language, extractors] of Object.entries(graphExtractors)) {
+      for (const extractor of extractors) {
+        codeExtractors.register(extractor, language);
+      }
+    }
+  },
+};
+
+export default plugin;
diff --git a/streaming_jupyter_integrations/__init__.py b/streaming_jupyter_integrations/__init__.py
index e69de29..fcc5253 100644
--- a/streaming_jupyter_integrations/__init__.py
+++ b/streaming_jupyter_integrations/__init__.py
@@ -0,0 +1,15 @@
+import json
+from pathlib import Path
+from typing import Dict, List
+
+HERE = Path(__file__).parent.resolve()
+
+with (HERE / "labextension" / "package.json").open() as fid:
+    data = json.load(fid)
+
+
+def _jupyter_labextension_paths() -> List[Dict[str, str]]:
+    return [{
+        "src": "labextension",
+        "dest": data["name"]
+    }]
diff --git a/streaming_jupyter_integrations/examples/basic.ipynb b/streaming_jupyter_integrations/examples/basic.ipynb
deleted file mode 100644
index f1cc87b..0000000
--- a/streaming_jupyter_integrations/examples/basic.ipynb
+++ /dev/null
@@ -1,81 +0,0 @@
-{
- "cells": [
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "b52c76df",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%reload_ext streamingcli.jupyter.integrations"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "c99e3966",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%flink_execute_sql\n",
-    "CREATE TABLE datagen (\n",
-    "    id INT\n",
-    ") WITH (\n",
-    "    'connector' = 'datagen',\n",
-    "    'number-of-rows' = '10'\n",
-    ")"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "97617672",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%flink_execute_sql\n",
-    "SHOW TABLES"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "1fbb19f0",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%flink_query_sql\n",
-    "SELECT * FROM datagen"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "ce9036f2",
-   "metadata": {},
-   "outputs": [],
-   "source": []
-  }
- ],
- "metadata": {
-  "kernelspec": {
-   "display_name": "Python 3 (ipykernel)",
-   "language": "python",
-   "name": "python3"
-  },
-  "language_info": {
-   "codemirror_mode": {
-    "name": "ipython",
-    "version": 3
-   },
-   "file_extension": ".py",
-   "mimetype": "text/x-python",
-   "name": "python",
-   "nbconvert_exporter": "python",
-   "pygments_lexer": "ipython3",
-   "version": "3.8.3"
-  }
- },
- "nbformat": 4,
- "nbformat_minor": 5
-}
diff --git a/streaming_jupyter_integrations/examples/basic_with_variables.ipynb b/streaming_jupyter_integrations/examples/basic_with_variables.ipynb
deleted file mode 100644
index abe1e6d..0000000
--- a/streaming_jupyter_integrations/examples/basic_with_variables.ipynb
+++ /dev/null
@@ -1,95 +0,0 @@
-{
- "cells": [
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "b52c76df",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%reload_ext streamingcli.jupyter.integrations"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "outputs": [],
-   "source": [
-    "number_of_rows = 10"
-   ],
-   "metadata": {
-    "collapsed": false,
-    "pycharm": {
-     "name": "#%%\n"
-    }
-   }
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "c99e3966",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%flink_execute_sql\n",
-    "CREATE TABLE datagen (\n",
-    "    id INT\n",
-    ") WITH (\n",
-    "    'connector' = 'datagen',\n",
-    "    'number-of-rows' = '{ number_of_rows }'\n",
-    ")"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "97617672",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%flink_execute_sql\n",
-    "SHOW TABLES"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "1fbb19f0",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%flink_execute_sql\n",
-    "SELECT * FROM datagen"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "ce9036f2",
-   "metadata": {},
-   "outputs": [],
-   "source": []
-  }
- ],
- "metadata": {
-  "kernelspec": {
-   "display_name": "Python 3 (ipykernel)",
-   "language": "python",
-   "name": "python3"
-  },
-  "language_info": {
-   "codemirror_mode": {
-    "name": "ipython",
-    "version": 3
-   },
-   "file_extension": ".py",
-   "mimetype": "text/x-python",
-   "name": "python",
-   "nbconvert_exporter": "python",
-   "pygments_lexer": "ipython3",
-   "version": "3.8.3"
-  }
- },
- "nbformat": 4,
- "nbformat_minor": 5
-}
\ No newline at end of file
diff --git a/streaming_jupyter_integrations/examples/udf.ipynb b/streaming_jupyter_integrations/examples/udf.ipynb
deleted file mode 100644
index eedda59..0000000
--- a/streaming_jupyter_integrations/examples/udf.ipynb
+++ /dev/null
@@ -1,116 +0,0 @@
-{
- "cells": [
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "56c1b557",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%reload_ext streamingcli.jupyter.integrations"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "914bea5f",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%flink_execute_sql\n",
-    "CREATE TABLE datagen (\n",
-    "    id INT\n",
-    ") WITH (\n",
-    "    'connector' = 'datagen',\n",
-    "    'number-of-rows' = '10'\n",
-    ")"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "2bbb7bce",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "## Writing to a `filename.txt` while filtering with UDF"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "a54cdac0",
-   "metadata": {
-    "tags": [
-     "udf"
-    ]
-   },
-   "outputs": [],
-   "source": [
-    "from pyflink.table.udf import udf\n",
-    "from pyflink.table import DataTypes\n",
-    "import sys\n",
-    "\n",
-    "@udf(result_type=DataTypes.BOOLEAN())\n",
-    "def filter_print(condition, message):\n",
-    "    with open('filename.txt', 'a+') as f:\n",
-    "        print(f'{message}', file=f)\n",
-    "    return condition"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "0641c4a7",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%flink_register_function --function_name filter_print --object_name filter_print "
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "8436640d",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "%%flink_query_sql\n",
-    "select * from datagen WHERE filter_print(true, id)"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "6938d1bc",
-   "metadata": {},
-   "outputs": [],
-   "source": []
-  }
- ],
- "metadata": {
-  "celltoolbar": "Tags",
-  "interpreter": {
-   "hash": "5e298b3215ee7f4e95b1172e93deec786f1c8a888c2b24d9520fb978679fd487"
-  },
-  "kernelspec": {
-   "display_name": "Python 3",
-   "language": "python",
-   "name": "python3"
-  },
-  "language_info": {
-   "codemirror_mode": {
-    "name": "ipython",
-    "version": 3
-   },
-   "file_extension": ".py",
-   "mimetype": "text/x-python",
-   "name": "python",
-   "nbconvert_exporter": "python",
-   "pygments_lexer": "ipython3",
-   "version": "3.8.12"
-  }
- },
- "nbformat": 4,
- "nbformat_minor": 5
-}
diff --git a/streaming_jupyter_integrations/magics.py b/streaming_jupyter_integrations/magics.py
index c4c32a6..1dfbad9 100644
--- a/streaming_jupyter_integrations/magics.py
+++ b/streaming_jupyter_integrations/magics.py
@@ -1,10 +1,11 @@
-from __future__ import print_function
+from __future__ import annotations, print_function
 
 import asyncio
 import os
 import signal
+import sys
 from functools import wraps
-from typing import Any, Callable, Dict, Iterable, Tuple
+from typing import Any, Callable, Dict, Iterable, Tuple, Union
 
 import nest_asyncio
 import pandas as pd
@@ -30,7 +31,7 @@
 from .jar_handler import JarHandler
 from .reflection import get_method_names_for
 from .sql_syntax_highlighting import SQLSyntaxHighlighting
-from .sql_utils import inline_sql_in_cell, is_dml, is_query
+from .sql_utils import inline_sql_in_cell, is_dml, is_dql
 from .variable_substitution import CellContentFormatter
 
 
@@ -52,16 +53,15 @@ def __init__(self, shell: Any):
         conf.set_integer("rest.port", 8099)
         conf.set_integer("parallelism.default", 1)
         self.s_env = StreamExecutionEnvironment(
-            get_gateway().jvm.org.apache.flink.streaming.api.environment.StreamExecutionEnvironment.createLocalEnvironmentWithWebUI(
+            get_gateway().jvm.org.apache.flink.streaming.api.environment.StreamExecutionEnvironment.createLocalEnvironmentWithWebUI(  # noqa: E501
                 conf._j_configuration
             )
         )
         self.st_env = StreamTableEnvironment.create(
             stream_execution_environment=self.s_env,
-            environment_settings=EnvironmentSettings.new_instance()
-                .in_streaming_mode()
-                .build(),
+            environment_settings=EnvironmentSettings.new_instance().in_streaming_mode().build(),
         )
+        self.__load_plugins()
         self.interrupted = False
         self.polling_ms = 100
         # 20ms
@@ -73,6 +73,7 @@ def __init__(self, shell: Any):
         self.jar_handler = JarHandler(project_root_dir=os.getcwd())
         # Enables nesting blocking async tasks
         nest_asyncio.apply()
+        self.__flink_execute_sql_file("init.sql")
 
     @line_magic
     @magic_arguments()
@@ -108,21 +109,12 @@ def _impl(self: 'Integrations', *method_args: Tuple[Any], **method_kwargs: Dict[
         "-p", "--path", type=str, help="A path to a local config file", required=True
     )
     def flink_execute_sql_file(self, line: str) -> None:
-        if self.background_execution_in_progress:
-            self.__retract_user_as_something_is_executing_in_background()
-            return
-
         args = parse_argstring(self.flink_execute_sql_file, line)
         path = args.path
-        with open(path, "r") as f:
-            statements = map(lambda s: self.__enrich_cell(s.rstrip(';')), sqlparse.split(f.read()))
-
-        self.background_execution_in_progress = True
-        self.deployment_bar.show_deployment_bar()
-        try:
-            self.__flink_execute_sql_file_internal(statements)
-        finally:
-            self.background_execution_in_progress = False
+        if os.path.exists(path):
+            self.__flink_execute_sql_file(path)
+        else:
+            print("File {} not found".format(path))
 
     @__interrupt_signal_decorator
     def __flink_execute_sql_file_internal(self, statements: Iterable[str]) -> None:
@@ -144,7 +136,7 @@ def flink_execute_sql(self, line: str, cell: str) -> None:
     @__interrupt_signal_decorator
     def __flink_execute_sql_internal(self, stmt: str) -> None:
         task = self.__internal_execute_sql(stmt)
-        if is_dml(stmt) or is_query(stmt):
+        if is_dml(stmt) or is_dql(stmt):
             print(
                 "This job runs in a background, please either wait or interrupt its execution before continuing"
             )
@@ -171,7 +163,7 @@ async def __internal_execute_sql(self, stmt: str) -> None:
                 # In Jupyter's main execution pool there is only one worker thread.
                 await asyncio.sleep(self.async_wait_s)
                 execution_result.wait(self.polling_ms)
-                if is_query(stmt):
+                if is_dql(stmt):
                     # if a select query has been executing then `wait` returns as soon as the first
                     # row is available. To display the results
                     print("Pulling query results...")
@@ -286,21 +278,7 @@ def flink_register_jar(self, line: str) -> None:
                 "Please specify either a local or remote path, use `%flink_register_jar?` for help."
             )
 
-        pipeline_classpaths = "pipeline.classpaths"
-        current_classpaths = (
-            self.st_env.get_config()
-                .get_configuration()
-                .get_string(pipeline_classpaths, "")
-        )
-        new_classpath = (
-            f"{current_classpaths};{classpath_to_add}"
-            if len(current_classpaths) > 0
-            else classpath_to_add
-        )
-        self.st_env.get_config().get_configuration().set_string(
-            pipeline_classpaths, new_classpath
-        )
-        print(f"Jar {classpath_to_add} registered")
+        self.__extend_classpath(classpath_to_add)
 
     @line_magic
     @magic_arguments()
@@ -369,6 +347,53 @@ def __enrich_cell(self, cell: str) -> str:
         joined_cell = inline_sql_in_cell(enriched_cell)
         return joined_cell
 
+    def __flink_execute_sql_file(self, path: Union[str, os.PathLike[str]]) -> None:
+        if self.background_execution_in_progress:
+            self.__retract_user_as_something_is_executing_in_background()
+            return
+
+        if os.path.exists(path):
+            with open(path, "r") as f:
+                statements = [self.__enrich_cell(s.rstrip(';')) for s in sqlparse.split(f.read())]
+        else:
+            return
+
+        self.background_execution_in_progress = True
+        self.deployment_bar.show_deployment_bar()
+        try:
+            self.__flink_execute_sql_file_internal(statements)
+        finally:
+            self.background_execution_in_progress = False
+
+    def __extend_classpath(self, classpath_to_add: str) -> None:
+        pipeline_classpaths = "pipeline.classpaths"
+        current_classpaths = (
+            self.st_env.get_config()
+                .get_configuration()
+                .get_string(pipeline_classpaths, "")
+        )
+        new_classpath = (
+            f"{current_classpaths};{classpath_to_add}"
+            if len(current_classpaths) > 0
+            else classpath_to_add
+        )
+        self.st_env.get_config().get_configuration().set_string(
+            pipeline_classpaths, new_classpath
+        )
+        for jar in classpath_to_add.split(";"):
+            print(f"Jar {jar} registered")
+
+    def __load_plugins(self) -> None:
+        if sys.version_info < (3, 10):
+            from importlib_metadata import entry_points
+        else:
+            from importlib.metadata import entry_points
+        for jar_provider in entry_points(group='catalog.jars.provider'):
+            provider_f = jar_provider.load()
+            classpath_to_add = provider_f()
+            if classpath_to_add:
+                self.__extend_classpath(classpath_to_add)
+
     @staticmethod
     def __retract_user_as_something_is_executing_in_background() -> None:
         print("Please wait for the previously submitted task to finish or cancel it.")
diff --git a/streaming_jupyter_integrations/sql_utils.py b/streaming_jupyter_integrations/sql_utils.py
index 4b82e40..190de86 100644
--- a/streaming_jupyter_integrations/sql_utils.py
+++ b/streaming_jupyter_integrations/sql_utils.py
@@ -1,3 +1,36 @@
+from typing import Iterable
+
+import sqlparse
+
+DDL_KEYWORDS = {
+    'CREATE',
+    'DROP',
+    'ALTER',
+    'USE',
+    'LOAD',
+    'UNLOAD'
+}
+
+QUERY_KEYWORDS = {
+    'VALUES',
+    'WITH',  # WITH ... query
+    'SELECT'
+}
+
+DQL_KEYWORDS = {
+    *QUERY_KEYWORDS,
+    'DESCRIBE',
+    'DESC',
+    'EXPLAIN',
+    'SHOW'
+}
+
+DML_KEYWORDS = {
+    "INSERT",
+    "EXECUTE"
+}
+
+
 def inline_sql_in_cell(cell_contents: str) -> str:
     """
     Converts cell contents to a single line SQL statement.
@@ -5,21 +38,22 @@ def inline_sql_in_cell(cell_contents: str) -> str:
     return cell_contents.replace("\n", " ")
 
 
+def is_ddl(sql: str) -> bool:
+    return __first_token_is_keyword(sql, DDL_KEYWORDS)
+
+
 def is_dml(sql: str) -> bool:
-    valid_dml_statements = [
-        "insert",
-        "update",
-        "delete",
-        "lock",
-        "call",
-        "explain",
-        "create",
-    ]
-    for dml in valid_dml_statements:
-        if dml in sql.lower():
-            return True
-    return False
-
-
-def is_query(sql: str) -> bool:
-    return (not is_dml(sql)) and "select" in sql.lower()
+    return __first_token_is_keyword(sql, DML_KEYWORDS)
+
+
+def is_dql(sql: str) -> bool:
+    return __first_token_is_keyword(sql, DQL_KEYWORDS)
+
+
+def __first_token_is_keyword(sql: str, keywords: Iterable[str]) -> bool:
+    if not sql or not sql.strip():
+        return False
+
+    parsed = sqlparse.parse(sql.upper())[0]
+    first_token = parsed.token_first()
+    return first_token.value in keywords
diff --git a/tsconfig.json b/tsconfig.json
new file mode 100644
index 0000000..b9c465a
--- /dev/null
+++ b/tsconfig.json
@@ -0,0 +1,24 @@
+{
+  "compilerOptions": {
+    "allowSyntheticDefaultImports": true,
+    "composite": true,
+    "declaration": true,
+    "esModuleInterop": true,
+    "incremental": true,
+    "jsx": "react",
+    "module": "esnext",
+    "moduleResolution": "node",
+    "noEmitOnError": true,
+    "noImplicitAny": true,
+    "noUnusedLocals": true,
+    "preserveWatchOutput": true,
+    "resolveJsonModule": true,
+    "outDir": "lib",
+    "rootDir": "src",
+    "strict": true,
+    "strictNullChecks": true,
+    "target": "es2017",
+    "types": []
+  },
+  "include": ["src/*"]
+}


Test patch:
diff --git a/tests/test_sql_utils.py b/tests/test_sql_utils.py
new file mode 100644
index 0000000..7b9da66
--- /dev/null
+++ b/tests/test_sql_utils.py
@@ -0,0 +1,304 @@
+import unittest
+
+from streaming_jupyter_integrations.sql_utils import is_ddl, is_dml, is_dql
+
+
+# Some of the following examples are taken from Flink SQL documentation
+# https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/overview/
+class TestSqlUtils(unittest.TestCase):
+    def test_simple_select(self):
+        simple_select = "   SELECT   * FROM\n\n  myTable\n\t WHERE   someField\r = \r\nsomeOtherField \n\n "
+        self.assertFalse(is_ddl(simple_select))
+        self.assertFalse(is_dml(simple_select))
+        self.assertTrue(is_dql(simple_select))
+
+    def test_select_hints(self):
+        select = """select * from
+    kafka_table1 /*+ OPTIONS('scan.startup.mode'='earliest-offset') */ t1
+    join
+    kafka_table2 /*+ OPTIONS('scan.startup.mode'='earliest-offset') */ t2
+    on t1.id = t2.id;
+        """
+        self.assertFalse(is_ddl(select))
+        self.assertFalse(is_dml(select))
+        self.assertTrue(is_dql(select))
+
+    def test_with_query(self):
+        with_query = "WITH orders_with_total AS (\n    " \
+                     "SELECT order_id, price + tax AS total\n    " \
+                     "FROM Orders\n)\nSELECT order_id, SUM(total)\n" \
+                     "FROM orders_with_total\nGROUP BY order_id;"
+        self.assertFalse(is_ddl(with_query))
+        self.assertFalse(is_dml(with_query))
+        self.assertTrue(is_dql(with_query))
+
+    def test_select_distinct(self):
+        select = "SELECT DISTINCT id FROM Orders"
+        self.assertFalse(is_ddl(select))
+        self.assertFalse(is_dml(select))
+        self.assertTrue(is_dql(select))
+
+    def test_select_values(self):
+        select = "SELECT order_id, price FROM (VALUES (1, 2.0), (2, 3.1))  AS t (order_id, price)"
+        self.assertFalse(is_ddl(select))
+        self.assertFalse(is_dml(select))
+        self.assertTrue(is_dql(select))
+
+    def test_select_cumulate(self):
+        select = """SELECT * FROM TABLE(
+    CUMULATE(
+      DATA => TABLE Bid,
+      TIMECOL => DESCRIPTOR(bidtime),
+      STEP => INTERVAL '2' MINUTES,
+      SIZE => INTERVAL '10' MINUTES));"""
+        self.assertFalse(is_ddl(select))
+        self.assertFalse(is_dml(select))
+        self.assertTrue(is_dql(select))
+
+    def test_select_window_aggregation(self):
+        select = """SELECT
+  user,
+  TUMBLE_START(order_time, INTERVAL '1' DAY) AS wStart,
+  SUM(amount) FROM Orders
+GROUP BY
+  TUMBLE(order_time, INTERVAL '1' DAY),
+  user"""
+        self.assertFalse(is_ddl(select))
+        self.assertFalse(is_dml(select))
+        self.assertTrue(is_dql(select))
+
+    def test_select_over_aggregation(self):
+        select = """SELECT order_id, order_time, amount,
+          SUM(amount) OVER w AS sum_amount,
+          AVG(amount) OVER w AS avg_amount
+        FROM Orders
+        WINDOW w AS (
+          PARTITION BY product
+          ORDER BY order_time
+          RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW)"""
+        self.assertFalse(is_ddl(select))
+        self.assertFalse(is_dml(select))
+        self.assertTrue(is_dql(select))
+
+    def test_create_table(self):
+        create = "CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH ('connector'='raw');"
+        self.assertTrue(is_ddl(create))
+        self.assertFalse(is_dml(create))
+        self.assertFalse(is_dql(create))
+
+        create_not_exist = "\n\nCREATE TABLE IF NOT EXISTS " \
+                           "Catalog.Orders (`user` BIGINT, product STRING, " \
+                           "amount INT) LIKE source_table (EXCLUDING PARTITIONS)"
+        self.assertTrue(is_ddl(create_not_exist))
+        self.assertFalse(is_dml(create_not_exist))
+        self.assertFalse(is_dql(create_not_exist))
+
+    def test_create_catalog(self):
+        create = "CREATE CATALOG Catalog WITH (key1  =  val1)"
+        self.assertTrue(is_ddl(create))
+        self.assertFalse(is_dml(create))
+        self.assertFalse(is_dql(create))
+
+    def test_create_database(self):
+        create = "CREATE DATABASE Catalog.Database WITH ('key1'  =  'val1')"
+        self.assertTrue(is_ddl(create))
+        self.assertFalse(is_dml(create))
+        self.assertFalse(is_dql(create))
+
+    def test_create_view(self):
+        create = "CREATE TEMPORARY VIEW IF NOT EXISTS MyView " \
+                 "(`column1`, `index`, `column2`) AS (SELECT column1, index, column5 FROM someTable);"
+        self.assertTrue(is_ddl(create))
+        self.assertFalse(is_dml(create))
+        self.assertFalse(is_dql(create))
+
+    def test_create_function(self):
+        create = "CREATE FUNCTION MyFunction AS pyflink.table.tests.test_udf.add LANGUAGE PYTHON"
+        self.assertTrue(is_ddl(create))
+        self.assertFalse(is_dml(create))
+        self.assertFalse(is_dql(create))
+
+    def test_drop_table(self):
+        drop = "  DROP\n TABLE \n\rOrders"
+        self.assertTrue(is_ddl(drop))
+        self.assertFalse(is_dml(drop))
+        self.assertFalse(is_dql(drop))
+
+    def test_drop_catalog(self):
+        drop = "  DROP\n CATALOG IF EXISTS \n\rCatalog"
+        self.assertTrue(is_ddl(drop))
+        self.assertFalse(is_dml(drop))
+        self.assertFalse(is_dql(drop))
+
+    def test_drop_database(self):
+        drop = "\n\nDROP DATABASE Production\n\nCASCADE\n\n"
+        self.assertTrue(is_ddl(drop))
+        self.assertFalse(is_dml(drop))
+        self.assertFalse(is_dql(drop))
+
+    def test_drop_view(self):
+        drop = "DROP VIEW Catalog.Database.View"
+        self.assertTrue(is_ddl(drop))
+        self.assertFalse(is_dml(drop))
+        self.assertFalse(is_dql(drop))
+
+    def test_drop_function(self):
+        drop = "DROP FUNCTION Function"
+        self.assertTrue(is_ddl(drop))
+        self.assertFalse(is_dml(drop))
+        self.assertFalse(is_dql(drop))
+
+    def test_alter_table(self):
+        alter = "ALTER TABLE Orders RENAME TO Chaoses"
+        self.assertTrue(is_ddl(alter))
+        self.assertFalse(is_dml(alter))
+        self.assertFalse(is_dql(alter))
+
+        alter_properties = "ALTER TABLE Orders SET (\n  'connector' = 'other_connector'\n);"
+        self.assertTrue(is_ddl(alter_properties))
+        self.assertFalse(is_dml(alter_properties))
+        self.assertFalse(is_dql(alter_properties))
+
+    def test_alter_view(self):
+        alter = "ALTER VIEW MyView RENAME TO YourView"
+        self.assertTrue(is_ddl(alter))
+        self.assertFalse(is_dml(alter))
+        self.assertFalse(is_dql(alter))
+
+        alter_expression = "ALTER VIEW MyView AS (VALUES (1, 2, 3))"
+        self.assertTrue(is_ddl(alter_expression))
+        self.assertFalse(is_dml(alter_expression))
+        self.assertFalse(is_dql(alter_expression))
+
+    def test_alter_database(self):
+        alter = "ALTER DATABASE Production SET ('key1' = 'val2', 'key2' = 'val1'\r\t)"
+        self.assertTrue(is_ddl(alter))
+        self.assertFalse(is_dml(alter))
+        self.assertFalse(is_dql(alter))
+
+    def test_alter_function(self):
+        alter = "ALTER FUNCTION AddFunction AS pyflink.table.tests.test_udf.sub"
+        self.assertTrue(is_ddl(alter))
+        self.assertFalse(is_dml(alter))
+        self.assertFalse(is_dql(alter))
+
+    def test_insert_from_select(self):
+        insert = "INSERT INTO country_page_view " \
+                 "PARTITION (date='2019-8-30', country='China')\n  " \
+                 "SELECT user, cnt FROM page_view_source;"
+        self.assertFalse(is_ddl(insert))
+        self.assertTrue(is_dml(insert))
+        self.assertFalse(is_dql(insert))
+
+        execute_insert = "EXECUTE  INSERT INTO country_page_view " \
+                         "PARTITION (date='2019-8-30', country='China')\n  " \
+                         "SELECT user, cnt FROM page_view_source;"
+        self.assertFalse(is_ddl(execute_insert))
+        self.assertTrue(is_dml(execute_insert))
+        self.assertFalse(is_dql(execute_insert))
+
+        execute_insert_newline = "EXECUTE \n\n\r\t\n INSERT INTO country_page_view\n\t " \
+                                 "PARTITION (date='2019-8-30', country='China')\n  " \
+                                 "SELECT user, cnt FROM page_view_source;"
+        self.assertFalse(is_ddl(execute_insert_newline))
+        self.assertTrue(is_dml(execute_insert_newline))
+        self.assertFalse(is_dql(execute_insert_newline))
+
+        insert_overwrite_partition = "INSERT OVERWRITE country_page_view " \
+                                     "PARTITION (date='2019-8-30')  \n  " \
+                                     "SELECT user, cnt, country FROM page_view_source;"
+        self.assertFalse(is_ddl(insert_overwrite_partition))
+        self.assertTrue(is_dml(insert_overwrite_partition))
+        self.assertFalse(is_dql(insert_overwrite_partition))
+
+        insert_append = "INSERT INTO country_page_view " \
+                        "PARTITION (date='2019-8-30', country='China') (user)  " \
+                        "SELECT user FROM page_view_source"
+        self.assertFalse(is_ddl(insert_append))
+        self.assertTrue(is_dml(insert_append))
+        self.assertFalse(is_dql(insert_append))
+
+    def test_insert_values(self):
+        insert = "INSERT INTO students\n  VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32)"
+        self.assertFalse(is_ddl(insert))
+        self.assertTrue(is_dml(insert))
+        self.assertFalse(is_dql(insert))
+
+    def test_describe(self):
+        describe = "DESCRIBE Orders"
+        self.assertFalse(is_ddl(describe))
+        self.assertFalse(is_dml(describe))
+        self.assertTrue(is_dql(describe))
+
+        desc = "DESC Database.Orders;"
+        self.assertFalse(is_ddl(desc))
+        self.assertFalse(is_dml(desc))
+        self.assertTrue(is_dql(desc))
+
+    def test_explain(self):
+        explain = """
+        EXPLAIN ESTIMATED_COST, CHANGELOG_MODE, JSON_EXECUTION_PLAN
+        SELECT `count`, word FROM MyTable1 WHERE word LIKE 'F%'
+        UNION ALL
+        SELECT `count`, word FROM MyTable2
+        """
+        self.assertFalse(is_ddl(explain))
+        self.assertFalse(is_dml(explain))
+        self.assertTrue(is_dql(explain))
+
+    def test_use(self):
+        uses = [
+            "USE CATALOG CatalogName",
+            "USE MODULES Module1, Module2, Module3",
+            "USE SomeDatabase"
+        ]
+        for use in uses:
+            with self.subTest(sql=use):
+                self.assertTrue(is_ddl(use))
+                self.assertFalse(is_dml(use))
+                self.assertFalse(is_dql(use))
+
+    def test_show_catalogs(self):
+        shows = [
+            "SHOW CATALOGS",
+            "SHOW CURRENT CATALOG",
+            "SHOW DATABASES",
+            "SHOW CURRENT DATABASE",
+            "show tables from db1",
+            "show tables from db1 like '%n';",
+            "show tables from db1 not like '%n';",
+            "show tables in catalog1.db1 not like '%n';"
+            "show tables",
+            "SHOW CREATE TABLE",
+            "show columns from catalog1.database1.orders",
+            "show columns in database1.orders like '%r';",
+            "show columns in catalog1.database1.orders not like '%_r';",
+            "show views",
+            "SHOW USER FUNCTIONS",
+            "SHOW full modules",
+            "show JARS"
+        ]
+
+        for show in shows:
+            with self.subTest(sql=show):
+                self.assertFalse(is_ddl(show))
+                self.assertFalse(is_dml(show))
+                self.assertTrue(is_dql(show))
+
+    def test_load(self):
+        load = "LOAD MODULE hive WITH ('hive-version' = '3.1.2')\n"
+        self.assertTrue(is_ddl(load))
+        self.assertFalse(is_dml(load))
+        self.assertFalse(is_dql(load))
+
+    def test_unload(self):
+        unload = "UNLOAD MODULE core"
+        self.assertTrue(is_ddl(unload))
+        self.assertFalse(is_dml(unload))
+        self.assertFalse(is_dql(unload))
+
+    def test_non_match(self):
+        non_match = "  UNKNOWNABLEKEYWORD * FROM (SELECT * FROM SomeTable)"
+        self.assertFalse(is_ddl(non_match))
+        self.assertFalse(is_dml(non_match))
+        self.assertFalse(is_dql(non_match))