By Steven Feuerstein
OTN Member since 2001

Ready to start coding? Hold your horses: you need to establish a design first

Installment 3 of Feuerstein's "Building a Code-Analysis Utility" series

Seems clear enough. Does that mean that my next step is to start writing code? No! Before I dive into the IFs and LOOPs, I need to put together enough of a design to guide my coding. In this article, I explore how I developed such a design and demonstrate how I was careful to avoid going into too much detail at the start.

Inputs And Outputs

Putting together a design for my utility starts with my asking, "What goes in and what comes out?" This question is a fundamental one for a program, because the inputs (which can range from parameters to underlying tables) drive the behavior of the program and there is no other way to detect the effect of a program than to examine its outputs (broadly defined).

All the inputs for OverloadCheck are:

• The name of the package or package.program that OverloadCheck will examine. Where overloadings are concerned, it doesn't make any sense to analyze standalone procedures or functions.
• The arguments associated with that program, as provided by ALL_ARGUMENTS and DBMS_DESCRIBE.

All the outputs from OverloadCheck are:

• Output to the screen displaying the results of the analysis. Are there any ambiguous overloadings? If so, what rules do they violate?
• Rows written to the cc_ambig_ovld_results with the same information.

First Thoughts on OverloadCheck's Architecture and Flow

Given those inputs and outputs, I can quickly come up with the concept for OverloadCheck's architecture (see Figure 1). The flow consists of four main stages:

1. Gather the data on the programs' arguments from ALL_ARGUMENTS and DBMS_DESCRIBE. I imagine I will load that information into a collection in OverloadCheck.
2. Analyze the argument information, and identify ambiguous overloadings, if any.
3. Write the results out to the screen and the results table.
4. Run utPLSQL to verify those results.

Figure 1. First thoughts on the overall flow for OverloadCheck

I suppose that does it—at a very high level. This is a good way to start, but it doesn't yet offer enough granularity to start coding.

Some Second (and Third, and...) Thoughts

Clearly, I need to think things through much more thoroughly before I can hope to produce a working and well-written OverloadCheck utility. Yet the reality is that I can't perform this thinking—and designing—independently of the code construction process. How many times have you come up with a good plan and started coding but then found reality intruding (as in: How did I forget about that?)—necessitating design changes?

Avoiding overdesigning is one of the most important principles I've learned from Extreme Programming (a lightweight methodology that takes accepted coding practices to their extreme and is the impetus for my utPLSQL unit-testing framework; see www.xprogramming.com for more information). Does it really make sense, for example, to come up with a master plan for a three-year development project when we all know that the plan will be shot full of holes within six months? In the context of OverloadCheck, does it really make sense for me to think everything through to the nth detail before building my packages?

The Extreme Programming folks like to ask this: What is the simplest way to make the code work?

I like the challenge of answering this question, because it makes me think on several levels at once. What is simple? It should mean

• Code that's transparent, easy to understand, change, and maintain
• Code that responds directly and intuitively to the requirements but does not reach beyond them

But what does work mean? The following criteria come immediately to mind:

• The code satisfies user requirements.
• The code executes within an acceptable amount of time.

I suggest, however, that another crucial reflection of the workability of a piece of code is its habitability, a term and concept offered by Richard Gabriel in his book Patterns of Software: Tales From The Software Community (Oxford University Press, 1996). He suggests that it is very important to write code that another programmer can inhabit—enter into with comfort and make changes without fear that the entire structure will collapse.

If a program is not habitable, it is also not maintainable. If it cannot be maintained without lots of pain, it may seem to work today but will have an extremely difficult time meeting requirements in the future.

I predict that over time an increasingly higher percentage of PL/SQL application code will be put into maintenance mode—enhancing and fixing existing, relatively stable applications. Therefore, it's crucial that we design and implement our code so that it is habitable.

A Simple, Workable Design for OverloadCheck

Following the Extreme Programming mantra, I would like to move beyond—but not too far beyond—my simplistic design (Figure 1). The most effective way to do that is to segregate distinct areas of functionality my utility will need and then create a package to hold that functionality. I like to think of these packages as convenient "buckets" into which I can drop programs and data types as I come up with new ideas.

Defining the buckets early on in the coding process helps keep the code base well organized and modular. Rather than end up with a few enormous blob programs that are difficult to manage, you find yourself working with a larger number of concise, narrowly purposed packages. It's relatively easy to change these packages, or even reorganize them, as your design evolves.

Enough, though, of the abstract discussion of this technique. Let's see how I apply it to OverloadCheck.

Going back to Design Version 1.0, here are my steps, in a nutshell: I accept the name of the package (or program within the package), get the argument data for that package, analyze the arguments (their number, datatypes, presence of default values, and so on), and then send the results to the screen or test repository.

Reflecting back on my many other programming exercises (I know that some sort of advantage has to accrue to a guy who's been writing programs since 1980, even one with an absolute minimum of formal education on the topic), I find myself thinking that specifying object names can be tricky. Do users provide the name of the package, its owner, and subprogram names as individual arguments or one argument? How do I determine whether that name references a valid, existing object? How do I make sure it is a package? You can write some complicated code that queries against ALL_OBJECTS, resolves synonyms (recursively perhaps), and so on. Or you can use the DBMS_UTILITY.NAME_RESOLVE procedure to do all the work for you.

I can tell right away that there will be lots of devil-in-the-details moments in building OverloadCheck—particularly as regards datatypes. DBMS_DESCRIBE uses numeric codes; ALL_ARGUMENTS offers strings. If I don't want my code to become an embarrassing mess in no time, I'll need to hide those details behind lots of enumerated constants and programs.

You've already seen the complexities of my argument information sources. I need to be able to cleanly extract, merge, and then draw conclusions from that raw data. It will probably involve a fair amount of postprocessing.

Reporting on the results of my analysis will also be a nit-picky affair: How do I avoid the pitfalls of DBMS_OUTPUT? How do I want the output to look?

I'm inevitably going to need some small utilities for my different buckets to share. For example, I strenuously avoid direct calls to DBMS_OUTPUT.PUT_LINE, given all of its nuisance value.

It's only going to get more and more complicated as I dive ever more deeply into ALL_ARGUMENTS and DBMS_DESCRIBE and try to handle more-challenging types of analysis. If I try to fool myself into thinking it's simple and that I can just knock it out in an evening, I will be doomed to failure. I may not want to elaborate every possible issue in advance, but I also want to be prepared for change.

Oh, and don't forget about testing. I'll need a utPLSQL-compatible package to hold my unit-test procedures.

After cogitating on all that and more, I find myself drawn to the buckets of functionality shown in Figure 2. It isn't enough to simply identify distinct areas such as that. I also need to be as clear as possible up front about the purpose of each bucket, what the bucket contains, what you can put in it, and what can be taken out. Another way of putting it is that for each bucket, I need to define the handshakes—how a program communicates with that bucket and how the bucket communicates back to the calling program.

If I keep the distinctions among my buckets nice and clean, it will be easier to add functionality in any of the given areas and also repair my programs over time. There will be a minimum of entanglements or codependencies among the buckets. It will be clear where to put that new function or procedure and, therefore, where to find it (most important of all, because how else could you use it?).

Figure 2. OverloadCheck functionality buckets

Table 1 provides an explanation of the OverloadCheck buckets (which now translate directly to packages). When I segregate my code like this, I also inevitably think in terms of a hierarchy of packages. At the very top of the hierarchy is the most complex package or program, built on top of lots of other elements. At the very bottom are my atomic packages—small, highly focused packages that serve one purpose and do not have lots of dependencies on other packages.

Figure 3 shows my conception of the hierarchy of OverloadCheck packages. At the lowest level are cc_util and cc_error. The cc_util package depends on no other OverloadCheck packages and is available for use by all. It may not even contain logic specific to OverloadCheck. The cc_error package consolidates all error-handling and assumption-validation logic. If any of the other packages detect a problem or raise an exception, you can rely on cc_error to gracefully deal with the situation.

Figure 3. The hierarchy of OverloadCheck packages I will need to create.

Up one level are cc_types and cc_names. These small, very specific packages should reference nothing but cc_util. One more layer up, and we start encountering pieces of code that are more robust and complex. The cc_arguments package has a very clear purpose: consolidate information from ALL_ARGUMENTS and DBMS_DESCRIBE. To do that, however, it will likely need to dip down to the lower levels. Above cc_arguments, we find cc_smartargs and cc_report. The former is built directly on top of cc_arguments, whereas cc_report uses lower-level packages and cc_smartargs to get its job done.

And at the top of the heap, we have OverloadCheck, the package designed for end users, containing the programs that are actually run by PL/SQL developers who want to check their code.