-
Notifications
You must be signed in to change notification settings - Fork 188
Expand file tree
/
Copy pathtestapi.html
More file actions
329 lines (298 loc) · 11.1 KB
/
testapi.html
File metadata and controls
329 lines (298 loc) · 11.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title></title>
</head>
<body>
<!-- Begin utPLSQL Body -->
<!-- $Id$ -->
<h1>Test an Entire Package API</h1>
<p>
Most packages consist of lots more than a single program, and you will
generally want to test each and every of the programs listed in the package
specification. When you generate a test package with <a href="utgen.html">utGen</a>,
it will produce a template unit test procedure for each program in the
package specification. You will then need to modify each of these programs.
</p>
<p>
One example of this more complex package structure is the table encapsulation
package. This kind of package establishes a layer of code and therefore
control between application requirements and underlying data structures.
While the building of such a layer is uncommon in the world of PL/SQL developers,
it is strongly recommended practice. A variety of tools, in fact, offer
automated table encapsulation package generation, including <a href="http://www.oracle.com">Oracle
Designer</a>, <a href="http://www.revealnet.com">RevealNet</a>'s PL/Generator
and a variety of IDE (integrated development environment) tools.
</p>
<p>
Suppose, then, that I used PL/Generator to generate a table encapsulation
package for the employee table. It would look like the code found in te_employee.pks
and te_employee.pkb<a href="#footnote">(1)</a> (being rather
lengthy, we will not reproduce it in the documentation. If you take a look,
you will see that their are dozens of programs in the API, which means
that you would have lots of work to do in building your unit test cases.
In addition, many of the programs will be performing DML operations (updating,
deleting, inserting). How you can easily and dependably test those programs?
</p>
<p>
When you are dealing with lots of programs that have a uniform structure
and behavior (which <i>should</i> be the case if you are building table
API packages), then you should look for ways to <i>generate</i>, rather
than write manually, your test package. utGen cannot do this generation
work for you, since the logic in your encapsulation package is specific
to your environment.
</p>
<p>
You can, instead, build your own custom generator or use an existing
generator that is sufficiently flexible to meet your needs. The original
creator of utPLSQL, <a href="http://www.stevenfeuerstein.com">Steven Feuerstein</a>,
has also been working on generator utilities for a number of years. One
of these utilities, currently "code named" GenX, came in very handy for
creating a test package for his PL/Generator-generated encapsulation packages.
</p>
<p>
Using CGML (Code Generation Markup Language), Steven created a template
(See te_utpkg.gdr in the Examples directory of the utPLSQL distribution)
that reads information from the data dictionary and defines the setup,
teardown and at least a good starting point for the unit test procedures.
Here is the template logic for the setup procedure:
</p>
<pre>
PROCEDURE {utprefix}setup
IS
BEGIN
-- Clean start
{utprefix}teardown;
[ASIS]
-- Generic copy of base table for testing
EXECUTE IMMEDIATE
'CREATE TABLE {tabprefix}[objname] AS
SELECT * FROM [objname]';
[ENDASIS]
[FOREACH]prog
[IF]{allprogs}[EQ]Y[OR][progname][LIKE]UPD%[OR][progname][LIKE]INS%[OR][progname][LIKE]DEL%
-- Create copy of base table for this unit test.
EXECUTE IMMEDIATE
'CREATE TABLE ^{progtab}^ AS
[ASIS]
SELECT * FROM [objname]';
[ENDASIS]
[ENDIF]
[ENDFOREACH]
END;
</pre>
<p>
You are not, of course, expected to understand all the logic and syntax
in this fragment. If you are interested in pursuing these sorts of genreation
opportunities and would like to check out GenX, drop a note to <a href="mailto:steven@stevenfeuerstein.com">Steven
Feuerstein</a>.
</p>
<p>
Here is a portion of the generated logic (found in ut_te_employee.pks
and ut_te_employee.pkb"<a href="#footnote">(1)</a>), the
program that tests the delete operation in the encapsulation package:
</p>
<pre>
PROCEDURE ut_del1
IS
fdbk PLS_INTEGER;
BEGIN
/* Delete that finds now rows. */
EXECUTE IMMEDIATE '
DELETE FROM ut_DEL1
WHERE employee_id = -1
';
te_employee.del (-1, rowcount_out => fdbk);
-- Test results
utassert.eqtable ('Delete rows', 'EMPLOYEE', 'ut_DEL1');
/* Successful delete */
EXECUTE IMMEDIATE '
DELETE FROM ut_DEL1
WHERE employee_id between 7800 and 7899
';
FOR rec IN (SELECT *
FROM employee
WHERE employee_id BETWEEN 7800 AND 7899)
LOOP
te_employee.del (
rec.employee_id,
rowcount_out => fdbk
);
END LOOP;
-- Test results
utassert.eqtable ('Delete rows', 'EMPLOYEE', 'ut_DEL1');
ROLLBACK;
EXCEPTION
WHEN OTHERS
THEN
utassert.this (
'DEL1 exception ' || SQLERRM,
SQLCODE = 0
);
END;
</pre>
<p>
In this procedure, I test for two scenarios: a delete that removes zero
rows and a delete that removes a specific set of rows. In both cases, I
perform the explicit (non-encapsulated) DML logic against a <i>copy</i>
of the actual table (this copy is created in the <a href="#SetupAPI">setup
procedure</a>; that is the reason I use dynamic SQL to refer to this table
-- it doesn't exist when the package is compiled!). Then I do the (hopefully)
same operation by using the API program. Finally, I call the appropriate
utAssert assertion program to compare the results -- and at the end of
the procedure issue a ROLLBACK so that my "source" table (employee, in
this case), i set back to the original data state. Notice that I also put
an assertion program in the exception section to trap any errors and flag
it as a failed test.
</p>
<p>
That should give you a good feel for the kind of code you might write
to test a table encapsulation package. The next two sections show you how
I used the setup and teardown procedures to manage the data structures
I use in my tests.
</p>
<h3><a name="SetupAPI"></a>Set Up Data Structures</h3>
<p>
As I contemplated how best to test these large packages, I revisited some
of my testing principles and found one to be of particular importance:
</p>
<p><b>Build isolated tests.</b></p>
<p>
This principle is important because it allows you to run one, all or
a subset of your tests without having to worry about the impact or dependencies
on the other tests. And test isolation is <i>particularly </i>important
when testing DML operations. The way to validate a successful DML operation
is by analyzing the contents of the "source" table against a "test" table.
If all the tests modify the same test table, ti will be very difficult
if not impossible to verify success or notice failure.
</p>
<p>
So I decided that the best way to run my unit tests for DML operations
was to create a separate test table for each unit test. As a consequence,
my setup procedure for the te_employee package looks like this:
(See ut_te_employee.pkb in the Examples directory of the utPLSQL distribution)
</p>
<pre>
PROCEDURE ut_setup
IS
BEGIN
ut_teardown;
EXECUTE IMMEDIATE 'CREATE TABLE ut_employee AS
SELECT * FROM employee';
EXECUTE IMMEDIATE 'CREATE TABLE ut_DEL1 AS
SELECT * FROM employee';
EXECUTE IMMEDIATE 'CREATE TABLE ut_DELBY_EMP_DEPT_LOOKUP AS
SELECT * FROM employee';
EXECUTE IMMEDIATE 'CREATE TABLE ut_DELBY_EMP_JOB_LOOKUP AS
SELECT * FROM employee';
EXECUTE IMMEDIATE 'CREATE TABLE ut_DELBY_EMP_MGR_LOOKUP AS
SELECT * FROM employee';
EXECUTE IMMEDIATE 'CREATE TABLE ut_INS1 AS
SELECT * FROM employee';
EXECUTE IMMEDIATE 'CREATE TABLE ut_UPD1 AS
SELECT * FROM employee';
EXECUTE IMMEDIATE 'CREATE TABLE ut_UPD$HIRE_DATE1 AS
SELECT * FROM employee';
EXECUTE IMMEDIATE 'CREATE TABLE ut_UPD$SALARY1 AS
SELECT * FROM employee';
END;
</pre>
<p>
I first remove all my data structures using the teardown procedure to make
sure I have a clean start. Then I use dynamic SQL (the Oracle8i version)
to create all my tables. I must rely on dynamic SQL because PL/SQL does
not yet support native DDL statements, such as CREATE TABLE.
</p>
<p>
Then I am set to test.
</p>
<h3><a name="TeardownAPI"></a>Tear Down Data Structures</h3>
<p>
Well, if I am going to create a whole bunch of data structures to run my
tests, I had better get rid of those structures when I am done. Here is
the teardown program I generated for the te_employee package:
</p>
<pre>
PROCEDURE ut_teardown
IS
BEGIN
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ut_employee';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ut_DEL1';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ut_DELBY_EMP_DEPT_LOOKUP';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ut_DELBY_EMP_JOB_LOOKUP';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ut_DELBY_EMP_MGR_LOOKUP';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ut_INS1';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ut_UPD1';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ut_UPD$HIRE_DATE1';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ut_UPD$SALARY1';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END;
</pre>
<p>
Again, I use dynamic SQL, but enclose each DROP TABLE statement inside
its own exception section so that if for any reason the DROP fails, I continue
on in an attempt to get as much done as possible.
</p>
<hr />
<h3><a name="footnote">Footnotes</a></h3>
<p>
1. These files are in the Examples directory of the utPLSQL distribution.
</p>
<!-- End utPLSQL Body -->
</body>
</html>