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 833d638 Add a special format for the insert (#8559)
833d638 is described below
commit 833d6386db3a796ad2ccced8ecb5f7ad0af392f5
Author: JingShang Lu <[email protected]>
AuthorDate: Fri Dec 11 11:33:53 2020 +0800
Add a special format for the insert (#8559)
---
.../visitor/format/impl/MySQLFormatSQLVisitor.java | 187 ++++++++++++++++++++-
.../sql/parser/mysql/MySQLFormatTest.java | 24 ++-
.../sql/parser/mysql/MySQLParameterizedTest.java | 6 +-
3 files changed, 207 insertions(+), 10 deletions(-)
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/format/impl/MySQLFormatSQLVisitor.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/format/impl/MySQLFormatSQLVisitor.java
index 8173eee..487eb29 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/format/impl/MySQLFormatSQLVisitor.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/format/impl/MySQLFormatSQLVisitor.java
@@ -34,12 +34,18 @@ import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.CreateD
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.CreateTableOptionsSpaceSeparatedContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.CteClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.DataTypeContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.DerivedColumnsContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.ExplicitTableContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.ExprContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.FieldLengthContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.IdentifierContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.InsertContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.InsertSelectClauseContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.InsertValuesClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.LiteralsContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.NumberLiteralsContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.OnDuplicateKeyClauseContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.PartitionNamesContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.PrecisionContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.ProjectionContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.ProjectionsContext;
@@ -49,6 +55,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.QueryEx
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.QuerySpecificationContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.RowConstructorListContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.SelectContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.SetAssignmentsClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.StandaloneAlterTableActionContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.StringListContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.StringLiteralsContext;
@@ -200,6 +207,175 @@ public abstract class MySQLFormatSQLVisitor extends
MySQLStatementBaseVisitor<St
}
@Override
+ public String visitInsert(final InsertContext ctx) {
+ visit(ctx.INSERT());
+ formatPrint(" ");
+ visit(ctx.insertSpecification());
+ formatPrint(" ");
+ if (null != ctx.INTO()) {
+ visit(ctx.INTO());
+ formatPrint(" ");
+ }
+ visit(ctx.tableName());
+ formatPrint(" ");
+ if (null != ctx.partitionNames()) {
+ formatPrintln();
+ visit(ctx.partitionNames());
+ }
+ if (null != ctx.insertValuesClause()) {
+ visit(ctx.insertValuesClause());
+ } else if (null != ctx.insertSelectClause()) {
+ visit(ctx.insertSelectClause());
+ } else {
+ visit(ctx.setAssignmentsClause());
+ }
+ if (null != ctx.onDuplicateKeyClause()) {
+ formatPrintln();
+ visit(ctx.onDuplicateKeyClause());
+ }
+ return result.toString();
+ }
+
+ @Override
+ public String visitPartitionNames(final PartitionNamesContext ctx) {
+ visit(ctx.PARTITION());
+ formatPrintln(" (");
+ int idenCount = ctx.identifier().size();
+ for (int i = 0; i < idenCount; i++) {
+ if (i == 0) {
+ visit(ctx.identifier(i));
+ } else {
+ formatPrint(" ,");
+ visit(ctx.identifier(i));
+ }
+ }
+ formatPrint(")");
+ return result.toString();
+ }
+
+ @Override
+ public String visitInsertValuesClause(final InsertValuesClauseContext ctx)
{
+ if (null != ctx.LP_()) {
+ formatPrint("(");
+ if (null != ctx.fields()) {
+ visit(ctx.fields());
+ }
+ formatPrint(")");
+ }
+ formatPrintln();
+ if (null != ctx.VALUE()) {
+ visit(ctx.VALUE());
+ } else {
+ visit(ctx.VALUES());
+ }
+ indentCount++;
+ formatPrintln();
+ if (!ctx.assignmentValues().isEmpty()) {
+ int valueCount = ctx.assignmentValues().size();
+ for (int i = 0; i < valueCount; i++) {
+ if (i == 0) {
+ visit(ctx.assignmentValues(i));
+ } else {
+ formatPrint(",");
+ formatPrintln();
+ visit(ctx.assignmentValues(i));
+ }
+ }
+ }
+ if (null != ctx.rowConstructorList()) {
+ indentCount++;
+ visit(ctx.rowConstructorList());
+ indentCount--;
+ }
+ indentCount--;
+ if (null != ctx.valueReference()) {
+ formatPrintln();
+ visit(ctx.valueReference());
+ }
+ return result.toString();
+ }
+
+ @Override
+ public String visitInsertSelectClause(final InsertSelectClauseContext ctx)
{
+ if (null != ctx.valueReference()) {
+ visit(ctx.valueReference());
+ formatPrint(" ");
+ }
+ if (null != ctx.LP_()) {
+ formatPrint("(");
+ if (null != ctx.fields()) {
+ visit(ctx.fields());
+ }
+ formatPrint(") ");
+ }
+ formatPrintln();
+ visit(ctx.select());
+ return result.toString();
+ }
+
+ @Override
+ public String visitSetAssignmentsClause(final SetAssignmentsClauseContext
ctx) {
+ if (null != ctx.valueReference()) {
+ visit(ctx.valueReference());
+ formatPrint(" ");
+ }
+ indentCount++;
+ visit(ctx.SET());
+ formatPrint(" ");
+ int assigntCount = ctx.assignment().size();
+ for (int i = 0; i < assigntCount; i++) {
+ if (i == 0) {
+ visit(ctx.assignment(i));
+ } else {
+ formatPrintln(",");
+ visit(ctx.assignment(i));
+ }
+ }
+ indentCount--;
+ return result.toString();
+ }
+
+ @Override
+ public String visitDerivedColumns(final DerivedColumnsContext ctx) {
+ formatPrint("(");
+ int aliasCount = ctx.alias().size();
+ for (int i = 0; i < aliasCount; i++) {
+ if (i == 0) {
+ visit(ctx.alias(i));
+ } else {
+ formatPrint(", ");
+ visit(ctx.alias(i));
+ }
+ }
+ formatPrint(")");
+ return result.toString();
+ }
+
+ @Override
+ public String visitOnDuplicateKeyClause(final OnDuplicateKeyClauseContext
ctx) {
+ visit(ctx.ON());
+ formatPrint(" ");
+ visit(ctx.DUPLICATE());
+ formatPrint(" ");
+ visit(ctx.KEY());
+ formatPrint(" ");
+ visit(ctx.UPDATE());
+ formatPrint(" ");
+ indentCount++;
+ int assignmentCount = ctx.assignment().size();
+ for (int i = 0; i < assignmentCount; i++) {
+ if (i == 0) {
+ visit(ctx.assignment(i));
+ } else {
+ formatPrintln();
+ visit(ctx.assignment(i));
+ }
+ }
+ indentCount--;
+ return result.toString();
+ }
+
+ @Override
public String visitTableName(final TableNameContext ctx) {
if (null != ctx.owner()) {
formatPrint(ctx.owner().getText());
@@ -310,11 +486,14 @@ public abstract class MySQLFormatSQLVisitor extends
MySQLStatementBaseVisitor<St
public String visitRowConstructorList(final RowConstructorListContext ctx)
{
int rowCount = ctx.assignmentValues().size();
for (int i = 0; i < rowCount; i++) {
- if (i != 0 && i != rowCount) {
- formatPrint(", ROW");
+ if (i == 0) {
+ visit(ctx.ROW(i));
+ formatPrint(" ");
visit(ctx.assignmentValues(i));
} else {
- formatPrint("ROW");
+ formatPrintln(",");
+ visit(ctx.ROW(i));
+ formatPrint(" ");
visit(ctx.assignmentValues(i));
}
}
@@ -613,7 +792,7 @@ public abstract class MySQLFormatSQLVisitor extends
MySQLStatementBaseVisitor<St
int childCount = node.getChildCount();
for (int i = 0; i < childCount; i++) {
- if (i != 0) {
+ if (i != 0 && !"(".equals(node.getChild(i - 1).getText()) &&
!")".equals(node.getChild(i).getText()) &&
!"(".equals(node.getChild(i).getText())) {
formatPrint(" ");
}
if (!shouldVisitNextChild(node, result)) {
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/test/java/org/apache/shardingsphere/sql/parser/mysql/MySQLFormatTest.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/test/java/org/apache/shardingsphere/sql/parser/mysql/MySQLFormatTest.java
index 8d12c2c..ccf6941 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/test/java/org/apache/shardingsphere/sql/parser/mysql/MySQLFormatTest.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/test/java/org/apache/shardingsphere/sql/parser/mysql/MySQLFormatTest.java
@@ -54,7 +54,7 @@ public final class MySQLFormatTest {
+ "\tid = 1;"});
testUnits.add(new String[]{"select_with_subquery", "select id, name,
age, count(*) as n, (select id, name, age, sex from table2 where id=2) as sid,
yyyy from table1 where id=1", "SELECT id ,"
+ " name , age , \n"
- + "\tCOUNT ( * ) AS n, \n"
+ + "\tCOUNT(*) AS n, \n"
+ "\t(\n"
+ "\t\tSELECT id , name , age , \n"
+ "\t\t\tsex \n"
@@ -90,15 +90,33 @@ public final class MySQLFormatTest {
+ "`runoob_author` VARCHAR(40) NOT NULL,\n"
+ "`runoob_test` NATIONAL CHAR(40),\n"
+ "`submission_date` DATE,\n"
- + "PRIMARY KEY ( `runoob_id` )\n"
+ + "PRIMARY KEY (`runoob_id`)\n"
+ ")ENGINE=InnoDB DEFAULT CHARSET=utf8;", "CREATE TABLE IF NOT
EXISTS `runoob_tbl` (\n"
+ "\t`runoob_id` INT UNSIGNED AUTO_INCREMENT,\n"
+ "\t`runoob_title` VARCHAR(100) NOT NULL,\n"
+ "\t`runoob_author` VARCHAR(40) NOT NULL,\n"
+ "\t`runoob_test` NATIONAL CHAR(40),\n"
+ "\t`submission_date` DATE,\n"
- + "\tPRIMARY KEY ( `runoob_id` )\n"
+ + "\tPRIMARY KEY (`runoob_id`)\n"
+ ") ENGINE = InnoDB DEFAULT CHARSET = utf8"});
+ testUnits.add(new String[]{"insert_with_muti_value", "INSERT INTO
t_order_item(order_id, user_id, status, creation_date) values (1, 1, 'insert',
'2017-08-08'), (2, 2, 'insert', '2017-08-08') "
+ + "ON DUPLICATE KEY UPDATE status = 'init'", "INSERT INTO
t_order_item (order_id , user_id , status , creation_date)\n"
+ + "VALUES\n"
+ + "\t(1, 1, 'insert', '2017-08-08'),\n"
+ + "\t(2, 2, 'insert', '2017-08-08')\n"
+ + "ON DUPLICATE KEY UPDATE status = 'init'"});
+ testUnits.add(new String[]{"insert_with_muti_set", "INSERT INTO
t_order SET order_id = 1, user_id = 1, status =
convert(to_base64(aes_encrypt(1, 'key')) USING utf8) "
+ + "ON DUPLICATE KEY UPDATE status = VALUES(status)", "INSERT
INTO t_order "
+ + "SET order_id = 1,\n"
+ + "\tuser_id = 1,\n"
+ + "\tstatus = CONVERT(to_base64(aes_encrypt(1 , 'key')) USING
utf8)\n"
+ + "ON DUPLICATE KEY UPDATE status = VALUES(status)"});
+ testUnits.add(new String[]{"insert_with_select", "INSERT INTO t_order
(order_id, user_id, status) SELECT order_id, user_id, status FROM t_order WHERE
order_id = 1", "INSERT INTO t_order "
+ + "(order_id , user_id , status) \n"
+ + "SELECT order_id , user_id , status \n"
+ + "FROM t_order\n"
+ + "WHERE \n"
+ + "\torder_id = 1;"});
}
private final String caseId;
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/test/java/org/apache/shardingsphere/sql/parser/mysql/MySQLParameterizedTest.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/test/java/org/apache/shardingsphere/sql/parser/mysql/MySQLParameterizedTest.java
index 04660c2..c087012 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/test/java/org/apache/shardingsphere/sql/parser/mysql/MySQLParameterizedTest.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/test/java/org/apache/shardingsphere/sql/parser/mysql/MySQLParameterizedTest.java
@@ -54,7 +54,7 @@ public final class MySQLParameterizedTest {
+ "\tid = ?;"});
testUnits.add(new String[]{"select_with_subquery", "select id, name,
age, count(*) as n, (select id, name, age, sex from table2 where id=2) as sid,
yyyy from table1 where id=1", "SELECT id ,"
+ " name , age , \n"
- + "\tCOUNT ( * ) AS n, \n"
+ + "\tCOUNT(*) AS n, \n"
+ "\t(\n"
+ "\t\tSELECT id , name , age , \n"
+ "\t\t\tsex \n"
@@ -90,14 +90,14 @@ public final class MySQLParameterizedTest {
+ "`runoob_author` VARCHAR(40) NOT NULL,\n"
+ "`runoob_test` NATIONAL CHAR(40),\n"
+ "`submission_date` DATE,\n"
- + "PRIMARY KEY ( `runoob_id` )\n"
+ + "PRIMARY KEY (`runoob_id`)\n"
+ ")ENGINE=InnoDB DEFAULT CHARSET=utf8;", "CREATE TABLE IF NOT
EXISTS `runoob_tbl` (\n"
+ "\t`runoob_id` INT UNSIGNED AUTO_INCREMENT,\n"
+ "\t`runoob_title` VARCHAR(100) NOT NULL,\n"
+ "\t`runoob_author` VARCHAR(40) NOT NULL,\n"
+ "\t`runoob_test` NATIONAL CHAR(40),\n"
+ "\t`submission_date` DATE,\n"
- + "\tPRIMARY KEY ( `runoob_id` )\n"
+ + "\tPRIMARY KEY (`runoob_id`)\n"
+ ") ENGINE = InnoDB DEFAULT CHARSET = utf8"});
}
