This really doesn't seem too hard! Here's a quick draft leveraging the
translation capabilities from the jOOQ 3.15.0-SNAPSHOT Professional Edition
to translate Teradata QUALIFY to PostgreSQL (I marked the interesting stuff
in yellow):
package org.jooq.r2dbc;
import static org.jooq.impl.DSL.val;
import java.util.Map;
import java.util.TreeMap;
import org.jooq.Configuration;
import org.jooq.Param;
import org.jooq.Query;
import org.jooq.SQLDialect;
import org.jooq.conf.ParamType;
import org.jooq.impl.DefaultConfiguration;
import org.reactivestreams.Publisher;
import org.reactivestreams.Subscriber;
import org.reactivestreams.Subscription;
import io.r2dbc.postgresql.PostgresqlConnectionConfiguration;
import io.r2dbc.postgresql.PostgresqlConnectionFactory;
import io.r2dbc.spi.Batch;
import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryMetadata;
import io.r2dbc.spi.ConnectionMetadata;
import io.r2dbc.spi.IsolationLevel;
import io.r2dbc.spi.Result;
import io.r2dbc.spi.Statement;
import io.r2dbc.spi.TransactionDefinition;
import io.r2dbc.spi.ValidationDepth;
import reactor.core.publisher.Flux;
public class X {
public static void main(String[] args) {
PostgresqlConnectionConfiguration config =
PostgresqlConnectionConfiguration.builder()
.username("postgres")
.password("test")
.host("localhost")
.database("postgres")
.build();
ConnectionFactory f = new PostgresqlConnectionFactory(config);
Flux.from(new X(f).factory().create())
.flatMap(connection -> connection
.createStatement(
"select row_number() over w, table_schema, table_name "
+ "from information_schema.tables "
+ "window w as (order by table_schema, table_name) "
+ "qualify row_number() over w between :1 and :2 "
+ "order by 1, 2 ")
.bind(1, 6)
.bind(2, 10)
.execute()
)
.flatMap(it -> it.map((a, b) -> String.format("%1$5s:
%2$s.%3$s", a.get(0), a.get(1), a.get(2))))
.collectList()
.block()
.stream()
.forEach(System.out::println);
}
final Configuration configuration;
final ConnectionFactory delegate;
public X(ConnectionFactory delegate) {
this.configuration = new DefaultConfiguration()
.set(SQLDialect.POSTGRES);
this.configuration
.settings()
.withRenderNamedParamPrefix("$")
.withParamType(ParamType.NAMED);
this.delegate = delegate;
}
ConnectionFactory factory() {
return new ConnectionFactory() {
@Override
public ConnectionFactoryMetadata getMetadata() {
return () -> "jOOQ";
}
@Override
public Publisher<? extends Connection> create() {
Publisher<? extends Connection> connection =
delegate.create();
return subscriber -> {
connection.subscribe(new Subscriber<Connection>() {
@Override
public void onSubscribe(Subscription s) {
subscriber.onSubscribe(s);
}
@Override
public void onNext(Connection connection) {
subscriber.onNext(new Connection() {
@Override
public Publisher<Void> beginTransaction() {
return connection.beginTransaction();
}
@Override
public Publisher<Void>
beginTransaction(TransactionDefinition definition) {
return
connection.beginTransaction(definition);
}
@Override
public Publisher<Void> close() {
return connection.close();
}
@Override
public Publisher<Void> commitTransaction() {
return connection.commitTransaction();
}
@Override
public Publisher<Void>
createSavepoint(String name) {
return connection.createSavepoint(name);
}
@Override
public boolean isAutoCommit() {
return connection.isAutoCommit();
}
@Override
public ConnectionMetadata getMetadata() {
return connection.getMetadata();
}
@Override
public IsolationLevel
getTransactionIsolationLevel() {
return
connection.getTransactionIsolationLevel();
}
@Override
public Publisher<Void>
releaseSavepoint(String name) {
return
connection.releaseSavepoint(name);
}
@Override
public Publisher<Void>
rollbackTransaction() {
return connection.rollbackTransaction();
}
@Override
public Publisher<Void>
rollbackTransactionToSavepoint(String name) {
return
connection.rollbackTransactionToSavepoint(name);
}
@Override
public Publisher<Void>
setAutoCommit(boolean autoCommit) {
return
connection.setAutoCommit(autoCommit);
}
@Override
public Publisher<Void>
setTransactionIsolationLevel(IsolationLevel isolationLevel) {
return
connection.setTransactionIsolationLevel(isolationLevel);
}
@Override
public Publisher<Boolean>
validate(ValidationDepth depth) {
return connection.validate(depth);
}
@Override
public Batch createBatch() {
// TODO
throw new
UnsupportedOperationException();
}
@Override
public Statement createStatement(String
input) {
return new Statement() {
Map<Integer, Param<?>> params = new
TreeMap<>();
@Override
public Statement add() {
// TODO
throw new
UnsupportedOperationException();
}
@Override
public Statement bind(int index,
Object value) {
params.put(index, val(value));
return this;
}
@Override
public Statement bind(String name,
Object value) {
// TODO
throw new
UnsupportedOperationException();
}
@Override
public Statement bindNull(int
index, Class<?> type) {
params.put(index, val(null,
type));
return this;
}
@Override
public Statement bindNull(String
name, Class<?> type) {
// TODO
throw new
UnsupportedOperationException();
}
@Override
public Publisher<? extends Result>
execute() {
Query query =
configuration.dsl().parser().parseQuery(input, params.values().toArray());
Statement statement =
connection.createStatement(query.getSQL());
int i = 0;
for (Param<?> o :
query.getParams().values())
if (!o.isInline())
if (o.getValue() ==
null)
statement.bindNull(i++, o.getType());
else
statement.bind(i++,
o.getValue());
return statement.execute();
}
};
}
});
}
@Override
public void onError(Throwable t) {
subscriber.onError(t);
}
@Override
public void onComplete() {
subscriber.onComplete();
}
});
};
}
};
}
}
The output being:
6:information_schema.administrable_role_authorizations
7:information_schema.applicable_roles
8:information_schema.attributes
9:information_schema.character_sets
10:information_schema.check_constraint_routine_usage
On Mon, Mar 22, 2021 at 9:17 PM Lukas Eder <[email protected]> wrote:
> Here we go. As I said, I think we should offer this out of the box, and
> integration test it: https://github.com/jOOQ/jOOQ/issues/11700. It's a
> rather low hanging fruit, much lower than executing jOOQ queries on R2DBC.
> I'll look into this later this week.
>
> On Mon, Mar 22, 2021 at 9:08 PM Lukas Eder <[email protected]> wrote:
>
>> Search "(?<!:):\\w+"
>> Replace "?"
>> 😉
>>
>> But it's a great hint. We should supprt that OOTB. At the time, I was
>> trying to get the R2DBC folks not to diverge too far from JDBC and accept ?
>> as well, but I failed.
>>
>> Am Montag, 22. März 2021 schrieb Magnus Persson <
>> [email protected]>:
>>
>>> It almost gets me all the way :)
>>>
>>> The input sql has named parameters, as in "SELECT c1, c2 FROM foo WHERE
>>> bar = :baz OR quz = :qux". I would like to have that rendered as "SELECT
>>> c1, c2 FROM foo WHERE bar = $1 OR quz = $2". The intention is to use jooq
>>> as an sql parser and executing it with r2dbc-postgres.
>>>
>>> On Monday, March 22, 2021 at 1:26:23 PM UTC+1 [email protected] wrote:
>>>
>>>> Hi Magnus,
>>>>
>>>> You can set Settings.paramType = ParamType.NAMED (this would produce
>>>> :1, :2 if you don't actually provide any named parameters), and then
>>>> Settings.renderNamedParamPrefix = "$" to replace the default prefix ":" by
>>>> "$".
>>>>
>>>> I hope this helps,
>>>> Lukas
>>>>
>>>> On Sat, Mar 20, 2021 at 5:01 PM Magnus Persson <[email protected]>
>>>> wrote:
>>>>
>>>>> I'm using the jooq sql parser with the intention of outputting sql and
>>>>> bind values. Unfortunatly the sql coming out of jooq has either named
>>>>> ":param" or indexed by "?". How would I go about having jooq output
>>>>> sequenced index markers such as "$1", "$2" etc?
>>>>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google
>>>>> Groups "jOOQ User Group" group.
>>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>>> an email to [email protected].
>>>>> To view this discussion on the web visit
>>>>> https://groups.google.com/d/msgid/jooq-user/8405b074-cb9e-4be5-9f2d-f8bc74d2fde8n%40googlegroups.com
>>>>> <https://groups.google.com/d/msgid/jooq-user/8405b074-cb9e-4be5-9f2d-f8bc74d2fde8n%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>>> .
>>>>>
>>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "jOOQ User Group" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/jooq-user/fed9b2bc-6dfd-48fc-bb32-1408021f2b73n%40googlegroups.com
>>> <https://groups.google.com/d/msgid/jooq-user/fed9b2bc-6dfd-48fc-bb32-1408021f2b73n%40googlegroups.com?utm_medium=email&utm_source=footer>
>>> .
>>>
>>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAB4ELO6sDQ3VzT-jmuJ4_e_iEKGBeFpFHx%3DQ6hB8P9uMw8JPbA%40mail.gmail.com.
