Re: Adding extra custom reports

[John Locke <..hidden..>]

Hi,

Hope I'm not intruding too much here, but I've been keeping an eye on
the project, and since I'm nearing the end of a big custom
Postgresql/PHP app, have some thoughts here...

Chris Travers wrote:
> On 11/6/06, David Ratte <..hidden..> wrote:
>   
>> Currently there is 'logic' in both the front-end code, and the back-end code.
>> I'd like to see both go away, and move all the logic into pgpsql embedded in
>> the database, getting rid of the 'backend' completely.
>>     
>
> We are moving in that general direction.  However, there are
> legitimate questions about how far to take this.  Certainly we intend
> to get all the logic out of the UI code and move to a templating
> system.  Certainly we intend to put most or all all data logic in the
> database.  But there is a legitimate disagreement about how far we
> take this beyond this point.    In particular, there are limits to how
> much logic can be placed in a PostgreSQL stored procedure due to
> limitations on returned datatypes (and the fact that one cannot add to
> these on the fly).
>
>   
I took over a big custom application from another developer who had used
stored plpgsql functions for all interactions with the underlying
tables, and it was a major pain.

I agree that having stored functions to manage transactions is a good
thing. But putting all business logic into the database really hampered
overall development. Much of this is probably due to my lack of
experience with Postgres, but these are the things that really made
things difficult:

* limitations on returned datatypes, as noted above

* having to create different stored functions for every possible
variation of a search - it was much simpler to build a query in PHP to
get the data returned directly, based on which search criteria were
entered, as opposed to creating a stored function for every variation. I
found I had to write long switch/case structures or if/then code just to
choose the right function! It was much quicker to simply build up a SQL
query, adding WHERE clauses for each search item. And, it eliminated a
dozen stored functions, and due to a (very awkward) join, eliminated
having to loop through results of one query to do another stored
function call for each result!

* difficulty managing changes - if the code is stored in a PHP/Perl
file, it's in a working copy and easy to track changes. It's a few extra
steps to keep the stored function in Subversion, and when working with
other developers, painstaking to identify which version of a stored
function is in the DB. (If there's a better way to manage this, I'd love
to know!)

> My own preference is to have a three tier structure with strict
> separation in logic between the tiers.
>
>   
Completely agree.

Cheers,

-- 
John Locke
"Open Source Solutions for Small Business Problems"
published by Charles River Media, June 2004
http://www.freelock.com