DoxRunner
Macquarie University Student Accommodation
Overview
This page shows how DoxRunner Process Raw operation handles LoadTest Database rules from preparation, documentation, and how they are applied to a raw script. resulting in a script that is configured with LoadTest Database and their associated parameters.
The benefits over LoadRunner's native text data file method are:
-
Consumed data doesn't need to be removed - a status column is used to flag consumed data;
-
Use of the status column allows data to move from one state to another across different scripts and across different tests, with minimal manual intervention;
-
Data can be changed/added during a test if it is noticed that there are insufficient records to complete the test or for other reasons.
The database itself is normally installed on a separate server. Access to it must be available to the LoadRunner infrastructure (eg. LGs) and the workstations used for scripting. See the section labeled Preparation below.
Configuring a script to support it can be complex if you aren't familiar with the process. The Process Raw operation can automate much of the script configuration if documented well.
Acknowledgment must go to Stuart Spencer, Canberra, Australia, who developed the
concepts and the four script files that are necessary for correct operation.
Four files are required and they can be supplied by request from the author.
How it works - in brief
The key to applying documented LoadTest Database rules to a raw script is the Process Raw operation (see below).
Before initiating it, become familiar with items to first.
Initialization code is prepared
A call to the InitializeDatabase() function is inserted into vuser_init.c.
Included files are configured
Four files are copied into the script's folder from the Included Files folder.
Also a file called DatabaseOperations.c is created.
Script-specific functions are prepared
Skeleton functions are added to the DatabaseOperations.c file depending on the documented table/operation configuration. A InitializeDatabase() function is also created.
Values are parameterized
All relevant values in the script are parameterized as specified by the scripter in the green Parameter Association screen.
Initiate the Process Raw operation
During the execution of the Process Raw operation, the documented LoadTest Database rules are presented to the scripter in the green Parameter Association screen, along with appropriate values from the script. The scripter associates the two using a simple point and click process.
Record the raw script
LoadTest Database parameters are not configured during recording.
Prepare MySQL environment
Make sure that the MySQL environment has been prepared in accordance with the instructions here.
Document the rules
The test case is the preferred place for documenting LoadTest Database rules.
The Solution document can also play a role.
Manually tailor the script
The scripter should check the resulting script, in particular the script-specific functions in DatabaseOperations.c.
For example. if there are script-specific functions that contain a Where clause, these need to be manually completed.
Also, calls need to be made to the script-specific functions, and inserted at relevant places within the main script.
How it works - in detail
Prepare
Make sure that the MySQL environment has been prepared in accordance with the instructions here.
Document the rules
The LoadTest Database rules are documented in a DoxRunner section that has a table. This section can be part of a test case, or located in a common part of the Solution document, or both. Each row of the table contains all details of each rule. Whether the rules are located in a section of a test case or in the Solution document, the table's basic structure is the same.
The illustration below shows an example of each. The only substantial difference is the bookmark.
The columns and operations are described in more detail below.
Initiate the Process Raw operation
The Process Raw operation does many things. In the context of the LoadTest Database, it takes a raw script and, using the documentation plus 4 extra files, converts it into a script instrumented for the LoadTest Database.
Record a raw script
Record the raw script in accordance with the DoxRunner guidelines. Make sure it's saved with its name starting with the Test Case ID, followed by a space, into the folder specified in the Configuration section of the document so that the Process Raw operation can identify it.
The documented rules are combined
The Process Raw operation reads the documented LoadTest Database rules from both the test case and the common area of the Solution document. It then combines them into one list. If a parameter name appears in both, the version in the test case takes precedence.
The illustration below shows the result when those in the illustration at above are combined.
The script files are assessed
All files in the script's folder are assessed for those attribute name / value pairs that may be a candidate for assigning to a parameter. Included in these candidates will be those that are relevant for assigning to LoadTest Database parameters.
Parameter Association
The attribute name / value pairs collected at are presented in the top left hand pane of the green Parameter Association screen.
The LoadTest Database rules from the documentation are displayed under the LoadTest Database tab in the top right hand pane.
The scripter is then required to manually associate each rule with a relevant attribute name / value pair.
The illustration below shows three views:
View 1: The initial attribute name / value pairs and LoadTest Database rules;
View 2: One of each is selected by the scripter, ready for the scripter to press one of the three association buttons;
View 3: All rules are associated and displayed in the bottom pane. The scripter is then expected to press the Apply button.
The name / value pairs are parameterised
As shown in the illustration below, the name / value pairs that the scripter associated with a LoadTest Database parameter in the green Parameter Association screen are parameterised by the Process Raw operation. The original values are appended as a comment for trace-ability.
The script is configured for MySQL
Four files are copied into the script's folder from the Included Files folder. Also a file called DatabaseOperations.c is created.
Skeleton script-specific functions are created
One function per Table / Operation combination is inserted into the newly created DatabaseOperations.c file (as interpreted from the documented rules), plus the InitializeDatabase() function. The name of each function is a concatenation of the Operation and the Table Name.
In the illustration below, the documented rules shown in the example at above specify these seven functions:
Initialize Database
A call to the InitializeDatabase() function is inserted into vuser_init.c.
Finalize the script
Every script / protocol is different, so the Process Raw operation can only go so far.
Once it's finished, the scripter should do the following:
Check the resulting script. Make sure the correct values have been parameterised and the script contains the five extra files listed in above. Make sure all relevant functions were created in the DatabaseOperations.c file.
Referring to above, manually update any functions in DatabaseOperations.c that require a Where clause (see also the section titled Script Specific Function Examples below),
Manually insert calls to the script-specific functions at appropriate places in the script. The illustration below shows an example where the Update_Stock function is called with status 60.
Note that it needs to be manually prepared by the scripter. The following is a guideline:
-
copy the commented-out lines shown in above;
-
paste them elsewhere in the script where it is appropriate;
-
un-commented the lines;
-
update the status as required.
Preparation
Before using the LoadTest Database functionality the following tasks need to be completed:
-
Install MySQL on a server accessible by all Load Generators and all workstations that will be using VUGen;
-
Contact the author who will send you four files (two DLLs and two C functions) that need to be included in all scripts;
-
Make sure the Included Files Folder is defined as a Configuration Item;
-
Update the mySQL.c file to include database configuration and authentication;
-
Copy the four files to the Included Files Folder.
The four files are:
-
mySQL.c
-
mySQL.dll
-
Regex.c
-
pcre3.dlll
Update the mySQL.c file
The illustration below shows the part of the mySQL.c file that needs to be updated with the server name and authentication.
Although it's not essential, all instances of the text "<mySQLServerName>" can be changed to the server name.
Database details
Database configuration
The MySQL database must be installed on a server that is accessible by all Load Generators during a test, and by the scripter using VUGen for unit testing scripts.
The following defines the configuration of the MySQL database. All items except the Database Name should be manually set in the db_mysql_init() function within the mySQL.c file because, once set for an organisation, they usually don't change. Place a master copy of the updated mySQL.c file in the Included Files folder, which is specified in the Configuration section of the Solution document.
The Database Name is expected to change from release to release (examples include R01 and R02) or project to project, or for any reason, so it should be specified as a Database CI in the Configuration section of the Solution document or test case.
-
Database Driver: MySQL ODBC 5.1 Driver
-
Database Server: <server.domain.local>
-
Database User: root
-
Database PWD: root
-
Database Name: This is best specified as a Database CI.
-
Database Port: 3306
General SQL database commands and concepts won't be covered here, so a level of SQL database knowledge is assumed. What will be covered is how to instrument a script and how it works within a performance test.
Table Columns
It's very important to understand that the column headings of the tables are translated into LoadRunner parameters, so they must conform to parameter naming rules.
All tables must contain the following three columns in addition to the data columns:
-
id int(10) unsigned NOT NULL AUTO_INCREMENT
-
status int(10) unsigned NOT NULL DEFAULT '0'
-
lastupdated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
id: This is used as the primary key. Its values must be unique. It is typically sequential, but not need be – so long as it is unique. When creating data via a csv file, use excel to manage this column. It need not be referenced in your scripts because the MySQL functions use it under the covers.
status: This is one of the most important columns in a table and is described in a separate section below.
lastupdated: This contains the date and time when the record was created or last updated. It is very handy when reviewing data after a test. When uploading from a csv file, use the current date and time or a time in the recent past. It must be in the following format: YYYY-MM-DD HH:MM. Example: 2013-09-05 15:45. Note that Excel may try to change it to a different format, so make sure you check the .csv file and, if it is different to this format, manually change it to conform.
Data columns: To be useful a table must have data columns. Details are provided below. The column names become parameter names.
The id column
It is this column that the MySQL functions use to manage the records. It is an integer that is unique. There should be no need to touch it manually.
The only time you need to consider it is when you upload records into a table. You need to make sure any uploaded records contain an id column with unique integers in it. Don't re-use values that already exist in the table. The mysql_put() function adds records with an auto-generated id that is one more than the highest existing id.
It is defined as follows:
-
id int(10) unsigned NOT NULL AUTO_INCREMENT
The status column
It is this column that you and the functions in MySQL.c use to manage the records.
It is defined as follows:
-
status int(10) unsigned NOT NULL DEFAULT '0'
Its value typically starts at zero, which is the value you should set it to when using a csv file to upload new data (although this is not mandatory). If using the mysql_put() function to insert records, you can set the initial status to whatever you want, but you will typically set it to zero.
If you only need to read a record and have no intention of updating it, the read-only functions in MySQL.c won't change the status, but you can specify what status to read.
If you need to update the record later during a test, you will want to use the mysql_get_and_burn() function, which automatically increments the status by 1, meaning no other VU will attempt to acquire it. For example, if the status is 0, the function updates it immediately to 1. If it's 10, then it will update it to 11, etc. When you want to update the record later within the script, use the mysql_update() or mysql_update_status() functions, where you can set a new status to whatever is relevant. For example, if you get and burn a record with status 50, it will automatically change it to 51. If you want the end state to be 60 (say to make it available to another script), function mysql_update() or mysql_update_status() can specify 60. The mysql_update_status() function is used if only the status is to change. Otherwise use mysql_update().
If an iteration failed, the record that was accessed by the mysql_get_and_burn() function will be left as burned. For example, if a script reads records with status 0, burns it to 1, then the iteration fails, it won't be updated to whatever value determines a successful iteration. Then after a test you can manually select all records with the burnt status (status 1), with lastupdated between the start and end times of the test, and you can see whether any iterations failed and what they were. You can then decide whether to manually reset the status to its original value to put them back into the pool of available data, or leave them burned and therefore unavailable to all future tests.
The lastupdated column
This is a very handy column. It contains the date and time when a record was created or updated. It can be used to search for records that were updated during a test.
It is defined as follows:
-
lastupdated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Its format is YYYY-MM-DD HH:MM:SS. When uploading records only the date component is necessary, but its format is essential.
When a test has completed, the records can be selected as follows (assuming the test started at 2/01/2016 15:10):
select * from <tablename> where lastupdated like '2016-01-02 15%';
Data columns
The data columns usually appear in a table after the above three columns. They can be defined using standard MySQL rules, including indexes, keeping mind that their names are used as parameter names so they must conform to LoadRunner rules.
For example, column name TODAY_DATE can be referenced in the script as {TODAY_DATE }.
Using the column names as parameter names makes it easy to manage and use the data in the scripts.
It is recommended that all columns be defined with a default value to prevent columns of a new record being set to NULL. This may happen if you haven't specified a value when using the mysql_put() function. NULL values may cause problems when attempting to read or manage a record.
Script details
Although a script can be instrumented for the LoadTest Database manually, if the rules are documented properly, then the best option is to use the Process Raw operation. This section describes both.
An important component is the functions. There are the generic functions contained in the mySQL.c file, and the script-specific functions that use them (based on the documented rules for that script).
Preparation
To use the LoadTest Database facility, you must download four files, These must be obtained from the author.
Summary of generic functions
The functions listed below are available in mySQL.c. There should be no need to change them other than those values that define the database. Use the DatabaseOperations.c file for script-specific interaction as described in the next section.
int db_mysql_init(const char *inDatabase)
One call to this function is required for all scripts that use the LoadTest database. The code to manage it is automatically created in DatabaseOperations.c and the call to it is inserted into vuser_init.c by the Process Raw operation.
int mysql_get_random(char *inTableName, char *inColumnNames, int inStatus, char *inWhereClause, int iLock)
Gets a random record that conforms to the value of inStatus and inWhereClause. Either lock the row or leave it unlocked (iLock can be set to one of the two #defines LOCKED or UNLOCKED).
int mysql_get_and_burn(char *inTableName, char *inColumnNames, int iRand, int inStatus, char *inWhereClause)
Gets one record with status equal to inStatus and that conforms to inWhereClause. The record is then immediately updated with a new status value set to one more than the original status. Eg. If the inStatus is 0, then this function updates it to 1. iRand can be set to one of the two #defines RANDOM or SEQUENTIAL.
int mysql_get_one_record(char *inTableName, char *inColumnNames, int iRand, int inStatus, char *inWhereClause, int iLock)
Identical to mysql_get_random() except that one of the random #define options (SEQUENTIAL or RANDOM) can be specified in argument iRand.
int mysql_update(char *inTableName, char *inColumnNames, int inStatus)
Update a record that was read previously, and set the status field to iStatus. The record must have been read earlier in the script by one of the above three functions.
int mysql_update_status(char *inTableName, int inStatus)
Update only the status of a record that was read previously.
int mysql_put(char *inTableName, char *inColumnNames, int inStatus)
Uses the SQL Insert command to insert a record into the database. The id and lastupdated fields are managed seamlessly, and the status field can be specified. Column names not in the list will be set to their default value assuming the table was defined with default values for all columns.
Script-specific functions
Script-specific functions are placed in a new action file called DatabaseOperations.c. The Process Raw operation will automatically prepare it with skeleton functions based on the LoadTest Database documentation. The scripter will need to review and update this code to suit the script.
In addition, code to call the functions must be inserted into the script manually by the scripter at locations that are relevant.
To make your code more readable, use the four #defines that are included in the mySQL.c file: They are:
#define RANDOM 1
#define SEQUENTIAL 0
#define LOCKED 1
#define NOTLOCKED 0
All char * arguments to the generic functions listed in the section above must be in double quotes. Refer to the examples in the next section.
inWhere can be empty (example: ""). If you are using the LIKE verb, make sure you use %%, not %. Also, the where clause must start with AND. Example: "AND estimate_id like 'EST%%'". Refer also to the examples in the next section.
The list of columns must be separated by a comma with no spaces.
Example: "CART_NO,PO_NO,REQUISITIONER", not " CART_NO, PO_NO, REQUISITIONER ".
Examples of script-specific functions
These are placed in DatabaseOperations.c. The Process Raw operation will create skeleton functions based on the LoadTest Database documentation defined earlier on this page. Many will need to be manually tailored by the scripter to suit the requirements of the script.
Reading a random record
Use the GetRandom() function to read a record randomly without burning it. Specify the status. If the lock option is NOTLOCKED, it remains available for any other VU to read. The specified column names are available to the script as parameters.
As created by the Process Raw operation
As updated by the scripter (example only)
Reading a random record and burning it
The reason for burning a record is to make sure no other VU can access it. The mysql_get_and_burn() function selects one record with a specified status, and increments that status by 1. The specified column names are available to the script as parameters. The record is locked, and its details are saved in preparation for an update. If an update is required, use one of the mysql_update() or mysql_update_status() functions.
As created by the Process Raw operation
As updated by the scripter (example only)
Updating a record
The mysql_update() function uses the id of the previously read record to identify the record to update. The specified column names must conform to LoadRunner parameter naming rules.
As created by the Process Raw operation
As updated by the scripter (example only)
Update the status only
The mysql_update_status() function uses the id of the previously read record to identify the record to update. No columns need to be specified - only the new status.
As created by the Process Raw operation
As updated by the scripter (example only)
Creating a new record
The mysql_put() command is used to create a new record. The id is automatically allocated but the status can be specified. If a column is left out of the list, its value is set to NULL. This may cause a problem later.
As created by the Process Raw operation
As updated by the scripter (example only)
Manually instrumenting a script for MySQL
The Process Raw operation will automatically instrument a script for MySQL, so long as the four files are in the Included Files folder (as configured in the Configuration section).
However, if for some reason you want to or need to do it manually, then follow these instructions, referring to the diagram.
The two DLLs are added under the Extra Files folder of the Solution Explorer in VUGen while the two generic C files are added under the Actions folder. An additional (empty) C file (DatabaseOperaions.c) is added under the Actions folder as well.
Copy the two DLL files to the script's folder (this step is important – don't forget it). Then open the script using VUGen.
Right click on the Actions folder in the Solution Explorer of VUGen.
Repeat for DatabaseOperations.
Right click on the Extra Files folder in the Solution Explorer of VUGen and...
Repeat for the pcre3.dll file.
Save and close VUGen.
The 5 files should now be included in the script.
Reopen the script in VUGen.
... select Add Files to Script.
The DatabaseOperations.c file should be empty. You should place all of your script-specific functions in it as detailed elsewhere on this page.
Navigate to the mySQL.dll file and select it. When navigating to it, make sure you only navigate to the version you copied into the script's folder, not the one where you got it from.
Select Create New Action.
Type in mySQL.
Repeat for Regex.
Copy mySQL.c and Regex.c from the Included Files folder and replace those that you created in steps 7 , 8 , and 9 .
Initializing the database in the script
Database initialization refers to how the script connects to the database at run-time. The critical function is db_mysql_init(), which is contained in file mySQL.c.
The Process Raw operation will automatically place database initialization code into the script as per the illustration below.
However, if you follow that illustration, you can manually instrument the script for database initialization.
It is recommended that you place the InitializeDatabase(); call in vuser_init.c as shown, and the full function in DatabaseOperations.c.
Note that the full function makes 10 attempts to connect. In practice it should connect first time, maybe the second time. This is because you don't want a test to fail if one VU is slow to connect. If it fails after 10 times, then surely there must be a problem
In the illustration below, the Additional Attribute "DatabaseName" and its parameter {aDatabaseName} is used. By extracting the database name from an Additional Attribute, the name of the database for all scripts in a scenario can be defined at run-time, which is very useful if you want to change databases between tests. Configuring it via Additional Attributes means that you don't need to change any script if you are changing databases.
If you don't want to use the Additional Attributes facility to define the database name, it is recommended that it be assigned in vuser_init as a Global Custom parameter.
The #defines at the top of vuser_init.c are used to make some arguments in some MySQL function calls easier to read.
Test Accounts
DoxRunner doesn't provide any special functionality for test accounts, relying on the functionality for text data files and/or the LoadTest Database functions.
There are advantages in storing test accounts in a LoadTest Database table rather than a text data file.
For example, if passwords change, they can be reset using a MySQL command, rather than editing the relevant text data file in each and every script.
A Unique/Once setting can be simulated by using the Get and Burn function.
That is, if a VU gets and burns a record in vuser_init prior to logon, then that record is no longer available to any other VU during the test.
Setting the status back to its original value in vuser_end after logoff using the Update Status function automatically releases it, ready for the next test.
Otherwise it cam be manually reset using a relevant SQL command.