t/17sqlfxml-producer.t

   1 #!/usr/bin/perl -w
   2 # vim:filetype=perl
   3
   4 # Before `make install' is performed this script should be runnable with
   5 # `make test'. After `make install' it should work as `perl test.pl'
   6
   7 local $^W = 0;
   8
   9 use strict;
  10 use Test::More;
  11 use Test::Exception;
  12 use Test::SQL::Translator qw(maybe_plan);
  13
  14 use Data::Dumper;
  15 my %opt;
  16 BEGIN { map { $opt{$_}=1 if s/^-// } @ARGV; }
  17 use constant DEBUG => (exists $opt{d} ? 1 : 0);
  18 use constant TRACE => (exists $opt{t} ? 1 : 0);
  19
  20 use FindBin qw/$Bin/;
  21
  22 my $file = "$Bin/data/mysql/sqlfxml-producer-basic.sql";
  23
  24 local $SIG{__WARN__} = sub {
  25     CORE::warn(@_)
  26         unless $_[0] =~ m#XML/Writer#;
  27 };
  28
  29 # Testing 1,2,3,4...
  30 #=============================================================================
  31
  32 BEGIN {
  33     maybe_plan(12,
  34         'XML::Writer',
  35         'Test::Differences',
  36         'SQL::Translator::Producer::XML::SQLFairy');
  37 }
  38
  39 use Test::Differences;
  40 use SQL::Translator;
  41 use SQL::Translator::Producer::XML::SQLFairy;
  42
  43 #
  44 # basic stuff
  45 #
  46 {
  47 my ($obj,$ans,$xml);
  48
  49 $ans = <<EOXML;
  50 <sqlf:schema name="" database="" xmlns:sqlf="http://sqlfairy.sourceforge.net/sqlfairy.xml">
  51   <sqlf:table name="Basic" order="1">
  52     <sqlf:fields>
  53       <sqlf:field name="id" data_type="integer" size="10" is_nullable="0" is_auto_increment="1" is_primary_key="1" is_foreign_key="0" order="1">
  54         <sqlf:comments>comment on id field</sqlf:comments>
  55       </sqlf:field>
  56       <sqlf:field name="title" data_type="varchar" size="100" is_nullable="0" default_value="hello" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="2">
  57         <sqlf:comments></sqlf:comments>
  58       </sqlf:field>
  59       <sqlf:field name="description" data_type="text" size="65535" is_nullable="1" default_value="" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="3">
  60         <sqlf:comments></sqlf:comments>
  61       </sqlf:field>
  62       <sqlf:field name="email" data_type="varchar" size="255" is_nullable="1" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="4">
  63         <sqlf:comments></sqlf:comments>
  64       </sqlf:field>
  65     </sqlf:fields>
  66     <sqlf:indices>
  67       <sqlf:index name="titleindex" type="NORMAL" fields="title" options="" />
  68     </sqlf:indices>
  69     <sqlf:constraints>
  70       <sqlf:constraint name="" type="PRIMARY KEY" fields="id" reference_table="" reference_fields="" on_delete="" on_update="" match_type="" expression="" options="" deferrable="1" />
  71       <sqlf:constraint name="" type="UNIQUE" fields="email" reference_table="" reference_fields="" on_delete="" on_update="" match_type="" expression="" options="" deferrable="1" />
  72     </sqlf:constraints>
  73   </sqlf:table>
  74 </sqlf:schema>
  75 EOXML
  76
  77 $obj = SQL::Translator->new(
  78     debug          => DEBUG,
  79     trace          => TRACE,
  80     show_warnings  => 1,
  81     add_drop_table => 1,
  82     from           => "MySQL",
  83     to             => "XML-SQLFairy",
  84 );
  85 lives_ok {$xml = $obj->translate($file);} "Translate (attrib_values=>1) ran";
  86 ok("$xml" ne ""                             ,"Produced something!");
  87 print "XML:\n$xml" if DEBUG;
  88 # Strip sqlf header with its variable date so we diff safely
  89 $xml =~ s/^([^\n]*\n){7}//m;
  90 eq_or_diff $xml, $ans, "XML looks right";
  91
  92 } # end basic stuff
  93
  94 #
  95 # View
  96 #
  97 # Thanks to Ken for the schema setup lifted from 13schema.t
  98 {
  99 my ($obj,$ans,$xml);
 100
 101 $ans = <<EOXML;
 102 <sqlf:schema name="" database="" xmlns:sqlf="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 103   <sqlf:view name="foo_view" fields="name,age" order="1">
 104     <sqlf:sql>select name, age from person</sqlf:sql>
 105   </sqlf:view>
 106 </sqlf:schema>
 107 EOXML
 108
 109     $obj = SQL::Translator->new(
 110         debug          => DEBUG,
 111         trace          => TRACE,
 112         show_warnings  => 1,
 113         add_drop_table => 1,
 114         from           => "MySQL",
 115         to             => "XML-SQLFairy",
 116     );
 117     my $s      = $obj->schema;
 118     my $name   = 'foo_view';
 119     my $sql    = 'select name, age from person';
 120     my $fields = 'name, age';
 121     my $v      = $s->add_view(
 122         name   => $name,
 123         sql    => $sql,
 124         fields => $fields,
 125         schema => $s,
 126     ) or die $s->error;
 127
 128     # As we have created a Schema we give translate a dummy string so that
 129     # it will run the produce.
 130     lives_ok {$xml =$obj->translate("FOO");} "Translate (View) ran";
 131     ok("$xml" ne ""                             ,"Produced something!");
 132     print "XML attrib_values=>1:\n$xml" if DEBUG;
 133     # Strip sqlf header with its variable date so we diff safely
 134     $xml =~ s/^([^\n]*\n){7}//m;
 135     eq_or_diff $xml, $ans                       ,"XML looks right";
 136 } # end View
 137
 138 #
 139 # Trigger
 140 #
 141 # Thanks to Ken for the schema setup lifted from 13schema.t
 142 {
 143 my ($obj,$ans,$xml);
 144
 145 $ans = <<EOXML;
 146 <sqlf:schema name="" database="" xmlns:sqlf="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 147   <sqlf:trigger name="foo_trigger" database_event="insert" on_table="foo" perform_action_when="after" order="1">
 148     <sqlf:action>update modified=timestamp();</sqlf:action>
 149   </sqlf:trigger>
 150 </sqlf:schema>
 151 EOXML
 152
 153     $obj = SQL::Translator->new(
 154         debug          => DEBUG,
 155         trace          => TRACE,
 156         show_warnings  => 1,
 157         add_drop_table => 1,
 158         from           => "MySQL",
 159         to             => "XML-SQLFairy",
 160     );
 161     my $s                   = $obj->schema;
 162     my $name                = 'foo_trigger';
 163     my $perform_action_when = 'after';
 164     my $database_event      = 'insert';
 165     my $on_table            = 'foo';
 166     my $action              = 'update modified=timestamp();';
 167     my $t                   = $s->add_trigger(
 168         name                => $name,
 169         perform_action_when => $perform_action_when,
 170         database_event      => $database_event,
 171         on_table            => $on_table,
 172         action              => $action,
 173     ) or die $s->error;
 174
 175     # As we have created a Schema we give translate a dummy string so that
 176     # it will run the produce.
 177     lives_ok {$xml =$obj->translate("FOO");} "Translate (Trigger) ran";
 178     ok("$xml" ne ""                             ,"Produced something!");
 179     print "XML attrib_values=>1:\n$xml" if DEBUG;
 180     # Strip sqlf header with its variable date so we diff safely
 181     $xml =~ s/^([^\n]*\n){7}//m;
 182     eq_or_diff $xml, $ans                       ,"XML looks right";
 183 } # end Trigger
 184
 185 #
 186 # Procedure
 187 #
 188 # Thanks to Ken for the schema setup lifted from 13schema.t
 189 {
 190 my ($obj,$ans,$xml);
 191
 192 $ans = <<EOXML;
 193 <sqlf:schema name="" database="" xmlns:sqlf="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 194   <sqlf:procedure name="foo_proc" parameters="foo,bar" owner="Nomar" order="1">
 195     <sqlf:sql>select foo from bar</sqlf:sql>
 196     <sqlf:comments>Go Sox!</sqlf:comments>
 197   </sqlf:procedure>
 198 </sqlf:schema>
 199 EOXML
 200
 201     $obj = SQL::Translator->new(
 202         debug          => DEBUG,
 203         trace          => TRACE,
 204         show_warnings  => 1,
 205         add_drop_table => 1,
 206         from           => "MySQL",
 207         to             => "XML-SQLFairy",
 208     );
 209     my $s          = $obj->schema;
 210     my $name       = 'foo_proc';
 211     my $sql        = 'select foo from bar';
 212     my $parameters = 'foo, bar';
 213     my $owner      = 'Nomar';
 214     my $comments   = 'Go Sox!';
 215     my $p          = $s->add_procedure(
 216         name       => $name,
 217         sql        => $sql,
 218         parameters => $parameters,
 219         owner      => $owner,
 220         comments   => $comments,
 221     ) or die $s->error;
 222
 223     # As we have created a Schema we give translate a dummy string so that
 224     # it will run the produce.
 225     lives_ok {$xml =$obj->translate("FOO");} "Translate (Procedure) ran";
 226     ok("$xml" ne ""                             ,"Produced something!");
 227     print "XML attrib_values=>1:\n$xml" if DEBUG;
 228     # Strip sqlf header with its variable date so we diff safely
 229     $xml =~ s/^([^\n]*\n){7}//m;
 230     eq_or_diff $xml, $ans                       ,"XML looks right";
 231 } # end Procedure