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     </table>
  87   </tables>
  88   <views></views>
  89   <triggers></triggers>
  90   <procedures></procedures>
  91 </schema>
  92 EOXML
  93
  94 $obj = SQL::Translator->new(
  95     debug          => DEBUG,
  96     trace          => TRACE,
  97     show_warnings  => 1,
  98     add_drop_table => 1,
  99     from           => "MySQL",
 100     to             => "XML-SQLFairy",
 101 );
 102 $xml = $obj->translate($file) or die $obj->error;
 103 ok("$xml" ne ""                             ,"Produced something!");
 104 print "XML:\n$xml" if DEBUG;
 105 # Strip sqlf header with its variable date so we diff safely
 106 $xml =~ s/^([^\n]*\n){7}//m;
 107 eq_or_diff $xml, $ans, "XML looks right";
 108
 109 } # end basic stuff
 110
 111 #
 112 # View
 113 #
 114 # Thanks to Ken for the schema setup lifted from 13schema.t
 115 {
 116 my ($obj,$ans,$xml);
 117
 118 $ans = <<EOXML;
 119 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 120   <extra />
 121   <tables></tables>
 122   <views>
 123     <view name="foo_view" fields="name,age" order="1">
 124       <sql>select name, age from person</sql>
 125       <extra hello="world" />
 126     </view>
 127   </views>
 128   <triggers></triggers>
 129   <procedures></procedures>
 130 </schema>
 131 EOXML
 132
 133     $obj = SQL::Translator->new(
 134         debug          => DEBUG,
 135         trace          => TRACE,
 136         show_warnings  => 1,
 137         add_drop_table => 1,
 138         from           => "MySQL",
 139         to             => "XML-SQLFairy",
 140     );
 141     my $s      = $obj->schema;
 142     my $name   = 'foo_view';
 143     my $sql    = 'select name, age from person';
 144     my $fields = 'name, age';
 145     my $v      = $s->add_view(
 146         name   => $name,
 147         sql    => $sql,
 148         fields => $fields,
 149         extra  => { hello => "world" },
 150         schema => $s,
 151     ) or die $s->error;
 152
 153     # As we have created a Schema we give translate a dummy string so that
 154     # it will run the produce.
 155     lives_ok {$xml =$obj->translate("FOO");} "Translate (View) ran";
 156     ok("$xml" ne ""                             ,"Produced something!");
 157     print "XML attrib_values=>1:\n$xml" if DEBUG;
 158     # Strip sqlf header with its variable date so we diff safely
 159     $xml =~ s/^([^\n]*\n){7}//m;
 160     eq_or_diff $xml, $ans                       ,"XML looks right";
 161 } # end View
 162
 163 #
 164 # Trigger
 165 #
 166 # Thanks to Ken for the schema setup lifted from 13schema.t
 167 {
 168 my ($obj,$ans,$xml);
 169
 170 $ans = <<EOXML;
 171 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 172   <extra />
 173   <tables></tables>
 174   <views></views>
 175   <triggers>
 176     <trigger name="foo_trigger" database_event="insert" on_table="foo" perform_action_when="after" order="1">
 177       <action>update modified=timestamp();</action>
 178       <extra hello="world" />
 179     </trigger>
 180   </triggers>
 181   <procedures></procedures>
 182 </schema>
 183 EOXML
 184
 185     $obj = SQL::Translator->new(
 186         debug          => DEBUG,
 187         trace          => TRACE,
 188         show_warnings  => 1,
 189         add_drop_table => 1,
 190         from           => "MySQL",
 191         to             => "XML-SQLFairy",
 192     );
 193     my $s                   = $obj->schema;
 194     my $name                = 'foo_trigger';
 195     my $perform_action_when = 'after';
 196     my $database_event      = 'insert';
 197     my $on_table            = 'foo';
 198     my $action              = 'update modified=timestamp();';
 199     my $t                   = $s->add_trigger(
 200         name                => $name,
 201         perform_action_when => $perform_action_when,
 202         database_event      => $database_event,
 203         on_table            => $on_table,
 204         action              => $action,
 205         extra               => { hello => "world" },
 206     ) or die $s->error;
 207
 208     # As we have created a Schema we give translate a dummy string so that
 209     # it will run the produce.
 210     lives_ok {$xml =$obj->translate("FOO");} "Translate (Trigger) ran";
 211     ok("$xml" ne ""                             ,"Produced something!");
 212     print "XML attrib_values=>1:\n$xml" if DEBUG;
 213     # Strip sqlf header with its variable date so we diff safely
 214     $xml =~ s/^([^\n]*\n){7}//m;
 215     eq_or_diff $xml, $ans                       ,"XML looks right";
 216 } # end Trigger
 217
 218 #
 219 # Procedure
 220 #
 221 # Thanks to Ken for the schema setup lifted from 13schema.t
 222 {
 223 my ($obj,$ans,$xml);
 224
 225 $ans = <<EOXML;
 226 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 227   <extra />
 228   <tables></tables>
 229   <views></views>
 230   <triggers></triggers>
 231   <procedures>
 232     <procedure name="foo_proc" parameters="foo,bar" owner="Nomar" order="1">
 233       <sql>select foo from bar</sql>
 234       <comments>Go Sox!</comments>
 235       <extra hello="world" />
 236     </procedure>
 237   </procedures>
 238 </schema>
 239 EOXML
 240
 241     $obj = SQL::Translator->new(
 242         debug          => DEBUG,
 243         trace          => TRACE,
 244         show_warnings  => 1,
 245         add_drop_table => 1,
 246         from           => "MySQL",
 247         to             => "XML-SQLFairy",
 248     );
 249     my $s          = $obj->schema;
 250     my $name       = 'foo_proc';
 251     my $sql        = 'select foo from bar';
 252     my $parameters = 'foo, bar';
 253     my $owner      = 'Nomar';
 254     my $comments   = 'Go Sox!';
 255     my $p          = $s->add_procedure(
 256         name       => $name,
 257         sql        => $sql,
 258         parameters => $parameters,
 259         owner      => $owner,
 260         comments   => $comments,
 261         extra      => { hello => "world" },
 262     ) or die $s->error;
 263
 264     # As we have created a Schema we give translate a dummy string so that
 265     # it will run the produce.
 266     lives_ok {$xml =$obj->translate("FOO");} "Translate (Procedure) ran";
 267     ok("$xml" ne ""                             ,"Produced something!");
 268     print "XML attrib_values=>1:\n$xml" if DEBUG;
 269     # Strip sqlf header with its variable date so we diff safely
 270     $xml =~ s/^([^\n]*\n){7}//m;
 271     eq_or_diff $xml, $ans                       ,"XML looks right";
 272 } # end Procedure
 273
 274 #
 275 # Field.extra
 276 #
 277 {
 278 my ($obj,$ans,$xml);
 279
 280 $ans = <<EOXML;
 281 <schema name="" database="" xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
 282   <extra />
 283   <tables>
 284     <table name="Basic" order="2">
 285       <extra />
 286       <fields>
 287         <field name="foo" data_type="integer" size="10" is_nullable="1" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" order="5">
 288           <extra ZEROFILL="1" />
 289           <comments></comments>
 290         </field>
 291       </fields>
 292       <indices></indices>
 293       <constraints></constraints>
 294     </table>
 295   </tables>
 296   <views></views>
 297   <triggers></triggers>
 298   <procedures></procedures>
 299 </schema>
 300 EOXML
 301
 302     $obj = SQL::Translator->new(
 303         debug          => DEBUG,
 304         trace          => TRACE,
 305         show_warnings  => 1,
 306         add_drop_table => 1,
 307         from           => "MySQL",
 308         to             => "XML-SQLFairy",
 309     );
 310     my $s = $obj->schema;
 311     my $t = $s->add_table( name => "Basic" ) or die $s->error;
 312     my $f = $t->add_field(
 313         name      => "foo",
 314         data_type => "integer",
 315         size      => "10",
 316     ) or die $t->error;
 317     $f->extra(ZEROFILL => "1");
 318
 319     # As we have created a Schema we give translate a dummy string so that
 320     # it will run the produce.
 321     lives_ok {$xml =$obj->translate("FOO");} "Translate (Field.extra) ran";
 322     ok("$xml" ne ""                             ,"Produced something!");
 323     print "XML:\n$xml" if DEBUG;
 324     # Strip sqlf header with its variable date so we diff safely
 325     $xml =~ s/^([^\n]*\n){7}//m;
 326     eq_or_diff $xml, $ans                       ,"XML looks right";
 327 } # end extra