GBASE数据库 | GBase 8c Compatibility Analysis with Distributed Databases

GBase 8c is a high-performance relational database management system designed with optimizations to achieve extensive compatibility with various mainstream databases, including Oracle, MySQL, and PostgreSQL. This flexibility allows users to minimize conversion costs and improve efficiency during database migration, data integration, and cross-platform usage. This article provides a detailed overview of GBase database (GBase数据库) compatibility features, helping users leverage its advantages in database migration, integration, and multi-source data processing.

1. Creating Databases with Compatibility Modes

GBase 8c supports standard SQL syntax, including Data Definition Language (DDL) and Data Manipulation Language (DML). The syntax for creating databases with different compatibility modes is as follows:

create database db_name with dbcompatibility 'compatibility_mode';

The compatibility mode determines the SQL syntax and behavior that GBase 8c should simulate. This is set using the DBCOMPATIBILITY parameter during database creation:

• 'A' - Oracle compatibility.
• 'B' - MySQL compatibility.
• 'C' - Teradata (TD) compatibility.
• 'PG' - PostgreSQL compatibility.

2. Data Compatibility

2.1 Data Type Compatibility

GBase 8c supports a variety of data types. For example, creating a table with commonly used data types:

create table datatypes
(
 nint integer,
 nbytea bit(6),
 nvarch varchar(10),
 nbit bit varying,
 nch char(10),
 ndate date,
 ntime time,
 ntimestamp timestamp,
 nbool boolean,
 nby bytea,
 njson json
);

2.2 Operator Compatibility

GBase 8c is compatible with arithmetic operators (+, -, *, /), string concatenation (concat), logical and comparison operators (and, or, not), and other common operators like in and exists. Example SQL queries:

select i+b as add, i-b as sub, i*b as mul, (b/i)::real as div from test_art_operation;

Result:

select concat('abcd','efgh','hijk') as result;

Result:

SELECT * FROM COMPANY WHERE SALARY <> 20000;
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 6500; 
SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;

Result:

3. Oracle Function Compatibility

GBase 8c supports common Oracle functions such as AVG, COUNT, LISTAGG, MAX, MIN, STDDEV, SUM, VARIANCE, and WM_CONCAT|STRING_AGG. Examples:

Window functions such as PARTITION BY, ORDER BY, UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, NULLS FIRST, NULLS LAST, ROWS are supported.

Example:

select * from test_o order by id2 nulls first;
select * from test_o order by id2 nulls last;
select mgr_id, sum(salary) mgr_salary, sum(mgr_salary) over (order by mgr_id rows between unbounded preceding and unbounded following) from emp group by mgr_id;
select name, mgr_id, salary, sum(salary) over(partition by mgr_id order by mgr_id) as group_sal from emp;

Result:

select rank() over(order by salary) from emp;
select lead(name) over(order by salary) from emp;

Result:

4. MySQL Function Compatibility

GBase 8c also supports common MySQL functions such as BTRIM, CHAR_LENGTH, INSTR, LEFT, LENGTH, POSITION, REPLACE, and REVERSE. Examples:

SELECT CHARACTER_LENGTH('GeeksforGeeks');
SELECT bit_length('world');
SELECT btrim('sring' , 'ing');
SELECT char_length('hello');
SELECT instr( 'abcdabcdabcd', 'bcd', 2, 2 );
SELECT lengthb('hello');
SELECT left('abcde', 2);
SELECT length('jose', 'UTF8');
SELECT lpad('hi', 5, 'xyza');
SELECT notlike(1,2);
SELECT notlike(1,1);
SELECT octet_length('jose');
SELECT overlay('hello' placing 'world' from 2 for 3 );
SELECT position('ing' in 'string');

Result:

GBase 8c also supports window functions such as RANK, ROW_NUMBER, DENSE_RANK, last_VALUE, CUME_DIST, NTILE, LEAD, and LAG. Examples:

select rank() over(order by salary) from emp;
select lead(name) over(order by salary) from emp;
select lag(name) over(order by salary) from emp;

Result:

Conclusion

GBase database (GBase数据库) achieves high compatibility with various mainstream relational databases through multiple compatibility modes. This compatibility reduces the complexity of database migration and integration, enhances development efficiency, and ensures system stability. By properly utilizing and maintaining GBase 8c, users can benefit from more flexible and efficient data processing solutions.