Create page from template using Run and SQL macros

[Adam Jacobson]

Hi, first up I'll qualify my ignorance by saying that I'm not a developer but, hey, at least I'm trying!

I've been tinkering with our newly acquired Run and SQL macros with the aim of achieving the following page-creation scenario, similar to the example provided on this Plugin wiki page:

1) Retrieve JIRA issue Summary field to screen via SQL plugin, for user verification

• Run macro passes the JIRA reference supplied by the user into the query:

{run:id=ref|replace=ref:TC-:Reference:text-required|titleRun=Retrieve}
|*Title*|{sql-query:dataSource=JIRADB|output=wiki|table=false}
select Summary from jiraissue where pkey='$ref'
{sql-query}

2) Display Summary on screen and offer to create page from template

• Run1 macro is nested within Run macro runs a CLI command like:

{cli:profile=confluence|showcommand=true|panel=true}-a addPage --space CONF --parent @home --title $... --file templates\\test.txt --noConvert --labels test1,test2 --replace{cli}
Page created: [$...]

My questions are:

1. The value initially stored in $ref doesn't seem available to the second Run1 macro because the page is refreshed. Can I keep it persistent?
2. How could I turn the value returned by SQL (the JIRA Summary) on the first run (Run) into a variable passed to the CLI title parameter on the second run (Run1)?
3. Do I need to investigate doing this using a Groovy script instead?

My next questions will probably be how to then crystallize various returned SQL values within a template sourced from a file...

All assistance appreciated.

[Bob Swift (personal) · Accepted solution]

1. You need one of the keep parameters - see documentation
2. There are 2 options.
   1. Use replace and render to put it into a variable that can be used in the second macro
   2. Go to a script using techniques similar to How to generate choice values using SQL
3. Perhaps - scripts allow you to do lots of things with a bit more complexity and control (security) needs. Second choice once the easier options don't meet requirements or are too cumbersome.

[Adam Jacobson]

Thanks for the pointers, Bob

[Adam Jacobson]

Hi Craig,

Yes, I came up with something that is not exactly the most elegant solution but seems to work. It's quite complex, because it prompts the user to enter and review details about the issue number before they proceed. It also retains JIRA as the source of truth - there is a non-editable section of the release note (defined using a User Macro) that updates according to the details in JIRA.

I've attached the source of the page and the User Macro. Bear in mind that coding is not my gig, so I'm sure it could be improved.

Basically this all uses a combination of the Run, SQL, CLI and replace-and-render macros, a Confluence User Macro, and an XHTML file that contains the template.

The process is:

• present user with an input box for the JIRA issue they would like to create a templated page for
• after user input, present a summary of the JIRA issue information for review
• after user proceeds, use the previously input issue number in a new routine
• the new routine replaces placeholders in the template with content from JIRA and the User Macro, creating a page in a defined space
• a link to the newly created page is then presented so the user can proceed to edit

I found the easiest way to create the XHTML template was to design a page in Confluence and then save the source to the file, replacing certain parts with a placeholder.

What I have learned from this is that, while it's all possible to do, I should learn how to write a script!

(pagesource.txt) (usermacro.txt)

[Craig Hylwa]

Hi Adam:

I am trying to create a similar solution, were you able to finally complete this? If so would you be willing to share the results with me?

Sincerely,

Craig T. Hylwa