Custom CQL Functions

Custom CQL Functions

To start, let’s create a simple CQL Function that is going to return all attachments within a specified space. To create a custom CQL Function go to Admin → Script CQL Functions, click on the Custom CQL functions link:

Fields available for a Custom CQL Functions are:

  • Note: optional field, used only for your reference and not used internally

  • Name: mandatory field used as the CQL Function display name. It should respect the list of reserved words and characters

  • Number of parameters: accepted by the CQL Function. In the example we define 1 parameter because we are expecting the user to provide the space key

  • Type: specifies if the CQL Function returns a single string value or a list of strings. (In the example we’re using 'Multi Value' because the function returns a list of attachments)

    • Single value: This type should return a single String value which will be the value that the Query Function will be tranformed into, appearing as a single quoted value following the = operator in the CQL statement being executed.

    • Multi value: This type should return an Iterable of String values which will be the values that the Query Function will be transformed into, appearing within the parenthesis following the IN clause in the CQL statement being executed.

  • Script file: path to the script accessible on the server

  • Inline script: the core of the function where we define what the CQL Function returns based on the input

Once the function is created it’s possible to retrieve the output using the ScriptRunner macro 'CQL Search'. Using the query 'title in spaceAttachments("ds")' here’s what the new custom CQL Function returns:

Binding Variables

There are two binding variables available in the CQL Function script:

  • params : Parameters passed into the Query Function in the CQL statement being executed

  • context : The class definition of the object that is passed as the context argument to the invoke method of a Query Function

Example CQL Functions

All the examples are available under Admin → Script CQL Functions → Custom CQL functions → Expand examples section.

Search Page By Label

This CQL Function returns all the pages that contain the Label specified

Configuration

Inline Script

import com.atlassian.confluence.labels.Label
import com.atlassian.confluence.pages.Page
import com.atlassian.confluence.pages.PageManager
import com.atlassian.confluence.spaces.Space
import com.atlassian.confluence.spaces.SpaceManager
import com.atlassian.sal.api.component.ComponentLocator

def spaceManager = ComponentLocator.getComponent(SpaceManager)
def pageManager = ComponentLocator.getComponent(PageManager)
String label = params[0] // <1>
def ids = []

for (Space space : spaceManager.getAllSpaces()) { // <2>
    for (Page page : pageManager.getPages(space, true)) { // <3>
        for (Label pageLabel : page.getLabels()) { // <4>
            if (pageLabel.getName().equalsIgnoreCase(label)) {
                ids << String.valueOf(page.getId()) // <5>
            }
        }
    }
}
ids

Line 10: Getting the label input value

Line 13: Iterating over all spaces

Line 14: Iterating over all pages

Line 15: Iterating over all labels

Line 17: Adding the page ID if a label matches the input

It’s possible to use this function with the ScriptRunner macro 'CQL Search' providing the query 'content in pagesWithLabel("finance")'

Linked Pages

This CQL Function returns all the linked pages associated with the specified page

Configuration

Inline Script

import com.atlassian.confluence.links.OutgoingLink
import com.atlassian.confluence.pages.Page
import com.atlassian.confluence.pages.PageManager
import com.atlassian.confluence.spaces.Space
import com.atlassian.confluence.spaces.SpaceManager
import com.atlassian.sal.api.component.ComponentLocator

String spaceKey = params[0] // <1>
String pageTitle = params[1]

SpaceManager spaceManager = ComponentLocator.getComponent(SpaceManager)
PageManager pageManager = ComponentLocator.getComponent(PageManager)
def ids = []

Space space = spaceManager.getSpace(spaceKey)

for (Page page : pageManager.getPages(space, true)) { // <2>

    for (OutgoingLink link : page.getOutgoingLinks()) { // <3>
        if (link.getDestinationPageTitle().equals(pageTitle) && link.getDestinationSpaceKey().equals(spaceKey)) { // <4>
            ids << String.valueOf(page.getId())
        }
    }
}

return ids

Line 9: Getting the space key and target page title from the inputs

Line 18: Iterating over all pages in the space

Line 20: Iterating over all outgoing links from a page

Line 21: Adding the page ID if the outgoing link matches both the space key and the target page title

It’s possible to use this function with the ScriptRunner macro 'CQL Search' providing the query 'content in linkedPages("ds", "Welcome to Confluence")'

Add-On Pages

This CQL Function returns all the pages that are using any macro of a specified add-on

Configuration

Inline Script

import com.atlassian.confluence.macro.browser.MacroMetadataSource
import com.atlassian.confluence.pages.Page
import com.atlassian.confluence.pages.PageManager
import com.atlassian.confluence.plugin.ConfluencePluginManager
import com.atlassian.confluence.search.v2.ContentSearch
import com.atlassian.confluence.search.v2.ISearch
import com.atlassian.confluence.search.v2.InvalidSearchException
import com.atlassian.confluence.search.v2.SearchConstants
import com.atlassian.confluence.search.v2.SearchManager
import com.atlassian.confluence.search.v2.SearchResult
import com.atlassian.confluence.search.v2.SearchResults
import com.atlassian.confluence.search.v2.query.MacroUsageQuery
import com.atlassian.plugin.ModuleDescriptor
import com.atlassian.plugin.Plugin
import com.atlassian.sal.api.component.ComponentLocator
import com.google.common.base.Function
import com.google.common.base.Predicate
import com.google.common.collect.Collections2
import com.google.common.collect.ImmutableList
import com.google.common.collect.Lists
import com.google.common.collect.Ordering

import javax.annotation.Nullable

final ConfluencePluginManager pluginManager = ComponentLocator.getComponent(ConfluencePluginManager)
PageManager pageManager = ComponentLocator.getComponent(PageManager)

String addOnKey = params[0]
Plugin plugin = pluginManager.getPlugin(addOnKey) // <1>
assert plugin: "Add-on with key '${addOnKey}' not found"

List<String> pluginMacros = getMacroModuleKeys(plugin) // <2>

List<SearchResult> searchResults = findAbstractPagesContainingMacro(pluginMacros) // <3>

def ids = []
for (final SearchResult searchResult : searchResults) { // <4>
    Page page = pageManager.getPage(searchResult.getSpaceKey(), searchResult.getDisplayTitle())
    ids << String.valueOf(page.getId())
}

return ids

private List<String> getMacroModuleKeys(Plugin plugin) {
    if (plugin == null) {
        return Collections.emptyList()
    }

    final Collection<ModuleDescriptor<?>> moduleDescriptors = plugin.getModuleDescriptors()

    final Collection<ModuleDescriptor<?>> macroModuleDescriptors = Collections2.filter(moduleDescriptors, new Predicate<ModuleDescriptor<?>>() {
        boolean apply(@Nullable ModuleDescriptor<?> moduleDescriptor) {
            return moduleDescriptor instanceof MacroMetadataSource
        }
    })

    final Function<ModuleDescriptor<?>, String> getKeyFunction = new Function<ModuleDescriptor<?>, String>() {
        String apply(ModuleDescriptor<?> moduleDescriptor) {
            return moduleDescriptor.getKey()
        }
    }

    final Function<ModuleDescriptor<?>, String> getNameFunction = new Function<ModuleDescriptor<?>, String>() {
        String apply(ModuleDescriptor<?> moduleDescriptor) {
            return moduleDescriptor.getName()
        }
    }

    final List<ModuleDescriptor<?>> sortedMacroModuleDescriptors = Ordering.natural().onResultOf(getNameFunction).immutableSortedCopy(macroModuleDescriptors)

    return Lists.transform(sortedMacroModuleDescriptors, getKeyFunction)
}

private List<SearchResult> findAbstractPagesContainingMacro(final List<String> pluginMacros) {

    final List<SearchResult> allResults = []
    for (String macroName : pluginMacros) {
        doSearch(macroName, 0, SearchConstants.MAX_LIMIT, allResults)
    }

    return ImmutableList.copyOf(allResults)
}

private void doSearch(
    final String macroName, final int startIndex, final int limit, final List<SearchResult> allResults
) {

    SearchManager searchManager = ComponentLocator.getComponent(SearchManager)

    final ISearch search = new ContentSearch(new MacroUsageQuery(macroName), null, null, startIndex, limit)
    try {
        SearchResults searchResults = searchManager.search(search)
        allResults.addAll(searchResults.getAll())
        if (searchResults.getUnfilteredResultsCount() > (startIndex + limit)) {
            doSearch(macroName, (startIndex + limit), limit, allResults)
        }
    } catch (InvalidSearchException e) {
        // We can't recover from this so we wrap the error in a runtime exception
        throw new RuntimeException("Error searching for pages containing the Forms for Confluence macros", e)
    }
}

Line 29: Getting the plugin associated with the key specified

Line 32: Retrieving all the macros associated with the plugin

Line 34: Getting all the pages that are using at least one plugin macro

Line 37: Iterating over the pages to return the list of IDs

It’s possible to use this function with the ScriptRunner macro 'CQL Search' providing the query 'content in addOnPages("com.adaptavist.confluence.formMailNG")'

The function retrieves the add-on macros defined in the plugin descriptor.

On this page