Jan
13
Written by:
QCTO Blog
Wednesday, January 13, 2010
Written by Finn Ellebaek Nielsen
Introduction
This blog post provides a short introduction to Continuous Integration (CI) and a description of how to integrate testing Oracle code with Quest Code Tester for Oracle (QCTO) in a CI environment.
Background
CI is the practice of early and frequent software integration through a so-called Build. A CI environment is typically configured to create a new Build once or several times a day but only when new or changed source code has been committed to the source code version control system.
A Build is typically broken down into the following subprocesses:
- Compilation: Generation of scripts that will be used for execution of SQL scripts and compilation of PL/SQL source code. The script generation can be tricky as it needs to cater for dependencies amongst the database objects that have changed since the last build. Assuming that the sequence of the generated script is right, any compilation errors are fatal.
- Test: Execution of automated tests at various levels, eg both unit and integration (component) tests. Any test cases failing are fatal.
- Inspection: Inspection of the software, such as whether it conforms to established project standards for naming conventions, formatting, size of subprograms, code complexity, etc. Any violations are fatal.
- Deployment: Optional deployment of the new build to a user acceptance test environment. Any deployment errors are fatal.
- Documentation: Generation of the documentation based on comments in source code and overview written by developers, eg using Javadoc, NDoc, PlDoc etc.
- Feedback: If any fatal errors have been encountered feedback must be generated and distributed, for instance in the form of an e-mail, an SMS or a red color to your Ambient Orb (a glass lamp you put in the office that through its color shows the state of the latest Build).
A dedicated CI server is typically set up with software that automates the CI process. Typically, this server will be configured to poll the source code version control system for changes every x seconds or once a day (in the evening). When the CI server detects a change it will start creating a new Build, covering all the components mentioned above.
You obtain the following benefits through adopting CI, among others:
- Any code change that has been committed to the version control system is integrated as early as possible with the existing code and other changes. This is valuable feedback to the developer that made the change and if the change broke existing behavior this is detected as soon as possible, which results in higher software quality and lower cost.
- The automated tests are automatically executed and you don't have to rely on developers or testers remembering to run the tests. Also, each developer will typically only run the unit tests, each covering one unit only and this is the first opportunity to run the integration (component) tests. This test level is necessary because it tests whether the components behave as expected when used together (aggregated behavior).
- The software is automatically checked for compliance with project standards.
- The documentation is automatically kept up to date.
Typically, Build software like make, Ant or Maven and CI software like CruiseControl and Continuum is used to obtain CI.
Since this blog is all about testing Oracle code with QCTO we shall focus on the testing aspect of CI only. Future blog posts may describe how to cover the other CI components with Oracle projects.
Automating QCTO
The CI server needs to be able to automate running the tests in QCTO and detect any unexpected outcome. Any CI server will be able to integrate most tools and programs through the following interface:
- Run an executable or MS-DOS batch file (or UNIX/Linux shell script) with one or more parameters and wait for it to finish.
- Interpret the exit code, standard output and standard error from the process after it has finished.
Basically, this can be achieved by two different means with QCTO, described in the following.
QCTO Command Line Interface
The command line interface to the QCTO executable facilitates a way of executing either all test cases in a test suite or a test definition through the following command line parameters:
- /u=: Username.
- /p=
: Password.
- /d=: Oracle TNS alias.
- /s=: Test suite with given GUID is to be run.
- /S=: Test suite with given name is to be run.
- /t=: Test definition with given GUID is to be run.
- /T=: Test definition with given name is to be run.
- /r: Run the given test suite provided with /s or /S, or the test definition provided with /t or /T.
- /Close: Close after the tests have been run.
Example of a command line that executes the test suite called My_Test_Suite:
"C:\Program Files\Quest Software\Quest Code Tester for
Oracle\QuestCodeTesterOracle.exe" /u=qcto /p=qcto /d=test /S="My_Test_Suite" /r /Close
Unfortunately, using the command line interface has the following drawbacks:
- The exit code from the QCTO executable is always 0, even if one or more test cases failed. You will need to investigate the test results (for the latest run) in the QCTO repository yourself or automate this with a SQL*Plus script.
- You cannot name your test suites with spaces as the command line interface doesn't handle these correctly, even when you quote the test suite name with double quotes. An alternative is to use the GUID instead (with the /s option instead of /S).
- You don't have full control over which test cases are executed: Either all test cases in a given suite or all test cases in a given test definition are executed.
- You need to have the QCTO frontend installed on the CI server in order to be able to call it as part of the Build process.
QCTO API
The QCTO PL/SQL API provides an interface that is more useful for CI. Using the API you can control:
- Which test cases that are to be executed:
- All test cases in a given test suite (by name or GUID).
- All test cases in a given test definition (by name or GUID).
- Specific test cases (by name or GUID).
- Present the outcome (including return values and output parameter values) and status on standard output.
- Provide an exit code indicating failure to the CI server if any of the test cases failed.
The API is provided in the QU_TEST package, installed in the QCTO repository.
Test Suite
The following procedures in QU_TEST are provided for executing test suites:
- RUN_SUITE_BY_GUID: Runs a given test suite through its GUID.
- RUN_SUITE_BY_NAME: Runs a given test suite through its name.
Each of these is available in three different variants:
- Simple overall status is returned in an output parameter.
- Simple overall status is returned in an output parameter and another output parameter contains a ref cursor, through which you can get details of the test cases that were executed.
- Simple overall status is returned in an output parameter and another output parameter contains a nested table, through which you can get details of the test cases that were executed.
The usage of these is described in detail in the QCTO on-line help.
SQL> declare
2 l_result varchar2(32767);
3 begin
4 qu_test.run_suite_by_name('My_Test_Suite', l_result);
5 dbms_output.put_line('l_result = ''' || l_result || '''');
6 end;
7 /
l_result = 'RUNTIME-ERROR'
PL/SQL procedure successfully completed.
which shows that this API is not very helpful when one or more test cases fail as it doesn't provide any details.
Test Definition
The following procedures are provided for executing test definitions:
- RUN_TEST_FOR: Runs a given test definition and returns an overall status in an output parameter.
- RUN_TEST_FOR2: Runs a given test definition, returns an overall status in an output parameter and returns a ref cursor useful for obtaining result details.
- RUN_TEST_FOR3: Like RUN_TEST_FOR2 with additional information.
- RUN_TEST_FOR4: Like RUN_TEST_FOR3 but providing a nested table instead of a ref cursor.
The usage of these is described in detail in the QCTO on-line help.
QCTO_CI
Let's look at an example that uses the most powerful of the QCTO test procedures -- RUN_TEST_FOR4. The example package QCTO_CI also shows how to use a few of the tables in the QCTO repository, namely:
- QU_SUITE: Test suites.
- QU_HARNESS: Test definitions.
- QU_SUITE_HARNESS: Test suite/test definitions relationships.
- QU_UNIT_TEST: Unit tests.
- QU_TEST_CASE: Test cases.
The package contains two main functions, described in the following.
RUN_TEST_SUITE
Runs given QCTO test suite. Shows details about each test case on DBMS_OUTPUT. This function simply runs each test definition in the suite by calling RUN_TEST_DEFINITION for each, found through QU_SUITE, QU_SUITE_HARNESS and QU_HARNESS. It takes the following parameters:
- Owner of program under test.
- QCTO test suite name or GUID.
- Comma-separated list of LIKE expressions for test case names or GUIDs for the test cases to run. LIKE is only supported with the names, not the GUIDs as that doesn't make sense. Default NULL, which means all test cases.
The number of test cases that failed is returned.
RUN_TEST_DEFINITION
Runs given QCTO test definition. Shows details about each test case on DBMS_OUTPUT. DBMS_OUTPUT is disabled while the test is running. This function takes the following parameters:
- Owner of program under test.
- QCTO test definition name or GUID.
- Comma-separated list of LIKE expressions for test case names or GUIDs for the test cases to run. LIKE is only supported with the names, not the GUIDs as that doesn't make sense. Default NULL, which means all test cases.
The function basically does the following:
- Finds test name/GUID through the GUID/name provided for the test definition.
- Builds a list of GUIDs for the test cases that are to be run. This is resolved through the third parameter.
- Calls RUN_TEST_FOR4.
- Show the detailed results on DBMS_OUTPUT and count the number of test cases that failed.
- Returns number of failed test cases.
COMMA_TO_TABLE
This function converts comma-separated values to an array containing the values. DBMS_UTILITY.COMMA_TO_TABLE does not work with values that are not legal SQL identifiers.
TABLE_TO_COMMA
Converts array elements to comma-separated values. DBMS_UTILITY.TABLE_TO_COMMA does not work with values that are not legal SQL identifiers.
Source Code
create or replace package qcto_ci as
/**
* Quest Code Tester for Oracle Continuous Integration interface.
*/
function run_test_suite(
p_program_owner in varchar2,
p_test_suite in varchar2,
p_test_cases in varchar2 := null
)
return integer;
function run_test_definition(
p_program_owner in varchar2,
p_test_definition in varchar2,
p_test_cases in varchar2 := null,
p_indent in pls_integer := 0
)
return integer;
function comma_to_table(p_s in varchar2)
return dbms_utility.uncl_array;
function table_to_comma(p_t in dbms_utility.uncl_array)
return varchar2;
end qcto_ci;
/
create or replace package body qcto_ci as
c_delimiter constant char(1) := ',';
/**
* Runs given Quest Code Tester for Oracle test suite. Shows details about each
* test case on DBMS_OUTPUT.
* @param p_program_owner
* Owner of program under test.
* @param p_test_suite
* QCTO test suite name or GUID.
* @param p_test_cases
* Comma-separated list of LIKE expressions for test case names
* or GUIDs for the test cases to run. LIKE is only supported
* with the names, not the GUIDs as that doesn't make sense.
* Default NULL, which means all test cases.
* @return Number of test cases that failed during the execution.
*/
function run_test_suite(
p_program_owner in varchar2,
p_test_suite in varchar2,
p_test_cases in varchar2 := null
)
return integer as
l_failures integer := 0;
begin
for s in (
select qs.name suite_name,
qs.universal_id
from qu_suite qs
where (
qs.universal_id = p_test_suite
or qs.name = p_test_suite
)
) loop
dbms_output.put_line('SUITE ' || s.suite_name);
for td in (
select qsh.harness_guid,
qh.program_name test_definition_name
from qu_suite_harness qsh,
qu_harness qh
where qsh.suite_guid = s.universal_id
and qh.universal_id = qsh.harness_guid
) loop
l_failures := l_failures + run_test_definition(
p_program_owner,
td.harness_guid /*td.test_definition_name*/,
p_test_cases,
1
);
end loop;
dbms_output.put_line(
'SUITE ' || s.suite_name || ': ' ||
l_failures || ' test case(s) failed'
);
end loop;
return l_failures;
end run_test_suite;
/**
* Runs given Quest Code Tester for Oracle test definition. Shows details about
* each test case on DBMS_OUTPUT. DBMS_OUTPUT is disabled while the test is
* running.
* @param p_program_owner
* Owner of program under test.
* @param p_test_definition
* QCTO test definition name or GUID.
* @param p_test_cases
* Comma-separated list of LIKE expressions for test case names
* or GUIDs for the test cases to run. LIKE is only supported
* with the names, not the GUIDs as that doesn't make sense.
* Default NULL, which means all test cases.
* @return Number of test cases that failed during the execution.
*/
function run_test_definition(
p_program_owner in varchar2,
p_test_definition in varchar2,
p_test_cases in varchar2 := null,
p_indent in pls_integer := 0
)
return integer as
l_result varchar2(100);
l_results qu_result_xp.last_run_results_api_nt;
l_failures integer := 0;
l_test_name varchar2(32767);
l_test_cases_csv varchar2(32767);
l_test_cases_array dbms_utility.uncl_array;
l_test_case_guid_array dbms_utility.uncl_array;
l_test_case_guid_csv varchar2(32767);
l_test_guid varchar2(100);
l_n binary_integer;
begin
if p_test_cases is not null then
-- Expand test case name filters by their GUIDs.
if p_test_definition like '{%}' then
l_test_guid := p_test_definition;
-- Find test name through GUID provided.
select program_name
into l_test_name
from qu_harness qh
where qh.universal_id = p_test_definition;
else
l_test_name := p_test_definition;
-- Find test GUID through name provided.
select qh.universal_id
into l_test_guid
from qu_harness qh
where qh.program_name = p_test_definition;
end if;
-- Make sure all test cases are GUIDs.
l_test_cases_array := comma_to_table(p_test_cases);
for i in
nvl(l_test_cases_array.first, 0) ..
nvl(l_test_cases_array.last, -1) loop
if l_test_cases_array(i) not like '{%}' then
for tc in (
select qtc.universal_id
from qu_unit_test qut,
qu_test_case qtc
where qut.harness_guid = l_test_guid
and qtc.unit_test_guid = qut.universal_id
and qtc.name like l_test_cases_array(i)
) loop
l_test_case_guid_array(l_test_case_guid_array.count + 1) :=
tc.universal_id;
end loop;
else
l_test_case_guid_array(l_test_case_guid_array.count + 1) :=
l_test_cases_array(i);
end if;
end loop;
l_test_case_guid_csv := table_to_comma(l_test_case_guid_array);
end if;
if p_test_cases is null or
p_test_cases is not null and l_test_case_guid_array.count > 0 then
-- Only run if we have been asked to run all test cases or if we have been
-- asked to run specific test cases and we found them.
qu_test.run_test_for4(
p_program_owner,
l_test_name,
l_result,
l_results,
null,
l_test_case_guid_csv,
',',
null
);
end if;
if l_results is not null then
for i in nvl(l_results.first, 0) .. nvl(l_results.last, -1) loop
if l_results(i).result_level not in ('INPUT', 'OUTPUT') and
l_results(i).result_status is not null then
-- Don't show input/output parameters but show the rest if they have been
-- executed (RESULT_STATUS not NULL).
dbms_output.put(
rpad (' ', (p_indent + l_results(i).h_level - 1) * 2) ||
l_results(i).result_level || ' ' ||
l_results(i).name ||
': '
);
if l_results(i).result_level in ('UNITTEST', 'TESTCASE') then
-- Only show start/stop for tests and test cases.
dbms_output.put(
'Started: ' ||
to_char(l_results(i).start_on, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
'Stopped: ' ||
to_char(l_results(i).end_on, 'yyyy-mm-dd hh24:mi:ss') || ' ' ||
'(' || round((l_results(i).end_on - l_results(i).start_on) * 86400) ||
's) '
);
end if;
dbms_output.put(l_results(i).result_status);
if l_results(i).result_status != 'SUCCESS' then
dbms_output.put(': ' || l_results(i).description);
end if;
dbms_output.put_line('');
if l_results(i).result_level = 'TESTCASE' and
l_results(i).result_status != 'SUCCESS' then
l_failures := l_failures + 1;
end if;
end if;
end loop;
end if;
dbms_output.put_line(
rpad(' ', p_indent * 2) || 'HARNESS ' ||
p_program_owner || '.' || l_test_name || ': ' ||
l_failures || ' test case(s) failed'
);
return l_failures;
end run_test_definition;
/**
* Converts comma-separated values to an array containing the values.
* DBMS_UTILITY.COMMA_TO_TABLE does not work with values that are not legal
* SQL identifiers.
* @param p_s Comma-separated values.
* @return Array of values.
*/
function comma_to_table(p_s in varchar2)
return dbms_utility.uncl_array as
i pls_integer;
j pls_integer;
l pls_integer;
l_result dbms_utility.uncl_array;
begin
i := 1;
j := instr(p_s, c_delimiter, i, 1);
if j = 0 then
j := l + 1;
end if;
l := nvl(length(p_s), 0);
while i <= l loop
l_result(l_result.count + 1) := substr(p_s, i, j - i);
i := j + 1;
j := instr(p_s, c_delimiter, i, 1);
if j = 0 then
j := l + 1;
end if;
end loop;
return l_result;
end comma_to_table;
/**
* Converts array elements to comma-separated values.
* DBMS_UTILITY.TABLE_TO_COMMA does not work with values that are not legal SQL
* identifiers.
* @param p_t Array of values.
* @return Comma-separated values.
*/
function table_to_comma(p_t in dbms_utility.uncl_array)
return varchar2 as
l_result varchar2(32767);
begin
for i in nvl(p_t.first, 0) .. nvl(p_t.last, -1) loop
l_result := l_result || p_t(i) || c_delimiter;
end loop;
return rtrim(l_result, c_delimiter);
end table_to_comma;
end qcto_ci;
/
Interfacing via SQL*Plus
By calling one of the two functions in the QCTO_CI package from a SQL*Plus script we can incorporate it into the Build process. Such a script run_qcto_test_definition.sql could look like the following (please note how we use the number of test cases that failed as the exit code):
set serveroutput on format truncated
set scan off
set define &
set scan on
set verify off
set feedback off
variable l_failures number
begin
:l_failures := 1;
end;
/
begin
:l_failures := qcto_ci.run_test_definition('&&1', '&&2', '&&3');
end;
/
exit :l_failures
The script takes 3 parameters corresponding to the parameters of the function. The SQL*Plus script could in turn be called from an MS-DOS batch script run_qcto_test_definition.bat (assuming that this also takes 3 parameters):
call sqlplus -s /@ @run_qcto_test_definition.sql "%1" "%2" "%3"
exit %ERRORLEVEL%
This is the MS-DOS batch script that would be called directly from the CI server. If you need to call this through cmd.exe (eg when you execute it from a Java environment) you will have to escape the percent character with an extra one in case you use that for the LIKE expressions as they would be regarded as references to environment variables. Example of how to call a test definition for the TEST_DATA_TYPES package owned by SCOTT and only executing the test cases with names matching '%BLOB%', '%CLOB%' or '%BOOLEAN%':
run_qcto_test_definition.bat SCOTT TEST_DATA_TYPES "%%BLOB%%,%%CLOB%%,%%BOOLEAN%%"
Output
The output produced can easily be tailored to your requirements in terms of details and format (eg HTML or XML). The source code provided in this blog post produces a plain output as illustrated by the following example:
set serveroutput on format truncated
SQL> begin
2 dbms_output.put_line(
3 qcto_ci.run_test_definition(
4 'SCOTT',
5 'TEST_DATA_TYPES',
6 '%BLOB%,%CLOB%,%BOOLEAN%'
7 )
8 );
9 end;
10 /
HARNESS SCOTT.TEST_DATA_TYPES: FAILURE: Test failed at this level.
UNITTEST TEST_BLOB: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
TESTCASE TEST_BLOB S: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
OUTCOME Scalar is equal to the expected value?: SUCCESS
OUTCOME Value modified by the program is NULL?: SUCCESS
UNITTEST TEST_BOOLEAN: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) FAILURE: Test failed at this level.
TESTCASE TEST_BOOLEAN S: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
OUTCOME Does the Boolean expression evaluate to FALSE?: SUCCESS
OUTARG B2: SUCCESS
OUTARG [Function return value]: SUCCESS
OUTCOME Value modified by the program is NULL?: SUCCESS
TESTCASE TEST_BOOLEAN F: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) FAILURE: Test failed at this level.
OUTCOME Does the Boolean expression evaluate to TRUE?: SUCCESS
OUTARG [Function return value]: SUCCESS
OUTCOME Does the Boolean expression evaluate to TRUE?: FAILURE: Expression evaluates to NULL.
OUTARG B2: SUCCESS
UNITTEST TEST_CLOB: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
TESTCASE TEST_CLOB S: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
OUTARG S: SUCCESS
OUTARG [Function return value]: SUCCESS
OUTCOME Scalar is equal to the expected value?: SUCCESS
OUTCOME Scalar is equal to the expected value?: SUCCESS
TESTCASE TEST_CLOB 32000: Started: 2010-01-13 19:49:43 Stopped: 2010-01-13 19:49:43 (0s) SUCCESS
OUTARG [Function return value]: SUCCESS
OUTCOME Scalar is equal to the expected value?: SUCCESS
OUTARG S: SUCCESS
OUTCOME Scalar is equal to the expected value?: SUCCESS
HARNESS SCOTT.TEST_DATA_TYPES: 1 test case(s) failed
1
PL/SQL procedure successfully completed.
Naming Conventions
Through a naming convention you can categorize your tests into the test level they belong to, for instance:
- Unit test case names are prefixed with a U.
- Integration/component test case names are prefixed with an I or a C.
- System test cases are prefixed with an S.
Such a naming convention would enable you to run all the unit and integration tests in a given test definition through a call like:
begin
dbms_output.put_line(
qcto_ci.run_test_definition(
'SCOTT',
'TEST_DATA_TYPES',
'U%,I%'
)
);
end;
/
References
- Continuous Integration: Improving Software Quality and Reducing Risk by Paul M. Duvall, Steve Matyas and Andrew Glover, Addison Wesley, 2007. ISBN 978-0321336385.
- Wikipedia: http://en.wikipedia.org/wiki/Continuous_integration.
- Ambient Orb: http://www.ambientdevices.com/cat/orb/orborder.html.