From: Jess Robinson Date: Tue, 26 Aug 2008 22:28:49 +0000 (+0000) Subject: Added patch from wreis, view support for pg producer X-Git-Tag: v0.11008~303 X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FSQL-Translator.git;a=commitdiff_plain;h=296c27014d3aae53005b1727c7ab2397ba1a3346 Added patch from wreis, view support for pg producer --- diff --git a/AUTHORS b/AUTHORS index b3b8ff1..440ecb1 100644 --- a/AUTHORS +++ b/AUTHORS @@ -18,6 +18,7 @@ The following people have contributed to the SQLFairy project: - Jason Williams - Ying Zhang - Guillermo Roditi +- Wallace Reis 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 --- 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 --- 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 --- 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 --- 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');