Script - Editable Paging Tables

Required Plugins

Implementation

The script here will allow for you to add editable paging tables to your wiki page. A drop down is provided in the run macro to allow you to page forward and backward. You can also set the size of resultset to return. This script also demonstrates how to store parameters with your current session:

value=session.getAttribute(SESSION_OFFSET_ATTRIBUTE)
session.setAttribute(SESSION_OFFSET_ATTRIBUTE, value)

This will enable you to revisit a page and pick up where you left off. You must provide the custom parameters and SQL inline.

**Note: do not use with the table of contents macro. There is a bug in the toc that causes the run macro to run twice! TOC Bug

For Reference:

CREATE TABLE confluence_editable_table
(
  id integer NOT NULL,
  "name" character varying,
  "value" character varying,
  CONSTRAINT "ID" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

{run:id=page|autoRun=true|hideColumnHeadings=true|replace=
 g1::Edit:group,
 limit:10:Size:select::5::10::25::50::75::100::200::300::400::500,
 direction:refresh:Select:select::forward::backward::begin::end::refresh,
 offset:0::hidden
 |titleRun=Page}

{groovy:output=wiki|glimit=$limit|goffset=$offset|gdirection=$direction|goffsetoveride=$overideoffset}

import com.atlassian.renderer.v2.RenderMode

def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA)
SESSION_OFFSET_ATTRIBUTE='hga_pageoffsetvalue'

value=session.getAttribute(SESSION_OFFSET_ATTRIBUTE)

//***UPDATE SQL: nested in between quotes
def countMacro = "{sql:id=countsql|datasource=test|table=false}select count(*) from confluence_editable_table;{sql}"

def count = subRenderer.render(countMacro, context, renderMode)

glimit=glimit.toInteger()

  if (value==null) {
    value=goffset
  } else {
    c=count.toInteger()
    value=value.toInteger()

    if (gdirection=='forward') {
      if (c>(value+glimit))
        value=value+glimit
    } else if (gdirection=='backward') {
      if ((value-glimit)<0)
        value=0
      else
        value=value-glimit
    } else if (gdirection=='begin') {
        value=0
    } else if (gdirection=='end') {
        if (glimit<c)
          value=c-glimit
        else
          value=0
    }
  }

session.setAttribute(SESSION_OFFSET_ATTRIBUTE, value)

//***UPDATE SQL: nested in between quotes
def selectResultsMacro = """
{sql-query:dataSource=test|output=wiki}

 select id, name, value,

 '{table:border=0|cellpadding=0|cellspacing=0}{table-row}{table-cell}'
 || '{run1:id=e' || id || '|buttonPadding=0'
 || '|keepRequestParameters=true|anchor=edit|titleRun=Edit}'
 || '{extract-link:redirect=true}'
 || '$page_url'
 || '?run_selection=run&run_edit_name={url-encode}' || name || '{url-encode}'
 || '&run_edit_value={url-encode}' || value || '{url-encode}'
 || '&run_edit_id=' || id
 || '&run_selection_hideEdit=false'
 || '#\$page_anchor_e' || id
 || '{extract-link}'
 || '{run1}'
 || '{table-cell} {table-cell}'
 || '{run1:id=d' || id
 || '|buttonPadding=0'
 || '|titleRun=Delete}'
 || '{sql:dataSource=test}'
 || 'delete from confluence_editable_table where id = ' || id
 || '{sql}'
 || '{extract-link:redirect=true}'
 || '$page_url?run_page_direction=refresh'
 || '{extract-link}'
 || '{run1}'
 || '{table-cell}{table-row}{table}' as "Actions"

 from confluence_editable_table order by id limit $limit offset $value

{sql-query}
"""
println selectResultsMacro
println "Page command = '$gdirection', limit=$glimit, offset=$value, rows=$count"

{groovy}

{run}

h3. Insert
{run:id=recinsert|titleRun=Insert|autoRun=false|hideColumnHeadings=true|keepRequestIds=false|replace=
 g1::Insert:group,
 id::id,
 name::name,
 value::value}
 {groovy:output=wiki|gid=$id|gname=$name|gvalue=$value}
    //***UPDATE SQL: nested in between quotes
    def insertMacro = """
        {sql:id=insertsql|datasource=test|p1=$gid|p2=$gname|p3=$gvalue}
         insert into confluence_editable_table(id, name, value)
         values(cast(? as integer), ?, ?)
        {sql}
    """

  println insertMacro
 {groovy}
 {extract-link:redirect=true} $page_url?run_page_direction=refresh{extract-link}
{run}

{anchor:edit}
h3. Edit
{run:id=edit|hideColumnHeadings=true|replace=
 g1::Edit:group,
 id::id:hidden,
 name::name,
 value::value
 |titleRun=Update}
{groovy:output=wiki|gid=$id|gname=$name|gvalue=$value}
 //***UPDATE SQL: nested in between quotes
 def updateMacro = """
   {sql:dataSource=test|p1=$gname|p2=$gvalue|p3=$gid}
     update confluence_editable_table
     set name = ?, value = ?
     where id = cast(? as integer)
   {sql}
 """
 println updateMacro
{groovy}
{extract-link:redirect=true} $page_url?run_page_direction=refresh{extract-link}
{run}