line
stmt
bran
cond
sub
pod
time
code
1
# AutoForm.pm
2
#
3
# $Id: AutoForm.pm,v 1.19 2005/04/11 17:02:35 scottb Exp $
4
#
5
6
package CGI::AutoForm;
7
8
1
1
6812
use strict;
1
3
1
36
9
1
1
905
use DBIx::IO::Mask;
1
33031
1
32
10
1
1
13
use DBIx::IO::Search;
1
8
1
20
11
1
1
6
use DBIx::IO::Table;
1
2
1
20
12
1
1
6
use DBIx::IO::GenLib ();
1
2
1
18300
13
14
15
*CGI::AutoForm::VERSION = \'1.05';
16
17
*CGI::AutoForm::DISPLAY_ONLY_GROUP = \'DISPLAY ONLY';
18
*CGI::AutoForm::INSERT_GROUP = \'INSERTABLE';
19
*CGI::AutoForm::EDIT_GROUP = \'DISPLAY EDIT';
20
*CGI::AutoForm::SEARCH_GROUP = \'SEARCHABLE';
21
22
*CGI::AutoForm::DEFAULT_RADIO_CHECKBOX_COLS = \2;
23
24
*CGI::AutoForm::DEFAULT_FIELD_LENGTH = \50;
25
26
*CGI::AutoForm::DEFAULT_MULTI_VALUE_DELIMITER = \':';
27
28
my @months = ('',qw(
29
January
30
February
31
March
32
April
33
May
34
June
35
July
36
August
37
September
38
October
39
November
40
December
41
));
42
my $month_field = {
43
FIELD_NAME => '_MONTH',
44
INPUT_CONTROL_TYPE => 'SELECT',
45
SEARCH_CONTROL_TYPE => 'SELECT',
46
};
47
my $month_picklist = [
48
map { { ID => $_, MASK => $months[$_] } } ('01'..'12')
49
];
50
51
my $day_field = {
52
FIELD_NAME => '_DAY',
53
INPUT_CONTROL_TYPE => 'SELECT',
54
SEARCH_CONTROL_TYPE => 'SELECT',
55
};
56
my $day_picklist = [
57
map { { ID => $_, MASK => int($_) } } ('01'..'31')
58
];
59
60
my $year_field = {
61
FIELD_NAME => '_YEAR',
62
INPUT_CONTROL_TYPE => 'SELECT',
63
SEARCH_CONTROL_TYPE => 'SELECT',
64
};
65
my $this_year = substr(DBIx::IO::GenLib::local_normal_sysdate(),0,4);
66
67
my $hour_field = {
68
FIELD_NAME => '_HOUR',
69
INPUT_CONTROL_TYPE => 'SELECT',
70
SEARCH_CONTROL_TYPE => 'SELECT',
71
};
72
my $hour_picklist = [
73
map { { ID => $_, MASK => $_ } } ('00'..'23')
74
];
75
76
my $min_field = {
77
FIELD_NAME => '_MIN',
78
INPUT_CONTROL_TYPE => 'SELECT',
79
SEARCH_CONTROL_TYPE => 'SELECT',
80
};
81
my $min_picklist = [
82
map { { ID => $_, MASK => $_ } } ('00'..'59')
83
];
84
85
my $rel_quantity_field = {
86
FIELD_NAME => '_QUANT',
87
INPUT_CONTROL_TYPE => 'TEXT',
88
SEARCH_CONTROL_TYPE => 'TEXT',
89
DATATYPE => 'INT',
90
INPUT_SIZE => 4,
91
INPUT_MAXLENGTH => 7,
92
};
93
94
my $rel_unit_field = {
95
FIELD_NAME => '_UNIT',
96
INPUT_CONTROL_TYPE => 'SELECT',
97
SEARCH_CONTROL_TYPE => 'SELECT',
98
};
99
my $rel_unit_picklist = [
100
{ ID => 'MINS', MASK => 'Minute(s)' },
101
{ ID => 'HRS', MASK => 'Hour(s)' },
102
{ ID => 'DAYS', MASK => 'Day(s)' },
103
{ ID => 'MONTHS', MASK => 'Month(s)' },
104
{ ID => 'YEARS', MASK => 'Years(s)' },
105
];
106
107
my $use_range_field = {
108
FIELD_NAME => '_UR',
109
INPUT_CONTROL_TYPE => 'CHECKBOX',
110
SEARCH_CONTROL_TYPE => 'CHECKBOX',
111
};
112
my $use_range_picklist = [
113
{ ID => 1, MASK => 'Check to use this date range criteria' },
114
];
115
116
my $start_range_field = {
117
FIELD_NAME => '_RS',
118
INPUT_CONTROL_TYPE => 'TEXT',
119
SEARCH_CONTROL_TYPE => 'TEXT',
120
};
121
122
my $end_range_field = {
123
FIELD_NAME => '_RE',
124
INPUT_CONTROL_TYPE => 'TEXT',
125
SEARCH_CONTROL_TYPE => 'TEXT',
126
};
127
128
=head1 NAME
129
130
CGI::AutoForm - Automated abstraction of HTML forms from a data source
131
132
=head1 SYNOPSIS
133
134
135
use CGI::AutoForm;
136
137
138
$form = new CGI::AutoForm($dbh,$form_name);
139
140
$form->action($action_url);
141
142
$form->add_group($CGI::AutoForm::EDIT_GROUP,$table_name);
143
144
$form->add_record($current_record);
145
146
$form_html = $self->prepare();
147
148
# insert $form_html into the BODY section of an (X)HTML document via a template
149
150
151
$group = $form->group_by_name($group_name);
152
153
$bool = $form->validate_query($query,$callback);
154
155
$records = $form->format_query($query);
156
157
$form_copy = $form->clone();
158
159
$form->reset_group();
160
161
162
#
163
# an example of customizing a data group's fields...
164
#
165
166
$rv = $form->add_group($CGI::AutoForm::INSERT_GROUP,undef,'Vote For Your Favorite Artist','ARTIST_VOTE');
167
168
$fields = $form->db_fields('ARTIST',$CGI::AutoForm::INSERT_GROUP);
169
170
$form->push_field($fields->{ARTIST_NAME});
171
172
$form->add_field( {
173
FIELD_NAME => 'VOTE',
174
INPUT_CONTROL_TYPE => 'RADIO',
175
REQUIRED => 'Y',
176
HEADING => 'Vote',
177
DATATYPE => 'CHAR',
178
INSERTABLE => 'Y',
179
},
180
[
181
{ ID => '1', MASK => '*', },
182
{ ID => '2', MASK => '**', },
183
{ ID => '3', MASK => '***', },
184
{ ID => '4', MASK => '****', },
185
]);
186
187
$form_html = $self->prepare( { 'ARTIST_VOTE.ARTIST_NAME' => 'Nonpoint', 'ARTIST_VOTE.VOTE' => 4 } );
188
189
190
191
=head1 DESCRIPTION
192
193
There are many CGI form abstractions available, (e.g. CGI.pm).
194
A unique and powerful advantage with this abstraction is that it can be tied closely
195
with a database schema. Each group of fields in the form can represent a database table (or view)
196
and the table/column properties and constraints are automagically discovered so your DBA can make DDL
197
changes that will be immediately reflected in the HTML forms (no duplication of the data dictionary in your code).
198
199
All user/operator input is checked tightly against database constraints and there is built-in magic
200
to provide convenient select lists, etc, and to enforce a discreet set of valid values against unique/primary keys in lookup tables
201
(see B). This means referential integrity even for MySQL. Metadata in MySQL's C and C types are also supported.
202
This also gives the operator a chance to correct mistakes with helpful hints instead of just getting a meaningless db error code.
203
204
This design allows you to get secure, database-driven web apps up and running is as little as a few
205
hours (see Cruddy! for an implementation L).
206
This is made possible with the help of the DBIx::IO abstraction, please refer to it for further details.
207
208
Another advantage this abstraction provides is the separation of presentation and style using style sheets and having human-friendly presentation
209
attributes stored in a database table that can be managed by non-engineers.
210
211
Typical CGI apps are characterized by collecting, updating, reporting and formatting data using forms and tables.
212
Form creation and processing can be divided into the following tasks:
213
214
1) Deciding what data to collect in order to perform the desired function.
215
216
2) Deciding how the operator will convey the desired information (input fields, checkboxes etc).
217
218
3) Form layout.
219
220
4) Imposing integrity constraints on the collected data.
221
222
5) Presentation and style.
223
224
6) Directing the collected data.
225
226
This class allows (but doesn't force) form elements and constraints to be defined in a database.
227
This way, the definitions are organized in a central repository, so they can be managed
228
in a shared environment separate from the code. Vanilla HTML is generated and several HTML classes
229
are used so that presentation and style can be dictated with style sheets (again separate from the code).
230
For flexibility, methods are given to modify form definitions and layout programmatically as well.
231
232
=head1 DETAILS
233
234
=head2 Form object structure
235
236
A form contains of a list of data groups. Each data group contains a list of form fields, and
237
a list of 0 or more data records that correspond to the form fields. Each form field is a hash
238
of attributes describing how the field should be rendered as a form element along with constraints, access controls and such.
239
240
A form object is blessed and will be referred throughout these docs as $form. Of its many attributes,
241
it holds an arrayref and a hashref of data groups, each referred to as $group. Each $group is
242
a (non-blessed) object-like hashref (sub-object) and of its many attributes, it holds an arrayref
243
and a hashref of data fields, each referred to as $field. Each $field in turn is a (non-blessed) object-like
244
hashref (sub-object) as well. Because these sub-objects are stored as a list and a hash by their parent,
245
it is best to use the methods provided when mutating the list/hash structures themselves. However,
246
modifying individual sub-object attributes may be done by accessing the hash keys directly (or iterating the lists).
247
248
If using a database on the backend to manage form data, a data group would correspond to a table,
249
each of its fields would correspond to columns, likewise, with the group's list of records.
250
251
One of the primary attributes of a data group is its type or usage and must be one of the following:
252
253
=over 2
254
255
=item Insert
256
257
Gather and validate data, e.g. for subsequent database insert.
258
259
=item Display/Update
260
261
Display a record with certain fields available for editing.
262
263
=item Search
264
265
Gather criteria for running a database report (with the help of DBIx::IO::Search).
266
267
=item Display Only
268
269
Display read-only data, e.g. a database report generated from a search form.
270
271
=back
272
273
Each of these types is referred to as $usage throughout these docs (see C for defined constants).
274
275
Each form object will be in a certain state with respect to the groups, fields
276
and records associated with it. The state is defined in terms of the current
277
group#, record# and field#. The state is altered when adding a new data group to a form or through the iterative
278
methods such as C, C, C. C will zero these
279
state properties. State is important for methods such as C, C
280
and such. Methods dependent on state will be annotated accordingly.
281
282
283
=head2 Form field attire
284
285
Form field attributes may be kept and managed in a database table (see contrib/ for DDL contained in the Cruddy! distribution L).
286
The default name of the table
287
that holds these attributes is C and may be overridden per $form object by setting $form->{attr_defn_table_name}.
288
NOTE MySQL users! If your database has case-sensitive table names then pay attention to this name.
289
290
Each record in C describes attributes/constraints of a single field in an HTML form. Each HTML form field
291
usually corresponds to a table column (or a view column for search forms or possibly updatable views otherwise).
292
The data dictionary will be queried if a form's field group corresponds to a table
293
that does not have any fields defined in C and appropriate default attributes will be used (schema auto-discovery).
294
NOTE! however that this is all or nothing - if using a database table to store form data and that table has at least one field defined in C, then
295
all fields in that table must have a record in C or bad things can happen. The exception here are Oracle LOB type fields
296
(BLOB, etc) - these fields are completely ignored by this module.
297
298
You may also elect to leave some of the fields in C NULL and they will be taken from the data dictionary. This is recommended
299
to avoid data duplication/syncing issues; essentially, it allows
300
the DBA to make changes that will be automagically reflected in the HTML forms (see C).
301
302
The following is a list of fields in C and how each influences the form HTML, presentation, access control, constraint checking, etc:
303
304
=over 2
305
306
=item TABLE_NAME
307
308
UPPER CASE name of a table (or group of fields) that corresponds to a data group of a form object
309
310
=item FIELD_NAME
311
312
UPPER CASE name of a field associated with table_name
313
314
=item APPEAR_ORDER
315
316
Relative order the fields will appear in the form's data group (integer, recommended to use sequence of 10's - 10, 20, 30...)
317
318
=item HEADING
319
320
User-friendly name of FIELD_NAME that will appear in the form
321
322
=item SEARCHABLE
323
324
True/false - allows this field to appear in a search group
325
326
=item UPDATABLE
327
328
True/false - allows this field to be updated (e.g. set false for a primary key sequence)
329
330
=item INSERTABLE
331
332
True/false - allows this field to be defined by the operator for inserts (e.g. set false for auto-generated primary key sequence)
333
334
=item INPUT_CONTROL_TYPE
335
336
Form control type for operator input (update/insert groups) one of: TEXT, TEXTAREA, PASSWORD, DATE, DATETIME, FILE, SELECT, RADIO, CHECKGROUP
337
338
=item MULTI_INSERT_DELIMITER
339
340
For the CHECKGROUP input control, will insert multiple values as one field with each value serialized by this string (deserialized automagically on display)
341
342
=item SEARCH_CONTROL_TYPE
343
344
Form control type for search groups, one of: SELECT, CHECKBOX, RADIO, TEXT, MATCH TEXT, COMMALIST, DATE, DATETIME, DATERANGE, DATETRANGE (query on a range of date + time), RANGE
345
346
=item SEARCH_MULT_SELECT
347
348
For search groups, allow multi-select on a select box (values will be OR'ed using the IN operator) accepts an integer that gives the height of the control
349
350
=item USE_DATA_DICT
351
352
If true, datatype, default_value, required, input_size and input_maxlength will be taken from the data dictionary if those fields are NULL (recommended to use this whenever possible)
353
354
=item DATATYPE
355
356
Datatype constraint for this field, one of CHAR, DATE, NUMBER, DATETIME, INT, INT UNSIGNED
357
358
=item DEFAULT_VALUE
359
360
Default value for insert groups, can be the special value _SYSDATE for date types, meaning insert the current date
361
362
=item REQUIRED
363
364
True/false constraint - is empty input (NULL) acceptable?
365
366
=item INPUT_SIZE
367
368
For input_control_type of TEXT or PASSWORD - the width of the control box
369
370
=item INPUT_MAXLENGTH
371
372
For input_control_type of TEXT, PASSWORD - the maximum length of text that can be entered
373
374
=item BRIEF_HEADING
375
376
Short, user-friendly heading for the brief tabular display results (see B)
377
378
=item ALT_MASK_FIELD
379
380
For auto-recognition of associated lookup table, an alternative name for the USER_MASK field (see B)
381
382
=item MASK_TABLE_NAME
383
384
For configured recognition of associated lookup table, the name of the table with primary keys that match this foreign key (see B)
385
386
=item MASK_FIELD_NAME
387
388
For configured recognition of associated lookup table, the name of the field that contains the user-friendly values (see B)
389
390
=item ID_FIELD_NAME
391
392
For configured recognition of associated lookup table, the name of the field that contains the ID values as a unique or primary key (see B)
393
394
=item NO_CACHE
395
396
True/false - use this for caching of the lookup table. If the records in the lookup table change frequently set this to TRUE. The default is FALSE,
397
which will cache the lookup table
398
399
=item RADIO_CHECKBOX_COLS
400
401
Integer value of the number of columns of buttons/controls for RADIO, CHECKBOX and CHECKGROUP controls.
402
403
=item FIELD_GROUP
404
405
Special considerations for a group of controls that govern a single field, only supported value is CONFIRM, which is useful for PASSWORD fields
406
407
=item ELEMENT_ATTRS
408
409
Additional attributes that will be added to the HTML control element (e.g. enter 'rows="5" cols="10"' to size a TEXTAREA control box)
410
411
=item HELP_SUMMARY
412
413
The heading of this field will be an active link and when clicked, will render this summary as a js alert giving useful hints to the operator about the use of this field
414
415
=back
416
417
Notes:
418
419
All values in TABLE_NAME and FIELD_NAME must be UPPER CASE. If you have use an RDBMS where table names are case-sensitive (MySQL on Linux/UNIX)
420
and you have two or more tables with the same name but different letter cases, this is probably not a good idea to begin with but there is no workaround.
421
422
True/false fields accept 'Y', 'N' or NULL only (NULL => false).
423
424
Doesn't make much sense to have a value for C if C AND C are set FALSE.
425
426
Values left NULL will be given reasonable defaults.
427
428
If C is used for C, two controls will be presented and the values entered into each must match. This is useful for PASSWORD fields
429
where the operator can't see the input or other important fields that can be mistyped (email TEXT box, for example).
430
431
For a well-defined set of records (with a common C, where C is FALSE) C does not have to refer to
432
a database table or any RDBMS entity at all. Feel free to make up a schema that doesn't even exist - I've done this to
433
manage an LDAP tree on the backend. You can even create your own data groups/controls that don't exist in C by defining a record in a perl hash variable with
434
the keys of the hash being field names of C (in UPPER CASE, example given in B).
435
436
You'll want to set C TRUE if the data group is supported by a database table so that properties/constraints are
437
automagically taken from the database schema.
438
439
For updates/inserts of submitted data, you may want to use only those fields that are updatable/insertable;
440
e.g. $table->insert({ %{$rec}{ map { $_->{UPDATABLE} eq 'Y' ? $_->{FIELD_NAME} : () } @$field_list } }).
441
442
For search groups, multiple values selected in a select box or checkbox will be OR'ed together using the C SQL operator.
443
444
Either alt_mask_field is populated or (mask_table_name, mask_field_name, id_field_name) as a group is populated (or none at all).
445
See B for details.
446
447
Careful with NO_CACHE as it can be confusing - the default (false) is to cache the underlying lookup table for the set of acceptable values.
448
If you have meta-data that changes on a daily basis (which is many times the case) set this to true ('Y').
449
450
This module caches data dictionary info from the database. So if you're using a persistent interpreter (mod_perl), you'll
451
need to restart the webserver to recognize changes to the database structures (DDL modifications).
452
453
Clarification on RADIO, CHECKBOX and CHECKGROUP control types.
454
455
A RADIO set of controls only allows one value to be selected. CHECKBOX allows multiple values to be selected and is valid for C
456
where the values are or'd together in the search criteria (SELECT with C set to an integer value will have the same behavior).
457
CHECKGROUP allows for multiple values on INSERT and UPDATES such that these values are serialized into one field by the value of C.
458
If you use CHECKGROUP then you'll want to set C or the default value will be used, which is not what you want.
459
This behavior is very similar to the MySQL C data type. In fact you should be using CHECKGROUP for any MySQL C fields, in which
460
case the value for C is optional and ignored anyway because MySQL always uses a comma.
461
462
Using CHECKBOX (or SELECT with C set to an integer value) for C where C is set to CHECKBOX will
463
probably not do what you want. If you want to do subset searching within a field of multiple values, use C, which will
464
accept C<%> as a wildcard.
465
466
Tip: use a select list for C and text input box for C for tables with numeric ID's as
467
the primary key. This will give a select list when doing a search masked with readable names and will give the numeric
468
ID value on inspection (not recommended for large tables as the select list becomes too large).
469
470
=head2 Select lists & ID masking
471
472
There is a significant amount of magic to mask ID values
473
with related lookup tables (meta-data) and verify referential integrity thereof. Take the example of a schema model of a CD
474
collection:
475
476
ARTIST
477
+-------------------------+
478
| artist_id |
479
| artist_name (user_mask) |
480
| ... |
481
+-------------------------+
482
|
483
|
484
|
485
TITLE /|\
486
+--------------+
487
| artist_id |
488
| title |
489
| year |
490
| ... |
491
+--------------+
492
493
In this simple example, you'd want to join these tables and present C to the operator rather
494
than the meaningless-to-humans C. The magic starts by specifying a discreet HTML form control type
495
(C) e.g. SELECT or RADIO where table_name = 'TITLE' and field_name = 'ARTIST_ID'.
496
This is enough to automagically populate the control
497
with values from the related lookup table (ARTIST) with the meaningful artist names and will put a constraint check
498
on the server side (I claimed "high" security after all) to verify referential integrity.
499
The masked values will then be translated back on insert/update.
500
501
This magic occurs when an appropriate control type is used and when there is a singular foreign key
502
where the foreign key column name (with the C<_ID> suffix stripped off, if present) matches a table name containing the unique/primary keys.
503
Additionally, the ID field name in the lookup
504
table must match the foreign key name and the human-friendly mask field must be named C.
505
506
In this example above, all conditions are met except the mask field name is C (not C) so we'll
507
populate the set of fields (mask_table_name, mask_field_name, id_field_name) with ('ARTIST', 'ARTIST_ID', 'ARTIST_NAME') respectively where field_name = C
508
and table_name = C
509
for ultimate control over table/field names of related lookup tables.
510
511
An example where you might use just alt_mask_field instead of the set (of fields (mask_table_name, mask_field_name, id_field_name) is perhaps
512
if you have a table COUNTRY with fields (country, user_mask) where country is the country code and user_mask is the country name
513
and a field in ARTIST (ARTIST.ORIGIN_COUNTRY) you'd simply set UI_TABLE_COLUMN.ALT_MASK_FIELD to 'COUNTRY' where field_name = C
514
and table_name = C and the magic will happen.
515
516
If the underlying RDBMS is MySQL some additional magic parses allowed values for C and C data types to obtain
517
this pick list (no related table with a foreign key is necessary).
518
519
If using a form control that demands a discreet set of values where none of the above conditions apply,
520
you must specify the list (see $pick_list under C).
521
522
This magic provides a great deal of convenience and security not only for translating ID values for human operators
523
but also for enforcing a discreet set of allowable values for certain form fields.
524
525
=head2 Tabular data groups
526
527
If passing a true value for $tabular to C, that group's data (via C) will be displayed
528
in a tabular form - one column for each field in the record (read-only). This is how you display multiple records in a data group.
529
The only fields that will be shown in a tabular view are the ones with a non-empty value for C.
530
531
If $tabular is false (the default), a vertical form with a field heading and field value on each line is produced;
532
each use HTML C