Skip Navigation Links

Display database table contents from the command line

Posted by brandon on March 3rd, 2009. Other posts by brandon

Some time ago I needed to easily display the contents of my database tables in order to verify if data had been updated, inserted, and deleted. It was good practice in leveraging the CLI task framework provided by WebSphere sMash.

Create a Groovy script called “show.groovy” in the “app/tasks” folder of your application. Create the folder, if necessary. In order to handle task invocations such as “zero show …”, simply create an “onCliTask()” method.

def onCliTask() {
    // get args
    def args = zget('/event/args')
    logger.FINE { "Args: $args" }
}

Invoke the CLI task from the app root with “zero show mytable”. By default, no message will be displayed, despite having logged the arguments. If you do “zero -loglevel=FINE show mytable” instead, you will see more messages. One line will be the logging of the CLI arguments.

FINE: Args: ["mytable"]

When you log at the “INFO” level, no additional markup is made. Only the string that you log. Our basic design for this CLI task is to specify the table you are interested in, get an instance of “zero.data.groovy.Manager”, select all the data, and display it in tabular form.

Something like the following if the “mytable” table has columns named “initials”, “name”, and “id”:

+==========+===============+====+
| initials |          name | id |
+==========+===============+====+
|      bs  | Brandon Smith |  1 |
+----------+---------------+----+
|      ng  |    Nell Gawor |  2 |
+----------+---------------+----+
|      gs  |    Greg Smith |  3 |
+----------+---------------+----+
|      rs  |  Robert Sager |  4 |
+----------+---------------+----+

So, with a bit of Groovy code, here is the script that will print out tables:

import zero.data.groovy.Manager
import zero.data.tasks.BaseSqlTask
import java.text.NumberFormat
import java.util.Locale
import java.util.Formatter
 
def onCliTask() {
 
    // get args
    def args = zget('/event/args')
    logger.FINE { "Args: $args" }
 
    def dbKey
    def table
    def argSize = args.size()
 
    // extract dbKey and table name
    if (argSize == 2) {
        dbKey = args[0]
        table = args[1]
    } else if (argSize == 1) {
        dbKey = ([getCommand:{}, runTask:{}] as BaseSqlTask).findDbKey()
        table = args[0]
    } else {
        logger.SEVERE { "Error is CLI usage:\n\tzero show [dbKey] tablename" }
    }
 
    logger.INFO { "Using database $dbKey" }
 
    def data = Manager.create(dbKey)
 
    def hasDisplayRowHeader = false
    def tableData = data.queryList("SELECT * FROM $table".toString())
 
    prettyPrint(tableData)
 
}
 
def formatNum(num) {
    if (num instanceof Number)
        return NumberFormat.instance.format(num)
    else
        return num.toString()
}
 
// Get the maximum width of the given column index name
def getMaxWidth(table, index) {
    return table.collect { row -> formatNum(row[index]).length() }.max()
}
 
/**
 * Prints out a table of data, padded for alignment
 * Each row must have the same number of columns
 *
 * @param table The table to print. A list of maps.
**/
def prettyPrint(table) {
 
    if (table) { // only print if there is data
 
        def out = new StringBuilder()
        def formatter = new Formatter(out, Locale.US)
        def head = new StringBuilder()
        def line = new StringBuilder()
 
        // get the column paddings and header
        def first = true
        def colPaddings = table[0].keySet().collect { col ->
            def width = getMaxWidth(table, col)
            width = (width > col.length()) ? width : col.length()
            formatter.format("${!first ? '' : '|'} %${width}s |", col)
            if (first) {
                head << '+'
            }
            (1..width+2).each { i -> head << '=' }
            head << '+'
            first = false
            width
        }
 
        head << '\n'
        line << head.toString().replace('=', '-')
        out.insert(0, head)
        out << '\n'
        out.append(head)
 
        table.each { row ->
            row.keySet().eachWithIndex { key, index ->
                formatter.format("${index ? '' : '|'} %${colPaddings[index]}s |", formatNum(row[key]))
            }
            out << '\n'
            out << line
        }
 
        logger.INFO { out.toString() }
 
    } else {
        logger.INFO { "No data found" }
    }
}
Tagged as , , , , | Filed in Cookbook
Comments Off
2 votes, average: 5.00 out of 52 votes, average: 5.00 out of 52 votes, average: 5.00 out of 52 votes, average: 5.00 out of 52 votes, average: 5.00 out of 5 (2 votes, average: 5.00 out of 5, rated)
Loading ... Loading ...

Comments are closed.