-
Notifications
You must be signed in to change notification settings - Fork 31
/
README
604 lines (480 loc) · 23 KB
/
README
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
NAME
Dancer::Plugin::SimpleCRUD - very simple CRUD
(create/read/update/delete)
DESCRIPTION
A plugin for Dancer web applications, to use a few lines of code to
create appropriate routes to support creating/editing/deleting/viewing
records within a database table. Uses CGI::FormBuilder to generate,
process and validate forms, Dancer::Plugin::Database for database
interaction and HTML::Table::FromDatabase to display lists of records.
Setting up forms and code to display and edit database records is a very
common requirement in web apps; this plugin tries to make something
basic trivially easy to set up and use.
SYNOPSIS
The following assumes that you already have a working Dancer app and
have put your database connection details in your "config.yml" to be
read by Dancer::Plugin::Database, which this plugin uses in order to
obtain a database connection.
# In your Dancer app,
use Dancer::Plugin::SimpleCRUD;
# Simple example:
simple_crud(
record_title => 'Widget',
prefix => '/widgets',
db_table => 'widgets',
editable => 1,
);
# The above would create a route to handle C</widgets>, listing all widgets,
# with options to add/edit entries (linking to C</widgets/add> and
# C</widgets/edit/:id> respectively) where a form to add a new entry or edit
# an existing entry will be created.
# All fields in the database table would be editable.
#
# There is also a view route, C</widgets/view/:id>, which shows all the values
# for the fields of a single database entry.
# A more in-depth synopsis, using all options (of course, usually you'd only
# need to use a few of the options where you need to change the default
# behaviour):
simple_crud(
record_title => 'Team',
prefix => '/teams',
db_table => 'team',
labels => { # More human-friendly labels for some columns
venue_id => 'Home Venue',
name => 'Team Name',
},
validation => { # validate values entered for some columns
division => qr/\d+/,
},
input_types => { # overriding form input type for some columns
supersecret => 'password',
lotsoftext' => 'textarea',
},
key_column => 'id', # id is default anyway
editable_columns => [ qw( venue_id name division ) ],
display_columns => [ qw( id venue_id name division ) ],
deleteable => 1,
editable => 1,
addable => 0, # does not allow adding rows
sortable => 1,
paginate => 300,
template => 'simple_crud.tt',
query_auto_focus => 1,
downloadable => 1,
foreign_keys => {
columnname => {
table => 'venues',
key_column => 'id',
label_column => 'name',
},
},
table_class => 'table table-bordered',
paginate_table_class => 'table table-borderless',
custom_columns => [
{
name => "division_news",
raw_column => "division",
transform => sub {
my $division_name = shift;
my $label = "News about $division_name";
$division_name =~ s/([^-_.~A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;
my $search = qq{http://news.google.com/news?q="$division_name"};
return "<a href='$search'>$label</a>";
},
column_class => "column-class",
},
],
auth => {
view => {
require_login => 1,
},
edit => {
require_role => 'Admin',
},
},
);
USAGE
This plugin provides a "simple_crud" keyword, which takes a hash of
options as described below, and sets up the appropriate routes to
present add/edit/delete options.
OPTIONS
The options you can pass to simple_crud are:
"record_title" (required)
What we're editing, for instance, if you're editing widgets, use
'Widget'. Will be used in form titles (for instance "Add a ...",
"Edit ..."), and button labels.
"prefix" (required)
The prefix for the routes which will be created. Given a prefix of
"/widgets", then you can go to "/widgets/new" to create a new
Widget, and "/widgets/42" to edit the widget with the ID (see
key_column) 42.
Don't confuse this with Dancer's "prefix" setting, which would be
prepended before the prefix you pass to this plugin. For example, if
you used:
prefix '/foo';
simple_crud(
prefix => 'bar',
...
);
... then you'd end up with e.g. "/foo/bar" as the record listing
page.
"db_table" (required)
The name of the database table.
"key_column" (optional, default: 'id')
Specify which column in the table is the primary key. If not given,
defaults to id.
"where_filter" (optional)
Specify one or more 'where' clauses to use to filter the table. For
example:
simple_crud(
prefix => 'bar',
where_filter => {user_id => 1000},
...
);
This would cause only rows with an user_id of 1000 to be displayed
in listings and search results, viewed, edited etc.
The "where_filter" parameter takes a hashref describing the WHERE
clause, as used by Dancer::Plugin::Database's "quick_select"
convenience method for example - see the where clause documentation
in Dancer::Plugin::Database::Core::Handle.
Alternatively, if the filter condition needs to be calculated at
runtime (for example, based on the logged in user calling it), then
you can provide a coderef which returns the WHERE clause hashref -
for instance:
where_filter => sub { { customer_id => logged_in_user()->{customer_id} } },
"db_connection_name" (optional)
We use Dancer::Plugin::Database to obtain database connections. This
option allows you to specify the name of a connection defined in the
config file to use. See the documentation for
Dancer::Plugin::Database for how multiple database configurations
work. If this is not supplied or is empty, the default database
connection details in your config file will be used - this is often
what you want, so unless your app is dealing with multiple DBs, you
probably won't need to worry about this option.
"labels" (optional)
A hashref of field_name => 'Label', if you want to provide more
user-friendly labels for some or all fields. As we're using
CGI::FormBuilder, it will do a reasonable job of figuring these out
for itself usually anyway - for instance, a field named "first_name"
will be shown as "First Name".
"input_types" (optional)
A hashref of field_name => input type, if you want to override the
default type of input which would be selected by CGI::FormBuilder or
by our DWIMmery (by default, password fields will be used for field
names like 'password', 'passwd' etc, and text area inputs will be
used for columns with type 'TEXT').
Valid values include anything allowed by HTML, e.g. "text",
"select", "textarea", "radio", "checkbox", "password", "hidden".
Example:
input_types => {
first_name => 'text',
secret => 'password',
gender => 'radio',
}
"validation" (optional)
A hashref of field_name => validation criteria which should be
passed to CGI::FormBuilder.
Example:
validation => {
email_address => 'EMAIL',
age => '/^\d+$/',
}
"message" (optional)
A hashref of field_name => messages to show if validation failed.
Default is "Invalid entry".
Example:
message => {
age => 'Please enter your age in years',
email => 'That is not a valid email address',
},
"jsmessage" (optional)
A hashref of field_name => message to show when Javascript
validation fails.
Default message is "- Invalid entry for the "$fieldname" field". See
above for example.
"sort_options" (optional)
A hashref of field_name => optionspec indicating how select options
should be sorted
This is currently a passthrough to CGI::FormBuilder's sortopts.
There are several built-in values:
NAME Sort option values by name
NUM Sort option values numerically
LABELNAME Sort option labels by name
LABELNUM Sort option labels numerically
See the documentation for "sortopts" in CGI::FormBuilder for more.
"acceptable_values" (optional)
A hashref of arrayrefs to declare that certain fields can take only
a set of acceptable values.
Example:
acceptable_values => {
gender => ['Male', 'Female'],
status => [qw(Alive Dead Zombie Unknown)],
}
You can automatically create option groups (on a field of type
"select") by specifying the acceptable values in CGI::FormBuilder's
"[value, label, category]" format, like this:
acceptable_values => {
gender => ['Male', 'Female'],
status => [qw(Alive Dead Zombie Unknown)],
threat_level => [
[ 'child_puke', 'Regurgitation', 'Child'],
[ 'child_knee', 'Knee Biter', 'Child'],
[ 'teen_eye', 'Eye Roll', 'Adolescent'],
[ 'teen_lip', 'Withering Sarcasm', 'Adolescent'],
[ 'adult_silent', 'Pointedly Ignore', 'Adult'],
[ 'adult_freak', 'Become Very Put Out', 'Adult'],
],
}
If you are letting FormBuilder choose the field type, you won't see
these categories unless you have enough options that it makes the
field into a select. If you want to see the categories all the time,
you can use the "input_types" option to force your field to be
rendered as a select.
"default_value" (optional)
A hashref of default values to have pre-selected on the add form.
Example:
default_value => {
gender => 'Female',
status => 'Unknown',
}
"editable_columns" (optional)
Specify an arrayref of fields which the user can edit. By default,
this is all columns in the database table, with the exception of the
key column.
"not_editable_columns" (optional)
Specify an arrayref of fields which should not be editable.
"required" (optional)
Specify an arrayref of fields which must be completed. If this is
not provided, DWIMmery based on whether the field is set to allow
null values in the database will be used - i.e. if that column can
contain null, then it doesn't have to be completed, otherwise, it
does.
"deletable"
Specify whether to support deleting records. If set to a true value,
a route will be created for "/prefix/delete/:id" to delete the
record with the ID given, and the edit form will have a "Delete
$record_title" button.
"editable"
Specify whether to support editing records. Defaults to true. If set
to a false value, it will not be possible to add or edit rows in the
table. See also "addable".
"addable"
Specify whether to support adding records. Defaults to the value of
"editable" if set, or true otherwise. If set to a false value, it
will not be possible to add rows in the table.
"sortable"
Specify whether to support sorting the table. Defaults to false. If
set to a true value, column headers will become clickable, allowing
the user to sort the output by each column, and with
ascending/descending order.
"paginate"
Specify whether to show results in pages (with next/previous
buttons). Defaults to undef, meaning all records are shown on one
page (not useful for large tables). When defined as a number, only
this number of results will be shown.
"display_columns"
Specify an arrayref of columns that should show up in the list.
Defaults to all.
"template"
Specify a template that will be applied to all output. This template
must have a "simple_crud" placeholder defined or you won't get any
output. This template must be located in your "views" directory.
Any global layout will be applied automatically because this option
causes the module to use the "template" keyword. If you don't use
this option, the "template" keyword is not used, which implies that
any "before_template_render" and "after_template_render" hooks won't
be called.
"query_auto_focus"
Specify whether to automatically set input focus to the query input
field. Defaults to true. If set to a false value, focus will not be
set. The focus is set using a simple inlined javascript.
"downloadable"
Specify whether to support downloading the results. Defaults to
false. If set to a true value, The results show on the HTML page can
be downloaded as CSV/TSV/JSON/XML. The download links will appear at
the top of the page.
"foreign_keys"
A hashref to specify columns in the table which are foreign keys;
for each one, the value should be a hashref containing the keys
"table", "key_column" and "label_column".
"custom_columns"
An arrayref of hashrefs to specify custom columns to appear in the
list view of an entity. (Previously, this was just a hashref of
column names and specs, and this style is still supported for
backwards compatibility, but is deprecated because it leaves the
order of the columns unpredictable.)
The keys of each hash are "name", the name to use for this custom
column, "raw_column" indicating a column from the table that should
be selected to build the custom column from, "transform", a subref
to be used as a HTML::Table::FromDatabase callback on the resulting
column, and "column_class", to specify a CSS class for the the
column. "column_class" is optional, and if no "transform" is
provided, sub { return shift; } will be used.
If your custom column has the same name as an existing column, your
customizations will be used in-place to override the display of the
content in that column. If sorting is enabled, the column will be
sorted by the underlying database content for that row, and not by
the output of your transform function.
For a somewhat spurious example:
...
custom_columns => [
{
name => 'email_provider',
raw_column => 'email',
transform => sub {
my $value = shift;
return (split /@/, 1)[1];
},
column_class => 'column-class',
},
],
...
The "transform" code ref is passed to HTML::Table::FromDatabase as a
callback for that column, so it can do anything a
HTML::Table::FromDatabase callback can do. In particular, the
coderef will receive the value of the column as the first parameter,
but also a reference to the whole row hashref as the second
parameter, so you can do a variety of cunning things.
An example of a custom column whose "transform" coderef uses the row
hashref to get other values for the same row could be:
...
custom_columns => [
{
name => 'salutation',
raw_column => 'name',
transform => sub {
my ($name_value, $row) = @_;
return "Hi, $row->{title} $name_value!";
},
}
],
...
"auth"
You can require that users be authenticated to view/edit records
using the "auth" option to enable authentication powered by
Dancer::Plugin::Auth::Extensible.
You can set different requirements for viewing and editing, for
example:
auth => {
view => {
require_login => 1,
},
edit => {
require_role => 'Admin',
},
},
The example above means that any logged in user can view records,
but only users with the 'Admin' role are able to create/edit/delete
records.
Or, to just require login for anything (same requirements for both
viewing and editing), you can use the shorthand:
auth => {
require_login => 1,
},
"table_class"
This provides a CSS class for the tables.
"paginate_table_class"
This provides a CSS class for the tables paginate buttons.
DWIMmery
This module tries to do what you'd expect it to do, so you can rock up
your web app with as little code and effort as possible, whilst still
giving you control to override its decisions wherever you need to.
Field types
CGI::FormBuilder is excellent at working out what kind of field to use
by itself, but we give it a little help where needed. For instance, if a
field looks like it's supposed to contain a password, we'll have it
rendered as a password entry box, rather than a standard text box.
If the column in the database is an ENUM, we'll limit the choices
available for this field to the choices defined by the ENUM list.
(Unless you've provided a set of acceptable values for this field using
the "acceptable_values" option to "simple_crud", in which case what you
say goes.)
Hooks
Hooks are provided, which can be used in the normal Dancer way, using
the "hook" keyword.
add_edit_row (deprecated, use add_edit_row_pre_save)
You can use the same code from your add_edit_row hook in an
add_edit_row_pre_save hook. The only modification is that the new hook
passes the editable params as a key of the first argument (called
"params"), rather than as the first argument itself. So, if your hook
had "my $args = shift;", it could just use "my $args = shift->{params};"
and it should work the same way.
add_edit_row_pre_save, add_edit_row_post_save
These fire right before and after a row is added/edited; a hashref is
passed with metadata such as the name of the table (in "table_name"),
the args from the original route setup ("args"), the table's key column
("key_column"), and the values of the editable params ("params").
In the post-save hook, you are also sent "success" (the return value of
quick_insert or quick_update) telling you if the save was successful
(which is a little redundant because your post-save hook won't be called
unless the insert or update was successful). You'll also get "dbh"
giving you the instance of the handle used to save the entity (so you
can access last_insert_id()), and "verb" (currently either 'create new'
or 'update').
For instance, if you were dealing with a users table, you could use the
pre_save hook to hash the password before storing it - assuming for the
sake of example that you have a "hash_pw()" function to return a hashed
password:
hook add_edit_row_pre_save => sub {
my $args = shift;
if ($args->{table_name} eq 'user') {
$args->{params}{password} = hash_pw($args->{params}{password});
}
};
delete_row_pre_delete, delete_row_post_delete
These fire right before and after a row is deleted. As with the
add_edit_row_pre_save and add_edit_row_post_save hooks, these are passed
a hashref with metadata such as the name of the table (in "table_name"),
the args from the original route setup ("args"), the table's key column
("key_column"), and the values of the editable params ("params"). As
with the post-save hook, delete_row_post_delete hook won't be called if
we weren't able to delete the row.
You could use these to clean up ancillary data associated with a
database row when it was deleted, for example.
AUTHOR
David Precious, "<[email protected]>"
ACKNOWLEDGEMENTS
Alberto Simões (ambs)
WK
Johnathan Barber
saberworks
jasonjayr
Paul Johnson (pjcj)
Rahul Kotamaraju
Michael J South (msouth)
Martijn Lievaart
Josh Rabinowitz
BUGS
Please report any bugs or feature requests to
"bug-dancer-plugin-simplecrud at rt.cpan.org", or through the web
interface at
<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Dancer-Plugin-SimpleCRUD
>. I will be notified, and then you'll automatically be notified of
progress on your bug as I make changes.
CONTRIBUTING
This module is developed on Github:
http://github.com/bigpresh/Dancer-Plugin-SimpleCRUD
Bug reports, ideas, suggestions, patches/pull requests all welcome.
Even just a quick "Hey, this is great, thanks" or "This is no good to me
because..." is greatly appreciated. It's always good to know if people
are using your code, and what they think.
SUPPORT
You can find documentation for this module with the perldoc command.
perldoc Dancer::Plugin::SimpleCRUD
You may find help with this module on the main Dancer IRC channel or
mailing list - see http://www.perldancer.org/
You can also look for information at:
* RT: CPAN's request tracker
<http://rt.cpan.org/NoAuth/Bugs.html?Dist=Dancer-Plugin-SimpleCRUD>
* AnnoCPAN: Annotated CPAN documentation
<http://annocpan.org/dist/Dancer-Plugin-SimpleCRUD>
* CPAN Ratings
<http://cpanratings.perl.org/d/Dancer-Plugin-SimpleCRUD>
* Search CPAN
<http://search.cpan.org/dist/Dancer-Plugin-SimpleCRUD/>
LICENSE AND COPYRIGHT
Copyright 2010-16 David Precious.
This program is free software; you can redistribute it and/or modify it
under the terms of either: the GNU General Public License as published
by the Free Software Foundation; or the Artistic License.
See http://dev.perl.org/licenses/ for more information.