DBSourceManager supports function based indexes
authorAugusto Mauch <augusto.mauch@openbravo.com>
Wed, 01 Jul 2015 16:21:28 +0200
changeset 612 2bf9eb701bee
parent 602 d5d21a543e48
child 613 f0f9e8b9ba6b
DBSourceManager supports function based indexes

This changes adds support for function based indexes. Some limitations apply:
- The functions used should accept only one input parameter (i.e. SUBSTRING is not supported, as it needs two)
- The use of user defined functions is not supported. Functions used in indexes must be deterministic, and as of this moment DBSourcemanger does not support this.
- Only one function can be used per index

The name function used in the index will be stored in the functionName attribute of the index-column:

<index name="C_BPARTNER_UPPER_NAME" unique="false">
<index-column name="NAME" functionName="UPPER"/>
</index>

The SQL command built to create the query will wrap the function name around the index column name, like this:

CREATE INDEX C_BPARTNER_UPPER_NAME ON C_BPARTNER (UPPER(NAME))

The OracleModelLoader and PostgreSQLModelLoader classes have been updated to retrieve the information needed for creating function based indexes in Oracle and PostgreSQL respectively. The SqlBuilder class has been updated to take into account the function applied to index columns when creating the SQL commands. The IndexColumn class and the mapping.xml file have been updated to take into account the index columns can have a new properly named functionName.
src/mapping.xml
src/org/apache/ddlutils/model/IndexColumn.java
src/org/apache/ddlutils/platform/SqlBuilder.java
src/org/apache/ddlutils/platform/oracle/OracleModelLoader.java
src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
--- a/src/mapping.xml	Wed May 27 14:58:33 2015 +0200
+++ b/src/mapping.xml	Wed Jul 01 16:21:28 2015 +0200
@@ -103,6 +103,7 @@
     <element name="index-column">
       <attribute name="name" property="name"/>
       <attribute name="size" property="size"/>
+      <attribute name="functionName" property="functionName"/>
     </element>
   </class>
 
--- a/src/org/apache/ddlutils/model/IndexColumn.java	Wed May 27 14:58:33 2015 +0200
+++ b/src/org/apache/ddlutils/model/IndexColumn.java	Wed Jul 01 16:21:28 2015 +0200
@@ -41,6 +41,8 @@
   protected String _name;
   /** The size of the column in the index. */
   protected String _size;
+  /** The name of the function that is applied to this column. */
+  protected String _functionName;
 
   // TODO: It might be useful if the referenced column is directly acessible
   // here ?
@@ -151,6 +153,25 @@
   }
 
   /**
+   * Gets the name of the function that applies to this column
+   * 
+   * @return The name of the function
+   */
+  public String getFunctionName() {
+    return _functionName;
+  }
+
+  /**
+   * Sets the name of the function that applies to this column
+   * 
+   * @param size
+   *          The size
+   */
+  public void setFunctionName(String _functionName) {
+    this._functionName = _functionName;
+  }
+
+  /**
    * {@inheritDoc}
    */
   public Object clone() throws CloneNotSupportedException {
@@ -182,8 +203,8 @@
    * @return <code>true</code> if this index column is equal (ignoring case) to the given one
    */
   public boolean equalsIgnoreCase(IndexColumn other) {
-    return new EqualsBuilder().append(_name.toUpperCase(), other._name.toUpperCase()).append(_size,
-        other._size).isEquals();
+    return new EqualsBuilder().append(_name.toUpperCase(), other._name.toUpperCase())
+        .append(_size, other._size).isEquals();
   }
 
   /**
--- a/src/org/apache/ddlutils/platform/SqlBuilder.java	Wed May 27 14:58:33 2015 +0200
+++ b/src/org/apache/ddlutils/platform/SqlBuilder.java	Wed Jul 01 16:21:28 2015 +0200
@@ -3374,7 +3374,14 @@
           if (idx > 0) {
             print(", ");
           }
-          printIdentifier(getColumnName(col));
+          if (idxColumn.getFunctionName() != null && !idxColumn.getFunctionName().isEmpty()) {
+            // wrap the function name around the column name
+            print(idxColumn.getFunctionName().toUpperCase() + "(");
+            printIdentifier(getColumnName(col));
+            print(")");
+          } else {
+            printIdentifier(getColumnName(col));
+          }
         }
 
         print(")");
--- a/src/org/apache/ddlutils/platform/oracle/OracleModelLoader.java	Wed May 27 14:58:33 2015 +0200
+++ b/src/org/apache/ddlutils/platform/oracle/OracleModelLoader.java	Wed Jul 01 16:21:28 2015 +0200
@@ -17,10 +17,13 @@
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Types;
+import java.util.StringTokenizer;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 
 import org.apache.ddlutils.model.ForeignKey;
+import org.apache.ddlutils.model.Index;
+import org.apache.ddlutils.model.IndexColumn;
 import org.apache.ddlutils.model.Reference;
 import org.apache.ddlutils.model.Table;
 import org.apache.ddlutils.platform.ModelLoaderBase;
@@ -35,6 +38,8 @@
 
   protected PreparedStatement _stmt_comments_tables;
 
+  private static final String VIRTUAL_COLUMN_PREFIX = "SYS_NC";
+
   /** Creates a new instance of BasicModelLoader */
   public OracleModelLoader() {
   }
@@ -101,14 +106,14 @@
         .prepareStatement("SELECT C.COLUMN_NAME, C2.COLUMN_NAME FROM USER_CONS_COLUMNS C, USER_CONS_COLUMNS C2 WHERE C.CONSTRAINT_NAME = ? and C2.CONSTRAINT_NAME = ? and c.position = c2.position ORDER BY C.POSITION");
 
     _stmt_listindexes = _connection
-        .prepareStatement("SELECT INDEX_NAME, UNIQUENESS FROM USER_INDEXES U WHERE TABLE_NAME = ? AND INDEX_TYPE = 'NORMAL' AND NOT EXISTS (SELECT 1 FROM USER_CONSTRAINTS WHERE TABLE_NAME = U.TABLE_NAME AND INDEX_NAME = U.INDEX_NAME AND CONSTRAINT_TYPE IN ('U', 'P')) ORDER BY INDEX_NAME");
+        .prepareStatement("SELECT U.INDEX_NAME, U.UNIQUENESS, UE.COLUMN_EXPRESSION FROM USER_INDEXES U LEFT JOIN USER_IND_EXPRESSIONS UE ON U.INDEX_NAME = UE.INDEX_NAME WHERE U.TABLE_NAME = ? AND (U.INDEX_TYPE = 'NORMAL' OR U.INDEX_TYPE = 'FUNCTION-BASED NORMAL') AND NOT EXISTS (SELECT 1 FROM USER_CONSTRAINTS WHERE TABLE_NAME = U.TABLE_NAME AND INDEX_NAME = U.INDEX_NAME AND CONSTRAINT_TYPE IN ('U', 'P')) ORDER BY INDEX_NAME");
     _stmt_listindexes_prefix = _connection
-        .prepareStatement("SELECT INDEX_NAME, UNIQUENESS FROM USER_INDEXES U WHERE TABLE_NAME = ? AND INDEX_TYPE = 'NORMAL' AND NOT EXISTS (SELECT 1 FROM USER_CONSTRAINTS WHERE TABLE_NAME = U.TABLE_NAME AND INDEX_NAME = U.INDEX_NAME AND CONSTRAINT_TYPE IN ('U', 'P')) AND (upper(INDEX_NAME) LIKE 'EM_"
+        .prepareStatement("SELECT U.INDEX_NAME, U.UNIQUENESS, UE.COLUMN_EXPRESSION FROM USER_INDEXES U LEFT JOIN USER_IND_EXPRESSIONS UE ON U.INDEX_NAME = UE.INDEX_NAME WHERE TABLE_NAME = ? AND (U.INDEX_TYPE = 'NORMAL' OR U.INDEX_TYPE = 'FUNCTION-BASED NORMAL') AND NOT EXISTS (SELECT 1 FROM USER_CONSTRAINTS WHERE TABLE_NAME = U.TABLE_NAME AND INDEX_NAME = U.INDEX_NAME AND CONSTRAINT_TYPE IN ('U', 'P')) AND (upper(U.INDEX_NAME) LIKE 'EM_"
             + _prefix
-            + "\\_%' ESCAPE '\\' OR (upper(INDEX_NAME)||UPPER(TABLE_NAME) IN (SELECT upper(NAME1)||UPPER(NAME2) FROM AD_EXCEPTIONS WHERE AD_MODULE_ID='"
-            + _moduleId + "'))) ORDER BY INDEX_NAME");
+            + "\\_%' ESCAPE '\\' OR (upper(U.INDEX_NAME)||UPPER(U.TABLE_NAME) IN (SELECT upper(NAME1)||UPPER(NAME2) FROM AD_EXCEPTIONS WHERE AD_MODULE_ID='"
+            + _moduleId + "'))) ORDER BY U.INDEX_NAME");
     _stmt_listindexes_noprefix = _connection
-        .prepareStatement("SELECT INDEX_NAME, UNIQUENESS FROM USER_INDEXES U WHERE TABLE_NAME = ? AND INDEX_TYPE = 'NORMAL' AND NOT EXISTS (SELECT 1 FROM USER_CONSTRAINTS WHERE TABLE_NAME = U.TABLE_NAME AND INDEX_NAME = U.INDEX_NAME AND CONSTRAINT_TYPE IN ('U', 'P')) AND upper(INDEX_NAME) NOT LIKE 'EM\\_%' ESCAPE '\\' ORDER BY INDEX_NAME");
+        .prepareStatement("SELECT U.INDEX_NAME, U.UNIQUENESS, UE.COLUMN_EXPRESSION FROM USER_INDEXES U LEFT JOIN USER_IND_EXPRESSIONS UE ON U.INDEX_NAME = UE.INDEX_NAME WHERE TABLE_NAME = ? AND (U.INDEX_TYPE = 'NORMAL' OR U.INDEX_TYPE = 'FUNCTION-BASED NORMAL') AND NOT EXISTS (SELECT 1 FROM USER_CONSTRAINTS WHERE TABLE_NAME = U.TABLE_NAME AND INDEX_NAME = U.INDEX_NAME AND CONSTRAINT_TYPE IN ('U', 'P')) AND upper(U.INDEX_NAME) NOT LIKE 'EM_%'  ORDER BY U.INDEX_NAME");
     _stmt_indexcolumns = _connection
         .prepareStatement("SELECT COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME = ? ORDER BY COLUMN_POSITION");
 
@@ -320,6 +325,57 @@
     return t;
   }
 
+  @Override
+  // Overrides readIndex to be able to discard indexes that use non supported functions
+  protected Index readIndex(ResultSet rs) throws SQLException {
+    String indexRealName = rs.getString(1);
+    String indexName = indexRealName.toUpperCase();
+
+    final Index inx = new Index();
+
+    inx.setName(indexName);
+    inx.setUnique(translateUniqueness(rs.getString(2)));
+    // The index expression will be defined only for function based indexes
+    final String indexExpression = rs.getString(3);
+    if (indexExpression != null && !indexExpression.isEmpty()
+        && !isValidExpression(indexExpression)) {
+      _log.error("The index " + inx.getName() + " uses a non supported function: "
+          + indexExpression);
+      return null;
+    }
+    _stmt_indexcolumns.setString(1, indexRealName);
+    fillList(_stmt_indexcolumns, new RowFiller() {
+      public void fillRow(ResultSet r) throws SQLException {
+        String columnName = r.getString(1);
+        IndexColumn inxcol = null;
+        if (indexExpression != null && !indexExpression.isEmpty()
+            && columnName.startsWith(VIRTUAL_COLUMN_PREFIX)) {
+          // The name of function based index columns needs to be translated from the name of the
+          // virtual column created by Oracle to the name of the column in its table
+          inxcol = getFunctionBasedIndexColumn(indexExpression);
+        } else {
+          inxcol = new IndexColumn();
+          inxcol.setName(columnName);
+        }
+        inx.addColumn(inxcol);
+      }
+    });
+    return inx;
+  }
+
+  // Given an index expression, returns the name of the referenced column
+  // The index expression will be like this: UPPER("COL1")
+  private IndexColumn getFunctionBasedIndexColumn(String indexExpression) {
+    // The column name will be wrapped by (" and ")
+    String functionName = indexExpression.substring(0, indexExpression.indexOf("("));
+    String columnName = indexExpression.substring(indexExpression.indexOf("(\"") + 2,
+        indexExpression.indexOf("\")"));
+    IndexColumn indexColumn = new IndexColumn();
+    indexColumn.setName(columnName);
+    indexColumn.setFunctionName(functionName);
+    return indexColumn;
+  }
+
   /*
    * Overloaded version for oracle as first sql does return one more value which needs to be passed
    * to the 2nd sql reading the columns (done this way to improve performance (issue 17796)
@@ -352,4 +408,45 @@
     return fk;
   }
 
+  // Check if an index expression is valid
+  // An index expression will be valid if it uses a function that accepts just one input parameter
+  // There is no need to check if the function exists, as the provided indexExpression belong to an
+  // existing index
+  private boolean isValidExpression(String indexExpressions) {
+    boolean isValid = true;
+    StringTokenizer st = new StringTokenizer(indexExpressions, "),");
+    while (st.hasMoreElements()) {
+      String indexExpression = st.nextToken();
+      if (!indexExpression.endsWith(")")) {
+        indexExpression = indexExpression + ")";
+      }
+      String functionName = indexExpression.substring(0, indexExpression.indexOf("("));
+      if (!isMonadicFunction(functionName)) {
+        isValid = false;
+        break;
+      }
+    }
+    return isValid;
+  }
+
+  // Check if a function is monadic (accepts just one input argument) by querying ALL_ARGUMENTS
+  protected boolean isMonadicFunction(String functionName) {
+    boolean isFunctionMonadic = true;
+    try {
+      PreparedStatement st = null;
+      st = _connection
+          .prepareStatement("select count(count(*)) from ALL_ARGUMENTS where OBJECT_NAME = ? and in_out = 'IN' group by subprogram_id having count(*) = 1");
+      st.setString(1, functionName.toUpperCase());
+      ResultSet rs = st.executeQuery();
+      if (rs.next()) {
+        int nMonadicFunctions = rs.getInt(1);
+        isFunctionMonadic = (nMonadicFunctions > 0);
+      }
+
+    } catch (SQLException e) {
+      e.printStackTrace();
+    }
+    return isFunctionMonadic;
+  }
+
 }
--- a/src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java	Wed May 27 14:58:33 2015 +0200
+++ b/src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java	Wed Jul 01 16:21:28 2015 +0200
@@ -21,6 +21,7 @@
 import java.util.Collection;
 import java.util.HashMap;
 import java.util.Iterator;
+import java.util.List;
 import java.util.Map;
 import java.util.StringTokenizer;
 import java.util.Vector;
@@ -65,6 +66,8 @@
 
   protected Map<Integer, Integer> _paramtypes = new HashMap<Integer, Integer>();
 
+  private static final String FUNCTION_BASED_COLUMN_INDEX_POSITION = "0";
+
   /** Creates a new instance of PostgreSqlModelLoader */
   public PostgreSqlModelLoader() {
   }
@@ -272,7 +275,7 @@
             + " WHERE pc.contype='f' and pc.conrelid= pc1.oid and pc.conname = ? and pa1.attrelid = pc1.oid and pa1.attnum = ANY(pc.conkey)"
             + " and pc.confrelid = pc2.oid and pa2.attrelid = pc2.oid and pa2.attnum = ANY(pc.confkey)");
 
-    sql = "SELECT PG_CLASS.RELNAME, CASE PG_INDEX.indisunique WHEN true THEN 'UNIQUE' ELSE 'NONUNIQUE' END"
+    sql = "SELECT PG_CLASS.RELNAME, CASE PG_INDEX.indisunique WHEN true THEN 'UNIQUE' ELSE 'NONUNIQUE' END, PG_GET_EXPR(PG_INDEX.indexprs,PG_INDEX.indrelid, true)"
         + " FROM PG_INDEX, PG_CLASS, PG_CLASS PG_CLASS1, PG_NAMESPACE"
         + " WHERE PG_INDEX.indexrelid = PG_CLASS.OID"
         + " AND PG_INDEX.indrelid = PG_CLASS1.OID"
@@ -1028,18 +1031,111 @@
       }
     });
 
-    /*
-     * Re-order the IndexColumn into the correct order (based on index-definition) as they are read
-     * without ordering them from the metadata
-     */
-    if (apositions.size() > 0) {
-      for (String pos : (apositions.get(0).split(","))) {
-        inx.addColumn(colMap.get(pos));
+    String indexExpression = rs.getString(3);
+    if (indexExpression != null && !indexExpression.isEmpty()) {
+      // if it is a function based index, check that the functions used are valid
+      if (isValidExpression(indexExpression)) {
+        List<IndexColumn> functionBasedIndexColumnList = getIndexColumnsFromExpression(indexExpression);
+        // colMap contains the index column where functions are not applied
+        // functionBasedIndexColumnList contains the index column where functions are applied
+        if (colMap.isEmpty()) {
+          // there are only function based index columns, no need to merge them with non-function
+          // based columns
+          for (IndexColumn indexColumn : functionBasedIndexColumnList) {
+            inx.addColumn(indexColumn);
+          }
+        } else {
+          int addedFunctionBasedIndexes = 0;
+          // if colMap is not empty we can be sure that apositions is not empty either
+          // all the values of apositions contains the same values, so we just take the first one
+          for (String pos : (apositions.get(0).split(","))) {
+            // if the position is FUNCTION_BASED_COLUMN_INDEX_POSITION, that means that the next
+            // column should be a function based one, use the next one
+            if (FUNCTION_BASED_COLUMN_INDEX_POSITION.equals(pos)) {
+              inx.addColumn(functionBasedIndexColumnList.get(addedFunctionBasedIndexes++));
+            } else {
+              inx.addColumn(colMap.get(pos));
+            }
+          }
+        }
+      } else {
+        _log.error("The index " + inx.getName() + " uses a non supported function: "
+            + indexExpression);
+        return null;
+      }
+    } else {
+      /*
+       * Re-order the IndexColumn into the correct order (based on index-definition) as they are
+       * read without ordering them from the metadata
+       */
+      if (apositions.size() > 0) {
+        for (String pos : (apositions.get(0).split(","))) {
+          inx.addColumn(colMap.get(pos));
+        }
       }
     }
     return inx;
   }
 
+  // Check if an index expression is valid
+  // An index expression will be valid if it uses a function that accepts just one input parameter
+  // There is no need to check if the function exists, as the provided indexExpression belong to an
+  // existing index
+  private boolean isValidExpression(String indexExpressions) {
+    boolean isValid = true;
+    StringTokenizer st = new StringTokenizer(indexExpressions, "),");
+    while (st.hasMoreElements()) {
+      String indexExpression = st.nextToken();
+      if (!indexExpression.endsWith(")")) {
+        indexExpression = indexExpression + ")";
+      }
+      String functionName = indexExpression.substring(0, indexExpression.indexOf("("));
+      if (!isMonadicFunction(functionName)) {
+        isValid = false;
+        break;
+      }
+    }
+    return isValid;
+  }
+
+  // Check if a function is monadic (accepts just one input argument) by querying PG_PROC
+  protected boolean isMonadicFunction(String functionName) {
+    boolean isFunctionMonadic = true;
+    try {
+      PreparedStatement st = _connection
+          .prepareStatement("select count(*) from pg_proc where upper(proname) = ? and pronargs = 1");
+      st.setString(1, functionName.toUpperCase());
+
+      ResultSet rs = st.executeQuery();
+      if (rs.next()) {
+        int nMonadicFunctions = rs.getInt(1);
+        isFunctionMonadic = (nMonadicFunctions > 0);
+      }
+    } catch (SQLException e) {
+      e.printStackTrace();
+    }
+    return isFunctionMonadic;
+  }
+
+  private List<IndexColumn> getIndexColumnsFromExpression(String indexExpressions) {
+    List<IndexColumn> indexColumnList = new ArrayList<IndexColumn>();
+    StringTokenizer st = new StringTokenizer(indexExpressions, "),");
+    while (st.hasMoreElements()) {
+      String indexExpression = st.nextToken();
+      if (!indexExpression.endsWith(")")) {
+        indexExpression = indexExpression + ")";
+      }
+      String functionName = indexExpression.substring(0, indexExpression.indexOf("("));
+      String columnName = indexExpression.substring(indexExpression.indexOf("(") + 1,
+          indexExpression.indexOf(":"));
+      IndexColumn inxcol = new IndexColumn();
+      inxcol.setName(columnName.toUpperCase());
+      inxcol.setFunctionName(functionName.toUpperCase());
+      indexColumnList.add(inxcol);
+    }
+    return indexColumnList;
+  }
+
   @Override
   protected Unique readUnique(ResultSet rs) throws SQLException {
     // similar to readTable, see there for definition of both (regarding case)