Zonemaster/Backend/DB/SQLite.pm untested

filter: Statementstmt 0.0% 0 / 105 Branchbran 0.0% 0 / 8 Conditioncond 0.0% 0 / 18 MC/DCmcdc 0.0% 0 / 10 Subroutinesub 0.0% 0 / 16 Podpod 0.0% 0 / 9 totaltotal 0.0% 0 / 166 CC 5 SCAR 14.6
CC
5
Cov
0%
create_schema
12.0
DEMOLISH
6.0
add_batch_job
6.0
CRAP 4.3
1 package Zonemaster::Backend::DB::SQLite;
2  
3 our $VERSION = '1.1.0';
4  
5 use Moose;
6 use 5.14.2;
7  
8 use DBI qw(:utils);
9 use Digest::MD5 qw(md5_hex);
10 use JSON::PP;
11  
12  
13 use Zonemaster::Backend::Errors;
14  
15 with 'Zonemaster::Backend::DB';
16  
17 =head1 CLASS METHODS
18  
19 =head2 from_config
20  
21 Construct a new instance from a Zonemaster::Backend::Config.
22  
23 my $db = Zonemaster::Backend::DB::SQLite->from_config( $config );
24  
25 =cut
26  
27 sub from_config {
28 my ( $class, $config ) = @_;
29  
30 my $file = $config->SQLITE_database_file;
31  
32 my $data_source_name = "DBI:SQLite:dbname=$file";
33  
34 return $class->new(
35 {
36 data_source_name => $data_source_name,
37 user => '',
38 password => '',
39 dbhandle => undef,
40 }
41 );
42 }
43  
44 sub DEMOLISH {
45 my ( $self ) = @_;
46 $self->dbh->disconnect() if defined $self->dbhandle && $self->dbhandle->ping;
47 }
48  
49 sub get_dbh_specific_attributes {
50 return { sqlite_extended_result_codes => 1 };
51 }
52  
53 sub create_schema {
54 my ( $self ) = @_;
55  
56 my $dbh = $self->dbh;
57  
58 # enable FOREIGN KEY support
59 $dbh->do( 'PRAGMA foreign_keys = ON;' );
60  
61 ####################################################################
62 # TEST RESULTS
63 ####################################################################
64 $dbh->do(
65 'CREATE TABLE IF NOT EXISTS test_results (
66 id integer PRIMARY KEY AUTOINCREMENT,
67 hash_id VARCHAR(16) NOT NULL,
68 domain VARCHAR(255) NOT NULL,
69 batch_id integer NULL,
70 created_at DATETIME NOT NULL,
71 started_at DATETIME DEFAULT NULL,
72 ended_at DATETIME DEFAULT NULL,
73 priority integer DEFAULT 10,
74 queue integer DEFAULT 0,
75 progress integer DEFAULT 0,
76 fingerprint character varying(32),
77 params text NOT NULL,
78 results text DEFAULT NULL,
79 undelegated boolean NOT NULL DEFAULT false,
80  
81 UNIQUE (hash_id)
82 )
83 '
84 ) or die Zonemaster::Backend::Error::Internal->new( reason => "SQLite error, could not create 'test_results' table", data => $dbh->errstr() );
85  
86 $dbh->do(
87 'CREATE INDEX IF NOT EXISTS test_results__hash_id ON test_results (hash_id)'
88 );
89 $self->dbh->do(
90 'CREATE INDEX IF NOT EXISTS test_results__fingerprint ON test_results (fingerprint)'
91 );
92 $dbh->do(
93 'CREATE INDEX IF NOT EXISTS test_results__batch_id_progress ON test_results (batch_id, progress)'
94 );
95 $dbh->do(
96 'CREATE INDEX IF NOT EXISTS test_results__progress ON test_results (progress)'
97 );
98 $dbh->do(
99 'CREATE INDEX IF NOT EXISTS test_results__domain_undelegated ON test_results (domain, undelegated)'
100 );
101  
102 ####################################################################
103 # LOG LEVEL
104 ####################################################################
105 $dbh->do(
106 "CREATE TABLE IF NOT EXISTS log_level (
107 value INTEGER,
108 level VARCHAR(15),
109  
110 UNIQUE (value)
111 )
112 "
113 ) or die Zonemaster::Backend::Error::Internal->new( reason => "SQLite error, could not create 'log_level' table", data => $dbh->errstr() );
114  
115 my ( $c ) = $dbh->selectrow_array( "SELECT count(*) FROM log_level" );
116 if ( $c == 0 ) {
117 $dbh->do(
118 "INSERT INTO log_level (value, level)
119 VALUES
120 (-2, 'DEBUG3'),
121 (-1, 'DEBUG2'),
122 ( 0, 'DEBUG'),
123 ( 1, 'INFO'),
124 ( 2, 'NOTICE'),
125 ( 3, 'WARNING'),
126 ( 4, 'ERROR'),
127 ( 5, 'CRITICAL')
128 "
129 );
130 }
131  
132 ####################################################################
133 # RESULT ENTRIES
134 ####################################################################
135 $dbh->do(
136 'CREATE TABLE IF NOT EXISTS result_entries (
137 hash_id VARCHAR(16) NOT NULL,
138 level INT NOT NULL,
139 module VARCHAR(255) NOT NULL,
140 testcase VARCHAR(255) NOT NULL,
141 tag VARCHAR(255) NOT NULL,
142 timestamp REAL NOT NULL,
143 args BLOB NOT NULL,
144  
145 FOREIGN KEY(hash_id) REFERENCES test_results(hash_id),
146 FOREIGN KEY(level) REFERENCES log_level(value)
147 )
148 '
149 ) or die Zonemaster::Backend::Error::Internal->new( reason => "SQLite error, could not create 'result_entries' table", data => $dbh->errstr() );
150  
151 $dbh->do(
152 'CREATE INDEX IF NOT EXISTS result_entries__hash_id ON result_entries (hash_id)'
153 );
154  
155 $dbh->do(
156 'CREATE INDEX IF NOT EXISTS result_entries__level ON result_entries (level)'
157 );
158  
159 ####################################################################
160 # BATCH JOBS
161 ####################################################################
162 $dbh->do(
163 'CREATE TABLE IF NOT EXISTS batch_jobs (
164 id integer PRIMARY KEY,
165 username character varying(50) NOT NULL,
166 created_at DATETIME NOT NULL
167 )
168 '
169 ) or die Zonemaster::Backend::Error::Internal->new( reason => "SQLite error, could not create 'batch_jobs' table", data => $dbh->errstr() );
170  
171  
172 ####################################################################
173 # USERS
174 ####################################################################
175 $dbh->do(
176 'CREATE TABLE IF NOT EXISTS users (
177 id INTEGER PRIMARY KEY AUTOINCREMENT,
178 username varchar(128),
179 api_key varchar(512),
180  
181 UNIQUE (username)
182 )
183 '
184 ) or die Zonemaster::Backend::Error::Internal->new( reason => "SQLite error, could not create 'users' table", data => $dbh->errstr() );
185  
186 ####################################################################
187 # SCHEMA VERSION
188 ####################################################################
189 $dbh->do(
190 "CREATE TABLE IF NOT EXISTS schema_version (
191 id INTEGER PRIMARY KEY,
192 version INTEGER NOT NULL,
193 CHECK (id = 1),
194 CHECK (version >= 1)
195 )"
196 )
197 or die Zonemaster::Backend::Error::Internal->new(
198 reason => "Failed to create 'schema_version' table",
199 data => $dbh->errstr()
200 );
201  
202 if ( !defined $dbh->selectrow_array( "SELECT 1 FROM schema_version LIMIT 1" ) ) {
203 $dbh->do( "INSERT INTO schema_version (id, version) VALUES (1, ?)", {}, $Zonemaster::Backend::DB::REQUIRED_SCHEMA_VERSION );
204 }
205  
206 return;
207 }
208  
209 =head2 drop_tables
210  
211 Drop all the tables if they exist.
212  
213 =cut
214  
215 sub drop_tables {
216 my ( $self ) = @_;
217  
218 $self->dbh->do( "DROP TABLE IF EXISTS schema_version" );
219 $self->dbh->do( "DROP TABLE IF EXISTS result_entries" );
220 $self->dbh->do( "DROP TABLE IF EXISTS test_results" );
221 $self->dbh->do( "DROP TABLE IF EXISTS log_level" );
222 $self->dbh->do( "DROP TABLE IF EXISTS users" );
223 $self->dbh->do( "DROP TABLE IF EXISTS batch_jobs" );
224  
225 return;
226 }
227  
228 sub add_batch_job {
229 my ( $self, $params ) = @_;
230 my $batch_id;
231  
232 my $dbh = $self->dbh;
233  
234 if ( $self->user_authorized( $params->{username}, $params->{api_key} ) ) {
235 $batch_id = $self->create_new_batch_job( $params->{username} );
236  
237 my $test_params = $params->{test_params};
238 my $priority = $test_params->{priority};
239 my $queue_label = $test_params->{queue};
240  
241 $dbh->{AutoCommit} = 0;
242 eval {$dbh->do( "DROP INDEX IF EXISTS test_results__hash_id " );};
243 eval {$dbh->do( "DROP INDEX IF EXISTS test_results__fingerprint " );};
244 eval {$dbh->do( "DROP INDEX IF EXISTS test_results__batch_id_progress " );};
245 eval {$dbh->do( "DROP INDEX IF EXISTS test_results__progress " );};
246 eval {$dbh->do( "DROP INDEX IF EXISTS test_results__domain_undelegated " );};
247  
248 my $sth = $dbh->prepare( '
249 INSERT INTO test_results (
250 hash_id,
251 domain,
252 batch_id,
253 created_at,
254 priority,
255 queue,
256 fingerprint,
257 params,
258 undelegated
259 ) VALUES (?,?,?,?,?,?,?,?,?)'
260 );
261 foreach my $domain ( @{$params->{domains}} ) {
262 $test_params->{domain} = _normalize_domain( $domain );
263  
264 my $fingerprint = $self->generate_fingerprint( $test_params );
265 my $encoded_params = $self->encode_params( $test_params );
266 my $undelegated = $self->undelegated ( $test_params );
267  
268 my $hash_id = substr(md5_hex(time().rand()), 0, 16);
269 $sth->execute(
270 $hash_id,
271 $test_params->{domain},
272 $batch_id,
273 $self->format_time( time() ),
274 $priority,
275 $queue_label,
276 $fingerprint,
277 $encoded_params,
278 $undelegated,
279 );
280 }
281 $dbh->do( "CREATE INDEX test_results__hash_id ON test_results (hash_id, created_at)" );
282 $dbh->do( "CREATE INDEX test_results__fingerprint ON test_results (fingerprint)" );
283 $dbh->do( "CREATE INDEX test_results__batch_id_progress ON test_results (batch_id, progress)" );
284 $dbh->do( "CREATE INDEX test_results__progress ON test_results (progress)" );
285 $dbh->do( "CREATE INDEX test_results__domain_undelegated ON test_results (domain, undelegated)" );
286  
287 $dbh->commit();
288 $dbh->{AutoCommit} = 1;
289 }
290 else {
291 die Zonemaster::Backend::Error::PermissionDenied->new( message => 'User not authorized to use batch mode', data => { username => $params->{username}} );
292 }
293  
294 return $batch_id;
295 }
296  
297 sub get_relative_start_time {
298 my ( $self, $hash_id ) = @_;
299  
300 return $self->dbh->selectrow_array(
301 q[
302 SELECT (julianday(?) - julianday(started_at)) * 3600 * 24
303 FROM test_results
304 WHERE hash_id = ?
305 ],
306 undef,
307 $self->format_time( time() ),
308 $hash_id,
309 );
310 }
311  
312 sub is_duplicate {
313 my ( $self ) = @_;
314  
315 # for the list of codes see: https://sqlite.org/rescode.html
316 return ( $self->dbh->err == 2067 );
317 }
318  
319 sub is_unknown_table {
320 my ( $self ) = @_;
321  
322 # for the list of codes see: https://sqlite.org/rescode.html
323 return ( $self->dbh->err == 1 )
324 && $self->dbh->errstr =~ /^no such table: /;
325 }
326  
327 no Moose;
328 __PACKAGE__->meta()->make_immutable();
329  
330 1;