Re: Proposal for 2.0: SODA 2.0

[Chris Travers <..hidden..>]

On Mon, Mar 8, 2010 at 10:11 AM, Luke <..hidden..> wrote:
> On Mon, 8 Mar 2010, Adam Thompson wrote:
>
> 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.

PL/PGSQL is basically PL1 + SQL......  We don't actually use it
exclusively.  A lot of the stored procs are just SQL queries wrapped
in a call interface.

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

Maybe by a baby step.  Also, this isn't a complete solution to all of
the problems.

It does buy us a couple of important things however, including a very
nice single point of controlling SQL injection problems.

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

I think we can agree that a black box DB is something we want to
avoid.  So let me just quickly mention something I think we need to
also discuss regarding moving towards 2.0.  This is a complementary
concern and needs to be subject to a different proposal.

The 1.3 database schema is sufficiently complex that a readily
understandable ERD is just simply not possible.  This is a problem as
it renders the db into something akin to a black box from the start.
I.e. if the database is so complex and interdependent, that how we
access it isn't too important because nobody can understand it without
a lot of experience.  Furthermore, it makes it more possible to have
stupid errors occur in inserting data, and makes it harder to track
down errors when they occur.  This also makes ad-hoc reporting very
difficult.

We can agree that this is not a good thing.

The other major discussions we need to have involve careful db schema
review, complete with ERD's.  We need to make sure that the database
schema is downsized and simplified where possible, and that each addon
module includes its own ERD.  the whole system might still be complex
if many addons are installed, but each addon could be separately
documented with its own ERD.

I would further argue that we should use table (rather than function)
permissions where possible, and only use function permission where
absolutely necessary.  There are a couple of reasons for this (I know
Josh D disagrees with me on that) but they boil down to better
security and better ability to utilize outside tools.

> If that is not a description of an ORM, then what is?

I think we are talking about two different things, and it is helpful
to see these issues as separate:

1)  What do we need to do to ensure that, for example, all GL
transactions are balanced even if they come in from an add-on?  (SODA
is involved here)

2)  What do we need to do in order to ensure that ad hoc reporting,
and maybe writing records from an addon that have constraints where db
constraints are sufficient is straightforward and doable?  SODA may be
of help in the second one (thought it may be overkill) but it gets in
the way of the first.  Ad hoc reporting is important and SODA is of no
help there.
>
> 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?

It's one solution to one problem.  It does not mean we shouldn't be
trying to make the relational structure of the database as accessible
and understandable as possible as well.  That's a different problem
which also needs a solution.

I don't see this as "either/or."  There are some rare cases where it
may be (inserting GL transactions, user management), but they should
be kept as minimal as possible.  In most areas it should be possible
to provide both sorts of interfaces.
>
> 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.

Not necessarily.  Our app wouldn't directly interact with the back
end, but that doesn't necessarily mean your app shouldn't be able to
without making an end-run around our model.  Nor should it mean that
we shouldn't make it as easy as possible, where we can guarantee the
most basic level of data integrity for your app to do so.

>  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.".
>
Correct me if I am wrong, but I see your point not as "I don't like
this approach to interacting with the db" but rather "I want a usable,
relational DB schema to work with."

I personally think we need to make a usable, relational db schema a
requirement for 2.0.  This proposal isn't something that I want to see
used to move away from it.  It is just designed to provide an
interface for developers to use (and in limited cases where we require
it of developers without any end-runs).

One thing I would suggest is that IF we need to move towards a
logical/physical schema distinction, that we provide views to provide
logical, relational interfaces.

Also, more opinions are helpful.  More issues raised ensures we make a
better project.  I think you are barking up the wrong tree but don't
see it as a waste of time :-)

Best Wishes,
Chris Travers