fixed issue 20421: Review needed indexes
authorAsier Lostalé <asier.lostale@openbravo.com>
Mon, 12 Aug 2013 13:20:58 +0200
changeset 20974 1cb4b186b7c6
parent 20973 8151a5b44678
child 20975 ca15098edeb0
fixed issue 20421: Review needed indexes

Added jUnit test to warranty no missing indexes for tab parent-child
relationships
src-test/org/openbravo/test/AllAntTaskTests.java
src-test/org/openbravo/test/AllTests.java
src-test/org/openbravo/test/model/IndexesTest.java
--- a/src-test/org/openbravo/test/AllAntTaskTests.java	Mon Aug 12 12:02:10 2013 +0530
+++ b/src-test/org/openbravo/test/AllAntTaskTests.java	Mon Aug 12 13:20:58 2013 +0200
@@ -46,6 +46,7 @@
 import org.openbravo.test.dal.ViewTest;
 import org.openbravo.test.expression.EvaluationTest;
 import org.openbravo.test.model.ClassLoaderTest;
+import org.openbravo.test.model.IndexesTest;
 import org.openbravo.test.model.OneToManyTest;
 import org.openbravo.test.model.RuntimeModelTest;
 import org.openbravo.test.model.UniqueConstraintTest;
@@ -115,6 +116,7 @@
     suite.addTestSuite(OneToManyTest.class);
     suite.addTestSuite(UniqueConstraintTest.class);
     suite.addTestSuite(ClassLoaderTest.class);
+    suite.addTestSuite(IndexesTest.class);
 
     // modularity
     suite.addTestSuite(DatasetServiceTest.class);
--- a/src-test/org/openbravo/test/AllTests.java	Mon Aug 12 12:02:10 2013 +0530
+++ b/src-test/org/openbravo/test/AllTests.java	Mon Aug 12 13:20:58 2013 +0200
@@ -11,7 +11,7 @@
  * under the License. 
  * The Original Code is Openbravo ERP. 
  * The Initial Developer of the Original Code is Openbravo SLU 
- * All portions are Copyright (C) 2008-2012 Openbravo SLU 
+ * All portions are Copyright (C) 2008-2013 Openbravo SLU 
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -40,6 +40,7 @@
 import org.openbravo.test.dal.ValidationTest;
 import org.openbravo.test.expression.EvaluationTest;
 import org.openbravo.test.model.ClassLoaderTest;
+import org.openbravo.test.model.IndexesTest;
 import org.openbravo.test.model.OneToManyTest;
 import org.openbravo.test.model.RuntimeModelTest;
 import org.openbravo.test.security.AccessLevelTest;
@@ -87,6 +88,7 @@
     suite.addTestSuite(RuntimeModelTest.class);
     suite.addTestSuite(OneToManyTest.class);
     suite.addTestSuite(ClassLoaderTest.class);
+    suite.addTestSuite(IndexesTest.class);
 
     // expression
     suite.addTestSuite(EvaluationTest.class);
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-test/org/openbravo/test/model/IndexesTest.java	Mon Aug 12 13:20:58 2013 +0200
@@ -0,0 +1,152 @@
+/*
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html 
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * under the License. 
+ * The Original Code is Openbravo ERP. 
+ * The Initial Developer of the Original Code is Openbravo SLU 
+ * All portions are Copyright (C) 2013 Openbravo SLU 
+ * All Rights Reserved. 
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+ */
+
+package org.openbravo.test.model;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.List;
+
+import org.openbravo.service.db.DalConnectionProvider;
+import org.openbravo.test.base.BaseTest;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+/**
+ * Tests cases to warranty standard database indexes from child to parent columns are present.
+ * 
+ * @author alostale
+ * 
+ */
+public class IndexesTest extends BaseTest {
+  final static private Logger log = LoggerFactory.getLogger(IndexesTest.class);
+
+  /**
+   * Verifies in subtabs that their tables have a index to for the FK column linking to their parent
+   * table.
+   */
+  public void testSubTabs() {
+    int windowName = 1;
+    int parentTab = 2;
+    int parentTable = 3;
+    int childTab = 4;
+    int childTable = 5;
+    int otherIndexes = 6;
+
+    String sql = "select *" //
+        + "from (" //
+        + "    select w.name as window_name," //
+        + "           t1.name as parentTab," //
+        + "           ptb.tablename as parentTable," //
+        + "           t2.name as childTab," //
+        + "           ctb.tablename as childTable," //
+        + "          (select count(*)" //
+        + "              from user_ind_columns" //
+        + "             where table_name = upper(ctb.tablename)" //
+        + "               and column_name = upper(ptb.tablename)||'_ID') as indexes_to_parent_any_col," //
+        + "           (select count(*)" //
+        + "              from user_ind_columns" //
+        + "             where table_name = upper(ctb.tablename)" //
+        + "               and column_name = upper(ptb.tablename)||'_ID'" //
+        + "               and column_position = 1) as indexes_to_parent_first_col" //
+        + "      from ad_tab t1," //
+        + "           ad_tab t2," //
+        + "           ad_table ptb," //
+        + "           ad_table ctb," //
+        + "           ad_window w," //
+        + "           (select ct.ad_tab_id as child, " //
+        + "                   (select pt.ad_tab_id" //
+        + "                      from ad_tab pt" //
+        + "                     where pt.ad_window_id = ct.ad_window_id" //
+        + "                       and pt.tablevel = (select max(tablevel)" //
+        + "                                            from ad_tab wt" //
+        + "                                           where wt.ad_window_id = pt.ad_window_id" //
+        + "                                             and wt.seqno < ct.seqno" //
+        + "                                             and wt.tablevel < ct.tablevel)" //
+        + "                       and pt.seqno    = (select max(seqno)" //
+        + "                                            from ad_tab wt" //
+        + "                                           where wt.ad_window_id = pt.ad_window_id" //
+        + "                                             and wt.seqno < ct.seqno" //
+        + "                                             and wt.tablevel = (select max(tablevel)" //
+        + "                                                      from ad_tab wt" //
+        + "                                                     where wt.ad_window_id = pt.ad_window_id" //
+        + "                                                       and wt.seqno < ct.seqno" //
+        + "                                                       and wt.tablevel < ct.tablevel))) as parent" //
+        + "              from ad_tab ct, ad_table t" //
+        + "             where tablevel > 0" //
+        + "               and ct.ad_table_id = t.ad_table_id" //
+        + "               and t.isview ='N') as rel" //
+        + "     where rel.child = t2.ad_tab_id" //
+        + "       and t2.ad_table_id = ctb.ad_table_id" //
+        + "       and rel.parent = t1.ad_tab_id" //
+        + "       and t1.ad_table_id = ptb.ad_table_id" //
+        + "       and t1.ad_window_id = w.ad_window_id" //
+        + "       and exists (select 1" //
+        + "                     from ad_column c" //
+        + "                    where c.ad_table_id = ctb.ad_table_id" //
+        + "                      and upper(columnname) = upper(ptb.tablename)||'_ID')" //
+        + ") i" //
+        + " where indexes_to_parent_first_col = 0" //
+        + " order by parenttable";
+
+    List<String> errors = new ArrayList<String>();
+    PreparedStatement sqlQuery = null;
+    ResultSet rs = null;
+    try {
+      sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql);
+      sqlQuery.execute();
+
+      rs = sqlQuery.getResultSet();
+      while (rs.next()) {
+        String msg = "Missing index in " + rs.getString(childTable) + "."
+            + rs.getString(parentTable) + "_ID. Because of child tab relationship in window "
+            + rs.getString(windowName) + " from tab " + rs.getString(childTab) + " to "
+            + rs.getString(parentTab) + ". ";
+
+        int otherIndexesCount = rs.getInt(otherIndexes);
+        if (otherIndexesCount > 0) {
+          msg += "There are other "
+              + otherIndexesCount
+              + " indexes or unique constraints including that column. You might recheck columns position for them.";
+        }
+        errors.add(msg);
+      }
+
+      for (String error : errors) {
+        log.error(error);
+      }
+
+      assertEquals("There are missing indexes!", 0, errors.size());
+    } catch (Exception e) {
+      log.error("Error when executing query", e);
+    } finally {
+      try {
+        if (sqlQuery != null) {
+          sqlQuery.close();
+        }
+        if (rs != null) {
+          rs.close();
+        }
+      } catch (Exception e) {
+        log.error("Error when closing statement", e);
+      }
+    }
+  }
+}