-COMMENT ON SCHEMA public IS 'Standard public schema';
-
-
---
--- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
---
-
-CREATE PROCEDURAL LANGUAGE plperl;
-
-
-SET search_path = public, pg_catalog;
-
---
--- Name: add_call(); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION add_call() RETURNS "trigger"
- AS $_X$
-if ($_TD->{event} eq 'INSERT'){
- $rv = spi_exec_query("SELECT nextval('public.forum_threads_ftid_seq') AS id");
- if ($rv->{processed} != 1){
- return 'SKIP';
- }
- $ftid = $rv->{rows}[0]->{id};
- $query = spi_prepare('INSERT INTO forum_threads (fbid,ftid,subject,uid) VALUES(-3,$1,$2,-3)','int4','varchar');
- $rv = spi_exec_prepared($query,$ftid,"$_TD->{new}{member}: $_TD->{new}{landing_tick}");
- spi_freeplan($query);
- if (rv->{status} != SPI_OK_INSERT){
- return 'SKIP';
- }
- $_TD->{new}{ftid} = $ftid;
- return 'MODIFY';
-}
-return 'SKIP';
-$_X$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.add_call() OWNER TO ndawn;
-
---
--- Name: add_intel(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, integer); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION add_intel(tick integer, eta integer, x1 integer, y1 integer, z1 integer, x2 integer, y2 integer, z2 integer, amount integer, mission character varying, uid integer) RETURNS boolean
- AS $_$my ($tick, $eta, $x1, $y1, $z1, $x2, $y2, $z2, $amount, $mission, $uid) = @_;
-$ingal = false;
-$tick = -1 unless defined $tick;
-if ($x1 == $x2 && $y1 == $y2) {
-$ingal = true;
-}
-if ($tick < 0){
- $rv = spi_exec_query("SELECT tick FROM planet_stats ORDER BY tick DESC LIMIT 1;");
- $tick = $rv->{rows}[0]->{tick};
-}
-$rv = spi_exec_query("SELECT id,tick FROM planet_stats WHERE x = $x1 AND y = $y1 AND z = $z1 AND (tick = $tick OR tick = (SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC;");
-unless ($rv->{processed} >= 1){
-return false;
-}
-$id1 = $rv->{rows}[0]->{id};
-$rv = spi_exec_query("SELECT id,tick FROM planet_stats WHERE x = $x2 AND y = $y2 AND z = $z2 AND (tick = $tick OR tick = (SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC;");
-unless ($rv->{processed} >= 1){
-return false;
-}
-$id2 = $rv->{rows}[0]->{id};
-$tick += $eta;
-spi_exec_query("INSERT INTO intel (target,sender,tick,eta,mission,uid,ingal, amount) VALUES ($id1, $id2, $tick,$eta, '$mission', $uid, $ingal, $amount)");
-return true;$_$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.add_intel(tick integer, eta integer, x1 integer, y1 integer, z1 integer, x2 integer, y2 integer, z2 integer, amount integer, mission character varying, uid integer) OWNER TO ndawn;
-
---
--- Name: add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying) RETURNS boolean
- AS $_$my ($tick, $x1, $y1, $z1, $x2, $y2, $z2, $amount, $mission, $user) = @_;
-$ingal = false;
-if ($x1 == $x2 && $y1 == $y2) {
-$ingal = true;
-}
-if ($tick < 0){
- $rv = spi_exec_query("SELECT tick FROM planet_stats ORDER BY tick DESC LIMIT 1;");
- $tick = $rv->{rows}[0]->{tick};
-}
-$rv = spi_exec_query("SELECT id FROM planet_stats WHERE x = $x1 AND y = $y1 AND z = $z1 AND tick = $tick;");
-unless ($rv->{processed} == 1){
-return false;
-}
-$id1 = $rv->{rows}[0]->{id};
-$rv = spi_exec_query("SELECT id FROM planet_stats WHERE x = $x2 AND y = $y2 AND z = $z2 AND tick = $tick;");
-unless ($rv->{processed} == 1){
-return false;
-}
-$id2 = $rv->{rows}[0]->{id};
-spi_exec_query("INSERT INTO intel (target,sender,tick,mission,\"user\",ingal, amount) VALUES ($id1, $id2, $tick, '$mission', '$user', $ingal, $amount)");
-return true;$_$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying) OWNER TO ndawn;
-
---
--- Name: add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying) RETURNS boolean
- AS $_$my ($tick, $eta, $x1, $y1, $z1, $x2, $y2, $z2, $amount, $mission, $uid) = @_;
-unless ($uid = /^(-?\d+)$/){
- $rv = spi_exec_query("SELECT id FROM users WHERE username = '$uid';");
- $uid = $rv->{rows}[0]->{id};
-}
-$ingal = false;
-if ($x1 == $x2 && $y1 == $y2) {
-$ingal = true;
-}
-if ($tick < 0){
- $rv = spi_exec_query("SELECT tick FROM planet_stats ORDER BY tick DESC LIMIT 1;");
- $tick = $rv->{rows}[0]->{tick};
-}
-$rv = spi_exec_query("SELECT id,tick FROM planet_stats WHERE x = $x1 AND y = $y1 AND z = $z1 AND (tick = $tick OR tick = (SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC;");
-unless ($rv->{processed} >= 1){
-return false;
-}
-$id1 = $rv->{rows}[0]->{id};
-$rv = spi_exec_query("SELECT id,tick FROM planet_stats WHERE x = $x2 AND y = $y2 AND z = $z2 AND (tick = $tick OR tick = (SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC;");
-unless ($rv->{processed} >= 1){
-return false;
-}
-$id2 = $rv->{rows}[0]->{id};
-$tick += $eta;
-spi_exec_query("INSERT INTO intel (target,sender,tick,eta,mission,uid,ingal, amount) VALUES ($id1, $id2, $tick,$eta, '$mission', $uid, $ingal, $amount)");
-return true;$_$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying) OWNER TO ndawn;
-
---
--- Name: add_intel4(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, integer); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION add_intel4(tick integer, eta integer, x1 integer, y1 integer, z1 integer, x2 integer, y2 integer, z2 integer, amount integer, mission character varying, uid integer) RETURNS information_schema.cardinal_number
- AS $_$my ($tick, $eta, $x1, $y1, $z1, $x2, $y2, $z2, $amount, $mission, $uid) = @_;
-$ingal = false;
-if ($x1 == $x2 && $y1 == $y2) {
-$ingal = true;
-}
-if ($tick < 0){
- $rv = spi_exec_query("SELECT tick FROM planet_stats ORDER BY tick DESC LIMIT 1;");
- $tick = $rv->{rows}[0]->{tick};
-}
-$rv = spi_exec_query("SELECT id,tick FROM planet_stats WHERE x = $x1 AND y = $y1 AND z = $z1 AND (tick = $tick OR tick = (SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC;");
-unless ($rv->{processed} >= 1){
-return false;
-}
-$id1 = $rv->{rows}[0]->{id};
-$rv = spi_exec_query("SELECT id,tick FROM planet_stats WHERE x = $x2 AND y = $y2 AND z = $z2 AND (tick = $tick OR tick = (SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC;");
-unless ($rv->{processed} >= 1){
-return false;
-}
-$id2 = $rv->{rows}[0]->{id};
-$tick += $eta;
-spi_exec_query("INSERT INTO intel (target,sender,tick,eta,mission,uid,ingal, amount) VALUES ($id1, $id2, $tick,$eta, '$mission', $uid, $ingal, $amount)");
-return true;$_$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.add_intel4(tick integer, eta integer, x1 integer, y1 integer, z1 integer, x2 integer, y2 integer, z2 integer, amount integer, mission character varying, uid integer) OWNER TO ndawn;
-
---
--- Name: add_user(); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION add_user() RETURNS "trigger"
- AS $_X$
-if ($_TD->{event} eq 'INSERT'){
- $rv = spi_exec_query("SELECT nextval('public.forum_threads_ftid_seq') AS id");
- if ($rv->{processed} != 1){
- return 'SKIP';
- }
- $ftid = $rv->{rows}[0]->{id};
- $query = spi_prepare('INSERT INTO forum_threads (fbid,ftid,subject,uid) VALUES($1,$2,$3,-3)','int4','int4','varchar');
- $rv = spi_exec_prepared($query,-1,$ftid,"$_TD->{new}{uid}: $_TD->{new}{username}");
- spi_freeplan($query);
- if (rv->{status} != SPI_OK_INSERT){
- return 'SKIP';
- }
- $_TD->{new}{ftid} = $ftid;
- return 'MODIFY';
-}
-return 'SKIP';
-$_X$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.add_user() OWNER TO postgres;
-
---
--- Name: calc_rank(integer); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION calc_rank(tick integer) RETURNS void
- AS $_$my ($tick) = @_;
-spi_exec_query("DELETE FROM rankings WHERE tick = $tick");
-my %ranks = ();
-my $rv = spi_exec_query("SELECT id, score FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY score DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
- $id = $rv->{rows}[$row-1]->{id};
- $ranks{$id}{'score'} = $row;
-}
-
-my $rv = spi_exec_query("SELECT id, value FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY value DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
- $id = $rv->{rows}[$row-1]->{id};
- $ranks{$id}{'value'} = $row;
-}
-
-my $rv = spi_exec_query("SELECT id, size FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY size DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
- $id = $rv->{rows}[$row-1]->{id};
- $ranks{$id}{'size'} = $row;
-}
-
-my $rv = spi_exec_query("SELECT id, (score-value) as xp FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY xp DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
- $id = $rv->{rows}[$row-1]->{id};
- $ranks{$id}{'xp'} = $row;
-}
-foreach $key (keys(%ranks)){
- spi_exec_query("INSERT INTO rankings (id,tick,score,value,size,xp) VALUES($key,$tick,".$ranks{$key}{'score'}.",".$ranks{$key}{'value'}.",".$ranks{$key}{'size'}.",".$ranks{$key}{'xp'}.")");
-}
-$_$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.calc_rank(tick integer) OWNER TO ndawn;
-
---
--- Name: calc_rank3(integer); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION calc_rank3(tick integer) RETURNS information_schema.cardinal_number
- AS $_$my ($tick) = @_;
-#spi_exec_query("DELETE FROM rankings WHERE tick = $tick");
-my %ranks = ();
-my $rv = spi_exec_query("SELECT id, score FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY score DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
- $id = $rv->{rows}[$row-1]->{id};
- #$ranks{$id}{'score'} = $row;
-}
-
-my $rv = spi_exec_query("SELECT id, value FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY value DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
- $id = $rv->{rows}[$row-1]->{id};
- #$ranks{$id}{'value'} = $row;
-}
-
-my $rv = spi_exec_query("SELECT id, size FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY size DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
- $id = $rv->{rows}[$row-1]->{id};
- #$ranks{$id}{'size'} = $row;
-}
-
-my $rv = spi_exec_query("SELECT id, (score-value) as xp FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY xp DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
- $id = $rv->{rows}[$row-1]->{id};
- #$ranks{$id}{'xp'} = $row;
-}
-foreach $key (keys(%ranks)){
- #spi_exec_query("INSERT INTO rankings (id,tick,score,value,size,xp) VALUES($key,$tick,".$ranks{$key}{'score'}.",".$ranks{$key}{'value'}.",".$ranks{$key}{'size'}.",".$ranks{$key}{'xp'}.")");
-}
-$_$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.calc_rank3(tick integer) OWNER TO ndawn;
-
---
--- Name: calculate_rankings(integer); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION calculate_rankings(integer) RETURNS void
- AS $_$my ($tick) = @_;
-spi_exec_query("DELETE FROM rankings WHERE tick = $tick");
-my $rv = spi_exec_query("SELECT id, score FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY score DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
-$id = $rv->{rows}[$row-1]->{id};
-spi_exec_query("INSERT INTO rankings (id,tick,score) VALUES($id,$tick,$row)");
-}
-
-my $rv = spi_exec_query("SELECT id, value FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY value DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
-$id = $rv->{rows}[$row-1]->{id};
-spi_exec_query("UPDATE rankings SET value = $row WHERE id = $id AND tick = $tick");
-}
-
-my $rv = spi_exec_query("SELECT id, size FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY size DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
-$id = $rv->{rows}[$row-1]->{id};
-spi_exec_query("UPDATE rankings SET size = $row WHERE id = $id AND tick = $tick");
-}
-
-my $rv = spi_exec_query("SELECT id, (score-value) as xp FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY xp DESC");
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-for ($row = 1; $row <= $nrows; ++$row ){
-$id = $rv->{rows}[$row-1]->{id};
-spi_exec_query("UPDATE rankings SET xp = $row WHERE id = $id AND tick = $tick");
-}
-$_$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.calculate_rankings(integer) OWNER TO ndawn;
-
---
--- Name: change_member(); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION change_member() RETURNS "trigger"
- AS $_X$if ($_TD->{event} eq 'INSERT' && $_TD->{new}{gid} == 2){
- $rv = spi_exec_query("SELECT * FROM users WHERE uid = $_TD->{new}{uid};");
- if ($rv->{rows}[0]->{planet}){
- spi_exec_query("UPDATE planets SET alliance_id = 1 WHERE id = $rv->{rows}[0]->{planet};");
- }
-}
-if ($_TD->{event} eq 'DELETE' && $_TD->{old}{gid} == 2){
- $rv = spi_exec_query("SELECT * FROM users WHERE uid = $_TD->{old}{uid};");
- if ($rv->{rows}[0]->{planet}){
- spi_exec_query("UPDATE planets SET alliance_id = NULL WHERE id = $rv->{rows}[0]{planet};");
- }
-}
-return;$_X$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.change_member() OWNER TO ndawn;
-
---
--- Name: coords(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION coords(x integer, y integer, z integer) RETURNS text
- AS $_$my ($x,$y,$z) = @_;
-return "$x:$y:$z";$_$
- LANGUAGE plperl IMMUTABLE;
-
-
-ALTER FUNCTION public.coords(x integer, y integer, z integer) OWNER TO ndawn;
-
---
--- Name: find_alliance_id(character varying); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION find_alliance_id(character varying) RETURNS integer
- AS $_$my ($name) = @_;
-print "test";
-my $query = spi_prepare('SELECT id FROM alliances WHERE name=$1','varchar');
-my $rv = spi_exec_prepared($query,$name);
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-if ($nrows == 1){
- $id = $rv->{rows}[0]->{id};
-}
-else {
- $rv = spi_exec_query("SELECT nextval('public.alliances_id_seq') AS id");
- if ($rv->{processed} != 1){
- return;
- }
- $id = $rv->{rows}[0]->{id};
- my $query = spi_prepare('INSERT INTO alliances(id,name,relationship) VALUES($1,$2,NULL)','int4','varchar');
- $rv = spi_exec_prepared($query,$id,$name);
- spi_freeplan($query);
- if (rv->{status} != SPI_OK_INSERT){
- return;
- }
-}
-return $id;$_$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.find_alliance_id(character varying) OWNER TO ndawn;
-
---
--- Name: findplanetid(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION findplanetid(character varying, character varying, character varying) RETURNS integer
- AS $_$my ($ruler, $planet, $race) = @_;
-my $query = spi_prepare('SELECT id, race FROM planets WHERE ruler=$1 AND planet=$2','varchar','varchar');
-my $rv = spi_exec_prepared($query,$ruler,$planet);
-spi_freeplan($query);
-my $status = $rv->{status};
-my $nrows = $rv->{processed};
-my $id;
-if ($nrows == 1){
- $id = $rv->{rows}[0]->{id};
- unless ($race eq $rv->{rows}[0]->{race}){
- $query = spi_prepare('UPDATE planets SET race=$1 where id=$2','varchar','int4');
- spi_exec_prepared($query,$race,$id);
- spi_freeplan($query);
- }
-}else {
- $rv = spi_exec_query("SELECT nextval('public.forum_threads_ftid_seq') AS id");
- if ($rv->{processed} != 1){
- return;
- }
- $ftid = $rv->{rows}[0]->{id};
- $query = spi_prepare('INSERT INTO forum_threads (fbid,ftid,subject) VALUES($1,$2,$3)','int4','int4','varchar');
- $rv = spi_exec_prepared($query,-2,$ftid,"$ruler OF $planet");
- spi_freeplan($query);
- if (rv->{status} != SPI_OK_INSERT){
- return;
- }
- $rv = spi_exec_query("SELECT nextval('public.planets_id_seq') AS id");
- if ($rv->{processed} != 1){
- return;
- }
- $id = $rv->{rows}[0]->{id};
- $query = spi_prepare('INSERT INTO planets(id,ruler,planet,race,ftid) VALUES($1,$2,$3,$4,$5)','int4','varchar','varchar','varchar','int4');
- $rv = spi_exec_prepared($query,$id,$ruler,$planet,$race,$ftid);
- spi_freeplan($query);
- if (rv->{status} != SPI_OK_INSERT){
- return;
- }
-
-}
-return $id;$_$
- LANGUAGE plperl;
-
-
-ALTER FUNCTION public.findplanetid(character varying, character varying, character varying) OWNER TO ndawn;
-
---
--- Name: groups(integer); Type: FUNCTION; Schema: public; Owner: ndawn
---
-
-CREATE FUNCTION groups(uid integer) RETURNS SETOF integer
- AS $_$SELECT gid FROM groupmembers WHERE uid = $1 UNION SELECT -1$_$
- LANGUAGE sql STABLE;
-
projects / ndwebbie.git / blobdiff