• Files
• Commits
• Log
• Graph
• Stats

# extracts user responsible for entering/updating result summary from
# request_lab_test_history table for insertion into request_result_summaries

# switch on query output using -q switch (verbose!!)
# run results_summary_user.sql to move table from test db

#===============================================================================
my $src = 'bristol'; # source of request_lab_test_history data
my $db  = 'test'; # move to bristol db using results_summary_user.sql
#===============================================================================

BEGIN {
	use Getopt::Std;
	getopts('q');
	our($opt_q);
	$ENV{SQL_TRACE} = $opt_q;
}

use lib '/home/raj/perl-lib';

use Data::Printer alias => 'p', use_prototypes => 0;
use SQL::Abstract::More;
use Modern::Perl;
use Local::DB;

my $sqla = SQL::Abstract::More->new;
my $dbix = Local::DB->dbix({ dbname => $src });

#==============================================================================
# to delete just user_id col on test.request_result_summaries table:
    # $dbix->update('test.request_result_summaries', { user_id => undef, time => \q!`time`! });
# or to recreate test.request_result_summaries table:
    recreate_test_rrs_table('test.request_result_summaries'); # exit;
#==============================================================================

{
	my ($sql, @bind) = $sqla->select(
		-columns  => [ qw(request_id user_id action) ],
		-from     => 'request_lab_test_history',
		-where    => { action => { rlike => 'result summary' } },
		-order_by => 'time',
	);

	my $query = $dbix->query($sql, @bind); # p $sql; p @bind;

	my $map = $dbix->select('lab_sections', [ qw(section_name id) ])->map; # p $map;

	REQ: while ( my $ref = $query->hash ) { # next unless $ref->{request_id} == 296930;
		my $request_id = $ref->{request_id}; # p $request_id;
		my $action     = $ref->{action}; # p $action;
        say "$request_id:$action" and next REQ if $action =~ /^deleted/;

		my ($section) = $action =~ /(?:new|updated) (.*) result summary/; # p $section;
		my $section_id = $map->{$section} or die "no section for $section"; # p $section_id;
		my $res = $dbix->update(
			"$db.request_result_summaries",
			{ time => \q!`time`!, user_id => $ref->{user_id} },
			{ request_id => $request_id, lab_section_id => $section_id },
		); # absence probably means section result summary deleted:
		say "failed to update $request_id:$section:$action" unless $res->rows;
	}
}

sub recreate_test_rrs_table {
	my $test_tbl = shift; say "dropping $test_tbl";
    $dbix->dbh->do('DROP TABLE IF EXISTS '.$test_tbl );
		say "creating new $test_tbl";
    $dbix->dbh->do( qq!CREATE TABLE $test_tbl (
	  `request_id` int(11) NOT NULL DEFAULT '0',
	  `lab_section_id` smallint(6) NOT NULL DEFAULT '0',
	  `results_summary` text,
	  `user_id` smallint(6) DEFAULT NULL,
	  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	  PRIMARY KEY (`request_id`,`lab_section_id`)
	) ENGINE=InnoDB! );

	my @cols = join ',', qw(request_id lab_section_id results_summary time);
	my $sql = qq!INSERT INTO $test_tbl (@cols)
		SELECT @cols FROM request_result_summaries!; # say $sql;
		say "copying data into $test_tbl";
    $dbix->dbh->do($sql);
		say 'done';
}