CohortExplorer/Command/Query/Search.pm

filter: Statementstmt 10.5% 14 / 133 Branchbran 0.0% 0 / 56 Conditioncond 0.0% 0 / 29 MC/DCmcdc 0.0% 0 / 17 Subroutinesub 40.0% 4 / 10 Podpod 100% 6 / 6 totaltotal 9.5% 24 / 251 CC 49 SCAR 57.1
CC
49
Cov
6%
create_query_params
552.0
process_table
272.0
process_result
42.0
CRAP 302.9
« CohortExplorer/Application.pm ↑ Summary CohortExplorer/Command/Query.pm »
1 package CohortExplorer::Command::Query::Search;
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  
8 1 ▶use base qw(CohortExplorer::Command::Query);
Subroutine BEGIN: called CC=1 SCAR=0.0
9 1 ▶use CLI::Framework::Exceptions qw( :all );
Subroutine BEGIN: called CC=1 SCAR=0.0
10  
11 #-------
12 # Command is available to both standard and longitudinal datasources
13 0 ▶sub usage_text {
Subroutine usage_text: not called CC=1 SCAR=6.9
Pod documented
14 0 q\
15 search [--out|o=<directory>] [--export|e=<table>] [--export-all|a] [--save-command|s] [--stats|S] [--cond|c=<cond>]
16 [variable] : search entities with/without conditions on variables
17
18
19 NOTES
20 The variables entity_id and visit (if applicable) must not be provided as arguments as they are already part of
21 the query-set. However, the user can impose conditions on both variables.
22  
23 Other variables in arguments/cond (option) must be referenced as <table>.<variable>.
24  
25 The conditions can be imposed using the operators such as =, !=, >, <, >=, <=, between, not_between, like,
26 not_like, ilike, in, not_in, regexp and not_regexp.
27  
28 The keyword undef can be used to search for null values.
29  
30 The directory specified in 'out' option must have RWX enabled (i.e. chmod 777) for CohortExplorer.
31  
32  
33 EXAMPLES
34 search --out=/home/user/exports --stats --save-command --cond=DS.Status='=, CTL, MCI' GDS.Score
35
36 search --out=/home/user/exports --stats --save-command --cond=CER.Score='<=, 30' GDS.Score
37  
38 search --out=/home/user/exports --export-all --cond=SD.Sex='=, Male' CER.Score DIS.Status
39  
40 search -o/home/user/exports -eDS -eSD -c entity_id='like, DCR%' DIS.Status
41  
42 search -o/home/user/exports -Ssa -c visit='in, 1, 3, 5' DIS.Status
43  
44 search -o/home/user/exports -c CER.Score='between, 25, 30' DIS.Status
45 \;
46 }
47  
48 0 ▶sub get_valid_variables {
Subroutine get_valid_variables: not called CC=2 SCAR=17.9
Pod documented
49 0 my ($self) = @_;
50 0 my $ds = $self->cache->get('cache')->{datasource};
51 0 my @vars = keys %{ $ds->variable_info };
52 0 ▶ return $ds->type eq 'standard'
Branch: 1 branch
BranchTrueFalseTotal
$ds->type eq 'standard' ? : 0 0 0
53 ? [ qw/entity_id/, @vars ]
54 : [ qw/entity_id visit/, @vars ];
55 }
56  
57 0 ▶sub create_query_params {
Subroutine create_query_params: not called CC=23 SCAR=63.1
Pod documented
58 0 my ( $self, $opts, @args ) = @_;
59 0 my ( $ds, $csv ) = @{ $self->cache->get('cache') }{qw/datasource csv/};
60 0 my $dialect = $ds->dialect;
61 0 my $struct = $ds->entity_structure;
62 0 my %map = @{ $struct->{-columns} };
63 0 ▶ my $aliase_in_having = $dialect->aliase_in_having || undef;
Condition0%0/2
$dialect->aliase_in_having || undef
l!l
00
MC/DC0%0/1
$dialect->aliase_in_having || undef
$dialect->aliase_in_having
Truth table0%0/2
A
A= $dialect->aliase_in_having
Aresult
11
00
64  
65 0 my ( @vars, %param );
66  
67 # Extract all variables from args/cond (option) except
68 # entity_id and visit as they are dealt separately
69 0 for my $v ( @args, keys %{ $opts->{cond} } ) {
70 0 ▶ if ( !grep ( $_ eq $v, ( 'entity_id', 'visit', @vars ) ) ) {
Branch: 1 branch
BranchTrueFalseTotal
unless (grep(($_ eq $v), ("entity_id", "visit", @vars))) 0 0 0
71 0 push @vars, $v;
72 }
73 }
74  
75 0 for (@vars) {
76 ##---- BUILD 'WHERE' FOR TABLES AND VARIABLES ----##
77 0 my ( $t, $v ) = @{ $ds->variable_info->{$_} }{qw/table variable/};
78 0 my $table_type = $ds->table_info->{$t}{__type__};
79
80 0 push @{ $param{$table_type}{-where}{ $map{table} }{-in} }, $t;
81 0 push @{ $param{$table_type}{-where}{ $map{variable} }{-in} }, $v;
82
83 my $col_sql = 'CAST( NULLIF( '
84 . $dialect->aggregate(
85 (
86 (
87 $table_type eq 'static' ? 'DISTINCT' : ''
88 )
89 . " CASE WHEN CONCAT($map{table}, '.', $map{variable} ) = '$_' THEN TRIM( $map{value} ) ELSE NULL END "
90 )
91 )
92 . ", '' ) AS "
93 0 ▶ . $ds->variable_info->{$_}{__type__} . ' )';
Branch: 1 branch
BranchTrueFalseTotal
$table_type eq 'static' ? : 0 0 0
94  
95 0 push @{ $param{$table_type}{-columns} }, $_ => $col_sql;
96
97 # Take into account the dialect's support for the use of aliases in 'having' clause
98 # Postgresql does not allow the use of column names but mysql does making the query more
99 # readable and easy to understand/debug
100 0 ▶ my $alias = $aliase_in_having ? "`$_`" : $col_sql;
Branch: 1 branch
BranchTrueFalseTotal
$aliase_in_having ? : 0 0 0
101
102 ##---- BUILD 'HAVING' FOR CONDITIONS ON VARIABLES ----##
103 0 ▶ if ( $opts->{cond}
Branch: 1 branch
BranchTrueFalseTotal
if ($opts->{"cond"} and $opts->{"cond"}{$_} and $csv->parse($opts->{"cond"}{$_})) 0 0 0
Condition0%0/6
$opts->{"cond"} and $opts->{"cond"}{$_} and $csv->parse($opts->{"cond"}{$_})
!ll&&!rl&&r
000
$opts->{"cond"} and $opts->{"cond"}{$_}
!ll&&!rl&&r
000
MC/DC0%0/3
$opts->{"cond"} and $opts->{"cond"}{$_} and $csv->parse($opts->{"cond"}{$_})
$opts->{"cond"} $opts->{"cond"}{$_} $csv->parse($opts->{"cond"}{$_})
Truth table0%0/4
A and B and C
A= $opts->{"cond"}B= $opts->{"cond"}{$_}C= $csv->parse($opts->{"cond"}{$_})
ABCresult
0XX0
10X0
1100
1111
104 && $opts->{cond}{$_}
105 && $csv->parse( $opts->{cond}{$_} ) )
106 {
107 0 my ( $opr, @conds ) = grep ( s/^\s*|\s*$//g, $csv->fields );
108 0 ▶ $param{$table_type}{-having}{$alias} = { ( $dialect->$opr || $opr ) => \@conds };
Condition0%0/3
$dialect->$opr || $opr
l!l&&r!l&&!r
000
MC/DC0%0/2
$dialect->$opr || $opr
$dialect->$opr $opr
Truth table0%0/3
A || B
A= $dialect->$oprB= $opr
ABresult
1X1
011
000
109 }
110 }
111
112 ##---- ADD IDENTIFIER, VISIT AND GROUP BY COLUMNS ----##
113 0 for ( keys %param ) {
114 0 ▶ if ( $_ eq 'static' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($_ eq 'static') { } 0 0 0
115 0 $param{$_}{-group_by} = 'entity_id';
116 }
117 else {
118 0 $param{$_}{-group_by} = [qw/entity_id visit/];
119 0 unshift @{ $param{$_}{-columns} }, visit => $map{visit};
120 0 ▶ my $alias = $aliase_in_having ? 'visit' : $map{visit};
Branch: 1 branch
BranchTrueFalseTotal
$aliase_in_having ? : 0 0 0
121 # Set condition on visit
122 0 ▶ if ( $opts->{cond}
Branch: 1 branch
BranchTrueFalseTotal
if ($opts->{"cond"} and $opts->{"cond"}{"visit"} and $csv->parse($opts->{"cond"}{"visit"})) 0 0 0
Condition0%0/6
$opts->{"cond"} and $opts->{"cond"}{"visit"} and $csv->parse($opts->{"cond"}{"visit"})
!ll&&!rl&&r
000
$opts->{"cond"} and $opts->{"cond"}{"visit"}
!ll&&!rl&&r
000
MC/DC0%0/3
$opts->{"cond"} and $opts->{"cond"}{"visit"} and $csv->parse($opts->{"cond"}{"visit"})
$opts->{"cond"} $opts->{"cond"}{"visit"} $csv->parse($opts->{"cond"}{"visit"})
Truth table0%0/4
A and B and C
A= $opts->{"cond"}B= $opts->{"cond"}{"visit"}C= $csv->parse($opts->{"cond"}{"visit"})
ABCresult
0XX0
10X0
1100
1111
123 && $opts->{cond}{visit}
124 && $csv->parse( $opts->{cond}{visit} ) )
125 {
126 0 my ( $opr, @conds ) = grep ( s/^\s*|\s*$//g, $csv->fields );
127 0 ▶ $param{$_}{-having}{$alias} = { ( $dialect->$opr || $opr ) => \@conds };
Condition0%0/3
$dialect->$opr || $opr
l!l&&r!l&&!r
000
MC/DC0%0/2
$dialect->$opr || $opr
$dialect->$opr $opr
Truth table0%0/3
A || B
A= $dialect->$oprB= $opr
ABresult
1X1
011
000
128 }
129 }
130
131 ###--- PARAMETERS COMMON TO BOTH STATIC AND DYNAMIC TABLE TYPES ---###
132 0 unshift @{ $param{$_}{-columns} }, entity_id => $map{entity_id};
133
134 0 ▶ if ( $opts->{cond} && $opts->{cond}{entity_id} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opts->{"cond"} and $opts->{"cond"}{"entity_id"}) 0 0 0
Condition0%0/3
$opts->{"cond"} and $opts->{"cond"}{"entity_id"}
!ll&&!rl&&r
000
MC/DC0%0/2
$opts->{"cond"} and $opts->{"cond"}{"entity_id"}
$opts->{"cond"} $opts->{"cond"}{"entity_id"}
Truth table0%0/3
A and B
A= $opts->{"cond"}B= $opts->{"cond"}{"entity_id"}
ABresult
0X0
100
111
135 # Set condition on entity_id
136 0 ▶ my $alias = $aliase_in_having ? 'entity_id' : $map{entity_id};
Branch: 1 branch
BranchTrueFalseTotal
$aliase_in_having ? : 0 0 0
137 0 my ( $opr, @conds ) = split /\s*,\s*/, $opts->{cond}{entity_id};
138 0 ▶ $param{$_}{-having}{$alias} = { ( $dialect->$opr || $opr ) => \@conds };
Condition0%0/3
$dialect->$opr || $opr
l!l&&r!l&&!r
000
MC/DC0%0/2
$dialect->$opr || $opr
$dialect->$opr $opr
Truth table0%0/3
A || B
A= $dialect->$oprB= $opr
ABresult
1X1
011
000
139 }
140
141 0 $param{$_}{-from} = $struct->{-from};
142 $param{$_}{-where} =
143 $struct->{-where}
144 0 ? { %{ $param{$_}{-where} }, %{ $struct->{-where} } }
145 0 ▶ : $param{$_}{-where};
Branch: 1 branch
BranchTrueFalseTotal
$struct->{'-where'} ? : 0 0 0
146  
147 # Make sure condition clause in 'tables' has no duplicate placeholders
148 $param{$_}{-where}{ $map{table} }{-in} = [
149 0 keys %{ { map { $_ => 1 } @{ $param{$_}{-where}{ $map{table} }{-in} } } } ];
150 }
151
152 0 return \%param;
153 }
154  
155 0 ▶sub process_result {
Subroutine process_result: not called CC=6 SCAR=37.4
Pod documented
156 0 my ( $self, $opts, $rs, $dir, @args ) = @_;
157 0 my $csv = $self->cache->get('cache')->{csv};
158 0 my %rs_entity;
159  
160 # Write result set
161 0 my $file = File::Spec->catfile( $dir, 'QueryOutput.csv' );
162 0 ▶ my $fh = FileHandle->new("> $file")
Branch: 1 branch
BranchTrueFalseTotal
my $fh = "FileHandle"->new("> $file") or throw_cmd_run_exception("error", "Failed to open file: $!") 0 0 0
163 or throw_cmd_run_exception( error => "Failed to open file: $!" );
164  
165 # Returns a ref to hash with key as entity_id and value either:
166 # list of visit numbers if the result-set contains visit column
167 # (i.e. dynamic tables- Longitudinal datasources) or,
168 # empty list (i.e. static tables)
169 0 for ( 0 .. $#$rs ) {
170 0 ▶ if ( $_ > 0 ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($_ > 0) 0 0 0
171 0 ▶ if ( $rs->[0][1] eq 'visit' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($rs->[0][1] eq 'visit') { } 0 0 0
172 0 push @{ $rs_entity{ $rs->[$_][0] } }, $rs->[$_][1];
173 }
174 else {
175 0 push @{ $rs_entity{ $rs->[$_][0] } }, ();
176 }
177 }
178 0 ▶ $csv->print( $fh, $rs->[$_] )
Branch: 1 branch
BranchTrueFalseTotal
$csv->print($fh, $rs->[$_]) or throw_cmd_run_exception("error", $csv->error_diag) 0 0 0
179 or throw_cmd_run_exception( error => $csv->error_diag );
180 }
181
182 0 $fh->close;
183 0 return \%rs_entity;
184 }
185  
186 0 ▶sub process_table {
Subroutine process_table: not called CC=16 SCAR=56.1
Pod documented
187 0 my ( $self, $table, $ts, $dir, $rs_entity ) = @_;
188 0 my ( $ds, $csv ) = @{ $self->cache->get('cache') }{qw/datasource csv/};
189 0 my $table_info = $ds->table_info;
190 0 my $var_info = $ds->variable_info;
191 0 my ( @vars, %data );
192
193 0 for ( keys %$var_info ) {
194 0 ▶ if ( $var_info->{$_}{table} eq $table ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($var_info->{$_}{"table"} eq $table) 0 0 0
195 0 push @vars, $var_info->{$_}{variable};
196 }
197 }
198
199 0 for (@$ts) {
200 0 ▶ if ( $table_info->{$table}{__type__} eq 'static' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($table_info->{$table}{'__type__'} eq 'static') { } 0 0 0
201 0 $data{ $_->[0] }{ $_->[1] } = $_->[2];
202 }
203 else {
204 0 $data{ $_->[0] }{ $_->[3] }{ $_->[1] } = $_->[2];
205 }
206 }
207  
208 # Add visit column to the header if the table is dynamic
209 0 my $file = File::Spec->catfile( $dir, 'data.csv' );
210 0 ▶ my $untaint = $1 if ( $file =~ /^(.+)$/ );
Branch: 1 branch
BranchTrueFalseTotal
if $file =~ /^(.+)$/ 0 0 0
211 0 ▶ my $fh = FileHandle->new("> $untaint")
Branch: 1 branch
BranchTrueFalseTotal
my $fh = "FileHandle"->new("> $untaint") or throw_cmd_run_exception("error", "Failed to open file: $!") 0 0 0
212 or throw_cmd_run_exception( error => "Failed to open file: $!" );
213
214 my @cols =
215 0 ▶ $table_info->{$table}{__type__} eq 'static'
Branch: 1 branch
BranchTrueFalseTotal
$table_info->{$table}{'__type__'} eq 'static' ? : 0 0 0
216 ? ( qw/entity_id/, @vars )
217 : ( qw/entity_id visit/, @vars );
218
219 0 ▶ $csv->print( $fh, \@cols )
Branch: 1 branch
BranchTrueFalseTotal
$csv->print($fh, \@cols) or throw_cmd_run_exception("error", $csv->error_diag) 0 0 0
220 or throw_cmd_run_exception( error => $csv->error_diag );
221
222 my @sorted_entity =
223 DBI::looks_like_number( ( keys %$rs_entity )[-1] )
224 0 ? sort { $a <=> $b } keys %$rs_entity
225 0 ▶ : sort { $a cmp $b } keys %$rs_entity;
Branch: 1 branch
BranchTrueFalseTotal
DBI::looks_like_number((keys %$rs_entity)[-1]) ? : 0 0 0
226  
227 # Write data for entities present in the result set
228 0 for my $entity (@sorted_entity) {
229 0 ▶ if ( $table_info->{$table}{__type__} eq 'static' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($table_info->{$table}{'__type__'} eq 'static') { } 0 0 0
230 0 my @vals = ( $entity, map { $data{$entity}{$_} } @vars );
231 0 ▶ $csv->print( $fh, \@vals )
Branch: 1 branch
BranchTrueFalseTotal
$csv->print($fh, \@vals) or throw_cmd_run_exception("error", $csv->error_diag) 0 0 0
232 or throw_cmd_run_exception( error => $csv->error_diag );
233 }
234 else {
235 0 ▶ for my $visit (
Branch: 1 branch
BranchTrueFalseTotal
@{$rs_entity->{$entity};} ? : 0 0 0
236 0 @{ $rs_entity->{$entity} }
237 0 ? @{ $rs_entity->{$entity} }
238 0 : keys %{ $data{$entity} }
239 )
240 {
241 0 my @vals = ( $entity, $visit, map { $data{$entity}{$visit}{$_} } @vars );
242 0 ▶ $csv->print( $fh, \@vals )
Branch: 1 branch
BranchTrueFalseTotal
$csv->print($fh, \@vals) or throw_cmd_run_exception("error", $csv->error_diag) 0 0 0
243 or throw_cmd_run_exception( error => $csv->error_diag );
244 }
245 }
246 }
247
248 0 $fh->close;
249 }
250  
251 0 ▶sub create_dataset {
Subroutine create_dataset: not called CC=6 SCAR=37.4
Pod documented
252 0 my ( $self, $rs ) = @_;
253  
254 # If the result set contains visit column group data
255 # by visit (i.e. dynamic tables/longitudinal datasources)
256 0 ▶ my $index = $rs->[0][1] eq 'visit' ? 1 : 0;
Branch: 1 branch
BranchTrueFalseTotal
$rs->[0][1] eq 'visit' ? : 0 0 0
257 0 my %data;
258
259 0 for my $r ( 1 .. $#$rs ) {
260 0 ▶ my $key = $index == 0 ? 1 : $rs->[$r][$index];
Branch: 1 branch
BranchTrueFalseTotal
$index == 0 ? : 0 0 0
261 0 for ( $index + 1 .. $#{ $rs->[0] } ) {
262 0 ▶ push @{ $data{$key}{ $rs->[0][$_] } }, $rs->[$r][$_] || ();
Condition0%0/3
$rs->[$r][$_] || ()
l!l&&r!l&&!r
000
MC/DC0%0/2
$rs->[$r][$_] || ()
$rs->[$r][$_] ()
Truth table0%0/3
A || B
A= $rs->[$r][$_]B= ()
ABresult
1X1
011
000
263 }
264 }
265  
266 0 return ( \%data, $index, splice @{ $rs->[0] }, 1 );
267 }
268  
269 #-------
270 1 1;
271 __END__
« CohortExplorer/Application.pm ↑ Summary CohortExplorer/Command/Query.pm »