From 8ee3f49e54ac746a61081301d3c5e7d73b583031 Mon Sep 17 00:00:00 2001
From: bones_was_here <bones_was_here@xa.org.au>
Date: Wed, 16 Mar 2022 21:25:07 +1000
Subject: [PATCH] Add PostgreSQL support to CA scripts
---
misc/infrastructure/keygen/config.pl | 3 ++-
misc/infrastructure/keygen/response.d0ir | 24 +++++++++++++++++++-----
2 files changed, 21 insertions(+), 6 deletions(-)
diff --git a/misc/infrastructure/keygen/config.pl b/misc/infrastructure/keygen/config.pl
index 65108fd7..97da9e13 100644
--- a/misc/infrastructure/keygen/config.pl
+++ b/misc/infrastructure/keygen/config.pl
@@ -7,7 +7,8 @@
my ($inc) = @_;
return 0 if ($inc >= 0) && check_dnsbl([qr/.*:.*:.*/], [], ['torexit.dan.me.uk', 'aspews.ext.sorbs.net']);
return 0 if ($inc >= 0) && check_banlist('http://rm.sudo.rm-f.org/~xonotic/bans/?action=list&servers=*');
- return 0 if check_sql('dbi:mysql:dbname=xonotic-ca', 'xonotic-ca', '************', 'ip', 0.2, 1, 20, 1000, $inc);
+ #return 0 if check_sql('dbi:mysql:dbname=xonotic-ca', 'xonotic-ca', '************', 'ip', 0.2, 1, 20, 1000, $inc);
+ return 0 if check_sql('dbi:Pg:dbname=xonotic-ca', '', '', 'ip', 0.2, 1, 20, 1000, $inc);
1;
}
},
diff --git a/misc/infrastructure/keygen/response.d0ir b/misc/infrastructure/keygen/response.d0ir
index 3396ce1e..04461155 100755
--- a/misc/infrastructure/keygen/response.d0ir
+++ b/misc/infrastructure/keygen/response.d0ir
@@ -82,20 +82,30 @@ sub check_dnsbl($$@)
return 0;
}
+# MySQL
# create table ip ( id INT AUTO_INCREMENT PRIMARY KEY, ip VARCHAR(64), t DATETIME, error BOOLEAN, INDEX(ip), INDEX(t), INDEX(error) );
+# standard SQL
+# CREATE TABLE ip ( id INT PRIMARY KEY generated always as identity, ip VARCHAR(64), t TIMESTAMP, error BOOLEAN );
+# CREATE INDEX ip_index ON ip ( ip, t, error );
our $__CACHED_DBH__;
sub check_ip_record
{
my ($DBH, $tbl, $ip) = @_;
- my $status = $DBH->selectrow_arrayref("select count(*) from $tbl where ip=? and error=false and t>date_sub(now(), interval 7 day)", undef, $ip)
+ # MySQL
+ #my $status = $DBH->selectrow_arrayref("select count(*) from $tbl where ip=? and error=false and t>date_sub(now(), interval 7 day)", undef, $ip)
+ # standard SQL
+ my $status = $DBH->selectrow_arrayref("select count(*) from $tbl where ip=? and error=false and t>(now() - interval '7' day)", undef, $ip)
or die "DBI/DBD: $!";
return $status->[0];
}
sub insert_ip_record
{
my ($DBH, $tbl, $ip) = @_;
- my $status = $DBH->selectall_arrayref("select error, t>date_sub(now(), interval 7 day) from $tbl where ip=?", undef, $ip)
+ # MySQL
+ #my $status = $DBH->selectall_arrayref("select error, t>date_sub(now(), interval 7 day) from $tbl where ip=?", undef, $ip)
+ # standard SQL
+ my $status = $DBH->selectall_arrayref("select error, t>(now() - interval '7' day) from $tbl where ip=?", undef, $ip)
or die "DBI/DBD: $!";
if(@$status)
{
@@ -128,9 +138,13 @@ sub check_sql($$$$$$$$$)
my $DBH = ($__CACHED_DBH__ ? $__CACHED_DBH__ : ($__CACHED_DBH__ = DBI->connect($dsn, $u, $p, { RaiseError => 1, AutoCommit => 0 })))
or die "DBI/DBD: $!";
eval {
- $DBH->do("set character set utf8");
- $DBH->do("set names utf8");
- $DBH->do("set time_zone = '+0:00'");
+ # PostgreSQL defaults to utf8 everywhere so we don't need to set it
+ # MySQL defaults to latin1
+ #$DBH->do("set character set utf8");
+ #$DBH->do("set names utf8");
+ #$DBH->do("set time_zone = '+0:00'");
+ # standard SQL
+ $DBH->do("SET TIME ZONE +0");
} or do {
undef $__CACHED_DBH__;
die $@;
--
2.39.5