Script - Editable Paging Tables

Required Add-ons

Implementation

The script here allows you to add editable paging tables to your wiki page. A drop down is provided in the run macro that allows 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 enables you to revisit a page and pick up where you left off. You must provide the custom parameters and SQL inline.

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)

def selectResultsMacro = """
{sql-query:dataSource=test|output=wiki}
 
 select id, first, last, '{section}{column:border=true}'
 || '{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}' || first || '{url-encode}'
 || '&run_edit_value={url-encode}' || last || '{url-encode}'
 || '&run_edit_id=' || id
 || '&run_selection_hideEdit=false'
 || '#\$page_anchor_e' || id
 || '{extract-link}'
 || '{run1}'
 || '{column}{column:border=true}'
 || '{run1:id=d' || id
 || '|buttonPadding=0'
 || '|titleRun=Delete}'
 || '{sql:dataSource=test}'
 || 'delete from confluence_editable_table where id = ' || id
 || '{sql}'
 || '{extract-link:redirect=false}'
 || '$page_url?run_page_direction=refresh'
 || '{extract-link}'
 || '{run1}'
 || '{column}{section}' 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,
 newid::New id,
 name::first,
 value::last}
 {groovy:output=wiki|gid=$newid|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, first, last)
         values(cast(? as integer) , ?, ?)
        {sql}
    """
 
  println insertMacro
 {groovy}
 {extract-link:redirect=false} $page_url?run_page_direction=refresh{extract-link}
{run}
==============================
{anchor:edit}
h3. Edit
{run:id=edit|hideColumnHeadings=true|replace=
 g1::Edit:group,
 id::id,
 name::first,
 value::last
 |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 first = ?, last = ?
     where id = cast(? as integer)
   {sql}
 """
 println updateMacro
{groovy}
{extract-link:redirect=false} $page_url?run_page_direction=refresh{extract-link}
{run}