How to do SQL database operations on lists of data

Description

This is a example of using advanced run macro techniques combined with the SQL for Confluence to provide some useful database operations through a Confluence page.

Links

Required Plugins

Run Self-Service Reports for Confluence
SQL for Confluence
Adaptavist Content Formatting Macros - to improve the look of the table

Steps

Copy the example macro to a new page - use Insert -> Wiki Markup
- Recommend using Wiki Markup for Confluence to preserve the wiki markup for easier debugging and maintenance.
Update the sql usage to match your database environment
- The example is tested with PostgreSQL and uses the || for string concatenation.
Generate the test database using the recreate example
- Comment out the drop the first time this is run
Experiment with the capabilities

Example page markup

{section} {column}

h3. List
{run-now:id=selection|replace=hideEdit:true::hidden,hideInsert:true::hidden}
{sql-query:dataSource=testDS|output=wiki}
  select 
  id,
  first || ' ' || last as "Name",
  extension as "Extension",
  '{run:id=e' || id 
  ||   '|buttonPadding=0'
  ||   '|keepRequestParameters=true'
  ||   '|anchor=edit'
  ||   '|titleRun=Edit}'
  ||     '{extract-link:redirect=true}'
  ||       '$page_url'
  ||       '?run_selection=run&run_edit_first={url-encode}' || first || '{url-encode}' 
  ||       '&run_edit_last={url-encode}' || last || '{url-encode}'
  ||       '&run_edit_extension=' || extension 
  ||       '&run_edit_id=' || id 
  ||       '&run_selection_hideEdit=false'
  ||       '#$page_anchor_e' || id
  ||     '{extract-link}'
  || '{run}'
  || '{run:id=d' || id   
  ||   '|buttonPadding=0'
  ||   '|titleRun=Delete}'
  ||     '{sql:dataSource=testDS}'
  ||       'delete from example where id = ' || id 
  ||     '{sql}'
  ||     '{extract-link:redirect=true}'
  ||       '$page_url?&run_selection=run'
  ||     '{extract-link}'
  || '{run}'
  as "Actions"

  from example order by id
{sql-query}
{run-now}

{anchor:edit}

h3. Edit
{run:id=edit|hideColumnHeadings=true|keepRequestParameters=true|replace=
    g1::Edit:group,
    id:::hidden,
    first::first,
    last::last,
    extension::extension:integer-select:1200:1299
  |titleRun=Change} 
    {sql:dataSource=testDS|p1=$first|p2=$last} 
      update example 
        set first = ?, last = ?, extension = $extension
        where id = $id 
    {sql} 
    {extract-link:redirect=true}
      $page_url?run_selection=run
    {extract-link}
{run}

h3. Insert
{run:id=insert|hideColumnHeadings=true|replace=
    g1::Insert:group,
    first::first,
    last::last,
    extension::extension:integer-select:1200:1299
  |titleRun=Insert}
    {sql:datasource=testDS|p1=$first|p2=$last}
      insert into example
        (first, last, extension) 
        values(?, ?, $extension)
    {sql}
    {extract-link:redirect=true}
      $page_url
    {extract-link}
{run}

{column} {column}

h3. Display
{sql-query:dataSource=testDS} select * from example order by id{sql-query}

h3. Recreate example table
{run:id=create|titleRun=Recreate}
  {sql:datasource=testDS|output=wiki|showSql=true}
    drop table if exists example;
    create table example (
      id serial not NULL,
      first text default NULL,
      last text default NULL,
      extension int default NULL
    );
    insert into example (first, last, extension) values('Bob', 'Jones', 1234);
    insert into example (first, last, extension) values('Jane', 'Doe', 1283);
    insert into example (first, last, extension) values('Bill', 'O''Mally', 1289);
    insert into example (first, last, extension) values('Billy Joe', 'Higgins-Smith', 1281);
    insert into example (first, last, extension) values('Karen', 'Cole III', 1280);
  {sql} 
  {extract-link:redirect=true}
    $page_url
  {extract-link}
{run}
 
{column} {section}