Testing N1QL: SQL for JSON (Part 3)

Welcome 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.