lib/Convert/Pheno/DB/SQLite.pm

filter: Statementstmt 99.5% 413 / 415 Branchbran 86.0% 129 / 150 Conditioncond 69.2% 63 / 91 MC/DCmcdc 35.2% 18 / 51 Subroutinesub 100% 43 / 43 Podpod 0.0% 0 / 15 totaltotal 87.0% 666 / 765 CC 120 SCAR 21.8
CC
120
Cov
92%
get_ontology_terms
21.9
execute_query_SQLite
20.0
validate_ohdsi_schema
7.1
CRAP 8.8
« lib/Convert/Pheno/BFF/DerivedEntities.pm ↑ Summary lib/Convert/Pheno/DB/Similarity.pm »
1 package Convert::Pheno::DB::SQLite;
2  
3 143 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 143 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 143 ▶use autodie;
Subroutine BEGIN: called CC=1 SCAR=0.0
6 143 ▶use feature qw(say);
Subroutine BEGIN: called CC=1 SCAR=0.0
7 143 ▶use DBI;
Subroutine BEGIN: called CC=1 SCAR=0.0
8 143 ▶use File::Spec::Functions qw(catfile);
Subroutine BEGIN: called CC=1 SCAR=0.0
9 143 ▶use Time::HiRes qw(time);
Subroutine BEGIN: called CC=1 SCAR=0.0
10 143 ▶use Data::Dumper;
Subroutine BEGIN: called CC=1 SCAR=0.0
11 143 ▶use Text::Similarity::Overlaps;
Subroutine BEGIN: called CC=1 SCAR=0.0
12 143 ▶use Exporter 'import';
Subroutine BEGIN: called CC=1 SCAR=0.0
13 143 ▶use Convert::Pheno::DB::Bundle qw(bundled_database_path);
Subroutine BEGIN: called CC=1 SCAR=0.0
14 143 ▶use Convert::Pheno::DB::Similarity;
Subroutine BEGIN: called CC=1 SCAR=0.0
15 143 our @EXPORT =
16 qw( $VERSION open_connections_SQLite close_connections_SQLite get_ontology_terms);
17 143 my @matches = qw(exact_match full_text_search relaxed_full_text_search);
18  
19 143 ▶use constant DEVEL_MODE => 0;
Subroutine BEGIN: called CC=1 SCAR=0.0
20 143 ▶use constant RELAXED_FTS_CANDIDATE_LIMIT => 200;
Subroutine BEGIN: called CC=1 SCAR=0.0
21 143 ▶use constant RELAXED_FTS_MAX_TOKENS => 12;
Subroutine BEGIN: called CC=1 SCAR=0.0
22  
23 143 my %COLUMN_MATCH_CONFIG = (
24 label => { exact_collate_nocase => 1 },
25 id => { exact_collate_nocase => 1 },
26 concept_id => { exact_collate_nocase => 0 },
27 vocabulary_id => { exact_collate_nocase => 1 },
28 );
29  
30 3682 ▶sub _ontology_prefix {
Subroutine _ontology_prefix: called CC=3 SCAR=11.0
31 3682 my ($ontology) = @_;
32 3682 ▶ return 'HP' if $ontology eq 'hpo';
Branch: 1 branch
BranchTrueFalseTotal
if $ontology eq "hpo" 72 3610 3682
33 3610 ▶ return 'NCIT' if $ontology eq 'cdisc';
Branch: 1 branch
BranchTrueFalseTotal
if $ontology eq "cdisc" 0 3610 3610
34 3610 return uc($ontology);
35 }
36  
37 7889 ▶sub _db_profile_enabled {
Subroutine _db_profile_enabled: called CC=3 SCAR=11.0
38 7889 my ($self) = @_;
39 7889 ▶ return $self && defined $self->{debug} && $self->{debug} >= 2;
Condition100%6/6
$self && defined $self->{'debug'} && $self->{'debug'} >= 2
!ll&&!rl&&r
787928
$self and defined $self->{"debug"}
!ll&&!rl&&r
64781510
MC/DC100%3/3
$self && defined $self->{'debug'} && $self->{'debug'} >= 2
$self defined $self->{"debug"} $self->{'debug'} >= 2
Truth table100%4/4
A and B && C
A= $selfB= defined $self->{"debug"}C= $self->{'debug'} >= 2
ABCresult
0XX0
10X0
1100
1111
40 }
41  
42 3687 ▶sub _db_profile_path {
Subroutine _db_profile_path: called CC=6 SCAR=18.0
43 3687 my ( $self, @keys ) = @_;
44 3687 ▶ return unless _db_profile_enabled($self);
Branch: 1 branch
BranchTrueFalseTotal
unless _db_profile_enabled($self) 3683 4 3687
45 4 ▶ return unless @keys;
Branch: 1 branch
BranchTrueFalseTotal
unless @keys 0 4 4
46  
47 4 ▶ my $node = $self->{db_profile} ||= {};
Condition50%1/2
$self->{"db_profile"} ||= {}
l!l
40
MC/DC0%0/1
$self->{"db_profile"} ||= {}
$self->{"db_profile"}
Truth table50%1/2
A
A= $self->{"db_profile"}
Aresult
11
00
48 4 for my $key ( @keys[ 0 .. $#keys - 1 ] ) {
49 6 ▶ $node->{$key} ||= {};
Condition100%2/2
$node->{$key} ||= {}
l!l
33
MC/DC100%1/1
$node->{$key} ||= {}
$node->{$key}
Truth table100%2/2
A
A= $node->{$key}
Aresult
11
00
50 6 $node = $node->{$key};
51 }
52  
53 4 return ( $node, $keys[-1] );
54 }
55  
56 3673 ▶sub _db_profile_inc {
Subroutine _db_profile_inc: called CC=3 SCAR=11.0
57 3673 my ( $self, @keys ) = @_;
58 3673 ▶ my ( $node, $leaf ) = _db_profile_path( $self, @keys ) or return 1;
Branch: 1 branch
BranchTrueFalseTotal
() = _db_profile_path($self, @keys) or return 1 2 3671 3673
59 2 ▶ $node->{$leaf} = ( $node->{$leaf} // 0 ) + 1;
Condition50%1/2
$node->{$leaf} // 0
l!l
02
MC/DC0%0/1
$node->{$leaf} // 0
$node->{$leaf}
Truth table50%1/2
A
A= $node->{$leaf}
Aresult
11
00
60 2 return 1;
61 }
62  
63 20 ▶sub _db_profile_add {
Subroutine _db_profile_add: called CC=4 SCAR=13.9
64 20 my ( $self, $value, @keys ) = @_;
65 20 ▶ return 1 unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 6 14 20
66 14 ▶ my ( $node, $leaf ) = _db_profile_path( $self, @keys ) or return 1;
Branch: 1 branch
BranchTrueFalseTotal
() = _db_profile_path($self, @keys) or return 1 2 12 14
67 2 ▶ $node->{$leaf} = ( $node->{$leaf} // 0 ) + $value;
Condition50%1/2
$node->{$leaf} // 0
l!l
02
MC/DC0%0/1
$node->{$leaf} // 0
$node->{$leaf}
Truth table50%1/2
A
A= $node->{$leaf}
Aresult
11
00
68 2 return 1;
69 }
70  
71 20 ▶sub _db_profile_get {
Subroutine _db_profile_get: called CC=5 SCAR=16.3
72 20 my ( $hashref, @keys ) = @_;
73 20 my $node = $hashref;
74 20 for my $key (@keys) {
75 47 ▶ return 0 unless ref $node eq 'HASH' && exists $node->{$key};
Branch: 1 branch
BranchTrueFalseTotal
unless ref $node eq "HASH" and exists $node->{$key} 9 38 47
Condition66%2/3
ref $node eq "HASH" and exists $node->{$key}
!ll&&!rl&&r
0938
MC/DC50%1/2
ref $node eq "HASH" and exists $node->{$key}
ref $node eq "HASH" exists $node->{$key}
Truth table66%2/3
A and B
A= ref $node eq "HASH"B= exists $node->{$key}
ABresult
0X0
100
111
76 38 $node = $node->{$key};
77 }
78 11 ▶ return $node // 0;
Condition50%1/2
$node // 0
l!l
110
MC/DC0%0/1
$node // 0
$node
Truth table50%1/2
A
A= $node
Aresult
11
00
79 }
80  
81 152 ▶sub _db_profile_reset {
Subroutine _db_profile_reset: called CC=2 SCAR=6.9
82 152 my ($self) = @_;
83 152 ▶ return 1 unless _db_profile_enabled($self);
Branch: 1 branch
BranchTrueFalseTotal
unless _db_profile_enabled($self) 151 1 152
84 1 $self->{db_profile} = { started_at => time };
85 1 return 1;
86 }
87  
88 152 ▶sub _emit_db_profile_summary {
Subroutine _emit_db_profile_summary: called CC=6 SCAR=18.2
89 152 my ($self) = @_;
90 152 ▶ return 1 unless _db_profile_enabled($self);
Branch: 1 branch
BranchTrueFalseTotal
unless _db_profile_enabled($self) 151 1 152
91  
92 1 my $profile = delete $self->{db_profile};
93 1 ▶ return 1 unless $profile;
Branch: 1 branch
BranchTrueFalseTotal
unless $profile 0 1 1
94  
95 1 ▶ my $elapsed = time - ( $profile->{started_at} || time );
Condition33%1/3
$profile->{'started_at'} || &Convert::Pheno::DB::SQLite::time()
l!l&&r!l&&!r
100
MC/DC0%0/2
$profile->{'started_at'} || &Convert::Pheno::DB::SQLite::time()
$profile->{'started_at'} &Convert::Pheno::DB::SQLite::time()
Truth table33%1/3
A || B
A= $profile->{'started_at'}B= &Convert::Pheno::DB::SQLite::time()
ABresult
1X1
011
000
96 1 my @lines = ('DB lookup profile:');
97  
98 1 push @lines,
99 sprintf(
100 ' mapping requests=%d cache_hits=%d db_lookups=%d elapsed=%.3fs',
101 _db_profile_get( $profile, 'mapping', 'requests' ),
102 _db_profile_get( $profile, 'mapping', 'cache_hits' ),
103 _db_profile_get( $profile, 'mapping', 'db_lookups' ),
104 $elapsed,
105 );
106  
107 1 push @lines,
108 sprintf(
109 ' final resolution exact=%d similarity=%d fallback_na=%d',
110 _db_profile_get( $profile, 'final_resolution', 'exact' ),
111 _db_profile_get( $profile, 'final_resolution', 'similarity' ),
112 _db_profile_get( $profile, 'final_resolution', 'fallback_na' ),
113 );
114  
115 1 push @lines,
116 sprintf(
117 ' sql exact_match=%d full_text_search=%d relaxed_full_text_search=%d rows_fetched=%d candidate_rows=%d shortlisted=%d failures=%d time=%.3fs',
118 _db_profile_get( $profile, 'sql', 'match_type', 'exact_match', 'executions' ),
119 _db_profile_get( $profile, 'sql', 'match_type', 'full_text_search', 'executions' ),
120 _db_profile_get( $profile, 'sql', 'match_type', 'relaxed_full_text_search', 'executions' ),
121 _db_profile_get( $profile, 'sql', 'rows_fetched' ),
122 _db_profile_get( $profile, 'sql', 'candidate_rows' ),
123 _db_profile_get( $profile, 'sql', 'shortlisted_candidates' ),
124 _db_profile_get( $profile, 'sql', 'failures' ),
125 _db_profile_get( $profile, 'sql', 'total_time' ),
126 );
127  
128 1 ▶ my $ontologies = $profile->{ontology} || {};
Condition50%1/2
$profile->{'ontology'} || {}
l!l
10
MC/DC0%0/1
$profile->{'ontology'} || {}
$profile->{'ontology'}
Truth table50%1/2
A
A= $profile->{'ontology'}
Aresult
11
00
129 1 for my $ontology ( sort keys %{$ontologies} ) {
130 1 push @lines,
131 sprintf(
132 ' ontology[%s] requests=%d cache_hits=%d db_lookups=%d exact=%d similarity=%d fallback_na=%d',
133 $ontology,
134 _db_profile_get( $ontologies, $ontology, 'requests' ),
135 _db_profile_get( $ontologies, $ontology, 'cache_hits' ),
136 _db_profile_get( $ontologies, $ontology, 'db_lookups' ),
137 _db_profile_get( $ontologies, $ontology, 'final_resolution', 'exact' ),
138 _db_profile_get( $ontologies, $ontology, 'final_resolution', 'similarity' ),
139 _db_profile_get( $ontologies, $ontology, 'final_resolution', 'fallback_na' ),
140 );
141 }
142  
143 1 print STDERR join( "\n", @lines ), "\n";
144 1 return 1;
145 }
146  
147 ########################
148 ########################
149 # SUBROUTINES FOR DB #
150 ########################
151 ########################
152  
153 152 ▶sub open_connections_SQLite {
Subroutine open_connections_SQLite: called CC=5 SCAR=16.1
Pod undocumented
154 152 my $self = shift;
155  
156 # **********************
157 # *** IMPORTANT STEP ***
158 # **********************
159 # Initialize database connections for improved performance.
160 # This section opens connections to all relevant SQLite databases at once.
161 # Doing this at the beginning, rather than on each call, has been found to
162 # improve speed by approximately 15%. The approach enhances efficiency by
163 # reducing the overhead of repeatedly opening and closing connections.
164  
165 # Exception Handling:
166 # The 'ohdsi' database is treated as an exception due to its larger size.
167 # Opening the 'ohdsi' database can impact performance timings, so it's only
168 # opened if explicitly required (indicated by $self->{ohdsi_db}).
169 152 _db_profile_reset($self);
170  
171 152 my @databases = @{ $self->{databases} };
172  
173 # Open databases
174 152 my $dbh = {};
175 152 my $ok = eval {
176 152 for my $database (@databases) {
177 $dbh->{$database} =
178 767 open_db_SQLite( $database, $self->{path_to_ohdsi_db} );
179 }
180  
181 # Add $dbh HANDLE to $self
182 151 $self->{dbh} = $dbh; # Dynamically adding attributes (setter)
183  
184 # Prepare the query once
185 151 prepare_query_SQLite($self);
186 151 1;
187 };
188  
189 152 ▶ unless ($ok) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($ok) 1 151 152
190 1 my $error = $@;
191 1 delete $self->{sth};
192 1 for my $database ( reverse @databases ) {
193 2 ▶ next unless exists $dbh->{$database};
Branch: 1 branch
BranchTrueFalseTotal
unless exists $dbh->{$database} 1 1 2
194 1 eval { close_db_SQLite( $dbh->{$database} ) };
195 }
196 1 delete $self->{dbh};
197 1 die $error;
198 }
199  
200 151 return 1;
201 }
202  
203 152 ▶sub close_connections_SQLite {
Subroutine close_connections_SQLite: called CC=7 SCAR=19.5
Pod undocumented
204 152 my $self = shift;
205 152 ▶ my $dbh = $self->{dbh} || {};
Condition50%1/2
$self->{'dbh'} || {}
l!l
1520
MC/DC0%0/1
$self->{'dbh'} || {}
$self->{'dbh'}
Truth table50%1/2
A
A= $self->{'dbh'}
Aresult
11
00
206 152 my @databases = @{ $self->{databases} };
207 152 my @errors;
208  
209 152 delete $self->{sth};
210 152 for my $database (@databases) {
211 767 ▶ next unless exists $dbh->{$database};
Branch: 1 branch
BranchTrueFalseTotal
unless exists $dbh->{$database} 0 767 767
212 767 my $ok = eval {
213 767 close_db_SQLite( $dbh->{$database} );
214 766 1;
215 };
216 767 ▶ push @errors, $@ unless $ok;
Branch: 1 branch
BranchTrueFalseTotal
unless $ok 1 766 767
217 }
218 152 delete $self->{dbh};
219  
220 152 my $profile_ok = eval {
221 152 _emit_db_profile_summary($self);
222 152 1;
223 };
224 152 ▶ push @errors, $@ unless $profile_ok;
Branch: 1 branch
BranchTrueFalseTotal
unless $profile_ok 0 152 152
225  
226 152 ▶ die join q{}, @errors if @errors;
Branch: 1 branch
BranchTrueFalseTotal
if @errors 1 151 152
227 151 return 1;
228 }
229  
230 769 ▶sub open_db_SQLite {
Subroutine open_db_SQLite: called CC=6 SCAR=18.0
Pod undocumented
231 769 my ( $ontology, $path_to_ohdsi_db ) = @_;
232  
233 # Construct database file path
234 769 my $dbfile = get_database_file_path( $ontology, $path_to_ohdsi_db );
235 768 ▶ die "Database file not found: $dbfile" unless -f $dbfile;
Branch: 1 branch
BranchTrueFalseTotal
unless -f $dbfile 0 768 768
236  
237 # Database connection settings
238 768 my $dsn = "dbi:SQLite:dbname=$dbfile";
239 768 my %db_options = (
240 PrintError => 0,
241 RaiseError => 1,
242 ReadOnly => 1,
243 AutoCommit => 1,
244 FetchHashKeyName => 'NAME_lc',
245 );
246  
247 # Connect to the database
248 768 ▶ my $db_handle = DBI->connect( $dsn, '', '', \%db_options )
Branch: 1 branch
BranchTrueFalseTotal
my $db_handle = "DBI"->connect($dsn, "", "", \%db_options) or die "Failed to connect to database: $DBI::errstr" 768 0 768
249 or die "Failed to connect to database: $DBI::errstr";
250  
251 # Optimizations for SQLite
252 768 $db_handle->do("PRAGMA synchronous = OFF");
253 768 $db_handle->do("PRAGMA cache_size = 800000");
254  
255 768 ▶ if ( defined $ontology && $ontology eq 'ohdsi' ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $ontology and $ontology eq "ohdsi") 40 728 768
Condition66%2/3
defined $ontology and $ontology eq "ohdsi"
!ll&&!rl&&r
072840
MC/DC50%1/2
defined $ontology and $ontology eq "ohdsi"
defined $ontology $ontology eq "ohdsi"
Truth table66%2/3
A and B
A= defined $ontologyB= $ontology eq "ohdsi"
ABresult
0X0
100
111
256 40 my $ok = eval {
257 40 validate_ohdsi_schema( $db_handle, $dbfile );
258 39 1;
259 };
260 40 ▶ unless ($ok) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($ok) 1 39 40
261 1 my $error = $@;
262 1 eval { $db_handle->disconnect() };
263 1 die $error;
264 }
265 }
266  
267 767 return $db_handle;
268 }
269  
270 40 ▶sub validate_ohdsi_schema {
Subroutine validate_ohdsi_schema: called CC=7 SCAR=19.6
Pod undocumented
271 40 my ( $dbh, $dbfile ) = @_;
272 40 my @required_concept_columns = qw(
273 label id concept_id vocabulary_id domain_id concept_class_id
274 standard_concept valid_start_date valid_end_date invalid_reason
275 );
276 40 my @required_mapping_columns = qw(
277 source_concept_id target_concept_id relationship_id
278 valid_start_date valid_end_date invalid_reason
279 );
280 40 my @required_fts_columns = qw(label id concept_id vocabulary_id);
281  
282 394 my %concept_column = map { $_->[1] => 1 }
283 40 @{ $dbh->selectall_arrayref('PRAGMA table_info(OHDSI_table)') };
284 my @missing_concept_columns =
285 40 grep { !$concept_column{$_} } @required_concept_columns;
286  
287 234 my %mapping_column = map { $_->[1] => 1 }
288 40 @{ $dbh->selectall_arrayref('PRAGMA table_info(OHDSI_maps_to)') };
289 my @missing_mapping_columns =
290 40 grep { !$mapping_column{$_} } @required_mapping_columns;
291  
292 160 my %fts_column = map { $_->[1] => 1 }
293 40 @{ $dbh->selectall_arrayref('PRAGMA table_info(OHDSI_fts)') };
294 40 my @missing_fts_columns = grep { !$fts_column{$_} } @required_fts_columns;
295  
296 40 ▶ return 1
Branch: 1 branch
BranchTrueFalseTotal
unless @missing_concept_columns or @missing_mapping_columns or @missing_fts_columns 39 1 40
Condition66%4/6
@missing_concept_columns or @missing_mapping_columns or @missing_fts_columns
l!l&&r!l&&!r
1039
@missing_concept_columns or @missing_mapping_columns
l!l&&r!l&&!r
1039
MC/DC33%1/3
@missing_concept_columns or @missing_mapping_columns or @missing_fts_columns
@missing_concept_columns @missing_mapping_columns @missing_fts_columns
Truth table50%2/4
A or B or C
A= @missing_concept_columnsB= @missing_mapping_columnsC= @missing_fts_columns
ABCresult
1XX1
01X1
0011
0000
297 unless @missing_concept_columns
298 || @missing_mapping_columns
299 || @missing_fts_columns;
300  
301 1 my @details;
302 1 ▶ push @details,
Branch: 1 branch
BranchTrueFalseTotal
if @missing_concept_columns 1 0 1
303 'OHDSI_table columns: ' . join( ', ', @missing_concept_columns )
304 if @missing_concept_columns;
305 1 ▶ push @details,
Branch: 1 branch
BranchTrueFalseTotal
if @missing_mapping_columns 1 0 1
306 'OHDSI_maps_to columns: ' . join( ', ', @missing_mapping_columns )
307 if @missing_mapping_columns;
308 1 ▶ push @details, 'OHDSI_fts columns: ' . join( ', ', @missing_fts_columns )
Branch: 1 branch
BranchTrueFalseTotal
if @missing_fts_columns 0 1 1
309 if @missing_fts_columns;
310  
311 1 die "Athena-OHDSI database <$dbfile> uses the pre-bundle schema "
312 . '(' . join( '; ', @details ) . '). '
313 . "Install the current ohdsi.db.\n";
314 }
315  
316 772 ▶sub get_database_file_path {
Subroutine get_database_file_path: called CC=6 SCAR=18.3
Pod undocumented
317 772 my ( $ontology, $path_to_ohdsi_db ) = @_;
318 772 ▶ my $filename = defined $ontology ? "$ontology.db" : '.db';
Branch: 1 branch
BranchTrueFalseTotal
defined $ontology ? : 772 0 772
319 772 ▶ return catfile( $path_to_ohdsi_db, $filename )
Branch: 1 branch
BranchTrueFalseTotal
if defined $ontology and $ontology eq "ohdsi" and defined $path_to_ohdsi_db 41 731 772
Condition66%4/6
defined $ontology and $ontology eq "ohdsi" and defined $path_to_ohdsi_db
!ll&&!rl&&r
731041
defined $ontology and $ontology eq "ohdsi"
!ll&&!rl&&r
073141
MC/DC33%1/3
defined $ontology and $ontology eq "ohdsi" and defined $path_to_ohdsi_db
defined $ontology $ontology eq "ohdsi" defined $path_to_ohdsi_db
Truth table50%2/4
A and B and C
A= defined $ontologyB= $ontology eq "ohdsi"C= defined $path_to_ohdsi_db
ABCresult
0XX0
10X0
1100
1111
320 if defined $ontology
321 && $ontology eq 'ohdsi'
322 && defined $path_to_ohdsi_db;
323  
324 731 ▶ return bundled_database_path(
Condition100%2/2
$Convert::Pheno::share_dir // ""
l!l
7301
MC/DC100%1/1
$Convert::Pheno::share_dir // ""
$Convert::Pheno::share_dir
Truth table100%2/2
A
A= $Convert::Pheno::share_dir
Aresult
11
00
325 $Convert::Pheno::share_dir // q{},
326 $ontology
327 );
328 }
329  
330 767 ▶sub close_db_SQLite {
Subroutine close_db_SQLite: called CC=1 SCAR=0.0
Pod undocumented
331 767 my $dbh = shift;
332 767 $dbh->disconnect();
333 767 return 1;
334 }
335  
336 151 ▶sub prepare_query_SQLite {
Subroutine prepare_query_SQLite: called CC=6 SCAR=17.9
Pod undocumented
337 151 my $self = shift;
338 151 my @databases = @{ $self->{databases} };
339  
340 ###############
341 # EXPLANATION #
342 ###############
343 #
344 # Optimization Decision: The "prepare step" is executed once at the start, rather than with each query.
345 # Although the speed gain is modest (~15%), this approach reduces repetitive processing.
346 # Flexibility in Querying: We've enabled searching in columns other than 'label'. This necessitates the creation
347 # of corresponding $sth (Statement Handle) for each case. As a result, we have structured nested statement handles
348 # like sth->{ncit}{label}, sth->{icd10}{label}, sth->{ohdsi}{concept_id}, and sth->{ohdsi}{label}.
349 # Extensibility for Match Types: The structure also incorporates a "match" type, paving the way for various matching
350 # methods in future enhancements.
351 # NB: While it's feasible to alter the "prepare" dynamically during queries, it's crucial to reset it to the default
352 # post-use. For faster performance, using smaller databases (e.g., ncit/icd10) is advisable.
353  
354 # NB:
355 # dbh = "Database Handle"
356 # sth = "Statement Handle"
357  
358 151 for my $match (@matches) {
359 453 for my $ontology (@databases) {
360 2295 ▶ my @columns = $match eq 'exact_match'
Branch: 2 branches
BranchTrueFalseTotal
$match eq 'exact_match' ? : 765 1530 2295
$ontology eq 'ohdsi' ? : 38 727 765
361 ? ( 'label', 'id', $ontology eq 'ohdsi' ? ('concept_id') : () )
362 : ('label');
363 2295 for my $column (@columns) {
364  
365 ##############################
366 # Start building the queries #
367 ##############################
368  
369 # Call build_query to construct the SQL query
370 3098 my $dbh = $self->{dbh}{$ontology};
371 3098 my $query = build_query( $ontology, $column, $match );
372  
373 # Prepare the query
374 3098 my $sth = $dbh->prepare($query);
375  
376 # Autovivification of $self->{sth}{$ontology}{$column}{$match}
377 3098 $self->{sth}{$ontology}{$column}{$match} =
378 $sth; # Dynamically adding nested attributes (setter)
379 }
380 }
381 }
382  
383 #print Dumper $self and die;
384 151 return 1;
385 }
386  
387 3103 ▶sub build_query {
Subroutine build_query: called CC=3 SCAR=11.0
Pod undocumented
388 3103 my ( $ontology, $column, $match ) = @_;
389 die "Unsupported SQLite lookup column <$column>\n"
390 3103 ▶ unless exists $COLUMN_MATCH_CONFIG{$column};
Branch: 1 branch
BranchTrueFalseTotal
unless exists $COLUMN_MATCH_CONFIG{$column} 0 3103 3103
391 3103 my $db = uc($ontology) . '_table';
392 3103 my $db_fts = uc($ontology) . '_fts';
393 my $exact_predicate =
394 $COLUMN_MATCH_CONFIG{$column}{exact_collate_nocase}
395 3103 ▶ ? qq($column = ? COLLATE NOCASE)
Branch: 1 branch
BranchTrueFalseTotal
$COLUMN_MATCH_CONFIG{$column}{'exact_collate_nocase'} ? : 3063 40 3103
396 : qq($column = ?);
397  
398 3103 my %match_type = (
399  
400 # Contains queries
401 #contains => qq(SELECT * FROM $db WHERE $column LIKE '%' || ? || '%' COLLATE NOCASE),
402  
403 # Exact search queries
404 # What out for leading spaces!!!
405 # SELECT * FROM HPO_table WHERE TRIM(label) = ? COLLATE NOCASE
406 exact_match => qq(SELECT * FROM $db WHERE $exact_predicate),
407  
408 # **********************
409 # *** IMPORTANT STEP ***
410 # **********************
411  
412 # Strict FTS supplies candidates containing every query token. Fuzzy
413 # search may retry with an all-but-one-token expression when this set
414 # is empty; BM25 and a hard limit keep that broader pass bounded.
415 full_text_search => qq(SELECT * FROM $db_fts WHERE $column MATCH ?),
416 relaxed_full_text_search =>
417 qq(SELECT * FROM $db_fts WHERE $column MATCH ? ORDER BY bm25($db_fts) LIMIT )
418 . RELAXED_FTS_CANDIDATE_LIMIT,
419  
420 # SOUNDEX using TABLE_fts but only on column <label>
421 # soundex => qq(SELECT * FROM $db_fts WHERE SOUNDEX($column) = SOUNDEX(?)) # NOT USED
422  
423 );
424 3103 return $match_type{$match};
425 }
426  
427 # MATCH parameters are still parsed as FTS syntax even when bound safely.
428 # Convert source labels into quoted literal words so operators such as OR,
429 # NEAR, quotes, and parentheses cannot change candidate retrieval.
430 15 ▶sub _fts_tokens {
Subroutine _fts_tokens: called CC=1 SCAR=0.0
431 15 my ($query) = @_;
432 15 $query = prune_problematic_chars( $query, 'full_text_search' );
433  
434 15 my %seen;
435 15 return grep { !$seen{lc $_}++ } ( $query =~ /([\p{L}\p{N}]+)/gu );
436 }
437  
438 43 ▶sub _quote_fts_token {
Subroutine _quote_fts_token: called CC=1 SCAR=0.0
439 43 my ($token) = @_;
440 43 $token =~ s/"/""/g;
441 43 return qq{"$token"};
442 }
443  
444 9 ▶sub build_strict_fts_query {
Subroutine build_strict_fts_query: called CC=2 SCAR=6.9
Pod undocumented
445 9 my @tokens = _fts_tokens(@_);
446 9 ▶ return unless @tokens;
Branch: 1 branch
BranchTrueFalseTotal
unless @tokens 1 8 9
447 8 return join ' AND ', map { _quote_fts_token($_) } @tokens;
448 }
449  
450 6 ▶sub build_relaxed_fts_query {
Subroutine build_relaxed_fts_query: called CC=5 SCAR=16.1
Pod undocumented
451 6 my @tokens = _fts_tokens(@_);
452 6 ▶ return unless @tokens >= 2 && @tokens <= RELAXED_FTS_MAX_TOKENS;
Branch: 1 branch
BranchTrueFalseTotal
unless @tokens >= 2 and @tokens <= 12 1 5 6
Condition66%2/3
@tokens >= 2 and @tokens <= 12
!ll&&!rl&&r
105
MC/DC50%1/2
@tokens >= 2 and @tokens <= 12
@tokens >= 2 @tokens <= 12
Truth table66%2/3
A and B
A= @tokens >= 2B= @tokens <= 12
ABresult
0X0
100
111
453  
454 5 my @quoted = map { _quote_fts_token($_) } @tokens;
455  
456 5 ▶ return join ' OR ', map { qq{($_)} } @quoted if @quoted == 2;
Branch: 1 branch
BranchTrueFalseTotal
if @quoted == 2 1 4 5
457  
458 4 my @clauses;
459 4 for my $omitted ( 0 .. $#quoted ) {
460 15 my @required = @quoted;
461 15 splice @required, $omitted, 1;
462 15 push @clauses, '(' . join( ' AND ', @required ) . ')';
463 }
464 4 return join ' OR ', @clauses;
465 }
466  
467 1942 ▶sub get_ontology_terms {
Subroutine get_ontology_terms: called CC=21 SCAR=30.8
Pod undocumented
468  
469 ###############
470 # START QUERY #
471 ###############
472  
473 1942 my $arg = shift;
474 1942 my $ontology = $arg->{ontology};
475 1942 my $sth_column_ref = $arg->{sth_column_ref}; # Contains hashref
476 1942 my $query = $arg->{query};
477 1942 my $column = $arg->{column};
478 1942 my $databases = $arg->{databases};
479 1942 my $search = $arg->{search};
480 1942 my $text_similarity_method = $arg->{text_similarity_method};
481 1942 my $min_text_similarity_score = $arg->{min_text_similarity_score};
482 1942 my $levenshtein_weight = $arg->{levenshtein_weight};
483 1942 my $self = $arg->{self};
484 1942 say "QUERY <$query> ONTOLOGY <$ontology> COLUM <$column> SEARCH <$search>\n min_text_similarity_score <$min_text_similarity_score> levenshtein_weight<$levenshtein_weight>"
485 if DEVEL_MODE;
486  
487 # A) 'exact'
488 # - exact_match
489 # B) Mixed queries:
490 # 1 - exact_match
491 # if no results are found
492 # 2 - FTS
493 # for which we rank by similarity with Text:Similarity
494  
495 # Default values
496 1942 ▶ my %default_value = (
Branch: 1 branch
BranchTrueFalseTotal
$ontology eq 'ohdsi' ? : 3 1939 1942
497 id => _ontology_prefix($ontology) . ':NA0000',
498 label => $ontology eq 'ohdsi' ? 'No matching concept' : 'NA'
499 );
500 1942 ▶ $default_value{concept_id} = 0 if $ontology eq 'ohdsi';
Branch: 1 branch
BranchTrueFalseTotal
if $ontology eq "ohdsi" 3 1939 1942
501  
502 # exact_match (always performed)
503 my ( $id, $label, $concept_id, $search_resolution, $search_evidence ) =
504 execute_query_SQLite(
505 {
506 sth => $sth_column_ref->{exact_match}, # IMPORTANT STEP
507 1942 query => $query,
508 ontology => $ontology,
509 databases => $databases,
510 search => $search,
511 match_type => 'exact_match',
512 text_similarity_method => $text_similarity_method, # Not used here
513 min_text_similarity_score => $min_text_similarity_score,
514 levenshtein_weight => $levenshtein_weight,
515 self => $self,
516 }
517 );
518  
519 # mixed/fuzzy
520 1942 ▶ unless ( defined $id && defined $label ) {
Branch: 1 branch
BranchTrueFalseTotal
unless (defined $id and defined $label) 282 1660 1942
Condition66%2/3
defined $id and defined $label
!ll&&!rl&&r
28201660
MC/DC50%1/2
defined $id and defined $label
defined $id defined $label
Truth table66%2/3
A and B
A= defined $idB= defined $label
ABresult
0X0
100
111
521 282 ▶ if ( $search eq 'mixed' || $search eq 'fuzzy' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($search eq "mixed" or $search eq "fuzzy") 5 277 282
Condition100%3/3
$search eq "mixed" or $search eq "fuzzy"
l!l&&r!l&&!r
23277
MC/DC100%2/2
$search eq "mixed" or $search eq "fuzzy"
$search eq "mixed" $search eq "fuzzy"
Truth table100%3/3
A or B
A= $search eq "mixed"B= $search eq "fuzzy"
ABresult
1X1
011
000
522 5 print "EXECUTING SEARCH <$search> on QUERY <$query>\n"
523 if DEVEL_MODE;
524 5 my $strict_query = build_strict_fts_query($query);
525 5 ▶ if ( defined $strict_query ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $strict_query) 5 0 5
526 ( $id, $label, $concept_id, $search_resolution, $search_evidence ) =
527 execute_query_SQLite(
528 {
529 5 sth => $sth_column_ref->{'full_text_search'},
530 query => $strict_query,
531 scoring_query => $query,
532 query_is_fts_expression => 1,
533 ontology => $ontology,
534 databases => $databases,
535 match_type => 'full_text_search',
536 search => $search,
537 text_similarity_method => $text_similarity_method,
538 min_text_similarity_score => $min_text_similarity_score,
539 levenshtein_weight => $levenshtein_weight,
540 self => $self,
541 }
542 );
543 }
544  
545 # Strict FTS requires every token. If it produces no candidates,
546 # fuzzy search retries with a bounded all-but-one-token query. The
547 # original normalized text remains the input to similarity scoring.
548 5 ▶ if (
Branch: 1 branch
BranchTrueFalseTotal
if ($search eq "fuzzy" and not defined $id and $search_evidence and not $search_evidence->{"candidates_evaluated"} // 0) 2 3 5
Condition72%8/11
$search eq "fuzzy" and not defined $id and $search_evidence and not $search_evidence->{"candidates_evaluated"} // 0
!ll&&!rl&&r
302
$search eq "fuzzy" and not defined $id and $search_evidence
!ll&&!rl&&r
302
$search eq "fuzzy" and not defined $id
!ll&&!rl&&r
212
$search_evidence->{"candidates_evaluated"} // 0
l!l
20
MC/DC0%0/4
$search eq "fuzzy" and not defined $id and $search_evidence and not $search_evidence->{"candidates_evaluated"} // 0
$search eq "fuzzy" not defined $id $search_evidence $search_evidence->{"candidates_evaluated"}
Truth table60%3/5
A and not(B) and C and not(D)
A= $search eq "fuzzy"B= not defined $idC= $search_evidenceD= $search_evidence->{"candidates_evaluated"}
ABCDresult
0XXX0
10XX0
110X0
11110
11101
549 $search eq 'fuzzy'
550 && !defined $id
551 && $search_evidence
552 && !( $search_evidence->{candidates_evaluated} // 0 )
553 )
554 {
555 2 my $relaxed_query = build_relaxed_fts_query($query);
556 2 ▶ if ( defined $relaxed_query ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $relaxed_query) 2 0 2
557 ( $id, $label, $concept_id, $search_resolution, $search_evidence ) =
558 execute_query_SQLite(
559 {
560 2 sth => $sth_column_ref->{'relaxed_full_text_search'},
561 query => $relaxed_query,
562 scoring_query => $query,
563 query_is_fts_expression => 1,
564 ontology => $ontology,
565 databases => $databases,
566 match_type => 'relaxed_full_text_search',
567 search => $search,
568 text_similarity_method => $text_similarity_method,
569 min_text_similarity_score => $min_text_similarity_score,
570 levenshtein_weight => $levenshtein_weight,
571 self => $self,
572 }
573 );
574 }
575 }
576 }
577 }
578  
579 # Set defaults if undefined
580 1942 ▶ $id = $id // $default_value{id};
Condition66%2/3
$id // $default_value{"id"}
l!l&&r!l&&!r
16642780
MC/DC0%0/2
$id // $default_value{"id"}
$id $default_value{"id"}
Truth table66%2/3
A // B
A= $idB= $default_value{"id"}
ABresult
1X1
011
000
581 1942 ▶ $label = $label // $default_value{label};
Condition66%2/3
$label // $default_value{"label"}
l!l&&r!l&&!r
16642780
MC/DC0%0/2
$label // $default_value{"label"}
$label $default_value{"label"}
Truth table66%2/3
A // B
A= $labelB= $default_value{"label"}
ABresult
1X1
011
000
582 1942 ▶ if ( $ontology eq 'ohdsi' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($ontology eq "ohdsi") 3 1939 1942
583 3 ▶ $concept_id = $concept_id // $default_value{concept_id};
Condition66%2/3
$concept_id // $default_value{"concept_id"}
l!l&&r!l&&!r
210
MC/DC0%0/2
$concept_id // $default_value{"concept_id"}
$concept_id $default_value{"concept_id"}
Truth table66%2/3
A // B
A= $concept_idB= $default_value{"concept_id"}
ABresult
1X1
011
000
584 }
585 $search_resolution = defined $id && $id eq $default_value{id}
586 1942 ▶ ? 'fallback_na'
Branch: 1 branch
BranchTrueFalseTotal
defined $id && $id eq $default_value{'id'} ? : 278 1664 1942
Condition80%4/5
defined $id and $id eq $default_value{"id"}
!ll&&!rl&&r
01664278
$search_resolution // "fallback_na"
l!l
16631
MC/DC50%1/2
defined $id and $id eq $default_value{"id"}
defined $id $id eq $default_value{"id"}
MC/DC100%1/1
$search_resolution // "fallback_na"
$search_resolution
Truth table66%2/3
A and B
A= defined $idB= $id eq $default_value{"id"}
ABresult
0X0
100
111
Truth table100%2/2
A
A= $search_resolution
Aresult
11
00
587 : $search_resolution // 'fallback_na';
588  
589 #############
590 # END QUERY #
591 #############
592  
593 1942 return ( $id, $label, $concept_id, $search_resolution, $search_evidence );
594  
595 }
596  
597 1949 ▶sub execute_query_SQLite {
Subroutine execute_query_SQLite: called CC=20 SCAR=30.0
Pod undocumented
598 1949 my $arg = shift;
599 1949 my $sth = $arg->{sth};
600 1949 my $query = $arg->{query};
601 1949 my $text_similarity_method = $arg->{text_similarity_method};
602 1949 my $min_text_similarity_score = $arg->{min_text_similarity_score};
603 1949 my $ontology = $arg->{ontology};
604 1949 my $match_type = $arg->{match_type};
605 1949 my @databases = @{ $arg->{databases} };
606 1949 my $search = $arg->{search};
607 1949 my $levenshtein_weight = $arg->{levenshtein_weight};
608 1949 my $self = $arg->{self};
609 1949 ▶ my $started_at = _db_profile_enabled($self) ? time : undef;
Branch: 1 branch
BranchTrueFalseTotal
_db_profile_enabled($self) ? : 1 1948 1949
610 1949 ▶ my $scoring_query = $arg->{scoring_query} // $query;
Condition66%2/3
$arg->{"scoring_query"} // $query
l!l&&r!l&&!r
619430
MC/DC0%0/2
$arg->{"scoring_query"} // $query
$arg->{"scoring_query"} $query
Truth table66%2/3
A // B
A= $arg->{"scoring_query"}B= $query
ABresult
1X1
011
000
611  
612 # Initialize $id and $label to undefined
613 1949 my ( $id, $label, $concept_id, $search_resolution, $search_evidence ) =
614 ( undef, undef, undef, undef, undef );
615  
616 # Premature return if $query is empty
617 1949 ▶ return ( $id, $label, $concept_id, $search_resolution, $search_evidence )
Branch: 1 branch
BranchTrueFalseTotal
if $query eq "" 11 1938 1949
618 if $query eq '';
619  
620 # Preprocess query for execution
621 $query = prune_problematic_chars( $query, $match_type )
622 1938 ▶ unless $arg->{query_is_fts_expression};
Branch: 1 branch
BranchTrueFalseTotal
unless $arg->{"query_is_fts_expression"} 1932 6 1938
623 1938 $scoring_query = prune_problematic_chars( $scoring_query, 'full_text_search' );
624  
625 # Columns in DBs
626 # *<ncit.db>, <icd10.db> and <cdisc.db> were pre-processed to have "id" and "label" columns only
627 # label [0]
628 # id [1]
629 #
630 # * <ohdsi.db> consists of 4 columns:
631 # concept_name => label [0]
632 # concept_code => id [1]
633 # concept_id => concept_id [2]
634 # vocabulary_id => vocabulary_id [3]
635  
636 # Define a hash for column positions in databases
637 # In case we encounter in the future a situation where order of columns is different
638 my $position = {
639 1938 map { $_ => { label => 0, id => 1 } } @databases # Assuming @databases is defined elsewhere
640 };
641 1938 my $id_column = $position->{$ontology}{id};
642 1938 my $label_column = $position->{$ontology}{label};
643 1938 my $concept_id_column = 2;
644  
645 # Execute the query
646 1938 $sth->bind_param( 1, $query );
647 1938 _db_profile_inc( $self, 'sql', 'match_type', $match_type, 'executions' );
648  
649 1938 ▶ my $execute_started_at = _db_profile_enabled($self) ? time : undef;
Branch: 1 branch
BranchTrueFalseTotal
_db_profile_enabled($self) ? : 1 1937 1938
650 1938 eval { $sth->execute(); };
651 1938 ▶ if ($@) {
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 1 1937 1938
652 1 _db_profile_inc( $self, 'sql', 'failures' );
653 1 warn "Query execution failed: $@";
654 1 return ( $id, $label, $concept_id, $search_resolution, $search_evidence );
655 }
656 1937 ▶ _db_profile_add( $self, time - $execute_started_at, 'sql', 'execute_time' )
Branch: 1 branch
BranchTrueFalseTotal
if defined $execute_started_at 1 1936 1937
657 if defined $execute_started_at;
658  
659 # Process results depending on the type of match
660 1937 ▶ if ( $match_type eq 'exact_match' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($match_type eq 'exact_match') { } 1931 6 1937
661 1931 say "MATCH_TYPE: <exact_match>" if DEVEL_MODE;
662 1931 my $candidate_rows = 0;
663 1931 my ( $runner_up_id, $runner_up_label );
664 1931 ▶ while ( my $row = $sth->fetchrow_arrayref ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $sth->fetchrow_arrayref) 1734 1931 3665
665 1734 _db_profile_inc( $self, 'sql', 'rows_fetched' );
666 1734 $candidate_rows++;
667 1734 ▶ if ( !defined $id ) {
Branch: 2 branches
BranchTrueFalseTotal
if (not defined $id) { } 1661 73 1734
elsif (not defined $runner_up_id) { } 73 0 73
668 1661 $id = _candidate_id( $ontology, $row, $id_column );
669 1661 $label = $row->[$label_column];
670 1661 $concept_id = $row->[$concept_id_column];
671 1661 $search_resolution = 'exact';
672 }
673 elsif ( !defined $runner_up_id ) {
674 73 $runner_up_id = _candidate_id( $ontology, $row, $id_column );
675 73 $runner_up_label = $row->[$label_column];
676 }
677 }
678 $search_evidence = {
679 1931 ▶ candidate_strategy => 'exact_index',
Branch: 2 branches
BranchTrueFalseTotal
defined $id ? : 1661 270 1931
defined $runner_up_id ? : 73 1858 1931
680 candidates_evaluated => $candidate_rows,
681 eligible_candidates => $candidate_rows,
682 defined $id
683 ? (
684 best_candidate_label => $label,
685 best_candidate_id => $id,
686 best_candidate_score => 1,
687 )
688 : (),
689 defined $runner_up_id
690 ? (
691 runner_up_label => $runner_up_label,
692 runner_up_id => $runner_up_id,
693 runner_up_score => 1,
694 score_margin => 0,
695 )
696 : (),
697 };
698 }
699 else {
700 6 say "MATCH_TYPE: <full_text_search>" if DEVEL_MODE;
701  
702 6 ▶ if ( $search eq 'mixed' ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($search eq 'mixed') { } 1 5 6
703  
704 # For other match types, use text similarity
705 1 my $stats;
706 1 ( $id, $label, $concept_id, $stats ) = similarity_match(
707 {
708 sth => $sth,
709 query => $scoring_query,
710 ontology => $ontology,
711 id_column => $id_column,
712 label_column => $label_column,
713 text_similarity_method => $text_similarity_method,
714 min_text_similarity_score => $min_text_similarity_score,
715 levenshtein_weight => $levenshtein_weight,
716 concept_id_column => $concept_id_column,
717 self => $self,
718 }
719 );
720 1 _db_profile_add( $self, $stats->{candidate_rows}, 'sql', 'candidate_rows' );
721 1 _db_profile_add( $self, $stats->{shortlisted_candidates}, 'sql', 'shortlisted_candidates' );
722 1 _db_profile_add( $self, $stats->{evaluation_time}, 'sql', 'similarity_time' );
723 1 ▶ $search_resolution = defined $id ? 'similarity' : undef;
Branch: 1 branch
BranchTrueFalseTotal
defined $id ? : 1 0 1
724 1 ▶ $search_evidence = _search_evidence(
Branch: 1 branch
BranchTrueFalseTotal
$match_type eq 'relaxed_full_text_search' ? : 0 1 1
725 $stats,
726 $match_type eq 'relaxed_full_text_search'
727 ? 'relaxed_fts'
728 : 'strict_fts'
729 );
730 }
731 else {
732  
733 # Call a subroutine to compute composite similarity.
734 5 my $stats;
735 5 ( $id, $label, $concept_id, $stats ) = composite_similarity_match(
736 {
737 sth => $sth,
738 query => $scoring_query,
739 ontology => $ontology,
740 id_column => $id_column,
741 label_column => $label_column,
742 text_similarity_method => $text_similarity_method, # cosine or dice
743 min_text_similarity_score => $min_text_similarity_score,
744 levenshtein_weight => $levenshtein_weight,
745 concept_id_column => $concept_id_column,
746 self => $self,
747 allow_spelling_variant =>
748 $match_type eq 'relaxed_full_text_search',
749  
750 # Possibly additional parameters, e.g., weighting factors
751 }
752 );
753 5 _db_profile_add( $self, $stats->{candidate_rows}, 'sql', 'candidate_rows' );
754 5 _db_profile_add( $self, $stats->{shortlisted_candidates}, 'sql', 'shortlisted_candidates' );
755 5 _db_profile_add( $self, $stats->{evaluation_time}, 'sql', 'similarity_time' );
756 5 ▶ $search_resolution = defined $id ? 'similarity' : undef;
Branch: 1 branch
BranchTrueFalseTotal
defined $id ? : 2 3 5
757 5 ▶ $search_evidence = _search_evidence(
Branch: 1 branch
BranchTrueFalseTotal
$match_type eq 'relaxed_full_text_search' ? : 2 3 5
758 $stats,
759 $match_type eq 'relaxed_full_text_search'
760 ? 'relaxed_fts'
761 : 'strict_fts'
762 );
763 }
764 }
765  
766 # Finish the statement handle
767 1937 $sth->finish();
768 1937 ▶ _db_profile_add( $self, time - $started_at, 'sql', 'total_time' )
Branch: 1 branch
BranchTrueFalseTotal
if defined $started_at 1 1936 1937
769 if defined $started_at;
770  
771 # Return the results
772 1937 return ( $id, $label, $concept_id, $search_resolution, $search_evidence );
773 }
774  
775 1748 ▶sub _candidate_id {
Subroutine _candidate_id: called CC=2 SCAR=6.9
776 1748 my ( $ontology, $row, $id_column ) = @_;
777 1748 ▶ return $ontology ne 'ohdsi'
Branch: 1 branch
BranchTrueFalseTotal
$ontology ne 'ohdsi' ? : 1740 8 1748
778 ? _ontology_prefix($ontology) . ':' . $row->[$id_column]
779 : $row->[3] . ':' . $row->[$id_column];
780 }
781  
782 6 ▶sub _search_evidence {
Subroutine _search_evidence: called CC=1 SCAR=0.0
783 6 my ( $stats, $strategy ) = @_;
784 return {
785 candidate_strategy => $strategy,
786 candidates_evaluated => $stats->{candidate_rows},
787 eligible_candidates => $stats->{shortlisted_candidates},
788 34 map { $_ => $stats->{$_} }
789 6 grep { exists $stats->{$_} }
790 qw(best_candidate_label best_candidate_id best_candidate_score token_similarity base_token_similarity normalized_levenshtein spelling_variant spelling_query_token spelling_candidate_token spelling_token_similarity runner_up_label runner_up_id runner_up_score score_margin),
791 };
792 }
793  
794 3889 ▶sub prune_problematic_chars {
Subroutine prune_problematic_chars: called CC=2 SCAR=6.9
Pod undocumented
795 3889 my ( $query, $match_type ) = @_;
796  
797 # **********************
798 # *** IMPORTANT STEP ***
799 # **********************
800 # full_text_search is supposed to be ONLY in text fields, but, for
801 # whatever reason the binding of parameters e.g, '2 - mild'
802 # that start with number produce exceptions on SQLite.
803  
804 # Prune
805 # "OPCS(v4-0.0):Cannulation of lymphatic duct"
806 # to
807 # "Cannulation of lymphatic duct"
808 3889 $query =~ s/^[^:]*://;
809  
810 # Remove leading number-pattern for all searches
811 #say "BEFORE <$query>";
812 3889 $query =~ s/^\d+\s+-\s+//; # '2 - mild' => 'mild'
813 #say "A1 <$query>";
814 3889 $query =~ s/^\d+\s+\((.+)\)$/$1/; # '0 (none)' => 'none'
815 #say "A2 <$query>";
816  
817 # Replace certain characters with spaces for spefific searches
818 3889 ▶ $query =~ tr#_,-/# # if $match_type eq 'full_text_search';
Branch: 1 branch
BranchTrueFalseTotal
if $match_type eq "full_text_search" 1955 1934 3889
819  
820 # Collapse duplicated spaces for all searches
821 3889 $query =~ tr/ //s;
822 3889 return $query;
823 }
824  
825 11 ▶sub _select_ranked_candidate {
Subroutine _select_ranked_candidate: called CC=7 SCAR=19.5
826 11 my ( $results, $candidate_rows, $min_score, $started_at ) = @_;
827 11 @{$results} = sort {
828 $b->{match_score} <=> $a->{match_score}
829 || $a->{sequence} <=> $b->{sequence}
830 11 ▶ } @{$results};
Condition33%1/3
$Convert::Pheno::DB::SQLite::b->{'match_score'} <=> $Convert::Pheno::DB::SQLite::a->{'match_score'} || $Convert::Pheno::DB::SQLite::a->{'sequence'} <=> $Convert::Pheno::DB::SQLite::b->{'sequence'}
l!l&&r!l&&!r
800
MC/DC0%0/2
$Convert::Pheno::DB::SQLite::b->{'match_score'} <=> $Convert::Pheno::DB::SQLite::a->{'match_score'} || $Convert::Pheno::DB::SQLite::a->{'sequence'} <=> $Convert::Pheno::DB::SQLite::b->{'sequence'}
$Convert::Pheno::DB::SQLite::b->{'match_score'} <=> $Convert::Pheno::DB::SQLite::a->{'match_score'} $Convert::Pheno::DB::SQLite::a->{'sequence'} <=> $Convert::Pheno::DB::SQLite::b->{'sequence'}
Truth table33%1/3
A || B
A= $Convert::Pheno::DB::SQLite::b->{'match_score'} <=> $Convert::Pheno::DB::SQLite::a->{'match_score'}B= $Convert::Pheno::DB::SQLite::a->{'sequence'} <=> $Convert::Pheno::DB::SQLite::b->{'sequence'}
ABresult
1X1
011
000
831  
832 11 my $winner = $results->[0];
833 11 my $runner_up = $results->[1];
834 11 my $eligible_candidates = grep { $_->{match_score} >= $min_score } @{$results};
835 my $stats = {
836 candidate_rows => $candidate_rows,
837 shortlisted_candidates => $eligible_candidates,
838 evaluation_time => defined $started_at ? time - $started_at : undef,
839 defined $winner
840 ? (
841 best_candidate_label => $winner->{label},
842 best_candidate_id => $winner->{id},
843 best_candidate_score => $winner->{match_score},
844 28 map { $_ => $winner->{$_} }
845 56 ▶ grep { exists $winner->{$_} }
Branch: 1 branch
BranchTrueFalseTotal
defined $runner_up ? : 4 7 11
846 qw(token_similarity base_token_similarity normalized_levenshtein spelling_variant spelling_query_token spelling_candidate_token spelling_token_similarity),
847 )
848 : (),
849 defined $runner_up
850 ? (
851 runner_up_label => $runner_up->{label},
852 runner_up_id => $runner_up->{id},
853 runner_up_score => $runner_up->{match_score},
854 score_margin => $winner->{match_score} - $runner_up->{match_score},
855 )
856 11 ▶ : (),
Branch: 2 branches
BranchTrueFalseTotal
defined $started_at ? : 0 11 11
defined $winner ? : 8 3 11
857 };
858  
859 return defined $winner && $winner->{match_score} >= $min_score
860 11 ▶ ? ( $winner->{id}, $winner->{label}, $winner->{concept_id}, $stats )
Branch: 1 branch
BranchTrueFalseTotal
defined $winner && $winner->{'match_score'} >= $min_score ? : 7 4 11
Condition100%3/3
defined $winner and $winner->{"match_score"} >= $min_score
!ll&&!rl&&r
317
MC/DC100%2/2
defined $winner and $winner->{"match_score"} >= $min_score
defined $winner $winner->{"match_score"} >= $min_score
Truth table100%3/3
A and B
A= defined $winnerB= $winner->{"match_score"} >= $min_score
ABresult
0X0
100
111
861 : ( undef, undef, undef, $stats );
862 }
863  
864 2 ▶sub similarity_match {
Subroutine similarity_match: called CC=5 SCAR=16.2
Pod undocumented
865 2 my $arg = shift;
866 2 my $sth = $arg->{sth};
867 2 my $query = $arg->{query};
868 2 my $ontology = $arg->{ontology};
869 2 my $id_column = $arg->{id_column};
870 2 my $label_column = $arg->{label_column};
871 2 my $min_score = $arg->{min_text_similarity_score};
872 2 my $sim_method = $arg->{text_similarity_method}; # 'dice' or 'cosine'
873 2 my $concept_id_column = $arg->{concept_id_column};
874 2 ▶ my $started_at = _db_profile_enabled( $arg->{self} ) ? time : undef;
Branch: 1 branch
BranchTrueFalseTotal
_db_profile_enabled($arg->{'self'}) ? : 0 2 2
875 2 my $candidate_rows = 0;
876 2 my $sequence = 0;
877  
878 # Create a new Text::Similarity object.
879 2 my $ts = Text::Similarity::Overlaps->new();
880  
881 2 my @results;
882 2 ▶ while ( my $row = $sth->fetchrow_arrayref() ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $sth->fetchrow_arrayref) 3 2 5
883 3 $candidate_rows++;
884 3 my $candidate_label = $row->[$label_column];
885 3 say "--- MIXED: Computing similarity for candidate <$candidate_label>"
886 if DEVEL_MODE;
887  
888 3 my $token_similarity =
889 Convert::Pheno::DB::Similarity::compute_token_similarity(
890 $query, $candidate_label, $sim_method, $ts );
891  
892 # Retain scores below the threshold for audit and review.
893 3 push @results,
894 {
895 id => _candidate_id( $ontology, $row, $id_column ),
896 label => $candidate_label,
897 match_score => $token_similarity,
898 token_similarity => $token_similarity,
899 concept_id => $row->[$concept_id_column],
900 sequence => $sequence,
901 };
902 3 $sequence++;
903 }
904  
905 2 my @selected = _select_ranked_candidate(
906 \@results, $candidate_rows, $min_score, $started_at
907 );
908 2 print Dumper( \@results ) if DEVEL_MODE;
909 2 ▶ if ( @results && DEVEL_MODE ) {
Branch: 1 branch
BranchTrueFalseTotal
if (@results and 0) 0 2 2
Condition50%1/2
@results and 0
!ll
02
MC/DC0%0/1
@results and 0
@results
Truth table50%1/2
A
A= @results
Aresult
00
11
910 0 say "--- WINNER ---";
911 0 print Dumper( $results[0] );
912 }
913 2 return @selected;
914 }
915  
916 9 ▶sub composite_similarity_match {
Subroutine composite_similarity_match: called CC=7 SCAR=19.5
Pod undocumented
917 9 my $arg = shift;
918 9 my $sth = $arg->{sth};
919 9 my $query = $arg->{query};
920 9 my $ontology = $arg->{ontology};
921 9 my $id_column = $arg->{id_column};
922 9 my $label_column = $arg->{label_column};
923 9 my $min_score = $arg->{min_text_similarity_score};
924 9 my $text_similarity_method = $arg->{text_similarity_method};
925 9 my $levenshtein_weight = $arg->{levenshtein_weight};
926 9 my $token_weight = 1 - $levenshtein_weight;
927 9 my $concept_id_column = $arg->{concept_id_column};
928 9 my $allow_spelling_variant = $arg->{allow_spelling_variant};
929 9 ▶ my $started_at = _db_profile_enabled( $arg->{self} ) ? time : undef;
Branch: 1 branch
BranchTrueFalseTotal
_db_profile_enabled($arg->{'self'}) ? : 0 9 9
930 9 my $candidate_rows = 0;
931 9 my $sequence = 0;
932 9 my $ts = Text::Similarity::Overlaps->new();
933  
934 9 my @results;
935 9 ▶ while ( my $row = $sth->fetchrow_arrayref() ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $sth->fetchrow_arrayref) 11 9 20
936 11 $candidate_rows++;
937 11 my $candidate_label = $row->[$label_column];
938 11 my $token_sim =
939 Convert::Pheno::DB::Similarity::compute_token_similarity( $query,
940 $candidate_label, $text_similarity_method, $ts );
941 11 my $base_token_sim = $token_sim;
942 11 my $spelling_variant;
943  
944 # Relaxed FTS retrieves labels through the unaffected words. Restore
945 # partial token overlap only when exactly one remaining token pair is
946 # close in spelling; all other fuzzy candidates retain the base score.
947 11 ▶ if ($allow_spelling_variant) {
Branch: 1 branch
BranchTrueFalseTotal
if ($allow_spelling_variant) 7 4 11
948 7 $spelling_variant =
949 Convert::Pheno::DB::Similarity::compute_single_token_spelling_similarity(
950 $query, $candidate_label, $text_similarity_method );
951 $token_sim = $spelling_variant->{adjusted_similarity}
952 if $spelling_variant
953 7 ▶ && $spelling_variant->{adjusted_similarity} > $token_sim;
Branch: 1 branch
BranchTrueFalseTotal
if $spelling_variant and $spelling_variant->{"adjusted_similarity"} > $token_sim 2 5 7
Condition66%2/3
$spelling_variant and $spelling_variant->{"adjusted_similarity"} > $token_sim
!ll&&!rl&&r
502
MC/DC50%1/2
$spelling_variant and $spelling_variant->{"adjusted_similarity"} > $token_sim
$spelling_variant $spelling_variant->{"adjusted_similarity"} > $token_sim
Truth table66%2/3
A and B
A= $spelling_variantB= $spelling_variant->{"adjusted_similarity"} > $token_sim
ABresult
0X0
100
111
954 }
955 11 my $lev_sim =
956 Convert::Pheno::DB::Similarity::compute_normalized_levenshtein(
957 $query, $candidate_label );
958 11 my $composite =
959 Convert::Pheno::DB::Similarity::combine_similarity_scores(
960 $token_sim, $lev_sim, $token_weight, $levenshtein_weight );
961  
962 push @results,
963 {
964 id => _candidate_id( $ontology, $row, $id_column ),
965 label => $candidate_label,
966 token_similarity => $token_sim,
967 base_token_similarity => $base_token_sim,
968 normalized_levenshtein => $lev_sim,
969 match_score => $composite,
970 concept_id => $row->[$concept_id_column],
971 sequence => $sequence,
972 $spelling_variant
973 ? (
974 spelling_variant => 1,
975 spelling_query_token => $spelling_variant->{query_token},
976 spelling_candidate_token => $spelling_variant->{candidate_token},
977 spelling_token_similarity => $spelling_variant->{spelling_token_similarity},
978 )
979 11 ▶ : (),
Branch: 1 branch
BranchTrueFalseTotal
$spelling_variant ? : 2 9 11
980 };
981 11 $sequence++;
982 }
983 9 my @selected = _select_ranked_candidate(
984 \@results, $candidate_rows, $min_score, $started_at
985 );
986 9 print Dumper \@results if DEVEL_MODE;
987 9 return @selected;
988 }
989 143 1;
« lib/Convert/Pheno/BFF/DerivedEntities.pm ↑ Summary lib/Convert/Pheno/DB/Similarity.pm »