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 06:27:41 UTC
[GitHub] [shardingsphere] FlyingZC opened a new issue, #27056: Support parsing Oracle CREATE OR sql
FlyingZC opened a new issue, #27056:
URL: https://github.com/apache/shardingsphere/issues/27056
# 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 OR REPLACE FUNCTION f1 (
p1 NUMBER
) RETURN NUMBER PARALLEL_ENABLE
IS
BEGIN
RETURN p1 * 2;
END;
```
```sql
CREATE OR REPLACE FUNCTION f1 (
b IN PLS_INTEGER
) RETURN PLS_INTEGER
IS
BEGIN
RETURN
CASE
WHEN b > 0 THEN 1
WHEN b <= 0 THEN -1
ELSE NULL
END;
END f1;
```
```sql
CREATE OR REPLACE FUNCTION f1 (
p1 NUMBER
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN p1 * 2;
END;
```
```sql
CREATE OR REPLACE FUNCTION function_for_position_between
(col NUMBER, lower_pos NUMBER, upper_pos NUMBER,
indexctx IN SYS.ODCIIndexCtx,
scanctx IN OUT position_im,
scanflg IN NUMBER)
RETURN NUMBER AS
rid ROWID;
storage_tab_name VARCHAR2(65);
lower_bound_stmt VARCHAR2(2000);
upper_bound_stmt VARCHAR2(2000);
col_val_stmt VARCHAR2(2000);
lower_bound NUMBER;
upper_bound NUMBER;
column_value NUMBER;
BEGIN
IF (indexctx.IndexInfo IS NOT NULL) THEN
storage_tab_name := indexctx.IndexInfo.INDEXSCHEMA || '.' ||
indexctx.IndexInfo.INDEXNAME || '_STORAGE_TAB';
IF (scanctx IS NULL) THEN
/* This is the first call. Open a cursor for future calls.
First, do some error checking
*/
IF (lower_pos > upper_pos) THEN
RAISE_APPLICATION_ERROR(-20101,
'Upper Position must be greater than or equal to Lower Position');
END IF;
IF (lower_pos <= 0) THEN
RAISE_APPLICATION_ERROR(-20101,
'Both Positions must be greater than zero');
END IF;
/* Obtain the upper and lower value bounds for the range we're interested in.
*/
upper_bound_stmt := 'Select MIN(col_val) FROM (Select /*+ INDEX_DESC(' ||
storage_tab_name || ') */ DISTINCT ' ||
'col_val FROM ' || storage_tab_name || ' ORDER BY ' ||
'col_val DESC) WHERE rownum <= ' || lower_pos;
EXECUTE IMMEDIATE upper_bound_stmt INTO upper_bound;
IF (lower_pos != upper_pos) THEN
lower_bound_stmt := 'Select MIN(col_val) FROM (Select /*+ INDEX_DESC(' ||
storage_tab_name || ') */ DISTINCT ' ||
'col_val FROM ' || storage_tab_name ||
' WHERE col_val < ' || upper_bound || ' ORDER BY ' ||
'col_val DESC) WHERE rownum <= ' ||
(upper_pos - lower_pos);
EXECUTE IMMEDIATE lower_bound_stmt INTO lower_bound;
ELSE
lower_bound := upper_bound;
END IF;
IF (lower_bound IS NULL) THEN
lower_bound := upper_bound;
END IF;
/* Store the lower and upper bounds for future function invocations for
the positions.
*/
scanctx := position_im(0, 0, lower_bound, upper_bound);
END IF;
/* Fetch the column value corresponding to the rowid, and see if it falls
within the determined range.
*/
col_val_stmt := 'Select col_val FROM ' || storage_tab_name ||
' WHERE base_rowid = ''' || indexctx.Rid || '''';
EXECUTE IMMEDIATE col_val_stmt INTO column_value;
IF (column_value <= scanctx.upper_bound AND
column_value >= scanctx.lower_bound AND
scanflg = ODCICONST.RegularCall) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
ELSE
RAISE_APPLICATION_ERROR(-20101, 'A column that has a domain index of' ||
'Position indextype must be the first argument');
END IF;
END;
```
```sql
CREATE OR REPLACE FUNCTION f (n INTEGER)
RETURN INTEGER
IS
BEGIN
IF n = 0 THEN
RETURN 1;
ELSIF n = 1 THEN
RETURN n;
END IF;
END;
```
# 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
[GitHub] [shardingsphere] FlyingZC closed issue #27056: Support parsing Oracle CREATE OR sql
Posted by "FlyingZC (via GitHub)" <gi...@apache.org>.
FlyingZC closed issue #27056: Support parsing Oracle CREATE OR sql
URL: https://github.com/apache/shardingsphere/issues/27056
--
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