Blackboxing SQL in CFScript - Part 1

Working in a small development group, one of my goals is code reduction and *blackbox * major aspects of our system. Doing so reduces the amount of code to maintain and increases the flexibility of our systems without have to continuously rollout deployments. My first experiment was a form generator which reads a JSON "definition" file to describe a form and its data/ui components. If a form needs modified in some manner, the JSON file gets updated. If we need a new form, we just create the JSON for it.

This paradigm has extended from forms to data grids, workflow, and documents. Something I've been thinking about for a while is: Can I do this for queries, and if so, should I?

A development pattern that I follow when returning data to JS from ColdFusion is to run the query and then roll into JSON. CF does allow for a query to be returned directly as JSON but often times the data needs to be massaged in some way. To read more about the pattern that I use can be found here. In short, the pattern loops over the query results and builds an array of structures in accordance to what is needed.

The process works great. But even though I am writing different queries and returning back to Angular different sets of data, the algorithmic pattern remains the same: Query -> Results -> Array -> Struct -> Return. Can generic enough code be written that would make this process easier for the developer?

The first step in this endeavor is to come up with a system design that is easy to implement and maintain. My initial thoughts are that the system will break down into three aspects:

• A file that contains only the SQL. This file will sit in an area that only the Coldfusion server can see which protects it from the web. The file will contain only the SQL. Parameters in the SQL are defined as :paramName - I am not sure how I will handle those as of yet, but for query reusability, parameters need to be considered.

• A file to contain the definition of how the query is to map to the returned JSON. It would list all the desired fields from the query and corelated property in the JSON array of objects. For example, the database field might be FIRST_NAME, but the name we pass back to the UI might be firstName.

• And finally the heart of the process: the black-box in CFScript to handle all the processing, formatting and returning of the data.

SQL File

The SQL file, where the query text will reside, is just a text file located on the server in an area that only the ColdFusion and its administrators can see. Queries, in CF, can be processed as text via myQuery.execute( sql = 'select * from dual'). Parameters can be signified in the query string by using the ":" (colon) notation. (More on that implementation of this process in part 2.)

Having the query in a separate text file allows for the query to be modified without modifying code. A front end query editor could be developed to allow technically oriented power users and business analysts to modify the query as needed. It also allows the easy swapping out of queries from development to production without having to move components or templates around.

And while this, at some level, feels like adding a layer to the platform, the reality is that the query needs to be written and exist somewhere. The only "extra" is that it exists in a separate file from the code.

A super simple query file might look like contacts.sql:

select first_name,
       last_name,
       department, 
       primary_phone,
       secondary_phone
from   contacts

Definition File

The definition is a JSON file that is used to tell the black-box what to do with a query. The definition file and the query file are a many to one. You can have multiple definition files call the same query, but a definition file can only call one query. This could be denormalized such that a definition could be used for different queries, but for our use case, this would be extra overhead to track.

As such the definition file contains the query file name. For our purposes, the queries will be located in one area, but it wouldn't be a stretch to allow full paths to queries be specified. The definition also contains the data source and an array of fields and how they will map to the corresponding JSON output.

Referring back to the contacts.sql above, a definition file might look like contacts.json:

{
    "query" : "contacts.sql",
    "dataSource" : "myDatasource",
    "fields" : [
        { "queryField" : "spriden_first_name" , "returnProperty" : "firstName" },
        { "queryField" : "spriden_last_name" , "returnProperty" : "lastName"},
        { "queryField" : "banner_id" , "returnProperty" : "erpId"},
        { "queryField" : "home_dept" , "returnProperty" : "homeDepartment"}
    ]
}

The definition file is describing to the system: find the contacts.sql and using myDatasource run the query while mapping these queryFields to these JSON returnPropertys.

But this JSON in of itself doesn't do any actual work. It's up to the black-box to do the heavy lifting.

Black-box CFScript

The black-box exists in a component (.cfc) file which I called queryBox. Along with our FormGen, workflow engine, data definitions, and objects, queryBox will exist as a separate aspect of the overarching system.

component {
    variables.queryPath = "[your path to queries";
    variables.queryMapPath = "[your path to query definitions / maps]";

    remote array function runQuery(_queryMap) returnformat = "json" {
        // setup array to be returned
        var _resultArray = arrayNew(1);

        //  open map file
        cffile(file = variables.queryMapPath & _queryMap & ".json" , action = "read" , variable = "_file");
        var _map = deserializeJSON(_file);

        //  from the map file, get the query
        cffile(file = variables.queryPath & _map.query , action = "read" , variable = "_file");
        var _sql = _file;

        // create query
        var _query = new Query();
            _query.setDatasource(_map.dataSource);

        var _result = _query.execute( sql = _sql );

        //loop through the results and build each row as a structure with using mapping
        for (_r in _result.getResult()) {
            var _struct = structNew();
            // loop through each field in the map and set the returnProperty the field value;
            for (_field in _map.fields) {
                _struct[_field.returnProperty] = _r[_field.queryField];
            }

            arrayAppend(_resultArray,_struct);
        }   
        return _resultArray;
    }
}

The result is a JSON formatted array of objects with each object in the array a record from the query.

[
    {   firstName: 'Terry', 
        lastName: 'Beard', 
        erpId: '0000001', 
        homeDepartment: 'ITS' 
    }
]

This basic code can then be enhanced to do other things like data formatting and running additional processing on fields and record data. But more on that in part 2.

Over the next few years, this process should significantly reduce our code base. Using this process, we can replace the queries in our existing code and have a lot less code to manage. We could also add a direct connection to the queryBox in the auth component of our system and allow the developer to skip the typical .cfcs that go with an application.

This will likely be a game changer in years to come for our first party systems.

Hope you found this post interesting.

Cheers!