Just the fairy.
[dbsrgits/SQL-Translator.git] / t / 17sqlfxml-producer.t
CommitLineData
d0c12b9f 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
1ea530d4 7local $^W = 0;
8
d0c12b9f 9use strict;
10use Test::More;
11use Test::Exception;
12
13use Data::Dumper;
2e11379e 14my %opt;
d0c12b9f 15BEGIN { map { $opt{$_}=1 if s/^-// } @ARGV; }
16use constant DEBUG => (exists $opt{d} ? 1 : 0);
17use constant TRACE => (exists $opt{t} ? 1 : 0);
18
19use FindBin qw/$Bin/;
20
21my $file = "$Bin/data/mysql/sqlfxml-producer-basic.sql";
22
fbc0552f 23local $SIG{__WARN__} = sub {
24 CORE::warn(@_)
25 unless $_[0] =~ m#XML/Writer#;
26};
d0c12b9f 27
28# Testing 1,2,3,4...
29#=============================================================================
30
31eval { require XML::Writer; };
32if ($@ && $@ =~ m!locate XML::Writer.pm in!) {
ac62dff1 33 plan skip_all => "You need XML::Writer to use XML::SQLFairy.";
d0c12b9f 34}
35eval { require Test::Differences; };
36if ($@ && $@ =~ m!locate Test/Differences.pm in!) {
37 plan skip_all => "You need Test::Differences for this test.";
38}
39use Test::Differences;
1e3867bf 40plan tests => 18;
d3422086 41
d0c12b9f 42use SQL::Translator;
ac62dff1 43use SQL::Translator::Producer::XML::SQLFairy;
d0c12b9f 44
d0c12b9f 45#
46# emit_empty_tags => 0
47#
1e3867bf 48{
49my ($obj,$ans,$xml);
d0c12b9f 50
51$ans = <<EOXML;
52<sqlt:schema xmlns:sqlt="http://sqlfairy.sourceforge.net/sqlfairy.xml">
3b80481c 53 <sqlt:name></sqlt:name>
d3422086 54 <sqlt:database></sqlt:database>
d0c12b9f 55 <sqlt:table>
56 <sqlt:name>Basic</sqlt:name>
57 <sqlt:order>1</sqlt:order>
58 <sqlt:fields>
59 <sqlt:field>
d3422086 60 <sqlt:name>id</sqlt:name>
d0c12b9f 61 <sqlt:data_type>integer</sqlt:data_type>
d3422086 62 <sqlt:size>10</sqlt:size>
3b80481c 63 <sqlt:is_nullable>0</sqlt:is_nullable>
d3422086 64 <sqlt:is_auto_increment>1</sqlt:is_auto_increment>
3b80481c 65 <sqlt:is_primary_key>1</sqlt:is_primary_key>
d3422086 66 <sqlt:is_foreign_key>0</sqlt:is_foreign_key>
67 <sqlt:comments>comment on id field</sqlt:comments>
d0c12b9f 68 <sqlt:order>1</sqlt:order>
d0c12b9f 69 </sqlt:field>
70 <sqlt:field>
d3422086 71 <sqlt:name>title</sqlt:name>
d0c12b9f 72 <sqlt:data_type>varchar</sqlt:data_type>
d3422086 73 <sqlt:size>100</sqlt:size>
74 <sqlt:is_nullable>0</sqlt:is_nullable>
d0c12b9f 75 <sqlt:default_value>hello</sqlt:default_value>
76 <sqlt:is_auto_increment>0</sqlt:is_auto_increment>
3b80481c 77 <sqlt:is_primary_key>0</sqlt:is_primary_key>
d3422086 78 <sqlt:is_foreign_key>0</sqlt:is_foreign_key>
79 <sqlt:comments></sqlt:comments>
d0c12b9f 80 <sqlt:order>2</sqlt:order>
d0c12b9f 81 </sqlt:field>
82 <sqlt:field>
d3422086 83 <sqlt:name>description</sqlt:name>
d0c12b9f 84 <sqlt:data_type>text</sqlt:data_type>
d3422086 85 <sqlt:size>65535</sqlt:size>
86 <sqlt:is_nullable>1</sqlt:is_nullable>
d0c12b9f 87 <sqlt:default_value></sqlt:default_value>
88 <sqlt:is_auto_increment>0</sqlt:is_auto_increment>
3b80481c 89 <sqlt:is_primary_key>0</sqlt:is_primary_key>
d3422086 90 <sqlt:is_foreign_key>0</sqlt:is_foreign_key>
91 <sqlt:comments></sqlt:comments>
d0c12b9f 92 <sqlt:order>3</sqlt:order>
d0c12b9f 93 </sqlt:field>
94 <sqlt:field>
d3422086 95 <sqlt:name>email</sqlt:name>
d0c12b9f 96 <sqlt:data_type>varchar</sqlt:data_type>
d3422086 97 <sqlt:size>255</sqlt:size>
3b80481c 98 <sqlt:is_nullable>1</sqlt:is_nullable>
d3422086 99 <sqlt:is_auto_increment>0</sqlt:is_auto_increment>
3b80481c 100 <sqlt:is_primary_key>0</sqlt:is_primary_key>
d3422086 101 <sqlt:is_foreign_key>0</sqlt:is_foreign_key>
102 <sqlt:comments></sqlt:comments>
d0c12b9f 103 <sqlt:order>4</sqlt:order>
d0c12b9f 104 </sqlt:field>
105 </sqlt:fields>
106 <sqlt:indices>
107 <sqlt:index>
d0c12b9f 108 <sqlt:name>titleindex</sqlt:name>
d0c12b9f 109 <sqlt:type>NORMAL</sqlt:type>
d3422086 110 <sqlt:fields>title</sqlt:fields>
111 <sqlt:options></sqlt:options>
d0c12b9f 112 </sqlt:index>
113 </sqlt:indices>
114 <sqlt:constraints>
115 <sqlt:constraint>
d0c12b9f 116 <sqlt:name></sqlt:name>
d3422086 117 <sqlt:type>PRIMARY KEY</sqlt:type>
118 <sqlt:fields>id</sqlt:fields>
119 <sqlt:reference_table></sqlt:reference_table>
d0c12b9f 120 <sqlt:on_delete></sqlt:on_delete>
121 <sqlt:on_update></sqlt:on_update>
d3422086 122 <sqlt:match_type></sqlt:match_type>
123 <sqlt:expression></sqlt:expression>
3b80481c 124 <sqlt:options></sqlt:options>
d3422086 125 <sqlt:deferrable>1</sqlt:deferrable>
d0c12b9f 126 </sqlt:constraint>
127 <sqlt:constraint>
d0c12b9f 128 <sqlt:name></sqlt:name>
d3422086 129 <sqlt:type>UNIQUE</sqlt:type>
130 <sqlt:fields>email</sqlt:fields>
131 <sqlt:reference_table></sqlt:reference_table>
d0c12b9f 132 <sqlt:on_delete></sqlt:on_delete>
133 <sqlt:on_update></sqlt:on_update>
d3422086 134 <sqlt:match_type></sqlt:match_type>
135 <sqlt:expression></sqlt:expression>
3b80481c 136 <sqlt:options></sqlt:options>
d3422086 137 <sqlt:deferrable>1</sqlt:deferrable>
d0c12b9f 138 </sqlt:constraint>
139 </sqlt:constraints>
140 </sqlt:table>
141</sqlt:schema>
142EOXML
143
144$obj = SQL::Translator->new(
145 debug => DEBUG,
146 trace => TRACE,
147 show_warnings => 1,
148 add_drop_table => 1,
ac62dff1 149 from => 'MySQL',
150 to => 'XML-SQLFairy',
d0c12b9f 151);
f11724ad 152lives_ok {$xml = $obj->translate($file);} "Translate (emit_empty_tags=>0) ran";
d0c12b9f 153ok("$xml" ne "" ,"Produced something!");
154print "XML:\n$xml" if DEBUG;
155# Strip sqlf header with its variable date so we diff safely
156$xml =~ s/^([^\n]*\n){7}//m;
157eq_or_diff $xml, $ans ,"XML looks right";
158
1e3867bf 159} # end emit_empty_tags=>0
160
d0c12b9f 161#
162# emit_empty_tags => 1
163#
1e3867bf 164{
165my ($obj,$ans,$xml);
d0c12b9f 166
167$ans = <<EOXML;
168<sqlt:schema xmlns:sqlt="http://sqlfairy.sourceforge.net/sqlfairy.xml">
3b80481c 169 <sqlt:name></sqlt:name>
d3422086 170 <sqlt:database></sqlt:database>
d0c12b9f 171 <sqlt:table>
172 <sqlt:name>Basic</sqlt:name>
173 <sqlt:order>2</sqlt:order>
174 <sqlt:fields>
175 <sqlt:field>
d3422086 176 <sqlt:name>id</sqlt:name>
d0c12b9f 177 <sqlt:data_type>integer</sqlt:data_type>
d3422086 178 <sqlt:size>10</sqlt:size>
179 <sqlt:is_nullable>0</sqlt:is_nullable>
d0c12b9f 180 <sqlt:default_value></sqlt:default_value>
181 <sqlt:is_auto_increment>1</sqlt:is_auto_increment>
3b80481c 182 <sqlt:is_primary_key>1</sqlt:is_primary_key>
d3422086 183 <sqlt:is_foreign_key>0</sqlt:is_foreign_key>
184 <sqlt:comments>comment on id field</sqlt:comments>
d0c12b9f 185 <sqlt:order>5</sqlt:order>
d0c12b9f 186 </sqlt:field>
187 <sqlt:field>
d3422086 188 <sqlt:name>title</sqlt:name>
d0c12b9f 189 <sqlt:data_type>varchar</sqlt:data_type>
d3422086 190 <sqlt:size>100</sqlt:size>
191 <sqlt:is_nullable>0</sqlt:is_nullable>
d0c12b9f 192 <sqlt:default_value>hello</sqlt:default_value>
193 <sqlt:is_auto_increment>0</sqlt:is_auto_increment>
3b80481c 194 <sqlt:is_primary_key>0</sqlt:is_primary_key>
d3422086 195 <sqlt:is_foreign_key>0</sqlt:is_foreign_key>
196 <sqlt:comments></sqlt:comments>
d0c12b9f 197 <sqlt:order>6</sqlt:order>
d0c12b9f 198 </sqlt:field>
199 <sqlt:field>
d3422086 200 <sqlt:name>description</sqlt:name>
d0c12b9f 201 <sqlt:data_type>text</sqlt:data_type>
d3422086 202 <sqlt:size>65535</sqlt:size>
203 <sqlt:is_nullable>1</sqlt:is_nullable>
d0c12b9f 204 <sqlt:default_value></sqlt:default_value>
205 <sqlt:is_auto_increment>0</sqlt:is_auto_increment>
3b80481c 206 <sqlt:is_primary_key>0</sqlt:is_primary_key>
d3422086 207 <sqlt:is_foreign_key>0</sqlt:is_foreign_key>
208 <sqlt:comments></sqlt:comments>
d0c12b9f 209 <sqlt:order>7</sqlt:order>
d0c12b9f 210 </sqlt:field>
211 <sqlt:field>
d3422086 212 <sqlt:name>email</sqlt:name>
d0c12b9f 213 <sqlt:data_type>varchar</sqlt:data_type>
d3422086 214 <sqlt:size>255</sqlt:size>
215 <sqlt:is_nullable>1</sqlt:is_nullable>
d0c12b9f 216 <sqlt:default_value></sqlt:default_value>
217 <sqlt:is_auto_increment>0</sqlt:is_auto_increment>
3b80481c 218 <sqlt:is_primary_key>0</sqlt:is_primary_key>
d3422086 219 <sqlt:is_foreign_key>0</sqlt:is_foreign_key>
220 <sqlt:comments></sqlt:comments>
d0c12b9f 221 <sqlt:order>8</sqlt:order>
d0c12b9f 222 </sqlt:field>
223 </sqlt:fields>
224 <sqlt:indices>
225 <sqlt:index>
d0c12b9f 226 <sqlt:name>titleindex</sqlt:name>
d0c12b9f 227 <sqlt:type>NORMAL</sqlt:type>
d3422086 228 <sqlt:fields>title</sqlt:fields>
229 <sqlt:options></sqlt:options>
d0c12b9f 230 </sqlt:index>
231 </sqlt:indices>
232 <sqlt:constraints>
233 <sqlt:constraint>
d0c12b9f 234 <sqlt:name></sqlt:name>
d3422086 235 <sqlt:type>PRIMARY KEY</sqlt:type>
236 <sqlt:fields>id</sqlt:fields>
237 <sqlt:reference_table></sqlt:reference_table>
238 <sqlt:reference_fields></sqlt:reference_fields>
d0c12b9f 239 <sqlt:on_delete></sqlt:on_delete>
240 <sqlt:on_update></sqlt:on_update>
d3422086 241 <sqlt:match_type></sqlt:match_type>
242 <sqlt:expression></sqlt:expression>
3b80481c 243 <sqlt:options></sqlt:options>
d3422086 244 <sqlt:deferrable>1</sqlt:deferrable>
d0c12b9f 245 </sqlt:constraint>
246 <sqlt:constraint>
d0c12b9f 247 <sqlt:name></sqlt:name>
d3422086 248 <sqlt:type>UNIQUE</sqlt:type>
249 <sqlt:fields>email</sqlt:fields>
250 <sqlt:reference_table></sqlt:reference_table>
251 <sqlt:reference_fields></sqlt:reference_fields>
d0c12b9f 252 <sqlt:on_delete></sqlt:on_delete>
253 <sqlt:on_update></sqlt:on_update>
d3422086 254 <sqlt:match_type></sqlt:match_type>
255 <sqlt:expression></sqlt:expression>
3b80481c 256 <sqlt:options></sqlt:options>
d3422086 257 <sqlt:deferrable>1</sqlt:deferrable>
d0c12b9f 258 </sqlt:constraint>
259 </sqlt:constraints>
260 </sqlt:table>
261</sqlt:schema>
262EOXML
263
d0c12b9f 264$obj = SQL::Translator->new(
265 debug => DEBUG,
266 trace => TRACE,
267 show_warnings => 1,
268 add_drop_table => 1,
ac62dff1 269 from => 'MySQL',
270 to => 'XML-SQLFairy',
d0c12b9f 271 producer_args => { emit_empty_tags => 1 },
272);
f11724ad 273lives_ok { $xml=$obj->translate($file); } "Translate (emit_empty_tags=>1) ran";
d0c12b9f 274ok("$xml" ne "" ,"Produced something!");
275print "XML emit_empty_tags=>1:\n$xml" if DEBUG;
276# Strip sqlf header with its variable date so we diff safely
277$xml =~ s/^([^\n]*\n){7}//m;
278eq_or_diff $xml, $ans ,"XML looks right";
d0c12b9f 279
1e3867bf 280} # end emit_empty_tags => 1
f11724ad 281
282#
283# attrib_values => 1
284#
1e3867bf 285{
286my ($obj,$ans,$xml);
f11724ad 287
288$ans = <<EOXML;
d3422086 289<sqlt:schema name="" database="" xmlns:sqlt="http://sqlfairy.sourceforge.net/sqlfairy.xml">
446dfcbd 290 <sqlt:table name="Basic" order="3">
f11724ad 291 <sqlt:fields>
d3422086 292 <sqlt:field name="id" data_type="integer" size="10" is_nullable="0" is_auto_increment="1" is_primary_key="1" is_foreign_key="0" comments="comment on id field" order="9" />
293 <sqlt: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" comments="" order="10" />
294 <sqlt: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" comments="" order="11" />
295 <sqlt:field name="email" data_type="varchar" size="255" is_nullable="1" is_auto_increment="0" is_primary_key="0" is_foreign_key="0" comments="" order="12" />
f11724ad 296 </sqlt:fields>
297 <sqlt:indices>
d3422086 298 <sqlt:index name="titleindex" type="NORMAL" fields="title" options="" />
f11724ad 299 </sqlt:indices>
300 <sqlt:constraints>
d3422086 301 <sqlt:constraint name="" type="PRIMARY KEY" fields="id" reference_table="" on_delete="" on_update="" match_type="" expression="" options="" deferrable="1" />
302 <sqlt:constraint name="" type="UNIQUE" fields="email" reference_table="" on_delete="" on_update="" match_type="" expression="" options="" deferrable="1" />
f11724ad 303 </sqlt:constraints>
304 </sqlt:table>
305</sqlt:schema>
306EOXML
307
308$obj = SQL::Translator->new(
309 debug => DEBUG,
310 trace => TRACE,
311 show_warnings => 1,
312 add_drop_table => 1,
313 from => "MySQL",
314 to => "XML-SQLFairy",
315 producer_args => { attrib_values => 1 },
316);
317lives_ok {$xml = $obj->translate($file);} "Translate (attrib_values=>1) ran";
318ok("$xml" ne "" ,"Produced something!");
319print "XML attrib_values=>1:\n$xml" if DEBUG;
320# Strip sqlf header with its variable date so we diff safely
321$xml =~ s/^([^\n]*\n){7}//m;
322eq_or_diff $xml, $ans ,"XML looks right";
323
1e3867bf 324} # end attrib_values => 1
325
326#
327# View
328#
329# Thanks to Ken for the schema setup lifted from 13schema.t
330{
331my ($obj,$ans,$xml);
332
333$ans = <<EOXML;
334<sqlt:schema xmlns:sqlt="http://sqlfairy.sourceforge.net/sqlfairy.xml">
1e3867bf 335 <sqlt:name></sqlt:name>
d3422086 336 <sqlt:database></sqlt:database>
1e3867bf 337 <sqlt:view>
1e3867bf 338 <sqlt:name>foo_view</sqlt:name>
1e3867bf 339 <sqlt:sql>select name, age from person</sqlt:sql>
d3422086 340 <sqlt:fields>name,age</sqlt:fields>
341 <sqlt:order>1</sqlt:order>
1e3867bf 342 </sqlt:view>
343</sqlt:schema>
344EOXML
345
346 $obj = SQL::Translator->new(
347 debug => DEBUG,
348 trace => TRACE,
349 show_warnings => 1,
350 add_drop_table => 1,
351 from => "MySQL",
352 to => "XML-SQLFairy",
353 );
354 my $s = $obj->schema;
355 my $name = 'foo_view';
356 my $sql = 'select name, age from person';
357 my $fields = 'name, age';
358 my $v = $s->add_view(
359 name => $name,
360 sql => $sql,
361 fields => $fields,
362 schema => $s,
363 ) or die $s->error;
364
365 # As we have created a Schema we give translate a dummy string so that
366 # it will run the produce.
367 lives_ok {$xml =$obj->translate("FOO");} "Translate (View) ran";
368 ok("$xml" ne "" ,"Produced something!");
369 print "XML attrib_values=>1:\n$xml" if DEBUG;
370 # Strip sqlf header with its variable date so we diff safely
371 $xml =~ s/^([^\n]*\n){7}//m;
372 eq_or_diff $xml, $ans ,"XML looks right";
373} # end View
374
375#
376# Trigger
377#
378# Thanks to Ken for the schema setup lifted from 13schema.t
379{
380my ($obj,$ans,$xml);
381
382$ans = <<EOXML;
383<sqlt:schema xmlns:sqlt="http://sqlfairy.sourceforge.net/sqlfairy.xml">
1e3867bf 384 <sqlt:name></sqlt:name>
d3422086 385 <sqlt:database></sqlt:database>
1e3867bf 386 <sqlt:trigger>
1e3867bf 387 <sqlt:name>foo_trigger</sqlt:name>
d3422086 388 <sqlt:database_event>insert</sqlt:database_event>
389 <sqlt:action>update modified=timestamp();</sqlt:action>
1e3867bf 390 <sqlt:on_table>foo</sqlt:on_table>
1e3867bf 391 <sqlt:perform_action_when>after</sqlt:perform_action_when>
d3422086 392 <sqlt:order>1</sqlt:order>
1e3867bf 393 </sqlt:trigger>
394</sqlt:schema>
395EOXML
396
397 $obj = SQL::Translator->new(
398 debug => DEBUG,
399 trace => TRACE,
400 show_warnings => 1,
401 add_drop_table => 1,
402 from => "MySQL",
403 to => "XML-SQLFairy",
404 );
405 my $s = $obj->schema;
406 my $name = 'foo_trigger';
407 my $perform_action_when = 'after';
408 my $database_event = 'insert';
409 my $on_table = 'foo';
410 my $action = 'update modified=timestamp();';
411 my $t = $s->add_trigger(
412 name => $name,
413 perform_action_when => $perform_action_when,
414 database_event => $database_event,
415 on_table => $on_table,
416 action => $action,
417 ) or die $s->error;
418
419 # As we have created a Schema we give translate a dummy string so that
420 # it will run the produce.
421 lives_ok {$xml =$obj->translate("FOO");} "Translate (Trigger) ran";
422 ok("$xml" ne "" ,"Produced something!");
423 print "XML attrib_values=>1:\n$xml" if DEBUG;
424 # Strip sqlf header with its variable date so we diff safely
425 $xml =~ s/^([^\n]*\n){7}//m;
426 eq_or_diff $xml, $ans ,"XML looks right";
427} # end Trigger
428
429#
430# Procedure
431#
432# Thanks to Ken for the schema setup lifted from 13schema.t
433{
434my ($obj,$ans,$xml);
435
436$ans = <<EOXML;
437<sqlt:schema xmlns:sqlt="http://sqlfairy.sourceforge.net/sqlfairy.xml">
1e3867bf 438 <sqlt:name></sqlt:name>
d3422086 439 <sqlt:database></sqlt:database>
1e3867bf 440 <sqlt:procedure>
1e3867bf 441 <sqlt:name>foo_proc</sqlt:name>
1e3867bf 442 <sqlt:sql>select foo from bar</sqlt:sql>
d3422086 443 <sqlt:parameters>foo,bar</sqlt:parameters>
444 <sqlt:owner>Nomar</sqlt:owner>
445 <sqlt:comments>Go Sox!</sqlt:comments>
446 <sqlt:order>1</sqlt:order>
1e3867bf 447 </sqlt:procedure>
448</sqlt:schema>
449EOXML
450
451 $obj = SQL::Translator->new(
452 debug => DEBUG,
453 trace => TRACE,
454 show_warnings => 1,
455 add_drop_table => 1,
456 from => "MySQL",
457 to => "XML-SQLFairy",
458 );
459 my $s = $obj->schema;
460 my $name = 'foo_proc';
461 my $sql = 'select foo from bar';
462 my $parameters = 'foo, bar';
463 my $owner = 'Nomar';
464 my $comments = 'Go Sox!';
465 my $p = $s->add_procedure(
466 name => $name,
467 sql => $sql,
468 parameters => $parameters,
469 owner => $owner,
470 comments => $comments,
471 ) or die $s->error;
d3422086 472
1e3867bf 473 # As we have created a Schema we give translate a dummy string so that
474 # it will run the produce.
475 lives_ok {$xml =$obj->translate("FOO");} "Translate (Procedure) ran";
476 ok("$xml" ne "" ,"Produced something!");
477 print "XML attrib_values=>1:\n$xml" if DEBUG;
478 # Strip sqlf header with its variable date so we diff safely
479 $xml =~ s/^([^\n]*\n){7}//m;
480 eq_or_diff $xml, $ans ,"XML looks right";
481} # end Procedure