Mojo/Hakkefuin/Backend/mariadb.pm untested

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