Re: Proposal for 2.0: SODA 2.0

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

I have to disagree with many (most?) of your basic points.
Exposing tables does not prevent refactoring - but it does require better documentation and a good change control process.
For extensive changes, views and SPs can be used to preserve backward compatibility.
The DB provides declarative tools to enforce business rules and security - sometimes these are harder to use, but if you can't express a requirement declaratively, you should be redefining the requirement anyway, because you then haven't actually defined your requirement, you've only give one example of how to satisfy an *unstated* requirement.
"Defining" your requirements in an SP merely moves procedural code from the client to the database server, and eliminates most of the benefit of an RDBMS.  At that point, you are functionally at the same place as using a middleware layer with (e.g.) an object store or flat files.

*sigh* I have more to say, but my thumbs are getting sore (typing on a blackberry).
-Adam
-----Original Message-----
From: Aurynn Shaw <..hidden..>
Date: Mon, 08 Mar 2010 11:12:48 
To: Development discussion for LedgerSMB<..hidden..>
Subject: Re: [Ledger-smb-devel] Proposal for 2.0: SODA 2.0

On 10-03-08 10:11 AM, Luke wrote:
> On Mon, 8 Mar 2010, Adam Thompson wrote:
>
>> 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.
>
> Since I find myself agreeing with this to a certain extent, I will chime
> in on something I would have otherwise remained out of.
>
> First of all, I will say that I am not clear on the programming for what
> you are doing--have never worked with PSQL stored procedures, and don't
> know how certain of the other language elements fit into this.
>
> That said, it looks like what you just explained is an order of DB
> obscurity beyond what has been done to date.  What I mean by that, is that
> the interface is getting further and further away from raw table access,
> and in this case specifically is getting quite far away from it.
>
> I understand your reasoning for moving as much as possible into the
> database.  I agreed with it in the beginning as a reaction to SL's
> codebase, even though I have never been very comfortable with it, but I am
> starting to wonder.
>
> We have turned the database from a device for storing and organizing the
> storage and retrieval of information, into a device for processing that
> information in ways which are less and less apparent.  In other words, it
> has been made into a blackbox.
> If that is not a description of an ORM, then what is?
>
> For a commercial app with a true API, I probably wouldn't have a problem
> with this idea.  However for an app in which you intend people to be
> writing their own frontends, extensions, interfaces, etc., and in which
> you intend those extensions and such to interact with the database, is this
> wise?
>
> I could see it, maybe, if all database interactivity was handled through
> an API.  In that case you're not talking about database access any more,
> but instead programatic access through an interface which requires no
> direct knowledge of the storage backend, and in fact demands that one
> never interacts directly with that backend.  But that's not what's
> happening here.  The way I understand it, these procedures are obscuring
> the database, from within the database.  So you end up with something of a
> hybrid condition.
>
> That just seems...undesirable.
>
> Now, given my disclaimers above, I recognize that I could be barking up
> the wrong tree, and if so I apologize for wasting your time with this.
> However Adam's comments reinforced what I had started thinking while
> reading your original message--that being that "this strikes me as a
> level of complexity and obscurity too far.".
>


Well, going over everything you've said, what I think is happening with 
putting the DB behind the stored procedure API is a worthwhile objective.
Instead of allowing an ORM to directly manipulate the tables, the stored 
procedure interface is able to enforce permissions, implement semantic 
integrity checks, and other necessities that ORMs don't allow.

The database tables should be likened to private methods and 
datastructures of an object - they're hidden, and exposed via an API 
interface, which allows for deep refactoring of underlying concepts 
without breaking other applications. Handling everything in stored 
procedures as opposed to allowing an ORM to manipulate internal 
structures achieves a similar abstraction and ability to refactor as 
necessary.

Allowing direct access to the tables, even for SELECT statements, would 
force table design to remain relatively static, even if significant 
redesign is necessary.

However, a good place to implement reporting queries would be with a 
Report permission level, and several views that provide the required 
information. This would allow for a static API that can be queried, and 
still permit underlying table modification as needed - A 
physical/logical schematic separation.

Thoughts?

Regards,
Aurynn



-- 
Aurynn Shaw

The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
We are all in the gutter, but some of us are looking at the stars.
   -- Oscar Wilde

..hidden..

------------------------------------------------------------------------------
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