1 84c190b6 2021-12-17 jrmu #!/usr/bin/perl
3 84c190b6 2021-12-17 jrmu package SQLite;
6 84c190b6 2021-12-17 jrmu use warnings;
7 84c190b6 2021-12-17 jrmu use OpenBSD::Pledge;
8 84c190b6 2021-12-17 jrmu use OpenBSD::Unveil;
9 84c190b6 2021-12-17 jrmu use Data::Dumper;
11 84c190b6 2021-12-17 jrmu use DBD::SQLite;
13 84c190b6 2021-12-17 jrmu use constant {
15 84c190b6 2021-12-17 jrmu ERRORS => 1,
16 84c190b6 2021-12-17 jrmu WARNINGS => 2,
19 84c190b6 2021-12-17 jrmu my %conf = %main::conf;
20 84c190b6 2021-12-17 jrmu my $staff = $conf{staff};
22 84c190b6 2021-12-17 jrmu my $verbose = $conf{verbose};
23 84c190b6 2021-12-17 jrmu my $dbpath = "/var/www/botnow/botnow.db";
24 84c190b6 2021-12-17 jrmu my $database = "/var/www/botnow/"; # database path
25 84c190b6 2021-12-17 jrmu main::cbind("msg", "-", "get", \&mget);
26 84c190b6 2021-12-17 jrmu main::cbind("msg", "-", "set", \&mset);
27 84c190b6 2021-12-17 jrmu main::cbind("msg", "-", "connectdb", \&mconnectdb);
28 84c190b6 2021-12-17 jrmu main::cbind("msg", "-", "insert", \&minsert);
29 84c190b6 2021-12-17 jrmu main::cbind("msg", "-", "update", \&mupdate);
30 84c190b6 2021-12-17 jrmu main::cbind("msg", "-", "delete", \&mdelete);
31 84c190b6 2021-12-17 jrmu main::cbind("msg", "-", "select", \&mselect);
34 84c190b6 2021-12-17 jrmu unveil("$dbpath", "rwc") or die "Unable to unveil $!";
35 84c190b6 2021-12-17 jrmu unveil("$dbpath-journal", "rwc") or die "Unable to unveil $!";
36 84c190b6 2021-12-17 jrmu unveil("$database", "rwxc") or die "Unable to unveil $!";
39 84c190b6 2021-12-17 jrmu # !connectdb
40 84c190b6 2021-12-17 jrmu sub mconnectdb {
41 84c190b6 2021-12-17 jrmu my ($bot, $nick, $host, $hand, $text) = @_;
42 84c190b6 2021-12-17 jrmu if (! (main::isstaff($bot, $nick))) { return; }
43 84c190b6 2021-12-17 jrmu if (connectdb()) {
44 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :connectdb succeeded");
46 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :ERROR: connectdb failed");
50 84c190b6 2021-12-17 jrmu # !insert <table> <keys> <vals>
51 84c190b6 2021-12-17 jrmu # Insert comma-separated keys and vals into table
52 84c190b6 2021-12-17 jrmu sub minsert {
53 84c190b6 2021-12-17 jrmu my ($bot, $nick, $host, $hand, $text) = @_;
54 84c190b6 2021-12-17 jrmu if (! (main::isstaff($bot, $nick))) { return; }
55 84c190b6 2021-12-17 jrmu if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+([[:ascii:]]+)/) {
56 84c190b6 2021-12-17 jrmu my ($table, $keys, $vals) = ($1, $2, $3);
57 84c190b6 2021-12-17 jrmu # strings in the values must be quoted
58 84c190b6 2021-12-17 jrmu if ($vals =~ s{,}{","}g) { $vals = '"'.$vals.'"'; }
59 84c190b6 2021-12-17 jrmu if (insertrow($table, $keys, $vals)) {
60 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :$table ($keys) => ($vals)");
62 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :$table insert failed");
65 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :invalid insert");
69 84c190b6 2021-12-17 jrmu # Set key = val where idkey = idval in table
70 84c190b6 2021-12-17 jrmu # !update <table> <idkey> <idval> <key> <val>
71 84c190b6 2021-12-17 jrmu sub mupdate {
72 84c190b6 2021-12-17 jrmu my ($bot, $nick, $host, $hand, $text) = @_;
73 84c190b6 2021-12-17 jrmu if (! (main::isstaff($bot, $nick))) { return; }
74 84c190b6 2021-12-17 jrmu if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+(\S+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
75 84c190b6 2021-12-17 jrmu my ($table, $idkey, $idval, $key, $val) = ($1, $2, $3, $4, $5);
76 84c190b6 2021-12-17 jrmu if (updaterow($table, $idkey, $idval, $key, $val)) {
77 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :$table $key => $val where $idkey = $idval");
79 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :update failed");
82 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :invalid update");
86 84c190b6 2021-12-17 jrmu # Delete rows where key = val in table
87 84c190b6 2021-12-17 jrmu # !delete <table> <key> <val>
88 84c190b6 2021-12-17 jrmu sub mdelete {
89 84c190b6 2021-12-17 jrmu my ($bot, $nick, $host, $hand, $text) = @_;
90 84c190b6 2021-12-17 jrmu if (! (main::isstaff($bot, $nick))) { return; }
91 84c190b6 2021-12-17 jrmu if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
92 84c190b6 2021-12-17 jrmu my ($table, $key, $val) = ($1, $2, $3);
93 84c190b6 2021-12-17 jrmu if (deleterows($table, $key, $val)) {
94 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :$table $key = $val deleted");
96 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :delete failed");
99 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :invalid delete");
103 84c190b6 2021-12-17 jrmu # Output rows where key = val in table
104 84c190b6 2021-12-17 jrmu # !select <table> <key> <val>
105 84c190b6 2021-12-17 jrmu sub mselect {
106 84c190b6 2021-12-17 jrmu my ($bot, $nick, $host, $hand, $text) = @_;
107 84c190b6 2021-12-17 jrmu if (! (main::isstaff($bot, $nick))) { return; }
108 84c190b6 2021-12-17 jrmu if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
109 84c190b6 2021-12-17 jrmu my ($table, $key, $val) = ($1, $2, $3);
110 84c190b6 2021-12-17 jrmu my @rows = selectrows($table, $key, $val);
111 84c190b6 2021-12-17 jrmu if (@rows) {
112 84c190b6 2021-12-17 jrmu foreach my $row (@rows) {
114 84c190b6 2021-12-17 jrmu foreach $key (keys %$row) {
115 84c190b6 2021-12-17 jrmu my $val = $row->{$key} || "";
116 84c190b6 2021-12-17 jrmu push(@pairs, "$key => $val");
118 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :$table ".join(',', @pairs));
121 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :no results");
124 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :select invalid");
128 84c190b6 2021-12-17 jrmu # Get value of key where idkey = idval in table
129 84c190b6 2021-12-17 jrmu # !get <table> <idkey> <idval> <key>
131 84c190b6 2021-12-17 jrmu my ($bot, $nick, $host, $hand, $text) = @_;
132 84c190b6 2021-12-17 jrmu if (! (main::isstaff($bot, $nick))) { return; }
133 84c190b6 2021-12-17 jrmu if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+(\S+)\s+([-_[:alnum:]]+)/) {
134 84c190b6 2021-12-17 jrmu my ($table, $idkey, $idval, $key) = ($1, $2, $3, $4);
135 84c190b6 2021-12-17 jrmu my $val = get($table, $idkey, $idval, $key);
136 84c190b6 2021-12-17 jrmu if (defined($val)) {
137 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :$table $key => $val where $idkey = $idval");
139 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :undefined");
142 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :invalid get");
145 84c190b6 2021-12-17 jrmu # !set <table> <idkey> <idval> <key> <val>
147 84c190b6 2021-12-17 jrmu my ($bot, $nick, $host, $hand, $text) = @_;
148 84c190b6 2021-12-17 jrmu if (! (main::isstaff($bot, $nick))) { return; }
149 84c190b6 2021-12-17 jrmu if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+(\S+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
150 84c190b6 2021-12-17 jrmu my ($table, $idkey, $idval, $key, $val) = ($1, $2, $3, $4, $5);
151 84c190b6 2021-12-17 jrmu if (set($table, $idkey, $idval, $key, $val)) {
152 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :$table $key => $val where $idkey = $idval");
154 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :failed set");
157 84c190b6 2021-12-17 jrmu main::putserv($bot, "PRIVMSG $nick :invalid set");
161 84c190b6 2021-12-17 jrmu # Connect to database, creating table if necessary
162 84c190b6 2021-12-17 jrmu # Returns true on success, false on failure
163 84c190b6 2021-12-17 jrmu sub connectdb {
164 84c190b6 2021-12-17 jrmu my $dsn = "dbi:SQLite:dbname=$dbpath";
165 84c190b6 2021-12-17 jrmu my $user = "";
166 84c190b6 2021-12-17 jrmu my $password = "";
167 84c190b6 2021-12-17 jrmu $dbh = DBI->connect($dsn, $user, $password, {
168 84c190b6 2021-12-17 jrmu PrintError => 1,
169 84c190b6 2021-12-17 jrmu RaiseError => 1,
170 84c190b6 2021-12-17 jrmu AutoCommit => 1,
171 84c190b6 2021-12-17 jrmu FetchHashKeyName => 'NAME_lc',
172 84c190b6 2021-12-17 jrmu }) or die "Couldn't connect to database: " . $DBI::errstr;
173 84c190b6 2021-12-17 jrmu if (!(-s "$dbpath")) {
174 84c190b6 2021-12-17 jrmu my $sql = main::readstr('table.sql');
175 84c190b6 2021-12-17 jrmu my @sql = split /;/m, $sql;
176 84c190b6 2021-12-17 jrmu foreach my $s (@sql) {
177 84c190b6 2021-12-17 jrmu $dbh->do($s);
180 84c190b6 2021-12-17 jrmu main::debug(ALL, "connected to $dbpath");
181 84c190b6 2021-12-17 jrmu return defined($dbh);
184 84c190b6 2021-12-17 jrmu # Inserts comma-separated keys and vals into table
185 84c190b6 2021-12-17 jrmu # Returns number of rows successfully inserted
186 84c190b6 2021-12-17 jrmu sub insertrow {
187 84c190b6 2021-12-17 jrmu my ($table, $keys, $vals) = @_;
188 84c190b6 2021-12-17 jrmu if (!defined($dbh)) { connectdb(); }
189 84c190b6 2021-12-17 jrmu my $rows = $dbh->do("INSERT INTO $table ($keys) values ($vals)");
190 84c190b6 2021-12-17 jrmu if ($rows) {
191 84c190b6 2021-12-17 jrmu main::debug(ALL, "INSERT INTO $table ($keys) values ($vals)");
193 84c190b6 2021-12-17 jrmu main::debug(ERRORS, "ERRORS: Failed INSERT INTO $table ($keys) values ($vals)");
195 84c190b6 2021-12-17 jrmu return $rows;
198 84c190b6 2021-12-17 jrmu # Update key, value pair for record where idkey equals idval in table
199 84c190b6 2021-12-17 jrmu # Returns number of rows successfully updated
200 84c190b6 2021-12-17 jrmu sub updaterow {
201 84c190b6 2021-12-17 jrmu my ($table, $idkey, $idval, $key, $val) = @_;
202 84c190b6 2021-12-17 jrmu if (!defined($dbh)) { connectdb(); }
203 84c190b6 2021-12-17 jrmu my $rows = $dbh->do("UPDATE $table SET $key = ? where $idkey = ?", undef, $val, $idval);
204 84c190b6 2021-12-17 jrmu if ($rows) {
205 84c190b6 2021-12-17 jrmu main::debug(ALL, "UPDATE $table SET $key = $val where $idkey = $idval");
207 84c190b6 2021-12-17 jrmu main::debug(ERRORS, "ERRORS: Failed UPDATE $table SET $key = $val where $idkey = $idval");
209 84c190b6 2021-12-17 jrmu return $rows;
212 84c190b6 2021-12-17 jrmu # Delete records from $table where $key = $val
213 84c190b6 2021-12-17 jrmu # Returns number of rows deleted
214 84c190b6 2021-12-17 jrmu sub deleterows {
215 84c190b6 2021-12-17 jrmu my ($table, $key, $val) = @_;
216 84c190b6 2021-12-17 jrmu if (!defined($dbh)) { connectdb(); }
217 84c190b6 2021-12-17 jrmu my $rows = $dbh->do("DELETE FROM $table WHERE $key = ?", undef, $val);
218 84c190b6 2021-12-17 jrmu if ($rows) {
219 84c190b6 2021-12-17 jrmu main::debug(ALL, "DELETE FROM $table WHERE $key = $val");
221 84c190b6 2021-12-17 jrmu main::debug(ERRORS, "ERRORS: Failed DELETE FROM $table WHERE $key = $val");
223 84c190b6 2021-12-17 jrmu return $rows;
226 84c190b6 2021-12-17 jrmu # Returns all records in the database
227 84c190b6 2021-12-17 jrmu sub selectall {
228 84c190b6 2021-12-17 jrmu my ($table) = @_;
229 84c190b6 2021-12-17 jrmu if (!defined($dbh)) { connectdb(); }
230 84c190b6 2021-12-17 jrmu my $sth = $dbh->prepare("SELECT * FROM $table");
231 84c190b6 2021-12-17 jrmu $sth->execute();
232 84c190b6 2021-12-17 jrmu my @results;
233 84c190b6 2021-12-17 jrmu while (my $row = $sth->fetchrow_hashref) {
234 84c190b6 2021-12-17 jrmu push(@results, $row);
236 84c190b6 2021-12-17 jrmu return @results;
239 84c190b6 2021-12-17 jrmu # Returns all records from table where key equals value
240 84c190b6 2021-12-17 jrmu sub selectrows {
241 84c190b6 2021-12-17 jrmu my ($table, $key, $val) = @_;
242 84c190b6 2021-12-17 jrmu if (!defined($dbh)) { connectdb(); }
243 84c190b6 2021-12-17 jrmu my $sth = $dbh->prepare("SELECT * FROM $table WHERE $key = ?");
244 84c190b6 2021-12-17 jrmu $sth->execute($val);
245 84c190b6 2021-12-17 jrmu my @results;
246 84c190b6 2021-12-17 jrmu while (my $row = $sth->fetchrow_hashref) {
247 84c190b6 2021-12-17 jrmu push(@results, $row);
249 84c190b6 2021-12-17 jrmu return @results;
252 84c190b6 2021-12-17 jrmu # Returns list of tables
253 84c190b6 2021-12-17 jrmu sub tables {
254 84c190b6 2021-12-17 jrmu # if (!defined($dbh)) { connectdb(); }
255 84c190b6 2021-12-17 jrmu # my $sth = $dbh->prepare(".tables");
256 84c190b6 2021-12-17 jrmu # $sth->execute($val);
257 84c190b6 2021-12-17 jrmu # my @results;
258 84c190b6 2021-12-17 jrmu # while (my $row = $sth->fetchrow_hashref) {
259 84c190b6 2021-12-17 jrmu # push(@results, $row);
261 84c190b6 2021-12-17 jrmu # return @results;
262 84c190b6 2021-12-17 jrmu return qw(bnc shell www irc smtp);
265 84c190b6 2021-12-17 jrmu # Returns value of key in record in table where idkey = idval
267 84c190b6 2021-12-17 jrmu my ($table, $idkey, $idval, $key) = @_;
268 84c190b6 2021-12-17 jrmu if (!defined($dbh)) { connectdb(); }
269 84c190b6 2021-12-17 jrmu my $sth = $dbh->prepare("SELECT * FROM $table WHERE $idkey = ?");
270 84c190b6 2021-12-17 jrmu $sth->execute($idval);
271 84c190b6 2021-12-17 jrmu if (my $row = $sth->fetchrow_hashref) {
272 84c190b6 2021-12-17 jrmu my $val = $row->{$key};
273 84c190b6 2021-12-17 jrmu if (!defined($val)) { $val = "undefined"; }
274 84c190b6 2021-12-17 jrmu main::debug(ALL, "get: $table $key => $val where $idkey = $idval");
275 84c190b6 2021-12-17 jrmu return $row->{$key};
277 84c190b6 2021-12-17 jrmu main::debug(ERRORS, "ERRORS: $table $key undefined where $idkey = $idval");
282 84c190b6 2021-12-17 jrmu # Sets value of key in the record in table where idkey = idval
283 84c190b6 2021-12-17 jrmu # Returns true on success; false on failure
285 84c190b6 2021-12-17 jrmu my ($table, $idkey, $idval, $key, $val) = @_;
286 84c190b6 2021-12-17 jrmu if (defined(get($table, $idkey, $idval, $idkey))) {
287 84c190b6 2021-12-17 jrmu main::debug(ALL, "set: update");
288 84c190b6 2021-12-17 jrmu return updaterow($table, $idkey, $idval, $key, $val) > 0;
290 84c190b6 2021-12-17 jrmu main::debug(ALL, "set: insert");
291 84c190b6 2021-12-17 jrmu return insertrow($table, "$idkey,$key", "\"$idval\",\"$val\"") > 0;
295 84c190b6 2021-12-17 jrmu # given a key, val pair in table, return the id that falls within expires seconds
297 84c190b6 2021-12-17 jrmu my ($table, $key, $val, $expires) = @_;
298 84c190b6 2021-12-17 jrmu my @rows = selectrows($table, $key, $val);
299 84c190b6 2021-12-17 jrmu if (scalar(@rows) == 0) {
300 84c190b6 2021-12-17 jrmu print "table => $table, key => $key, val => $val\n\n";
302 84c190b6 2021-12-17 jrmu my $maxrow;
303 84c190b6 2021-12-17 jrmu foreach my $row (@rows) {
304 84c190b6 2021-12-17 jrmu if (!defined($maxrow)) { $maxrow = $row; }
305 84c190b6 2021-12-17 jrmu if ($row->{localtime} > $maxrow->{localtime}) {
306 84c190b6 2021-12-17 jrmu $maxrow = $row;
309 84c190b6 2021-12-17 jrmu if (abs(time() - $maxrow->{localtime}) <= $expires) {
310 84c190b6 2021-12-17 jrmu main::debug(ALL, "id: $maxrow->{id} where $key = $val at $expires");
311 84c190b6 2021-12-17 jrmu return $maxrow->{id};
313 84c190b6 2021-12-17 jrmu main::debug(ERRORS, "no id found");
318 84c190b6 2021-12-17 jrmu 1; # MUST BE LAST STATEMENT IN FILE