Spanner

In Aspen 2.5, we were faced with the challenge of increasing performance by using stored procedures while needing to maintain compatibility with both SQL Server and Oracle. The process of developing stored procedures for both SQL Server and Oracle would have been not only time-consuming, but would have resulted in twice as many chances for error. The team referred to this as the “dual database tax”.

A fellow architect and I brainstormed the possibility of writing our own stored procedure language and a translator that would convert our language into highly-performant SQL Server and Oracle stored procedure notations. SQL Server and Oracle stored procedures are substantially different, but if we could achieve this goal, we would greatly reduce development effort and decrease the occurrences of platform-specific bugs.

In Aspen 2.5, we developed the Aspen Meta-Language (AML) and a compiler named Spanner.

AML and Spanner provided the following:

The ability for the company to simultaneously ship SQL Server and Oracle releases.
A platform agnostic language for writing stored procedures (with approximately 70 commands).
High-level functions to solve common problems such as “paginated” views of data.
A macro capability for improving code reuse. (Neither T-SQL or PL/SQL provide a macro capability. This dramatically improved developer productivity.)
A dramatic decrease in the use of “dynamic SQL”. Because AML provided for macros, dynamic SQL could be replaced with dynamic AML that resulted in concrete SQL in stored procedures. This substantially decreased the chance of runtime errors and improved performance.
The ability to easily integrate customizations into the core product (since all future customizations would be available to both SQL Server and Oracle).

Within the first year of use, developers had written more than 250,000 lines of AML code that resulted in more than 1 million lines of SQL Server and Oracle specific output.

Employer: SumTotal Systems
Roles: Lead Software Architect, Development Lead
Style: Desktop app, Database-driven
Focus Areas: Language Processing, Code Generation
Technology: C#/.NET 1.0/1.1, XSLT (Extensible Stylesheet Language), SQL Server 2000, Oracle 9i