Return to SQLite.pm CVS log

Up to [local] / botnow

Initial revision

#!/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