[dbsrgits/DBIx-Class.git] / t / resultset / update_delete.t

use strict;
use warnings;

use lib qw(t/lib);
use lib qw(t/dqlib);
use Test::More;
use Test::Exception;

use DBICTest::Schema::CD;
BEGIN {
  # the default scalarref table name will not work well for this test
  DBICTest::Schema::CD->table('cd');
}

use DBICTest;
use DBIC::DebugObj;
use DBIC::SqlMakerTest;

my $schema = DBICTest->init_schema;

my ($sql, @bind);
my $debugobj = DBIC::DebugObj->new (\$sql, \@bind);
my $orig_debugobj = $schema->storage->debugobj;
my $orig_debug = $schema->storage->debug;

my $tkfks = $schema->resultset('FourKeys_to_TwoKeys');

my ($fa, $fb, $fc) = $tkfks->related_resultset ('fourkeys')->populate ([
  [qw/foo bar hello goodbye sensors read_count/],
  [qw/1   1   1     1       a       10         /],
  [qw/2   2   2     2       b       20         /],
  [qw/1   1   1     2       c       30         /],
]);

# This is already provided by DBICTest
#my ($ta, $tb) = $tkfk->related_resultset ('twokeys')->populate ([
#  [qw/artist  cd /],
#  [qw/1       1  /],
#  [qw/2       2  /],
#]);
my ($ta, $tb) = $schema->resultset ('TwoKeys')
                  ->search ( [ { artist => 1, cd => 1 }, { artist => 2, cd => 2 } ])
                    ->all;

my $tkfk_cnt = $tkfks->count;

my $non_void_ctx = $tkfks->populate ([
  { autopilot => 'a', fourkeys =>  $fa, twokeys => $ta, pilot_sequence => 10 },
  { autopilot => 'b', fourkeys =>  $fb, twokeys => $tb, pilot_sequence => 20 },
  { autopilot => 'x', fourkeys =>  $fa, twokeys => $tb, pilot_sequence => 30 },
  { autopilot => 'y', fourkeys =>  $fb, twokeys => $ta, pilot_sequence => 40 },
]);
is ($tkfks->count, $tkfk_cnt += 4, 'FourKeys_to_TwoKeys populated succesfully');

#
# Make sure the forced group by works (i.e. the joining does not cause double-updates)
#

# create a resultset matching $fa and $fb only
my $fks = $schema->resultset ('FourKeys')->search (
  {
    sensors => { '!=', 'c' },
    ( map { $_ => [1, 2] } qw/foo bar hello goodbye/ ),
  }, { join => 'fourkeys_to_twokeys'}
);

is ($fks->count, 4, 'Joined FourKey count correct (2x2)');

$schema->storage->debugobj ($debugobj);
$schema->storage->debug (1);
$fks->update ({ read_count => \ 'read_count + 1' });
$schema->storage->debugobj ($orig_debugobj);
$schema->storage->debug ($orig_debug);

is_same_sql_bind (
  $sql,
  \@bind,
  'UPDATE fourkeys
   SET read_count = read_count + 1
   WHERE ( ( ( bar = ? OR bar = ? ) AND ( foo = ? OR foo = ? ) AND ( goodbye = ? OR goodbye = ? ) AND ( hello = ? OR hello = ? ) AND sensors != ? ) )
  ',
  [ ("'1'", "'2'") x 4, "'c'" ],
  'Correct update-SQL with multijoin with pruning',
);

is ($fa->discard_changes->read_count, 11, 'Update ran only once on discard-join resultset');
is ($fb->discard_changes->read_count, 21, 'Update ran only once on discard-join resultset');
is ($fc->discard_changes->read_count, 30, 'Update did not touch outlier');

# make the multi-join stick
$fks = $fks->search({ 'fourkeys_to_twokeys.pilot_sequence' => { '!=' => 666 } });

$schema->storage->debugobj ($debugobj);
$schema->storage->debug (1);
$fks->update ({ read_count => \ 'read_count + 1' });
$schema->storage->debugobj ($orig_debugobj);
$schema->storage->debug ($orig_debug);

is_same_sql_bind (
  $sql,
  \@bind,
  'UPDATE fourkeys
   SET read_count = read_count + 1
   WHERE ( bar = ? AND foo = ? AND goodbye = ? AND hello = ? ) OR ( bar = ? AND foo = ? AND goodbye = ? AND hello = ? )',
  [ map { "'$_'" } ( (1) x 4, (2) x 4 ) ],
  'Correct update-SQL with multijoin without pruning',
);

is ($fa->discard_changes->read_count, 12, 'Update ran only once on joined resultset');
is ($fb->discard_changes->read_count, 22, 'Update ran only once on joined resultset');
is ($fc->discard_changes->read_count, 30, 'Update did not touch outlier');

# try the same sql with forced multicolumn in
$schema->storage->_use_multicolumn_in (1);
$schema->storage->debugobj ($debugobj);
$schema->storage->debug (1);
throws_ok { $fks->update ({ read_count => \ 'read_count + 1' }) } # this can't actually execute, we just need the "as_query"
  qr/\QDBI Exception:/ or do { $sql = ''; @bind = () };
$schema->storage->_use_multicolumn_in (undef);
$schema->storage->debugobj ($orig_debugobj);
$schema->storage->debug ($orig_debug);

is_same_sql_bind (
  $sql,
  \@bind,
  'UPDATE fourkeys
    SET read_count = read_count + 1
    WHERE (
      (foo, bar, hello, goodbye) IN (
        SELECT me.foo, me.bar, me.hello, me.goodbye
          FROM fourkeys me
          LEFT JOIN fourkeys_to_twokeys fourkeys_to_twokeys ON
                fourkeys_to_twokeys.f_bar = me.bar
            AND fourkeys_to_twokeys.f_foo = me.foo
            AND fourkeys_to_twokeys.f_goodbye = me.goodbye
            AND fourkeys_to_twokeys.f_hello = me.hello
        WHERE fourkeys_to_twokeys.pilot_sequence != ? AND ( bar = ? OR bar = ? ) AND ( foo = ? OR foo = ? ) AND ( goodbye = ? OR goodbye = ? ) AND ( hello = ? OR hello = ? ) AND sensors != ?
      )
    )
  ',
  [
    "'666'",
    ("'1'", "'2'") x 4,
    "'c'",
  ],
  'Correct update-SQL with multicolumn in support',
);

#
# Make sure multicolumn in or the equivalent functions correctly
#

my $sub_rs = $tkfks->search (
  [
    { map { $_ => 1 } qw/artist.artistid cd.cdid fourkeys.foo fourkeys.bar fourkeys.hello fourkeys.goodbye/ },
    { map { $_ => 2 } qw/artist.artistid cd.cdid fourkeys.foo fourkeys.bar fourkeys.hello fourkeys.goodbye/ },
  ],
  {
    join => [ 'fourkeys', { twokeys => [qw/artist cd/] } ],
  },
);

is ($sub_rs->count, 2, 'Only two rows from fourkeys match');

# attempts to delete a grouped rs should fail miserably
throws_ok (
  sub { $sub_rs->search ({}, { distinct => 1 })->delete },
  qr/attempted a delete operation on a resultset which does group_by/,
  'Grouped rs update/delete not allowed',
);

# grouping on PKs only should pass
$sub_rs->search (
  {},
  {
    group_by => [ reverse $sub_rs->result_source->primary_columns ],     # reverse to make sure the PK-list comparison works
  },
)->update ({ pilot_sequence => \ 'pilot_sequence + 1' });

is_deeply (
  [ $tkfks->search ({ autopilot => [qw/a b x y/]}, { order_by => 'autopilot' })
            ->get_column ('pilot_sequence')->all
  ],
  [qw/11 21 30 40/],
  'Only two rows incremented',
);

# also make sure weird scalarref usage works (RT#51409)
$tkfks->search (
  \ 'pilot_sequence BETWEEN 11 AND 21',
)->update ({ pilot_sequence => \ 'pilot_sequence + 1' });

is_deeply (
  [ $tkfks->search ({ autopilot => [qw/a b x y/]}, { order_by => 'autopilot' })
            ->get_column ('pilot_sequence')->all
  ],
  [qw/12 22 30 40/],
  'Only two rows incremented (where => scalarref works)',
);

{
  my $rs = $schema->resultset('FourKeys_to_TwoKeys')->search (
    {
      -or => [
        { 'me.pilot_sequence' => 12 },
        { 'me.autopilot'      => 'b' },
      ],
    }
  );
  lives_ok { $rs->update({ autopilot => 'z' }) }
    'Update with table name qualifier in -or conditions lives';
  is_deeply (
    [ $tkfks->search ({ pilot_sequence => [12, 22]})
              ->get_column ('autopilot')->all
    ],
    [qw/z z/],
    '... and yields the right data',
  );
}


$sub_rs->delete;
is ($tkfks->count, $tkfk_cnt -= 2, 'Only two rows deleted');

# make sure limit-only deletion works
cmp_ok ($tkfk_cnt, '>', 1, 'More than 1 row left');
$tkfks->search ({}, { rows => 1 })->delete;
is ($tkfks->count, $tkfk_cnt -= 1, 'Only one row deleted');


# check with sql-equality, as sqlite will accept most bad sql just fine
$schema->storage->debugobj ($debugobj);
$schema->storage->debug (1);

{
  my $rs = $schema->resultset('CD')->search(
    { 'me.year' => { '!=' => 2010 } },
  );

  $rs->search({}, { join => 'liner_notes' })->delete;
  is_same_sql_bind (
    $sql,
    \@bind,
    'DELETE FROM cd WHERE ( year != ? )',
    ["'2010'"],
    'Non-restricting multijoins properly thrown out'
  );

  $rs->search({}, { prefetch => 'liner_notes' })->delete;
  is_same_sql_bind (
    $sql,
    \@bind,
    'DELETE FROM cd WHERE ( year != ? )',
    ["'2010'"],
    'Non-restricting multiprefetch thrown out'
  );

  $rs->search({}, { prefetch => 'artist' })->delete;
  is_same_sql_bind (
    $sql,
    \@bind,
    'DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM cd me JOIN artist artist ON artist.artistid = me.artist WHERE ( me.year != ? ) ) )',
    ["'2010'"],
    'Restricting prefetch left in, selector thrown out'
  );

  # switch artist and cd to fully qualified table names
  # make sure nothing is stripped out
  my $cd_rsrc = $schema->source('CD');
  $cd_rsrc->name('main.cd');
  $cd_rsrc->relationship_info($_)->{attrs}{cascade_delete} = 0
    for $cd_rsrc->relationships;

  my $art_rsrc = $schema->source('Artist');
  $art_rsrc->name(\'main.artist');
  $art_rsrc->relationship_info($_)->{attrs}{cascade_delete} = 0
    for $art_rsrc->relationships;

  $rs->delete;
  is_same_sql_bind (
    $sql,
    \@bind,
    'DELETE FROM main.cd WHERE ( year != ? )',
    ["'2010'"],
    'delete with fully qualified table name'
  );

  $rs->create({ title => 'foo', artist => 1, year => 2000 });
  $rs->delete_all;
  is_same_sql_bind (
    $sql,
    \@bind,
    'DELETE FROM main.cd WHERE ( cdid = ? )',
    ["'1'"],
    'delete_all with fully qualified table name'
  );

  $rs->create({ cdid => 42, title => 'foo', artist => 2, year => 2000 });
  $rs->find(42)->delete;
  is_same_sql_bind (
    $sql,
    \@bind,
    'DELETE FROM main.cd WHERE ( cdid = ? )',
    ["'42'"],
    'delete of object from table with fully qualified name'
  );

  $rs->create({ cdid => 42, title => 'foo', artist => 2, year => 2000 });
  $rs->find(42)->related_resultset('artist')->delete;
  is_same_sql_bind (
    $sql,
    \@bind,
    'DELETE FROM main.artist WHERE ( artistid IN ( SELECT me.artistid FROM main.artist me WHERE ( me.artistid = ? ) ) )',
    ["'2'"],
    'delete of related object from scalarref fully qualified named table',
  );

  $schema->resultset('Artist')->find(3)->related_resultset('cds')->delete;
  is_same_sql_bind (
    $sql,
    \@bind,
    'DELETE FROM main.cd WHERE ( artist = ? )',
    ["'3'"],
    'delete of related object from fully qualified named table',
  );

  $schema->resultset('Artist')->find(3)->cds_unordered->delete;
  is_same_sql_bind (
    $sql,
    \@bind,
    'DELETE FROM main.cd WHERE ( artist = ? )',
    ["'3'"],
    'delete of related object from fully qualified named table via relaccessor',
  );

  $rs->search({}, { prefetch => 'artist' })->delete;
  is_same_sql_bind (
    $sql,
    \@bind,
    'DELETE FROM main.cd WHERE ( cdid IN ( SELECT me.cdid FROM main.cd me JOIN main.artist artist ON artist.artistid = me.artist WHERE ( me.year != ? ) ) )',
    ["'2010'"],
    'delete with fully qualified table name and subquery correct'
  );

  # check that as_subselect_rs works ok
  # inner query is untouched, then a selector
  # and an IN condition
  $schema->resultset('CD')->search({
    'me.cdid' => 1,
    'artist.name' => 'partytimecity',
  }, {
    join => 'artist',
  })->as_subselect_rs->delete;

  is_same_sql_bind (
    $sql,
    \@bind,
    '
      DELETE FROM main.cd
      WHERE (
        cdid IN (
          SELECT me.cdid
            FROM (
              SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
                FROM main.cd me
                JOIN main.artist artist ON artist.artistid = me.artist
              WHERE artist.name = ? AND me.cdid = ?
            ) me
        )
      )
    ',
    ["'partytimecity'", "'1'"],
    'Delete from as_subselect_rs works correctly'
  );
}

$schema->storage->debugobj ($orig_debugobj);
$schema->storage->debug ($orig_debug);

done_testing;
Commit	Line	Data
ff1234ad	1	use strict;
	2	use warnings;
	3
	4	use lib qw(t/lib);
c5ffac15	5	use lib qw(t/dqlib);
ff1234ad	6	use Test::More;
ff1234ad	7	use Test::Exception;
31073ac7	8
	9	use DBICTest::Schema::CD;
	10	BEGIN {
	11	# the default scalarref table name will not work well for this test
	12	DBICTest::Schema::CD->table('cd');
	13	}
	14
ff1234ad	15	use DBICTest;
887d8da0	16	use DBIC::DebugObj;
887d8da0	17	use DBIC::SqlMakerTest;
ff1234ad	18
be64931c	19	my $schema = DBICTest->init_schema;
	20
	21	my ($sql, @bind);
	22	my $debugobj = DBIC::DebugObj->new (\$sql, \@bind);
	23	my $orig_debugobj = $schema->storage->debugobj;
	24	my $orig_debug = $schema->storage->debug;
ff1234ad	25
	26	my $tkfks = $schema->resultset('FourKeys_to_TwoKeys');
	27
fd8076c8	28	my ($fa, $fb, $fc) = $tkfks->related_resultset ('fourkeys')->populate ([
ff1234ad	29	[qw/foo bar hello goodbye sensors read_count/],
	30	[qw/1 1 1 1 a 10 /],
	31	[qw/2 2 2 2 b 20 /],
fd8076c8	32	[qw/1 1 1 2 c 30 /],
ff1234ad	33	]);
	34
	35	# This is already provided by DBICTest
	36	#my ($ta, $tb) = $tkfk->related_resultset ('twokeys')->populate ([
	37	# [qw/artist cd /],
	38	# [qw/1 1 /],
	39	# [qw/2 2 /],
	40	#]);
	41	my ($ta, $tb) = $schema->resultset ('TwoKeys')
	42	->search ( [ { artist => 1, cd => 1 }, { artist => 2, cd => 2 } ])
	43	->all;
	44
	45	my $tkfk_cnt = $tkfks->count;
	46
	47	my $non_void_ctx = $tkfks->populate ([
	48	{ autopilot => 'a', fourkeys => $fa, twokeys => $ta, pilot_sequence => 10 },
	49	{ autopilot => 'b', fourkeys => $fb, twokeys => $tb, pilot_sequence => 20 },
	50	{ autopilot => 'x', fourkeys => $fa, twokeys => $tb, pilot_sequence => 30 },
	51	{ autopilot => 'y', fourkeys => $fb, twokeys => $ta, pilot_sequence => 40 },
	52	]);
	53	is ($tkfks->count, $tkfk_cnt += 4, 'FourKeys_to_TwoKeys populated succesfully');
	54
	55	#
	56	# Make sure the forced group by works (i.e. the joining does not cause double-updates)
	57	#
	58
	59	# create a resultset matching $fa and $fb only
fd8076c8	60	my $fks = $schema->resultset ('FourKeys')->search (
	61	{
	62	sensors => { '!=', 'c' },
	63	( map { $_ => [1, 2] } qw/foo bar hello goodbye/ ),
	64	}, { join => 'fourkeys_to_twokeys'}
	65	);
ff1234ad	66
ff1234ad	67	is ($fks->count, 4, 'Joined FourKey count correct (2x2)');
be64931c	68
	69	$schema->storage->debugobj ($debugobj);
	70	$schema->storage->debug (1);
ff1234ad	71	$fks->update ({ read_count => \ 'read_count + 1' });
be64931c	72	$schema->storage->debugobj ($orig_debugobj);
be64931c	73	$schema->storage->debug ($orig_debug);
ff1234ad	74
be64931c	75	is_same_sql_bind (
	76	$sql,
	77	\@bind,
	78	'UPDATE fourkeys
	79	SET read_count = read_count + 1
fd8076c8	80	WHERE ( ( ( bar = ? OR bar = ? ) AND ( foo = ? OR foo = ? ) AND ( goodbye = ? OR goodbye = ? ) AND ( hello = ? OR hello = ? ) AND sensors != ? ) )
	81	',
	82	[ ("'1'", "'2'") x 4, "'c'" ],
	83	'Correct update-SQL with multijoin with pruning',
	84	);
	85
	86	is ($fa->discard_changes->read_count, 11, 'Update ran only once on discard-join resultset');
	87	is ($fb->discard_changes->read_count, 21, 'Update ran only once on discard-join resultset');
	88	is ($fc->discard_changes->read_count, 30, 'Update did not touch outlier');
	89
	90	# make the multi-join stick
	91	$fks = $fks->search({ 'fourkeys_to_twokeys.pilot_sequence' => { '!=' => 666 } });
	92
	93	$schema->storage->debugobj ($debugobj);
	94	$schema->storage->debug (1);
	95	$fks->update ({ read_count => \ 'read_count + 1' });
	96	$schema->storage->debugobj ($orig_debugobj);
	97	$schema->storage->debug ($orig_debug);
	98
	99	is_same_sql_bind (
	100	$sql,
	101	\@bind,
	102	'UPDATE fourkeys
	103	SET read_count = read_count + 1
be64931c	104	WHERE ( bar = ? AND foo = ? AND goodbye = ? AND hello = ? ) OR ( bar = ? AND foo = ? AND goodbye = ? AND hello = ? )',
be64931c	105	[ map { "'$_'" } ( (1) x 4, (2) x 4 ) ],
fd8076c8	106	'Correct update-SQL with multijoin without pruning',
be64931c	107	);
ff1234ad	108
fd8076c8	109	is ($fa->discard_changes->read_count, 12, 'Update ran only once on joined resultset');
	110	is ($fb->discard_changes->read_count, 22, 'Update ran only once on joined resultset');
	111	is ($fc->discard_changes->read_count, 30, 'Update did not touch outlier');
be64931c	112
	113	# try the same sql with forced multicolumn in
	114	$schema->storage->_use_multicolumn_in (1);
	115	$schema->storage->debugobj ($debugobj);
	116	$schema->storage->debug (1);
66137dff	117	throws_ok { $fks->update ({ read_count => \ 'read_count + 1' }) } # this can't actually execute, we just need the "as_query"
eba59b7e	118	qr/\QDBI Exception:/ or do { $sql = ''; @bind = () };
be64931c	119	$schema->storage->_use_multicolumn_in (undef);
	120	$schema->storage->debugobj ($orig_debugobj);
	121	$schema->storage->debug ($orig_debug);
	122
	123	is_same_sql_bind (
	124	$sql,
	125	\@bind,
	126	'UPDATE fourkeys
	127	SET read_count = read_count + 1
	128	WHERE (
	129	(foo, bar, hello, goodbye) IN (
	130	SELECT me.foo, me.bar, me.hello, me.goodbye
	131	FROM fourkeys me
fd8076c8	132	LEFT JOIN fourkeys_to_twokeys fourkeys_to_twokeys ON
	133	fourkeys_to_twokeys.f_bar = me.bar
	134	AND fourkeys_to_twokeys.f_foo = me.foo
	135	AND fourkeys_to_twokeys.f_goodbye = me.goodbye
	136	AND fourkeys_to_twokeys.f_hello = me.hello
	137	WHERE fourkeys_to_twokeys.pilot_sequence != ? AND ( bar = ? OR bar = ? ) AND ( foo = ? OR foo = ? ) AND ( goodbye = ? OR goodbye = ? ) AND ( hello = ? OR hello = ? ) AND sensors != ?
be64931c	138	)
	139	)
	140	',
fd8076c8	141	[
	142	"'666'",
	143	("'1'", "'2'") x 4,
	144	"'c'",
	145	],
be64931c	146	'Correct update-SQL with multicolumn in support',
be64931c	147	);
ff1234ad	148
ff1234ad	149	#
be64931c	150	# Make sure multicolumn in or the equivalent functions correctly
ff1234ad	151	#
	152
	153	my $sub_rs = $tkfks->search (
	154	[
	155	{ map { $_ => 1 } qw/artist.artistid cd.cdid fourkeys.foo fourkeys.bar fourkeys.hello fourkeys.goodbye/ },
	156	{ map { $_ => 2 } qw/artist.artistid cd.cdid fourkeys.foo fourkeys.bar fourkeys.hello fourkeys.goodbye/ },
	157	],
	158	{
	159	join => [ 'fourkeys', { twokeys => [qw/artist cd/] } ],
	160	},
	161	);
	162
	163	is ($sub_rs->count, 2, 'Only two rows from fourkeys match');
	164
	165	# attempts to delete a grouped rs should fail miserably
	166	throws_ok (
	167	sub { $sub_rs->search ({}, { distinct => 1 })->delete },
	168	qr/attempted a delete operation on a resultset which does group_by/,
	169	'Grouped rs update/delete not allowed',
	170	);
	171
	172	# grouping on PKs only should pass
af668ad6	173	$sub_rs->search (
	174	{},
	175	{
be64931c	176	group_by => [ reverse $sub_rs->result_source->primary_columns ], # reverse to make sure the PK-list comparison works
af668ad6	177	},
af668ad6	178	)->update ({ pilot_sequence => \ 'pilot_sequence + 1' });
ff1234ad	179
	180	is_deeply (
	181	[ $tkfks->search ({ autopilot => [qw/a b x y/]}, { order_by => 'autopilot' })
8273e845	182	->get_column ('pilot_sequence')->all
ff1234ad	183	],
	184	[qw/11 21 30 40/],
	185	'Only two rows incremented',
	186	);
	187
af668ad6	188	# also make sure weird scalarref usage works (RT#51409)
	189	$tkfks->search (
	190	\ 'pilot_sequence BETWEEN 11 AND 21',
	191	)->update ({ pilot_sequence => \ 'pilot_sequence + 1' });
	192
	193	is_deeply (
	194	[ $tkfks->search ({ autopilot => [qw/a b x y/]}, { order_by => 'autopilot' })
8273e845	195	->get_column ('pilot_sequence')->all
af668ad6	196	],
	197	[qw/12 22 30 40/],
	198	'Only two rows incremented (where => scalarref works)',
	199	);
	200
59ac6523	201	{
	202	my $rs = $schema->resultset('FourKeys_to_TwoKeys')->search (
	203	{
	204	-or => [
	205	{ 'me.pilot_sequence' => 12 },
	206	{ 'me.autopilot' => 'b' },
	207	],
	208	}
	209	);
	210	lives_ok { $rs->update({ autopilot => 'z' }) }
	211	'Update with table name qualifier in -or conditions lives';
	212	is_deeply (
	213	[ $tkfks->search ({ pilot_sequence => [12, 22]})
	214	->get_column ('autopilot')->all
	215	],
	216	[qw/z z/],
	217	'... and yields the right data',
	218	);
	219	}
	220
	221
ff1234ad	222	$sub_rs->delete;
ff1234ad	223	is ($tkfks->count, $tkfk_cnt -= 2, 'Only two rows deleted');
fef47a8e	224
	225	# make sure limit-only deletion works
	226	cmp_ok ($tkfk_cnt, '>', 1, 'More than 1 row left');
	227	$tkfks->search ({}, { rows => 1 })->delete;
	228	is ($tkfks->count, $tkfk_cnt -= 1, 'Only one row deleted');
	229
887d8da0	230
fd8076c8	231	# check with sql-equality, as sqlite will accept most bad sql just fine
be64931c	232	$schema->storage->debugobj ($debugobj);
887d8da0	233	$schema->storage->debug (1);
fd8076c8	234
	235	{
	236	my $rs = $schema->resultset('CD')->search(
	237	{ 'me.year' => { '!=' => 2010 } },
	238	);
	239
	240	$rs->search({}, { join => 'liner_notes' })->delete;
	241	is_same_sql_bind (
	242	$sql,
	243	\@bind,
	244	'DELETE FROM cd WHERE ( year != ? )',
	245	["'2010'"],
	246	'Non-restricting multijoins properly thrown out'
	247	);
	248
	249	$rs->search({}, { prefetch => 'liner_notes' })->delete;
	250	is_same_sql_bind (
	251	$sql,
	252	\@bind,
	253	'DELETE FROM cd WHERE ( year != ? )',
	254	["'2010'"],
	255	'Non-restricting multiprefetch thrown out'
	256	);
	257
	258	$rs->search({}, { prefetch => 'artist' })->delete;
	259	is_same_sql_bind (
	260	$sql,
	261	\@bind,
	262	'DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM cd me JOIN artist artist ON artist.artistid = me.artist WHERE ( me.year != ? ) ) )',
	263	["'2010'"],
	264	'Restricting prefetch left in, selector thrown out'
	265	);
	266
554f3621	267	# switch artist and cd to fully qualified table names
	268	# make sure nothing is stripped out
	269	my $cd_rsrc = $schema->source('CD');
	270	$cd_rsrc->name('main.cd');
	271	$cd_rsrc->relationship_info($_)->{attrs}{cascade_delete} = 0
	272	for $cd_rsrc->relationships;
	273
	274	my $art_rsrc = $schema->source('Artist');
	275	$art_rsrc->name(\'main.artist');
	276	$art_rsrc->relationship_info($_)->{attrs}{cascade_delete} = 0
	277	for $art_rsrc->relationships;
	278
	279	$rs->delete;
fd8076c8	280	is_same_sql_bind (
	281	$sql,
	282	\@bind,
554f3621	283	'DELETE FROM main.cd WHERE ( year != ? )',
fd8076c8	284	["'2010'"],
554f3621	285	'delete with fully qualified table name'
	286	);
	287
	288	$rs->create({ title => 'foo', artist => 1, year => 2000 });
	289	$rs->delete_all;
	290	is_same_sql_bind (
	291	$sql,
	292	\@bind,
	293	'DELETE FROM main.cd WHERE ( cdid = ? )',
	294	["'1'"],
	295	'delete_all with fully qualified table name'
	296	);
	297
	298	$rs->create({ cdid => 42, title => 'foo', artist => 2, year => 2000 });
	299	$rs->find(42)->delete;
	300	is_same_sql_bind (
	301	$sql,
	302	\@bind,
	303	'DELETE FROM main.cd WHERE ( cdid = ? )',
	304	["'42'"],
	305	'delete of object from table with fully qualified name'
	306	);
	307
	308	$rs->create({ cdid => 42, title => 'foo', artist => 2, year => 2000 });
	309	$rs->find(42)->related_resultset('artist')->delete;
	310	is_same_sql_bind (
	311	$sql,
	312	\@bind,
	313	'DELETE FROM main.artist WHERE ( artistid IN ( SELECT me.artistid FROM main.artist me WHERE ( me.artistid = ? ) ) )',
	314	["'2'"],
	315	'delete of related object from scalarref fully qualified named table',
	316	);
	317
	318	$schema->resultset('Artist')->find(3)->related_resultset('cds')->delete;
	319	is_same_sql_bind (
	320	$sql,
	321	\@bind,
	322	'DELETE FROM main.cd WHERE ( artist = ? )',
	323	["'3'"],
	324	'delete of related object from fully qualified named table',
fd8076c8	325	);
fd8076c8	326
554f3621	327	$schema->resultset('Artist')->find(3)->cds_unordered->delete;
fd8076c8	328	is_same_sql_bind (
	329	$sql,
	330	\@bind,
554f3621	331	'DELETE FROM main.cd WHERE ( artist = ? )',
	332	["'3'"],
	333	'delete of related object from fully qualified named table via relaccessor',
	334	);
	335
	336	$rs->search({}, { prefetch => 'artist' })->delete;
	337	is_same_sql_bind (
	338	$sql,
	339	\@bind,
	340	'DELETE FROM main.cd WHERE ( cdid IN ( SELECT me.cdid FROM main.cd me JOIN main.artist artist ON artist.artistid = me.artist WHERE ( me.year != ? ) ) )',
fd8076c8	341	["'2010'"],
	342	'delete with fully qualified table name and subquery correct'
	343	);
	344
31073ac7	345	# check that as_subselect_rs works ok
	346	# inner query is untouched, then a selector
	347	# and an IN condition
	348	$schema->resultset('CD')->search({
	349	'me.cdid' => 1,
	350	'artist.name' => 'partytimecity',
	351	}, {
	352	join => 'artist',
	353	})->as_subselect_rs->delete;
	354
	355	is_same_sql_bind (
	356	$sql,
	357	\@bind,
	358	'
554f3621	359	DELETE FROM main.cd
31073ac7	360	WHERE (
	361	cdid IN (
	362	SELECT me.cdid
	363	FROM (
	364	SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
554f3621	365	FROM main.cd me
554f3621	366	JOIN main.artist artist ON artist.artistid = me.artist
31073ac7	367	WHERE artist.name = ? AND me.cdid = ?
	368	) me
	369	)
	370	)
	371	',
	372	["'partytimecity'", "'1'"],
	373	'Delete from as_subselect_rs works correctly'
	374	);
fd8076c8	375	}
887d8da0	376
47980c14	377	$schema->storage->debugobj ($orig_debugobj);
	378	$schema->storage->debug ($orig_debug);
	379
fef47a8e	380	done_testing;