How to create/access a $defaultGroupClause

[eschwegs]

Three are several default clause velocity variables ($defaultSelectClause, $defaultWhereClause, etc) to use in the operationBinding . However, the $defaultGroupClause is not among them. I'm looking to make use of what smartGWT would have generated for that in a <customSQL> tag but can't find a way to do so. It should be as simple as not including the " AS <dsFieldName>" parts of what gets generated for the $defaultSelectClause.

I have explored velocity from with in the <customSQL> tag and adding to the template context from a serverConstructor and the properties of the group by DSField objects but can't find a way to generically turn the dsRequest.groupBy field name array into the <tableName>.<fieldName> / <customSelectClause> text needed for the Group By SQL.

Can you point me to some documentation on this.

[Isomorphic]

If you look at the 13.0 Server Summaries docs, we have added $defaultGroupClause, and there's a lot more detail and more examples on how to do customization of grouped queries.

However, even in 12.1, there is the SQLDataSource.getSQLClause API, where one of the clause types is "group", which gets you basically the same thing. You can then take that and use dsRequest.addToTemplateContext() to make it available in the <customSQL> template.

[eschwegs]

Thanks, that got me over the first hump. I can now pass along the group clause generated by the getSQLClause API.

My next issue is now to modify the dsRequest's groupby and output such that the desired SELECT and GROUP BY clauses are generated for use in the operation binding.

In certain cases, I have the need to remove particular fields and add others. In a server side SQLDataSource.executeFetch() method I have attempted to
1) remove fields from outputs and groups with the dsRequest API. i.e. dsRequest.getOutputs().remove("fieldNameA") & dsRequest.getGroupBy.remove("fieldNameA") (or even the setOutputs()/setGroupBy() APIs)
2) add others in a similar manner.



When it comes to generating the $defaultSelectClause, or attempting to get the group clause via getSQLClause API, the generated clauses don't recognize the dsRequest modifications. What am I doing wrong?

Thanks in advance.

Eric

using: SmartClient Version: v12.1p_2020-07-31/PowerEdition Deployment (built 2020-07-31)

[Isomorphic]

Use the actual setters. dsRequest.getGroupBy() / getOutputs() etc do not return modifiable collections.

[eschwegs]

I went back to using the setter API and still see the issue. I can't seem to find a showcase to verify the "bug" in, but you should be able to insert the following code (replacing "PK_Test_Session" with an appropriate field name) into a SQLDataSource serverConstructor's execute_fetch method and observe what I'm seeing.

log ("debug initial "
+ "\n groupBy start=" + dsrequest.getGroupBy().stream().collect(Collectors.joining(", "))
+ "\n outputs start=" + dsrequest.getOutputs().stream().collect(Collectors.joining(", "))
+ "\n getSQLClause group start : " + SQLDataSource.getSQLClause(SQLClauseType.Group, dsrequest)
+ "\n getSQLClause select start : " + SQLDataSource.getSQLClause(SQLClauseType.Select, dsrequest)
);

List<String> groupBy = dsrequest.getGroupBy();
List<String> outputs = dsrequest.getOutputs();
groupBy.remove(groupBy.get(0));
outputs.remove(outputs.get(0));
groupBy.add("PK_Test_Session");
outputs.add("PK_Test_Session");
dsrequest.setGroupBy(groupBy);
dsrequest.setOutputs(outputs);

log ("debug after adding PK_Test_Session to outputs and groups "
+ "\n groupBy =" + dsrequest.getGroupBy().stream().collect(Collectors.joining(", "))
+ "\n outputs =" + dsrequest.getOutputs().stream().collect(Collectors.joining(", "))
+ "\n getSQLClause group : " + SQLDataSource.getSQLClause(SQLClauseType.Group, dsrequest)
+ "\n getSQLClause select : " + SQLDataSource.getSQLClause(SQLClauseType.Select, dsrequest)
);

i.e.

=== 2021-06-24 12:35:55,987 [6-68] DEBUG SummaryTreeDMI - debug initial
groupBy start=TestEDNm_Board_d_Board_Type
outputs start=TestEDNm_Board_d_Board_Type
getSQLClause group start : TEDVBoard_d_Board_Type_TBL.String
getSQLClause select start : TEDVBoard_d_Board_Type_TBL.String AS TestEDNm_Board_d_Board_Type
=== 2021-06-24 12:35:55,997 [6-68] DEBUG SummaryTreeDMI - debug after adding PK_Test_Session to outputs
groupBy =PK_Test_Session
outputs =PK_Test_Session
getSQLClause group : $defaultGroupClause
getSQLClause select : TEDVBoard_d_Board_Type_TBL.String AS TestEDNm_Board_d_Board_Type

[Isomorphic]

dsRequest.getOutputs() / getGroupBy() do not return modifiable collections. You modify them and then call setters, we detect that nothing has changed (since you already modified them!) and nothing is updated.

[eschwegs]

I'm obviously missing something that is probably starring me right in the face. I thought cloning the list would make it look to be a different object to the framework, but that didn't work. If this snippet isn't what you are eluding to, can you please correct it for me.

List<String> clonedOutputs = new ArrayList<String>(dsrequest.getOutputs());
clonedOutputs.add("PK_Test_Session");
dsrequest.setOutputs(clonedOutputs);

[Isomorphic]

Yes, that's the correct way to do it. Previously, you were directly modifying the returned collections.

Just apply that approach to your removals above.

[Isomorphic]

Wait a sec - are you saying that you actually applied this (to each of the cases - not just one) and it's not working? That's very surprising as this area has lots of automated test coverage, we use this feature extensively and so do many other customers. Please confirm - we're hoping this is just something like forgetting to save before re-testing!

Note: we're also hardening the APIs so that copies are returned, just to avoid anyone having a similar problem in the future.

[eschwegs]

Correct, it has been tested: That code is copy and pasted from my trials. I've quadruple checked it was running, including adding unique txt to log prints, clean compiles and fully exiting out of eclipse... I listed the version I'm on above. (We are currently held up moving to 13.o due to an others developers failing testing of it with our stuff. I have not tried another 12.x version yet).

[Isomorphic]

We tried your code in the latest patched 12.1 and could not reproduce the problem.

However we also don’t have a record of a bug being fixed in this area and again, no other reports, no failures for automated tests.

So how this can possibly be happening for you is still a mystery, but it definitely makes sense to try the latest patched build.

[eschwegs]

You are correct. It does work with the latest 12.1 code. I'll need to migrate & requalify under it.