1 package SQL::Translator::Schema::Table;
7 SQL::Translator::Schema::Table - SQL::Translator table object
11 use SQL::Translator::Schema::Table;
12 my $table = SQL::Translator::Schema::Table->new( name => 'foo' );
16 C<SQL::Translator::Schema::Table> is the table object.
23 use SQL::Translator::Utils qw(parse_list_arg ex2err throw);
24 use SQL::Translator::Types qw(schema_obj);
25 use SQL::Translator::Role::ListAttr;
26 use SQL::Translator::Schema::Constants;
27 use SQL::Translator::Schema::Constraint;
28 use SQL::Translator::Schema::Field;
29 use SQL::Translator::Schema::Index;
31 use Carp::Clan '^SQL::Translator';
34 extends 'SQL::Translator::Schema::Object';
36 our $VERSION = '1.59';
38 # Stringify to our name, being careful not to pass any args through so we don't
39 # accidentally set it to undef. We also have to tweak bool so the object is
40 # still true when it doesn't have a name (which shouldn't happen!).
42 '""' => sub { shift->name },
43 'bool' => sub { $_[0]->name || $_[0] },
53 my $table = SQL::Translator::Schema::Table->new(
60 Add a constraint to the table. Returns the newly created
61 C<SQL::Translator::Schema::Constraint> object.
63 my $c1 = $table->add_constraint(
66 fields => [ 'foo_id' ],
69 my $c2 = SQL::Translator::Schema::Constraint->new( name => 'uniq' );
70 $c2 = $table->add_constraint( $constraint );
77 default => sub { +[] },
84 my $constraint_class = 'SQL::Translator::Schema::Constraint';
87 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
89 $constraint->table( $self );
93 $args{'table'} = $self;
94 $constraint = $constraint_class->new( \%args ) or
95 return $self->error( $constraint_class->error );
99 # If we're trying to add a PK when one is already defined,
100 # then just add the fields to the existing definition.
103 my $pk = $self->primary_key;
104 if ( $pk && $constraint->type eq PRIMARY_KEY ) {
105 $self->primary_key( $constraint->fields );
106 $pk->name($constraint->name) if $constraint->name;
107 my %extra = $constraint->extra;
108 $pk->extra(%extra) if keys %extra;
112 elsif ( $constraint->type eq PRIMARY_KEY ) {
113 for my $fname ( $constraint->fields ) {
114 if ( my $f = $self->get_field( $fname ) ) {
115 $f->is_primary_key( 1 );
120 # See if another constraint of the same type
121 # covers the same fields. -- This doesn't work! ky
123 # elsif ( $constraint->type ne CHECK_C ) {
124 # my @field_names = $constraint->fields;
126 # grep { $_->type eq $constraint->type }
127 # $self->get_constraints
129 # my %fields = map { $_, 1 } $c->fields;
130 # for my $field_name ( @field_names ) {
131 # if ( $fields{ $field_name } ) {
142 push @{ $self->_constraints }, $constraint;
148 =head2 drop_constraint
150 Remove a constraint from the table. Returns the constraint object if the index
151 was found and removed, an error otherwise. The single parameter can be either
152 an index name or an C<SQL::Translator::Schema::Constraint> object.
154 $table->drop_constraint('myconstraint');
158 sub drop_constraint {
160 my $constraint_class = 'SQL::Translator::Schema::Constraint';
163 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
164 $constraint_name = shift->name;
167 $constraint_name = shift;
170 if ( ! ($self->_has_constraints && grep { $_->name eq $constraint_name } @ { $self->_constraints }) ) {
171 return $self->error(qq[Can't drop constraint: "$constraint_name" doesn't exist]);
174 my @cs = @{ $self->_constraints };
175 my ($constraint_id) = grep { $cs[$_]->name eq $constraint_name } (0..$#cs);
176 my $constraint = splice(@{$self->_constraints}, $constraint_id, 1);
183 Add an index to the table. Returns the newly created
184 C<SQL::Translator::Schema::Index> object.
186 my $i1 = $table->add_index(
188 fields => [ 'name' ],
192 my $i2 = SQL::Translator::Schema::Index->new( name => 'id' );
193 $i2 = $table->add_index( $index );
200 default => sub { [] },
207 my $index_class = 'SQL::Translator::Schema::Index';
210 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
212 $index->table( $self );
216 $args{'table'} = $self;
217 $index = $index_class->new( \%args ) or return
218 $self->error( $index_class->error );
220 foreach my $ex_index ($self->get_indices) {
221 return if ($ex_index->equals($index));
223 push @{ $self->_indices }, $index;
229 Remove an index from the table. Returns the index object if the index was
230 found and removed, an error otherwise. The single parameter can be either
231 an index name of an C<SQL::Translator::Schema::Index> object.
233 $table->drop_index('myindex');
239 my $index_class = 'SQL::Translator::Schema::Index';
242 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
243 $index_name = shift->name;
249 if ( ! ($self->_has_indices && grep { $_->name eq $index_name } @{ $self->_indices }) ) {
250 return $self->error(qq[Can't drop index: "$index_name" doesn't exist]);
253 my @is = @{ $self->_indices };
254 my ($index_id) = grep { $is[$_]->name eq $index_name } (0..$#is);
255 my $index = splice(@{$self->_indices}, $index_id, 1);
262 Add an field to the table. Returns the newly created
263 C<SQL::Translator::Schema::Field> object. The "name" parameter is
264 required. If you try to create a field with the same name as an
265 existing field, you will get an error and the field will not be created.
267 my $f1 = $table->add_field(
269 data_type => 'integer',
273 my $f2 = SQL::Translator::Schema::Field->new(
277 $f2 = $table->add_field( $field2 ) or die $table->error;
284 default => sub { +{} },
291 my $field_class = 'SQL::Translator::Schema::Field';
294 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
296 $field->table( $self );
300 $args{'table'} = $self;
301 $field = $field_class->new( \%args ) or return
302 $self->error( $field_class->error );
305 my $existing_order = { map { $_->order => $_->name } $self->get_fields };
307 # supplied order, possible unordered assembly
308 if ( $field->order ) {
309 if($existing_order->{$field->order}) {
311 "Requested order '%d' for column '%s' conflicts with already existing column '%s'",
314 $existing_order->{$field->order},
319 my $last_field_no = max(keys %$existing_order) || 0;
320 if ( $last_field_no != scalar keys %$existing_order ) {
322 "Table '%s' field order incomplete - unable to auto-determine order for newly added field",
327 $field->order( $last_field_no + 1 );
330 # We know we have a name as the Field->new above errors if none given.
331 my $field_name = $field->name;
333 if ( $self->get_field($field_name) ) {
334 return $self->error(qq[Can't use field name "$field_name": field exists]);
337 $self->_fields->{ $field_name } = $field;
345 Remove a field from the table. Returns the field object if the field was
346 found and removed, an error otherwise. The single parameter can be either
347 a field name or an C<SQL::Translator::Schema::Field> object.
349 $table->drop_field('myfield');
355 my $field_class = 'SQL::Translator::Schema::Field';
358 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
359 $field_name = shift->name;
365 my $cascade = $args{'cascade'};
367 if ( ! ($self->_has_fields && exists $self->_fields->{ $field_name } ) ) {
368 return $self->error(qq[Can't drop field: "$field_name" doesn't exists]);
371 my $field = delete $self->_fields->{ $field_name };
374 # Remove this field from all indices using it
375 foreach my $i ($self->get_indices()) {
376 my @fs = $i->fields();
377 @fs = grep { $_ ne $field->name } @fs;
381 # Remove this field from all constraints using it
382 foreach my $c ($self->get_constraints()) {
383 my @cs = $c->fields();
384 @cs = grep { $_ ne $field->name } @cs;
394 Get or set the comments on a table. May be called several times to
395 set and it will accumulate the comments. Called in an array context,
396 returns each comment individually; called in a scalar context, returns
397 all the comments joined on newlines.
399 $table->comments('foo');
400 $table->comments('bar');
401 print join( ', ', $table->comments ); # prints "foo, bar"
407 coerce => sub { ref($_[0]) eq 'ARRAY' ? $_[0] : [$_[0]] },
408 default => sub { [] },
411 around comments => sub {
414 my @comments = ref $_[0] ? @{ $_[0] } : @_;
416 for my $arg ( @comments ) {
417 $arg = $arg->[0] if ref $arg;
418 push @{ $self->$orig }, $arg if defined $arg && $arg;
421 @comments = @{$self->$orig};
422 return wantarray ? @comments
423 : @comments ? join( "\n", @comments )
427 =head2 get_constraints
429 Returns all the constraint objects as an array or array reference.
431 my @constraints = $table->get_constraints;
435 sub get_constraints {
438 if ( $self->_has_constraints ) {
440 ? @{ $self->_constraints } : $self->_constraints;
443 $self->error('No constraints');
444 return wantarray ? () : undef;
450 Returns all the index objects as an array or array reference.
452 my @indices = $table->get_indices;
459 if ( $self->_has_indices ) {
461 ? @{ $self->_indices }
465 $self->error('No indices');
466 return wantarray ? () : undef;
472 Returns a field by the name provided.
474 my $field = $table->get_field('foo');
480 my $field_name = shift or return $self->error('No field name');
481 my $case_insensitive = shift;
482 return $self->error(qq[Field "$field_name" does not exist])
483 unless $self->_has_fields;
484 if ( $case_insensitive ) {
485 $field_name = uc($field_name);
486 foreach my $field ( keys %{$self->_fields} ) {
487 return $self->_fields->{$field} if $field_name eq uc($field);
489 return $self->error(qq[Field "$field_name" does not exist]);
491 return $self->error( qq[Field "$field_name" does not exist] ) unless
492 exists $self->_fields->{ $field_name };
493 return $self->_fields->{ $field_name };
498 Returns all the field objects as an array or array reference.
500 my @fields = $table->get_fields;
508 sort { $a->[0] <=> $b->[0] }
509 map { [ $_->order, $_ ] }
510 values %{ $self->_has_fields ? $self->_fields : {} };
513 return wantarray ? @fields : \@fields;
516 $self->error('No fields');
517 return wantarray ? () : undef;
523 Determine whether the view is valid or not.
525 my $ok = $view->is_valid;
531 return $self->error('No name') unless $self->name;
532 return $self->error('No fields') unless $self->get_fields;
535 $self->get_fields, $self->get_indices, $self->get_constraints
537 return $object->error unless $object->is_valid;
543 =head2 is_trivial_link
545 True if table has no data (non-key) fields and only uses single key joins.
549 has is_trivial_link => ( is => 'lazy', init_arg => undef );
551 sub _build_is_trivial_link {
553 return 0 if $self->is_data;
557 foreach my $field ( $self->get_fields ) {
558 next unless $field->is_foreign_key;
559 $fk{$field->foreign_key_reference->reference_table}++;
562 foreach my $referenced (keys %fk){
563 if($fk{$referenced} > 1){
573 Returns true if the table has some non-key fields.
577 has is_data => ( is => 'lazy', init_arg => undef );
582 foreach my $field ( $self->get_fields ) {
583 if ( !$field->is_primary_key and !$field->is_foreign_key ) {
593 Determine whether the table can link two arg tables via many-to-many.
595 my $ok = $table->can_link($table1,$table2);
599 has _can_link => ( is => 'ro', init_arg => undef, default => sub { +{} } );
602 my ( $self, $table1, $table2 ) = @_;
604 return $self->_can_link->{ $table1->name }{ $table2->name }
605 if defined $self->_can_link->{ $table1->name }{ $table2->name };
607 if ( $self->is_data == 1 ) {
608 $self->_can_link->{ $table1->name }{ $table2->name } = [0];
609 $self->_can_link->{ $table2->name }{ $table1->name } = [0];
610 return $self->_can_link->{ $table1->name }{ $table2->name };
615 foreach my $field ( $self->get_fields ) {
616 if ( $field->is_foreign_key ) {
617 push @{ $fk{ $field->foreign_key_reference->reference_table } },
618 $field->foreign_key_reference;
622 if ( !defined( $fk{ $table1->name } ) or !defined( $fk{ $table2->name } ) )
624 $self->_can_link->{ $table1->name }{ $table2->name } = [0];
625 $self->_can_link->{ $table2->name }{ $table1->name } = [0];
626 return $self->_can_link->{ $table1->name }{ $table2->name };
629 # trivial traversal, only one way to link the two tables
630 if ( scalar( @{ $fk{ $table1->name } } == 1 )
631 and scalar( @{ $fk{ $table2->name } } == 1 ) )
633 $self->_can_link->{ $table1->name }{ $table2->name } =
634 [ 'one2one', $fk{ $table1->name }, $fk{ $table2->name } ];
635 $self->_can_link->{ $table1->name }{ $table2->name } =
636 [ 'one2one', $fk{ $table2->name }, $fk{ $table1->name } ];
638 # non-trivial traversal. one way to link table2,
639 # many ways to link table1
641 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
642 and scalar( @{ $fk{ $table2->name } } == 1 ) )
644 $self->_can_link->{ $table1->name }{ $table2->name } =
645 [ 'many2one', $fk{ $table1->name }, $fk{ $table2->name } ];
646 $self->_can_link->{ $table2->name }{ $table1->name } =
647 [ 'one2many', $fk{ $table2->name }, $fk{ $table1->name } ];
649 # non-trivial traversal. one way to link table1,
650 # many ways to link table2
652 elsif ( scalar( @{ $fk{ $table1->name } } == 1 )
653 and scalar( @{ $fk{ $table2->name } } > 1 ) )
655 $self->_can_link->{ $table1->name }{ $table2->name } =
656 [ 'one2many', $fk{ $table1->name }, $fk{ $table2->name } ];
657 $self->_can_link->{ $table2->name }{ $table1->name } =
658 [ 'many2one', $fk{ $table2->name }, $fk{ $table1->name } ];
660 # non-trivial traversal. many ways to link table1 and table2
662 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
663 and scalar( @{ $fk{ $table2->name } } > 1 ) )
665 $self->_can_link->{ $table1->name }{ $table2->name } =
666 [ 'many2many', $fk{ $table1->name }, $fk{ $table2->name } ];
667 $self->_can_link->{ $table2->name }{ $table1->name } =
668 [ 'many2many', $fk{ $table2->name }, $fk{ $table1->name } ];
670 # one of the tables didn't export a key
671 # to this table, no linking possible
674 $self->_can_link->{ $table1->name }{ $table2->name } = [0];
675 $self->_can_link->{ $table2->name }{ $table1->name } = [0];
678 return $self->_can_link->{ $table1->name }{ $table2->name };
683 Get or set the table's name.
685 Errors ("No table name") if you try to set a blank name.
687 If provided an argument, checks the schema object for a table of
688 that name and disallows the change if one exists (setting the error to
689 "Can't use table name "%s": table exists").
691 my $table_name = $table->name('foo');
697 isa => sub { throw("No table name") unless $_[0] },
704 if ( my ($arg) = @_ ) {
705 if ( my $schema = $self->schema ) {
706 return $self->error( qq[Can't use table name "$arg": table exists] )
707 if $schema->get_table( $arg );
711 return ex2err($orig, $self, @_);
716 Get or set the table's schema object.
718 my $schema = $table->schema;
722 has schema => ( is => 'rw', isa => schema_obj('Schema'), weak_ref => 1 );
724 around schema => \&ex2err;
732 Gets or sets the table's primary key(s). Takes one or more field
733 names (as a string, list or array[ref]) as an argument. If the field
734 names are present, it will create a new PK if none exists, or it will
735 add to the fields of an existing PK (and will unique the field names).
736 Returns the C<SQL::Translator::Schema::Constraint> object representing
741 $table->primary_key('id');
742 $table->primary_key(['name']);
743 $table->primary_key('id','name']);
744 $table->primary_key(['id','name']);
745 $table->primary_key('id,name');
746 $table->primary_key(qw[ id name ]);
748 my $pk = $table->primary_key;
753 my $fields = parse_list_arg( @_ );
757 for my $f ( @$fields ) {
758 return $self->error(qq[Invalid field "$f"]) unless
759 $self->get_field($f);
763 for my $c ( $self->get_constraints ) {
764 if ( $c->type eq PRIMARY_KEY ) {
766 $c->fields( @{ $c->fields }, @$fields );
772 $constraint = $self->add_constraint(
783 for my $c ( $self->get_constraints ) {
784 return $c if $c->type eq PRIMARY_KEY;
793 Get or set the table's options (e.g., table types for MySQL). Returns
794 an array or array reference.
796 my @options = $table->options;
800 with ListAttr options => ( append => 1 );
804 Get or set the table's order.
806 my $order = $table->order(3);
810 has order => ( is => 'rw', default => sub { 0 } );
812 around order => sub {
813 my ( $orig, $self, $arg ) = @_;
815 if ( defined $arg && $arg =~ /^\d+$/ ) {
816 return $self->$orig($arg);
824 Read-only method to return a list or array ref of the field names. Returns undef
825 or an empty list if the table has no fields set. Useful if you want to
826 avoid the overload magic of the Field objects returned by the get_fields method.
828 my @names = $constraint->field_names;
839 return wantarray ? @fields : \@fields;
842 $self->error('No fields');
843 return wantarray ? () : undef;
853 Determines if this table is the same as another
855 my $isIdentical = $table1->equals( $table2 );
861 my $case_insensitive = shift;
863 return 0 unless $self->SUPER::equals($other);
864 return 0 unless $case_insensitive ? uc($self->name) eq uc($other->name) : $self->name eq $other->name;
865 return 0 unless $self->_compare_objects(scalar $self->options, scalar $other->options);
866 return 0 unless $self->_compare_objects(scalar $self->extra, scalar $other->extra);
869 # Go through our fields
871 foreach my $field ( $self->get_fields ) {
872 my $otherField = $other->get_field($field->name, $case_insensitive);
873 return 0 unless $field->equals($otherField, $case_insensitive);
874 $checkedFields{$field->name} = 1;
876 # Go through the other table's fields
877 foreach my $otherField ( $other->get_fields ) {
878 next if $checkedFields{$otherField->name};
883 # Go through our constraints
884 my %checkedConstraints;
886 foreach my $constraint ( $self->get_constraints ) {
887 foreach my $otherConstraint ( $other->get_constraints ) {
888 if ( $constraint->equals($otherConstraint, $case_insensitive) ) {
889 $checkedConstraints{$otherConstraint} = 1;
895 # Go through the other table's constraints
897 foreach my $otherConstraint ( $other->get_constraints ) {
898 next if $checkedFields{$otherConstraint};
899 foreach my $constraint ( $self->get_constraints ) {
900 if ( $otherConstraint->equals($constraint, $case_insensitive) ) {
908 # Go through our indices
911 foreach my $index ( $self->get_indices ) {
912 foreach my $otherIndex ( $other->get_indices ) {
913 if ( $index->equals($otherIndex, $case_insensitive) ) {
914 $checkedIndices{$otherIndex} = 1;
920 # Go through the other table's indices
922 foreach my $otherIndex ( $other->get_indices ) {
923 next if $checkedIndices{$otherIndex};
924 foreach my $index ( $self->get_indices ) {
925 if ( $otherIndex->equals($index, $case_insensitive) ) {
935 =head1 LOOKUP METHODS
937 The following are a set of shortcut methods for getting commonly used lists of
938 fields and constraints. They all return lists or array refs of Field or
945 The primary key fields.
949 All foreign key fields.
953 All the fields except the primary key.
961 All fields with unique constraints.
963 =item unique_constraints
965 All this tables unique constraints.
967 =item fkey_constraints
969 All this tables foreign key constraints. (See primary_key method to get the
970 primary key constraint)
978 my @fields = grep { $_->is_primary_key } $me->get_fields;
979 return wantarray ? @fields : \@fields;
985 push @fields, $_->fields foreach $me->fkey_constraints;
986 return wantarray ? @fields : \@fields;
991 my @fields = grep { !$_->is_primary_key } $me->get_fields;
992 return wantarray ? @fields : \@fields;
998 grep { !$_->is_foreign_key and !$_->is_primary_key } $me->get_fields;
999 return wantarray ? @fields : \@fields;
1005 push @fields, $_->fields foreach $me->unique_constraints;
1006 return wantarray ? @fields : \@fields;
1009 sub unique_constraints {
1011 my @cons = grep { $_->type eq UNIQUE } $me->get_constraints;
1012 return wantarray ? @cons : \@cons;
1015 sub fkey_constraints {
1017 my @cons = grep { $_->type eq FOREIGN_KEY } $me->get_constraints;
1018 return wantarray ? @cons : \@cons;
1021 # Must come after all 'has' declarations
1022 around new => \&ex2err;
1030 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
1031 Allen Day E<lt>allenday@ucla.eduE<gt>.