/
How to do SQL database operations on lists of data - 6.x
Document toolboxDocument toolbox

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

Owned by Former user (Deleted)
Last updated: Jul 03, 2019

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.

Required Plugins

Steps

  1. 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.
  2. Update the sql usage to match your database environment
    The example is tested with PostgreSQL and uses the || for string concatenation.
  3. Generate the test database using the recreate example
    Comment out the drop the first time this is run.
  4. 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}

Related content