import TabItem from "@theme/TabItem"; import Tabs from "@theme/Tabs";

SQL Compatibility

Why Is There Compatibility?

Glitter employs SQL as its API, but since multiple storage engines execute the execution logic, an intermediate layer is needed to serve as a proxy and oversee the operation

The following factors could contribute to SQL compatibility:

• Not all SQL operations adhere to the requirements of a deterministic state machine, such as time functions and auto-incrementing IDs
• It's possible that full_text storage engines can only offer a small subset of SQL functionality, like query_string search.
• Maintaining consistency between the storage engines and the intermediate layer involves additional work due to the existence of the intermediate layer.

Supported Syntax

The types of SQL that the standard and full_text storage engines currently support will be used to explain the specific distinctions between the two engines.

General Guidelines

• Do not support ? placeholders
• Do not support USE statement

DQL

Data Query Language is used to query records that meet certain conditions.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

#### **standard** - **Support** `SELECT` statement [📖](https://dev.mysql.com/doc/refman/5.7/en/select.html) - *Do not support* `SELECT ... INTO` statement - *Do not support* `JOIN` statement - *Do not support* `UNION` statement - *Do not support* `SELECT ... FROM expression WHERE ...`

full_text

• Support SELECT statement 📖
• Support Full-text search & field weight based on Function
• Support Result highlighting based on Hint
• Support Result scoring based on returned dynamic field _score
• Partial support Limited support for Functions
• Partial support WHERE conditions only support column comparison expressions, i.e., expressions like "a < 1" where the left-hand side must be a column name; supports Like queries
• Do not support SELECT ... INTO statement
• Do not support JOIN statement
• Do not support UNION statement
• Do not support SELECT ... FROM expression WHERE ...
• Do not support SELECT result as field expression

Supported Functions:

Name	Description	Documentation
g_match	Full-Text Search

DML

Data Manipulation Language (DML) is a type of language used in SQL for reading, modifying, inserting, and deleting data in a database. It is primarily used to manipulate records related to business processes.

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

#### **standard** - **Support** `INSERT` statement [📖](https://dev.mysql.com/doc/refman/5.7/en/insert.html) - **Support** `DELETE` statement [📖](https://dev.mysql.com/doc/refman/5.7/en/delete.html) - **Support** `UPDATE` statement [📖](https://dev.mysql.com/doc/refman/5.7/en/update.html)

full_text

• Support INSERT statement 📖
• Support DELETE statement 📖
• Support UPDATE statement 📖
• Do not support INSERT ... FROM SELECT statement
• Do not support Above statements must have scalar values, expressions are not allowed.

DDL

Data Definition Language (DDL) is used to define various database objects such as databases, tables, views, indexes, and others

Supported Data Types

Category	Supported Types
Integer	tinyint smallint int bigint
Floating Point	float double
String	varchar text
Binary	blob longblob

Syntax Support

<Tabs defaultValue="Python" values={[ { label: "Python", value: "Python" }, { label: "JavaScript", value: "JavaScript" }, { label: "Golang", value: "Golang" }, ]}

#### **standard** - **Support** `CREATE DATABASE` statement - **Support** `CREATE TABLE` statement - **Support** `DROP TABLE` statement - **Partial support** `ALTER TABLE` statement

ALTER TABLE

Currently only supports adding columns.

full_text

• Supported CREATE DATABASE statement
• Supported CREATE TABLE statement
• Supported DROP TABLE statement
• Do not support ALTER TABLE statement

DCL

Access rights and security levels are defined using Data Control Language. DCL is not yet supported; instead, utilizes the SDK for access control functions.