1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 package org.apache.shardingsphere.infra.database.opengauss.metadata.data.loader;
19
20 import com.google.common.collect.LinkedHashMultimap;
21 import com.google.common.collect.Multimap;
22 import org.apache.shardingsphere.infra.database.core.metadata.data.loader.DialectMetaDataLoader;
23 import org.apache.shardingsphere.infra.database.core.metadata.data.loader.MetaDataLoaderMaterial;
24 import org.apache.shardingsphere.infra.database.core.metadata.data.loader.type.SchemaMetaDataLoader;
25 import org.apache.shardingsphere.infra.database.core.metadata.data.model.ColumnMetaData;
26 import org.apache.shardingsphere.infra.database.core.metadata.data.model.IndexMetaData;
27 import org.apache.shardingsphere.infra.database.core.metadata.data.model.SchemaMetaData;
28 import org.apache.shardingsphere.infra.database.core.metadata.data.model.TableMetaData;
29 import org.apache.shardingsphere.infra.database.core.metadata.database.datatype.DataTypeLoader;
30 import org.apache.shardingsphere.infra.database.core.type.DatabaseType;
31 import org.apache.shardingsphere.infra.spi.type.typed.TypedSPILoader;
32
33 import java.sql.Connection;
34 import java.sql.PreparedStatement;
35 import java.sql.ResultSet;
36 import java.sql.SQLException;
37 import java.util.Collection;
38 import java.util.Collections;
39 import java.util.HashSet;
40 import java.util.LinkedHashMap;
41 import java.util.LinkedList;
42 import java.util.Map;
43 import java.util.Optional;
44 import java.util.stream.Collectors;
45
46
47
48
49 public final class OpenGaussMetaDataLoader implements DialectMetaDataLoader {
50
51 private static final String BASIC_TABLE_META_DATA_SQL = "SELECT table_name, column_name, ordinal_position, data_type, udt_name, column_default, table_schema, is_nullable"
52 + " FROM information_schema.columns WHERE table_schema IN (%s)";
53
54 private static final String TABLE_META_DATA_SQL_WITHOUT_TABLES = BASIC_TABLE_META_DATA_SQL + " ORDER BY ordinal_position";
55
56 private static final String TABLE_META_DATA_SQL_WITH_TABLES = BASIC_TABLE_META_DATA_SQL + " AND table_name IN (%s) ORDER BY ordinal_position";
57
58 private static final String PRIMARY_KEY_META_DATA_SQL = "SELECT tc.table_name, kc.column_name, kc.table_schema FROM information_schema.table_constraints tc"
59 + " JOIN information_schema.key_column_usage kc ON kc.table_schema = tc.table_schema AND kc.table_name = tc.table_name AND kc.constraint_name = tc.constraint_name"
60 + " WHERE tc.constraint_type = 'PRIMARY KEY' AND kc.ordinal_position IS NOT NULL AND kc.table_schema IN (%s)";
61
62 private static final String BASIC_INDEX_META_DATA_SQL = "SELECT tablename, indexname, schemaname FROM pg_indexes WHERE schemaname IN (%s)";
63
64 private static final String ADVANCE_INDEX_META_DATA_SQL =
65 "SELECT idx.relname as index_name, insp.nspname as index_schema, tbl.relname as table_name, att.attname AS column_name, pgi.indisunique as is_unique"
66 + " FROM pg_index pgi JOIN pg_class idx ON idx.oid = pgi.indexrelid JOIN pg_namespace insp ON insp.oid = idx.relnamespace JOIN pg_class tbl ON tbl.oid = pgi.indrelid"
67 + " JOIN pg_namespace tnsp ON tnsp.oid = tbl.relnamespace JOIN pg_attribute att ON att.attrelid = tbl.oid AND att.attnum = ANY(pgi.indkey) WHERE tnsp.nspname IN (%s)";
68
69 @Override
70 public Collection<SchemaMetaData> load(final MetaDataLoaderMaterial material) throws SQLException {
71 try (Connection connection = material.getDataSource().getConnection()) {
72 Collection<String> schemaNames = SchemaMetaDataLoader.loadSchemaNames(connection, TypedSPILoader.getService(DatabaseType.class, "openGauss"));
73 Map<String, Multimap<String, IndexMetaData>> schemaIndexMetaDataMap = loadIndexMetaDataMap(connection, schemaNames);
74 Map<String, Multimap<String, ColumnMetaData>> schemaColumnMetaDataMap = loadColumnMetaDataMap(connection, material.getActualTableNames(), schemaNames);
75 Collection<SchemaMetaData> result = new LinkedList<>();
76 for (String each : schemaNames) {
77 Multimap<String, IndexMetaData> tableIndexMetaDataMap = schemaIndexMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
78 Multimap<String, ColumnMetaData> tableColumnMetaDataMap = schemaColumnMetaDataMap.getOrDefault(each, LinkedHashMultimap.create());
79 result.add(new SchemaMetaData(each, createTableMetaDataList(tableIndexMetaDataMap, tableColumnMetaDataMap)));
80 }
81 return result;
82 }
83 }
84
85 private Map<String, Multimap<String, IndexMetaData>> loadIndexMetaDataMap(final Connection connection, final Collection<String> schemaNames) throws SQLException {
86 Map<String, Multimap<String, IndexMetaData>> result = new LinkedHashMap<>();
87 try (PreparedStatement preparedStatement = connection.prepareStatement(getIndexMetaDataSQL(schemaNames)); ResultSet resultSet = preparedStatement.executeQuery()) {
88 while (resultSet.next()) {
89 String schemaName = resultSet.getString("schemaname");
90 String tableName = resultSet.getString("tablename");
91 String indexName = resultSet.getString("indexname");
92 Multimap<String, IndexMetaData> indexMetaDataMap = result.computeIfAbsent(schemaName, key -> LinkedHashMultimap.create());
93 indexMetaDataMap.put(tableName, new IndexMetaData(indexName));
94 }
95 }
96 try (PreparedStatement preparedStatement = connection.prepareStatement(getAdvanceIndexMetaDataSQL(schemaNames)); ResultSet resultSet = preparedStatement.executeQuery()) {
97 while (resultSet.next()) {
98 String schemaName = resultSet.getString("index_schema");
99 String tableName = resultSet.getString("table_name");
100 String columnName = resultSet.getString("column_name");
101 String indexName = resultSet.getString("index_name");
102 boolean isUnique = resultSet.getBoolean("is_unique");
103 Collection<IndexMetaData> indexMetaDatas = result.getOrDefault(schemaName, LinkedHashMultimap.create()).get(tableName);
104 if (indexMetaDatas.isEmpty()) {
105 continue;
106 }
107 Optional<IndexMetaData> indexMetaData = indexMetaDatas.stream().filter(each -> each.getName().equals(indexName)).findFirst();
108 if (indexMetaData.isPresent()) {
109 indexMetaData.get().setUnique(isUnique);
110 indexMetaData.get().getColumns().add(columnName);
111 }
112 }
113 }
114 return result;
115 }
116
117 private String getIndexMetaDataSQL(final Collection<String> schemaNames) {
118 return String.format(BASIC_INDEX_META_DATA_SQL, schemaNames.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
119 }
120
121 private String getAdvanceIndexMetaDataSQL(final Collection<String> schemaNames) {
122 return String.format(ADVANCE_INDEX_META_DATA_SQL, schemaNames.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
123 }
124
125 private Map<String, Multimap<String, ColumnMetaData>> loadColumnMetaDataMap(final Connection connection, final Collection<String> tables,
126 final Collection<String> schemaNames) throws SQLException {
127 Map<String, Multimap<String, ColumnMetaData>> result = new LinkedHashMap<>();
128 try (PreparedStatement preparedStatement = connection.prepareStatement(getColumnMetaDataSQL(schemaNames, tables)); ResultSet resultSet = preparedStatement.executeQuery()) {
129 Map<String, Integer> dataTypes = new DataTypeLoader().load(connection.getMetaData(), getType());
130 Collection<String> primaryKeys = loadPrimaryKeys(connection, schemaNames);
131 while (resultSet.next()) {
132 String tableName = resultSet.getString("table_name");
133 String schemaName = resultSet.getString("table_schema");
134 Multimap<String, ColumnMetaData> columnMetaDataMap = result.computeIfAbsent(schemaName, key -> LinkedHashMultimap.create());
135 columnMetaDataMap.put(tableName, loadColumnMetaData(dataTypes, primaryKeys, resultSet));
136 }
137 }
138 return result;
139 }
140
141 private String getColumnMetaDataSQL(final Collection<String> schemaNames, final Collection<String> tables) {
142 String schemaNameParam = schemaNames.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(","));
143 return tables.isEmpty() ? String.format(TABLE_META_DATA_SQL_WITHOUT_TABLES, schemaNameParam)
144 : String.format(TABLE_META_DATA_SQL_WITH_TABLES, schemaNameParam, tables.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
145 }
146
147 private Collection<String> loadPrimaryKeys(final Connection connection, final Collection<String> schemaNames) throws SQLException {
148 Collection<String> result = new HashSet<>();
149 try (PreparedStatement preparedStatement = connection.prepareStatement(getPrimaryKeyMetaDataSQL(schemaNames)); ResultSet resultSet = preparedStatement.executeQuery()) {
150 while (resultSet.next()) {
151 String schemaName = resultSet.getString("table_schema");
152 String tableName = resultSet.getString("table_name");
153 String columnName = resultSet.getString("column_name");
154 result.add(schemaName + "," + tableName + "," + columnName);
155 }
156 }
157 return result;
158 }
159
160 private String getPrimaryKeyMetaDataSQL(final Collection<String> schemaNames) {
161 return String.format(PRIMARY_KEY_META_DATA_SQL, schemaNames.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
162 }
163
164 private ColumnMetaData loadColumnMetaData(final Map<String, Integer> dataTypeMap, final Collection<String> primaryKeys, final ResultSet resultSet) throws SQLException {
165 String schemaName = resultSet.getString("table_schema");
166 String tableName = resultSet.getString("table_name");
167 String columnName = resultSet.getString("column_name");
168 String dataType = resultSet.getString("udt_name");
169 boolean isPrimaryKey = primaryKeys.contains(schemaName + "," + tableName + "," + columnName);
170 String columnDefault = resultSet.getString("column_default");
171 boolean generated = null != columnDefault && columnDefault.startsWith("nextval(");
172
173 boolean caseSensitive = true;
174 boolean isNullable = "YES".equals(resultSet.getString("is_nullable"));
175 return new ColumnMetaData(columnName, dataTypeMap.get(dataType), isPrimaryKey, generated, caseSensitive, true, false, isNullable);
176 }
177
178 private Collection<TableMetaData> createTableMetaDataList(final Multimap<String, IndexMetaData> tableIndexMetaDataMap, final Multimap<String, ColumnMetaData> tableColumnMetaDataMap) {
179 Collection<TableMetaData> result = new LinkedList<>();
180 for (String each : tableColumnMetaDataMap.keySet()) {
181 Collection<ColumnMetaData> columnMetaDataList = tableColumnMetaDataMap.get(each);
182 Collection<IndexMetaData> indexMetaDataList = tableIndexMetaDataMap.get(each);
183 result.add(new TableMetaData(each, columnMetaDataList, indexMetaDataList, Collections.emptyList()));
184 }
185 return result;
186 }
187
188 @Override
189 public String getDatabaseType() {
190 return "openGauss";
191 }
192 }