1 84c190b6 2021-12-17 jrmu #!/usr/bin/perl
  2 84c190b6 2021-12-17 jrmu 
  3 84c190b6 2021-12-17 jrmu package SQLite;
  4 84c190b6 2021-12-17 jrmu 
  5 84c190b6 2021-12-17 jrmu use strict;
  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;
 10 84c190b6 2021-12-17 jrmu use DBI;
 11 84c190b6 2021-12-17 jrmu use DBD::SQLite;
 12 84c190b6 2021-12-17 jrmu 
 13 84c190b6 2021-12-17 jrmu use constant {
 14 84c190b6 2021-12-17 jrmu 	NONE => 0,
 15 84c190b6 2021-12-17 jrmu 	ERRORS => 1,
 16 84c190b6 2021-12-17 jrmu 	WARNINGS => 2,
 17 84c190b6 2021-12-17 jrmu 	ALL => 3,
 18 84c190b6 2021-12-17 jrmu };
 19 84c190b6 2021-12-17 jrmu my %conf = %main::conf;
 20 84c190b6 2021-12-17 jrmu my $staff = $conf{staff};
 21 84c190b6 2021-12-17 jrmu my $dbh;
 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);
 32 84c190b6 2021-12-17 jrmu 
 33 84c190b6 2021-12-17 jrmu sub init {
 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 $!";
 37 84c190b6 2021-12-17 jrmu }
 38 84c190b6 2021-12-17 jrmu 
 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");
 45 84c190b6 2021-12-17 jrmu 	} else {
 46 84c190b6 2021-12-17 jrmu 		main::putserv($bot, "PRIVMSG $nick :ERROR: connectdb failed");
 47 84c190b6 2021-12-17 jrmu 	}
 48 84c190b6 2021-12-17 jrmu }
 49 84c190b6 2021-12-17 jrmu 
 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)");
 61 84c190b6 2021-12-17 jrmu 		} else {
 62 84c190b6 2021-12-17 jrmu 			main::putserv($bot, "PRIVMSG $nick :$table insert failed");
 63 84c190b6 2021-12-17 jrmu 		}
 64 84c190b6 2021-12-17 jrmu 	} else {
 65 84c190b6 2021-12-17 jrmu 		main::putserv($bot, "PRIVMSG $nick :invalid insert");
 66 84c190b6 2021-12-17 jrmu 	}
 67 84c190b6 2021-12-17 jrmu }
 68 84c190b6 2021-12-17 jrmu 
 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");
 78 84c190b6 2021-12-17 jrmu 		} else {
 79 84c190b6 2021-12-17 jrmu 			main::putserv($bot, "PRIVMSG $nick :update failed");
 80 84c190b6 2021-12-17 jrmu 		}
 81 84c190b6 2021-12-17 jrmu 	} else {
 82 84c190b6 2021-12-17 jrmu 		main::putserv($bot, "PRIVMSG $nick :invalid update");
 83 84c190b6 2021-12-17 jrmu 	}
 84 84c190b6 2021-12-17 jrmu }
 85 84c190b6 2021-12-17 jrmu 
 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");
 95 84c190b6 2021-12-17 jrmu 		} else {
 96 84c190b6 2021-12-17 jrmu 			main::putserv($bot, "PRIVMSG $nick :delete failed");
 97 84c190b6 2021-12-17 jrmu 		}
 98 84c190b6 2021-12-17 jrmu 	} else {
 99 84c190b6 2021-12-17 jrmu 		main::putserv($bot, "PRIVMSG $nick :invalid delete");
100 84c190b6 2021-12-17 jrmu 	}
101 84c190b6 2021-12-17 jrmu }
102 84c190b6 2021-12-17 jrmu 
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) {
113 84c190b6 2021-12-17 jrmu 				my @pairs;
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");
117 84c190b6 2021-12-17 jrmu 				}
118 84c190b6 2021-12-17 jrmu 				main::putserv($bot, "PRIVMSG $nick :$table ".join(',', @pairs));
119 84c190b6 2021-12-17 jrmu 			}
120 84c190b6 2021-12-17 jrmu 		} else {
121 84c190b6 2021-12-17 jrmu 			main::putserv($bot, "PRIVMSG $nick :no results");
122 84c190b6 2021-12-17 jrmu 		}
123 84c190b6 2021-12-17 jrmu 	} else {
124 84c190b6 2021-12-17 jrmu 		main::putserv($bot, "PRIVMSG $nick :select invalid");
125 84c190b6 2021-12-17 jrmu 	}
126 84c190b6 2021-12-17 jrmu }
127 84c190b6 2021-12-17 jrmu 
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>
130 84c190b6 2021-12-17 jrmu sub mget {
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");
138 84c190b6 2021-12-17 jrmu 		} else {
139 84c190b6 2021-12-17 jrmu 			main::putserv($bot, "PRIVMSG $nick :undefined");
140 84c190b6 2021-12-17 jrmu 		}
141 84c190b6 2021-12-17 jrmu 	} else {
142 84c190b6 2021-12-17 jrmu 		main::putserv($bot, "PRIVMSG $nick :invalid get");
143 84c190b6 2021-12-17 jrmu 	}
144 84c190b6 2021-12-17 jrmu }
145 84c190b6 2021-12-17 jrmu # !set <table> <idkey> <idval> <key> <val>
146 84c190b6 2021-12-17 jrmu sub mset {
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");
153 84c190b6 2021-12-17 jrmu 		} else {
154 84c190b6 2021-12-17 jrmu 			main::putserv($bot, "PRIVMSG $nick :failed set");
155 84c190b6 2021-12-17 jrmu 		}
156 84c190b6 2021-12-17 jrmu 	} else {
157 84c190b6 2021-12-17 jrmu 		main::putserv($bot, "PRIVMSG $nick :invalid set");
158 84c190b6 2021-12-17 jrmu 	}
159 84c190b6 2021-12-17 jrmu }
160 84c190b6 2021-12-17 jrmu 
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);
178 84c190b6 2021-12-17 jrmu 		}
179 84c190b6 2021-12-17 jrmu 	}
180 84c190b6 2021-12-17 jrmu 	main::debug(ALL, "connected to $dbpath");
181 84c190b6 2021-12-17 jrmu 	return defined($dbh);
182 84c190b6 2021-12-17 jrmu }
183 84c190b6 2021-12-17 jrmu 
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)");
192 84c190b6 2021-12-17 jrmu 	} else {
193 84c190b6 2021-12-17 jrmu 		main::debug(ERRORS, "ERRORS: Failed INSERT INTO $table ($keys) values ($vals)");
194 84c190b6 2021-12-17 jrmu 	}
195 84c190b6 2021-12-17 jrmu 	return $rows;
196 84c190b6 2021-12-17 jrmu }
197 84c190b6 2021-12-17 jrmu 
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");
206 84c190b6 2021-12-17 jrmu 	} else {
207 84c190b6 2021-12-17 jrmu 		main::debug(ERRORS, "ERRORS: Failed UPDATE $table SET $key = $val where $idkey = $idval");
208 84c190b6 2021-12-17 jrmu 	}
209 84c190b6 2021-12-17 jrmu 	return $rows;
210 84c190b6 2021-12-17 jrmu }
211 84c190b6 2021-12-17 jrmu 
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");
220 84c190b6 2021-12-17 jrmu 	} else {
221 84c190b6 2021-12-17 jrmu 		main::debug(ERRORS, "ERRORS: Failed DELETE FROM $table WHERE $key = $val");
222 84c190b6 2021-12-17 jrmu 	}
223 84c190b6 2021-12-17 jrmu 	return $rows;
224 84c190b6 2021-12-17 jrmu }
225 84c190b6 2021-12-17 jrmu 
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);
235 84c190b6 2021-12-17 jrmu 	}
236 84c190b6 2021-12-17 jrmu 	return @results;
237 84c190b6 2021-12-17 jrmu }
238 84c190b6 2021-12-17 jrmu 
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);
248 84c190b6 2021-12-17 jrmu 	}
249 84c190b6 2021-12-17 jrmu 	return @results;
250 84c190b6 2021-12-17 jrmu }
251 84c190b6 2021-12-17 jrmu 
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);
260 84c190b6 2021-12-17 jrmu 	#	}
261 84c190b6 2021-12-17 jrmu 	#	return @results;
262 84c190b6 2021-12-17 jrmu 	return qw(bnc shell www irc smtp);
263 84c190b6 2021-12-17 jrmu }
264 84c190b6 2021-12-17 jrmu 
265 84c190b6 2021-12-17 jrmu # Returns value of key in record in table where idkey = idval
266 84c190b6 2021-12-17 jrmu sub get {
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};
276 84c190b6 2021-12-17 jrmu 	} else {
277 84c190b6 2021-12-17 jrmu 		main::debug(ERRORS, "ERRORS: $table $key undefined where $idkey = $idval");
278 84c190b6 2021-12-17 jrmu 		return;
279 84c190b6 2021-12-17 jrmu 	}
280 84c190b6 2021-12-17 jrmu }
281 84c190b6 2021-12-17 jrmu 
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
284 84c190b6 2021-12-17 jrmu sub set {
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;
289 84c190b6 2021-12-17 jrmu 	} else {
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;
292 84c190b6 2021-12-17 jrmu 	}
293 84c190b6 2021-12-17 jrmu }
294 84c190b6 2021-12-17 jrmu 
295 84c190b6 2021-12-17 jrmu # given a key, val pair in table, return the id that falls within expires seconds
296 84c190b6 2021-12-17 jrmu sub id {
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";
301 84c190b6 2021-12-17 jrmu 	}
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;
307 84c190b6 2021-12-17 jrmu 		}
308 84c190b6 2021-12-17 jrmu 	}
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};
312 84c190b6 2021-12-17 jrmu 	} else {
313 84c190b6 2021-12-17 jrmu 		main::debug(ERRORS, "no id found");
314 84c190b6 2021-12-17 jrmu 		return;
315 84c190b6 2021-12-17 jrmu 	}
316 84c190b6 2021-12-17 jrmu }
317 84c190b6 2021-12-17 jrmu 
318 84c190b6 2021-12-17 jrmu 1; # MUST BE LAST STATEMENT IN FILE