From: Jess Robinson Date: Wed, 17 Sep 2008 13:29:09 +0000 (+0000) Subject: Pg views and sqlite views, patch from wreis X-Git-Tag: v0.11008~298 X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FSQL-Translator.git;a=commitdiff_plain;h=a25ac5d2649e414439ab60a075297361f2e5819d Pg views and sqlite views, patch from wreis --- diff --git a/Changes b/Changes index 0fdf29b..9dfdf9a 100644 --- a/Changes +++ b/Changes @@ -4,6 +4,8 @@ * Added support for CREATE VIEW + tests in the Pg producer (wreis) * Added support for CREATE VIEW + tests in the sqlite producer (groditi) * Added proper argument parsing and documentation to MySQL Parser and Producer (ribasushi) +* Using DROP VIEW instead of OR REPLACE clause in the Pg producer, as replace only allows replacement with same number of columns (wreis) +* Added support for DROP VIEW and fixed CREATE VIEW statement in the sqlite producer (wreis) # ---------------------------------------------------------- # 0.09001 2008-08-19 diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm index 7737b91..ac68634 100644 --- a/lib/SQL/Translator/Producer/PostgreSQL.pm +++ b/lib/SQL/Translator/Producer/PostgreSQL.pm @@ -40,7 +40,7 @@ use strict; use warnings; use vars qw[ $DEBUG $WARN $VERSION %used_names ]; $VERSION = sprintf "%d.%02d", q$Revision: 1.29 $ =~ /(\d+)\.(\d+)/; -$DEBUG = 1 unless defined $DEBUG; +$DEBUG = 0 unless defined $DEBUG; use SQL::Translator::Schema::Constants; use SQL::Translator::Utils qw(debug header_comment); @@ -176,8 +176,8 @@ and table_constraint is: # ------------------------------------------------------------------- sub produce { my $translator = shift; - $DEBUG = $translator->debug; - $WARN = $translator->show_warnings; + local $DEBUG = $translator->debug; + local $WARN = $translator->show_warnings; my $no_comments = $translator->no_comments; my $add_drop_table = $translator->add_drop_table; my $schema = $translator->schema; @@ -210,7 +210,7 @@ sub produce { for my $view ( $schema->get_views ) { push @table_defs, create_view($view, { - add_replace_view => $add_drop_table, + add_drop_view => $add_drop_table, quote_table_names => $qt, quote_field_names => $qf, no_comments => $no_comments, @@ -416,6 +416,7 @@ sub create_view { my ($view, $options) = @_; my $qt = $options->{quote_table_names} || ''; my $qf = $options->{quote_field_names} || ''; + my $add_drop_view = $options->{add_drop_view}; my $view_name = $view->name; debug("PKG: Looking at view '${view_name}'\n"); @@ -423,8 +424,8 @@ sub create_view { my $create = ''; $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n" unless $options->{no_comments}; + $create .= "DROP VIEW ${qt}${view_name}${qt};\n" if $add_drop_view; $create .= 'CREATE'; - $create .= ' OR REPLACE' if $options->{add_replace_view}; my $extra = $view->extra; $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary}; diff --git a/lib/SQL/Translator/Producer/SQLite.pm b/lib/SQL/Translator/Producer/SQLite.pm index eea69c4..3060875 100644 --- a/lib/SQL/Translator/Producer/SQLite.pm +++ b/lib/SQL/Translator/Producer/SQLite.pm @@ -77,7 +77,10 @@ sub produce { } for my $view ( $schema->get_views ) { - push @table_defs, create_view($view, {no_comments => $no_comments,}); + push @table_defs, create_view($view, { + add_drop_view => $add_drop_table, + no_comments => $no_comments, + }); } # $create .= "COMMIT;\n"; @@ -123,6 +126,7 @@ sub mk_name { sub create_view { my ($view, $options) = @_; + my $add_drop_view = $options->{add_drop_view}; my $view_name = $view->name; debug("PKG: Looking at view '${view_name}'\n"); @@ -131,6 +135,7 @@ sub create_view { my $extra = $view->extra; my $create = ''; $create .= "--\n-- View: ${view_name}\n--\n" unless $options->{no_comments}; + $create .= "DROP VIEW IF EXISTS $view_name;\n" if $add_drop_view; $create .= 'CREATE'; $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary}; $create .= ' VIEW'; @@ -138,7 +143,7 @@ sub create_view { $create .= " ${view_name}"; if( my $sql = $view->sql ){ - $create .= " AS (\n ${sql}\n )"; + $create .= " AS\n ${sql}"; } $create .= ";\n\n"; return $create; diff --git a/t/46xml-to-pg.t b/t/46xml-to-pg.t index e3b3540..4747e16 100644 --- a/t/46xml-to-pg.t +++ b/t/46xml-to-pg.t @@ -59,7 +59,8 @@ CREATE TABLE "Another" ( -CREATE OR REPLACE VIEW "email_list" ( "email" ) AS ( +DROP VIEW "email_list"; +CREATE VIEW "email_list" ( "email" ) AS ( SELECT email FROM Basic WHERE email IS NOT NULL ); diff --git a/t/47postgres-producer.t b/t/47postgres-producer.t index ba9f44d..5dc7ccc 100644 --- a/t/47postgres-producer.t +++ b/t/47postgres-producer.t @@ -112,7 +112,7 @@ my $view1 = SQL::Translator::Schema::View->new( 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 ( +my $view_sql_replace = "CREATE 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'); @@ -128,7 +128,7 @@ my $view2 = SQL::Translator::Schema::View->new( 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 ( +my $view2_sql_replace = "CREATE 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'); diff --git a/t/48xml-to-sqlite.t b/t/48xml-to-sqlite.t index 3473824..f35846f 100644 --- a/t/48xml-to-sqlite.t +++ b/t/48xml-to-sqlite.t @@ -62,9 +62,9 @@ CREATE TABLE Another ( ); -CREATE VIEW email_list AS ( - SELECT email FROM Basic WHERE email IS NOT NULL - ); +DROP VIEW IF EXISTS email_list; +CREATE VIEW email_list AS + SELECT email FROM Basic WHERE email IS NOT NULL; COMMIT;