Proposal for 2.0: SODA 2.0

[Chris Travers <..hidden..>]

This proposal covers our service oriented database architecture and
how I am proposing it changes for 2.0.  Some of these changes require
PostgreSQL 8.4 and reasonably recent DBD::Pg to work, and they
represent substantial changes in interface from 1.3.

1)  Rich Data Type Definitions

Custom types will be used to represent the expected application object
definitions in the database.  The DB will accept input and provide
output of these types.

For example, we may have a table named journal_line

We may have a type defined as:
CREATE TYPE journal_entry (
   id int,
   reference text,
   description text,
   date_posted date,
   approved  bool,
   template bool,
   line_items journal_line[]
);

I understand that most recent versions of DBD::Pg can handle these
type conversions meaning that the data structures can be consistently
represented in the database.  We can then then have something like:

CREATE OR REPLACE FUNCTION journal_entry__get(in_id int)
returns journal_entry AS
$$
SELECT j.id, j.reference, j.description, j.date_posted, j.approved,
j.template, as_array(ROW(l.*))
FROM journal j
 JOIN journal_line l ON (j.id = l.journal_id)
WHERE id = $1
$$ language sql;


The second major suggestion I would like to make is that the stored
procedures which take a full object call that object "self" so that we
can add additional arguments as necessary, address overloading, of
optional arguments, etc.  So the definition statement of
journal_entry__post might be:

CREATE OR REPLACE FUNCTION journal_entry__post(self journal_entry).....

There are two major problems I am trying to solve with this proposal.
The first is that I would like to add a database check to ensure all
transactions are balanced, and I would like applications to be able to
discover the structure that the data is expected in.   Having a
separate API call to save individual lines doesn't allow the db check
to work, and table constraints of this sort aren't deferrable.  While
we could pass in 2-dimensional text arrays with comments like we do in
1.3, this doesn't allow automatic mapping, so any change to the array
structure causes every application accessing it to be rewritten.

One more issue I am grappling with that would like some feedback on.
The double-underscore is easy to mess up and only have a single
underscore.  Does anyone have any better approach here?  One option
might actually be to overload by type of self arg.  However, this
seems like it would get messy when we are trying to address return
values.  One option might be to require a self arg of appropriate type
on every function though that might cause other difficulties.

What do people think?

Best Wishes,
Chris Travers