Better auditing

[chb0github]

I have been evaluating several db migration tools and mybatis has been the most promising (+1)

However, one thing that I would love to see is better auditing in the change log.

Specifically:

1. db user executing the script
2. Local user connected
3. Client connection string
4. Source version tag (which could subsitute as the DB version... maybe?)

This is kinda the schema I had in mind:

CREATE TABLE IF NOT EXISTS CHANGELOG
(
  ordinal INT NOT NULL AUTO_INCREMENT,
  version varchar(50) null,
  description varchar(200) not null COMMENT 'Use _ as delimiter and use file naming convention'l,
  script varchar(1000) not null COMMENT 'Fully qualified path of the script executed',
  hash VARCHAR(64) COMMENT 'sha256 of script',
  tag VARCHAR(64) COMMENT 'current git tag. Maybe use this as the version?',
  client_id VARCHAR(50) COMMENT '$SSH_CLIENT This field should be enough to accomodate ipv6',
  installed_with varchar(100) not null DEFAULT CURRENT_USER,
  installed_by varchar(100) not null COMMENT 'this is the user on the actual localhost $USER',
  installed_on timestamp default CURRENT_TIMESTAMP not null,
  execution_time int not null
);

Based on what I am seeing from this project I can probably achieve this with hooks. It would be great to have it baked in

[harawata]

Auditing was one of the use cases I had in my mind when I designed the hook feature.

You should create a separate table for auditing. As it's a one-time operation, doing it in bootstrap would make sense.

Then insert those information you need using hooks. In your list, tag and client_id might be tricky to get, but other items should be straight-forward. Please read advanced usage section if you haven't.

[chb0github]

I can get onboard with that thinking.

Can I use hooks to inject environment variables? like ${env.SSH_CLIENT} environment variable are available inside the JVM.

[harawata]

Sure. In a JavaScript hook script, you can get environment variables as follows.

java.lang.System.getenv("SSH_CLIENT");

It's still Java :)

[chb0github]

Well, since I would be adding to a DB then I really need to have a templated insert statement for the hook.