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

[Galder Zamarreño (JIRA)]

    [ https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12628855#comment-12628855 ] 

Galder Zamarreño commented on ISPN-1204:
----------------------------------------

I had a chat online with Nicolas to clarify the use case for this:

- In the current solution the DB schema is retrieved from the configured table name. So, given a table name, "portal.cachetable", the schema is 'portal' and table name 'cachetable'. If you want to support multiple applications sharing the same db, you're forced to use a different table name.
- The solution proposed by Nicolas allows an SQL statement to be run within an implicit schema, which is mapped to a particular username+password. This way, simply configuring user name and password is enough to allow multiple applications to share the same table name, cos each is mapped to a different schema, and hence don't class.
- Configuring username+password is seen as more administrator-friendly than having to modify table name.
- On top of that, having each app use different username+password (without need to change table name) serves as a way to isolate data from a security perspective at the database level. This is important for PaaS environments.

It shouldn't be hard to implement, so moving it to 5.1

> 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: Galder Zamarreño
>             Fix For: 5.2.0.FINAL
>
>
> 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. Feel free to resolve it as won't fix if you don't find it relevant.
> NB1: 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
> NB2: This patch works well on all listed DB only if auto commit is set to true which should be true in your case since it seems to be the exact same code as JBC

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