From 504d39ae463cfa4719aaf6d0eaa5656ccbc6d74e Mon Sep 17 00:00:00 2001
From: Michael Andreen <harv@ruin.nu>
Date: Fri, 5 Jun 2009 21:42:00 +0200
Subject: [PATCH] Convert messy pl/perl functions into cleaner pl/pgsql
 functions.

---
 database/noperl.sql     | 48 +++++++++++++++++++++++++++++++++++++++++
 scripts/parseplanets.pl |  2 +-
 2 files changed, 49 insertions(+), 1 deletion(-)
 create mode 100644 database/noperl.sql

diff --git a/database/noperl.sql b/database/noperl.sql
new file mode 100644
index 0000000..f882134
--- /dev/null
+++ b/database/noperl.sql
@@ -0,0 +1,48 @@
+CREATE OR REPLACE FUNCTION find_planet_id(_ruler text, _planet text, _race race) RETURNS integer
+    AS $_$
+DECLARE
+	p RECORD;
+	pid INTEGER;
+	thread INTEGER;
+BEGIN
+	SELECT id, race INTO p FROM planets WHERE ruler = _ruler AND planet = _planet;
+	IF FOUND THEN
+		IF _race <> p.race THEN
+			UPDATE planets SET race = _race WHERE id = p.id;
+		END IF;
+		pid := p.id;
+	ELSE
+		INSERT INTO forum_threads (fbid,subject,uid) VALUES(-2, _ruler || ' OF ' || _planet, -3)
+			RETURNING ftid INTO thread;
+		INSERT INTO planets(ruler,planet,race,ftid) VALUES(_ruler,_planet,_race,thread)
+			RETURNING id INTO pid;
+	END IF;
+	RETURN pid;
+END;
+$_$
+    LANGUAGE plpgsql;
+
+DROP FUNCTION findplanetid(character varying, character varying, character varying);
+
+CREATE OR REPLACE FUNCTION find_alliance_id(alliance text) RETURNS integer
+    AS $_$
+DECLARE
+	aid INTEGER;
+BEGIN
+	SELECT id FROM INTO aid alliances WHERE name = alliance;
+	IF NOT FOUND THEN
+		INSERT INTO alliances(name) VALUES($1)
+			RETURNING id INTO aid;
+	END IF;
+	RETURN aid;
+END;
+$_$
+    LANGUAGE plpgsql;
+
+DROP FUNCTION find_alliance_id(character varying);
+
+CREATE OR REPLACE FUNCTION coords(x integer, y integer, z integer) RETURNS text
+    AS $_$
+SELECT $1 || ':' || $2 || ':' || $3
+$_$
+    LANGUAGE sql IMMUTABLE;
diff --git a/scripts/parseplanets.pl b/scripts/parseplanets.pl
index fa31c1e..50225ce 100755
--- a/scripts/parseplanets.pl
+++ b/scripts/parseplanets.pl
@@ -47,7 +47,7 @@ $dumps->execute($tick);
 if (@_ = $dumps->fetchrow){
 	$_ = $_[0];
 	$hour = (gmtime($_[1]))[2];
-	my $planetid = $dbh->prepare(qq{SELECT findplanetid(?,?,?)});
+	my $planetid = $dbh->prepare(q{SELECT find_planet_id($1,$2,$3)});
 	while (m/(\d+)\t(\d+)\t(\d+)\t\"(.*)\"\t\"(.*)\"\t(Ter|Cat|Zik|Xan|Etd)\t(\d+)\t(\d+)\t(\d+)\t(\d+)/g){
 		$planetid->execute($5,$4,$6);
 		my @id = $planetid->fetchrow;
-- 
2.39.2