Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Testing N1QL: SQL for JSON (Part 3)

DZone's Guide to

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.

· Database Zone
Free Resource

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

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:

Image title

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. 

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

Topics:
json ,grammar ,tutorial ,database ,data generation

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}