Blame
Date:
Tue Dec 21 08:25:54 2021 UTC
Message:
Use php 7 due to compatibility issues
001
2021-12-17
jrmu
#!/usr/bin/perl
002
2021-12-17
jrmu
003
2021-12-17
jrmu
package SQLite;
004
2021-12-17
jrmu
005
2021-12-17
jrmu
use strict;
006
2021-12-17
jrmu
use warnings;
007
2021-12-17
jrmu
use OpenBSD::Pledge;
008
2021-12-17
jrmu
use OpenBSD::Unveil;
009
2021-12-17
jrmu
use Data::Dumper;
010
2021-12-17
jrmu
use DBI;
011
2021-12-17
jrmu
use DBD::SQLite;
012
2021-12-17
jrmu
013
2021-12-17
jrmu
use constant {
014
2021-12-17
jrmu
NONE => 0,
015
2021-12-17
jrmu
ERRORS => 1,
016
2021-12-17
jrmu
WARNINGS => 2,
017
2021-12-17
jrmu
ALL => 3,
018
2021-12-17
jrmu
};
019
2021-12-17
jrmu
my %conf = %main::conf;
020
2021-12-17
jrmu
my $staff = $conf{staff};
021
2021-12-17
jrmu
my $dbh;
022
2021-12-17
jrmu
my $verbose = $conf{verbose};
023
2021-12-17
jrmu
my $dbpath = "/var/www/botnow/botnow.db";
024
2021-12-17
jrmu
my $database = "/var/www/botnow/"; # database path
025
2021-12-17
jrmu
main::cbind("msg", "-", "get", \&mget);
026
2021-12-17
jrmu
main::cbind("msg", "-", "set", \&mset);
027
2021-12-17
jrmu
main::cbind("msg", "-", "connectdb", \&mconnectdb);
028
2021-12-17
jrmu
main::cbind("msg", "-", "insert", \&minsert);
029
2021-12-17
jrmu
main::cbind("msg", "-", "update", \&mupdate);
030
2021-12-17
jrmu
main::cbind("msg", "-", "delete", \&mdelete);
031
2021-12-17
jrmu
main::cbind("msg", "-", "select", \&mselect);
032
2021-12-17
jrmu
033
2021-12-17
jrmu
sub init {
034
2021-12-17
jrmu
unveil("$dbpath", "rwc") or die "Unable to unveil $!";
035
2021-12-17
jrmu
unveil("$dbpath-journal", "rwc") or die "Unable to unveil $!";
036
2021-12-17
jrmu
unveil("$database", "rwxc") or die "Unable to unveil $!";
037
2021-12-17
jrmu
}
038
2021-12-17
jrmu
039
2021-12-17
jrmu
# !connectdb
040
2021-12-17
jrmu
sub mconnectdb {
041
2021-12-17
jrmu
my ($bot, $nick, $host, $hand, $text) = @_;
042
2021-12-17
jrmu
if (! (main::isstaff($bot, $nick))) { return; }
043
2021-12-17
jrmu
if (connectdb()) {
044
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :connectdb succeeded");
045
2021-12-17
jrmu
} else {
046
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :ERROR: connectdb failed");
047
2021-12-17
jrmu
}
048
2021-12-17
jrmu
}
049
2021-12-17
jrmu
050
2021-12-17
jrmu
# !insert <table> <keys> <vals>
051
2021-12-17
jrmu
# Insert comma-separated keys and vals into table
052
2021-12-17
jrmu
sub minsert {
053
2021-12-17
jrmu
my ($bot, $nick, $host, $hand, $text) = @_;
054
2021-12-17
jrmu
if (! (main::isstaff($bot, $nick))) { return; }
055
2021-12-17
jrmu
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+([[:ascii:]]+)/) {
056
2021-12-17
jrmu
my ($table, $keys, $vals) = ($1, $2, $3);
057
2021-12-17
jrmu
# strings in the values must be quoted
058
2021-12-17
jrmu
if ($vals =~ s{,}{","}g) { $vals = '"'.$vals.'"'; }
059
2021-12-17
jrmu
if (insertrow($table, $keys, $vals)) {
060
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :$table ($keys) => ($vals)");
061
2021-12-17
jrmu
} else {
062
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :$table insert failed");
063
2021-12-17
jrmu
}
064
2021-12-17
jrmu
} else {
065
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :invalid insert");
066
2021-12-17
jrmu
}
067
2021-12-17
jrmu
}
068
2021-12-17
jrmu
069
2021-12-17
jrmu
# Set key = val where idkey = idval in table
070
2021-12-17
jrmu
# !update <table> <idkey> <idval> <key> <val>
071
2021-12-17
jrmu
sub mupdate {
072
2021-12-17
jrmu
my ($bot, $nick, $host, $hand, $text) = @_;
073
2021-12-17
jrmu
if (! (main::isstaff($bot, $nick))) { return; }
074
2021-12-17
jrmu
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+(\S+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
075
2021-12-17
jrmu
my ($table, $idkey, $idval, $key, $val) = ($1, $2, $3, $4, $5);
076
2021-12-17
jrmu
if (updaterow($table, $idkey, $idval, $key, $val)) {
077
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :$table $key => $val where $idkey = $idval");
078
2021-12-17
jrmu
} else {
079
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :update failed");
080
2021-12-17
jrmu
}
081
2021-12-17
jrmu
} else {
082
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :invalid update");
083
2021-12-17
jrmu
}
084
2021-12-17
jrmu
}
085
2021-12-17
jrmu
086
2021-12-17
jrmu
# Delete rows where key = val in table
087
2021-12-17
jrmu
# !delete <table> <key> <val>
088
2021-12-17
jrmu
sub mdelete {
089
2021-12-17
jrmu
my ($bot, $nick, $host, $hand, $text) = @_;
090
2021-12-17
jrmu
if (! (main::isstaff($bot, $nick))) { return; }
091
2021-12-17
jrmu
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
092
2021-12-17
jrmu
my ($table, $key, $val) = ($1, $2, $3);
093
2021-12-17
jrmu
if (deleterows($table, $key, $val)) {
094
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :$table $key = $val deleted");
095
2021-12-17
jrmu
} else {
096
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :delete failed");
097
2021-12-17
jrmu
}
098
2021-12-17
jrmu
} else {
099
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :invalid delete");
100
2021-12-17
jrmu
}
101
2021-12-17
jrmu
}
102
2021-12-17
jrmu
103
2021-12-17
jrmu
# Output rows where key = val in table
104
2021-12-17
jrmu
# !select <table> <key> <val>
105
2021-12-17
jrmu
sub mselect {
106
2021-12-17
jrmu
my ($bot, $nick, $host, $hand, $text) = @_;
107
2021-12-17
jrmu
if (! (main::isstaff($bot, $nick))) { return; }
108
2021-12-17
jrmu
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
109
2021-12-17
jrmu
my ($table, $key, $val) = ($1, $2, $3);
110
2021-12-17
jrmu
my @rows = selectrows($table, $key, $val);
111
2021-12-17
jrmu
if (@rows) {
112
2021-12-17
jrmu
foreach my $row (@rows) {
113
2021-12-17
jrmu
my @pairs;
114
2021-12-17
jrmu
foreach $key (keys %$row) {
115
2021-12-17
jrmu
my $val = $row->{$key} || "";
116
2021-12-17
jrmu
push(@pairs, "$key => $val");
117
2021-12-17
jrmu
}
118
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :$table ".join(',', @pairs));
119
2021-12-17
jrmu
}
120
2021-12-17
jrmu
} else {
121
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :no results");
122
2021-12-17
jrmu
}
123
2021-12-17
jrmu
} else {
124
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :select invalid");
125
2021-12-17
jrmu
}
126
2021-12-17
jrmu
}
127
2021-12-17
jrmu
128
2021-12-17
jrmu
# Get value of key where idkey = idval in table
129
2021-12-17
jrmu
# !get <table> <idkey> <idval> <key>
130
2021-12-17
jrmu
sub mget {
131
2021-12-17
jrmu
my ($bot, $nick, $host, $hand, $text) = @_;
132
2021-12-17
jrmu
if (! (main::isstaff($bot, $nick))) { return; }
133
2021-12-17
jrmu
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+(\S+)\s+([-_[:alnum:]]+)/) {
134
2021-12-17
jrmu
my ($table, $idkey, $idval, $key) = ($1, $2, $3, $4);
135
2021-12-17
jrmu
my $val = get($table, $idkey, $idval, $key);
136
2021-12-17
jrmu
if (defined($val)) {
137
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :$table $key => $val where $idkey = $idval");
138
2021-12-17
jrmu
} else {
139
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :undefined");
140
2021-12-17
jrmu
}
141
2021-12-17
jrmu
} else {
142
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :invalid get");
143
2021-12-17
jrmu
}
144
2021-12-17
jrmu
}
145
2021-12-17
jrmu
# !set <table> <idkey> <idval> <key> <val>
146
2021-12-17
jrmu
sub mset {
147
2021-12-17
jrmu
my ($bot, $nick, $host, $hand, $text) = @_;
148
2021-12-17
jrmu
if (! (main::isstaff($bot, $nick))) { return; }
149
2021-12-17
jrmu
if ($text =~ /^([-_~@!,\.[:alnum:]]+)\s+([-_~@!,\.[:alnum:]]+)\s+(\S+)\s+([-_[:alnum:]]+)\s+(\S+)/) {
150
2021-12-17
jrmu
my ($table, $idkey, $idval, $key, $val) = ($1, $2, $3, $4, $5);
151
2021-12-17
jrmu
if (set($table, $idkey, $idval, $key, $val)) {
152
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :$table $key => $val where $idkey = $idval");
153
2021-12-17
jrmu
} else {
154
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :failed set");
155
2021-12-17
jrmu
}
156
2021-12-17
jrmu
} else {
157
2021-12-17
jrmu
main::putserv($bot, "PRIVMSG $nick :invalid set");
158
2021-12-17
jrmu
}
159
2021-12-17
jrmu
}
160
2021-12-17
jrmu
161
2021-12-17
jrmu
# Connect to database, creating table if necessary
162
2021-12-17
jrmu
# Returns true on success, false on failure
163
2021-12-17
jrmu
sub connectdb {
164
2021-12-17
jrmu
my $dsn = "dbi:SQLite:dbname=$dbpath";
165
2021-12-17
jrmu
my $user = "";
166
2021-12-17
jrmu
my $password = "";
167
2021-12-17
jrmu
$dbh = DBI->connect($dsn, $user, $password, {
168
2021-12-17
jrmu
PrintError => 1,
169
2021-12-17
jrmu
RaiseError => 1,
170
2021-12-17
jrmu
AutoCommit => 1,
171
2021-12-17
jrmu
FetchHashKeyName => 'NAME_lc',
172
2021-12-17
jrmu
}) or die "Couldn't connect to database: " . $DBI::errstr;
173
2021-12-17
jrmu
if (!(-s "$dbpath")) {
174
2021-12-17
jrmu
my $sql = main::readstr('table.sql');
175
2021-12-17
jrmu
my @sql = split /;/m, $sql;
176
2021-12-17
jrmu
foreach my $s (@sql) {
177
2021-12-17
jrmu
$dbh->do($s);
178
2021-12-17
jrmu
}
179
2021-12-17
jrmu
}
180
2021-12-17
jrmu
main::debug(ALL, "connected to $dbpath");
181
2021-12-17
jrmu
return defined($dbh);
182
2021-12-17
jrmu
}
183
2021-12-17
jrmu
184
2021-12-17
jrmu
# Inserts comma-separated keys and vals into table
185
2021-12-17
jrmu
# Returns number of rows successfully inserted
186
2021-12-17
jrmu
sub insertrow {
187
2021-12-17
jrmu
my ($table, $keys, $vals) = @_;
188
2021-12-17
jrmu
if (!defined($dbh)) { connectdb(); }
189
2021-12-17
jrmu
my $rows = $dbh->do("INSERT INTO $table ($keys) values ($vals)");
190
2021-12-17
jrmu
if ($rows) {
191
2021-12-17
jrmu
main::debug(ALL, "INSERT INTO $table ($keys) values ($vals)");
192
2021-12-17
jrmu
} else {
193
2021-12-17
jrmu
main::debug(ERRORS, "ERRORS: Failed INSERT INTO $table ($keys) values ($vals)");
194
2021-12-17
jrmu
}
195
2021-12-17
jrmu
return $rows;
196
2021-12-17
jrmu
}
197
2021-12-17
jrmu
198
2021-12-17
jrmu
# Update key, value pair for record where idkey equals idval in table
199
2021-12-17
jrmu
# Returns number of rows successfully updated
200
2021-12-17
jrmu
sub updaterow {
201
2021-12-17
jrmu
my ($table, $idkey, $idval, $key, $val) = @_;
202
2021-12-17
jrmu
if (!defined($dbh)) { connectdb(); }
203
2021-12-17
jrmu
my $rows = $dbh->do("UPDATE $table SET $key = ? where $idkey = ?", undef, $val, $idval);
204
2021-12-17
jrmu
if ($rows) {
205
2021-12-17
jrmu
main::debug(ALL, "UPDATE $table SET $key = $val where $idkey = $idval");
206
2021-12-17
jrmu
} else {
207
2021-12-17
jrmu
main::debug(ERRORS, "ERRORS: Failed UPDATE $table SET $key = $val where $idkey = $idval");
208
2021-12-17
jrmu
}
209
2021-12-17
jrmu
return $rows;
210
2021-12-17
jrmu
}
211
2021-12-17
jrmu
212
2021-12-17
jrmu
# Delete records from $table where $key = $val
213
2021-12-17
jrmu
# Returns number of rows deleted
214
2021-12-17
jrmu
sub deleterows {
215
2021-12-17
jrmu
my ($table, $key, $val) = @_;
216
2021-12-17
jrmu
if (!defined($dbh)) { connectdb(); }
217
2021-12-17
jrmu
my $rows = $dbh->do("DELETE FROM $table WHERE $key = ?", undef, $val);
218
2021-12-17
jrmu
if ($rows) {
219
2021-12-17
jrmu
main::debug(ALL, "DELETE FROM $table WHERE $key = $val");
220
2021-12-17
jrmu
} else {
221
2021-12-17
jrmu
main::debug(ERRORS, "ERRORS: Failed DELETE FROM $table WHERE $key = $val");
222
2021-12-17
jrmu
}
223
2021-12-17
jrmu
return $rows;
224
2021-12-17
jrmu
}
225
2021-12-17
jrmu
226
2021-12-17
jrmu
# Returns all records in the database
227
2021-12-17
jrmu
sub selectall {
228
2021-12-17
jrmu
my ($table) = @_;
229
2021-12-17
jrmu
if (!defined($dbh)) { connectdb(); }
230
2021-12-17
jrmu
my $sth = $dbh->prepare("SELECT * FROM $table");
231
2021-12-17
jrmu
$sth->execute();
232
2021-12-17
jrmu
my @results;
233
2021-12-17
jrmu
while (my $row = $sth->fetchrow_hashref) {
234
2021-12-17
jrmu
push(@results, $row);
235
2021-12-17
jrmu
}
236
2021-12-17
jrmu
return @results;
237
2021-12-17
jrmu
}
238
2021-12-17
jrmu
239
2021-12-17
jrmu
# Returns all records from table where key equals value
240
2021-12-17
jrmu
sub selectrows {
241
2021-12-17
jrmu
my ($table, $key, $val) = @_;
242
2021-12-17
jrmu
if (!defined($dbh)) { connectdb(); }
243
2021-12-17
jrmu
my $sth = $dbh->prepare("SELECT * FROM $table WHERE $key = ?");
244
2021-12-17
jrmu
$sth->execute($val);
245
2021-12-17
jrmu
my @results;
246
2021-12-17
jrmu
while (my $row = $sth->fetchrow_hashref) {
247
2021-12-17
jrmu
push(@results, $row);
248
2021-12-17
jrmu
}
249
2021-12-17
jrmu
return @results;
250
2021-12-17
jrmu
}
251
2021-12-17
jrmu
252
2021-12-17
jrmu
# Returns list of tables
253
2021-12-17
jrmu
sub tables {
254
2021-12-17
jrmu
# if (!defined($dbh)) { connectdb(); }
255
2021-12-17
jrmu
# my $sth = $dbh->prepare(".tables");
256
2021-12-17
jrmu
# $sth->execute($val);
257
2021-12-17
jrmu
# my @results;
258
2021-12-17
jrmu
# while (my $row = $sth->fetchrow_hashref) {
259
2021-12-17
jrmu
# push(@results, $row);
260
2021-12-17
jrmu
# }
261
2021-12-17
jrmu
# return @results;
262
2021-12-17
jrmu
return qw(bnc shell www irc smtp);
263
2021-12-17
jrmu
}
264
2021-12-17
jrmu
265
2021-12-17
jrmu
# Returns value of key in record in table where idkey = idval
266
2021-12-17
jrmu
sub get {
267
2021-12-17
jrmu
my ($table, $idkey, $idval, $key) = @_;
268
2021-12-17
jrmu
if (!defined($dbh)) { connectdb(); }
269
2021-12-17
jrmu
my $sth = $dbh->prepare("SELECT * FROM $table WHERE $idkey = ?");
270
2021-12-17
jrmu
$sth->execute($idval);
271
2021-12-17
jrmu
if (my $row = $sth->fetchrow_hashref) {
272
2021-12-17
jrmu
my $val = $row->{$key};
273
2021-12-17
jrmu
if (!defined($val)) { $val = "undefined"; }
274
2021-12-17
jrmu
main::debug(ALL, "get: $table $key => $val where $idkey = $idval");
275
2021-12-17
jrmu
return $row->{$key};
276
2021-12-17
jrmu
} else {
277
2021-12-17
jrmu
main::debug(ERRORS, "ERRORS: $table $key undefined where $idkey = $idval");
278
2021-12-17
jrmu
return;
279
2021-12-17
jrmu
}
280
2021-12-17
jrmu
}
281
2021-12-17
jrmu
282
2021-12-17
jrmu
# Sets value of key in the record in table where idkey = idval
283
2021-12-17
jrmu
# Returns true on success; false on failure
284
2021-12-17
jrmu
sub set {
285
2021-12-17
jrmu
my ($table, $idkey, $idval, $key, $val) = @_;
286
2021-12-17
jrmu
if (defined(get($table, $idkey, $idval, $idkey))) {
287
2021-12-17
jrmu
main::debug(ALL, "set: update");
288
2021-12-17
jrmu
return updaterow($table, $idkey, $idval, $key, $val) > 0;
289
2021-12-17
jrmu
} else {
290
2021-12-17
jrmu
main::debug(ALL, "set: insert");
291
2021-12-17
jrmu
return insertrow($table, "$idkey,$key", "\"$idval\",\"$val\"") > 0;
292
2021-12-17
jrmu
}
293
2021-12-17
jrmu
}
294
2021-12-17
jrmu
295
2021-12-17
jrmu
# given a key, val pair in table, return the id that falls within expires seconds
296
2021-12-17
jrmu
sub id {
297
2021-12-17
jrmu
my ($table, $key, $val, $expires) = @_;
298
2021-12-17
jrmu
my @rows = selectrows($table, $key, $val);
299
2021-12-17
jrmu
if (scalar(@rows) == 0) {
300
2021-12-17
jrmu
print "table => $table, key => $key, val => $val\n\n";
301
2021-12-17
jrmu
}
302
2021-12-17
jrmu
my $maxrow;
303
2021-12-17
jrmu
foreach my $row (@rows) {
304
2021-12-17
jrmu
if (!defined($maxrow)) { $maxrow = $row; }
305
2021-12-17
jrmu
if ($row->{localtime} > $maxrow->{localtime}) {
306
2021-12-17
jrmu
$maxrow = $row;
307
2021-12-17
jrmu
}
308
2021-12-17
jrmu
}
309
2021-12-17
jrmu
if (abs(time() - $maxrow->{localtime}) <= $expires) {
310
2021-12-17
jrmu
main::debug(ALL, "id: $maxrow->{id} where $key = $val at $expires");
311
2021-12-17
jrmu
return $maxrow->{id};
312
2021-12-17
jrmu
} else {
313
2021-12-17
jrmu
main::debug(ERRORS, "no id found");
314
2021-12-17
jrmu
return;
315
2021-12-17
jrmu
}
316
2021-12-17
jrmu
}
317
2021-12-17
jrmu
318
2021-12-17
jrmu
1; # MUST BE LAST STATEMENT IN FILE
IRCNow