Generate Wire Protocol for Google Visualizations in Grails

Print This Post Print This Post

Let’s say, hypothetically, you have a bunch of data, say, from a database resultset or a csv file and you’d like to display it in the browser using Google Visualizations. To properly display it you need to output the data in a format compatible with Google’s Wire Protocol, preferably in JSON.

The Dataset

Let’s start with a data set. I’ll create one manually just to show the structure expected here. Note that the ‘dataset’ variable is a list and each row is a map

1
2
3
4
5
def dataset = []
dataset << [col1:1, col2:4, col3:1.5]
dataset << [col1:2, col2:3, col3:2.5]
dataset << [col1:3, col2:2, col3:1.5]
dataset << [col1:4, col2:1, col3:2.5]

Converting to Wire Protocol

Google has a predefined format that the data needs to appear in so their javascript can properly load it into the Table or View objects. Also, it’s important to preserve the data types. The following function takes in the web request parameters and the dataset. The first step (lines xx-xx) is to pass through the columns and determine their basic types. These types will be added to the JSON output. The next step (lines xx-xx) is to convert the dataset to one that will lend itself to Wire’s JSON output format. This probably isn’t the most memory-conscience method, so I don’t recommend processing really large data sets. You’ll see that it assigns the value to the ‘v’ name (line xx), but Wire allows for an additional ‘f’ name, which is the formatted version of the same value. Feel free to adapt this code to support that. The third step (lines xx-xx) involves parsing the web request parameters for those expected by the Wire protocol (i.e. version, reqId, sig, etc). The final part of the function puts all the parts together in a map.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
    def dataListToJson(params, dataset)
    {
        def cols = []
        if (dataset.size() > 0) {
            dataset[0].each { col, val ->
                def type = "number"
                if (val instanceof java.util.Date || val instanceof java.sql.Date) {
                    type = "date"
                } else if (val instanceof java.lang.String) {
                    type = "string"
                } else if (val instanceof java.lang.Integer || val instanceof java.lang.Double) {
                    type = "number"
                }
                cols << [id:col, label:col, type:type]
            }
 
        }
 
        def jsonDataset = []
        dataset.each { row ->
            def colset = []
            row.each { col, val ->
                colset << [v:val]
            }
            def rowset = [c:colset]
            jsonDataset << rowset
        }
 
 
        def extras = [version:0.6, status:"ok"]
        if (params.tqx != null) {
            def parts = params.tqx.split(";")
            parts.each { part ->
                def props = part.split(":")
                extras[props[0]] = props[1]
            }
        }
 
 
        def data = [
                table : [
                cols : cols,
                rows : jsonDataset
                ]        
                ]
        data += extras
        return data
    }

The Controller

With the above convert function, the controller could be as simple as the following code. The steps follow the retrieval of the data, the call to the conversion function above, and then rendering it.

1
2
3
4
5
6
7
8
9
10
import grails.converters.JSON
 
class GoogleVisualizationsController {
 
    def query = {
        def dataset = getSomeDataset()
        def toJson = dataListToJson(params, dataset)
        render(text:"google.visualization.Query.setResponse(${toJson as JSON});", contentType:"text/plain")
    }
}

SQL Query Execution

The example dataset above isn’t useful. At all. You may want to query a database to generate the results, either on-demand, or beforehand in a caching strategy. Either way, you’ll want to preserve the data types to the best of your ability, especially if the function isn’t hardcoded to the query being executed. The following function will take a SQL statement and an open JDBC connection. It will then iterate over the rows & columns and generate the dataset with preserved data types (the code below represents most, but not all types defined in javax.sql). It is relatively simplistic and not optimized for performance, memory, etc.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
def executeSqlQuery( sql, connection)
    {
        def dataset = []
        connection.eachRow(sql) { row ->
            def rowset = [:]
 
            def rsmd = row.getMetaData()
            for (int i = 1; i <= rsmd.getColumnCount();i++) {
                def colName = rsmd.getColumnName(i)
 
                switch (rsmd.getColumnType(i)) {
                    case java.sql.Types.DATE:
                    case java.sql.Types.TIMESTAMP:
                    case java.sql.Types.TIME:
                        rowset["${colName}"] = row.getDate(i)
                        break
                    case java.sql.Types.DECIMAL:
                    case java.sql.Types.DOUBLE:
                        rowset["${colName}"] = row.getDouble(i)
                        break
                    case java.sql.Types.BIGINT:
                    case java.sql.Types.INTEGER:
                        rowset["${colName}"] = row.getInt(i)
                        break
                    case java.sql.Types.CHAR:
                    case java.sql.Types.VARCHAR:
                    default:
                        rowset["${colName}"] = row.getString(i)
                }
            }
 
            dataset.add(rowset)
        }
 
        return dataset
    }

More Information
Google Wire Protocol: http://code.google.com/apis/visualization/documentation/dev/implementing_data_source.html
Google Visualizations API: http://code.google.com/apis/visualization/

Comments are closed.