[infinispan-issues] [JBoss JIRA] Commented: (ISPN-1204) Allow to be DB schema independent
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
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