SODA in LedgerSMB 1.3: A Critical Perspective

Following my Introduction to SODA post, I figured it would be a good idea to discuss the implementation of this on LedgerSMB 1.3, as well as a critical perspective as to how it has not only delivered benefits but also fallen short of our goals, and what will be done to remedy this situation.  Future follow-up posts will flesh out how we will implement this in the database, as well as reference mappers in Perl, as well as possibly other languages (PHP and Python are being considered).

Quick Review

SODA, or Service Oriented Database Architecture, is the approach the LedgerSMB project towards bridging the object oriented vs relational paradigms.  Unlike ORM systems, which design the programming objects first and then map these to a relational model either automatically (by creating tables) or manually (by defining mappings), SODA makes the relational design the primary one and then maps the relations out to interface types, which eventually will become semantically meaningful enough to define object-oriented interfaces to the underlying relational operations.

SODA is based on the idea that the database is the center of the processing environment, not merely the bottom tier of a specific application.  If it is a bottom tier, it is a bottom tier that many applications may use in common, and where consistency and security may need to be enforced across applications.  The database must then not only store, retrieve, and manipulate data, but must offer consistent interfaces for doing this.  These interfaces should be self-documenting to the point that a code generator can pick up on them  and create wrapper classes.  Additionally the database should be designed such that multiple programs can coordinate with eachother though the database.

LedgerSMB is closely tied to the PostgreSQL RDBMS, which we believe provides an ideal feature set for addressing these concerns.  PostgreSQL is one of the most extensible and powerful database management systems anywhere, it is open source, and it performs well under the workloads we care about.

The inspiration for SODA has come in part from the idea of RESTful web services and the emphasis on semantic clarity and re-use of existing frameworks to achieve loosely coupled, interactive systems.

PostgreSQL features currently used

As of LedgerSMB 1.3, stored procedures and to a lesser extent custom types play a central role in our approach.  Simple stored procedures are written in SQL, while more complex ones benefit from the semantic sugar and control structures of PL/PGSQL.   For those who haven't played with PL/PGSQL, it's PostgreSQL's answer to Oracle's PL/SQL, both being essentially PL/1 programming structures with integrated SQL query execution.  The language is clear, concise, and powerful enough for virtually all database access, and it performs quite well under virtually all workloads.

Because LedgerSMB 1.3 is the first branch to use this approach, many concepts are necessarily underdeveloped.  Additionally, LedgerSMB 1.3 was originally developed with PostgreSQL 8.1 in mind-- we started when PostgreSQL 8.3 had just been released.  Now we support 8.2 and higher.  This means that many of the advanced features of composite types, such as arrays of them, are unavailable in the current version, necessitating sometimes ugly and very un-SODA-like workarounds.

Additionally, the LISTEN/NOTIFY framework is used in some cases, sometimes with queue tables, as a transactional form of inter-process communication, but currently since LedgerSMB is usually installed as a single application, there are few opportunities to make use of this within the software distribution itself.


Implementation on LedgerSMB 1.3

SODA in 1.3 is very stored-procedure centric.  Stored procedures are defined.  Their arguments are named for object properties, prefixed with in_ to avoid collisions with columns.  The stored procedure names themselves are supposed to be sufficiently descriptive to allow them to be bundled together into classes.  In particular, the format is supposed to be the class name followed by a double underscore, followed by the method name.  In practice this is very hard to maintain because double and single underscores are not very visually distinct from eachother.   At the same time it isn't clear there is an alternative regarding naming of stored procedures.

On the Perl side, the magic occurs in two modules which contain the only SQL code in new modules for LedgerSMB 1.3:  LedgerSMB and LedgerSMB::DBObject.  LedgerSMB.pm's method "call_procedure" provides a low-level query generation for a named stored procedure along with enumerated arguments, while LedgerSMB::DBObject's "exec_method" provides higher-level mapping for stored procedures arguments to stored procedure mapping.   These provide methods of adding ORDER BY clauses to queries but few other functions at the moment.

These methods return lists of hashrefs, and handling these is the application's job.  One useful function is LedgerSMB->merge() which merges the properties of a hashref into the current object. 

The actual working classes in perl inherit these methods from DBObject and this allows for extremely simple perl wrappers for the stored procedure.  A typical wrapper may contain only a few lines of code, like this:

=item get_asset_class()

Requires id to be set.

Sets all other standard properties if the record is found.

=cut

sub get_asset_class {
    my ($self) = @_;
    my ($ref) = $self->exec_method(funcname => 'asset_class__get');
    $self->merge($ref);
}

The inputs then come from the web interface and get passed to the stored procedures quickly passed in.  In other words, the stored procedures define the application and everything else is written around these stored procedures.  

Benefits Realized

As mentioned in my previous post,  This framework in LedgerSMB 1.3 has allowed us to separate relatively agile development on the front-end from heavy engineering on the back end.  This has allowed us to re-engineer a surprising subset of the application remarkably quickly for customers requiring significant scalability.  In general, it is far easier to troubleshoot and correct both misbehavior and poor performance with hand-tuned SQL and PL/PGSQL stored procedures than it is to troubleshoot the queries which ORM's may generate.


Improvement possible

There are many improvements that can be made to our current implementation.   Although the framework so far has resulted many benefits these are not as great as they could be or what is needed as we get deeper into the re-engineering of the financial portions of the application.  Many of these limitations are due to limitations in older PostgreSQL versions which can be solved by taking advantage of newer features in 8.4 and higher.  Other limitations are problems with approach which are solved by changing our coding conventions.  The development of this approach will likely change over time. 

SODA has on the whole been very helpful but has also fallen well short of what we have hoped it would do.  It is by no means close enough to use to generate code in a variety of languages, and the interfaces are not as self-documenting or as manageable as we would like.

Shortcoming 1:  Complex data handling


The first major shortcoming in LedgerSMB 1.3's design is that PostgreSQL, prior to 8.4, has been unable to handle arrays of complex data types,  This means that complex types are relatively flat, and they lack the ability to express the sorts of data structures that object oriented programmers expect  Starting with 8.4, however, this sort of expressiveness is built into the database back-end.


In 1.3 we employ two strategies for addressing this issue.  The first is to use two dimensional arrays of the lowest denominator type (often TEXT) to simulate arrays of tuples.  This however breaks discoverability because the programmer (and hence the software) has to have intimate knowledge of the internal structure of the array.


The second option is to use multiple cross-indexed arrays for different fields.  This poses other discoverability issues, in particular that the argument names are insufficient by themselves to tell you the relationship between the arrays.  Neither of these are very good approaches, but there aren't better ones that we have found for passing large quantities of data into stored procedures for PostgreSQL versions prior to 8.4.

Starting with PostgreSQL 8.4, however this picture changes dramatically.  A tuple can contain members which are arrays of tuples.  Thus we get a level of expressiveness in PostgreSQL types which provides equivalent capabilities to structural and object oriented programming.  Looking towards LedgerSMB 1.4, we will make clear use of this feature.


Shortcoming 2:  Class management

The second major difficulty at present is class management.   The double-underscore is a poor separator between class and method name because it is not visually striking enough to readily notice when only a single underscore has been added.  Unfortunately there isn't a clear replacement, and schemas have the disadvantage of being a flat namespace.   Moreover schemas are already being considered for organizing the relational aspects of the software and so re-using schemas to solve this problem may cause more harm than good

Long-run I am looking at requiring that SODA functions accept the composite type that defines their first argument and allows for checking of return types which could then be handed off to constructors of other types.  This area my not come into play until we begin work on LedgerSMB 2.0 though.

Shortcoming 3:  Function Overloading and Interface Discovery

One obvious problem that crops up is that function overloading can cause problems here with overloaded functions.  If you have two functions with the same name and number of arguments, but the arguments differ by type, how does the application discover which interface to use?  Worse, if the argument names are the same, the 1.3 mapper will tend to grab the first (usually oldest) function and map the call to it.  Our response in 1.3 was to include test cases that test for function overloading and fail if non-whitelisted functions are overloaded.  This is far from optimal.

Although this problem was first brought up in relation to the fact that we use a weakly typed language (Perl), the language used has surprisingly little to do with the problem.  The fundamental issue is discovery, and adding type handling to Perl, or switching to a strongly typed language in fact does not address the problem at all.


With a discoverable interface, the first thing that must be done is define the discovery criteria.  With LedgerSMB 1.x that discovery criteria is the function name, which means that function overloading is out.  In future versions we may add the first argument type to this discovery, and in that case, allow for limited function overloading as long as the first type is different.


Misc Other Areas of Improvement


As we move towards 1.4 and beyond I expect more focus to be given to consistent interfaces for non-object-oriented environments.  Views, for example, may be added as consistent interfaces, displaying much of the data that would be accessible via a stored procedure.

Additionally,  we are looking at adding more type handling to Perl via Moose in order to solve the problem of internationalization of date and number formats, and other handling of type-specific operations.  This is beginning in 1.4 and will be on-going.



Conclusions


While SODA has delivered many benefits to LedgerSMB, it has not quite yet achieved all we'd like it to achieve.  The goal of fully discoverable interfaces in the database has not been reached yet.   A great deal of effort is yet to be done to reach this goal.