view release on metacpan or  search on metacpan

lib/Alzabo/RDBMSRules/MySQL.pm  view on Meta::CPAN

        $sql .= join ', ', map {$_->name} $new->primary_key;
        $sql .= ')';

        push @sql, $sql;
    }

    foreach ( $new->primary_key )
    {
        if ( $_->sequenced &&
             ! ( $old->has_column( $_->name ) &&
                 $old->column( $_->name )->is_primary_key ) )
        {
            my $sql = $self->column_sql($_);
            push @sql,
                'ALTER TABLE ' . $new->name . ' CHANGE COLUMN ' . $_->name . ' ' . $sql;
        }
    }

    return @sql;
}

sub alter_table_name_sql
{
    my $self = shift;
    my $table = shift;

    return 'RENAME TABLE ' . $table->former_name . ' TO ' . $table->name;
}

sub alter_table_attributes_sql
{
    my $self = shift;
    my %p = @_;

    # This doesn't work right if new table has no attributes
    return;

    return 'ALTER TABLE ' . $p{new}->name . ' ' . join ' ', $p{new}->attributes;
}

sub alter_column_name_sql
{
    my $self = shift;
    my $column = shift;

    return
        ( 'ALTER TABLE ' . $column->table->name . ' CHANGE COLUMN ' .
          $column->former_name . ' ' . $self->column_sql($column)
        );
}

sub reverse_engineer
{
    my $self = shift;
    my $schema = shift;

    my $driver = $schema->driver;

    my $has_table_types =
        $driver->one_row( sql  => 'SHOW VARIABLES LIKE ?',
                          bind => 'table_type' );

    foreach my $table ( $driver->tables )
    {
        my $table_name = $self->_clean_table_name($table);

        my $t = $schema->make_table( name => $table_name );

        foreach my $row ( $driver->rows( sql => "DESCRIBE $table" ) )
        {
            my ($type, @a);
            if ( $row->[1] =~ /\A(?:ENUM|SET)/i )
            {
                $type = $row->[1];
            }
            else
            {
                ($type, @a) = split /\s+/, $row->[1];
            }

            my $default = $row->[4] if defined $row->[4] && uc $row->[4] ne 'NULL';

            my $seq = 0;
            foreach my $a ( split /\s+/, $row->[5] )
            {
                if ( uc $a eq 'AUTO_INCREMENT' )
                {
                    $seq = 1;
                }
                else
                {
                    push @a, $a;
                }
            }

            my %p;
            if ( $type !~ /ENUM|SET/i
                 && $type =~ /(\w+)\((\d+)(?:\s*,\s*(\d+))?\)$/ )
            {
                $type = uc $1;
                $type = 'INTEGER' if $type eq 'INT';

                # skip defaults
                unless ( $type eq 'TINYINT' && ( $2 == 4 || $2 == 3 ) ||
                         $type eq 'SMALLINT' && ( $2 == 6 || $2 == 5 ) ||
                         $type eq 'MEDIUMINT' && ( $2 == 9 || $2 == 8 ) ||
                         $type eq 'INTEGER' && ( $2 == 11 || $2 == 10 )  ||
                         $type eq 'BIGINT' && ( $2 == 21 || $2 == 20 ) ||
                         $type eq 'YEAR' && $2 == 4 ||
                         $type eq 'TIMESTAMP' && $2 == 14
                       )
                {
                    $p{length} = $2;
                    $p{precision} = $3;
                }
            }

            $type = $self->_capitalize_type($type);

            $default = undef
                if $self->_can_ignore_default( $type, $default );

            my $c = $t->make_column( name => $row->[0],
                                     type => $type,
                                     nullable => $row->[2] eq 'YES',
                                     sequenced => $seq,
                                     default => $default,
                                     attributes => \@a,
                                     primary_key => $row->[3] eq 'PRI',
                                     %p,
                                   );
        }

        my %i;
        foreach my $row ( $driver->rows( sql => "SHOW INDEX FROM $table" ) )
        {
            next if $row->[2] eq 'PRIMARY';

            my $type_i = $driver->major_version >= 4 ? 10 : 9;
            $i{ $row->[2] }{fulltext} =
                $row->[$type_i] && $row->[$type_i] =~ /fulltext/i ? 1 : 0;

            $i{ $row->[2] }{cols}[ $row->[3] - 1 ]{column} = $t->column( $row->[4] );
            if ( defined $row->[7] )
            {
                # MySQL (at least 4.0.17) reports a sub_part of 1 for
                # the second column of a fulltext index.
                if ( ! $i{ $row->[2] }{fulltext} || $row->[7] > 1 )
                {
                    $i{ $row->[2] }{cols}[ $row->[3] - 1 ]{prefix} = $row->[7]
                }
            }

            $i{ $row->[2] }{unique} = $row->[1] ? 0 : 1;

        }

        foreach my $index (keys %i)
        {
            $t->make_index( columns  => $i{$index}{cols},
                            unique   => $i{$index}{unique},
                            fulltext => $i{$index}{fulltext} );
        }

        if ( $has_table_types )
        {
            my $table_type =
                ( $driver->one_row( sql  => 'SHOW TABLE STATUS LIKE ?',
                                    bind => $table_name ) )[1];

            $t->add_attribute( 'TYPE=' . uc $table_type );
        }
    }
}

my %ignored_defaults = ( DATETIME => '0000-00-00 00:00:00',
                         DATE => '0000-00-00',
                         YEAR => '0000',
                         CHAR => '',
                         VARCHAR => '',
                         TINTYTEXT => '',
                         SMALLTEXT => '',
                         MEDIUMTEXT => '',
                         TEXT => '',
                         LONGTEXT => '',
                       );

sub _can_ignore_default
{
    my $self = shift;
    my $type = shift;
    my $default = shift;

    return 1 unless defined $default;

    return 1
        if exists $ignored_defaults{$type} && $default eq $ignored_defaults{$type};

    if ( $type eq 'DECIMAL' )
    {
        return 1 if $default =~ /0\.0+/;
    }

    if ( $type =~ /INT/ )
    {
        return 1 unless $default;
    }

    return 0;
}

sub rules_id
{
    return 'MySQL';
}

1;

__END__

=head1 NAME

Alzabo::RDBMSRules::MySQL - MySQL specific database rules.

=head1 SYNOPSIS

  use Alzabo::RDBMSRules::MySQL;

=head1 DESCRIPTION