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