Commit | Line | Data |
8b31f62e |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | use lib qw(t/lib); |
a5a7bb73 |
6 | use DBICTest ':DiffSQL'; |
8b31f62e |
7 | use DBIx::Class::SQLMaker::LimitDialects; |
8 | |
9 | my ($LIMIT, $OFFSET) = ( |
10 | DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype, |
11 | DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype, |
12 | ); |
13 | |
14 | my $schema = DBICTest->init_schema; |
15 | |
16 | $schema->storage->_sql_maker->limit_dialect ('SkipFirst'); |
17 | |
18 | my $rs_selectas_col = $schema->resultset ('BooksInLibrary')->search ({}, { |
19 | '+select' => ['owner.name'], |
20 | '+as' => ['owner.name'], |
21 | join => 'owner', |
22 | rows => 1, |
23 | offset => 2, |
24 | }); |
25 | |
26 | is_same_sql_bind( |
27 | $rs_selectas_col->as_query, |
28 | '( |
29 | SELECT SKIP ? FIRST ? me.id, me.source, me.owner, me.title, me.price, owner.name |
30 | FROM books me |
31 | JOIN owners owner ON owner.id = me.owner |
32 | WHERE ( source = ? ) |
33 | )', |
34 | [ |
35 | [ $OFFSET => 2 ], |
36 | [ $LIMIT => 1 ], |
37 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
38 | ], |
39 | ); |
40 | |
41 | $schema->storage->_sql_maker->quote_char ([qw/ [ ] /]); |
42 | $schema->storage->_sql_maker->name_sep ('.'); |
43 | |
44 | my $rs_selectas_rel = $schema->resultset ('BooksInLibrary')->search ({}, { |
45 | '+select' => ['owner.name'], |
46 | '+as' => ['owner_name'], |
47 | join => 'owner', |
48 | rows => 1, |
49 | offset => 2, |
50 | }); |
51 | |
52 | is_same_sql_bind( |
53 | $rs_selectas_rel->as_query, |
54 | '( |
55 | SELECT SKIP ? FIRST ? [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], [owner].[name] |
56 | FROM [books] [me] |
57 | JOIN [owners] [owner] ON [owner].[id] = [me].[owner] |
58 | WHERE ( [source] = ? ) |
59 | )', |
60 | [ |
61 | [ $OFFSET => 2 ], |
62 | [ $LIMIT => 1 ], |
63 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
64 | ], |
65 | ); |
66 | |
67 | { |
68 | my $subq = $schema->resultset('Owners')->search({ |
69 | 'count.id' => { -ident => 'owner.id' }, |
70 | 'count.name' => 'fail', # no one would do this in real life, the rows makes even less sense |
71 | }, { alias => 'owner', rows => 1 })->count_rs; |
72 | |
73 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, { |
74 | columns => [ |
75 | { owner_name => 'owner.name' }, |
76 | { owner_books => $subq->as_query }, |
77 | ], |
78 | join => 'owner', |
79 | rows => 1, |
80 | offset => 2, |
81 | }); |
82 | |
83 | is_same_sql_bind( |
84 | $rs_selectas_rel->as_query, |
85 | '( |
86 | SELECT SKIP ? FIRST ? |
87 | [owner].[name], |
88 | ( SELECT COUNT(*) FROM |
89 | ( SELECT FIRST ? [owner].[id] FROM [owners] [owner] |
8273e845 |
90 | WHERE [count].[id] = [owner].[id] and [count].[name] = ? |
8b31f62e |
91 | ) [owner] |
92 | ) |
93 | FROM [books] [me] |
94 | JOIN [owners] [owner] ON [owner].[id] = [me].[owner] |
95 | WHERE ( [source] = ? ) |
96 | )', |
97 | [ |
98 | [ $OFFSET => 2 ], # outer |
99 | [ $LIMIT => 1 ], # outer |
100 | [ {%$LIMIT} => 1 ], # inner |
101 | [ { dbic_colname => 'count.name' } => 'fail' ], |
102 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
103 | ], |
104 | ) |
105 | }; |
106 | |
107 | { |
108 | my $rs = $schema->resultset('Artist')->search({}, { |
109 | columns => 'name', |
110 | offset => 1, |
111 | order_by => 'name', |
112 | }); |
113 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
114 | |
115 | like ( |
116 | ${$rs->as_query}->[0], |
117 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
118 | 'Newlines/spaces preserved in final sql', |
119 | ); |
120 | } |
121 | |
122 | { |
123 | my $subq = $schema->resultset('Owners')->search({ |
124 | 'books.owner' => { -ident => 'owner.id' }, |
125 | }, { alias => 'owner', select => ['id'], offset => 3, rows => 4 }); |
126 | |
127 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1, offset => 2 } ); |
128 | |
129 | is_same_sql_bind( |
130 | $rs_selectas_rel->as_query, |
131 | '( |
132 | SELECT SKIP ? FIRST ? [me].[id], [me].[owner] |
133 | FROM [books] [me] |
134 | WHERE ( ( (EXISTS ( |
135 | SELECT SKIP ? FIRST ? [owner].[id] FROM [owners] [owner] WHERE ( [books].[owner] = [owner].[id] ) |
136 | )) AND [source] = ? ) ) |
137 | )', |
138 | [ |
139 | [ $OFFSET => 2 ], #outer |
140 | [ $LIMIT => 1 ], #outer |
141 | [ {%$OFFSET} => 3 ], #inner |
142 | [ {%$LIMIT} => 4 ], #inner |
143 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
144 | ], |
145 | 'Pagination with sub-query in WHERE works' |
146 | ); |
147 | |
148 | } |
149 | |
150 | |
151 | done_testing; |