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 executedcontext
: 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> } } } } return 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.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.plugin.PluginAccessor 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 def pluginManager = ComponentLocator.getComponent(PluginAccessor) 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.