Overview of GBase 8c B Compatibility Database
Tags: database
Published: 2024-07-09 07:39:16

GBase 8c boasts powerful and rich plugin features, including support for the Dolphin Extension Reference. This extension enhances MySQL compatibility (dbcompatibility='B', referred to as B compatibility database hereafter) in GBase 8c, covering keywords, data types, constants and macros, functions and operators, expressions, type conversions, DDL/DML/DCL syntax, stored procedures/custom functions, system views, and more.

The Dolphin plugin inherits the original SQL syntax of the kernel. This article briefly introduces the new/modified content for the kernel syntax.

## Installation and Usage Restrictions

### Using Dolphin

The plugin is automatically installed and loaded, requiring no manual installation.

1. Install GBase 8c.
2. Create a B database and connect to it using the initial user. The Dolphin plugin is enabled by default.

```sql
CREATE DATABASE dbname WITH DBCOMPATIBILITY='B';
\q
$ gsql -d dbname -p 15400 -U gbase
```

### Dolphin Usage Restrictions

- The Dolphin plugin is not supported in the mini version.
- The Dolphin plugin cannot be deleted.
- The Dolphin plugin can only be created in a B compatibility database.
- The Dolphin plugin needs to create data types, functions, etc., under schemas like pg_catalog. Therefore, loading the Dolphin plugin requires initial user privileges. GBase 8c automatically loads the Dolphin plugin the first time the initial user or a user with initial user privileges connects to the B database. If a B compatibility database has never been connected to by the initial user or a user with initial user privileges, the Dolphin plugin will not be loaded.
- All new/modified syntax in Dolphin cannot be viewed through the \h help command in the gsql client, nor does it support auto-completion in the gsql client.
- Creating the Dolphin plugin will delete any existing same-named functions and types required by the plugin and any dependent objects that existed previously.
- The Dolphin plugin depends on the public schema, so it does not support deleting the public schema using the drop schema method.
- When connecting to a B compatibility database with the Dolphin plugin installed, the GUC parameter behavior_compat_options will be modified by default, adding display_leading_zero and select_into_return_null options to maintain compatibility.

## Keyword Syntax Introduction

SQL has reserved and non-reserved words. According to standards, reserved words must not be used as other identifiers. Non-reserved words have special meanings in specific contexts but can be used as identifiers in other contexts. Identifiers must follow these naming conventions:

- Identifiers must be letters, underscores, digits (0-9), or dollar signs ($).
- Identifiers must begin with a letter (a-z) or an underscore (_).

### Notes

These naming conventions are recommended but not mandatory.

In special cases, double quotes or backticks (`) can be used to avoid errors caused by special characters.

Compared to native GBase 8c syntax, Dolphin introduces the following keyword modifications:

- Added MEDIUMINT as a non-reserved keyword.
- The keyword DATE can be used as a function.
- Added LAST_DAY as a reserved keyword to distinguish between the original GBase 8c LAST_DAY function and the Dolphin LAST_DAY function at the syntax level.
- Added GET_FORMAT as a non-reserved keyword for syntax recognition of the GET_FORMAT function.
- Added DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND as non-reserved keywords for the EXTRACT function to recognize corresponding units.
- Changed the keyword AUTHID level from RESERVED_KEYWORD to COL_NAME_KEYWORD, allowing it to be used as a table or column name.
- Changed the keyword BODY level from UNRESERVED_KEYWORD to RESERVED_KEYWORD.
- Added DUAL as a reserved keyword.

### Example

1. Create a table with FIXED(p,s), FIXED, decimal, and number types.

```
CREATE TABLE dec_type_t1
(
  DEC_COL1 FIXED,
  DEC_COL2 FIXED(20,5),
  DEC_COL3 DECIMAL,
  DEC_COL4 NUMBER
);
```

2. View the table structure.

```sql
\d dec_type_t1
```

The result will be:

```sql
Table "public.dec_type_t1"
Column  | Type         | Modifiers
--------+--------------+-----------
DEC_COL1|numeric(10,0) |
DEC_COL2|numeric(20,5) |
DEC_COL3|numeric(10,0) |
DEC_COL4|numeric       |
```

These examples and notes illustrate the powerful features and compatibility improvements brought by the Dolphin plugin in GBase 8c.