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