Testing N1QL: SQL for JSON (Part 3)
Following along with the series, get a look at how MySQL's Random Query Generator can help you make templates for your own queries with JSON in mind.
Join the DZone community and get the full member experience.
Join For FreeWelcome back to Testing N1QL! If you're just getting in, check out part one and part two. Now that we've been introduced to the Random Query Generator, let's dive into an example of how you can use it with N1QL.
Example of Grammar used by N1QL:
query:
select ;
select:
SELECT select_from FROM BUCKET_NAME WHERE complex_condition;
complex_condition:
(condition) AND (condition) | (condition) OR (condition) | condition;
condition:
numeric_condition | string_condition ;
select_from:
COUNT(*) | COUNT( field ) | SUM( non_string_field ) | SUM(DISTINCT non_string_field ) | AVG( non_string_field ) | AVG( DISTINCT non_string_field ) | MAX( non_string_field ) | MIN( non_string_field );
numeric_condition:
numeric_field < numeric_value |
numeric_field = numeric_value |
numeric_field > numeric_value |
numeric_field >= numeric_value |
numeric_field <= numeric_value |
(numeric_condition) AND (numeric_condition)|
(numeric_condition) OR (numeric_condition)|
NOT (numeric_condition) |
numeric_between_condition |
numeric_is_not_null |
numeric_not_equals_condition |
numeric_is_null |
numeric_in_condition ;
string_condition:
string_field < string_values |
string_field > string_values |
string_field >= string_values |
string_field <= string_values |
(string_condition) AND (string_condition) |
(string_condition) OR (string_condition) |
string_not_between_condition |
NOT (string_condition) |
string_is_not_null |
string_is_null |
string_not_equals_condition |
string_in_conidtion |
string_like_condition |
string_equals_condition |
string_not_like_condition ;
string_equals_condition:
string_field = string_values;
string_not_equals_condition:
string_field != string_values | string_field <> string_values ;
string_between_condition:
string_field BETWEEN LOWER_BOUND_VALUE and UPPER_BOUND_VALUE;
string_not_between_condition:
string_field NOT BETWEEN LOWER_BOUND_VALUE and UPPER_BOUND_VALUE;
string_is_not_null:
string_field IS NOT NULL;
string_in_conidtion:
string_field IN ( string_field_list );
string_is_null:
string_field IS NULL;
string_like_condition:
string_field LIKE 'STRING_VALUES%' | string_field LIKE '%STRING_VALUES' | string_field LIKE STRING_VALUES | string_field LIKE '%STRING_VALUES%';
string_not_like_condition:
string_field NOT LIKE 'STRING_VALUES%' | string_field NOT LIKE '%STRING_VALUES' | string_field NOT LIKE STRING_VALUES | string_field NOT LIKE '%STRING_VALUES%';
field_list:
NUMERIC_FIELD_LIST | STRING_FIELD_LIST | NUMERIC_FIELD_LIST, STRING_FIELD_LIST | NUMERIC_FIELD_LIST, STRING_FIELD_LIST, BOOL_FIELD_LIST;
field:
NUMERIC_FIELD | STRING_FIELD;
We use the RANDGEN Perl script to generate different patterns of templates using this grammar.
Example Perl command:
perl gensql.pl –grammar=<path to specific grammar file> --queries=<number of queries> --seed=573 > template
Example of a simple query template generated by RQG:
select *
from bucket_name
where numeric_field=numeric_value
Example of an aggregate query template generated by RQG:
SELECT SUM(DISTINCT NUMERIC_FIELD )
FROM BUCKET_NAME
WHERE ((BOOL_FIELD = true OR NUMERIC_FIELD < NUMERIC_VALUE))
AND ((STRING_FIELD NOT LIKE STRING_VALUES AND NUMERIC_FIELD IN ( LIST )))
ORDER BY NUMERIC_FIELD_LIST;
We identified a standard relational database, Flightstats, and created our own database schema in MySQL. We created different database definitions for different kinds of operations. Each database definition corresponds to a single schema in MySQL, and each MySQL table is mapped to a Couchbase bucket and defines sufficient columns to map to different data types being tested, since we need to test different JSON data types.
An example of mapping MySQL schema to Couchbase Bucket is shown in Figure 2 below:
Fig. 2: Data mapping
That's where we're going to wrap up for today. It's a bit short, but it's important to get a look at how Randgen works in action. Next time, we'll dive into using the templates we just made to generate queries.
Opinions expressed by DZone contributors are their own.
Comments