How to dynamically generate grid columns?

[vitor.eduardods]

Dear colleagues,

I am working in project that has a view with a grid that its design must be drived by data, i.e., its columns matches a database query where each row should be a columns. Take a look at the example below:

Assuming that we have a database table named country (code; name) and another, ranges (id; start; end; country_code):

COUNTRY
1 | Australia
2 | Brazil
3 | England
4 | Germany
5 | Ireland
6 | USA

RANGES
1 | 001 | 100 | 6
2 | 101 | 200 | 3
3 | 201 | 300 | 1
4 | 301 | 400 | 4

So, the grid should have this appearance:

START	END	USA	BRAZIL	ENGLAND	IRELAND	AUSTRALIA	GERMANY
001	100	x
101	200			x
201	300					x
301	400						x

Please, ignore the countries ordering.

Is it possible?

Best regards

Vitor Eduardo

[Isomorphic]

You can use DataSource.fetchData() to retrieve the data from the server that represents columns, then make ListGridField objects from that, then fetch the data that is to appear in the grid.

For a more advanced version of this that will allow other server-side features to be used, consider creating a DynamicDSGenerator - see the QuickStart Guide for this - discussion starts in the Server Framework chapter.

[vitor.eduardods]

Thanks for reply. I will study for a while and then I back with my doubts, ok?

[Blama]

Hi vitor.eduardods,

you are joining and tables and pivoting data. If you are using Oracle you can use it's PIVOT function (bit difficult to understand. I don't know about availability other RDBMS products).
PIVOT in Oracle will much likely the fastest way to solve if speed is a concern (HIGH data volume), but it is a bit annoying when the column list changes (difficult to put in a DB-View).

A solution would be to have the SELECT statement as customSql in your .ds.xml operationBinding, but you'd still have to solve the changing column-list issue, which would be via DynamicDSGenerator then, as already suggested.
Make sure to have a different Datasource-ID for every different columnList (or always a different Datasource-ID).
See this thread for where I did something similar (but with constant column list).

I'm not too sure about this, but if you are only interested in data display, SmartGWT's Cube Grid could also be a possibility.

Best regards
Blama

PS:
@Isomorpic: The Cube Analytics sample seems to be broken (no data and "java.net.ConnectException: Connection refused").

[vitor.eduardods]

You can use DataSource.fetchData() to retrieve the data from the server that represents columns, then make ListGridField objects from that, then fetch the data that is to appear in the grid.

When you say "...then fetch the data that is to appear in the grid." are you referring to use a DMI.call, or a second DataSource? Is not so clear to me this point, since I will still be forced to write a descriptor for my DS.

An important information that I forgot to share, my legacy backend is composed of EJBs using façade pattern.

[Isomorphic]

You can use either a normal DataSource operation, or use DMI to invoke a non-DataSource RPC. In either case, the information that is required is the minimum possible - you are just specifying that a particular server method is allowed to be called.

If you have any further confusion on how to set this up, please be sure you've read the Server Framework chapter of the QuickStart Guide.

[vitor.eduardods]

Hi, I have tried to implement the DynamicDSGenerator, following closely the showcase. However, when my DMI method getNumberRanges, that is responsible for generate dynamic ds is invoked, the data source name condition fails. See the code snippets below:

When a load my dynamic ds in the presenter:

    private Object[] loadNumberRangesDataSource() {
        
        DataSource.load(LUNummernkreiseContent.DYNAMICDS_ID, dataSourceCallbackFunction(), true);
        
        return new Object[] { AppController.getRequestToken() };
        
    }

My DMI method responsible by generate the dynamic ds:

    public RPCResponse getNumberRanges(HttpSession httpSession, String requestToken) {
        
        final RPCResponse response = new RPCResponse();
        
        final Map<String, String> data;
        
        try {
            
            final ServiceLocator locator = ServiceLocator.getInstance();
            final SCAFacade scaFacade = locator.getService(SCAFacade.class);
            
            final NumberRangesTransferData[] numberRanges = scaFacade.getNumberRanges();
            
            if ( numberRanges != null ) {
                
                data = new HashMap<String, String>();
                
                DataSource.addDynamicDSGenerator(new DynamicDSGenerator() {
                    
                    @Override
                    public DataSource getDataSource(String arg0, DSRequest arg1) {

                        StringWriter writer = new StringWriter();

                        try {
                                  
                            if ( LUNummernkreiseContent.DYNAMICDS_ID.equals( arg0 ) ) {
                                
                                writer.write("<DataSource ID=\""+ LUNummernkreiseContent.DYNAMICDS_ID +"\" >\n");
                                writer.write("<fields>\n");
                                
                                final Map<String, Map<String, String>> statictFields = staticFieldsForDynamicNumberRangesDS();
                                
                                XML.recordToXML("field", statictFields.get( LUNummernkreiseContent.ID_FIELD ), writer);
                                XML.recordToXML("field", statictFields.get( LUNummernkreiseContent.START_FIELD ), writer);
                                XML.recordToXML("field", statictFields.get( LUNummernkreiseContent.END_FIELD ), writer);
                                XML.recordToXML("field", statictFields.get( LUNummernkreiseContent.DELIVERYTYPE_FIELD ), writer);
                                XML.recordToXML("field", statictFields.get( LUNummernkreiseContent.COUNTRYNAME_FIELD ), writer);
                                XML.recordToXML("field", statictFields.get( LUNummernkreiseContent.COUNTRYCODE_FIELD ), writer);
                                
                                for (NumberRangesTransferData numberRange : numberRanges) {
                                    
                                    data.put(LUNummernkreiseContent.ID_FIELD, String.valueOf( numberRange.getId() ) );
                                    data.put(LUNummernkreiseContent.START_FIELD, String.valueOf( numberRange.getStart() ) );
                                    data.put(LUNummernkreiseContent.END_FIELD, String.valueOf( numberRange.getEnd() ) );
                                    data.put(LUNummernkreiseContent.DELIVERYTYPE_FIELD, numberRange.getDeliveryType() );
                                    data.put(LUNummernkreiseContent.COUNTRYNAME_FIELD, numberRange.getCountryName() );
                                    data.put(LUNummernkreiseContent.COUNTRYCODE_FIELD, numberRange.getCountryCode() );
                                    
                                    String[] lines = numberRange.getCnk().split( "~&!&~" );
                                    
                                    for (String line : lines) {
                                        
                                        String[] attributes = line.split( "~@!@~" );
                                        
                                        XML.recordToXML("field", dynamicFieldsForDynamicNumberRangesDS( attributes[0], attributes[1] ), writer);
                                        writer.write("\n");
                                        
                                        data.put( LUNummernkreiseContent.CUSTOMER_FIELD + attributes[0], attributes[3] );
                                            
                                    }
                                    
                                }
                                
                                writer.write("</fields>\n");  
                                writer.write("</DataSource>");
                                
                                return DataSource.fromXML( writer.toString() ); 
                            }
                            
                            
                        } catch (Exception e) {

                            logger.severe(MessageIDs.NUMBERRANGES_DYNAMIC_DATASOURCE_GENERATION, e.getMessage(), new Object());
                            response.setData( ResourceUtils.getMessage(MessageIDs.NUMBERRANGES_DYNAMIC_DATASOURCE_GENERATION, null) );
                            response.setStatus( RPCResponse.STATUS_FAILURE );
                            
                        }
                        
                        return null;
                        
                    }
                    
                });
                
                response.setData( data );
                response.setStatus( RPCResponse.STATUS_SUCCESS );
                
            }
            
            
        } catch (Exception e) {
            
            logger.severe(MessageIDs.NUMBERRANGES_DYNAMIC_DATASOURCE_LOADING, e.getMessage(), new Object());
            response.setData( ResourceUtils.getMessage(MessageIDs.NUMBERRANGES_DYNAMIC_DATASOURCE_LOADING, null) );
            response.setStatus( RPCResponse.STATUS_FAILURE );
            
        }
        
        
        return response;
        
    }

Take a look above and see that, I am trying to implement the same aproach sugested in the showcase, but the condition

( LUNummernkreiseContent.DYNAMICDS_ID.equals( arg0 )

never is true. Why not since, I am passing the ID as arg when loading the ds?

Thanks in advance!

Vitor Eduardo

[Isomorphic]

See docs - when you add a DynamicDSGenerator, you can register it for just a specific ID, for IDs matching a pattern, or from any and all DataSource lookups. You probably want to register just for the specific ID of the DataSource you are making dynamic. If you register for all IDs, you will be involved in every single DataSource lookup, and if your code crashes, then you will break other DataSources and framework internals.

[vitor.eduardods]

Hi, My ds loader is operating for a unique ds. Please, look that and see that I am using only a constant for the dynamic ds id instead of a string text. When I call the DMI method, I do not know why, but the id is not accessable, words like "transactional", "element", etc, etc are coming in getDataSource arg0.

[Isomorphic]

Again, see docs - there are 3 signatures for addDynamicDSGenerator, you are using the one that registers for all DataSource loading. That's why you see request for other DataSources that aren't yours - this is exactly what's expected, and exactly what we just described.

[vitor.eduardods]

Ok, there are three methods signatures. But the point is that "DynamicNumberRangesDS" never match. I made a test using "Dynamic" as prefix, and then nothing inside

 
 DataSource.addDynamicDSGenerator(new DynamicDSGenerator()

has been executed.

[Isomorphic]

Well, that's a different problem. To begin troubleshooting that, start by looking at the server logs for the request to load the DataSource, and if you don't seen any of those at all, then look at the browser's built-in tools to see what happened to the request. It may be resulting in a 404 due to simply contacting the wrong URL, if you've set up your project in an unusual way and not set the dataSource.loaderURL properly to match.

[vitor.eduardods]

Hi Isomorphic, I suppose that I fixed my first problem. Thank you so much for you engage with me chasing the solution. Nonetheless, I have a doubt: assuming that my DS is a dynamic DS may I still have a .java file with DMI logic expressing business rules for CRUD operations as a conventional DS?

[Blama]

Hi vitor.eduardods,

yes you can do that. The "dynamic" part is only the generation of the XML that is used for all processing logic (client and server).

Best regards
Blama

[vitor.eduardods]

It is working. But , my dynamic fields that are booleans, all of them are checked (represented as checkbox) as whether they were all true, but they are not. Look at the data below and a screenshot (by the way, columns names blurred since are confidential). Any idea?

[
    {countryName=Schweiz, start=1, customer1=true, countryCode=571   , customer2=false, customer3=false, customer4=false, end=100, id=5, deliveryType=FZG},
    {countryName= , start=101, customer1=false, countryCode=533   , customer2=true, customer3=false, customer4=false, end=200, id=6, deliveryType=FZG},
    {countryName=Schweiz, start=201, customer1=false, countryCode=571   , customer2=false, customer3=true, customer4=false, end=300, id=7, deliveryType=BEMI},
    {countryName=, start=301, customer1=false, countryCode=516   , customer2=false, customer3=false, customer4=true, end=400, id=8, deliveryType=EX33},
    {countryName=, start=401, customer1=true, countryCode=524   , customer2=false, customer3=false, customer4=false, end=500, id=9, deliveryType=FZG},
    {countryName=, start=501, customer1=true, countryCode=524   , customer2=false, customer3=false, customer4=false, end=600, id=10, deliveryType=FZG}
]