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 b4f7def optimize mysql alter table add foreign key parse & route
logic (#10401)
b4f7def is described below
commit b4f7def6b176922122f7a5a7d489c8b92c14a583
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Thu May 20 17:56:31 2021 +0800
optimize mysql alter table add foreign key parse & route logic (#10401)
* optimize mysql alter table add foreign key parse & route logic
* fix checkstyle
---
.../src/test/resources/sharding/alter.xml | 60 ++++++++++++++++++++++
.../impl/MySQLDDLStatementSQLVisitor.java | 57 ++++++++++++--------
.../src/main/resources/case/ddl/alter-table.xml | 20 ++++++++
3 files changed, 115 insertions(+), 22 deletions(-)
diff --git
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/alter.xml
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/alter.xml
index 0dd0b26..ca1371d 100644
---
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/alter.xml
+++
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/alter.xml
@@ -77,4 +77,64 @@
<input sql="ALTER TABLE t_single add constraint t_single_fk foreign
key (order_id) references t_single_extend (order_id)" />
<output sql="ALTER TABLE t_single add constraint t_single_fk foreign
key (order_id) references t_single_extend (order_id)" />
</rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_single_data_node_binding_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_order add constraint t_order_fk foreign key
(order_id) references t_order_item (order_id)" />
+ <output sql="ALTER TABLE t_order_0 add constraint t_order_fk foreign
key (order_id) references t_order_item_0 (order_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_single_data_node_unbinding_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_order add constraint t_order_fk foreign key
(order_id) references t_order_extend (order_id)" />
+ <output sql="ALTER TABLE t_order_0 add constraint t_order_fk foreign
key (order_id) references t_order_extend_0 (order_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_multi_data_node_and_single_data_node_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_account add constraint t_account_fk foreign
key (account_id) references t_order (account_id)" />
+ <output sql="ALTER TABLE t_account_0 add constraint t_account_fk
foreign key (account_id) references t_order_0 (account_id)" />
+ <output sql="ALTER TABLE t_account_1 add constraint t_account_fk
foreign key (account_id) references t_order_0 (account_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_multi_data_node_binding_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_account add constraint t_account_fk foreign
key (account_id) references t_account_detail (account_id)" />
+ <output sql="ALTER TABLE t_account_0 add constraint t_account_fk
foreign key (account_id) references t_account_detail_0 (account_id)" />
+ <output sql="ALTER TABLE t_account_1 add constraint t_account_fk
foreign key (account_id) references t_account_detail_1 (account_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_single_data_node_and_broadcast_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_order add constraint t_order_fk foreign key
(order_id) references t_config (order_id)" />
+ <output sql="ALTER TABLE t_order_0 add constraint t_order_fk foreign
key (order_id) references t_config (order_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_multi_data_node_and_broadcast_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_account add constraint t_account_fk foreign
key (account_id) references t_config (account_id)" />
+ <output sql="ALTER TABLE t_account_0 add constraint t_account_fk
foreign key (account_id) references t_config (account_id)" />
+ <output sql="ALTER TABLE t_account_1 add constraint t_account_fk
foreign key (account_id) references t_config (account_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_single_data_node_and_single_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_order add constraint t_order_fk foreign key
(order_id) references t_single (order_id)" />
+ <output sql="ALTER TABLE t_order_0 add constraint t_order_fk foreign
key (order_id) references t_single (order_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_multi_data_node_and_single_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_account add constraint t_account_fk foreign
key (account_id) references t_single (account_id)" />
+ <output sql="ALTER TABLE t_account_0 add constraint t_account_fk
foreign key (account_id) references t_single (account_id)" />
+ <output sql="ALTER TABLE t_account_1 add constraint t_account_fk
foreign key (account_id) references t_single (account_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_broadcast_and_single_data_node_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_config add constraint t_config_fk foreign
key (order_id) references t_order (order_id)" />
+ <output sql="ALTER TABLE t_config add constraint t_config_fk foreign
key (order_id) references t_order_0 (order_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_broadcast_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_config add constraint t_config_fk foreign
key (order_id) references t_order_type (order_id)" />
+ <output sql="ALTER TABLE t_config add constraint t_config_fk foreign
key (order_id) references t_order_type (order_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_broadcast_and_single_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_config add constraint t_config_fk foreign
key (order_id) references t_single (order_id)" />
+ <output sql="ALTER TABLE t_config add constraint t_config_fk foreign
key (order_id) references t_single (order_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_single_and_single_data_node_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_single add constraint t_single_fk foreign
key (order_id) references t_order (order_id)" />
+ <output sql="ALTER TABLE t_single add constraint t_single_fk foreign
key (order_id) references t_order_0 (order_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_single_and_broadcast_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_single add constraint t_single_fk foreign
key (order_id) references t_config (order_id)" />
+ <output sql="ALTER TABLE t_single add constraint t_single_fk foreign
key (order_id) references t_config (order_id)" />
+ </rewrite-assertion>
+ <rewrite-assertion
id="alter_table_with_single_table_with_add_foreign_constraint_for_mysql"
db-type="MySQL">
+ <input sql="ALTER TABLE t_single add constraint t_single_fk foreign
key (order_id) references t_single_extend (order_id)" />
+ <output sql="ALTER TABLE t_single add constraint t_single_fk foreign
key (order_id) references t_single_extend (order_id)" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLDDLStatementSQLVisitor.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLDDLStatementSQLVisitor.java
index b6da203..6f6e858 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLDDLStatementSQLVisitor.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLDDLStatementSQLVisitor.java
@@ -24,10 +24,13 @@ import
org.apache.shardingsphere.sql.parser.api.visitor.operation.SQLStatementVi
import org.apache.shardingsphere.sql.parser.api.visitor.type.DDLSQLVisitor;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AddColumnContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AddTableConstraintContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AlterDatabaseContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AlterEventContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AlterFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AlterInstanceContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AlterListContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AlterListItemContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AlterLogfileGroupContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AlterProcedureContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AlterServerContext;
@@ -61,6 +64,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.DropSer
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.DropTableContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.DropTriggerContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.DropViewContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.FieldDefinitionContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.FlowControlStatementContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.IfStatementContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.KeyListWithExpressionContext;
@@ -78,8 +82,6 @@ import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.TableEl
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.TruncateTableContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.ValidStatementContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.WhileStatementContext;
-import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.FieldDefinitionContext;
-import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.AlterListContext;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.AlterDefinitionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.CreateDefinitionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.column.ColumnDefinitionSegment;
@@ -264,38 +266,49 @@ public final class MySQLDDLStatementSQLVisitor extends
MySQLStatementSQLVisitor
if (ctx.alterListItem().isEmpty()) {
return result;
}
- for (MySQLStatementParser.AlterListItemContext each :
ctx.alterListItem()) {
+ for (AlterListItemContext each : ctx.alterListItem()) {
if (each instanceof AddColumnContext) {
result.getValue().add((AddColumnDefinitionSegment)
visit(each));
}
if (each instanceof ChangeColumnContext) {
- ModifyColumnDefinitionSegment modifyColumnDefinition = new
ModifyColumnDefinitionSegment(
- each.getStart().getStartIndex(),
each.getStop().getStopIndex(),
- (ColumnDefinitionSegment) visit(((ChangeColumnContext)
each).columnDefinition()));
- if (null != ((MySQLStatementParser.ChangeColumnContext)
each).place()) {
-
modifyColumnDefinition.setColumnPosition((ColumnPositionSegment)
visit(((MySQLStatementParser.ChangeColumnContext) each).place()));
- }
- result.getValue().add(modifyColumnDefinition);
+
result.getValue().add(generateModifyColumnDefinitionSegment((ChangeColumnContext)
each));
}
if (each instanceof ModifyColumnContext) {
- ColumnSegment column = new
ColumnSegment(((ModifyColumnContext)
each).columnInternalRef.start.getStartIndex(), ((ModifyColumnContext)
each).columnInternalRef.stop.getStopIndex(),
- (IdentifierValue) visit(((ModifyColumnContext)
each).columnInternalRef));
- ModifyColumnDefinitionSegment modifyColumnDefinition = new
ModifyColumnDefinitionSegment(
- each.getStart().getStartIndex(),
each.getStop().getStopIndex(),
- generateColumnDefinitionSegment(column,
((MySQLStatementParser.ModifyColumnContext) each).fieldDefinition()));
- if (null != ((MySQLStatementParser.ModifyColumnContext)
each).place()) {
-
modifyColumnDefinition.setColumnPosition((ColumnPositionSegment)
visit(((MySQLStatementParser.ModifyColumnContext) each).place()));
- }
- result.getValue().add(modifyColumnDefinition);
+
result.getValue().add(generateModifyColumnDefinitionSegment((ModifyColumnContext)
each));
}
if (each instanceof AlterTableDropContext) {
AlterTableDropContext alterTableDrop = (AlterTableDropContext)
each;
if (null == alterTableDrop.KEY() && null ==
alterTableDrop.CHECK() && null == alterTableDrop.CONSTRAINT() && null ==
alterTableDrop.keyOrIndex()) {
- ColumnSegment column = new
ColumnSegment(alterTableDrop.columnInternalRef.start.getStartIndex(),
alterTableDrop.columnInternalRef.stop.getStopIndex(),
- (IdentifierValue)
visit(alterTableDrop.columnInternalRef));
- result.getValue().add(new
DropColumnDefinitionSegment(each.getStart().getStartIndex(),
each.getStop().getStopIndex(), Collections.singletonList(column)));
+
result.getValue().add(generateDropColumnDefinitionSegment(alterTableDrop));
}
}
+ if (each instanceof AddTableConstraintContext) {
+ result.getValue().add((ConstraintDefinitionSegment)
visit(((AddTableConstraintContext) each).tableConstraintDef()));
+ }
+ }
+ return result;
+ }
+
+ private DropColumnDefinitionSegment
generateDropColumnDefinitionSegment(final AlterTableDropContext ctx) {
+ ColumnSegment column = new
ColumnSegment(ctx.columnInternalRef.start.getStartIndex(),
ctx.columnInternalRef.stop.getStopIndex(),
+ (IdentifierValue) visit(ctx.columnInternalRef));
+ return new DropColumnDefinitionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), Collections.singletonList(column));
+ }
+
+ private ModifyColumnDefinitionSegment
generateModifyColumnDefinitionSegment(final ModifyColumnContext ctx) {
+ ColumnSegment column = new
ColumnSegment(ctx.columnInternalRef.start.getStartIndex(),
ctx.columnInternalRef.stop.getStopIndex(), (IdentifierValue)
visit(ctx.columnInternalRef));
+ ModifyColumnDefinitionSegment modifyColumnDefinition = new
ModifyColumnDefinitionSegment(
+ ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
generateColumnDefinitionSegment(column, ctx.fieldDefinition()));
+ if (null != ctx.place()) {
+ modifyColumnDefinition.setColumnPosition((ColumnPositionSegment)
visit(ctx.place()));
+ }
+ return modifyColumnDefinition;
+ }
+
+ private ModifyColumnDefinitionSegment
generateModifyColumnDefinitionSegment(final ChangeColumnContext ctx) {
+ ModifyColumnDefinitionSegment result = new
ModifyColumnDefinitionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), (ColumnDefinitionSegment)
visit(ctx.columnDefinition()));
+ if (null != ctx.place()) {
+ result.setColumnPosition((ColumnPositionSegment)
visit(ctx.place()));
}
return result;
}
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/ddl/alter-table.xml
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/ddl/alter-table.xml
index 94765e2..ebb1f51 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/ddl/alter-table.xml
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/ddl/alter-table.xml
@@ -661,18 +661,38 @@
<alter-table sql-case-id="alter_table_add_index">
<table name="t_order" start-index="12" stop-index="18" />
+ <add-constraint index-name="order_index" start-index="24"
stop-index="51">
+ <index-column name="order_id" start-index="43" stop-index="50" />
+ </add-constraint>
</alter-table>
<alter-table sql-case-id="alter_table_add_fulltext_index">
<table name="t_order" start-index="12" stop-index="18" />
+ <add-constraint index-name="idx_column1" start-index="24"
stop-index="59">
+ <index-column name="column1" start-index="52" stop-index="58" />
+ </add-constraint>
</alter-table>
<alter-table sql-case-id="alter_table_add_composite_index">
<table name="t_order" start-index="12" stop-index="18" />
+ <add-constraint index-name="order_index" start-index="24"
stop-index="68">
+ <index-column name="order_id" start-index="43" stop-index="50" />
+ <index-column name="user_id" start-index="53" stop-index="59" />
+ <index-column name="status" start-index="62" stop-index="67" />
+ </add-constraint>
</alter-table>
<alter-table sql-case-id="alter_table_add_indexes">
<table name="t_order" start-index="12" stop-index="18" />
+ <add-constraint index-name="idx_column1" start-index="24"
stop-index="50">
+ <index-column name="column1" start-index="43" stop-index="49" />
+ </add-constraint>
+ <add-constraint index-name="idx_column2" start-index="57"
stop-index="83">
+ <index-column name="column2" start-index="76" stop-index="82" />
+ </add-constraint>
+ <add-constraint index-name="idx_column3" start-index="90"
stop-index="116">
+ <index-column name="column3" start-index="109" stop-index="115" />
+ </add-constraint>
</alter-table>
<alter-table sql-case-id="alter_table_drop_index">
