概述¶

Sqlines是一款开源软件，支持多种数据库之间的SQL语句语法的的转换，神通数据库将此工具修改适配，新增了ShenTong数据库选项，目前可以支持PostgreSQL、MySQL、Oracle向ShenTong的SQL语法转换。

使用简介¶

Sqlines工具安装在$SZ_OSCAR_HOME/bin目录下，数据库安装后，可在终端中直接执行sqlines即可使用。

参数说明¶

参数	值域	功能
-?		帮助菜单
-s	[dbtype]	源端数据库
-t	[dbtype]	目标数据库
-in	FILE_PATH	输入SQL文件路径
-out	FILE_PATH	输出SQL文件路径
-log	FILE_PATH	日志文件路径

源端数据库类型支持：Oracle、Mysql、Postgresql

目的端数据库类型支持：ShenTong

对于无法识别的数据库类型，工具不会报错，SQL不做任何转换写入输出SQL文件中。

使用sqlines¶

[user@ShenTong sqlines]$ sqlines -?
SQLines 3.1.330 - SQL Assessment and Conversion Tool.
Portions Copyright (c) 2020 SQLines.
Portions Copyright (c) 2021 Huawei Technologies Co.,Ltd.
All Rights Reserved.

How to use:
    sqlines -option=value [...n]

Options:
   -s        - Source type
   -t        - Target type
   -in       - List of files (wildcards *.* are allowed)
   -out      - Output directory (the current directory by default)
   -log      - Log file (sqlines.log by default)
   -?        - Print how to use

Example:
Convert script.sql file from Oracle to ShenTong
   ./sqlines -s=oracle -t=shentong -in=script.sql

PostgreSQL 转 ShenTong¶

删除IF

Create table IF NOT EXISTS tb as select * from basetb;

Create table IF NOT EXISTS tb as execute p1();

Create index IF NOT EXISTS idx on tb(a);

Create sequence IF NOT EXISTS sqc;

Create schema IF NOT EXISTS schm;

ShenTong中很多语法暂时不支持 if not exists判断，因此在转换时会给删掉。

如： Create schema IF NOT EXISTS schm; => Create schema schm;

MySQL 转 ShenTong¶

数据类型映射¶

MYSQL 数据类型	ShenTong 数据类型
TINYINT	SMALLINT
MEDIUMINT	INT
DOUBLE	DOUBLE PRECISION
FLOAT	DOUBLE PRECISION
DATETIME	TIMESTAMP
TINYBLOB	VARBINARY
BLOB	BLOB
MEDIUMBLOB	BLOB
LONGBLOB	BLOB
TINYTEXT	TEXT
MEDIUMTEXT	CLOB
LONGTEXT	CLOB
BINARY	BINARY
VARBINARY	VARBINARY

Mysql中很多数据类型与ShenTong有差别，对于表中的数据类型，可以进行转换成为ShenTong的数据类型。

CREATE TABLE¶

删除if

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name select_statement

对于create table as语句，ShenTong不支持使用 if not exists 判断，因此会删除if判断。

删除不支持表属性

table_option:
  {ENGINE|TYPE} = engine_name
 | AUTO_INCREMENT = value
 | AVG_ROW_LENGTH = value
 | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
 | CHECKSUM = {0 | 1}
 | COMMENT = 'string'
 | CONNECTION = 'connect_string'
 | MAX_ROWS = value
 | MIN_ROWS = value
 | PACK_KEYS = {0 | 1 | DEFAULT}
 | PASSWORD = 'string'
 | DELAY_KEY_WRITE = {0 | 1}
 | ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
 | UNION = (tbl_name[, tbl_name]...)
 | INSERT_METHOD = { NO | FIRST | LAST }
 | DATA DIRECTORY = 'absolute path to directory'
 | INDEX DIRECTORY = 'absolute path to directory'

举例：

Create table tb(a int) MAX_ROWS = 1000, CHECNSUM = 1, ……;中的MAX_ROWS等

ShenTong 并不支持这个地方添加这些语法用来指定一些属性，转换时全部删除处理。

CREATE DATABASE¶

CREATE DATABASE [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE = collation_name

1、ShenTong 语法不支持 if 判断，转换时会删掉

2、对于创建参数，ShenTong和mysql也不大一样。其行为如下：

[DEFAULT] CHARACTER SET *charset_name*：删除

[DEFAULT] COLLATE = collation_name：将COLLATE转换为 LC_COLLATE

CREATE FUNCTION/PROCDURE¶

若不存在or replace则自动添加。

在 AS后的函数体部分，前后自动添加 $$ 符号。

语言属性自动添加或修改为 language plpgsql;

SQL%NOTFOUND => NOT FOUND

SQL%FOUND => FOUND

SQL%ROWCOUNT => V_SQLROWCOUNT

CREATE INDEX¶

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)

index_col_name:
    col_name [(length)] [ASC | DESC]

ShenTong 不支持SPATIAL类型的 index，因此若有这两种的话，转换时会删除这两个关键字。

CREATE SCHEMA¶

CREATE SCHEMA [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

对于创建参数，ShenTong 和mysql也不大一样。其行为如下：

[DEFAULT] CHARACTER SET charset_name：注释掉。

[DEFAULT] COLLATE collation_name: 将COLLATE关键字转换为 LC_COLLATE

ALTER TABLE¶

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
……

1、ShenTong 不支持ignore选项，转换时会给删除。

2、ADD COLUMN时，ShenTong 不支持使用first、after来指定列的位置，会将其删除。

DROP INDEX¶

DROP INDEX index_name ON tbl_name

ShenTong 不支持on子句，如 drop index idxa on tba，转换时会删掉ON子句。

INSERT¶

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

ShenTong 的insert不支持 LOW_PRIORITY / DELAYED / HIGH_PROPRITY / IGNORE等选项，转换时会直接删掉这些选项。

UPDATE¶

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

ShenTong 不支持LOW_PRIORITY IGNORE 选项，转换时会直接删除

DELETE¶

single delete：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_definition]
    [ORDER BY ...]
[LIMIT row_count]

muilty-delete：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*] ...]
    USING table_references
    [WHERE where_definition]

ShenTong 不支持 LOW_PRIORITY QUICK IGNORE 选项，转换时会直接删除。

SELECT¶

SELECT
[ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name']
[FROM table_references]
[WHERE where_definition]
[GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}  [ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]]

ShenTong 不支持 DISTINCTROW 关键字，转换时会给删掉。

ShenTong 不支持 [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]这些关键字，转换时都会给删除。

RENAME¶

RENAME TABLE tbl_name TO new_tbl_name;

支持将rename语法转换为 alter table rename语法

如： RENAME TABLE tba TO tbb； => ALTER TABLE tba RENAME TO tbb;

Oracle 转 ShenTong¶

数据类型转换¶

Oracle 数据类型	ShenTong 数据类型
BINARY_FLOAT	REAL
BINARY_DOUBLE	DOUBLE PRECISION
BLOB	BLOB
CLOB	CLOB
DATE	TIMESTAMP
FLOAT	DOUBLE PRECISION
INTERVAL YEAR(4) TO MONTH	INTERVAL YEAR(4) TO MONTH
INTERVAL DAY(4) TO SECOND(8)	INTERVAL DAY(4) TO SECOND(8)
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMP WITH LOCAL TIME ZONE
LONG	CLOB
LONG RAW	BLOB
NCHAR(8)	CHAR(8)
NCHAR VARYING(7)	VARCHAR(7)
NCLOB	CLOB
NUMBER(8)	INT
NUMBER(1,0)	SMALLINT
NUMBER(4,0)	SMALLINT
NUMBER(8,0)	INT
NUMBER(12,0)	BIGINT
NUMBER(20,0)	DECIMAL(20,0)
NUMBER(10,2)	DECIMAL(10,2)
NUMBER	DOUBLE PRECISION
NUMBER(*)	DOUBLE PRECISION
NVARCHAR2(12)	VARCHAR(12)
RAW(8)	BINARY(8)
REAL	DOUBLE PRECISION
SMALLINT	DECIMAL(38)
UROWID(16)	VARCHAR(16)
VARCHAR2(18)	VARCHAR(18)
BFILE	BFILE
ROWID	CHAR(10)
SYS_REFCURSOR	REFCURSOR
XMLTYPE	XML

PL转换¶

没有or replace时会给自动添加上。

调用传参操作符 => 会转换为 :=

注解

神通数据库的PL与Oracle兼容度比较高，因此大部分语法都支持，不用进行转换。

CREATE TABLE¶

CREATE [ GLOBAL TEMPORARY | SHARDED | DUPLICATED ] TABLE
  [ schema. ] table
  [ SHARING = { METADATA | DATA | EXTENDED DATA | NONE } ]
  { relational_table | object_table | XMLType_table }
  [ PARENT [ schema. ] table ] ;

ShenTong没有SHARDED、 DUPLICATED的表，转换时会删除此关键字。

ShenTong没有SHARING参数选项，转换时会删除此参数。

部分存储参数全部删除： SEGMENT、PCTFREE、PCTUSED、INITRANS、MAXTRANS、COMPRESS、NOCOMPRESS、NOCACHE、LOGGING、NOLOGGING、NOPARALLEL、PARALLEL、NOMONITORING、TABLESPACE 、STORAGE、LOB、COMPUTE、ENABLE、REVERSE

CREATE VIEW¶

CREATE [OR REPLACE]
  [[NO] FORCE] [ EDITIONING | EDITIONABLE [ EDITIONING ] | NONEDITIONABLE ]
VIEW [schema.] view
  [ SHARING = { METADATA | DATA | EXTENDED DATA | NONE } ]
  [ ( { alias [ VISIBLE | INVISIBLE ] [ inline_constraint... ]
      | out_of_line_constraint
      }
        [, { alias [ VISIBLE | INVISIBLE ] [ inline_constraint...]
           | out_of_line_constraint
           }
        ]
    )
  | object_view_clause
  | XMLType_view_clause
  ]
  [ DEFAULT COLLATION collation_name ]
  [ BEQUEATH { CURRENT_USER | DEFINER } ]
AS subquery [ subquery_restriction_clause ]
  [ CONTAINER_MAP | CONTAINERS_DEFAULT ] ;

CREATE OR REPLACE 与VIEW关键词之间的参数，[[NO] FORCE] [ EDITIONING | EDITIONABLE [ EDITIONING ] | NONEDITIONABLE ] 转换时会被删除。

[ SHARING = { METADATA | DATA | EXTENDED DATA | NONE } ]转换时会被删除。

CREATE SEQUENCE¶

CREATE SEQUENCE [ schema. ] sequence
  [ SHARING = { METADATA | DATA | NONE } ]
  [ { INCREMENT BY | START WITH } integer
    | { MAXVALUE integer | NOMAXVALUE }
    | { MINVALUE integer | NOMINVALUE }
    | { CYCLE | NOCYCLE }
    | { CACHE integer | NOCACHE }
    | { ORDER | NOORDER }
    | { KEEP | NOKEEP }
    | { SESSION | GLOBAL }
  ]...
;

ShenTong不支持[ SHARING = { METADATA | DATA | NONE } ]，转换时会删除。

ShenTong不支持参数ORDER NOORDER，KEEP， NOKEEP， SESSION， GLOBAL，转换时会删除

ALTER INDEX¶

ALTER INDEX [ schema. ]index
  { { deallocate_unused_clause
        | allocate_extent_clause
        | shrink_clause
        | parallel_clause
        | physical_attributes_clause
        | logging_clause
        | partial_index_clause
    } ...
    | rebuild_clause [ { DEFERRED | IMMEDIATE } INVALIDATION]
    | PARAMETERS ( 'ODCI_parameters' )
    | COMPILE
    | { ENABLE | DISABLE }
    | UNUSABLE [ ONLINE ] [ { DEFERRED | IMMEDIATE } INVALIDATION ]
    | VISIBLE | INVISIBLE
    | RENAME TO new_name
    | COALESCE [ CLEANUP ] [ parallel_clause ]
    | { MONITORING | NOMONITORING } USAGE
    | UPDATE BLOCK REFERENCES
    | alter_index_partitioning
  }
  ;

rebuild_clause 的三个参数，转换时会给删除。

如：alter index idx rebuild immediate invalidation; => alter index idx rebuild;

ENABLE / VISABLE 关键字支持改为 REBUILD

如：alter index idx enable; => alter index idx rebuild;

DISABLE / INVLSIBLE 支持改为 UNUSABLE

如：alter index idx disable => alter index idx unusable;

ShenTong的UNUSBALE后面没有参数，转换时给删除

如：alter index idx UNUSBAL online; => alter index idx UNUSBALE;

ALTER SEQUENCE¶

ALTER SEQUENCE [ schema. ] sequence
  { INCREMENT BY integer
  | { MAXVALUE integer | NOMAXVALUE }
  | { MINVALUE integer | NOMINVALUE }
  | { CYCLE | NOCYCLE }
  | { CACHE integer | NOCACHE }
  | { ORDER | NOORDER }
  | { KEEP | NOKEEP }
  | { SESSION | GLOBAL }
  } ...
;

ShenTong不支持参数 { KEEP | NOKEEP }， { SESSION | GLOBAL }，转换时会删掉。

DROP INDEX¶

DROP INDEX [ schema. ] index [ ONLINE ] [ FORCE ] [ { DEFERRED | IMMEDIATE } INVALIDATION ];

ShenTong仅支持 DROP INDEX name；后面的参数 ONLINE FORCE DEFERRED IMMEDIATE INVALIDATION 转换时都会删除。

DROP MATERIALIZED VIEW¶

DROP MATERIALIZED VIEW [ schema. ] materialized_view
   [ PRESERVE TABLE ] ;

ShenTong不支持加参数，后面的preserve table会被删掉。

DROP TABLE¶

DROP TABLE [ schema. ] table [ CASCADE CONSTRAINTS ] [ PURGE ] ;

后面的参数 cascade constraints、purge 会删掉。

DROP TABLESPACE¶

DROP TABLESPACE tablespace
  [ { DROP | KEEP } QUOTA ]
  [ INCLUDING CONTENTS [ { AND | KEEP } DATAFILES ] [ CASCADE CONSTRAINTS ] ]
  ;

ShenTong只支持 DROP TABLESPACE tablespace；

各种选项参数都会被删除。

DROP TYPE¶

DROP TYPE [ schema. ] type_name [ FORCE | VALIDATE ] ;

ShenTong不支持 FORCE VALIDATE参数，转换时会删除。

DROP VIEW¶

DROP VIEW [ schema. ] view [ CASCADE CONSTRAINTS ] ;

参数只保留cascade;

ANALYZE¶

ANALYZE
  { { TABLE [ schema. ] table
    | INDEX [ schema. ] index
    } [ partition_extension_clause ]
  | CLUSTER [ schema. ] cluster
  }
  { validation_clauses
  | LIST CHAINED ROWS [ into_clause ]
  | DELETE [ SYSTEM ] STATISTICS
  } ;

ShenTong在analyze后面不支持添加 TABLE INDEX关键字，转换时会删除这两个关键字。

SELECT¶

[ with_clause ]
      SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list
      FROM { table_reference | join_clause | ( join_clause ) }
               [ , { table_reference | join_clause | (join_clause) } ] ...
        [ where_clause ]
        [ hierarchical_query_clause ]
        [ group_by_clause ]
        [ model_clause ]

对于某些情况，oracle需要加FROM DUAL，我们不需要，转换时会给删除。

如： select 1 from dual; => select 1;

我们不支持unique关键字，会转变为distinct

如：select unique * from tb; => select distinct * from tb;

EXECUTE¶

EXECUTE IMMEDIATE function();

我们不支持IMMEDIATE参数，会给删除。

GRANT¶

GRANT USAGE ON LANGUAGE SPL TO …

ShenTong 内叫做plpgsql，会将SPL关键字转换为 PLPGSQL；

REVOKE¶

REVOKE USAGE ON LANGUAGE SPL FROM …

ShenTong 内叫做plpgsql，会将SPL关键字转换为 PLPGSQL；

RENAME¶

RENAME old_name TO new_name ;

支持将rename修改为alter table rename

如： rename oldname to newname; => alter table oldname rename to newname;

TRUNCATE¶

TRUNCATE TABLE [schema.] table
  [ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ]
  [ {DROP [ ALL ] | REUSE} STORAGE ] [ CASCADE ] ;

转换时最多仅会保留 truncate table name cascade；其他都会删除。

函数转换¶

有一些系统函数、无参函数等，是有差异的，但语义基本一样，因此可支持做一些映射

源端函数	ShenTong 函数
Charindex（str1， str2）	REAL
CURRENT DATE	DOUBLE PRECISION
CURRENT TIMESTAMP	BLOB
Convert(varchar, source)	To_char(source)
USER	CURRENT_USER
Getdate()	Now()
ISNULL(expr, replace)	ISNULL(expr, replace)
NVL(expr, expr)	NVL(expr, expr)
SYSDATE()	CURRENT_TIMESTAMP()
SYSTIMESTAMP	CURRENT_TIMESTAMP