[infinispan-issues] [JBoss JIRA] Created: (ISPN-1204) Allow to be DB schema independent

[Nicolas Filotto (JIRA)]

Allow to be DB schema independent
---------------------------------

                 Key: ISPN-1204
                 URL: https://issues.jboss.org/browse/ISPN-1204
             Project: Infinispan
          Issue Type: Enhancement
          Components: Loaders and Stores
            Reporter: Nicolas Filotto
            Assignee: Manik Surtani


The current code checks if a table exists thanks to con.getMetaData().getTables(...) which is totally DB schema dependent, your code allow us to specify the schema by prefixing the table name with the name of the schema in the config which is not really convenient in practice especially if we have a lot of config files. You could easily make your code fully DB schema independent by replacing the method org.infinispan.loaders.jdbc.TableManipulation.tableExists(Connection connection, String tableName) with this content:
{code}
   public boolean tableExists(Connection connection, String tableName) {
      assertNotNull(getTableName(), "table name is mandatory");
      Statement stmt = null;
      ResultSet trs = null;
      try {
         stmt = connection.createStatement();
         trs = stmt.executeQuery("SELECT count(*) FROM " + tableName);
         return trs.next();
      }
      catch (SQLException e) {
         if (log.isTraceEnabled()) {
            log.trace("SQLException occurs while checking the table " + tableName, e);
         }
         return false;
      }
      finally {
         JdbcUtil.safeClose(trs);
         JdbcUtil.safeClose(stmt);
      }
   }
{code}

I know that it is a much less elegant and standard approach but it allows to simplify so much the config that I think that it makes sense to at least think about at it more than one second. Be free to resolve it as won't fix if you don't find it relevant.

FYI: We use the same approach in our product (EXOJCR-1374) with JBC and we successfully tested it on Oracle, MySQL, MS SQL, PostgreSQL, DB2 and Sybase

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira