| 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 |