i can't add a function for dbaugment

[lgfausak]

Sorry to open this as an issue, I was hoping to just ask the question on a forum or something but I couldn't find where to post it? Where do user questions and comments go?

I have been experimenting with dbaugment. This is really fantastic. I am able to add my own custom audits using the config.yaml as an example. The only thing I can't make work is exporting the get_session_variable() function. (I am creating my own functions, but this one is a good example). Where does the function definition go in the augment.yaml file? I've attached a work in progress, trying to get any changes to a table NOTIFYied. Perfect for the augment definition. Where to I put my auto.get_session_variable() function so that it comes out in the dbaugment run?

augmenter:
  audit_columns:
    fullnotify:
      columns:
      - modified_table_pk
      triggers:
      - audit_fullnotify
  columns:
    modified_table_pk:
      not_null: true
      type: integer
  function_templates:
    functempl_audit_fullnotify: |2-

      DECLARE
        user_id integer := auto.get_session_variable('audit_user', 0::integer);
        mod_val integer;
      BEGIN
        if TG_OP == 'INSERT':
          mod_val = NEW.{{modified_table_pk}};
        else:
          mod_val = OLD.{{modified_table_pk}};
        endif;
        perform pg_notify(TG_TABLE_NAME || '_change',
          json_build_object('operation',TG_OP,'pk',mod_val,'id',user_id)::text);
        if TG_OP == 'DELETE':
          return OLD;
        endif;
        return NEW;
      END
  functions:
    audit_fullnotify():
      description: |-
        cause a notify tablename_change notification
      language: plpgsql
      returns: trigger
      security_definer: true
      source: '{{functempl_audit_fullnotify}}'
  triggers:
    audit_fullnotify:
      events:
      - insert
      - update
      - delete
      level: row
      name: '{{table_name}}_20_audit_fullnotify'
      procedure: audit_fullnotify()
      timing: before
schema auto:
  table login:
    audit_columns: fullnotify
  table loginrole:
    audit_columns: fullnotify
  table role:
    audit_columns: fullnotify
  table session:
    audit_columns: fullnotify
  table activity:
    audit_columns: fullnotify

When I run this I get the appropriate code for updating my database, everything except the get_session_variable() function. I realize I can just declare this in my database and be done with it, but, it feels like I am missing the injection technique.

Thanks,

-g

[jmafc]

There is a mailing list (http://pgfoundry.org/mailman/listinfo/pyrseas-general ) to which you can post questions such as this. It's mentioned in the first paragraph of http://pyrseas.readthedocs.org/en/latest/devel.html but it probably belongs in a page for general users. I hope @rhunwicks can see this either here or in the M/L, but if not I'll try to take a closer look tomorrow.

[jmafc]

Greg, if by "injection technique" you mean how to actually update the target database, the example at the bottom of http://pyrseas.readthedocs.org/en/latest/dbaugment.html is basically it, i.e.,

dbaugment moviesdb film.yaml | yamltodb moviesdb -u

The -u option will run the SQL statements against the target database. Alternatively, you can use -1 and either pipe the output to psql or save it and run it later.

[lgfausak]

no, that's not it, i've got that figured out, thanks! I have had some functionality in my database, like auditing, and notification, that I really don't like to have there. I got to thinking about the augment stuff and realize that it is a perfect fit. for the grunt work that I dont want to show up in schemas, I do it with augmentation.

I have included my schema which declares the basis for my application. Then, I want to put the applications augments in another file (so my schema drawing don't have it, and other databases (sqlite,mysql) dont get polluted by it). That augment file is attached as well.

I have a declaration for full, which is similar to yours, but i tossed out the ip addresses and i changed the user id to integer. anyway, in the third file i have attached i declare the functions that are needed by the augment. i don't know how to make the augmentation i am doing dependent on the functions i have declared elsewhere.

as an example. first you create a database with: dbtoyaml -u yourdb ab.yaml

then, you augment the database with dbaugment yourdb aug.yaml | yamltodb -u yourdb

but, you still can't use the database until the supporting functions are created, with this line: psql yourdb -f extra.sql

now you can use the database because the low level functions needed by the augmentation now exist.

I know it is something basic i am missing. this is a lot like makefile dependencies, i just want to add some.

-g

On Thu, Sep 25, 2014 at 4:20 PM, Joe Abbate [email protected] wrote:

Greg, if by "injection technique" you mean how to actually update the target database, the example at the bottom of http://pyrseas.readthedocs.org/en/latest/dbaugment.html is basically it, i.e.,

dbaugment moviesdb film.yaml | yamltodb moviesdb -u

The -u option will run the SQL statements against the target database. Alternatively, you can use -1 and either pipe the output to psql or save it and run it later.

— Reply to this email directly or view it on GitHub https://github.com/perseas/Pyrseas/issues/107#issuecomment-56885908.

Greg Fausak [email protected]

[rhunwicks]

I'm travelling at the moment and can't look, but I think you can probably achieve what you want with functions and function templates. I'm including a sampe augment.yaml. If this doesn't work, then I can look at it tomorrow.

augmenter:

audit_columns:

common:

  columns:

  - created_timestamp

  - created_by_user

  - modified_timestamp

  - modified_by_user

  triggers:

  - common_audit

columns:

created_timestamp:

  name: created

modified_timestamp:

  name: modified

created_by_user:

  name: created_by

modified_by_user:

  name: modified_by

function_templates:

audit_common_template: |2-

  BEGIN

    IF TG_OP = 'INSERT' THEN

      NEW.{{created_timestamp}} := CURRENT_TIMESTAMP::timestamp(0);

      NEW.{{created_by_user}} :=

pyrseas.get_session_variable('audit_user', SESSION_USER);

    ELSIF TG_OP = 'UPDATE' THEN

      NEW.{{created_timestamp}} := OLD.created_timestamp;

      NEW.{{created_by_user}} := OLD.created_by;

    END IF;

    NEW.{{modified_timestamp}} := CURRENT_TIMESTAMP::timestamp(0);

    NEW.{{modified_by_user}} :=

pyrseas.get_session_variable('audit_user', SESSION_USER);

    RETURN NEW;

  END;

functions:

common_audit():

  description: |-

    Maintain created and modified user and timestamp audit columns

  language: plpgsql

  returns: trigger

  security_definer: true

  source: '{{audit_common_template}}'

triggers:

common_audit:

  events:

  - insert

  - update

  level: row

  name: '{{table_name}}_20_audit'

  procedure: common_audit()

  timing: before

schema fdw_owner:

table price_market:

 audit_columns: common

table price_marketproduct:

 audit_columns: common

On Fri, Sep 26, 2014 at 3:42 AM, Greg Fausak [email protected] wrote:

no, that's not it, i've got that figured out, thanks! I have had some functionality in my database, like auditing, and notification, that I really don't like to have there. I got to thinking about the augment stuff and realize that it is a perfect fit. for the grunt work that I dont want to show up in schemas, I do it with augmentation.

I have included my schema which declares the basis for my application. Then, I want to put the applications augments in another file (so my schema drawing don't have it, and other databases (sqlite,mysql) dont get polluted by it). That augment file is attached as well.

I have a declaration for full, which is similar to yours, but i tossed out the ip addresses and i changed the user id to integer. anyway, in the third file i have attached i declare the functions that are needed by the augment. i don't know how to make the augmentation i am doing dependent on the functions i have declared elsewhere.

as an example. first you create a database with: dbtoyaml yourdb ab.yaml

then, you augment the database with dbaugment yourdb aug.yaml | yamltodb -u yourdb

but, you still can't use the database until the supporting functions are created, with this line: psql yourdb -f extra.sql

now you can use the database because the low level functions needed by the augmentation now exist.

I know it is something basic i am missing. this is a lot like makefile dependencies, i just want to add some.

-g

On Thu, Sep 25, 2014 at 4:20 PM, Joe Abbate [email protected] wrote:

Greg, if by "injection technique" you mean how to actually update the target database, the example at the bottom of http://pyrseas.readthedocs.org/en/latest/dbaugment.html is basically it, i.e.,

dbaugment moviesdb film.yaml | yamltodb moviesdb -u

The -u option will run the SQL statements against the target database. Alternatively, you can use -1 and either pipe the output to psql or save it and run it later.

— Reply to this email directly or view it on GitHub https://github.com/perseas/Pyrseas/issues/107#issuecomment-56885908.

Greg Fausak [email protected]

— Reply to this email directly or view it on GitHub https://github.com/perseas/Pyrseas/issues/107#issuecomment-56888617.

[jmafc]

Greg, I assume this has been resolved, but perhaps we should improve our user documentation?

[jmafc]

Leaving this open only to update the documentation for dbaugment.

[jmafc]

@lgfausak Please read https://pyrseas.wordpress.com/2018/09/12/the-future-of-pyrseas-revisited/ . If I understand correctly, you were able to fix your problem by yourself. So, in the spirit of open source, you're the best candidate :-) for writing the documentation (plus, @rhunwicks provided additional details/examples).