PL/sql application Frameworks for Custom Systems

Download 125.79 Kb.
NamePL/sql application Frameworks for Custom Systems
A typeDocumentation
  1   2   3   4

Database Development

PL/SQL Application Frameworks for Custom Systems

Bill Coulam, Church of Jesus Christ of Latter-Day Saints

If the lessons of history teach us anything it is that nobody learns the lessons that history teaches us. - Unknown

Attention to application architecture is crucial to the aspects of deliverables that users, managers and developers care about: speed of delivery, cost, quality, flexibility, robustness, scalability, performance, and so on.

Unfortunately, eager management and markets usually shout louder than the wisdom of generations who already learned their lessons the hard way. Application architecture (including the application framework), testing, data modeling and documentation are usually the first to be sacrificed on the altar of time and cost savings. This should not happen. Software architecture and the data model are the two biggest slabs of any system's foundation. They cannot be ignored or handled poorly. It's like building a beach house on two stilts propped in a pile of rubble, expecting it to weather the next hurricane.

This myopic approach to software construction seems to be the sad default in too many shops that utilize PL/SQL as well. Many perceive PL/SQL as a "toy" language, a necessary inconvenience, or a language not worthy of full attention like Java and the C derivatives. Best practices like Design by Contract, Test-First, Keep it Simple (KISS) and Don’t Repeat Yourself (DRY) are given the cold shoulder as if they didn't apply. Something "exotic" like a PL/SQL framework is not even considered, let alone removed from development planning.

In harmony with Feuerstein and other PL/SQL evangelists, this paper is an attempt to encourage the Oracle community to care enough about our craft to design and produce top-notch work, quickly and reliably, using frameworks.

If the reader is unfamiliar with application frameworks and libraries in general, or how they fit into software construction best practices, I will provide a brief overview. Further learning can be had from the programming gurus (Knuth, McConnell, Hunt and Thomas, Fowler, Meyer, Beck and others1), mentors and coaches, and hard-won experience.

If I am preaching to the converted, then this paper will also provide pointers on how to build, improve and introduce a PL/SQL framework.

A list of known PL/SQL frameworks and libraries is presented for veterans looking for new tools, or the lucky few currently in the build vs. buy stage.

Finally, a few framework success and horror stories "from the trenches" will be shared and we'll look at applying the author's open-source starter framework to a sample application.

What is an application framework?

An application framework is a collection of software modules or components that implement common functionality used by developers to write software in a rapid, consistent manner.

Examples of commercial and public frameworks are J2EE, .NET, JFC, Struts, Spring, Spry, Flex, Silverlight, Ruby on Rails, and Fusebox. There have been many others in the past, and there will be many more. These frameworks were all born of a need to make development of desktop and web applications easier, quicker and more reliable.

In-house frameworks for a company's custom-built applications are no different. Although portions might have been anticipated and designed up front, the majority of a custom framework is usually created after the problem domain is fully understood, to simplify existing code, handle emerging commonalities, centralize algorithms, refactor, optimize, etc. Sometimes in-house frameworks integrate 3rd party libraries and provide a unifying face to an otherwise disparate collection of specialized utilities.

A framework must be well-built, tested and documented to be useful to its intended audience, ideally bundled with training media or a sample application to demonstrate its use. Good documentation, training and enforcement are essential to beat the learning curve and start realizing the efficiencies and boosts to quality that attend building with frameworks.

Related to frameworks are libraries and components. Frameworks are organized collections of related libraries, and libraries are organized collections of related components. A component (implemented as a function or procedure in PL/SQL) is the finest-grain building block upon which a framework-based application is built. I use the generic term routine when referring to a function or procedure.

Note: What I'm covering here might not be considered a proper framework by object-oriented purists2. The Sofware Engineering Body of Knowledge defines a framework as an OO-related artifact that is "a partially complete software subsystem that can be extended by appropriately instantiating specific plug-ins (also known as hot spots)3." However, I've composed several enterprise PL/SQL applications using little more than three libraries (all part of the same custom framework) that handled the user interface, data and lower-level layers4. Although the PL/SQL framework doesn't "extend and instantiate", it is a full-fledged framework, in my opinion.

Oracle-specific nuances

In this paper, I use the terms module, library and package interchangeably. However, in the object-oriented world, application frameworks are usually built as class libraries, so they often use the two terms as if they were the same thing, rather than distinguishing frameworks as containers for libraries, as I've done here.

In addition, there is an Oracle-specific thing, called a library, that we should clarify before proceeding. Oracle provides the ability to make calls to external C or Java code. When writing PL/SQL that needs to dip into this external code, one needs to write a little interface that tells the PL/SQL engine a little about the external call. This interface is called a library. This kind of library is unrelated to the libraries we are speaking of in relation to application frameworks.

Although Oracle does provide the ability to build with objects and inheritance, the frameworks introduced in this paper are straightforward PL/SQL packages and routines that are designed with structured coding principles. So we won't be talking about subclassing, inversion of control, the Hollywood Principle, or other aspects of object-oriented framework construction…thank goodness.

Is a PL/SQL application framework needed?

Why do we never have time to do it right, but always have time to do it over? - Unknown

The short answer to do you need a framework, is "Yes." A better answer is "It depends."

In an ideal world, you will have the luxury of setting standards, creating templates, tailoring a development methodology, and setting up all the tools and infrastructure (including the application framework) well before developers begin writing code. It can happen. I had just such an opportunity at a startup in Colorado. But for the majority of us, things are not that simple.

For most, lots of legacy code has already been written; things are difficult to maintain, let alone enhance; and something has to be done soon before things implode. It is time to survey the current state of affairs at the company, acknowledging future direction, and then make a plan. Your need for a PL/SQL application framework depends on a number of factors, only a few of which we'll have the space to cover here.

Assessing framework suitability and needs


Are you at the start of a project's or company's lifecycle? Or are you in the middle? Perhaps it is long after most in-house systems are already built?

The best time to put a framework in place is before any code is built, of course. But except for low-level, essential stuff like exception handling, logging, configuration, code lookup, etc., the majority of an enterprise's framework is built after the problem domain is fully explored, frequently well after a few applications have already been built, and candidates for simplification and refactoring are beginning to emerge. So if you are at the beginning of a project or company history, you might put a few basics in place, and then hold off for a while as things develop and the fog clears.

If you are in the middle, it depends on the pain being experienced (mentioned further below). If it is too great, you should begin refactoring efforts soon, materializing a few more components of your in-house framework. If not, you might wait a little longer as things unfold.

If you are nearing or past the completion of a number of systems, hopefully you have been keeping track of the multiplying commonalities, holes and pain points. Now that most of the details are understood, it is time to expand the framework, working the changes into existing code using best coding and testing practices. The further a developer or team digs to follow best practices, reusable application frameworks materialize out of their work products, thereby benefiting themselves and all future applications produced by that shop.

Existing Applications

Examine your existing code base. This could take a long time if you are inheriting legacy systems. You need to have a sense for the structure, relationships and robustness of your systems. You might start by making a catalog of triggers, types, packages and standalone routines. Note any naming schemes and common conventions used. Solving a few production problems is often a good way to gain needed insight. You might attempt an application topology map using a tool like TOAD, ClearSQL, or simply Visio. Determine application structure, flow and dependencies in this manner, or use the data dictionary to map it for you. See if you can discern the layers, either intentional or accidental. Be sure to examine how low-level functionality is handled, especially exception handling, debugging, logging, auditing, locking and messaging. Is there any other functionality implemented redundantly or in a central location? Note them. All of these activities will foster familiarity with your systems. You should now have a better sense of what you have, what you need, and the time and risk involved to refactor anything.

Is there an existing framework in place? Is it used? Is it used consistently? Is it designed and documented well? Does it meet your needs? If you answered Yes to all of the above, lucky you! You can stop right now and enjoy the fruits of wise architecture. Your systems are probably fairly stable and maintenance is a breeze. If you answered No, it is time to build a good framework or improve the one you have and start using it.

PL/SQL Investment

Another reason to examine the code base early on is to determine the quantity of PL/SQL used. If all you've got is a few standalone procedures here and there, or a handful of packages, then it would be hard to justify a framework. In this case, just pay attention to best practices with the little you do write and manage, and refactor any redundant code into a package of common functions. But if your shop has, or will have, a significant investment in PL/SQL, it should be built upon a solid foundation, and hung upon a rich, robust framework, period. No arguments that "there's no time" are acceptable. You can't afford not to build upon, or refactor upon, a common framework.

Technical Strategy

Architectural direction also plays a significant role. If your technical leadership avoid PL/SQL like the plague, and try to treat Oracle as an interchangeable piece of the application stack, then you probably do not need a framework. Some shops dictate all business logic and DML be written in the middle tier language, obviating the need for PL/SQL entirely, let alone a framework that would be bypassed. Some dictate that PL/SQL be used for all DML access, passing in parameters and returning a ref cursor to the result set. In shops such as these, only a partial framework would be necessary. Then there are companies that take full advantage of Oracle and PL/SQL, where a complete framework would be a necessity.

Cost of Maintenance and Enhancements

How are things going with maintenance, enhancements and DB upgrades? Does the thought of touching a legacy system fill the development manager with fear and trepidation? What if there was a bug in production; can you turn on debug logging for a specific package, session or user without recompiling anything? Can you take advantage of that new 10g/11g PL/SQL enhancement by modifying one routine, and a single regression test; or will it take you months of impact analysis and hard work? Can you build a huge PL/SQL package in an hour based on generated code and framework calls, or does it take your developers a few painstaking days to complete? Is that sensitive financial algorithm found in one place, or replicated in a dozen hard-to-protect locations? How many times have your developers written their own version of is_a_number()5? Tired of designing the same user-role-authorization tables and code for each new application at your company? What if you go global; can you accommodate a dynamic localized date format with a one-line change, or will it take you weeks to revisit all the code? These are all points of pain solved by robust frameworks. If your PL/SQL systems are causing you pain, you need a framework. You probably need other things fixed as well, but a good framework will ease the hurt.

The sooner you get over the learning curve and start using a good framework, the faster you will blow the old "Fast. Flexible. Cheap. Pick two." maxim out of the water and reach the holy grail of all three.

What should a PL/SQL framework contain?

You know you've achieved perfection in design, not when you have nothing more to add, but when you have nothing more to take away. - Antoine de Saint-Exupery

Once you embark on designing or finding a suitable framework, it helps to know what it ought to contain. Start with the low-level features that every system needs, no matter what type of application you are building. Then proceed to functionality specific to your application or industry.

Common libraries

Most database-driven systems need certain services before development begins:

IO - DB interaction with the OS and file system to read and write files, and write output to the screen on demand.

Codes - table(s) and API for getting and setting the types, categories, codes and literals used in most data models.

Parameters/Configuration - table(s) and API for maintaining and querying configurable parameters for the development, testing and production environments; parameters like timeouts, defaults, URIs, debugging toggles, etc.

Literals, types and standard cursors - package based static/standard elements, including pre-loaded, in-memory collections of "busy" reference tables; used by both front and back-end code.

Assertions - simple method of verifying assumptions and complying with design-by-contract tenets.

Messages and Email - table(s) and API to handle text messages sent to various targets (table logs, emails, SMS, files). Standardizes, sanitizes and can internationalize the informational, warning and error text fed to end users.

Logging - table(s) and API to log messages of varying severity, with or without context, error stacks, etc. Sometimes combined with the messaging/email library, sometimes kept separate.

Locking - table(s) and API to provide optimistic, pessimistic, logical and/or finer-grained locks than those provided by Oracle for those areas that must be serialized or protected from concurrent modifications.

Auditing - table(s) and API to answer when the business asks "Who did what, and when?" Could wrap Oracle's built-in auditing capabilities, or your own custom solution.

Debugging, Timing and other Instrumentation - table(s) and API to dynamically turn on debugging and timing6.

Error Handling - constants and API to standardize how exceptions are handled.

Unit Testing - tables and API (really a full library or subframework) dedicated to storing, measuring and reporting tests and test suites.

String manipulation - API for wrapping common string operations, like delimited text to collection and vice versa, string cleansing, format validation, encoding, and so on.

Number manipulation - API for wrapping common numeric operations, like number validation, numeric collection comparison, subtypes for anchoring and so on. If your systems are algorithm and math-centric, a math or formula library could spawn from this initial effort.

Date handling - constants and API for formatting, validating and displaying dates.

Directory Integration - API for interacting with LDAP directory servers, particularly for confirming authentication. In more advanced enterprises, application authorization and role-based security is also stored in the central directory.

ETL-related modules - API to wrap use of certain Oracle features and built-ins, like directory creation and management, external tables, pipelining, partitioning, parallel DML and DDL, creation and management of transportable tablespaces.

Code Templates - table(s)/files and API to access common PL/SQL code structures, as well as a common SQL and PL/SQL code repository. Could wrap with a tool plugin or web-interface to allow team-based administration and population.

DBA-centric - API to provide version-agnostic, and error-trapping DDL operations for shops using automated builds of database-specific scripts and code.

These are features most every application requires. Beyond this, you'll need to examine other common Oracle built-ins you will be using (or are already using) to determine if your framework is still lacking. Do you use Oracle AQ, Pipes, Alerts, VPD, Flashback, Materialized Views, etc? Standardize their use and management into a packaged API. Then enforce and monitor their adoption. The first phase of your custom framework is now complete.

Industry-specific libraries

Make it work. Make it right. Make it fast. -Kent Beck

After finalizing your initial framework of low-level components, dive into getting working code for your customer as soon as possible. Further modules for the framework should be added later as the problems and details of the business come to light, and opportunities for re-factoring become apparent.

Domain experience is an essential ingredient in any framework design effort. It is hard to try to follow a front-loaded, top-down design process under the best of circumstances. Without knowing the architectural demands of the domain, such an attempt is premature, if not foolhardy.7

The identification of more narrowly-focused framework additions depends on your powers of observation, and passion for elegant, simple, reusable code. Perhaps your application has special needs in image-handling, web pagination, reporting, SOA-fluency, geospatial mapping, inventory tracking, financial modeling, zip code distance calculation, ad-infinitum. Whatever your system does, you just need to find the hotspots, the things done more than once and/or inconsistently. Factor them out into a standardized, packaged API, and then introduce them into the code, testing vigorously as you go.
  1   2   3   4

Share in:


PL/sql application Frameworks for Custom Systems iconAbstract As web developers we try to design systems that can protect...

PL/sql application Frameworks for Custom Systems iconEmbedding sql server Express into Custom Applications

PL/sql application Frameworks for Custom Systems iconAbstract This document provides step-by-step instructions for building...

PL/sql application Frameworks for Custom Systems iconThe Layer2 Cloud Connector is now able to connect to Microsoft Flow...

PL/sql application Frameworks for Custom Systems iconSql database administrator skilled in variety of areas including...

PL/sql application Frameworks for Custom Systems iconMicrosoft Office SharePoint Server Custom Application Development:...

PL/sql application Frameworks for Custom Systems iconStorage Provisioning for ms sql database Application

PL/sql application Frameworks for Custom Systems iconThis transaction will be used by aafes to report application errors...

PL/sql application Frameworks for Custom Systems iconTechnologies Microsoft Synchronization Framework 1, sql express Localdb...

PL/sql application Frameworks for Custom Systems iconSql server Technical Article Partitioned Table and Index Strategies Using sql server 2008

forms and shapes

When copying material provide a link © 2017