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

   1 package SQL::Translator::Parser::DBI::PostgreSQL;
   2
   3 =head1 NAME
   4
   5 SQL::Translator::Parser::DBI::PostgreSQL - parser for DBD::Pg
   6
   7 =head1 SYNOPSIS
   8
   9 See SQL::Translator::Parser::DBI.
  10
  11 =head1 DESCRIPTION
  12
  13 Uses DBI to query PostgreSQL system tables to determine schema structure.
  14
  15 =cut
  16
  17 use strict;
  18 use DBI;
  19 use Data::Dumper;
  20 use SQL::Translator::Schema::Constants;
  21
  22 use vars qw[ $DEBUG $VERSION @EXPORT_OK ];
  23 $VERSION = '1.59';
  24 $DEBUG   = 0 unless defined $DEBUG;
  25
  26 my $actions = {c => 'cascade',
  27                r => 'restrict',
  28                a => 'no action',
  29                n => 'set null',
  30                d => 'set default',
  31            };
  32
  33 # -------------------------------------------------------------------
  34 sub parse {
  35     my ( $tr, $dbh ) = @_;
  36
  37     my $schema = $tr->schema;
  38
  39     my $column_select = $dbh->prepare(
  40       "SELECT a.attname, t.typname, a.attnum,a.atttypmod as length,
  41               a.attnotnull, a.atthasdef, d.adsrc
  42        FROM pg_type t,pg_attribute a
  43        LEFT JOIN pg_attrdef d ON (d.adrelid = a.attrelid AND a.attnum = d.adnum)
  44        WHERE a.attrelid=? AND attnum>0
  45          AND a.atttypid=t.oid
  46        ORDER BY a.attnum"
  47     );
  48
  49     my $index_select  = $dbh->prepare(
  50       "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique,
  51               i.indisprimary, pg_get_indexdef(oid) AS create_string
  52        FROM pg_class c,pg_index i
  53        WHERE c.relnamespace=2200 AND c.relkind='i'
  54          AND c.oid=i.indexrelid AND i.indrelid=?"
  55     );
  56
  57     my $table_select  = $dbh->prepare(
  58       "SELECT oid,relname FROM pg_class WHERE relnamespace IN
  59           (SELECT oid FROM pg_namespace WHERE nspname='public')
  60           AND relkind='r';"
  61     );
  62
  63     my $fk_select = $dbh->prepare(
  64         q/
  65 SELECT r.conname,
  66        c.relname,
  67        d.relname AS frelname,
  68        r.conkey,
  69        ARRAY(SELECT column_name::varchar
  70                FROM information_schema.columns
  71               WHERE ordinal_position = ANY  (r.conkey)
  72                 AND table_schema = n.nspname
  73                 AND table_name   =   c.relname ) AS fields,
  74        r.confkey,
  75        ARRAY(SELECT column_name::varchar
  76                FROM information_schema.columns
  77               WHERE ordinal_position = ANY  (r.confkey)
  78                 AND table_schema =   n.nspname
  79                 AND table_name   =   d.relname ) AS reference_fields,
  80        r.confupdtype,
  81        r.confdeltype,
  82        r.confmatchtype
  83
  84 FROM pg_catalog.pg_constraint r
  85
  86 JOIN pg_catalog.pg_class c
  87   ON c.oid = r.conrelid
  88  AND r.contype = 'f'
  89
  90 JOIN pg_catalog.pg_class d
  91   ON d.oid = r.confrelid
  92
  93 JOIN pg_catalog.pg_namespace n
  94   ON n.oid = c.relnamespace
  95
  96 WHERE pg_catalog.pg_table_is_visible(c.oid)
  97   AND n.nspname = ?
  98   AND c.relname = ?
  99 ORDER BY 1;
 100         /) or die "Can't prepare: $@";
 101
 102     $table_select->execute();
 103
 104     while ( my $tablehash = $table_select->fetchrow_hashref ) {
 105
 106         my $table_name = $$tablehash{'relname'};
 107         my $table_oid  = $$tablehash{'oid'};
 108
 109         my $table = $schema->add_table(
 110                                        name => $table_name,
 111               #what is type?               type => $table_info->{TABLE_TYPE},
 112                                           ) || die $schema->error;
 113
 114         $column_select->execute($table_oid);
 115
 116         while (my $columnhash = $column_select->fetchrow_hashref ) {
 117
 118             #data_type seems to not be populated; perhaps there needs to
 119             #be a mapping of query output to reserved constants in sqlt?
 120
 121             my $col = $table->add_field(
 122                               name        => $$columnhash{'attname'},
 123                               default_value => $$columnhash{'adsrc'},
 124                               data_type   => $$columnhash{'typname'},
 125                               order       => $$columnhash{'attnum'},
 126                              ) || die $table->error;
 127
 128             $col->{size} = [$$columnhash{'length'}] if $$columnhash{'length'}>0;
 129             $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1;
 130         }
 131
 132         $index_select->execute($table_oid);
 133
 134         my @column_names = $table->field_names();
 135         while (my $indexhash = $index_select->fetchrow_hashref ) {
 136               #don't deal with function indexes at the moment
 137             next if ($$indexhash{'indkey'} eq ''
 138                      or !defined($$indexhash{'indkey'}) );
 139
 140             my $type;
 141             if      ($$indexhash{'indisprimary'}) {
 142                 $type = UNIQUE; #PRIMARY_KEY;
 143
 144                 #tell sqlt that this is the primary key:
 145                 my $col_name=$column_names[($$indexhash{'indkey'} - 1)];
 146                 $table->get_field($col_name)->{is_primary_key}=1;
 147
 148             } elsif ($$indexhash{'indisunique'}) {
 149                 $type = UNIQUE;
 150             } else {
 151                 $type = NORMAL;
 152             }
 153
 154             my @column_ids = split /\s+/, $$indexhash{'indkey'};
 155             my @columns;
 156             foreach my $col (@column_ids) {
 157                 push @columns, $column_names[($col - 1)];
 158             }
 159
 160             $table->add_index(
 161                               name         => $$indexhash{'relname'},
 162                               type         => $type,
 163                               fields       => \@columns,
 164                              ) || die $table->error;
 165         }
 166
 167         $fk_select->execute('public',$table_name) or die "Can't execute: $@";
 168         my $fkeys = $fk_select->fetchall_arrayref({});
 169         $DEBUG and print Dumper $fkeys;
 170         for my $con (@$fkeys){
 171             my $con_name         = $con->{conname};
 172             my $fields           = $con->{fields};
 173             my $reference_fields = $con->{reference_fields};
 174             my $reference_table  = $con->{frelname};
 175             my $on_upd           = $con->{confupdtype};
 176             my $on_del           = $con->{confdeltype};
 177             $table->add_constraint(
 178                                    name   => $con_name,
 179                                    type   => 'foreign_key',
 180                                    fields =>  $fields,
 181                                    reference_fields => $reference_fields,
 182                                    reference_table => $reference_table,
 183                                    on_delete  => $actions->{$on_upd},
 184                                    on_update  => $actions->{$on_del},
 185                                );
 186         }
 187     }
 188
 189
 190     return 1;
 191 }
 192
 193 1;
 194
 195 # -------------------------------------------------------------------
 196 # Time is a waste of money.
 197 # Oscar Wilde
 198 # -------------------------------------------------------------------
 199
 200 =pod
 201
 202 =head1 AUTHOR
 203
 204 Scott Cain E<lt>cain@cshl.eduE<gt>, previous author:
 205 Paul Harrington E<lt>harringp@deshaw.comE<gt>.
 206
 207 =head1 SEE ALSO
 208
 209 SQL::Translator, DBD::Pg.
 210
 211 =cut