Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

MacroResult
Chart
dataOrientationhorizontal
typebar
PluginDownload Count
WhizzPlugin1000
BangPlugin990
ZippyPlugin600
AnotherPlugin500

But it the chart macro can also take output from a SQL macro:

MacroResult
Chart
dataOrientationhorizontal
typebar

SQL Query
dataSourcePluginStats
SELECT name AS Plugin,
	downloadcount AS "Download Count"
FROM plugin
ORDER BY 2 DESC
LIMIT 5;

(or CSV, or In fact chart can take input from any other table-generating macro (e.g. CSV)This plugin composability allows very powerful combinations. .

Like Unix pipes, one macro fetches data and feeds it to another.

 

you can string together and reuse macros. This plugin composability is one of Confluence's most powerful features.

This is great, but only works when the macro in question was designed to accept the "rich text" XHTML generated by another macro. The chart plugin can have a nested macroFor example, but the SQL Query plugin macro can't , for exampletake macro output as its input.

Use-cases

Why would we want to send dynamic content to a macro not expecting it? Here are a few situations:

  • You write a param macro that returns are using the SQL macro to query a database, but want to use a HTTP parameter from the URL , and want to use it to parametrize a SQL query(param macro) in the query to make it dynamic.
  • You want to use SQL results in Javascript in a html macro.

...

Dynamic Wrapper Macros

The solution is to create a wrapper user macro that accepts rich text as input,   converts it to plain text

 

 

 

 strips out any XHTML tags, and feeds the resulting plain text to the macro you're interested in.

sqlquery_dynamic

The first example lets the SQL Query macro take dynamic input. Here is a silly example, with SQL that emits XHTML for a Confluence @user reference, (/) tick and a custom param user macro:

Image Added

rendering as:

Image Added

 

The macro definition looks like this:

Image Added

With macro body:

Code Block
## @param dataSource:title=Data source name|type=string|required=true|desc=Type or select a data source configured by your Confluence administrator.
## @param output:title=Output format|type=enum|default=html|enumValues=html,xhtml,wiki,unrenderedWiki
## @param table:title=Show result as a table|type=boolean|default=true
## @param printsql:title=Print SQL after results|type=boolean|default=true
## @param autototal:title=Auto row total|type=boolean|default=false|desc=Adds a row to the end of the table that totals numeric columns.
## @param columnTypes:title=Column Types|type=string|desc=Comma separated list of type indicators. Column type determines sorting and other characteristics. See https://bobswift.atlassian.net/wiki/display/TBL/Common+Table+Capabilities

## Given rendered macro output (HTML), strips HTML formatting elements, leaving just the content.
#macro(clean $body)
#foreach($i in [1..10])
## (?:..) is a non-capturing group. 
#set($body=$body.replaceAll('(?smi)<span(?: [^>]*)?>(.*?)</span>', '$1'))
#set($body=$body.replaceAll('(?smi)<p(?: [^>]*)?>(.*?)</p>', ' $1'))
#set($body=$body.replaceAll('(?smi)<div(?: [^>]*)?>(.*?)</div>', '$1'))
#set($body=$body.replaceAll('(?smi)<p(?: [^>]*)?>(.*?)</p>', ' $1'))
#end
#set($body=$body.replaceAll('<br ?/>', '
'))
#set($body=$body.replaceAll('&apos;', "'"))
#set($body=$body.replaceAll('&lt;', "<"))
#set($body=$body.replaceAll('&gt;', ">"))
#set($body=$body.replaceAll('&quot;', '"'))
#set($body=$body.replaceAll('&;', '"'))
$body#end
##
<ac:structured-macro ac:name="sql-query">
  <ac:parameter ac:name="output">$paramoutput</ac:parameter>
  <ac:parameter ac:name="dataSource">$paramdataSource</ac:parameter>
  <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
  <ac:parameter ac:name="table">$paramtable</ac:parameter>
  #if($paramcolumnTypes)<ac:parameter ac:name="columnTypes">$paramcolumnTypes</ac:parameter>#end
  <ac:parameter ac:name="rowStyles">border-bottom:black 2px solid;border-top:black 2px solid;,background:#ffffee,background:#fff</ac:parameter>
  <ac:parameter ac:name="autoTotal">$paramautototal</ac:parameter>
  <ac:parameter ac:name="noDataMessage">no results</ac:parameter>
  <ac:plain-text-body><![CDATA[#clean($body)]]></ac:plain-text-body>
</ac:structured-macro>

#if($paramprintsql == true)
<small>Rendering SQL: $generalUtil.htmlEncode($body)</small><br>
#end