Re: Proposal for 2.0: SODA 2.0

["Adam Thompson" <..hidden..>]

I'm wondering why the push to move so much functionality into the DB instead of using an ORM, which seems to be where you're headed anyway?
Or, to rephrase that: if you don't want an ORM in the Perl framework, why are you trying to build one in the DB?
I'm perfectly OK dealing with relational data.  In fact, one of the things that's attractive about LSMB is the RDBMS back-end.  With security implemented on a table and column level, I can use whatever report generator I want to build custom reports.  Making me access everything through stored procedures eliminates any value LSMB has over other accounting systems... at least for me.
If I have to connect as a fully-privileged user, I guess I can, but doing an end-run around the app is something I associate with Inuit products, not LSMB.
-Adam

-----Original Message-----
From: Chris Travers <..hidden..>
Date: Mon, 8 Mar 2010 08:11:51 
To: Development discussion for LedgerSMB<..hidden..>
Subject: [Ledger-smb-devel] Proposal for 2.0: SODA 2.0

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

------------------------------------------------------------------------------
Download Intel&#174; Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel