2 role SQL::Translator::Parser::DBI {
3 use DBI::Const::GetInfoType;
4 use DBI::Const::GetInfo::ANSI;
5 use DBI::Const::GetInfoReturn;
7 use MooseX::Types::Moose qw(HashRef Maybe Str);
9 use SQL::Translator::Object::Column;
10 use SQL::Translator::Object::ForeignKey;
11 use SQL::Translator::Object::Index;
12 use SQL::Translator::Object::PrimaryKey;
13 use SQL::Translator::Object::Table;
14 use SQL::Translator::Object::View;
16 use SQL::Translator::Types qw(Schema Table);
22 default => sub { shift->dbh->get_info(29) || q{"} }
29 default => sub { shift->dbh->get_info(41) || '.' }
32 has 'schema_name' => (
39 has 'catalog_name' => (
47 my $dbtype = $self->dbh->get_info($GetInfoType{SQL_DBMS_NAME}) || $self->dbh->{Driver}{Name};
49 my $class = __PACKAGE__ . '::'. $dbtype;
50 Class::MOP::load_class($class);
51 $class->meta->apply($self);
54 method _is_auto_increment(HashRef $column_info) { 0 }
56 method _column_default_value(HashRef $column_info) { $column_info->{COLUMN_DEF} }
58 method _column_data_type(HashRef $column_info) { $column_info->{DATA_TYPE} }
60 method _add_tables(Schema $schema) {
61 my $sth = $self->dbh->table_info($self->catalog_name, $self->schema_name, '%', "TABLE,VIEW,'LOCAL TEMPORARY','GLOBAL TEMPORARY'");
62 while (my $table_info = $sth->fetchrow_hashref) {
63 if ($table_info->{TABLE_TYPE} =~ /^(TABLE|LOCAL TEMPORARY|GLOBAL TEMPORARY)$/) {
64 my $temp = $table_info->{TABLE_TYPE} =~ /TEMPORARY$/ ? 1 : 0;
65 my $table = SQL::Translator::Object::Table->new({ name => $table_info->{TABLE_NAME}, temporary => $temp, schema => $schema });
66 $schema->add_table($table);
68 $self->_add_columns($table);
69 $self->_add_primary_key($table);
70 $self->_add_indexes($table);
72 elsif ($table_info->{TABLE_TYPE} eq 'VIEW') {
73 my $sql = $self->_get_view_sql($table_info->{TABLE_NAME});
74 my $view = SQL::Translator::Object::View->new({ name => $table_info->{TABLE_NAME}, sql => $sql });
75 $schema->add_view($view);
76 $self->_add_columns($view);
79 $self->_add_foreign_keys($schema->get_table($_), $schema) for $schema->table_ids;
82 method _add_columns(Table $table) {
83 my $sth = $self->dbh->column_info($self->catalog_name, $self->schema_name, $table->name, '%');
84 while (my $column_info = $sth->fetchrow_hashref) {
85 my $column = SQL::Translator::Object::Column->new({ name => $column_info->{COLUMN_NAME},
86 data_type => $self->_column_data_type($column_info),
87 size => $column_info->{COLUMN_SIZE},
88 default_value => $self->_column_default_value($column_info),
89 is_auto_increment => $self->_is_auto_increment($column_info),
90 is_nullable => $column_info->{NULLABLE},
92 $table->add_column($column);
96 method _add_primary_key(Table $table) {
97 my $pk_info = $self->dbh->primary_key_info($self->catalog_name, $self->schema_name, $table->name);
99 my ($pk_name, @pk_cols);
100 while (my $pk_col = $pk_info->fetchrow_hashref) {
101 $pk_name = $pk_col->{PK_NAME};
102 push @pk_cols, $pk_col->{COLUMN_NAME};
104 return unless $pk_name;
106 my $pk = SQL::Translator::Object::PrimaryKey->new({ name => $pk_name });
107 $pk->add_column($table->get_column($_)) for @pk_cols;
108 $table->add_index($pk);
111 method _add_foreign_keys(Table $table, Schema $schema) {
112 my $fk_info = $self->dbh->foreign_key_info($self->catalog_name, $self->schema_name, $table->name, $self->catalog_name, $self->schema_name, undef);
113 return unless $fk_info;
116 while (my $fk_col = $fk_info->fetchrow_hashref) {
117 my $fk_name = $fk_col->{FK_NAME};
119 push @{$fk_data->{$fk_name}{columns}}, $fk_col->{FK_COLUMN_NAME};
120 $fk_data->{$fk_name}{table} = $fk_col->{FK_TABLE_NAME};
121 $fk_data->{$fk_name}{uk} = $schema->get_table($fk_col->{UK_TABLE_NAME})->get_index($fk_col->{UK_NAME});
124 foreach my $fk_name (keys %$fk_data) {
125 my $fk = SQL::Translator::Object::ForeignKey->new({ name => $fk_name, references => $fk_data->{$fk_name}{uk} });
126 $fk->add_column($schema->get_table($fk_data->{$fk_name}{table})->get_column($_)) for @{$fk_data->{$fk_name}{columns}};
127 $table->add_constraint($fk);
131 method _add_indexes(Table $table) {
132 my $index_info = $self->dbh->statistics_info($self->catalog_name, $self->schema_name, $table->name, 1, 0);
134 return unless defined $index_info;
136 my ($index_name, $index_type, @index_cols);
137 while (my $index_col = $index_info->fetchrow_hashref) {
138 $index_name = $index_col->{INDEX_NAME};
139 $index_type = $index_col->{NON_UNIQUE} ? 'NORMAL' : 'UNIQUE';
140 push @index_cols, $index_col->{COLUMN_NAME};
142 return if $table->exists_index($index_name);
143 my $index = SQL::Translator::Object::Index->new({ name => $index_name, type => $index_type });
144 $index->add_column($table->get_column($_)) for @index_cols;
145 $table->add_index($index);