Configuring Case Sensitivity in GBase 8c Compatibility Mode
Tags: database
Published: 2024-07-11 06:05:11

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`:

```sql
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:
```sql
\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:
```sql
select column_name from information_schema.columns where table_name='t1';
```

Output:
```
column_name
-------------
iD
Name
(2 rows)
```

Insert data and perform update operations:
```sql
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:

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

Check the tables:
```sql
\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:
```sql
\d+ t2
```

Output:
```
Did not find any relation named "t2".
```

Check with double quotes:
```sql
\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:

```sql
ALTER DATABASE test SET dolphin.lower_case_table_names TO 0;
```

Reconnect to the database for the changes to take effect:
```sh
gsql -r test -p 15400
```

Verify the parameter value:
```sql
SHOW dolphin.lower_case_table_names;
```

Output:
```
dolphin.lower_case_table_names
--------------------------------
0
```

Create and check a new table:
```sql
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:
```sql
SELECT * FROM T3;
```

Output:
```
id | NAme
----+------
(0 rows)
```

Attempt querying with a different case:
```sql
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

```sql
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:
```sql
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:
```sql
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.