> I was thinking similar thoughts, but you not only beat me to it, you made
> some good points I had not thought of!
>
> The only thing I can think of adding: is that it would be good to lock down
> the database so that only the middleware can access it, everything else
> accesses the database via the middleware.
In general, I am not convinced that middleware is inherently more
maintainable than in-db procedures.
But the fundamental question is: Is this a a one-application
database? If it is, you can use the middleware to be that application
lock the db down so only the middleware can use it etc.
But what if it isn't? What if we want to support a variety of
applications against the same relational database? This has to be
fairly commonplace.....
In this way my experience is that it is often helpful to maintain
several levels of stable, public API's both on a table level if
possible (as attachment points for triggers), stored proc API's for
actually inserting data into relevant areas while enforcing
appropriate business logic, and so forth.
One of the things we are doing in LedgerSMB is to make the stored
procedures discoverable, so the argument names (and eventually the
return types) will have meaning the application can use in building
calls for the procedure. This eases one important maintenance point
because arguments are automatically picked up by the application and
as long as best practices in coding are followed, will be handled
sanely. (The interface will be extended in the future so that return
types determine the class, and the arguments in determine whether we
are talking about a presumed object property or a presumed
application-specified argument.) Theoretically, we should be able to
build objects in languages picking up methods and properties from the
Pg system catalogs but we haven't gotten that far yet with code
generation.
Best Wishes,
Chris Travers