GBASE数据库 | GBase 8c Enterprise Database Feature: Compatibility

Introduction

With the rapid development of information technology, database systems play a crucial role in various application scenarios. For enterprises, selecting a database product with high performance, reliability, and compatibility is essential. This article delves into the compatibility features of the GBase database (GBase数据库), helping readers understand its strengths and advantages in practical applications.

Overview of GBase 8c

GBase 8c is a high-performance distributed database management system that supports various storage models (row-based, column-based, in-memory) and multiple deployment configurations (standalone, primary-standby, distributed). It boasts high performance, high availability, scalability, and robust security. GBase 8c is adaptable to physical machines, virtual machines, containers, private clouds, and public clouds, making it suitable for industries like finance, telecommunications, and government. Key features of GBase 8c include:

• High Performance: Based on a shared-nothing architecture, it supports high-concurrency transaction processing.
• High Availability: Offers multi-location deployment and disaster recovery capabilities.
• Cost-Effectiveness: Achieves cost optimization through fine-grained resource scheduling and intelligent cluster management.
• Multiple Storage Models: Supports row-based, column-based, and in-memory storage to suit various application scenarios.
• Flexible Deployment: Supports centralized and distributed deployment options.
• Standard Interface Support: Compatible with ODBC, JDBC, Web, GCI (OCI/OCCI), Python, and other international database standards and development interfaces.

Overview of GBase 8c Compatibility

GBase 8c extensively supports SQL standards and is highly compatible with the syntax and operations of mainstream relational databases such as Oracle, PostgreSQL, and MySQL. GBase 8c’s compatibility spans various levels, including syntax compatibility, data type compatibility, SQL query compatibility, and function compatibility across different database systems. These features allow GBase 8c to seamlessly integrate with multiple database systems, providing flexible data processing solutions for users.

The DBCOMPATIBILITY parameter in GBase 8c specifies the type of database compatibility. It supports compatibility modes for Oracle, MySQL, TD, and PostgreSQL, and must be set when creating the database, as it cannot be modified later. For example, to create a database compatible with Oracle, use the following command:

CREATE DATABASE oracle WITH ENCODING = 'UTF8' DBCOMPATIBILITY = 'A' OWNER test;

The compatibility modes include:

• Mode A: Oracle compatibility
• Mode B: MySQL compatibility
• Mode C: TD compatibility
• Mode PG: PostgreSQL compatibility

1. SQL Standard Support

GBase 8c fully supports the SQL92 standard, including Data Definition Language (DDL), Data Manipulation Language (DML), Transaction Control Language (TCL), and Data Control Language (DCL). It also supports some SQL99 and SQL2003 standards, ensuring reliable performance even with complex queries.

2. Oracle Compatibility

To enable Oracle compatibility, set the DBCOMPATIBILITY parameter to A when creating the database:

CREATE DATABASE oracle_compatible WITH DBCOMPATIBILITY = 'A' ENCODING 'utf8';

In this mode, GBase 8c supports Oracle data types such as NUMBER and VARCHAR2, as well as built-in functions and operators.

3. PostgreSQL Compatibility

Setting the DBCOMPATIBILITY parameter to PG enables compatibility with PostgreSQL:

CREATE DATABASE postgres_compatible WITH DBCOMPATIBILITY = 'PG' ENCODING 'utf8';

In this mode, PostgreSQL features like sequences, default values, and data type behavior for CHAR and VARCHAR are supported.

4. MySQL Compatibility

For MySQL users, setting the DBCOMPATIBILITY parameter to B enables compatibility:

CREATE DATABASE my_sql_compatible WITH DBCOMPATIBILITY = 'B' ENCODING 'utf8';

In this mode, GBase 8c supports MySQL's AUTO_INCREMENT attribute and treats invalid string inputs as 0.

Data Type Compatibility

GBase 8c offers varying data type support across compatibility modes to cater to different database user preferences.

1. Extended Integer Data Types

GBase 8c extends the range of integer data types:

• TINYINT: -128 ~ 127
• SMALLINT: -32768 ~ 32767
• INT: -2147483648 ~ 2147483647
• BIGINT: -9223372036854775808 ~ 9223372036854775807

2. Extended Character Data Types

GBase 8c introduces additional character data types to meet different application needs:

• CHAR(n): Fixed-length string, max n is 1024
• BYTEA(n): Variable-length byte string, max n is 1024
• BPCHAR(n): Fixed-length compressed string, max n is 1024
• TEXT: Character large object, max 1,073,741,823 bytes
• TSVECTOR(n): Variable-length string array type
• TSVECTOR: Unbounded string array

3. Function Compatibility

GBase 8c supports various built-in functions, such as:

• OIDS(), OIDAGG(), OIDTOIDX(), OIDFROMIDX(), OIDEQ(): For object ID operations.
• @@: Special placeholder for the current OID.
• INTERVAL: Interval data type and related functions.

4. Behavioral Differences and Limitations

While GBase 8c has made significant efforts to ensure compatibility, there are behavioral differences and limitations across modes. For example:

• In Oracle mode, empty strings are treated as NULL, whereas they are not in PostgreSQL mode.
• String concatenation operators and date handling behaviors may vary across modes.

Example Scenario

To better illustrate GBase 8c's compatibility, here’s a practical example of a financial data processing scenario:

-- Create a PostgreSQL-compatible database
CREATE DATABASE finance_transactions WITH DBCOMPATIBILITY = 'PG';

-- Design a table structure suitable for high-frequency trading
CREATE TABLE trades (
   trade_id SERIAL PRIMARY KEY,
   stock_symbol VARCHAR(20),
   trade_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
   quantity INT,
   price DECIMAL(10, 2)
);

-- Insert multiple trades
INSERT INTO trades (stock_symbol, quantity, price) VALUES ('GBASE', 100, 199.99);

-- Query latest trades for a stock
SELECT * FROM trades WHERE stock_symbol = 'GBASE' ORDER BY trade_time DESC;

-- Generate a report for trade count and average price
SELECT stock_symbol, COUNT(*) AS trade_count, AVG(price) AS avg_price FROM trades GROUP BY stock_symbol;

In this example, by setting the database to PostgreSQL-compatible mode, users can use syntax and operations similar to PostgreSQL in GBase 8c while leveraging GBase 8c's enterprise-grade features like high performance and distributed processing for handling large-scale financial transaction data.

Conclusion

GBase 8c is designed for core business operations in critical industries, where performance, stability, and security are essential. As data volumes grow, distributed databases become a powerful solution for high concurrency and throughput needs.

GBase Database’s compatibility and optimization capabilities make it an ideal choice for enterprise databases. It not only supports multiple database systems but also offers strong optimization tools to ensure secure, stable, and efficient data processing. With GBase database (GBase数据库), enterprises can seamlessly migrate and integrate databases, enhancing data processing flexibility and efficiency.