Skip to content

DbBot MSSQL Usage Guide

Jump to bottom Edit New page
Ellen edited this page Aug 9, 2013 · 22 revisions

Table of Contents

Overview

What is DbBot MSSQL

DbBot MSSQL - A fork of (github.com/robotframework/DbBot)

Upon completion of Robot test suites, an output.xml file is generated that summarizes the results of the executed tests. This file is difficult to read and manipulate. DbBot MSSQL parses this output.xml file and writes the information into a SQL Server® database. You can then run reports and statistics on various test runs to investigate frequently failing tests and suites. Potentially, the DbBot can be used against any output.xml file generated by an up-to-date version of Robot.

Prerequisites

* Python 2.7 installed * [pymssql](http://code.google.com/p/pymssql/) - pymssql is the Python language extension module that provides access to Microsoft SQL Servers from Python scripts Note: Pymssql installer requires a valid python entry in registry 1. Navigate to http://code.google.com/p/pymssql/downloads/list 2. Download and install [pymssql-2.0.0b1-dev-20130111.win32-py2.7.exe](http://code.google.com/p/pymssql/downloads/detail?name=pymssql-2.0.0b1-dev-20130111.win32-py2.7.exe) * Microsoft SQL Server® Database (tested with SQL Server® 2008) 1. Create an empty MSSQL Server Database [following these steps](http://msdn.microsoft.com/en-us/library/ms186312.aspx)

Configure and Run DbBot

Configuring DbBot's Database Properties

  1. Open mssql_dbbot\dbbot\robot_database.py in a text editor
  2. Locate the command to connect to the database, for example: return pymssql.connect(host='sql.host.name', user='exampleuser', password='password', database='RobotDB')
  3. Modify the database details to point to your database
  4. Change the username and password to the appropriate values
  5. Add the MSSQL directory to your PYTHONPATH environment variable. A valid PYTHONPATH could be: C:\DbBot\mssql_dbbot

Run Tests

The following options can be used when running `dbbot.py`:
Option Strings Description
-h, --help Prints help instructions and lists these options
-v, --verbose Be verbose about the operation
-d, --dry-run Do everything except commit to database
-k, --also-keywords In addition, parse suites' and tests' keywords
-c, --clear-database Clear database of existing data before parsing
-m, --log-messages Parse and store messages; Requires keywords to be parsed
-f, --files [output.xml files] One or more Robot Framework output.xml files to parse
  1. Open a command prompt window
  2. Navigate to your mssql_dbbot directory, For example: cd c:\mssql_dbbot
  3. Perform the following command, changing the last option to point to your output.xml file:
python dbbot.py -f c:\output.xml```
4. Check that the values were loaded into the database specified in `mssql_dbbot\dbbot\robot_database.py` (see [Configure Database Properties](#configure))

<h2 id="failbot">FailBot</h2>
<h3 id="whatisfailbot">What is FailBot</h3>
The FailBot script reads the Microsoft SQL Server® database and generates a report on the failing suites, tests, and keywords. It orders these by most failures at the top.

<h3 id="failbotprereqs">FailBot Prerequisites </h3>
* Working installation of Dbbot

<h3 id="runfailbot">Run FailBot</h3>
<h4 id="failbotoptions">FailBot Options</h4>
FailBot can run with the following options:

|Option Strings	 | Description|
|-----------|-------------|
| -h, --help	 | Will print help instructions, and a list of these options |
| -v, --verbose	 | Be verbose about the operation |
| -o, --output | Path to the resulting html file |
| -n, --name	 | Specify the report name / title |

<h4 id="torunfailbot">To run FailBot:</h4>  
  
1. Navigate to the ``mssql_dbbot`` directory  
2. From this directory, execute the following command:  
```python failbot\bin\failbot --name "[report title]" -o [path for output file]```  
Replacing [report title] and [path for output file] fields with appropriate values.

Your report should be generated and saved in the location you provided in [path for output file] parameter. 

<h4 id="toaddnewsection">To add a new section to FailBot reports:</h4>  
1. Navigate to ``failbot/failbot``  
2. Open the file ``database_reader.py`` for edit  
3. Create a new function:  
  1. Copy and paste one of the existing functions from one of the existing sections - for example:  

			def most_failed_tests(self):
				sql_statement = '''
					SELECT count(tests.id) as count, tests.name, suites.name, tests.id
					FROM tests, test_status, suites
					WHERE tests.id = test_status.test_id
					AND tests.suite_id = suites.id
					AND test_status.status = 'FAIL'
					GROUP BY tests.name, tests.suite_id, tests.id, suites.name
					ORDER BY count DESC
				'''
				return self._fetch_by(sql_statement)  

  2. Change the function name to something more appropriate  
  3. Change the SQL statement stored in the _sql_statement_ variable to the query you wish to execute
4. Update the HTML writing functionality:  
  1. Open ``failbot/html_writer.py`` in a text editor or an IDE  
  2. Find the _produce_ function, which aggregates information from various sections of the database:   
  
                def produce(self):
                       self._verbose('- Producing summaries from database')
		            output_html = self._full_layout.substitute({
					'page_title': self._report_title,
					'time_stamp': self._return_time_string(),
					'most_failed_suites': self._table_of_most_failed_suites(),
					'most_failed_tests': self._table_of_most_failed_tests(),
					'most_failed_keywords': self._table_of_most_failed_keywords()
				})  
  
  3. Add a line to the _output_html_ variable using one of the 'most_*' lines as an example   
  4. Create a new function for formatting your new table using an existing one as an example:  

			def _table_of_most_failed_tests(self):
				return self._format_three_column_table_with_suite_name(self._db.most_failed_tests())  
Choose between _format_three_column_table_with_suite_name and _format_two_column_table_with_suite_name, depending on the number of attributes to display - two or three, respectively.

<h2 id="regressiontests">Automated Regression Tests</h2>  

The automated regression suite provided with DbBot is written in the Robot Framework. It runs the DbBot with various types of test output.xml files.

<h3 id="runregression">Run Regression Tests</h3>  

The regression suite is located in: ```mssql_dbbot/Testdbbot```.    

1. Open `mssql_dbbot\dbbot\robot_database.py` in a text editor  
2. Locate the command to connect to the database, for example:  
```return pymssql.connect(host='sql.host.name', user='exampleuser', password='password', database='RobotDB')```  
3. Modify the database details to point to your test database (so as not to affect your actual results)   
4. Navigate to ```mssql_dbbot/Testdbbot```    
5. Execute the following command:  
```pybot Testdbbot/DbBot_Output_Tests.txt```

The test output.xml files used are stored in the ``testdata`` directory.

<h3 id="addregression">Add New Tests to the Regression Suite</h3>
To add tests of a similar kind to the regression suite:

1. Save the output.xml you wish to test to ``testdata/<name of tested area>`` for example: ``testdata/large_xml/test_output.xml``
2. Open ``Testdbbot\DbBot_Output_Tests.txt`` in a text editor
3. Create a new Test Case with the following format:

		<<Your Test Case Name>>
			[Documentation]                                 Input large output file containing 20,000 tests
			${console_output} =                             Console Output For Output File in Sub-Directory  <<name of the dir your created in the previous steps>>
			Database Commit Should Have Succeeded           ${console_output}

Note: The final keyword can be replaced with a keyword to check for a parse error -- if this test is expected to fail:

     Parse Error Should Be Present                   ${console_output}


<h3 id="knownissues">Known Issues</h3>

 * Output.xml files that have been generated from two output.xml files, which have been combined with Robot, cannot be processed by DbBot -- automated regression test currently fails.

<h2 id="troubleshooting">Troubleshooting</h2>
<h3 id="pythonpatherror">PYTHONPATH Error</h3>  
If FailBot PYTHONPATH Error occurs, despite the PYTHONPATH variable being set correctly:  
1. Navigate to:  ``failbot\bin\failbot.pyc``  
2. If this file is present, delete it
Add a custom footer
Add a custom sidebar

Clone this wiki locally