Creating custom SQL functions for domain generators or ad hoc views

Hypothetical scenario:

We have an Ad Hoc View (Table type), and one column can contain HTML tags. Since those tags are not rendered as HTML (tags are visible), we would like to create a calculated field that would strip tags from that column's content.

Solutions:

It is possible to do that on domain level in calculated fields sections. for this we would have to write a function in applicationContext-semanticLayer.xml. In the defaultSQLGenerator bean add a new entry:

<entry key="escapeHTML">
    <value>
        return "regexp_replace(" + sqlArgs[0] + ", '\\&lt;.*?&gt;', '','g')"
    </value>
</entry>

After restarting the server this function will be available to be used in domain designer calculated fields section. You just do escapeHTML(fieldName).

However, while this approach might work for databases that have the regexp_replace function, but for example for MySQL, it is not good. So here you have to add a new groovy function that can be later used in the ad hoc editor.

In /WEB-INF/groovy/groovy_column/BaseGroovyColumn.groovy add the following function:

def StripTags(String s) {
    if (s == null) {
        return null;
    } else {
        return s.replaceAll("\\<.*?>","");
    }
}

and in /WEB-INF/applicationContext-el.xml add :

<bean parent="functionDef">
    <property name="name" value="StripTags"/>
    <property name="javaType" value="java.lang.String"/>
    <property name="argumentTypes">
        <list>
            <value>#{ objectTypeMapper.checkType('String') }</value>
        </list>
    </property>
</bean>

NOTE - as of 6.3.0 you need to edit applicationContext-el-operators.xml instead of applicationContext-el.xml

The result is that now you have StripTags function that can be used in ad hoc calculated fields.

Something to note about this approach:

• Since there is no SQL implementation, the property "inMemory" should be true, but this is covered because the parent bean "functionDef" already has inMemory set to true.
• The argumentTypes list is set correctly for a single String argument. If you have different argument types, you can look in applicationContext-el.xml for examples. In more complex cases, you may need to implement custom logic in a subclass of OperatorDefinitionImpl.

Explanation of the difference between sqlArgs and args arrays:

• sqlArgs contains strings, which are SQL versions of the arguments passed to the function.
• args contains DomEL expression objects for the arguments; if you know something about these objects, you can access some useful information. For example, a string constant like 'abc' in DomEL will get turned into a Literal object, and you can get the value by accessing the "value" property, as above.