Repository: ignite Updated Branches: refs/heads/master 4b0998e5d -> 8de8fe50c
IGNITE-5414 Implemented usage of UNIQUE index for key fields generation, if PRIMARY KEY not found. Project: http://git-wip-us.apache.org/repos/asf/ignite/repo Commit: http://git-wip-us.apache.org/repos/asf/ignite/commit/8de8fe50 Tree: http://git-wip-us.apache.org/repos/asf/ignite/tree/8de8fe50 Diff: http://git-wip-us.apache.org/repos/asf/ignite/diff/8de8fe50 Branch: refs/heads/master Commit: 8de8fe50c663a0a20fc36b82ae6a621b2864d010 Parents: 4b0998e Author: Alexey Kuznetsov <[email protected]> Authored: Fri Jun 9 14:36:30 2017 +0700 Committer: Alexey Kuznetsov <[email protected]> Committed: Fri Jun 9 14:36:30 2017 +0700 ---------------------------------------------------------------------- .../visor/query/VisorQueryIndexField.java | 32 ++--- .../frontend/controllers/domains-controller.js | 46 ++++---- .../views/configuration/domains-import.tpl.pug | 4 +- .../apache/ignite/console/agent/AgentUtils.java | 24 ++-- .../ignite/console/agent/db/DbColumn.java | 17 ++- .../apache/ignite/console/agent/db/DbTable.java | 21 ++-- .../db/dialect/DatabaseMetadataDialect.java | 27 ++++- .../agent/db/dialect/JdbcMetadataDialect.java | 42 ++++++- .../agent/db/dialect/OracleMetadataDialect.java | 116 +++++++++++++++---- 9 files changed, 230 insertions(+), 99 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ignite/blob/8de8fe50/modules/core/src/main/java/org/apache/ignite/internal/visor/query/VisorQueryIndexField.java ---------------------------------------------------------------------- diff --git a/modules/core/src/main/java/org/apache/ignite/internal/visor/query/VisorQueryIndexField.java b/modules/core/src/main/java/org/apache/ignite/internal/visor/query/VisorQueryIndexField.java index fb32dd1..f1d120d 100644 --- a/modules/core/src/main/java/org/apache/ignite/internal/visor/query/VisorQueryIndexField.java +++ b/modules/core/src/main/java/org/apache/ignite/internal/visor/query/VisorQueryIndexField.java @@ -37,10 +37,10 @@ public class VisorQueryIndexField extends VisorDataTransferObject { private static final long serialVersionUID = 0L; /** Index field name. */ - private String fldName; + private String name; /** Index field sort order. */ - private boolean fldSort; + private boolean sort; /** * Create data transfer object for given cache type metadata. @@ -50,14 +50,14 @@ public class VisorQueryIndexField extends VisorDataTransferObject { } /** - * Create data transfer object for given cache type metadata. + * Create data transfer object for index field. * - * @param fldName Index field name. - * @param fldSort Index field sort order. + * @param name Index field name. + * @param sort Index field sort order. */ - public VisorQueryIndexField(String fldName, boolean fldSort) { - this.fldName = fldName; - this.fldSort = fldSort; + public VisorQueryIndexField(String name, boolean sort) { + this.name = name; + this.sort = sort; } /** @@ -76,27 +76,27 @@ public class VisorQueryIndexField extends VisorDataTransferObject { /** * @return Index field name. */ - public String getFieldName() { - return fldName; + public String getName() { + return name; } /** * @return Index field sort order. */ - public boolean isFieldSort() { - return fldSort; + public boolean getSortOrder() { + return sort; } /** {@inheritDoc} */ @Override protected void writeExternalData(ObjectOutput out) throws IOException { - U.writeString(out, fldName); - out.writeBoolean(fldSort); + U.writeString(out, name); + out.writeBoolean(sort); } /** {@inheritDoc} */ @Override protected void readExternalData(byte protoVer, ObjectInput in) throws IOException, ClassNotFoundException { - fldName = U.readString(in); - fldSort = in.readBoolean(); + name = U.readString(in); + sort = in.readBoolean(); } /** {@inheritDoc} */ http://git-wip-us.apache.org/repos/asf/ignite/blob/8de8fe50/modules/web-console/frontend/controllers/domains-controller.js ---------------------------------------------------------------------- diff --git a/modules/web-console/frontend/controllers/domains-controller.js b/modules/web-console/frontend/controllers/domains-controller.js index 992c829..b8fae5d 100644 --- a/modules/web-console/frontend/controllers/domains-controller.js +++ b/modules/web-console/frontend/controllers/domains-controller.js @@ -697,16 +697,14 @@ export default ['$rootScope', '$scope', '$http', '$state', '$filter', '$timeout' _fillCommonCachesOrTemplates($scope.importCommon)($scope.importCommon.action); - _.forEach(tables, function(tbl, idx) { + _.forEach(tables, (tbl, idx) => { tbl.id = idx; tbl.action = IMPORT_DM_NEW_CACHE; - tbl.generatedCacheName = toJavaClassName(tbl.tbl) + 'Cache'; + tbl.generatedCacheName = toJavaClassName(tbl.table) + 'Cache'; tbl.cacheOrTemplate = DFLT_PARTITIONED_CACHE.value; - tbl.label = tbl.schema + '.' + tbl.tbl; + tbl.label = tbl.schema + '.' + tbl.table; tbl.edit = false; - tbl.use = LegacyUtils.isDefined(_.find(tbl.cols, function(col) { - return col.key; - })); + tbl.use = LegacyUtils.isDefined(_.find(tbl.columns, (col) => col.key)); }); $scope.importDomain.action = 'tables'; @@ -718,7 +716,7 @@ export default ['$rootScope', '$scope', '$http', '$state', '$filter', '$timeout' } $scope.applyDefaults = function() { - _.forEach($scope.importDomain.displayedTables, function(table) { + _.forEach($scope.importDomain.displayedTables, (table) => { table.edit = false; table.action = $scope.importCommon.action; table.cacheOrTemplate = $scope.importCommon.cacheOrTemplate; @@ -770,9 +768,7 @@ export default ['$rootScope', '$scope', '$http', '$state', '$filter', '$timeout' let lastItem; const newItems = []; - _.forEach(_mapCaches(data.generatedCaches), function(cache) { - $scope.caches.push(cache); - }); + _.forEach(_mapCaches(data.generatedCaches), (cache) => $scope.caches.push(cache)); _.forEach(data.savedDomains, function(savedItem) { const idx = _.findIndex($scope.domains, function(domain) { @@ -847,11 +843,11 @@ export default ['$rootScope', '$scope', '$http', '$state', '$filter', '$timeout' const valFields = []; const aliases = []; - const tableName = table.tbl; + const tableName = table.table; let typeName = toJavaClassName(tableName); if (_.find($scope.importDomain.tables, - (tbl, ix) => tbl.use && ix !== curIx && tableName === tbl.tbl)) { + (tbl, ix) => tbl.use && ix !== curIx && tableName === tbl.table)) { typeName = typeName + '_' + toJavaClassName(table.schema); containDup = true; @@ -869,7 +865,7 @@ export default ['$rootScope', '$scope', '$http', '$state', '$filter', '$timeout' let _containKey = false; - _.forEach(table.cols, function(col) { + _.forEach(table.columns, function(col) { const fld = dbField(col.name, SqlTypes.findJdbcType(col.type), col.nullable, col.unsigned); qryFields.push({name: fld.javaFieldName, className: fld.javaType}); @@ -892,18 +888,17 @@ export default ['$rootScope', '$scope', '$http', '$state', '$filter', '$timeout' }); containKey &= _containKey; - - if (table.idxs) { - _.forEach(table.idxs, function(idx) { - const fields = Object.keys(idx.fields); + if (table.indexes) { + _.forEach(table.indexes, (idx) => { + const idxFields = _.map(idx.fields, (idxFld) => ({ + name: toJavaFieldName(idxFld.name), + direction: idxFld.sortOrder + })); indexes.push({ - name: idx.name, indexType: 'SORTED', fields: _.map(fields, function(fieldName) { - return { - name: toJavaFieldName(fieldName), - direction: idx.fields[fieldName] - }; - }) + name: idx.name, + indexType: 'SORTED', + fields: idxFields }); }); } @@ -946,10 +941,11 @@ export default ['$rootScope', '$scope', '$http', '$state', '$filter', '$timeout' newDomain.keyType = keyField.javaType; - // Exclude key column from query fields and indexes. + // Exclude key column from query fields. newDomain.fields = _.filter(newDomain.fields, (field) => field.name !== keyField.javaFieldName); - _.forEach(newDomain.indexes, function(index) { + // Exclude key column from indexes. + _.forEach(newDomain.indexes, (index) => { index.fields = _.filter(index.fields, (field) => field.name !== keyField.javaFieldName); }); http://git-wip-us.apache.org/repos/asf/ignite/blob/8de8fe50/modules/web-console/frontend/views/configuration/domains-import.tpl.pug ---------------------------------------------------------------------- diff --git a/modules/web-console/frontend/views/configuration/domains-import.tpl.pug b/modules/web-console/frontend/views/configuration/domains-import.tpl.pug index 20a7b85..29bbf4a 100644 --- a/modules/web-console/frontend/views/configuration/domains-import.tpl.pug +++ b/modules/web-console/frontend/views/configuration/domains-import.tpl.pug @@ -74,7 +74,7 @@ mixin td-ellipses-lbl(w, lbl) .settings-row.settings-row_small-label +text('JDBC URL:', 'selectedPreset.jdbcUrl', '"jdbcUrl"', true, 'JDBC URL', 'JDBC URL for connecting to database<br>Refer to your database documentation for details') .settings-row.settings-row_small-label - +text('User:', 'selectedPreset.user', '"jdbdUser"', false, '', 'User name for connecting to database') + +text('User:', 'selectedPreset.user', '"jdbcUser"', false, '', 'User name for connecting to database') .settings-row.settings-row_small-label +password('Password:', 'selectedPreset.password', '"jdbcPassword"', false, '', 'Password for connecting to database<br>Note, password would not be saved in preferences for security reasons')(ignite-on-enter='importDomainNext()') .settings-row @@ -132,7 +132,7 @@ mixin td-ellipses-lbl(w, lbl) td(width='30px' style='min-width: 30px; max-width: 30px') input(type='checkbox' ng-model='table.use' ng-change='selectTable()') +td-ellipses-lbl('130px', '{{table.schema}}') - +td-ellipses-lbl('160px', '{{table.tbl}}') + +td-ellipses-lbl('160px', '{{table.table}}') td(colspan='2' width='288px' style='min-width: 160px; max-width: 160px') div.td-ellipsis a(ng-if='!table.edit' ng-click='startEditDbTableCache(table)') {{tableActionView(table)}} http://git-wip-us.apache.org/repos/asf/ignite/blob/8de8fe50/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/AgentUtils.java ---------------------------------------------------------------------- diff --git a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/AgentUtils.java b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/AgentUtils.java index 1999afc..797951c 100644 --- a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/AgentUtils.java +++ b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/AgentUtils.java @@ -17,8 +17,6 @@ package org.apache.ignite.console.agent; -import com.fasterxml.jackson.annotation.JsonAutoDetect; -import com.fasterxml.jackson.annotation.PropertyAccessor; import com.fasterxml.jackson.databind.ObjectMapper; import com.fasterxml.jackson.datatype.jsonorg.JsonOrgModule; import io.socket.client.Ack; @@ -39,7 +37,12 @@ public class AgentUtils { private static final Logger log = Logger.getLogger(AgentUtils.class.getName()); /** JSON object mapper. */ - private static final ObjectMapper mapper = new ObjectMapper(); + private static final ObjectMapper MAPPER = new ObjectMapper(); + + static { + // Register special module with basic serializers. + MAPPER.registerModule(new JsonOrgModule()); + } /** */ private static final Ack NOOP_CB = new Ack() { @@ -51,15 +54,6 @@ public class AgentUtils { } }; - static { - JsonOrgModule module = new JsonOrgModule(); - - mapper.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.NONE); - mapper.setVisibility(PropertyAccessor.FIELD, JsonAutoDetect.Visibility.ANY); - - mapper.registerModule(module); - } - /** * Default constructor. */ @@ -172,9 +166,9 @@ public class AgentUtils { */ public static Object toJSON(Object obj) { if (obj instanceof Iterable) - return mapper.convertValue(obj, JSONArray.class); + return MAPPER.convertValue(obj, JSONArray.class); - return mapper.convertValue(obj, JSONObject.class); + return MAPPER.convertValue(obj, JSONObject.class); } /** @@ -186,6 +180,6 @@ public class AgentUtils { * @throws IllegalArgumentException If conversion fails due to incompatible type. */ public static <T> T fromJSON(Object obj, Class<T> toValType) throws IllegalArgumentException { - return mapper.convertValue(obj, toValType); + return MAPPER.convertValue(obj, toValType); } } http://git-wip-us.apache.org/repos/asf/ignite/blob/8de8fe50/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/DbColumn.java ---------------------------------------------------------------------- diff --git a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/DbColumn.java b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/DbColumn.java index 723ee25..61c15ba 100644 --- a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/DbColumn.java +++ b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/DbColumn.java @@ -17,6 +17,8 @@ package org.apache.ignite.console.agent.db; +import org.apache.ignite.internal.util.typedef.internal.S; + /** * Database table column. */ @@ -54,35 +56,40 @@ public class DbColumn { /** * @return Column name. */ - public String name() { + public String getName() { return name; } /** * @return Column JDBC type. */ - public int type() { + public int getType() { return type; } /** * @return {@code true} if this column belongs to primary key. */ - public boolean key() { + public boolean isKey() { return key; } /** * @return {@code true} if {@code NULL } allowed for column in database. */ - public boolean nullable() { + public boolean isNullable() { return nullable; } /** * @return {@code true} if column is unsigned. */ - public boolean unsigned() { + public boolean isUnsigned() { return unsigned; } + + /** {@inheritDoc} */ + @Override public String toString() { + return S.toString(DbColumn.class, this); + } } http://git-wip-us.apache.org/repos/asf/ignite/blob/8de8fe50/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/DbTable.java ---------------------------------------------------------------------- diff --git a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/DbTable.java b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/DbTable.java index b74cfe5..653bb07 100644 --- a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/DbTable.java +++ b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/DbTable.java @@ -17,9 +17,9 @@ package org.apache.ignite.console.agent.db; -import org.apache.ignite.cache.QueryIndex; - import java.util.Collection; +import org.apache.ignite.internal.util.typedef.internal.S; +import org.apache.ignite.internal.visor.query.VisorQueryIndex; /** * Database table. @@ -35,7 +35,7 @@ public class DbTable { private final Collection<DbColumn> cols; /** Indexes. */ - private final Collection<QueryIndex> idxs; + private final Collection<VisorQueryIndex> idxs; /** * Default columns. @@ -45,7 +45,7 @@ public class DbTable { * @param cols Columns. * @param idxs Indexes; */ - public DbTable(String schema, String tbl, Collection<DbColumn> cols, Collection<QueryIndex> idxs) { + public DbTable(String schema, String tbl, Collection<DbColumn> cols, Collection<VisorQueryIndex> idxs) { this.schema = schema; this.tbl = tbl; this.cols = cols; @@ -55,28 +55,33 @@ public class DbTable { /** * @return Schema name. */ - public String schema() { + public String getSchema() { return schema; } /** * @return Table name. */ - public String table() { + public String getTable() { return tbl; } /** * @return Columns. */ - public Collection<DbColumn> columns() { + public Collection<DbColumn> getColumns() { return cols; } /** * @return Indexes. */ - public Collection<QueryIndex> indexes() { + public Collection<VisorQueryIndex> getIndexes() { return idxs; } + + /** {@inheritDoc} */ + @Override public String toString() { + return S.toString(DbTable.class, this); + } } http://git-wip-us.apache.org/repos/asf/ignite/blob/8de8fe50/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/DatabaseMetadataDialect.java ---------------------------------------------------------------------- diff --git a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/DatabaseMetadataDialect.java b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/DatabaseMetadataDialect.java index 674bda5..e049d74 100644 --- a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/DatabaseMetadataDialect.java +++ b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/DatabaseMetadataDialect.java @@ -20,16 +20,19 @@ package org.apache.ignite.console.agent.db.dialect; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.SQLException; +import java.util.ArrayList; import java.util.Collection; import java.util.Collections; import java.util.LinkedHashMap; import java.util.List; +import java.util.Map; import java.util.Set; import org.apache.ignite.cache.QueryIndex; import org.apache.ignite.cache.QueryIndexType; import org.apache.ignite.console.agent.db.DbColumn; import org.apache.ignite.console.agent.db.DbTable; +import org.apache.ignite.internal.visor.query.VisorQueryIndex; /** * Base class for database metadata dialect. @@ -81,7 +84,12 @@ public abstract class DatabaseMetadataDialect { * @return New {@code DbTable} instance. */ protected DbTable table(String schema, String tbl, Collection<DbColumn> cols, Collection<QueryIndex>idxs) { - return new DbTable(schema, tbl, cols, idxs); + Collection<VisorQueryIndex> res = new ArrayList<>(idxs.size()); + + for (QueryIndex idx : idxs) + res.add(new VisorQueryIndex(idx)); + + return new DbTable(schema, tbl, cols, res); } /** @@ -99,4 +107,21 @@ public abstract class DatabaseMetadataDialect { return idx; } + + /** + * Select first shortest index. + * + * @param uniqueIdxs Unique indexes with columns. + * @return Unique index that could be used instead of primary key. + */ + protected Map.Entry<String, Set<String>> uniqueIndexAsPk(Map<String, Set<String>> uniqueIdxs) { + Map.Entry<String, Set<String>> uniqueIdxAsPk = null; + + for (Map.Entry<String, Set<String>> uniqueIdx : uniqueIdxs.entrySet()) { + if (uniqueIdxAsPk == null || uniqueIdxAsPk.getValue().size() > uniqueIdx.getValue().size()) + uniqueIdxAsPk = uniqueIdx; + } + + return uniqueIdxAsPk; + } } http://git-wip-us.apache.org/repos/asf/ignite/blob/8de8fe50/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/JdbcMetadataDialect.java ---------------------------------------------------------------------- diff --git a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/JdbcMetadataDialect.java b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/JdbcMetadataDialect.java index dcfd9c0..031e3a7 100644 --- a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/JdbcMetadataDialect.java +++ b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/JdbcMetadataDialect.java @@ -23,8 +23,8 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; -import java.util.HashSet; import java.util.LinkedHashMap; +import java.util.LinkedHashSet; import java.util.List; import java.util.Map; import java.util.Set; @@ -140,13 +140,45 @@ public class JdbcMetadataDialect extends DatabaseMetadataDialect { if (sys.contains(schema)) continue; - Collection<String> pkCols = new HashSet<>(); + Collection<String> pkCols = new LinkedHashSet<>(); try (ResultSet pkRs = dbMeta.getPrimaryKeys(tblCatalog, tblSchema, tblName)) { while (pkRs.next()) pkCols.add(pkRs.getString(PK_COL_NAME_IDX)); } + Map.Entry<String, Set<String>> uniqueIdxAsPk = null; + + // If PK not found, trying to use first UNIQUE index as key. + if (pkCols.isEmpty()) { + Map<String, Set<String>> uniqueIdxs = new LinkedHashMap<>(); + + try (ResultSet idxRs = dbMeta.getIndexInfo(tblCatalog, tblSchema, tblName, true, true)) { + while (idxRs.next()) { + String idxName = idxRs.getString(IDX_NAME_IDX); + String colName = idxRs.getString(IDX_COL_NAME_IDX); + + if (idxName == null || colName == null) + continue; + + Set<String> idxCols = uniqueIdxs.get(idxName); + + if (idxCols == null) { + idxCols = new LinkedHashSet<>(); + + uniqueIdxs.put(idxName, idxCols); + } + + idxCols.add(colName); + } + } + + uniqueIdxAsPk = uniqueIndexAsPk(uniqueIdxs); + + if (uniqueIdxAsPk != null) + pkCols.addAll(uniqueIdxAsPk.getValue()); + } + Collection<DbColumn> cols = new ArrayList<>(); try (ResultSet colsRs = dbMeta.getColumns(tblCatalog, tblSchema, tblName, null)) { @@ -162,15 +194,17 @@ public class JdbcMetadataDialect extends DatabaseMetadataDialect { } } + String uniqueIdxAsPkName = uniqueIdxAsPk != null ? uniqueIdxAsPk.getKey() : null; + Map<String, QueryIndex> idxs = new LinkedHashMap<>(); try (ResultSet idxRs = dbMeta.getIndexInfo(tblCatalog, tblSchema, tblName, false, true)) { while (idxRs.next()) { String idxName = idxRs.getString(IDX_NAME_IDX); - String colName = idxRs.getString(IDX_COL_NAME_IDX); - if (idxName == null || colName == null) + // Skip {@code null} names and unique index used as PK. + if (idxName == null || colName == null || idxName.equals(uniqueIdxAsPkName)) continue; QueryIndex idx = idxs.get(idxName); http://git-wip-us.apache.org/repos/asf/ignite/blob/8de8fe50/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/OracleMetadataDialect.java ---------------------------------------------------------------------- diff --git a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/OracleMetadataDialect.java b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/OracleMetadataDialect.java index 1722948..6d12c81 100644 --- a/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/OracleMetadataDialect.java +++ b/modules/web-console/web-agent/src/main/java/org/apache/ignite/console/agent/db/dialect/OracleMetadataDialect.java @@ -28,6 +28,7 @@ import java.util.Collection; import java.util.Collections; import java.util.HashSet; import java.util.LinkedHashMap; +import java.util.LinkedHashSet; import java.util.List; import java.util.Map; import java.util.Set; @@ -61,21 +62,36 @@ import static java.sql.Types.VARCHAR; public class OracleMetadataDialect extends DatabaseMetadataDialect { /** SQL to get columns metadata. */ private static final String SQL_COLUMNS = "SELECT a.owner, a.table_name, a.column_name, a.nullable," + - " a.data_type, a.data_precision, a.data_scale " + - "FROM all_tab_columns a %s " + + " a.data_type, a.data_precision, a.data_scale" + + " FROM all_tab_columns a %s" + " %s " + " ORDER BY a.owner, a.table_name, a.column_id"; /** SQL to get list of PRIMARY KEYS columns. */ private static final String SQL_PRIMARY_KEYS = "SELECT b.column_name" + " FROM all_constraints a" + - " INNER JOIN all_cons_columns b ON a.owner = b.owner AND a.constraint_name = b.constraint_name" + + " INNER JOIN all_cons_columns b" + + " ON a.owner = b.owner" + + " AND a.constraint_name = b.constraint_name" + " WHERE a.owner = ? and a.table_name = ? AND a.constraint_type = 'P'"; + /** SQL to get list of UNIQUE INDEX columns. */ + private static final String SQL_UNIQUE_INDEXES_KEYS = "SELECT a.index_name, b.column_name" + + " FROM all_indexes a" + + " INNER JOIN all_ind_columns b" + + " ON a.index_name = b.index_name" + + " AND a.table_owner = b.table_owner" + + " AND a.table_name = b.table_name" + + " AND a.owner = b.index_owner" + + " WHERE a.owner = ? AND a.table_name = ? AND a.uniqueness = 'UNIQUE'" + + " ORDER BY b.column_position"; + /** SQL to get indexes metadata. */ private static final String SQL_INDEXES = "SELECT i.index_name, u.column_expression, i.column_name, i.descend" + " FROM all_ind_columns i" + - " LEFT JOIN user_ind_expressions u on u.index_name = i.index_name and i.table_name = u.table_name" + + " LEFT JOIN user_ind_expressions u" + + " ON u.index_name = i.index_name" + + " AND i.table_name = u.table_name" + " WHERE i.index_owner = ? and i.table_name = ?" + " ORDER BY i.index_name, i.column_position"; @@ -100,6 +116,12 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect { /** Numeric scale index. */ private static final int DATA_SCALE_IDX = 7; + /** Unique index name index. */ + private static final int UNQ_IDX_NAME_IDX = 1; + + /** Unique index column name index. */ + private static final int UNQ_IDX_COL_NAME_IDX = 2; + /** Index name index. */ private static final int IDX_NAME_IDX = 1; @@ -114,9 +136,9 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect { /** {@inheritDoc} */ @Override public Set<String> systemSchemas() { - return new HashSet<>(Arrays.asList("ANONYMOUS", "CTXSYS", "DBSNMP", "EXFSYS", "LBACSYS", "MDSYS", "MGMT_VIEW", - "OLAPSYS", "OWBSYS", "ORDPLUGINS", "ORDSYS", "OUTLN", "SI_INFORMTN_SCHEMA", "SYS", "SYSMAN", "SYSTEM", - "TSMSYS", "WK_TEST", "WKSYS", "WKPROXY", "WMSYS", "XDB", + return new HashSet<>(Arrays.asList("ANONYMOUS", "APPQOSSYS", "CTXSYS", "DBSNMP", "EXFSYS", "LBACSYS", "MDSYS", + "MGMT_VIEW", "OLAPSYS", "OWBSYS", "ORDPLUGINS", "ORDSYS", "OUTLN", "SI_INFORMTN_SCHEMA", "SYS", "SYSMAN", + "SYSTEM", "TSMSYS", "WK_TEST", "WKSYS", "WKPROXY", "WMSYS", "XDB", "APEX_040000", "APEX_PUBLIC_USER", "DIP", "FLOWS_30000", "FLOWS_FILES", "MDDATA", "ORACLE_OCM", "SPATIAL_CSW_ADMIN_USR", "SPATIAL_WFS_ADMIN_USR", "XS$NULL", @@ -235,11 +257,11 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect { * @throws SQLException If failed to retrieve primary key columns. */ private Set<String> primaryKeys(PreparedStatement stmt, String owner, String tbl) throws SQLException { - Set<String> pkCols = new HashSet<>(); - stmt.setString(1, owner); stmt.setString(2, tbl); + Set<String> pkCols = new LinkedHashSet<>(); + try (ResultSet pkRs = stmt.executeQuery()) { while(pkRs.next()) pkCols.add(pkRs.getString(1)); @@ -249,24 +271,64 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect { } /** + * Retrieve unique indexes with columns. + * + * @param stmt Prepared SQL statement to execute. + * @param owner DB owner. + * @param tbl Table name. + * @return Unique indexes. + * @throws SQLException If failed to retrieve unique indexes columns. + */ + private Map<String, Set<String>> uniqueIndexes(PreparedStatement stmt, String owner, String tbl) throws SQLException { + stmt.setString(1, owner); + stmt.setString(2, tbl); + + Map<String, Set<String>> uniqueIdxs = new LinkedHashMap<>(); + + try (ResultSet idxsRs = stmt.executeQuery()) { + while (idxsRs.next()) { + String idxName = idxsRs.getString(UNQ_IDX_NAME_IDX); + String colName = idxsRs.getString(UNQ_IDX_COL_NAME_IDX); + + Set<String> idxCols = uniqueIdxs.get(idxName); + + if (idxCols == null) { + idxCols = new LinkedHashSet<>(); + + uniqueIdxs.put(idxName, idxCols); + } + + idxCols.add(colName); + } + } + + return uniqueIdxs; + } + + /** * Retrieve index columns. * * @param stmt Prepared SQL statement to execute. * @param owner DB owner. * @param tbl Table name. + * @param uniqueIdxAsPk Optional unique index that used as PK. * @return Indexes. * @throws SQLException If failed to retrieve indexes columns. */ - private Collection<QueryIndex> indexes(PreparedStatement stmt, String owner, String tbl) throws SQLException { - Map<String, QueryIndex> idxs = new LinkedHashMap<>(); - + private Collection<QueryIndex> indexes(PreparedStatement stmt, String owner, String tbl, String uniqueIdxAsPk) throws SQLException { stmt.setString(1, owner); stmt.setString(2, tbl); + Map<String, QueryIndex> idxs = new LinkedHashMap<>(); + try (ResultSet idxsRs = stmt.executeQuery()) { while (idxsRs.next()) { String idxName = idxsRs.getString(IDX_NAME_IDX); + // Skip unique index used as PK. + if (idxName.equals(uniqueIdxAsPk)) + continue; + QueryIndex idx = idxs.get(idxName); if (idx == null) { @@ -287,12 +349,9 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect { } /** {@inheritDoc} */ - @Override public Collection<DbTable> tables(Connection conn, List<String> schemas, boolean tblsOnly) - throws SQLException { - Collection<DbTable> tbls = new ArrayList<>(); - + @Override public Collection<DbTable> tables(Connection conn, List<String> schemas, boolean tblsOnly) throws SQLException { PreparedStatement pkStmt = conn.prepareStatement(SQL_PRIMARY_KEYS); - + PreparedStatement uniqueIdxsStmt = conn.prepareStatement(SQL_UNIQUE_INDEXES_KEYS); PreparedStatement idxStmt = conn.prepareStatement(SQL_INDEXES); if (schemas.isEmpty()) @@ -300,16 +359,13 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect { Set<String> sysSchemas = systemSchemas(); + Collection<DbTable> tbls = new ArrayList<>(); + try (Statement colsStmt = conn.createStatement()) { for (String schema: schemas) { if (systemSchemas().contains(schema) || (schema != null && schema.startsWith("FLOWS_"))) continue; - Collection<DbColumn> cols = new ArrayList<>(); - - Set<String> pkCols = Collections.emptySet(); - Collection<QueryIndex> idxs = Collections.emptyList(); - String sql = String.format(SQL_COLUMNS, tblsOnly ? "INNER JOIN all_tables b on a.table_name = b.table_name and a.owner = b.owner" : "", schema != null ? String.format(" WHERE a.owner = '%s' ", schema) : ""); @@ -320,6 +376,10 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect { boolean first = true; + Set<String> pkCols = Collections.emptySet(); + Collection<DbColumn> cols = new ArrayList<>(); + Collection<QueryIndex> idxs = Collections.emptyList(); + while (colsRs.next()) { String owner = colsRs.getString(OWNER_IDX); String tbl = colsRs.getString(TBL_NAME_IDX); @@ -339,7 +399,17 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect { prevTbl = tbl; cols = new ArrayList<>(); pkCols = primaryKeys(pkStmt, owner, tbl); - idxs = indexes(idxStmt, owner, tbl); + + Map.Entry<String, Set<String>> uniqueIdxAsPk = null; + + if (pkCols.isEmpty()) { + uniqueIdxAsPk = uniqueIndexAsPk(uniqueIndexes(uniqueIdxsStmt, owner, tbl)); + + if (uniqueIdxAsPk != null) + pkCols.addAll(uniqueIdxAsPk.getValue()); + } + + idxs = indexes(idxStmt, owner, tbl, uniqueIdxAsPk != null ? uniqueIdxAsPk.getKey() : null); } String colName = colsRs.getString(COL_NAME_IDX);
