[dbsrgits/SQL-Translator.git] / t / 28xml-xmi-parser-sqlfairy.t

#!/usr/bin/perl -w
# vim:filetype=perl

# Before `make install' is performed this script should be runnable with
# `make test'. After `make install' it should work as `perl test.pl'

use strict;
use FindBin qw/$Bin/;
use Data::Dumper;

# run with -d for debug
my %opt;
BEGIN { map { $opt{$_}=1 if s/^-// } @ARGV; }
use constant DEBUG => (exists $opt{d} ? 1 : 0);

use Test::More;
use Test::Exception;
use SQL::Translator;
use SQL::Translator::Schema::Constants;

# Usefull test subs for the schema objs
#=============================================================================

sub test_field {
    my ($f1,$test) = @_;
    unless ($f1) {
        fail " Field '$test->{name}' doesn't exist!";
        # TODO Do a skip on the following tests
        return;
    }

	is( $f1->name, $test->{name}, "  Field name '$test->{name}'" );

	is( $f1->data_type, $test->{data_type}, "    Type is '$test->{data_type}'" )
	if exists $test->{data_type};

	is( $f1->size, $test->{size}, "    Size is '$test->{size}'" )
	if exists $test->{size};

	is( $f1->default_value, $test->{default_value},
	"    Default value is ".(defined($test->{default_value}) ? "'$test->{default_value}'" : "UNDEF" ) )
	if exists $test->{default_value};

	is( $f1->is_nullable, $test->{is_nullable},
		"    ".($test->{is_nullable} ? 'can' : 'cannot').' be null' )
	if exists $test->{is_nullable};

	is( $f1->is_unique, $test->{is_unique},
		"    ".($test->{is_unique} ? 'can' : 'cannot').' be unique' )
	if exists $test->{is_unique};

	is( $f1->is_primary_key, $test->{is_primary_key},
		"    is ".($test->{is_primary_key} ? '' : 'not').' a primary_key' )
	if exists $test->{is_primary_key};

	is( $f1->is_foreign_key, $test->{is_foreign_key},
		"    is ".($test->{is_foreign_key} ? '' : 'not').' a foreign_key' )
	if exists $test->{is_foreign_key};

	is( $f1->is_auto_increment, $test->{is_auto_increment},
	"    is ".($test->{is_auto_increment} ?  '' : 'not').' an auto_increment' )
	if exists $test->{is_auto_increment};
}

sub constraint_ok {
    my ($con,$test) = @_;
	#$test->{name} ||= "<anon>";

	if ( exists $test->{name} ) {
		is( $con->name, $test->{name}, "  Constraint '$test->{name}'" );
	}
	else {
		ok( $con, "  Constraint" );
	}

	is( $con->type, $test->{type}, "    type is '$test->{type}'" )
	if exists $test->{type};

	is( $con->table->name, $test->{table}, "    table is '$test->{table}'" )
	if exists $test->{table};

	is( join(",",$con->fields), $test->{fields},
	"    fields is '$test->{fields}'" )
	if exists $test->{fields};

	is( $con->reference_table, $test->{reference_table},
	"    reference_table is '$test->{reference_table}'" )
	if exists $test->{reference_table};

	is( join(",",$con->reference_fields), $test->{reference_fields},
	"    reference_fields is '$test->{reference_fields}'" )
	if exists $test->{reference_fields};

	is( $con->match_type, $test->{match_type},
	"    match_type is '$test->{match_type}'" )
	if exists $test->{match_type};

	is( $con->on_delete_do, $test->{on_delete_do},
	"    on_delete_do is '$test->{on_delete_do}'" )
	if exists $test->{on_delete_do};

	is( $con->on_update_do, $test->{on_update_do},
	"    on_update_do is '$test->{on_update_do}'" )
	if exists $test->{on_update_do};
}

sub test_table {
    my $tbl = shift;
    my %arg = @_;
	$arg{constraints} ||= [];
    my $name = $arg{name} || die "Need a table name to test.";

	my @fldnames = map { $_->{name} } @{$arg{fields}};
	is_deeply( [ map {$_->name}   $tbl->get_fields ],
               [ map {$_->{name}} @{$arg{fields}} ],
               "Table $name\'s fields" );
    foreach ( @{$arg{fields}} ) {
        my $name = $_->{name} || die "Need a field name to test.";
        test_field( $tbl->get_field($name), $_ );
    }

	if ( my @tcons = @{$arg{constraints}} ) {
		my @cons = $tbl->get_constraints;
		is(scalar(@cons), scalar(@tcons),
		"Table $name has ".scalar(@tcons)." Constraints");
		foreach ( @cons ) {
			my $ans = { table => $tbl->name, %{shift @tcons}};
			constraint_ok( $_, $ans  );
		}
	}
}

# Testing 1,2,3,..
#=============================================================================

plan tests => 151;

my $testschema = "$Bin/data/xmi/OrderDB.sqlfairy.poseidon2.xmi";
die "Can't find test schema $testschema" unless -e $testschema;

my $obj;
$obj = SQL::Translator->new(
    filename => $testschema,
    from     => 'XML-XMI-SQLFairy',
    to       => 'MySQL',
    debug          => DEBUG,
    show_warnings  => 1,
);
my $sql = $obj->translate;
ok( $sql, "Got some SQL");
print $sql if DEBUG;


#
# Test the schema
#
my $scma = $obj->schema;
is( $scma->is_valid, 1, 'Schema is valid' );
my @tblnames = map {$_->name} $scma->get_tables;
is(scalar(@{$scma->get_tables}), scalar(@tblnames), "Right number of tables");
is_deeply( \@tblnames, 
    [qw/Order OrderLine Customer ContactDetails ContactDetails_Customer/]
,"tables");

test_table( $scma->get_table("Customer"),
    name => "Customer",
    fields => [
    {
        name => "name",
        data_type => "VARCHAR",
		size => 255,
        default_value => undef,
        is_nullable => 0,
        is_primary_key => 0,
    },
    {
        name => "email",
        data_type => "VARCHAR",
		size => 255,
        default_value => undef,
        is_nullable => 1,
        is_primary_key => 0,
    },
    {
        name => "CustomerID",
        data_type => "INT",
		size => 10,
        default_value => undef,
        is_nullable => 0,
        is_primary_key => 1,
    },
    ],
	constraints => [
		{
			type => "PRIMARY KEY",
			fields => "CustomerID",
		},
        #{
		#	name => "UniqueEmail",
		#	type => "UNIQUE",
		#	fields => "email",
		#},
	],
);

test_table( $scma->get_table("ContactDetails_Customer"),
    name => "ContactDetails_Customer",
    fields => [
    {
        name => "ContactDetailsID",
        data_type => "INT",
		size => 10,
        default_value => undef,
        is_nullable => 0,
        is_primary_key => 1,
        is_auto_increment => 0,
    },
    {
        name => "CustomerID",
        data_type => "INT",
		size => 10,
        default_value => undef,
        is_nullable => 0,
        is_primary_key => 1,
        is_auto_increment => 0,
    },
    ],
	constraints => [
		{
			type => "FOREIGN KEY",
			fields => "ContactDetailsID",
			reference_table => "ContactDetails",
			reference_fields => "ContactDetailsID",
		},
		{
			type => "FOREIGN KEY",
			fields => "CustomerID",
			reference_table => "Customer",
			reference_fields => "CustomerID",
		},
		{
			type => "PRIMARY KEY",
			fields => "ContactDetailsID,CustomerID",
		},
	],
);

test_table( $scma->get_table("ContactDetails"),
    name => "ContactDetails",
    fields => [
    {
        name => "address",
        data_type => "VARCHAR",
        size => "255",
        default_value => undef,
        is_nullable => 1,
        is_primary_key => 0,
    },
    {
        name => "telephone",
        data_type => "VARCHAR",
        size => "255",
        default_value => undef,
        is_nullable => 1,
        is_primary_key => 0,
    },
    {
        name => "ContactDetailsID",
        data_type => "INT",
		size => 10,
        default_value => undef,
        is_nullable => 0,
        is_primary_key => 1,
        is_auto_increment => 1,
    },
    ],
	constraints => [
		{
			type => "PRIMARY KEY",
			fields => "ContactDetailsID",
		},
	],
);

test_table( $scma->get_table("Order"),
    name => "Order",
    fields => [
    {
        name => "invoiceNumber",
        data_type => "INT",
		size => 10,
        default_value => undef,
        is_nullable => 0,
        is_primary_key => 1,
    },
    {
        name => "orderDate",
        data_type => "DATE",
        default_value => undef,
        is_nullable => 0,
        is_primary_key => 0,
    },
    {
        name => "CustomerID",
        data_type => "INT",
		size => 10,
        default_value => undef,
        is_nullable => 0,
        is_primary_key => 0,
        is_foreign_key => 1,
    },
    ],
	constraints => [
		{
			type => "PRIMARY KEY",
			fields => "invoiceNumber",
		},
		{
			type => "FOREIGN KEY",
			fields => "CustomerID",
			reference_table => "Customer",
			reference_fields => "CustomerID",
		},
	],
	# TODO
	#indexes => [
	#	{
	#		name => "idxOrderDate",
	#		type => "INDEX",
	#		fields => "orderDate",
	#	},
	#],
);


test_table( $scma->get_table("OrderLine"),
    name => "OrderLine",
    fields => [
    {
        name => "lineNumber",
        data_type => "INT",
		size => 255,
        default_value => 1,
        is_nullable => 0,
        is_primary_key => 0,
    },
    {
        name => "quantity",
        data_type => "INT",
		size => 255,
        default_value => 1,
        is_nullable => 0,
        is_primary_key => 0,
    },
    {
        name => "OrderLineID",
        data_type => "INT",
		size => 10,
        default_value => undef,
        is_nullable => 0,
        is_primary_key => 1,
    },
    {
        name => "invoiceNumber",
        data_type => "INT",
		size => 10,
        default_value => undef,
        is_nullable => 1,
        is_primary_key => 1,
    },
    ],
	constraints => [
		{
			type => "PRIMARY KEY",
			fields => "OrderLineID,invoiceNumber",
		},
		{
			type => "FOREIGN KEY",
			fields => "invoiceNumber",
			reference_table => "Order",
			reference_fields => "invoiceNumber",
		},
	],
);
Commit	Line	Data
f992cfaa	1	#!/usr/bin/perl -w
55e462b4	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
	7	use strict;
	8	use FindBin qw/$Bin/;
	9	use Data::Dumper;
	10
	11	# run with -d for debug
	12	my %opt;
	13	BEGIN { map { $opt{$_}=1 if s/^-// } @ARGV; }
	14	use constant DEBUG => (exists $opt{d} ? 1 : 0);
	15
	16	use Test::More;
	17	use Test::Exception;
	18	use SQL::Translator;
	19	use SQL::Translator::Schema::Constants;
	20
	21	# Usefull test subs for the schema objs
	22	#=============================================================================
	23
	24	sub test_field {
	25	my ($f1,$test) = @_;
	26	unless ($f1) {
	27	fail " Field '$test->{name}' doesn't exist!";
	28	# TODO Do a skip on the following tests
	29	return;
	30	}
	31
	32	is( $f1->name, $test->{name}, " Field name '$test->{name}'" );
f992cfaa	33
55e462b4	34	is( $f1->data_type, $test->{data_type}, " Type is '$test->{data_type}'" )
55e462b4	35	if exists $test->{data_type};
f992cfaa	36
55e462b4	37	is( $f1->size, $test->{size}, " Size is '$test->{size}'" )
55e462b4	38	if exists $test->{size};
f992cfaa	39
55e462b4	40	is( $f1->default_value, $test->{default_value},
	41	" Default value is ".(defined($test->{default_value}) ? "'$test->{default_value}'" : "UNDEF" ) )
	42	if exists $test->{default_value};
f992cfaa	43
f992cfaa	44	is( $f1->is_nullable, $test->{is_nullable},
55e462b4	45	" ".($test->{is_nullable} ? 'can' : 'cannot').' be null' )
55e462b4	46	if exists $test->{is_nullable};
f992cfaa	47
f992cfaa	48	is( $f1->is_unique, $test->{is_unique},
55e462b4	49	" ".($test->{is_unique} ? 'can' : 'cannot').' be unique' )
55e462b4	50	if exists $test->{is_unique};
f992cfaa	51
f992cfaa	52	is( $f1->is_primary_key, $test->{is_primary_key},
55e462b4	53	" is ".($test->{is_primary_key} ? '' : 'not').' a primary_key' )
55e462b4	54	if exists $test->{is_primary_key};
f992cfaa	55
f992cfaa	56	is( $f1->is_foreign_key, $test->{is_foreign_key},
55e462b4	57	" is ".($test->{is_foreign_key} ? '' : 'not').' a foreign_key' )
55e462b4	58	if exists $test->{is_foreign_key};
f992cfaa	59
f992cfaa	60	is( $f1->is_auto_increment, $test->{is_auto_increment},
55e462b4	61	" is ".($test->{is_auto_increment} ? '' : 'not').' an auto_increment' )
	62	if exists $test->{is_auto_increment};
	63	}
	64
	65	sub constraint_ok {
	66	my ($con,$test) = @_;
	67	#$test->{name} \|\|= "<anon>";
	68
	69	if ( exists $test->{name} ) {
	70	is( $con->name, $test->{name}, " Constraint '$test->{name}'" );
	71	}
	72	else {
	73	ok( $con, " Constraint" );
	74	}
f992cfaa	75
55e462b4	76	is( $con->type, $test->{type}, " type is '$test->{type}'" )
55e462b4	77	if exists $test->{type};
f992cfaa	78
55e462b4	79	is( $con->table->name, $test->{table}, " table is '$test->{table}'" )
55e462b4	80	if exists $test->{table};
f992cfaa	81
55e462b4	82	is( join(",",$con->fields), $test->{fields},
	83	" fields is '$test->{fields}'" )
	84	if exists $test->{fields};
f992cfaa	85
55e462b4	86	is( $con->reference_table, $test->{reference_table},
	87	" reference_table is '$test->{reference_table}'" )
	88	if exists $test->{reference_table};
f992cfaa	89
55e462b4	90	is( join(",",$con->reference_fields), $test->{reference_fields},
	91	" reference_fields is '$test->{reference_fields}'" )
	92	if exists $test->{reference_fields};
f992cfaa	93
55e462b4	94	is( $con->match_type, $test->{match_type},
	95	" match_type is '$test->{match_type}'" )
	96	if exists $test->{match_type};
f992cfaa	97
55e462b4	98	is( $con->on_delete_do, $test->{on_delete_do},
	99	" on_delete_do is '$test->{on_delete_do}'" )
	100	if exists $test->{on_delete_do};
f992cfaa	101
55e462b4	102	is( $con->on_update_do, $test->{on_update_do},
	103	" on_update_do is '$test->{on_update_do}'" )
	104	if exists $test->{on_update_do};
	105	}
	106
	107	sub test_table {
	108	my $tbl = shift;
	109	my %arg = @_;
	110	$arg{constraints} \|\|= [];
	111	my $name = $arg{name} \|\| die "Need a table name to test.";
f992cfaa	112
55e462b4	113	my @fldnames = map { $_->{name} } @{$arg{fields}};
	114	is_deeply( [ map {$_->name} $tbl->get_fields ],
	115	[ map {$_->{name}} @{$arg{fields}} ],
	116	"Table $name\'s fields" );
	117	foreach ( @{$arg{fields}} ) {
	118	my $name = $_->{name} \|\| die "Need a field name to test.";
	119	test_field( $tbl->get_field($name), $_ );
	120	}
f992cfaa	121
55e462b4	122	if ( my @tcons = @{$arg{constraints}} ) {
	123	my @cons = $tbl->get_constraints;
	124	is(scalar(@cons), scalar(@tcons),
	125	"Table $name has ".scalar(@tcons)." Constraints");
	126	foreach ( @cons ) {
	127	my $ans = { table => $tbl->name, %{shift @tcons}};
	128	constraint_ok( $_, $ans );
	129	}
	130	}
	131	}
	132
	133	# Testing 1,2,3,..
	134	#=============================================================================
	135
ae15bb99	136	plan tests => 151;
55e462b4	137
	138	my $testschema = "$Bin/data/xmi/OrderDB.sqlfairy.poseidon2.xmi";
	139	die "Can't find test schema $testschema" unless -e $testschema;
	140
	141	my $obj;
	142	$obj = SQL::Translator->new(
	143	filename => $testschema,
	144	from => 'XML-XMI-SQLFairy',
	145	to => 'MySQL',
	146	debug => DEBUG,
	147	show_warnings => 1,
	148	);
	149	my $sql = $obj->translate;
	150	ok( $sql, "Got some SQL");
	151	print $sql if DEBUG;
	152
	153
	154	#
	155	# Test the schema
	156	#
	157	my $scma = $obj->schema;
	158	is( $scma->is_valid, 1, 'Schema is valid' );
	159	my @tblnames = map {$_->name} $scma->get_tables;
	160	is(scalar(@{$scma->get_tables}), scalar(@tblnames), "Right number of tables");
ae15bb99	161	is_deeply( \@tblnames,
	162	[qw/Order OrderLine Customer ContactDetails ContactDetails_Customer/]
	163	,"tables");
55e462b4	164
	165	test_table( $scma->get_table("Customer"),
	166	name => "Customer",
	167	fields => [
	168	{
55e462b4	169	name => "name",
	170	data_type => "VARCHAR",
	171	size => 255,
	172	default_value => undef,
	173	is_nullable => 0,
	174	is_primary_key => 0,
	175	},
	176	{
	177	name => "email",
	178	data_type => "VARCHAR",
	179	size => 255,
	180	default_value => undef,
	181	is_nullable => 1,
	182	is_primary_key => 0,
	183	},
f992cfaa	184	{
	185	name => "CustomerID",
	186	data_type => "INT",
	187	size => 10,
	188	default_value => undef,
	189	is_nullable => 0,
	190	is_primary_key => 1,
	191	},
55e462b4	192	],
	193	constraints => [
	194	{
	195	type => "PRIMARY KEY",
f992cfaa	196	fields => "CustomerID",
55e462b4	197	},
	198	#{
	199	# name => "UniqueEmail",
	200	# type => "UNIQUE",
	201	# fields => "email",
	202	#},
	203	],
	204	);
	205
ae15bb99	206	test_table( $scma->get_table("ContactDetails_Customer"),
	207	name => "ContactDetails_Customer",
	208	fields => [
	209	{
	210	name => "ContactDetailsID",
	211	data_type => "INT",
	212	size => 10,
	213	default_value => undef,
	214	is_nullable => 0,
	215	is_primary_key => 1,
	216	is_auto_increment => 0,
	217	},
	218	{
	219	name => "CustomerID",
	220	data_type => "INT",
	221	size => 10,
	222	default_value => undef,
	223	is_nullable => 0,
	224	is_primary_key => 1,
	225	is_auto_increment => 0,
	226	},
	227	],
	228	constraints => [
	229	{
	230	type => "FOREIGN KEY",
	231	fields => "ContactDetailsID",
	232	reference_table => "ContactDetails",
	233	reference_fields => "ContactDetailsID",
	234	},
	235	{
	236	type => "FOREIGN KEY",
	237	fields => "CustomerID",
	238	reference_table => "Customer",
	239	reference_fields => "CustomerID",
	240	},
	241	{
	242	type => "PRIMARY KEY",
	243	fields => "ContactDetailsID,CustomerID",
	244	},
	245	],
	246	);
	247
	248	test_table( $scma->get_table("ContactDetails"),
	249	name => "ContactDetails",
	250	fields => [
	251	{
	252	name => "address",
	253	data_type => "VARCHAR",
	254	size => "255",
	255	default_value => undef,
	256	is_nullable => 1,
	257	is_primary_key => 0,
	258	},
	259	{
	260	name => "telephone",
	261	data_type => "VARCHAR",
	262	size => "255",
	263	default_value => undef,
	264	is_nullable => 1,
	265	is_primary_key => 0,
	266	},
	267	{
	268	name => "ContactDetailsID",
	269	data_type => "INT",
270	size => 10,
271	default_value => undef,
272	is_nullable => 0,
273	is_primary_key => 1,
274	is_auto_increment => 1,
275	},
276	],
277	constraints => [
278	{
279	type => "PRIMARY KEY",
280	fields => "ContactDetailsID",
281	},
282	],
283	);
284
55e462b4	285	test_table( $scma->get_table("Order"),
	286	name => "Order",
	287	fields => [
	288	{
f992cfaa	289	name => "invoiceNumber",
	290	data_type => "INT",
	291	size => 10,
55e462b4	292	default_value => undef,
55e462b4	293	is_nullable => 0,
f992cfaa	294	is_primary_key => 1,
55e462b4	295	},
55e462b4	296	{
f992cfaa	297	name => "orderDate",
f992cfaa	298	data_type => "DATE",
55e462b4	299	default_value => undef,
55e462b4	300	is_nullable => 0,
f992cfaa	301	is_primary_key => 0,
55e462b4	302	},
55e462b4	303	{
f992cfaa	304	name => "CustomerID",
55e462b4	305	data_type => "INT",
f992cfaa	306	size => 10,
55e462b4	307	default_value => undef,
	308	is_nullable => 0,
	309	is_primary_key => 0,
	310	is_foreign_key => 1,
	311	},
	312	],
	313	constraints => [
	314	{
	315	type => "PRIMARY KEY",
f992cfaa	316	fields => "invoiceNumber",
55e462b4	317	},
	318	{
	319	type => "FOREIGN KEY",
f992cfaa	320	fields => "CustomerID",
55e462b4	321	reference_table => "Customer",
f992cfaa	322	reference_fields => "CustomerID",
55e462b4	323	},
	324	],
	325	# TODO
	326	#indexes => [
	327	# {
	328	# name => "idxOrderDate",
	329	# type => "INDEX",
	330	# fields => "orderDate",
	331	# },
	332	#],
	333	);
	334
	335
	336	test_table( $scma->get_table("OrderLine"),
	337	name => "OrderLine",
	338	fields => [
	339	{
	340	name => "lineNumber",
	341	data_type => "INT",
	342	size => 255,
	343	default_value => 1,
	344	is_nullable => 0,
f992cfaa	345	is_primary_key => 0,
55e462b4	346	},
	347	{
	348	name => "quantity",
	349	data_type => "INT",
	350	size => 255,
	351	default_value => 1,
	352	is_nullable => 0,
	353	is_primary_key => 0,
	354	},
	355	{
f992cfaa	356	name => "OrderLineID",
	357	data_type => "INT",
	358	size => 10,
	359	default_value => undef,
	360	is_nullable => 0,
	361	is_primary_key => 1,
	362	},
	363	{
	364	name => "invoiceNumber",
55e462b4	365	data_type => "INT",
	366	size => 10,
	367	default_value => undef,
	368	is_nullable => 1,
f992cfaa	369	is_primary_key => 1,
55e462b4	370	},
	371	],
	372	constraints => [
	373	{
	374	type => "PRIMARY KEY",
f992cfaa	375	fields => "OrderLineID,invoiceNumber",
55e462b4	376	},
	377	{
	378	type => "FOREIGN KEY",
f992cfaa	379	fields => "invoiceNumber",
55e462b4	380	reference_table => "Order",
f992cfaa	381	reference_fields => "invoiceNumber",
55e462b4	382	},
	383	],
	384	);