You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "FlyingZC (via GitHub)" <gi...@apache.org> on 2023/07/13 08:09:45 UTC
[GitHub] [shardingsphere] FlyingZC opened a new issue, #27100: Support parsing Oracle CREATE TABLE sql
FlyingZC opened a new issue, #27100:
URL: https://github.com/apache/shardingsphere/issues/27100
# Background
Hi community,
This issue is for #26878.
ShardingSphere parser engine helps users parse a SQL to get the AST (Abstract Syntax Tree) and visit this tree to get SQLStatement (Java Object). Currently, we are planning to enhance the support for Oracle SQL parsing in ShardingSphere.
More details:
https://shardingsphere.apache.org/document/current/en/reference/sharding/parse/
# Task
This issue is to support more oracle sql parse, as follows:
```sql
CREATE TABLE int_order_hist
AS SELECT *
FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
```
```sql
CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
```
```sql
CREATE TABLE list_customers
( customer_id NUMBER(6)
, cust_first_name VARCHAR2(20)
, cust_last_name VARCHAR2(20)
, cust_address CUST_ADDRESS_TYP
, nls_territory VARCHAR2(30)
, cust_email VARCHAR2(30))
PARTITION BY LIST (nls_territory) (
PARTITION asia VALUES ('CHINA', 'THAILAND'),
PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
PARTITION west VALUES ('AMERICA'),
PARTITION east VALUES ('INDIA'),
PARTITION rest VALUES (DEFAULT));
```
```sql
CREATE TABLE l(a char(16),b char(16), c char(16), d char(240),
PRIMARY KEY(a,b,c)) ORGANIZATION INDEX pctthreshold 10 overflow;
EXECUTE DBMS_IOT.BUILD_CHAIN_ROWS_TABLE('SYS','L','LC');
```
```sql
CREATE TABLE oe.categories_tab_sys OF oe.category_typ
(category_id PRIMARY KEY)
OBJECT ID SYSTEM GENERATED;
CREATE TABLE oe.categories_tab_pkbased OF oe.category_typ
(category_id PRIMARY KEY)
OBJECT ID PRIMARY KEY;
```
# Process
1. First confirm that this is a correct oracle sql syntax, if not please ignore;
2. Compare SQL definitions in Oficial SQL Doc and ShardingSphere SQL Doc;
3. If there is any difference in ShardingSphere SQL Doc, please correct them by referring to the Official SQL Doc;
4. Run mvn install the current_file_module;
5. Check whether there are any exceptions. If indeed, please fix them. (Especially xxxVisitor.class);
6. Add new corresponding SQL case in SQL Cases and expected parsed result in Expected Statment XML;
7. Run SQLParserParameterizedTest to make sure no exceptions.
# Relevant Skills
1. Master JAVA language
2. Have a basic understanding of Antlr `g4` file
3. Be familiar with Oracle SQLs
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support parsing Oracle CREATE TABLE sql [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27100:
URL: https://github.com/apache/shardingsphere/issues/27100#issuecomment-1784213044
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support parsing Oracle CREATE TABLE sql [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #27100:
URL: https://github.com/apache/shardingsphere/issues/27100#issuecomment-5329259417
Hi, thanks for raising this task.
We verified every statement listed in this issue against the current master Oracle parser:
- `CREATE TABLE ... AS SELECT ... WHERE ROWID IN (subquery)` — parses fine
- `CREATE TABLE ... PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(...)) (PARTITION ... VALUES LESS THAN (TO_DATE(...)))` — parses fine
- `CREATE TABLE ... PARTITION BY LIST (...) (PARTITION ... VALUES (DEFAULT))` — parses fine
- `CREATE TABLE ... PRIMARY KEY (...) ORGANIZATION INDEX PCTTHRESHOLD 10 OVERFLOW` — parses fine
- `CREATE TABLE ... OF type (column PRIMARY KEY) OBJECT IDENTIFIER IS { SYSTEM GENERATED | PRIMARY KEY }` — parses fine, which is the form documented in the [Oracle 19c SQL Language Reference](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html); the `OBJECT ID ...` shorthand used in the issue text is not the documented syntax
The remaining `EXECUTE DBMS_IOT.BUILD_CHAIN_ROWS_TABLE(...)` is a SQL*Plus client command (shorthand for an anonymous PL/SQL block), not a SQL statement parsed by the server, so it is out of scope for the SQL parser.
In short, the requested Oracle CREATE TABLE parsing support is already available on current master, so I am closing this issue as completed. Thanks for contributing to the Oracle parser enhancement series (#26878).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] commented on issue #27100: Support parsing Oracle CREATE TABLE sql
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27100:
URL: https://github.com/apache/shardingsphere/issues/27100#issuecomment-1676080706
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support parsing Oracle CREATE TABLE sql [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #27100: Support parsing Oracle CREATE TABLE sql
URL: https://github.com/apache/shardingsphere/issues/27100
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org