统一SQL支持改写建表语句自增序列

统一SQL支持改写建表语句自增序列

【统一sql】:https://www.light-pg.com/docs/LTSQL/current/index.html

LightDB 统一SQL 是一款基于 Go 开发的 SQL 转换中间件，支持将 Oracle 常用 SQL 语法翻译转换为其他数据库的 SQL 。以主流数据库Oracle的SQL为基准，将SQL语句转换为其他信创数据库（LightDB、PostgreSQL、TDSQL、DM8、OceanBase、openGauss等）的SQL语句。目标是降低业务部门适配信创数据库的成本，让用户尽可能少地修改数据访问层代码，使基于Hibernate、JPA、MyBatis等框架的Java应用程序，基于oci、libmysqlclient、cres开发工具、hsdb的c/c++应用程序以及其它能够调用c函数的程序都能够直接切换到目标信创数据库，实现信创数据库之间的平滑迁移。

前言

Oracle2DM、Oracle2TDSQL-MYSQL5.7、Oracle2OceanBase-Oracle、Oracle2PostgreSQL、Oracle2LightDB-Oracle支持改写Oracle的DDL中列定义默认值使用expr表达式的用法支持函数、序列。

序列用法至少支持NEXTVAL、CURRVAL。

Oracle2TDQL-MySQL5.7/Oracle2OceanBase-MySQL的SYSDATE函数需要改写成CURRENT_TIMESTAMP()

自增序列、函数

转化

oracle2postgresql:

nextval\currval 关键字转为 nextval()\currval()函数

oracle2DM8、oracle2OceanBase、oracle2LightDB-Oracle

nextval\currval 关键字转为 自定义的nextval\currval关键字

oracle2TDSQL-Mysql5.7

nextval\currval 关键字转为 自定义nextval()\currval()函数

Oracle:
create table table_name ( ... default sequence_name.nextval, ...default sequence_name.currval)

转PostgreSQL:
create table table_name ( ... default nextval(‘sequence_name‘), ...default currval(‘sequence_name‘))

转DM8/ OceanBase-Oracle /LightDB-Oracle
create table table_name ( ... default sequence_name.nextval, ...default sequence_name.currval)

转TDSQL-MySQL5.7
create table table_name ( ... default `unisql`.nextval(‘sequence_name‘), ...default `unisql`.currval(‘sequence_name‘))


oracle
sysdate 关键字

转TDSQL-MySQL5.7
current_timestamp() 函数

其中对转TDSQL-Mysql5.7 自定义函数如下所示

DELIMITER $$
DROP FUNCTION IF EXISTS unisql.`CURRVAL` $$
CREATE FUNCTION unisql.`CURRVAL` (V_SEQ_CODE VARCHAR (64))
    RETURNS BIGINT (16)
    READS SQL DATA
BEGIN
  DECLARE VALUE BIGINT;
  SELECT CURRENT_VAL INTO VALUE FROM unisql.`UNISQL_SEQUENCE` WHERE SEQ_CODE = V_SEQ_CODE;
  if VALUE is null then
    SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Sequence not found';
  end if;
  RETURN VALUE;
END $$
DELIMITER ;

DELIMITER $$
DROP FUNCTION IF EXISTS unisql.`NEXTVAL`$$
CREATE FUNCTION unisql.`NEXTVAL`(V_SEQ_CODE VARCHAR(64))
    RETURNS BIGINT(16)
    MODIFIES SQL DATA
BEGIN
    UPDATE unisql.UNISQL_SEQUENCE SET CURRENT_VAL = CURRENT_VAL + INCREMENT_VAL WHERE SEQ_CODE = V_SEQ_CODE;
    if ROW_COUNT() = 0 then
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Sequence not found';
    end if;
    RETURN unisql.CURRVAL(V_SEQ_CODE);
END$$
DELIMITER ;

结果

通过统一sql转化之后的结果：

数据准备：
drop table table_name;
create sequence table_name_seq;

-- 转换前Oracle SQL:
create table table_name (
  id NUMBER DEFAULT table_name_seq.NEXTVAL PRIMARY KEY,
  column2 NUMBER,
  column3 NUMBER DEFAULT table_name_seq.CURRVAL
)

-- 转换后PostgreSQL SQL:
CREATE TABLE table_name (id decimal DEFAULT nextval('table_name_seq') PRIMARY KEY,column2 decimal,column3 decimal DEFAULT currval('table_name_seq'))

-- 转换后DM8/ OceanBase-Oracle /LightDB-Oracle
CREATE TABLE table_name (id number DEFAULT table_name_seq.NEXTVAL PRIMARY KEY,column2 number,column3 number DEFAULT table_name_seq.CURRVAL)

-- 转换后TDSQL-Mysql5.7
CREATE TABLE `table_name` (`id` decimal DEFAULT `unisql`.nextval('table_name_seq') PRIMARY KEY,`column2` decimal,`column3` decimal DEFAULT `unisql`.currval('table_name_seq'))


-- 转换前Oracle SQL:
select sysdate from dual

-- 转换后TDSQL-Mysql5.7
SELECT current_timestamp() FROM dual

总结

当currval,nextval作为表字段和sequence_name.currval,sequence_name.nextval出现在同一个SQL语句时,建议表字段使用双引号引起来,否则转换后可能得到和预期不一致的结果。 同时注意表的别名和sequence_name不要相同,会造成一定的歧义。

-- 如果表结构定义如下：
DROP TABLE unisql_key_test_2;
CREATE TABLE unisql_key_test_2(
    id NUMBER,
    currval varchar(10),
    nextval varchar(10)
);
INSERT INTO unisql_key_test_2(currval,nextval) values('currval1','nextval1');

-- 同时使用不支持,转换结果可能和预期不一致。
SELECT unisql_seq_1.nextval,nextval,unisql_seq_1.currval,currval FROM unisql_key_test_2;

-- 推荐使用方式：
-- 如果需要同时使用，表字段请用双引号包裹起来
CREATE TABLE unisql_key_test_3(
    id NUMBER,
    "currval" varchar(10),
    "nextval" varchar(10)
);
INSERT INTO unisql_key_test_3("currval","nextval") values('currval1','nextval1');
SELECT unisql_seq_1.nextval,"nextval",unisql_seq_1.currval,"currval" FROM unisql_key_test_3;


-- 注意以下语句转换会得到和预期不一样的结果
SELECT unisql_seq_1.nextval,unisql_seq_1.lnextval,unisql_seq_1.currval,unisql_seq_1.currval FROM unisql_key_test_1 unisql_seq_1;

Oracle2TDSQL-MySQL5.7如何创建序列，详见：https://www.light-pg.com/docs/LTSQL/current/develop/Oracle2TDSQL-MySQL/sql-syntax.html#sequence