#!/usr/bin/perl
use strict;
use FindBin qw/$Bin/;
use Test::More;
use Test::SQL::Translator;
use Test::Exception;
use Test::Differences;
use Data::Dumper;
use SQL::Translator;
use SQL::Translator::Schema::Constants;
BEGIN {
  maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy', 'SQL::Translator::Producer::Oracle');
}
my $xmlfile = "$Bin/data/xml/schema.xml";
my $sqlt;
$sqlt = SQL::Translator->new(
  no_comments       => 1,
  quote_table_names => 0,
  quote_field_names => 0,
  show_warnings     => 0,
  add_drop_table    => 1,
);
die "Can't find test schema $xmlfile" unless -e $xmlfile;
my @sql = $sqlt->translate(
  from     => 'XML-SQLFairy',
  to       => 'Oracle',
  filename => $xmlfile,
) or die $sqlt->error;
my $sql_string = $sqlt->translate(
  from     => 'XML-SQLFairy',
  to       => 'Oracle',
  filename => $xmlfile,
) or die $sqlt->error;
my $want = [
  'DROP TABLE Basic CASCADE CONSTRAINTS',
  'DROP SEQUENCE sq_Basic_id',
  'CREATE SEQUENCE sq_Basic_id',
  'CREATE TABLE Basic (
  id number(10) NOT NULL,
  title varchar2(100) DEFAULT \'hello\' NOT NULL,
  description clob DEFAULT \'\',
  email varchar2(500),
  explicitnulldef varchar2(4000),
  explicitemptystring varchar2(4000) DEFAULT \'\',
  emptytagdef varchar2(4000) DEFAULT \'\',
  another_id number(10) DEFAULT \'2\',
  timest date,
  PRIMARY KEY (id),
  CONSTRAINT u_Basic_emailuniqueindex UNIQUE (email),
  CONSTRAINT u_Basic_very_long_index_name_o UNIQUE (title)
)',
  'DROP TABLE Another CASCADE CONSTRAINTS',
  'DROP SEQUENCE sq_Another_id',
  'CREATE SEQUENCE sq_Another_id',
  'CREATE TABLE Another (
  id number(10) NOT NULL,
  num number(10,2),
  PRIMARY KEY (id)
)',
  'DROP VIEW email_list',
  'CREATE VIEW email_list AS
SELECT email FROM Basic WHERE (email IS NOT NULL)',
  'ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk FOREIGN KEY (another_id) REFERENCES Another (id)',
  'CREATE OR REPLACE TRIGGER ai_Basic_id
BEFORE INSERT ON Basic
FOR EACH ROW WHEN (
 new.id IS NULL OR new.id = 0
)
BEGIN
 SELECT sq_Basic_id.nextval
 INTO :new.id
 FROM dual;
END;
',
  'CREATE OR REPLACE TRIGGER ai_Another_id
BEFORE INSERT ON Another
FOR EACH ROW WHEN (
 new.id IS NULL OR new.id = 0
)
BEGIN
 SELECT sq_Another_id.nextval
 INTO :new.id
 FROM dual;
END;
',
  'CREATE INDEX titleindex ON Basic (title)'
];
is_deeply(\@sql, $want, 'Got correct Oracle statements in list context');
eq_or_diff(
  $sql_string, q|DROP TABLE Basic CASCADE CONSTRAINTS;
DROP SEQUENCE sq_Basic_id01;
CREATE SEQUENCE sq_Basic_id01;
CREATE TABLE Basic (
  id number(10) NOT NULL,
  title varchar2(100) DEFAULT 'hello' NOT NULL,
  description clob DEFAULT '',
  email varchar2(500),
  explicitnulldef varchar2(4000),
  explicitemptystring varchar2(4000) DEFAULT '',
  emptytagdef varchar2(4000) DEFAULT '',
  another_id number(10) DEFAULT '2',
  timest date,
  PRIMARY KEY (id),
  CONSTRAINT u_Basic_emailuniqueindex01 UNIQUE (email),
  CONSTRAINT u_Basic_very_long_index_name01 UNIQUE (title)
);
DROP TABLE Another CASCADE CONSTRAINTS;
DROP SEQUENCE sq_Another_id01;
CREATE SEQUENCE sq_Another_id01;
CREATE TABLE Another (
  id number(10) NOT NULL,
  num number(10,2),
  PRIMARY KEY (id)
);
DROP VIEW email_list;
CREATE VIEW email_list AS
SELECT email FROM Basic WHERE (email IS NOT NULL);
ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk01 FOREIGN KEY (another_id) REFERENCES Another (id);
CREATE INDEX titleindex01 ON Basic (title);
CREATE OR REPLACE TRIGGER ai_Basic_id01
BEFORE INSERT ON Basic
FOR EACH ROW WHEN (
 new.id IS NULL OR new.id = 0
)
BEGIN
 SELECT sq_Basic_id01.nextval
 INTO :new.id
 FROM dual;
END;
/
CREATE OR REPLACE TRIGGER ai_Another_id01
BEFORE INSERT ON Another
FOR EACH ROW WHEN (
 new.id IS NULL OR new.id = 0
)
BEGIN
 SELECT sq_Another_id01.nextval
 INTO :new.id
 FROM dual;
END;
/
|
);