sqlCommenter

qb supports the sqlCommenter specification by Google for appending contextual information to executed queries.

sqlCommenter support is off by default, but can be activated by setting the sqlCommenter.enabled setting.

moduleSettings = {
    "qb": {
        "sqlCommenter": {
            "enabled": true
        }
    }
};

Once enabled, qb will append a comment on to every non-commented query. This happens as the query is ran, so you will not see this output when calling toSQL() or dump().

sqlCommenter will only add a comment to non-commented queries. If you query contains a comment anywhere in it, sqlCommenter will ignore it.

An example query with a sqlCommenter comment looks like this:

SELECT * FROM foo /*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/

Configuring sqlCommenter

The default configuration structure for sqlCommenter is as follows:

settings = {
    "sqlCommenter": {
        "enabled": false,
        "commenters": [
            { "class": "FrameworkCommenter@qb", "properties": {} },
            { "class": "RouteInfoCommenter@qb", "properties": {} },
            { "class": "DBInfoCommenter@qb", "properties": {} }
        ]
    }
};

When the enabled flag is false, no comments will be appended.

The commenters array are the different components that will add contextual information to each query. You define them by defining a struct with a class key pointing to a WireBox mapping and a properties key containing a struct of any necessary properties.

Commenters

Each Commenter must implement the ICommenter interface. (The implements keyword is not required.). They will be called with the sql being commented and the current datasource. It should return a struct of key/value pairs that will become comments.

Here is an example of the FrameworkCommenter@qb:

component singleton accessors="true" {

    property name="coldboxVersion" inject="coldbox:coldboxSetting:version";

    property name="properties";

    /**
     * Returns a struct of key/value comment pairs to append to the SQL.
     *
     * @sql         The SQL to append the comments to. This is provided if you need to
     *              inspect the SQL to make any decisions about what comments to return.
     * @datasource  The datasource that will execute the query. If null, the default datasource will be used.
     *              This can be used to make decisions about what comments to return.
     */
    public struct function getComments( required string sql, string datasource ) {
        return { "version": "coldbox-#variables.coldboxVersion#" };
    }

}

You may use any. all, or none of the commenters provided by qb. You may also create your own for your application. You may even see commenters pop up on ForgeBox for popular use cases.

For example, if you use cbauth (or cbsecurity using cbauth), this commenter will add the current user ID to each query.

component singleton accessors="true" {

    property name="auth" inject="AuthenticationService@cbauth";

    property name="properties";

    /**
     * Returns a struct of key/value comment pairs to append to the SQL.
     *
     * @sql         The SQL to append the comments to. This is provided if you need to
     *              inspect the SQL to make any decisions about what comments to return.
     * @datasource  The datasource that will execute the query. If null, the default datasource will be used.
     *              This can be used to make decisions about what comments to return.
     */
    public struct function getComments( required string sql, string datasource ) {
        return { "userId": variables.auth.getUserId() };
    }

}

Parsing Commented SQL

The comment generated by sqlCommenter is escaped and url-encoded. It can be reversed by calling the SQLCommenter.parseCommentedSQL method with the full query or the SQLCommenter.parseCommentString method with just the comment.

parseCommentedSQL

Name	Type	Required	Default	Description
sql	string	true	​	The commented SQL string to parse.

Parses a commented SQL string into the SQL and a struct of the key/value pair comments.

getInstance( "ColdBoxSQLCommenter@qb" )
    .parseCommentedSQL( "SELECT * FROM foo /*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/" );

Result

{
    "sql": "SELECT * FROM foo",
    "comments": {
        "action": "index",
        "dbDriver": "mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)",
        "event": "Main.index",
        "framework": "coldbox-6.0.0",
        "handler": "Main",
        "route": "/"
    }
}

parseCommentString

Name	Type	Required	Default	Description
commentString	string	true	​	The comment string to parse into a struct.

Parses a comment string into a struct.

getInstance( "ColdBoxSQLCommenter@qb" )
    .parseCommentString(
        "/*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/"
    );

Result

{
    "action": "index",
    "dbDriver": "mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)",
    "event": "Main.index",
    "framework": "coldbox-6.0.0",
    "handler": "Main",
    "route": "/"
}

Integration with non-ColdBox applications

Out of the box, qb includes a ColdBoxSQLCommenter. Since sqlCommenter adds contextual information, some level of framework or application integration is necessary. You can create your own sqlCommenter instance by extending the qb.models.SQLCommenter.SQLCommenter abstract component. If you create a SQLCommenter for a specific framework, consider sharing it with others on ForgeBox.