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(14,
  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 # Due to formatters being able to change style, e.g. by entries in .rc files
  44 # in $HOME, the layout and or indent might differ slightly. As leading white
  45 # is not important in XML, strip it when comparing
  46 sub xml_equals
  47 {
  48     my ($got, $expect, $msg) = (@_, "XML looks right");
  49     $got    =~ s/^ +//gm;
  50     $expect =~ s/^ +//gm;
  51     eq_or_diff $got, $expect, $msg;
  52 }
  53
  54 #
  55 # basic stuff
  56 #
  57 {
  58 my ($obj,$ans,$xml);
  59
  60 $ans = <<EOXML;
  61 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
  62   <extra />
  63   <tables>
  64     <table name="Basic" order="1">
  65       <extra />
  66       <fields>
  67         <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">
  68           <extra />
  69           <comments>comment on id field</comments>
  70         </field>
  71         <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">
  72           <extra />
  73           <comments></comments>
  74         </field>
  75         <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">
  76           <extra />
  77           <comments></comments>
  78         </field>
  79         <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">
  80           <extra />
  81           <comments></comments>
  82         </field>
  83       </fields>
  84       <indices>
  85         <index name="titleindex" type="NORMAL" fields="title" options="">
  86           <extra />
  87         </index>
  88       </indices>
  89       <constraints>
  90         <constraint name="" type="PRIMARY KEY" fields="id" reference_table="" reference_fields="" on_delete="" on_update="" match_type="" expression="" options="" deferrable="1">
  91           <extra />
  92         </constraint>
  93         <constraint name="" type="UNIQUE" fields="email" reference_table="" reference_fields="" on_delete="" on_update="" match_type="" expression="" options="" deferrable="1">
  94           <extra />
  95         </constraint>
  96       </constraints>
  97       <comments></comments>
  98     </table>
  99   </tables>
 100   <views></views>
 101   <triggers></triggers>
 102   <procedures></procedures>
 103 </schema>
 104 EOXML
 105
 106 $obj = SQL::Translator->new(
 107     debug          => DEBUG,
 108     trace          => TRACE,
 109     show_warnings  => 1,
 110     add_drop_table => 1,
 111     from           => "MySQL",
 112     to             => "XML-SQLFairy",
 113 );
 114 $xml = $obj->translate($file) or die $obj->error;
 115 ok("$xml" ne ""                             ,"Produced something!");
 116 print "XML:\n$xml" if DEBUG;
 117 # Strip sqlf header with its variable date so we diff safely
 118 $xml =~ s/^([^\n]*\n){7}//m;
 119 xml_equals $xml, $ans;
 120
 121 } # end basic stuff
 122
 123 #
 124 # View
 125 #
 126 # Thanks to Ken for the schema setup lifted from 13schema.t
 127 {
 128 my ($obj,$ans,$xml);
 129
 130 $ans = <<EOXML;
 131 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 132   <extra />
 133   <tables></tables>
 134   <views>
 135     <view name="foo_view" fields="name,age" order="1">
 136       <sql>select name, age from person</sql>
 137       <extra hello="world" />
 138     </view>
 139   </views>
 140   <triggers></triggers>
 141   <procedures></procedures>
 142 </schema>
 143 EOXML
 144
 145     $obj = SQL::Translator->new(
 146         debug          => DEBUG,
 147         trace          => TRACE,
 148         show_warnings  => 1,
 149         add_drop_table => 1,
 150         from           => "MySQL",
 151         to             => "XML-SQLFairy",
 152     );
 153     my $s      = $obj->schema;
 154     my $name   = 'foo_view';
 155     my $sql    = 'select name, age from person';
 156     my $fields = 'name, age';
 157     my $v      = $s->add_view(
 158         name   => $name,
 159         sql    => $sql,
 160         fields => $fields,
 161         extra  => { hello => "world" },
 162         schema => $s,
 163     ) or die $s->error;
 164
 165     # As we have created a Schema we give translate a dummy string so that
 166     # it will run the produce.
 167     lives_ok {$xml =$obj->translate("FOO");} "Translate (View) ran";
 168     ok("$xml" ne ""                             ,"Produced something!");
 169     print "XML attrib_values=>1:\n$xml" if DEBUG;
 170     # Strip sqlf header with its variable date so we diff safely
 171     $xml =~ s/^([^\n]*\n){7}//m;
 172     xml_equals $xml, $ans;
 173 } # end View
 174
 175 #
 176 # Trigger
 177 #
 178 # Thanks to Ken for the schema setup lifted from 13schema.t
 179 {
 180 my ($obj,$ans,$xml);
 181
 182 $ans = <<EOXML;
 183 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 184   <extra />
 185   <tables>
 186     <table name="Basic" order="1">
 187       <extra />
 188       <fields></fields>
 189       <indices></indices>
 190       <constraints></constraints>
 191       <comments></comments>
 192     </table>
 193   </tables>
 194   <views></views>
 195   <triggers>
 196     <trigger name="foo_trigger" database_events="insert" on_table="Basic" perform_action_when="after" order="1">
 197       <action>update modified=timestamp();</action>
 198       <extra hello="world" />
 199     </trigger>
 200   </triggers>
 201   <procedures></procedures>
 202 </schema>
 203 EOXML
 204
 205     $obj = SQL::Translator->new(
 206         debug          => DEBUG,
 207         trace          => TRACE,
 208         show_warnings  => 1,
 209         add_drop_table => 1,
 210         from           => "MySQL",
 211         to             => "XML-SQLFairy",
 212     );
 213     my $s                   = $obj->schema;
 214     my $name                = 'foo_trigger';
 215     my $perform_action_when = 'after';
 216     my $database_event      = 'insert';
 217     my $action              = 'update modified=timestamp();';
 218     my $table = $s->add_table( name => "Basic" ) or die $s->error;
 219     my $t                   = $s->add_trigger(
 220         name                => $name,
 221         perform_action_when => $perform_action_when,
 222         database_events     => [$database_event],
 223         table               => $table,
 224         action              => $action,
 225         extra               => { hello => "world" },
 226     ) or die $s->error;
 227
 228     # As we have created a Schema we give translate a dummy string so that
 229     # it will run the produce.
 230     lives_ok {$xml =$obj->translate("FOO");} "Translate (Trigger) ran";
 231     ok("$xml" ne ""                             ,"Produced something!");
 232     print "XML attrib_values=>1:\n$xml" if DEBUG;
 233     # Strip sqlf header with its variable date so we diff safely
 234     $xml =~ s/^([^\n]*\n){7}//m;
 235     xml_equals $xml, $ans;
 236 } # end Trigger
 237
 238 #
 239 # Procedure
 240 #
 241 # Thanks to Ken for the schema setup lifted from 13schema.t
 242 {
 243 my ($obj,$ans,$xml);
 244
 245 $ans = <<EOXML;
 246 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 247   <extra />
 248   <tables></tables>
 249   <views></views>
 250   <triggers></triggers>
 251   <procedures>
 252     <procedure name="foo_proc" parameters="foo,bar" owner="Nomar" order="1">
 253       <sql>select foo from bar</sql>
 254       <comments>Go Sox!</comments>
 255       <extra hello="world" />
 256     </procedure>
 257   </procedures>
 258 </schema>
 259 EOXML
 260
 261     $obj = SQL::Translator->new(
 262         debug          => DEBUG,
 263         trace          => TRACE,
 264         show_warnings  => 1,
 265         add_drop_table => 1,
 266         from           => "MySQL",
 267         to             => "XML-SQLFairy",
 268     );
 269     my $s          = $obj->schema;
 270     my $name       = 'foo_proc';
 271     my $sql        = 'select foo from bar';
 272     my $parameters = 'foo, bar';
 273     my $owner      = 'Nomar';
 274     my $comments   = 'Go Sox!';
 275     my $p          = $s->add_procedure(
 276         name       => $name,
 277         sql        => $sql,
 278         parameters => $parameters,
 279         owner      => $owner,
 280         comments   => $comments,
 281         extra      => { hello => "world" },
 282     ) or die $s->error;
 283
 284     # As we have created a Schema we give translate a dummy string so that
 285     # it will run the produce.
 286     lives_ok {$xml =$obj->translate("FOO");} "Translate (Procedure) ran";
 287     ok("$xml" ne ""                             ,"Produced something!");
 288     print "XML attrib_values=>1:\n$xml" if DEBUG;
 289     # Strip sqlf header with its variable date so we diff safely
 290     $xml =~ s/^([^\n]*\n){7}//m;
 291     xml_equals $xml, $ans;
 292 } # end Procedure
 293
 294 #
 295 # Field.extra
 296 #
 297 {
 298 my ($obj,$ans,$xml);
 299
 300 $ans = <<EOXML;
 301 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 302   <extra />
 303   <tables>
 304     <table name="Basic" order="1">
 305       <extra />
 306       <fields>
 307         <field name="foo" data_type="integer" size="10" is_nullable="1" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="1">
 308           <extra ZEROFILL="1" />
 309           <comments></comments>
 310         </field>
 311         <field name="bar" data_type="numeric" size="10,2" is_nullable="1" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="2">
 312           <extra />
 313           <comments></comments>
 314         </field>
 315         <field name="baz" data_type="decimal" size="8,3" is_nullable="1" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="3">
 316           <extra />
 317           <comments></comments>
 318         </field>
 319       </fields>
 320       <indices></indices>
 321       <constraints></constraints>
 322       <comments></comments>
 323     </table>
 324   </tables>
 325   <views></views>
 326   <triggers></triggers>
 327   <procedures></procedures>
 328 </schema>
 329 EOXML
 330
 331     $obj = SQL::Translator->new(
 332         debug          => DEBUG,
 333         trace          => TRACE,
 334         show_warnings  => 1,
 335         add_drop_table => 1,
 336         from           => "MySQL",
 337         to             => "XML-SQLFairy",
 338     );
 339     my $s = $obj->schema;
 340     my $t = $s->add_table( name => "Basic" ) or die $s->error;
 341     my $f = $t->add_field(
 342         name      => "foo",
 343         data_type => "integer",
 344         size      => "10",
 345     ) or die $t->error;
 346     $f->extra(ZEROFILL => "1");
 347
 348     $t->add_field(
 349         name      => "bar",
 350         data_type => "numeric",
 351         size      => "10,2",
 352     ) or die $t->error;
 353     $t->add_field(
 354         name      => "baz",
 355         data_type => "decimal",
 356         size      => [8,3],
 357     ) or die $t->error;
 358
 359
 360     # As we have created a Schema we give translate a dummy string so that
 361     # it will run the produce.
 362     lives_ok {$xml =$obj->translate("FOO");} "Translate (Field.extra) ran";
 363     ok("$xml" ne ""                             ,"Produced something!");
 364     print "XML:\n$xml" if DEBUG;
 365     # Strip sqlf header with its variable date so we diff safely
 366     $xml =~ s/^([^\n]*\n){7}//m;
 367     xml_equals $xml, $ans;
 368 } # end extra