It can happen now and then that macros are removed in Confluence and replaced by a new feature. For example the View PDF Macro that was marked as obsolete with Confluence 5.9. But what to do to find the affected pages? Here PocketQuery for Confluence can help. A very simple one, admittedly, but I think it’s a practical example to use templates as well as converters and to have a quick access.
Preconditions
- Of course PocketQuery for Confluence should be installed.
- A connection to the respective database of Confluence should be established.
Preparation
To find the macro on any page, you must first display the format in which it was saved. The macro is stored here in the same format as it can be found in the database. For example the View PDF Macro:
<ac:structured-macro ac:name="viewpdf">
<ac:parameter ac:name="name">
<ri:attachment ri:filename="My_document.pdf"/>
</ac:parameter>
</ac:structured-macro>
Only the following part is interesting:
ac:name="viewpdf"
Setting up PocketQuery for Confluence
We need:
- a new query (Query)
- a converter
- a template (optional)
SQL Queries
SELECT
title,
"content".contentid,
spaces.spacekey,
MAX("version"), (CHAR_LENGTH(bodycontent.body) - CHAR_LENGTH(REPLACE(bodycontent.body, :searchterm, ''))) / CHAR_LENGTH('ac:name="viewpdf') AS "occurrences"
FROM
"content"
JOIN spaces ON spaces.spaceid = "content".spaceid
JOIN bodycontent ON bodycontent.contentid = "content".contentid
WHERE
contenttype = 'PAGE'
AND bodycontent.body LIKE concat('%', :searchterm, '%')
GROUP BY
content.contentid,
title,
spaces.spacekey,
bodycontent.body
ORDER BY
spacekey,
title
The parameter is created as a ‘String’ type and stored as a search term.
Converter
In general you don’t need the converter, but it makes the process a bit more user-friendly to create URLs and make them clickable.
The converter is based on the standard converter, but a few columns are put together.
function convert(json) { // json is the result as json string
var baseURL = 'https://confluence.mycompany.com'
var result = []; // the array-of-objects we will return
var parsedJsonObject = JSON.parse(json); // parse json string
var current, index; // loop variables
for (index in parsedJsonObject) { // iterate through the result
// only continue if this property is not inherited
if (parsedJsonObject.hasOwnProperty(index)) {
current = parsedJsonObject[index]; // current object
result.push({ // add a converted object
'Page Title (Page ID)': '<a href="'+baseURL+'/pages/viewpage.action?pageId='+current.contentid+'" target="_blank">'+current.title + ' ('+ current.contentid+')</a>',
'Space': '<a href="'+baseURL+'/display/'+current.spacekey+'/" target="_blank">'+current.spacekey+'</a>',
'Occurrences': current.occurrences
});
}
}
return result; // return the converted result
}
Important! In line 2 the Base URL of Confluence must be entered.
Template
In our example we added the default template with the button “Add default template” - this is optional and only necessary if you want to change the appearance.
Save and Insert Macro
In the query you have to select the converter and template.
The macro can then be inserted on a Confluence page. From the preparation step the search term for the macro has to be inserted.
The search term should best be packed in wildcards, because before and after there are other contents. For example %ac:name=”viewpdf”%
It is helpful to activate dynamic loading.
Result
A table with all the linked pages that use the macro at some point, so they can easily be edited.