Users/SQLite.pm

filter: Statementstmt 90.0% 153 / 170 Branchbran 60.9% 39 / 64 Conditioncond 45.0% 9 / 20 MC/DCmcdc 18.1% 2 / 11 Subroutinesub 100% 16 / 16 Podpod 80.0% 8 / 10 totaltotal 78.0% 227 / 291 CC 47 SCAR 22.3
CC
47
Cov
79%
list
18.7
configure
12.9
ensure_storage
8.1
CRAP 9.3
« Users.pm ↑ Summary
1 package Concierge::Users::SQLite v0.9.4;
2 6 ▶use v5.36;
Subroutine BEGIN: called CC=1 SCAR=0.0
3 6 ▶use Carp qw/ croak /;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 6 ▶use DBI;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 6 ▶use parent qw/ Concierge::Users::Meta /;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 # ABSTRACT: SQLite backend for Concierge::Users
8  
9 # ==============================================================================
10 # Configure Class Method - One-time setup (called by Users->setup)
11 # ==============================================================================
12  
13 46 ▶sub configure {
Subroutine configure: called CC=11 SCAR=25.6
Pod documented
14 46 my ($class, $setup_config) = @_;
15  
16 # Extract storage_dir
17 46 my $storage_dir = $setup_config->{storage_dir};
18  
19 # Build SQLite DSN and file path
20 46 my $db_file = "$storage_dir/users.db";
21 46 my $dsn = "dbi:SQLite:$db_file";
22  
23 # Connect to database
24 46 my $dbh = DBI->connect($dsn, '', '', {
25 RaiseError => 0,
26 AutoCommit => 1,
27 PrintError => 0,
28 sqlite_unicode => 1,
29 });
30  
31 46 ▶ unless ($dbh) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($dbh) 0 46 46
32 return {
33 0 ▶ success => 0,
Condition0%0/2
$DBI::errstr || 'Unknown error'
l!l
00
MC/DC0%0/1
$DBI::errstr || 'Unknown error'
$DBI::errstr
Truth table0%0/2
A
A= $DBI::errstr
Aresult
11
00
34 message => sprintf(
35 "SQLite backend connection failed:\n" .
36 " - Database file: %s\n" .
37 " - Error: %s",
38 $db_file,
39 $DBI::errstr || 'Unknown error'
40 ),
41 };
42 }
43  
44 # Create temporary object for ensure_storage
45 my $temp_backend = bless {
46 dbh => $dbh,
47 table_name => 'users',
48 storage_dir => $storage_dir,
49 db_file => $db_file,
50 field_definitions => $setup_config->{field_definitions},
51 46 ▶ fields => $setup_config->{fields} || [],
Condition50%1/2
$setup_config->{'fields'} || []
l!l
460
MC/DC0%0/1
$setup_config->{'fields'} || []
$setup_config->{'fields'}
Truth table50%1/2
A
A= $setup_config->{'fields'}
Aresult
11
00
52 }, $class;
53  
54 # Check for existing data and archive if present
55 46 my $check_sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
56 46 my $sth = $dbh->prepare($check_sql);
57 46 ▶ if ($sth) {
Branch: 1 branch
BranchTrueFalseTotal
if ($sth) 46 0 46
58 46 $sth->execute('users');
59 46 my ($table_exists) = $sth->fetchrow_array();
60 46 $sth->finish();
61  
62 46 ▶ if ($table_exists) {
Branch: 1 branch
BranchTrueFalseTotal
if ($table_exists) 2 44 46
63 # Check if table has data
64 2 my $count_sql = "SELECT COUNT(*) FROM users";
65 2 my $count_sth = $dbh->prepare($count_sql);
66 2 ▶ if ($count_sth) {
Branch: 1 branch
BranchTrueFalseTotal
if ($count_sth) 2 0 2
67 2 $count_sth->execute();
68 2 my ($user_count) = $count_sth->fetchrow_array();
69 2 $count_sth->finish();
70  
71 # Archive if table has data
72 2 ▶ if ($user_count > 0) {
Branch: 1 branch
BranchTrueFalseTotal
if ($user_count > 0) { } 1 1 2
73 1 my $archive_result = $temp_backend->_archive_user_data();
74 1 ▶ unless ($archive_result->{success}) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($archive_result->{"success"}) 0 1 1
75 0 $temp_backend->disconnect();
76 return {
77 success => 0,
78 message => $archive_result->{message},
79 0 };
80 }
81 } else {
82 # Drop empty table
83 1 $dbh->do("DROP TABLE users");
84 }
85 }
86 }
87 }
88  
89 # Ensure storage (table) exists
90 46 my $storage_ok = $temp_backend->ensure_storage();
91 46 ▶ unless ($storage_ok) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($storage_ok) 0 46 46
92 return {
93 0 success => 0,
94 message => "Failed to initialize storage for SQLite backend",
95 };
96 }
97  
98 # Disconnect temp object
99 46 $temp_backend->disconnect();
100  
101 # Return success with config
102 return {
103 success => 1,
104 message => "SQLite backend configured successfully",
105 config => {
106 storage_dir => $storage_dir,
107 db_file => 'users.db',
108 db_full_path => $db_file,
109 table_name => 'users',
110 fields => $setup_config->{fields} || [],
111 field_definitions => $setup_config->{field_definitions},
112 },
113 46 ▶ };
Condition50%1/2
$setup_config->{'fields'} || []
l!l
460
MC/DC0%0/1
$setup_config->{'fields'} || []
$setup_config->{'fields'}
Truth table50%1/2
A
A= $setup_config->{'fields'}
Aresult
11
00
114 }
115  
116 # ==============================================================================
117 # Constructor - Runtime instantiation (called by Users->new)
118 # ==============================================================================
119 42 ▶sub new {
Subroutine new: called CC=6 SCAR=20.5
Pod documented
120 42 my ($class, $runtime_config) = @_;
121  
122 # Extract parameters from saved config (no validation needed)
123 42 my $storage_dir = $runtime_config->{storage_dir};
124 42 my $db_file = $runtime_config->{db_full_path};
125 42 ▶ my $table_name = $runtime_config->{table_name} || 'users';
Condition50%1/2
$runtime_config->{'table_name'} || 'users'
l!l
420
MC/DC0%0/1
$runtime_config->{'table_name'} || 'users'
$runtime_config->{'table_name'}
Truth table50%1/2
A
A= $runtime_config->{'table_name'}
Aresult
11
00
126  
127 # Build SQLite DSN
128 42 my $dsn = "dbi:SQLite:$db_file";
129  
130 # Connect to database
131 42 my $dbh = DBI->connect($dsn, '', '', {
132 RaiseError => 0,
133 AutoCommit => 1,
134 PrintError => 0,
135 sqlite_unicode => 1,
136 });
137  
138 42 ▶ unless ($dbh) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($dbh) 0 42 42
139 0 ▶ croak sprintf(
Condition0%0/2
$DBI::errstr || 'Unknown error'
l!l
00
MC/DC0%0/1
$DBI::errstr || 'Unknown error'
$DBI::errstr
Truth table0%0/2
A
A= $DBI::errstr
Aresult
11
00
140 "SQLite backend connection failed:\n" .
141 " - Database file: %s\n" .
142 " - Error: %s",
143 $db_file,
144 $DBI::errstr || 'Unknown error'
145 );
146 }
147  
148 return bless {
149 dbh => $dbh,
150 table_name => $table_name,
151 storage_dir => $storage_dir,
152 db_file => $db_file,
153 fields => $runtime_config->{fields} || [],
154 field_definitions => $runtime_config->{field_definitions} || {},
155 42 ▶ }, $class;
Condition50%2/4
$runtime_config->{'fields'} || []
l!l
420
$runtime_config->{'field_definitions'} || {}
l!l
420
MC/DC0%0/1
$runtime_config->{'fields'} || []
$runtime_config->{'fields'}
MC/DC0%0/1
$runtime_config->{'field_definitions'} || {}
$runtime_config->{'field_definitions'}
Truth table50%1/2
A
A= $runtime_config->{'fields'}
Aresult
11
00
Truth table50%1/2
A
A= $runtime_config->{'field_definitions'}
Aresult
11
00
156 }
157  
158 # Report backend configuration (for debugging/info)
159 1 ▶sub config {
Subroutine config: called CC=1 SCAR=0.0
Pod undocumented
160 1 my ($self) = @_;
161  
162 return {
163 storage_dir => $self->{storage_dir},
164 db_file => $self->{db_file},
165 db_full_path => $self->{db_file},
166 table_name => $self->{table_name},
167 fields => $self->{fields},
168 field_definitions => $self->{field_definitions},
169 1 };
170 }
171  
172 # Ensure storage (table) exists
173 46 ▶sub ensure_storage {
Subroutine ensure_storage: called CC=8 SCAR=20.9
Pod undocumented
174 46 my ($self) = @_;
175  
176 # Check if table exists
177 46 my $check_sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
178 46 my $sth = $self->{dbh}->prepare($check_sql);
179 46 ▶ return 0 unless $sth;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 46 46
180 46 $sth->execute($self->{table_name});
181 46 my ($exists) = $sth->fetchrow_array();
182  
183 46 ▶ return 1 if $exists; # Table already exists
Branch: 1 branch
BranchTrueFalseTotal
if $exists 0 46 46
184  
185 # Build CREATE TABLE SQL
186 46 my @field_defs;
187 my @indexes;
188  
189 46 foreach my $field (@{$self->{fields}}) {
190 # All fields are TEXT in our schema
191 572 my $field_def = "$field TEXT";
192  
193 # Check if field is required
194 572 my $field_def_info = $self->{field_definitions}{$field};
195 572 ▶ if ($field_def_info && $field_def_info->{required}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($field_def_info and $field_def_info->{"required"}) 185 387 572
Condition66%2/3
$field_def_info and $field_def_info->{"required"}
!ll&&!rl&&r
0387185
MC/DC50%1/2
$field_def_info and $field_def_info->{"required"}
$field_def_info $field_def_info->{"required"}
Truth table66%2/3
A and B
A= $field_def_infoB= $field_def_info->{"required"}
ABresult
0X0
100
111
196 185 $field_def .= " NOT NULL";
197 }
198  
199 572 push @field_defs, $field_def;
200 }
201  
202 # Primary key on user_id
203 46 push @indexes, "CREATE UNIQUE INDEX idx_user_id ON $self->{table_name}(user_id)";
204  
205 # Create table
206 46 my $create_sql = "CREATE TABLE $self->{table_name} (\n" .
207 join(",\n ", @field_defs) . "\n)";
208  
209 46 my $result = $self->{dbh}->do($create_sql);
210 46 ▶ return 0 unless $result;
Branch: 1 branch
BranchTrueFalseTotal
unless $result 0 46 46
211  
212 # Create indexes
213 46 foreach my $index_sql (@indexes) {
214 46 $self->{dbh}->do($index_sql);
215 }
216  
217 46 return 1;
218 }
219  
220 # Archive existing user data (internal method, called by configure)
221 1 ▶sub _archive_user_data {
Subroutine _archive_user_data: called CC=2 SCAR=7.1
222 1 my ($self) = @_;
223  
224 # Generate timestamp for archive table name
225 1 my $timestamp = $self->archive_timestamp();
226 1 my $archive_table = "$self->{table_name}_$timestamp";
227  
228 # Rename table
229 1 my $rename_sql = "ALTER TABLE $self->{table_name} RENAME TO $archive_table";
230 1 my $result = $self->{dbh}->do($rename_sql);
231  
232 1 ▶ unless ($result) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($result) 0 1 1
233 return {
234 success => 0,
235 message => "Failed to archive existing table: " . $self->{dbh}->errstr
236 0 };
237 }
238  
239 1 return { success => 1 };
240 }
241  
242 # Add bare record with user_id and null_values
243 97 ▶sub add {
Subroutine add: called CC=4 SCAR=14.1
Pod documented
244 97 my ($self, $user_id, $initial_record) = @_;
245 97 ▶ return { success => 0, message => "Add Record failed: missing user_id" }
Branch: 1 branch
BranchTrueFalseTotal
unless $user_id 0 97 97
246 unless $user_id;
247 97 ▶ return { success => 0, message => "Add Record failed: missing initial record" }
Branch: 1 branch
BranchTrueFalseTotal
unless $initial_record 0 97 97
248 unless $initial_record;
249  
250 97 my %record = $initial_record->%*;
251 97 $record{created_date} = $self->current_timestamp();
252 # Add last_mod_date timestamp
253 97 $record{last_mod_date} = $self->current_timestamp();
254  
255 # Insert into database
256 97 my @fields = keys %record;
257 97 my @placeholders = map { '?' } @fields;
258 97 my @values = @record{@fields};
259  
260 97 my $sql = "INSERT INTO $self->{table_name} (" .
261 join(', ', @fields) . ") VALUES (" .
262 join(', ', @placeholders) . ")";
263  
264 97 my $sth = $self->{dbh}->prepare($sql);
265 97 ▶ if ($sth->execute(@values)) {
Branch: 1 branch
BranchTrueFalseTotal
if ($sth->execute(@values)) { } 97 0 97
266 97 return { success => 1, message => "Initial record created for user '$user_id'" };
267 } else {
268 0 return { success => 0, message => "Failed to create initial user record: " . $self->{dbh}->errstr };
269 }
270 }
271  
272 # Fetch user by ID
273 131 ▶sub fetch {
Subroutine fetch: called CC=3 SCAR=11.0
Pod documented
274 131 my ($self, $user_id) = @_;
275  
276 131 my $sql = "SELECT * FROM $self->{table_name} WHERE user_id = ?";
277 131 my $sth = $self->{dbh}->prepare($sql);
278 131 $sth->execute($user_id);
279  
280 131 my $user_data = $sth->fetchrow_hashref();
281  
282 return {
283 131 ▶ success => $user_data ? 1 : 0,
Branch: 2 branches
BranchTrueFalseTotal
$user_data ? : 29 102 131
$user_data ? : 29 102 131
284 data => $user_data,
285 message => $user_data ? '' : "User '$user_id' not found"
286 };
287 }
288  
289 # Update user
290 100 ▶sub update {
Subroutine update: called CC=4 SCAR=14.2
Pod documented
291 100 my ($self, $user_id, $updates) = @_;
292  
293 # Remove readonly fields from updates
294 100 my %readonly = map { $_ => 1 } qw(user_id created_date last_mod_date);
295 100 delete $updates->{$_} for keys %readonly;
296  
297 # Add last_mod_date timestamp
298 100 $updates->{last_mod_date} = $self->current_timestamp();
299  
300 100 my @fields = keys %$updates;
301 100 my @values = values %$updates;
302 100 push @values, $user_id; # For WHERE clause
303  
304 my $sql = "UPDATE $self->{table_name} SET " .
305 100 join(', ', map { "$_ = ?" } @fields) .
306 " WHERE user_id = ?";
307  
308 100 my $sth = $self->{dbh}->prepare($sql);
309 100 ▶ unless ($sth) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($sth) 0 100 100
310 0 return { success => 0, message => "Failed to prepare update: " . $self->{dbh}->errstr };
311 }
312  
313 100 ▶ if ($sth->execute(@values)) {
Branch: 1 branch
BranchTrueFalseTotal
if ($sth->execute(@values)) { } 100 0 100
314 100 return { success => 1, message => "User '$user_id' updated" };
315 } else {
316 0 return { success => 0, message => "Failed to update user: " . $self->{dbh}->errstr };
317 }
318 }
319  
320 # List users with filters
321 8 ▶sub list {
Subroutine list: called CC=14 SCAR=29.3
Pod documented
322 8 my ($self, $filters, $options) = @_;
323  
324 # Build WHERE clause from DSL filter structure
325 8 my @where_clauses;
326 my @where_values;
327  
328 8 ▶ if (ref $filters eq 'HASH' && exists $filters->{or_groups}) {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $filters eq "HASH" and exists $filters->{"or_groups"}) 2 6 8
Condition66%2/3
ref $filters eq "HASH" and exists $filters->{"or_groups"}
!ll&&!rl&&r
062
MC/DC50%1/2
ref $filters eq "HASH" and exists $filters->{"or_groups"}
ref $filters eq "HASH" exists $filters->{"or_groups"}
Truth table66%2/3
A and B
A= ref $filters eq "HASH"B= exists $filters->{"or_groups"}
ABresult
0X0
100
111
329 # Parse DSL filter structure
330 2 my @or_groups;
331  
332 2 foreach my $and_group (@{$filters->{or_groups}}) {
333 2 my @and_clauses;
334 2 foreach my $condition (@$and_group) {
335 2 my ($field, $op, $value) = ($condition->{field}, $condition->{op}, $condition->{value});
336  
337 2 my $clause;
338 2 ▶ if ($op eq '=') {
Branch: 5 branches
BranchTrueFalseTotal
if ($op eq '=') { } 1 1 2
elsif ($op eq ':') { } 1 0 1
elsif ($op eq '!') { } 0 0 0
elsif ($op eq '>') { } 0 0 0
elsif ($op eq '<') { } 0 0 0
339 1 $clause = "$field = ?";
340 1 push @where_values, $value;
341 } elsif ($op eq ':') {
342 1 $clause = "$field LIKE ?";
343 1 push @where_values, "%$value%";
344 } elsif ($op eq '!') {
345 0 $clause = "$field NOT LIKE ?";
346 0 push @where_values, "%$value%";
347 } elsif ($op eq '>') {
348 0 $clause = "$field > ?";
349 0 push @where_values, $value;
350 } elsif ($op eq '<') {
351 0 $clause = "$field < ?";
352 0 push @where_values, $value;
353 } else {
354 0 next; # Skip unknown operators
355 }
356  
357 2 push @and_clauses, $clause;
358 }
359  
360 # Join AND conditions
361 2 ▶ if (@and_clauses) {
Branch: 1 branch
BranchTrueFalseTotal
if (@and_clauses) 2 0 2
362 2 push @or_groups, "(" . join(" AND ", @and_clauses) . ")";
363 }
364 }
365  
366 # Join OR groups
367 2 ▶ if (@or_groups) {
Branch: 1 branch
BranchTrueFalseTotal
if (@or_groups) 2 0 2
368 2 push @where_clauses, join(" OR ", @or_groups);
369 }
370 }
371  
372 # Data query
373 8 my $sql = "SELECT * FROM $self->{table_name}";
374 8 ▶ if (@where_clauses) {
Branch: 1 branch
BranchTrueFalseTotal
if (@where_clauses) 2 6 8
375 2 $sql .= " WHERE " . join(' AND ', @where_clauses);
376 }
377  
378 8 my $sth = $self->{dbh}->prepare($sql);
379 8 $sth->execute(@where_values);
380  
381 8 my @users;
382 8 ▶ while (my $row = $sth->fetchrow_hashref()) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $sth->fetchrow_hashref) 62 8 70
383 62 push @users, $row;
384 }
385  
386 return {
387 8 data => \@users,
388 total_count => scalar @users,
389 };
390 }
391  
392 # Delete user
393 1 ▶sub delete {
Subroutine delete: called CC=2 SCAR=7.2
Pod documented
394 1 my ($self, $user_id) = @_;
395  
396 1 my $sql = "DELETE FROM $self->{table_name} WHERE user_id = ?";
397 1 my $sth = $self->{dbh}->prepare($sql);
398  
399 1 ▶ if ($sth->execute($user_id)) {
Branch: 1 branch
BranchTrueFalseTotal
if ($sth->execute($user_id)) { } 1 0 1
400 1 return { success => 1, message => "User '$user_id' deleted" };
401 } else {
402 0 return { success => 0, message => "Failed to delete user: " . $self->{dbh}->errstr };
403 }
404 }
405  
406 # Cleanup
407 176 ▶sub disconnect {
Subroutine disconnect: called CC=2 SCAR=6.9
Pod documented
408 176 my $self = shift;
409 176 ▶ if ($self->{dbh}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"dbh"}) 88 88 176
410 88 $self->{dbh}->disconnect();
411 88 $self->{dbh} = undef;
412 }
413 }
414  
415 88 ▶sub DESTROY {
Subroutine DESTROY: called CC=1 SCAR=0.0
416 88 my $self = shift;
417 88 $self->disconnect();
418 }
419  
420 6 1;
421  
422 __END__
« Users.pm ↑ Summary