From: Graham Barr <gbarr@pobox.com>
Date: Fri, 9 Oct 2009 05:19:01 +0000 (-0500)
Subject: Add specific methods for adding foreign keys and indexes for MySQL
X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FSQL-Translator-2.0-ish.git;a=commitdiff_plain;h=8a8127589f36580ba226fcf3c2891d572c1ef692

Add specific methods for adding foreign keys and indexes for MySQL
---

diff --git a/lib/SQL/Translator/Parser/DBI/MySQL.pm b/lib/SQL/Translator/Parser/DBI/MySQL.pm
index b4d8b82..817aaad 100644
--- a/lib/SQL/Translator/Parser/DBI/MySQL.pm
+++ b/lib/SQL/Translator/Parser/DBI/MySQL.pm
@@ -29,4 +29,53 @@ role SQL::Translator::Parser::DBI::MySQL {
 
         return $default_value;
     }
+
+    method _add_foreign_keys(Table $table, Schema $schema) {
+        my $fk_info = $self->dbh->foreign_key_info($self->catalog_name, $self->schema_name, $table->name, $self->catalog_name, $self->schema_name, undef);
+        return unless $fk_info;
+        my $fk_data;
+        while (my $fk_col = $fk_info->fetchrow_hashref) {
+            my $fk_name = $fk_col->{FK_NAME};
+            push @{$fk_data->{$fk_name}{columns}}, $fk_col->{FKCOLUMN_NAME};
+            push @{$fk_data->{$fk_name}{reference_columns}}, $fk_col->{PKCOLUMN_NAME};
+            $fk_data->{$fk_name}{table} = $fk_col->{FKTABLE_NAME};
+            $fk_data->{$fk_name}{reference_table} = $fk_col->{PKTABLE_NAME};
+            my $pk_name = $fk_col->{PK_NAME};
+            $pk_name = 'PRIMARY' unless defined $pk_name;
+            $fk_data->{$fk_name}{uk} = $schema->get_table($fk_col->{PKTABLE_NAME})->get_index($pk_name);
+        }
+
+        foreach my $fk_name (keys %$fk_data) {
+          my $fk = SQL::Translator::Object::ForeignKey->new(
+            {name => $fk_name, references => $fk_data->{$fk_name}{uk}, reference_table => $fk_data->{$fk_name}{reference_table}, reference_columns => $fk_data->{$fk_name}{reference_columns}});
+          $fk->add_column($schema->get_table($fk_data->{$fk_name}{table})->get_column($_))
+            for @{$fk_data->{$fk_name}{columns}};
+          $schema->get_table($fk_data->{$fk_name}{table})->add_constraint($fk);
+        }
+    }
+
+    method _add_indexes(Table $table) {
+        my $index_info = $self->dbh->prepare(qq{SHOW INDEX FROM } . $table->name);
+
+        $index_info->execute;
+
+        my %indexes;
+        while (my $index_col = $index_info->fetchrow_hashref('NAME_uc')) {
+          my $index_name = $index_col->{KEY_NAME};
+
+          next if $index_name eq 'PRIMARY';
+          $indexes{$index_name}{index_type} = $index_col->{NON_UNIQUE} ? 'NORMAL' : 'UNIQUE';
+          my $column = $table->get_column($index_col->{COLUMN_NAME});
+          push @{$indexes{$index_name}{index_cols}},
+            {column => $column, pos => $index_col->{SEQ_IN_INDEX}};
+        }
+        foreach my $index_name (keys %indexes) {
+          next if $table->exists_index($index_name);
+          my $index = SQL::Translator::Object::Index->new(
+            {name => $index_name, type => $indexes{$index_name}{index_type}});
+          $index->add_column($_->{column})
+            for sort { $a->{pos} <=> $b->{pos} } @{$indexes{$index_name}{index_cols}};
+          $table->add_index($index);
+        }
+    }
 }