FlyingZC opened a new issue, #27084: URL: https://github.com/apache/shardingsphere/issues/27084
# 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 call_detail_records ( id NUMBER , from_number VARCHAR2(20) , to_number VARCHAR2(20) , date_of_call DATE , distance VARCHAR2(1) , call_duration_in_s NUMBER(4) ) PARTITION BY RANGE(date_of_call) INTERVAL (NUMTODSINTERVAL(1,'DAY')) SUBPARTITION BY LIST(distance) SUBPARTITION TEMPLATE ( SUBPARTITION local VALUES('L') TABLESPACE tbs1 , SUBPARTITION medium_long VALUES ('M') TABLESPACE tbs2 , SUBPARTITION long_distance VALUES ('D') TABLESPACE tbs3 , SUBPARTITION international VALUES ('I') TABLESPACE tbs4 ) (PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2005','dd-MON-yyyy'))) PARALLEL; CREATE INDEX from_number_ix ON call_detail_records(from_number) LOCAL PARALLEL NOLOGGING; CREATE INDEX to_number_ix ON call_detail_records(to_number) LOCAL PARALLEL NOLOGGING; ``` ```sql CREATE TABLE composite_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) SUBPARTITION BY HASH (channel_id) (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')), PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')), PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')), PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')), PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')), PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')), PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')), PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')), PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')), PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')) SUBPARTITIONS 8, PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')) (SUBPARTITION ch_c, SUBPARTITION ch_i, SUBPARTITION ch_p, SUBPARTITION ch_s, SUBPARTITION ch_t), PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE) SUBPARTITIONS 4) ; ``` ```sql CREATE TABLE credit_card_accounts ( account_number NUMBER(16) NOT NULL , customer_id NUMBER NOT NULL , customer_region VARCHAR2(2) NOT NULL , is_active VARCHAR2(1) NOT NULL , date_opened DATE NOT NULL ) PARTITION BY LIST (customer_region) SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 16 ( PARTITION emea VALUES ('EU','ME','AF') , PARTITION amer VALUES ('NA','LA') , PARTITION apac VALUES ('SA','AU','NZ','IN','CH') ) PARALLEL; CREATE BITMAP INDEX is_active_bix ON credit_card_accounts(is_active) LOCAL PARALLEL NOLOGGING; ``` ```sql CREATE TABLE current_inventory ( warehouse_id NUMBER , warehouse_region VARCHAR2(2) , product_id NUMBER , product_category VARCHAR2(12) , amount_in_stock NUMBER , unit_of_shipping VARCHAR2(20) , products_per_unit NUMBER , last_updated DATE ) PARTITION BY LIST (warehouse_region) SUBPARTITION BY LIST (product_category) SUBPARTITION TEMPLATE ( SUBPARTITION perishable VALUES ('DAIRY','PRODUCE','MEAT','BREAD') , SUBPARTITION non_perishable VALUES ('CANNED','PACKAGED') , SUBPARTITION durable VALUES ('TOYS','KITCHENWARE') ) ( PARTITION p_northwest VALUES ('OR', 'WA') , PARTITION p_southwest VALUES ('AZ', 'UT', 'NM') , PARTITION p_northeast VALUES ('NY', 'VM', 'NJ') , PARTITION p_southeast VALUES ('FL', 'GA') , PARTITION p_northcentral VALUES ('SD', 'WI') , PARTITION p_southcentral VALUES ('OK', 'TX') ); CREATE INDEX warehouse_id_ix ON current_inventory(warehouse_id) LOCAL PARALLEL NOLOGGING; CREATE INDEX product_id_ix ON current_inventory(product_id) LOCAL PARALLEL NOLOGGING; ``` ```sql CREATE TABLE admin_docindex( token char(20), doc_id NUMBER, token_frequency NUMBER, token_offsets VARCHAR2(2000), CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id)) ORGANIZATION INDEX TABLESPACE admin_tbs PCTTHRESHOLD 20 OVERFLOW TABLESPACE admin_tbs2; ``` # 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
