This content originally appeared on DEV Community and was authored by Cong Li
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.
This content originally appeared on DEV Community and was authored by Cong Li
Cong Li | Sciencx (2024-07-11T06:05:11+00:00) Configuring Case Sensitivity in GBase 8c Compatibility Mode. Retrieved from https://www.scien.cx/2024/07/11/configuring-case-sensitivity-in-gbase-8c-compatibility-mode/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.