Whitespace

[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Parser / DBI / PostgreSQL.pm

diff --git a/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm b/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm
index 9606651..1496ace 100644 (file)
--- a/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm
+++ b/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm
@@ -28,7 +28,7 @@ See SQL::Translator::Parser::DBI.
 
 =head1 DESCRIPTION
 
-Uses DBI to query PostgreSQL system tables to determine schema structure.   
+Uses DBI to query PostgreSQL system tables to determine schema structure.
 
 =cut
 
@@ -55,25 +55,34 @@ sub parse {
     my $schema = $tr->schema;
 
     my $column_select = $dbh->prepare(
-      "SELECT a.attname, t.typname, a.attnum,a.atttypmod as length,
-              a.attnotnull, a.atthasdef, d.adsrc
-       FROM pg_type t,pg_attribute a
-       LEFT JOIN pg_attrdef d ON (d.adrelid = a.attrelid AND a.attnum = d.adnum)
+      "SELECT a.attname, format_type(t.oid, a.atttypmod) as typname, a.attnum,
+              a.atttypmod as length, a.attnotnull, a.atthasdef, ad.adsrc,
+              d.description
+       FROM pg_type t, pg_attribute a
+       LEFT JOIN pg_attrdef ad ON (ad.adrelid = a.attrelid AND a.attnum = ad.adnum)
+       LEFT JOIN pg_description d ON (a.attrelid=d.objoid AND a.attnum=d.objsubid)
        WHERE a.attrelid=? AND attnum>0
          AND a.atttypid=t.oid
        ORDER BY a.attnum"
-    ); 
+    );
 
     my $index_select  = $dbh->prepare(
       "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique,
+              ARRAY(SELECT a.attname
+                  FROM pg_attribute a
+                  WHERE a.attrelid=i.indrelid AND a.attnum = ANY(i.indkey)
+              ) AS attname,
               i.indisprimary, pg_get_indexdef(oid) AS create_string
        FROM pg_class c,pg_index i
-       WHERE c.relnamespace=2200 AND c.relkind='i'
+       WHERE c.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname='public') AND c.relkind='i'
          AND c.oid=i.indexrelid AND i.indrelid=?"
     );
 
     my $table_select  = $dbh->prepare(
-      "SELECT oid,relname FROM pg_class WHERE relnamespace IN
+      "SELECT c.oid, c.relname, d.description
+       FROM pg_class c
+       LEFT JOIN pg_description d ON c.oid=d.objoid AND d.objsubid=0
+       WHERE relnamespace IN
           (SELECT oid FROM pg_namespace WHERE nspname='public')
           AND relkind='r';"
     );
@@ -116,24 +125,25 @@ WHERE pg_catalog.pg_table_is_visible(c.oid)
   AND c.relname = ?
 ORDER BY 1;
         /) or die "Can't prepare: $@";
-    
+
     $table_select->execute();
 
     while ( my $tablehash = $table_select->fetchrow_hashref ) {
 
         my $table_name = $$tablehash{'relname'};
-        my $table_oid  = $$tablehash{'oid'}; 
-
+        my $table_oid  = $$tablehash{'oid'};
         my $table = $schema->add_table(
                                        name => $table_name,
               #what is type?               type => $table_info->{TABLE_TYPE},
                                           ) || die $schema->error;
 
+        $table->comments($$tablehash{'description'}) if $$tablehash{'description'};
+
         $column_select->execute($table_oid);
 
         while (my $columnhash = $column_select->fetchrow_hashref ) {
 
-            #data_type seems to not be populated; perhaps there needs to 
+            #data_type seems to not be populated; perhaps there needs to
             #be a mapping of query output to reserved constants in sqlt?
 
             my $col = $table->add_field(
@@ -143,8 +153,10 @@ ORDER BY 1;
                               order       => $$columnhash{'attnum'},
                              ) || die $table->error;
 
-            $col->{size} = [$$columnhash{'length'}] if $$columnhash{'length'}>0;
+            $col->{size} = [$$columnhash{'length'}]
+                if $$columnhash{'length'}>0 && $$columnhash{'length'}<=0xFFFF;
             $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1;
+            $col->comments($$columnhash{'description'}) if $$columnhash{'description'};
         }
 
         $index_select->execute($table_oid);
@@ -152,7 +164,7 @@ ORDER BY 1;
         my @column_names = $table->field_names();
         while (my $indexhash = $index_select->fetchrow_hashref ) {
               #don't deal with function indexes at the moment
-            next if ($$indexhash{'indkey'} eq '' 
+            next if ($$indexhash{'indkey'} eq ''
                      or !defined($$indexhash{'indkey'}) );
 
             my $type;
@@ -169,11 +181,9 @@ ORDER BY 1;
                 $type = NORMAL;
             }
 
+
             my @column_ids = split /\s+/, $$indexhash{'indkey'};
-            my @columns;
-            foreach my $col (@column_ids) {
-                push @columns, $column_names[($col - 1)];
-            }
+            my @columns = split /\s+/, $$indexhash{'attname'};
 
             $table->add_index(
                               name         => $$indexhash{'relname'},
@@ -181,10 +191,10 @@ ORDER BY 1;
                               fields       => \@columns,
                              ) || die $table->error;
         }
-        
+
         $fk_select->execute('public',$table_name) or die "Can't execute: $@";
         my $fkeys = $fk_select->fetchall_arrayref({});
-        print Dumper $fkeys;
+        $DEBUG and print Dumper $fkeys;
         for my $con (@$fkeys){
             my $con_name         = $con->{conname};
             my $fields           = $con->{fields};
@@ -203,7 +213,7 @@ ORDER BY 1;
                                );
         }
     }
-    
+
 
     return 1;
 }
@@ -219,7 +229,7 @@ ORDER BY 1;
 
 =head1 AUTHOR
 
-Scott Cain E<lt>cain@cshl.eduE<gt>, previous author: 
+Scott Cain E<lt>cain@cshl.eduE<gt>, previous author:
 Paul Harrington E<lt>harringp@deshaw.comE<gt>.
 
 =head1 SEE ALSO