[dbsrgits/SQL-Translator.git] / t / 51-xml-to-oracle.t

#!/usr/bin/perl
use strict;

use FindBin qw/$Bin/;
use Test::More;
use Test::SQL::Translator;
use Test::Exception;
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,
    show_warnings  => 1,
    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_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(255),
  explicitnulldef varchar2,
  explicitemptystring varchar2 DEFAULT \'\',
  emptytagdef varchar2 DEFAULT \'\',
  another_id number(10) DEFAULT \'2\',
  timest date,
  PRIMARY KEY (id),
  CONSTRAINT emailuniqueindex UNIQUE (email)
);',
          'DROP TABLE Another CASCADE CONSTRAINTS;',
          'DROP SEQUENCE sq_Another_id01;',
          'CREATE SEQUENCE sq_Another_id01;',
          'CREATE TABLE Another (
  id number(10) NOT NULL,
  PRIMARY KEY (id)
);',
          '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 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;
/'];

is_deeply(\@sql, $want, 'Got correct Oracle statements in list context');

is($sql_string, q|DROP TABLE Basic CASCADE CONSTRAINTS;

DROP SEQUENCE sq_Basic_id02;

CREATE SEQUENCE sq_Basic_id02;

CREATE TABLE Basic (
  id number(10) NOT NULL,
  title varchar2(100) DEFAULT 'hello' NOT NULL,
  description clob DEFAULT '',
  email varchar2(255),
  explicitnulldef varchar2,
  explicitemptystring varchar2 DEFAULT '',
  emptytagdef varchar2 DEFAULT '',
  another_id number(10) DEFAULT '2',
  timest date,
  PRIMARY KEY (id),
  CONSTRAINT emailuniqueindex UNIQUE (email)
);

DROP TABLE Another CASCADE CONSTRAINTS;

DROP SEQUENCE sq_Another_id02;

CREATE SEQUENCE sq_Another_id02;

CREATE TABLE Another (
  id number(10) NOT NULL,
  PRIMARY KEY (id)
);

CREATE VIEW email_list AS
SELECT email FROM Basic WHERE email IS NOT NULL;

ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk02 FOREIGN KEY (another_id) REFERENCES Another (id);

CREATE OR REPLACE TRIGGER ai_Basic_id02
BEFORE INSERT ON Basic
FOR EACH ROW WHEN (
 new.id IS NULL OR new.id = 0
)
BEGIN
 SELECT sq_Basic_id02.nextval
 INTO :new.id
 FROM dual;
END;
/

CREATE OR REPLACE TRIGGER ts_Basic_timest02
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_id02
BEFORE INSERT ON Another
FOR EACH ROW WHEN (
 new.id IS NULL OR new.id = 0
)
BEGIN
 SELECT sq_Another_id02.nextval
 INTO :new.id
 FROM dual;
END;
/|);
#!/usr/bin/perl
use strict;

use FindBin qw/$Bin/;
use Test::More;
use Test::SQL::Translator;
use Test::Exception;
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,
    show_warnings  => 1,
    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_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(255),
  explicitnulldef varchar2,
  explicitemptystring varchar2 DEFAULT \'\',
  emptytagdef varchar2 DEFAULT \'\',
  another_id number(10) DEFAULT \'2\',
  timest date,
  PRIMARY KEY (id),
  CONSTRAINT emailuniqueindex UNIQUE (email)
);',
          'DROP TABLE Another CASCADE CONSTRAINTS;',
          'DROP SEQUENCE sq_Another_id01;',
          'CREATE SEQUENCE sq_Another_id01;',
          'CREATE TABLE Another (
  id number(10) NOT NULL,
  PRIMARY KEY (id)
);',
          '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 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;
/'];

is_deeply(\@sql, $want, 'Got correct Oracle statements in list context');

is($sql_string, q|DROP TABLE Basic CASCADE CONSTRAINTS;

DROP SEQUENCE sq_Basic_id02;

CREATE SEQUENCE sq_Basic_id02;

CREATE TABLE Basic (
  id number(10) NOT NULL,
  title varchar2(100) DEFAULT 'hello' NOT NULL,
  description clob DEFAULT '',
  email varchar2(255),
  explicitnulldef varchar2,
  explicitemptystring varchar2 DEFAULT '',
  emptytagdef varchar2 DEFAULT '',
  another_id number(10) DEFAULT '2',
  timest date,
  PRIMARY KEY (id),
  CONSTRAINT emailuniqueindex UNIQUE (email)
);

DROP TABLE Another CASCADE CONSTRAINTS;

DROP SEQUENCE sq_Another_id02;

CREATE SEQUENCE sq_Another_id02;

CREATE TABLE Another (
  id number(10) NOT NULL,
  PRIMARY KEY (id)
);

CREATE VIEW email_list AS
SELECT email FROM Basic WHERE email IS NOT NULL;

ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk02 FOREIGN KEY (another_id) REFERENCES Another (id);

CREATE OR REPLACE TRIGGER ai_Basic_id02
BEFORE INSERT ON Basic
FOR EACH ROW WHEN (
 new.id IS NULL OR new.id = 0
)
BEGIN
 SELECT sq_Basic_id02.nextval
 INTO :new.id
 FROM dual;
END;
/

CREATE OR REPLACE TRIGGER ts_Basic_timest02
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_id02
BEFORE INSERT ON Another
FOR EACH ROW WHEN (
 new.id IS NULL OR new.id = 0
)
BEGIN
 SELECT sq_Another_id02.nextval
 INTO :new.id
 FROM dual;
END;
/|);
Commit	Line	Data
e56dabb7	1	#!/usr/bin/perl
	2	use strict;
	3
	4	use FindBin qw/$Bin/;
	5	use Test::More;
	6	use Test::SQL::Translator;
	7	use Test::Exception;
	8	use Data::Dumper;
	9	use SQL::Translator;
	10	use SQL::Translator::Schema::Constants;
	11
	12	BEGIN {
	13	maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy',
	14	'SQL::Translator::Producer::Oracle');
	15	}
	16
	17	my $xmlfile = "$Bin/data/xml/schema.xml";
	18
	19	my $sqlt;
	20	$sqlt = SQL::Translator->new(
	21	no_comments => 1,
	22	show_warnings => 1,
	23	add_drop_table => 1,
	24	);
	25
	26	die "Can't find test schema $xmlfile" unless -e $xmlfile;
	27
	28	my @sql = $sqlt->translate(
	29	from => 'XML-SQLFairy',
	30	to => 'Oracle',
	31	filename => $xmlfile,
	32	) or die $sqlt->error;
	33
	34	my $sql_string = $sqlt->translate(
	35	from => 'XML-SQLFairy',
	36	to => 'Oracle',
	37	filename => $xmlfile,
	38	) or die $sqlt->error;
	39
	40	my $want = [
	41	'DROP TABLE Basic CASCADE CONSTRAINTS;',
	42	'DROP SEQUENCE sq_Basic_id01;',
	43	'CREATE SEQUENCE sq_Basic_id01;',
	44	'CREATE TABLE Basic (
	45	id number(10) NOT NULL,
	46	title varchar2(100) DEFAULT \'hello\' NOT NULL,
	47	description clob DEFAULT \'\',
	48	email varchar2(255),
	49	explicitnulldef varchar2,
	50	explicitemptystring varchar2 DEFAULT \'\',
	51	emptytagdef varchar2 DEFAULT \'\',
	52	another_id number(10) DEFAULT \'2\',
	53	timest date,
	54	PRIMARY KEY (id),
	55	CONSTRAINT emailuniqueindex UNIQUE (email)
	56	);',
	57	'DROP TABLE Another CASCADE CONSTRAINTS;',
	58	'DROP SEQUENCE sq_Another_id01;',
	59	'CREATE SEQUENCE sq_Another_id01;',
	60	'CREATE TABLE Another (
	61	id number(10) NOT NULL,
	62	PRIMARY KEY (id)
	63	);',
	64	'CREATE VIEW email_list AS
65	SELECT email FROM Basic WHERE email IS NOT NULL;',
66	'ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk01 FOREIGN KEY (another_id) REFERENCES Another (id);',
67	'CREATE OR REPLACE TRIGGER ai_Basic_id01
68	BEFORE INSERT ON Basic
69	FOR EACH ROW WHEN (
70	new.id IS NULL OR new.id = 0
71	)
72	BEGIN
73	SELECT sq_Basic_id01.nextval
74	INTO :new.id
75	FROM dual;
76	END;
77	/',
78	'CREATE OR REPLACE TRIGGER ts_Basic_timest01
79	BEFORE INSERT OR UPDATE ON Basic
80	FOR EACH ROW WHEN (new.timest IS NULL)
81	BEGIN
82	SELECT sysdate INTO :new.timest FROM dual;
83	END;
84	/',
85	'CREATE OR REPLACE TRIGGER ai_Another_id01
86	BEFORE INSERT ON Another
87	FOR EACH ROW WHEN (
88	new.id IS NULL OR new.id = 0
89	)
90	BEGIN
91	SELECT sq_Another_id01.nextval
92	INTO :new.id
93	FROM dual;
94	END;
95	/'];
96
97	is_deeply(\@sql, $want, 'Got correct Oracle statements in list context');
98
99	is($sql_string, q\|DROP TABLE Basic CASCADE CONSTRAINTS;
100
101	DROP SEQUENCE sq_Basic_id02;
102
103	CREATE SEQUENCE sq_Basic_id02;
104
105	CREATE TABLE Basic (
106	id number(10) NOT NULL,
107	title varchar2(100) DEFAULT 'hello' NOT NULL,
108	description clob DEFAULT '',
109	email varchar2(255),
110	explicitnulldef varchar2,
111	explicitemptystring varchar2 DEFAULT '',
112	emptytagdef varchar2 DEFAULT '',
113	another_id number(10) DEFAULT '2',
114	timest date,
115	PRIMARY KEY (id),
116	CONSTRAINT emailuniqueindex UNIQUE (email)
117	);
118
119	DROP TABLE Another CASCADE CONSTRAINTS;
120
121	DROP SEQUENCE sq_Another_id02;
122
123	CREATE SEQUENCE sq_Another_id02;
124
125	CREATE TABLE Another (
126	id number(10) NOT NULL,
127	PRIMARY KEY (id)
128	);
129
130	CREATE VIEW email_list AS
131	SELECT email FROM Basic WHERE email IS NOT NULL;
132
133	ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk02 FOREIGN KEY (another_id) REFERENCES Another (id);
134
135	CREATE OR REPLACE TRIGGER ai_Basic_id02
136	BEFORE INSERT ON Basic
137	FOR EACH ROW WHEN (
138	new.id IS NULL OR new.id = 0
139	)
140	BEGIN
141	SELECT sq_Basic_id02.nextval
142	INTO :new.id
143	FROM dual;
144	END;
145	/
146
147	CREATE OR REPLACE TRIGGER ts_Basic_timest02
148	BEFORE INSERT OR UPDATE ON Basic
149	FOR EACH ROW WHEN (new.timest IS NULL)
150	BEGIN
151	SELECT sysdate INTO :new.timest FROM dual;
152	END;
153	/
154
155	CREATE OR REPLACE TRIGGER ai_Another_id02
156	BEFORE INSERT ON Another
157	FOR EACH ROW WHEN (
158	new.id IS NULL OR new.id = 0
159	)
160	BEGIN
161	SELECT sq_Another_id02.nextval
162	INTO :new.id
163	FROM dual;
164	END;
165	/\|);
166	#!/usr/bin/perl
167	use strict;
168
169	use FindBin qw/$Bin/;
170	use Test::More;
171	use Test::SQL::Translator;
172	use Test::Exception;
173	use Data::Dumper;
174	use SQL::Translator;
175	use SQL::Translator::Schema::Constants;
176
177	BEGIN {
178	maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy',
179	'SQL::Translator::Producer::Oracle');
180	}
181
182	my $xmlfile = "$Bin/data/xml/schema.xml";
183
184	my $sqlt;
185	$sqlt = SQL::Translator->new(
186	no_comments => 1,
187	show_warnings => 1,
188	add_drop_table => 1,
189	);
190
191	die "Can't find test schema $xmlfile" unless -e $xmlfile;
192
193	my @sql = $sqlt->translate(
194	from => 'XML-SQLFairy',
195	to => 'Oracle',
196	filename => $xmlfile,
197	) or die $sqlt->error;
198
199	my $sql_string = $sqlt->translate(
200	from => 'XML-SQLFairy',
201	to => 'Oracle',
202	filename => $xmlfile,
203	) or die $sqlt->error;
204
205	my $want = [
206	'DROP TABLE Basic CASCADE CONSTRAINTS;',
207	'DROP SEQUENCE sq_Basic_id01;',
208	'CREATE SEQUENCE sq_Basic_id01;',
209	'CREATE TABLE Basic (
210	id number(10) NOT NULL,
211	title varchar2(100) DEFAULT \'hello\' NOT NULL,
212	description clob DEFAULT \'\',
213	email varchar2(255),
214	explicitnulldef varchar2,
215	explicitemptystring varchar2 DEFAULT \'\',
216	emptytagdef varchar2 DEFAULT \'\',
217	another_id number(10) DEFAULT \'2\',
218	timest date,
219	PRIMARY KEY (id),
220	CONSTRAINT emailuniqueindex UNIQUE (email)
221	);',
222	'DROP TABLE Another CASCADE CONSTRAINTS;',
223	'DROP SEQUENCE sq_Another_id01;',
224	'CREATE SEQUENCE sq_Another_id01;',
225	'CREATE TABLE Another (
226	id number(10) NOT NULL,
227	PRIMARY KEY (id)
228	);',
229	'CREATE VIEW email_list AS
230	SELECT email FROM Basic WHERE email IS NOT NULL;',
231	'ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk01 FOREIGN KEY (another_id) REFERENCES Another (id);',
232	'CREATE OR REPLACE TRIGGER ai_Basic_id01
233	BEFORE INSERT ON Basic
234	FOR EACH ROW WHEN (
235	new.id IS NULL OR new.id = 0
236	)
237	BEGIN
238	SELECT sq_Basic_id01.nextval
239	INTO :new.id
240	FROM dual;
241	END;
242	/',
243	'CREATE OR REPLACE TRIGGER ts_Basic_timest01
244	BEFORE INSERT OR UPDATE ON Basic
245	FOR EACH ROW WHEN (new.timest IS NULL)
246	BEGIN
247	SELECT sysdate INTO :new.timest FROM dual;
248	END;
249	/',
250	'CREATE OR REPLACE TRIGGER ai_Another_id01
251	BEFORE INSERT ON Another
252	FOR EACH ROW WHEN (
253	new.id IS NULL OR new.id = 0
254	)
255	BEGIN
256	SELECT sq_Another_id01.nextval
257	INTO :new.id
258	FROM dual;
259	END;
260	/'];
261
262	is_deeply(\@sql, $want, 'Got correct Oracle statements in list context');
263
264	is($sql_string, q\|DROP TABLE Basic CASCADE CONSTRAINTS;
265
266	DROP SEQUENCE sq_Basic_id02;
267
268	CREATE SEQUENCE sq_Basic_id02;
269
270	CREATE TABLE Basic (
271	id number(10) NOT NULL,
272	title varchar2(100) DEFAULT 'hello' NOT NULL,
273	description clob DEFAULT '',
274	email varchar2(255),
275	explicitnulldef varchar2,
276	explicitemptystring varchar2 DEFAULT '',
277	emptytagdef varchar2 DEFAULT '',
278	another_id number(10) DEFAULT '2',
279	timest date,
280	PRIMARY KEY (id),
281	CONSTRAINT emailuniqueindex UNIQUE (email)
282	);
283
284	DROP TABLE Another CASCADE CONSTRAINTS;
285
286	DROP SEQUENCE sq_Another_id02;
287
288	CREATE SEQUENCE sq_Another_id02;
289
290	CREATE TABLE Another (
291	id number(10) NOT NULL,
292	PRIMARY KEY (id)
293	);
294
295	CREATE VIEW email_list AS
296	SELECT email FROM Basic WHERE email IS NOT NULL;
297
298	ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk02 FOREIGN KEY (another_id) REFERENCES Another (id);
299
300	CREATE OR REPLACE TRIGGER ai_Basic_id02
301	BEFORE INSERT ON Basic
302	FOR EACH ROW WHEN (
303	new.id IS NULL OR new.id = 0
304	)
305	BEGIN
306	SELECT sq_Basic_id02.nextval
307	INTO :new.id
308	FROM dual;
309	END;
310	/
311
312	CREATE OR REPLACE TRIGGER ts_Basic_timest02
313	BEFORE INSERT OR UPDATE ON Basic
314	FOR EACH ROW WHEN (new.timest IS NULL)
315	BEGIN
316	SELECT sysdate INTO :new.timest FROM dual;
317	END;
318	/
319
320	CREATE OR REPLACE TRIGGER ai_Another_id02
321	BEFORE INSERT ON Another
322	FOR EACH ROW WHEN (
323	new.id IS NULL OR new.id = 0
324	)
325	BEGIN
326	SELECT sq_Another_id02.nextval
327	INTO :new.id
328	FROM dual;
329	END;
330	/\|);