12:22 p.m.
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
12:26 p.m.
New subject: [JBoss JIRA] Updated: (ISPN-1204) Allow to be DB schema independent
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Nicolas Filotto updated ISPN-1204:
----------------------------------
Description:
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.
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
was:
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
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.
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
12:29 p.m.
New subject: [JBoss JIRA] Updated: (ISPN-1204) Allow to be DB schema independent
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Nicolas Filotto updated ISPN-1204:
----------------------------------
Description:
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
was:
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.
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
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. 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
6:18 p.m.
New subject: [JBoss JIRA] Updated: (ISPN-1204) Allow to be DB schema independent
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Manik Surtani updated ISPN-1204:
--------------------------------
Assignee: Trustin Lee (was: Manik Surtani)
Fix Version/s: 5.1.0.BETA1
5.1.0.Final
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: Trustin Lee
Fix For: 5.1.0.BETA1, 5.1.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
4:31 p.m.
New subject: [JBoss JIRA] Updated: (ISPN-1204) Allow to be DB schema independent
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Manik Surtani updated ISPN-1204:
--------------------------------
Assignee: Galder Zamarreño (was: Trustin Lee)
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.1.0.BETA1, 5.1.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
4:37 p.m.
New subject: [JBoss JIRA] Updated: (ISPN-1204) Allow to be DB schema independent
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Galder Zamarreño updated ISPN-1204:
-----------------------------------
Fix Version/s: 5.2.0.FINAL
(was: 5.1.0.BETA1)
(was: 5.1.0.FINAL)
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
11:51 a.m.
New subject: [JBoss JIRA] Commented: (ISPN-1204) Allow to be DB schema independent
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Nicolas Filotto commented on ISPN-1204:
---------------------------------------
Just to let you know that the ability to host several instances of a product on the same
db instance, is regularly asked by customers (mostly customers of Red Hat). Believe me, we
had to do the same thing at eXo JCR level for this exact same reason, so you are of course
free to decide whatever is the best for your product but since 5.1.0.FINAL will be a
supported version if I were you I would try to do it for this version, at least it was my
duty to inform you.
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
4:35 p.m.
New subject: [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
4:37 p.m.
New subject: [JBoss JIRA] Updated: (ISPN-1204) Make JDBC cache store use implicit schemas for database tables
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Galder Zamarreño updated ISPN-1204:
-----------------------------------
Fix Version/s: 5.1.0.BETA1
5.1.0.FINAL
Make JDBC cache store use implicit schemas for database tables
--------------------------------------------------------------
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.1.0.BETA1, 5.1.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
4:37 p.m.
New subject: [JBoss JIRA] Updated: (ISPN-1204) Make JDBC cache store use implicit schemas for database tables
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Galder Zamarreño updated ISPN-1204:
-----------------------------------
Summary: Make JDBC cache store use implicit schemas for database tables (was:
Allow to be DB schema independent)
Fix Version/s: (was: 5.2.0.FINAL)
Make JDBC cache store use implicit schemas for database tables
--------------------------------------------------------------
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.1.0.BETA1, 5.1.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
5:11 p.m.
New subject: [JBoss JIRA] Commented: (ISPN-1204) Make JDBC cache store use implicit schemas for database tables
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Nicolas Filotto commented on ISPN-1204:
---------------------------------------
Great news, thank you so much for your understanding
Make JDBC cache store use implicit schemas for database tables
--------------------------------------------------------------
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.1.0.BETA1, 5.1.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
1:08 p.m.
New subject: [JBoss JIRA] Updated: (ISPN-1204) Make JDBC cache store use implicit schemas for database tables
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Galder Zamarreño updated ISPN-1204:
-----------------------------------
Status: Pull Request Sent (was: Open)
Git Pull Request: https://github.com/infinispan/infinispan/pull/545
Make JDBC cache store use implicit schemas for database tables
--------------------------------------------------------------
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.1.0.BETA1, 5.1.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
1:23 p.m.
New subject: [JBoss JIRA] Updated: (ISPN-1204) Make JDBC cache store use implicit schemas for database tables
[
https://issues.jboss.org/browse/ISPN-1204?page=com.atlassian.jira.plugin....
]
Manik Surtani updated ISPN-1204:
--------------------------------
Status: Resolved (was: Pull Request Sent)
Resolution: Done
Make JDBC cache store use implicit schemas for database tables
--------------------------------------------------------------
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.1.0.BETA1, 5.1.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
4604
days inactive
4695
days old
infinispan-issues@lists.jboss.org
12 comments
3 participants
participants (3)
-
Galder Zamarreño (JIRA) -
Manik Surtani (JIRA)
-
Nicolas Filotto (JIRA)