You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2022/05/02 00:26:36 UTC
[shardingsphere] branch master updated: support get postgres create sql (#16996)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 1f1ddc19bdb support get postgres create sql (#16996)
1f1ddc19bdb is described below
commit 1f1ddc19bdbbd4042abf443e11955498aab0b578
Author: Chuxin Chen <ch...@qq.com>
AuthorDate: Mon May 2 08:26:29 2022 +0800
support get postgres create sql (#16996)
* support postgres create sql
* support postgres create sql
* support postgres create sql
* support postgres create sql
* support postgres create sql
* Merge branch 'master' of https://github.com/apache/shardingsphere into support_get_create_sql_for_pg
# Conflicts:
# shardingsphere-features/shardingsphere-db-discovery/shardingsphere-db-discovery-provider/shardingsphere-db-discovery-mysql/src/main/java/org/apache/shardingsphere/dbdiscovery/mysql/type/replication/MySQLNormalReplicationMySQLDatabaseDiscoveryType.java
* Merge branch 'master' of https://github.com/apache/shardingsphere into support_get_create_sql_for_pg
# Conflicts:
# shardingsphere-features/shardingsphere-db-discovery/shardingsphere-db-discovery-provider/shardingsphere-db-discovery-mysql/src/main/java/org/apache/shardingsphere/dbdiscovery/mysql/type/replication/MySQLNormalReplicationMySQLDatabaseDiscoveryType.java
* support postgres ddl generator
* support postgres ddl generator
* support postgres ddl generator
* support postgres ddl generator
* support postgres ddl generator
* add pipeline ddl generator.
* support get create sql for pg
* support get create sql for pg
* support get create sql for pg
* support postgres create sql
* support postgres create sql
* support postgres create sql
* support postgres create sql
* support create sql for postgres
* support create sql for postgres
* support generate logic table
* support generate logic table
---
pom.xml | 7 +
.../src/main/release-docs/LICENSE | 1 +
.../shardingsphere-infra-common/pom.xml | 5 +
.../dialect/postgres/PostgreDDLSQLGenerator.java | 73 +++++
.../dialect/postgres/PostgresAbstractLoader.java | 67 +++++
.../postgres/PostgresColumnPropertiesLoader.java | 319 +++++++++++++++++++++
.../postgres/PostgresConstraintsLoader.java | 262 +++++++++++++++++
.../postgres/PostgresTablePropertiesLoader.java | 147 ++++++++++
.../ddlgenerator/spi/DialectDDLSQLGenerator.java | 40 +++
.../spi/DialectDDLSQLGeneratorFactory.java | 47 +++
.../ddlgenerator/util/FreemarkerManager.java | 73 +++++
...etadata.ddlgenerator.spi.DialectDDLSQLGenerator | 18 ++
.../check_constraint/9.2_plus/get_cols.ftl | 28 ++
.../template/columns/12_plus/properties.ftl | 56 ++++
.../columns/default/edit_mode_types_multi.ftl | 30 ++
.../11_plus/get_constraint_include.ftl | 31 ++
.../exclusion_constraint/11_plus/properties.ftl | 47 +++
.../9.2_plus/get_constraint_cols.ftl | 40 +++
.../template/foreign_key/9.1_plus/properties.ftl | 46 +++
.../template/foreign_key/default/get_cols.ftl | 24 ++
.../foreign_key/default/get_constraint_cols.ftl | 30 ++
.../foreign_key/default/get_constraints.ftl | 54 ++++
.../template/foreign_key/default/get_parent.ftl | 24 ++
.../11_plus/get_constraint_include.ftl | 31 ++
.../index_constraint/11_plus/properties.ftl | 48 ++++
.../default/get_costraint_cols.ftl | 27 ++
.../main/resources/template/macro/constraints.ftl | 106 +++++++
.../main/resources/template/macro/db_catalogs.ftl | 22 ++
.../table/10_plus/get_columns_for_table.ftl | 37 +++
.../template/table/10_plus/get_inherits.ftl | 29 ++
.../resources/template/table/12_plus/create.ftl | 121 ++++++++
.../template/table/12_plus/properties.ftl | 92 ++++++
.../template/table/default/get_database_id.ftl | 18 ++
.../template/table/default/get_schema_id.ftl | 18 ++
.../template/table/default/get_table_id.ftl | 18 ++
35 files changed, 2036 insertions(+)
diff --git a/pom.xml b/pom.xml
index 2b8f92547ca..abde02b310f 100644
--- a/pom.xml
+++ b/pom.xml
@@ -161,6 +161,7 @@
<templating-maven-plugin.version>1.0.0</templating-maven-plugin.version>
<git-commit-id-plugin.version>4.9.10</git-commit-id-plugin.version>
<apache-rat-plugin.version>0.12</apache-rat-plugin.version>
+ <freemarker.version>2.3.31</freemarker.version>
<spotless-maven-plugin.version>2.22.1</spotless-maven-plugin.version>
</properties>
@@ -736,6 +737,12 @@
</exclusion>
</exclusions>
</dependency>
+
+ <dependency>
+ <groupId>org.freemarker</groupId>
+ <artifactId>freemarker</artifactId>
+ <version>${freemarker.version}</version>
+ </dependency>
</dependencies>
</dependencyManagement>
diff --git a/shardingsphere-distribution/shardingsphere-proxy-distribution/src/main/release-docs/LICENSE b/shardingsphere-distribution/shardingsphere-proxy-distribution/src/main/release-docs/LICENSE
index 0baa83586e0..bd93aec6f94 100644
--- a/shardingsphere-distribution/shardingsphere-proxy-distribution/src/main/release-docs/LICENSE
+++ b/shardingsphere-distribution/shardingsphere-proxy-distribution/src/main/release-docs/LICENSE
@@ -238,6 +238,7 @@ The text of each license is the standard Apache 2.0 license.
esri-geometry-api 2.2.0: https://github.com/Esri/geometry-api-java, Apache 2.0
failsafe 2.3.3: https://github.com/jhalterman/failsafe, Apache 2.0
failureaccess 1.0.1:https://github.com/google/guava, Apache 2.0
+ freemarker 2.3.31: https://freemarker.apache.org/, Apache 2.0
groovy 4.0.1: https://groovy.apache.org/, Apache 2.0
grpc-api 1.27.1: https://github.com/grpc/grpc-java, Apache 2.0
grpc-context 1.27.1: https://github.com/grpc/grpc-java, Apache 2.0
diff --git a/shardingsphere-infra/shardingsphere-infra-common/pom.xml b/shardingsphere-infra/shardingsphere-infra-common/pom.xml
index 91fa448a93c..a82d0421eaf 100644
--- a/shardingsphere-infra/shardingsphere-infra-common/pom.xml
+++ b/shardingsphere-infra/shardingsphere-infra-common/pom.xml
@@ -69,6 +69,11 @@
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
</dependency>
+
+ <dependency>
+ <groupId>org.freemarker</groupId>
+ <artifactId>freemarker</artifactId>
+ </dependency>
</dependencies>
<build>
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgreDDLSQLGenerator.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgreDDLSQLGenerator.java
new file mode 100644
index 00000000000..6f23e2d2399
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgreDDLSQLGenerator.java
@@ -0,0 +1,73 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.infra.metadata.ddlgenerator.dialect.postgres;
+
+import org.apache.shardingsphere.infra.metadata.ddlgenerator.spi.DialectDDLSQLGenerator;
+import org.apache.shardingsphere.infra.metadata.ddlgenerator.util.FreemarkerManager;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Collection;
+import java.util.Map;
+
+/**
+ * DDL SQL generator for PostgreSQL.
+ */
+public final class PostgreDDLSQLGenerator implements DialectDDLSQLGenerator {
+
+ // TODO support version, partitions, index etc.
+ @Override
+ public String generateDDLSQL(final String tableName, final String schemaName, final DataSource dataSource) throws SQLException {
+ try (Connection connection = dataSource.getConnection()) {
+ Map<String, Object> context = new PostgresTablePropertiesLoader(connection, tableName, schemaName).loadTableProperties();
+ new PostgresColumnPropertiesLoader(connection).loadColumnProperties(context);
+ new PostgresConstraintsLoader(connection).loadConstraints(context);
+ return doGenerateDDLSQL(context);
+ }
+ }
+
+ private String doGenerateDDLSQL(final Map<String, Object> context) {
+ formatColumnList(context);
+ return FreemarkerManager.getSqlFromTemplate(context, "table/12_plus/create.ftl");
+ }
+
+ @SuppressWarnings("unchecked")
+ private void formatColumnList(final Map<String, Object> context) {
+ Collection<Map<String, Object>> columns = (Collection<Map<String, Object>>) context.get("columns");
+ for (Map<String, Object> each : columns) {
+ if (each.containsKey("cltype")) {
+ typeFormatter(each, (String) each.get("cltype"));
+ }
+ }
+ }
+
+ private void typeFormatter(final Map<String, Object> column, final String columnType) {
+ if (columnType.contains("[]")) {
+ column.put("cltype", columnType.substring(0, columnType.length() - 2));
+ column.put("hasSqrBracket", true);
+ } else {
+ column.put("hasSqrBracket", false);
+ }
+ }
+
+ @Override
+ public String getType() {
+ return "PostgreSQL";
+ }
+}
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresAbstractLoader.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresAbstractLoader.java
new file mode 100644
index 00000000000..84555321e4d
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresAbstractLoader.java
@@ -0,0 +1,67 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.infra.metadata.ddlgenerator.dialect.postgres;
+
+import lombok.Getter;
+import lombok.SneakyThrows;
+import org.apache.shardingsphere.infra.metadata.ddlgenerator.util.FreemarkerManager;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Collection;
+import java.util.LinkedHashMap;
+import java.util.LinkedList;
+import java.util.Map;
+
+/**
+ * Postgres abstract loader.
+ */
+@Getter
+public abstract class PostgresAbstractLoader {
+
+ private final Connection connection;
+
+ protected PostgresAbstractLoader(final Connection connection) {
+ this.connection = connection;
+ }
+
+ @SneakyThrows
+ protected Collection<Map<String, Object>> executeByTemplate(final Map<String, Object> param, final String path) {
+ try (
+ Statement statement = connection.createStatement();
+ ResultSet resultSet = statement.executeQuery(FreemarkerManager.getSqlFromTemplate(param, path))) {
+ return getRows(resultSet);
+ }
+ }
+
+ protected Collection<Map<String, Object>> getRows(final ResultSet resultSet) throws SQLException {
+ ResultSetMetaData metaData = resultSet.getMetaData();
+ Collection<Map<String, Object>> result = new LinkedList<>();
+ while (resultSet.next()) {
+ Map<String, Object> row = new LinkedHashMap<>();
+ for (int i = 1; i <= metaData.getColumnCount(); i++) {
+ row.put(metaData.getColumnName(i), resultSet.getObject(i));
+ }
+ result.add(row);
+ }
+ return result;
+ }
+}
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresColumnPropertiesLoader.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresColumnPropertiesLoader.java
new file mode 100644
index 00000000000..e26425a5e2a
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresColumnPropertiesLoader.java
@@ -0,0 +1,319 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.infra.metadata.ddlgenerator.dialect.postgres;
+
+import lombok.SneakyThrows;
+
+import java.sql.Array;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.LinkedHashMap;
+import java.util.LinkedList;
+import java.util.Map;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import java.util.stream.Collectors;
+
+/**
+ * Postgres column properties loader.
+ */
+public final class PostgresColumnPropertiesLoader extends PostgresAbstractLoader {
+
+ private static final Pattern LENGTH_PRECISION_PATTERN = Pattern.compile("(\\d+),(\\d+)");
+
+ private static final Pattern LENGTH_PATTERN = Pattern.compile("(\\d+)");
+
+ private static final Pattern BRACKETS_PATTERN = Pattern.compile("(\\(\\d+\\))");
+
+ public PostgresColumnPropertiesLoader(final Connection connection) {
+ super(connection);
+ }
+
+ /**
+ * Load column properties.
+ *
+ * @param context load context
+ */
+ @SneakyThrows
+ public void loadColumnProperties(final Map<String, Object> context) {
+ Collection<Map<String, Object>> typeAndInheritedColumns = getTypeAndInheritedColumns(context);
+ Collection<Map<String, Object>> allColumns = executeByTemplate(context, "columns/12_plus/properties.ftl");
+ for (Map<String, Object> each : allColumns) {
+ for (Map<String, Object> column : typeAndInheritedColumns) {
+ if (each.get("name").equals(column.get("name"))) {
+ each.put(getInheritedFromTableOrType(context), column.get("inheritedfrom"));
+ }
+ }
+ }
+ if (!allColumns.isEmpty()) {
+ Map<String, Collection<String>> editTypes = getEditTypes(allColumns);
+ for (Map<String, Object> each : allColumns) {
+ columnFormatter(each, editTypes.getOrDefault(each.get("atttypid").toString(), new LinkedList<>()));
+ }
+ }
+ context.put("columns", allColumns);
+ }
+
+ private Collection<Map<String, Object>> getTypeAndInheritedColumns(final Map<String, Object> context) {
+ if (null != context.get("typoid")) {
+ return getColumnFromType(context);
+ }
+ if (null != context.get("coll_inherits")) {
+ Collection<String> collInherits = convertPgArrayToList(context.get("coll_inherits"));
+ context.put("coll_inherits", collInherits);
+ if (!collInherits.isEmpty()) {
+ return getColumnFromInherits(collInherits);
+ }
+ }
+ return Collections.emptyList();
+ }
+
+ private Collection<Map<String, Object>> getColumnFromInherits(final Collection<String> collInherits) {
+ Collection<Map<String, Object>> result = new LinkedList<>();
+ for (Map<String, Object> each : executeByTemplate(new LinkedHashMap<>(), "table/10_plus/get_inherits.ftl")) {
+ if (collInherits.contains((String) each.get("inherits"))) {
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("tid", each.get("oid"));
+ result.addAll(executeByTemplate(param, "table/10_plus/get_columns_for_table.ftl"));
+ }
+ }
+ return result;
+ }
+
+ private Collection<Map<String, Object>> getColumnFromType(final Map<String, Object> context) {
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("tid", context.get("typoid"));
+ return executeByTemplate(param, "table/10_plus/get_columns_for_table.ftl");
+ }
+
+ @SuppressWarnings("unchecked")
+ private String getInheritedFromTableOrType(final Map<String, Object> context) {
+ String result = "inheritedfrom";
+ if (null != context.get("typoid")) {
+ result += "type";
+ } else if (null != context.get("coll_inherits") && !((Collection<String>) context.get("coll_inherits")).isEmpty()) {
+ result += "table";
+ }
+ return result;
+ }
+
+ @SneakyThrows
+ private Collection<String> convertPgArrayToList(final Object array) {
+ return Arrays.stream((String[]) ((Array) array).getArray()).collect(Collectors.toList());
+ }
+
+ private Map<String, Collection<String>> getEditTypes(final Collection<Map<String, Object>> allColumns) throws SQLException {
+ Map<String, Collection<String>> result = new LinkedHashMap<>();
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("type_ids", allColumns.stream().map(each -> each.get("atttypid").toString()).collect(Collectors.joining(",")));
+ for (Map<String, Object> each : executeByTemplate(param, "columns/default/edit_mode_types_multi.ftl")) {
+ result.put(each.get("main_oid").toString(), covertPgArrayAndSort(each.get("edit_types")));
+ }
+ return result;
+ }
+
+ private Collection<String> covertPgArrayAndSort(final Object editTypes) throws SQLException {
+ return Arrays.stream((String[]) ((Array) editTypes).getArray()).sorted(String::compareTo).collect(Collectors.toList());
+ }
+
+ private void columnFormatter(final Map<String, Object> column, final Collection<String> editTypes) {
+ handlePrimaryColumn(column);
+ fetchLengthPrecision(column);
+ editTypes.add(column.get("cltype").toString());
+ column.put("edit_types", editTypes);
+ column.put("cltype", parseTypeName(column.get("cltype").toString()));
+ }
+
+ private void handlePrimaryColumn(final Map<String, Object> column) {
+ if (column.containsKey("attnum") && column.containsKey("indkey")) {
+ if (Arrays.stream(column.get("indkey").toString().split(" ")).collect(Collectors.toList()).contains(column.get("attnum").toString())) {
+ column.put("is_pk", true);
+ column.put("is_primary_key", true);
+ } else {
+ column.put("is_pk", false);
+ column.put("is_primary_key", false);
+ }
+ }
+ }
+
+ private void fetchLengthPrecision(final Map<String, Object> column) {
+ String fullType = getFullDataType(column);
+ if (column.containsKey("elemoid")) {
+ handleLengthPrecision((Long) column.get("elemoid"), column, fullType);
+ }
+ }
+
+ private void handleLengthPrecision(final Long elemoid, final Map<String, Object> column, final String fullType) {
+ boolean precision = false;
+ boolean length = false;
+ String typeval = "";
+ Long[] lTypes = {1560L, 1561L, 1562L, 1563L, 1042L, 1043L, 1014L, 1015L};
+ Long[] dTypes = {1083L, 1114L, 1115L, 1183L, 1184L, 1185L, 1186L, 1187L, 1266L, 1270L};
+ Long[] pTypes = {1231L, 1700L};
+ if (0 != elemoid) {
+ if (Arrays.asList(lTypes).contains(elemoid)) {
+ typeval = "L";
+ } else if (Arrays.asList(dTypes).contains(elemoid)) {
+ typeval = "D";
+ } else if (Arrays.asList(pTypes).contains(elemoid)) {
+ typeval = "P";
+ } else {
+ typeval = " ";
+ }
+ }
+ if ("P".equals(typeval)) {
+ precision = true;
+ }
+ if (precision || "L".equals(typeval) || "D".equals(typeval)) {
+ length = true;
+ }
+
+ if (length && precision) {
+ Matcher matcher = LENGTH_PRECISION_PATTERN.matcher(fullType);
+ if (matcher.find()) {
+ column.put("attlen", matcher.group(1));
+ column.put("attprecision", matcher.group(2));
+ }
+ } else if (length) {
+ Matcher matcher = LENGTH_PATTERN.matcher(fullType);
+ if (matcher.find()) {
+ column.put("attlen", matcher.group(1));
+ column.put("attprecision", null);
+ }
+ }
+ }
+
+ private String getFullDataType(final Map<String, Object> column) {
+ String namespace = (String) column.get("typnspname");
+ String typeName = (String) column.get("typname");
+ Integer numdims = (Integer) column.get("attndims");
+ String schema = null != namespace ? namespace : "";
+ String array = "";
+ String length = "";
+ String name = checkSchemaInName(typeName, schema);
+ if (name.startsWith("_")) {
+ if (null == numdims || 0 == numdims) {
+ numdims = 1;
+ }
+ name = name.substring(1);
+ }
+ if (name.endsWith("[]")) {
+ if (null == numdims || 0 == numdims) {
+ numdims = 1;
+ }
+ name = name.substring(0, name.length() - 2);
+ }
+ if (name.startsWith("\"") && name.endsWith("\"")) {
+ name = name.substring(1, name.length() - 1);
+ }
+ if (numdims == 1) {
+ array = "[]";
+ }
+ Integer typmod = (Integer) column.get("atttypmod");
+ if (-1 != typmod) {
+ length = checkTypmod(typmod, name);
+ }
+ return getFullTypeValue(name, schema, length, array);
+ }
+
+ private String checkSchemaInName(final String typname, final String schema) {
+ if (typname.indexOf(schema + "\".") > 0) {
+ return typname.substring(schema.length() + 3);
+ }
+ if (typname.indexOf(schema + ".") > 0) {
+ return typname.substring(schema.length() + 1);
+ }
+ return typname;
+ }
+
+ private String getFullTypeValue(final String name, final String schema, final String length, final String array) {
+ if ("char".equals(name) && "pg_catalog".equals(schema)) {
+ return "\"char\"" + array;
+ } else if ("time with time zone".equals(name)) {
+ return "time" + length + " with time zone" + array;
+ } else if ("time without time zone".equals(name)) {
+ return "time" + length + " without time zone" + array;
+ } else if ("timestamp with time zone".equals(name)) {
+ return "timestamp" + length + " with time zone" + array;
+ } else if ("timestamp without time zone".equals(name)) {
+ return "timestamp" + length + " without time zone" + array;
+ } else {
+ return name + length + array;
+ }
+ }
+
+ private String checkTypmod(final Integer typmod, final String name) {
+ String result = "(";
+ if ("numeric".equals(name)) {
+ int len = (typmod - 4) >> 16;
+ int prec = (typmod - 4) & 0xffff;
+ result += String.valueOf(len);
+ result += "," + prec;
+ } else if ("time".equals(name) || "timetz".equals(name) || "time without time zone".equals(name) || "time with time zone".equals(name)
+ || "timestamp".equals(name) || "timestamptz".equals(name) || "timestamp without time zone".equals(name) || "timestamp with time zone".equals(name)
+ || "bit".equals(name) || "bit varying".equals(name) || "varbit".equals(name)) {
+ int len = typmod;
+ result += String.valueOf(len);
+ } else if ("interval".equals(name)) {
+ int len = typmod & 0xffff;
+ result += len > 6 ? "" : String.valueOf(len);
+ } else if ("date".equals(name)) {
+ result = "";
+ } else {
+ int len = typmod - 4;
+ result += String.valueOf(len);
+ }
+ if (!result.isEmpty()) {
+ result += ")";
+ }
+ return result;
+ }
+
+ private String parseTypeName(final String name) {
+ String result = name;
+ boolean isArray = false;
+ if (result.endsWith("[]")) {
+ isArray = true;
+ result = result.substring(0, result.lastIndexOf("[]"));
+ }
+ int idx = result.indexOf("(");
+ if (idx > 0 && result.endsWith(")")) {
+ result = result.substring(0, idx);
+ } else if (idx > 0 && result.startsWith("time")) {
+ int endIdx = result.indexOf(")");
+ if (1 != endIdx) {
+ Matcher matcher = BRACKETS_PATTERN.matcher(result);
+ StringBuffer buffer = new StringBuffer();
+ while (matcher.find()) {
+ matcher.appendReplacement(buffer, "");
+ }
+ matcher.appendTail(buffer);
+ result = buffer.toString();
+ }
+ } else if (result.startsWith("interval")) {
+ result = "interval";
+ }
+ if (isArray) {
+ result += "[]";
+ }
+ return result;
+ }
+}
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresConstraintsLoader.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresConstraintsLoader.java
new file mode 100644
index 00000000000..080f57bf13d
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresConstraintsLoader.java
@@ -0,0 +1,262 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.infra.metadata.ddlgenerator.dialect.postgres;
+
+import java.sql.Connection;
+import java.util.Collection;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.LinkedHashMap;
+import java.util.LinkedList;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**
+ * Postgres constraints loader.
+ */
+public final class PostgresConstraintsLoader extends PostgresAbstractLoader {
+
+ public PostgresConstraintsLoader(final Connection connection) {
+ super(connection);
+ }
+
+ /**
+ * Load constraints.
+ *
+ * @param context load context
+ */
+ public void loadConstraints(final Map<String, Object> context) {
+ loadPrimaryOrUniqueConstraint(context, "primary_key", "p");
+ loadPrimaryOrUniqueConstraint(context, "unique_constraint", "u");
+ context.put("foreign_key", fetchForeignKeys(context));
+ context.put("check_constraint", fetchCheckConstraints(context));
+ context.put("exclude_constraint", getExclusionConstraints(context));
+ }
+
+ private Collection<Map<String, Object>> fetchCheckConstraints(final Map<String, Object> context) {
+ Collection<Map<String, Object>> result = new LinkedList<>();
+ for (Map<String, Object> each : getCheckConstraints((Long) context.get("tid"))) {
+ if (!isPartitionAndConstraintInherited(each, context)) {
+ result.add(each);
+ }
+ }
+ return result;
+ }
+
+ private Collection<Map<String, Object>> fetchForeignKeys(final Map<String, Object> context) {
+ Collection<Map<String, Object>> result = new LinkedList<>();
+ for (Map<String, Object> each : getForeignKeys((Long) context.get("tid"))) {
+ if (!isPartitionAndConstraintInherited(each, context)) {
+ result.add(each);
+ }
+ }
+ return result;
+ }
+
+ private void loadPrimaryOrUniqueConstraint(final Map<String, Object> context, final String name, final String type) {
+ Collection<Map<String, Object>> constraintsProperties = fetchConstraintsProperties(context, type);
+ fetchConstraintsColumns(constraintsProperties);
+ context.put(name, constraintsProperties.stream().filter(each -> !isPartitionAndConstraintInherited(each, context)).collect(Collectors.toList()));
+ }
+
+ private void fetchConstraintsColumns(final Collection<Map<String, Object>> constraintsProperties) {
+ for (Map<String, Object> each : constraintsProperties) {
+ Collection<Map<String, Object>> columns = new LinkedList<>();
+ for (Map<String, Object> col : fetchConstraintsCols(each)) {
+ Map<String, Object> column = new HashMap<>();
+ column.put("column", stripQuote((String) col.get("column")));
+ columns.add(column);
+ }
+ each.put("columns", columns);
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("cid", each.get("oid"));
+ Collection<Object> includes = new LinkedList<>();
+ for (Map<String, Object> include : executeByTemplate(param, "index_constraint/11_plus/get_constraint_include.ftl")) {
+ includes.add(include.get("colname"));
+ }
+ each.put("include", includes);
+ }
+ }
+
+ private String stripQuote(final String column) {
+ String result = column;
+ if (column.startsWith("\"")) {
+ result = result.substring(1);
+ }
+ if (column.endsWith("\"")) {
+ result = result.substring(0, result.length() - 1);
+ }
+ return result;
+ }
+
+ private Collection<Map<String, Object>> fetchConstraintsCols(final Map<String, Object> constraintColProperties) {
+ Map<String, Object> map = new HashMap<>();
+ map.put("cid", constraintColProperties.get("oid"));
+ map.put("colcnt", constraintColProperties.get("col_count"));
+ return executeByTemplate(map, "index_constraint/default/get_costraint_cols.ftl");
+ }
+
+ private Collection<Map<String, Object>> fetchConstraintsProperties(final Map<String, Object> context, final String constraintType) {
+ Map<String, Object> param = new HashMap<>();
+ param.put("did", context.get("did"));
+ param.put("tid", context.get("tid"));
+ param.put("cid", context.get("cid"));
+ param.put("constraint_type", constraintType);
+ return executeByTemplate(param, "index_constraint/11_plus/properties.ftl");
+ }
+
+ private Collection<Map<String, Object>> getExclusionConstraints(final Map<String, Object> context) {
+ Map<String, Object> param = new HashMap<>();
+ param.put("tid", context.get("tid"));
+ param.put("did", context.get("did"));
+ Collection<Map<String, Object>> result = executeByTemplate(param, "exclusion_constraint/11_plus/properties.ftl");
+ for (Map<String, Object> each : result) {
+ getExclusionConstraintsColumns(each);
+ }
+ return result;
+ }
+
+ private void getExclusionConstraintsColumns(final Map<String, Object> exclusionConstraintsProperties) {
+ Map<String, Object> param = new HashMap<>();
+ param.put("cid", exclusionConstraintsProperties.get("oid"));
+ param.put("col_count", exclusionConstraintsProperties.get("col_count"));
+ Collection<Map<String, Object>> columns = new LinkedList<>();
+ for (Map<String, Object> each : executeByTemplate(param, "exclusion_constraint/9.2_plus/get_constraint_cols.ftl")) {
+ boolean order = (((int) each.get("options")) & 1) == 0;
+ boolean nullsOrder = (((int) each.get("options")) & 2) != 0;
+ Map<String, Object> col = new HashMap<>();
+ col.put("column", strip((String) each.get("coldef")));
+ col.put("oper_class", each.get("opcname"));
+ col.put("order", order);
+ col.put("nulls_order", nullsOrder);
+ col.put("operator", each.get("oprname"));
+ col.put("col_type", each.get("datatype"));
+ col.put("is_exp", each.get("is_exp"));
+ columns.add(col);
+ }
+ exclusionConstraintsProperties.put("columns", columns);
+ Map<String, Object> map = new HashMap<>();
+ map.put("cid", exclusionConstraintsProperties.get("oid"));
+ Collection<String> include = new LinkedList<>();
+ for (Map<String, Object> each : executeByTemplate(map, "exclusion_constraint/11_plus/get_constraint_include.ftl")) {
+ include.add(each.get("colname").toString());
+ }
+ exclusionConstraintsProperties.put("include", include);
+ }
+
+ private Collection<Map<String, Object>> getForeignKeys(final Long tid) {
+ Map<String, Object> param = new HashMap<>();
+ param.put("tid", tid);
+ Collection<Map<String, Object>> result = executeByTemplate(param, "foreign_key/9.1_plus/properties.ftl");
+ for (Map<String, Object> each : result) {
+ Collection<Map<String, Object>> columns = new LinkedList<>();
+ Set<String> cols = new HashSet<>();
+ for (Map<String, Object> col : getForeignKeysCols(tid, each)) {
+ Map<String, Object> foreignKeysRef = new HashMap<>();
+ foreignKeysRef.put("local_column", col.get("conattname"));
+ foreignKeysRef.put("references", each.get("confrelid"));
+ foreignKeysRef.put("referenced", col.get("confattname"));
+ foreignKeysRef.put("references_table_name", each.get("refnsp") + "." + each.get("reftab"));
+ columns.add(foreignKeysRef);
+ cols.add((String) col.get("conattname"));
+ }
+ setRemoteName(each, columns);
+ Optional<String> coveringindex = searchCoveringIndex(tid, cols);
+ each.put("coveringindex", coveringindex.orElse(null));
+ each.put("autoindex", !coveringindex.isPresent());
+ each.put("hasindex", coveringindex.isPresent());
+ each.put("columns", columns);
+ }
+ return result;
+ }
+
+ private void setRemoteName(final Map<String, Object> foreignKey, final Collection<Map<String, Object>> columns) {
+ Map<String, Object> param = new HashMap<>();
+ param.put("tid", columns.iterator().next().get("references"));
+ Collection<Map<String, Object>> parents = executeByTemplate(param, "foreign_key/default/get_parent.ftl");
+ for (Map<String, Object> each : parents) {
+ foreignKey.put("remote_schema", each.get("schema"));
+ foreignKey.put("remote_table", each.get("table"));
+ break;
+ }
+ }
+
+ private Collection<Map<String, Object>> getForeignKeysCols(final Long tid, final Map<String, Object> foreignKeyProperties) {
+ Map<String, Object> param = new HashMap<>();
+ param.put("tid", tid);
+ Collection<Map<String, Object>> keys = new LinkedList<>();
+ Map<String, Object> key = new HashMap<>();
+ key.put("confkey", foreignKeyProperties.get("confkey"));
+ key.put("conkey", foreignKeyProperties.get("conkey"));
+ keys.add(key);
+ param.put("keys", keys);
+ return executeByTemplate(param, "foreign_key/default/get_constraint_cols.ftl");
+ }
+
+ private boolean isPartitionAndConstraintInherited(final Map<String, Object> constraint, final Map<String, Object> context) {
+ return context.containsKey("relispartition") && (boolean) context.get("relispartition") && constraint.containsKey("conislocal") && (boolean) constraint.get("conislocal");
+ }
+
+ private Optional<String> searchCoveringIndex(final Long tid, final Set<String> cols) {
+ Map<String, Object> param = new HashMap<>();
+ param.put("tid", tid);
+ for (Map<String, Object> each : executeByTemplate(param, "foreign_key/default/get_constraints.ftl")) {
+ Map<String, Object> map = new HashMap<>();
+ map.put("cid", each.get("oid"));
+ map.put("colcnt", each.get("col_count"));
+ Collection<Map<String, Object>> rows = executeByTemplate(map, "foreign_key/default/get_cols.ftl");
+ Set<String> indexCols = new HashSet<>();
+ for (Map<String, Object> row : rows) {
+ indexCols.add(strip(row.get("column").toString()));
+ }
+ if (isSame(indexCols, cols)) {
+ return Optional.of((String) each.get("idxname"));
+ }
+ }
+ return Optional.empty();
+ }
+
+ private boolean isSame(final Set<String> indexCols, final Set<String> cols) {
+ Set<String> copyIndexCols = new HashSet<>(indexCols);
+ Set<String> copyCols = new HashSet<>(cols);
+ copyIndexCols.removeAll(copyCols);
+ if (0 == copyIndexCols.size()) {
+ cols.removeAll(indexCols);
+ return 0 == cols.size();
+ }
+ return false;
+ }
+
+ private String strip(final String column) {
+ if (column.startsWith("\"")) {
+ return column.substring(1);
+ }
+ if (column.endsWith("\"")) {
+ return column.substring(0, column.length() - 1);
+ }
+ return column;
+ }
+
+ private Collection<Map<String, Object>> getCheckConstraints(final Long tid) {
+ Map<String, Object> param = new HashMap<>();
+ param.put("tid", tid);
+ return executeByTemplate(param, "check_constraint/9.2_plus/get_cols.ftl");
+ }
+}
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresTablePropertiesLoader.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresTablePropertiesLoader.java
new file mode 100644
index 00000000000..631d6204e0a
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/dialect/postgres/PostgresTablePropertiesLoader.java
@@ -0,0 +1,147 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.infra.metadata.ddlgenerator.dialect.postgres;
+
+import lombok.SneakyThrows;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.LinkedHashMap;
+import java.util.Map;
+
+/**
+ * Postgres table properties loader.
+ */
+public final class PostgresTablePropertiesLoader extends PostgresAbstractLoader {
+
+ private final String tableName;
+
+ private final String schemaName;
+
+ public PostgresTablePropertiesLoader(final Connection connection, final String tableName, final String schemaName) {
+ super(connection);
+ this.tableName = tableName;
+ this.schemaName = schemaName;
+ }
+
+ /**
+ * Load table properties.
+ *
+ * @return table properties
+ */
+ @SneakyThrows
+ public Map<String, Object> loadTableProperties() {
+ Map<String, Object> result = new LinkedHashMap<>();
+ fetchDataBaseId(result);
+ fetchSchemaId(result);
+ fetchTableId(result);
+ fetchTableProperties(result);
+ return result;
+ }
+
+ private void fetchDataBaseId(final Map<String, Object> context) throws SQLException {
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("databaseName", getConnection().getCatalog());
+ appendFirstRow(executeByTemplate(param, "table/default/get_database_id.ftl"), context);
+ }
+
+ private void fetchTableId(final Map<String, Object> context) {
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("schemaName", schemaName);
+ param.put("tableName", tableName);
+ appendFirstRow(executeByTemplate(param, "table/default/get_table_id.ftl"), context);
+ }
+
+ private void fetchSchemaId(final Map<String, Object> context) {
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("schemaName", schemaName);
+ appendFirstRow(executeByTemplate(param, "table/default/get_schema_id.ftl"), context);
+ }
+
+ private void fetchTableProperties(final Map<String, Object> context) {
+ appendFirstRow(executeByTemplate(context, "table/12_plus/properties.ftl"), context);
+ updateAutovacuumProperties(context);
+ checkRlspolicySupport(context);
+ }
+
+ private void updateAutovacuumProperties(final Map<String, Object> context) {
+ if (null == context.get("autovacuum_enabled")) {
+ context.put("autovacuum_enabled", "x");
+ } else if ("true".equalsIgnoreCase(context.get("autovacuum_enabled").toString())) {
+ context.put("autovacuum_enabled", "t");
+ } else {
+ context.put("autovacuum_enabled", "f");
+ }
+ if (null == context.get("toast_autovacuum_enabled")) {
+ context.put("toast_autovacuum_enabled", "x");
+ } else if ("true".equalsIgnoreCase(context.get("toast_autovacuum_enabled").toString())) {
+ context.put("toast_autovacuum_enabled", "t");
+ } else {
+ context.put("toast_autovacuum_enabled", "f");
+ }
+ context.put("autovacuum_custom", anyIsTrue(Arrays.asList(
+ context.get("autovacuum_vacuum_threshold"),
+ context.get("autovacuum_vacuum_scale_factor"),
+ context.get("autovacuum_analyze_threshold"),
+ context.get("autovacuum_analyze_scale_factor"),
+ context.get("autovacuum_vacuum_cost_delay"),
+ context.get("autovacuum_vacuum_cost_limit"),
+ context.get("autovacuum_freeze_min_age"),
+ context.get("autovacuum_freeze_max_age"),
+ context.get("autovacuum_freeze_table_age"))) || "t".equals(context.get("autovacuum_enabled")) || "f".equals(context.get("autovacuum_enabled")));
+ context.put("toast_autovacuum", anyIsTrue(Arrays.asList(
+ context.get("toast_autovacuum_vacuum_threshold"),
+ context.get("toast_autovacuum_vacuum_scale_factor"),
+ context.get("toast_autovacuum_analyze_threshold"),
+ context.get("toast_autovacuum_analyze_scale_factor"),
+ context.get("toast_autovacuum_vacuum_cost_delay"),
+ context.get("toast_autovacuum_vacuum_cost_limit"),
+ context.get("toast_autovacuum_freeze_min_age"),
+ context.get("toast_autovacuum_freeze_max_age"),
+ context.get("toast_autovacuum_freeze_table_age"))) || "t".equals(context.get("toast_autovacuum_enabled")) || "f".equals(context.get("toast_autovacuum_enabled")));
+ }
+
+ private void checkRlspolicySupport(final Map<String, Object> context) {
+ if (context.containsKey("rlspolicy")) {
+ if (context.get("rlspolicy") instanceof String && "true".equals(context.get("rlspolicy"))) {
+ context.put("rlspolicy", true);
+ }
+ if (context.get("forcerlspolicy") instanceof String && "true".equals(context.get("forcerlspolicy"))) {
+ context.put("forcerlspolicy", true);
+ }
+ }
+ }
+
+ private boolean anyIsTrue(final Collection<Object> collection) {
+ for (Object each : collection) {
+ if (each instanceof Boolean && (Boolean) each) {
+ return true;
+ }
+ }
+ return false;
+ }
+
+ private void appendFirstRow(final Collection<Map<String, Object>> rows, final Map<String, Object> context) {
+ for (Map<String, Object> each : rows) {
+ context.putAll(each);
+ break;
+ }
+ }
+}
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/spi/DialectDDLSQLGenerator.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/spi/DialectDDLSQLGenerator.java
new file mode 100644
index 00000000000..5ec4ce16356
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/spi/DialectDDLSQLGenerator.java
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.infra.metadata.ddlgenerator.spi;
+
+import org.apache.shardingsphere.spi.type.typed.StatelessTypedSPI;
+
+import javax.sql.DataSource;
+import java.sql.SQLException;
+
+/**
+ * Dialect DDL SQL generator.
+ */
+public interface DialectDDLSQLGenerator extends StatelessTypedSPI {
+
+ /**
+ * Generate DDL SQL.
+ *
+ * @param tableName table name
+ * @param schemaName schema name
+ * @param dataSource dataSource
+ * @return sql
+ * @throws SQLException sql exception
+ */
+ String generateDDLSQL(String tableName, String schemaName, DataSource dataSource) throws SQLException;
+}
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/spi/DialectDDLSQLGeneratorFactory.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/spi/DialectDDLSQLGeneratorFactory.java
new file mode 100644
index 00000000000..38089828205
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/spi/DialectDDLSQLGeneratorFactory.java
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.infra.metadata.ddlgenerator.spi;
+
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+import org.apache.shardingsphere.infra.database.type.DatabaseType;
+import org.apache.shardingsphere.spi.ShardingSphereServiceLoader;
+import org.apache.shardingsphere.spi.type.typed.TypedSPIRegistry;
+
+import java.util.Optional;
+
+/**
+ * Dialect DDL SQL generator factory.
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public final class DialectDDLSQLGeneratorFactory {
+
+ static {
+ ShardingSphereServiceLoader.register(DialectDDLSQLGenerator.class);
+ }
+
+ /**
+ * Create new instance of dialect DDL SQL generator.
+ *
+ * @param databaseType database type
+ * @return new instance of dialect DDL SQL generator
+ */
+ public static Optional<DialectDDLSQLGenerator> newInstance(final DatabaseType databaseType) {
+ return TypedSPIRegistry.findRegisteredService(DialectDDLSQLGenerator.class, databaseType.getName());
+ }
+}
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/util/FreemarkerManager.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/util/FreemarkerManager.java
new file mode 100644
index 00000000000..0191dac1d67
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/ddlgenerator/util/FreemarkerManager.java
@@ -0,0 +1,73 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.infra.metadata.ddlgenerator.util;
+
+import freemarker.template.Configuration;
+import freemarker.template.Template;
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+import lombok.SneakyThrows;
+
+import java.io.File;
+import java.io.StringWriter;
+import java.util.Map;
+import java.util.Objects;
+
+/**
+ * Freemarker manager.
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public final class FreemarkerManager {
+
+ private static final FreemarkerManager INSTANCE = new FreemarkerManager();
+
+ private final Configuration templateConfig = createTemplateConfiguration();
+
+ /**
+ * Get freemarker manager instance.
+ *
+ * @return freemarker manager instance
+ */
+ public static FreemarkerManager getInstance() {
+ return INSTANCE;
+ }
+
+ @SneakyThrows
+ private Configuration createTemplateConfiguration() {
+ Configuration result = new Configuration(Configuration.VERSION_2_3_31);
+ result.setDirectoryForTemplateLoading(new File(Objects.requireNonNull(FreemarkerManager.class.getClassLoader().getResource("template")).getFile()));
+ result.setDefaultEncoding("UTF-8");
+ return result;
+ }
+
+ /**
+ * Get sql from template.
+ *
+ * @param data data
+ * @param path path
+ * @return sql
+ */
+ @SneakyThrows
+ public static String getSqlFromTemplate(final Map<String, Object> data, final String path) {
+ Template template = FreemarkerManager.getInstance().templateConfig.getTemplate(path);
+ try (StringWriter result = new StringWriter()) {
+ template.process(data, result);
+ return result.toString();
+ }
+ }
+}
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/META-INF/services/org.apache.shardingsphere.infra.metadata.ddlgenerator.spi.DialectDDLSQLGenerator b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/META-INF/services/org.apache.shardingsphere.infra.metadata.ddlgenerator.spi.DialectDDLSQLGenerator
new file mode 100644
index 00000000000..18e416e59d3
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/META-INF/services/org.apache.shardingsphere.infra.metadata.ddlgenerator.spi.DialectDDLSQLGenerator
@@ -0,0 +1,18 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+org.apache.shardingsphere.infra.metadata.ddlgenerator.dialect.postgres.PostgreDDLSQLGenerator
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/check_constraint/9.2_plus/get_cols.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/check_constraint/9.2_plus/get_cols.ftl
new file mode 100644
index 00000000000..7a4a471c8ab
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/check_constraint/9.2_plus/get_cols.ftl
@@ -0,0 +1,28 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT c.oid, conname as name, relname, nspname, description as comment,
+pg_catalog.pg_get_expr(conbin, conrelid, true) as consrc,
+connoinherit, NOT convalidated as convalidated, conislocal
+FROM pg_catalog.pg_constraint c
+JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
+JOIN pg_catalog.pg_namespace nl ON nl.oid=relnamespace
+LEFT OUTER JOIN
+pg_catalog.pg_description des ON (des.objoid=c.oid AND
+des.classoid='pg_constraint'::regclass)
+WHERE contype = 'c'
+AND conrelid = ${ tid?c }::oid
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/columns/12_plus/properties.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/columns/12_plus/properties.ftl
new file mode 100644
index 00000000000..4075ad0b4f4
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/columns/12_plus/properties.ftl
@@ -0,0 +1,56 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT att.attname as name, att.atttypid, att.attlen, att.attnum, att.attndims,
+att.atttypmod, att.attacl, att.attnotnull, att.attoptions, att.attstattarget,
+att.attstorage, att.attidentity,
+pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval,
+pg_catalog.format_type(ty.oid,NULL) AS typname,
+pg_catalog.format_type(ty.oid,att.atttypmod) AS displaytypname,
+pg_catalog.format_type(ty.oid,att.atttypmod) AS cltype,
+CASE WHEN ty.typelem > 0 THEN ty.typelem ELSE ty.oid END as elemoid,
+(SELECT nspname FROM pg_catalog.pg_namespace WHERE oid = ty.typnamespace) as typnspname,
+ty.typstorage AS defaultstorage,
+description, pi.indkey,
+(SELECT count(1) FROM pg_catalog.pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup,
+CASE WHEN length(coll.collname::text) > 0 AND length(nspc.nspname::text) > 0 THEN
+pg_catalog.concat(pg_catalog.quote_ident(nspc.nspname),'.',pg_catalog.quote_ident(coll.collname))
+ELSE '' END AS collspcname,
+EXISTS(SELECT 1 FROM pg_catalog.pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As is_fk,
+(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=att.attrelid AND sl1.objsubid=att.attnum) AS seclabels,
+(CASE WHEN (att.attnum < 1) THEN true ElSE false END) AS is_sys_column,
+(CASE WHEN (att.attidentity in ('a', 'd')) THEN 'i' WHEN (att.attgenerated in ('s')) THEN 'g' ELSE 'n' END) AS colconstype,
+(CASE WHEN (att.attgenerated in ('s')) THEN pg_catalog.pg_get_expr(def.adbin, def.adrelid) END) AS genexpr, tab.relname as relname,
+(CASE WHEN tab.relkind = 'v' THEN true ELSE false END) AS is_view_only,
+seq.*
+FROM pg_catalog.pg_attribute att
+JOIN pg_catalog.pg_type ty ON ty.oid=atttypid
+LEFT OUTER JOIN pg_catalog.pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
+LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=att.attrelid AND des.objsubid=att.attnum AND des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN (pg_catalog.pg_depend dep JOIN pg_catalog.pg_class cs ON dep.classid='pg_class'::regclass AND dep.objid=cs.oid AND cs.relkind='S') ON dep.refobjid=att.attrelid AND dep.refobjsubid=att.attnum
+LEFT OUTER JOIN pg_catalog.pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
+LEFT OUTER JOIN pg_catalog.pg_collation coll ON att.attcollation=coll.oid
+LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON coll.collnamespace=nspc.oid
+LEFT OUTER JOIN pg_catalog.pg_sequence seq ON cs.oid=seq.seqrelid
+LEFT OUTER JOIN pg_catalog.pg_class tab on tab.oid = att.attrelid
+WHERE att.attrelid = ${tid?c}::oid
+<#if clid?? >
+ AND att.attnum = ${clid?c}::int
+</#if>
+AND att.attnum > 0
+AND att.attisdropped IS FALSE
+ORDER BY att.attnum;
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/columns/default/edit_mode_types_multi.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/columns/default/edit_mode_types_multi.ftl
new file mode 100644
index 00000000000..afdf9bbadf0
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/columns/default/edit_mode_types_multi.ftl
@@ -0,0 +1,30 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT t.main_oid, pg_catalog.ARRAY_AGG(t.typname) as edit_types
+FROM
+(SELECT pc.castsource AS main_oid, pg_catalog.format_type(tt.oid,NULL) AS typname
+FROM pg_catalog.pg_type tt
+JOIN pg_catalog.pg_cast pc ON tt.oid=pc.casttarget
+WHERE pc.castsource IN (${type_ids})
+AND pc.castcontext IN ('i', 'a')
+UNION
+SELECT tt.typbasetype AS main_oid, pg_catalog.format_type(tt.oid,NULL) AS typname
+FROM pg_catalog.pg_type tt
+WHERE tt.typbasetype IN (${type_ids})
+) t
+GROUP BY t.main_oid;
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/exclusion_constraint/11_plus/get_constraint_include.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/exclusion_constraint/11_plus/get_constraint_include.ftl
new file mode 100644
index 00000000000..86712a38008
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/exclusion_constraint/11_plus/get_constraint_include.ftl
@@ -0,0 +1,31 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT a.attname as colname
+FROM (
+SELECT
+i.indnkeyatts,
+i.indrelid,
+pg_catalog.unnest(indkey) AS table_colnum,
+pg_catalog.unnest(ARRAY(SELECT pg_catalog.generate_series(1, i.indnatts) AS n)) attnum
+FROM
+pg_catalog.pg_index i
+WHERE i.indexrelid = ${cid}::OID
+) i JOIN pg_catalog.pg_attribute a
+ON (a.attrelid = i.indrelid AND i.table_colnum = a.attnum)
+WHERE i.attnum > i.indnkeyatts
+ORDER BY i.attnum
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/exclusion_constraint/11_plus/properties.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/exclusion_constraint/11_plus/properties.ftl
new file mode 100644
index 00000000000..902e631763e
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/exclusion_constraint/11_plus/properties.ftl
@@ -0,0 +1,47 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT cls.oid,
+cls.relname as name,
+indnkeyatts as col_count,
+amname,
+CASE WHEN length(spcname::text) > 0 THEN spcname ELSE
+(SELECT sp.spcname FROM pg_catalog.pg_database dtb
+JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
+WHERE dtb.oid = ${ did?c }::oid)
+END as spcname,
+CASE contype
+WHEN 'p' THEN desp.description
+WHEN 'u' THEN desp.description
+WHEN 'x' THEN desp.description
+ELSE des.description
+END AS comment,
+condeferrable,
+condeferred,
+substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor,
+pg_catalog.pg_get_expr(idx.indpred, idx.indrelid, true) AS indconstraint
+FROM pg_catalog.pg_index idx
+JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
+LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
+JOIN pg_catalog.pg_am am ON am.oid=cls.relam
+LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
+LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
+LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
+WHERE indrelid = ${tid?c}::oid
+AND contype='x'
+ORDER BY cls.relname
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/exclusion_constraint/9.2_plus/get_constraint_cols.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/exclusion_constraint/9.2_plus/get_constraint_cols.ftl
new file mode 100644
index 00000000000..eaeb7e104be
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/exclusion_constraint/9.2_plus/get_constraint_cols.ftl
@@ -0,0 +1,40 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<#list (0..colcnt) as n >
+<#if n?counter != 1 >
+UNION
+</#if>
+SELECT
+i.indoption[${n?index}] AS options,
+pg_catalog.pg_get_indexdef(i.indexrelid, ${n?counter}, true) AS coldef,
+op.oprname,
+CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname
+,
+coll.collname,
+nspc.nspname as collnspname,
+pg_catalog.format_type(ty.oid,NULL) AS datatype,
+CASE WHEN pg_catalog.pg_get_indexdef(i.indexrelid, ${n?counter}, true) = a.attname THEN FALSE ELSE TRUE END AS is_exp
+FROM pg_catalog.pg_index i
+JOIN pg_catalog.pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = ${n?counter})
+JOIN pg_catalog.pg_type ty ON ty.oid=a.atttypid
+LEFT OUTER JOIN pg_catalog.pg_opclass o ON (o.oid = i.indclass[${n?index}])
+LEFT OUTER JOIN pg_catalog.pg_constraint c ON (c.conindid = i.indexrelid) LEFT OUTER JOIN pg_catalog.pg_operator op ON (op.oid = c.conexclop[${n?counter}])
+LEFT OUTER JOIN pg_catalog.pg_collation coll ON a.attcollation=coll.oid
+LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON coll.collnamespace=nspc.oid
+WHERE i.indexrelid = ${cid}::oid
+</#list>
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/9.1_plus/properties.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/9.1_plus/properties.ftl
new file mode 100644
index 00000000000..30ac6fe9f2f
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/9.1_plus/properties.ftl
@@ -0,0 +1,46 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT ct.oid,
+conname as name,
+condeferrable,
+condeferred,
+confupdtype,
+confdeltype,
+CASE confmatchtype
+WHEN 's' THEN FALSE
+WHEN 'f' THEN TRUE
+END AS confmatchtype,
+conkey,
+confkey,
+confrelid,
+nl.nspname as fknsp,
+cl.relname as fktab,
+nr.nspname as refnsp,
+cr.relname as reftab,
+description as comment,
+convalidated,
+conislocal
+FROM pg_catalog.pg_constraint ct
+JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
+JOIN pg_catalog.pg_namespace nl ON nl.oid=cl.relnamespace
+JOIN pg_catalog.pg_class cr ON cr.oid=confrelid
+JOIN pg_catalog.pg_namespace nr ON nr.oid=cr.relnamespace
+LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=ct.oid AND des.classoid='pg_constraint'::regclass)
+WHERE contype='f' AND
+conrelid = ${tid?c}::oid
+ORDER BY conname
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_cols.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_cols.ftl
new file mode 100644
index 00000000000..345bf2fca13
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_cols.ftl
@@ -0,0 +1,24 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<#list (0..colcnt) as n >
+<#if n?counter != 1 >
+UNION SELECT pg_catalog.pg_get_indexdef(${ cid?c }, ${ n?counter?c }, true) AS column
+<#else>
+SELECT pg_catalog.pg_get_indexdef(${ cid?c } , ${ n?counter?c } , true) AS column
+</#if>
+</#list>
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_constraint_cols.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_constraint_cols.ftl
new file mode 100644
index 00000000000..4874c8e6117
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_constraint_cols.ftl
@@ -0,0 +1,30 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<#list keys as keypair >
+<#if keypair?counter != 1 >
+ UNION
+</#if>
+SELECT a1.attname as conattname,
+a2.attname as confattname
+FROM pg_catalog.pg_attribute a1,
+pg_catalog.pg_attribute a2
+WHERE a1.attrelid=${tid}::oid
+AND a1.attnum=${keypair.conkey}
+AND a2.attrelid=${confrelid}::oid
+AND a2.attnum=${keypair.confkey}
+</#list>
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_constraints.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_constraints.ftl
new file mode 100644
index 00000000000..2cb7f6315c1
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_constraints.ftl
@@ -0,0 +1,54 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT cls.oid, cls.relname as idxname, indnatts as col_count
+FROM pg_catalog.pg_index idx
+JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
+LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
+LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
+WHERE idx.indrelid = ${tid}::oid
+AND con.contype='p'
+
+UNION
+
+SELECT cls.oid, cls.relname as idxname, indnatts
+FROM pg_catalog.pg_index idx
+JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
+LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
+LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
+WHERE idx.indrelid = ${tid}::oid
+AND con.contype='x'
+
+UNION
+
+SELECT cls.oid, cls.relname as idxname, indnatts
+FROM pg_catalog.pg_index idx
+JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
+LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
+LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
+WHERE idx.indrelid = ${tid}::oid
+AND con.contype='u'
+
+UNION
+
+SELECT cls.oid, cls.relname as idxname, indnatts
+FROM pg_catalog.pg_index idx
+JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
+LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
+LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
+WHERE idx.indrelid = ${tid}::oid
+AND conname IS NULL
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_parent.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_parent.ftl
new file mode 100644
index 00000000000..e87fe363020
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/foreign_key/default/get_parent.ftl
@@ -0,0 +1,24 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT nsp.nspname AS schema,
+rel.relname AS table
+FROM
+pg_catalog.pg_class rel
+JOIN pg_catalog.pg_namespace nsp
+ON rel.relnamespace = nsp.oid::oid
+WHERE rel.oid = ${tid?c}::oid
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/index_constraint/11_plus/get_constraint_include.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/index_constraint/11_plus/get_constraint_include.ftl
new file mode 100644
index 00000000000..05e357b2101
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/index_constraint/11_plus/get_constraint_include.ftl
@@ -0,0 +1,31 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT a.attname as colname
+FROM (
+SELECT
+i.indnkeyatts,
+i.indrelid,
+pg_catalog.unnest(indkey) AS table_colnum,
+pg_catalog.unnest(ARRAY(SELECT pg_catalog.generate_series(1, i.indnatts) AS n)) attnum
+FROM
+pg_catalog.pg_index i
+WHERE i.indexrelid = ${cid?c}::OID
+) i JOIN pg_catalog.pg_attribute a
+ON (a.attrelid = i.indrelid AND i.table_colnum = a.attnum)
+WHERE i.attnum > i.indnkeyatts
+ORDER BY i.attnum
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/index_constraint/11_plus/properties.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/index_constraint/11_plus/properties.ftl
new file mode 100644
index 00000000000..109e836a137
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/index_constraint/11_plus/properties.ftl
@@ -0,0 +1,48 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT cls.oid,
+cls.relname as name,
+indnkeyatts as col_count,
+CASE WHEN length(spcname::text) > 0 THEN spcname ELSE
+(SELECT sp.spcname FROM pg_catalog.pg_database dtb
+JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
+WHERE dtb.oid = ${ did?c }::oid)
+END as spcname,
+CASE contype
+WHEN 'p' THEN desp.description
+WHEN 'u' THEN desp.description
+WHEN 'x' THEN desp.description
+ELSE des.description
+END AS comment,
+condeferrable,
+condeferred,
+conislocal,
+substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
+FROM pg_catalog.pg_index idx
+JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
+LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
+LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
+LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
+LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
+WHERE indrelid = ${tid?c}::oid
+<#if cid?? >
+AND cls.oid = ${cid?c}::oid
+</#if>
+AND contype='${constraint_type}'
+ORDER BY cls.relname
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/index_constraint/default/get_costraint_cols.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/index_constraint/default/get_costraint_cols.ftl
new file mode 100644
index 00000000000..7424e5fc4b1
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/index_constraint/default/get_costraint_cols.ftl
@@ -0,0 +1,27 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT * FROM (
+<#list 1..(colcnt?number)!1 as i>
+ <#if i != 1>
+ UNION SELECT pg_catalog.pg_get_indexdef(${ cid?c }, ${ i?c }, true) AS column, ${ i } AS dummy
+ <#else>
+ SELECT pg_catalog.pg_get_indexdef(${ cid?c }, ${ i?c }, true) AS column, ${ i } AS dummy
+ </#if>
+</#list>
+) tmp
+ORDER BY dummy
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/macro/constraints.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/macro/constraints.ftl
new file mode 100644
index 00000000000..bc334709526
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/macro/constraints.ftl
@@ -0,0 +1,106 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<#macro PRIMARY_KEY data>
+<#if data.columns?size gt 0 >
+<#if data.name?? >CONSTRAINT ${data.name} </#if>PRIMARY KEY (<#list data.columns as c>
+<#if c?counter != 1 >, </#if>${c.column}</#list>)<#if data.include?size gt 0 >
+INCLUDE(<#list data.include as col ><#if col?counter != 1 >, </#if>${col}</#list>)</#if>
+<#if data.fillfactor?? >
+WITH (FILLFACTOR=${data.fillfactor})</#if>
+<#if data.spcname?? && data.spcname != "pg_default" >
+USING INDEX TABLESPACE ${data.spcname }</#if>
+<#if data.condeferrable!false >
+DEFERRABLE<#if data.condeferred!false > INITIALLY DEFERRED</#if></#if>
+</#if>
+</#macro>
+
+<#macro UNIQUE unique_data >
+<#list unique_data as data >
+<#if data.columns?size gt 0 ><#if data?counter !=1 >,</#if>
+<#if data.name?? >CONSTRAINT ${data.name} </#if>UNIQUE (<#list data.columns as c>
+<#if c?counter != 1 >, </#if>${c.column}</#list>)<#if data.include?size gt 0 >
+INCLUDE(<#list data.include as col><#if col?counter != 1 >, </#if>${col}</#list>)</#if>
+<#if data.fillfactor?? >
+WITH (FILLFACTOR=${data.fillfactor})</#if>
+<#if data.spcname?? && data.spcname != "pg_default" >
+USING INDEX TABLESPACE ${data.spcname}</#if>
+<#if data.condeferrable!false >
+DEFERRABLE<#if data.condeferred!false > INITIALLY DEFERRED</#if></#if>
+</#if>
+</#list>
+</#macro>
+
+<#macro FOREIGN_KEY foreign_key_data >
+<#list foreign_key_data as data><#if data?counter != 1 >,</#if >
+<#if data.name?? >CONSTRAINT ${data.name} </#if >FOREIGN KEY (<#list data.columns as columnobj ><#if columnobj?counter != 1 >
+, </#if >${columnobj.local_column}</#list>)
+REFERENCES ${data.remote_schema}${data.remote_table } (<#list data.columns as columnobj><#if columnobj?counter != 1 >
+, </#if >${columnobj.referenced}</#list>) <#if data.confmatchtype!false >MATCH FULL<#else >MATCH SIMPLE</#if>
+ON UPDATE<#if data.confupdtype == 'a' >
+NO ACTION<#elseif data.confupdtype == 'r' >
+RESTRICT<#elseif data.confupdtype == 'c' >
+CASCADE<#elseif data.confupdtype == 'n' >
+SET NULL<#elseif data.confupdtype == 'd' >
+SET DEFAULT</#if >
+ON DELETE<#if data.confdeltype == 'a' >
+NO ACTION<#elseif data.confdeltype == 'r' >
+RESTRICT<#elseif data.confdeltype == 'c' >
+CASCADE<#elseif data.confdeltype == 'n' >
+SET NULL<#elseif data.confdeltype == 'd' >
+SET DEFAULT</#if >
+<#if data.condeferrable >
+DEFERRABLE<#if data.condeferred >
+INITIALLY DEFERRED</#if>
+</#if>
+<#if !data.convalidated >
+NOT VALID</#if>
+</#list>
+</#macro>
+
+<#macro CHECK check_data >
+<#list check_data as data><#if data?counter !=1 >,</#if >
+<#if data.name?? >CONSTRAINT ${data.name } </#if>CHECK (${ data.consrc })<#if data.convalidated!false >
+NOT VALID</#if ><#if data.connoinherit!false > NO INHERIT</#if >
+</#list>
+</#macro>
+
+<#macro EXCLUDE exclude_data >
+<#list exclude_data as data ><#if data?counter != 1 >,</#if >
+<#if data.name?? >CONSTRAINT ${data.name } </#if>EXCLUDE <#if data.amname?? && data.amname != '' >USING ${data.amname}</#if > (
+<#list data.columns as col><#if col?counter != 1 >,
+</#if ><#if col.is_exp!false >${col.column}<#else >${col.column}</#if ><#if col.oper_class?? && col.oper_class != '' > ${col.oper_class}</#if><#if col.order?? && col.is_sort_nulls_applicable!false ><#if col.order!false > ASC<#else > DESC</#if > NULLS</#if > <#if col.nulls_order!false && col.is_sort_nulls_applicable!false ><#if col.nulls_order!false >FIRST <#else >LAST </#if ></#if >WITH ${col.operator}</#list>)
+<#if data.include?size gt 0 >
+INCLUDE(<#list data.include as col ><#if col?counter != 1 >, </#if >${col}</#list>)
+</#if ><#if data.fillfactor?? >
+WITH (FILLFACTOR=${data.fillfactor})</#if ><#if data.spcname?? && data.spcname != "pg_default" >
+USING INDEX TABLESPACE ${data.spcname }</#if ><#if data.indconstraint?? >
+WHERE (${data.indconstraint})</#if><#if data.condeferrable!false >
+DEFERRABLE<#if data.condeferred!false >
+INITIALLY DEFERRED</#if>
+</#if>
+</#list>
+</#macro>
+
+<#macro CONSTRAINT_COMMENTS schema, table, data >
+<#list data as d>
+<#if d.name?? && d.comment?? >
+COMMENT ON CONSTRAINT ${d.name } ON ${schema}.${table}
+IS '${d.comment}';
+</#if>
+</#list>
+</#macro>
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/macro/db_catalogs.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/macro/db_catalogs.ftl
new file mode 100644
index 00000000000..7fe3c1662ff
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/macro/db_catalogs.ftl
@@ -0,0 +1,22 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<#macro VALID_CATALOGS(server_type) >
+AND n.nspname NOT LIKE 'pg\_%' <#if server_type == 'ppas' >
+AND n.nspname NOT IN ('information_schema', 'pgagent', 'dbo', 'sys') <#else>
+AND n.nspname NOT IN ('information_schema') </#if>
+</#macro>
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/10_plus/get_columns_for_table.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/10_plus/get_columns_for_table.ftl
new file mode 100644
index 00000000000..e6994f0c35a
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/10_plus/get_columns_for_table.ftl
@@ -0,0 +1,37 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT
+ a.attname AS name, pg_catalog.format_type(a.atttypid, NULL) AS cltype,
+ pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval, a.attidentity as clidentity,
+ pg_catalog.quote_ident(n.nspname)||'.'||pg_catalog.quote_ident(c.relname) as inheritedfrom,
+ c.oid as inheritedid
+FROM
+ pg_catalog.pg_class c
+JOIN
+ pg_catalog.pg_namespace n ON c.relnamespace=n.oid
+JOIN
+ pg_catalog.pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped AND a.attnum > 0
+LEFT OUTER JOIN
+ pg_catalog.pg_attrdef def ON adrelid=a.attrelid AND adnum=a.attnum
+WHERE
+<#if tid?? >
+ c.oid = ${tid?c}::OID
+<#else >
+ c.relname = '${tname}'
+</#if>
+
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/10_plus/get_inherits.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/10_plus/get_inherits.ftl
new file mode 100644
index 00000000000..524279bdddb
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/10_plus/get_inherits.ftl
@@ -0,0 +1,29 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<#import "../../macro/db_catalogs.ftl" as CATALOG>
+SELECT c.oid, c.relname , nspname,
+CASE WHEN nspname NOT LIKE 'pg\_%' THEN
+pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(c.relname)
+ELSE pg_catalog.quote_ident(c.relname)
+END AS inherits
+FROM pg_catalog.pg_class c
+JOIN pg_catalog.pg_namespace n
+ON n.oid=c.relnamespace
+WHERE relkind='r' AND NOT relispartition
+<@CATALOG.VALID_CATALOGS server_type />
+ORDER BY relnamespace, c.relname
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/12_plus/create.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/12_plus/create.ftl
new file mode 100644
index 00000000000..8a2e47855d0
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/12_plus/create.ftl
@@ -0,0 +1,121 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<#import "../../macro/constraints.ftl" as CONSTRAINTS>
+<#assign with_clause = false>
+<#if fillfactor!false || parallel_workers!false || toast_tuple_target!false || (autovacuum_custom!false && add_vacuum_settings_in_sql!false) || autovacuum_enabled == 't' || autovacuum_enabled == 'f' || (toast_autovacuum!false && add_vacuum_settings_in_sql!false) || toast_autovacuum_enabled == 't' || toast_autovacuum_enabled == 'f' >
+ <#assign with_clause = true>
+</#if>
+CREATE <#if relpersistence!false >UNLOGGED </#if>TABLE IF NOT EXISTS ${schema}.${name}
+<#if typname?? >
+OF ${typname }
+</#if>
+<#if like_relation?? || coll_inherits?size gt 0 || columns?size gt 0 || primary_key?size gt 0 || unique_constraint?size gt 0 || foreign_key?size gt 0 || check_constraint?size gt 0 || exclude_constraint?size gt 0 >
+(
+</#if>
+<#if like_relation?? >
+LIKE ${like_relation }<#if like_default_value!false >
+ INCLUDING DEFAULTS</#if><#if like_constraints!false >
+ INCLUDING CONSTRAINTS</#if><#if like_indexes!false >
+ INCLUDING INDEXES</#if><#if like_storage!false >
+ INCLUDING STORAGE</#if><#if like_comments!false >
+ INCLUDING COMMENTS</#if><#if columns?size gt 0 >,
+</#if>
+</#if>
+<#if columns?? && columns?size gt 0 >
+<#list columns as c >
+<#if c.name?? && c.cltype?? >
+<#if c.inheritedfromtable?? >-- Inherited from table ${c.inheritedfromtable}: <#elseif c.inheritedfromtype?? >-- Inherited from type ${c.inheritedfromtype}: </#if>${c.name} ${c.displaytypname}<#if c.collspcname?? && c.collspcname?length gt 0 > COLLATE ${c.collspcname}</#if><#if c.attnotnull!false > NOT NULL</#if><#if c.defval?? && c.defval != '' && c.colconstype?? && c.colconstype != 'g' > DEFAULT ${c.defval}</#if>
+<#if c.colconstype?? && c.colconstype == 'i' && c.attidentity?? && c.attidentity != '' >
+<#if c.attidentity?? && c.attidentity == 'a' > GENERATED ALWAYS AS IDENTITY<#elseif c.attidentity?? && c.attidentity == 'd' > GENERATED BY DEFAULT AS IDENTITY</#if>
+<#if c.seqincrement?? || c.seqcycle!false || c.seqincrement?? || c.seqstart?? || c.seqmin?? || c.seqmax?? || c.seqcache?? > ( </#if>
+<#if c.seqcycle!false >
+CYCLE </#if><#if c.seqincrement?? && c.seqincrement?number gt -1 >
+INCREMENT ${c.seqincrement} </#if><#if c.seqstart?? && c.seqstart?number gt -1>
+START ${c.seqstart} </#if><#if c.seqmin?? && c.seqmin?number gt -1>
+MINVALUE ${c.seqmin} </#if><#if c.seqmax?? && c.seqmax?number gt -1>
+MAXVALUE ${c.seqmax} </#if><#if c.seqcache?? && c.seqcache?number gt -1>
+CACHE ${c.seqcache} </#if>
+<#if c.seqincrement?? || c.seqcycle!false || c.seqincrement?? || c.seqstart?? || c.seqmin?? || c.seqmax?? || c.seqcache?? >)</#if>
+</#if>
+<#if c.colconstype?? && c.colconstype == 'g' && c.genexpr?? && c.genexpr != '' > GENERATED ALWAYS AS (${c.genexpr}) STORED</#if>
+<#if c?counter lt columns?size>,
+</#if>
+</#if>
+</#list>
+</#if>
+<#if primary_key?size gt 0 ><#if columns?size gt 0 >,</#if>
+<@CONSTRAINTS.PRIMARY_KEY data=primary_key[0] /></#if><#if unique_constraint?size gt 0 ><#if columns?size gt 0 || primary_key?size gt 0 >,</#if>
+<@CONSTRAINTS.UNIQUE unique_data=unique_constraint /></#if><#if foreign_key?size gt 0 ><#if columns?size gt 0 || primary_key?size gt 0 || unique_constraint?size gt 0 >,</#if>
+<@CONSTRAINTS.FOREIGN_KEY foreign_key_data=foreign_key /></#if><#if check_constraint?size gt 0 ><#if columns?size gt 0 || primary_key?size gt 0 || unique_constraint?size gt 0 || foreign_key?size gt 0 >,</#if>
+<@CONSTRAINTS.CHECK check_data=check_constraint/></#if><#if exclude_constraint?size gt 0 ><#if columns?size gt 0 || primary_key?size gt 0 || unique_constraint?size gt 0 || foreign_key?size gt 0 || check_constraint?size gt 0 >,</#if>
+<@CONSTRAINTS.EXCLUDE exclude_data=exclude_constraint/></#if>
+<#if like_relation?? || coll_inherits?size gt 0 || columns?size gt 0 || primary_key?size gt 0 || unique_constraint?size gt 0 || foreign_key?size gt 0 || check_constraint?size gt 0 || exclude_constraint?size gt 0 >
+)</#if><#if relkind?? && relkind == 'p' > PARTITION BY ${ partition_scheme }</#if>
+<#if !(coll_inherits??) && !(spcname??) && !with_clause >;</#if>
+<#if coll_inherits?? && coll_inherits?size gt 0>
+INHERITS (<#list coll_inherits as val ><#if val?counter != 1 >, </#if>${val}</#list>)<#if !(spcname??) && !with_clause >;</#if>
+</#if>
+<#if with_clause >
+<#assign add_comma=false>
+WITH (
+<#if fillfactor?? ><#assign add_comma=true>
+FILLFACTOR = ${ fillfactor }</#if><#if parallel_workers?? >
+<#if add_comma >,
+</#if>
+parallel_workers = ${ parallel_workers }<#assign add_comma=true></#if><#if toast_tuple_target?? >
+<#if add_comma >,
+</#if>
+toast_tuple_target = ${ toast_tuple_target }<#assign add_comma=true></#if><#if autovacuum_enabled?? && (autovacuum_enabled == 't' || autovacuum_enabled == 'f') >
+<#if add_comma >,
+</#if>
+autovacuum_enabled = <#if autovacuum_enabled == 't' >TRUE<#else >FALSE</#if><#assign add_comma=true></#if><#if toast_autovacuum_enabled?? && (toast_autovacuum_enabled == 't' || toast_autovacuum_enabled == 'f') >
+<#if add_comma >,
+</#if>
+toast.autovacuum_enabled = <#if toast_autovacuum_enabled == 't' >TRUE<#else >FALSE</#if><#assign add_comma=true></#if><#if autovacuum_custom!false >
+<#list vacuum_table as opt ><#if opt.name?? && opt.value?? >
+<#if add_comma >,
+</#if>
+${opt.name} = ${opt.value}<#assign add_comma=true></#if>
+</#list></#if><#if toast_autovacuum!false >
+<#list vacuum_toast as opt ><#if opt.name?? && opt.value?? >
+<#if add_comma >,
+</#if>
+toast.${opt.name} = ${opt.value}<#assign add_comma=true></#if>
+</#list></#if>
+<#if spcname?? >)<#else>);</#if>
+</#if>
+<#if spcname?? >
+TABLESPACE ${spcname };
+</#if>
+<#if description?? >
+COMMENT ON TABLE ${schema}.${name}
+IS '${description}';
+</#if>
+<#if columns?? && columns?size gt 0 >
+<#list columns as c >
+<#if c.description?? >
+COMMENT ON COLUMN ${schema}.${name}.${c.name}
+IS '${c.description}';
+</#if>
+</#list>
+</#if>
+<@CONSTRAINTS.CONSTRAINT_COMMENTS schema=schema table=name data=primary_key/>
+<@CONSTRAINTS.CONSTRAINT_COMMENTS schema=schema table=name data=unique_constraint/>
+<@CONSTRAINTS.CONSTRAINT_COMMENTS schema=schema table=name data=foreign_key/>
+<@CONSTRAINTS.CONSTRAINT_COMMENTS schema=schema table=name data=check_constraint/>
+<@CONSTRAINTS.CONSTRAINT_COMMENTS schema=schema table=name data=exclude_constraint/>
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/12_plus/properties.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/12_plus/properties.ftl
new file mode 100644
index 00000000000..983cf168b65
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/12_plus/properties.ftl
@@ -0,0 +1,92 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
+(CASE WHEN length(spc.spcname::text) > 0 OR rel.relkind = 'p' THEN spc.spcname ELSE
+(SELECT sp.spcname FROM pg_catalog.pg_database dtb
+JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
+WHERE dtb.oid = ${ did?c }::oid)
+END) as spcname,
+(CASE rel.relreplident
+WHEN 'd' THEN 'default'
+WHEN 'n' THEN 'nothing'
+WHEN 'f' THEN 'full'
+WHEN 'i' THEN 'index'
+END) as replica_identity,
+(select nspname FROM pg_catalog.pg_namespace WHERE oid = ${scid?c}::oid ) as schema,
+pg_catalog.pg_get_userbyid(rel.relowner) AS relowner, rel.relkind,
+(CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
+rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
+EXISTS(select 1 FROM pg_catalog.pg_trigger
+JOIN pg_catalog.pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
+JOIN pg_catalog.pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
+WHERE tgrelid=rel.oid) AS isrepl,
+(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
+(SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
+pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(c.relname)
+ELSE pg_catalog.quote_ident(c.relname) END AS inherited_tables
+FROM pg_catalog.pg_inherits i
+JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
+JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
+WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
+(SELECT count(*)
+FROM pg_catalog.pg_inherits i
+JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
+JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
+WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
+(CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'toast_tuple_target=([0-9]*)') AS toast_tuple_target,
+(substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS autovacuum_enabled,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
+substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
+(substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS toast_autovacuum_enabled,
+substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
+substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
+substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
+substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
+substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
+substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
+substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
+substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
+substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
+rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reloftype,
+CASE WHEN typ.typname IS NOT NULL THEN (select pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace WHERE oid = ${scid?c}::oid )||'.'||pg_catalog.quote_ident(typ.typname) ELSE typ.typname END AS typname,
+typ.typrelid AS typoid, rel.relrowsecurity as rlspolicy, rel.relforcerowsecurity as forcerlspolicy,
+(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
+(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
+(CASE WHEN rel.oid <= ${ datlastsysoid?c}::oid THEN true ElSE false END) AS is_sys_table
+-- Added for partition table
+<#if tid?? >, (CASE WHEN rel.relkind = 'p' THEN pg_catalog.pg_get_partkeydef(${ tid?c }::oid) ELSE '' END) AS partition_scheme </#if>
+FROM pg_catalog.pg_class rel
+LEFT OUTER JOIN pg_catalog.pg_tablespace spc on spc.oid=rel.reltablespace
+LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
+LEFT OUTER JOIN pg_catalog.pg_class tst ON tst.oid = rel.reltoastrelid
+LEFT JOIN pg_catalog.pg_type typ ON rel.reloftype=typ.oid
+WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = ${ scid?c }::oid
+AND NOT rel.relispartition
+<#if tid?? > AND rel.oid = ${ tid?c }::oid </#if>
+ORDER BY rel.relname;
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/default/get_database_id.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/default/get_database_id.ftl
new file mode 100644
index 00000000000..e5d003cd00c
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/default/get_database_id.ftl
@@ -0,0 +1,18 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT oid AS did, datlastsysoid FROM pg_catalog.pg_database WHERE datname = '${databaseName}';
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/default/get_schema_id.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/default/get_schema_id.ftl
new file mode 100644
index 00000000000..bb9332b6847
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/default/get_schema_id.ftl
@@ -0,0 +1,18 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT oid AS scid FROM pg_catalog.pg_namespace WHERE nspname = '${schemaName}';
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/default/get_table_id.ftl b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/default/get_table_id.ftl
new file mode 100644
index 00000000000..7853ed437ca
--- /dev/null
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/resources/template/table/default/get_table_id.ftl
@@ -0,0 +1,18 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT tablename::REGCLASS::OID AS tid FROM pg_catalog.pg_tables WHERE schemaname = '${schemaName}' and tablename = '${tableName}';