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::Schema::Constants;
26 use SQL::Translator::Schema::Constraint;
27 use SQL::Translator::Schema::Field;
28 use SQL::Translator::Schema::Index;
30 use Carp::Clan '^SQL::Translator';
33 extends 'SQL::Translator::Schema::Object';
35 our $VERSION = '1.59';
37 # Stringify to our name, being careful not to pass any args through so we don't
38 # accidentally set it to undef. We also have to tweak bool so the object is
39 # still true when it doesn't have a name (which shouldn't happen!).
41 '""' => sub { shift->name },
42 'bool' => sub { $_[0]->name || $_[0] },
52 my $table = SQL::Translator::Schema::Table->new(
59 Add a constraint to the table. Returns the newly created
60 C<SQL::Translator::Schema::Constraint> object.
62 my $c1 = $table->add_constraint(
65 fields => [ 'foo_id' ],
68 my $c2 = SQL::Translator::Schema::Constraint->new( name => 'uniq' );
69 $c2 = $table->add_constraint( $constraint );
76 default => sub { +[] },
83 my $constraint_class = 'SQL::Translator::Schema::Constraint';
86 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
88 $constraint->table( $self );
92 $args{'table'} = $self;
93 $constraint = $constraint_class->new( \%args ) or
94 return $self->error( $constraint_class->error );
98 # If we're trying to add a PK when one is already defined,
99 # then just add the fields to the existing definition.
102 my $pk = $self->primary_key;
103 if ( $pk && $constraint->type eq PRIMARY_KEY ) {
104 $self->primary_key( $constraint->fields );
105 $pk->name($constraint->name) if $constraint->name;
106 my %extra = $constraint->extra;
107 $pk->extra(%extra) if keys %extra;
111 elsif ( $constraint->type eq PRIMARY_KEY ) {
112 for my $fname ( $constraint->fields ) {
113 if ( my $f = $self->get_field( $fname ) ) {
114 $f->is_primary_key( 1 );
119 # See if another constraint of the same type
120 # covers the same fields. -- This doesn't work! ky
122 # elsif ( $constraint->type ne CHECK_C ) {
123 # my @field_names = $constraint->fields;
125 # grep { $_->type eq $constraint->type }
126 # $self->get_constraints
128 # my %fields = map { $_, 1 } $c->fields;
129 # for my $field_name ( @field_names ) {
130 # if ( $fields{ $field_name } ) {
141 push @{ $self->_constraints }, $constraint;
147 =head2 drop_constraint
149 Remove a constraint from the table. Returns the constraint object if the index
150 was found and removed, an error otherwise. The single parameter can be either
151 an index name or an C<SQL::Translator::Schema::Constraint> object.
153 $table->drop_constraint('myconstraint');
157 sub drop_constraint {
159 my $constraint_class = 'SQL::Translator::Schema::Constraint';
162 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
163 $constraint_name = shift->name;
166 $constraint_name = shift;
169 if ( ! ($self->_has_constraints && grep { $_->name eq $constraint_name } @ { $self->_constraints }) ) {
170 return $self->error(qq[Can't drop constraint: "$constraint_name" doesn't exist]);
173 my @cs = @{ $self->_constraints };
174 my ($constraint_id) = grep { $cs[$_]->name eq $constraint_name } (0..$#cs);
175 my $constraint = splice(@{$self->_constraints}, $constraint_id, 1);
182 Add an index to the table. Returns the newly created
183 C<SQL::Translator::Schema::Index> object.
185 my $i1 = $table->add_index(
187 fields => [ 'name' ],
191 my $i2 = SQL::Translator::Schema::Index->new( name => 'id' );
192 $i2 = $table->add_index( $index );
199 default => sub { [] },
206 my $index_class = 'SQL::Translator::Schema::Index';
209 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
211 $index->table( $self );
215 $args{'table'} = $self;
216 $index = $index_class->new( \%args ) or return
217 $self->error( $index_class->error );
219 foreach my $ex_index ($self->get_indices) {
220 return if ($ex_index->equals($index));
222 push @{ $self->_indices }, $index;
228 Remove an index from the table. Returns the index object if the index was
229 found and removed, an error otherwise. The single parameter can be either
230 an index name of an C<SQL::Translator::Schema::Index> object.
232 $table->drop_index('myindex');
238 my $index_class = 'SQL::Translator::Schema::Index';
241 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
242 $index_name = shift->name;
248 if ( ! ($self->_has_indices && grep { $_->name eq $index_name } @{ $self->_indices }) ) {
249 return $self->error(qq[Can't drop index: "$index_name" doesn't exist]);
252 my @is = @{ $self->_indices };
253 my ($index_id) = grep { $is[$_]->name eq $index_name } (0..$#is);
254 my $index = splice(@{$self->_indices}, $index_id, 1);
261 Add an field to the table. Returns the newly created
262 C<SQL::Translator::Schema::Field> object. The "name" parameter is
263 required. If you try to create a field with the same name as an
264 existing field, you will get an error and the field will not be created.
266 my $f1 = $table->add_field(
268 data_type => 'integer',
272 my $f2 = SQL::Translator::Schema::Field->new(
276 $f2 = $table->add_field( $field2 ) or die $table->error;
283 default => sub { +{} },
290 my $field_class = 'SQL::Translator::Schema::Field';
293 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
295 $field->table( $self );
299 $args{'table'} = $self;
300 $field = $field_class->new( \%args ) or return
301 $self->error( $field_class->error );
304 my $existing_order = { map { $_->order => $_->name } $self->get_fields };
306 # supplied order, possible unordered assembly
307 if ( $field->order ) {
308 if($existing_order->{$field->order}) {
310 "Requested order '%d' for column '%s' conflicts with already existing column '%s'",
313 $existing_order->{$field->order},
318 my $last_field_no = max(keys %$existing_order) || 0;
319 if ( $last_field_no != scalar keys %$existing_order ) {
321 "Table '%s' field order incomplete - unable to auto-determine order for newly added field",
326 $field->order( $last_field_no + 1 );
329 # We know we have a name as the Field->new above errors if none given.
330 my $field_name = $field->name;
332 if ( $self->get_field($field_name) ) {
333 return $self->error(qq[Can't use field name "$field_name": field exists]);
336 $self->_fields->{ $field_name } = $field;
344 Remove a field from the table. Returns the field object if the field was
345 found and removed, an error otherwise. The single parameter can be either
346 a field name or an C<SQL::Translator::Schema::Field> object.
348 $table->drop_field('myfield');
354 my $field_class = 'SQL::Translator::Schema::Field';
357 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
358 $field_name = shift->name;
364 my $cascade = $args{'cascade'};
366 if ( ! ($self->_has_fields && exists $self->_fields->{ $field_name } ) ) {
367 return $self->error(qq[Can't drop field: "$field_name" doesn't exists]);
370 my $field = delete $self->_fields->{ $field_name };
373 # Remove this field from all indices using it
374 foreach my $i ($self->get_indices()) {
375 my @fs = $i->fields();
376 @fs = grep { $_ ne $field->name } @fs;
380 # Remove this field from all constraints using it
381 foreach my $c ($self->get_constraints()) {
382 my @cs = $c->fields();
383 @cs = grep { $_ ne $field->name } @cs;
393 Get or set the comments on a table. May be called several times to
394 set and it will accumulate the comments. Called in an array context,
395 returns each comment individually; called in a scalar context, returns
396 all the comments joined on newlines.
398 $table->comments('foo');
399 $table->comments('bar');
400 print join( ', ', $table->comments ); # prints "foo, bar"
406 coerce => sub { ref($_[0]) eq 'ARRAY' ? $_[0] : [$_[0]] },
407 default => sub { [] },
410 around comments => sub {
413 my @comments = ref $_[0] ? @{ $_[0] } : @_;
415 for my $arg ( @comments ) {
416 $arg = $arg->[0] if ref $arg;
417 push @{ $self->$orig }, $arg if defined $arg && $arg;
420 @comments = @{$self->$orig};
421 return wantarray ? @comments
422 : @comments ? join( "\n", @comments )
426 =head2 get_constraints
428 Returns all the constraint objects as an array or array reference.
430 my @constraints = $table->get_constraints;
434 sub get_constraints {
437 if ( $self->_has_constraints ) {
439 ? @{ $self->_constraints } : $self->_constraints;
442 $self->error('No constraints');
443 return wantarray ? () : undef;
449 Returns all the index objects as an array or array reference.
451 my @indices = $table->get_indices;
458 if ( $self->_has_indices ) {
460 ? @{ $self->_indices }
464 $self->error('No indices');
465 return wantarray ? () : undef;
471 Returns a field by the name provided.
473 my $field = $table->get_field('foo');
479 my $field_name = shift or return $self->error('No field name');
480 my $case_insensitive = shift;
481 return $self->error(qq[Field "$field_name" does not exist])
482 unless $self->_has_fields;
483 if ( $case_insensitive ) {
484 $field_name = uc($field_name);
485 foreach my $field ( keys %{$self->_fields} ) {
486 return $self->_fields->{$field} if $field_name eq uc($field);
488 return $self->error(qq[Field "$field_name" does not exist]);
490 return $self->error( qq[Field "$field_name" does not exist] ) unless
491 exists $self->_fields->{ $field_name };
492 return $self->_fields->{ $field_name };
497 Returns all the field objects as an array or array reference.
499 my @fields = $table->get_fields;
507 sort { $a->[0] <=> $b->[0] }
508 map { [ $_->order, $_ ] }
509 values %{ $self->_has_fields ? $self->_fields : {} };
512 return wantarray ? @fields : \@fields;
515 $self->error('No fields');
516 return wantarray ? () : undef;
522 Determine whether the view is valid or not.
524 my $ok = $view->is_valid;
530 return $self->error('No name') unless $self->name;
531 return $self->error('No fields') unless $self->get_fields;
534 $self->get_fields, $self->get_indices, $self->get_constraints
536 return $object->error unless $object->is_valid;
542 =head2 is_trivial_link
544 True if table has no data (non-key) fields and only uses single key joins.
548 has is_trivial_link => ( is => 'lazy', init_arg => undef );
550 sub _build_is_trivial_link {
552 return 0 if $self->is_data;
556 foreach my $field ( $self->get_fields ) {
557 next unless $field->is_foreign_key;
558 $fk{$field->foreign_key_reference->reference_table}++;
561 foreach my $referenced (keys %fk){
562 if($fk{$referenced} > 1){
572 Returns true if the table has some non-key fields.
576 has is_data => ( is => 'lazy', init_arg => undef );
581 foreach my $field ( $self->get_fields ) {
582 if ( !$field->is_primary_key and !$field->is_foreign_key ) {
592 Determine whether the table can link two arg tables via many-to-many.
594 my $ok = $table->can_link($table1,$table2);
598 has _can_link => ( is => 'ro', init_arg => undef, default => sub { +{} } );
601 my ( $self, $table1, $table2 ) = @_;
603 return $self->_can_link->{ $table1->name }{ $table2->name }
604 if defined $self->_can_link->{ $table1->name }{ $table2->name };
606 if ( $self->is_data == 1 ) {
607 $self->_can_link->{ $table1->name }{ $table2->name } = [0];
608 $self->_can_link->{ $table2->name }{ $table1->name } = [0];
609 return $self->_can_link->{ $table1->name }{ $table2->name };
614 foreach my $field ( $self->get_fields ) {
615 if ( $field->is_foreign_key ) {
616 push @{ $fk{ $field->foreign_key_reference->reference_table } },
617 $field->foreign_key_reference;
621 if ( !defined( $fk{ $table1->name } ) or !defined( $fk{ $table2->name } ) )
623 $self->_can_link->{ $table1->name }{ $table2->name } = [0];
624 $self->_can_link->{ $table2->name }{ $table1->name } = [0];
625 return $self->_can_link->{ $table1->name }{ $table2->name };
628 # trivial traversal, only one way to link the two tables
629 if ( scalar( @{ $fk{ $table1->name } } == 1 )
630 and scalar( @{ $fk{ $table2->name } } == 1 ) )
632 $self->_can_link->{ $table1->name }{ $table2->name } =
633 [ 'one2one', $fk{ $table1->name }, $fk{ $table2->name } ];
634 $self->_can_link->{ $table1->name }{ $table2->name } =
635 [ 'one2one', $fk{ $table2->name }, $fk{ $table1->name } ];
637 # non-trivial traversal. one way to link table2,
638 # many ways to link table1
640 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
641 and scalar( @{ $fk{ $table2->name } } == 1 ) )
643 $self->_can_link->{ $table1->name }{ $table2->name } =
644 [ 'many2one', $fk{ $table1->name }, $fk{ $table2->name } ];
645 $self->_can_link->{ $table2->name }{ $table1->name } =
646 [ 'one2many', $fk{ $table2->name }, $fk{ $table1->name } ];
648 # non-trivial traversal. one way to link table1,
649 # many ways to link table2
651 elsif ( scalar( @{ $fk{ $table1->name } } == 1 )
652 and scalar( @{ $fk{ $table2->name } } > 1 ) )
654 $self->_can_link->{ $table1->name }{ $table2->name } =
655 [ 'one2many', $fk{ $table1->name }, $fk{ $table2->name } ];
656 $self->_can_link->{ $table2->name }{ $table1->name } =
657 [ 'many2one', $fk{ $table2->name }, $fk{ $table1->name } ];
659 # non-trivial traversal. many ways to link table1 and table2
661 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
662 and scalar( @{ $fk{ $table2->name } } > 1 ) )
664 $self->_can_link->{ $table1->name }{ $table2->name } =
665 [ 'many2many', $fk{ $table1->name }, $fk{ $table2->name } ];
666 $self->_can_link->{ $table2->name }{ $table1->name } =
667 [ 'many2many', $fk{ $table2->name }, $fk{ $table1->name } ];
669 # one of the tables didn't export a key
670 # to this table, no linking possible
673 $self->_can_link->{ $table1->name }{ $table2->name } = [0];
674 $self->_can_link->{ $table2->name }{ $table1->name } = [0];
677 return $self->_can_link->{ $table1->name }{ $table2->name };
682 Get or set the table's name.
684 Errors ("No table name") if you try to set a blank name.
686 If provided an argument, checks the schema object for a table of
687 that name and disallows the change if one exists (setting the error to
688 "Can't use table name "%s": table exists").
690 my $table_name = $table->name('foo');
696 isa => sub { throw("No table name") unless $_[0] },
703 if ( my ($arg) = @_ ) {
704 if ( my $schema = $self->schema ) {
705 return $self->error( qq[Can't use table name "$arg": table exists] )
706 if $schema->get_table( $arg );
710 return ex2err($orig, $self, @_);
715 Get or set the table's schema object.
717 my $schema = $table->schema;
721 has schema => ( is => 'rw', isa => schema_obj('Schema'), weak_ref => 1 );
723 around schema => \&ex2err;
731 Gets or sets the table's primary key(s). Takes one or more field
732 names (as a string, list or array[ref]) as an argument. If the field
733 names are present, it will create a new PK if none exists, or it will
734 add to the fields of an existing PK (and will unique the field names).
735 Returns the C<SQL::Translator::Schema::Constraint> object representing
740 $table->primary_key('id');
741 $table->primary_key(['name']);
742 $table->primary_key('id','name']);
743 $table->primary_key(['id','name']);
744 $table->primary_key('id,name');
745 $table->primary_key(qw[ id name ]);
747 my $pk = $table->primary_key;
752 my $fields = parse_list_arg( @_ );
756 for my $f ( @$fields ) {
757 return $self->error(qq[Invalid field "$f"]) unless
758 $self->get_field($f);
762 for my $c ( $self->get_constraints ) {
763 if ( $c->type eq PRIMARY_KEY ) {
765 $c->fields( @{ $c->fields }, @$fields );
771 $constraint = $self->add_constraint(
782 for my $c ( $self->get_constraints ) {
783 return $c if $c->type eq PRIMARY_KEY;
792 Get or set the table's options (e.g., table types for MySQL). Returns
793 an array or array reference.
795 my @options = $table->options;
801 default => sub { [] },
802 coerce => \&parse_list_arg,
805 around options => sub {
808 my $options = parse_list_arg( @_ );
810 push @{ $self->$orig }, @$options;
812 return wantarray ? @{ $self->$orig } : $self->$orig;
817 Get or set the table's order.
819 my $order = $table->order(3);
823 has order => ( is => 'rw', default => sub { 0 } );
825 around order => sub {
826 my ( $orig, $self, $arg ) = @_;
828 if ( defined $arg && $arg =~ /^\d+$/ ) {
829 return $self->$orig($arg);
837 Read-only method to return a list or array ref of the field names. Returns undef
838 or an empty list if the table has no fields set. Useful if you want to
839 avoid the overload magic of the Field objects returned by the get_fields method.
841 my @names = $constraint->field_names;
852 return wantarray ? @fields : \@fields;
855 $self->error('No fields');
856 return wantarray ? () : undef;
866 Determines if this table is the same as another
868 my $isIdentical = $table1->equals( $table2 );
874 my $case_insensitive = shift;
876 return 0 unless $self->SUPER::equals($other);
877 return 0 unless $case_insensitive ? uc($self->name) eq uc($other->name) : $self->name eq $other->name;
878 return 0 unless $self->_compare_objects(scalar $self->options, scalar $other->options);
879 return 0 unless $self->_compare_objects(scalar $self->extra, scalar $other->extra);
882 # Go through our fields
884 foreach my $field ( $self->get_fields ) {
885 my $otherField = $other->get_field($field->name, $case_insensitive);
886 return 0 unless $field->equals($otherField, $case_insensitive);
887 $checkedFields{$field->name} = 1;
889 # Go through the other table's fields
890 foreach my $otherField ( $other->get_fields ) {
891 next if $checkedFields{$otherField->name};
896 # Go through our constraints
897 my %checkedConstraints;
899 foreach my $constraint ( $self->get_constraints ) {
900 foreach my $otherConstraint ( $other->get_constraints ) {
901 if ( $constraint->equals($otherConstraint, $case_insensitive) ) {
902 $checkedConstraints{$otherConstraint} = 1;
908 # Go through the other table's constraints
910 foreach my $otherConstraint ( $other->get_constraints ) {
911 next if $checkedFields{$otherConstraint};
912 foreach my $constraint ( $self->get_constraints ) {
913 if ( $otherConstraint->equals($constraint, $case_insensitive) ) {
921 # Go through our indices
924 foreach my $index ( $self->get_indices ) {
925 foreach my $otherIndex ( $other->get_indices ) {
926 if ( $index->equals($otherIndex, $case_insensitive) ) {
927 $checkedIndices{$otherIndex} = 1;
933 # Go through the other table's indices
935 foreach my $otherIndex ( $other->get_indices ) {
936 next if $checkedIndices{$otherIndex};
937 foreach my $index ( $self->get_indices ) {
938 if ( $otherIndex->equals($index, $case_insensitive) ) {
948 =head1 LOOKUP METHODS
950 The following are a set of shortcut methods for getting commonly used lists of
951 fields and constraints. They all return lists or array refs of Field or
958 The primary key fields.
962 All foreign key fields.
966 All the fields except the primary key.
974 All fields with unique constraints.
976 =item unique_constraints
978 All this tables unique constraints.
980 =item fkey_constraints
982 All this tables foreign key constraints. (See primary_key method to get the
983 primary key constraint)
991 my @fields = grep { $_->is_primary_key } $me->get_fields;
992 return wantarray ? @fields : \@fields;
998 push @fields, $_->fields foreach $me->fkey_constraints;
999 return wantarray ? @fields : \@fields;
1002 sub nonpkey_fields {
1004 my @fields = grep { !$_->is_primary_key } $me->get_fields;
1005 return wantarray ? @fields : \@fields;
1011 grep { !$_->is_foreign_key and !$_->is_primary_key } $me->get_fields;
1012 return wantarray ? @fields : \@fields;
1018 push @fields, $_->fields foreach $me->unique_constraints;
1019 return wantarray ? @fields : \@fields;
1022 sub unique_constraints {
1024 my @cons = grep { $_->type eq UNIQUE } $me->get_constraints;
1025 return wantarray ? @cons : \@cons;
1028 sub fkey_constraints {
1030 my @cons = grep { $_->type eq FOREIGN_KEY } $me->get_constraints;
1031 return wantarray ? @cons : \@cons;
1034 # Must come after all 'has' declarations
1035 around new => \&ex2err;
1043 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
1044 Allen Day E<lt>allenday@ucla.eduE<gt>.