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';
33 use Sub::Quote qw(quote_sub);
35 extends 'SQL::Translator::Schema::Object';
37 our $VERSION = '1.59';
39 # Stringify to our name, being careful not to pass any args through so we don't
40 # accidentally set it to undef. We also have to tweak bool so the object is
41 # still true when it doesn't have a name (which shouldn't happen!).
43 '""' => sub { shift->name },
44 'bool' => sub { $_[0]->name || $_[0] },
54 my $table = SQL::Translator::Schema::Table->new(
61 Add a constraint to the table. Returns the newly created
62 C<SQL::Translator::Schema::Constraint> object.
64 my $c1 = $table->add_constraint(
67 fields => [ 'foo_id' ],
70 my $c2 = SQL::Translator::Schema::Constraint->new( name => 'uniq' );
71 $c2 = $table->add_constraint( $constraint );
78 default => quote_sub(q{ +[] }),
85 my $constraint_class = 'SQL::Translator::Schema::Constraint';
88 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
90 $constraint->table( $self );
94 $args{'table'} = $self;
95 $constraint = $constraint_class->new( \%args ) or
96 return $self->error( $constraint_class->error );
100 # If we're trying to add a PK when one is already defined,
101 # then just add the fields to the existing definition.
104 my $pk = $self->primary_key;
105 if ( $pk && $constraint->type eq PRIMARY_KEY ) {
106 $self->primary_key( $constraint->fields );
107 $pk->name($constraint->name) if $constraint->name;
108 my %extra = $constraint->extra;
109 $pk->extra(%extra) if keys %extra;
113 elsif ( $constraint->type eq PRIMARY_KEY ) {
114 for my $fname ( $constraint->fields ) {
115 if ( my $f = $self->get_field( $fname ) ) {
116 $f->is_primary_key( 1 );
121 # See if another constraint of the same type
122 # covers the same fields. -- This doesn't work! ky
124 # elsif ( $constraint->type ne CHECK_C ) {
125 # my @field_names = $constraint->fields;
127 # grep { $_->type eq $constraint->type }
128 # $self->get_constraints
130 # my %fields = map { $_, 1 } $c->fields;
131 # for my $field_name ( @field_names ) {
132 # if ( $fields{ $field_name } ) {
143 push @{ $self->_constraints }, $constraint;
149 =head2 drop_constraint
151 Remove a constraint from the table. Returns the constraint object if the index
152 was found and removed, an error otherwise. The single parameter can be either
153 an index name or an C<SQL::Translator::Schema::Constraint> object.
155 $table->drop_constraint('myconstraint');
159 sub drop_constraint {
161 my $constraint_class = 'SQL::Translator::Schema::Constraint';
164 if ( UNIVERSAL::isa( $_[0], $constraint_class ) ) {
165 $constraint_name = shift->name;
168 $constraint_name = shift;
171 if ( ! ($self->_has_constraints && grep { $_->name eq $constraint_name } @ { $self->_constraints }) ) {
172 return $self->error(qq[Can't drop constraint: "$constraint_name" doesn't exist]);
175 my @cs = @{ $self->_constraints };
176 my ($constraint_id) = grep { $cs[$_]->name eq $constraint_name } (0..$#cs);
177 my $constraint = splice(@{$self->_constraints}, $constraint_id, 1);
184 Add an index to the table. Returns the newly created
185 C<SQL::Translator::Schema::Index> object.
187 my $i1 = $table->add_index(
189 fields => [ 'name' ],
193 my $i2 = SQL::Translator::Schema::Index->new( name => 'id' );
194 $i2 = $table->add_index( $index );
201 default => quote_sub(q{ [] }),
208 my $index_class = 'SQL::Translator::Schema::Index';
211 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
213 $index->table( $self );
217 $args{'table'} = $self;
218 $index = $index_class->new( \%args ) or return
219 $self->error( $index_class->error );
221 foreach my $ex_index ($self->get_indices) {
222 return if ($ex_index->equals($index));
224 push @{ $self->_indices }, $index;
230 Remove an index from the table. Returns the index object if the index was
231 found and removed, an error otherwise. The single parameter can be either
232 an index name of an C<SQL::Translator::Schema::Index> object.
234 $table->drop_index('myindex');
240 my $index_class = 'SQL::Translator::Schema::Index';
243 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
244 $index_name = shift->name;
250 if ( ! ($self->_has_indices && grep { $_->name eq $index_name } @{ $self->_indices }) ) {
251 return $self->error(qq[Can't drop index: "$index_name" doesn't exist]);
254 my @is = @{ $self->_indices };
255 my ($index_id) = grep { $is[$_]->name eq $index_name } (0..$#is);
256 my $index = splice(@{$self->_indices}, $index_id, 1);
263 Add an field to the table. Returns the newly created
264 C<SQL::Translator::Schema::Field> object. The "name" parameter is
265 required. If you try to create a field with the same name as an
266 existing field, you will get an error and the field will not be created.
268 my $f1 = $table->add_field(
270 data_type => 'integer',
274 my $f2 = SQL::Translator::Schema::Field->new(
278 $f2 = $table->add_field( $field2 ) or die $table->error;
285 default => quote_sub(q{ +{} }),
292 my $field_class = 'SQL::Translator::Schema::Field';
295 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
297 $field->table( $self );
301 $args{'table'} = $self;
302 $field = $field_class->new( \%args ) or return
303 $self->error( $field_class->error );
306 my $existing_order = { map { $_->order => $_->name } $self->get_fields };
308 # supplied order, possible unordered assembly
309 if ( $field->order ) {
310 if($existing_order->{$field->order}) {
312 "Requested order '%d' for column '%s' conflicts with already existing column '%s'",
315 $existing_order->{$field->order},
320 my $last_field_no = max(keys %$existing_order) || 0;
321 if ( $last_field_no != scalar keys %$existing_order ) {
323 "Table '%s' field order incomplete - unable to auto-determine order for newly added field",
328 $field->order( $last_field_no + 1 );
331 # We know we have a name as the Field->new above errors if none given.
332 my $field_name = $field->name;
334 if ( $self->get_field($field_name) ) {
335 return $self->error(qq[Can't use field name "$field_name": field exists]);
338 $self->_fields->{ $field_name } = $field;
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');
356 my $field_class = 'SQL::Translator::Schema::Field';
359 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
360 $field_name = shift->name;
366 my $cascade = $args{'cascade'};
368 if ( ! ($self->_has_fields && exists $self->_fields->{ $field_name } ) ) {
369 return $self->error(qq[Can't drop field: "$field_name" doesn't exists]);
372 my $field = delete $self->_fields->{ $field_name };
375 # Remove this field from all indices using it
376 foreach my $i ($self->get_indices()) {
377 my @fs = $i->fields();
378 @fs = grep { $_ ne $field->name } @fs;
382 # Remove this field from all constraints using it
383 foreach my $c ($self->get_constraints()) {
384 my @cs = $c->fields();
385 @cs = grep { $_ ne $field->name } @cs;
395 Get or set the comments on a table. May be called several times to
396 set and it will accumulate the comments. Called in an array context,
397 returns each comment individually; called in a scalar context, returns
398 all the comments joined on newlines.
400 $table->comments('foo');
401 $table->comments('bar');
402 print join( ', ', $table->comments ); # prints "foo, bar"
408 coerce => quote_sub(q{ ref($_[0]) eq 'ARRAY' ? $_[0] : [$_[0]] }),
409 default => quote_sub(q{ [] }),
412 around comments => sub {
415 my @comments = ref $_[0] ? @{ $_[0] } : @_;
417 for my $arg ( @comments ) {
418 $arg = $arg->[0] if ref $arg;
419 push @{ $self->$orig }, $arg if defined $arg && $arg;
422 @comments = @{$self->$orig};
423 return wantarray ? @comments
424 : @comments ? join( "\n", @comments )
428 =head2 get_constraints
430 Returns all the constraint objects as an array or array reference.
432 my @constraints = $table->get_constraints;
436 sub get_constraints {
439 if ( $self->_has_constraints ) {
441 ? @{ $self->_constraints } : $self->_constraints;
444 $self->error('No constraints');
445 return wantarray ? () : undef;
451 Returns all the index objects as an array or array reference.
453 my @indices = $table->get_indices;
460 if ( $self->_has_indices ) {
462 ? @{ $self->_indices }
466 $self->error('No indices');
467 return wantarray ? () : undef;
473 Returns a field by the name provided.
475 my $field = $table->get_field('foo');
481 my $field_name = shift or return $self->error('No field name');
482 my $case_insensitive = shift;
483 return $self->error(qq[Field "$field_name" does not exist])
484 unless $self->_has_fields;
485 if ( $case_insensitive ) {
486 $field_name = uc($field_name);
487 foreach my $field ( keys %{$self->_fields} ) {
488 return $self->_fields->{$field} if $field_name eq uc($field);
490 return $self->error(qq[Field "$field_name" does not exist]);
492 return $self->error( qq[Field "$field_name" does not exist] ) unless
493 exists $self->_fields->{ $field_name };
494 return $self->_fields->{ $field_name };
499 Returns all the field objects as an array or array reference.
501 my @fields = $table->get_fields;
509 sort { $a->[0] <=> $b->[0] }
510 map { [ $_->order, $_ ] }
511 values %{ $self->_has_fields ? $self->_fields : {} };
514 return wantarray ? @fields : \@fields;
517 $self->error('No fields');
518 return wantarray ? () : undef;
524 Determine whether the view is valid or not.
526 my $ok = $view->is_valid;
532 return $self->error('No name') unless $self->name;
533 return $self->error('No fields') unless $self->get_fields;
536 $self->get_fields, $self->get_indices, $self->get_constraints
538 return $object->error unless $object->is_valid;
544 =head2 is_trivial_link
546 True if table has no data (non-key) fields and only uses single key joins.
550 has is_trivial_link => ( is => 'lazy', init_arg => undef );
552 sub _build_is_trivial_link {
554 return 0 if $self->is_data;
558 foreach my $field ( $self->get_fields ) {
559 next unless $field->is_foreign_key;
560 $fk{$field->foreign_key_reference->reference_table}++;
563 foreach my $referenced (keys %fk){
564 if($fk{$referenced} > 1){
574 Returns true if the table has some non-key fields.
578 has is_data => ( is => 'lazy', init_arg => undef );
583 foreach my $field ( $self->get_fields ) {
584 if ( !$field->is_primary_key and !$field->is_foreign_key ) {
594 Determine whether the table can link two arg tables via many-to-many.
596 my $ok = $table->can_link($table1,$table2);
600 has _can_link => ( is => 'ro', init_arg => undef, default => quote_sub(q{ +{} }) );
603 my ( $self, $table1, $table2 ) = @_;
605 return $self->_can_link->{ $table1->name }{ $table2->name }
606 if defined $self->_can_link->{ $table1->name }{ $table2->name };
608 if ( $self->is_data == 1 ) {
609 $self->_can_link->{ $table1->name }{ $table2->name } = [0];
610 $self->_can_link->{ $table2->name }{ $table1->name } = [0];
611 return $self->_can_link->{ $table1->name }{ $table2->name };
616 foreach my $field ( $self->get_fields ) {
617 if ( $field->is_foreign_key ) {
618 push @{ $fk{ $field->foreign_key_reference->reference_table } },
619 $field->foreign_key_reference;
623 if ( !defined( $fk{ $table1->name } ) or !defined( $fk{ $table2->name } ) )
625 $self->_can_link->{ $table1->name }{ $table2->name } = [0];
626 $self->_can_link->{ $table2->name }{ $table1->name } = [0];
627 return $self->_can_link->{ $table1->name }{ $table2->name };
630 # trivial traversal, only one way to link the two tables
631 if ( scalar( @{ $fk{ $table1->name } } == 1 )
632 and scalar( @{ $fk{ $table2->name } } == 1 ) )
634 $self->_can_link->{ $table1->name }{ $table2->name } =
635 [ 'one2one', $fk{ $table1->name }, $fk{ $table2->name } ];
636 $self->_can_link->{ $table1->name }{ $table2->name } =
637 [ 'one2one', $fk{ $table2->name }, $fk{ $table1->name } ];
639 # non-trivial traversal. one way to link table2,
640 # many ways to link table1
642 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
643 and scalar( @{ $fk{ $table2->name } } == 1 ) )
645 $self->_can_link->{ $table1->name }{ $table2->name } =
646 [ 'many2one', $fk{ $table1->name }, $fk{ $table2->name } ];
647 $self->_can_link->{ $table2->name }{ $table1->name } =
648 [ 'one2many', $fk{ $table2->name }, $fk{ $table1->name } ];
650 # non-trivial traversal. one way to link table1,
651 # many ways to link table2
653 elsif ( scalar( @{ $fk{ $table1->name } } == 1 )
654 and scalar( @{ $fk{ $table2->name } } > 1 ) )
656 $self->_can_link->{ $table1->name }{ $table2->name } =
657 [ 'one2many', $fk{ $table1->name }, $fk{ $table2->name } ];
658 $self->_can_link->{ $table2->name }{ $table1->name } =
659 [ 'many2one', $fk{ $table2->name }, $fk{ $table1->name } ];
661 # non-trivial traversal. many ways to link table1 and table2
663 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
664 and scalar( @{ $fk{ $table2->name } } > 1 ) )
666 $self->_can_link->{ $table1->name }{ $table2->name } =
667 [ 'many2many', $fk{ $table1->name }, $fk{ $table2->name } ];
668 $self->_can_link->{ $table2->name }{ $table1->name } =
669 [ 'many2many', $fk{ $table2->name }, $fk{ $table1->name } ];
671 # one of the tables didn't export a key
672 # to this table, no linking possible
675 $self->_can_link->{ $table1->name }{ $table2->name } = [0];
676 $self->_can_link->{ $table2->name }{ $table1->name } = [0];
679 return $self->_can_link->{ $table1->name }{ $table2->name };
684 Get or set the table's name.
686 Errors ("No table name") if you try to set a blank name.
688 If provided an argument, checks the schema object for a table of
689 that name and disallows the change if one exists (setting the error to
690 "Can't use table name "%s": table exists").
692 my $table_name = $table->name('foo');
698 isa => sub { throw("No table name") unless $_[0] },
705 if ( my ($arg) = @_ ) {
706 if ( my $schema = $self->schema ) {
707 return $self->error( qq[Can't use table name "$arg": table exists] )
708 if $schema->get_table( $arg );
712 return ex2err($orig, $self, @_);
717 Get or set the table's schema object.
719 my $schema = $table->schema;
723 has schema => ( is => 'rw', isa => schema_obj('Schema'), weak_ref => 1 );
725 around schema => \&ex2err;
733 Gets or sets the table's primary key(s). Takes one or more field
734 names (as a string, list or array[ref]) as an argument. If the field
735 names are present, it will create a new PK if none exists, or it will
736 add to the fields of an existing PK (and will unique the field names).
737 Returns the C<SQL::Translator::Schema::Constraint> object representing
742 $table->primary_key('id');
743 $table->primary_key(['name']);
744 $table->primary_key('id','name']);
745 $table->primary_key(['id','name']);
746 $table->primary_key('id,name');
747 $table->primary_key(qw[ id name ]);
749 my $pk = $table->primary_key;
754 my $fields = parse_list_arg( @_ );
758 for my $f ( @$fields ) {
759 return $self->error(qq[Invalid field "$f"]) unless
760 $self->get_field($f);
764 for my $c ( $self->get_constraints ) {
765 if ( $c->type eq PRIMARY_KEY ) {
767 $c->fields( @{ $c->fields }, @$fields );
773 $constraint = $self->add_constraint(
784 for my $c ( $self->get_constraints ) {
785 return $c if $c->type eq PRIMARY_KEY;
794 Get or set the table's options (e.g., table types for MySQL). Returns
795 an array or array reference.
797 my @options = $table->options;
801 with ListAttr options => ( append => 1 );
805 Get or set the table's order.
807 my $order = $table->order(3);
811 has order => ( is => 'rw', default => quote_sub(q{ 0 }) );
813 around order => sub {
814 my ( $orig, $self, $arg ) = @_;
816 if ( defined $arg && $arg =~ /^\d+$/ ) {
817 return $self->$orig($arg);
825 Read-only method to return a list or array ref of the field names. Returns undef
826 or an empty list if the table has no fields set. Useful if you want to
827 avoid the overload magic of the Field objects returned by the get_fields method.
829 my @names = $constraint->field_names;
840 return wantarray ? @fields : \@fields;
843 $self->error('No fields');
844 return wantarray ? () : undef;
854 Determines if this table is the same as another
856 my $isIdentical = $table1->equals( $table2 );
862 my $case_insensitive = shift;
864 return 0 unless $self->SUPER::equals($other);
865 return 0 unless $case_insensitive ? uc($self->name) eq uc($other->name) : $self->name eq $other->name;
866 return 0 unless $self->_compare_objects(scalar $self->options, scalar $other->options);
867 return 0 unless $self->_compare_objects(scalar $self->extra, scalar $other->extra);
870 # Go through our fields
872 foreach my $field ( $self->get_fields ) {
873 my $otherField = $other->get_field($field->name, $case_insensitive);
874 return 0 unless $field->equals($otherField, $case_insensitive);
875 $checkedFields{$field->name} = 1;
877 # Go through the other table's fields
878 foreach my $otherField ( $other->get_fields ) {
879 next if $checkedFields{$otherField->name};
884 # Go through our constraints
885 my %checkedConstraints;
887 foreach my $constraint ( $self->get_constraints ) {
888 foreach my $otherConstraint ( $other->get_constraints ) {
889 if ( $constraint->equals($otherConstraint, $case_insensitive) ) {
890 $checkedConstraints{$otherConstraint} = 1;
896 # Go through the other table's constraints
898 foreach my $otherConstraint ( $other->get_constraints ) {
899 next if $checkedFields{$otherConstraint};
900 foreach my $constraint ( $self->get_constraints ) {
901 if ( $otherConstraint->equals($constraint, $case_insensitive) ) {
909 # Go through our indices
912 foreach my $index ( $self->get_indices ) {
913 foreach my $otherIndex ( $other->get_indices ) {
914 if ( $index->equals($otherIndex, $case_insensitive) ) {
915 $checkedIndices{$otherIndex} = 1;
921 # Go through the other table's indices
923 foreach my $otherIndex ( $other->get_indices ) {
924 next if $checkedIndices{$otherIndex};
925 foreach my $index ( $self->get_indices ) {
926 if ( $otherIndex->equals($index, $case_insensitive) ) {
936 =head1 LOOKUP METHODS
938 The following are a set of shortcut methods for getting commonly used lists of
939 fields and constraints. They all return lists or array refs of Field or
946 The primary key fields.
950 All foreign key fields.
954 All the fields except the primary key.
962 All fields with unique constraints.
964 =item unique_constraints
966 All this tables unique constraints.
968 =item fkey_constraints
970 All this tables foreign key constraints. (See primary_key method to get the
971 primary key constraint)
979 my @fields = grep { $_->is_primary_key } $me->get_fields;
980 return wantarray ? @fields : \@fields;
986 push @fields, $_->fields foreach $me->fkey_constraints;
987 return wantarray ? @fields : \@fields;
992 my @fields = grep { !$_->is_primary_key } $me->get_fields;
993 return wantarray ? @fields : \@fields;
999 grep { !$_->is_foreign_key and !$_->is_primary_key } $me->get_fields;
1000 return wantarray ? @fields : \@fields;
1006 push @fields, $_->fields foreach $me->unique_constraints;
1007 return wantarray ? @fields : \@fields;
1010 sub unique_constraints {
1012 my @cons = grep { $_->type eq UNIQUE } $me->get_constraints;
1013 return wantarray ? @cons : \@cons;
1016 sub fkey_constraints {
1018 my @cons = grep { $_->type eq FOREIGN_KEY } $me->get_constraints;
1019 return wantarray ? @cons : \@cons;
1022 # Must come after all 'has' declarations
1023 around new => \&ex2err;
1031 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
1032 Allen Day E<lt>allenday@ucla.eduE<gt>.