#!/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 ts_Basic_timest BEFORE INSERT OR UPDATE ON Basic FOR EACH ROW WHEN (new.timest IS NULL) BEGIN SELECT sysdate INTO :new.timest 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 ts_Basic_timest01 BEFORE INSERT OR UPDATE ON Basic FOR EACH ROW WHEN (new.timest IS NULL) BEGIN SELECT sysdate INTO :new.timest 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; / |);