Testing N1QL: SQL for JSON (Part 2)
Testing N1QL: SQL for JSON (Part 2)
MySQL's Random Query Generator helps generate databases and run queries against them, lending you a hand for functional and stress testing.
Join the DZone community and get the full member experience.Join For Free
Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper
Welcome back to Testing N1QL! If you're just getting in, check out part one, which sets the tone for the series and dives into the basics of testing Couchbase's N1QL. Today, we're going to talk about the MySQL Random Query Generator and how it can be used for N1QL. The Random Query Generator is a tool for generating complete databases and executing queries against them for the purpose of functional and stress testing.
The tool is capable of executing test scenarios that include:
Generating a database from a description of the desired data contents and properties.
Executing random queries based on SQL templates the queries must conform to.
Comparing the results returned by different server versions, configurations, or storage engines.
Monitoring the server during the test and detect deadlocks, crashes, replication failures and other interesting events.
Randgen is written in Perl and takes a description of the queries to run from a grammar file. It looks very similar to a standard YACC grammar, except that there are no actions to execute for every rule. The script starts from a top-level element named query and will then walk the grammar randomly in order to produce random queries.The grammar is explained more in the following sections.We defined our own grammar, which is taken by the RQG framework. Since you can write grammar for SQL, you can also do the same for N1QL. The RQG has a facility for running the same test multiple times, with each run using slightly different command-line options and SQL grammar. For more information, see RandomQueryGeneratorCombinations.
The Random Data Generator script is used to create a set of tables based on the requirements specified in a configuration file. Queries can then be issued against those tables. The operation and configuration of the Data Generator are described in RandomDataGenerator. If you want to know more about Randgen, you can check out its documentation.
How RQG Was Written to Generate N1QL Queries
The diagram below gives a basic flow on how we used Randgen to generate N1QL queries.
Basic flow diagram for RQG:
Steps for generating templates:
Install the MySQL Random Query Generator.
Create grammar for generating SQL templates.
Templates help generalize query patterns.
Templates vary based on SQL statements, e.g. JOIN, SUBQUERY, and Datetime Functions.
Templates can be mapped to actual SQL/N1QL based on RDBMS schema.
If we would have used SQL grammar, then the queries generated by RQG would be data centric and not generic. Hence, we came up with query template. We generated templates using RQG with grammar as input. In the grammar, we define a set of rules that generate signatures of SQL and N1QL queries. These template files are stored as ZIP files in our test repository.
Now that we've covered the basics of how it works, next time, we'll look into specific examples of how you can use Randgen to test out your N1QL.
Opinions expressed by DZone contributors are their own.