SQLTABLE personalizing

[SergeyPo]

For those of us who loves using SQLTABLE but is missing some simple
personalizing methods (more link columns, image instead of 'id' in
links etc.) here is very basic function. It allows to update the whole
column of a SQLTABLE object using regular expressions. I hope it helps
someone who is as lazy as me.

E.g. SQLTABLE produces:

<tr>
<td>id</td>
<td>name</td>
<td>friends number</td>
</tr>
<tr>
<td>1</td>
<td>John</td>
<td>100</td>
</tr>
<tr>
<td>2</td>
<td>Steve</td>
<td>500</td>
</tr>
<tr>
<td>3</td>
<td>Ron</td>
<td>200</td>
</tr>

Example 1:
Replace 'friends number' value with hyperlink that is to open friends
list:

table = update_column(table, argument_col_num=0, target_col_num=-1,
pattern="(\d+)",
replace_with="A(%(g1)s, _href=URL(r=request,
f='friends_list', args=[%(arg)s]))")

will produce:

<tr>
<td>id</td>
<td>name</td>
<td>friends number</td>
</tr>
<tr>
<td>1</td>
<td>John</td>
<td><a href="/friends_list/1">100</a></td>
</tr>
<tr>
<td>2</td>
<td>Steve</td>
<td><a href="/friends_list/2">500</a></td>
</tr>

Example 2:
Replace 'id' value in first column with a PNG image (a button) linked
to opening the object:

table = update_column(table, argument_col_num=0, target_col_num=0,
replace_with="A(IMG(_src=URL(r=request,
c='static', f='button.png'), _style='padding:0px;'),
_onclick='javascript:alarm(\"%(arg)s\");', _style='cursor:pointer;',
_href=URL(r=request, f='show', args=[%(arg)s]))")


Place this function to models and it will be available from all views.

import re
def update_column(table, argument_col_num=0, target_col_num=-1,
pattern="(\d+)",
replace_with="A(%(g1)s, _href=URL(r=request,
f='show', args=[%(arg)s]))"):
#pass 'replace_with' as a string like "A(%(g1)s, URL(r=request,
f='show', args=[%(arg)s]))"
#where (g1) means group1 from reg. exp. match (count from 1)
# (arg) is value of column number 'argument_col_num'
regexp = re.compile(pattern)
tagz = re.compile("<.*?>")
for i in range(len(table.components[1])):
target_td = table.components[1][i].components[target_col_num]
arg_td = table.components[1][i].components[argument_col_num]
if type(target_td) == type(TD()) and len(target_td.components)
> 0 and len(arg_td.components) > 0:
m = re.match(regexp, str(target_td.components[0]))
a = re.sub(tagz, '' , str(arg_td.components[0]))
d = {'arg': a}
if m:
for j in range(1, len(m.groups()) + 1): d['g' + str
(j)] = m.group(j)
table.components[1][i].components[target_col_num] = TD(eval
(replace_with % d))
return table


I am not sure this should be contributed into gluon.sqlhtml as method
for SQLTABLE but definitely saves me much time for quick prototyping.

[mdipierro]

actually you can already the representation of a field. For example in
case 2 you can just do

db.table.field.represent = lambda value: A('image',_href=URL
(r=request,f='download',args=value))

then make the SQLTABLE

Massimo

[SergeyPo]

Pity that I did not grasp it from docs and this mailing list... Do I
need to import t2 or anything else to use it?

[mdipierro]

nope, just web2py

you can also personalize widgets in forms with

db.table.field.widget

[Markus Gritsch]

On Tue, Mar 3, 2009 at 7:16 PM, SergeyPo <ser...@zarealye.com> wrote:
>
> Pity that I did not grasp it from docs and this mailing list...

It's not your fault. It's one of those things which hopefully get
documented soon.

Markus

[SergeyPo]

I see what happened: I was just using version 1.47 on my development
machine, so did not see new possibilities in source code. And with all
those T2, T3 discussions in mailing list, there was a misconception
that widgets, representation and other cool stuff are provided by T2,
T3. I do not need most of T2 features so I just did not care...

I still have to use my ugly 'update_column' function because I make
many SQLTABLEs with aggregation and I don't see how I can setup
representation for a column referred as 'SUM(db.table.field)/COUNT
(db.table.field)' <<there is no AVG aggregator in gluon!>>