1 package Maypole::Model::CDBI;
6 Maypole::Model::CDBI - Model class based on Class::DBI
10 This is a master model class which uses L<Class::DBI> to do all the hard
11 work of fetching rows and representing them as objects. It is a good
12 model to copy if you're replacing it with other database abstraction
15 It implements a base set of methods required for a Maypole Data Model.
17 It inherits accessor and helper methods from L<Maypole::Model::Base>.
19 When specified as the application model, it will use Class::DBI::Loader
20 to generate the model classes from the provided database. If you do not
21 wish to use this functionality, use L<Maypole::Model::CDBI::Plain> which
22 will instead use Class::DBI classes provided.
26 use base qw(Maypole::Model::Base Class::DBI);
27 #use Class::DBI::Plugin::Type;
28 use Class::DBI::Loader;
29 use Class::DBI::AbstractSearch;
30 use Class::DBI::Plugin::RetrieveAll;
31 use Class::DBI::Pager;
32 use Lingua::EN::Inflect::Number qw(to_PL);
35 use Maypole::Model::CDBI::AsForm;
36 use Maypole::Model::CDBI::FromCGI;
37 use CGI::Untaint::Maypole;
41 Set the class you use to untaint and validate form data
42 Note it must be of type CGI::Untaint::Maypole (takes $r arg) or CGI::Untaint
45 sub Untainter { 'CGI::Untaint::Maypole' };
49 #use Class::DBI::FromCGI;
51 #sub Untainter { 'CGI::Untaint' };
54 __PACKAGE__->mk_classdata($_) for (qw/COLUMN_INFO/);
58 Action methods are methods that are accessed through web (or other public) interface.
62 If there is an object in C<$r-E<gt>objects>, then it should be edited
63 with the parameters in C<$r-E<gt>params>; otherwise, a new object should
64 be created with those parameters, and put back into C<$r-E<gt>objects>.
65 The template should be changed to C<view>, or C<edit> if there were any
66 errors. A hash of errors will be passed to the template.
70 sub do_edit : Exported {
71 my ($self, $r, $obj) = @_;
73 my $config = $r->config;
74 my $table = $r->table;
76 # handle cancel button hit
77 if ( $r->{params}->{cancel} ) {
79 $r->objects( [$self->retrieve_all] );
83 my $required_cols = $config->{$table}{required_cols} || [];
84 my $ignored_cols = $config->{$table}{ignore_cols} || [];
86 ($obj, my $fatal, my $creating) = $self->_do_update_or_create($r, $obj, $required_cols, $ignored_cols);
88 # handle errors, if none, proceed to view the newly created/updated object
89 my %errors = $fatal ? (FATAL => $fatal) : $obj->cgi_update_errors;
92 # Set it up as it was:
93 $r->template_args->{cgi_params} = $r->params;
95 # replace user unfriendly error messages with something nicer
97 foreach (@{$config->{$table}->{required_cols}}) {
98 next unless ($errors{$_});
101 $r->template_args->{errors}{ucfirst($_)} = 'This field is required, please provide a valid value';
102 $r->template_args->{errors}{$key} = 'This field is required, please provide a valid value';
103 delete $errors{$key};
106 foreach (keys %errors) {
109 $r->template_args->{errors}{ucfirst($_)} = 'Please provide a valid value for this field';
110 $r->template_args->{errors}{$key} = 'Please provide a valid value for this field';
113 undef $obj if $creating;
115 die "do_update failed with error : $fatal" if ($fatal);
116 $r->template("edit");
118 $r->template("view");
123 $r->objects( $obj ? [$obj] : []);
126 # split out from do_edit to be reported by Mp::P::Trace
127 sub _do_update_or_create {
128 my ($self, $r, $obj, $required_cols, $ignored_cols) = @_;
133 my $h = $self->Untainter->new( %{$r->params} );
137 # We have something to edit
138 eval { $obj->update_from_cgi( $r => {
139 required => $required_cols,
140 ignore => $ignored_cols,
142 $obj->update(); # pos fix for bug 17132 'autoupdate required by do_edit'
147 $obj = $self->create_from_cgi( $r => {
148 required => $required_cols,
149 ignore => $ignored_cols,
155 return $obj, $fatal, $creating;
159 # split out from do_edit to be reported by Mp::P::Trace
160 #sub _do_update_or_create {
161 # my ($self, $r, $obj, $required_cols, $ignored_cols) = @_;
166 # my $h = $self->Untainter->new( %{$r->params} );
170 # # We have something to edit
171 # eval { $obj->update_from_cgi( $h => {
172 # required => $required_cols,
173 # ignore => $ignored_cols,
175 # $obj->update(); # pos fix for bug 17132 'autoupdate required by do_edit'
180 # $obj = $self->create_from_cgi( $h => {
181 # required => $required_cols,
182 # ignore => $ignored_cols,
189 # return $obj, $fatal, $creating;
194 Deprecated method that calls do_delete or a given classes delete method, please
195 use do_delete instead
199 Unsuprisingly, this command causes a database record to be forever lost.
201 This method replaces the, now deprecated, delete method provided in prior versions
205 sub delete : Exported {
207 my ($sub) = (caller(1))[3];
208 # So subclasses can still send delete down ...
209 $sub =~ /^(.+)::([^:]+)$/;
210 if ($1 ne "Maypole::Model::Base" && $2 ne "delete") {
211 $self->SUPER::delete(@_);
213 warn "Maypole::Model::CDBI delete method is deprecated\n";
214 $self->do_delete(@_);
219 my ( $self, $r ) = @_;
220 # FIXME: handle fatal error with exception
221 $_->SUPER::delete for @{ $r->objects || [] };
223 $r->objects( [ $self->retrieve_all ] );
224 $r->{template} = "list";
230 Deprecated searching method - use do_search instead.
234 This action method searches for database records, it replaces
235 the, now deprecated, search method previously provided.
239 sub search : Exported {
241 my ($sub) = (caller(1))[3];
242 # So subclasses can still send search down ...
243 if ($sub =~ /^(.+)::([^:]+)$/) {
244 return ($1 ne "Maypole::Model::Base" && $2 ne "search") ?
245 $self->SUPER::search(@_) : $self->do_search(@_);
247 $self->SUPER::search(@_);
251 sub do_search : Exported {
252 my ( $self, $r ) = @_;
253 my %fields = map { $_ => 1 } $self->columns;
254 my $oper = "like"; # For now
255 my %params = %{ $r->{params} };
256 my %values = map { $_ => { $oper, $params{$_} } }
257 grep { defined $params{$_} && length ($params{$_}) && $fields{$_} }
260 $r->template("list");
261 if ( !%values ) { return $self->list($r) }
262 my $order = $self->order($r);
263 $self = $self->do_pager($r);
267 \%values, ( $order ? { order_by => $order } : () )
271 $r->{template_args}{search} = 1;
276 The C<list> method fills C<$r-E<gt>objects> with all of the
277 objects in the class. The results are paged using a pager.
281 sub list : Exported {
282 my ( $self, $r ) = @_;
283 my $order = $self->order($r);
284 $self = $self->do_pager($r);
286 $r->objects( [ $self->retrieve_all_sorted_by($order) ] );
289 $r->objects( [ $self->retrieve_all ] );
293 ###############################################################################
296 =head1 Helper Methods
301 This class method is passed the name of a model class that represensts a table
302 and allows the master model class to do any set-up required.
307 my ( $self, $child ) = @_;
308 $child->autoupdate(1);
309 if ( my $col = $child->stringify_column ) {
310 $child->columns( Stringify => $col );
317 This method returns a list of has-many accessors. A brewery has many
318 beers, so C<BeerDB::Brewery> needs to return C<beers>.
323 my ( $self, $r ) = @_;
324 return keys %{ $self->meta_info('has_many') || {} };
330 Given an accessor name as a method, this function returns the class this accessor returns.
335 my ( $self, $r, $accessor ) = @_;
336 my $meta = $self->meta_info;
337 my @rels = keys %$meta;
340 $related = $meta->{$_}{$accessor};
343 return unless $related;
345 my $mapping = $related->{args}->{mapping};
346 if ( $mapping and @$mapping ) {
347 return $related->{foreign_class}->meta_info('has_a')->{$$mapping[0]}->{foreign_class};
350 return $related->{foreign_class};
356 $class->related_meta($col);
358 Returns the hash ref of relationship meta info for a given column.
363 my ($self,$r, $accssr) = @_;
364 $self->_croak("You forgot to put the place holder for 'r' or forgot the accssr parameter") unless $accssr;
365 my $class_meta = $self->meta_info;
366 if (my ($rel_type) = grep { defined $class_meta->{$_}->{$accssr} }
368 { return $class_meta->{$rel_type}->{$accssr} };
373 =head2 stringify_column
375 Returns the name of the column to use when stringifying
380 sub stringify_column {
383 $class->columns("Stringify"),
384 ( grep { /^(name|title)$/i } $class->columns ),
385 ( grep { /(name|title)/i } $class->columns ),
386 ( grep { !/id$/i } $class->primary_columns ),
392 Sets the pager template argument ($r->{template_args}{pager})
393 to a Class::DBI::Pager object based on the rows_per_page
394 value set in the configuration of the application.
396 This pager is used via the pager macro in TT Templates, and
397 is also accessible via Mason.
402 my ( $self, $r ) = @_;
403 if ( my $rows = $r->config->rows_per_page ) {
404 return $r->{template_args}{pager} =
405 $self->pager( $rows, $r->query->{page} );
407 else { return $self }
413 Returns the SQL order syntax based on the order parameter passed
414 to the request, and the valid columns.. i.e. 'title ASC' or 'date_created DESC'.
416 $sql .= $self->order($r);
418 If the order column is not a column of this table,
419 or an order argument is not passed, then the return value is undefined.
421 Note: the returned value does not start with a space.
426 my ( $self, $r ) = @_;
427 my %ok_columns = map { $_ => 1 } $self->columns;
429 my $order = $q->{order};
430 return unless $order and $ok_columns{$order};
431 $order .= ' DESC' if $q->{o2} and $q->{o2} eq 'desc';
437 This method is inherited from Maypole::Model::Base and calls setup_database,
438 which uses Class::DBI::Loader to create and load Class::DBI classes from
439 the given database schema.
443 =head2 setup_database
445 The $opts argument is a hashref of options. The "options" key is a hashref of
446 Database connection options . Other keys may be various Loader arguments or
447 flags. It has this form:
449 # DB connection options
450 options { AutoCommit => 1 , ... },
459 my ( $class, $config, $namespace, $dsn, $u, $p, $opts ) = @_;
460 $dsn ||= $config->dsn;
461 $u ||= $config->user;
462 $p ||= $config->pass;
463 $opts ||= $config->opts;
465 warn "No DSN set in config" unless $dsn;
466 $config->loader || $config->loader(
467 Class::DBI::Loader->new(
468 namespace => $namespace,
475 $config->{classes} = [ $config->{loader}->classes ];
476 $config->{tables} = [ $config->{loader}->tables ];
478 my @table_class = map { $_ . " => " . $config->{loader}->_table2class($_) } @{ $config->{tables} };
479 warn( 'Loaded tables to classes: ' . join ', ', @table_class )
480 if $namespace->debug;
485 returns class for given table
490 my ( $self, $r, $table ) = @_;
491 return $r->config->loader->_table2class($table); # why not find_class ?
496 Returns 1 or more objects of the given class when provided with the request
502 my @pcs = $class->primary_columns;
505 @pks{@pcs}=(@{$r->{args}});
506 return $class->retrieve( %pks );
508 return $class->retrieve( $r->{args}->[0] );
517 Private method to return the class a column
518 belongs to that was inherited by an is_a relationship.
519 This should probably be public but need to think of API
524 my ($class, $col) = @_;
525 $class->_croak( "Need a column for _isa_class." ) unless $col;
527 my $isa = $class->meta_info("is_a") || {};
528 foreach ( keys %$isa ) {
529 $isaclass = $isa->{$_}->foreign_class;
530 return $isaclass if ($isaclass->find_column($col));
532 return; # col not in a is_a class
537 # Thanks to dave baird -- form builder for these private functions
540 my $dbh = $self->db_Main;
541 return $self->COLUMN_INFO if ref $self->COLUMN_INFO;
543 my $meta; # The info we are after
544 my ($catalog, $schema) = (undef, undef);
545 # Dave is suspicious this (above undefs) could
546 # break things if driver useses this info
548 # '%' is a search pattern for columns - matches all columns
549 if ( my $sth = $dbh->column_info( $catalog, $schema, $self->table, '%' ) )
551 $dbh->errstr && die "Error getting column info sth: " . $dbh->errstr;
552 $self->COLUMN_INFO( $self->_hash_type_meta( $sth ) );
553 # use Data::Dumper; warn "col info for typed is " . Dumper($self->COLUMN_INFO);
557 $self->COLUMN_INFO( $self->_hash_typeless_meta( ) );
558 # use Data::Dumper; warn "col info TYPELESS is " . Dumper($self->COLUMN_INFO);
560 return $self->COLUMN_INFO;
565 my ($self, $sth) = @_;
567 while ( my $row = $sth->fetchrow_hashref )
570 my ($col_meta, $col_name);
572 foreach my $key ( keys %$row)
574 my $value = $row->{$key} || $row->{ uc $key };
575 $col_meta->{$key} = $value;
576 $col_name = $row->{COLUMN_NAME} || $row->{column_name};
579 $meta->{$col_name} = $col_meta;
584 # typeless db e.g. sqlite
585 sub _hash_typeless_meta
589 $self->set_sql( fb_meta_dummy => 'SELECT * FROM __TABLE__ WHERE 1=0' )
590 unless $self->can( 'sql_fb_meta_dummy' );
592 my $sth = $self->sql_fb_meta_dummy;
594 $sth->execute or die "Error executing column info: " . $sth->errstr;;
596 # see 'Statement Handle Attributes' in the DBI docs for a list of available attributes
597 my $cols = $sth->{NAME};
598 my $types = $sth->{TYPE};
599 # my $sizes = $sth->{PRECISION}; # empty
600 # my $nulls = $sth->{NULLABLE}; # empty
602 # we haven't actually fetched anything from the sth, so need to tell DBI we're not going to
607 foreach my $col ( @$cols )
611 $col_meta->{NULLABLE} = 1;
613 # in my limited testing, the columns are returned in the same order as they were defined in the schema
614 $col_meta->{ORDINAL_POSITION} = $order++;
616 # type_name is taken literally from the schema, but is not actually used by sqlite,
617 # so it can be anything, e.g. varchar or varchar(xxx) or VARCHAR etc.
618 my $type = shift( @$types );
619 $type =~ /(\w+)\((\w+)\)/;
620 $col_meta->{type} = $type;
621 $col_meta->{TYPE_NAME} = $1;
623 $col_meta->{COLUMN_SIZE} = $size if $type =~ /(CHAR|INT)/i;
624 $meta->{$col} = $col_meta;
633 my $type = $class->column_type('column_name');
635 This returns the 'type' of this column (VARCHAR(20), BIGINT, etc.)
636 For now, it returns "BOOL" for tinyints.
638 TODO :: TEST with enums and postgres
643 my $col = shift or die "Need a column for column_type";
644 my $info = $class->_column_info->{$col} ||
645 eval { $class->_isa_class($col)->_column_info($col) } ||
648 my $type = $info->{mysql_type_name} || $info->{type};
650 $type = $info->{TYPE_NAME};
651 if ($info->{COLUMN_SIZE}) { $type .= "($info->{COLUMN_SIZE})"; }
654 if ($type and $type =~ /^tinyint/i and $info->{COLUMN_SIZE} == 1) {
660 =head2 column_nullable
662 Returns true if a column can be NULL and false if not.
666 sub column_nullable {
668 my $col = shift or $class->_croak( "Need a column for column_nullable" );
669 my $info = $class->_column_info->{$col} ||
670 eval { $class->_isa_class($col)->_column_info($col) } ||
672 return $info->{NULLABLE};
675 =head2 column_default
677 Returns default value for column or the empyty string.
678 Columns with NULL, CURRENT_TIMESTAMP, or Zeros( 0000-00...) for dates and times
685 my $col = shift or $class->_croak( "Need a column for column_default");
686 #return unless $class->find_column($col); # not a real column
688 my $info = $class->_column_info->{$col} ||
689 eval { $class->_isa_class($col)->_column_info($col) } ||
692 my $def = $info->{COLUMN_DEF};
693 $def = '' unless defined $def; # is this good?
701 =head2 get_classmetadata
703 Gets class meta data *excluding cgi input* for the passed in class or the
704 calling class. *NOTE* excludes cgi inputs. This method is handy to call from
705 templates when you need some metadata for a related class.
709 sub get_classmetadata {
710 my ($self, $class) = @_; # class is class we want data for
712 $class = ref $class || $class;
716 $res{colnames} = {$class->column_names};
717 $res{columns} = [$class->display_columns];
718 $res{list_columns} = [$class->list_columns];
719 $res{moniker} = $class->moniker;
720 $res{plural} = $class->plural_moniker;
721 $res{table} = $class->table;