File: [local] / botnow / SQLite.pm (download)
Revision 1.1.1.1 (vendor branch), Sat May 15 15:12:32 2021 UTC (3 years, 1 month ago) by bountyht
Branch: ircnow, MAIN
CVS Tags: start, HEAD Changes since 1.1: +0 -0 lines
Second import
|
#!/usr/bin/perl
package SQLite;
use strict;
use warnings;
use OpenBSD::Pledge;
use OpenBSD::Unveil;
use Data::Dumper;
use DBI;
use DBD::SQLite;
use constant {
NONE => 0,
ERRORS => 1,
WARNINGS => 2,
ALL => 3,
};
my %conf = %main::conf;
my $staff = $conf{staff};
my $dbh;
my $verbose = $conf{verbose};
my $dbpath = "/var/www/botnow/botnow.db";
my $database = "/var/www/botnow/"; # database path
main::cbind("msg", "-", "get", \&mget);
main::cbind("msg", "-", "set", \&mset);
main::cbind("msg", "-", "connectdb", \&mconnectdb);
main::cbind("msg", "-", "insert", \&minsert);
main::cbind("msg", "-", "update", \&mupdate);
main::cbind("msg", "-", "delete", \&mdelete);
main::cbind("msg", "-", "select", \&mselect);
sub init {
unveil("$dbpath", "rwc") or die "Unable to unveil $!";
unveil("$dbpath-journal", "rwc") or die "Unable to unveil $!";
unveil("$database", "rwxc") or die "Unable to unveil $!";
}
# !connectdb
sub mconnectdb {
my ($bot, $nick, $host, $hand, $text) = @_;
if (! (main::isstaff($bot, $nick))) { return; }
if (connectdb()) {
main::putserv($bot, "PRIVMSG $nick :connectdb succeeded");
} else {
main::putserv($bot, "PRIVMSG $nick :ERROR: connectdb failed");
}
}
# !insert <table> <keys> <vals>
# Insert comma-separated keys and vals into table
sub minsert {
my ($bot, $nick, $host, $hand, $text) = @_;
if (! (main::isstaff($bot, $nick))) { return; }
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+([[:ascii:]]+)/) {
my ($table, $keys, $vals) = ($1, $2, $3);
# strings in the values must be quoted
if ($vals =~ s{,}{","}g) { $vals = '"'.$vals.'"'; }
if (insertrow($table, $keys, $vals)) {
main::putserv($bot, "PRIVMSG $nick :$table ($keys) => ($vals)");
} else {
main::putserv($bot, "PRIVMSG $nick :$table insert failed");
}
} else {
main::putserv($bot, "PRIVMSG $nick :invalid insert");
}
}
# Set key = val where idkey = idval in table
# !update <table> <idkey> <idval> <key> <val>
sub mupdate {
my ($bot, $nick, $host, $hand, $text) = @_;
if (! (main::isstaff($bot, $nick))) { return; }
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+(\S+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
my ($table, $idkey, $idval, $key, $val) = ($1, $2, $3, $4, $5);
if (updaterow($table, $idkey, $idval, $key, $val)) {
main::putserv($bot, "PRIVMSG $nick :$table $key => $val where $idkey = $idval");
} else {
main::putserv($bot, "PRIVMSG $nick :update failed");
}
} else {
main::putserv($bot, "PRIVMSG $nick :invalid update");
}
}
# Delete rows where key = val in table
# !delete <table> <key> <val>
sub mdelete {
my ($bot, $nick, $host, $hand, $text) = @_;
if (! (main::isstaff($bot, $nick))) { return; }
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
my ($table, $key, $val) = ($1, $2, $3);
if (deleterows($table, $key, $val)) {
main::putserv($bot, "PRIVMSG $nick :$table $key = $val deleted");
} else {
main::putserv($bot, "PRIVMSG $nick :delete failed");
}
} else {
main::putserv($bot, "PRIVMSG $nick :invalid delete");
}
}
# Output rows where key = val in table
# !select <table> <key> <val>
sub mselect {
my ($bot, $nick, $host, $hand, $text) = @_;
if (! (main::isstaff($bot, $nick))) { return; }
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
my ($table, $key, $val) = ($1, $2, $3);
my @rows = selectrows($table, $key, $val);
if (@rows) {
foreach my $row (@rows) {
my @pairs;
foreach $key (keys %$row) {
my $val = $row->{$key} || "";
push(@pairs, "$key => $val");
}
main::putserv($bot, "PRIVMSG $nick :$table ".join(',', @pairs));
}
} else {
main::putserv($bot, "PRIVMSG $nick :no results");
}
} else {
main::putserv($bot, "PRIVMSG $nick :select invalid");
}
}
# Get value of key where idkey = idval in table
# !get <table> <idkey> <idval> <key>
sub mget {
my ($bot, $nick, $host, $hand, $text) = @_;
if (! (main::isstaff($bot, $nick))) { return; }
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+(\S+)\s+([-_[:alnum:]]+)/) {
my ($table, $idkey, $idval, $key) = ($1, $2, $3, $4);
my $val = get($table, $idkey, $idval, $key);
if (defined($val)) {
main::putserv($bot, "PRIVMSG $nick :$table $key => $val where $idkey = $idval");
} else {
main::putserv($bot, "PRIVMSG $nick :undefined");
}
} else {
main::putserv($bot, "PRIVMSG $nick :invalid get");
}
}
# !set <table> <idkey> <idval> <key> <val>
sub mset {
my ($bot, $nick, $host, $hand, $text) = @_;
if (! (main::isstaff($bot, $nick))) { return; }
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+(\S+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
my ($table, $idkey, $idval, $key, $val) = ($1, $2, $3, $4, $5);
if (set($table, $idkey, $idval, $key, $val)) {
main::putserv($bot, "PRIVMSG $nick :$table $key => $val where $idkey = $idval");
} else {
main::putserv($bot, "PRIVMSG $nick :failed set");
}
} else {
main::putserv($bot, "PRIVMSG $nick :invalid set");
}
}
# Connect to database, creating table if necessary
# Returns true on success, false on failure
sub connectdb {
my $dsn = "dbi:SQLite:dbname=$dbpath";
my $user = "";
my $password = "";
$dbh = DBI->connect($dsn, $user, $password, {
PrintError => 1,
RaiseError => 1,
AutoCommit => 1,
FetchHashKeyName => 'NAME_lc',
}) or die "Couldn't connect to database: " . $DBI::errstr;
if (!(-s "$dbpath")) {
my $sql = main::readstr('table.sql');
my @sql = split /;/m, $sql;
foreach my $s (@sql) {
$dbh->do($s);
}
}
main::debug(ALL, "connected to $dbpath");
return defined($dbh);
}
# Inserts comma-separated keys and vals into table
# Returns number of rows successfully inserted
sub insertrow {
my ($table, $keys, $vals) = @_;
if (!defined($dbh)) { connectdb(); }
my $rows = $dbh->do("INSERT INTO $table ($keys) values ($vals)");
if ($rows) {
main::debug(ALL, "INSERT INTO $table ($keys) values ($vals)");
} else {
main::debug(ERRORS, "ERRORS: Failed INSERT INTO $table ($keys) values ($vals)");
}
return $rows;
}
# Update key, value pair for record where idkey equals idval in table
# Returns number of rows successfully updated
sub updaterow {
my ($table, $idkey, $idval, $key, $val) = @_;
if (!defined($dbh)) { connectdb(); }
my $rows = $dbh->do("UPDATE $table SET $key = ? where $idkey = ?", undef, $val, $idval);
if ($rows) {
main::debug(ALL, "UPDATE $table SET $key = $val where $idkey = $idval");
} else {
main::debug(ERRORS, "ERRORS: Failed UPDATE $table SET $key = $val where $idkey = $idval");
}
return $rows;
}
# Delete records from $table where $key = $val
# Returns number of rows deleted
sub deleterows {
my ($table, $key, $val) = @_;
if (!defined($dbh)) { connectdb(); }
my $rows = $dbh->do("DELETE FROM $table WHERE $key = ?", undef, $val);
if ($rows) {
main::debug(ALL, "DELETE FROM $table WHERE $key = $val");
} else {
main::debug(ERRORS, "ERRORS: Failed DELETE FROM $table WHERE $key = $val");
}
return $rows;
}
# Returns all records in the database
sub selectall {
my ($table) = @_;
if (!defined($dbh)) { connectdb(); }
my $sth = $dbh->prepare("SELECT * FROM $table");
$sth->execute();
my @results;
while (my $row = $sth->fetchrow_hashref) {
push(@results, $row);
}
return @results;
}
# Returns all records from table where key equals value
sub selectrows {
my ($table, $key, $val) = @_;
if (!defined($dbh)) { connectdb(); }
my $sth = $dbh->prepare("SELECT * FROM $table WHERE $key = ?");
$sth->execute($val);
my @results;
while (my $row = $sth->fetchrow_hashref) {
push(@results, $row);
}
return @results;
}
# Returns list of tables
sub tables {
# if (!defined($dbh)) { connectdb(); }
# my $sth = $dbh->prepare(".tables");
# $sth->execute($val);
# my @results;
# while (my $row = $sth->fetchrow_hashref) {
# push(@results, $row);
# }
# return @results;
return qw(bnc shell www irc smtp);
}
# Returns value of key in record in table where idkey = idval
sub get {
my ($table, $idkey, $idval, $key) = @_;
if (!defined($dbh)) { connectdb(); }
my $sth = $dbh->prepare("SELECT * FROM $table WHERE $idkey = ?");
$sth->execute($idval);
if (my $row = $sth->fetchrow_hashref) {
my $val = $row->{$key};
if (!defined($val)) { $val = "undefined"; }
main::debug(ALL, "get: $table $key => $val where $idkey = $idval");
return $row->{$key};
} else {
main::debug(ERRORS, "ERRORS: $table $key undefined where $idkey = $idval");
return;
}
}
# Sets value of key in the record in table where idkey = idval
# Returns true on success; false on failure
sub set {
my ($table, $idkey, $idval, $key, $val) = @_;
if (defined(get($table, $idkey, $idval, $idkey))) {
main::debug(ALL, "set: update");
return updaterow($table, $idkey, $idval, $key, $val) > 0;
} else {
main::debug(ALL, "set: insert");
return insertrow($table, "$idkey,$key", "\"$idval\",\"$val\"") > 0;
}
}
# given a key, val pair in table, return the id that falls within expires seconds
sub id {
my ($table, $key, $val, $expires) = @_;
my @rows = selectrows($table, $key, $val);
if (scalar(@rows) == 0) {
print "table => $table, key => $key, val => $val\n\n";
}
my $maxrow;
foreach my $row (@rows) {
if (!defined($maxrow)) { $maxrow = $row; }
if ($row->{localtime} > $maxrow->{localtime}) {
$maxrow = $row;
}
}
if (abs(time() - $maxrow->{localtime}) <= $expires) {
main::debug(ALL, "id: $maxrow->{id} where $key = $val at $expires");
return $maxrow->{id};
} else {
main::debug(ERRORS, "no id found");
return;
}
}
1; # MUST BE LAST STATEMENT IN FILE