[dbsrgits/DBIx-Class.git] / t / 750firebird.t

use strict;
use warnings;

use Test::More;
use Test::Exception;
use lib qw(t/lib);
use DBICTest;
use Scope::Guard ();

# tests stolen from 749sybase_asa.t

my ($dsn, $user, $pass)    = @ENV{map { "DBICTEST_FIREBIRD_${_}" }      qw/DSN USER PASS/};
my ($dsn2, $user2, $pass2) = @ENV{map { "DBICTEST_FIREBIRD_ODBC_${_}" } qw/DSN USER PASS/};

plan skip_all => <<'EOF' unless $dsn || $dsn2;
Set $ENV{DBICTEST_FIREBIRD_DSN} and/or $ENV{DBICTEST_FIREBIRD_ODBC_DSN},
_USER and _PASS to run these tests
EOF

my @info = (
  [ $dsn,  $user,  $pass  ],
  [ $dsn2, $user2, $pass2 ],
);

my $schema;

foreach my $conn_idx (0..$#info) {
  my ($dsn, $user, $pass) = @{ $info[$conn_idx] || [] };

  next unless $dsn;

  $schema = DBICTest::Schema->connect($dsn, $user, $pass, {
    auto_savepoint  => 1,
    quote_char      => q["],
    name_sep        => q[.],
    on_connect_call => 'use_softcommit',
  });
  my $dbh = $schema->storage->dbh;

  my $sg = Scope::Guard->new(\&cleanup);

  eval { $dbh->do(q[DROP TABLE "artist"]) };
  $dbh->do(<<EOF);
  CREATE TABLE "artist" (
    "artistid" INT PRIMARY KEY,
    "name" VARCHAR(255),
    "charfield" CHAR(10),
    "rank" INT DEFAULT 13
  )
EOF
  eval { $dbh->do(q[DROP GENERATOR "gen_artist_artistid"]) };
  $dbh->do('CREATE GENERATOR "gen_artist_artistid"');
  eval { $dbh->do('DROP TRIGGER "artist_bi"') };
  $dbh->do(<<EOF);
  CREATE TRIGGER "artist_bi" FOR "artist"
  ACTIVE BEFORE INSERT POSITION 0
  AS
  BEGIN
   IF (NEW."artistid" IS NULL) THEN
    NEW."artistid" = GEN_ID("gen_artist_artistid",1);
  END
EOF
  eval { $dbh->do('DROP TABLE "sequence_test"') };
  $dbh->do(<<EOF);
  CREATE TABLE "sequence_test" (
    "pkid1" INT NOT NULL,
    "pkid2" INT NOT NULL,
    "nonpkid" INT,
    "name" VARCHAR(255)
  )
EOF
  $dbh->do('ALTER TABLE "sequence_test" ADD CONSTRAINT "sequence_test_constraint" PRIMARY KEY ("pkid1", "pkid2")');
  eval { $dbh->do('DROP GENERATOR "pkid1_seq"') };
  eval { $dbh->do('DROP GENERATOR "pkid2_seq"') };
  eval { $dbh->do('DROP GENERATOR "nonpkid_seq"') };
  $dbh->do('CREATE GENERATOR "pkid1_seq"');
  $dbh->do('CREATE GENERATOR "pkid2_seq"');
  $dbh->do('SET GENERATOR "pkid2_seq" TO 9');
  $dbh->do('CREATE GENERATOR "nonpkid_seq"');
  $dbh->do('SET GENERATOR "nonpkid_seq" TO 19');

  my $ars = $schema->resultset('Artist');
  is ( $ars->count, 0, 'No rows at first' );

# test primary key handling
  my $new = $ars->create({ name => 'foo' });
  ok($new->artistid, "Auto-PK worked");

# test auto increment using generators WITHOUT triggers
  for (1..5) {
      my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' });
      is($st->pkid1, $_, "Firebird Auto-PK without trigger: First primary key");
      is($st->pkid2, $_ + 9, "Firebird Auto-PK without trigger: Second primary key");
      is($st->nonpkid, $_ + 19, "Firebird Auto-PK without trigger: Non-primary key");
  }
  my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 });
  is($st->pkid1, 55, "Firebird Auto-PK without trigger: First primary key set manually");

# test savepoints
  eval {
    $schema->txn_do(sub {
      eval {
        $schema->txn_do(sub {
          $ars->create({ name => 'in_savepoint' });
          die "rolling back savepoint";
        });
      };
      ok ((not $ars->search({ name => 'in_savepoint' })->first),
        'savepoint rolled back');
      $ars->create({ name => 'in_outer_txn' });
      die "rolling back outer txn";
    });
  };

  like $@, qr/rolling back outer txn/,
    'correct exception for rollback';

  ok ((not $ars->search({ name => 'in_outer_txn' })->first),
    'outer txn rolled back');

# test explicit key spec
  $new = $ars->create ({ name => 'bar', artistid => 66 });
  is($new->artistid, 66, 'Explicit PK worked');
  $new->discard_changes;
  is($new->artistid, 66, 'Explicit PK assigned');

# row update
  lives_ok {
    $new->update({ name => 'baz' })
  } 'update survived';
  $new->discard_changes;
  is $new->name, 'baz', 'row updated';

# test populate
  lives_ok (sub {
    my @pop;
    for (1..2) {
      push @pop, { name => "Artist_$_" };
    }
    $ars->populate (\@pop);
  });

# test populate with explicit key
  lives_ok (sub {
    my @pop;
    for (1..2) {
      push @pop, { name => "Artist_expkey_$_", artistid => 100 + $_ };
    }
    $ars->populate (\@pop);
  });

# count what we did so far
  is ($ars->count, 6, 'Simple count works');

# test ResultSet UPDATE
  lives_and {
    $ars->search({ name => 'foo' })->update({ rank => 4 });

    is eval { $ars->search({ name => 'foo' })->first->rank }, 4;
  } 'Can update a column';

  my ($updated) = $schema->resultset('Artist')->search({name => 'foo'});
  is eval { $updated->rank }, 4, 'and the update made it to the database';


# test LIMIT support
  my $lim = $ars->search( {},
    {
      rows => 3,
      offset => 4,
      order_by => 'artistid'
    }
  );
  is( $lim->count, 2, 'ROWS+OFFSET count ok' );
  is( $lim->all, 2, 'Number of ->all objects matches count' );

# test iterator
  $lim->reset;
  is( eval { $lim->next->artistid }, 101, "iterator->next ok" );
  is( eval { $lim->next->artistid }, 102, "iterator->next ok" );
  is( $lim->next, undef, "next past end of resultset ok" );

# test multiple executing cursors
  {
    my $rs1 = $ars->search({}, { order_by => { -asc  => 'artistid' }});
    my $rs2 = $ars->search({}, { order_by => { -desc => 'artistid' }});

    is $rs1->next->artistid, 1,   'multiple cursors';
    is $rs2->next->artistid, 102, 'multiple cursors';
  }

# test empty insert
  {
    local $ars->result_source->column_info('artistid')->{is_auto_increment} = 0;

    lives_ok { $ars->create({}) }
      'empty insert works';
  }

# test blobs (stolen from 73oracle.t)
  eval { $dbh->do('DROP TABLE "bindtype_test2"') };
  $dbh->do(q[
  CREATE TABLE "bindtype_test2"
  (
    "id"     INT PRIMARY KEY,
    "bytea"  INT,
    "a_blob" BLOB,
    "a_clob" BLOB SUB_TYPE TEXT
  )
  ]);

  my %binstr = ( 'small' => join('', map { chr($_) } ( 1 .. 127 )) );
  $binstr{'large'} = $binstr{'small'} x 1024;

  my $maxloblen = length $binstr{'large'};
  local $dbh->{'LongReadLen'} = $maxloblen;

  my $rs = $schema->resultset('BindType2');
  my $id = 0;

  foreach my $type (qw( a_blob a_clob )) {
    foreach my $size (qw( small large )) {
      $id++;

# turn off horrendous binary DBIC_TRACE output
      local $schema->storage->{debug} = 0;

      lives_ok { $rs->create( { 'id' => $id, $type => $binstr{$size} } ) }
      "inserted $size $type without dying";

      ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" );
    }
  }
}

done_testing;

# clean up our mess

sub cleanup {
  my $dbh;
  eval {
    $schema->storage->disconnect; # to avoid object FOO is in use errors
    $dbh = $schema->storage->dbh;
  };
  return unless $dbh;

  eval { $dbh->do('DROP TRIGGER "artist_bi"') };
  diag $@ if $@;

  foreach my $generator (qw/gen_artist_artistid pkid1_seq pkid2_seq
                            nonpkid_seq/) {
    eval { $dbh->do(qq{DROP GENERATOR "$generator"}) };
    diag $@ if $@;
  }

  foreach my $table (qw/artist bindtype_test2 sequence_test/) {
    eval { $dbh->do(qq[DROP TABLE "$table"]) };
    diag $@ if $@;
  }
}
Commit	Line	Data
3a8aae80	1	use strict;
	2	use warnings;
	3
	4	use Test::More;
	5	use Test::Exception;
	6	use lib qw(t/lib);
	7	use DBICTest;
dff4c3a3	8	use Scope::Guard ();
3a8aae80	9
	10	# tests stolen from 749sybase_asa.t
	11
	12	my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_FIREBIRD_${_}" } qw/DSN USER PASS/};
	13	my ($dsn2, $user2, $pass2) = @ENV{map { "DBICTEST_FIREBIRD_ODBC_${_}" } qw/DSN USER PASS/};
	14
	15	plan skip_all => <<'EOF' unless $dsn \|\| $dsn2;
	16	Set $ENV{DBICTEST_FIREBIRD_DSN} and/or $ENV{DBICTEST_FIREBIRD_ODBC_DSN},
	17	_USER and _PASS to run these tests
	18	EOF
	19
	20	my @info = (
	21	[ $dsn, $user, $pass ],
	22	[ $dsn2, $user2, $pass2 ],
	23	);
	24
145b2a3d	25	my $schema;
3a8aae80	26
5c6ed0b5	27	foreach my $conn_idx (0..$#info) {
9633951d	28	my ($dsn, $user, $pass) = @{ $info[$conn_idx] \|\| [] };
3a8aae80	29
	30	next unless $dsn;
	31
32323fc2	32	$schema = DBICTest::Schema->connect($dsn, $user, $pass, {
dd2109ee	33	auto_savepoint => 1,
	34	quote_char => q["],
	35	name_sep => q[.],
	36	on_connect_call => 'use_softcommit',
32323fc2	37	});
3a8aae80	38	my $dbh = $schema->storage->dbh;
3a8aae80	39
dff4c3a3	40	my $sg = Scope::Guard->new(\&cleanup);
3a8aae80	41
930689e6	42	eval { $dbh->do(q[DROP TABLE "artist"]) };
3a8aae80	43	$dbh->do(<<EOF);
930689e6	44	CREATE TABLE "artist" (
	45	"artistid" INT PRIMARY KEY,
	46	"name" VARCHAR(255),
	47	"charfield" CHAR(10),
	48	"rank" INT DEFAULT 13
3a8aae80	49	)
3a8aae80	50	EOF
930689e6	51	eval { $dbh->do(q[DROP GENERATOR "gen_artist_artistid"]) };
	52	$dbh->do('CREATE GENERATOR "gen_artist_artistid"');
	53	eval { $dbh->do('DROP TRIGGER "artist_bi"') };
3a8aae80	54	$dbh->do(<<EOF);
930689e6	55	CREATE TRIGGER "artist_bi" FOR "artist"
3a8aae80	56	ACTIVE BEFORE INSERT POSITION 0
	57	AS
	58	BEGIN
930689e6	59	IF (NEW."artistid" IS NULL) THEN
930689e6	60	NEW."artistid" = GEN_ID("gen_artist_artistid",1);
3a8aae80	61	END
3a8aae80	62	EOF
e1958268	63	eval { $dbh->do('DROP TABLE "sequence_test"') };
	64	$dbh->do(<<EOF);
	65	CREATE TABLE "sequence_test" (
	66	"pkid1" INT NOT NULL,
	67	"pkid2" INT NOT NULL,
	68	"nonpkid" INT,
	69	"name" VARCHAR(255)
	70	)
	71	EOF
	72	$dbh->do('ALTER TABLE "sequence_test" ADD CONSTRAINT "sequence_test_constraint" PRIMARY KEY ("pkid1", "pkid2")');
	73	eval { $dbh->do('DROP GENERATOR "pkid1_seq"') };
	74	eval { $dbh->do('DROP GENERATOR "pkid2_seq"') };
	75	eval { $dbh->do('DROP GENERATOR "nonpkid_seq"') };
	76	$dbh->do('CREATE GENERATOR "pkid1_seq"');
	77	$dbh->do('CREATE GENERATOR "pkid2_seq"');
	78	$dbh->do('SET GENERATOR "pkid2_seq" TO 9');
	79	$dbh->do('CREATE GENERATOR "nonpkid_seq"');
	80	$dbh->do('SET GENERATOR "nonpkid_seq" TO 19');
3a8aae80	81
	82	my $ars = $schema->resultset('Artist');
	83	is ( $ars->count, 0, 'No rows at first' );
	84
	85	# test primary key handling
	86	my $new = $ars->create({ name => 'foo' });
	87	ok($new->artistid, "Auto-PK worked");
	88
e1958268	89	# test auto increment using generators WITHOUT triggers
	90	for (1..5) {
	91	my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' });
	92	is($st->pkid1, $_, "Firebird Auto-PK without trigger: First primary key");
	93	is($st->pkid2, $_ + 9, "Firebird Auto-PK without trigger: Second primary key");
	94	is($st->nonpkid, $_ + 19, "Firebird Auto-PK without trigger: Non-primary key");
	95	}
	96	my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 });
	97	is($st->pkid1, 55, "Firebird Auto-PK without trigger: First primary key set manually");
	98
32323fc2	99	# test savepoints
a499b173	100	eval {
	101	$schema->txn_do(sub {
	102	eval {
	103	$schema->txn_do(sub {
	104	$ars->create({ name => 'in_savepoint' });
	105	die "rolling back savepoint";
	106	});
	107	};
	108	ok ((not $ars->search({ name => 'in_savepoint' })->first),
	109	'savepoint rolled back');
	110	$ars->create({ name => 'in_outer_txn' });
	111	die "rolling back outer txn";
	112	});
	113	};
5c6ed0b5	114
	115	like $@, qr/rolling back outer txn/,
	116	'correct exception for rollback';
	117
a499b173	118	ok ((not $ars->search({ name => 'in_outer_txn' })->first),
a499b173	119	'outer txn rolled back');
32323fc2	120
3a8aae80	121	# test explicit key spec
	122	$new = $ars->create ({ name => 'bar', artistid => 66 });
	123	is($new->artistid, 66, 'Explicit PK worked');
	124	$new->discard_changes;
	125	is($new->artistid, 66, 'Explicit PK assigned');
	126
e1958268	127	# row update
babd3d8e	128	lives_ok {
	129	$new->update({ name => 'baz' })
	130	} 'update survived';
	131	$new->discard_changes;
	132	is $new->name, 'baz', 'row updated';
	133
3a8aae80	134	# test populate
	135	lives_ok (sub {
	136	my @pop;
	137	for (1..2) {
	138	push @pop, { name => "Artist_$_" };
	139	}
	140	$ars->populate (\@pop);
	141	});
	142
	143	# test populate with explicit key
	144	lives_ok (sub {
	145	my @pop;
	146	for (1..2) {
	147	push @pop, { name => "Artist_expkey_$_", artistid => 100 + $_ };
	148	}
17d59d97	149	$ars->populate (\@pop);
3a8aae80	150	});
	151
	152	# count what we did so far
	153	is ($ars->count, 6, 'Simple count works');
	154
e1958268	155	# test ResultSet UPDATE
17d59d97	156	lives_and {
	157	$ars->search({ name => 'foo' })->update({ rank => 4 });
	158
dd2109ee	159	is eval { $ars->search({ name => 'foo' })->first->rank }, 4;
72537d31	160	} 'Can update a column';
	161
	162	my ($updated) = $schema->resultset('Artist')->search({name => 'foo'});
dd2109ee	163	is eval { $updated->rank }, 4, 'and the update made it to the database';
72537d31	164
72537d31	165
3a8aae80	166	# test LIMIT support
	167	my $lim = $ars->search( {},
	168	{
	169	rows => 3,
	170	offset => 4,
	171	order_by => 'artistid'
	172	}
	173	);
	174	is( $lim->count, 2, 'ROWS+OFFSET count ok' );
	175	is( $lim->all, 2, 'Number of ->all objects matches count' );
	176
	177	# test iterator
	178	$lim->reset;
dd2109ee	179	is( eval { $lim->next->artistid }, 101, "iterator->next ok" );
dd2109ee	180	is( eval { $lim->next->artistid }, 102, "iterator->next ok" );
3a8aae80	181	is( $lim->next, undef, "next past end of resultset ok" );
3a8aae80	182
17d59d97	183	# test multiple executing cursors
	184	{
	185	my $rs1 = $ars->search({}, { order_by => { -asc => 'artistid' }});
	186	my $rs2 = $ars->search({}, { order_by => { -desc => 'artistid' }});
	187
c13488fd	188	is $rs1->next->artistid, 1, 'multiple cursors';
c13488fd	189	is $rs2->next->artistid, 102, 'multiple cursors';
17d59d97	190	}
17d59d97	191
3a8aae80	192	# test empty insert
	193	{
	194	local $ars->result_source->column_info('artistid')->{is_auto_increment} = 0;
	195
	196	lives_ok { $ars->create({}) }
	197	'empty insert works';
	198	}
	199
	200	# test blobs (stolen from 73oracle.t)
e1958268	201	eval { $dbh->do('DROP TABLE "bindtype_test2"') };
	202	$dbh->do(q[
	203	CREATE TABLE "bindtype_test2"
	204	(
	205	"id" INT PRIMARY KEY,
	206	"bytea" INT,
	207	"a_blob" BLOB,
	208	"a_clob" BLOB SUB_TYPE TEXT
	209	)
	210	]);
	211
	212	my %binstr = ( 'small' => join('', map { chr($_) } ( 1 .. 127 )) );
	213	$binstr{'large'} = $binstr{'small'} x 1024;
	214
	215	my $maxloblen = length $binstr{'large'};
	216	local $dbh->{'LongReadLen'} = $maxloblen;
	217
	218	my $rs = $schema->resultset('BindType2');
	219	my $id = 0;
	220
	221	foreach my $type (qw( a_blob a_clob )) {
	222	foreach my $size (qw( small large )) {
	223	$id++;
3a8aae80	224
3a8aae80	225	# turn off horrendous binary DBIC_TRACE output
e1958268	226	local $schema->storage->{debug} = 0;
3a8aae80	227
e1958268	228	lives_ok { $rs->create( { 'id' => $id, $type => $binstr{$size} } ) }
e1958268	229	"inserted $size $type without dying";
3a8aae80	230
e1958268	231	ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" );
3a8aae80	232	}
	233	}
	234	}
	235
	236	done_testing;
	237
	238	# clean up our mess
dff4c3a3	239
dff4c3a3	240	sub cleanup {
145b2a3d	241	my $dbh;
	242	eval {
	243	$schema->storage->disconnect; # to avoid object FOO is in use errors
	244	$dbh = $schema->storage->dbh;
	245	};
	246	return unless $dbh;
	247
930689e6	248	eval { $dbh->do('DROP TRIGGER "artist_bi"') };
145b2a3d	249	diag $@ if $@;
145b2a3d	250
e1958268	251	foreach my $generator (qw/gen_artist_artistid pkid1_seq pkid2_seq
	252	nonpkid_seq/) {
	253	eval { $dbh->do(qq{DROP GENERATOR "$generator"}) };
	254	diag $@ if $@;
	255	}
145b2a3d	256
e1958268	257	foreach my $table (qw/artist bindtype_test2 sequence_test/) {
9633951d	258	eval { $dbh->do(qq[DROP TABLE "$table"]) };
e1958268	259	diag $@ if $@;
3a8aae80	260	}
3a8aae80	261	}