Page Comparison

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,
 insertidnewid::New id,
 name::first,
 value::last}
 {groovy:output=wiki|gid=$insertid$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,
 updateidid::id,
 name::first,
 value::last
 |titleRun=Update}
{groovy:output=wiki|gid=$updateid$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}