jmafc/Pyrseas — Issue #17: Feature: Support Declarative Implementation of Business Logic
Author: rhunwicks
Created: 2012-01-12 07:06:17+00:00
State: open

See http://pyrseas.wordpress.com/2012/01/02/business-logic-in-the-database/ for an interesting discussion regarding this...

This functionality is based on the following hypothesis:

* it is better to enforce data integrity as close to the database as possible
* it is better to use native Postgresql functionality where possible, e.g. domains, enums, foreign key and check constraints
* Some common data integrity and business logic requirements cannot be enforced in the database without code, e.g. denormalisations, rollups, audit timestamps and journal/history tables
* These things are frequently not implemented in the database because it is not considered cost-effective
* If these things could be implemented declaratively then it would be cost-effective to implement them in the database, and the data would be better protected as a result, and both the developer and the DBA would sleep better at night.

In implementing this functionality in Pyrseas we have the following general requirements - these are a starting point for discussion, so feel free to add new ones or recommend deletions :-)

* *yamltodb* should only create objects required to support the definitions in the file. I.e. if the .yaml file contains no business logic declarations then no additional objects are created in the database and Pyrseas can be used exactly as it is now
* we should only require schema-level privileges, rather than superuser privileges. This means that all code has to go into the schema defined in the *.yaml* file and we will not create a *pyrseas* schema to hold code.
* if we need session variables we will use the GD global dictionary from pl/pythonu, in preference to using temporary tables or requiring new *custom_variable_classes* in *postgresql.conf*
* Pyrseas should support round-tripping: if we do *yamltodb* followed by *dbtoyaml* we should end up with the same file, if possible. If this is not possible (because it would require identifying unaltered Pyrseas-generated functions and triggers and turning them back into the correct yaml declarations), then at the very least doing *yamltodb*, followed by *dbtoyaml* followed by *yamltodb* should generate exactly the same database
* *dbtoyaml* should capture any changes made to functionality originally generated by Pyrseas such that they are retained by a subsequent *yamltodb*
* much of this functionality will be implemented by row-level triggers and so performance will be a primary consideration
* Pyrseas-generated code will be plpgsql where possible and plpythonu otherwise (see the earlier comment about session variables)
* Pyrseas will generate the minimum code required to implement the functionality, e.g. it will generate a single trigger function called from many triggers rather than a separate trigger function for each trigger, where the code for the trigger function is essentially the same
* Pyrseas-generated triggers will have a naming convention to allow them to be identified and to allow the firing order to be controlled, e.g. *tablename_pyr_20_set_audit_columns*. It might be useful to allow the .yaml file to specify a shorter table alias which gets used in trigger and function names if it exists, e.g. *cst_pyr_20_set_audit_columns* instead of *acme_customers_pyr_20_set_audit_columns*
* code generated by Pyrseas will use the default SECURITY INVOKER privilege where possible. However, it is likely that some functionality will require SECURITY DEFINER privileges. For example, if we implement journaling/history table functionality then the schema owner will have insert privileges on those tables, but the users won't (otherwise they could fake the audit trail). Therefore the insert to the journal table will need to be done by a SECURITY DEFINER function. However, there are some issues with this: see http://lwn.net/Articles/223129/ and http://thread.gmane.org/gmane.comp.db.postgresql.general/85365/focus=77287. I think this means we need to store the current search path at the start of every function and then `set search path = ` and then restore the previous search path at the end, assuming that the problem with the functions and operators being resolved before the function is executed has been solved in the intervening 5 years. If not, then the Pyrseas-generated code needs to schema-qualify every function and operator (e.g. `3 OPERATOR(pg_catalog.+) 4 OPERATOR(pg_catalog.=) 7`

Presuming that we reach consensus on the general design principles I will create additional issues, linked to this one, for individual bits of functionality, hopefully with examples of the database-side code that Pyrseas could generate and potentially with some unit tests that we could use, e.g. pgTAP tests