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 'parse_list_arg';
24 use SQL::Translator::Schema::Constants;
25 use SQL::Translator::Schema::Constraint;
26 use SQL::Translator::Schema::Field;
27 use SQL::Translator::Schema::Index;
30 use base 'SQL::Translator::Schema::Object';
32 use vars qw( $VERSION );
36 # Stringify to our name, being careful not to pass any args through so we don't
37 # accidentally set it to undef. We also have to tweak bool so the object is
38 # still true when it doesn't have a name (which shouldn't happen!).
40 '""' => sub { shift->name },
41 'bool' => sub { $_[0]->name || $_[0] },
45 # ----------------------------------------------------------------------
47 __PACKAGE__->_attributes( qw/schema name comments options order/ );
55 my $table = SQL::Translator::Schema::Table->new(
64 my $self = $class->SUPER::new (@_)
67 $self->{_order} = { map { $_ => 0 } qw/
76 # ----------------------------------------------------------------------
83 Add a constraint to the table. Returns the newly created
84 C<SQL::Translator::Schema::Constraint> object.
86 my $c1 = $table->add_constraint(
89 fields => [ 'foo_id' ],
92 my $c2 = SQL::Translator::Schema::Constraint->new( name => 'uniq' );
93 $c2 = $table->add_constraint( $constraint );
98 my $constraint_class = 'SQL::Translator::Schema::Constraint';
101 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
103 $constraint->table( $self );
107 $args{'table'} = $self;
108 $constraint = $constraint_class->new( \%args ) or
109 return $self->error( $constraint_class->error );
113 # If we're trying to add a PK when one is already defined,
114 # then just add the fields to the existing definition.
117 my $pk = $self->primary_key;
118 if ( $pk && $constraint->type eq PRIMARY_KEY ) {
119 $self->primary_key( $constraint->fields );
120 $pk->name($constraint->name) if $constraint->name;
121 my %extra = $constraint->extra;
122 $pk->extra(%extra) if keys %extra;
126 elsif ( $constraint->type eq PRIMARY_KEY ) {
127 for my $fname ( $constraint->fields ) {
128 if ( my $f = $self->get_field( $fname ) ) {
129 $f->is_primary_key( 1 );
134 # See if another constraint of the same type
135 # covers the same fields. -- This doesn't work! ky
137 # elsif ( $constraint->type ne CHECK_C ) {
138 # my @field_names = $constraint->fields;
140 # grep { $_->type eq $constraint->type }
141 # $self->get_constraints
143 # my %fields = map { $_, 1 } $c->fields;
144 # for my $field_name ( @field_names ) {
145 # if ( $fields{ $field_name } ) {
156 push @{ $self->{'constraints'} }, $constraint;
162 # ----------------------------------------------------------------------
163 sub drop_constraint {
167 =head2 drop_constraint
169 Remove a constraint from the table. Returns the constraint object if the index
170 was found and removed, an error otherwise. The single parameter can be either
171 an index name or an C<SQL::Translator::Schema::Constraint> object.
173 $table->drop_constraint('myconstraint');
178 my $constraint_class = 'SQL::Translator::Schema::Constraint';
181 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
182 $constraint_name = shift->name;
185 $constraint_name = shift;
188 if ( ! grep { $_->name eq $constraint_name } @ { $self->{'constraints'} } ) {
189 return $self->error(qq[Can't drop constraint: "$constraint_name" doesn't exist]);
192 my @cs = @{ $self->{'constraints'} };
193 my ($constraint_id) = grep { $cs[$_]->name eq $constraint_name } (0..$#cs);
194 my $constraint = splice(@{$self->{'constraints'}}, $constraint_id, 1);
199 # ----------------------------------------------------------------------
206 Add an index to the table. Returns the newly created
207 C<SQL::Translator::Schema::Index> object.
209 my $i1 = $table->add_index(
211 fields => [ 'name' ],
215 my $i2 = SQL::Translator::Schema::Index->new( name => 'id' );
216 $i2 = $table->add_index( $index );
221 my $index_class = 'SQL::Translator::Schema::Index';
224 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
226 $index->table( $self );
230 $args{'table'} = $self;
231 $index = $index_class->new( \%args ) or return
232 $self->error( $index_class->error );
234 foreach my $ex_index ($self->get_indices) {
235 return if ($ex_index->equals($index));
237 push @{ $self->{'indices'} }, $index;
241 # ----------------------------------------------------------------------
248 Remove an index from the table. Returns the index object if the index was
249 found and removed, an error otherwise. The single parameter can be either
250 an index name of an C<SQL::Translator::Schema::Index> object.
252 $table->drop_index('myindex');
257 my $index_class = 'SQL::Translator::Schema::Index';
260 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
261 $index_name = shift->name;
267 if ( ! grep { $_->name eq $index_name } @{ $self->{'indices'} }) {
268 return $self->error(qq[Can't drop index: "$index_name" doesn't exist]);
271 my @is = @{ $self->{'indices'} };
272 my ($index_id) = grep { $is[$_]->name eq $index_name } (0..$#is);
273 my $index = splice(@{$self->{'indices'}}, $index_id, 1);
278 # ----------------------------------------------------------------------
285 Add an field to the table. Returns the newly created
286 C<SQL::Translator::Schema::Field> object. The "name" parameter is
287 required. If you try to create a field with the same name as an
288 existing field, you will get an error and the field will not be created.
290 my $f1 = $table->add_field(
292 data_type => 'integer',
296 my $f2 = SQL::Translator::Schema::Field->new(
300 $f2 = $table->add_field( $field2 ) or die $table->error;
305 my $field_class = 'SQL::Translator::Schema::Field';
308 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
310 $field->table( $self );
314 $args{'table'} = $self;
315 $field = $field_class->new( \%args ) or return
316 $self->error( $field_class->error );
319 $field->order( ++$self->{_order}{field} );
320 # We know we have a name as the Field->new above errors if none given.
321 my $field_name = $field->name;
323 if ( exists $self->{'fields'}{ $field_name } ) {
324 return $self->error(qq[Can't create field: "$field_name" exists]);
327 $self->{'fields'}{ $field_name } = $field;
332 # ----------------------------------------------------------------------
339 Remove a field from the table. Returns the field object if the field was
340 found and removed, an error otherwise. The single parameter can be either
341 a field name or an C<SQL::Translator::Schema::Field> object.
343 $table->drop_field('myfield');
348 my $field_class = 'SQL::Translator::Schema::Field';
351 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
352 $field_name = shift->name;
358 my $cascade = $args{'cascade'};
360 if ( ! exists $self->{'fields'}{ $field_name } ) {
361 return $self->error(qq[Can't drop field: "$field_name" doesn't exists]);
364 my $field = delete $self->{'fields'}{ $field_name };
367 # Remove this field from all indices using it
368 foreach my $i ($self->get_indices()) {
369 my @fs = $i->fields();
370 @fs = grep { $_ ne $field->name } @fs;
374 # Remove this field from all constraints using it
375 foreach my $c ($self->get_constraints()) {
376 my @cs = $c->fields();
377 @cs = grep { $_ ne $field->name } @cs;
385 # ----------------------------------------------------------------------
392 Get or set the comments on a table. May be called several times to
393 set and it will accumulate the comments. Called in an array context,
394 returns each comment individually; called in a scalar context, returns
395 all the comments joined on newlines.
397 $table->comments('foo');
398 $table->comments('bar');
399 print join( ', ', $table->comments ); # prints "foo, bar"
404 my @comments = ref $_[0] ? @{ $_[0] } : @_;
406 for my $arg ( @comments ) {
407 $arg = $arg->[0] if ref $arg;
408 push @{ $self->{'comments'} }, $arg if defined $arg && $arg;
411 if ( @{ $self->{'comments'} || [] } ) {
413 ? @{ $self->{'comments'} }
414 : join( "\n", @{ $self->{'comments'} } )
418 return wantarray ? () : undef;
422 # ----------------------------------------------------------------------
423 sub get_constraints {
427 =head2 get_constraints
429 Returns all the constraint objects as an array or array reference.
431 my @constraints = $table->get_constraints;
437 if ( ref $self->{'constraints'} ) {
439 ? @{ $self->{'constraints'} } : $self->{'constraints'};
442 $self->error('No constraints');
443 return wantarray ? () : undef;
447 # ----------------------------------------------------------------------
454 Returns all the index objects as an array or array reference.
456 my @indices = $table->get_indices;
462 if ( ref $self->{'indices'} ) {
464 ? @{ $self->{'indices'} }
465 : $self->{'indices'};
468 $self->error('No indices');
469 return wantarray ? () : undef;
473 # ----------------------------------------------------------------------
480 Returns a field by the name provided.
482 my $field = $table->get_field('foo');
487 my $field_name = shift or return $self->error('No field name');
488 my $case_insensitive = shift;
489 if ( $case_insensitive ) {
490 $field_name = uc($field_name);
491 foreach my $field ( keys %{$self->{fields}} ) {
492 return $self->{fields}{$field} if $field_name eq uc($field);
494 return $self->error(qq[Field "$field_name" does not exist]);
496 return $self->error( qq[Field "$field_name" does not exist] ) unless
497 exists $self->{'fields'}{ $field_name };
498 return $self->{'fields'}{ $field_name };
501 # ----------------------------------------------------------------------
508 Returns all the field objects as an array or array reference.
510 my @fields = $table->get_fields;
517 sort { $a->[0] <=> $b->[0] }
518 map { [ $_->order, $_ ] }
519 values %{ $self->{'fields'} || {} };
522 return wantarray ? @fields : \@fields;
525 $self->error('No fields');
526 return wantarray ? () : undef;
530 # ----------------------------------------------------------------------
537 Determine whether the view is valid or not.
539 my $ok = $view->is_valid;
544 return $self->error('No name') unless $self->name;
545 return $self->error('No fields') unless $self->get_fields;
548 $self->get_fields, $self->get_indices, $self->get_constraints
550 return $object->error unless $object->is_valid;
556 # ----------------------------------------------------------------------
557 sub is_trivial_link {
561 =head2 is_trivial_link
563 True if table has no data (non-key) fields and only uses single key joins.
568 return 0 if $self->is_data;
569 return $self->{'is_trivial_link'} if defined $self->{'is_trivial_link'};
571 $self->{'is_trivial_link'} = 1;
575 foreach my $field ( $self->get_fields ) {
576 next unless $field->is_foreign_key;
577 $fk{$field->foreign_key_reference->reference_table}++;
580 foreach my $referenced (keys %fk){
581 if($fk{$referenced} > 1){
582 $self->{'is_trivial_link'} = 0;
587 return $self->{'is_trivial_link'};
597 Returns true if the table has some non-key fields.
602 return $self->{'is_data'} if defined $self->{'is_data'};
604 $self->{'is_data'} = 0;
606 foreach my $field ( $self->get_fields ) {
607 if ( !$field->is_primary_key and !$field->is_foreign_key ) {
608 $self->{'is_data'} = 1;
609 return $self->{'is_data'};
613 return $self->{'is_data'};
616 # ----------------------------------------------------------------------
623 Determine whether the table can link two arg tables via many-to-many.
625 my $ok = $table->can_link($table1,$table2);
629 my ( $self, $table1, $table2 ) = @_;
631 return $self->{'can_link'}{ $table1->name }{ $table2->name }
632 if defined $self->{'can_link'}{ $table1->name }{ $table2->name };
634 if ( $self->is_data == 1 ) {
635 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
636 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
637 return $self->{'can_link'}{ $table1->name }{ $table2->name };
642 foreach my $field ( $self->get_fields ) {
643 if ( $field->is_foreign_key ) {
644 push @{ $fk{ $field->foreign_key_reference->reference_table } },
645 $field->foreign_key_reference;
649 if ( !defined( $fk{ $table1->name } ) or !defined( $fk{ $table2->name } ) )
651 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
652 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
653 return $self->{'can_link'}{ $table1->name }{ $table2->name };
656 # trivial traversal, only one way to link the two tables
657 if ( scalar( @{ $fk{ $table1->name } } == 1 )
658 and scalar( @{ $fk{ $table2->name } } == 1 ) )
660 $self->{'can_link'}{ $table1->name }{ $table2->name } =
661 [ 'one2one', $fk{ $table1->name }, $fk{ $table2->name } ];
662 $self->{'can_link'}{ $table1->name }{ $table2->name } =
663 [ 'one2one', $fk{ $table2->name }, $fk{ $table1->name } ];
665 # non-trivial traversal. one way to link table2,
666 # many ways to link table1
668 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
669 and scalar( @{ $fk{ $table2->name } } == 1 ) )
671 $self->{'can_link'}{ $table1->name }{ $table2->name } =
672 [ 'many2one', $fk{ $table1->name }, $fk{ $table2->name } ];
673 $self->{'can_link'}{ $table2->name }{ $table1->name } =
674 [ 'one2many', $fk{ $table2->name }, $fk{ $table1->name } ];
676 # non-trivial traversal. one way to link table1,
677 # many ways to link table2
679 elsif ( scalar( @{ $fk{ $table1->name } } == 1 )
680 and scalar( @{ $fk{ $table2->name } } > 1 ) )
682 $self->{'can_link'}{ $table1->name }{ $table2->name } =
683 [ 'one2many', $fk{ $table1->name }, $fk{ $table2->name } ];
684 $self->{'can_link'}{ $table2->name }{ $table1->name } =
685 [ 'many2one', $fk{ $table2->name }, $fk{ $table1->name } ];
687 # non-trivial traversal. many ways to link table1 and table2
689 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
690 and scalar( @{ $fk{ $table2->name } } > 1 ) )
692 $self->{'can_link'}{ $table1->name }{ $table2->name } =
693 [ 'many2many', $fk{ $table1->name }, $fk{ $table2->name } ];
694 $self->{'can_link'}{ $table2->name }{ $table1->name } =
695 [ 'many2many', $fk{ $table2->name }, $fk{ $table1->name } ];
697 # one of the tables didn't export a key
698 # to this table, no linking possible
701 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
702 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
705 return $self->{'can_link'}{ $table1->name }{ $table2->name };
708 # ----------------------------------------------------------------------
715 Get or set the table's name.
717 Errors ("No table name") if you try to set a blank name.
719 If provided an argument, checks the schema object for a table of
720 that name and disallows the change if one exists (setting the error to
721 "Can't use table name "%s": table exists").
723 my $table_name = $table->name('foo');
730 my $arg = shift || return $self->error( "No table name" );
731 if ( my $schema = $self->schema ) {
732 return $self->error( qq[Can't use table name "$arg": table exists] )
733 if $schema->get_table( $arg );
735 $self->{'name'} = $arg;
738 return $self->{'name'} || '';
741 # ----------------------------------------------------------------------
748 Get or set the table's schema object.
750 my $schema = $table->schema;
755 if ( my $arg = shift ) {
756 return $self->error('Not a schema object') unless
757 UNIVERSAL::isa( $arg, 'SQL::Translator::Schema' );
758 $self->{'schema'} = $arg;
761 return $self->{'schema'};
764 # ----------------------------------------------------------------------
771 Gets or sets the table's primary key(s). Takes one or more field
772 names (as a string, list or array[ref]) as an argument. If the field
773 names are present, it will create a new PK if none exists, or it will
774 add to the fields of an existing PK (and will unique the field names).
775 Returns the C<SQL::Translator::Schema::Constraint> object representing
780 $table->primary_key('id');
781 $table->primary_key(['name']);
782 $table->primary_key('id','name']);
783 $table->primary_key(['id','name']);
784 $table->primary_key('id,name');
785 $table->primary_key(qw[ id name ]);
787 my $pk = $table->primary_key;
792 my $fields = parse_list_arg( @_ );
796 for my $f ( @$fields ) {
797 return $self->error(qq[Invalid field "$f"]) unless
798 $self->get_field($f);
802 for my $c ( $self->get_constraints ) {
803 if ( $c->type eq PRIMARY_KEY ) {
805 $c->fields( @{ $c->fields }, @$fields );
811 $constraint = $self->add_constraint(
822 for my $c ( $self->get_constraints ) {
823 return $c if $c->type eq PRIMARY_KEY;
830 # ----------------------------------------------------------------------
837 Get or set the table's options (e.g., table types for MySQL). Returns
838 an array or array reference.
840 my @options = $table->options;
845 my $options = parse_list_arg( @_ );
847 push @{ $self->{'options'} }, @$options;
849 if ( ref $self->{'options'} ) {
850 return wantarray ? @{ $self->{'options'} || [] } : ($self->{'options'} || '');
853 return wantarray ? () : [];
857 # ----------------------------------------------------------------------
864 Get or set the table's order.
866 my $order = $table->order(3);
870 my ( $self, $arg ) = @_;
872 if ( defined $arg && $arg =~ /^\d+$/ ) {
873 $self->{'order'} = $arg;
876 return $self->{'order'} || 0;
879 # ----------------------------------------------------------------------
884 Read-only method to return a list or array ref of the field names. Returns undef
885 or an empty list if the table has no fields set. Useful if you want to
886 avoid the overload magic of the Field objects returned by the get_fields method.
888 my @names = $constraint->field_names;
895 sort { $a->order <=> $b->order }
896 values %{ $self->{'fields'} || {} };
899 return wantarray ? @fields : \@fields;
902 $self->error('No fields');
903 return wantarray ? () : undef;
907 # ----------------------------------------------------------------------
914 Determines if this table is the same as another
916 my $isIdentical = $table1->equals( $table2 );
922 my $case_insensitive = shift;
924 return 0 unless $self->SUPER::equals($other);
925 return 0 unless $case_insensitive ? uc($self->name) eq uc($other->name) : $self->name eq $other->name;
926 return 0 unless $self->_compare_objects(scalar $self->options, scalar $other->options);
927 return 0 unless $self->_compare_objects(scalar $self->extra, scalar $other->extra);
930 # Go through our fields
932 foreach my $field ( $self->get_fields ) {
933 my $otherField = $other->get_field($field->name, $case_insensitive);
934 return 0 unless $field->equals($otherField, $case_insensitive);
935 $checkedFields{$field->name} = 1;
937 # Go through the other table's fields
938 foreach my $otherField ( $other->get_fields ) {
939 next if $checkedFields{$otherField->name};
944 # Go through our constraints
945 my %checkedConstraints;
947 foreach my $constraint ( $self->get_constraints ) {
948 foreach my $otherConstraint ( $other->get_constraints ) {
949 if ( $constraint->equals($otherConstraint, $case_insensitive) ) {
950 $checkedConstraints{$otherConstraint} = 1;
956 # Go through the other table's constraints
958 foreach my $otherConstraint ( $other->get_constraints ) {
959 next if $checkedFields{$otherConstraint};
960 foreach my $constraint ( $self->get_constraints ) {
961 if ( $otherConstraint->equals($constraint, $case_insensitive) ) {
969 # Go through our indices
972 foreach my $index ( $self->get_indices ) {
973 foreach my $otherIndex ( $other->get_indices ) {
974 if ( $index->equals($otherIndex, $case_insensitive) ) {
975 $checkedIndices{$otherIndex} = 1;
981 # Go through the other table's indices
983 foreach my $otherIndex ( $other->get_indices ) {
984 next if $checkedIndices{$otherIndex};
985 foreach my $index ( $self->get_indices ) {
986 if ( $otherIndex->equals($index, $case_insensitive) ) {
996 # ----------------------------------------------------------------------
998 =head1 LOOKUP METHODS
1000 The following are a set of shortcut methods for getting commonly used lists of
1001 fields and constraints. They all return lists or array refs of Field or
1008 The primary key fields.
1012 All foreign key fields.
1014 =item nonpkey_fields
1016 All the fields except the primary key.
1024 All fields with unique constraints.
1026 =item unique_constraints
1028 All this tables unique constraints.
1030 =item fkey_constraints
1032 All this tables foreign key constraints. (See primary_key method to get the
1033 primary key constraint)
1041 my @fields = grep { $_->is_primary_key } $me->get_fields;
1042 return wantarray ? @fields : \@fields;
1045 # ----------------------------------------------------------------------
1049 push @fields, $_->fields foreach $me->fkey_constraints;
1050 return wantarray ? @fields : \@fields;
1053 # ----------------------------------------------------------------------
1054 sub nonpkey_fields {
1056 my @fields = grep { !$_->is_primary_key } $me->get_fields;
1057 return wantarray ? @fields : \@fields;
1060 # ----------------------------------------------------------------------
1064 grep { !$_->is_foreign_key and !$_->is_primary_key } $me->get_fields;
1065 return wantarray ? @fields : \@fields;
1068 # ----------------------------------------------------------------------
1072 push @fields, $_->fields foreach $me->unique_constraints;
1073 return wantarray ? @fields : \@fields;
1076 # ----------------------------------------------------------------------
1077 sub unique_constraints {
1079 my @cons = grep { $_->type eq UNIQUE } $me->get_constraints;
1080 return wantarray ? @cons : \@cons;
1083 # ----------------------------------------------------------------------
1084 sub fkey_constraints {
1086 my @cons = grep { $_->type eq FOREIGN_KEY } $me->get_constraints;
1087 return wantarray ? @cons : \@cons;
1090 # ----------------------------------------------------------------------
1093 undef $self->{'schema'}; # destroy cyclical reference
1094 undef $_ for @{ $self->{'constraints'} };
1095 undef $_ for @{ $self->{'indices'} };
1096 undef $_ for values %{ $self->{'fields'} };
1101 # ----------------------------------------------------------------------
1107 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
1108 Allen Day E<lt>allenday@ucla.eduE<gt>.