Decoupling an Application from the Physical Database Schema

Contents How A SQL Generator Works The Short Answer The Long Answer Code Example for Techies Inherent, Transparent Data Integrity Run The Above Code Now What About Performance Faster to Code Faster At Runtime Completion Changing Database Schemas Conclusion Learn More Evaluate Contact, Discuss Abstract: It is all too common for many layers of even well-architected applications to be hard-wired to a database schema; usually through SQL. Much of this SQL and other database-related application software has to be continually maintained and updated as the business application is adapted and enhanced over time; and of course, large volumes of SQL often have to be written in the first place. In addition, other dependencies percolate up through the system as the database schema changes. Consider data edit rules generally located near the edge of the network, or perhaps edit logic that executes in-browser. These edit rules almost certainly reflect characteristics of data columns (numeric, alpha, length, scale, precision etc.) and also relations in the database. The best way to avoid application dependencies with the database is to not manually write SQL (and edit rules) in the first place. Applications architected this way are far, far more flexible, accommodate change and incur much lower labor costs. Lower costs at initial development, because less SQL facilitates initial development and expedites database schema modifications. Lower maintenance costs also accrue over the longer term, for without massive volumes of SQL or other query strings to maintain, application development is far more agile and cost efficient. Q: How is it possible to nominalize if not eliminate the primary source of software development and maintenance costs associated with database access and update? A: Don't write SQL in the first place. Use a metadata-driven SQL generator. Q.E.D. The business application is no longer tightly coupled to the database. How a SQL Generator Works There is now a way to decouple your application from the database without writing any code. The Short Answer A deep scan of the database schema is performed with a graphical utility from TriCoron written in Java (so it runs on just about everything). During this schema scan, metadata is acquired to drive the SQL generator (and also drive edit rules, as it happens). For best possible speed, the extracted metadata is then compiled into Java classes. At runtime, the metadata is accessed and passed to a SQL generator. The generated SQL is then executed and the results passed to the requesting application. The SQL generator also generates DML to update, delete and insert records and relations in the database. All this takes place over SOAP as well, so the SQL generator at the backend of your system fits nicely with your SOA.

The Long Answer

TriCoron's schema scanner automatically acquires a large amount of metadata for all DB tables, columns and cross-table relations. The metadata to drive the SQL generator is encapsulated into a Java class, for optimal performance. A Java class is created for every possible combination of table and table-to-table relationship in the database. (Machines are so fast these days, it is easier to generate all possible persistent objects, and throw away or just not deploy the generated objects you do not use.) You can, of course, through the TriCoron metadata extractor GUI, deselect objects you know with certainty are not going be needed (to reduce on build time¹).

Consider the 1:n relationship between the familiar CUSTOMER and SALES_ORDER tables in the venerable Oracle DEMO database. A class called CustomerSalesOrderEJServer.java is generated that encapsulates, as manifest constants, the metadata for these tables, along with foreign key maps and other metadata.

CustomerSalesOrderEJServer extends a generic database access and update superclass that contains the logic to build and execute JDBC PreparedStatements on the fly. A corresponding POJO is also automatically generated. The corresponding POJO, in this example, is named CustomerSalesOrderServiceDAO.java that is, in essence, a corresponding facade or Java Bean-style state class. Instances of CustomerSalesOrderServiceDAO are created and managed dynamically for your application through a sundry factory design pattern.

If your app is running in Java, you can call this facade class directly, avoiding Web service overhead. You can issue simple yet powerful create(), store(), load() and remove() method calls against the instance of CustomerSalesOrderServiceDAO created by the aforementioned object factory. All possible accessor and mutator methods are also generated for persistent object instance fields (such as getName() and setName() corresponding to the CUSTOMER.NAME column).

A single, stripped-down JavaDoc file for the CustomerSalesOrderServiceDAO.java POJO is here for the technically curious.

Code Example For Techies

string[] newvalues = new string[attributes.Length];

string errormessage = proserviceproxy.inputEdit(objectname, newvalues);
if (errormessage != null)
{       // display the message received over soap to the user
	MessageBox.Show(errormessage,
		"Input Error",
		System.Windows.Forms.MessageBoxButtons.OK,
 		System.Windows.Forms.MessageBoxIcon.Error);
	// bail out of event handler
	return;    
}

proserviceproxy.store(objectname, currentvalues, newvalues);

proserviceproxy.setCommit();

Inherent, Transparent Data Integrity

Run The Above Code Now

What About Performance?

Performance with a metadata-driven SQL generator may indeed be superior to other, automated alternatives, or what you have in production today.

Faster to Code

With high-end DB server hardware and software now relatively inexpensive, the issue today is: speed of development. People time. DB access code, exception logic, transaction handling, SQL maintenance, SOAP XML message generation and handling, all of this can easily add up to one-third of a project's development budget. So don't spend the money.

A side benefit of not having SQL, and hiding JDBC from the application, is being able to show system stakeholders and clients how quickly incremental requirements can be implemented.

Faster At Runtime

DB Schema Aligns With Application

When most business applications are developed, the database schema is developed along with application software as requirements are realized and documented. Inevitably, it is determined that columns are in the wrong tables, there are redundant columns, or data has to be moved out and normalized to other tables. Once the project reaches a certain point, database changes become painful. Why? Because there is an existing software codebase that contains SQL (or other query languages²) that couples the application software to the database design. This tight coupling can prevent even the best DBAs from optimizing the design of the database.

In most environments, if the DBA or DB designer changes the schema significantly to reflect the most optimal design for the application, the existing codebase will still compile, but it won't run. A seemingly endless, torrential flood of SQL exceptions are likely after major schema changes, especially when a major renormalization effort takes place. The project has to essentially stop as the existing software is updated to align with the updated database.

At a certain point in a nearly every project, this normalization and realignment process, of both the database and the code, stops happening. This is because it would simply take too much time to get the existing code running on the reorganized, renormalized database. It is easier to simply add more tables and write new data access objects (and even more SQL) than to change what already exists.

For these reasons, nearly every database and its corresponding application software are out of alignment. This produces significant runtime inefficiencies: long response and customer wait times under load, unplanned hardware expenditures, slow maintenance request turnaround; and other technical and financial issues often occur.

Consider a superior alternative:

The result? Because the persistent database objects are now typed, the compiler shows misalignments between the application software and the new schema³. The existing code can easily be brought into alignment with the new schema using an IDE, rather than chasing down SQL exceptions and countless query/update strings.

Thus, the application system runs faster in production because the software more closely aligns with the optimal database schema design for best performance. Best use is made of existing hardware. Peak loads are handled more adroitly; your system has more capacity and throughput.

Pooled Prepared Statements

Writing, testing and maintaining JDBC prepared statements is a PITA. In certain databases, prepared statements are even compiled into stored procedures and cached in memory inside the database server. Most databases will even cache query plans. JDBC drivers contain further optimizations if you use JDBC prepared statements.

Tip: Don't write prepared statements manually. For a large database (where you need prepared statements the most) the labor costs to write, test and maintain hundreds, perhaps thousands, of prepared statements, is neither economical nor practical. Better to use a metadata-driven SQL generator that generates, prepares, pools and normalizes results from Prepared Statements automatically, on the fly. Transparently.

Built-in Realtime Performance Metrics

So how good are the generated metadata driven prepared statements and generated SQL? REALLY good in fact: it really depends on your particular database. Sometimes, the application makes metadata driven queries that hit columns that do not have indexes (it happens). The result is a serial sweep of an entire database table, also known as the notorious Full Table Scan.

So, in many cases, generated SQL performs better than hand-written SQL.

TriCoron provides built-in performance metrics that can be enabled and disabled in real-time via a checkboxes through a servlet (included, free). You can then see how fast the SQL generator is working for you, in your environment, with your exact hardware. You can also trace the generated SQL prepared statements for subsequent optimization if required. Tables that require indexes or have contention issues show up rapidly because TriCoron's performance and diagnostics servlet automatically enables and disables tkprof in real time. This realtime database performance metrics acquisition capability is very useful in practice.

The point is, it is easy to see how your database is performing in development, QA or production deployments. A couple of mouse clicks through a browser are all it takes.

Completion

What is really slick is that all of the SOAP and Java objects and methods to access and update your databases show up in your favorite IDE.

All of the generated persistent objects are typed. Thus your IDE provides interactive selection of your database objects from scrolling lists. When the object you want is selected, the methods and parameters are then displayed. Very slick, we use this all of the time; it is a huge timesaver in practice. Of course, the complete JavaDoc is also produced by the ANT build scripts.

As a Web service, these persistent objects are a bit more loosely coupled (as one would expect over SOAP) and thus are untyped. (SOAP methods provide additional metadata, see image below.) This is what you see in Visual Studio .NET 2003 (all database schemas, SQL Server or Oracle, doesn't matter, it's the same, consistent Web service interface):

Note that the service endpoint IP port number of 7001; this is a BEA WebLogic server. This SOAP interface is the same when WebSphere, JBoss or other J2EE app servers are targeted. And whether a SQL Server or Oracle database is targeted, the SOAP interface is identical. Also, this is the same dynamic Web service interface for any database schema.

Obviously none of this is true with raw SQL and JDBC or competitive object query based systems.

Changing Database Schemas

See performance note above.

Conclusion

Loose coupling of your applications components is the key to application flexibility, low development and maintenance costs, and application agility. Loosely couple your application logic to the database and eliminate query strings², also over SOAP if you prefer, and you have made a huge, notable, step forward, to more agile, cost-efficient software engineering. SOA is the way.

Learn More

Evaluate

Contact, Discuss

If you have any questions, would like to learn more, or would just like to give some feedback, please email support at tricoron.com, submit a question or call TriCoron Service at 1-866-SOA-TODAY and we will discuss your immediate term business and technical needs. Initial consultations are, of course, free with our compliments.

[1] Recently, on a medium sized Oracle database with > 150 tables, over 700 persistent objects were produced. Compile time on an inexpensive PC was less than 25 minutes, and this includes a generated Struts JSP application as well as the SOAP Web services access and update layer. ANT 1.5.4 was used with Sun Java 5. This PC used in this build has a 3G CPU, 1G of RAM, a 7200 RPM SATA drive and runs Windows XPSP2.
[2] OQL, EJBQL, HQL etc. all of these query strings are similar in the sense as they each ultimately are all database schema dependent, some to a less degree than another. The only real solution is to eliminate query strings of any type from your application to decouple your application software from the database, using SOAP or Java objects.
[3] This is even true, though to a lesser degree, with SOAP clients when the database schema is changed.

Copyright © TriCoron 2004 All Rights Reserved Worldwide