Re: An Introduction to SODA in LedgerSMB

["Chris Travers" <..hidden..>]

Joshua Drake wrote:

> I am sorry, but I spend most of my time "fixing" the very idea that
> somehow object-oreinted development works in the relational model.

> It doesn't.

> We must separate the two and the place to make the cohesive "glue" is
> not within the database with composite type, compound arrays.

Agreed on the first point.  BTW, here is my explanation regarding the
object<->relational mismatches (maybe you find it useful or not,
depending on if you agree with me and depending on who you are talking
to):

Object-oriented frameworks organize data elements into objects based
on flow dependencies (encapsulation prior to operation).  Good RDBMS
design organize data elements based on intrinsic data dependencies.
Because objects lack data dependency tracking, and because database
relations lack flow dependency tracking, they are not mathematically
equivalent.

As to the latter point, the question is where we put the glue and what
it looks like.  I guess the issue is that accounting software poses a
fundamental problem for relational databases using current
technologies and techniques.  The basic problem is that you
essentially have group constraints in accounting rules which cannot be
adequately expressed in current generations of relational database
models (for example the constraint that every accounting transaction
must be balanced).  Although there is no problem defining relations in
this manner based solely on the math involved, no technology exists to
enforce these transaction-level constraints outside of stored
procedures.  I see four options for handling this problem, and all
have fundamental advantages and disadvantages:

1)  Use indexed, nested arrays as arguments for stored procedures.
This poses a couple of problems in that LedgerSMB currently depends on
discoverability of interfaces for mapping of arguments.  Nested arrays
do not contain in themselves enough information to generate an
adequate mapping.  Hence the application must have pre-existing
knowledge of the exact expectations of the interface, or else errors
will be generated (or worse, wrong data will be stored).

2)  Do line-by-line inserts via separate stored procedures (our
current approach).  This is limiting for a number of reasons,
primarily the inability to enforce insert-set level constraints (for
example, the constraint that transactions must be balanced).

3)  Create rich discoverable data types which lead to native
object-oriented interfaces for data operations.  The actual stored
procedures handle insert-set level constraints and then activate other
stored procedures to do the actual inserts.  In essence this is one
form the glue could take.  It has the advantages of both of the first
two options, with a cost of some complexity in the discovery and
argument-generating logic (I.e. the application is responsible for
enforcing these, and checking before committing whether to commit or
roll back).

4)  Create rich discoverable types based on text documents (i.e. XML)
and have the database itself break these down and process them
internally.  Semantically it is equivalent to option 3, and has an
advantage that the integration logic on the code-side would be easier.
 However, it seems to me that the cost in terms of stored procedure
and DTD complexity is sufficient to rule it out, even aside from any
arguments about performance.

My preferred option (number 3) is not practical at the moment anyway
since it is only possible in beta versions of PostgreSQL.  Hence I
think we need to stick with #2 at the moment (even though it likely
means refusing to support add-ons in other languages which touch
financial portions of the software for now).

However, if any other possibilities exist which are viable, we can add
them to the list as well.

Hopefully this makes things a little more clear.

Best Wishes,
Chris Travers