1 package SQL::Translator::Schema::Table;
3 # ----------------------------------------------------------------------
4 # $Id: Table.pm,v 1.37 2007-10-24 10:55:44 schiffbruechige Exp $
5 # ----------------------------------------------------------------------
6 # Copyright (C) 2002-4 SQLFairy Authors
8 # This program is free software; you can redistribute it and/or
9 # modify it under the terms of the GNU General Public License as
10 # published by the Free Software Foundation; version 2.
12 # This program is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 # General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, write to the Free Software
19 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21 # -------------------------------------------------------------------
27 SQL::Translator::Schema::Table - SQL::Translator table object
31 use SQL::Translator::Schema::Table;
32 my $table = SQL::Translator::Schema::Table->new( name => 'foo' );
36 C<SQL::Translator::Schema::Table> is the table object.
43 use SQL::Translator::Utils 'parse_list_arg';
44 use SQL::Translator::Schema::Constants;
45 use SQL::Translator::Schema::Constraint;
46 use SQL::Translator::Schema::Field;
47 use SQL::Translator::Schema::Index;
50 use base 'SQL::Translator::Schema::Object';
52 use vars qw( $VERSION $FIELD_ORDER );
54 $VERSION = sprintf "%d.%02d", q$Revision: 1.37 $ =~ /(\d+)\.(\d+)/;
57 # Stringify to our name, being careful not to pass any args through so we don't
58 # accidentally set it to undef. We also have to tweak bool so the object is
59 # still true when it doesn't have a name (which shouldn't happen!).
61 '""' => sub { shift->name },
62 'bool' => sub { $_[0]->name || $_[0] },
66 # ----------------------------------------------------------------------
68 __PACKAGE__->_attributes( qw/schema name comments options order/ );
76 my $table = SQL::Translator::Schema::Table->new(
83 # ----------------------------------------------------------------------
90 Add a constraint to the table. Returns the newly created
91 C<SQL::Translator::Schema::Constraint> object.
93 my $c1 = $table->add_constraint(
96 fields => [ 'foo_id' ],
99 my $c2 = SQL::Translator::Schema::Constraint->new( name => 'uniq' );
100 $c2 = $table->add_constraint( $constraint );
105 my $constraint_class = 'SQL::Translator::Schema::Constraint';
108 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
110 $constraint->table( $self );
114 $args{'table'} = $self;
115 $constraint = $constraint_class->new( \%args ) or
116 return $self->error( $constraint_class->error );
120 # If we're trying to add a PK when one is already defined,
121 # then just add the fields to the existing definition.
124 my $pk = $self->primary_key;
125 if ( $pk && $constraint->type eq PRIMARY_KEY ) {
126 $self->primary_key( $constraint->fields );
127 $pk->name($constraint->name) if $constraint->name;
128 my %extra = $constraint->extra;
129 $pk->extra(%extra) if keys %extra;
133 elsif ( $constraint->type eq PRIMARY_KEY ) {
134 for my $fname ( $constraint->fields ) {
135 if ( my $f = $self->get_field( $fname ) ) {
136 $f->is_primary_key( 1 );
141 # See if another constraint of the same type
142 # covers the same fields. -- This doesn't work! ky
144 # elsif ( $constraint->type ne CHECK_C ) {
145 # my @field_names = $constraint->fields;
147 # grep { $_->type eq $constraint->type }
148 # $self->get_constraints
150 # my %fields = map { $_, 1 } $c->fields;
151 # for my $field_name ( @field_names ) {
152 # if ( $fields{ $field_name } ) {
163 push @{ $self->{'constraints'} }, $constraint;
169 # ----------------------------------------------------------------------
170 sub drop_constraint {
174 =head2 drop_constraint
176 Remove a constraint from the table. Returns the constraint object if the index
177 was found and removed, an error otherwise. The single parameter can be either
178 an index name or an C<SQL::Translator::Schema::Constraint> object.
180 $table->drop_constraint('myconstraint');
185 my $constraint_class = 'SQL::Translator::Schema::Constraint';
188 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
189 $constraint_name = shift->name;
192 $constraint_name = shift;
195 if ( ! grep { $_->name eq $constraint_name } @ { $self->{'constraints'} } ) {
196 return $self->error(qq[Can't drop constraint: "$constraint_name" doesn't exist]);
199 my @cs = @{ $self->{'constraints'} };
200 my ($constraint_id) = grep { $cs[$_]->name eq $constraint_name } (0..$#cs);
201 my $constraint = splice(@{$self->{'constraints'}}, $constraint_id, 1);
206 # ----------------------------------------------------------------------
213 Add an index to the table. Returns the newly created
214 C<SQL::Translator::Schema::Index> object.
216 my $i1 = $table->add_index(
218 fields => [ 'name' ],
222 my $i2 = SQL::Translator::Schema::Index->new( name => 'id' );
223 $i2 = $table->add_index( $index );
228 my $index_class = 'SQL::Translator::Schema::Index';
231 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
233 $index->table( $self );
237 $args{'table'} = $self;
238 $index = $index_class->new( \%args ) or return
239 $self->error( $index_class->error );
241 foreach my $ex_index ($self->get_indices) {
242 return if ($ex_index->equals($index));
244 push @{ $self->{'indices'} }, $index;
248 # ----------------------------------------------------------------------
255 Remove an index from the table. Returns the index object if the index was
256 found and removed, an error otherwise. The single parameter can be either
257 an index name of an C<SQL::Translator::Schema::Index> object.
259 $table->drop_index('myindex');
264 my $index_class = 'SQL::Translator::Schema::Index';
267 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
268 $index_name = shift->name;
274 if ( ! grep { $_->name eq $index_name } @{ $self->{'indices'} }) {
275 return $self->error(qq[Can't drop index: "$index_name" doesn't exist]);
278 my @is = @{ $self->{'indices'} };
279 my ($index_id) = grep { $is[$_]->name eq $index_name } (0..$#is);
280 my $index = splice(@{$self->{'indices'}}, $index_id, 1);
285 # ----------------------------------------------------------------------
292 Add an field to the table. Returns the newly created
293 C<SQL::Translator::Schema::Field> object. The "name" parameter is
294 required. If you try to create a field with the same name as an
295 existing field, you will get an error and the field will not be created.
297 my $f1 = $table->add_field(
299 data_type => 'integer',
303 my $f2 = SQL::Translator::Schema::Field->new(
307 $f2 = $table->add_field( $field2 ) or die $table->error;
312 my $field_class = 'SQL::Translator::Schema::Field';
315 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
317 $field->table( $self );
321 $args{'table'} = $self;
322 $field = $field_class->new( \%args ) or return
323 $self->error( $field_class->error );
326 $field->order( ++$FIELD_ORDER );
327 # We know we have a name as the Field->new above errors if none given.
328 my $field_name = $field->name;
330 if ( exists $self->{'fields'}{ $field_name } ) {
331 return $self->error(qq[Can't create field: "$field_name" exists]);
334 $self->{'fields'}{ $field_name } = $field;
339 # ----------------------------------------------------------------------
346 Remove a field from the table. Returns the field object if the field was
347 found and removed, an error otherwise. The single parameter can be either
348 a field name or an C<SQL::Translator::Schema::Field> object.
350 $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 ( ! 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;
392 # ----------------------------------------------------------------------
399 Get or set the comments on a table. May be called several times to
400 set and it will accumulate the comments. Called in an array context,
401 returns each comment individually; called in a scalar context, returns
402 all the comments joined on newlines.
404 $table->comments('foo');
405 $table->comments('bar');
406 print join( ', ', $table->comments ); # prints "foo, bar"
411 my @comments = ref $_[0] ? @{ $_[0] } : @_;
413 for my $arg ( @comments ) {
414 $arg = $arg->[0] if ref $arg;
415 push @{ $self->{'comments'} }, $arg if defined $arg && $arg;
418 if ( @{ $self->{'comments'} || [] } ) {
420 ? @{ $self->{'comments'} }
421 : join( "\n", @{ $self->{'comments'} } )
425 return wantarray ? () : undef;
429 # ----------------------------------------------------------------------
430 sub get_constraints {
434 =head2 get_constraints
436 Returns all the constraint objects as an array or array reference.
438 my @constraints = $table->get_constraints;
444 if ( ref $self->{'constraints'} ) {
446 ? @{ $self->{'constraints'} } : $self->{'constraints'};
449 $self->error('No constraints');
450 return wantarray ? () : undef;
454 # ----------------------------------------------------------------------
461 Returns all the index objects as an array or array reference.
463 my @indices = $table->get_indices;
469 if ( ref $self->{'indices'} ) {
471 ? @{ $self->{'indices'} }
472 : $self->{'indices'};
475 $self->error('No indices');
476 return wantarray ? () : undef;
480 # ----------------------------------------------------------------------
487 Returns a field by the name provided.
489 my $field = $table->get_field('foo');
494 my $field_name = shift or return $self->error('No field name');
495 my $case_insensitive = shift;
496 if ( $case_insensitive ) {
497 $field_name = uc($field_name);
498 foreach my $field ( keys %{$self->{fields}} ) {
499 return $self->{fields}{$field} if $field_name eq uc($field);
501 return $self->error(qq[Field "$field_name" does not exist]);
503 return $self->error( qq[Field "$field_name" does not exist] ) unless
504 exists $self->{'fields'}{ $field_name };
505 return $self->{'fields'}{ $field_name };
508 # ----------------------------------------------------------------------
515 Returns all the field objects as an array or array reference.
517 my @fields = $table->get_fields;
524 sort { $a->[0] <=> $b->[0] }
525 map { [ $_->order, $_ ] }
526 values %{ $self->{'fields'} || {} };
529 return wantarray ? @fields : \@fields;
532 $self->error('No fields');
533 return wantarray ? () : undef;
537 # ----------------------------------------------------------------------
544 Determine whether the view is valid or not.
546 my $ok = $view->is_valid;
551 return $self->error('No name') unless $self->name;
552 return $self->error('No fields') unless $self->get_fields;
555 $self->get_fields, $self->get_indices, $self->get_constraints
557 return $object->error unless $object->is_valid;
563 # ----------------------------------------------------------------------
564 sub is_trivial_link {
568 =head2 is_trivial_link
570 True if table has no data (non-key) fields and only uses single key joins.
575 return 0 if $self->is_data;
576 return $self->{'is_trivial_link'} if defined $self->{'is_trivial_link'};
578 $self->{'is_trivial_link'} = 1;
582 foreach my $field ( $self->get_fields ) {
583 next unless $field->is_foreign_key;
584 $fk{$field->foreign_key_reference->reference_table}++;
587 foreach my $referenced (keys %fk){
588 if($fk{$referenced} > 1){
589 $self->{'is_trivial_link'} = 0;
594 return $self->{'is_trivial_link'};
604 Returns true if the table has some non-key fields.
609 return $self->{'is_data'} if defined $self->{'is_data'};
611 $self->{'is_data'} = 0;
613 foreach my $field ( $self->get_fields ) {
614 if ( !$field->is_primary_key and !$field->is_foreign_key ) {
615 $self->{'is_data'} = 1;
616 return $self->{'is_data'};
620 return $self->{'is_data'};
623 # ----------------------------------------------------------------------
630 Determine whether the table can link two arg tables via many-to-many.
632 my $ok = $table->can_link($table1,$table2);
636 my ( $self, $table1, $table2 ) = @_;
638 return $self->{'can_link'}{ $table1->name }{ $table2->name }
639 if defined $self->{'can_link'}{ $table1->name }{ $table2->name };
641 if ( $self->is_data == 1 ) {
642 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
643 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
644 return $self->{'can_link'}{ $table1->name }{ $table2->name };
649 foreach my $field ( $self->get_fields ) {
650 if ( $field->is_foreign_key ) {
651 push @{ $fk{ $field->foreign_key_reference->reference_table } },
652 $field->foreign_key_reference;
656 if ( !defined( $fk{ $table1->name } ) or !defined( $fk{ $table2->name } ) )
658 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
659 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
660 return $self->{'can_link'}{ $table1->name }{ $table2->name };
663 # trivial traversal, only one way to link the two tables
664 if ( scalar( @{ $fk{ $table1->name } } == 1 )
665 and scalar( @{ $fk{ $table2->name } } == 1 ) )
667 $self->{'can_link'}{ $table1->name }{ $table2->name } =
668 [ 'one2one', $fk{ $table1->name }, $fk{ $table2->name } ];
669 $self->{'can_link'}{ $table1->name }{ $table2->name } =
670 [ 'one2one', $fk{ $table2->name }, $fk{ $table1->name } ];
672 # non-trivial traversal. one way to link table2,
673 # many ways to link table1
675 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
676 and scalar( @{ $fk{ $table2->name } } == 1 ) )
678 $self->{'can_link'}{ $table1->name }{ $table2->name } =
679 [ 'many2one', $fk{ $table1->name }, $fk{ $table2->name } ];
680 $self->{'can_link'}{ $table2->name }{ $table1->name } =
681 [ 'one2many', $fk{ $table2->name }, $fk{ $table1->name } ];
683 # non-trivial traversal. one way to link table1,
684 # many ways to link table2
686 elsif ( scalar( @{ $fk{ $table1->name } } == 1 )
687 and scalar( @{ $fk{ $table2->name } } > 1 ) )
689 $self->{'can_link'}{ $table1->name }{ $table2->name } =
690 [ 'one2many', $fk{ $table1->name }, $fk{ $table2->name } ];
691 $self->{'can_link'}{ $table2->name }{ $table1->name } =
692 [ 'many2one', $fk{ $table2->name }, $fk{ $table1->name } ];
694 # non-trivial traversal. many ways to link table1 and table2
696 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
697 and scalar( @{ $fk{ $table2->name } } > 1 ) )
699 $self->{'can_link'}{ $table1->name }{ $table2->name } =
700 [ 'many2many', $fk{ $table1->name }, $fk{ $table2->name } ];
701 $self->{'can_link'}{ $table2->name }{ $table1->name } =
702 [ 'many2many', $fk{ $table2->name }, $fk{ $table1->name } ];
704 # one of the tables didn't export a key
705 # to this table, no linking possible
708 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
709 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
712 return $self->{'can_link'}{ $table1->name }{ $table2->name };
715 # ----------------------------------------------------------------------
722 Get or set the table's name.
724 Errors ("No table name") if you try to set a blank name.
726 If provided an argument, checks the schema object for a table of
727 that name and disallows the change if one exists (setting the error to
728 "Can't use table name "%s": table exists").
730 my $table_name = $table->name('foo');
737 my $arg = shift || return $self->error( "No table name" );
738 if ( my $schema = $self->schema ) {
739 return $self->error( qq[Can't use table name "$arg": table exists] )
740 if $schema->get_table( $arg );
742 $self->{'name'} = $arg;
745 return $self->{'name'} || '';
748 # ----------------------------------------------------------------------
755 Get or set the table's schema object.
757 my $schema = $table->schema;
762 if ( my $arg = shift ) {
763 return $self->error('Not a schema object') unless
764 UNIVERSAL::isa( $arg, 'SQL::Translator::Schema' );
765 $self->{'schema'} = $arg;
768 return $self->{'schema'};
771 # ----------------------------------------------------------------------
778 Gets or sets the table's primary key(s). Takes one or more field
779 names (as a string, list or array[ref]) as an argument. If the field
780 names are present, it will create a new PK if none exists, or it will
781 add to the fields of an existing PK (and will unique the field names).
782 Returns the C<SQL::Translator::Schema::Constraint> object representing
787 $table->primary_key('id');
788 $table->primary_key(['name']);
789 $table->primary_key('id','name']);
790 $table->primary_key(['id','name']);
791 $table->primary_key('id,name');
792 $table->primary_key(qw[ id name ]);
794 my $pk = $table->primary_key;
799 my $fields = parse_list_arg( @_ );
803 for my $f ( @$fields ) {
804 return $self->error(qq[Invalid field "$f"]) unless
805 $self->get_field($f);
809 for my $c ( $self->get_constraints ) {
810 if ( $c->type eq PRIMARY_KEY ) {
812 $c->fields( @{ $c->fields }, @$fields );
818 $constraint = $self->add_constraint(
829 for my $c ( $self->get_constraints ) {
830 return $c if $c->type eq PRIMARY_KEY;
837 # ----------------------------------------------------------------------
844 Get or set the table's options (e.g., table types for MySQL). Returns
845 an array or array reference.
847 my @options = $table->options;
852 my $options = parse_list_arg( @_ );
854 push @{ $self->{'options'} }, @$options;
856 if ( ref $self->{'options'} ) {
857 return wantarray ? @{ $self->{'options'} || [] } : ($self->{'options'} || '');
860 return wantarray ? () : [];
864 # ----------------------------------------------------------------------
871 Get or set the table's order.
873 my $order = $table->order(3);
877 my ( $self, $arg ) = @_;
879 if ( defined $arg && $arg =~ /^\d+$/ ) {
880 $self->{'order'} = $arg;
883 return $self->{'order'} || 0;
886 # ----------------------------------------------------------------------
891 Read-only method to return a list or array ref of the field names. Returns undef
892 or an empty list if the table has no fields set. Usefull if you want to
893 avoid the overload magic of the Field objects returned by the get_fields method.
895 my @names = $constraint->field_names;
902 sort { $a->order <=> $b->order }
903 values %{ $self->{'fields'} || {} };
906 return wantarray ? @fields : \@fields;
909 $self->error('No fields');
910 return wantarray ? () : undef;
914 # ----------------------------------------------------------------------
921 Determines if this table is the same as another
923 my $isIdentical = $table1->equals( $table2 );
929 my $case_insensitive = shift;
931 return 0 unless $self->SUPER::equals($other);
932 return 0 unless $case_insensitive ? uc($self->name) eq uc($other->name) : $self->name eq $other->name;
933 return 0 unless $self->_compare_objects(scalar $self->options, scalar $other->options);
934 return 0 unless $self->_compare_objects(scalar $self->extra, scalar $other->extra);
937 # Go through our fields
939 foreach my $field ( $self->get_fields ) {
940 my $otherField = $other->get_field($field->name, $case_insensitive);
941 return 0 unless $field->equals($otherField, $case_insensitive);
942 $checkedFields{$field->name} = 1;
944 # Go through the other table's fields
945 foreach my $otherField ( $other->get_fields ) {
946 next if $checkedFields{$otherField->name};
951 # Go through our constraints
952 my %checkedConstraints;
954 foreach my $constraint ( $self->get_constraints ) {
955 foreach my $otherConstraint ( $other->get_constraints ) {
956 if ( $constraint->equals($otherConstraint, $case_insensitive) ) {
957 $checkedConstraints{$otherConstraint} = 1;
963 # Go through the other table's constraints
965 foreach my $otherConstraint ( $other->get_constraints ) {
966 next if $checkedFields{$otherConstraint};
967 foreach my $constraint ( $self->get_constraints ) {
968 if ( $otherConstraint->equals($constraint, $case_insensitive) ) {
976 # Go through our indices
979 foreach my $index ( $self->get_indices ) {
980 foreach my $otherIndex ( $other->get_indices ) {
981 if ( $index->equals($otherIndex, $case_insensitive) ) {
982 $checkedIndices{$otherIndex} = 1;
988 # Go through the other table's indices
990 foreach my $otherIndex ( $other->get_indices ) {
991 next if $checkedIndices{$otherIndex};
992 foreach my $index ( $self->get_indices ) {
993 if ( $otherIndex->equals($index, $case_insensitive) ) {
1003 # ----------------------------------------------------------------------
1005 =head1 LOOKUP METHODS
1007 The following are a set of shortcut methods for getting commonly used lists of
1008 fields and constraints. They all return lists or array refs of Field or
1015 The primary key fields.
1019 All foreign key fields.
1021 =item nonpkey_fields
1023 All the fields except the primary key.
1031 All fields with unique constraints.
1033 =item unique_constraints
1035 All this tables unique constraints.
1037 =item fkey_constraints
1039 All this tables foreign key constraints. (See primary_key method to get the
1040 primary key constraint)
1048 my @fields = grep { $_->is_primary_key } $me->get_fields;
1049 return wantarray ? @fields : \@fields;
1052 # ----------------------------------------------------------------------
1056 push @fields, $_->fields foreach $me->fkey_constraints;
1057 return wantarray ? @fields : \@fields;
1060 # ----------------------------------------------------------------------
1061 sub nonpkey_fields {
1063 my @fields = grep { !$_->is_primary_key } $me->get_fields;
1064 return wantarray ? @fields : \@fields;
1067 # ----------------------------------------------------------------------
1071 grep { !$_->is_foreign_key and !$_->is_primary_key } $me->get_fields;
1072 return wantarray ? @fields : \@fields;
1075 # ----------------------------------------------------------------------
1079 push @fields, $_->fields foreach $me->unique_constraints;
1080 return wantarray ? @fields : \@fields;
1083 # ----------------------------------------------------------------------
1084 sub unique_constraints {
1086 my @cons = grep { $_->type eq UNIQUE } $me->get_constraints;
1087 return wantarray ? @cons : \@cons;
1090 # ----------------------------------------------------------------------
1091 sub fkey_constraints {
1093 my @cons = grep { $_->type eq FOREIGN_KEY } $me->get_constraints;
1094 return wantarray ? @cons : \@cons;
1097 # ----------------------------------------------------------------------
1100 undef $self->{'schema'}; # destroy cyclical reference
1101 undef $_ for @{ $self->{'constraints'} };
1102 undef $_ for @{ $self->{'indices'} };
1103 undef $_ for values %{ $self->{'fields'} };
1108 # ----------------------------------------------------------------------
1114 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>,
1115 Allen Day E<lt>allenday@ucla.eduE<gt>.