How to do SQL database operations on lists of data - 6.x

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 marco 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}