Commit | Line | Data |
75f025cf |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
7 | use DBIC::SqlMakerTest; |
8 | |
9 | my $schema = DBICTest->init_schema; |
10 | |
11 | $schema->storage->_sql_maker->limit_dialect ('GenericSubQ'); |
12 | |
13 | my $rs = $schema->resultset ('BooksInLibrary')->search ({}, { |
14 | '+columns' => [{ owner_name => 'owner.name' }], |
15 | join => 'owner', |
16 | rows => 2, |
17 | order_by => 'me.title', |
18 | }); |
19 | |
20 | is_same_sql_bind( |
21 | $rs->as_query, |
22 | '( |
23 | SELECT id, source, owner, title, price, |
24 | owner_name |
25 | FROM ( |
26 | SELECT me.id, me.source, me.owner, me.title, me.price, |
27 | owner.name AS owner_name |
28 | FROM books me |
29 | JOIN owners owner ON owner.id = me.owner |
30 | WHERE ( source = ? ) |
75f025cf |
31 | ) me |
32 | WHERE |
33 | ( |
34 | SELECT COUNT(*) |
35 | FROM books rownum__emulation |
36 | WHERE rownum__emulation.title < me.title |
37 | ) < 2 |
b13d2248 |
38 | ORDER BY me.title |
75f025cf |
39 | )', |
0e773352 |
40 | [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
41 | => 'Library' ] ], |
75f025cf |
42 | ); |
43 | |
44 | is_deeply ( |
45 | [ $rs->get_column ('title')->all ], |
46 | ['Best Recipe Cookbook', 'Dynamical Systems'], |
47 | 'Correct columns selected with rows', |
48 | ); |
49 | |
50 | $schema->storage->_sql_maker->quote_char ('"'); |
51 | $schema->storage->_sql_maker->name_sep ('.'); |
52 | |
53 | $rs = $schema->resultset ('BooksInLibrary')->search ({}, { |
54 | order_by => { -desc => 'title' }, |
55 | '+select' => ['owner.name'], |
56 | '+as' => ['owner.name'], |
57 | join => 'owner', |
58 | rows => 3, |
59 | offset => 1, |
60 | }); |
61 | |
62 | is_same_sql_bind( |
63 | $rs->as_query, |
64 | '( |
65 | SELECT "id", "source", "owner", "title", "price", |
66 | "owner__name" |
67 | FROM ( |
68 | SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price", |
69 | "owner"."name" AS "owner__name" |
70 | FROM "books" "me" |
71 | JOIN "owners" "owner" ON "owner"."id" = "me"."owner" |
72 | WHERE ( "source" = ? ) |
75f025cf |
73 | ) "me" |
74 | WHERE |
75 | ( |
76 | SELECT COUNT(*) |
77 | FROM "books" "rownum__emulation" |
78 | WHERE "rownum__emulation"."title" > "me"."title" |
79 | ) BETWEEN 1 AND 3 |
b13d2248 |
80 | ORDER BY "title" DESC |
75f025cf |
81 | )', |
0e773352 |
82 | [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
83 | => 'Library' ] ], |
75f025cf |
84 | ); |
85 | |
86 | is_deeply ( |
87 | [ $rs->get_column ('title')->all ], |
88 | [ 'Dynamical Systems', 'Best Recipe Cookbook' ], |
89 | 'Correct columns selected with rows', |
90 | ); |
91 | |
92 | $rs = $schema->resultset ('BooksInLibrary')->search ({}, { |
93 | order_by => 'title', |
94 | 'select' => ['owner.name'], |
95 | 'as' => ['owner_name'], |
96 | join => 'owner', |
97 | offset => 1, |
98 | }); |
99 | |
100 | is_same_sql_bind( |
101 | $rs->as_query, |
102 | '( |
103 | SELECT "owner_name" |
104 | FROM ( |
105 | SELECT "owner"."name" AS "owner_name", "title" |
106 | FROM "books" "me" |
107 | JOIN "owners" "owner" ON "owner"."id" = "me"."owner" |
108 | WHERE ( "source" = ? ) |
75f025cf |
109 | ) "me" |
110 | WHERE |
111 | ( |
112 | SELECT COUNT(*) |
113 | FROM "books" "rownum__emulation" |
114 | WHERE "rownum__emulation"."title" < "me"."title" |
115 | ) BETWEEN 1 AND 4294967295 |
b13d2248 |
116 | ORDER BY "title" |
75f025cf |
117 | )', |
0e773352 |
118 | [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
119 | => 'Library' ] ], |
75f025cf |
120 | ); |
121 | |
122 | is_deeply ( |
123 | [ $rs->get_column ('owner_name')->all ], |
124 | [ ('Newton') x 2 ], |
125 | 'Correct columns selected with rows', |
126 | ); |
127 | |
d7632687 |
128 | { |
129 | $rs = $schema->resultset('Artist')->search({}, { |
130 | columns => 'name', |
131 | offset => 1, |
132 | order_by => 'name', |
133 | }); |
134 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
135 | |
136 | like ( |
137 | ${$rs->as_query}->[0], |
138 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
139 | 'Newlines/spaces preserved in final sql', |
140 | ); |
141 | } |
142 | |
75f025cf |
143 | done_testing; |