NAME

CGI::Bus::tmsql - SQL database user interface to view and edit data

SYNOPSIS

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;

DESCRIPTION

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

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
}

Transaction Sequence and Calls

-chk

Check or compute fields values before insert, update, delete operation - cmdchk call

-ins

Insert record into database - cmdins call

-upd

Update record in the database. Previous values parameters names have -pxpv prefix. cmdupd call.

-del

Delete record in the database. Previous values parameters names have -pxpv prefix. cmddel call.

-sel

Select record fields into CGI params to view or edit. Previous values parameters will be reseted. cmdsel call.

-crt

Create new record fields values into CGI params. Initiate to present new record form. cmdcrt call.

-qry

List query condition values initiate as CGI params - cmdqry call

-htm

HTML page begin output - print operations action bar and hidden HTML - cmdhtm call

-frm

Output HTML record form for query condition, view or edit - cmdfrm call

-lst

List records to user according to filter, view chosen, query condition given - cmdlst call

-end

Commit database transaction, the last command, implemented inside eval call along with htmlres and rollback.

DATA DESCRIPTION

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 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

Table Description

{ -tbl => table_name, -alias, -join, -joina, -joinw } element of Form Description -form slot

Required are -tbl and some of joins for joined tables

-alias

Table alias used in SQL selects

-join

Join operation in 'FROM' SQL 'SELECT' clause at the left of the table mention

-joina

Join operation in 'FROM' SQL 'SELECT' clause at the right of the table mention

-joinw

Join condition for 'WHERE' SQL 'SELECT' clause

-tbl

Table name in database

Field Description

{ -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.

-cdb

Convert param to database sub{}. Sub to convert field value (CGI param) to database form

-cdbC

Convert or compute field value sub{} on 'C' database command - 'i'nsert, 'u'pdate, or 'd'elete. In 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.

-cdbCa

Convert or compute field value sub{} after 'C' database command - 'i'nsert, 'u'pdate, or 'd'elete. In 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

-chk => "perl code"
-chk => sub{}
-chk => [ sub{}, message string ]

Check field value before -ins or -upd action - insert or update.

-clst

Convert sub{} to use in list. Sub{}(self, {field definition}, field data, {row data}) to convert value to HTML for use in list of records (-lst action). Default is nothing.

-cmt

-cmt_LNG

Comment to field. LNG is lngname

-colspan

'td' HTML tag attribute - number of form columns to occupy widget with

-crt

Create default field value. Default field value for new record creation (-crt action). Sub{} or value.

-cstr

Convert to string sub{}. Sub{} to convert value to external form - for use in list of records (-lst action) or record edit/view form (-frm action)

-col

Table column name. Database table column name of the field. May be database function call to use in SQL SELECT. Default is field name -fld

-del

Delete record field value, sub{} or value, computed inside cmdchk under -del command. See also -ins, -upd, -sav.

-fld

Field name, unique in the Form Description, used as CGI param name with or without prefix like -pxpv

-flg

Field flags string:

'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)

-frm

Compute field value sub{} to evaluate on -chk or -frm command. See also -sav

-hide

Hide field from Form (-frm) condition sub{}

-hidel

Hide field from Form (-frm) condition sub{}. Fields at the right will be shifted to the left.

-inp

Input or edit HTML widget description: -inp=>{?attributes} or -inp=>[input_type => {?attributes}]. Short form may be used to describe 'textfield' or 'htmltextfield', 'textarea' or 'htmltextarea', or 'popup_menu'. -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.

-inphtml

Input/edit widget HTML background. Value or sub{} to compute it. HTML code to place into form with HTML widget -inp embedded. May contain 'TD' or 'TH' HTML tags. '$_' marks the insertion point for widget. See also htmlddlb.

-ins

Insert field value, sub{} or value, computed inside cmdchk under -ins command. See also -upd, -del, -sav.

-lbl

-lbl_LNG

Field label. External name of the field to use in list of records (-lst) or form (-frm). LNG is lngname

-lblhtml

Label HTML background. Value or sub{} to compute it. HTML code to place into form with field label -lbl embedded. May contain 'TD' or 'TH' HTML tags. '$_' marks the insertion point for label.

-null

NULL or undefined value to present

-qry

Default query condition. Default value for query condition for the list of records. Sub{} or value.

-sav

Field value or sub{} to compute it before data save - -ins, -upd, -del - for computed when saved fields, inside cmdchk. Computed fields may be implemented via -frm sub. See also -cdbC, -frm.

-src

Source of value, RESERVED. Source value field for join, insert, edit

-upd

Update field value, sub{} or value, computed inside cmdchk under -upd command. See also -ins, -del, sav.

-width

Width of the data cell. Number of characters to use in lists. Or 'td' or 'th' HTML tag attribute to use in forms and lists

Views Description

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.

-cmdCCC

Transacion command 'CCC' hook subroutine reference. Commands and default operation are described in CGI::Bus::tm, see TRANSACTION SEQUENCE and -cmdCCC slot.

-cmt

-cmt_LNG

Comment text for view, string or array ref. LNG is lngname

-dsub

Data feeder sub{} to provide array ref of rows instead SQL SELECT

-fields

-fields => [field names to view] Field names to use in view, in left to right order. Default fields and order is specified by -form and -flg.

-filter

Filter 'WHERE' clause for SQL Select - string or sub{} - to use instead upper -fltlst or -filter

-gant1

-gant2

Datetime field names or SQL expressions to produce left and right valies to generate Gant (timeline) chart. Values returned should be ISO date-time 'yyyy-mm-dd hh:mm:ss', where time digits are optional.

-groupby

'GROUP BY' clause string for SQL 'SELECT'

-htmlts

-htmlte

Starting and ending records table HTML, including '<table >' tags.

-htmlg1

Gant (timeline) chart cell HTML, including '<td>' tags.

-href

-href => [ ?url, ?command_param, ?command_value ]

URL to open view entry, used with key parameters added

-hrefc

Last view column number inside hyperlink. Default is 0 - first column only; -1 means all columns.

-key

-key => [field,...]

Key fields to open list entry parameters

-lst

-lst_LNG

View name as displayed to user, default is key of view. LNG is lngname

-listrnm

View rows number default margin. Margin of the number of the rows returned by -lst operation

-listurm

-listurm => [field,...]

Fields to include into hyperlinks to produce 'unread marks' behaviour, defaults are 'w'here key fields

-orderby

'ORDER BY' clause string or array ref for SQL 'SELECT'. May be redefined by user in 'Query' screen.

-refresh

Refresh frequency for view HTML page, used to generate <meta http-equiv="refresh" CONTENT=XX>

-rowlst

Sub{}(self, [fields definitions], [row data], {row data}) to allow row to be included in the list displayed. To use instead of upper -rowlst.

-sub

View implementation sub{} instead cmdlst

-where

'WHERE' clause part string for SQL 'SELECT'. Complete 'WHERE' clause is formed with -fltlst or -filter, -where, 'Query' screen condition or -wherepar.

-wherepar

Default 'Query' screen 'WHERE' condition to be used as a part for SQL 'SELECT'.

-width

Width of view records table, in '&nbsp;' chars

Access Control Description

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})

-cC

Substitution for 'c' slot (see below) and 'C' command ('i'nsert, 'u'pdate, 'd'elete). Array ref is treated as usernames are used for insert, fieldnames otherwise.

-htaccess

Force to generate '.htaccess' files without Apache - boolean or sub{} condition

-oswrite

Sub{} or array ref with usernames of operation system writers, used for file access control settings, default are Windows 'System' and 'Administrators'

-read

Sub{} or array ref with fieldnames with reader usernames. Usernames may be delimited with commas or semicolons inside field.

-readsub

Sub{} to test record read permission, along with -read and -sread.

-sread

Sub{} or array ref with usernames of system readers

-swrite

Sub{} or array ref with usernames of system writers

-write

Sub{} or array ref with fieldnames with writer usernames. Usernames may be delimited with commas or semicolons inside field.

Version Store Description

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

-cof

Checked out pointer field name, RESERVED

-npf

New version pointer field name

-sd

State deleted value of -sf

-sf

State field name

-cvd

Condition version disable sub{}, used instead of condition param(-sf) eq -svd

-svd

State versioning disable -sf value

-utf

Update time field name. -sav -form field attribute may be used instead.

-uuf

Update user field name. -sav -form field attribute may be used instead.

File Store Description

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

-acl

Access control list set sub{}, may be used inside fsacl

-ksplit

Subdirectories size to split key value to, or splitter sub{}. Some file systems do not like very many entries in directory. 0 - do not split.

-path

Path to file store root directory

-urf

Filesystem URL of file store root directory, to form 'file://' URLs

-url

URL of file store root directory

-vspath

Path to separate store old versions, optional

-vsurf

Filesystem URL to separate store old versions, optional

-vsurl

URL to separate store old versions, optional

Other Descriptions

-filter, -fltsel, -fltlst, -fltedt, -opflg, -listrnm, -lboxrnm, -listurm

Usage Tips

File and Version Store

Access to files in File Store is direct, unlike data in database, where previous values of the record are available. So, files editing is available only during 'edit' record state, determined by -svd or -cvd. Files editing is always available in applications without versioning (-vsd settings).

Template Records with File and Version Store

There may be special template data records to create new records with. User should open template record, edit it as needed, and invoke 'Insert' action. Attached to template files inside File Store (-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.

Short Usernames

It is not comfortable for user to always input user names with domain name part, especially if domain is the same as user logged in. So, software may translate short user names to full, or use variants of the user names. But long and complex SQL queries are not comfortable for software. And domain name is not always natural part of the name of the person. So, user names should be short if possible.

SLOTS

-acd

Access control description hash ref. See Access Control Description

-cmd

Current transaction command cached by cmd

-cmdc

Current transaction command cached inside cnd calls

-cmdCCC

Transaction command 'CCC' hook subroutine reference. Default operation is cmdCCC call. Commands are described in TRANSACTION SEQUENCE above.

-cmde

Current transaction command edit state flag cached by cmd calls

-cmdg

Generic (general, global) transaction command cached by cmd, available via cmdg. This is any -cmd command except form exchanges - -frm, -ins, -upd commands.

-cnd

Transaction condition string generated by cnd

-filter

Filter 'WHERE' clause for all SQL commands - string or sub{}. -fltsel, -fltlst, -fltedt takes precedence

-fltedt

Filter 'WHERE' clause SQL 'UPDATE' and 'DELETE' commands - string or sub{}

-fltlst

Filter 'WHERE' clause SQL 'SELECT' command to list records - string or sub{}

-fltsel

Filter 'WHERE' clause SQL 'SELECT' command to view record - string or sub{}

-form

Form data description, see Form Description

-formtgf

Form Target Frame. Target frame to open form for create new or edit existed record. Undefined value means the same browser window as for list of records, '_BLANK' opens new browser window

-fsd

File store description hash ref. See File Store Description

-ftext

Full-text search expression template for use in query condition for -lists, '$_' is placeholder. '%$_' placeholder prepends search string with '%' sign to use in 'LIKE'. Example: '(col1 LIKE %$_ OR col2 LIKE %$_)'. Example: 'MATCH (col1,col2,...) AGAINST ($_)'.

-genedt

Generated by cmdsql SQL 'INSERT', 'UPDATE', or 'DELETE' statement

-genfrom

Generated by cmdlst or cmdsel or cmdsql SQL statement 'FROM' clause

-gensel

Generated by cmdlst or cmdsel SQL 'SELECT' statement to list record(s)

-genselg

Generated by cmdlst SQL 'SELECT' statement to get margins for Gant (timeline) chart using min(-gant1) and max(-gant2).

-genselt

Title of generated by cmdlst SQL 'SELECT' statement to display above records.

-genwhr

Generated by cmdlst or cmdsel or cmdsql SQL statement 'WHERE' clause

-htmlts

-htmlte

Starting and ending data (form or view) table HTML. Commonly used by default are '<table>' and '/<table>'.

-keyfld

Single key field name for versioning and file fields, cached by keyfld

-lboxrnm

Listbox rows number margin for htmlddlb

-lists

Views data description, see Views Description

-listrnm

View rows number default margin. Margin of the number of the rows returned by -lst operation

-listurm

-listurm => [field,...]

Fields to include into hyperlinks to produce 'unread marks' behaviour, defaults are 'w'here key fields

-logo

Logotype to place at the left of the toolbar. May be image URL or HTML. See also -tbarl

-opflg

Operations allowed letters: '<' leftmost left navigation action bar from 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

-pxcb

-pxcb => '_tcb_' - Transaction command or button name prefix

-pxqc

-pxqc => '_tsw_' - Special widget name prefix

-pxpv

-pxpv => '_tpv_' - Previous value parameter name prefix for -upd and -del operation

-pxqc

-pxqc => '_tqc_' - Query condition parameter name prefix for save by -lst operation

-refresh

Refresh frequency for all lists (views) of recods, used to generate <meta http-equiv="refresh" CONTENT=XX>

-rowlst

-rowsel

-rowedt

-rowsav

-rowins

-rowupd

-rowdel

Subs{} to allow or filter row operation given. Are used at a lower level, then -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.

-rowsav1

-rowsav2

-rowsav1a

-rowsav2a

Like -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).

-rowsel1a

-rowsel2a

-rowsel3a

Like -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.

-tbarl

Left toolbar HTML or HTML strings array ref for -htm operation

-tbarr

Right toolbar HTML or HTML strings array ref for -htm operation

-vsd

Version store description hash ref. See Version Store Description

-width

Width of data (form or view) table, in '&nbsp;' chars

METHODS

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

aclsel () -> SQL SELECT WHERE clause part

aclsel (?'-t', ?'-and', ?'-not', ?field name,...,?[user names],...)

Generates WHERE clause part for SQL SELECT from -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.

May be used in -fltlst, -fltsel, -filter.

cmd -> current command

cmd (-command) -> is command matched?

cmd (-cmd) -> execution of all commands required

Current transaction command

cmdchk () -> check before insert, update, delete

Check fields (CGI params) values before insert or update with -chk subs{}. Calculate fields values with -frm, -sav, -ins, -upd, -del subs{} before insert, update, delete

cmdcrt () -> params with default values

Create CGI params with -crt default values for new record

cmddel (?-opt) -> DBI delete record

Delete record in database using cmdsel(undef,-pxpv) to fetch previous values and cmdsql('-del',opt,@_) to update database. Options: '!s'elect, '!v'ersion.

cmdfrm () -> HTML form fields printed

Print HTML form fields for new, view or edit record

cmdg -> current generic command

cmdg (-command,...) -> is one of commands matched?

Current generic (general, global) transaction command is any -cmd command except form exchanges like -frm, -ins, -upd.

cmdhtm () -> top form HTML printed

Print HTML at the top of the list of records or form of record. By other words, print htmlbar and htmlhid.

cmdins (?-opt) -> DBI insert record

Insert new record into database using cmdsql('-ins',opt,@_). Options: none

cmdlst (?-opt, ?view name, ?where condition) -> list of records HTML printed

Generate and invoke DBI SQL SELECT according to filter, view choosen, query condition given. Print result set in HTML form. Option string signs: 'g'enarate, e'x'ecute, 'm'inimize display, '!q'uery params use, default is '-gx'.

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')))
    }
});

cmdqry () -> params with default values

Create CGI params with -qry default values for query parameters form

cmdscan (? 'select...' || list name, ? sub{}) -> stmt handle

cmdscan (?-cmdlstOpt, list name, ?cmdlst args, ? sub{}) -> stmt handle

Scan data like cmdlst and eval sub{} given. Default SQL SELECT is obtained with 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.

cmdscan1 ( cmdscan args ) -> first row

Execute cmdscan and return first row in hash ref or empty value

cmdsel (?opt, ?prefix) -> DBI select record into params

Generate and execute DBI SQL SELECT for single record, place data fetched into CGI params. Option string signs: 'g'enarate, e'x'ecute, default is 'gx'. Default params names prefix is empty.

cmdsql (?cmd, ?opt, ?pv_prefix, ?nv_prefix) -> DBI SQL executed

Generate and execute edit DBI SQL command - '-ins','-upd', '-del'. Option string signs: 'g'enarate, e'x'ecute, default is 'gx'. Default previous values params names prefix is -pxpv. Default new values params names prefix is empty.

cmdupd (?-opt) -> DBI update record

Update record in database using cmdsel(undef,-pxpv) to fetch previous values and cmdsql('-upd',opt,@_) to update database. Options: '!s'elect, '!v'ersion.

cnd -> current transaction command condition string

cnd (?-cmd, param => format,...) -> condition string

cnd (?-cmd, ?'+', ?'-and|or', ?'+and|or', ?'prefix.', param, ?'fieldname=', format,...) -> condition string

Makes dbi transaction command condition string or returns current condition string. '-+and|or' prepends or appends 'and' or 'or' to condition string generated if it is not empty. Field values may contain condition expression used for -lst transaction. Field values are given from CGI params. Field formats may be empty, quote ("'", '"'), sub, string template with '?' placeholder for value.

eval (?dbi connect parameters, ?sub{}) -> DBI transaction

Connect to the database, execute given sub{} in DBI transaction, print htmlres, commit or rollback on errors. Default procedure is cmd('-cmd'). See also evaluate

evaluate () -> operation requested by browser

Full CGI::Bus::tmsql execution. Like eval, but with starting HTTP, starting and ending HTML page. Uses '-htpgstart' and '-htpfstart' from parent CGI::Bus

htmlbar (?opt) -> action bar HTML

Generate action bar HTML string dependent on options or -opflg, current transaction, -tbarl , -tbarr.

htmlddlb (widget | false, name, data, field name,...) -> HTML drop-down list box

htmlddlb (widget | false, name, data, ?container, ?feed sub, field name,...)

Generate input helper drop-down list box HTML. May be used in -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.

htmlhid () -> hidden HTML

Generate hidden HTML string. Hidden HTML contains saved parameters values like previous values of fields.

htmllst (data, [display cols], {col=>parname}, [href], header, row join, col join, footer) -> Data HTML list

Produce HTML output of data. Data is SQL SELECT or array ref. Columns with display columns numbers will be displayed. Parameter column numbers will be used to form hyperlinks. Hyperlink hint is the same as -href in Views Description. Rows and columns joins are HTML delimiters to be inserted between rows and columns.

htmlres () -> transaction result HTML

Generate transaction result HTML string - empty or success or error message.

htmlself (command, param=>val,...) -> self command hyperlink HTML

htmlself (command, param=>val,..., {attributes})

htmlself ({attributes}, command, param=>val,...)

Generate self script command hyperlink HTML. See cmd and TRANSACTION SEQUENCE for commands. Attributes for 'A' HTML tag may be given as an array or hash ref.

pxnme ( prefix => name ) -> prefixed name

pxcb ( name ) -> command button name

pxsw ( name ) -> special widget name

pxpv ( name ) -> previous value name

pxqc ( name ) -> query condition name

Prefix field or param name given to construct special name. Prefixes are -pxcb, -pxsw, -pxpv, -pxqc.

qlst () -> queried list name

List (view) name queried or default

qparampv (name,...) -> previous value of param

qparam call with -pxpv prefixed param names.

qparampx (prefix) -> [field names]

qparampx (prefix,...) -> qparam prefixed

Get params names with prefix given or qparam call with prefixed param names. Empty prefix means non-prefixed names.

qparamsw (name,...) -> special widget value

qparam call with -pxsw prefixed param names.

VERSION

26/11/2004

New htmlddlb optional 'field' parameter.

17/11/2004

New -rowsel1a, -rowsel2a, -rowsel3a slots.

05/11/2004

Changed:

-flg new 'f'etch flag used in cmdlst.

25/10/2004

Changed:

cmdlst binds now database columns to hash ref available from -rowlst and -clst.

12/10/2004

New:

New -listurm Views Description SLOTS.

25/06/2003

Changed:

New -listrnm Views Description slot used in cmdlst, alike common -listrnm

21/05/2003

Fixed:

Excluded 'order by', 'group by', 'limit' clauses from gant (timeline) chart minimum and maximum margins SQL 'select' command to escape fields to be defined in 'select' list.

29/04/2003

New:

-wherepar view description slot

17/04/2003

Fixed:

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.

31/03/2003

New:

-htaccess subslot of -acd

17/11/2002

New:

'thead' and 'tbody' HTML tags in cmdlst.

31/10/2002

Changed:

-inp Field Description: 'htmltextarea' URLs display behaviour is extended with special URL protocol 'fsurl://' to be translated to fsurl value.

31/08/2002

Changed:

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

05/06/2002

New:

-refresh view and common slots

02/06/2002

New:

-htmlts, -htmlte, -width common slots; -width field slot.

Changed:

Timeline view supplied with predefined date margin columns from -gant1 and -gant2, so 'gwo.cgi' changed

16-17/05/2002

New:

-rowsav1 and -rowsav2 events inside cmdsql.

-htmlts, -htmlte, -gant1, -gant2, -htmlg1 view slots.

05/04/2002 - 06/05/2002

New:

MySQL 'LIMIT rows' clause generation

15/10/2001 - 23/03/2002

New:

Implemented and Documented.

ToDo:

- review & test & debug

Questions:

- how to move 'qparampv' and 'qparamsw' methods up to the application object?

Issues:

- 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.

AUTHOR

Andrew V Makarow <makarow at mail.com>

cpanm CGI::Bus

perl -MCPAN -e shell
install CGI::Bus