Verbose deploy/revert/verity

[benzvan]

I would like the -v and --verbose options to work with the deploy, revert, and verify commands by showing the output from the database in response from the query. For example:

> sqitch --verbose deploy
Deploying changes to flipr_test
  + users .. deploying
table FLIPR.USERS created.
  + users .. verifying
 Name                                    Null?    Type
 ----------------------------------------- -------- ----------------------------
 NICKNAME                                NOT NULL VARCHAR2(512 CHAR)
 PASSWORD                                NOT NULL VARCHAR2(512 CHAR)
 TIMESTAMP                               NOT NULL TIMESTAMP(6) WITH TIME ZONE
  + users .. ok

> sqitch -v revert -y
Reverting all changes from flipr_test
  - users .. reverting
table FLIPR.USERS dropped.
  - users .. verifying
describe flipr.users
ERROR:                             
---------------------------------- 
ERROR: object FLIPR.USERS does not exist 
  - users .. ok

[theory]

I could see --verbose being passed on to Engine clients that have their own verbose settings, or to set environment variables that engines respect. Good idea.

[benzvan]

I'm not sure how it would work for the Oracle engine, which is what we use. For sqlplus, SET ECHO ON and SET SERVEROUTPUT ON work great, but they have no effect in sqitch scripts, I assume because sqitch uses the engine directly.

[theory]

Probably would just add those commands to the _script method.

[benzvan]

My untrained research has also turned up the ora_verbose value in the driver which may be useful. Thanks for adding this to the TODO list.

[wkoszek]

@theory Would love to have this feature, especially for debugging stuff like GitLab Runners.

Is there any hacky way to turn on sqitch verbose debugging?

[theory]

You can pass -vvv to turn on triple verbosity. The downside is that I was lazy about calling the verbosity method when developing Sqitch, so there are a lot of places it should get noisier but it doesn't. Really need to go back and instrument it all over the place.

You can enable quite verbose tracing with the DBI, however. Just set the DBI_TRACE environment variable when you run Sqitch. This will allow you to see what queries it sends to the registry and how it processes them. Of course, that won't help for your change scripts. For those, consult the documentation for your database engine's command-line client.

[wkoszek]

@theory Hm. Coming back to this ticket after my AWS RDS + PostgreSQL + sqitch debugging session. This -vvv did nothing to me. I get no additional output from sqitch for this:

$ cat l
#!/bin/sh

#export DBI_TRACE=1

export U=myuser
export RDSHOST="xxxxxxxxxxx"
export PGHOST=xxxxxxxxxxxxxxx
export PGUSER=$U
export PGPASSWORD=`aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-east-2 --username $U`

sqitch -vvv deploy

But this DBI_TRACE is a life-saver, and it let me debug the RDS + IAM authentication. It'd be awesome if we documented it somewhere for us, non-Perl crowd. What I'm thinking: should we document -vvv and maybe make it do:

$ENV{'DBI_TRACE'} = "yes";

? I think it's a huge time saver if I could tell folks to just pass -vvv and debug their setups.

[theory]

Yeah, it might make sense to have -vvv turn on DBI tracing. Does this work for you?

--- a/lib/App/Sqitch/Role/DBIEngine.pm
+++ b/lib/App/Sqitch/Role/DBIEngine.pm
@@ -22,6 +22,11 @@ requires '_char2ts';
 requires '_listagg_format';
 requires '_no_table_error';
 requires '_handle_lookup_index';
+requires 'use_driver';
+
+after use_driver => sub {
+    DBI->trace(1) if $_[0]->sqitch->verbosity > 2;
+};
 
 sub _dt($) {
     require App::Sqitch::DateTime;

[wkoszek]

@theory This looks great. We should add this.