Added patch from wreis, view support for pg producer

diff --git a/AUTHORS b/AUTHORS
index b3b8ff1..440ecb1 100644 (file)
--- a/AUTHORS
+++ b/AUTHORS
@@ -18,6 +18,7 @@ The following people have contributed to the SQLFairy project:
 -   Jason Williams <smdwilliams@users.sourceforge.net>
 -   Ying Zhang <zyolive@yahoo.com>
 -   Guillermo Roditi <groditi@cpan.org>
+-   Wallace Reis <wreis@cpan.org>
 
 If you would like to contribute to the project, you can send patches
 to the developers mailing list:

diff --git a/Changes b/Changes
index 3d36a19..3651b57 100644 (file)
--- a/Changes
+++ b/Changes
@@ -1,6 +1,7 @@
 # ----------------------------------------------------------
-#
+# 
 # ----------------------------------------------------------
+* Added support for CREATE VIEW + tests in the Pg producer (wreis)
 * Added support for CREATE VIEW + tests in the sqlite producer (groditi)
 
 # ----------------------------------------------------------
@@ -16,7 +17,7 @@
 # 0.09000 2008-02-25
 # ----------------------------------------------------------
 
-* Fix Pg produces idea of which field types need a size param
+* Fix Pg produces idea of which field types need a size param (wreis)
 * Add support for COLLATE table option to MySQL parser
 * Allow DEFAULT CHARACTER SET without '=' (as produced by mysqldump)
 

diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm
index a7abf10..7737b91 100644 (file)
--- a/lib/SQL/Translator/Producer/PostgreSQL.pm
+++ b/lib/SQL/Translator/Producer/PostgreSQL.pm
@@ -43,7 +43,7 @@ $VERSION = sprintf "%d.%02d", q$Revision: 1.29 $ =~ /(\d+)\.(\d+)/;
 $DEBUG = 1 unless defined $DEBUG;
 
 use SQL::Translator::Schema::Constants;
-use SQL::Translator::Utils qw(header_comment);
+use SQL::Translator::Utils qw(debug header_comment);
 use Data::Dumper;
 
 my %translate;
@@ -208,6 +208,15 @@ sub produce {
 
     }
 
+    for my $view ( $schema->get_views ) {
+      push @table_defs, create_view($view, {
+        add_replace_view  => $add_drop_table,
+        quote_table_names => $qt,
+        quote_field_names => $qf,
+        no_comments       => $no_comments,
+      });
+    }
+
     $output = join("\n\n", @table_defs);
     if ( @fks ) {
         $output .= "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
@@ -403,6 +412,41 @@ sub create_table
     return $create_statement, \@fks;
 }
 
+sub create_view {
+    my ($view, $options) = @_;
+    my $qt = $options->{quote_table_names} || '';
+    my $qf = $options->{quote_field_names} || '';
+
+    my $view_name = $view->name;
+    debug("PKG: Looking at view '${view_name}'\n");
+
+    my $create = '';
+    $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n"
+        unless $options->{no_comments};
+    $create .= 'CREATE';
+    $create .= ' OR REPLACE' if $options->{add_replace_view};
+
+    my $extra = $view->extra;
+    $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
+    $create .= " VIEW ${qt}${view_name}${qt}";
+
+    if ( my @fields = $view->fields ) {
+        my $field_list = join ', ', map { "${qf}${_}${qf}" } @fields;
+        $create .= " ( ${field_list} )";
+    }
+
+    if ( my $sql = $view->sql ) {
+        $create .= " AS (\n    ${sql}\n  )";
+    }
+
+    if ( $extra->{check_option} ) {
+        $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
+    }
+
+    $create .= ";\n\n";
+    return $create;
+}
+
 { 
 
     my %field_name_scope;

diff --git a/t/46xml-to-pg.t b/t/46xml-to-pg.t
index ced163f..e3b3540 100644 (file)
--- a/t/46xml-to-pg.t
+++ b/t/46xml-to-pg.t
@@ -57,6 +57,12 @@ CREATE TABLE "Another" (
   PRIMARY KEY ("id")
 );
 
+
+
+CREATE OR REPLACE VIEW "email_list" ( "email" ) AS (
+    SELECT email FROM Basic WHERE email IS NOT NULL
+  );
+
 ALTER TABLE "Basic" ADD FOREIGN KEY ("another_id")
   REFERENCES "Another" ("id") DEFERRABLE;
 SQL

diff --git a/t/47postgres-producer.t b/t/47postgres-producer.t
index d52136f..ba9f44d 100644 (file)
--- a/t/47postgres-producer.t
+++ b/t/47postgres-producer.t
@@ -14,7 +14,7 @@ use FindBin qw/$Bin/;
 #=============================================================================
 
 BEGIN {
-    maybe_plan(7,
+    maybe_plan(9,
         'SQL::Translator::Producer::PostgreSQL',
         'Test::Differences',
     )
@@ -104,3 +104,31 @@ my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field($field5,{ p
 
 is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works');
 
+my $view1 = SQL::Translator::Schema::View->new(
+    name   => 'view_foo',
+    fields => [qw/id name/],
+    sql    => 'SELECT id, name FROM thing',
+);
+my $create_opts = { add_replace_view => 1, no_comments => 1 };
+my $view1_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view1, $create_opts);
+
+my $view_sql_replace = "CREATE OR REPLACE VIEW view_foo ( id, name ) AS (
+    SELECT id, name FROM thing
+  );\n\n";
+is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
+
+my $view2 = SQL::Translator::Schema::View->new(
+    name   => 'view_foo2',
+    sql    => 'SELECT id, name FROM thing',
+    extra  => {
+      'temporary'    => '1',
+      'check_option' => 'cascaded',
+    },
+);
+my $create2_opts = { add_replace_view => 1, no_comments => 1 };
+my $view2_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view2, $create2_opts);
+
+my $view2_sql_replace = "CREATE OR REPLACE TEMPORARY VIEW view_foo2 AS (
+    SELECT id, name FROM thing
+  ) WITH CASCADED CHECK OPTION;\n\n";
+is($view2_sql1, $view2_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL 2');