openGauss Sqlines 使用指导

[user@openGauss sqlines]$ sh build.sh -i

[user@openGauss sqlines]$ export PATH=$PATH:`pwd`/bin

[user@openGauss 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

[user@openGauss sqlines]$ sh build.sh -m

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;

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

语法：column_definition:
  		col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition]

举例：
Create table tb(a int NOT NULL, b text PRIMARY KEY, c int AUTO_INCREMENT……)括号内的部分。在创建表定义列时，可以立马为列指定很多的属性与约束，如NOT NULL等。

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等

CREATE DATABASE [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]
 
create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE = collation_name

**[DEFAULT] CHARACTER SET *charset_name***：  删除

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)
 
index_col_name:
    col_name [(length)] [ASC | DESC]

CREATE SCHEMA [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]
 
create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...
 
alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
……

DROP INDEX index_name ON tbl_name

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

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

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]
 

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]]

RENAME TABLE tbl_name TO new_tbl_name;

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

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 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 }
  ]...
;

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
  }
  ;

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

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

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

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

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

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

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

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

  [ 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 ]

EXECUTE IMMEDIATE function();

GRANT USAGE ON LANGUAGE SPL TO …

REVOKE USAGE ON LANGUAGE SPL FROM … 

RENAME old_name TO new_name ;

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