1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
7 use DBICTest ':DiffSQL';
9 # the entire point of the subclass is that parenthesis have to be
10 # just right for ACCESS to be happy
11 # globalize for entirety of the test
12 $SQL::Abstract::Test::parenthesis_significant = 1;
14 my $schema = DBICTest->init_schema (storage_type => 'DBIx::Class::Storage::DBI::ACCESS', no_deploy => 1, quote_names => 1);
17 $schema->resultset('Artist')->search(
22 join => [{ cds => 'tracks' }],
23 '+select' => [ 'tracks.title' ],
24 '+as' => [ 'track_title' ],
28 SELECT [me].[artistid], [me].[name], [me].[rank], [me].[charfield],
34 ON [cds].[artist] = [me].[artistid]
36 LEFT JOIN [track] [tracks]
37 ON [tracks].[cd] = [cds].[cdid]
39 WHERE ( [artistid] = ? )
42 [{ sqlt_datatype => 'integer', dbic_colname => 'artistid' }
45 'correct SQL for two-step left join'
49 $schema->resultset('Track')->search(
54 join => [{ cd => 'artist' }],
55 '+select' => [ 'artist.name' ],
56 '+as' => [ 'artist_name' ],
60 SELECT [me].[trackid], [me].[cd], [me].[position], [me].[title], [me].[last_updated_on], [me].[last_updated_at],
66 ON [cd].[cdid] = [me].[cd]
68 INNER JOIN [artist] [artist]
69 ON [artist].[artistid] = [cd].[artist]
71 WHERE ( [trackid] = ? )
74 [{ sqlt_datatype => 'integer', dbic_colname => 'trackid' }
77 'correct SQL for two-step inner join',
81 my $sa = $schema->storage->sql_maker;
82 # the legacy tests assume no quoting - leave things as-is
83 local $sa->{quote_char};
85 # my ($self, $table, $fields, $where, $order, @rest) = @_;
86 my ($sql, @bind) = $sa->select(
90 { "-join_type" => "LEFT", artist => "artist" },
91 { "artist.artistid" => { -ident => "me.artist" } },
94 [ 'cd.cdid', 'cd.artist', 'cd.title', 'cd.year', 'artist.artistid', 'artist.name' ],
100 'SELECT cd.cdid, cd.artist, cd.title, cd.year, artist.artistid, artist.name FROM (cd me LEFT JOIN artist artist ON artist.artistid = me.artist)', [],
101 'one-step join parenthesized'
104 ($sql, @bind) = $sa->select(
108 { "-join_type" => "LEFT", track => "track" },
109 { "track.cd" => { -ident => "me.cdid" } },
112 { artist => "artist" },
113 { "artist.artistid" => { -ident => "me.artist" } },
116 [ 'track.title', 'cd.cdid', 'cd.artist', 'cd.title', 'cd.year', 'artist.artistid', 'artist.name' ],
122 'SELECT track.title, cd.cdid, cd.artist, cd.title, cd.year, artist.artistid, artist.name FROM ((cd me LEFT JOIN track track ON track.cd = me.cdid) INNER JOIN artist artist ON artist.artistid = me.artist)', [],
123 'two-step join parenthesized and inner join prepended with INNER'