|
CGI::Bus::tmsql - SQL database user interface to view and edit data |
CGI::Bus::tmsql - SQL database user interface to view and edit data
use CGI::Bus;
$s =CGI::Bus->new();
$s->dbi("DBI:mysql:mysql","root","");
$s->tmsql->set(-form=>[
{-tbl=>'mysql.user', -alias=>'u'}
,{-flg=>'ak"', -fld=>'host',-crt=>'localhost',-qry=>'localhost'}
,{-flg=>'cieulqsk"', -fld=>'user'}
,{-flg=>'cevls"', -fld=>'password'}
]);
$s->tmsql->set(-lists=>{'default'=>{-orderby=>'1 ASC, 2 DESC'}});
$s->tmsql->evaluate;
This module is an SQL database user interface to view and edit data, page transaction manager.
There are a common TRANSACTION SEQUENCE and two programming interfaces.
Simplest interface uses cmd(-command) conditions and
direct DBI calls along with cnd and qparam calls.
More usable interface (SQL generator) uses DATA DESCRIPTION inside
-form and -lists slots
with eval or evaluate or cmd('-cmd') call.
See CGI::Bus::tm as base class.
See CGI::Bus::Base for inherited slots and methods.
Transaction sequence is a series of hooks dependent on
current command cmd and
current generic (or general or global) command cmdg.
cmd(-command) calls are used as conditions.
In simplest programming interface this conditions should be programmed.
In more usable programming interface (SQL generator)
this conditions are hidden inside
eval or cmd(-cmd) call evaluating cmdCCC methods,
but may be hooked with -cmdCCC slots.
Using simplest interface, transaction sequence looks like something as:
my $s =CGI::Bus->new();
my $t =$s->tmsql;
my $d =$s->dbi; #
eval { # inside $t->eval(?connect, sub{
if ($t->cmd(-chk)) { # check before insert, update, delete # $t->cmdchk
}
if ($t->cmd(-ins)) { # insert record # $t->cmdins
$d->execute('insert ...', @{$t->qparam([names])});
}
if ($t->cmd(-upd)) { # update record where cnd # $t->cmdupd
$t->cnd(-upd, field=>flags,...);
$d->execute('update ...' .$t->cnd, @{$p->qparam([names])});
}
if ($t->cmd(-del)) { # delete record where cnd # $t->cmddel
$t->cnd(-del, field=>flags,...);
$d->execute('delete ...' .$t->cnd);
}
if ($t->cmd(-sel)) { # select record fields to edit # $t->cmdsel
$t->cnd(-sel, field=>flags,...);
$p->qparam($d->selectrow_hashref('...' .$t->cnd));
}
if ($t->cmd(-crt)) { # create new record values # $t->cmdcrt
}
if ($t->cmd(-qry)) { # list query condition # $t->cmdqry
}
if ($t->cmd(-htm)) { # html page begin # $t->cmdhtm
print $t->htmlhid;
print $t->htmlbar;
}
if ($t->cmd(-frm)) { # html record form # $t->cmdfrm
# print html form
}
if ($t->cmd(-lst)) { # list records # $t->cmdlst
$t->cnd(-lst, field=>flags,...);
$d->select...
}
if ($t->cmd(-end)) { # commit, inside $t->eval call
$d->commit
}
};
$t->print($t->htmlres);# result msg, inside $t->eval call
}
cmdchk call
cmdins call
-pxpv prefix.
cmdupd call.
-pxpv prefix.
cmddel call.
cmdsel call.
cmdcrt call.
cmdqry call
cmdhtm call
cmdfrm call
cmdlst call
eval call along with htmlres and rollback.
About all SLOTS of CGI::Bus::tmsql object are related to
data description, but several slots have a complex format described below.
All slots are available via new or set call.
Form description -form slot contains Table Description,
Field Description, and HTML elements as array ref:
set(-form =>
[{ -tbl => table_name, -alias, -join, -joina, -joinw },
{ -fld => field_name, -flg, -col, -src,
-lbl, -lblhtml, -cmt,
-inp, -inphtml, -hide, -hidel, -colspan, -width,
-chk, -sav,
-qry, -crt, -frm,
-null, -cstr, -clst, -cdb, -cdbC, -cdbCa }
........,
HTML, .......])
Related to table fields should be described below table.
Sequence of fields corresponds to form and default views.
HTML element may be ``'' - to continue field placement in the current row (otherwise next field will be placed at the next row), ``\t'' - to skip table cell, ``\f'' - to finish tabular layout, ``</table>'' - to finish tabular layout and output of labels, ``string'' - string to print, \&sub - to eval and print
{ -tbl => table_name, -alias, -join, -joina, -joinw }
element of Form Description -form slot
Required are -tbl and some of joins for joined tables
{ -fld => field_name, -flg, -col, -src,
-lbl, -lblhtml, -cmt,
-inp, -inphtml, -hide, -hidel,
-chk, -sav, -ins, -upd,
-qry, -crt, -frm,
-null, -cstr, -clst, -cdb, -cdbC, -cdbCa }
element of Form Description -form slot.
Required are only -fld and -flg.
cmdsql it will be evaluated
just before command, when SQL generation.
Autogenerated key value may be implemented via '-cdbi' sub.
Computed fields may be implemented via -frm or -sav sub.
See also -cdbCa, -frm, -sav.
cmdsql it will be evaluated just after SQL execution.
Generated by database key value may be retrieved via '-cdbia' sub{},
e.g. -cdbia => sub{$_[0]->dbi->{'mysql_insertid'}}.
See also -cdbC, -frm, -sav.
-chk => "perl code"
-chk => sub{}
-chk => [ sub{}, message string ]
Check field value before -ins or -upd action - insert or update.
-lst action).
Default is nothing.
lngname
-crt action).
Sub{} or value.
-lst action)
or record edit/view form (-frm action)
-fld
cmdchk
under -del command. See also -ins, -upd, -sav.
Form Description, used as CGI param name
with or without prefix like -pxpv
'a'll - use in all operations below (except 'f'etch, 'k'ey and below)
'c'reate - create new record
'e'dit - edit record and field
'v'iew - view record or field
'av' - always view only, not edit
'cv' - view only when new record created
'ev' - view only when record is edited
'l'ist - list records
'f'etch - fetch only when list
'q'uery - query condition form to list records
'i'nsert - insert to database 'u'pdate - update in database 'd'elete - delete in database (reserved) 's'elect - select from database
'k'ey - key field, use to open record from list of records
'w'here key - use field along with key in update or delete condition
for optimistic locking
'm'andatory - value is required
'g'enerated - value is generated by database
'"',"'" - quote method of the value ('[' reserved)
'n'umeric - numeric value (reserved)
-chk or -frm command.
See also -sav
-frm) condition sub{}
-frm) condition sub{}.
Fields at the right will be shifted to the left.
-htmlopt => 1 attribute causes to use data as HTML if
HTML formatting detected with ishtml CGI::Bus method.
'htmltextarea' URLs display behaviour is extended with
special URL protocol 'fsurl://' to be translated to fsurl value.
See also CGI::Bus for 'htmltextfield' and 'htmltextarea'
attributes.
-inp embedded.
May contain 'TD' or 'TH' HTML tags.
'$_' marks the insertion point for widget.
See also htmlddlb.
cmdchk
under -ins command. See also -upd, -del, -sav.
-lst) or form (-frm).
LNG is lngname
-lbl embedded.
May contain 'TD' or 'TH' HTML tags.
'$_' marks the insertion point for label.
-ins, -upd, -del -
for computed when saved fields, inside cmdchk.
Computed fields may be implemented via -frm sub.
See also -cdbC, -frm.
cmdchk
under -upd command. See also -ins, -del, sav.
Views description -lists slot contains hash ref:
set(-lists =>
{ view_name=>{ -lst, -lbl, -cmt,
-fields, -key, -listurm,
-dsub, -sub,
-where, -wherepar, -filter, -rowlst, -orderby, -groupby,
-href, -hrefc,
-htmlts, -htmlte, -width, -refresh,
-gant1, -gant2, -htmlg1
}
, ........} )
Default view should be marked with '_' at the end of the name or it will be the first in sorted order. Hidden (from list of views) view should be marked with '_' at the begin of the name.
lngname
-form and -flg.
-fltlst or -filter
-href => [ ?url, ?command_param, ?command_value ]
URL to open view entry, used with key parameters added
-key => [field,...]
Key fields to open list entry parameters
lngname
-lst operation
-listurm => [field,...]
Fields to include into hyperlinks to produce 'unread marks' behaviour, defaults are 'w'here key fields
-rowlst.
cmdlst
-fltlst or -filter,
-where, 'Query' screen condition or -wherepar.
Simple access control may be implemented with
-filter, -fltlst, -fltedt slots, that are
additions to SQL statement 'where' clause.
Operations with single record (-sel, -ins, -upd, -del)
may use -acd slot described below.
Only this slot may be used with File Store Description.
set(-acd=>{-read, -readsub, -sread, -write, -swrite, -oswrite})
-read and -sread.
Record versioning description related slots are
-keyfld - single key field name and
-vsd - version store description.
-vsd slots
-npf, -cof, -cvd, -sf, -svd, -sd, -uuf, -utf
are described below
-sf
-sf) eq -svd
-sf value
-sav -form field attribute may be used instead.
-sav -form field attribute may be used instead.
Attached to records files storing related slots are
-keyfld - single key field name and
-fsd - file store description.
-fsd slots
-path, -vspath, -urf, -url, -vsurf, -vsurl, -ksplit, -acl
are described below
fsacl
-filter, -fltsel, -fltlst, -fltedt, -opflg,
-listrnm, -lboxrnm, -listurm
-svd or -cvd.
Files editing is always available in applications without
versioning (-vsd settings).
-fsd)
and under Version Store (-vsd) may be involved by
opening template record, editing it,
choosing 'edit' (-svd) record state,
invoking 'Insert' action.
Creation record by template could not be implemented via
'template' -> 'edit' -> 'Update' transition, because of
template's own 'edit' state. So, 'template' -> 'non-template' -> 'Update'
transition should only be applied to template record.
Access Control Description
cmd
cnd calls
cmdCCC call.
Commands are described in TRANSACTION SEQUENCE above.
cmd calls
cmd, available via cmdg.
This is any -cmd command except form exchanges -
-frm, -ins, -upd commands.
cnd
-fltsel, -fltlst, -fltedt takes precedence
Form Description
File Store Description
-lists,
'$_' is placeholder.
'%$_' placeholder prepends search string with '%' sign to use in 'LIKE'.
Example: '(col1 LIKE %$_ OR col2 LIKE %$_)'.
Example: 'MATCH (col1,col2,...) AGAINST ($_)'.
cmdsql SQL 'INSERT', 'UPDATE', or 'DELETE' statement
cmdlst or cmdsel or cmdsql SQL statement 'FROM' clause
cmdlst or cmdsel SQL 'SELECT' statement to list record(s)
cmdlst SQL 'SELECT' statement to get margins for
Gant (timeline) chart using min(-gant1) and max(-gant2).
cmdlst SQL 'SELECT' statement to display above records.
cmdlst or cmdsel or cmdsql SQL statement 'WHERE' clause
keyfld
htmlddlb
Views Description
-lst operation
-listurm => [field,...]
Fields to include into hyperlinks to produce 'unread marks' behaviour, defaults are 'w'here key fields
-tbarl
htmlbar;
'a'll:
'c'reate/'i'nsert,
'e'dit/'u'pdate,
'd'elete record;
's'elect record (?), '!s'elect record button;
'v'iew record, '!v'iew record mode;
'l'ist records, 'q'uery condition
-upd and -del operation
-lst operation
-cmdCCC subs{}.
May be used for access control.
-rowlst is evaluated for each row in cmdlst to filter rows to display.
-rowedt controls record edit appearance.
-rowupd and -rowdel (and -rowsav in this cases)
are evaluated when previous values
of fields are fetched to -pxpv parameters.
-rowsav, but immediatelly before or 'a'fter database (SQL) command
generation and execution.
Actual field values to be used for or 'a'fter database command are
available as CGI params.
-rowsav1 is used for edited record only
(new value fieldnames not prefixed).
-rowsav2 is used in each cmdsql call
(new value fieldnames may be prefixed).
-rowsel, but immediatelly 'a'fter database (SQL SELECT) command
generation and execution.
Field values are available as CGI params,
both previous (-pxpv) and pending.
-rowsel1a is used while editing record operation only
(when previous field values selected).
-rowsel2a is used in each interactive cmdsel call
(previous or current field values selected).
-rowsel3a is used after each record fetch using cmdsel call.
-htm operation
-htm operation
Version Store Description
Common methods: qparampv, qparamsw, qparampx, htmlself,
cmd, cmdg,
htmlbar, htmlhid, htmlres, htmlself,
htmlddlb, htmllst,
eval, evaluate
Simplest programming interface: cmd, cnd, and above
Usable programming interface: evaluate, eval, cmd
Advanced usable: cmdchk,
cmdsql, cmdins, cmdupd, cmddel, cmdsel,
cmdcrt, cmdqry, cmdhtm, cmdfrm, cmdlst,
acltest, aclsel,
and above
-acd
data or field names given.
'-t' option (default) is used to 't'est with acltest('-lst')
if condition needed (user not in -sread or -swrite).
'-and' option produces ' AND' prefix before not empty condition.
'-not' option produces 'AND ... NOT IN' behaviour.
User names array ref replaces default or current user names list. Sub{}($self,$field,$users) will be evaluated for the field name followed. m/^\$_(regexp|rlike)$/i value produces 'REGEXP' or 'RLIKE' MySQL-like expression on user names to match the field followed. m/.*\$_.*/ value is treated as a template string to be used for the field followed, with '$_f' and '$_u' placeholders for field and user names.
-chk subs{}.
Calculate fields values with -frm, -sav,
-ins, -upd, -del subs{}
before insert, update, delete
-crt default values for new record
cmdsel(undef,-pxpv) to fetch previous values
and cmdsql('-del',opt,@_) to update database.
Options: '!s'elect, '!v'ersion.
-cmd command
except form exchanges like -frm, -ins, -upd.
htmlbar and htmlhid.
cmdsql('-ins',opt,@_).
Options: none
cmdlst call may be used inside -cmdfrm custom sub{}
to embed view -
with '-gxm!q' options, 'view' and 'where' args,
under cmd(-sel) condition:
set(-cmdfrm =>sub{
my $s =shift;
$s->cmdfrm(@_);
if ($s->cmd('-sel')) {
$s->cmdlst('-gxm!q','All Versions','gwo.idnv=' .$s->dbi->quote($s->param('id')))
}
});
-qry default values for query parameters form
cmdlst('g');
'-!q' cmdlst option may be used.
If sub{} is omitted, DBI statement handle executed will be returned.
cmdsel and other methods may be called within sub{}.
cmdscan may be used to program data improvements or corrections.
cmdscan and return first row in hash ref or empty value
-pxpv.
Default new values params names prefix is empty.
cmdsel(undef,-pxpv) to fetch previous values
and cmdsql('-upd',opt,@_) to update database.
Options: '!s'elect, '!v'ersion.
-lst transaction.
Field values are given from CGI params.
Field formats may be
empty, quote (``''', '``'), sub, string template with '?' placeholder for value.
htmlres, commit or rollback on errors.
Default procedure is cmd('-cmd').
See also evaluate
eval, but with starting HTTP,
starting and ending HTML page.
Uses '-htpgstart' and '-htpfstart' from parent CGI::Bus
-opflg,
current transaction, -tbarl , -tbarr.
-inphtml.
Optional 'widget' parameter (html, '$_' placeholder, any other string or empty value),
is used only to be placed after some javascript may be generated.
Name is used as the common part of names of the HTML widgets - submits,
scrolling_list, buttons, which names are generated by appending '_' sign
and suffix.
Data may be array ref with list of values,
hash ref with internal and external values,
list name in -lists,
SQL Select statement,
sub{} to produce above.
Container may be used with list name or SQL Select, it may be [] or {}.
Feed sub{} may be used with container to fill it.
Other arguments are field names to fill with values.
Field names with leading ``\t'' corresponds to multivalue fields,
leading ``\tmsab\t'' hints to try Microsoft Address Book before if possible.
-href in Views Description.
Rows and columns joins are HTML delimiters to be inserted between rows
and columns.
cmd and TRANSACTION SEQUENCE for commands.
Attributes for 'A' HTML tag may be given as an array or hash ref.
-pxcb, -pxsw, -pxpv, -pxqc.
qparam call with -pxpv prefixed param names.
qparam call with prefixed param names.
Empty prefix means non-prefixed names.
qparam call with -pxsw prefixed param names.
New htmlddlb optional 'field' parameter.
New -rowsel1a, -rowsel2a, -rowsel3a slots.
-flg new 'f'etch flag used in cmdlst.
cmdlst binds now database columns to hash ref available from -rowlst and -clst.
-listurm Views Description SLOTS.
-listrnm Views Description slot used in cmdlst, alike common -listrnm
-wherepar view description slot
acltest - fixed record read delegation via -readsub:
it processes further only array refs from acl now,
false values will be ignored in loop, true - returned.
-htaccess subslot of -acd
cmdlst.
-inp Field Description: 'htmltextarea' URLs display behaviour
is extended with special URL protocol 'fsurl://' to be translated
to fsurl value.
cmdlst - fixed parsing of query condition parameters with
leading '<>=' operators
acltest - field may contain several user names delimited with commas with
spaces allowed. Regular expression is used instead of 'eq' comparison.
aclsel - sub{}, m/^\$_(regexp|rlike)$/i, m/.*\$_.*/
special parameters introduced
for the field name followed
-refresh view and common slots
-htmlts, -htmlte, -width common slots;
-width field slot.
-gant1 and -gant2,
so 'gwo.cgi' changed
-rowsav1 and -rowsav2 events inside cmdsql.
- review & test & debug
- how to move 'qparampv' and 'qparamsw' methods up to the application object?
- Values of SQL statement parameters are all binded inside SQL statement. - Access rights encounted with multiple 'field IN(usernames)' expressions if there are multiple fields with usernames. SQL may be too big and slow. - Multiple columns cannot be SQL Selected into multiple rows. Such views (lists) should be implemented with '-dsub' subs. F.e., list of user names in multiple user name fields. - Conditions like 'value IN (fields)' may be implemented only with WHERE query condition field - File attachments should be published via some access control scripts if application (password, not web server) authentication is used.
Andrew V Makarow <makarow at mail.com>
|
CGI::Bus::tmsql - SQL database user interface to view and edit data |