package DB; use strict; use warnings; use WardenApp::Constants; use constant DB_ENGINE_MYSQL => 'mysql'; use constant DB_ENGINE_SQLITE => 'sqlite'; use constant DB_SECTION => 'db'; use DBI; sub connectDB { my ($cfg, $db_engine) = @_; my $dbh; if(lc $db_engine eq DB_ENGINE_MYSQL) { my $db = Constants::assignValue('MYSQL.db', $cfg, DB_SECTION); my $host = Constants::assignValue('MYSQL.host', $cfg, DB_SECTION); my $user = Constants::assignValue('MYSQL.user', $cfg, DB_SECTION); my $pass = Constants::assignValue('MYSQL.pass', $cfg, DB_SECTION); my $port = Constants::assignValue('MYSQL.port', $cfg, DB_SECTION); $dbh = DBI->connect("DBI:mysql:host=" . $host . ";port=" . $port . ";database=" . $db, $user, $pass, {RaiseError => 0,AutoCommit => 0}) || die "Database connection not made: $DBI::errstr"; } elsif (lc $db_engine eq DB_ENGINE_SQLITE) { my $db = Constants::assignValue('SQLITE.db', $cfg, DB_SECTION); my $user = Constants::assignValue('SQLITE.user', $cfg, DB_SECTION); my $pass = Constants::assignValue('SQLITE.pass', $cfg, DB_SECTION); $dbh = DBI->connect("DBI:SQLite:" . $db, $user, $pass, {RaiseError => 0,AutoCommit => 1}) || die "Database connection not made: $DBI::errstr"; } return \$dbh; } sub getOldDataDB { my ($db_engine, $expr, $maxage) = @_; my ($num, $word) = $maxage =~ /(\d+)([dmhDMH])/; my ($word_long, $word_desc); $word_long = "HOUR" if $word =~ /[hH]/; $word_long = "DAY" if $word =~ /[dD]/; $word_long = "MONTH" if $word =~ /[mM]/; my $c; $c = "<" if($expr eq "OLDER"); $c = ">" if($expr eq "NEWER"); if($db_engine eq DB_ENGINE_MYSQL) { return sprintf("detected %s DATE_SUB(NOW(), INTERVAL %d %s)", $c, $num, $word_long); } if($db_engine eq DB_ENGINE_SQLITE) { return sprintf("datetime(detected) %s datetime('now','-%d %s')", $c, $num, $word_long); } return ""; } sub closeDB { my $dbh = shift; $$dbh->disconnect; } sub getQueryCondThreshold { my ($db_engine, $table, $columns, $params, $threshold) = @_; my $columns_q = join ", ", @$columns; my $params_q = join " AND ", grep { $_ } @$params; return sprintf("SELECT %s FROM %s WHERE %s GROUP BY source HAVING COUNT(id) > %s", $columns_q, $table, $params_q, $threshold); } sub joinIN { my ($column, $data) = @_; return (@$data ? sprintf("%s IN (%s) AND ", $column, join ",", map { "'$_'" } @$data) : ""); } sub joinNotIN { my ($column, $data) = @_; return (@$data ? sprintf("%s NOT IN (%s) AND ", $column, join ",", map { "'$_'" } @$data) : ""); } sub joinLIKE { my ($column, $data) = @_; my $ret = (@$data ? sprintf("%s", join ",", map { "$column LIKE '$_%' OR " } @$data) : ""); return ($ret ne "" ? substr($ret, 0, -4) . " AND " : ""); } 1;