lib/SQL/Translator/Parser/DBI/Oracle.pm

   1 package SQL::Translator::Parser::DBI::Oracle;
   2
   3 # -------------------------------------------------------------------
   4 # Copyright (C) 2006-2009 SQLFairy Authors
   5 #
   6 # This program is free software; you can redistribute it and/or
   7 # modify it under the terms of the GNU General Public License as
   8 # published by the Free Software Foundation; version 2.
   9 #
  10 # This program is distributed in the hope that it will be useful, but
  11 # WITHOUT ANY WARRANTY; without even the implied warranty of
  12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  13 # General Public License for more details.
  14 #
  15 # You should have received a copy of the GNU General Public License
  16 # along with this program; if not, write to the Free Software
  17 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
  18 # 02111-1307  USA
  19 # -------------------------------------------------------------------
  20
  21 =head1 NAME
  22
  23 SQL::Translator::Parser::DBI::Oracle - parser for DBD::Oracle
  24
  25 =head1 SYNOPSIS
  26
  27 See SQL::Translator::Parser::DBI.
  28
  29 =head1 DESCRIPTION
  30
  31 Uses DBI introspection methods to determine schema details.
  32
  33 =cut
  34
  35 use strict;
  36 use warnings;
  37 use DBI;
  38 use SQL::Translator::Schema::Constants;
  39 use SQL::Translator::Schema::Table;
  40 use SQL::Translator::Schema::Field;
  41 use SQL::Translator::Schema::Constraint;
  42
  43 use vars qw[ $VERSION ];
  44 $VERSION = '1.60';
  45
  46 # -------------------------------------------------------------------
  47 sub parse {
  48     my ( $tr, $dbh ) = @_;
  49
  50     my $schema = $tr->schema;
  51
  52     my $sth = $dbh->table_info();
  53
  54     while(my $table_info = $sth->fetchrow_hashref('NAME_uc')) {
  55         next unless ($table_info->{TABLE_TYPE} eq 'TABLE');
  56         next if ($table_info->{TABLE_NAME} =~ /\$/);
  57
  58         # create the table
  59
  60         my $table = $schema->add_table(
  61             name => $table_info->{TABLE_NAME},
  62             type => $table_info->{TABLE_TYPE},
  63         );
  64
  65         # add the fields (columns) for this table
  66
  67         my $sth;
  68
  69         $sth = $dbh->column_info(
  70             undef,
  71             $table_info->{TABLE_SCHEM},
  72             $table_info->{TABLE_NAME},
  73             '%'
  74         );
  75
  76         while(my $column = $sth->fetchrow_hashref('NAME_uc')) {
  77             my $f = $table->add_field(
  78                 name          => $column->{COLUMN_NAME},
  79                 default_value => $column->{COLUMN_DEF},
  80                 data_type     => $column->{TYPE_NAME},
  81                 order         => $column->{ORDINAL_POSITION},
  82                 size          => $column->{COLUMN_SIZE},
  83             ) || die $table->error;
  84
  85             $f->is_nullable( $column->{NULLABLE} == 1 );
  86         }
  87
  88         # add the primary key info
  89
  90         $sth = $dbh->primary_key_info(
  91             undef,
  92             $table_info->{TABLE_SCHEM},
  93             $table_info->{TABLE_NAME},
  94         );
  95
  96         while(my $primary_key = $sth->fetchrow_hashref('NAME_uc')) {
  97             my $f = $table->get_field( $primary_key->{COLUMN_NAME} );
  98             $f->is_primary_key(1);
  99         }
 100
 101         # add the foreign key info (constraints)
 102
 103         $sth = $dbh->foreign_key_info(
 104             undef,
 105             undef,
 106             undef,
 107             undef,
 108             $table_info->{TABLE_SCHEM},
 109             $table_info->{TABLE_NAME},
 110         );
 111
 112         my $cons = {};
 113         while(my $foreign_key = $sth->fetchrow_hashref('NAME_uc')) {
 114             my $name = $foreign_key->{FK_NAME};
 115             $cons->{$name}->{reference_table} = $foreign_key->{UK_TABLE_NAME};
 116             push @{ $cons->{$name}->{fields} },
 117                 $foreign_key->{FK_COLUMN_NAME};
 118             push @{ $cons->{$name}->{reference_fields} },
 119                 $foreign_key->{UK_COLUMN_NAME};
 120         }
 121
 122         for my $name ( keys %$cons ) {
 123             my $c = $table->add_constraint(
 124                 type             => FOREIGN_KEY,
 125                 name             => $name,
 126                 fields           => $cons->{$name}->{fields},
 127                 reference_fields => $cons->{$name}->{reference_fields},
 128                 reference_table  => $cons->{$name}->{reference_table},
 129             ) || die $table->error;
 130         }
 131     }
 132
 133     return 1;
 134 }
 135
 136 1;
 137
 138 =pod
 139
 140 =head1 AUTHOR
 141
 142 Earl Cahill E<lt>cpan@spack.netE<gt>.
 143
 144 =head1 ACKNOWLEDGEMENT
 145
 146 Initial revision of this module came almost entirely from work done by
 147 Todd Hepler E<lt>thepler@freeshell.orgE<gt>.  My changes were
 148 quite minor (ensuring NAME_uc, changing a couple variable names,
 149 skipping tables with a $ in them).
 150
 151 Todd claimed his work to be an almost verbatim copy of
 152 SQL::Translator::Parser::DBI::PostgreSQL revision 1.7
 153
 154 For me, the real work happens in DBD::Oracle and DBI, which, also
 155 for me, that is the beauty of having introspection methods in DBI.
 156
 157 =head1 SEE ALSO
 158
 159 SQL::Translator, DBD::Oracle.
 160
 161 =cut