[dbsrgits/DBIx-Class.git] / t / 747mssql_ado.t

use strict;
use warnings;

use Test::More;
use Test::Exception;
use Try::Tiny;
use DBIx::Class::Optional::Dependencies ();
use lib qw(t/lib);
use DBICTest;

plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('test_rdbms_mssql_ado')
  unless DBIx::Class::Optional::Dependencies->req_ok_for ('test_rdbms_mssql_ado');

# Example DSN (from frew):
# dbi:ADO:PROVIDER=sqlncli10;SERVER=tcp:172.24.2.10;MARS Connection=True;Initial Catalog=CIS;UID=cis_web;PWD=...;DataTypeCompatibility=80;

my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ADO_${_}" } qw/DSN USER PASS/};

plan skip_all => 'Set $ENV{DBICTEST_MSSQL_ADO_DSN}, _USER and _PASS to run this test'
  unless ($dsn && $user);

DBICTest::Schema->load_classes(qw/VaryingMAX ArtistGUID/);

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

my $maxloblen = length $binstr{'large'};

my $schema = DBICTest::Schema->connect($dsn, $user, $pass, {
  auto_savepoint => 1,
  LongReadLen => $maxloblen,
});

$schema->storage->ensure_connected;

isa_ok($schema->storage, 'DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server');

my $ver = $schema->storage->_server_info->{normalized_dbms_version};

ok $ver, 'can introspect DBMS version';

# 2005 and greater
is $schema->storage->sql_limit_dialect, ($ver >= 9 ? 'RowNumberOver' : 'Top'),
  'correct limit dialect detected';

$schema->storage->dbh_do (sub {
    my ($storage, $dbh) = @_;
    try { local $^W = 0; $dbh->do("DROP TABLE artist") };
    $dbh->do(<<'SQL');
CREATE TABLE artist (
   artistid INT IDENTITY NOT NULL,
   name VARCHAR(100),
   rank INT NOT NULL DEFAULT '13',
   charfield CHAR(10) NULL,
   primary key(artistid)
)
SQL
});

$schema->storage->dbh_do (sub {
  my ($storage, $dbh) = @_;
  try { local $^W = 0; $dbh->do("DROP TABLE artist_guid") };
  $dbh->do(<<"SQL");
CREATE TABLE artist_guid (
 artistid UNIQUEIDENTIFIER NOT NULL,
 name VARCHAR(100),
 rank INT NULL,
 charfield CHAR(10) NULL,
 a_guid UNIQUEIDENTIFIER,
 primary key(artistid)
)
SQL
});

my $have_max = $ver >= 9; # 2005 and greater

$schema->storage->dbh_do (sub {
    my ($storage, $dbh) = @_;
    try { local $^W = 0; $dbh->do("DROP TABLE varying_max_test") };
    $dbh->do("
CREATE TABLE varying_max_test (
   id INT IDENTITY NOT NULL,
" . ($have_max ? "
   varchar_max VARCHAR(MAX),
   nvarchar_max NVARCHAR(MAX),
   varbinary_max VARBINARY(MAX),
" : "
   varchar_max TEXT,
   nvarchar_max NTEXT,
   varbinary_max IMAGE,
") . "
   primary key(id)
)");
});

my $ars = $schema->resultset('Artist');

my $new = $ars->create({ name => 'foo' });
ok($new->artistid > 0, 'Auto-PK worked');

# make sure select works
my $found = $schema->resultset('Artist')->search({ name => 'foo' })->first;
is $found->artistid, $new->artistid, 'search works';

# test large column list in select
$found = $schema->resultset('Artist')->search({ name => 'foo' }, {
  select => ['artistid', 'name', map \"'foo' foo_$_", 0..50],
  as     => ['artistid', 'name', map        "foo_$_", 0..50],
})->first;
is $found->artistid, $new->artistid, 'select with big column list';
is $found->get_column('foo_50'), 'foo', 'last item in big column list';

# create a few more rows
for (1..12) {
  $schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
}

# test multiple active cursors
my $rs1 = $schema->resultset('Artist')->search({}, { order_by => 'artistid' });
my $rs2 = $schema->resultset('Artist')->search({}, { order_by => 'name' });

while ($rs1->next) {
  ok try { $rs2->next }, 'multiple active cursors';
}

# test bug where ADO blows up if the first bindparam is shorter than the second
is $schema->resultset('Artist')->search({ artistid => 2 })->first->name,
  'Artist 1',
  'short bindparam';

is $schema->resultset('Artist')->search({ artistid => 13 })->first->name,
  'Artist 12',
  'longer bindparam';

# 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');

# test basic transactions
$schema->txn_do(sub {
  $ars->create({ name => 'transaction_commit' });
});
ok($ars->search({ name => 'transaction_commit' })->first,
  'transaction committed');
$ars->search({ name => 'transaction_commit' })->delete,
throws_ok {
  $schema->txn_do(sub {
    $ars->create({ name => 'transaction_rollback' });
    die 'rolling back';
  });
} qr/rolling back/, 'rollback executed';
is $ars->search({ name => 'transaction_rollback' })->first, undef,
  'transaction rolled back';

# test two-phase commit and inner transaction rollback from nested transactions
$schema->txn_do(sub {
  $ars->create({ name => 'in_outer_transaction' });
  $schema->txn_do(sub {
    $ars->create({ name => 'in_inner_transaction' });
  });
  ok($ars->search({ name => 'in_inner_transaction' })->first,
    'commit from inner transaction visible in outer transaction');
  throws_ok {
    $schema->txn_do(sub {
      $ars->create({ name => 'in_inner_transaction_rolling_back' });
      die 'rolling back inner transaction';
    });
  } qr/rolling back inner transaction/, 'inner transaction rollback executed';
});
ok($ars->search({ name => 'in_outer_transaction' })->first,
  'commit from outer transaction');
ok($ars->search({ name => 'in_inner_transaction' })->first,
  'commit from inner transaction');
is $ars->search({ name => 'in_inner_transaction_rolling_back' })->first,
  undef,
  'rollback from inner transaction';
$ars->search({ name => 'in_outer_transaction' })->delete;
$ars->search({ name => 'in_inner_transaction' })->delete;

# 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, 18, 'Simple count works');

# test empty insert
my $current_artistid = $ars->search({}, {
  select => [ { max => 'artistid' } ], as => ['artistid']
})->first->artistid;

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

$row->discard_changes;

is $row->artistid, $current_artistid+1,
  'empty insert generated correct PK';

# test that autoinc column still works after empty insert
  $row = $ars->create({ name => 'after_empty_insert' });

  is $row->artistid, $current_artistid+2,
    'autoincrement column functional aftear empty insert';

my $rs = $schema->resultset('VaryingMAX');

foreach my $size (qw/small large/) {
  local $schema->storage->{debug} = 0 if $size eq 'large';

  my $str = $binstr{$size};
  my $row;
  lives_ok {
    $row = $rs->create({
      varchar_max => $str, nvarchar_max => $str, varbinary_max => $str
    });
  } "created $size VARXXX(MAX) LOBs";

  lives_ok {
    $row->discard_changes;
  } 're-selected just-inserted LOBs';

  cmp_ok try { $row->varchar_max },   'eq', $str, 'VARCHAR(MAX) matches';
  cmp_ok try { $row->nvarchar_max },  'eq', $str, 'NVARCHAR(MAX) matches';
  cmp_ok try { $row->varbinary_max }, 'eq', $str, 'VARBINARY(MAX) matches';
}

# test regular blobs

try { local $^W = 0; $schema->storage->dbh->do('DROP TABLE bindtype_test') };
$schema->storage->dbh->do(qq[
CREATE TABLE bindtype_test
(
  id     INT IDENTITY NOT NULL PRIMARY KEY,
  bytea  INT NULL,
  blob   IMAGE NULL,
  blob2  IMAGE NULL,
  clob   TEXT NULL,
  clob2  TEXT NULL,
  a_memo NTEXT NULL
)
],{ RaiseError => 1, PrintError => 1 });

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

foreach my $type (qw( blob clob a_memo )) {
  foreach my $size (qw( small large )) {
    $id++;

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

    my $from_db = eval { $rs->find($id)->$type } || '';
    diag $@ if $@;

    ok($from_db eq $binstr{$size}, "verified inserted $size $type" )
      or do {
        my $hexdump = sub {
          join '', map sprintf('%02X', ord), split //, shift
        };
        diag 'Got: ', "\n", substr($hexdump->($from_db),0,255), '...',
          substr($hexdump->($from_db),-255);
        diag 'Size: ', length($from_db);
        diag 'Expected Size: ', length($binstr{$size});
        diag 'Expected: ', "\n",
          substr($hexdump->($binstr{$size}), 0, 255),
          "...", substr($hexdump->($binstr{$size}),-255);
      };
  }
}
# test IMAGE update
lives_ok {
  $rs->search({ id => 0 })->update({ blob => $binstr{small} });
} 'updated IMAGE to small binstr without dying';

lives_ok {
  $rs->search({ id => 0 })->update({ blob => $binstr{large} });
} 'updated IMAGE to large binstr without dying';

# test GUIDs
lives_ok {
  $row = $schema->resultset('ArtistGUID')->create({ name => 'mtfnpy' })
} 'created a row with a GUID';

ok(
  eval { $row->artistid },
  'row has GUID PK col populated',
);
diag $@ if $@;

my $guid = try { $row->artistid }||'';

ok(($guid !~ /^{.*?}\z/), 'GUID not enclosed in braces')
  or diag "GUID is: $guid";

ok(
  eval { $row->a_guid },
  'row has a GUID col with auto_nextval populated',
);
diag $@ if $@;

my $row_from_db = $schema->resultset('ArtistGUID')
  ->search({ name => 'mtfnpy' })->first;

is try { $row_from_db->artistid }, try { $row->artistid },
  'PK GUID round trip (via ->search->next)';

is try { $row_from_db->a_guid }, try { $row->a_guid },
  'NON-PK GUID round trip (via ->search->next)';

$row_from_db = try { $schema->resultset('ArtistGUID')
  ->find($row->artistid) };

is try { $row_from_db->artistid }, try { $row->artistid },
  'PK GUID round trip (via ->find)';

is try { $row_from_db->a_guid }, try { $row->a_guid },
  'NON-PK GUID round trip (via ->find)';

($row_from_db) = $schema->resultset('ArtistGUID')
  ->search({ name => 'mtfnpy' })->all;

is try { $row_from_db->artistid }, try { $row->artistid },
  'PK GUID round trip (via ->search->all)';

is try { $row_from_db->a_guid }, try { $row->a_guid },
  'NON-PK GUID round trip (via ->search->all)';

lives_ok {
  $row = $schema->resultset('ArtistGUID')->create({
      artistid => '70171270-4822-4450-81DF-921F99BA3C06',
      name => 'explicit_guid',
  });
} 'created a row with explicit PK GUID';

is try { $row->artistid }, '70171270-4822-4450-81DF-921F99BA3C06',
  'row has correct PK GUID';

lives_ok {
  $row->update({ artistid => '70171270-4822-4450-81DF-921F99BA3C07' });
} "updated row's PK GUID";

is try { $row->artistid }, '70171270-4822-4450-81DF-921F99BA3C07',
  'row has correct PK GUID';

lives_ok {
  $row->delete;
} 'deleted the row';

lives_ok {
  $schema->resultset('ArtistGUID')->populate([{
      artistid => '70171270-4822-4450-81DF-921F99BA3C06',
      name => 'explicit_guid',
  }]);
} 'created a row with explicit PK GUID via ->populate in void context';

done_testing;

# clean up our mess
END {
  local $SIG{__WARN__} = sub {};
  if (my $dbh = try { $schema->storage->_dbh }) {
    (try { $dbh->do("DROP TABLE $_") })
      for qw/artist artist_guid varying_max_test bindtype_test/;
  }

  undef $schema;
}
# vim:sw=2 sts=2
Commit	Line	Data
4ffa5700	1	use strict;
	2	use warnings;
	3
	4	use Test::More;
2edf3352	5	use Test::Exception;
2edf3352	6	use Try::Tiny;
199fbc45	7	use DBIx::Class::Optional::Dependencies ();
4ffa5700	8	use lib qw(t/lib);
	9	use DBICTest;
	10
199fbc45	11	plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('test_rdbms_mssql_ado')
	12	unless DBIx::Class::Optional::Dependencies->req_ok_for ('test_rdbms_mssql_ado');
	13
56dca25f	14	# Example DSN (from frew):
	15	# dbi:ADO:PROVIDER=sqlncli10;SERVER=tcp:172.24.2.10;MARS Connection=True;Initial Catalog=CIS;UID=cis_web;PWD=...;DataTypeCompatibility=80;
	16
4ffa5700	17	my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ADO_${_}" } qw/DSN USER PASS/};
	18
	19	plan skip_all => 'Set $ENV{DBICTEST_MSSQL_ADO_DSN}, _USER and _PASS to run this test'
	20	unless ($dsn && $user);
	21
2edf3352	22	DBICTest::Schema->load_classes(qw/VaryingMAX ArtistGUID/);
	23
	24	my %binstr = ( 'small' => join('', map { chr($_) } ( 1 .. 127 )) );
	25	$binstr{'large'} = $binstr{'small'} x 1024;
	26
	27	my $maxloblen = length $binstr{'large'};
	28
	29	my $schema = DBICTest::Schema->connect($dsn, $user, $pass, {
	30	auto_savepoint => 1,
	31	LongReadLen => $maxloblen,
	32	});
	33
4ffa5700	34	$schema->storage->ensure_connected;
4ffa5700	35
2edf3352	36	isa_ok($schema->storage, 'DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server');
4ffa5700	37
56dca25f	38	my $ver = $schema->storage->_server_info->{normalized_dbms_version};
	39
	40	ok $ver, 'can introspect DBMS version';
	41
2edf3352	42	# 2005 and greater
56dca25f	43	is $schema->storage->sql_limit_dialect, ($ver >= 9 ? 'RowNumberOver' : 'Top'),
	44	'correct limit dialect detected';
	45
4ffa5700	46	$schema->storage->dbh_do (sub {
4ffa5700	47	my ($storage, $dbh) = @_;
2edf3352	48	try { local $^W = 0; $dbh->do("DROP TABLE artist") };
4ffa5700	49	$dbh->do(<<'SQL');
	50	CREATE TABLE artist (
	51	artistid INT IDENTITY NOT NULL,
	52	name VARCHAR(100),
	53	rank INT NOT NULL DEFAULT '13',
	54	charfield CHAR(10) NULL,
	55	primary key(artistid)
	56	)
	57	SQL
	58	});
	59
2edf3352	60	$schema->storage->dbh_do (sub {
	61	my ($storage, $dbh) = @_;
	62	try { local $^W = 0; $dbh->do("DROP TABLE artist_guid") };
	63	$dbh->do(<<"SQL");
	64	CREATE TABLE artist_guid (
	65	artistid UNIQUEIDENTIFIER NOT NULL,
	66	name VARCHAR(100),
	67	rank INT NULL,
	68	charfield CHAR(10) NULL,
	69	a_guid UNIQUEIDENTIFIER,
	70	primary key(artistid)
	71	)
	72	SQL
	73	});
	74
	75	my $have_max = $ver >= 9; # 2005 and greater
	76
	77	$schema->storage->dbh_do (sub {
	78	my ($storage, $dbh) = @_;
	79	try { local $^W = 0; $dbh->do("DROP TABLE varying_max_test") };
	80	$dbh->do("
	81	CREATE TABLE varying_max_test (
	82	id INT IDENTITY NOT NULL,
	83	" . ($have_max ? "
	84	varchar_max VARCHAR(MAX),
	85	nvarchar_max NVARCHAR(MAX),
	86	varbinary_max VARBINARY(MAX),
	87	" : "
	88	varchar_max TEXT,
	89	nvarchar_max NTEXT,
	90	varbinary_max IMAGE,
	91	") . "
	92	primary key(id)
	93	)");
	94	});
	95
	96	my $ars = $schema->resultset('Artist');
	97
	98	my $new = $ars->create({ name => 'foo' });
4ffa5700	99	ok($new->artistid > 0, 'Auto-PK worked');
	100
	101	# make sure select works
	102	my $found = $schema->resultset('Artist')->search({ name => 'foo' })->first;
	103	is $found->artistid, $new->artistid, 'search works';
	104
7282bf38	105	# test large column list in select
7282bf38	106	$found = $schema->resultset('Artist')->search({ name => 'foo' }, {
56dca25f	107	select => ['artistid', 'name', map \"'foo' foo_$_", 0..50],
56dca25f	108	as => ['artistid', 'name', map "foo_$_", 0..50],
7282bf38	109	})->first;
	110	is $found->artistid, $new->artistid, 'select with big column list';
	111	is $found->get_column('foo_50'), 'foo', 'last item in big column list';
e38348dd	112
4ffa5700	113	# create a few more rows
8bcd9ece	114	for (1..12) {
4ffa5700	115	$schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
	116	}
	117
	118	# test multiple active cursors
7c5b1b9f	119	my $rs1 = $schema->resultset('Artist')->search({}, { order_by => 'artistid' });
7c5b1b9f	120	my $rs2 = $schema->resultset('Artist')->search({}, { order_by => 'name' });
4ffa5700	121
4ffa5700	122	while ($rs1->next) {
2edf3352	123	ok try { $rs2->next }, 'multiple active cursors';
4ffa5700	124	}
4ffa5700	125
8bcd9ece	126	# test bug where ADO blows up if the first bindparam is shorter than the second
	127	is $schema->resultset('Artist')->search({ artistid => 2 })->first->name,
	128	'Artist 1',
	129	'short bindparam';
	130
	131	is $schema->resultset('Artist')->search({ artistid => 13 })->first->name,
	132	'Artist 12',
	133	'longer bindparam';
	134
2edf3352	135	# test explicit key spec
	136	$new = $ars->create ({ name => 'bar', artistid => 66 });
	137	is($new->artistid, 66, 'Explicit PK worked');
	138	$new->discard_changes;
	139	is($new->artistid, 66, 'Explicit PK assigned');
	140
	141	# test basic transactions
	142	$schema->txn_do(sub {
	143	$ars->create({ name => 'transaction_commit' });
	144	});
	145	ok($ars->search({ name => 'transaction_commit' })->first,
	146	'transaction committed');
	147	$ars->search({ name => 'transaction_commit' })->delete,
	148	throws_ok {
	149	$schema->txn_do(sub {
	150	$ars->create({ name => 'transaction_rollback' });
	151	die 'rolling back';
	152	});
	153	} qr/rolling back/, 'rollback executed';
	154	is $ars->search({ name => 'transaction_rollback' })->first, undef,
	155	'transaction rolled back';
	156
	157	# test two-phase commit and inner transaction rollback from nested transactions
	158	$schema->txn_do(sub {
	159	$ars->create({ name => 'in_outer_transaction' });
	160	$schema->txn_do(sub {
	161	$ars->create({ name => 'in_inner_transaction' });
	162	});
	163	ok($ars->search({ name => 'in_inner_transaction' })->first,
	164	'commit from inner transaction visible in outer transaction');
	165	throws_ok {
	166	$schema->txn_do(sub {
	167	$ars->create({ name => 'in_inner_transaction_rolling_back' });
	168	die 'rolling back inner transaction';
	169	});
	170	} qr/rolling back inner transaction/, 'inner transaction rollback executed';
	171	});
	172	ok($ars->search({ name => 'in_outer_transaction' })->first,
	173	'commit from outer transaction');
	174	ok($ars->search({ name => 'in_inner_transaction' })->first,
	175	'commit from inner transaction');
	176	is $ars->search({ name => 'in_inner_transaction_rolling_back' })->first,
	177	undef,
	178	'rollback from inner transaction';
	179	$ars->search({ name => 'in_outer_transaction' })->delete;
	180	$ars->search({ name => 'in_inner_transaction' })->delete;
	181
	182	# test populate
	183	lives_ok (sub {
	184	my @pop;
	185	for (1..2) {
	186	push @pop, { name => "Artist_$_" };
	187	}
	188	$ars->populate (\@pop);
	189	});
	190
	191	# test populate with explicit key
	192	lives_ok (sub {
	193	my @pop;
	194	for (1..2) {
	195	push @pop, { name => "Artist_expkey_$_", artistid => 100 + $_ };
	196	}
	197	$ars->populate (\@pop);
	198	});
199
200	# count what we did so far
201	is ($ars->count, 18, 'Simple count works');
202
203	# test empty insert
204	my $current_artistid = $ars->search({}, {
205	select => [ { max => 'artistid' } ], as => ['artistid']
206	})->first->artistid;
207
208	my $row;
209	lives_ok { $row = $ars->create({}) }
210	'empty insert works';
211
212	$row->discard_changes;
213
214	is $row->artistid, $current_artistid+1,
215	'empty insert generated correct PK';
216
217	# test that autoinc column still works after empty insert
218	$row = $ars->create({ name => 'after_empty_insert' });
219
220	is $row->artistid, $current_artistid+2,
221	'autoincrement column functional aftear empty insert';
222
223	my $rs = $schema->resultset('VaryingMAX');
224
225	foreach my $size (qw/small large/) {
49eeb48d	226	local $schema->storage->{debug} = 0 if $size eq 'large';
2edf3352	227
	228	my $str = $binstr{$size};
	229	my $row;
	230	lives_ok {
	231	$row = $rs->create({
	232	varchar_max => $str, nvarchar_max => $str, varbinary_max => $str
	233	});
	234	} "created $size VARXXX(MAX) LOBs";
	235
	236	lives_ok {
	237	$row->discard_changes;
	238	} 're-selected just-inserted LOBs';
	239
	240	cmp_ok try { $row->varchar_max }, 'eq', $str, 'VARCHAR(MAX) matches';
	241	cmp_ok try { $row->nvarchar_max }, 'eq', $str, 'NVARCHAR(MAX) matches';
	242	cmp_ok try { $row->varbinary_max }, 'eq', $str, 'VARBINARY(MAX) matches';
2edf3352	243	}
	244
	245	# test regular blobs
	246
	247	try { local $^W = 0; $schema->storage->dbh->do('DROP TABLE bindtype_test') };
	248	$schema->storage->dbh->do(qq[
	249	CREATE TABLE bindtype_test
	250	(
	251	id INT IDENTITY NOT NULL PRIMARY KEY,
	252	bytea INT NULL,
	253	blob IMAGE NULL,
74b5397c	254	blob2 IMAGE NULL,
2edf3352	255	clob TEXT NULL,
74b5397c	256	clob2 TEXT NULL,
2edf3352	257	a_memo NTEXT NULL
	258	)
	259	],{ RaiseError => 1, PrintError => 1 });
	260
	261	$rs = $schema->resultset('BindType');
	262	my $id = 0;
	263
	264	foreach my $type (qw( blob clob a_memo )) {
	265	foreach my $size (qw( small large )) {
	266	$id++;
	267
	268	lives_ok { $rs->create( { 'id' => $id, $type => $binstr{$size} } ) }
	269	"inserted $size $type without dying" or next;
	270
	271	my $from_db = eval { $rs->find($id)->$type } \|\| '';
	272	diag $@ if $@;
	273
	274	ok($from_db eq $binstr{$size}, "verified inserted $size $type" )
	275	or do {
	276	my $hexdump = sub {
	277	join '', map sprintf('%02X', ord), split //, shift
	278	};
	279	diag 'Got: ', "\n", substr($hexdump->($from_db),0,255), '...',
	280	substr($hexdump->($from_db),-255);
	281	diag 'Size: ', length($from_db);
	282	diag 'Expected Size: ', length($binstr{$size});
	283	diag 'Expected: ', "\n",
	284	substr($hexdump->($binstr{$size}), 0, 255),
	285	"...", substr($hexdump->($binstr{$size}),-255);
	286	};
	287	}
	288	}
	289	# test IMAGE update
	290	lives_ok {
	291	$rs->search({ id => 0 })->update({ blob => $binstr{small} });
	292	} 'updated IMAGE to small binstr without dying';
	293
	294	lives_ok {
	295	$rs->search({ id => 0 })->update({ blob => $binstr{large} });
	296	} 'updated IMAGE to large binstr without dying';
	297
	298	# test GUIDs
	299	lives_ok {
	300	$row = $schema->resultset('ArtistGUID')->create({ name => 'mtfnpy' })
	301	} 'created a row with a GUID';
	302
	303	ok(
	304	eval { $row->artistid },
	305	'row has GUID PK col populated',
	306	);
	307	diag $@ if $@;
	308
	309	my $guid = try { $row->artistid }\|\|'';
	310
	311	ok(($guid !~ /^{.*?}\z/), 'GUID not enclosed in braces')
	312	or diag "GUID is: $guid";
	313
	314	ok(
	315	eval { $row->a_guid },
	316	'row has a GUID col with auto_nextval populated',
	317	);
	318	diag $@ if $@;
	319
	320	my $row_from_db = $schema->resultset('ArtistGUID')
321	->search({ name => 'mtfnpy' })->first;
322
323	is try { $row_from_db->artistid }, try { $row->artistid },
324	'PK GUID round trip (via ->search->next)';
325
326	is try { $row_from_db->a_guid }, try { $row->a_guid },
327	'NON-PK GUID round trip (via ->search->next)';
328
329	$row_from_db = try { $schema->resultset('ArtistGUID')
330	->find($row->artistid) };
331
332	is try { $row_from_db->artistid }, try { $row->artistid },
333	'PK GUID round trip (via ->find)';
334
335	is try { $row_from_db->a_guid }, try { $row->a_guid },
336	'NON-PK GUID round trip (via ->find)';
337
338	($row_from_db) = $schema->resultset('ArtistGUID')
339	->search({ name => 'mtfnpy' })->all;
340
341	is try { $row_from_db->artistid }, try { $row->artistid },
342	'PK GUID round trip (via ->search->all)';
343
344	is try { $row_from_db->a_guid }, try { $row->a_guid },
345	'NON-PK GUID round trip (via ->search->all)';
346
347	lives_ok {
348	$row = $schema->resultset('ArtistGUID')->create({
349	artistid => '70171270-4822-4450-81DF-921F99BA3C06',
350	name => 'explicit_guid',
351	});
352	} 'created a row with explicit PK GUID';
353
354	is try { $row->artistid }, '70171270-4822-4450-81DF-921F99BA3C06',
355	'row has correct PK GUID';
356
357	lives_ok {
358	$row->update({ artistid => '70171270-4822-4450-81DF-921F99BA3C07' });
359	} "updated row's PK GUID";
360
361	is try { $row->artistid }, '70171270-4822-4450-81DF-921F99BA3C07',
362	'row has correct PK GUID';
363
364	lives_ok {
365	$row->delete;
366	} 'deleted the row';
367
368	lives_ok {
369	$schema->resultset('ArtistGUID')->populate([{
370	artistid => '70171270-4822-4450-81DF-921F99BA3C06',
371	name => 'explicit_guid',
372	}]);
373	} 'created a row with explicit PK GUID via ->populate in void context';
374
8bcd9ece	375	done_testing;
8bcd9ece	376
4ffa5700	377	# clean up our mess
4ffa5700	378	END {
2edf3352	379	local $SIG{__WARN__} = sub {};
	380	if (my $dbh = try { $schema->storage->_dbh }) {
	381	(try { $dbh->do("DROP TABLE $_") })
	382	for qw/artist artist_guid varying_max_test bindtype_test/;
4ffa5700	383	}
65d35121	384
65d35121	385	undef $schema;
4ffa5700	386	}
4ffa5700	387	# vim:sw=2 sts=2