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.
24 use SQL::Translator::Utils 'parse_list_arg';
25 use SQL::Translator::Schema::Constants;
26 use SQL::Translator::Schema::Constraint;
27 use SQL::Translator::Schema::Field;
28 use SQL::Translator::Schema::Index;
31 use base 'SQL::Translator::Schema::Object';
33 use vars qw( $VERSION );
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] },
46 __PACKAGE__->_attributes( qw/schema name comments options order/ );
54 my $table = SQL::Translator::Schema::Table->new(
63 my $self = $class->SUPER::new (@_)
66 $self->{_order} = { map { $_ => 0 } qw/
79 Add a constraint to the table. Returns the newly created
80 C<SQL::Translator::Schema::Constraint> object.
82 my $c1 = $table->add_constraint(
85 fields => [ 'foo_id' ],
88 my $c2 = SQL::Translator::Schema::Constraint->new( name => 'uniq' );
89 $c2 = $table->add_constraint( $constraint );
94 my $constraint_class = 'SQL::Translator::Schema::Constraint';
97 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
99 $constraint->table( $self );
103 $args{'table'} = $self;
104 $constraint = $constraint_class->new( \%args ) or
105 return $self->error( $constraint_class->error );
109 # If we're trying to add a PK when one is already defined,
110 # then just add the fields to the existing definition.
113 my $pk = $self->primary_key;
114 if ( $pk && $constraint->type eq PRIMARY_KEY ) {
115 $self->primary_key( $constraint->fields );
116 $pk->name($constraint->name) if $constraint->name;
117 my %extra = $constraint->extra;
118 $pk->extra(%extra) if keys %extra;
122 elsif ( $constraint->type eq PRIMARY_KEY ) {
123 for my $fname ( $constraint->fields ) {
124 if ( my $f = $self->get_field( $fname ) ) {
125 $f->is_primary_key( 1 );
130 # See if another constraint of the same type
131 # covers the same fields. -- This doesn't work! ky
133 # elsif ( $constraint->type ne CHECK_C ) {
134 # my @field_names = $constraint->fields;
136 # grep { $_->type eq $constraint->type }
137 # $self->get_constraints
139 # my %fields = map { $_, 1 } $c->fields;
140 # for my $field_name ( @field_names ) {
141 # if ( $fields{ $field_name } ) {
152 push @{ $self->{'constraints'} }, $constraint;
158 sub drop_constraint {
162 =head2 drop_constraint
164 Remove a constraint from the table. Returns the constraint object if the index
165 was found and removed, an error otherwise. The single parameter can be either
166 an index name or an C<SQL::Translator::Schema::Constraint> object.
168 $table->drop_constraint('myconstraint');
173 my $constraint_class = 'SQL::Translator::Schema::Constraint';
176 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
177 $constraint_name = shift->name;
180 $constraint_name = shift;
183 if ( ! grep { $_->name eq $constraint_name } @ { $self->{'constraints'} } ) {
184 return $self->error(qq[Can't drop constraint: "$constraint_name" doesn't exist]);
187 my @cs = @{ $self->{'constraints'} };
188 my ($constraint_id) = grep { $cs[$_]->name eq $constraint_name } (0..$#cs);
189 my $constraint = splice(@{$self->{'constraints'}}, $constraint_id, 1);
200 Add an index to the table. Returns the newly created
201 C<SQL::Translator::Schema::Index> object.
203 my $i1 = $table->add_index(
205 fields => [ 'name' ],
209 my $i2 = SQL::Translator::Schema::Index->new( name => 'id' );
210 $i2 = $table->add_index( $index );
215 my $index_class = 'SQL::Translator::Schema::Index';
218 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
220 $index->table( $self );
224 $args{'table'} = $self;
225 $index = $index_class->new( \%args ) or return
226 $self->error( $index_class->error );
228 foreach my $ex_index ($self->get_indices) {
229 return if ($ex_index->equals($index));
231 push @{ $self->{'indices'} }, $index;
241 Remove an index from the table. Returns the index object if the index was
242 found and removed, an error otherwise. The single parameter can be either
243 an index name of an C<SQL::Translator::Schema::Index> object.
245 $table->drop_index('myindex');
250 my $index_class = 'SQL::Translator::Schema::Index';
253 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
254 $index_name = shift->name;
260 if ( ! grep { $_->name eq $index_name } @{ $self->{'indices'} }) {
261 return $self->error(qq[Can't drop index: "$index_name" doesn't exist]);
264 my @is = @{ $self->{'indices'} };
265 my ($index_id) = grep { $is[$_]->name eq $index_name } (0..$#is);
266 my $index = splice(@{$self->{'indices'}}, $index_id, 1);
277 Add an field to the table. Returns the newly created
278 C<SQL::Translator::Schema::Field> object. The "name" parameter is
279 required. If you try to create a field with the same name as an
280 existing field, you will get an error and the field will not be created.
282 my $f1 = $table->add_field(
284 data_type => 'integer',
288 my $f2 = SQL::Translator::Schema::Field->new(
292 $f2 = $table->add_field( $field2 ) or die $table->error;
297 my $field_class = 'SQL::Translator::Schema::Field';
300 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
302 $field->table( $self );
306 $args{'table'} = $self;
307 $field = $field_class->new( \%args ) or return
308 $self->error( $field_class->error );
311 $field->order( ++$self->{_order}{field} );
312 # We know we have a name as the Field->new above errors if none given.
313 my $field_name = $field->name;
315 if ( exists $self->{'fields'}{ $field_name } ) {
316 return $self->error(qq[Can't create field: "$field_name" exists]);
319 $self->{'fields'}{ $field_name } = $field;
331 Remove a field from the table. Returns the field object if the field was
332 found and removed, an error otherwise. The single parameter can be either
333 a field name or an C<SQL::Translator::Schema::Field> object.
335 $table->drop_field('myfield');
340 my $field_class = 'SQL::Translator::Schema::Field';
343 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
344 $field_name = shift->name;
350 my $cascade = $args{'cascade'};
352 if ( ! exists $self->{'fields'}{ $field_name } ) {
353 return $self->error(qq[Can't drop field: "$field_name" doesn't exists]);
356 my $field = delete $self->{'fields'}{ $field_name };
359 # Remove this field from all indices using it
360 foreach my $i ($self->get_indices()) {
361 my @fs = $i->fields();
362 @fs = grep { $_ ne $field->name } @fs;
366 # Remove this field from all constraints using it
367 foreach my $c ($self->get_constraints()) {
368 my @cs = $c->fields();
369 @cs = grep { $_ ne $field->name } @cs;
383 Get or set the comments on a table. May be called several times to
384 set and it will accumulate the comments. Called in an array context,
385 returns each comment individually; called in a scalar context, returns
386 all the comments joined on newlines.
388 $table->comments('foo');
389 $table->comments('bar');
390 print join( ', ', $table->comments ); # prints "foo, bar"
395 my @comments = ref $_[0] ? @{ $_[0] } : @_;
397 for my $arg ( @comments ) {
398 $arg = $arg->[0] if ref $arg;
399 push @{ $self->{'comments'} }, $arg if defined $arg && $arg;
402 if ( @{ $self->{'comments'} || [] } ) {
404 ? @{ $self->{'comments'} }
405 : join( "\n", @{ $self->{'comments'} } )
409 return wantarray ? () : undef;
413 sub get_constraints {
417 =head2 get_constraints
419 Returns all the constraint objects as an array or array reference.
421 my @constraints = $table->get_constraints;
427 if ( ref $self->{'constraints'} ) {
429 ? @{ $self->{'constraints'} } : $self->{'constraints'};
432 $self->error('No constraints');
433 return wantarray ? () : undef;
443 Returns all the index objects as an array or array reference.
445 my @indices = $table->get_indices;
451 if ( ref $self->{'indices'} ) {
453 ? @{ $self->{'indices'} }
454 : $self->{'indices'};
457 $self->error('No indices');
458 return wantarray ? () : undef;
468 Returns a field by the name provided.
470 my $field = $table->get_field('foo');
475 my $field_name = shift or return $self->error('No field name');
476 my $case_insensitive = shift;
477 if ( $case_insensitive ) {
478 $field_name = uc($field_name);
479 foreach my $field ( keys %{$self->{fields}} ) {
480 return $self->{fields}{$field} if $field_name eq uc($field);
482 return $self->error(qq[Field "$field_name" does not exist]);
484 return $self->error( qq[Field "$field_name" does not exist] ) unless
485 exists $self->{'fields'}{ $field_name };
486 return $self->{'fields'}{ $field_name };
495 Returns all the field objects as an array or array reference.
497 my @fields = $table->get_fields;
504 sort { $a->[0] <=> $b->[0] }
505 map { [ $_->order, $_ ] }
506 values %{ $self->{'fields'} || {} };
509 return wantarray ? @fields : \@fields;
512 $self->error('No fields');
513 return wantarray ? () : undef;
523 Determine whether the view is valid or not.
525 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 sub is_trivial_link {
546 =head2 is_trivial_link
548 True if table has no data (non-key) fields and only uses single key joins.
553 return 0 if $self->is_data;
554 return $self->{'is_trivial_link'} if defined $self->{'is_trivial_link'};
556 $self->{'is_trivial_link'} = 1;
560 foreach my $field ( $self->get_fields ) {
561 next unless $field->is_foreign_key;
562 $fk{$field->foreign_key_reference->reference_table}++;
565 foreach my $referenced (keys %fk){
566 if($fk{$referenced} > 1){
567 $self->{'is_trivial_link'} = 0;
572 return $self->{'is_trivial_link'};
582 Returns true if the table has some non-key fields.
587 return $self->{'is_data'} if defined $self->{'is_data'};
589 $self->{'is_data'} = 0;
591 foreach my $field ( $self->get_fields ) {
592 if ( !$field->is_primary_key and !$field->is_foreign_key ) {
593 $self->{'is_data'} = 1;
594 return $self->{'is_data'};
598 return $self->{'is_data'};
607 Determine whether the table can link two arg tables via many-to-many.
609 my $ok = $table->can_link($table1,$table2);
613 my ( $self, $table1, $table2 ) = @_;
615 return $self->{'can_link'}{ $table1->name }{ $table2->name }
616 if defined $self->{'can_link'}{ $table1->name }{ $table2->name };
618 if ( $self->is_data == 1 ) {
619 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
620 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
621 return $self->{'can_link'}{ $table1->name }{ $table2->name };
626 foreach my $field ( $self->get_fields ) {
627 if ( $field->is_foreign_key ) {
628 push @{ $fk{ $field->foreign_key_reference->reference_table } },
629 $field->foreign_key_reference;
633 if ( !defined( $fk{ $table1->name } ) or !defined( $fk{ $table2->name } ) )
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 };
640 # trivial traversal, only one way to link the two tables
641 if ( scalar( @{ $fk{ $table1->name } } == 1 )
642 and scalar( @{ $fk{ $table2->name } } == 1 ) )
644 $self->{'can_link'}{ $table1->name }{ $table2->name } =
645 [ 'one2one', $fk{ $table1->name }, $fk{ $table2->name } ];
646 $self->{'can_link'}{ $table1->name }{ $table2->name } =
647 [ 'one2one', $fk{ $table2->name }, $fk{ $table1->name } ];
649 # non-trivial traversal. one way to link table2,
650 # many ways to link table1
652 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
653 and scalar( @{ $fk{ $table2->name } } == 1 ) )
655 $self->{'can_link'}{ $table1->name }{ $table2->name } =
656 [ 'many2one', $fk{ $table1->name }, $fk{ $table2->name } ];
657 $self->{'can_link'}{ $table2->name }{ $table1->name } =
658 [ 'one2many', $fk{ $table2->name }, $fk{ $table1->name } ];
660 # non-trivial traversal. one way to link table1,
661 # many ways to link table2
663 elsif ( scalar( @{ $fk{ $table1->name } } == 1 )
664 and scalar( @{ $fk{ $table2->name } } > 1 ) )
666 $self->{'can_link'}{ $table1->name }{ $table2->name } =
667 [ 'one2many', $fk{ $table1->name }, $fk{ $table2->name } ];
668 $self->{'can_link'}{ $table2->name }{ $table1->name } =
669 [ 'many2one', $fk{ $table2->name }, $fk{ $table1->name } ];
671 # non-trivial traversal. many ways to link table1 and table2
673 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
674 and scalar( @{ $fk{ $table2->name } } > 1 ) )
676 $self->{'can_link'}{ $table1->name }{ $table2->name } =
677 [ 'many2many', $fk{ $table1->name }, $fk{ $table2->name } ];
678 $self->{'can_link'}{ $table2->name }{ $table1->name } =
679 [ 'many2many', $fk{ $table2->name }, $fk{ $table1->name } ];
681 # one of the tables didn't export a key
682 # to this table, no linking possible
685 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
686 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
689 return $self->{'can_link'}{ $table1->name }{ $table2->name };
698 Get or set the table's name.
700 Errors ("No table name") if you try to set a blank name.
702 If provided an argument, checks the schema object for a table of
703 that name and disallows the change if one exists (setting the error to
704 "Can't use table name "%s": table exists").
706 my $table_name = $table->name('foo');
713 my $arg = shift || return $self->error( "No table name" );
714 if ( my $schema = $self->schema ) {
715 return $self->error( qq[Can't use table name "$arg": table exists] )
716 if $schema->get_table( $arg );
718 $self->{'name'} = $arg;
721 return $self->{'name'} || '';
730 Get or set the table's schema object.
732 my $schema = $table->schema;
737 if ( my $arg = shift ) {
738 return $self->error('Not a schema object') unless
739 UNIVERSAL::isa( $arg, 'SQL::Translator::Schema' );
740 $self->{'schema'} = $arg;
743 return $self->{'schema'};
752 Gets or sets the table's primary key(s). Takes one or more field
753 names (as a string, list or array[ref]) as an argument. If the field
754 names are present, it will create a new PK if none exists, or it will
755 add to the fields of an existing PK (and will unique the field names).
756 Returns the C<SQL::Translator::Schema::Constraint> object representing
761 $table->primary_key('id');
762 $table->primary_key(['name']);
763 $table->primary_key('id','name']);
764 $table->primary_key(['id','name']);
765 $table->primary_key('id,name');
766 $table->primary_key(qw[ id name ]);
768 my $pk = $table->primary_key;
773 my $fields = parse_list_arg( @_ );
777 for my $f ( @$fields ) {
778 return $self->error(qq[Invalid field "$f"]) unless
779 $self->get_field($f);
783 for my $c ( $self->get_constraints ) {
784 if ( $c->type eq PRIMARY_KEY ) {
786 $c->fields( @{ $c->fields }, @$fields );
792 $constraint = $self->add_constraint(
803 for my $c ( $self->get_constraints ) {
804 return $c if $c->type eq PRIMARY_KEY;
817 Get or set the table's options (e.g., table types for MySQL). Returns
818 an array or array reference.
820 my @options = $table->options;
825 my $options = parse_list_arg( @_ );
827 push @{ $self->{'options'} }, @$options;
829 if ( ref $self->{'options'} ) {
830 return wantarray ? @{ $self->{'options'} || [] } : ($self->{'options'} || '');
833 return wantarray ? () : [];
843 Get or set the table's order.
845 my $order = $table->order(3);
849 my ( $self, $arg ) = @_;
851 if ( defined $arg && $arg =~ /^\d+$/ ) {
852 $self->{'order'} = $arg;
855 return $self->{'order'} || 0;
862 Read-only method to return a list or array ref of the field names. Returns undef
863 or an empty list if the table has no fields set. Useful if you want to
864 avoid the overload magic of the Field objects returned by the get_fields method.
866 my @names = $constraint->field_names;
873 sort { $a->order <=> $b->order }
874 values %{ $self->{'fields'} || {} };
877 return wantarray ? @fields : \@fields;
880 $self->error('No fields');
881 return wantarray ? () : undef;
891 Determines if this table is the same as another
893 my $isIdentical = $table1->equals( $table2 );
899 my $case_insensitive = shift;
901 return 0 unless $self->SUPER::equals($other);
902 return 0 unless $case_insensitive ? uc($self->name) eq uc($other->name) : $self->name eq $other->name;
903 return 0 unless $self->_compare_objects(scalar $self->options, scalar $other->options);
904 return 0 unless $self->_compare_objects(scalar $self->extra, scalar $other->extra);
907 # Go through our fields
909 foreach my $field ( $self->get_fields ) {
910 my $otherField = $other->get_field($field->name, $case_insensitive);
911 return 0 unless $field->equals($otherField, $case_insensitive);
912 $checkedFields{$field->name} = 1;
914 # Go through the other table's fields
915 foreach my $otherField ( $other->get_fields ) {
916 next if $checkedFields{$otherField->name};
921 # Go through our constraints
922 my %checkedConstraints;
924 foreach my $constraint ( $self->get_constraints ) {
925 foreach my $otherConstraint ( $other->get_constraints ) {
926 if ( $constraint->equals($otherConstraint, $case_insensitive) ) {
927 $checkedConstraints{$otherConstraint} = 1;
933 # Go through the other table's constraints
935 foreach my $otherConstraint ( $other->get_constraints ) {
936 next if $checkedFields{$otherConstraint};
937 foreach my $constraint ( $self->get_constraints ) {
938 if ( $otherConstraint->equals($constraint, $case_insensitive) ) {
946 # Go through our indices
949 foreach my $index ( $self->get_indices ) {
950 foreach my $otherIndex ( $other->get_indices ) {
951 if ( $index->equals($otherIndex, $case_insensitive) ) {
952 $checkedIndices{$otherIndex} = 1;
958 # Go through the other table's indices
960 foreach my $otherIndex ( $other->get_indices ) {
961 next if $checkedIndices{$otherIndex};
962 foreach my $index ( $self->get_indices ) {
963 if ( $otherIndex->equals($index, $case_insensitive) ) {
973 =head1 LOOKUP METHODS
975 The following are a set of shortcut methods for getting commonly used lists of
976 fields and constraints. They all return lists or array refs of Field or
983 The primary key fields.
987 All foreign key fields.
991 All the fields except the primary key.
999 All fields with unique constraints.
1001 =item unique_constraints
1003 All this tables unique constraints.
1005 =item fkey_constraints
1007 All this tables foreign key constraints. (See primary_key method to get the
1008 primary key constraint)
1016 my @fields = grep { $_->is_primary_key } $me->get_fields;
1017 return wantarray ? @fields : \@fields;
1023 push @fields, $_->fields foreach $me->fkey_constraints;
1024 return wantarray ? @fields : \@fields;
1027 sub nonpkey_fields {
1029 my @fields = grep { !$_->is_primary_key } $me->get_fields;
1030 return wantarray ? @fields : \@fields;
1036 grep { !$_->is_foreign_key and !$_->is_primary_key } $me->get_fields;
1037 return wantarray ? @fields : \@fields;
1043 push @fields, $_->fields foreach $me->unique_constraints;
1044 return wantarray ? @fields : \@fields;
1047 sub unique_constraints {
1049 my @cons = grep { $_->type eq UNIQUE } $me->get_constraints;
1050 return wantarray ? @cons : \@cons;
1053 sub fkey_constraints {
1055 my @cons = grep { $_->type eq FOREIGN_KEY } $me->get_constraints;
1056 return wantarray ? @cons : \@cons;
1061 undef $self->{'schema'}; # destroy cyclical reference
1062 undef $_ for @{ $self->{'constraints'} };
1063 undef $_ for @{ $self->{'indices'} };
1064 undef $_ for values %{ $self->{'fields'} };
1073 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
1074 Allen Day E<lt>allenday@ucla.eduE<gt>.