Re: Implementing "thick"/"fat" databases - Mailing list pgsql-general

On Sun, Jul 24, 2011 at 12:34 AM, Karsten Hilbert
<Karsten.Hilbert@gmx.net> wrote:
> Hello Chris,
>
>> > In LedgerSMB, we take this a step further by making the procedures
>> > into discoverable interfaces,
>
> how do you do that ?

The way we do things in 1.3 beta is relatively free-form and not idea.
 In future versions I think we intend to tighten this up.    I will
give you examples of where we are for now as well as where we are
headed.

In 1.3, the idea is that as much of the function declaration should be
semantically meaningful as possible.  However we didn't spec out
everything.  (Problems and solutions below)

So you might have a function like:

CREATE OR REPLACE FUNCTION
batch_search(in_class_id int, in_description text, in_created_by_eid int,
        in_date_from date, in_date_to date,
        in_amount_gt numeric,
        in_amount_lt numeric, in_approved bool)


An application can query the argument names as long as the application
knows this is a routine for searching batches.  It can then map in the
class_id, description, created_by_eid, date_from, date_to, amount_gt,
amount_lt, and approved fields from a specified source to the
arguments and generate an SQL query to hit this argument.

There are some problems with this approach as we follow it right now.
The biggest one is consistency.  If the variables are not always named
consistently to object properties it can cause maintenance problems.
So this means object definitions and sql statements form complementary
portions of code and are in different languages.

A second problem is function overloading with loosely typed languages.
 Our solution is to test for and not allow function overloading.

The final problem is that this does not separate object properties
from function arguments.

A better approach would be to name object properties and arguments
with different prefixes.  So:

CREATE OR REPLACE FUNCTION  -- we aren't doing anything with a batch,
just looking for them
batch_search(arg_class_id int, arg_description text, arg_created_by_eid int,
        arg_date_from date, arg_date_to date,
        arg_amount_gt numeric,
        arg_amount_lt numeric, arg_approved bool)
RETURNS SETOF batch_search_result

but


CREATE OR REPLACE FUNCTION batch_post(obj_id INTEGER) RETURNS batch

If we enforce that the function return type must return a type whose
properties can be specified as input properties, the following becomes
possible:

1)  Automatic argument mapping to object properties in a fairly robust way and
2)  Automatic merging of return results back into the object that called it.

It would also be possible to write code generators to create object
wrappers for the SQL types and functions which could then be extended
as needed.  It also means that object properties and methods as
general data structures have at least part of their definition in SQL
code.  Applications of course may have to extend this in various ways
in order to avoid ravioli code.  But it means you have an internally
consistent basis to build things on.

Best Wishes,
Chris Travers