Augmenter Configuration

Augmenter can be customized by including a config section in the yaml input file. This will allow the user to control how Augmenter generates the required database code.

The config section looks like:

config:
  owner: schema_owner # The database user who should own the Augmenter-generated objects
  debug: false # Set to true to include DEBUG messages in generated code
  naming:
    trigger_prefix: pyr_  # The text used to prefix all triggers generated by Augmenter
    trigger_suffix: _trg  # The text used to suffix all triggers generated by Augmenter
    function_prefix: pyr_ # The text used to prefix all functions generated by Augmenter
    function_suffix: _fn # The text used to suffix all functions generated by Augmenter
  audit: # See the Audit Columns wiki page for a full explanation
  - default:
      columns:
      - modified_date_time:
          not_null: true
          type: timestamp with time zone
          auto: modified_timestamp

By default, trigger names will incorporate the name of the table they maintain. The trigger must also indicate their purpose and if the table name is long this can make the trigger names unwieldy. Therefore, Augmenter will allow an alias to be defined for each table, and will use that in preference to the table name when generating function and trigger names. For example:

schema: example
  table example_customers:
    alias: cst
    audited: true

Would generate a trigger cst_NN_audit instead of example_customers_NN_audit. The NN is a two digit number used to control the trigger firing order.

If debug: true has been set in the configuration, then dbaugment will include RAISE DEBUG messages in the generated plpgsql code.

For example:

CREATE OR REPLACE FUNCTION example_schema.parents_xx_cascade()
  RETURNS trigger AS
$BODY$
BEGIN
  -- Force example_schema.children to copy the denormalized columns via child_parent_fk
  IF TG_OP = 'UPDATE' AND (NEW.grandparent_id != OLD.grandparent_id) THEN
    UPDATE example_schema.children SET
      grandparent_id = NULL
    WHERE parent_id = NEW.parent_id;
  END IF;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql;

would become:

CREATE OR REPLACE FUNCTION example_schema.grandparents_xx_cascade()
  RETURNS trigger AS
$BODY$
BEGIN
  RAISE DEBUG 'TG_NAME=% TG_OP=% TG_WHEN=% TG_RELNAME=%', TG_NAME, TG_OP, TG_WHEN, TG_RELNAME;
  -- Force example_schema.children to copy the denormalized columns via child_parent_fk
  IF TG_OP = 'UPDATE' AND (NEW.grandparent_id != OLD.grandparent_id) THEN
    RAISE DEBUG 'Force example_schema.children to copy the denormalized columns via child_parent_fk';
    UPDATE example_schema.children SET
      grandparent_id = NULL
    WHERE parent_id = NEW.parent_id;
  END IF;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql;

[jmafc] This is my revised pyrseas.cfg file:

extender:
  columns:
    created_by_ip_addr:
      not_null: true
      type: inet
    column created_by_user:
      not_null: true
      type: character varying(63)
    created_date:
      default: ('now'::text)::date
      not_null: true
      type: date
    created_timestamp:
      not_null: true
      type: timestamp with time zone
    modified_by_ip_addr:
      not_null: true
      type: inet
    modified_by_user:
      not_null: true
      type: character varying(63)
    modified_timestamp:
      not_null: true
      type: timestamp with time zone
  functions:
    function aud_dflt:
      language: plpgsql
      security_definer: true
      returns: trigger
      source: "\nBEGIN\n    NEW.{{modified_by_user}} = CURRENT_USER;\n    NEW.__modified_timestamp__ = CURRENT_TIMESTAMP;\n    RETURN NEW;\n\      END "
  audit_columns default:
    columns:
    - modified_by_user
    - modified_timestamp
    triggers:
      "{{table_name}}_20_aud_dflt":
        events:
        - update
        level: row
        procedure: aud_dflt
        timing: before
  audit_columns created_date_only:
    columns:
    - created_date

The first line is so that the config file can be used for other Pyrseas utilities (e.g., dbappgen). Each supported feature will follow as feature_name default. User-defined config files can use the same format with a name other than default. We may also provide other standard features such as last_modified_only. Each feature will be followed by template definitions of YAML to be added to the existing schema. In the above example, the string {{table_name}} will be replaced by the current table name. In the source code for the function, {{modified_timestamp}} is a stand-in for the name of the column.

In starting to implement the POC version, instead of defining the configuration in a separate pyrseas.cfg file, I defined the configuration in audit.py, as follows:

AUD_DFLT_FUNC = """
BEGIN
    NEW.__modified_timestamp__ = CURRENT_TIMESTAMP;
    NEW.__modified_by_user__ = CURRENT_USER;
    RETURN NEW;
END"""

AUD_MOD_TS_FUNC = """
BEGIN
    NEW.__modified_timestamp__ = CURRENT_TIMESTAMP;
    RETURN NEW;
END"""

AUDIT_COLUMNS = {
    'audit_columns default': {
        'table __table__': {
            'columns': [
                {'modified_timestamp': {
                        'name': 'modified_timestamp',
                        'not_null': True,
                        'type': 'timestamp with time zone'},
                 'modified_by_user': {
                        'name': 'modified_by_user',
                        'not_null': True,
                        'type': 'character varying(63)'}}],
            'triggers': {
                '__table__20_aud_dflt': {
                    'events': ['update'],
                    'level': 'row',
                    'procedure': 'aud_dflt',
                    'timing': 'before'}}},
        'function aud_dflt': {
            'language': 'plpgsql',
            'returns': 'trigger',
            'security_definer': True,
            'source': AUD_DFLT_FUNC}},
    'audit_columns modified_ts_only': {
        'table __table__': {
            'columns': [
                {'modified_timestamp': {
                        'name': 'modified_timestamp',
                        'not_null': True,
                        'type': 'timestamp with time zone'}}],
            'triggers': {
                '__table__20_aud_dflt': {
                    'events': ['update'],
                    'level': 'row',
                    'procedure': 'aud_mod_ts',
                    'timing': 'before'}}},
        'function aud_mod_ts': {
            'language': 'plpgsql',
            'returns': 'trigger',
            'security_definer': True,
            'source': AUD_MOD_TS_FUNC}}}

For distribution, it would be simple to use the Python yaml library to output a consolidated file in YAML format. There's quite a bit of redundancy in the configuration spec and I'm wondering what we could to make it less repetitive.