Pretty-Printing A ColdFusion Query Object In Lucee CFML 5.2.9.31
Last week, in my article about using INNER JOIN in a MySQL UPDATE statement, I shared the various states of the database using the output provided by the MySQL CLI (Command-Line Interface) tool. The MySQL CLI pretty-prints the resultant queries using a fixed-width ASCII table format, making it really easy to copy-paste the data into demos. As a fun code kata in these trying COVID19 times, I thought I would try to recreate that Query serialization functionality in Lucee CFML 5.2.9.31.
Before we look at any ColdFusion code, let's first look at what the MySQL CLI is doing. If I bash into my MySQL Docker container and SELECT all the records from the friend table, here's the output that I get:
mysql> SELECT * FROM friend ;
+----+-------+-----+----------------------------------------------+
| id | name | age | catchPhrase |
+----+-------+-----+----------------------------------------------+
| 1 | Tina | 39 | If it ain't broke, try harder. |
| 2 | Danny | 28 | |
| 3 | Ellen | 50 | Whatchamhoozy |
| 4 | Ralph | 8 | Cookies! |
| 5 | Sandi | 33 | Don't forget to stop and smell the chickens! |
+----+-------+-----+----------------------------------------------+
5 rows in set (0.00 sec)
As you can see, the MySQL CLI outputs the query object as an ASCII table in which each column value left justified based on the row with the widest value. This is the output behavior that I want to recreate in ColdFusion.
NOTE: As I'm writing this post, I am only just now seeing that the numeric column values are being right-justified within the column output. Since I didn't notice that before, my algorithm left-justifies everything.
To make this code kata a little bit more interesting, I wanted to make my serialization algorithm more dynamic, especially since it's not tied to any single query; but rather, can be invoked across any number of queries of different sizes.
In addition to being able to pass-in the query object, I'll also allow for several optional arguments:
records- The query object being printed.columnNames- OPTIONAL, the columns to print (in the order provided).newline- OPTIONAL, the string to use as the line-break.maxValueLength- OPTIONAL, the length at which to truncate column values.
Before we look at my solution, let's look at how it can be used, exercising a few of these optional arguments. Note that my solution has been encapsulated within the ColdFusion component, QueryPrinter.cfc:
<cfscript>
printer = new QueryPrinter();
friends = queryExecute( "SELECT * FROM friend ;" );
// The default printer call just outputs the entirety of the query data.
echo( "<pre>" & printer.toString( friends ) & "</pre>" );
// However, we can also output a subset of columns.
echo( "<pre>" & printer.toString( friends, [ "id", "name" ] ) & "</pre>" );
// We can even pass-in an optional argument that will truncate the column values. By
// default, the printer will return every column value (event LONG_VARCHAR values).
// Let's look at how the output changes based on the different truncation lengths.
for ( maxValueLength in [ 25, 10, 5, 3, 1 ] ) {
stringifiedFriends = printer.toString(
records = friends,
maxValueLength = maxValueLength
);
echo( "<pre>" & stringifiedFriends & "</pre>" );
}
</cfscript>
Here, we've started off using the simplest invocation: passing in just the query. But, we've also tried a subset of columns as well as various column truncation lengths. And, when we run this ColdFusion code, we get the following browser output:
As you can see, the ColdFusion Query object has been serialized using the pretty-printing, fixed-width ASCII format.
Ok, now that we see what we're trying to do, let's look at the ColdFusion code that I came up with. Ultimately, I put my code into a ColdFusion component because I wanted to have a bunch of helper methods to break-up the algorithm; and, I didn't want all of those helper methods hanging out in the page-scope.
My QueryPrinter.cfc component only exposes a single public method, .toString(). The rest of the methods are private and are here only to make the logic easier to reason about:
component
output = false
hint = "I provide methods for serializing a Query object for output using a MySQL-CLI inspired fixed-width format."
{
/**
* I serialize and return the given query using a MySQL-CLI inspired fixed-width
* format.
*
* @records I am the query being serialized.
* @columnNames I am the OPTIONAL list of columnNames to include in the serialization.
* @newline I am the OPTIONAL string of characters used to separate lines of output.
* @maxValueLength I am the OPTIONAL length at which to truncate column values.
*/
public string function toString(
required query records,
array columnNames = records.columnArray(),
string newline = chr( 10 ),
numeric maxValueLength = 0
) {
var columnWidths = getMaxWidths( columnNames, maxValueLength, records );
var hrule = getHrule( columnWidths );
// Each header / record within the query will be serialized into a temporary
// buffer, which will then be collapsed into a single string-value at the end.
var buffer = [
hrule,
serializeHeader( columnNames, columnWidths, maxValueLength ),
hrule
];
for ( var row in records ) {
buffer.append( serializeRow( columnNames, columnWidths, maxValueLength, row ) );
}
buffer.append( hrule );
return( buffer.toList( newline ) );
}
// ---
// PRIVATE METHODS.
// ---
/**
* I collapse the given array of values using the given joiners and wrappers.
*
* @values I am the array being collapsed.
* @prefix I am the prefix for the serialized values.
* @infix I am the infix for the serialized values.
* @suffix I am the suffix for the serialized values.
*/
private string function collapseValues(
required array values,
required string prefix,
required string infix,
required string suffix
) {
return( prefix & values.toList( infix ) & suffix );
}
/**
* I generate the horizontal-rule for given column widths.
*
* @columnWidths I am the width of each column being spanned by the hrule.
*/
private string function getHrule( required array columnWidths ) {
var columnValues = columnWidths.map(
( width ) => {
return( "-".repeatString( width ) );
}
);
return( collapseValues( columnValues, "+-", "-+-", "-+" ) );
}
/**
* I return the max width of the values in each of the given columns.
*
* @columnNames I am the list of columns to visit.
* @maxValueLength I am the length at which column values will be truncated.
* @records I am the query being inspected.
*/
private array function getMaxWidths(
required array columnNames,
required numeric maxValueLength,
required query records
) {
var maxWidths = columnNames.map(
( columnName ) => {
var maxWidth = columnName.len();
// Iterate over each row in the given column, looking for the longest
// value - we'll worry about truncation afterwards.
for ( var value in records.columnData( columnName ) ) {
maxWidth = max( maxWidth, toString( value ).len() );
}
// Truncation should only be applied if the maxValueLength is non-ZERO.
if ( maxValueLength ) {
return( min( maxWidth, maxValueLength ) );
} else {
return( maxWidth );
}
}
);
return( maxWidths );
}
/**
* I serialize and return the given column headers.
*
* @columnNames I am the list of columns to visit.
* @columnWidths I am the width at which to print each corresponding column.
* @maxValueLength I am the length at which column values will be truncated.
*/
private string function serializeHeader(
required array columnNames,
required array columnWidths,
required numeric maxValueLength
) {
var values = columnNames.map(
( columnName, i ) => {
var value = columnName.ljustify( columnWidths[ i ] );
return( truncate( value, maxValueLength ) );
}
);
return( collapseValues( values, "| ", " | ", " |" ) );
}
/**
* I serialize and return the given data-row.
*
* @columnNames I am the list of columns to visit.
* @columnWidths I am the width at which to print each corresponding column.
* @maxValueLength I am the length at which column values will be truncated.
* @data I am the row data being serialized.
*/
private string function serializeRow(
required array columnNames,
required array columnWidths,
required numeric maxValueLength,
required struct data
) {
var values = columnNames.map(
( columnName, i ) => {
var value = toString( data[ columnName ] ).ljustify( columnWidths[ i ] );
return( truncate( value, maxValueLength ) );
}
);
return( collapseValues( values, "| ", " | ", " |" ) );
}
/**
* I truncate the given value at the given length.
*
* @value I am the value being truncated.
* @maxValueLength I am the length at which the value should be truncated.
*/
private string function truncate(
required string value,
required numeric maxValueLength
) {
// Truncation should only be applied if the maxValueLength is non-ZERO. As such,
// if there is not length supplied, return the given value as-is.
if ( ! maxValueLength ) {
return( value );
}
var valueLength = value.len();
if ( valueLength <= maxValueLength ) {
return( value );
}
var ellipsis = "...";
var ellipsisLength = ellipsis.len();
// When truncating, we want to append the ellipsis; however, we can only do this
// if there is enough VALUE to afford both the truncation and the ellipsis. If
// not, we just have to perform a hard-truncation of the value.
if (
( valueLength <= ellipsisLength ) ||
( maxValueLength <= ellipsisLength )
) {
return( value.left( maxValueLength ) );
}
return( value.left( maxValueLength - ellipsisLength ) & ellipsis );
}
}
Anyway, this was just a fun little mental exercise to help break up the week. When I look at this, however, I must say that I am loving the look-and-feel of ColdFusion / CFML / Lucee these days. It feels like a beautiful compromise between the delicious flexibility of JavaScript and the comforting type-safety of TypeScript. What a wonderful time to be writing ColdFusion!
Want to use code from this post? Check out the license.
Reader Comments
Hey Ben..
Thanks for this function.. I used it to output some unit test results in a bash test runner.
https://github.com/chapmandu/serverless-cfml
Regards,
Adam
@Adam,
So cool!! I've heard of Fuseless, but haven't tried it out yet. I'm not really that experienced with Lambda to begin with. Will definitely check out your project. Glad to be able to have helped in some small way!