Add Query-Pre-Processor

[mp911de]

We should consider adding a Query Pre-Processor API that allows generic pre-processing of queries. The first use-case of query-pre-processing is named parameter expansion (see #23). Another case can be generic query augmentation to add or remove parts of a query and to mutate bound parameters. Query pre-processors can be modeled as a filter function to compose a chain of functions.

[wellingtoncosta]

Should this be implemented at compile time or at runtime? Sorry if it is a stupid question.

[mp911de]

There’s no such thing as a stupid question. We want to provide a runtime API so code can intercept and modify queries prior to execution.

[wellingtoncosta]

Got it. What kind of modifications? Optimizations? I'm so curious.

[mp911de]

You can use it to generate events, modify the query to e.g. implement security requirements, implement multi-tenancy, add generic query predicates.

[thekalinga]

Does this also support transparent routing of all queries to read/replica server and all CUDs to write/primary server

[mp911de]

Not sure this is applicable. If you’re using a database setup that supports primary/replica routing, then this would be the right place. Typically, command routing is rather a NoSql datastore feature. Am 20. Jan. 2019, 15:47 +0100 schrieb Ashok Koyi <notifications@github.com>:

…

Does this also support transparent routing of all queries to read/replica server and all CUDs to write/primary server — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[uaihebert]

@mp911de is this still required? I could take a look into it.

[mp911de]

I think this ticket requires some design up-front. Looking into Spring 5's WebClient and ExchangeFunction is a good exercise to learn about delegation, filtering, and pre-/post-processing.

[mp911de]

#73 brings PreparedOperation which can form a base to encapsulate operations that should happen on a Statement (SQL-rendering and binding of values)

[Shabin]

I am migrating from a non reactive to a reactive app. I have a requirement to update all my queries with a custom value, where I replace some pattern like :id in the query with a value specific to the current execution context.
In non reactive, I had extended JdbcTemplate and intercepted all the query methods to change the sql with the required value.

public class CustomJdbcTemplate extends JdbcTemplate {
    public <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) throws DataAccessException {
       // replace placeholders
       String statement = QueryHelper.getInstance().populateDefinedQueryPlaceHolders(sql);
       return super.query(statement, newArgTypePreparedStatementSetter(args, argTypes), rse);
   }
   //similar code in all query methods
}

What would be the correct approach for this with DatabaseClient? I assume a common query preprocessor would be a solution for this.

[mp911de]

Ideally, intercept calls execute with a custom DatabaseClient implementation that just pre-processes your sql.

[Shabin]

Tried doing that. Found it complicated that I had to implement all the GenericExecuteSpec, SelectFromSpec etc, for simply doing a query preprocessor.
If DefaultDatabaseClient was public I could have simply extended it.

[crahul19]

Any update on if this is gonna be implemented soon? I need to add a filter like " AND someCode IN (A,B,C)" to all my queries and there's 100's of them. It would be really helpful if there was a simple way of achieving this 3-4 years after this ticket was created?

[mp911de]

The most straightforward workaround is to provide a DatabaseClient implementation that intercepts the sql(…) method, does its rewrite, and delegates to the DatabaseClient instance created by Spring.

[crahul19]

Do I need to use AOP for intercepting the sql(…) method of DatabaseClient? We have mostly used ReactiveCrudRepository in the DAO where we use both the @query annotation and method name for generating the query but I can use the same DatabaseClient implementation for the Repository by defining a bean of type R2dbcEntityOperations.

If you could provide some hint on how can I intercept the sql() method then that will be highly appreciated.

Thanks In Advance.