DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Testing N1QL: SQL for JSON (Part 3)

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.

Prerna Manaktala user avatar by
Prerna Manaktala
·
Mar. 03, 17 · Tutorial
Like (5)
Save
Tweet
Share
4.10K Views

Join the DZone community and get the full member experience.

Join For Free

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. 

JSON Relational database Database sql MySQL

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Using JSON Web Encryption (JWE)
  • The Role of Data Governance in Data Strategy: Part II
  • Why It Is Important To Have an Ownership as a DevOps Engineer
  • How to Secure Your CI/CD Pipeline

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: