Script - Editable Paging Tables

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. You must provide the custom parameters and SQL inline.
{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}