File Coverage

blib/lib/JsonSQL/Query/Select.pm
Criterion Covered Total %
statement 112 150 74.6
branch 38 64 59.3
condition 7 9 77.7
subroutine 15 15 100.0
pod 2 2 100.0
total 174 240 72.5


line stmt bran cond sub pod time code
1             # ABSTRACT: JsonSQL::Query::Select object. Stores a Perl representation of a SELECT statement created from a JSON string.
2              
3              
4              
5 1     1   359576 use strict;
  1         3  
  1         25  
6 1     1   4 use warnings;
  1         2  
  1         24  
7 1     1   16 use 5.014;
  1         3  
8              
9             package JsonSQL::Query::Select;
10              
11             our $VERSION = '0.4'; # VERSION
12              
13 1     1   5 use base qw( JsonSQL::Query::Query );
  1         1  
  1         306  
14              
15 1     1   9 use JsonSQL::Validator;
  1         4  
  1         31  
16 1     1   9 use JsonSQL::Error;
  1         2  
  1         18  
17 1     1   255 use JsonSQL::Param::Fields;
  1         2  
  1         22  
18 1     1   5 use JsonSQL::Param::Field;
  1         2  
  1         14  
19 1     1   264 use JsonSQL::Param::Tables;
  1         3  
  1         40  
20 1     1   279 use JsonSQL::Param::Joins;
  1         4  
  1         42  
21 1     1   297 use JsonSQL::Param::ConditionDispatcher;
  1         23  
  1         41  
22 1     1   326 use JsonSQL::Param::OrderBy;
  1         4  
  1         56  
23              
24             # Using this as a crutch for now, but will deprecate at some point.
25 1     1   324 use SQL::Maker::Select;
  1         7353  
  1         1490  
26              
27             #use Data::Dumper;
28             #use constant DEBUG => 0; # toggle me
29              
30              
31              
32             sub new {
33 5     5 1 3779 my ( $class, $query_rulesets, $json_query ) = @_;
34            
35             # Inherit from JsonSQL::Query::Query base class.
36 5         34 my $self = $class->SUPER::new($query_rulesets, 'select');
37 5 50       13 if ( eval { $self->is_error } ) {
  5         58  
38 0         0 return (0, "Could not create JsonSQL SELECT query object: $self->{message}");
39             }
40            
41             # Validate the $json_query to make sure it conforms to the 'select' JSON schema.
42 5         114 my $validator = $self->{_validator};
43 5         23 my $selecthashref = $validator->validate_schema($json_query);
44 5 50       14 if ( eval { $selecthashref->is_error } ) {
  5         80  
45 0         0 return (0, $selecthashref->{message});
46             }
47            
48             # Save the default DB schema to use, if one is provided.
49 5 100       193 if ( defined $selecthashref->{defaultschema} ) {
50 1         5 $self->{_defaultSchema} = $selecthashref->{defaultschema};
51             }
52            
53             # For our purposes, a minimum SELECT query must have at least one FROM, which can be specified on its own or as part of a JOIN.
54 5 100       21 if ( defined $selecthashref->{from} ) {
55 4         36 my $selectfrom = JsonSQL::Param::Tables->new($selecthashref->{from}, $self);
56 4 50       7 if ( eval { $selectfrom->is_error } ) {
  4         95  
57 0         0 return (0, $selectfrom->{message});
58             } else {
59 4         104 $self->{_selectFrom} = $selectfrom;
60             }
61             }
62            
63 5 100       17 if ( defined $selecthashref->{joins} ) {
64 1         13 my $selectjoins = JsonSQL::Param::Joins->new($selecthashref->{joins}, $self);
65 1 50       3 if ( eval { $selectjoins->is_error } ) {
  1         14  
66 0         0 return (0, $selectjoins->{message});
67             } else {
68 1         31 $self->{_selectJoins} = $selectjoins;
69             }
70             }
71            
72 5   100     27 my $fromExists = ( defined $self->{_selectFrom} && scalar @{ $self->{_selectFrom} } );
73 5   100     44 my $joinsExist = ( defined $self->{_selectJoins} && scalar @{ $self->{_selectJoins} } );
74 5 50 66     24 unless ( $fromExists or $joinsExist ) {
75 0         0 return (0, "No valid from_items for SELECT statement.");
76             }
77            
78             # Although it is not recommended, column identifiers can be specified without a table param for simple queries.
79             # In these limited cases where there is no ambiguity, we take the from_item as the table param for the column.
80             # Unfortunately, there is no clean way to support this for JOINs. So, column identifiers that are part of JOIN
81             # conditions must be fully qualified, or else they will probably fail the whitelisting check.
82 5         13 my $default_table_rules = [];
83 5 100       18 if ( $fromExists ) {
84             # Note: we are taking just the first from_item in the list. So, whitelisting checks will probably fail if
85             # there is more than one FROM table. Best to use fully qualified column identifiers in this case.
86 4         13 $default_table_rules = $self->{_selectFrom}->[0]->{_tableRules};
87             }
88            
89             # For our purposes, a minimum SELECT query must have field expressions defined.
90 5         71 my $selectfields = JsonSQL::Param::Fields->new($selecthashref->{fields}, $self, $default_table_rules);
91 5 100       11 if ( eval { $selectfields->is_error } ) {
  5         64  
92 1         67 return (0, $selectfields->{message});
93             }
94            
95 4 50       107 if ( @{ $selectfields } ) {
  4         14  
96 4         10 $self->{_selectFields} = $selectfields;
97             } else {
98 0         0 return (0, "No valid field expressions for SELECT statement.");
99             }
100            
101             # The rest of the parameters are optional, but we still break on parsing errors.
102 4         8 my @select_errors;
103            
104 4   50     20 $self->{_selectDistinct} = $selecthashref->{distinct} || 'false';
105            
106 4 100       15 if ( defined $selecthashref->{where} ) {
107 1         13 my $selectwhere = JsonSQL::Param::ConditionDispatcher->parse($selecthashref->{where}, $self, $default_table_rules);
108 1 50       4 if ( eval { $selectwhere->is_error } ) {
  1         12  
109 0         0 push(@select_errors, "Error creating WHERE clause: $selectwhere->{message}");
110             } else {
111 1         35 $self->{_selectWhere} = $selectwhere;
112             }
113             }
114              
115 4 50       16 if ( defined $selecthashref->{groupby} ) {
116 0         0 my $selectgroupby = JsonSQL::Param::Fields->new($selecthashref->{groupby}, $self, $default_table_rules);
117 0 0       0 if ( eval { $selectgroupby->is_error } ) {
  0         0  
118 0         0 push(@select_errors, "Error creating GROUP BY clause: $selectgroupby->{message}");
119             } else {
120 0         0 $self->{_selectGroupBy} = $selectgroupby;
121             }
122             }
123              
124 4 50       13 if ( defined $selecthashref->{having} ) {
125 0         0 my $selecthaving = JsonSQL::Param::ConditionDispatcher->parse($selecthashref->{having}, $self, $default_table_rules);
126 0 0       0 if ( eval { $selecthaving->is_error } ) {
  0         0  
127 0         0 push(@select_errors, "Error creating HAVING clause: $selecthaving->{message}");
128             } else {
129 0         0 $self->{_selectHaving} = $selecthaving;
130             }
131             }
132              
133 4 50       10 if (defined $selecthashref->{orderby}) {
134 0         0 my $selectorderby = JsonSQL::Param::OrderBy->new($selecthashref->{orderby}, $self, $default_table_rules);
135 0 0       0 if ( eval { $selectorderby->is_error } ) {
  0         0  
136 0         0 push(@select_errors, "Error creating ORDER BY clause: $selectorderby->{message}");
137             } else {
138 0         0 $self->{_selectOrderBy} = $selectorderby;
139             }
140             }
141              
142 4 50       11 if (defined $selecthashref->{limit}) {
143 0         0 $self->{_selectLimit} = $selecthashref->{limit};
144             }
145            
146 4 50       11 if (defined $selecthashref->{offset}) {
147 0         0 $self->{_selectOffset} = $selecthashref->{offset};
148             }
149            
150 4 50       11 if ( @select_errors ) {
151 0         0 my $err = "Error(s) parsing some SELECT parameters: \n\t";
152 0         0 $err .= join("\n\t", @select_errors);
153 0         0 return (0, $err);
154             } else {
155 4         12 bless $self, $class;
156 4         42 return $self;
157             }
158             }
159              
160              
161             sub get_select {
162 4     4 1 2164 my $self = shift;
163            
164 4         29 my $makerObj = SQL::Maker::Select->new(quote_char => "'");
165            
166 4         92 for my $field (@{ $self->{_selectFields}->get_fields($self) }) {
  4         20  
167 6 100       53 if (ref $field eq 'HASH') {
168 1         6 $makerObj->add_select(%$field);
169             } else {
170 5         20 $makerObj->add_select($field);
171             }
172             }
173            
174 4 100       76 if (defined $self->{_selectFrom}) {
175 3         6 for my $from (@{ $self->{_selectFrom}->get_tables($self) }) {
  3         12  
176 3         11 $makerObj->add_from($from);
177             }
178             }
179              
180 4 100       46 if (defined $self->{_selectJoins}) {
181 1         2 for my $join (@{ $self->{_selectJoins}->get_joins($self) }) {
  1         6  
182 1         7 $makerObj->add_join(%$join);
183             }
184             }
185            
186 4 100       32 if (defined $self->{_selectWhere} ) {
187 1         8 my ($sql, @binds) = $self->{_selectWhere}->get_cond($self);
188 1         7 $makerObj->add_where_raw($sql, @binds);
189             }
190              
191 4 50       100 if (defined $self->{_selectGroupBy}) {
192 0         0 for my $grouping (@{ $self->{_selectGroupBy}->get_fields($self) }) {
  0         0  
193 0         0 $makerObj->add_group_by($grouping);
194             }
195             }
196              
197             ## SQL::Maker doesn't support this at the moment, so leaving disabled.
198             # if (defined $self->{_selectHaving}) {
199             # $makerObj->add_having($self->{_selectHaving}->get_sql_obj);
200             # }
201              
202 4 50       15 if (defined $self->{_selectOrderBy}) {
203 0         0 for my $ordering (@{ $self->{_selectOrderBy}->get_ordering($self) }) {
  0         0  
204             #print "Ref: " . ref($ordering) . "\n";
205 0 0       0 if ( ref($ordering) eq 'ARRAY' ) {
206             #print "@$ordering\n";
207 0         0 $makerObj->add_order_by(@$ordering);
208             } else {
209             #print "$ordering\n";
210 0         0 $makerObj->add_order_by($ordering);
211             }
212             #print Dumper($ordering);
213             }
214             #die;
215             }
216            
217 4 50       11 if (defined $self->{_selectLimit}) {
218 0         0 $makerObj->limit($self->{_selectLimit});
219             }
220            
221 4 50       18 if (defined $self->{_selectOffset}) {
222 0         0 $makerObj->offset($self->{_selectOffset});
223             }
224            
225 4         15 my $sql = $makerObj->as_sql;
226              
227             ## SMELL: Hack to add support for SELECT DISTINCT
228 4 50       802 if ( $self->{_selectDistinct} eq 'true' ) {
229 0         0 $sql =~ s/SELECT/SELECT DISTINCT/;
230             }
231            
232 4         16 my @binds = $makerObj->bind;
233            
234 4         88 return ($sql, \@binds);
235             }
236              
237              
238             1;
239              
240             __END__
241              
242             =pod
243              
244             =encoding UTF-8
245              
246             =head1 NAME
247              
248             JsonSQL::Query::Select - JsonSQL::Query::Select object. Stores a Perl representation of a SELECT statement created from a JSON string.
249              
250             =head1 VERSION
251              
252             version 0.4
253              
254             =head1 SYNOPSIS
255              
256             Use this to generate an SQL SELECT statement from a JSON string.
257              
258             To use this:
259              
260             use JsonSQL::Query::Select;
261            
262             my $jsonString = '{
263             "fields": [
264             {"column": "*"}
265             ],
266             "from": [
267             {"table": "my_table"}
268             ]
269             }';
270            
271             my $whitelisting_rules = [
272             { schema => '#anySchema', 'my_table' => [ '#anyColumn' ] }
273             ];
274            
275             my ( $selectObj, $err ) = JsonSQL::Query::Select->new($whitelisting_rules, $jsonString);
276             if ( $selectObj ) {
277             my ( $sql, $binds ) = $selectObj->get_select;
278             <...>
279             } else {
280             die $err;
281             }
282              
283             Now you can go ahead and use $sql and $binds directly with the L<DBI> module to do the query.
284              
285             =head1 DESCRIPTION
286              
287             This is a JsonSQL Query module that supports SQL generation for a broad range of the most common SQL SELECT features, including JOINs.
288              
289             Examples of SELECT features supported by this module:
290              
291             =head2 A simple SELECT statement (minimum),
292              
293             {
294             "fields": [
295             {"column": "*"}
296             ],
297             "from": [
298             {"table": "my_table"}
299             ]
300             }
301              
302             =head2 A more complicated SELECT statement,
303              
304             {
305             "fields": [
306             {"column": "field1"},
307             {"column": "field2", "alias": "test"}
308             ],
309             "from": [
310             {"table": "table1", "schema": "MySchema"}
311             ],
312             "where": {
313             "and": [
314             { "eq": {"field": {"column": "field2"}, "value": "Test.Field2"} },
315             { "eq": {"field": {"column": "field1"}, "value": "453.6"} },
316             { "or": [
317             { "eq": {"field": {"column": "field2"}, "value": "field3"} },
318             { "gt": {"field": {"column": "field3"}, "value": "45"} }
319             ]}
320             ]
321             }
322             }
323              
324             =head2 A SELECT statement with JOINs,
325              
326             {
327             "fields": [
328             {"column": "field1"},
329             {"column": "field2", "alias": "test"}
330             ],
331             "joins": [
332             {"jointype": "inner", "from": {"table": "table1", "schema": "MySchema"}, "to": {"table": "table2", "schema": "MySchema"}, "on": {"eq": {"field": {"column": "field2"}, "value": {"column": "field1"}} }}
333             ],
334             "where": {
335             "and": [
336             { "eq": {"field": {"column": "field2"}, "value": "Test.Field2"} },
337             { "eq": {"field": {"column": "field1"}, "value": "453.6"} },
338             { "or": [
339             { "eq": {"field": {"column": "field2"}, "value": "field3"} },
340             { "gt": {"field": {"column": "field3"}, "value": "45"} }
341             ]}
342             ]
343             }
344             }
345              
346             =head2 Mapping of JSON object properties to SELECT parameters:
347              
348             =head3 Required,
349              
350             =over
351              
352             =item fields => [ { table => "table1", column => "column1" }, { table => "table1", column => "column2" } ]
353              
354             Generates: 'table1'.'column1', 'table1'.'column2'
355             See L<JsonSQL::Param::Fields> and L<JsonSQL::Param::Field> for more info.
356              
357             =item from => [ { schema => "myschema", table = "table1" } ] ( if you are using a JOIN, you can omit the FROM )
358              
359             Generates FROM 'myschema'.'table1'
360             See L<JsonSQL::Param::Tables> and L<JsonSQL::Param::Table> for more info.
361              
362             =back
363              
364             =head3 Optional,
365              
366             =over
367              
368             =item joins => [ { jointype => "inner", from => { table => "table1" }, to => { table => "table2" }, on => { eq => { field => { table => "table1", column => "column1" }, value => { table => "table2", column: "column2"}} } } ]
369              
370             Generates: FROM 'table1' INNER JOIN 'table2' ON 'table1'.'column1' = 'table2'.'column2'
371             See L<JsonSQL::Param::Joins> and L<JsonSQL::Param::Join> for more info.
372              
373             =item where => { eq => { field => { table => "table1", column => "column1" }, value => 32 } }
374              
375             Generates: WHERE 'table1'.'column1' = ?
376             Bind: [ 32 ]
377             See L<JsonSQL::Param::Condition> and L<JsonSQL::Param::ConditionDispatcher> for more info.
378              
379             =item orderby => [ { field => { table => "table1", column => "column1" }, order => 'ASC'} ]
380              
381             Generates: ORDER BY 'table'.'column1' ASC
382             See L<JsonSQL::Param::OrderBy> and L<JsonSQL::Param::Order> for more info.
383              
384             =item groupby => [ { table => "table1", column => "column1" } ]
385              
386             Generates: GROUP BY 'table1'.'column1'
387             See L<JsonSQL::Param::Fields> and L<JsonSQL::Param::Field> for more info.
388              
389             =item having => { eq => { field => { table => "table1", column => "column1" }, value => 32 } }
390              
391             Generates: HAVING 'table1'.'column1' = ?
392             Bind: [ 32 ]
393             See L<JsonSQL::Param::Condition> and L<JsonSQL::Param::ConditionDispatcher> for more info.
394              
395             =item distinct => 'true'
396              
397             Generates: DISTINCT
398              
399             =item limit => 23
400              
401             Generates: LIMIT ?
402             Bind: [ 23 ]
403              
404             =item offset => 12
405              
406             Generates: OFFSET ?
407             Bind: [ 12 ]
408              
409             =back
410              
411             =head3 Additional Properties,
412              
413             =over
414              
415             =item defaultschema => 'myschema'
416              
417             If you are using DB schemas, this property can be used to generate the schema identifier for your queries. Particularly useful for
418             per-user DB schemas.
419              
420             =back
421              
422             See L<JsonSQL::Schemas::select> to view the restrictions enforced by the JSON schema.
423              
424             =head2 Whitelisting Module
425              
426             A set of whitelisting rules is required to successfully use this module to generate SQL. See L<JsonSQL::Validator> to learn how this works.
427              
428             =head1 METHODS
429              
430             =head2 Constructor new($query_rulesets, $json_query)
431              
432             Instantiates and returns a new JsonSQL::Query::Select object.
433              
434             $query_rulesets => The whitelisting rules to validate the query with.
435             $json_query => A stringified JSON object representing the query.
436              
437             Returns (0, <error message>) on failure.
438              
439             =head2 ObjectMethod get_select -> ( $sql, $binds )
440              
441             Generates the SQL statement represented by the object. Returns:
442              
443             $sql => An SQL SELECT string.
444             $binds => An arrayref of parameterized values to pass to the query.
445              
446             =head1 AUTHOR
447              
448             Chris Hoefler <bhoefler@draper.com>
449              
450             =head1 COPYRIGHT AND LICENSE
451              
452             This software is copyright (c) 2017 by Chris Hoefler.
453              
454             This is free software; you can redistribute it and/or modify it under
455             the same terms as the Perl 5 programming language system itself.
456              
457             =cut