You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2021/04/13 03:24:02 UTC
[shardingsphere] branch master updated: 6480 create table xmltype
(#10060)
This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 3bbee64 6480 create table xmltype (#10060)
3bbee64 is described below
commit 3bbee64960b8be4182b2291c4d857bfb23b1e3a1
Author: coco <co...@gmail.com>
AuthorDate: Tue Apr 13 11:23:23 2021 +0800
6480 create table xmltype (#10060)
* feature: add xml type table related rule.
* feature: add xml type table related rule and unit test.
* fix: unit test in create xml type table related rule.
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 10 ++++-
.../src/main/antlr4/imports/oracle/DDLStatement.g4 | 42 ++++++++++++++++-
.../main/antlr4/imports/oracle/OracleKeyword.g4 | 52 ++++++++++++++++++++++
.../src/main/resources/case/ddl/create-table.xml | 24 ++++++++++
.../main/resources/sql/supported/ddl/create.xml | 9 +++-
5 files changed, 132 insertions(+), 5 deletions(-)
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index 008f69e..120cbdf 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -203,6 +203,14 @@ locationSpecifier
: identifier
;
+xmlSchemaURLName
+ : identifier
+ ;
+
+elementName
+ : identifier
+ ;
+
subpartitionName
: identifier
;
@@ -428,7 +436,7 @@ dataTypeName
| BOOLEAN | PLS_INTEGER | BINARY_INTEGER | INTEGER | NUMBER | NATURAL | NATURALN | POSITIVE | POSITIVEN | SIGNTYPE
| SIMPLE_INTEGER | BFILE | MLSLABEL | UROWID | DATE | TIMESTAMP | TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH LOCAL TIME ZONE
| INTERVAL DAY TO SECOND | INTERVAL YEAR TO MONTH | JSON | FLOAT | REAL | DOUBLE PRECISION | INT | SMALLINT
- | DECIMAL | NUMERIC | DEC | IDENTIFIER_
+ | DECIMAL | NUMERIC | DEC | IDENTIFIER_ | XMLTYPE
;
datetimeTypeSuffix
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DDLStatement.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
index 3e80491..a74f4fc 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
@@ -68,7 +68,45 @@ createSharingClause
;
createDefinitionClause
- : createRelationalTableClause | createObjectTableClause
+ : createRelationalTableClause | createObjectTableClause | createXMLTypeTableClause
+ ;
+
+createXMLTypeTableClause
+ : OF? XMLTYPE
+ (LP_ (objectProperties) RP_)?
+ (XMLTYPE xmlTypeStorageClause)?
+ (xmlSchemaSpecClause)?
+ (xmlTypeVirtualColumnsClause)?
+ (ON COMMIT (DELETE | PRESERVE) ROWS)?
+ (oidClause)?
+ (oidIndexClause)?
+ (physicalProperties)?
+ (tableProperties)?
+ ;
+
+xmlTypeStorageClause
+ : STORE
+ (AS ( OBJECT RELATIONAL | ((SECUREFILE | BASICFILE)? (CLOB | BINARY XML) (lobSegname (LP_ lobParameters RP_)? | (LP_ lobParameters RP_))?)))
+ | (ALL VARRAYS AS (LOBS | TABLES ))
+ ;
+
+xmlSchemaSpecClause
+ : (XMLSCHEMA xmlSchemaURLName)? ELEMENT (elementName | xmlSchemaURLName POUND_ elementName)?
+ (STORE ALL VARRAYS AS (LOBS | TABLES))?
+ ((ALLOW | DISALLOW) NONSCHEMA)?
+ ((ALLOW | DISALLOW) ANYSCHEMA)?
+ ;
+
+xmlTypeVirtualColumnsClause
+ : VIRTUAL COLUMNS LP_ (columnName AS LP_ expr RP_ (COMMA_ columnName AS LP_ expr RP_)+) RP_
+ ;
+
+oidClause
+ : OBJECT IDENTIFIER IS (SYSTEM GENERATED | PRIMARY KEY)
+ ;
+
+oidIndexClause
+ : OIDINDEX indexName? LP_ (physicalAttributesClause | TABLESPACE tablespaceName)+ RP_
;
createRelationalTableClause
@@ -464,7 +502,7 @@ segmentAttributesClause
;
physicalAttributesClause
- : (PCTFREE NUMBER_ | PCTUSED NUMBER_ | INITRANS NUMBER_ | storageClause)*
+ : (PCTFREE NUMBER_ | PCTUSED NUMBER_ | INITRANS NUMBER_ | storageClause)+
;
loggingClause
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
index 0aa5b0b..a98986d 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
@@ -1351,6 +1351,58 @@ WITHOUT
: W I T H O U T
;
+XMLTYPE
+ : X M L T Y P E
+ ;
+
+RELATIONAL
+ : R E L A T I O N A L
+ ;
+
+XML
+ : X M L
+ ;
+
+VARRAYS
+ : V A R R A Y S
+ ;
+
+LOBS
+ : L O B S
+ ;
+
+TABLES
+ : T A B L E S
+ ;
+
+ALLOW
+ : A L L O W
+ ;
+
+DISALLOW
+ : D I S A L L O W
+ ;
+
+NONSCHEMA
+ : N O N S C H E M A
+ ;
+
+ANYSCHEMA
+ : A N Y S C H E M A
+ ;
+
+XMLSCHEMA
+ : X M L S C H E M A
+ ;
+
+COLUMNS
+ : C O L U M N S
+ ;
+
+OIDINDEX
+ : O I D I N D E X
+ ;
+
EDITIONABLE
: E D I T I O N A B L E
;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/ddl/create-table.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/ddl/create-table.xml
index af3a707..e293688 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/ddl/create-table.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/ddl/create-table.xml
@@ -1138,6 +1138,30 @@
</column-definition>
</create-table>
+ <create-table sql-case-id="create_table_with_xmltype_table_oracle">
+ <table name="xwarehouses" start-index="13" stop-index="23" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_with_xmltype_column_oracle">
+ <table name="xwarehouses" start-index="13" stop-index="23" />
+ <column-definition type="NUMBER" start-index="26" stop-index="44">
+ <column name="warehouse_id" />
+ </column-definition>
+ <column-definition type="XMLTYPE" start-index="47" stop-index="68">
+ <column name="warehouse_spec" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_with_xmltype_column_clob_oracle">
+ <table name="xwarehouses" start-index="13" stop-index="23" />
+ <column-definition type="NUMBER" start-index="26" stop-index="44">
+ <column name="warehouse_id" />
+ </column-definition>
+ <column-definition type="XMLTYPE" start-index="47" stop-index="68">
+ <column name="warehouse_spec" />
+ </column-definition>
+ </create-table>
+
<create-table sql-case-id="create_table_with_double_quota">
<table name="t_order" start-delimiter=""" end-delimiter=""" start-index="13" stop-index="21" />
<column-definition type="INTEGER" start-index="24" stop-index="41">
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/ddl/create.xml b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/ddl/create.xml
index d6cc0aa..5f87bcc 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/ddl/create.xml
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/ddl/create.xml
@@ -92,11 +92,15 @@
<sql-case id="create_table_with_out_of_line_constraints_oracle" value="CREATE TABLE t_order_item (item_id NUMBER(10), order_id NUMBER(10), user_id NUMBER(10), status VARCHAR2(10), column1 VARCHAR2(10), column2 VARCHAR2(10), column3 VARCHAR2(10), PRIMARY KEY (item_id), UNIQUE (item_id), FOREIGN KEY (order_id) REFERENCES t_order (order_id) ON DELETE CASCADE), CHECK (item_id > 0))" db-types="Oracle" />
<sql-case id="create_table_with_exist_index" value="CREATE TABLE t_order (order_id NUMBER(10) PRIMARY KEY USING INDEX order_index, user_id NUMBER(10), status VARCHAR2(10), column1 VARCHAR2(10), column2 VARCHAR2(10), column3 VARCHAR2(10))" db-types="Oracle" />
<sql-case id="create_table_with_create_index" value="CREATE TABLE t_order (order_id NUMBER(10) PRIMARY KEY USING INDEX (CREATE INDEX order_index ON t_order (order_id)), user_id NUMBER(10), status VARCHAR2(10), column1 VARCHAR2(10), column2 VARCHAR2(10), column3 VARCHAR2(10))" db-types="Oracle" />
- <sql-case id="create_table_with_partition_oracle" value="CREATE TABLE t_order (order_id NUMBER(10), user_id NUMBER(10), status VARCHAR2(10), column1 VARCHAR2(10), column2 VARCHAR2(10), column3 VARCHAR2(10)) PARTITION BY HASH (order_id) PARTITIONS 2" db-types="Oracle" />
<sql-case id="create_table_with_double_quota" value="CREATE TABLE "t_order" ("order_id" INTEGER, "user_id" INTEGER, "status" VARCHAR(10), "column1" VARCHAR(10), "column2" VARCHAR(10), "column3" VARCHAR(10))" db-types="PostgreSQL" />
<sql-case id="create_local_temporary_table" value="CREATE LOCAL TEMPORARY TABLE t_order (order_id INTEGER, user_id INTEGER, status VARCHAR(10), column1 VARCHAR(10), column2 VARCHAR(10), column3 VARCHAR(10))" db-types="PostgreSQL" />
<sql-case id="create_table_with_range_partition" value="CREATE TABLE t_order (order_id INTEGER, user_id INTEGER, status VARCHAR(10), column1 VARCHAR(10), column2 VARCHAR(10), column3 VARCHAR(10)) PARTITION BY RANGE (order_id)" db-types="PostgreSQL" />
-<!-- TODO support create table with like and inherits on PostgreSQL-->
+ <sql-case id="create_table_with_partition_oracle" value="CREATE TABLE t_order (order_id NUMBER(10), user_id NUMBER(10), status VARCHAR2(10), column1 VARCHAR2(10), column2 VARCHAR2(10), column3 VARCHAR2(10)) PARTITION BY HASH (order_id) PARTITIONS 2" db-types="Oracle" />
+ <sql-case id="create_table_with_xmltype_table_oracle" value="CREATE TABLE xwarehouses OF XMLTYPE XMLSCHEMA "http://www.example.com/xwarehouses.xsd" ELEMENT "Warehouse" ;" db-types="Oracle" />
+ <sql-case id="create_table_with_xmltype_column_oracle" value="CREATE TABLE xwarehouses (warehouse_id NUMBER, warehouse_spec XMLTYPE) XMLTYPE warehouse_spec STORE AS CLOB (TABLESPACE example STORAGE (INITIAL 6144) CHUNK 4000 NOCACHE LOGGING);" db-types="Oracle" />
+ <sql-case id="create_table_with_xmltype_column_clob_oracle" value="CREATE TABLE xwarehouses (warehouse_id NUMBER, warehouse_spec XMLTYPE) XMLTYPE warehouse_spec STORE AS SECUREFILE CLOB (TABLESPACE auto_seg_ts STORAGE (INITIAL 6144) CACHE);" db-types="Oracle" />
+
+ <!-- TODO support create table with like and inherits on PostgreSQL-->
<!-- <sql-case id="create_table_like" value="CREATE TABLE t_order_bak (LIKE t_order)" db-types="PostgreSQL" />-->
<!-- <sql-case id="create_table_inherits" value="CREATE TABLE t_order_bak() inherits (t_order)" db-types="PostgreSQL" />-->
<sql-case id="create_table_with_bracket" value="CREATE TABLE [t_order] ([order_id] INT, [user_id] INT, [status] VARCHAR(10), [column1] VARCHAR(10), [column2] VARCHAR(10), [column3] VARCHAR(10))" db-types="SQLServer" />
@@ -117,6 +121,7 @@
<sql-case id="create_table_in_tablespace" value="CREATE TABLE t_order (id serial,name text,location text) TABLESPACE diskvol1" db-types="PostgreSQL" />
<sql-case id="create_table_with_sign_column" value="CREATE TABLE t_order(id INT PRIMARY KEY, order_id BIGINT(20) SIGNED)" db-types="MySQL" />
<sql-case id="create_table_with_unsigned_column" value="CREATE TABLE t_order(id INT PRIMARY KEY, order_id BIGINT(20) UNSIGNED)" db-types="MySQL" />
+
<!-- create index test-->
<sql-case id="create_index" value="CREATE INDEX t_log_index ON t_log (id)" db-types="H2,MySQL,PostgreSQL,Oracle,SQLServer" />
<sql-case id="create_index_with_space" value=" CREATE INDEX