Hello, Here is my patch against 1.4.196 sources.
I haven't submitted it officially because I think the implementation is not clean: I build an auxiliary request by taking main request SQL text instead of using internal structures of the parsed request. The reason is that it seems not obvious by looking at H2 code how to do that. Anyway, this works in my case. Feel free to modify and improve it! Boris. On Mon, Jan 29, 2018 at 6:17 AM, Dhaval Shewale <[email protected]> wrote: > 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 a topic in the > Google Groups "H2 Database" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/h2-database/I6QNmf_0Ic4/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- 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.
rank.patch
Description: Binary data
