Fix SQLite producer create_view so it doesn't generate statements with semicolons.
[dbsrgits/SQL-Translator.git] / t / 43xml-to-db2.t
CommitLineData
0209d0a0 1#!/usr/bin/perl
2use strict;
3
4use FindBin qw/$Bin/;
5use Test::More;
6use Test::SQL::Translator;
c16711cb 7use Test::Differences;
0209d0a0 8use Test::Exception;
9use Data::Dumper;
10use SQL::Translator;
11use SQL::Translator::Schema::Constants;
12
0209d0a0 13BEGIN {
14 maybe_plan(1, 'SQL::Translator::Parser::XML::SQLFairy',
15 'SQL::Translator::Producer::DB2');
16}
17
18my $xmlfile = "$Bin/data/xml/schema.xml";
19
20my $sqlt;
21$sqlt = SQL::Translator->new(
22 no_comments => 1,
9768b204 23 show_warnings => 0,
0209d0a0 24 add_drop_table => 1,
25);
26
27die "Can't find test schema $xmlfile" unless -e $xmlfile;
28
29my $sql = $sqlt->translate(
30 from => 'XML-SQLFairy',
31 to => 'DB2',
32 filename => $xmlfile,
33) or die $sqlt->error;
34
c16711cb 35eq_or_diff($sql, << "SQL");
0209d0a0 36DROP TABLE Basic;
37
5e2c196a 38CREATE TABLE Basic (
b08b5416 39 id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
40 title VARCHAR(100) NOT NULL DEFAULT 'hello',
41 description VARCHAR(0) DEFAULT '',
1022b157 42 email VARCHAR(500),
b08b5416 43 explicitnulldef VARCHAR(0),
44 explicitemptystring VARCHAR(0) DEFAULT '',
45 emptytagdef VARCHAR(0) DEFAULT '',
46 another_id INTEGER DEFAULT 2,
47 timest TIMESTAMP,
48 PRIMARY KEY (id),
49 CONSTRAINT emailuniqueindex UNIQUE (email)
0209d0a0 50);
51
b08b5416 52DROP TABLE Another;
53
54CREATE TABLE Another (
55 id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
5ca2365a 56 num NUMERIC(10,2),
b08b5416 57 PRIMARY KEY (id)
58);
59
60ALTER TABLE Basic ADD FOREIGN KEY (another_id) REFERENCES Another(id);
61
0209d0a0 62CREATE INDEX titleindex ON Basic ( title );
63
64CREATE VIEW email_list AS
3910f248 65SELECT email FROM Basic WHERE (email IS NOT NULL);
0209d0a0 66
67CREATE TRIGGER foo_trigger after insert ON Basic REFERENCING OLD AS oldrow NEW AS newrow FOR EACH ROW MODE DB2SQL update modified=timestamp();
bc2ed06a 68
69CREATE TRIGGER bar_trigger before insert, update ON Basic REFERENCING OLD AS oldrow NEW AS newrow FOR EACH ROW MODE DB2SQL update modified2=timestamp();
0209d0a0 70SQL