Mojo/Hakkefuin/Backend/sqlite.pm untested

filter: Statementstmt 0.0% 0 / 190 Branchbran 0.0% 0 / 58 Conditioncond 0.0% 0 / 25 MC/DCmcdc 0.0% 0 / 16 Subroutinesub 0.0% 0 / 19 Podpod 0.0% 0 / 16 totaltotal 0.0% 0 / 324 CC 30 SCAR 26.7
CC
30
Cov
0%
update
20.0
update_csrf
20.0
update_cookie
20.0
CRAP 14.4
1 package Mojo::Hakkefuin::Backend::sqlite;
2 use Mojo::Base 'Mojo::Hakkefuin::Backend';
3  
4 use Mojo::SQLite;
5 use CellBIS::SQL::Abstract;
6  
7 has 'sqlite';
8 has 'file_db';
9 has 'file_migration';
10 has abstract =>
11 sub { state $abstract = CellBIS::SQL::Abstract->new(db_type => 'sqlite') };
12  
13 sub new {
14 my $self = shift->SUPER::new(@_);
15  
16 $self->file_migration($self->dir . '/mhf_sqlite.sql');
17 $self->file_db('sqlite:' . $self->dir . '/mhf_sqlite.db');
18  
19 $self->sqlite(Mojo::SQLite->new($self->file_db));
20 return $self;
21 }
22  
23 sub check_table {
24 my $self = shift;
25  
26 my $result = {result => 0, code => 400};
27 my $q = $self->abstract->select('sqlite_master', ['name'],
28 {where => 'type=\'table\' AND tbl_name=\'' . $self->table_name . '\''});
29  
30 if (my $dbh = $self->sqlite->db->query($q)) {
31 $result = {result => $dbh->hash, code => 200};
32 eval { $self->_ensure_indexes };
33 }
34 return $result;
35 }
36  
37 sub create_table {
38 my $self = shift;
39 my $table_query = $self->table_query;
40  
41 my $result = {result => 0, code => 400};
42  
43 if (my $dbh = $self->sqlite->db->query($table_query)) {
44 $result->{result} = $dbh->rows;
45 $result->{code} = 200;
46 eval { $self->_ensure_indexes };
47 }
48 return $result;
49 }
50  
51 sub _ensure_indexes {
52 my $self = shift;
53  
54 my $table = $self->table_name;
55 my @idx_sql = (
56 'CREATE INDEX IF NOT EXISTS idx_'
57 . $table
58 . '_identify ON '
59 . $table . ' ('
60 . $self->identify . ')',
61 'CREATE INDEX IF NOT EXISTS idx_'
62 . $table
63 . '_cookie ON '
64 . $table . ' ('
65 . $self->cookie . ')',
66 'CREATE INDEX IF NOT EXISTS idx_'
67 . $table
68 . '_expire_date ON '
69 . $table . ' ('
70 . $self->expire_date . ')',
71 );
72  
73 for my $sql (@idx_sql) {
74 eval { $self->sqlite->db->query($sql) };
75 }
76 }
77  
78 sub table_query {
79 my $self = shift;
80  
81 $self->abstract->create_table(
82 $self->table_name,
83 [
84 $self->id, $self->identify, $self->cookie,
85 $self->csrf, $self->create_date, $self->expire_date,
86 $self->cookie_lock, $self->lock
87 ],
88 {
89 $self->id =>
90 {type => {name => 'integer'}, is_primarykey => 1, is_autoincre => 1},
91 $self->identify => {type => {name => 'text'}},
92 $self->cookie => {type => {name => 'text'}},
93 $self->csrf => {type => {name => 'text'}},
94 $self->create_date => {type => {name => 'datetime'}},
95 $self->expire_date => {type => {name => 'datetime'}},
96 $self->cookie_lock =>
97 {type => {name => 'text'}, default => '\'no-lock\'', is_null => 1},
98 $self->lock => {type => {name => 'integer'}},
99 }
100 );
101 }
102  
103 sub create {
104 my ($self, $identify, $cookie, $csrf, $expires) = @_;
105  
106 return {result => 0, code => 500, data => $cookie} unless $self->_table_ok;
107  
108 my $result = {result => 0, code => 400, data => $cookie};
109  
110 my $mhf_utils = $self->mhf_util->new;
111 my $now_time = $mhf_utils->sql_datetime(0);
112 my $expire_time = $mhf_utils->sql_datetime($expires);
113  
114 my @q = (
115 $self->table_name,
116 {
117 $self->identify => $identify,
118 $self->cookie => $cookie,
119 $self->csrf => $csrf,
120 $self->create_date => $now_time,
121 $self->expire_date => $expire_time,
122 $self->cookie_lock => 'no_lock',
123 $self->lock => 0
124 },
125 {on_conflict => undef}
126 );
127  
128 if (my $dbh = $self->sqlite->db->insert(@q)) {
129 $result->{result} = $dbh->rows;
130 $result->{code} = 200;
131 }
132 return $result;
133 }
134  
135 sub read {
136 my ($self, $identify, $cookie) = @_;
137  
138 $identify //= 'null';
139 $cookie //= 'null';
140  
141 return {result => 0, code => 500, data => $cookie} unless $self->_table_ok;
142  
143 my $result = {result => 0, code => 400, data => $cookie};
144 my @q = (
145 $self->table_name, '*',
146 {$self->identify => $identify, $self->cookie => $cookie}
147 );
148 if (my $dbh = $self->sqlite->db->select(@q)) {
149 $result->{result} = 1;
150 $result->{code} = 200;
151 $result->{data} = $dbh->hash;
152 }
153 return $result;
154 }
155  
156 sub update {
157 my ($self, $id, $cookie, $csrf) = @_;
158  
159 my $mhf_utils = $self->mhf_util->new;
160 my $now_time = $mhf_utils->sql_datetime(0);
161  
162 return {result => 0, code => 500, csrf => $csrf, cookie => $cookie}
163 unless $self->_table_ok;
164  
165 my $result = {result => 0, code => 400, csrf => $csrf, cookie => $cookie};
166 return $result unless $id && $csrf;
167  
168 my @q = (
169 $self->table_name,
170 {$self->cookie, => $cookie, $self->csrf => $csrf},
171 {$self->id => $id, $self->expire_date => {'>=', $now_time}}
172 );
173 if (my $dbh = $self->sqlite->db->update(@q)) {
174 $result->{result} = $dbh->rows;
175 $result->{code} = 200;
176 }
177 return $result;
178 }
179  
180 sub update_csrf {
181 my ($self, $id, $csrf) = @_;
182  
183 my $mhf_utils = $self->mhf_util->new;
184 my $now_time = $mhf_utils->sql_datetime(0);
185  
186 return {result => 0, code => 500, data => $csrf} unless $self->_table_ok;
187  
188 my $result = {result => 0, code => 400, data => $csrf};
189 return $result unless $id && $csrf;
190  
191 my @q = (
192 $self->table_name,
193 {$self->csrf => $csrf},
194 {$self->id => $id, $self->expire_date => {'>=', $now_time}}
195 );
196 if (my $dbh = $self->sqlite->db->update(@q)) {
197 $result->{result} = $dbh->rows;
198 $result->{code} = 200;
199 }
200 return $result;
201 }
202  
203 sub update_cookie {
204 my ($self, $id, $cookie) = @_;
205  
206 my $mhf_utils = $self->mhf_util->new;
207 my $now_time = $mhf_utils->sql_datetime(0);
208  
209 return {result => 0, code => 500, data => $cookie} unless $self->_table_ok;
210  
211 my $result = {result => 0, code => 400, data => $cookie};
212 return $result unless $id && $cookie;
213  
214 my @q = (
215 $self->table_name,
216 {$self->cookie => $cookie},
217 {$self->id => $id, $self->expire_date => {'>=', $now_time}}
218 );
219 if (my $dbh = $self->sqlite->db->update(@q)) {
220 $result->{result} = $dbh->rows;
221 $result->{code} = 200;
222 }
223 return $result;
224 }
225  
226 sub upd_coolock {
227 my ($self, $id, $cookie_lock) = @_;
228  
229 my $mhf_utils = $self->mhf_util->new;
230 my $now_time = $mhf_utils->sql_datetime(0);
231  
232 return {result => 0, code => 500, data => $cookie_lock}
233 unless $self->_table_ok;
234  
235 my $result = {result => 0, code => 400, data => $cookie_lock};
236 return $result unless $id && defined $cookie_lock;
237  
238 my @q = (
239 $self->table_name,
240 {$self->cookie_lock => $cookie_lock},
241 {$self->id => $id, $self->expire_date => {'>=', $now_time}}
242 );
243 if (my $dbh = $self->sqlite->db->update(@q)) {
244 $result->{result} = $dbh->rows;
245 $result->{code} = 200;
246 }
247 return $result;
248 }
249  
250 sub upd_lckstate {
251 my ($self, $id, $state) = @_;
252  
253 my $mhf_utils = $self->mhf_util->new;
254 my $now_time = $mhf_utils->sql_datetime(0);
255  
256 return {result => 0, code => 500, data => $state} unless $self->_table_ok;
257  
258 my $result = {result => 0, code => 400, data => $state};
259 return $result unless $id && defined $state;
260  
261 my @q = (
262 $self->table_name,
263 {$self->lock => $state},
264 {$self->id => $id, $self->expire_date => {'>=', $now_time}}
265 );
266 if (my $dbh = $self->sqlite->db->update(@q)) {
267 $result->{result} = $dbh->rows;
268 $result->{code} = 200;
269 }
270 return $result;
271 }
272  
273 sub delete {
274 my ($self, $identify, $cookie) = @_;
275  
276 return {result => 0, code => 500, data => $cookie} unless $self->_table_ok;
277  
278 my $result = {result => 0, code => 400, data => $cookie};
279 return $result unless $identify && $cookie;
280  
281 my @q = (
282 $self->table_name, {$self->identify => $identify, $self->cookie => $cookie}
283 );
284 if (my $dbh = $self->sqlite->db->delete(@q)) {
285 $result->{result} = $dbh->rows;
286 $result->{code} = 200;
287 }
288 return $result;
289 }
290  
291 sub check {
292 my ($self, $identify, $cookie) = @_;
293  
294 return {result => 0, code => 500, data => $cookie} unless $self->_table_ok;
295  
296 my $result = {result => 0, code => 400, data => $cookie};
297 return $result unless $identify && $cookie;
298  
299 my @q = (
300 $self->table_name,
301 '*',
302 {
303 -or => {$self->identify => $identify, $self->cookie => $cookie},
304 $self->expire_date => {'>', "'datetime('now', 'localtime')"}
305 }
306 );
307 if (my $rv = $self->sqlite->db->select(@q)) {
308 my $r_data = $rv->hash;
309 $result = {
310 result => 1,
311 code => 200,
312 data => {
313 cookie => $cookie,
314 id => $r_data->{$self->id},
315 csrf => $r_data->{$self->csrf},
316 identify => $r_data->{$self->identify},
317 cookie_lock => $r_data->{$self->cookie_lock},
318 lock => $r_data->{$self->lock}
319 }
320 };
321 }
322 return $result;
323 }
324  
325 sub empty_table {
326 my $self = shift;
327 my $result = {result => 0, code => 500, data => 'can\'t delete table'};
328  
329 if (my $dbh = $self->sqlite->db->query('DELETE FROM ' . $self->table_name)) {
330 $result->{result} = $dbh->rows;
331 $result->{code} = 200;
332 $result->{data} = '';
333 }
334 return $result;
335 }
336  
337 sub drop_table {
338 my $self = shift;
339 my $result = {result => 0, code => 500, data => 'can\'t drop table'};
340  
341 if (my $dbh
342 = $self->sqlite->db->query('DROP TABLE IF EXISTS ' . $self->table_name))
343 {
344 $result->{result} = $dbh->rows;
345 $result->{code} = 200;
346 $result->{data} = '';
347 $self->table_ready(0);
348 }
349 return $result;
350 }
351  
352 1;
353  
354 =encoding utf8
355  
356 =head1 NAME
357  
358 Mojo::Hakkefuin::Backend::sqlite - SQLite Backend.
359  
360 =head1 SYNOPSIS
361  
362 use Mojo::Hakkefuin::Backend::sqlite;
363  
364 my $sqlite = Mojo::Hakkefuin:Backend:sqlite->new(
365 dir => 'path/your/dir/migrations'
366 );
367  
368 =head1 DESCRIPTION
369  
370 L<Mojo::Hakkefuin::Backend::sqlite> is a backend for L<Mojolicious::Plugin::Hakkefuin>
371 based on L<Mojo::Hakkefuin::Backend>. All necessary tables will be created automatically.
372  
373 =head1 ATTRIBUTES
374  
375 L<Mojo::Hakkefuin::Backend::sqlite> inherits all attributes from L<Mojo::Hakkefuin::Backend>
376 and implements the following new ones.
377  
378 =head2 dir
379  
380 # Example use as a config
381 my $backend = Mojo::Hakkefuin::Backend::sqlite->new(
382 ...
383 dir => '/path/of/dir/location/file/database/',
384 ...
385 );
386
387 # use as a method
388 my $backend = $backend->dir;
389 $backend->dir('/path/of/dir/location/file/database/');
390  
391 This attribute for specify path (directory address) of directory
392 SQLite database file or migrations configuration file.
393  
394 =head1 METHODS
395  
396 L<Mojo::Hakkefuin::Backend::sqlite> inherits all methods
397 from L<Mojo::Hakkefuin::Backend> and implements the following new ones.
398 In this module contains 2 section methods that is B<Table Interaction> and B<Data Interaction>.
399  
400 =head2 Table Interaction
401  
402 A section for all activities related to interaction data in a database table.
403  
404 =head3 table_query
405  
406 my $table_query = $backend->table_query;
407 $backend->table_query;
408  
409 This method used by the C<create_table> method to generate
410 a query that will be used to create a database table.
411  
412 =head3 check_table
413  
414 $backend->check_table();
415 my $check_table = $backend->check_table;
416
417 This method is used to check whether a table in the DBMS exists or not
418  
419 =head3 create_table
420  
421 $backend->create_table();
422 my $create_table = $backend->create_table;
423  
424 This method is used to create database table.
425  
426 =head3 empty_table
427  
428 $backend->empty_table;
429 my $empty_table = $backend->empty_table;
430
431 This method is used to delete all database table
432 (It means to delete all data in a table).
433  
434 =head3 drop_table
435  
436 $backend->drop_table;
437 my $drop_table = $backend->drop_table;
438  
439 This method is used to drop database table
440 (It means to delete all data in a table and its contents).
441  
442 =head2 Data Interaction
443  
444 A section for all activities related to data interaction in a database table.
445 These options are currently available:
446  
447 =over 2
448  
449 =item $id
450  
451 A variable containing a unique id that is generated when inserting data
452 into a table.
453  
454 =item $identify
455  
456 The variables that contain important information for data login
457 but not crucial information.
458  
459 =item $cookie
460  
461 The variable that contains the cookie hash, which hash is used
462 in the HTTP header.
463  
464 =item $csrf
465  
466 The variable that contains the csrf token hash, which hash is used
467 in the HTTP header.
468  
469 =item $expires
470  
471 The variable that contains timestamp format. e.g. C<2023-03-23 12:01:53>.
472 For more information see L<Mojo::Hakkefuin::Utils>.
473  
474 =back
475  
476 =head3 create($identify, $cookie, $csrf, $expires)
477  
478 $backend->create($identify, $cookie, $csrf, $expires)
479
480 Method for insert data login.
481  
482 =head3 read($identify, $cookie)
483  
484 $backend->read($identify, $cookie);
485  
486 Method for read data login.
487  
488 =head3 update()
489  
490 $backend->update($id, $cookie, $csrf);
491  
492 Method for update data login.
493  
494 =head3 update_csrf()
495  
496 $backend->update_csrf($id, $csrf);
497  
498 Method for update CSRF token login.
499  
500 =head3 update_cookie()
501  
502 $backend->update_cookie($id, $cookie);
503  
504 Method for update cookie login.
505  
506 =head3 upd_coolock()
507  
508 $backend->upd_coolock();
509  
510 Method for update cookie lock session.
511  
512 =head3 upd_lckstate()
513  
514 $backend->upd_lckstate();
515  
516 Method for update lock state condition.
517  
518 =head3 delete($identify, $cookie)
519  
520 $backend->delete($identify, $cookie);
521  
522 Method for delete data login.
523  
524 =head3 check($identify, $cookie)
525  
526 $backend->check($identify, $cookie);
527
528 Method for check data login.
529  
530 =head2 new()
531  
532 use Mojo::Hakkefuin::Backend::sqlite;
533
534 my $backend = Mojo::Hakkefuin::Backend::sqlite->new(
535 dir => 'path/your/dir/file/database'
536 );
537  
538 Construct a new L<Mojo::Hakkefuin::Backend::sqlite> object.
539  
540 =head1 SEE ALSO
541  
542 =over 2
543  
544 =item * L<Mojo::Hakkefuin>
545  
546 =item * L<Mojo::Hakkefuin::Backend>
547  
548 =item * L<Mojolicious::Plugin::Hakkefuin>
549  
550 =item * L<Mojo::SQLite>
551  
552 =back
553  
554 =cut