What is Random Query Generator (RQG) ? Yes a tool which generates random SQL queries - Hurray!
We use RQG to qualify different areas of the MySQL Server like optimizer, runtime , partitioning, replication, engines, Etc.
I am working with MySQL QA team @ Oracle, involved in maintaining the RQG tool. Here are some of my experiences on this excellent test framework.
Usefulness of RQG:
The tool is used for functional testing as well as to test/reach parts of the code which is difficult to cover by regular tests, what I mean is :
When a database is tested we write queries to verify its functionality but there are various functional areas which have to be considered for testing for example:
One can thus imagine in a database change in most cases means the process involved in testing is time consuming and grows based on the functionality been added/modified.
So how does one go about testing in this case, definitely plan for the testing, identifying scenarios, understanding the requirements and developing testcases for testing it, then automating it etc.. would be a way to go. But do you see what it takes not only the time but the effort and quality of verification when it comes to a database like MySQL where developers develop features and with brilliant brains on deck we require some efficient and fast way of testing/verifying the features to provide the best quality of an feature.
What if we had a way to generate the required data, the queries efficiently quickly be large and accurate to test the functionality and also verify the requirements of a feature? well RQG answers this need...
An example select query generated by RQG would be:
SELECT alias1 . `col_time_key` AS field1 , alias2 . `col_time_key` AS field2 , alias1 . `col_int_key` AS field3 FROM ( CC AS alias1 STRAIGHT_JOIN ( SELECT SQ1_alias2 . * FROM ( C AS SQ1_alias1 LEFT OUTER JOIN ( A AS SQ1_alias2 RIGHT JOIN CC AS SQ1_alias3 ON (SQ1_alias3 . `col_varchar_key` = SQ1_alias2 . `col_varchar_nokey` OR ( 73, 7 ) IN ( SELECT SQL_SMALL_RESULT C_SQ1_alias1 . `col_int_nokey` AS C_SQ1_field1 , MAX( C_SQ1_alias2 . `col_int_nokey` ) AS C_SQ1_field2 FROM CC AS C_SQ1_alias1 ) ) ) ON (SQ1_alias3 . `col_varchar_key` = SQ1_alias2 . `col_varchar_nokey` ) ) WHERE SQ1_alias1 . `col_varchar_nokey` != 'v' ) AS alias2 ON (alias2 . `col_int_key` = alias1 . `col_int_nokey` ) ) WHERE ( alias1 . `col_varchar_nokey` <> ( SELECT SUM( DISTINCT SQ2_alias1 . `col_varchar_key` ) AS SQ2_field1 FROM ( CC AS SQ2_alias1 RIGHT JOIN CC AS SQ2_alias2 ON (SQ2_alias2 . `col_int_key` = SQ2_alias1 . `col_int_nokey` ) ) ) ) OR ( alias1 . `col_varchar_key` LIKE CONCAT( 'j' , '%') OR alias1 . `col_varchar_key` = alias1 . `col_varchar_key` ) AND ( alias1 . `col_int_key` = alias1 . `col_int_key` AND alias1 . `col_int_key` IS NOT NULL ) HAVING field3 = 'f' ORDER BY alias1 . `col_time_key` DESC , CONCAT( alias2 . `col_varchar_key`, alias2 . `col_varchar_key` ) , alias1 . `col_datetime_key` , alias1 .`pk` DESC , CONCAT( alias1 . `col_varchar_nokey`, alias1 . `col_varchar_key` ) , CONCAT( alias1 . `col_varchar_key`, alias1 . `col_varchar_key` );
Now we can generate many such queries with RQG by writing few rules in a grammar file. These rules define how to generate a queries example either simple queries or complex queries like the above.
The power of RQG is not only at generating such queries but also validating such queries against a database server. RQG also poses other intelligence built in the form of Validators, Transformers , Reporters , Simplifiers.
Another important aspect of testing is to ensure functionality remains as is ( Regression testing) this can be achieved with RQG easily, the tool provides comparison of results between two or more servers of MySQL, JavaDB and few more supported databases so far, this makes it a powerful feature to have while testing databases.
This post is in general to give a feel of what is the use of RQG what it has in a brief, I will try and put up some more blog posts which can give a bit more detailed information on RQG and how to use it for testing, meanwhile if your interested there is a detailed document at GitHub on RQG.
RQG is a tool developed in Perl and its Open Sourced and available to view at launchapd.
Feel free to let me know if you have any questions/suggestions or interests in RQG will try and add more information based on it.
We use RQG to qualify different areas of the MySQL Server like optimizer, runtime , partitioning, replication, engines, Etc.
I am working with MySQL QA team @ Oracle, involved in maintaining the RQG tool. Here are some of my experiences on this excellent test framework.
Usefulness of RQG:
The tool is used for functional testing as well as to test/reach parts of the code which is difficult to cover by regular tests, what I mean is :
When a database is tested we write queries to verify its functionality but there are various functional areas which have to be considered for testing for example:
- The data set for testing. (Tables/Views, Indexes, no of columns, data-types,constraints, actual data to be loaded in the tables).
- The type of queries for testing (DCL, DML, Joins (self, inner, outer etc..), nested, correlated, non-correlated queries, Stored Procedures, Triggers).
- Ensure functionality works as is. (Results are accurate etc..).
- Usage: Syntax, Semantics, Etc...
- Meta-data checks (Information Schema, Performance Schema), Etc...
- Multi-threaded (ACID properties)
One can thus imagine in a database change in most cases means the process involved in testing is time consuming and grows based on the functionality been added/modified.
So how does one go about testing in this case, definitely plan for the testing, identifying scenarios, understanding the requirements and developing testcases for testing it, then automating it etc.. would be a way to go. But do you see what it takes not only the time but the effort and quality of verification when it comes to a database like MySQL where developers develop features and with brilliant brains on deck we require some efficient and fast way of testing/verifying the features to provide the best quality of an feature.
What if we had a way to generate the required data, the queries efficiently quickly be large and accurate to test the functionality and also verify the requirements of a feature? well RQG answers this need...
An example select query generated by RQG would be:
SELECT alias1 . `col_time_key` AS field1 , alias2 . `col_time_key` AS field2 , alias1 . `col_int_key` AS field3 FROM ( CC AS alias1 STRAIGHT_JOIN ( SELECT SQ1_alias2 . * FROM ( C AS SQ1_alias1 LEFT OUTER JOIN ( A AS SQ1_alias2 RIGHT JOIN CC AS SQ1_alias3 ON (SQ1_alias3 . `col_varchar_key` = SQ1_alias2 . `col_varchar_nokey` OR ( 73, 7 ) IN ( SELECT SQL_SMALL_RESULT C_SQ1_alias1 . `col_int_nokey` AS C_SQ1_field1 , MAX( C_SQ1_alias2 . `col_int_nokey` ) AS C_SQ1_field2 FROM CC AS C_SQ1_alias1 ) ) ) ON (SQ1_alias3 . `col_varchar_key` = SQ1_alias2 . `col_varchar_nokey` ) ) WHERE SQ1_alias1 . `col_varchar_nokey` != 'v' ) AS alias2 ON (alias2 . `col_int_key` = alias1 . `col_int_nokey` ) ) WHERE ( alias1 . `col_varchar_nokey` <> ( SELECT SUM( DISTINCT SQ2_alias1 . `col_varchar_key` ) AS SQ2_field1 FROM ( CC AS SQ2_alias1 RIGHT JOIN CC AS SQ2_alias2 ON (SQ2_alias2 . `col_int_key` = SQ2_alias1 . `col_int_nokey` ) ) ) ) OR ( alias1 . `col_varchar_key` LIKE CONCAT( 'j' , '%') OR alias1 . `col_varchar_key` = alias1 . `col_varchar_key` ) AND ( alias1 . `col_int_key` = alias1 . `col_int_key` AND alias1 . `col_int_key` IS NOT NULL ) HAVING field3 = 'f' ORDER BY alias1 . `col_time_key` DESC , CONCAT( alias2 . `col_varchar_key`, alias2 . `col_varchar_key` ) , alias1 . `col_datetime_key` , alias1 .`pk` DESC , CONCAT( alias1 . `col_varchar_nokey`, alias1 . `col_varchar_key` ) , CONCAT( alias1 . `col_varchar_key`, alias1 . `col_varchar_key` );
Now we can generate many such queries with RQG by writing few rules in a grammar file. These rules define how to generate a queries example either simple queries or complex queries like the above.
The power of RQG is not only at generating such queries but also validating such queries against a database server. RQG also poses other intelligence built in the form of Validators, Transformers , Reporters , Simplifiers.
Another important aspect of testing is to ensure functionality remains as is ( Regression testing) this can be achieved with RQG easily, the tool provides comparison of results between two or more servers of MySQL, JavaDB and few more supported databases so far, this makes it a powerful feature to have while testing databases.
This post is in general to give a feel of what is the use of RQG what it has in a brief, I will try and put up some more blog posts which can give a bit more detailed information on RQG and how to use it for testing, meanwhile if your interested there is a detailed document at GitHub on RQG.
RQG is a tool developed in Perl and its Open Sourced and available to view at launchapd.
Feel free to let me know if you have any questions/suggestions or interests in RQG will try and add more information based on it.
No comments:
Post a Comment