1 package SQL::Translator::Schema::Table;
3 # ----------------------------------------------------------------------
4 # $Id: Table.pm,v 1.28 2004-11-05 13:19:31 grommit 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.28 $ =~ /(\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 );
130 elsif ( $constraint->type eq PRIMARY_KEY ) {
131 for my $fname ( $constraint->fields ) {
132 if ( my $f = $self->get_field( $fname ) ) {
133 $f->is_primary_key( 1 );
138 # See if another constraint of the same type
139 # covers the same fields. -- This doesn't work! ky
141 # elsif ( $constraint->type ne CHECK_C ) {
142 # my @field_names = $constraint->fields;
144 # grep { $_->type eq $constraint->type }
145 # $self->get_constraints
147 # my %fields = map { $_, 1 } $c->fields;
148 # for my $field_name ( @field_names ) {
149 # if ( $fields{ $field_name } ) {
160 push @{ $self->{'constraints'} }, $constraint;
166 # ----------------------------------------------------------------------
173 Add an index to the table. Returns the newly created
174 C<SQL::Translator::Schema::Index> object.
176 my $i1 = $table->add_index(
178 fields => [ 'name' ],
182 my $i2 = SQL::Translator::Schema::Index->new( name => 'id' );
183 $i2 = $table->add_index( $index );
188 my $index_class = 'SQL::Translator::Schema::Index';
191 if ( UNIVERSAL::isa( $_[0], $index_class ) ) {
193 $index->table( $self );
197 $args{'table'} = $self;
198 $index = $index_class->new( \%args ) or return
199 $self->error( $index_class->error );
202 push @{ $self->{'indices'} }, $index;
206 # ----------------------------------------------------------------------
213 Add an field to the table. Returns the newly created
214 C<SQL::Translator::Schema::Field> object. The "name" parameter is
215 required. If you try to create a field with the same name as an
216 existing field, you will get an error and the field will not be created.
218 my $f1 = $table->add_field(
220 data_type => 'integer',
224 my $f2 = SQL::Translator::Schema::Field->new(
228 $f2 = $table->add_field( $field2 ) or die $table->error;
233 my $field_class = 'SQL::Translator::Schema::Field';
236 if ( UNIVERSAL::isa( $_[0], $field_class ) ) {
238 $field->table( $self );
242 $args{'table'} = $self;
243 $field = $field_class->new( \%args ) or return
244 $self->error( $field_class->error );
247 $field->order( ++$FIELD_ORDER );
248 # We know we have a name as the Field->new above errors if none given.
249 my $field_name = $field->name;
251 if ( exists $self->{'fields'}{ $field_name } ) {
252 return $self->error(qq[Can't create field: "$field_name" exists]);
255 $self->{'fields'}{ $field_name } = $field;
261 # ----------------------------------------------------------------------
268 Get or set the comments on a table. May be called several times to
269 set and it will accumulate the comments. Called in an array context,
270 returns each comment individually; called in a scalar context, returns
271 all the comments joined on newlines.
273 $table->comments('foo');
274 $table->comments('bar');
275 print join( ', ', $table->comments ); # prints "foo, bar"
280 my @comments = ref $_[0] ? @{ $_[0] } : @_;
282 for my $arg ( @comments ) {
283 $arg = $arg->[0] if ref $arg;
284 push @{ $self->{'comments'} }, $arg if defined $arg && $arg;
287 if ( @{ $self->{'comments'} || [] } ) {
289 ? @{ $self->{'comments'} }
290 : join( "\n", @{ $self->{'comments'} } )
294 return wantarray ? () : undef;
298 # ----------------------------------------------------------------------
299 sub get_constraints {
303 =head2 get_constraints
305 Returns all the constraint objects as an array or array reference.
307 my @constraints = $table->get_constraints;
313 if ( ref $self->{'constraints'} ) {
315 ? @{ $self->{'constraints'} } : $self->{'constraints'};
318 $self->error('No constraints');
319 return wantarray ? () : undef;
323 # ----------------------------------------------------------------------
330 Returns all the index objects as an array or array reference.
332 my @indices = $table->get_indices;
338 if ( ref $self->{'indices'} ) {
340 ? @{ $self->{'indices'} }
341 : $self->{'indices'};
344 $self->error('No indices');
345 return wantarray ? () : undef;
349 # ----------------------------------------------------------------------
356 Returns a field by the name provided.
358 my $field = $table->get_field('foo');
363 my $field_name = shift or return $self->error('No field name');
364 return $self->error( qq[Field "$field_name" does not exist] ) unless
365 exists $self->{'fields'}{ $field_name };
366 return $self->{'fields'}{ $field_name };
369 # ----------------------------------------------------------------------
376 Returns all the field objects as an array or array reference.
378 my @fields = $table->get_fields;
385 sort { $a->[0] <=> $b->[0] }
386 map { [ $_->order, $_ ] }
387 values %{ $self->{'fields'} || {} };
390 return wantarray ? @fields : \@fields;
393 $self->error('No fields');
394 return wantarray ? () : undef;
398 # ----------------------------------------------------------------------
405 Determine whether the view is valid or not.
407 my $ok = $view->is_valid;
412 return $self->error('No name') unless $self->name;
413 return $self->error('No fields') unless $self->get_fields;
416 $self->get_fields, $self->get_indices, $self->get_constraints
418 return $object->error unless $object->is_valid;
424 # ----------------------------------------------------------------------
425 sub is_trivial_link {
429 =head2 is_trivial_link
431 True if table has no data (non-key) fields and only uses single key joins.
436 return 0 if $self->is_data;
437 return $self->{'is_trivial_link'} if defined $self->{'is_trivial_link'};
439 $self->{'is_trivial_link'} = 1;
443 foreach my $field ( $self->get_fields ) {
444 next unless $field->is_foreign_key;
445 $fk{$field->foreign_key_reference->reference_table}++;
448 foreach my $referenced (keys %fk){
449 if($fk{$referenced} > 1){
450 $self->{'is_trivial_link'} = 0;
455 return $self->{'is_trivial_link'};
465 Returns true if the table has some non-key fields.
470 return $self->{'is_data'} if defined $self->{'is_data'};
472 $self->{'is_data'} = 0;
474 foreach my $field ( $self->get_fields ) {
475 if ( !$field->is_primary_key and !$field->is_foreign_key ) {
476 $self->{'is_data'} = 1;
477 return $self->{'is_data'};
481 return $self->{'is_data'};
484 # ----------------------------------------------------------------------
491 Determine whether the table can link two arg tables via many-to-many.
493 my $ok = $table->can_link($table1,$table2);
497 my ( $self, $table1, $table2 ) = @_;
499 return $self->{'can_link'}{ $table1->name }{ $table2->name }
500 if defined $self->{'can_link'}{ $table1->name }{ $table2->name };
502 if ( $self->is_data == 1 ) {
503 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
504 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
505 return $self->{'can_link'}{ $table1->name }{ $table2->name };
510 foreach my $field ( $self->get_fields ) {
511 if ( $field->is_foreign_key ) {
512 push @{ $fk{ $field->foreign_key_reference->reference_table } },
513 $field->foreign_key_reference;
517 if ( !defined( $fk{ $table1->name } ) or !defined( $fk{ $table2->name } ) )
519 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
520 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
521 return $self->{'can_link'}{ $table1->name }{ $table2->name };
524 # trivial traversal, only one way to link the two tables
525 if ( scalar( @{ $fk{ $table1->name } } == 1 )
526 and scalar( @{ $fk{ $table2->name } } == 1 ) )
528 $self->{'can_link'}{ $table1->name }{ $table2->name } =
529 [ 'one2one', $fk{ $table1->name }, $fk{ $table2->name } ];
530 $self->{'can_link'}{ $table1->name }{ $table2->name } =
531 [ 'one2one', $fk{ $table2->name }, $fk{ $table1->name } ];
533 # non-trivial traversal. one way to link table2,
534 # many ways to link table1
536 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
537 and scalar( @{ $fk{ $table2->name } } == 1 ) )
539 $self->{'can_link'}{ $table1->name }{ $table2->name } =
540 [ 'many2one', $fk{ $table1->name }, $fk{ $table2->name } ];
541 $self->{'can_link'}{ $table2->name }{ $table1->name } =
542 [ 'one2many', $fk{ $table2->name }, $fk{ $table1->name } ];
544 # non-trivial traversal. one way to link table1,
545 # many ways to link table2
547 elsif ( scalar( @{ $fk{ $table1->name } } == 1 )
548 and scalar( @{ $fk{ $table2->name } } > 1 ) )
550 $self->{'can_link'}{ $table1->name }{ $table2->name } =
551 [ 'one2many', $fk{ $table1->name }, $fk{ $table2->name } ];
552 $self->{'can_link'}{ $table2->name }{ $table1->name } =
553 [ 'many2one', $fk{ $table2->name }, $fk{ $table1->name } ];
555 # non-trivial traversal. many ways to link table1 and table2
557 elsif ( scalar( @{ $fk{ $table1->name } } > 1 )
558 and scalar( @{ $fk{ $table2->name } } > 1 ) )
560 $self->{'can_link'}{ $table1->name }{ $table2->name } =
561 [ 'many2many', $fk{ $table1->name }, $fk{ $table2->name } ];
562 $self->{'can_link'}{ $table2->name }{ $table1->name } =
563 [ 'many2many', $fk{ $table2->name }, $fk{ $table1->name } ];
565 # one of the tables didn't export a key
566 # to this table, no linking possible
569 $self->{'can_link'}{ $table1->name }{ $table2->name } = [0];
570 $self->{'can_link'}{ $table2->name }{ $table1->name } = [0];
573 return $self->{'can_link'}{ $table1->name }{ $table2->name };
576 # ----------------------------------------------------------------------
583 Get or set the table's name.
585 Errors ("No table name") if you try to set a blank name.
587 If provided an argument, checks the schema object for a table of
588 that name and disallows the change if one exists (setting the error to
589 "Can't use table name "%s": table exists").
591 my $table_name = $table->name('foo');
598 my $arg = shift || return $self->error( "No table name" );
599 if ( my $schema = $self->schema ) {
600 return $self->error( qq[Can't use table name "$arg": table exists] )
601 if $schema->get_table( $arg );
603 $self->{'name'} = $arg;
606 return $self->{'name'} || '';
609 # ----------------------------------------------------------------------
616 Get or set the table's schema object.
618 my $schema = $table->schema;
623 if ( my $arg = shift ) {
624 return $self->error('Not a schema object') unless
625 UNIVERSAL::isa( $arg, 'SQL::Translator::Schema' );
626 $self->{'schema'} = $arg;
629 return $self->{'schema'};
632 # ----------------------------------------------------------------------
639 Gets or sets the table's primary key(s). Takes one or more field
640 names (as a string, list or array[ref]) as an argument. If the field
641 names are present, it will create a new PK if none exists, or it will
642 add to the fields of an existing PK (and will unique the field names).
643 Returns the C<SQL::Translator::Schema::Constraint> object representing
648 $table->primary_key('id');
649 $table->primary_key(['name']);
650 $table->primary_key('id','name']);
651 $table->primary_key(['id','name']);
652 $table->primary_key('id,name');
653 $table->primary_key(qw[ id name ]);
655 my $pk = $table->primary_key;
660 my $fields = parse_list_arg( @_ );
664 for my $f ( @$fields ) {
665 return $self->error(qq[Invalid field "$f"]) unless
666 $self->get_field($f);
670 for my $c ( $self->get_constraints ) {
671 if ( $c->type eq PRIMARY_KEY ) {
673 $c->fields( @{ $c->fields }, @$fields );
679 $constraint = $self->add_constraint(
690 for my $c ( $self->get_constraints ) {
691 return $c if $c->type eq PRIMARY_KEY;
698 # ----------------------------------------------------------------------
705 Get or set the table's options (e.g., table types for MySQL). Returns
706 an array or array reference.
708 my @options = $table->options;
713 my $options = parse_list_arg( @_ );
715 push @{ $self->{'options'} }, @$options;
717 if ( ref $self->{'options'} ) {
718 return wantarray ? @{ $self->{'options'} || [] } : $self->{'options'};
721 return wantarray ? () : [];
725 # ----------------------------------------------------------------------
732 Get or set the table's order.
734 my $order = $table->order(3);
738 my ( $self, $arg ) = @_;
740 if ( defined $arg && $arg =~ /^\d+$/ ) {
741 $self->{'order'} = $arg;
744 return $self->{'order'} || 0;
747 # ----------------------------------------------------------------------
752 Read-only method to return a list or array ref of the field names. Returns undef
753 or an empty list if the table has no fields set. Usefull if you want to
754 avoid the overload magic of the Field objects returned by the get_fields method.
756 my @names = $constraint->field_names;
763 sort { $a->order <=> $b->order }
764 values %{ $self->{'fields'} || {} };
767 return wantarray ? @fields : \@fields;
770 $self->error('No fields');
771 return wantarray ? () : undef;
775 # ----------------------------------------------------------------------
777 =head1 LOOKUP METHODS
779 The following are a set of shortcut methods for getting commonly used lists of
780 fields and constraints. They all return lists or array refs of Field or
787 The primary key fields.
791 All foreign key fields.
795 All the fields except the primary key.
803 All fields with unique constraints.
805 =item unique_constraints
807 All this tables unique constraints.
809 =item fkey_constraints
811 All this tables foreign key constraints. (See primary_key method to get the
812 primary key constraint)
820 my @fields = grep { $_->is_primary_key } $me->get_fields;
821 return wantarray ? @fields : \@fields;
824 # ----------------------------------------------------------------------
828 push @fields, $_->fields foreach $me->fkey_constraints;
829 return wantarray ? @fields : \@fields;
832 # ----------------------------------------------------------------------
835 my @fields = grep { !$_->is_primary_key } $me->get_fields;
836 return wantarray ? @fields : \@fields;
839 # ----------------------------------------------------------------------
843 grep { !$_->is_foreign_key and !$_->is_primary_key } $me->get_fields;
844 return wantarray ? @fields : \@fields;
847 # ----------------------------------------------------------------------
851 push @fields, $_->fields foreach $me->unique_constraints;
852 return wantarray ? @fields : \@fields;
855 # ----------------------------------------------------------------------
856 sub unique_constraints {
858 my @cons = grep { $_->type eq UNIQUE } $me->get_constraints;
859 return wantarray ? @cons : \@cons;
862 # ----------------------------------------------------------------------
863 sub fkey_constraints {
865 my @cons = grep { $_->type eq FOREIGN_KEY } $me->get_constraints;
866 return wantarray ? @cons : \@cons;
869 # ----------------------------------------------------------------------
872 undef $self->{'schema'}; # destroy cyclical reference
873 undef $_ for @{ $self->{'constraints'} };
874 undef $_ for @{ $self->{'indices'} };
875 undef $_ for values %{ $self->{'fields'} };
880 # ----------------------------------------------------------------------
886 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>,
887 Allen Day E<lt>allenday@ucla.eduE<gt>.