SQL 兼容性
#
SQL 是使用者与数据库交流的标准语言。SQL 解析引擎负责将 SQL 字符串解析为抽象语法树，供 SphereEx-DBPlusEngine 理解并实现其增量功能。SphereEx-DBPlusEngine 目前支持 MySQL、PostgreSQL、SQLServer、Oracle、openGauss 以及符合 SQL92 规范的 SQL 方言。由于 SQL 语法的复杂性，目前仍然存在少量不支持的 SQL。此外，SphereEx-DBPlusEngine 为数据库提供了分布式协作的能力，同时将一部分数据库特性抽象到了上层，进行统一管理，以降低用户的使用难度。因此，对于统一提供的特性，原生的 SQL 将不再下发到数据库，并提示该操作不被支持，用户可使用 SphereEx-DBPlusEngine 提供的的方式进行代替。
不兼容情况
#
MySQL
#
| SQL |
|---|
| CLONE LOCAL DATA DIRECTORY = ‘clone_dir’ |
| INSTALL COMPONENT ‘file://component1’,‘file://component2’ |
| UNINSTALL COMPONENT ‘file://component1’,‘file://component2’ |
| REPAIR TABLE table_name |
| OPTIMIZE TABLE table_name |
| CHECKSUM TABLE table_name |
| CHECK TABLE table_name |
| SET RESOURCE GROUP group_name |
| DROP RESOURCE GROUP group_name |
| CREATE RESOURCE GROUP group_name TYPE=SYSTEM |
| ALTER RESOURCE GROUP rg1 VCPU=0-63 |
| SQL |
|---|
| CREATE USER ‘username’@‘localhost’ IDENTIFIED BY ‘password’ |
| ALTER USER ‘username’@‘localhost’ IDENTIFIED BY ‘new_password’ |
| DROP USER ‘username’@‘localhost’ |
| CREATE ROLE ‘rolename’ |
| DROP ROLE ‘rolename’ |
| SHOW CREATE USER username |
| SET PASSWORD='password’ |
| SET ROLE DEFAULT |
| SQL |
|---|
| GRANT ALL ON db1.* TO ‘username’@‘localhost’ |
| GRANT SELECT ON db1.* TO ‘username’ |
| GRANT ‘role1’,‘role2’ TO ‘user1’@‘localhost’ |
| REVOKE INSERT ON db1.* FROM ‘username’@‘localhost’ |
| REVOKE ‘role1’,‘role2’ FROM ‘user1’@‘localhost’ |
| REVOKE ALL PRIVILEGES,GRANT OPTION FROM user_or_role |
| SHOW GRANTS FOR ‘username’@‘localhost’ |
| SHOW GRANTS FOR CURRENT_USER |
| FLUSH PRIVILEGES |
PostgreSQL
#
| SQL |
|---|
| CREATE TYPE avg_state AS (total bigint,count bigint) |
| CREATE AGGREGATE my_avg(int4)(stype=avg_state,sfunc=avg_transfn,finalfunc=avg_finalfn) |
| CREATE TABLE agg_data_2k AS SELECT g FROM generate_series(0,1999) g |
| CREATE SCHEMA schema_name |
| ALTER AGGREGATE alt_aggs(int) OWNER TO regress_alter_generic_user2 |
| CREATE CONVERSION alt_conv1 FOR ‘LATIN1’ TO ‘UTF8’ FROM iso8859_1_to_utf8 |
| CREATE FOREIGN DATA WRAPPER alt_fdw1 |
| CREATE SERVER alt_ferv1 FOREIGN DATA WRAPPER alt_fdw1 |
| CREATE LANGUAGE alt_lang1 HANDLER plpgsql_call_handler |
| CREATE STATISTICS alt_stat1 ON a,b FROM alt_regress_1 |
| CREATE TEXT SEARCH DICTIONARY alt_ts_dict(template=simple) |
| CREATE RULE dev_view_test_ins AS ON INSERT TO dev_view_test DO INSTEAD INSERT INTO def_test SELECT new.* |
| ALTER TABLE alterlock SET(toast.autovacuum_enabled=off) |
| CREATE PUBLICATION pub1 FOR TABLE alter1.t1,ALL TABLES IN SCHEMA alter2 |
openGauss
#
| SQL |
|---|
| CREATE TYPE avg_state AS (total bigint,count bigint) |
| CREATE AGGREGATE my_avg(int4)(stype=avg_state,sfunc=avg_transfn,finalfunc=avg_finalfn) |
| CREATE TABLE agg_data_2k AS SELECT g FROM generate_series(0,1999) g |
| CREATE SCHEMA schema_name |
| ALTER AGGREGATE alt_aggs(int) OWNER TO regress_alter_generic_user2 |
| CREATE CONVERSION alt_conv1 FOR ‘LATIN1’ TO ‘UTF8’ FROM iso8859_1_to_utf8 |
| CREATE FOREIGN DATA WRAPPER alt_fdw1 |
| CREATE SERVER alt_ferv1 FOREIGN DATA WRAPPER alt_fdw1 |
| CREATE LANGUAGE alt_lang1 HANDLER plpgsql_call_handler |
| CREATE STATISTICS alt_stat1 ON a,b FROM alt_regress_1 |
| CREATE TEXT SEARCH DICTIONARY alt_ts_dict(template=simple) |
| CREATE RULE dev_view_test_ins AS ON INSERT TO dev_view_test DO INSTEAD INSERT INTO def_test SELECT new.* |
| ALTER TABLE alterlock SET(toast.autovacuum_enabled=off) |
| CREATE PUBLICATION pub1 FOR TABLE alter1.t1,ALL TABLES IN SCHEMA alter2 |