# sqlCommenter

qb supports the [sqlCommenter specification by Google](https://google.github.io/sqlcommenter/) for appending contextual information to executed queries.

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

```cfscript
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()`](#tosql) or [`dump()`](#dump).

{% hint style="warning" %}
sqlCommenter will only add a comment to **non-commented** queries.  If you query contains a comment anywhere in it, sqlCommenter will ignore it.
{% endhint %}

An example query with a sqlCommenter comment looks like this:

{% code overflow="wrap" %}

```sql
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'*/
```

{% endcode %}

### Configuring sqlCommenter

The default configuration structure for sqlCommenter is as follows:

```cfscript
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`:

```cfscript
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](https://forgebox.io) 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.

```cfscript
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.

{% code overflow="wrap" %}

```cfscript
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'*/" );
```

{% endcode %}

{% code title="Result" %}

```json
{
    "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": "/"
    }
}
```

{% endcode %}

#### parseCommentString

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

Parses a comment string into a struct.

{% code overflow="wrap" %}

```cfscript
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'*/"
    );
```

{% endcode %}

{% code title="Result" %}

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

{% endcode %}

### 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](https://forgebox.io).