Contribute a properties-based SQL Provider

[Jeff Mesnil <jm...@gmail.com>]

Hi,

We have integrated Artemis 1.x in WildFly application server with a
different SQLProvider implementation to support Artemis JDBC store.
The issue we had at the time with Artemis SQLProvider was that
supporting a new databases required to add a new class to specify its
specific JDBC statements and ultimately require new releases for each
new DB we wanted to test & support.

To circumvent this issue, we wrote a SQLProvider[1] to use for Artemis
JDBC store that is using a Properties file[2] and a simple overridding
mechanism to provide DB-specific statements.
For example to support Oracle DB, Artemis mechanism required to write
(and maintain) this class:
https://github.com/apache/activemq-artemis/blob/master/artemis-jdbc-store/src/main/java/org/apache/activemq/artemis/jdbc/store/drivers/oracle/Oracle12CSQLProvider.java

Using our property-based implementation, we only need to add 2 lines
to the properties file:

https://github.com/wildfly/wildfly/blob/master/feature-pack/src/main/resources/modules/system/layers/base/org/wildfly/extension/messaging-activemq/main/database/journal-sql.properties#L43-L45

When the SQLProvider will be queried for a SQL statement, it will
first searched for a DB-specific dialect and falls back to a generic
one else:

https://github.com/wildfly/wildfly/blob/master/messaging-activemq/src/main/java/org/wildfly/extension/messaging/activemq/PropertySQLProviderFactory.java#L249

The dialect itself is determined used DataSource properties and metadata:

https://github.com/wildfly/wildfly/blob/master/messaging-activemq/src/main/java/org/wildfly/extension/messaging/activemq/PropertySQLProviderFactory.java#L85

In addition, this properties file can be easily modified by users
without requiring a new release to support another database.

We have started development on WildFly 12 and I am looking to
integrate Artemis 2.4.x.
I don't want to have to maintain a 2nd implementation of the SQL
provider in WildFly codebase and I really think that our
properties-based approach is sounder than the current Artemis
implementation.

I'd like to propose to contribute back this properties-based SQL
provider to Artemis codebase in replacement to its current
implementation. I think this would be beneficial for Artemis as it
would reduce the amount of work to support a new DB, reduce the number
of classes for a better maintainability and, of course, ease the
integration of Artemis into WildFly.

What do you think?

jeff

[1] https://github.com/wildfly/wildfly/blob/master/messaging-activemq/src/main/java/org/wildfly/extension/messaging/activemq/PropertySQLProviderFactory.java
[2] https://github.com/wildfly/wildfly/blob/master/feature-pack/src/main/resources/modules/system/layers/base/org/wildfly/extension/messaging-activemq/main/database/journal-sql.properties
-- 
Jeff Mesnil
jmesnil@gmail.com
http://jmesnil.net/weblog/

Re: Contribute a properties-based SQL Provider

[Martyn Taylor <mt...@redhat.com>]

Hi Jeff,

I think I much prefer this approach of keeping the database specific logic
(i.e. the SQL statements) separate from the implementation.  This would
allow us to have a pure JDBC API + SQL implementation, and adding support
for a new vendor would just be a case of dropping in a new properties
file.  I've taken a look at what you've done and I much prefer it to our
generic SQL provider implementation.

I'd still like to be able to extend the properties impl, as some vendors do
offer their own APIs to manage things like large objects, which I assume,
are highly optimised over the JDBC API.  How about we replace all our impls
(expect the postgres file impl) with what you have here and make the
properties based driver the default?  We can then override this with
implementations that use the optimised APIs by passing the driver class to
the config?  We still need to ensure that our connections are managed in
the same way, but other than that I think it should be relatively straight
forward.  Let me know if you need any help with this.

Nice work!
Martyn



On Thu, Nov 30, 2017 at 2:59 PM, Jeff Mesnil <jm...@gmail.com> wrote:

> Hi,
>
> We have integrated Artemis 1.x in WildFly application server with a
> different SQLProvider implementation to support Artemis JDBC store.
> The issue we had at the time with Artemis SQLProvider was that
> supporting a new databases required to add a new class to specify its
> specific JDBC statements and ultimately require new releases for each
> new DB we wanted to test & support.
>
> To circumvent this issue, we wrote a SQLProvider[1] to use for Artemis
> JDBC store that is using a Properties file[2] and a simple overridding
> mechanism to provide DB-specific statements.
> For example to support Oracle DB, Artemis mechanism required to write
> (and maintain) this class:
> https://github.com/apache/activemq-artemis/blob/master/
> artemis-jdbc-store/src/main/java/org/apache/activemq/
> artemis/jdbc/store/drivers/oracle/Oracle12CSQLProvider.java
>
> Using our property-based implementation, we only need to add 2 lines
> to the properties file:
>
> https://github.com/wildfly/wildfly/blob/master/feature-
> pack/src/main/resources/modules/system/layers/base/org/wildfly/extension/
> messaging-activemq/main/database/journal-sql.properties#L43-L45
>
> When the SQLProvider will be queried for a SQL statement, it will
> first searched for a DB-specific dialect and falls back to a generic
> one else:
>
> https://github.com/wildfly/wildfly/blob/master/messaging-
> activemq/src/main/java/org/wildfly/extension/messaging/activemq/
> PropertySQLProviderFactory.java#L249
>
> The dialect itself is determined used DataSource properties and metadata:
>
> https://github.com/wildfly/wildfly/blob/master/messaging-
> activemq/src/main/java/org/wildfly/extension/messaging/activemq/
> PropertySQLProviderFactory.java#L85
>
> In addition, this properties file can be easily modified by users
> without requiring a new release to support another database.
>
> We have started development on WildFly 12 and I am looking to
> integrate Artemis 2.4.x.
> I don't want to have to maintain a 2nd implementation of the SQL
> provider in WildFly codebase and I really think that our
> properties-based approach is sounder than the current Artemis
> implementation.
>
> I'd like to propose to contribute back this properties-based SQL
> provider to Artemis codebase in replacement to its current
> implementation. I think this would be beneficial for Artemis as it
> would reduce the amount of work to support a new DB, reduce the number
> of classes for a better maintainability and, of course, ease the
> integration of Artemis into WildFly.
>
> What do you think?
>
> jeff
>
> [1] https://github.com/wildfly/wildfly/blob/master/messaging-
> activemq/src/main/java/org/wildfly/extension/messaging/activemq/
> PropertySQLProviderFactory.java
> [2] https://github.com/wildfly/wildfly/blob/master/feature-
> pack/src/main/resources/modules/system/layers/base/org/wildfly/extension/
> messaging-activemq/main/database/journal-sql.properties
> --
> Jeff Mesnil
> jmesnil@gmail.com
> http://jmesnil.net/weblog/
>