Configuring Case Sensitivity in GBase 8c Compatibility Mode

MySQL and SQL Server support case sensitivity. How does GBase 8c handle this? Let's explore GBase 8c's performance in terms of case sensitivity for object names and case-insensitive data queries.

1. Column Names Support Case Sensitivity

To ensure compatibility with MySQL and SQL Server, start by creating a GBase 8c database in compatibility mode.

Creating a Database and Table

In the GBase 8c Database Management System, execute the following commands to create a database named test and a table named t1:

CREATE DATABASE test DBCOMPATIBILITY 'B' encoding 'UTF-8' LC_COLLATE 'zh_CN.utf8' LC_CTYPE 'zh_CN.utf8';
CREATE TABLE t1(Name varchar(10), iD int);

Check the table structure:

\d+ t1

Output:

Table "public.t1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
Name   | character varying(10) |           | extended |              |
iD     | integer               |           | plain    |              |
Has OIDs: no
Options: orientation=row, compression=no

Verify the column names:

select column_name from information_schema.columns where table_name='t1';

Output:

column_name
-------------
iD
Name
(2 rows)

Insert data and perform update operations:

insert into t1(name, ID) values ('Test', 1);
update t1 set name='new_test' where Id=1;
select * from t1;

Output:

Name   | iD
-------+----
new_test |  1
(1 row)

As demonstrated, GBase 8c allows case-sensitive column names while ignoring case during DML operations, ensuring compatibility with MySQL and SQL Server.

2. Table Names Support Case Sensitivity

By default, GBase 8c is case-insensitive. To enforce case sensitivity, two methods can be used.

Method 1: Using Double Quotes

To create a table with a case-sensitive name, use double quotes:

CREATE TABLE "T2" (id int, Name varchar(10));

Check the tables:

\d+

Output:

List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+-------+-------+------------+----------------------------------+-------------
public | T2   | table | gbase | 0 bytes    | {orientation=row, compression=no} |
public | t1   | table | gbase | 8192 bytes | {orientation=row, compression=no} |

Verify table structure:

\d+ t2

Output:

Did not find any relation named "t2".

Check with double quotes:

\d+ "T2"

Output:

Table "public.T2"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id     | integer               |           | plain    |              |
Name   | character varying(10) |           | extended |              |
Has OIDs: no
Options: orientation=row, compression=no

This method requires using double quotes for all operations involving case-sensitive names.

Method 2: Using dolphin.lower_case_table_names Parameter

To enforce case sensitivity without using double quotes, adjust the dolphin.lower_case_table_names parameter:

ALTER DATABASE test SET dolphin.lower_case_table_names TO 0;

Reconnect to the database for the changes to take effect:

gsql -r test -p 15400

Verify the parameter value:

SHOW dolphin.lower_case_table_names;

Output:

dolphin.lower_case_table_names
--------------------------------
0

Create and check a new table:

CREATE TABLE T3(id int, NAme varchar(10));
\d+ T3

Output:

Table "public.T3"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id     | integer               |           | plain    |              |
NAme   | character varying(10) |           | extended |              |
Has OIDs: no
Options: orientation=row, compression=no

Query the table:

SELECT * FROM T3;

Output:

id | NAme
----+------
(0 rows)

Attempt querying with a different case:

SELECT * FROM t3;

Output:

ERROR: relation "t3" does not exist

This ensures case-sensitive table names without needing double quotes.

3. Data Case-Insensitive Queries

MySQL and SQL Server support case-insensitive data queries. GBase 8c also supports this with the utf8_general_ci collation.

Example in MySQL

CREATE TABLE t4(id int, name varchar(100)) COLLATE utf8_general_ci;
INSERT INTO t4 VALUES (1, 'ABC'), (2, 'ABc'), (3, 'abc');
SELECT * FROM t4 WHERE name='abc';

Output:

id | name
----+------
1  | ABC
2  | ABc
3  | abc

Example in GBase 8c

First, ensure the utf8_general_ci collation is supported:

SELECT * FROM pg_collation WHERE collcollate='utf8_general_ci';

Output:

collname | collcollate | collctype
---------+--------------+-----------
utf8_general_ci | utf8_general_ci | utf8_general_ci

Create a table with the utf8_general_ci collation:

CREATE TABLE t4(id int, name varchar(100)) COLLATE utf8_general_ci;
INSERT INTO t4 VALUES (1, 'ABC'), (2, 'ABc'), (3, 'abc');
SELECT * FROM t4 WHERE name='abc';
SELECT * FROM t4 WHERE name='ABC';

Output for both queries:

id | name
----+------
1  | ABC
2  | ABc
3  | abc

To use this feature, ensure the database encoding is UTF8 and exclude_reserved_words is not set.

This configuration guide ensures that GBase 8c handles case sensitivity for both object names and data queries effectively, maintaining compatibility with MySQL and SQL Server.