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.2