Introduction to Sqlines
Sqlines is an open source software that supports the conversion of SQL statement syntax between various databases. openGauss modifies and adapts this tool, and adds an openGauss database option. Currently, it can support SQL from PostgreSQL, MySQL, and Oracle to openGauss syntax transformation.
How to obtain and use
Download the code from the community to any location: openGauss/openGauss-tools-sqllines (gitee.com)
Enter the root directory of the code, execute the script to compile and install sqlines:
[user@openGauss33 sqlines]$ sh build. sh -i
- The sqlines will be installed in the /bin folder in the root directory, which can be added to the environment variable for easy use: [
user@openGauss33 sqlines]$ export PATH=$PATH:pwd/bin
- Use sqlines
[user@openGauss33 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 openGauss
./sqlines -s=oracle -t=opengauss -in=script.sql
Parameter Description:
Parameter Value Range Function
-? - Help Menu
-s [ oracle | mysql | postgresql ] Source database
-t [ opengauss ] Target database
-in FILE_PATH Input file
-out [ FILE_PATH | /* empty /] Output file, if not specified, the output is in the in folder,
-log [ FILE_PATH | / empty * /] Output log, if not specified, the output will be in the current folder
- Execute the script to uninstall sqlines:
[user@openGauss33 sqlines]$ sh build.sh -m
PostgreSQL to openGauss
delete 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;
Many grammars in openGauss do not support if not exists judgment for the time being, so they will be deleted during conversion.
For example: Create schema IF NOT EXISTS schm; => Create schema schm;
MySQL to openGauss
data type
MYSQL data type openGauss data type Remarks
TINYINT SMALLINT
MEDIUMINT INT
DOUBLE DOUBLE PRECISION
FLOAT DOUBLE PRECISION
DATETIME BLOB
BYTEA
BLOB
BYTE
LONGBLOB BYTEA
TINYTEXT TEXT
MEDIUMTEXT TEXT
LONGTEXT TEXT
BINARY BYTEA
VARBINARY BYTEA
Many data types in Mysql are different from openGauss. For the data types in the table, they can be converted to openGauss data types.
CREATE TABLE
delete if
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name select_statement
For the create table as statement, openGauss does not support the use of if not exists judgment, so the if judgment will be deleted.
Column constraint
syntax: column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] Example: Create table tb(a
int
NOT NULL, b text PRIMARY KEY, c int AUTO_INCREMENT...) The part inside the brackets. When creating a table definition column, you can immediately specify many attributes and constraints for the column, such as NOT NULL.
Behavior when converting is as follows:
AUTO_INCREMENT: Add a sentence to create a sequence SQL in front, and convert auto_increment to defualt nextval(seq);
COMMENT 'string': Delete the
table attribute
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'
Example:
Create table tb(a int) MAX_ROWS = 1000, CHECNSUM = 1, ...; MAX_ROWS in
openGauss does not support this place These syntaxes are added to specify some attributes, and all processing is deleted during conversion.
CREATE DATABASE
CREATE DATABASE [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE = collation_name
- openGauss syntax does not support if judgment, and will be deleted during conversion
2 , For the creation parameters, openGauss and mysql are not the same. Its behavior is as follows:
[DEFAULT] CHARACTER SET *charset_name: Delete
[DEFAULT] COLLATE = *collation_name: Convert COLLATE to LC_COLLATE
CREATE FUNCTION/PROCDURE
If it does not exist or replace, it will be added automatically.
In the function body after AS, $$ symbols are automatically added before and after.
The language attribute is automatically added or modified to 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]
openGauss does not support FULLTEXT and SPATIAL type indexes, so if there are these two types, these two keywords will be deleted during conversion.
CREATE SCHEMA
CREATE SCHEMA [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
The syntax of openGauss does not support the if judgment and will be deleted during conversion
For the creation parameters, openGauss and mysql are not the same. It behaves as follows:
[DEFAULT] CHARACTER SET charset_name: Commented out.
[DEFAULT] COLLATE collation_name: Convert the COLLATE keyword to LC_COLLATE
ALTER TABLE
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...
alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
...
openGauss does not The ignore option is supported, which will be deleted during conversion.
When ADD COLUMN, openGauss does not support using first and after to specify the position of the column and will delete it.
DROP INDEX
DROP INDEX index_name ON tbl_name
openGauss does not support the on clause, such as drop index idxa on tba, the ON clause will be deleted during conversion.
INSERT
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
The insert of openGauss does not support LOW_PRIORITY / DELAYED / HIGH_PROPRITY / IGNORE and other options, These options will be deleted directly when converting.
UPDATE
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
openGauss does not support the LOW_PRIORITY \ IGNORE option, it will be deleted directly during conversion
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]
openGauss 不支持 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]]
openGauss does not support the DISTINCTROW keyword, it will be deleted during conversion.
openGauss does not support [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] These keywords will be deleted during conversion.
RENAME
RENAME TABLE tbl_name TO new_tbl_name;
support conversion of rename syntax to alter table rename syntax such as: RENAME
TABLE tba TO tbb; => ALTER TABLE tba RENAME TO tbb;
Oracle to openGauss
data type
ORACLE OPENGAUSS Remarks
BINARY_FLOAT REAL
BINARY_DOUBLE BUBLE PRECISIONA
CLOAT
TEXT
DATE TIMESTAMP
FLOAT DOUBLE PRECISION
INTERVAL YEAR(4) TO MONTH INTERVAL YEAR TO MONTH
INTERVAL DAY(4) TO SECOND(8) INTERVAL DAY TO SECOND(8)
TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMP WITH TIME ZONE
LONG TEXT
LONG RAW BYTEA
NCHAR(8) CHAR(8)
NCHAR VARYING(7) VARCHAR(7)
NCLOB TEXT
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) BYTEA
REAL DOUBLE PRECISION
SMALLINT DECIMAL(38)
UROWID(16) VARCHAR(16)
VARCHAR2(18) VARCHAR(18)
BFILE VARCHAR(255)
ROWID CHAR(10)
SYS_REFCURSOR REFCURSOR
XMLTYPE XML
CREATE FUNCTION/PROCDURE
will be automatically added when there is no or replace.
In the function body after AS, $$ symbols are automatically added before and after.
The language attribute of the function is automatically modified or added to language plpgsql; the
RETURN keyword of the function is converted to RETURNS
DBMS_OUTPUT.PUT_LINE('err'); => RAISE NOTICE '%','err';
calling the parameter operator => will be converted For:=
EXISTS IF NOT FOUND => EXISTS
SQL%NOTFOUND => NOT FOUND
SQL%FOUND => FOUND
SQL%ROWCOUNT => V_SQLROWCOUNT
SYS_REFCURSOR => REFCURSOR
CREATE TABLE
CREATE [ GLOBAL TEMPORARY | SHARDED | DUPLICATED ] TABLE
[ schema. ]
[ SHARING = { METADATA | DATA | EXTENDED DATA | NONE } ]
{ relational_table | object_table | XMLType_table }
[ PARENT [ schema. ] table ] ;
openGauss does not have SHARDED and DUPLICATED tables, and this keyword will be deleted during conversion.
openGauss does not have the SHARING parameter option, and this parameter will be deleted during conversion.
Delete all these storage parameters: 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 ]
between VI and CE[keywords]; [ EDITIONING | EDITIONABLE [ EDITIONING ] | NONEDITIONABLE ] are removed when converting.
[ SHARING = { METADATA | DATA | EXTENDED DATA | NONE } ] will be removed when converting.
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 } ]
...
;
, which are removed during conversion.
openGauss不支持参数NOCACHE， 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 ]
| USAGE
| UPDATE BLOCK REFERENCES
| alter_index_partitioning
}
;
The three parameters of rebuild_clause will be deleted during conversion.
Such as: alter index idx rebuild immediate invalidation; => alter index idx rebuild;
ENABLE / VISABLE keyword supports changing to REBUILD
such as: alter index idx enable; => alter index idx rebuild;
DISABLE / INVLSIBLE supports changing to UNUSABLE
such as: alter index idx disable => alter index idx unusable;
There is no parameter behind UNUSBALE in openGauss, and it will be deleted during conversion.
For example: alter index idx ununsable online; => alter index idx ununsable;
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 } }
...
;
{ CYCLE | NOCYCLE }, NOCACHE, { ORDER | NOORDER }, { KEEP | NOKEEP }, { SESSION | GLOBAL }, will be deleted during conversion.
DROP INDEX
DROP INDEX [ schema. ] index [ ONLINE ] [ FORCE ] [ { DEFERRED | IMMEDIATE } INVALIDATION ];
openGauss only supports DROP INDEX name; the following parameters ONLINE \ FORCE \ DEFERRED \ IMMEDIATE \ INVALIDATION will be deleted during conversion.
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW [ schema. ] materialized_view
[ PRESERVE TABLE ] ;
openGauss does not support adding parameters, and the subsequent preserve table will be deleted.
DROP TABLE
DROP TABLE [ schema. ] table [ CASCADE CONSTRAINTS ] [ PURGE ] ; the
following parameters cascade constraints and purge will be deleted.
DROP TABLESPACE
DROP TABLESPACE tablespace
[ { DROP | KEEP } QUOTA ]
[ INCLUDING CONTENTS [ { AND | KEEP } DATAFILES ] [ CASCADE CONSTRAINTS ] ]
;
openGauss only supports DROP TABLESPACE tablespace;
various option parameters will be deleted.
DROP TYPE
DROP TYPE [ schema. ] type_name [ FORCE | VALIDATE ] ;
openGauss does not support the FORCE\VALIDATE parameter, it will be removed during conversion.
DROP VIEW
DROP VIEW [ schema. ] view [ CASCADE CONSTRAINTS ] ;
Parameters only retain cascade;
ANALYZE
ANALYZE
{ { TABLE [ schema. ] table
| INDEX [ schema. ] index
} [ partition_extension_clause ]
| CLUSTER [ schema. ] cluster
}
{ validation_c
| LIST CHAINED ROWS [ into_clause ]
| DELETE [ SYSTEM ] STATISTICS
} ;
openGauss does not support adding TABLE \ INDEX keywords after analyze, and these two keywords will be deleted during conversion.
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 ]
For some cases, oracle needs to add FROM DUAL, we don't need it, and it will be deleted during conversion.
Such as: select 1 from dual; => select 1;
we do not support the unique keyword, it will be changed to distinct
such as: select unique * from tb; => select distinct * from tb;
EXECUTE
EXECUTE IMMEDIATE function();
we do not support IMMEDIATE parameter, will be deleted.
GRANT
GRANT USAGE ON LANGUAGE SPL TO ...
called plpgsql in openGauss, will convert the SPL keyword to PLPGSQL;
REVOKE
REVOKE USAGE ON LANGUAGE SPL FROM ...
called plpgsql in openGauss, will convert the SPL keyword to PLPGSQL;
RENAME
RENAME old_name TO new_name;
Support changing rename to alter table rename
such as: 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 ] ;
Only the truncate table name cascade will be kept at most during conversion; others will be deleted.
Function conversion
has some system functions, functions without parameters, etc., which are different, but the semantics are basically the same, so it can support some mapping
Source openGauss Remarks
Charindex (str1, str2) Position(str1 in str2)
CURRENT DATE CURRENT_DATE
CURRENT TIMESTAMP CURRENT_TIMESTAMP
Convert (varchar, source) To_char(source)
USER CURRENT_USER
Getdate() Now()
ISNULL(expr, replace) COALESCE(expr, replace)
NVL(expr, expr) COALESCE(expr, expr)
SYSDATE() CURRENT_TIMESTAMP()
SYSTIMESTAMP CURRENT_TIMESTAMP
Top comments (0)