Overview of GBase 8c B Compatibility Database

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.

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
);


1. 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.