...
Required Add-ons
Implementation
The script here will allow for 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:
| Code Block |
|---|
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. 
Image Removed
...
.
| 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 |
 Image Added
For Reference:
| Code Block |
|---|
CREATE TABLE confluence_editable_table
(
id integer NOT NULL,
"name" character varying,
"value" character varying,
CONSTRAINT "ID" PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
|
|
| Code Block |
|---|
{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, |
|
...
...
...
...
...
...
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: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}'
|| '{ |
|
...
...
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"
|
|
...
...
{run}
==============================
h3. Insert
{run:id=recinsert|titleRun=Insert|autoRun=false|hideColumnHeadings=true|keepRequestIds=false|replace=
g1::Insert:group,
|
|
...
...
...
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, |
|
...
...
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 |
|
...
...
...
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 |
|
...
...
last = ?
where id = cast(? as integer)
{sql}
"""
println updateMacro
{groovy}
{extract-link:redirect= |
|
...
false} $page_url?run_page_direction=refresh{extract-link}
{run}
|
|