1use strict;
2use warnings;
3
4use Test::More;
5
6use lib qw(t/lib);
7use DBICTest ':DiffSQL';
8use DBIx::Class::_Util 'sigwarn_silencer';
9
10my $ROWS = DBIx::Class::SQLMaker::ClassicExtensions->__rows_bindtype;
11
12my $schema = DBICTest->init_schema();
13my $art_rs = $schema->resultset('Artist');
14my $cdrs = $schema->resultset('CD');
15
16my @tests = (
17  {
18    rs => $cdrs,
19    search => \[ "title = ? AND year LIKE ?", [ title => 'buahaha' ], [ year => '20%' ] ],
20    attrs => { rows => 5 },
21    sqlbind => \[
22      "( SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE (title = ? AND year LIKE ?) LIMIT ?)",
23      [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'title' } => 'buahaha' ],
24      [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'year' } => '20%' ],
25      [ $ROWS => 5 ],
26    ],
27  },
28
29  {
30    rs => $cdrs,
31    search => {
32      artistid => { 'in' => $art_rs->search({}, { rows => 1 })->get_column( 'artistid' )->as_query },
33    },
34    sqlbind => \[
35      "( SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE artistid IN ( SELECT me.artistid FROM artist me LIMIT ? ) )",
36      [ $ROWS => 1 ],
37    ],
38  },
39
40  {
41    rs => $art_rs,
42    attrs => {
43      'select' => [
44        $cdrs->search({}, { rows => 1 })->get_column('id')->as_query,
45      ],
46    },
47    sqlbind => \[
48      "( SELECT (SELECT me.id FROM cd me LIMIT ?) FROM artist me )",
49      [ $ROWS => 1 ],
50    ],
51  },
52
53  {
54    rs => $art_rs,
55    attrs => {
56      '+select' => [
57        $cdrs->search({}, { rows => 1 })->get_column('id')->as_query,
58      ],
59    },
60    sqlbind => \[
61      "( SELECT me.artistid, me.name, me.rank, me.charfield, (SELECT me.id FROM cd me LIMIT ?) FROM artist me )",
62      [ $ROWS => 1 ],
63    ],
64  },
65
66  {
67    rs => $cdrs,
68    attrs => {
69      alias => 'cd2',
70      from => [
71        { cd2 => $cdrs->search({ artist => { '>' => 20 } })->as_query },
72      ],
73    },
74    sqlbind => \[
75      "( SELECT cd2.cdid, cd2.artist, cd2.title, cd2.year, cd2.genreid, cd2.single_track FROM (
76            SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE artist > ?
77          ) cd2
78        )",
79      [ { sqlt_datatype => 'integer', dbic_colname => 'artist' } => 20 ]
80    ],
81  },
82
83  {
84    rs => $art_rs,
85    attrs => {
86      from => [
87        { 'me' => 'artist' },
88        [
89          { 'cds' => $cdrs->search({}, { 'select' => [\'me.artist as cds_artist' ]})->as_query },
90          { 'me.artistid' => 'cds_artist' }
91        ]
92      ]
93    },
94    sqlbind => \[
95      "( SELECT me.artistid, me.name, me.rank, me.charfield FROM artist me JOIN (SELECT me.artist as cds_artist FROM cd me) cds ON me.artistid = cds_artist )"
96    ],
97  },
98
99  {
100    rs => $cdrs,
101    attrs => {
102      alias => 'cd2',
103      from => [
104        { cd2 => $cdrs->search(
105            { artist => { '>' => 20 } },
106            {
107                alias => 'cd3',
108                from => [
109                { cd3 => $cdrs->search( { artist => { '<' => 40 } } )->as_query }
110                ],
111            }, )->as_query },
112      ],
113    },
114    sqlbind => \[
115      "( SELECT cd2.cdid, cd2.artist, cd2.title, cd2.year, cd2.genreid, cd2.single_track
116        FROM
117          (SELECT cd3.cdid, cd3.artist, cd3.title, cd3.year, cd3.genreid, cd3.single_track
118            FROM
119              (SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
120                FROM cd me WHERE artist < ?) cd3
121            WHERE artist > ?) cd2
122      )",
123      [ { sqlt_datatype => 'integer', dbic_colname => 'artist' } => 40 ],
124      [ { dbic_colname => 'artist' } => 20 ], # no rsrc in outer manual from - hence no resolution
125    ],
126  },
127
128  {
129    rs => $cdrs,
130    search => {
131      year => {
132        '=' => $cdrs->search(
133          { artistid => { '=' => \'me.artistid' } },
134          { alias => 'inner' }
135        )->get_column('year')->max_rs->as_query,
136      },
137    },
138    sqlbind => \[
139      "( SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE year = (SELECT MAX(inner.year) FROM cd inner WHERE artistid = me.artistid) )",
140    ],
141  },
142
143  {
144    rs => $cdrs,
145    attrs => {
146      alias => 'cd2',
147      from => [
148        { cd2 => $cdrs->search({ title => 'Thriller' })->as_query },
149      ],
150    },
151    sqlbind => \[
152      "(SELECT cd2.cdid, cd2.artist, cd2.title, cd2.year, cd2.genreid, cd2.single_track FROM (
153          SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE title = ?
154        ) cd2
155      )",
156      [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'title' }
157          => 'Thriller'
158      ]
159    ],
160  },
161);
162
163
164for my $i (0 .. $#tests) {
165  my $t = $tests[$i];
166  for my $p (1, 2) {  # repeat everything twice, make sure we do not clobber search arguments
167    local $SIG{__WARN__} = sigwarn_silencer( qr/\Q{from} structures with conditions not conforming to the SQL::Abstract::Classic syntax are deprecated/ );
168
169    is_same_sql_bind (
170      $t->{rs}->search ($t->{search}, $t->{attrs})->as_query,
171      $t->{sqlbind},
172      sprintf 'Testcase %d, pass %d', $i+1, $p,
173    );
174  }
175}
176
177done_testing;
178