ignite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From akuznet...@apache.org
Subject ignite git commit: IGNITE-5414 Implemented usage of UNIQUE index if PRIMARY KEY not found.
Date Tue, 06 Jun 2017 11:23:49 GMT
Repository: ignite
Updated Branches:
  refs/heads/ignite-5414 [created] 9d285ad36


IGNITE-5414 Implemented usage of UNIQUE index 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/9d285ad3
Tree: http://git-wip-us.apache.org/repos/asf/ignite/tree/9d285ad3
Diff: http://git-wip-us.apache.org/repos/asf/ignite/diff/9d285ad3

Branch: refs/heads/ignite-5414
Commit: 9d285ad369236ee523c87402292175e7d30d7be8
Parents: 986c07a
Author: Alexey Kuznetsov <akuznetsov@apache.org>
Authored: Tue Jun 6 18:23:32 2017 +0700
Committer: Alexey Kuznetsov <akuznetsov@apache.org>
Committed: Tue Jun 6 18:23:32 2017 +0700

----------------------------------------------------------------------
 .../db/dialect/DatabaseMetadataDialect.java     | 18 ++++
 .../agent/db/dialect/JdbcMetadataDialect.java   | 42 ++++++++-
 .../agent/db/dialect/OracleMetadataDialect.java | 95 +++++++++++++++++---
 3 files changed, 139 insertions(+), 16 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/ignite/blob/9d285ad3/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..6ff2eca 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
@@ -24,6 +24,7 @@ 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;
@@ -99,4 +100,21 @@ public abstract class DatabaseMetadataDialect {
 
         return idx;
     }
+
+    /**
+     * Select firts 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/9d285ad3/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/9d285ad3/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..a9a7fd8 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,7 +257,7 @@ 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<>();
+        Set<String> pkCols = new LinkedHashSet<>();
 
         stmt.setString(1, owner);
         stmt.setString(2, tbl);
@@ -249,15 +271,51 @@ 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 {
+        Map<String, Set<String>> uniqueIdxs = new LinkedHashMap<>();
+
+        stmt.setString(1, owner);
+        stmt.setString(2, tbl);
+
+        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 {
+    private Collection<QueryIndex> indexes(PreparedStatement stmt, String owner, String
tbl, String uniqueIdxAsPk) throws SQLException {
         Map<String, QueryIndex> idxs = new LinkedHashMap<>();
 
         stmt.setString(1, owner);
@@ -267,6 +325,10 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect {
             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) {
@@ -292,7 +354,7 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect {
         Collection<DbTable> tbls = new ArrayList<>();
 
         PreparedStatement pkStmt = conn.prepareStatement(SQL_PRIMARY_KEYS);
-
+        PreparedStatement uniqueIdxsStmt = conn.prepareStatement(SQL_UNIQUE_INDEXES_KEYS);
         PreparedStatement idxStmt = conn.prepareStatement(SQL_INDEXES);
 
         if (schemas.isEmpty())
@@ -306,7 +368,6 @@ public class OracleMetadataDialect extends DatabaseMetadataDialect {
                     continue;
 
                 Collection<DbColumn> cols = new ArrayList<>();
-
                 Set<String> pkCols = Collections.emptySet();
                 Collection<QueryIndex> idxs = Collections.emptyList();
 
@@ -339,7 +400,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);


Mime
View raw message