Hi Boris,
If you have implemented the rank function in java for H2, can you pls share
the complete code.
Dhaval
On Thursday, July 21, 2016 at 12:59:21 AM UTC+5:30, Boris Granveaud wrote:
>
> hum it sounds not easy to implement, at least for me because it requires
> significant changes in Select / Parser.
>
> in the meantime, I have finished a first working implementation which
> builds a new query by picking elements from the main request. The main
> drawback is that the query is built in text and then reparsed. This looks
> like this:
>
> public class Rank extends Expression {
> ...
>
> public Rank(Select mainSelect, ArrayList<Expression> partitions,
> ArrayList<SelectOrderBy> rankOrderList, boolean dense) {
> ...
> }
>
> private Prepared buildRankSelect(Session session) {
> // build rank map select
> StatementBuilder sb = new StatementBuilder();
>
> sb.append("SELECT ");
>
> sb.resetCount();
> for (Expression e : partitions) {
> sb.appendExceptFirst(",");
> sb.append(e.getSQL());
> }
> for (SelectOrderBy o : rankOrderList) {
> sb.appendExceptFirst(",");
> sb.append(o.expression.getSQL());
> }
>
> sb.append(" FROM ");
>
> TableFilter filter = mainSelect.getTopTableFilter();
> if (filter != null) {
> sb.resetCount();
> int i = 0;
> do {
> sb.appendExceptFirst(" ");
> sb.append(filter.getPlanSQL(i++ > 0));
> filter = filter.getJoin();
> } while (filter != null);
> } else {
> sb.resetCount();
> int i = 0;
> for (TableFilter f : mainSelect.getTopFilters()) {
> do {
> sb.appendExceptFirst(" ");
> sb.append(f.getPlanSQL(i++ > 0));
> f = f.getJoin();
> } while (f != null);
> }
> }
>
> if (mainSelect.getCondition() != null) {
> sb.append(" WHERE ").append(
> StringUtils.unEnclose(mainSelect.getCondition().getSQL()));
> }
>
> sb.append(" ORDER BY ");
>
> sb.resetCount();
> for (Expression e : partitions) {
> sb.appendExceptFirst(",");
> sb.append(StringUtils.unEnclose(e.getSQL()));
> }
> for (SelectOrderBy o : rankOrderList) {
> sb.appendExceptFirst(",");
> sb.append(StringUtils.unEnclose(o.getSQL()));
> }
>
> System.out.println("SQL=" + sb.toString());
>
> // execute
> Parser parser = new Parser(session);
> return parser.prepare(sb.toString());
> }
>
>
>
>
> on the good side, it works without changes to H2 classes. I've tested it
> successfully in my project where I execute Oracle requests in H2 based unit
> tests.
>
>
> what do you think? is it too "hackish"? or am I missing something?
>
>
> I can provide a complete patch if you want.
>
>
> BTW, I have difficulties when I execute H2 tests with 'build test': some
> tests generate error messages and when I reach testIndex, it just runs
> forever.
>
>
> Boris.
>
>
>
> Le dimanche 17 juillet 2016 21:25:25 UTC+2, Noel Grandin a écrit :
>>
>> I think you're going to need to run your new query before the main select
>> and then re-init the main select
>>
>> Running it on-demand like that is going to lead to it trying to run
>> somewhere inside the main select and confusing things.
>>
>> Which means that the top-level select code will probably have to
>> somewhere do an explicit walk over the tree to run RANK-type queries, but
>> that is fine, I always assumed that RANK-type stuff would need special
>> handling at the top level.
>>
>>
>>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
