Skip to content

Commit 7e32cba

Browse files
DDieterichDDieterich
committed
Numerous changes for readability, removal of elapsed, PROF table comments, Error message updates
1 parent 739f14f commit 7e32cba

16 files changed

Lines changed: 303 additions & 258 deletions

docs/About.md renamed to docs/About-wtPLSQL.md

File renamed without changes.

docs/Definitions.md renamed to docs/Other-Definitions.md

Lines changed: 1 addition & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -1,30 +1,8 @@
11
[Website Home Page](README.md)
22

3-
# Definitions
3+
# Other Definitions
44

55
---
6-
## wtPLSQL
7-
These are the working definitions for the wtPLSQL project.
8-
9-
**Annotation** - PL/SQL comment used to identify a DBOUT or exclude source code lines from code coverage data.
10-
11-
**Assertion** - A function that performs a single test and records/reports the result.
12-
13-
**Coverage** - An indication of the amount or percentage of source code tested.
14-
15-
**DBOUT** - Database Object Under Test. The database object that is the target of testing. White-box testing is oriented toward a specific DBOUT. Code coverage is also oriented toward a specific DBOUT.
16-
17-
**SUT** - System Under Test. This includes one or more DBOUTs that comprise an application or system. Continuous Integration (CI) testing is typically done at a system level.
18-
19-
**Setup** - Modifying the database or environment in preparation for a test, testcase, or test runner.
20-
21-
**Teardown** - Cleaning or restoring a database after a test, testcase, or test runner.
22-
23-
**Testcase** - A logical grouping of assertions to run happy path, decision tree, boundary condition, and/or fault insertion tests. May included one or more setup, teardown, and intermediate setups.
24-
25-
**Test Runner** - A PL/SQL package that exercises a DBOUT and uses assertions to confirm the correct funcionality of the DBOUT. It may have zero or more testcases. It always contains a call to the WTPLSQL.TEST_RUN procedure. It may contain DBOUT annotations and/or "exclude source lines" annotations.
26-
27-
***
286
## Oracle Database
297
Note: Some Oracle database terms overlap with Object Oriented terms.
308

‎docs/README.md‎

Lines changed: 39 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -9,26 +9,25 @@ Use [GitHub "issues"](https://github.com/DDieterich/wtPLSQL/issues) for support.
99

1010
## Example wtPLSQL Test Results
1111

12-
This is the summary from the WT_ASSERT package self-test. This is the default
13-
DBMS_OUTPUT format.
12+
This is the summary from the WT_ASSERT package self-test. This is the default DBMS_OUTPUT format. Because test results and code coverage is stored in Oracle tables, other report formats a simple to create.
1413

1514
```
16-
wtPLSQL 1.1.0 - Run ID 421: 20-May-2018 03:39:54 PM
15+
wtPLSQL 1.1.0 - Run ID 7: 09-Jun-2018 11:48:42 AM
1716
1817
Test Results for WTP.WT_ASSERT
19-
Total Testcases: 150 Total Assertions: 404
20-
Minimum Elapsed msec: 0 Failed Assertions: 0
21-
Average Elapsed msec: 7 Error Assertions: 0
22-
Maximum Elapsed msec: 1134 Test Yield: 100.00%
23-
Total Run Time (sec): 2.9
18+
Total Testcases: 150 Total Assertions: 404
19+
Minimum Interval msec: 0 Failed Assertions: 0
20+
Average Interval msec: 7 Error Assertions: 0
21+
Maximum Interval msec: 761 Test Yield: 100.00%
22+
Total Run Time (sec): 2.8
2423
2524
Code Coverage for PACKAGE BODY WTP.WT_ASSERT
26-
Ignored Lines: 1103 Total Profiled Lines: 1464
27-
Excluded Lines: 6 Total Executed Lines: 309
28-
Minimum Elapsed usec: 0 Not Executed Lines: 0
29-
Average Elapsed usec: 3054 Unknown Lines: 46
30-
Maximum Elapsed usec: 332876 Code Coverage: 100.00%
31-
Trigger Source Offset: 0
25+
Ignored Lines: 1103 Total Profiled Lines: 1464
26+
Excluded Lines: 6 Total Executed Lines: 309
27+
Minimum LineExec usec: 0 Not Executed Lines: 0
28+
Average LineExec usec: 394 Unknown Lines: 46
29+
Maximum LineExec usec: 65814 Code Coverage: 100.00%
30+
Trigger Source Offset: 0
3231
```
3332

3433
To view the complete test results from the wtPLSQL self-test, go to the [test_allO.LST](https://github.com/DDieterich/wtPLSQL/blob/master/src/core/test_allO.LST) file in GitHub.
@@ -38,15 +37,35 @@ To view the complete test results from the wtPLSQL self-test, go to the [test_al
3837

3938
wtPLSQL helps with white-box testing of Oracle database objects. It is particularly well suited for unit testing and simple integration testing. It is written in PL/SQL. It contains a self-test which makes it easier to support and customize.
4039

41-
Like utPLSQL, wtPLSQL provides a set of assertion tests that can be used to determine how well an Oracle database object is performing. These assertions record the outcome (success or failure) of each test. These assertions also record the time between calls. A test runner (PL/SQL package) must be created with these assertion tests included. When the test runner is executed, the outcome and timing of the assertion tests are recorded. The [Core Features page](Core-Features.md) introduces the main functionality of wtPLSQL.
40+
Like utPLSQL, wtPLSQL provides a set of assertion tests that can be used to determine how well an Oracle database object is performing. These assertions record the outcome (success or failure) of each test. These assertions also record the time between calls. A test runner (PL/SQL package) must be created with these assertion tests included. The [Core Features page](Core-Features.md) introduces the main functionality of wtPLSQL.
4241

43-
A simple text based reporting package called "WT_TEXT_REPORT" is included with the core installation. Custom reports are easy to create because the outcome and timing data is stored in the Oracle database. A set of DBDocs and E-R diagrams are provided to assist with any reporting customization.
42+
A simple text based reporting package called "WT_TEXT_REPORT" is included with the core installation. Custom reports are easy to create because the assertion outcomes and interval time between assertions are stored in the Oracle database. A set of DBDocs and E-R diagrams are provided to assist with any reporting customization.
4443

45-
Because wtPLSQL is for PL/SQL developers, a [Best Practices page](Best-Practices.md) has some guidance for creating Test Runner packages in PL/SQL.
44+
Because all testing with wtPLSQL is for driven by custom PL/SQL packages, a [Best Practices page](Best-Practices.md) has some guidance for creating Test Runner packages.
4645

47-
The [About page](About.md) has more information about the history and testing methodology of wtPLSQL.
46+
The [About wtPLQSL page](About-wtPLSQL.md) has more information about the history and testing methodology of wtPLSQL.
4847

49-
The [Definitions page](Definitions.md) includes definitions from many sources to help define the terms used in various software testing methodologies.
48+
## wtPLSQL Definitions
49+
50+
These are the working definitions for the wtPLSQL project.
51+
52+
**Annotation** - PL/SQL comment used to identify a DBOUT or ignore source code lines from code coverage data.
53+
54+
**Assertion** - A function that performs a single test and records/reports the result.
55+
56+
**Coverage** - An indication of the amount or percentage of source code tested.
57+
58+
**DBOUT** - Database Object Under Test. The database object that is the target of testing. White-box testing is oriented toward a specific DBOUT. Code coverage is also oriented toward a specific DBOUT.
59+
60+
**Setup** - Modifying the database or environment in preparation for an assertion, testcase, or group of either.
61+
62+
**Teardown** - Restoring a database or environment after an assertion, testcase, or group of either.
63+
64+
**Testcase** - A logical grouping of assertions to run happy path, decision tree, boundary condition, and/or fault insertion tests. May included one or more setup, teardown, and intermediate setups.
65+
66+
**Test Runner** - A PL/SQL package that exercises a DBOUT and uses assertions to confirm the correct funcionality of the DBOUT. It may have zero or more testcases. It always contains a call to the WTPLSQL.TEST_RUN procedure. It may contain DBOUT annotations and/or "exclude source lines" annotations.
67+
68+
The [Other Definitions page](Other-Definitions.md) includes definitions from many sources to help define the terms used in various software testing methodologies.
5069

5170
## How does wtPLSQL compare to utPLSQL V3?
5271

@@ -61,7 +80,7 @@ wtPLSQL has a different focus than utPLSQL V3. More information is available [i
6180
### Site Map
6281

6382
* [Core Features](Core-Features.md)
64-
* [About](About.md)
83+
* [About wtPLSQL](About-wtPLSQL.md)
6584
* [Best Practices](Best-Practices.md)
6685
* [Definitions](Definitions.md)
6786
* [utPLSQL V3 Comparison](utPLSQL-V3-Comparison)

‎docs/utPLSQL-V3-Comparison.md‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -17,15 +17,15 @@ Abbreviations:
1717
## Goals
1818
The "ut3" project ["follows industry standards and best patterns of modern Unit Testing frameworks like JUnit and RSpec"](https://github.com/utPLSQL/utPLSQL).
1919

20-
The "wt" project avoids "unit testing" by adopting practices for ["white box testing"](https://github.com/DDieterich/wtPLSQL/wiki/About#white-box-testing).
20+
The "wt" project avoids "unit testing" by adopting practices for ["white box testing"](https://github.com/DDieterich/wtPLSQL/wiki/About-wtPLSQL#white-box-testing).
2121

2222
## Customization
2323
The "ut3" project incorporates a wide variety of technologies and platforms. It also has a large and diverse set of capabilities that will reduce the need for customization.
2424

2525
The "wt" project is centered on one platform with a very simple implementation. It is easier to customize smaller, simpler systems.
2626

2727
## Testing Methodologies
28-
There is a longer discussion about unit testing methodologies in the [About Page](https://github.com/DDieterich/wtPLSQL/wiki/About#unit-testing).
28+
There is a longer discussion about unit testing methodologies in the [About wtPSQL Page](https://github.com/DDieterich/wtPLSQL/wiki/About-wtPLSQL#unit-testing).
2929

3030
Fundamentally, the Oracle database is a relational database. The relational database is based on transaction processing. Data is stored and shared in a precise manner between processes.
3131

@@ -58,7 +58,7 @@ TDD places no value on 100% code coverage. TDD typically avoids testing sad path
5858

5959
White box testing is centered on 100% code coverage. "Happy path" and "sad path" testing are typically required to achieve 100% code coverage.
6060

61-
Here is more discussion on [Test Driven Development](About.html#test-driven-development)
61+
Here is more discussion on [Test Driven Development](About-wtPLSQL.html#test-driven-development)
6262

6363
---
6464
[Website Home Page](README.md)

‎src/core/README.txt‎

Lines changed: 9 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -52,14 +52,12 @@ UnInstall Procedure:
5252

5353
Custom Error Codes:
5454
-------------------
55-
20001 - WTPLSQL Runner Name is NULL
56-
20002 - WTPLSQL Runner Name is not valid
57-
20003 - WT_ASSERT User Test Result is FAIL (g_raise_exception is TRUE)
58-
20004 - WT_PROFILER Test Run ID is NULL
59-
20005 - WT_PROFILER dbms_profiler.INTERNAL_VERSION_CHECK failed
60-
20006 - WT_PROFILER dbms_profiler.START_PROFILER failed
61-
20007 - WT_PROFILER g_rec.test_run_id is null
62-
20008 - WT_PROFILER Regular Expression Failure from NOT_EXECUTABLE
63-
20009 - WT_RESULT "in_test_run_id" cannot be NULL
64-
20010 - WT_TEST_RUN_STAT Unknown WT_RESUTS.STATUS "status"
65-
20011 - WT_TEST_RUN_STAT Unknown WT_DBOUT_PROFILES.STATUS "status"
55+
20001 - WTPLSQL: RUNNER_NAME is NULL
56+
20002 - WTPLSQL: RUNNER_NAME (name) is not valid
57+
20003 - WT_ASSERT: User Test Result is FAIL (g_raise_exception is TRUE)
58+
20004 - WT_PROFILER: in_test_run_id is NULL
59+
20005 - WT_PROFILER: dbms_profiler.INTERNAL_VERSION_CHECK returned (error)
60+
20006 - WT_PROFILER: dbms_profiler.START_PROFILER returned (error)
61+
20009 - WT_RESULT: "in_test_run_id" cannot be NULL
62+
20010 - WT_TEST_RUN_STAT: Unknown Result status
63+
20011 - WT_TEST_RUN_STAT: Unknown Profile status

‎src/core/install.sql‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,6 +7,7 @@
77

88
-- Capture output
99
spool install
10+
set showmode off
1011

1112
-- Shared Setup Script
1213
@common_setup.sql
@@ -133,6 +134,7 @@ WHENEVER SQLERROR continue
133134
-- Note2: Includes "Drop Table" and "Drop Sequence" statements
134135
--
135136
@proftab.sql
137+
@proftab_comments.sql
136138
--
137139
create index plsql_profiler_runs_idx1
138140
on plsql_profiler_runs (run_date);
@@ -189,4 +191,5 @@ grant execute on wt_text_report to public;
189191
@wt_text_report.pkb
190192
/
191193

194+
set showmode on
192195
spool off

‎src/core/proftab_comments.sql‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
2+
--
3+
-- Comments taken from
4+
-- Oracle Database Online Documentation 11g Release 2 (11.2)
5+
-- Database PL/SQL Packages and Types Reference
6+
-- https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_profil.htm#ARPLS67461
7+
--
8+
9+
comment on table PLSQL_PROFILER_RUNS is 'Table of profiler runs for DBMS_PROFILER';
10+
comment on column PLSQL_PROFILER_RUNS.runid is '(PRIMARY KEY) Unique run identifier from plsql_profiler_runnumber';
11+
comment on column PLSQL_PROFILER_RUNS.related_run is 'Runid of related run (for client/server correlation)';
12+
comment on column PLSQL_PROFILER_RUNS.run_owner is 'User who started run';
13+
comment on column PLSQL_PROFILER_RUNS.run_date is 'Start time of run';
14+
comment on column PLSQL_PROFILER_RUNS.run_comment is 'User provided comment for this run';
15+
comment on column PLSQL_PROFILER_RUNS.run_total_time is 'Elapsed time for this run in nanoseconds';
16+
comment on column PLSQL_PROFILER_RUNS.run_system_info is 'Currently unused';
17+
comment on column PLSQL_PROFILER_RUNS.run_comment1 is 'Additional comment';
18+
comment on column PLSQL_PROFILER_RUNS.spare1 is 'Unused';
19+
20+
comment on table PLSQL_PROFILER_UNITS is 'Table of program units for DBMS_PROFILER';
21+
comment on column PLSQL_PROFILER_UNITS.runid is '(Primary key) References plsql_profiler_runs';
22+
comment on column PLSQL_PROFILER_UNITS.unit_number is '(Primary key) Internally generated library unit #';
23+
comment on column PLSQL_PROFILER_UNITS.unit_type is 'Library unit type';
24+
comment on column PLSQL_PROFILER_UNITS.unit_owner is 'Library unit owner name';
25+
comment on column PLSQL_PROFILER_UNITS.unit_name is 'Library unit name timestamp on library unit';
26+
comment on column PLSQL_PROFILER_UNITS.unit_timestamp is 'In the future will be used to detect changes to unit between runs';
27+
comment on column PLSQL_PROFILER_UNITS.total_time is 'Total time spent in this unit in nanoseconds. The profiler does not set this field, but it is provided for the convenience of analysis tools';
28+
comment on column PLSQL_PROFILER_UNITS.spare1 is 'Unused';
29+
comment on column PLSQL_PROFILER_UNITS.spare2 is 'Unused';
30+
31+
comment on table PLSQL_PROFILER_DATA is 'Table of program units for DBMS_PROFILER';
32+
comment on column PLSQL_PROFILER_DATA.runid is 'Primary key, unique (generated) run identifier';
33+
comment on column PLSQL_PROFILER_DATA.unit_number is 'Primary key, internally generated library unit number';
34+
comment on column PLSQL_PROFILER_DATA.line# is 'Primary key, not null, line number in unit';
35+
comment on column PLSQL_PROFILER_DATA.total_occur is 'Number of times line was executed';
36+
comment on column PLSQL_PROFILER_DATA.total_time is 'Total time spent executing line in nanoseconds';
37+
comment on column PLSQL_PROFILER_DATA.min_time is 'Minimum execution time for this line in nanoseconds';
38+
comment on column PLSQL_PROFILER_DATA.max_time is 'Maximum execution time for this line in nanoseconds';
39+
comment on column PLSQL_PROFILER_DATA.spare1 is 'Unused';
40+
comment on column PLSQL_PROFILER_DATA.spare2 is 'Unused';
41+
comment on column PLSQL_PROFILER_DATA.spare3 is 'Unused';
42+
comment on column PLSQL_PROFILER_DATA.spare4 is 'Unused';

‎src/core/uninstall.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@
66
--
77

88
spool uninstall
9+
set showmode off
910

1011
@common_setup.sql
1112

@@ -42,4 +43,5 @@ begin
4243
end;
4344
/
4445

46+
set showmode on
4547
spool off

‎src/core/upgrades/update_all_stats.sql‎

Lines changed: 23 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -25,34 +25,34 @@ begin
2525
,sum(case status when 'FAIL' then 1 else 0 end)
2626
,sum(case status when 'ERR' then 1 else 0 end)
2727
,count(distinct testcase)
28-
,min(elapsed_msecs)
29-
,max(elapsed_msecs)
30-
,sum(elapsed_msecs)
28+
,min(interval_msecs)
29+
,max(interval_msecs)
30+
,sum(interval_msecs)
3131
into test_run_stats_rec.asserts
3232
,test_run_stats_rec.passes
3333
,test_run_stats_rec.failures
3434
,test_run_stats_rec.errors
3535
,test_run_stats_rec.testcases
36-
,test_run_stats_rec.min_elapsed_msecs
37-
,test_run_stats_rec.max_elapsed_msecs
38-
,test_run_stats_rec.tot_elapsed_msecs
36+
,test_run_stats_rec.min_interval_msecs
37+
,test_run_stats_rec.max_interval_msecs
38+
,test_run_stats_rec.tot_interval_msecs
3939
from wt_results
4040
where test_run_id = buff.id;
4141
--
4242
if test_run_stats_rec.asserts = 0
4343
then
4444
test_run_stats_rec.test_yield := NULL;
45-
test_run_stats_rec.avg_elapsed_msecs := NULL;
45+
test_run_stats_rec.avg_interval_msecs := NULL;
4646
else
4747
test_run_stats_rec.test_yield := round(test_run_stats_rec.passes /
4848
test_run_stats_rec.asserts, 3);
49-
test_run_stats_rec.avg_elapsed_msecs := round(test_run_stats_rec.tot_elapsed_msecs /
50-
test_run_stats_rec.asserts, 3);
49+
test_run_stats_rec.avg_interval_msecs := round(test_run_stats_rec.tot_interval_msecs /
50+
test_run_stats_rec.asserts, 3);
5151
end if;
5252
--
5353
select count(*)
5454
,sum(case status when 'EXEC' then 1 else 0 end)
55-
,sum(case status when 'ANNO' then 1 else 0 end)
55+
,sum(case status when 'IGNR' then 1 else 0 end)
5656
,sum(case status when 'EXCL' then 1 else 0 end)
5757
,sum(case status when 'NOTX' then 1 else 0 end)
5858
,sum(case status when 'UNKN' then 1 else 0 end)
@@ -61,7 +61,7 @@ begin
6161
,sum(case status when 'EXEC' then total_usecs/total_occur else 0 end)
6262
into test_run_stats_rec.profiled_lines
6363
,test_run_stats_rec.executed_lines
64-
,test_run_stats_rec.annotated_lines
64+
,test_run_stats_rec.ignored_lines
6565
,test_run_stats_rec.excluded_lines
6666
,test_run_stats_rec.notexec_lines
6767
,test_run_stats_rec.unknown_lines
@@ -109,34 +109,37 @@ begin
109109
from wt_results
110110
group by test_run_id, testcase)
111111
loop
112+
--
113+
testcase_stats_rec.test_run_id := buff.test_run_id;
114+
testcase_stats_rec.testcase := buff.testcase;
112115
--
113116
select count(*)
114117
,sum(case status when 'PASS' then 1 else 0 end)
115118
,sum(case status when 'FAIL' then 1 else 0 end)
116119
,sum(case status when 'ERR' then 1 else 0 end)
117-
,min(elapsed_msecs)
118-
,max(elapsed_msecs)
119-
,sum(elapsed_msecs)
120+
,min(interval_msecs)
121+
,max(interval_msecs)
122+
,sum(interval_msecs)
120123
into testcase_stats_rec.asserts
121124
,testcase_stats_rec.passes
122125
,testcase_stats_rec.failures
123126
,testcase_stats_rec.errors
124-
,testcase_stats_rec.min_elapsed_msecs
125-
,testcase_stats_rec.max_elapsed_msecs
126-
,testcase_stats_rec.tot_elapsed_msecs
127+
,testcase_stats_rec.min_interval_msecs
128+
,testcase_stats_rec.max_interval_msecs
129+
,testcase_stats_rec.tot_interval_msecs
127130
from wt_results
128131
where test_run_id = buff.test_run_id
129132
and testcase = buff.testcase;
130133
--
131134
if testcase_stats_rec.asserts = 0
132135
then
133136
testcase_stats_rec.test_yield := NULL;
134-
testcase_stats_rec.avg_elapsed_msecs := NULL;
137+
testcase_stats_rec.avg_interval_msecs := NULL;
135138
else
136139
testcase_stats_rec.test_yield := round(testcase_stats_rec.passes /
137140
testcase_stats_rec.asserts, 3);
138-
testcase_stats_rec.avg_elapsed_msecs := round(testcase_stats_rec.tot_elapsed_msecs /
139-
testcase_stats_rec.asserts, 3);
141+
testcase_stats_rec.avg_interval_msecs := round(testcase_stats_rec.tot_interval_msecs /
142+
testcase_stats_rec.asserts, 3);
140143
end if;
141144
--
142145
begin

0 commit comments

Comments
 (0)