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" scope="row">
 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         scope               => 'row',
 226         extra               => { hello => "world" },
 227     ) or die $s->error;
 228
 229     # As we have created a Schema we give translate a dummy string so that
 230     # it will run the produce.
 231     lives_ok {$xml =$obj->translate("FOO");} "Translate (Trigger) ran";
 232     ok("$xml" ne ""                             ,"Produced something!");
 233     print "XML attrib_values=>1:\n$xml" if DEBUG;
 234     # Strip sqlf header with its variable date so we diff safely
 235     $xml =~ s/^([^\n]*\n){7}//m;
 236     xml_equals $xml, $ans;
 237 } # end Trigger
 238
 239 #
 240 # Procedure
 241 #
 242 # Thanks to Ken for the schema setup lifted from 13schema.t
 243 {
 244 my ($obj,$ans,$xml);
 245
 246 $ans = <<EOXML;
 247 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 248   <extra />
 249   <tables></tables>
 250   <views></views>
 251   <triggers></triggers>
 252   <procedures>
 253     <procedure name="foo_proc" parameters="foo,bar" owner="Nomar" order="1">
 254       <sql>select foo from bar</sql>
 255       <comments>Go Sox!</comments>
 256       <extra hello="world" />
 257     </procedure>
 258   </procedures>
 259 </schema>
 260 EOXML
 261
 262     $obj = SQL::Translator->new(
 263         debug          => DEBUG,
 264         trace          => TRACE,
 265         show_warnings  => 1,
 266         add_drop_table => 1,
 267         from           => "MySQL",
 268         to             => "XML-SQLFairy",
 269     );
 270     my $s          = $obj->schema;
 271     my $name       = 'foo_proc';
 272     my $sql        = 'select foo from bar';
 273     my $parameters = 'foo, bar';
 274     my $owner      = 'Nomar';
 275     my $comments   = 'Go Sox!';
 276     my $p          = $s->add_procedure(
 277         name       => $name,
 278         sql        => $sql,
 279         parameters => $parameters,
 280         owner      => $owner,
 281         comments   => $comments,
 282         extra      => { hello => "world" },
 283     ) or die $s->error;
 284
 285     # As we have created a Schema we give translate a dummy string so that
 286     # it will run the produce.
 287     lives_ok {$xml =$obj->translate("FOO");} "Translate (Procedure) ran";
 288     ok("$xml" ne ""                             ,"Produced something!");
 289     print "XML attrib_values=>1:\n$xml" if DEBUG;
 290     # Strip sqlf header with its variable date so we diff safely
 291     $xml =~ s/^([^\n]*\n){7}//m;
 292     xml_equals $xml, $ans;
 293 } # end Procedure
 294
 295 #
 296 # Field.extra
 297 #
 298 {
 299 my ($obj,$ans,$xml);
 300
 301 $ans = <<EOXML;
 302 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 303   <extra />
 304   <tables>
 305     <table name="Basic" order="1">
 306       <extra />
 307       <fields>
 308         <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">
 309           <extra ZEROFILL="1" />
 310           <comments></comments>
 311         </field>
 312         <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">
 313           <extra />
 314           <comments></comments>
 315         </field>
 316         <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">
 317           <extra />
 318           <comments></comments>
 319         </field>
 320       </fields>
 321       <indices></indices>
 322       <constraints></constraints>
 323       <comments></comments>
 324     </table>
 325   </tables>
 326   <views></views>
 327   <triggers></triggers>
 328   <procedures></procedures>
 329 </schema>
 330 EOXML
 331
 332     $obj = SQL::Translator->new(
 333         debug          => DEBUG,
 334         trace          => TRACE,
 335         show_warnings  => 1,
 336         add_drop_table => 1,
 337         from           => "MySQL",
 338         to             => "XML-SQLFairy",
 339     );
 340     my $s = $obj->schema;
 341     my $t = $s->add_table( name => "Basic" ) or die $s->error;
 342     my $f = $t->add_field(
 343         name      => "foo",
 344         data_type => "integer",
 345         size      => "10",
 346     ) or die $t->error;
 347     $f->extra(ZEROFILL => "1");
 348
 349     $t->add_field(
 350         name      => "bar",
 351         data_type => "numeric",
 352         size      => "10,2",
 353     ) or die $t->error;
 354     $t->add_field(
 355         name      => "baz",
 356         data_type => "decimal",
 357         size      => [8,3],
 358     ) or die $t->error;
 359
 360
 361     # As we have created a Schema we give translate a dummy string so that
 362     # it will run the produce.
 363     lives_ok {$xml =$obj->translate("FOO");} "Translate (Field.extra) ran";
 364     ok("$xml" ne ""                             ,"Produced something!");
 365     print "XML:\n$xml" if DEBUG;
 366     # Strip sqlf header with its variable date so we diff safely
 367     $xml =~ s/^([^\n]*\n){7}//m;
 368     xml_equals $xml, $ans;
 369 } # end extra