Hi Liangda,
I can see your efforts from this analysis, nice. :)
Here are some tips for your questions,
1. Since the proofread work of MySQL definitions has finished, its SQL
classification is a great reference for our Oracle work. It is more precise to
classify each SQL by Oracle doc though, in order to lessen the refactoring work
and keep the classification in line among different databases, it is suggested
to refer to MySQL file to sort out Oracle SQLs.
2. In fact, there are definitely some specific SQLs from Oracle. A preferable
way to handle them is to keep aligned with the Oracle document.
3. Until now, we have been using Release 19 of Oracle for proofreading, so
please take your task by this release.
Cheers,
Trista
----------------------------------------------------------------
Juan Pan (Trista)
Senior DBA & PMC of Apache ShardingSphere
E-mail: [email protected]
On 03/17/2021 08:02，Liangda Wang<[email protected]> wrote：
Hi Trista,
thanks a lot for your answer! It’s much clear now :)
Based on the guidline I‘ve tried to map the Oracle DDL/TCL SQL to its targeting
file in following table. And I have still questions regarding it:
I find it a little bit difficult to distingish between DCL and DDL. Based on
MySQL standard I could find ANALYZE defined as DCL (more specific: Table
Maintenance Statement), and RENAME as DDL (linked below). Others are not
defined in MySQL and I think they’re more like DDL as defined in Oracle
originally. Could you please have a look whether this would be the correct
mapping?
For example, I first thought ALTER SYNONYM and ALTER SESSION will be DCL like
ALTER ROLE, but after this mapping exercise I find only ALTER/CREATE… related
to Account Management are defined DCL. So the others like ALTER SYNONYM and
ALTER SESSION should be still DDL, is it right? Sorry for this silly question,
just want to make sure I’m writing in the correct file
For AUDIT and NOAUDIT there’re two versions: traditional (used in releases
earlier than Oracle Database 12c) and unified (beginning with Oracle Database
12c). Are we going to use only unified version or do we need both?
|
DDL in Oracle (except ALTER, DROP, CREATE, TRUNCATE)
|
Targeting File regarding to Definition and MySQL standard
|
Estimated effort: 1(low)-5(high)
|
|
ANALYZE
|
DCL
|
5
|
|
ASSOCIATE STATISITICS
|
DDL
|
5
|
|
DISASSOCIATE STATISITICS
|
DDL
|
3
|
|
AUDIT (Traditional Auditing)
|
DDL
|
4
|
|
AUDIT (Unified Auditing)
|
DDL
|
3
|
|
NOAUDIT (Traditional Auditing)
|
DDL
|
3
|
|
NOAUDIT (Unified Auditing)
|
DDL
|
2
|
|
COMMENT
|
DDL
|
4
|
|
FLASHBACK DATABASE
|
DDL
|
3
|
|
FLASHBACK TABLE
|
DDL
|
4
|
|
PURGE
|
DDL
|
2
|
|
RENAME
|
DDL
|
1
|
|
REVOKE
|
Already defined in DCL
|
-
|
|
GRANT
|
Already defined in DCL
|
-
|
|
TCL in Oracle
|
Targeting File regarding to MySQL
|
Estimated effort: 1(low)-5(high)
|
|
SET TRANSACTION
|
TCL
|
2
|
|
SET CONSTRAINT(S)
|
TCL
|
1
|
|
COMMIT
|
Already defined in TCL
|
-
|
|
ROLLBACK
|
Already defined in TCL
|
-
|
|
SAVEPOINT
|
Already defined in TCL
|
-
|
Thanks and best regards
Liangda
From: Juan Pan
Sent: Sunday, March 14, 2021 11:59 AM
To: Liangda Wang
Subject: Re:[GSoC] Proofread the DDL/TCL SQL definitions for ShardingSphere
Parser
Hi Liangda,
Since the SQL classification mainly refers to MySQL, that will confuse you when
considering Oracle's SQL. I will try to answer your questions.
Firstly, your summary about the DDL/TCL of Oracle is precisely the content of
GSoC.
Secondly, what're the grounds for SQL classification?
Here are some principles FYR,
1. MySQL's standard
2. The idea of each definition itself, e.g., TCL means Transaction Control
Language. Therefore all the SQLs for transaction control are supposed to be TCL.
But don't worry. You can apply for GSoC using the SQL definition of Oracle and
do this task in practice referring to
`shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/antlr4/imports/mysql`
Cheers,
Trista
----------------------------------------------------------------
Juan Pan (Trista)
Senior DBA & PMC of Apache ShardingSphere
E-mail: [email protected]
On 03/13/2021 07:31，Liangda Wang<[email protected]> wrote：
Hi Trista,
this is Liangda from Github/Jira
I would like to connect with you to learn more about the scope of this GSoC
task with Apache Shardingsphere, as I’ll start to prepare the proposal in the
following weeks.
In COMDEV-399 you’ve listed:
This issue is to proofread the following definitions,
All the DDL SQL definitions for Oracle except for ALTER, DROP, CREATE and
TRUNCATE.
All the TCL (Transaction Control Language) SQL definitions for Oracle
I took a look in the Oracle Types of SQL Statement document and summerize the
following relevant SQL definitions for DDL and TCL:
DDL
1. ANALYZE
2. ASSOCIATE STATISTICS
3. AUDIT
4. COMMENT
5. DISASSOCIATE STATISTICS
6. FLASHBACK ... (All statements beginning with FLASHBACK)
7. GRANT (defined already in DCL)
8. NOAUDIT
9. PURGE
10. RENAME
11. REVOKE (defined already in DCL)
TCL
1. COMMIT (defined already in TCL)
2. ROLLBACK (defined already in TCL)
3. SAVEPOINT (defined already in TCL)
4. SET TRANSACTION (defined already in TCL, but not complete yet)
5. SET CONSTRAINT
So these makes me really confused because our seperation of DCL, DDL, DML and
TCL are different from the official document form Oracle.
For example the most ALTER statements and the ALTER SYNONYM statement you
suggest should be part of DDL, ALTER SESSION should be part of “Session Control
Statement” (in our case DCL?):
The DDL statements are:
· ALTER ... (All statements beginning with ALTER, except ALTER SESSION
and ALTER SYSTEM—see "Session Control Statements" and "System Control
Statement")
Could you please share with me how you defined DCL, DDL, DML and TCL. Is the
scope listing above correct or is there anything here I missunderstood?
Thank you in advance and Wish you a nice weekend!
Best regards
Liangda
