Mojo/Hakkefuin/Backend/pg.pm untested

filter: Statementstmt 0.0% 0 / 195 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 / 334 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::pg;
2 use Mojo::Base 'Mojo::Hakkefuin::Backend';
3  
4 use Mojo::Loader 'load_class';
5 use CellBIS::SQL::Abstract;
6  
7 has 'pg';
8 has 'file_migration';
9 has abstract => sub { 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_pg.sql');
16 my $class = 'Mojo::Pg';
17 load_class $class;
18 $self->pg($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(
28 'information_schema.tables',
29 ['table_name'],
30 {
31 where =>
32 'table_type=\'BASE TABLE\' AND table_schema=\'public\' AND table_name=\''
33 . $self->table_name . '\''
34 }
35 );
36 if (my $dbh = $self->pg->db->query($q)) {
37 $result->{result} = $dbh->hash;
38 $result->{code} = 200;
39 eval { $self->_ensure_indexes };
40 }
41  
42 return $result;
43 }
44  
45 sub create_table {
46 my $self = shift;
47 my $table_query = $self->table_query;
48  
49 my $result = {result => 0, code => 400};
50  
51 if (my $dbh = $self->pg->db->query($table_query)) {
52 $result->{result} = $dbh->rows;
53 $result->{code} = 200;
54 eval { $self->_ensure_indexes };
55 }
56 return $result;
57 }
58  
59 sub _ensure_indexes {
60 my $self = shift;
61  
62 my $table = $self->table_name;
63 my @idx_sql = (
64 'CREATE INDEX IF NOT EXISTS idx_'
65 . $table
66 . '_identify ON '
67 . $table . ' ('
68 . $self->identify . ')',
69 'CREATE INDEX IF NOT EXISTS idx_'
70 . $table
71 . '_cookie ON '
72 . $table . ' ('
73 . $self->cookie . ')',
74 'CREATE INDEX IF NOT EXISTS idx_'
75 . $table
76 . '_expire_date ON '
77 . $table . ' ('
78 . $self->expire_date . ')',
79 );
80  
81 for my $sql (@idx_sql) {
82 eval { $self->pg->db->query($sql) };
83 }
84 }
85  
86 sub table_query {
87 my $self = shift;
88  
89 $self->abstract->new(db_type => 'pg')->create_table(
90 $self->table_name,
91 [
92 $self->id, $self->identify, $self->cookie,
93 $self->csrf, $self->create_date, $self->expire_date,
94 $self->cookie_lock, $self->lock
95 ],
96 {
97 $self->id => {type => {name => 'bigserial'}, is_primarykey => 1},
98 $self->identify => {type => {name => 'text'}},
99 $self->cookie => {type => {name => 'text'}},
100 $self->csrf => {type => {name => 'text'}},
101 $self->create_date => {type => {name => 'TIMESTAMP'}},
102 $self->expire_date => {type => {name => 'TIMESTAMP'}},
103 $self->cookie_lock =>
104 {type => {name => 'text'}, 'default' => '\'no_lock\'', is_null => 1},
105 $self->lock => {type => {name => 'int'}}
106 }
107 );
108 }
109  
110 sub create {
111 my ($self, $identify, $cookie, $csrf, $expires) = @_;
112  
113 return {result => 0, code => 500, data => $cookie} unless $self->_table_ok;
114  
115 my $result = {result => 0, code => 400, data => $cookie};
116  
117 my $mhf_utils = $self->mhf_util->new;
118 my $now_time = $mhf_utils->sql_datetime(0);
119 my $expire_time = $mhf_utils->sql_datetime($expires);
120  
121 my @q = (
122 $self->table_name,
123 {
124 $self->identify => $identify,
125 $self->cookie => $cookie,
126 $self->csrf => $csrf,
127 $self->create_date => $now_time,
128 $self->expire_date => $expire_time,
129 $self->cookie_lock => 'no_lock',
130 $self->lock => 0
131 }
132 );
133 if (my $dbh = $self->pg->db->insert(@q)) {
134 $result->{result} = $dbh->rows;
135 $result->{code} = 200;
136 }
137 return $result;
138 }
139  
140 sub read {
141 my ($self, $identify, $cookie) = @_;
142  
143 $identify //= 'null';
144 $cookie //= 'null';
145  
146 return {result => 0, code => 500, data => $cookie} unless $self->_table_ok;
147  
148 my $result = {result => 0, code => 400, data => $cookie};
149 my @q = (
150 $self->table_name, '*',
151 {$self->identify => $identify, $self->cookie => $cookie}
152 );
153 if (my $dbh = $self->pg->db->select(@q)) {
154 $result->{result} = 1;
155 $result->{code} = 200;
156 $result->{data} = $dbh->hash;
157 }
158 return $result;
159 }
160  
161 sub update {
162 my ($self, $id, $cookie, $csrf) = @_;
163  
164 my $mhf_utils = $self->mhf_util->new;
165 my $now_time = $mhf_utils->sql_datetime(0);
166  
167 return {result => 0, code => 500, csrf => $csrf, cookie => $cookie}
168 unless $self->_table_ok;
169  
170 my $result = {result => 0, code => 400, csrf => $csrf, cookie => $cookie};
171 return $result unless $id && $csrf;
172  
173 my @q = (
174 $self->table_name,
175 {$self->cookie, => $cookie, $self->csrf => $csrf},
176 {$self->id => $id, $self->expire_date => {'>=', $now_time}}
177 );
178 if (my $dbh = $self->pg->db->update(@q)) {
179 $result->{result} = $dbh->rows;
180 $result->{code} = 200;
181 }
182 return $result;
183 }
184  
185 sub update_csrf {
186 my ($self, $id, $csrf) = @_;
187  
188 my $mhf_utils = $self->mhf_util->new;
189 my $now_time = $mhf_utils->sql_datetime(0);
190  
191 return {result => 0, code => 500, data => $csrf} unless $self->_table_ok;
192  
193 my $result = {result => 0, code => 400, data => $csrf};
194 return $result unless $id && $csrf;
195  
196 my @q = (
197 $self->table_name,
198 {$self->csrf => $csrf},
199 {$self->id => $id, $self->expire_date => {'>=', $now_time}}
200 );
201 if (my $dbh = $self->pg->db->update(@q)) {
202 $result->{result} = $dbh->rows;
203 $result->{code} = 200;
204 }
205 return $result;
206 }
207  
208 sub update_cookie {
209 my ($self, $id, $cookie) = @_;
210  
211 my $mhf_utils = $self->mhf_util->new;
212 my $now_time = $mhf_utils->sql_datetime(0);
213  
214 return {result => 0, code => 500, data => $cookie} unless $self->_table_ok;
215  
216 my $result = {result => 0, code => 400, data => $cookie};
217 return $result unless $id && $cookie;
218  
219 my @q = (
220 $self->table_name,
221 {$self->cookie => $cookie},
222 {$self->id => $id, $self->expire_date => {'>=', $now_time}}
223 );
224 if (my $dbh = $self->pg->db->update(@q)) {
225 $result->{result} = $dbh->rows;
226 $result->{code} = 200;
227 }
228 return $result;
229 }
230  
231 sub upd_coolock {
232 my ($self, $id, $cookie_lock) = @_;
233  
234 my $mhf_utils = $self->mhf_util->new;
235 my $now_time = $mhf_utils->sql_datetime(0);
236  
237 return {result => 0, code => 500, data => $cookie_lock}
238 unless $self->_table_ok;
239  
240 my $result = {result => 0, code => 400, data => $cookie_lock};
241 return $result unless $id && defined $cookie_lock;
242  
243 my @q = (
244 $self->table_name,
245 {$self->cookie_lock => $cookie_lock},
246 {$self->id => $id, $self->expire_date => {'>=', $now_time}}
247 );
248 if (my $dbh = $self->pg->db->update(@q)) {
249 $result->{result} = $dbh->rows;
250 $result->{code} = 200;
251 }
252 return $result;
253 }
254  
255 sub upd_lckstate {
256 my ($self, $id, $state) = @_;
257  
258 my $mhf_utils = $self->mhf_util->new;
259 my $now_time = $mhf_utils->sql_datetime(0);
260  
261 return {result => 0, code => 500, data => $state} unless $self->_table_ok;
262  
263 my $result = {result => 0, code => 400, data => $state};
264 return $result unless $id && defined $state;
265  
266 my @q = (
267 $self->table_name,
268 {$self->lock => $state},
269 {$self->id => $id, $self->expire_date => {'>=', $now_time}}
270 );
271 if (my $dbh = $self->pg->db->update(@q)) {
272 $result->{result} = $dbh->rows;
273 $result->{code} = 200;
274 }
275 return $result;
276 }
277  
278 sub delete {
279 my ($self, $identify, $cookie) = @_;
280  
281 return {result => 0, code => 500, data => $cookie} unless $self->_table_ok;
282  
283 my $result = {result => 0, code => 400, data => $cookie};
284 return $result unless $identify && $cookie;
285  
286 my @q = (
287 $self->table_name, {$self->identify => $identify, $self->cookie => $cookie}
288 );
289 if (my $dbh = $self->pg->db->delete(@q)) {
290 $result->{result} = $dbh->rows;
291 $result->{code} = 200;
292 }
293 return $result;
294 }
295  
296 sub check {
297 my ($self, $identify, $cookie) = @_;
298  
299 my $mhf_utils = $self->mhf_util->new;
300 my $now_time = $mhf_utils->sql_datetime(0);
301  
302 return {result => 0, code => 500, data => $cookie} unless $self->_table_ok;
303  
304 my $result = {result => 0, code => 400, data => $cookie};
305 return $result unless $identify && $cookie;
306  
307 my @q = (
308 $self->table_name,
309 '*',
310 {
311 -or => {$self->identify => $identify, $self->cookie => $cookie},
312 $self->expire_date => {'>', '\'NOW()'}
313 }
314 );
315 if (my $rv = $self->pg->db->select(@q)) {
316 my $r_data = $rv->hash;
317 $result = {
318 result => 1,
319 code => 200,
320 data => {
321 cookie => $cookie,
322 id => $r_data->{$self->id},
323 csrf => $r_data->{$self->csrf},
324 identify => $r_data->{$self->identify},
325 cookie_lock => $r_data->{$self->cookie_lock},
326 lock => $r_data->{$self->lock}
327 }
328 };
329 }
330 return $result;
331 }
332  
333 sub empty_table {
334 my $self = shift;
335 my $result = {result => 0, code => 500, data => 'can\'t delete table'};
336  
337 if (my $dbh = $self->pg->db->query('DELETE FROM ' . $self->table_name)) {
338 $result->{result} = $dbh->rows;
339 $result->{code} = 200;
340 $result->{data} = '';
341 }
342 return $result;
343 }
344  
345 sub drop_table {
346 my $self = shift;
347 my $result = {result => 0, code => 500, data => 'can\'t drop table'};
348  
349 if (my $dbh
350 = $self->pg->db->query('DROP TABLE IF EXISTS ' . $self->table_name))
351 {
352 $result->{result} = $dbh->rows;
353 $result->{code} = 200;
354 $result->{data} = '';
355 $self->table_ready(0);
356 }
357 return $result;
358 }
359  
360 1;
361  
362 =encoding utf8
363  
364 =head1 NAME
365  
366 Mojo::Hakkefuin::Backend::pg - PostgreSQL Backend.
367  
368 =head1 SYNOPSIS
369  
370 use Mojo::Hakkefuin::Backend::pg;
371
372 my $backend = Mojo::Hakkefuin::Backend::pg->new(
373 dir => 'path/your/dir/migrations',
374 dsn => 'postgresql://username:password@hostname/database'
375 );
376
377 # use as a method
378 my $backend = $backend->dsn;
379 $backend->dsn('postgresql://username:password@hostname:port/database');
380  
381 =head1 DESCRIPTION
382  
383 L<Mojo::Hakkefuin::Backend::pg> is a backend for L<Mojolicious::Plugin::Hakkefuin>
384 based on L<Mojo::Hakkefuin::Backend>. All necessary tables will be created automatically.
385  
386 =head1 ATTRIBUTES
387  
388 L<Mojo::Hakkefuin::Backend::pg> inherits all attributes from L<Mojo::Hakkefuin::Backend>
389 and implements the following new ones.
390  
391 =head2 dsn
392
393 # Example use as a config
394 my $backend = Mojo::Hakkefuin::Backend::pg->new(
395 ...
396 dsn => 'postgresql://username:password@hostname:port/database',
397 ...
398 );
399
400 # use as a method
401 my $backend = $backend->dsn;
402 $backend->dsn('postgresql://username:password@hostname:port/database');
403  
404 =head2 dir
405  
406 # Example use as a config
407 my $backend = Mojo::Hakkefuin::Backend::pg->new(
408 ...
409 dir => 'path/your/dir/migrations',
410 ...
411 );
412
413 # use as a method
414 my $backend = $backend->dir;
415 $backend->dir('path/your/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::pg> 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::pg;
558
559 my $backend = Mojo::Hakkefuin::Backend::pg->new(
560 dir => 'path/your/dir/migrations',
561 dsn => 'postgresql://username:password@hostname:port/database'
562 );
563  
564 Construct a new L<Mojo::Hakkefuin::Backend::pg> 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::Pg>
577  
578 =back
579  
580 =cut