Re: Adding extra custom reports

[Josh Berkus <..hidden..>]

Ed,

> I have done a few projects where we stuck a lot of code on the DB server
> and whilst it's nice in theory, it did lead to a DB performance
> bottleneck, and it is a bit limiting when later on you suddenly wish
> that you could support other databases after all (eg replication is
> stronger in MySql...)

Just for the record, MySQL replication is easier to set up, but less 
powerful than PostgreSQL replication.

The question of where to locate business rules depends heavily on the 
application design ... whether it is code-centric or database-centric.   
Most financial applications are database-centric because generaly if you 
have a financial database you will will have multiple applications 
connecting to it, some of which the designers of the financial database 
have no control over.  We certainly expect this to be the case with 
LedgerSMB, where people will want to connect reporting tools, other 
financial apps and CRMs.

Also, it's a myth that using stored procedures permanently ties you to one 
database any more than using database security of performance 
optimizations do.  You just have to write a version of the SP for each 
database system and make it part of the SQL files to create the database.  
For some combinations this is easy (PostgreSQL, Oracle, Informix) because 
their procedural languages are similar.  For others, it would be hard 
because their procedural language is very different (DB2, MSSQL) or not 
full featured (MySQL).

However, given a well-defined API (e.g. procedure book_transaction takes 
these inputs, produces these outputs and should update these records) 
maintaining these SPs is no harder than it maintaining a set of middleware 
libraries, and without a well-defined API it is no more difficult.

PostgreSQL also gives us the option of writing our SPs in Perl.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco