On 22 Jul 2011, at 21:15, Karl Nack wrote:
> But this still falls short, since we're still basically managing the
> transaction in the application layer.
The problem you're facing here is that database statements work with records, while your example has a need to handle a
setof (different types of) records in one go.
> The holy grail, so to speak, would be:
>
> SELECT create_transaction(1, current_date, 'Transaction 1', ((1, 50),
> (2, -50)));
Now imagine someone not familiar with your design reading this query...
To start with, they're going to assume this query SELECTs data, while it actually inserts it. Secondly, it's impossible
tosee what the different data-values are supposed to represent without looking up the function - and quite possibly,
it'simplementation. They're going to wonder what (1,50) and (2, -50) mean, what kind of date current_date gets assigned
to,etc.
Having to write queries like these is even worse, even if you designed the function. You'll be looking at your own
documentationa lot while writing these.
It would seem to me that the API you would provide for business logic like this should provide the users of said API
withenough context to create valid statements. For example, you could use XML to describe the data (I'm no fan of XML,
butit does suit a need here and allows validation of the provided data), especially as Postgres has XML parsing
functionality.
Or you could use a more sophisticated procedural language (plpython or plphp, for example) that's capable of
marshallingand unmarshalling data structures to strings and vice versa (eg. '{foo:1,bar:2}').
You would still have a SELECT statement that INSERTs data, which is semantically a bad thing to do IMHO.
Perhaps the better solution is (as others mentioned already) to move the data interpretation to a
(web)service/applicationserver and have that perform the actual database operations.
With that in mind, you would put business logic ("process an invoice") into an "application server", while you put data
integritylogic ("don't allow transactions with no line_items") into the database.
Alban Hertroys
--
The scale of a problem often equals the size of an ego.
!DSPAM:737,4e2a9c2112098024710106!
