CohortExplorer/Command/Query.pm

filter: Statementstmt 13.8% 46 / 332 Branchbran 2.8% 4 / 140 Conditioncond 0.0% 0 / 62 MC/DCmcdc 0.0% 0 / 35 Subroutinesub 46.1% 12 / 26 Podpod 100% 14 / 14 totaltotal 12.4% 76 / 609 CC 107 SCAR 57.0
CC
107
Cov
9%
validate
552.0
process
506.0
summary_stats
420.0
CRAP 299.7
« CohortExplorer/Command/Query/Search.pm ↑ Summary CohortExplorer/Command/Find.pm »
1 package CohortExplorer::Command::Query;
2  
3 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5  
6 1 our $VERSION = 0.14;
7 1 our ( $COMMAND_HISTORY_FILE, $COMMAND_HISTORY_CONFIG, $COMMAND_HISTORY );
8 1 our @EXPORT_OK = qw($COMMAND_HISTORY);
9  
10 1 my $arg_max = 500;
11  
12 #-------
13 1 ▶BEGIN {
Subroutine BEGIN: called CC=4 SCAR=13.9
14 1 ▶ use base qw(CLI::Framework::Command Exporter);
Subroutine BEGIN: called CC=1 SCAR=0.0
15 1 ▶ use CLI::Framework::Exceptions qw( :all );
Subroutine BEGIN: called CC=1 SCAR=0.0
16 1 ▶ use CohortExplorer::Datasource;
Subroutine BEGIN: called CC=1 SCAR=0.0
17 1 ▶ use Exception::Class::TryCatch;
Subroutine BEGIN: called CC=1 SCAR=0.0
18 1 ▶ use FileHandle;
Subroutine BEGIN: called CC=1 SCAR=0.0
19 1 ▶ use File::HomeDir;
Subroutine BEGIN: called CC=1 SCAR=0.0
20 1 ▶ use File::Spec;
Subroutine BEGIN: called CC=1 SCAR=0.0
21 1 ▶ use Config::General;
Subroutine BEGIN: called CC=1 SCAR=0.0
22  
23 # Untaint and set command history file
24 1 ▶ $COMMAND_HISTORY_FILE = $1
Branch: 1 branch
BranchTrueFalseTotal
if "File::Spec"->catfile("File::HomeDir"->my_home, ".CohortExplorer_History") =~ /^(.+)$/ 1 0 1
25 if File::Spec->catfile( File::HomeDir->my_home, ".CohortExplorer_History" ) =~ /^(.+)$/;
26
27 1 my $fh = FileHandle->new(">> $COMMAND_HISTORY_FILE");
28  
29 # Throw exception if command history file does not exist or
30 # is not readable and writable
31 1 ▶ if ( !$fh ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($fh) 0 1 1
32 0 throw_cmd_run_exception( error =>
33 "'$COMMAND_HISTORY_FILE' must exist with RW enabled (i.e. chmod 766) for CohortExplorer"
34 );
35 }
36 1 $fh->close;
37  
38 # Read command history file
39 1 eval {
40 1 $COMMAND_HISTORY_CONFIG =
41 Config::General->new(
42 -ConfigFile => $COMMAND_HISTORY_FILE,
43 -MergeDuplicateOptions => "false",
44 -StoreDelimiter => "=",
45 -SaveSorted => 1
46 );
47 };
48 1 ▶ if ( catch my $e ) {
Branch: 1 branch
BranchTrueFalseTotal
if (&catch(my $e)) 0 1 1
49 0 throw_cmd_run_exception( error => $e );
50 }
51 1 $COMMAND_HISTORY = { $COMMAND_HISTORY_CONFIG->getall };
52 }
53  
54 0 ▶sub option_spec {
Subroutine option_spec: not called CC=1 SCAR=6.9
Pod documented
55 (
56 0 [],
57 [ 'cond|c=s%' => 'impose conditions' ],
58 [ 'out|o=s' => 'provide output directory' ],
59 [ 'save-command|s' => 'save command' ],
60 [ 'stats|S' => 'show summary statistics' ],
61 [ 'export|e=s@' => 'export tables by name' ],
62 [ 'export-all|a' => 'export all tables' ],
63 []
64 );
65 }
66  
67 0 ▶sub validate {
Subroutine validate: not called CC=23 SCAR=63.1
Pod documented
68 0 my ( $self, $opts, @args ) = @_;
69 0 my ( $ds, $csv, $verbose ) = @{ $self->cache->get('cache') }{qw/datasource csv verbose/};
70
71 0 ▶ print STDERR "\nValidating command options/arguments ...\n\n" if $verbose;
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 0 0 0
72
73 ##----- VALIDATE ARG LENGTH, EXPORT AND OUT OPTIONS -----##
74 0 ▶ if ( !$opts->{out} || !-d $opts->{out} || !-w $opts->{out} ) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($opts->{"out"} and -d $opts->{"out"} and -w $opts->{"out"}) 0 0 0
Condition0%0/6
$opts->{"out"} and -d $opts->{"out"} and -w $opts->{"out"}
!ll&&!rl&&r
000
$opts->{"out"} and -d $opts->{"out"}
!ll&&!rl&&r
000
MC/DC0%0/3
$opts->{"out"} and -d $opts->{"out"} and -w $opts->{"out"}
$opts->{"out"} -d $opts->{"out"} -w $opts->{"out"}
Truth table0%0/4
A and B and C
A= $opts->{"out"}B= -d $opts->{"out"}C= -w $opts->{"out"}
ABCresult
0XX0
10X0
1100
1111
75 0 throw_cmd_validation_exception( error =>
76 "Option 'out' is required. The directory specified in 'out' option must exist with RWX enabled (i.e. chmod 777) for CohortExplorer"
77 );
78 }
79  
80
81 0 ▶ if ( $opts->{export} && $opts->{export_all} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opts->{"export"} and $opts->{"export_all"}) 0 0 0
Condition0%0/3
$opts->{"export"} and $opts->{"export_all"}
!ll&&!rl&&r
000
MC/DC0%0/2
$opts->{"export"} and $opts->{"export_all"}
$opts->{"export"} $opts->{"export_all"}
Truth table0%0/3
A and B
A= $opts->{"export"}B= $opts->{"export_all"}
ABresult
0X0
100
111
82 0 throw_cmd_validation_exception( error =>
83 'Mutually exclusive options (export and export-all) specified together' );
84 }
85 0 ▶ if ( @args == 0 || @args > $arg_max ) {
Branch: 1 branch
BranchTrueFalseTotal
if (@args == 0 or @args > $arg_max) 0 0 0
Condition0%0/3
@args == 0 or @args > $arg_max
l!l&&r!l&&!r
000
MC/DC0%0/2
@args == 0 or @args > $arg_max
@args == 0 @args > $arg_max
Truth table0%0/3
A or B
A= @args == 0B= @args > $arg_max
ABresult
1X1
011
000
86 0 throw_cmd_validation_exception(
87 error => "At least 1-$arg_max variable(s) are required" );
88 }
89
90 # Match table names supplied in the export option to
91 # datasource tables and throw exception if they don't match
92 0 ▶ if ( $opts->{export} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opts->{"export"}) 0 0 0
93 0 my @invalid_tables = grep { !$ds->table_info->{$_} } @{ $opts->export };
94 0 ▶ if (@invalid_tables) {
Branch: 1 branch
BranchTrueFalseTotal
if (@invalid_tables) 0 0 0
95 0 throw_cmd_validation_exception( error => 'Invalid table(s) ' . join ( ', ', @invalid_tables ) . ' in export' );
96 }
97 }
98  
99 # Set export to all tables
100 0 ▶ if ( $opts->{export_all} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opts->{"export_all"}) 0 0 0
101 0 $opts->{export} = [ keys %{$ds->table_info} ];
102 }
103  
104 # --- VALIDATE CONDITION OPTION AND ARGS ---
105 # Get valid variables for validation
106 0 my @vars = @{ $self->get_valid_variables };
107
108 0 for my $v (@args) {
109 # Throw exception if entity_id/visit are supplied as an argument
110 0 ▶ if ( $v =~ /^(?:entity_id|visit)$/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($v =~ /^(?:entity_id|visit)$/) 0 0 0
111 0 throw_cmd_validation_exception( error =>
112 "'entity_id' and 'visit' (if applicable) need not be supplied as arguments as they are already part of the query set"
113 );
114 }
115  
116 # Throw exception if some invalid variable is supplied as an argument
117 0 ▶ if ( !grep( $_ eq $v, @vars ) ) {
Branch: 1 branch
BranchTrueFalseTotal
unless (grep(($_ eq $v), @vars)) 0 0 0
118 0 throw_cmd_validation_exception(
119 error => "Invalid variable '$v' in arguments" );
120 }
121 }
122  
123 # Condition can be imposed on all variables including
124 # entity_id and visit (if applicable)
125 0 for my $v ( keys %{ $opts->{cond} } ) {
126  
127 # Throw exception if some invalid variable is supplied as argument
128 0 ▶ if ( !grep( $_ eq $v, @vars ) ) {
Branch: 1 branch
BranchTrueFalseTotal
unless (grep(($_ eq $v), @vars)) 0 0 0
129 0 throw_cmd_validation_exception(
130 error => "Invalid variable '$v' in condition option" );
131 }
132  
133 # Regexp to validate condition option
134 0 ▶ if ( $opts->{cond}{$v} =~
Branch: 1 branch
BranchTrueFalseTotal
if ($opts->{'cond'}{$v} =~ /^\s*(=|\!=|>|<|>=|<=|between|not_between|like|not_like|ilike|in|not_in|regexp|not_regexp)\s*,\s*([^\`]+)\s*$/) { } 0 0 0
135 /^\s*(=|\!=|>|<|>=|<=|between|not_between|like|not_like|ilike|in|not_in|regexp|not_regexp)\s*,\s*([^\`]+)\s*$/
136 )
137 {
138 0 my ( $opr, $val ) = ( $1, $2 );
139 0 $opts->{cond}{$v} =~ s/$opr,\s+/$opr,/;
140  
141 # Validating SQL conditions
142 0 ▶ if ( $opr && $val && $csv->parse($val) ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opr and $val and $csv->parse($val)) { } 0 0 0
Condition0%0/6
$opr and $val and $csv->parse($val)
!ll&&!rl&&r
000
$opr and $val
!ll&&!rl&&r
000
MC/DC0%0/3
$opr and $val and $csv->parse($val)
$opr $val $csv->parse($val)
Truth table0%0/4
A and B and C
A= $oprB= $valC= $csv->parse($val)
ABCresult
0XX0
10X0
1100
1111
143 0 my @val = grep ( s/^\s*|\s*$//g, $csv->fields );
144  
145 # Operators between and not_between require array but for others it is optional
146 0 ▶ if ( $opr =~ /(?:between)/ && scalar @val != 2 ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opr =~ /(?:between)/ and scalar @val != 2) 0 0 0
Condition0%0/3
$opr =~ /(?:between)/ and scalar @val != 2
!ll&&!rl&&r
000
MC/DC0%0/2
$opr =~ /(?:between)/ and scalar @val != 2
$opr =~ /(?:between)/ scalar @val != 2
Truth table0%0/3
A and B
A= $opr =~ /(?:between)/B= scalar @val != 2
ABresult
0X0
100
111
147 0 throw_cmd_validation_exception( error =>
148 "Expecting min and max for '$opr' in '$v' (i.e. between, min, max )" );
149 }
150 }
151 else {
152 0 throw_cmd_validation_exception(
153 error => "Invalid condition '$opts->{cond}{$v}' on variable '$v'" );
154 }
155 }
156 else {
157 0 throw_cmd_validation_exception(
158 error => "Invalid condition '$opts->{cond}{$v}' on variable '$v'" );
159 }
160 }
161 }
162  
163 0 ▶sub run {
Subroutine run: not called CC=7 SCAR=40.3
Pod documented
164  
165 # Overall running of the command
166 0 my ( $self, $opts, @args ) = @_;
167 0 my $rs = $self->process( $opts, @args );
168 0 ▶ if ( $opts->{save_command} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opts->{"save_command"}) 0 0 0
169 0 $self->save_command( $opts, @args );
170 }
171  
172 # If result-set is not empty
173 0 ▶ if (@$rs) {
Branch: 1 branch
BranchTrueFalseTotal
if (@$rs) 0 0 0
174 0 my $dir;
175 0 ▶ if ( $opts->{out} =~ /^(.+)$/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opts->{"out"} =~ /^(.+)$/) 0 0 0
176 0 $dir = File::Spec->catdir( $1, 'CohortExplorer-' . time . $$ );
177 }
178  
179 # Create dir to export data
180 0 eval { mkdir $dir };
181 0 ▶ if ( catch my $e ) {
Branch: 1 branch
BranchTrueFalseTotal
if (&catch(my $e)) { } 0 0 0
182 0 warn $e . "\n";
183 0 $dir = $1;
184 }
185 else {
186 0 eval { chmod 0777, $dir };
187 0 ▶ if ( catch my $e ) {
Branch: 1 branch
BranchTrueFalseTotal
if (&catch(my $e)) 0 0 0
188 0 warn $e . "\n";
189 0 $dir = $1;
190 }
191 }
192 0 $self->export( $opts, $rs, $dir, @args );
193
194 return {
195 headingText => 'summary statistics',
196 rows => $self->summary_stats( $opts, $rs, $dir )
197 }
198 0 ▶ if $opts->{stats};
Branch: 1 branch
BranchTrueFalseTotal
if $opts->{"stats"} 0 0 0
199 }
200 0 return;
201 }
202  
203 0 ▶sub process {
Subroutine process: not called CC=22 SCAR=62.3
Pod documented
204 0 my ( $self, $opts, @args ) = @_;
205 0 my ( $ds, $verbose ) = @{ $self->cache->get('cache') }{qw/datasource verbose/};
206
207 ##----- PREPARE QUERY PARAMETERS FROM CONDITION OPTION AND ARGS -----##
208 # Query parameters can be static, dynamic or both
209 # Static type is applicable to 'standard' datasource but it may also apply to
210 # 'longitudinal' datasource provided the datasource contains tables which are
211 # independent of visits (i.e. static tables).
212 # Dynamic type only applies to longitudinal datasources
213 0 my $param = $self->create_query_params( $opts, @args );
214 0 ▶ my $aliase_in_having = 1 if $ds->dialect->name eq 'mysql';
Branch: 1 branch
BranchTrueFalseTotal
if $ds->dialect->name eq "mysql" 0 0 0
215 0 my $dbh = $ds->dbh;
216 0 my ( $stmt, $vars, $sth, @rows );
217
218 # Construct sql query for static/dynamic or both types (if applicable)
219 0 for my $p ( keys %$param ) {
220 0 tie my %c, 'Tie::IxHash', @{ $param->{$p}{-columns} };
221 $param->{$p}{-columns} = [
222 map {
223 0 $c{$_} . ' AS '
224 . $dbh->quote_identifier($_)
225 } keys %c
226 ];
227 0 eval {
228 0 ( $param->{$p}{stmt}, @{ $param->{$p}{bind} } ) =
229 0 $ds->sqla->select( %{ $param->{$p} } );
230 };
231 0 ▶ if ( catch my $e ) {
Branch: 1 branch
BranchTrueFalseTotal
if (&catch(my $e)) 0 0 0
232 0 throw_cmd_run_exception( error => $e );
233 }
234  
235 # Filter literals from @bind. 'Visit' is not treated as a variable, only to
236 # avoid clash between the column and table name, what if some table is named as visit? (I saw one!!)
237 # Get all indices in @bind containing literals (i.e. variable/column names or undef)
238 0 my @bind = @{ $param->{$p}{bind} };
239 0 my @placeholders;
240 0 for ( 0 .. $#bind ) {
241 0 ▶ if ( ( $c{ $bind[$_] } && $bind[$_] ne 'visit' ) || $bind[$_] eq 'undef' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($c{$bind[$_]} and $bind[$_] ne "visit" or $bind[$_] eq "undef") 0 0 0
Condition0%0/6
$c{$bind[$_]} and $bind[$_] ne "visit" or $bind[$_] eq "undef"
l!l&&r!l&&!r
000
$c{$bind[$_]} and $bind[$_] ne "visit"
!ll&&!rl&&r
000
MC/DC0%0/3
$c{$bind[$_]} and $bind[$_] ne "visit" or $bind[$_] eq "undef"
$c{$bind[$_]} $bind[$_] ne "visit" $bind[$_] eq "undef"
Truth table0%0/5
A and B or C
A= $c{$bind[$_]}B= $bind[$_] ne "visit"C= $bind[$_] eq "undef"
ABCresult
11X1
0X11
1011
0X00
1000
242 0 push @placeholders, $_;
243 }
244 }
245  
246 # Remove variable names from placeholders as they need to be hard coded
247 0 ▶ if (@placeholders) {
Branch: 1 branch
BranchTrueFalseTotal
if (@placeholders) 0 0 0
248 0 for ( 0 .. $#placeholders ) {
249 0 my @chunks = split /\?/, $param->{$p}{stmt};
250 0 my $val = $bind[ $placeholders[$_] ];
251 0 ▶ my $literal = $aliase_in_having ? "`$val`" : ( $c{$val} || $val );
Branch: 1 branch
BranchTrueFalseTotal
$aliase_in_having ? : 0 0 0
Condition0%0/3
$c{$val} or $val
l!l&&r!l&&!r
000
MC/DC0%0/2
$c{$val} or $val
$c{$val} $val
Truth table0%0/3
A or B
A= $c{$val}B= $val
ABresult
1X1
011
000
252
253 0 $chunks[ $placeholders[$_] - $_ ] .= $literal;
254 0 $param->{$p}{stmt} = join '?', @chunks;
255 0 $param->{$p}{stmt} =~ s/([\w\)\"\`\'])\?/$1/g;
256 0 delete( $param->{$p}{bind}->[ $placeholders[$_] ] );
257 }
258  
259 # Update @bind
260 0 @{ $param->{$p}{bind} } = grep( defined($_), @{ $param->{$p}{bind} } );
261 }
262  
263 # undef needs to be hard coded as 'is null' or 'is not null'
264 # depending upon the operator (i.e. = or !=)
265 0 $param->{$p}{stmt} =~ s/\s+=\s+undef\s+/ IS NULL /g;
266 0 $param->{$p}{stmt} =~ s/\s+!=\s+undef\s+/ IS NOT NULL /g;
267 0 $vars->{$p} = [ map { $dbh->quote_identifier($_) } grep ( $_ ne 'entity_id', keys %c ) ];
268 }
269  
270 # Either static or dynamic parameter
271 0 ▶ if ( keys %$param == 1 ) {
Branch: 1 branch
BranchTrueFalseTotal
if (keys %$param == 1) { } 0 0 0
272 0 $stmt = $param->{ ( keys %$param )[0] }{stmt};
273 }
274 else {
275 # Give priority to visit dependent tables (i.e. dynamic tables) therefore do left join
276 # Inner join is done only when conditions are imposed on variables from static tables
277 $stmt =
278 'SELECT dynamic.entity_id, '
279 0 ▶ . join( ', ', map { @{ $vars->{$_} } } keys %$param )
Branch: 1 branch
BranchTrueFalseTotal
!$param->{'static'}{'-having'}{'entity_id'} && keys %{$$param{'static'}{'-having'};} == 1 || keys %{$$param{'static'}{'-having'};} > 1 ? : 0 0 0
Condition0%0/6
not $param->{"static"}{"-having"}{"entity_id"} and keys %{$$param{"static"}{"-having"};} == 1 or keys %{$$param{"static"}{"-having"};} > 1
l!l&&r!l&&!r
000
not $param->{"static"}{"-having"}{"entity_id"} and keys %{$$param{"static"}{"-having"};} == 1
!ll&&!rl&&r
000
MC/DC0%0/3
not $param->{"static"}{"-having"}{"entity_id"} and keys %{$$param{"static"}{"-having"};} == 1 or keys %{$$param{"static"}{"-having"};} > 1
not $param->{"static"}{"-having"}{"entity_id"} keys %{$$param{"static"}{"-having"};} == 1 keys %{$$param{"static"}{"-having"};} > 1
Truth table0%0/5
not(A) and B or C
A= not $param->{"static"}{"-having"}{"entity_id"}B= keys %{$$param{"static"}{"-having"};} == 1C= keys %{$$param{"static"}{"-having"};} > 1
ABCresult
11X1
0X11
1011
0X00
1000
280 . ' FROM '
281 . join(
282 (
283 (
284 (
285 !$param->{static}{-having}{entity_id}
286 0 && keys %{ $param->{static}{-having} } == 1
287 )
288 0 || keys %{ $param->{static}{-having} } > 1
289 ) ? ' INNER JOIN ' : ' LEFT OUTER JOIN '
290 ),
291 0 map { "( " . $param->{$_}{stmt} . " ) AS $_" } keys %$param
292 ) . ' ON dynamic.entity_id = static.entity_id';
293 }
294
295 0 my @bind = map { @{ $param->{$_}{bind} } } keys %$param;
296
297 0 ▶ print STDERR "Running the query with "
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 0 0 0
298 . scalar @bind
299 . " bind variables ...\n\n"
300 if $verbose;
301
302 0 require Time::HiRes;
303 0 my $timeStart = Time::HiRes::time();
304
305 0 eval {
306 0 $sth = $dbh->prepare_cached($stmt);
307 0 $sth->execute(@bind);
308 };
309
310 0 ▶ if ( catch my $e ) {
Branch: 1 branch
BranchTrueFalseTotal
if (&catch(my $e)) 0 0 0
311 0 throw_cmd_run_exception( error => $e );
312 }
313
314 0 my $timeEnd = Time::HiRes::time();
315
316 0 ( my $command = lc ref $self ) =~ s/^.+:://;
317
318 0 ▶ printf( "Found %d rows in %.2f sec matching the %s query criteria ...\n\n",
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 0 0 0
Condition0%0/2
$sth->rows || 0
l!l
00
MC/DC0%0/1
$sth->rows || 0
$sth->rows
Truth table0%0/2
A
A= $sth->rows
Aresult
11
00
319 ( $sth->rows || 0 ),
320 ( $timeEnd - $timeStart ), $command )
321 if $verbose;
322
323 0 ▶ if ( $sth->rows ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($sth->rows) 0 0 0
324 0 my @cols = @{ $sth->{NAME} };
325 0 @rows = @{ $sth->fetchall_arrayref( [] ) };
326  
327 # Sort results by entity_id (entity_id can be number or text)
328 @rows =
329 DBI::looks_like_number( $rows[0]->[0] )
330 0 ? sort { $a->[0] <=> $b->[0] } @rows
331 0 ▶ : sort { $a->[0] cmp $b->[0] } @rows;
Branch: 1 branch
BranchTrueFalseTotal
DBI::looks_like_number($rows[0][0]) ? : 0 0 0
332 0 unshift @rows, \@cols;
333 }
334 0 $sth->finish;
335 0 return \@rows;
336 }
337  
338 0 ▶sub save_command {
Subroutine save_command: not called CC=10 SCAR=47.0
Pod documented
339 0 my ( $self, $opts, @args ) = @_;
340 0 my ( $ds, $ds_name, $verbose ) = @{ $self->cache->get('cache') }{qw/datasource datasource_name verbose/};
341 0 my $count = scalar keys %{ $COMMAND_HISTORY->{datasource}{$ds_name} };
342 0 ( my $command = lc ref $self ) =~ s/^.+:://;
343 0 ▶ print STDERR "Saving command ...\n\n" if $verbose;
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 0 0 0
344 0 require POSIX;
345  
346 # Remove the save-command option
347 0 delete $opts->{save_command};
348  
349 # Construct the command run by the user and store it in $COMMAND_HISTORY
350 0 for my $opt ( keys %$opts ) {
351 0 ▶ if ( ref $opts->{$opt} eq 'ARRAY' ) {
Branch: 2 branches
BranchTrueFalseTotal
if (ref $opts->{$opt} eq 'ARRAY') { } 0 0 0
elsif (ref $opts->{$opt} eq 'HASH') { } 0 0 0
352 0 $command .= " --$opt=" . join( " --$opt=", @{ $opts->{$opt} } );
353 }
354 elsif ( ref $opts->{$opt} eq 'HASH' ) {
355 $command .= join( ' ',
356 0 map { "--$opt=$_='$opts->{$opt}{$_}' " }
357 0 keys %{ $opts->{$opt} } );
358 }
359 else {
360 0 ( $_ = $opt ) =~ s/_/-/g;
361 0 $command .= " --$_=$opts->{$opt} ";
362 0 ▶ $command =~ s/($_)=1/$1/ if $opts->{export_all} || $opts->{stats};
Branch: 1 branch
BranchTrueFalseTotal
if $opts->{"export_all"} or $opts->{"stats"} 0 0 0
Condition0%0/3
$opts->{"export_all"} or $opts->{"stats"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$opts->{"export_all"} or $opts->{"stats"}
$opts->{"export_all"} $opts->{"stats"}
Truth table0%0/3
A or B
A= $opts->{"export_all"}B= $opts->{"stats"}
ABresult
1X1
011
000
363 }
364 }
365 0 $command .= ' ' . join( ' ', @args );
366 0 ▶ $command =~ s/\-\-export=[^\s]+\s*/ /g if $opts->{export_all};
Branch: 1 branch
BranchTrueFalseTotal
if $opts->{"export_all"} 0 0 0
367 0 $command =~ s/\s+/ /g;
368 0 for ( keys %{ $COMMAND_HISTORY->{datasource} } ) {
369 0 ▶ if ( $_ eq $ds_name ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($_ eq $ds_name) 0 0 0
370 0 $COMMAND_HISTORY->{datasource}{$_}{ ++$count } = {
371 datetime => POSIX::strftime( '%d/%m/%Y %T', localtime ),
372 command => $command
373 };
374 }
375 }
376 }
377  
378 0 ▶sub export {
Subroutine export: not called CC=16 SCAR=56.1
Pod documented
379 0 my ( $self, $opts, $rs, $dir, @args ) = @_;
380 0 my ( $ds, $verbose ) = @{ $self->cache->get('cache') }{qw/datasource verbose/};
381
382 ##---- WRITE QUERY PARAMETERS FILE -----##
383 0 my $file = File::Spec->catfile( $dir, 'QueryParameters' );
384 0 ▶ my $fh = FileHandle->new("> $file") or throw_cmd_run_exception( error => "Failed to open file: $!" );
Branch: 1 branch
BranchTrueFalseTotal
my $fh = "FileHandle"->new("> $file") or throw_cmd_run_exception("error", "Failed to open file: $!") 0 0 0
385
386 0 print $fh "Query Parameters" . "\n\n";
387 0 print $fh "Arguments supplied: " . join( ', ', @args ) . "\n\n";
388 0 print $fh "Conditions imposed: " . scalar( keys %{ $opts->{cond} } ) . "\n\n";
389 0 my @vars = keys %{ $opts->{cond} };
390  
391 # Write all imposed conditions
392 0 for ( 0 .. $#vars ) {
393 0 $opts->{cond}{ $vars[$_] } =~ /^\s*([^,]+),\s*(.+)\s*$/;
394 0 print $fh ( $_ + 1 ) . ") $vars[$_]: '$1' => $2" . "\n";
395 }
396  
397 # Write all tables to be exported
398 print $fh "\n"
399 . "Tables exported: "
400 0 ▶ . ( $opts->{export} ? join ', ', @{ $opts->{export} } : 'None' ) . "\n";
Branch: 1 branch
BranchTrueFalseTotal
$opts->{'export'} ? : 0 0 0
401 0 $fh->close;
402
403 0 ▶ print STDERR "Exporting query results in $dir ...\n\n" if $verbose;
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 0 0 0
404  
405 # Process result set and get entities in the result set
406 0 my $rs_entity = $self->process_result( $opts, $rs, $dir, @args );
407 0 ▶ if ( $opts->{export} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opts->{"export"}) 0 0 0
408 0 my ( $stmt, @bind, $sth );
409 0 my $struct = $ds->entity_structure;
410 0 my %map = @{ $struct->{-columns} };
411  
412 # Construct sql query with a placeholder for table name
413 # Columns follow the order: entity_id, variable, value and visit (if applicable)
414 0 eval {
415 ( $stmt, @bind ) =
416 $ds->sqla->select(
417 -columns =>
418 0 ▶ [ map { $map{$_} || 'NULL' } qw/entity_id variable value visit/ ],
Condition0%0/2
$map{$_} || 'NULL'
l!l
00
MC/DC0%0/1
$map{$_} || 'NULL'
$map{$_}
Truth table0%0/2
A
A= $map{$_}
Aresult
11
00
419 -from => $struct->{-from},
420 0 -where => { %{ $struct->{-where} }, $map{table} => { '=' => '?' } }
421 );
422 };
423 0 ▶ if ( catch my $e ) {
Branch: 1 branch
BranchTrueFalseTotal
if (&catch(my $e)) 0 0 0
424 0 throw_cmd_run_exception( error => $e );
425 }
426  
427 0 $sth = $ds->dbh->prepare_cached($stmt);
428  
429 # The user might have supplied multiple conditions in the where clause
430 # of entity_structure() method so split the $stmt by '?' and get the index of
431 # 'table' placeholder
432 0 my @chunks = split /\?/, $stmt;
433 0 my ($placeholder) = grep ( $chunks[$_] =~ /\s+$map{table}\s+=\s+/, 0 .. $#chunks );
434 0 for my $table ( @{ $opts->{export} } ) {
435  
436 # Ensure the user has access to at least one variable in the table to be exported
437 0 ▶ if ( grep ( /^$table\..+$/, keys %{ $ds->variable_info } ) ) {
Branch: 1 branch
BranchTrueFalseTotal
if (grep /^$table\..+$/, keys %{$ds->variable_info;}) { } 0 0 0
438 0 $bind[$placeholder] = $table;
439 0 eval { $sth->execute(@bind); };
440 0 ▶ if ( catch my $e ) {
Branch: 1 branch
BranchTrueFalseTotal
if (&catch(my $e)) 0 0 0
441 0 throw_cmd_run_exception( error => $e );
442 }
443
444 0 my $rows = $sth->fetchall_arrayref( [] );
445 0 $sth->finish;
446
447 0 ▶ if (@$rows) {
Branch: 1 branch
BranchTrueFalseTotal
if (@$rows) { } 0 0 0
448 0 ▶ print STDERR "Exporting $table ...\n\n" if $verbose;
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 0 0 0
449 0 my $subdir = File::Spec->catdir( $dir, $table );
450  
451 # Create dir to export data
452 0 eval { mkdir $subdir };
453 0 ▶ if ( catch my $e ) {
Branch: 1 branch
BranchTrueFalseTotal
if (&catch(my $e)) 0 0 0
454 0 warn $e . "\n";
455 0 $dir = $1;
456 }
457  
458 # Write metadata
459 0 $self->export_metadata( $table, $subdir );
460  
461 # Process table set
462 0 $self->process_table( $table, $rows, $subdir, $rs_entity );
463 }
464 else {
465 0 ▶ print STDERR "Omitting $table (no entities) ...\n\n"
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 0 0 0
466 if $verbose;
467 }
468 }
469 else {
470 0 ▶ print STDERR "Omitting $table (no variables) ...\n\n"
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 0 0 0
471 if $verbose;
472 }
473 }
474 }
475 }
476  
477 0 ▶sub summary_stats {
Subroutine summary_stats: not called CC=20 SCAR=60.4
Pod documented
478 0 my ( $self, $opts, $rs, $dir ) = @_;
479 0 my ( $ds, $csv, $verbose ) = @{ $self->cache->get('cache') }{qw/datasource csv verbose/};
480 0 ▶ print STDERR "Preparing dataset for summary statistics ...\n\n"
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 0 0 0
481 if $verbose;
482  
483 # Create data for calculating summary statistics from the result set
484 0 my ( $data, $key_index, @cols ) = $self->create_dataset($rs);
485 0 my $var_info = $ds->variable_info;
486  
487 # Open a file for writing descriotive statistics
488 0 my $file = File::Spec->catfile( $dir, "SummaryStatistics.csv" );
489 0 ▶ my $fh = FileHandle->new("> $file") or throw_cmd_run_exception( error => "Failed to open file: $!" );
Branch: 1 branch
BranchTrueFalseTotal
my $fh = "FileHandle"->new("> $file") or throw_cmd_run_exception("error", "Failed to open file: $!") 0 0 0
490 0 ▶ $csv->print( $fh, \@cols ) or throw_cmd_run_exception( error => $csv->error_diag );
Branch: 1 branch
BranchTrueFalseTotal
$csv->print($fh, \@cols) or throw_cmd_run_exception("error", $csv->error_diag) 0 0 0
491 0 push my @stats, [@cols];
492  
493 # Sort keys (i.e. visit/entity_id) according to their type (text/number)
494 my @keys =
495 DBI::looks_like_number( ( keys %$data )[-1] )
496 0 ▶ ? sort { $a <=> $b } keys %$data
Branch: 1 branch
BranchTrueFalseTotal
DBI::looks_like_number((keys %$data)[-1]) ? : 0 0 0
497 : sort keys %$data;
498
499 0 ▶ @cols = $key_index == 0 ? @cols : splice @cols, 1;
Branch: 1 branch
BranchTrueFalseTotal
$key_index == 0 ? : 0 0 0
500
501 0 ▶ print STDERR "calculating summary statistics for "
Branch: 1 branch
BranchTrueFalseTotal
if $verbose 0 0 0
502 . ( $#cols + 1 )
503 . ' query variable(s): '
504 . join( ', ', @cols )
505 . " ... \n\n"
506 if $verbose;
507  
508 # Key can be entity_id, visit or none depending on the command (i.e. search/compare) run.
509 # For longitudinal datasources the search command calculates statistics with respect to visit,
510 # hence the key is visit. Standard datasources are not visit based so no key is used.
511 # Compare command uses entity_id as the key when calculating statistics for longitudinal datasources.
512
513 0 require Statistics::Descriptive;
514
515 0 for my $key (@keys) {
516 0 ▶ push my @rows, ( $key_index == 0 ? () : $key );
Branch: 1 branch
BranchTrueFalseTotal
$key_index == 0 ? : 0 0 0
517 0 for my $c (@cols) {
518 0 my $sdf = Statistics::Descriptive::Full->new;
519  
520 # Calculate statistics for categorical variables with type 'text' and boolean variables only
521 0 ▶ if ( $var_info->{$c}
Branch: 2 branches
BranchTrueFalseTotal
if ($var_info->{$c} and $var_info->{$c}{'category'}) { } 0 0 0
elsif ($var_info->{$c} and $var_info->{$c}{'type'} =~ /(signed|decimal|int|float|numeric|real|double)/i and scalar @{$data->{$key}{$c};} > 0) { } 0 0 0
Condition0%0/3
$var_info->{$c} and $var_info->{$c}{"category"}
!ll&&!rl&&r
000
MC/DC0%0/2
$var_info->{$c} and $var_info->{$c}{"category"}
$var_info->{$c} $var_info->{$c}{"category"}
Truth table0%0/3
A and B
A= $var_info->{$c}B= $var_info->{$c}{"category"}
ABresult
0X0
100
111
522 && $var_info->{$c}{category} )
523 {
524 0 ▶ my $N = @{ $data->{$key}{$c} } || 1;
Condition0%0/2
@{$data->{$key}{$c};} || 1
l!l
00
MC/DC0%0/1
@{$data->{$key}{$c};} || 1
@{$data->{$key}{$c};}
Truth table0%0/2
A
A= @{$data->{$key}{$c};}
Aresult
11
00
525 tie my %category, 'Tie::IxHash',
526 0 map { /^([^,]+),\s*(.+)$/, $1 => $2 } split /\s*\n\s*/,
527 0 $var_info->{$c}{category};
528  
529 # Order of categories should remain the same
530 0 tie my %count, 'Tie::IxHash', map { $_ => 0 } keys %category;
531  
532 # Get break-down by each category
533 0 for ( @{ $data->{$key}{$c} } ) {
534 0 $count{$_}++;
535 }
536 push @rows,
537 0 sprintf( "N: %1s\n", scalar @{ $data->{$key}{$c} } ) . join "\n", map {
538 0 sprintf( ( $category{$_} || $_ ) . "\: %d (%1.2f%s",
539 0 ▶ $count{$_}, $count{$_} * 100 / $N, '%)' )
Condition0%0/3
$category{$_} || $_
l!l&&r!l&&!r
000
MC/DC0%0/2
$category{$_} || $_
$category{$_} $_
Truth table0%0/3
A || B
A= $category{$_}B= $_
ABresult
1X1
011
000
540 } keys %count;
541 }
542  
543 # Calculate statistics for integer/decimal variables
544 ▶ elsif (
Condition0%0/6
$var_info->{$c} and $var_info->{$c}{"type"} =~ /(signed|decimal|int|float|numeric|real|double)/i and scalar @{$data->{$key}{$c};} > 0
!ll&&!rl&&r
000
$var_info->{$c} and $var_info->{$c}{"type"} =~ /(signed|decimal|int|float|numeric|real|double)/i
!ll&&!rl&&r
000
MC/DC0%0/3
$var_info->{$c} and $var_info->{$c}{"type"} =~ /(signed|decimal|int|float|numeric|real|double)/i and scalar @{$data->{$key}{$c};} > 0
$var_info->{$c} $var_info->{$c}{"type"} =~ /(signed|decimal|int|float|numeric|real|double)/i scalar @{$data->{$key}{$c};} > 0
Truth table0%0/4
A and B and C
A= $var_info->{$c}B= $var_info->{$c}{"type"} =~ /(signed|decimal|int|float|numeric|real|double)/iC= scalar @{$data->{$key}{$c};} > 0
ABCresult
0XX0
10X0
1100
1111
545 $var_info->{$c}
546 && ( $var_info->{$c}{type} =~
547 /(signed|decimal|int|float|numeric|real|double)/i )
548 0 && scalar @{ $data->{$key}{$c} } > 0
549 )
550 {
551  
552 # Remove single/double quotes (if any) from the numeric array
553 0 $sdf->add_data( map { s/[\'\"]+//; $_ } @{ $data->{$key}{$c} } );
554 0 eval {
555 0 push @rows,
556 sprintf(
557 "N: %3s\nMean: %.2f\nMedian: %.2f\nSD: %.2f\nMax: %.2f\nMin: %.2f",
558 $sdf->count, $sdf->mean, $sdf->median, $sdf->standard_deviation,
559 $sdf->max, $sdf->min );
560 };
561 0 ▶ if ( catch my $e ) {
Branch: 1 branch
BranchTrueFalseTotal
if (&catch(my $e)) 0 0 0
562 0 throw_cmd_run_exception($e);
563 }
564 }
565  
566 # For all other variable types (e.g. date, datetime) get no. of observations alone
567 else {
568 0 push @rows, sprintf( "N: %3s\n", scalar @{ $data->{$key}{$c} } );
569 }
570 }
571 0 ▶ $csv->print( $fh, \@rows )
Branch: 1 branch
BranchTrueFalseTotal
$csv->print($fh, \@rows) or throw_cmd_run_exception("error", $csv->error_diag) 0 0 0
572 or throw_cmd_run_exception( error => $csv->error_diag );
573 0 push @stats, [@rows];
574 }
575 0 $fh->close;
576 0 return \@stats;
577 }
578  
579 0 ▶sub export_metadata {
Subroutine export_metadata: not called CC=11 SCAR=48.8
Pod documented
580 0 my ( $self, $table, $dir ) = @_;
581 0 my ( $ds, $csv ) = @{ $self->cache->get('cache') }{qw/datasource csv/};
582 0 my $var_info = $ds->variable_info;
583  
584 # First two columns are always variable and table names
585 0 my @cols = qw/variable table/;
586 0 for ( keys %{ { @{ $ds->variable_structure->{-columns} } } } ) {
587 0 ▶ if ( $_ ne 'variable' && $_ ne 'table' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($_ ne "variable" and $_ ne "table") 0 0 0
Condition0%0/3
$_ ne "variable" and $_ ne "table"
!ll&&!rl&&r
000
MC/DC0%0/2
$_ ne "variable" and $_ ne "table"
$_ ne "variable" $_ ne "table"
Truth table0%0/3
A and B
A= $_ ne "variable"B= $_ ne "table"
ABresult
0X0
100
111
588 0 push @cols, $_;
589 }
590 }
591 0 my $file = File::Spec->catfile( $dir, 'variables.csv' );
592 0 ▶ my $untaint = $1 if ( $file =~ /^(.+)$/ );
Branch: 1 branch
BranchTrueFalseTotal
if $file =~ /^(.+)$/ 0 0 0
593
594 0 ▶ my $fh = FileHandle->new("> $untaint") or throw_cmd_run_exception( error => "Failed to open file: $!" );
Branch: 1 branch
BranchTrueFalseTotal
my $fh = "FileHandle"->new("> $untaint") or throw_cmd_run_exception("error", "Failed to open file: $!") 0 0 0
595 0 ▶ $csv->print( $fh, \@cols ) or throw_cmd_run_exception( error => $csv->error_diag );
Branch: 1 branch
BranchTrueFalseTotal
$csv->print($fh, \@cols) or throw_cmd_run_exception("error", $csv->error_diag) 0 0 0
596
597 0 for my $v ( keys %$var_info ) {
598 0 ▶ if ( $var_info->{$v}{table} eq $table ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($var_info->{$v}{"table"} eq $table) 0 0 0
599 0 ▶ my @vals = map { $var_info->{$v}{$_} || '' } @cols;
Condition0%0/2
$var_info->{$v}{$_} || ''
l!l
00
MC/DC0%0/1
$var_info->{$v}{$_} || ''
$var_info->{$v}{$_}
Truth table0%0/2
A
A= $var_info->{$v}{$_}
Aresult
11
00
600 0 ▶ $csv->print( $fh, \@vals ) or throw_cmd_run_exception( error => $csv->error_diag );
Branch: 1 branch
BranchTrueFalseTotal
$csv->print($fh, \@vals) or throw_cmd_run_exception("error", $csv->error_diag) 0 0 0
601 }
602 }
603 0 $fh->close;
604 }
605  
606 #------------- SUBCLASSES HOOKS -------------#
607 0 ▶sub usage_text { }
Subroutine usage_text: not called CC=1 SCAR=0.0
Pod documented
608  
609 0 ▶sub get_valid_variables { }
Subroutine get_valid_variables: not called CC=1 SCAR=0.0
Pod documented
610  
611 0 ▶sub create_query_params { }
Subroutine create_query_params: not called CC=1 SCAR=0.0
Pod documented
612  
613 0 ▶sub process_result { }
Subroutine process_result: not called CC=1 SCAR=0.0
Pod documented
614  
615 0 ▶sub process_table { }
Subroutine process_table: not called CC=1 SCAR=0.0
Pod documented
616  
617 0 ▶sub create_dataset { }
Subroutine create_dataset: not called CC=1 SCAR=0.0
Pod documented
618  
619 1 ▶END {
Subroutine END: called CC=2 SCAR=7.6
620  
621 # Write saved commands to command history file
622 1 eval {
623 1 $COMMAND_HISTORY_CONFIG->save_file( $COMMAND_HISTORY_FILE, $COMMAND_HISTORY );
624 };
625 1 ▶ if ( catch my $e ) {
Branch: 1 branch
BranchTrueFalseTotal
if (&catch(my $e)) 0 1 1
626 0 throw_cmd_run_exception( error => $e );
627 }
628 }
629
630 #-------
631 1 1;
632 __END__
« CohortExplorer/Command/Query/Search.pm ↑ Summary CohortExplorer/Command/Find.pm »