[
https://issues.apache.org/jira/browse/FINERACT-2022?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Aleksandar Vidakovic updated FINERACT-2022:
-------------------------------------------
Description:
h2. Background and Motivation
Fineract initially had only JPA as its storage mechanism. When we moved from
Mifos to the Apache Software Foundation we had to give up Hibernate as the
default/standard JPA implementation and used the Apache sister project OpenJPA.
OpenJPA had a couple of performance and standard compliance related issues
which lead some developers to use plain native SQL queries in their service
implementations. Because MySQL was for a long time the only configured database
in the development environment developers started to introduce database system
dependent query constructs (e. g. date functions). This was one of the main
reasons why Fineract got stuck for a long time with MySQL 5.7 and for a while
we couldn't even upgrade to the latest MySQL 8 version. Support for PostgreSQL
was requested by the community repeatedly over the years, but couldn't be
provided due to the these database dependency issues. Starting with Fineract
1.6.x we replaced OpenJPA with EclipseLink and added a thin abstraction layer
for database system specific functions which allowed us to support PostgreSQL.
Nevertheless, we still have a lot (and growing) number of SQL native queries
that are provided as Strings. This makes refactoring at the least a very time
consuming task. Our database compatibility support is very limited (only
specific functions) and as we wrote the code ourselves we have an additional
burden to maintain it. Supporting more database systems (SQL Server, Oracle) in
this setup is not worth the effort.
h2. Target Personas
* developers
* integrators
* BaaS
h2. Goals
* introduce QueryDSL to generate code for type-safe SQL queries
* use QueryDSL also for complex JPA queries
* make sure all repository classes follow Spring Data best practices (aka
remove homegrown boilerplate code and annotations as much as possible)
* implement any unsupported features related to database queries as QueryDSL
extensions/plugins
* automatically generate most/all of the query related code
* avoid/remove all custom "@Query" annotations from repository classes
* avoid/remove all JdbcTemplate use; all queries need to be included
* avoid SQL/OQL queries as much as possible by using Spring Data query
function mapping
h2. Non-Goals
* replace all JPA related code with pure Spring Data JDBC and QueryDSL
h2. Proposed API Changes
h3. Spring Data JPA
Generate OQL DSL with QueryDSL and replace all custom JPA queries (either in
"@Query" annotations and/or in direct use of "EntityManager").
h3. Spring Data JDBC
Generate SQL DSL with QueryDSL and replace all (String based) SQL queries
(either in "@Query" annotations and/or in direct use of "JdbcTemplate").
h3. Business logic services
Avoid all direct use of JdbcTemplate or EntityManager. Make sure all query
related data is contained only in repository classes. Optional: enforce this as
a architecture rule with ArchUnit.
h2. Risks
Effectively we'll have three different database abstraction mechanisms in place
with with slightly different behavior:
* Spring Data JPA
* Spring Data JDBC
* QueryDSL
It might make sense to eliminate at least one of them in a later iteration.
h2. ETA
We have roughly 50 feature packages/modules with database related
functionality. Some are larger than others; first migrations could take a bit
longer before we establish a routine. A rough calculation would be 3-4 days per
feature/module which results in 150-200 development days or about 6 months to
finish this task. It might make sense to introduce specific unit tests to
ensure that we don't introduce regressions. This could add some overhead (maybe
1 day) to the initial estimate.
h2. Diagrams
TBD
h4.
> Type-safe native SQL queries
> ----------------------------
>
> Key: FINERACT-2022
> URL: https://issues.apache.org/jira/browse/FINERACT-2022
> Project: Apache Fineract
> Issue Type: Improvement
> Reporter: Aleksandar Vidakovic
> Priority: Major
> Fix For: 1.10.0
>
>
> h2. Background and Motivation
> Fineract initially had only JPA as its storage mechanism. When we moved from
> Mifos to the Apache Software Foundation we had to give up Hibernate as the
> default/standard JPA implementation and used the Apache sister project
> OpenJPA. OpenJPA had a couple of performance and standard compliance related
> issues which lead some developers to use plain native SQL queries in their
> service implementations. Because MySQL was for a long time the only
> configured database in the development environment developers started to
> introduce database system dependent query constructs (e. g. date functions).
> This was one of the main reasons why Fineract got stuck for a long time with
> MySQL 5.7 and for a while we couldn't even upgrade to the latest MySQL 8
> version. Support for PostgreSQL was requested by the community repeatedly
> over the years, but couldn't be provided due to the these database dependency
> issues. Starting with Fineract 1.6.x we replaced OpenJPA with EclipseLink and
> added a thin abstraction layer for database system specific functions which
> allowed us to support PostgreSQL.
> Nevertheless, we still have a lot (and growing) number of SQL native queries
> that are provided as Strings. This makes refactoring at the least a very time
> consuming task. Our database compatibility support is very limited (only
> specific functions) and as we wrote the code ourselves we have an additional
> burden to maintain it. Supporting more database systems (SQL Server, Oracle)
> in this setup is not worth the effort.
> h2. Target Personas
> * developers
> * integrators
> * BaaS
> h2. Goals
> * introduce QueryDSL to generate code for type-safe SQL queries
> * use QueryDSL also for complex JPA queries
> * make sure all repository classes follow Spring Data best practices (aka
> remove homegrown boilerplate code and annotations as much as possible)
> * implement any unsupported features related to database queries as QueryDSL
> extensions/plugins
> * automatically generate most/all of the query related code
> * avoid/remove all custom "@Query" annotations from repository classes
> * avoid/remove all JdbcTemplate use; all queries need to be included
> * avoid SQL/OQL queries as much as possible by using Spring Data query
> function mapping
> h2. Non-Goals
> * replace all JPA related code with pure Spring Data JDBC and QueryDSL
> h2. Proposed API Changes
> h3. Spring Data JPA
> Generate OQL DSL with QueryDSL and replace all custom JPA queries (either in
> "@Query" annotations and/or in direct use of "EntityManager").
> h3. Spring Data JDBC
> Generate SQL DSL with QueryDSL and replace all (String based) SQL queries
> (either in "@Query" annotations and/or in direct use of "JdbcTemplate").
> h3. Business logic services
> Avoid all direct use of JdbcTemplate or EntityManager. Make sure all query
> related data is contained only in repository classes. Optional: enforce this
> as a architecture rule with ArchUnit.
> h2. Risks
> Effectively we'll have three different database abstraction mechanisms in
> place with with slightly different behavior:
> * Spring Data JPA
> * Spring Data JDBC
> * QueryDSL
> It might make sense to eliminate at least one of them in a later iteration.
> h2. ETA
> We have roughly 50 feature packages/modules with database related
> functionality. Some are larger than others; first migrations could take a bit
> longer before we establish a routine. A rough calculation would be 3-4 days
> per feature/module which results in 150-200 development days or about 6
> months to finish this task. It might make sense to introduce specific unit
> tests to ensure that we don't introduce regressions. This could add some
> overhead (maybe 1 day) to the initial estimate.
> h2. Diagrams
> TBD
> h4.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
