From: Michael Andreen Date: Mon, 17 Mar 2008 08:33:28 +0000 (+0100) Subject: Updated schema. Without owners this time. X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=2f204838415712eaa1b2c4efe85a66052652373f Updated schema. Without owners this time. --- diff --git a/database/ndawn.sql b/database/ndawn.sql index a3972c3..cb9b5b6 100644 --- a/database/ndawn.sql +++ b/database/ndawn.sql @@ -9,1926 +9,1684 @@ SET client_min_messages = warning; SET escape_string_warning = off; -- --- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres --- - -COMMENT ON SCHEMA public IS 'Standard public schema'; - - --- --- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres +-- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: - -- 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 +-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: - -- -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; - +CREATE PROCEDURAL LANGUAGE plpgsql; -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; +SET search_path = public, pg_catalog; +SET default_tablespace = ''; -ALTER FUNCTION public.calc_rank(tick integer) OWNER TO ndawn; +SET default_with_oids = false; -- --- Name: calc_rank3(integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: alliance_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -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; - +CREATE TABLE alliance_stats ( + id integer NOT NULL, + tick integer NOT NULL, + size integer NOT NULL, + members integer NOT NULL, + score integer NOT NULL, + sizerank integer NOT NULL, + scorerank integer NOT NULL, + size_gain integer NOT NULL, + score_gain integer NOT NULL, + sizerank_gain integer NOT NULL, + scorerank_gain integer NOT NULL, + size_gain_day integer NOT NULL, + score_gain_day integer NOT NULL, + sizerank_gain_day integer NOT NULL, + scorerank_gain_day integer NOT NULL, + members_gain integer NOT NULL, + members_gain_day integer NOT NULL +); -ALTER FUNCTION public.calc_rank3(tick integer) OWNER TO ndawn; -- --- Name: calculate_rankings(integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: alliances; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -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"); -} +CREATE TABLE alliances ( + id integer NOT NULL, + name character varying NOT NULL, + relationship text +); -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; +-- +-- Name: calls; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- +CREATE TABLE calls ( + id integer NOT NULL, + member integer NOT NULL, + dc integer, + landing_tick integer NOT NULL, + info text NOT NULL, + covered boolean DEFAULT false NOT NULL, + shiptypes text, + open boolean DEFAULT true NOT NULL, + ftid integer +); -ALTER FUNCTION public.calculate_rankings(integer) OWNER TO ndawn; -- --- Name: change_member(); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: channel_flags; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -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; - +CREATE TABLE channel_flags ( + id integer NOT NULL, + name text NOT NULL +); -ALTER FUNCTION public.change_member() OWNER TO ndawn; -- --- Name: coords(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: channel_group_flags; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE FUNCTION coords(x integer, y integer, z integer) RETURNS text - AS $_$my ($x,$y,$z) = @_; -return "$x:$y:$z";$_$ - LANGUAGE plperl IMMUTABLE; - +CREATE TABLE channel_group_flags ( + channel integer NOT NULL, + "group" integer NOT NULL, + flag integer NOT NULL +); -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 +-- Name: channels; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -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; - +CREATE TABLE channels ( + id integer NOT NULL, + name text NOT NULL, + description text NOT NULL +); -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 +-- Name: planet_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -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; - +CREATE TABLE planet_stats ( + id integer NOT NULL, + tick integer NOT NULL, + x integer NOT NULL, + y integer NOT NULL, + z integer NOT NULL, + size integer NOT NULL, + score integer NOT NULL, + value integer NOT NULL, + xp integer NOT NULL, + sizerank integer NOT NULL, + scorerank integer NOT NULL, + valuerank integer NOT NULL, + xprank integer NOT NULL, + size_gain integer NOT NULL, + score_gain integer NOT NULL, + value_gain integer NOT NULL, + xp_gain integer NOT NULL, + sizerank_gain integer NOT NULL, + scorerank_gain integer NOT NULL, + valuerank_gain integer NOT NULL, + xprank_gain integer NOT NULL, + size_gain_day integer NOT NULL, + score_gain_day integer NOT NULL, + value_gain_day integer NOT NULL, + xp_gain_day integer NOT NULL, + sizerank_gain_day integer NOT NULL, + scorerank_gain_day integer NOT NULL, + valuerank_gain_day integer NOT NULL, + xprank_gain_day integer NOT NULL +); -ALTER FUNCTION public.findplanetid(character varying, character varying, character varying) OWNER TO ndawn; -- --- Name: groups(integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: planets; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE FUNCTION groups(uid integer) RETURNS SETOF integer - AS $_$SELECT gid FROM groupmembers WHERE uid = $1 UNION SELECT -1$_$ - LANGUAGE sql STABLE; - +CREATE TABLE planets ( + id integer NOT NULL, + ruler character varying NOT NULL, + planet character varying NOT NULL, + race character varying, + nick character varying, + planet_status text, + hit_us integer DEFAULT 0 NOT NULL, + alliance_id integer, + channel text, + ftid integer +); -ALTER FUNCTION public.groups(uid integer) OWNER TO ndawn; -- --- Name: max_bank_hack(integer, integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: current_planet_stats; Type: VIEW; Schema: public; Owner: - -- -CREATE FUNCTION max_bank_hack(metal integer, crystal integer, eonium integer, tvalue integer, value integer) RETURNS integer - AS $_$SELECT LEAST(2000*15*$4::numeric/$5,$1*0.10, 15*7500)::integer + LEAST(2000*15*$4::numeric/$5,$2*0.10, 15*7500)::integer+LEAST(2000*15*$4::numeric/$5,$3*0.10, 15*7500)::integer$_$ - LANGUAGE sql IMMUTABLE; - +CREATE VIEW current_planet_stats AS + SELECT p.id, p.nick, p.planet_status, p.hit_us, ps.x, ps.y, ps.z, p.ruler, p.planet, p.race, ps.size, ps.score, ps.value, ps.xp, ps.sizerank, ps.scorerank, ps.valuerank, ps.xprank, alliances.name AS alliance, alliances.relationship, p.alliance_id, p.channel, p.ftid FROM (((SELECT planet_stats.id, planet_stats.tick, planet_stats.x, planet_stats.y, planet_stats.z, planet_stats.size, planet_stats.score, planet_stats.value, planet_stats.xp, planet_stats.sizerank, planet_stats.scorerank, planet_stats.valuerank, planet_stats.xprank FROM planet_stats WHERE (planet_stats.tick = (SELECT max(planet_stats.tick) AS max FROM planet_stats))) ps NATURAL JOIN planets p) LEFT JOIN alliances ON ((alliances.id = p.alliance_id))); -ALTER FUNCTION public.max_bank_hack(metal integer, crystal integer, eonium integer, tvalue integer, value integer) OWNER TO ndawn; -- --- Name: old_claim(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: current_planet_stats_full; Type: VIEW; Schema: public; Owner: - -- -CREATE FUNCTION old_claim("timestamp" timestamp with time zone) RETURNS boolean - AS $_$SELECT NOW() - '10 minutes'::INTERVAL > $1;$_$ - LANGUAGE sql IMMUTABLE; +CREATE VIEW current_planet_stats_full AS + SELECT p.id, p.nick, p.planet_status, p.hit_us, ps.x, ps.y, ps.z, p.ruler, p.planet, p.race, ps.size, ps.score, ps.value, ps.xp, ps.sizerank, ps.scorerank, ps.valuerank, ps.xprank, alliances.name AS alliance, alliances.relationship, p.alliance_id, p.channel, p.ftid, ps.size_gain, ps.score_gain, ps.value_gain, ps.xp_gain, ps.sizerank_gain, ps.scorerank_gain, ps.valuerank_gain, ps.xprank_gain, ps.size_gain_day, ps.score_gain_day, ps.value_gain_day, ps.xp_gain_day, ps.sizerank_gain_day, ps.scorerank_gain_day, ps.valuerank_gain_day, ps.xprank_gain_day FROM (((SELECT planet_stats.id, planet_stats.tick, planet_stats.x, planet_stats.y, planet_stats.z, planet_stats.size, planet_stats.score, planet_stats.value, planet_stats.xp, planet_stats.sizerank, planet_stats.scorerank, planet_stats.valuerank, planet_stats.xprank, planet_stats.size_gain, planet_stats.score_gain, planet_stats.value_gain, planet_stats.xp_gain, planet_stats.sizerank_gain, planet_stats.scorerank_gain, planet_stats.valuerank_gain, planet_stats.xprank_gain, planet_stats.size_gain_day, planet_stats.score_gain_day, planet_stats.value_gain_day, planet_stats.xp_gain_day, planet_stats.sizerank_gain_day, planet_stats.scorerank_gain_day, planet_stats.valuerank_gain_day, planet_stats.xprank_gain_day FROM planet_stats WHERE (planet_stats.tick = (SELECT max(planet_stats.tick) AS max FROM planet_stats))) ps NATURAL JOIN planets p) LEFT JOIN alliances ON ((alliances.id = p.alliance_id))); + + +-- +-- Name: defense_missions; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- +CREATE TABLE defense_missions ( + call integer NOT NULL, + fleet integer NOT NULL, + announced boolean DEFAULT false NOT NULL, + pointed boolean DEFAULT false NOT NULL +); -ALTER FUNCTION public.old_claim("timestamp" timestamp with time zone) OWNER TO ndawn; -- --- Name: planetcoords(integer, integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: defense_requests; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE FUNCTION planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) RETURNS record - AS $_$SELECT x,y,z FROM planet_stats WHERE id = $1 AND (tick >= $2 OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$ - LANGUAGE sql STABLE; +CREATE TABLE defense_requests ( + id integer NOT NULL, + uid integer NOT NULL, + message text NOT NULL, + sent boolean DEFAULT false NOT NULL +); -ALTER FUNCTION public.planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) OWNER TO ndawn; +-- +-- Name: dumps; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE dumps ( + tick integer NOT NULL, + type text NOT NULL, + dump text NOT NULL, + modified integer DEFAULT 0 NOT NULL +); + -- --- Name: planetid(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: fleet_scans; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE FUNCTION planetid(x integer, y integer, z integer, tick integer) RETURNS integer - AS $_$SELECT id FROM planet_stats WHERE x = $1 AND y = $2 AND z = $3 AND (tick >= $4 OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$ - LANGUAGE sql STABLE; +CREATE TABLE fleet_scans ( + id integer NOT NULL, + scan integer NOT NULL +); -ALTER FUNCTION public.planetid(x integer, y integer, z integer, tick integer) OWNER TO ndawn; +-- +-- Name: fleet_ships; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE fleet_ships ( + id integer NOT NULL, + ship text NOT NULL, + amount integer NOT NULL, + num integer NOT NULL +); + -- --- Name: plperl_call_handler(); Type: FUNCTION; Schema: public; Owner: postgres +-- Name: fleets; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE FUNCTION plperl_call_handler() RETURNS language_handler - AS '$libdir/plperl', 'plperl_call_handler' - LANGUAGE c; - +CREATE TABLE fleets ( + uid integer NOT NULL, + target integer, + mission text NOT NULL, + tick integer NOT NULL, + id integer NOT NULL, + eta integer, + back integer, + sender integer NOT NULL, + amount integer, + name text NOT NULL, + ingal boolean DEFAULT false NOT NULL +); -ALTER FUNCTION public.plperl_call_handler() OWNER TO postgres; -- --- Name: populate_ticks(); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: forum_access; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE FUNCTION populate_ticks() RETURNS void - AS $_$my $rv = spi_exec_query("SELECT tick FROM planet_stats ORDER BY tick DESC LIMIT 1;"); -my $nrows = $rv->{processed}; -if ($nrows == 1){ -$tick = $rv->{rows}[0]->{tick}; -spi_exec_query("DELETE FROM ticks;"); -spi_exec_query("INSERT INTO ticks(tick) (SELECT generate_series(36, tick,tick/50) FROM (SELECT tick FROM planet_stats ORDER BY tick DESC LIMIT 1) as foo);"); -spi_exec_query("INSERT INTO ticks(tick) VALUES($tick)"); -}$_$ - LANGUAGE plperl; - +CREATE TABLE forum_access ( + fbid integer NOT NULL, + gid integer NOT NULL, + post boolean DEFAULT false NOT NULL, + moderate boolean DEFAULT false NOT NULL +); -ALTER FUNCTION public.populate_ticks() OWNER TO ndawn; -- --- Name: tick(); Type: FUNCTION; Schema: public; Owner: postgres +-- Name: forum_boards; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE FUNCTION tick() RETURNS integer - AS $$SELECT value::integer FROM misc WHERE id = 'TICK'$$ - LANGUAGE sql STABLE; - +CREATE TABLE forum_boards ( + fbid integer NOT NULL, + fcid integer NOT NULL, + board text NOT NULL +); -ALTER FUNCTION public.tick() OWNER TO postgres; -- --- Name: unclaim_target(); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: forum_categories; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE FUNCTION unclaim_target() RETURNS "trigger" - AS $_X$ -if ($_TD->{event} eq 'DELETE' && $_TD->{old}{launched} eq 't'){ - my $uid = $_TD->{old}{uid}; - my $query = spi_prepare(q{UPDATE users - SET attack_points = attack_points - 1 - WHERE uid = $1},'int4'); - spi_exec_prepared($query,$uid); - spi_freeplan($query); -} -return; -$_X$ - LANGUAGE plperl; - +CREATE TABLE forum_categories ( + fcid integer NOT NULL, + category text NOT NULL +); -ALTER FUNCTION public.unclaim_target() OWNER TO ndawn; -- --- Name: update_user_planet(); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: forum_posts; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE FUNCTION update_user_planet() RETURNS "trigger" - AS $_X$my $rv = spi_exec_query("SELECT * FROM groupmembers WHERE uid = $_TD->{new}{uid} AND gid = 2;"); -if ($rv->{processed} == 1){# && ($_TD->{old}{planet} != $_TD->{new}{planet})){ - if ($_TD->{old}{planet}){ - spi_exec_query("UPDATE planets SET alliance_id = NULL WHERE id = $_TD->{old}{planet};"); - } - if ($_TD->{new}{planet}){ - spi_exec_query("UPDATE planets SET alliance_id = 1 WHERE id = $_TD->{new}{planet};"); - } -} -if ($_TD->{old}{planet}){ - spi_exec_query("UPDATE planets SET nick = NULL WHERE id = $_TD->{old}{planet};"); -} -if ($_TD->{new}{planet}){ - spi_exec_query("UPDATE planets SET nick = '$_TD->{new}{username}' WHERE id = $_TD->{new}{planet};"); -} -return;$_X$ - LANGUAGE plperl; - +CREATE TABLE forum_posts ( + fpid integer NOT NULL, + ftid integer NOT NULL, + message text NOT NULL, + "time" timestamp with time zone DEFAULT now() NOT NULL, + uid integer NOT NULL, + textsearch tsvector +); -ALTER FUNCTION public.update_user_planet() OWNER TO ndawn; -- --- Name: updated_target(); Type: FUNCTION; Schema: public; Owner: ndawn +-- Name: forum_thread_visits; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE FUNCTION updated_target() RETURNS "trigger" - AS $_X$my $query = spi_prepare('UPDATE raid_targets SET modified = NOW() WHERE id = $1','int4'); -my $target = $_TD->{new}{target}; -$target = $_TD->{old}{target} if ($_TD->{event} eq 'DELETE'); -spi_exec_prepared($query,$target); -spi_freeplan($query);$_X$ - LANGUAGE plperl; - +CREATE TABLE forum_thread_visits ( + uid integer NOT NULL, + ftid integer NOT NULL, + "time" timestamp with time zone DEFAULT now() NOT NULL +); -ALTER FUNCTION public.updated_target() OWNER TO ndawn; -- --- Name: concat(text); Type: AGGREGATE; Schema: public; Owner: ndawn +-- Name: forum_threads; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE AGGREGATE concat(text) ( - SFUNC = textcat, - STYPE = text, - INITCOND = '' +CREATE TABLE forum_threads ( + ftid integer NOT NULL, + fbid integer NOT NULL, + subject text NOT NULL, + sticky boolean DEFAULT false NOT NULL, + uid integer NOT NULL ); -ALTER AGGREGATE public.concat(text) OWNER TO ndawn; - -SET default_tablespace = ''; - -SET default_with_oids = false; - -- --- Name: alliance_stats; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: galaxies; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE TABLE alliance_stats ( - id integer NOT NULL, +CREATE TABLE galaxies ( + x integer NOT NULL, + y integer NOT NULL, tick integer NOT NULL, size integer NOT NULL, - members integer NOT NULL, score integer NOT NULL, + value integer NOT NULL, + xp integer NOT NULL, + planets integer NOT NULL, sizerank integer NOT NULL, scorerank integer NOT NULL, + valuerank integer NOT NULL, + xprank integer NOT NULL, size_gain integer NOT NULL, score_gain integer NOT NULL, + value_gain integer NOT NULL, + xp_gain integer NOT NULL, + planets_gain integer NOT NULL, sizerank_gain integer NOT NULL, scorerank_gain integer NOT NULL, + valuerank_gain integer NOT NULL, + xprank_gain integer NOT NULL, size_gain_day integer NOT NULL, score_gain_day integer NOT NULL, + value_gain_day integer NOT NULL, + xp_gain_day integer NOT NULL, + planets_gain_day integer NOT NULL, sizerank_gain_day integer NOT NULL, scorerank_gain_day integer NOT NULL, - members_gain integer NOT NULL, - members_gain_day integer NOT NULL + valuerank_gain_day integer NOT NULL, + xprank_gain_day integer NOT NULL ); -ALTER TABLE public.alliance_stats OWNER TO ndawn; - -- --- Name: alliances; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: graphs; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE TABLE alliances ( +CREATE TABLE graphs ( + type text NOT NULL, id integer NOT NULL, - name character varying NOT NULL, - relationship text + last_modified timestamp with time zone DEFAULT now() NOT NULL, + tick integer NOT NULL, + img bytea NOT NULL ); -ALTER TABLE public.alliances OWNER TO ndawn; - -- --- Name: alliances_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: groupmembers; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE SEQUENCE alliances_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - +CREATE TABLE groupmembers ( + gid integer NOT NULL, + uid integer NOT NULL +); -ALTER TABLE public.alliances_id_seq OWNER TO ndawn; -- --- Name: alliances_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: groups; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -ALTER SEQUENCE alliances_id_seq OWNED BY alliances.id; +CREATE TABLE groups ( + gid integer NOT NULL, + groupname text NOT NULL, + flag character(1), + attack boolean DEFAULT false NOT NULL +); -- --- Name: calls; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: incomings; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE TABLE calls ( - id integer NOT NULL, - member integer NOT NULL, - dc integer, - landing_tick integer NOT NULL, - info text NOT NULL, - covered boolean DEFAULT false NOT NULL, - shiptypes text, - open boolean DEFAULT true NOT NULL, - ftid integer +CREATE TABLE incomings ( + call integer NOT NULL, + sender integer NOT NULL, + eta integer NOT NULL, + amount integer NOT NULL, + fleet text NOT NULL, + shiptype text DEFAULT '?'::text NOT NULL, + id integer NOT NULL ); -ALTER TABLE public.calls OWNER TO ndawn; - -- --- Name: calls_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: misc; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE SEQUENCE calls_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - +CREATE TABLE misc ( + id text NOT NULL, + value text +); -ALTER TABLE public.calls_id_seq OWNER TO ndawn; -- --- Name: calls_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: planet_data; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -ALTER SEQUENCE calls_id_seq OWNED BY calls.id; +CREATE TABLE planet_data ( + id integer NOT NULL, + scan integer NOT NULL, + tick integer NOT NULL, + rid integer NOT NULL, + amount integer NOT NULL +); -- --- Name: channel_flags; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_data_types; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE TABLE channel_flags ( +CREATE TABLE planet_data_types ( id integer NOT NULL, + category text NOT NULL, name text NOT NULL ); -ALTER TABLE public.channel_flags OWNER TO ndawn; - --- --- Name: channel_flags_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn --- - -CREATE SEQUENCE channel_flags_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - - -ALTER TABLE public.channel_flags_id_seq OWNER TO ndawn; - --- --- Name: channel_flags_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn --- - -ALTER SEQUENCE channel_flags_id_seq OWNED BY channel_flags.id; - - -- --- Name: channel_group_flags; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: scans; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE TABLE channel_group_flags ( - channel integer NOT NULL, - "group" integer NOT NULL, - flag integer NOT NULL +CREATE TABLE scans ( + tick integer NOT NULL, + scan_id numeric(10,0) NOT NULL, + planet integer, + type text, + uid integer DEFAULT -1 NOT NULL, + groupscan boolean DEFAULT false NOT NULL, + parsed boolean DEFAULT false NOT NULL, + id integer NOT NULL ); -ALTER TABLE public.channel_group_flags OWNER TO ndawn; - -- --- Name: channels; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_scans; Type: VIEW; Schema: public; Owner: - -- -CREATE TABLE channels ( - id integer NOT NULL, - name text NOT NULL, - description text NOT NULL -); - +CREATE VIEW planet_scans AS + SELECT DISTINCT ON (s.planet) s.id, s.planet, s.tick, m.metal, c.crystal, e.eonium, mr.metal_roids, cr.crystal_roids, er.eonium_roids FROM ((((((scans s JOIN (SELECT planet_data.scan AS id, planet_data.amount AS metal_roids FROM planet_data WHERE (planet_data.rid = 1)) mr USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS crystal_roids FROM planet_data WHERE (planet_data.rid = 2)) cr USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS eonium_roids FROM planet_data WHERE (planet_data.rid = 3)) er USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS metal FROM planet_data WHERE (planet_data.rid = 4)) m USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS crystal FROM planet_data WHERE (planet_data.rid = 5)) c USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS eonium FROM planet_data WHERE (planet_data.rid = 6)) e USING (id)) ORDER BY s.planet, s.tick DESC, s.id DESC; -ALTER TABLE public.channels OWNER TO ndawn; -- --- Name: channels_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: raid_access; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE SEQUENCE channels_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - +CREATE TABLE raid_access ( + raid integer NOT NULL, + gid integer NOT NULL +); -ALTER TABLE public.channels_id_seq OWNER TO ndawn; -- --- Name: channels_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: raid_claims; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -ALTER SEQUENCE channels_id_seq OWNED BY channels.id; +CREATE TABLE raid_claims ( + target integer NOT NULL, + uid integer NOT NULL, + wave integer NOT NULL, + joinable boolean DEFAULT false NOT NULL, + launched boolean DEFAULT false NOT NULL, + "timestamp" timestamp with time zone DEFAULT now() NOT NULL +); -- --- Name: planet_stats; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: raid_targets; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE TABLE planet_stats ( +CREATE TABLE raid_targets ( id integer NOT NULL, - tick integer NOT NULL, - x integer NOT NULL, - y integer NOT NULL, - z integer NOT NULL, - size integer NOT NULL, - score integer NOT NULL, - value integer NOT NULL, - xp integer NOT NULL, - sizerank integer NOT NULL, - scorerank integer NOT NULL, - valuerank integer NOT NULL, - xprank integer NOT NULL, - size_gain integer NOT NULL, - score_gain integer NOT NULL, - value_gain integer NOT NULL, - xp_gain integer NOT NULL, - sizerank_gain integer NOT NULL, - scorerank_gain integer NOT NULL, - valuerank_gain integer NOT NULL, - xprank_gain integer NOT NULL, - size_gain_day integer NOT NULL, - score_gain_day integer NOT NULL, - value_gain_day integer NOT NULL, - xp_gain_day integer NOT NULL, - sizerank_gain_day integer NOT NULL, - scorerank_gain_day integer NOT NULL, - valuerank_gain_day integer NOT NULL, - xprank_gain_day integer NOT NULL + raid integer NOT NULL, + planet integer NOT NULL, + comment text, + modified timestamp with time zone DEFAULT now() NOT NULL ); -ALTER TABLE public.planet_stats OWNER TO ndawn; - -- --- Name: planets; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: raids; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE TABLE planets ( +CREATE TABLE raids ( id integer NOT NULL, - ruler character varying NOT NULL, - planet character varying NOT NULL, - race character varying, - nick character varying, - planet_status text, - hit_us integer DEFAULT 0 NOT NULL, - alliance_id integer, - channel text, - ftid integer + tick integer NOT NULL, + open boolean DEFAULT false NOT NULL, + waves integer DEFAULT 3 NOT NULL, + message text NOT NULL, + removed boolean DEFAULT false NOT NULL, + released_coords boolean DEFAULT false NOT NULL ); -ALTER TABLE public.planets OWNER TO ndawn; - -- --- Name: current_planet_stats; Type: VIEW; Schema: public; Owner: ndawn +-- Name: ship_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE VIEW current_planet_stats AS - SELECT p.id, p.nick, p.planet_status, p.hit_us, ps.x, ps.y, ps.z, p.ruler, p.planet, p.race, ps.size, ps.score, ps.value, ps.xp, ps.sizerank, ps.scorerank, ps.valuerank, ps.xprank, alliances.name AS alliance, alliances.relationship, p.alliance_id, p.channel, p.ftid FROM (((SELECT planet_stats.id, planet_stats.tick, planet_stats.x, planet_stats.y, planet_stats.z, planet_stats.size, planet_stats.score, planet_stats.value, planet_stats.xp, planet_stats.sizerank, planet_stats.scorerank, planet_stats.valuerank, planet_stats.xprank FROM planet_stats WHERE (planet_stats.tick = (SELECT max(planet_stats.tick) AS max FROM planet_stats))) ps NATURAL JOIN planets p) LEFT JOIN alliances ON ((alliances.id = p.alliance_id))); - +CREATE TABLE ship_stats ( + name text NOT NULL, + class text NOT NULL, + t1 text NOT NULL, + type text NOT NULL, + init integer NOT NULL, + armor integer NOT NULL, + damage integer NOT NULL, + metal integer NOT NULL, + crystal integer NOT NULL, + eonium integer NOT NULL, + race text NOT NULL, + guns integer DEFAULT 0 NOT NULL, + eres integer DEFAULT 0 NOT NULL, + t2 text, + t3 text +); -ALTER TABLE public.current_planet_stats OWNER TO ndawn; -- --- Name: current_planet_stats_full; Type: VIEW; Schema: public; Owner: ndawn +-- Name: smslist; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE VIEW current_planet_stats_full AS - SELECT p.id, p.nick, p.planet_status, p.hit_us, ps.x, ps.y, ps.z, p.ruler, p.planet, p.race, ps.size, ps.score, ps.value, ps.xp, ps.sizerank, ps.scorerank, ps.valuerank, ps.xprank, alliances.name AS alliance, alliances.relationship, p.alliance_id, p.channel, p.ftid, ps.size_gain, ps.score_gain, ps.value_gain, ps.xp_gain, ps.sizerank_gain, ps.scorerank_gain, ps.valuerank_gain, ps.xprank_gain, ps.size_gain_day, ps.score_gain_day, ps.value_gain_day, ps.xp_gain_day, ps.sizerank_gain_day, ps.scorerank_gain_day, ps.valuerank_gain_day, ps.xprank_gain_day FROM (((SELECT planet_stats.id, planet_stats.tick, planet_stats.x, planet_stats.y, planet_stats.z, planet_stats.size, planet_stats.score, planet_stats.value, planet_stats.xp, planet_stats.sizerank, planet_stats.scorerank, planet_stats.valuerank, planet_stats.xprank, planet_stats.size_gain, planet_stats.score_gain, planet_stats.value_gain, planet_stats.xp_gain, planet_stats.sizerank_gain, planet_stats.scorerank_gain, planet_stats.valuerank_gain, planet_stats.xprank_gain, planet_stats.size_gain_day, planet_stats.score_gain_day, planet_stats.value_gain_day, planet_stats.xp_gain_day, planet_stats.sizerank_gain_day, planet_stats.scorerank_gain_day, planet_stats.valuerank_gain_day, planet_stats.xprank_gain_day FROM planet_stats WHERE (planet_stats.tick = (SELECT max(planet_stats.tick) AS max FROM planet_stats))) ps NATURAL JOIN planets p) LEFT JOIN alliances ON ((alliances.id = p.alliance_id))); - +CREATE TABLE smslist ( + nick text NOT NULL, + sms text NOT NULL, + info text +); -ALTER TABLE public.current_planet_stats_full OWNER TO ndawn; -- --- Name: defense_missions; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: structure_scans; Type: VIEW; Schema: public; Owner: - -- -CREATE TABLE defense_missions ( - call integer NOT NULL, - fleet integer NOT NULL, - announced boolean DEFAULT false NOT NULL, - pointed boolean DEFAULT false NOT NULL -); - +CREATE VIEW structure_scans AS + SELECT DISTINCT ON (s.planet) s.id, s.planet, s.tick, t.total, d.distorters, sc.seccents FROM (((scans s JOIN (SELECT planet_data.scan AS id, sum(planet_data.amount) AS total FROM planet_data WHERE ((planet_data.rid >= 14) AND (planet_data.rid <= 24)) GROUP BY planet_data.scan) t USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS distorters FROM planet_data WHERE (planet_data.rid = 18)) d USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS seccents FROM planet_data WHERE (planet_data.rid = 24)) sc USING (id)) ORDER BY s.planet, s.tick DESC, s.id DESC; -ALTER TABLE public.defense_missions OWNER TO ndawn; -- --- Name: defense_requests; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: users; Type: TABLE; Schema: public; Owner: -; Tablespace: -- -CREATE TABLE defense_requests ( - id integer NOT NULL, +CREATE TABLE users ( uid integer NOT NULL, - message text NOT NULL, - sent boolean DEFAULT false NOT NULL + username text NOT NULL, + planet integer, + password text NOT NULL, + attack_points integer DEFAULT 0 NOT NULL, + defense_points integer DEFAULT 0 NOT NULL, + scan_points integer DEFAULT 0 NOT NULL, + humor_points integer DEFAULT 0 NOT NULL, + hostmask text, + sms text, + rank integer, + laston timestamp with time zone, + ftid integer, + css text, + last_forum_visit timestamp with time zone, + email text, + pnick text, + info text ); -ALTER TABLE public.defense_requests OWNER TO ndawn; - -- --- Name: defense_requests_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: usersingroup; Type: VIEW; Schema: public; Owner: - -- -CREATE SEQUENCE defense_requests_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - +CREATE VIEW usersingroup AS + SELECT groups.gid, groups.groupname, users.uid, users.username FROM ((users NATURAL JOIN groupmembers) NATURAL JOIN groups); -ALTER TABLE public.defense_requests_id_seq OWNER TO ndawn; -- --- Name: defense_requests_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: add_call(); Type: FUNCTION; Schema: public; Owner: - -- -ALTER SEQUENCE defense_requests_id_seq OWNED BY defense_requests.id; +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; -- --- Name: dumps; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: add_intel(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, integer); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE dumps ( - tick integer NOT NULL, - "type" text NOT NULL, - dump text NOT NULL, - modified integer DEFAULT 0 NOT NULL -); - +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 TABLE public.dumps OWNER TO ndawn; -- --- Name: fleet_scans; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE fleet_scans ( - id integer NOT NULL, - scan integer NOT NULL -); - +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 TABLE public.fleet_scans OWNER TO ndawn; -- --- Name: fleet_ships; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE fleet_ships ( - id integer NOT NULL, - ship text NOT NULL, - amount integer NOT NULL -); - +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 TABLE public.fleet_ships OWNER TO ndawn; -- --- Name: fleets; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: add_intel4(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, integer); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE fleets ( - uid integer NOT NULL, - target integer, - mission text NOT NULL, - tick integer NOT NULL, - id integer NOT NULL, - eta integer, - back integer, - sender integer NOT NULL, - amount integer, - name text NOT NULL, - ingal boolean DEFAULT false NOT NULL -); - +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 TABLE public.fleets OWNER TO ndawn; -- --- Name: fleets_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: add_user(); Type: FUNCTION; Schema: public; Owner: - -- -CREATE SEQUENCE fleets_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - +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 TABLE public.fleets_id_seq OWNER TO ndawn; -- --- Name: fleets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: calc_rank(integer); Type: FUNCTION; Schema: public; Owner: - -- -ALTER SEQUENCE fleets_id_seq OWNED BY fleets.id; - +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; +} --- --- Name: forum_access; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: --- +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; +} -CREATE TABLE forum_access ( - fbid integer NOT NULL, - gid integer NOT NULL, - post boolean DEFAULT false NOT NULL, - moderate boolean DEFAULT false NOT NULL -); +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 TABLE public.forum_access OWNER TO ndawn; -- --- Name: forum_boards; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: calc_rank3(integer); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE forum_boards ( - fbid integer NOT NULL, - fcid integer NOT NULL, - board text NOT NULL -); - - -ALTER TABLE public.forum_boards OWNER TO 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; +} --- --- Name: forum_boards_fbid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn --- +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; +} -CREATE SEQUENCE forum_boards_fbid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +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 TABLE public.forum_boards_fbid_seq OWNER TO ndawn; -- --- Name: forum_boards_fbid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: calculate_rankings(integer); Type: FUNCTION; Schema: public; Owner: - -- -ALTER SEQUENCE forum_boards_fbid_seq OWNED BY forum_boards.fbid; - +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)"); +} --- --- Name: forum_categories; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: --- +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"); +} -CREATE TABLE forum_categories ( - fcid integer NOT NULL, - category text NOT NULL -); +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 TABLE public.forum_categories OWNER TO ndawn; -- --- Name: forum_categories_fcid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: change_member(); Type: FUNCTION; Schema: public; Owner: - -- -CREATE SEQUENCE forum_categories_fcid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - +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 TABLE public.forum_categories_fcid_seq OWNER TO ndawn; -- --- Name: forum_categories_fcid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: coords(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: - -- -ALTER SEQUENCE forum_categories_fcid_seq OWNED BY forum_categories.fcid; +CREATE FUNCTION coords(x integer, y integer, z integer) RETURNS text + AS $_$my ($x,$y,$z) = @_; +return "$x:$y:$z";$_$ + LANGUAGE plperl IMMUTABLE; -- --- Name: forum_posts; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: find_alliance_id(character varying); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE forum_posts ( - fpid integer NOT NULL, - ftid integer NOT NULL, - message text NOT NULL, - "time" timestamp with time zone DEFAULT now() NOT NULL, - uid integer NOT NULL -); - - -ALTER TABLE public.forum_posts OWNER TO 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; --- --- Name: forum_posts_fpid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn --- -CREATE SEQUENCE forum_posts_fpid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +-- +-- Name: findplanetid(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: - +-- + +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,uid) VALUES($1,$2,$3,-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 TABLE public.forum_posts_fpid_seq OWNER TO ndawn; -- --- Name: forum_posts_fpid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: groups(integer); Type: FUNCTION; Schema: public; Owner: - -- -ALTER SEQUENCE forum_posts_fpid_seq OWNED BY forum_posts.fpid; +CREATE FUNCTION groups(uid integer) RETURNS SETOF integer + AS $_$SELECT gid FROM groupmembers WHERE uid = $1 UNION SELECT -1$_$ + LANGUAGE sql STABLE; -- --- Name: forum_thread_visits; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: max_bank_hack(integer, integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE forum_thread_visits ( - uid integer NOT NULL, - ftid integer NOT NULL, - "time" timestamp with time zone DEFAULT now() NOT NULL -); - +CREATE FUNCTION max_bank_hack(metal integer, crystal integer, eonium integer, tvalue integer, value integer) RETURNS integer + AS $_$SELECT LEAST(2000*15*$4::numeric/$5,$1*0.10, 15*7500)::integer + LEAST(2000*15*$4::numeric/$5,$2*0.10, 15*7500)::integer+LEAST(2000*15*$4::numeric/$5,$3*0.10, 15*7500)::integer$_$ + LANGUAGE sql IMMUTABLE; -ALTER TABLE public.forum_thread_visits OWNER TO ndawn; -- --- Name: forum_threads; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: old_claim(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE forum_threads ( - ftid integer NOT NULL, - fbid integer NOT NULL, - subject text NOT NULL, - sticky boolean DEFAULT false NOT NULL, - uid integer -); - +CREATE FUNCTION old_claim("timestamp" timestamp with time zone) RETURNS boolean + AS $_$SELECT NOW() - '10 minutes'::INTERVAL > $1;$_$ + LANGUAGE sql IMMUTABLE; -ALTER TABLE public.forum_threads OWNER TO ndawn; -- --- Name: forum_threads_ftid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: planetcoords(integer, integer); Type: FUNCTION; Schema: public; Owner: - -- -CREATE SEQUENCE forum_threads_ftid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - +CREATE FUNCTION planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) RETURNS record + AS $_$SELECT x,y,z FROM planet_stats WHERE id = $1 AND (tick >= $2 OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$ + LANGUAGE sql STABLE; -ALTER TABLE public.forum_threads_ftid_seq OWNER TO ndawn; -- --- Name: forum_threads_ftid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: planetid(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: - -- -ALTER SEQUENCE forum_threads_ftid_seq OWNED BY forum_threads.ftid; +CREATE FUNCTION planetid(x integer, y integer, z integer, tick integer) RETURNS integer + AS $_$SELECT id FROM planet_stats WHERE x = $1 AND y = $2 AND z = $3 AND (tick >= $4 OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$ + LANGUAGE sql STABLE; -- --- Name: galaxies; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: populate_ticks(); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE galaxies ( - x integer NOT NULL, - y integer NOT NULL, - tick integer NOT NULL, - size integer NOT NULL, - score integer NOT NULL, - value integer NOT NULL, - xp integer NOT NULL, - planets integer NOT NULL, - sizerank integer NOT NULL, - scorerank integer NOT NULL, - valuerank integer NOT NULL, - xprank integer NOT NULL, - size_gain integer NOT NULL, - score_gain integer NOT NULL, - value_gain integer NOT NULL, - xp_gain integer NOT NULL, - planets_gain integer NOT NULL, - sizerank_gain integer NOT NULL, - scorerank_gain integer NOT NULL, - valuerank_gain integer NOT NULL, - xprank_gain integer NOT NULL, - size_gain_day integer NOT NULL, - score_gain_day integer NOT NULL, - value_gain_day integer NOT NULL, - xp_gain_day integer NOT NULL, - planets_gain_day integer NOT NULL, - sizerank_gain_day integer NOT NULL, - scorerank_gain_day integer NOT NULL, - valuerank_gain_day integer NOT NULL, - xprank_gain_day integer NOT NULL -); - +CREATE FUNCTION populate_ticks() RETURNS void + AS $_$my $rv = spi_exec_query("SELECT tick FROM planet_stats ORDER BY tick DESC LIMIT 1;"); +my $nrows = $rv->{processed}; +if ($nrows == 1){ +$tick = $rv->{rows}[0]->{tick}; +spi_exec_query("DELETE FROM ticks;"); +spi_exec_query("INSERT INTO ticks(tick) (SELECT generate_series(36, tick,tick/50) FROM (SELECT tick FROM planet_stats ORDER BY tick DESC LIMIT 1) as foo);"); +spi_exec_query("INSERT INTO ticks(tick) VALUES($tick)"); +}$_$ + LANGUAGE plperl; -ALTER TABLE public.galaxies OWNER TO ndawn; -- --- Name: graphs; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: tick(); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE graphs ( - "type" text NOT NULL, - id integer NOT NULL, - last_modified timestamp with time zone DEFAULT now() NOT NULL, - tick integer NOT NULL, - img bytea NOT NULL -); - +CREATE FUNCTION tick() RETURNS integer + AS $$SELECT value::integer FROM misc WHERE id = 'TICK'$$ + LANGUAGE sql STABLE; -ALTER TABLE public.graphs OWNER TO ndawn; -- --- Name: groupmembers; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: unclaim_target(); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE groupmembers ( - gid integer NOT NULL, - uid integer NOT NULL -); - +CREATE FUNCTION unclaim_target() RETURNS trigger + AS $_X$ +if ($_TD->{event} eq 'DELETE' && $_TD->{old}{launched} eq 't'){ + my $uid = $_TD->{old}{uid}; + my $query = spi_prepare(q{UPDATE users + SET attack_points = attack_points - 1 + WHERE uid = $1},'int4'); + spi_exec_prepared($query,$uid); + spi_freeplan($query); +} +return; +$_X$ + LANGUAGE plperl; -ALTER TABLE public.groupmembers OWNER TO ndawn; -- --- Name: groups; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: update_forum_post(); Type: FUNCTION; Schema: public; Owner: - -- -CREATE TABLE groups ( - gid integer NOT NULL, - groupname text NOT NULL, - flag character(1), - attack boolean DEFAULT false NOT NULL -); - +CREATE FUNCTION update_forum_post() RETURNS trigger + AS $$ +DECLARE + rec RECORD; +BEGIN + SELECT setweight(to_tsvector(coalesce(ft.subject,'')), 'A') + || setweight(to_tsvector(coalesce(u.username,'')), 'B') AS ts + INTO STRICT rec + FROM forum_threads ft, users u + WHERE NEW.ftid = ft.ftid AND u.uid = NEW.uid; + NEW.textsearch := rec.ts + || setweight(to_tsvector(coalesce(NEW.message,'')), 'D'); + return NEW; +END; +$$ + LANGUAGE plpgsql; -ALTER TABLE public.groups OWNER TO ndawn; -- --- Name: groups_gid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: update_user_planet(); Type: FUNCTION; Schema: public; Owner: - -- -CREATE SEQUENCE groups_gid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - +CREATE FUNCTION update_user_planet() RETURNS trigger + AS $_X$my $rv = spi_exec_query("SELECT * FROM groupmembers WHERE uid = $_TD->{new}{uid} AND gid = 2;"); +if ($rv->{processed} == 1){# && ($_TD->{old}{planet} != $_TD->{new}{planet})){ + if ($_TD->{old}{planet}){ + spi_exec_query("UPDATE planets SET alliance_id = NULL WHERE id = $_TD->{old}{planet};"); + } + if ($_TD->{new}{planet}){ + spi_exec_query("UPDATE planets SET alliance_id = 1 WHERE id = $_TD->{new}{planet};"); + } +} +if ($_TD->{old}{planet}){ + spi_exec_query("UPDATE planets SET nick = NULL WHERE id = $_TD->{old}{planet};"); +} +if ($_TD->{new}{planet}){ + spi_exec_query("UPDATE planets SET nick = '$_TD->{new}{username}' WHERE id = $_TD->{new}{planet};"); +} +return;$_X$ + LANGUAGE plperl; -ALTER TABLE public.groups_gid_seq OWNER TO ndawn; -- --- Name: groups_gid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: updated_target(); Type: FUNCTION; Schema: public; Owner: - -- -ALTER SEQUENCE groups_gid_seq OWNED BY groups.gid; +CREATE FUNCTION updated_target() RETURNS trigger + AS $_X$my $query = spi_prepare('UPDATE raid_targets SET modified = NOW() WHERE id = $1','int4'); +my $target = $_TD->{new}{target}; +$target = $_TD->{old}{target} if ($_TD->{event} eq 'DELETE'); +spi_exec_prepared($query,$target); +spi_freeplan($query);$_X$ + LANGUAGE plperl; -- --- Name: incomings; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: concat(text); Type: AGGREGATE; Schema: public; Owner: - -- -CREATE TABLE incomings ( - call integer NOT NULL, - sender integer NOT NULL, - eta integer NOT NULL, - amount integer NOT NULL, - fleet text NOT NULL, - shiptype text DEFAULT '?'::text NOT NULL, - id integer NOT NULL +CREATE AGGREGATE concat(text) ( + SFUNC = textcat, + STYPE = text, + INITCOND = '' ); -ALTER TABLE public.incomings OWNER TO ndawn; - -- --- Name: incomings_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: alliances_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE SEQUENCE incomings_id_seq +CREATE SEQUENCE alliances_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -ALTER TABLE public.incomings_id_seq OWNER TO ndawn; - --- --- Name: incomings_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn --- - -ALTER SEQUENCE incomings_id_seq OWNED BY incomings.id; - - -- --- Name: intel_messages; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: alliances_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE TABLE intel_messages ( - id integer NOT NULL, - uid integer NOT NULL, - message text NOT NULL, - handled boolean DEFAULT false NOT NULL, - handled_by integer, - report_date timestamp with time zone DEFAULT now() NOT NULL -); - +ALTER SEQUENCE alliances_id_seq OWNED BY alliances.id; -ALTER TABLE public.intel_messages OWNER TO ndawn; -- --- Name: intel_messages_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: calls_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE SEQUENCE intel_messages_id_seq +CREATE SEQUENCE calls_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -ALTER TABLE public.intel_messages_id_seq OWNER TO ndawn; - --- --- Name: intel_messages_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn --- - -ALTER SEQUENCE intel_messages_id_seq OWNED BY intel_messages.id; - - -- --- Name: log; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: calls_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE TABLE log ( - id integer NOT NULL, - uid integer NOT NULL, - "time" timestamp without time zone DEFAULT now() NOT NULL, - text text NOT NULL -); - +ALTER SEQUENCE calls_id_seq OWNED BY calls.id; -ALTER TABLE public.log OWNER TO ndawn; -- --- Name: log_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: channel_flags_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE SEQUENCE log_id_seq +CREATE SEQUENCE channel_flags_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -ALTER TABLE public.log_id_seq OWNER TO ndawn; - --- --- Name: log_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn --- - -ALTER SEQUENCE log_id_seq OWNED BY log.id; - - -- --- Name: misc; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: channel_flags_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE TABLE misc ( - id text NOT NULL, - value text -); - +ALTER SEQUENCE channel_flags_id_seq OWNED BY channel_flags.id; -ALTER TABLE public.misc OWNER TO ndawn; -- --- Name: planet_data; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: channels_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE TABLE planet_data ( - id integer NOT NULL, - scan integer NOT NULL, - tick integer NOT NULL, - rid integer NOT NULL, - amount integer NOT NULL -); - +CREATE SEQUENCE channels_id_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -ALTER TABLE public.planet_data OWNER TO ndawn; -- --- Name: planet_data_types; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: channels_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE TABLE planet_data_types ( - id integer NOT NULL, - category text NOT NULL, - name text NOT NULL -); - +ALTER SEQUENCE channels_id_seq OWNED BY channels.id; -ALTER TABLE public.planet_data_types OWNER TO ndawn; -- --- Name: planet_data_types_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: defense_requests_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE SEQUENCE planet_data_types_id_seq +CREATE SEQUENCE defense_requests_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -ALTER TABLE public.planet_data_types_id_seq OWNER TO ndawn; - -- --- Name: planet_data_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: defense_requests_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -ALTER SEQUENCE planet_data_types_id_seq OWNED BY planet_data_types.id; +ALTER SEQUENCE defense_requests_id_seq OWNED BY defense_requests.id; -- --- Name: planet_graphs; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: fleet_ships_num_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE TABLE planet_graphs ( - planet integer NOT NULL, - tick integer NOT NULL, - "type" text NOT NULL, - graph bytea NOT NULL -); - +CREATE SEQUENCE fleet_ships_num_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -ALTER TABLE public.planet_graphs OWNER TO ndawn; -- --- Name: scans; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: fleet_ships_num_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE TABLE scans ( - tick integer NOT NULL, - scan_id numeric(10,0) NOT NULL, - planet integer, - "type" text, - uid integer DEFAULT -1 NOT NULL, - groupscan boolean DEFAULT false NOT NULL, - parsed boolean DEFAULT false NOT NULL, - id integer NOT NULL -); +ALTER SEQUENCE fleet_ships_num_seq OWNED BY fleet_ships.num; + + +-- +-- Name: fleets_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- +CREATE SEQUENCE fleets_id_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -ALTER TABLE public.scans OWNER TO ndawn; -- --- Name: planet_scans; Type: VIEW; Schema: public; Owner: ndawn +-- Name: fleets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE VIEW planet_scans AS - SELECT DISTINCT ON (s.planet) s.id, s.planet, s.tick, m.metal, c.crystal, e.eonium, mr.metal_roids, cr.crystal_roids, er.eonium_roids FROM ((((((scans s JOIN (SELECT planet_data.scan AS id, planet_data.amount AS metal_roids FROM planet_data WHERE (planet_data.rid = 1)) mr USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS crystal_roids FROM planet_data WHERE (planet_data.rid = 2)) cr USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS eonium_roids FROM planet_data WHERE (planet_data.rid = 3)) er USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS metal FROM planet_data WHERE (planet_data.rid = 4)) m USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS crystal FROM planet_data WHERE (planet_data.rid = 5)) c USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS eonium FROM planet_data WHERE (planet_data.rid = 6)) e USING (id)) ORDER BY s.planet, s.tick DESC, s.id DESC; - +ALTER SEQUENCE fleets_id_seq OWNED BY fleets.id; -ALTER TABLE public.planet_scans OWNER TO ndawn; -- --- Name: planets_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: forum_boards_fbid_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE SEQUENCE planets_id_seq +CREATE SEQUENCE forum_boards_fbid_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -ALTER TABLE public.planets_id_seq OWNER TO ndawn; - -- --- Name: planets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: forum_boards_fbid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -ALTER SEQUENCE planets_id_seq OWNED BY planets.id; +ALTER SEQUENCE forum_boards_fbid_seq OWNED BY forum_boards.fbid; -- --- Name: raid_access; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_categories_fcid_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE TABLE raid_access ( - raid integer NOT NULL, - gid integer NOT NULL -); - +CREATE SEQUENCE forum_categories_fcid_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -ALTER TABLE public.raid_access OWNER TO ndawn; -- --- Name: raid_claims; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_categories_fcid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE TABLE raid_claims ( - target integer NOT NULL, - uid integer NOT NULL, - wave integer NOT NULL, - joinable boolean DEFAULT false NOT NULL, - launched boolean DEFAULT false NOT NULL, - "timestamp" timestamp with time zone DEFAULT now() NOT NULL -); - +ALTER SEQUENCE forum_categories_fcid_seq OWNED BY forum_categories.fcid; -ALTER TABLE public.raid_claims OWNER TO ndawn; -- --- Name: raid_targets; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_posts_fpid_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE TABLE raid_targets ( - id integer NOT NULL, - raid integer NOT NULL, - planet integer NOT NULL, - "comment" text, - modified timestamp with time zone DEFAULT now() NOT NULL -); +CREATE SEQUENCE forum_posts_fpid_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + +-- +-- Name: forum_posts_fpid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE forum_posts_fpid_seq OWNED BY forum_posts.fpid; -ALTER TABLE public.raid_targets OWNER TO ndawn; -- --- Name: raid_targets_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: forum_threads_ftid_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE SEQUENCE raid_targets_id_seq +CREATE SEQUENCE forum_threads_ftid_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -ALTER TABLE public.raid_targets_id_seq OWNER TO ndawn; - -- --- Name: raid_targets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: forum_threads_ftid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -ALTER SEQUENCE raid_targets_id_seq OWNED BY raid_targets.id; +ALTER SEQUENCE forum_threads_ftid_seq OWNED BY forum_threads.ftid; -- --- Name: raids; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: groups_gid_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE TABLE raids ( - id integer NOT NULL, - tick integer NOT NULL, - open boolean DEFAULT false NOT NULL, - waves integer DEFAULT 3 NOT NULL, - message text NOT NULL, - removed boolean DEFAULT false NOT NULL, - released_coords boolean DEFAULT false NOT NULL -); +CREATE SEQUENCE groups_gid_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + +-- +-- Name: groups_gid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE groups_gid_seq OWNED BY groups.gid; -ALTER TABLE public.raids OWNER TO ndawn; -- --- Name: raids_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: incomings_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE SEQUENCE raids_id_seq +CREATE SEQUENCE incomings_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -ALTER TABLE public.raids_id_seq OWNER TO ndawn; - -- --- Name: raids_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: incomings_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -ALTER SEQUENCE raids_id_seq OWNED BY raids.id; +ALTER SEQUENCE incomings_id_seq OWNED BY incomings.id; -- --- Name: scans_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: planet_data_types_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE SEQUENCE scans_id_seq +CREATE SEQUENCE planet_data_types_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -ALTER TABLE public.scans_id_seq OWNER TO ndawn; - -- --- Name: scans_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: planet_data_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -ALTER SEQUENCE scans_id_seq OWNED BY scans.id; +ALTER SEQUENCE planet_data_types_id_seq OWNED BY planet_data_types.id; -- --- Name: ship_stats; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: planets_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE TABLE ship_stats ( - name text NOT NULL, - "class" text NOT NULL, - t1 text NOT NULL, - "type" text NOT NULL, - init integer NOT NULL, - armor integer NOT NULL, - damage integer NOT NULL, - metal integer NOT NULL, - crystal integer NOT NULL, - eonium integer NOT NULL, - race text NOT NULL, - guns integer DEFAULT 0 NOT NULL, - eres integer DEFAULT 0 NOT NULL, - t2 text, - t3 text -); - +CREATE SEQUENCE planets_id_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -ALTER TABLE public.ship_stats OWNER TO ndawn; -- --- Name: smslist; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: planets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE TABLE smslist ( - nick text NOT NULL, - sms text NOT NULL, - info text -); - +ALTER SEQUENCE planets_id_seq OWNED BY planets.id; -ALTER TABLE public.smslist OWNER TO ndawn; -- --- Name: structure_scans; Type: VIEW; Schema: public; Owner: ndawn +-- Name: raid_targets_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE VIEW structure_scans AS - SELECT DISTINCT ON (s.planet) s.id, s.planet, s.tick, t.total, d.distorters, sc.seccents FROM (((scans s JOIN (SELECT planet_data.scan AS id, sum(planet_data.amount) AS total FROM planet_data WHERE ((planet_data.rid >= 14) AND (planet_data.rid <= 24)) GROUP BY planet_data.scan) t USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS distorters FROM planet_data WHERE (planet_data.rid = 18)) d USING (id)) JOIN (SELECT planet_data.scan AS id, planet_data.amount AS seccents FROM planet_data WHERE (planet_data.rid = 24)) sc USING (id)) ORDER BY s.planet, s.tick DESC, s.id DESC; +CREATE SEQUENCE raid_targets_id_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + + +-- +-- Name: raid_targets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- +ALTER SEQUENCE raid_targets_id_seq OWNED BY raid_targets.id; -ALTER TABLE public.structure_scans OWNER TO ndawn; -- --- Name: test; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: raids_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE SEQUENCE test +CREATE SEQUENCE raids_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -ALTER TABLE public.test OWNER TO ndawn; - -SET default_with_oids = true; - -- --- Name: ticks; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: raids_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE TABLE ticks ( - tick integer NOT NULL -); +ALTER SEQUENCE raids_id_seq OWNED BY raids.id; -ALTER TABLE public.ticks OWNER TO ndawn; +-- +-- Name: scans_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE scans_id_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -SET default_with_oids = false; -- --- Name: users; Type: TABLE; Schema: public; Owner: ndawn; Tablespace: +-- Name: scans_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE TABLE users ( - uid integer NOT NULL, - username text NOT NULL, - planet integer, - "password" text NOT NULL, - attack_points integer DEFAULT 0 NOT NULL, - defense_points integer DEFAULT 0 NOT NULL, - scan_points integer DEFAULT 0 NOT NULL, - humor_points integer DEFAULT 0 NOT NULL, - hostmask text, - sms text, - rank integer, - laston timestamp with time zone, - ftid integer, - css text, - last_forum_visit timestamp with time zone, - email text, - pnick text, - info text -); - +ALTER SEQUENCE scans_id_seq OWNED BY scans.id; -ALTER TABLE public.users OWNER TO ndawn; -- --- Name: users_uid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- Name: test; Type: SEQUENCE; Schema: public; Owner: - -- -CREATE SEQUENCE users_uid_seq +CREATE SEQUENCE test INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -ALTER TABLE public.users_uid_seq OWNER TO ndawn; - -- --- Name: users_uid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- Name: users_uid_seq; Type: SEQUENCE; Schema: public; Owner: - -- -ALTER SEQUENCE users_uid_seq OWNED BY users.uid; +CREATE SEQUENCE users_uid_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -- --- Name: usersingroup; Type: VIEW; Schema: public; Owner: ndawn +-- Name: users_uid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- -CREATE VIEW usersingroup AS - SELECT groups.gid, groups.groupname, users.uid, users.username FROM ((users NATURAL JOIN groupmembers) NATURAL JOIN groups); - +ALTER SEQUENCE users_uid_seq OWNED BY users.uid; -ALTER TABLE public.usersingroup OWNER TO ndawn; -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE alliances ALTER COLUMN id SET DEFAULT nextval('alliances_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE calls ALTER COLUMN id SET DEFAULT nextval('calls_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE channel_flags ALTER COLUMN id SET DEFAULT nextval('channel_flags_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE channels ALTER COLUMN id SET DEFAULT nextval('channels_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE defense_requests ALTER COLUMN id SET DEFAULT nextval('defense_requests_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: num; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE fleet_ships ALTER COLUMN num SET DEFAULT nextval('fleet_ships_num_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE fleets ALTER COLUMN id SET DEFAULT nextval('fleets_id_seq'::regclass); -- --- Name: fbid; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: fbid; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE forum_boards ALTER COLUMN fbid SET DEFAULT nextval('forum_boards_fbid_seq'::regclass); -- --- Name: fcid; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: fcid; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE forum_categories ALTER COLUMN fcid SET DEFAULT nextval('forum_categories_fcid_seq'::regclass); -- --- Name: fpid; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: fpid; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE forum_posts ALTER COLUMN fpid SET DEFAULT nextval('forum_posts_fpid_seq'::regclass); -- --- Name: ftid; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: ftid; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE forum_threads ALTER COLUMN ftid SET DEFAULT nextval('forum_threads_ftid_seq'::regclass); -- --- Name: gid; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: gid; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE groups ALTER COLUMN gid SET DEFAULT nextval('groups_gid_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE incomings ALTER COLUMN id SET DEFAULT nextval('incomings_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn --- - -ALTER TABLE intel_messages ALTER COLUMN id SET DEFAULT nextval('intel_messages_id_seq'::regclass); - - --- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn --- - -ALTER TABLE log ALTER COLUMN id SET DEFAULT nextval('log_id_seq'::regclass); - - --- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE planet_data_types ALTER COLUMN id SET DEFAULT nextval('planet_data_types_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE planets ALTER COLUMN id SET DEFAULT nextval('planets_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE raid_targets ALTER COLUMN id SET DEFAULT nextval('raid_targets_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE raids ALTER COLUMN id SET DEFAULT nextval('raids_id_seq'::regclass); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE scans ALTER COLUMN id SET DEFAULT nextval('scans_id_seq'::regclass); -- --- Name: uid; Type: DEFAULT; Schema: public; Owner: ndawn +-- Name: uid; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE users ALTER COLUMN uid SET DEFAULT nextval('users_uid_seq'::regclass); -- --- Name: accounts_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: accounts_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY users @@ -1936,7 +1694,7 @@ ALTER TABLE ONLY users -- --- Name: alliance_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: alliance_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY alliance_stats @@ -1944,7 +1702,7 @@ ALTER TABLE ONLY alliance_stats -- --- Name: alliances_name_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: alliances_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY alliances @@ -1952,7 +1710,7 @@ ALTER TABLE ONLY alliances -- --- Name: alliances_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: alliances_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY alliances @@ -1960,7 +1718,7 @@ ALTER TABLE ONLY alliances -- --- Name: calls_member_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: calls_member_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY calls @@ -1968,7 +1726,7 @@ ALTER TABLE ONLY calls -- --- Name: calls_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: calls_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY calls @@ -1976,7 +1734,7 @@ ALTER TABLE ONLY calls -- --- Name: channel_flags_name_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: channel_flags_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY channel_flags @@ -1984,7 +1742,7 @@ ALTER TABLE ONLY channel_flags -- --- Name: channel_flags_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: channel_flags_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY channel_flags @@ -1992,7 +1750,7 @@ ALTER TABLE ONLY channel_flags -- --- Name: channel_group_flags_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: channel_group_flags_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY channel_group_flags @@ -2000,7 +1758,7 @@ ALTER TABLE ONLY channel_group_flags -- --- Name: channels_name_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: channels_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY channels @@ -2008,7 +1766,7 @@ ALTER TABLE ONLY channels -- --- Name: channels_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: channels_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY channels @@ -2016,7 +1774,7 @@ ALTER TABLE ONLY channels -- --- Name: defense_missions_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: defense_missions_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY defense_missions @@ -2024,7 +1782,7 @@ ALTER TABLE ONLY defense_missions -- --- Name: defense_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: defense_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY defense_requests @@ -2032,15 +1790,15 @@ ALTER TABLE ONLY defense_requests -- --- Name: dumps_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: dumps_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY dumps - ADD CONSTRAINT dumps_pkey PRIMARY KEY (tick, "type", modified); + ADD CONSTRAINT dumps_pkey PRIMARY KEY (tick, type, modified); -- --- Name: fleet_scans_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: fleet_scans_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY fleet_scans @@ -2048,7 +1806,15 @@ ALTER TABLE ONLY fleet_scans -- --- Name: fleet_ships_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: fleet_ships_num_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY fleet_ships + ADD CONSTRAINT fleet_ships_num_key UNIQUE (num); + + +-- +-- Name: fleet_ships_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY fleet_ships @@ -2056,7 +1822,7 @@ ALTER TABLE ONLY fleet_ships -- --- Name: fleets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: fleets_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY fleets @@ -2064,7 +1830,7 @@ ALTER TABLE ONLY fleets -- --- Name: forum_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_access_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY forum_access @@ -2072,7 +1838,7 @@ ALTER TABLE ONLY forum_access -- --- Name: forum_boards_fcid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_boards_fcid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY forum_boards @@ -2080,7 +1846,7 @@ ALTER TABLE ONLY forum_boards -- --- Name: forum_boards_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_boards_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY forum_boards @@ -2088,7 +1854,7 @@ ALTER TABLE ONLY forum_boards -- --- Name: forum_categories_category_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_categories_category_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY forum_categories @@ -2096,7 +1862,7 @@ ALTER TABLE ONLY forum_categories -- --- Name: forum_categories_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_categories_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY forum_categories @@ -2104,7 +1870,7 @@ ALTER TABLE ONLY forum_categories -- --- Name: forum_posts_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_posts_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY forum_posts @@ -2112,7 +1878,7 @@ ALTER TABLE ONLY forum_posts -- --- Name: forum_thread_visits_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_thread_visits_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY forum_thread_visits @@ -2120,7 +1886,7 @@ ALTER TABLE ONLY forum_thread_visits -- --- Name: forum_threads_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_threads_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY forum_threads @@ -2128,7 +1894,7 @@ ALTER TABLE ONLY forum_threads -- --- Name: galaxies_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: galaxies_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY galaxies @@ -2136,15 +1902,15 @@ ALTER TABLE ONLY galaxies -- --- Name: graphs_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: graphs_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY graphs - ADD CONSTRAINT graphs_pkey PRIMARY KEY ("type", id); + ADD CONSTRAINT graphs_pkey PRIMARY KEY (type, id); -- --- Name: groupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: groupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY groupmembers @@ -2152,7 +1918,7 @@ ALTER TABLE ONLY groupmembers -- --- Name: groups_groupname_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: groups_groupname_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY groups @@ -2160,7 +1926,7 @@ ALTER TABLE ONLY groups -- --- Name: groups_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: groups_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY groups @@ -2168,7 +1934,7 @@ ALTER TABLE ONLY groups -- --- Name: incomings_call_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: incomings_call_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY incomings @@ -2176,7 +1942,7 @@ ALTER TABLE ONLY incomings -- --- Name: incomings_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: incomings_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY incomings @@ -2184,23 +1950,7 @@ ALTER TABLE ONLY incomings -- --- Name: intel_messages_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: --- - -ALTER TABLE ONLY intel_messages - ADD CONSTRAINT intel_messages_pkey PRIMARY KEY (id); - - --- --- Name: log_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: --- - -ALTER TABLE ONLY log - ADD CONSTRAINT log_pkey PRIMARY KEY (id); - - --- --- Name: misc_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: misc_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY misc @@ -2208,7 +1958,7 @@ ALTER TABLE ONLY misc -- --- Name: planet_data_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_data_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY planet_data @@ -2216,7 +1966,7 @@ ALTER TABLE ONLY planet_data -- --- Name: planet_data_types_category_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_data_types_category_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY planet_data_types @@ -2224,7 +1974,7 @@ ALTER TABLE ONLY planet_data_types -- --- Name: planet_data_types_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_data_types_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY planet_data_types @@ -2232,15 +1982,15 @@ ALTER TABLE ONLY planet_data_types -- --- Name: planet_graphs_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_stats_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- -ALTER TABLE ONLY planet_graphs - ADD CONSTRAINT planet_graphs_pkey PRIMARY KEY (planet, "type"); +ALTER TABLE ONLY planet_stats + ADD CONSTRAINT planet_stats_id_key UNIQUE (id, tick); -- --- Name: planet_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY planet_stats @@ -2248,15 +1998,15 @@ ALTER TABLE ONLY planet_stats -- --- Name: planet_stats_tick_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_stats_x_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY planet_stats - ADD CONSTRAINT planet_stats_tick_key UNIQUE (tick, x, y, z); + ADD CONSTRAINT planet_stats_x_key UNIQUE (x, y, z, tick); -- --- Name: planets_ftid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: planets_ftid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY planets @@ -2264,7 +2014,7 @@ ALTER TABLE ONLY planets -- --- Name: planets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: planets_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY planets @@ -2272,7 +2022,7 @@ ALTER TABLE ONLY planets -- --- Name: planets_ruler_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: planets_ruler_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY planets @@ -2280,7 +2030,7 @@ ALTER TABLE ONLY planets -- --- Name: raid_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: raid_access_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY raid_access @@ -2288,7 +2038,7 @@ ALTER TABLE ONLY raid_access -- --- Name: raid_claims_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: raid_claims_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY raid_claims @@ -2296,7 +2046,7 @@ ALTER TABLE ONLY raid_claims -- --- Name: raid_targets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: raid_targets_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY raid_targets @@ -2304,15 +2054,15 @@ ALTER TABLE ONLY raid_targets -- --- Name: raid_targets_raid_planet_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: raid_targets_raid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY raid_targets - ADD CONSTRAINT raid_targets_raid_planet_key UNIQUE (planet, raid); + ADD CONSTRAINT raid_targets_raid_key UNIQUE (raid, planet); -- --- Name: raids_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: raids_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY raids @@ -2320,7 +2070,7 @@ ALTER TABLE ONLY raids -- --- Name: scans_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: scans_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY scans @@ -2328,7 +2078,7 @@ ALTER TABLE ONLY scans -- --- Name: scans_scan_id_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: scans_scan_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY scans @@ -2336,7 +2086,7 @@ ALTER TABLE ONLY scans -- --- Name: ship_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: ship_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY ship_stats @@ -2344,7 +2094,7 @@ ALTER TABLE ONLY ship_stats -- --- Name: smslist_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: smslist_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY smslist @@ -2352,15 +2102,7 @@ ALTER TABLE ONLY smslist -- --- Name: ticks_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: --- - -ALTER TABLE ONLY ticks - ADD CONSTRAINT ticks_pkey PRIMARY KEY (tick); - - --- --- Name: users_planet_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: users_planet_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY users @@ -2368,7 +2110,7 @@ ALTER TABLE ONLY users -- --- Name: users_tfid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn; Tablespace: +-- Name: users_tfid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- ALTER TABLE ONLY users @@ -2376,175 +2118,189 @@ ALTER TABLE ONLY users -- --- Name: fleets_ingal_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: fleets_ingal_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX fleets_ingal_index ON fleets USING btree (ingal); -- --- Name: fleets_mission_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: fleets_mission_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX fleets_mission_index ON fleets USING btree (mission); -- --- Name: fleets_sender_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: fleets_sender_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX fleets_sender_index ON fleets USING btree (sender); -- --- Name: fleets_target_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: fleets_target_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX fleets_target_index ON fleets USING btree (target); -- --- Name: fleets_tick_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: fleets_tick_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX fleets_tick_index ON fleets USING btree (tick); -- --- Name: forum_access_gid_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_access_gid_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX forum_access_gid_index ON forum_access USING btree (gid); -- --- Name: forum_posts_ftid_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_posts_ftid_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX forum_posts_ftid_index ON forum_posts USING btree (ftid); -- --- Name: forum_posts_time_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_posts_textsearch_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX forum_posts_time_index ON forum_posts USING btree ("time"); +CREATE INDEX forum_posts_textsearch_index ON forum_posts USING gin (textsearch); -- --- Name: forum_thread_visits_time_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: forum_posts_time_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX forum_thread_visits_time_index ON forum_thread_visits USING btree ("time"); +CREATE INDEX forum_posts_time_index ON forum_posts USING btree ("time"); -- --- Name: groupmembers_uid_key; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: groupmembers_uid_key; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX groupmembers_uid_key ON groupmembers USING btree (uid); -- --- Name: planet_data_id_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_data_id_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX planet_data_id_index ON planet_data USING btree (id); -- --- Name: planet_stats_coord_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_stats_score_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX planet_stats_coord_index ON planet_stats USING btree (x, y, z); +CREATE INDEX planet_stats_score_index ON planet_stats USING btree (tick, score); -- --- Name: planet_stats_id_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_stats_scorerank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX planet_stats_id_index ON planet_stats USING btree (id); +CREATE INDEX planet_stats_scorerank_index ON planet_stats USING btree (tick, scorerank); -- --- Name: planet_stats_score_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_stats_size_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX planet_stats_score_index ON planet_stats USING btree (tick, score); +CREATE INDEX planet_stats_size_index ON planet_stats USING btree (tick, size); -- --- Name: planet_stats_size_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_stats_sizerank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX planet_stats_size_index ON planet_stats USING btree (tick, size); +CREATE INDEX planet_stats_sizerank_index ON planet_stats USING btree (tick, sizerank); -- --- Name: planet_stats_tick_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_stats_value_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX planet_stats_tick_index ON planet_stats USING btree (tick); +CREATE INDEX planet_stats_value_index ON planet_stats USING btree (tick, value); -- --- Name: planet_stats_value_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: planet_stats_valuerank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- -CREATE INDEX planet_stats_value_index ON planet_stats USING btree (tick, value); +CREATE INDEX planet_stats_valuerank_index ON planet_stats USING btree (tick, valuerank); + + +-- +-- Name: planet_stats_xprank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX planet_stats_xprank_index ON planet_stats USING btree (tick, xprank); -- --- Name: planets_alliance_id_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: planets_alliance_id_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX planets_alliance_id_index ON planets USING btree (alliance_id); -- --- Name: planets_nick_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: planets_nick_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX planets_nick_index ON planets USING btree (nick); -- --- Name: raid_targets_modified_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: raid_targets_modified_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE INDEX raid_targets_modified_index ON raid_targets USING btree (modified); -- --- Name: smslist_nick_key; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: scans_parsed_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX scans_parsed_index ON scans USING btree (parsed); + + +-- +-- Name: smslist_nick_key; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE UNIQUE INDEX smslist_nick_key ON smslist USING btree (lower(nick)); -- --- Name: users_hostmask_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: users_hostmask_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE UNIQUE INDEX users_hostmask_index ON users USING btree (lower(hostmask)); -- --- Name: users_pnick_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: users_pnick_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE UNIQUE INDEX users_pnick_index ON users USING btree (lower(pnick)); -- --- Name: users_username_index; Type: INDEX; Schema: public; Owner: ndawn; Tablespace: +-- Name: users_username_index; Type: INDEX; Schema: public; Owner: -; Tablespace: -- CREATE UNIQUE INDEX users_username_index ON users USING btree (lower(username)); -- --- Name: add_call; Type: TRIGGER; Schema: public; Owner: ndawn +-- Name: add_call; Type: TRIGGER; Schema: public; Owner: - -- CREATE TRIGGER add_call @@ -2554,7 +2310,7 @@ CREATE TRIGGER add_call -- --- Name: add_remove_member; Type: TRIGGER; Schema: public; Owner: ndawn +-- Name: add_remove_member; Type: TRIGGER; Schema: public; Owner: - -- CREATE TRIGGER add_remove_member @@ -2564,7 +2320,7 @@ CREATE TRIGGER add_remove_member -- --- Name: add_user; Type: TRIGGER; Schema: public; Owner: ndawn +-- Name: add_user; Type: TRIGGER; Schema: public; Owner: - -- CREATE TRIGGER add_user @@ -2574,7 +2330,7 @@ CREATE TRIGGER add_user -- --- Name: unclaim_target; Type: TRIGGER; Schema: public; Owner: ndawn +-- Name: unclaim_target; Type: TRIGGER; Schema: public; Owner: - -- CREATE TRIGGER unclaim_target @@ -2584,7 +2340,17 @@ CREATE TRIGGER unclaim_target -- --- Name: update_planet; Type: TRIGGER; Schema: public; Owner: ndawn +-- Name: update_forum_post; Type: TRIGGER; Schema: public; Owner: - +-- + +CREATE TRIGGER update_forum_post + BEFORE INSERT OR UPDATE ON forum_posts + FOR EACH ROW + EXECUTE PROCEDURE update_forum_post(); + + +-- +-- Name: update_planet; Type: TRIGGER; Schema: public; Owner: - -- CREATE TRIGGER update_planet @@ -2594,7 +2360,7 @@ CREATE TRIGGER update_planet -- --- Name: update_target; Type: TRIGGER; Schema: public; Owner: ndawn +-- Name: update_target; Type: TRIGGER; Schema: public; Owner: - -- CREATE TRIGGER update_target @@ -2604,7 +2370,7 @@ CREATE TRIGGER update_target -- --- Name: alliance_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: alliance_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY alliance_stats @@ -2612,7 +2378,7 @@ ALTER TABLE ONLY alliance_stats -- --- Name: calls_dc_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: calls_dc_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY calls @@ -2620,7 +2386,7 @@ ALTER TABLE ONLY calls -- --- Name: calls_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: calls_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY calls @@ -2628,7 +2394,7 @@ ALTER TABLE ONLY calls -- --- Name: calls_member_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: calls_member_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY calls @@ -2636,7 +2402,7 @@ ALTER TABLE ONLY calls -- --- Name: channel_group_flags_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: channel_group_flags_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY channel_group_flags @@ -2644,7 +2410,7 @@ ALTER TABLE ONLY channel_group_flags -- --- Name: channel_group_flags_flag_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: channel_group_flags_flag_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY channel_group_flags @@ -2652,7 +2418,7 @@ ALTER TABLE ONLY channel_group_flags -- --- Name: channel_group_flags_group_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: channel_group_flags_group_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY channel_group_flags @@ -2660,7 +2426,7 @@ ALTER TABLE ONLY channel_group_flags -- --- Name: defense_missions_call_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: defense_missions_call_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY defense_missions @@ -2668,7 +2434,7 @@ ALTER TABLE ONLY defense_missions -- --- Name: defense_missions_fleet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: defense_missions_fleet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY defense_missions @@ -2676,7 +2442,7 @@ ALTER TABLE ONLY defense_missions -- --- Name: defense_requests_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: defense_requests_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY defense_requests @@ -2684,7 +2450,7 @@ ALTER TABLE ONLY defense_requests -- --- Name: fleet_scans_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: fleet_scans_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY fleet_scans @@ -2692,7 +2458,7 @@ ALTER TABLE ONLY fleet_scans -- --- Name: fleet_scans_scan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: fleet_scans_scan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY fleet_scans @@ -2700,7 +2466,7 @@ ALTER TABLE ONLY fleet_scans -- --- Name: fleet_ships_fleet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: fleet_ships_fleet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY fleet_ships @@ -2708,7 +2474,7 @@ ALTER TABLE ONLY fleet_ships -- --- Name: fleet_ships_ship_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: fleet_ships_ship_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY fleet_ships @@ -2716,7 +2482,7 @@ ALTER TABLE ONLY fleet_ships -- --- Name: fleets_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: fleets_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY fleets @@ -2724,7 +2490,7 @@ ALTER TABLE ONLY fleets -- --- Name: fleets_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: fleets_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY fleets @@ -2732,7 +2498,7 @@ ALTER TABLE ONLY fleets -- --- Name: fleets_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: fleets_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY fleets @@ -2740,7 +2506,7 @@ ALTER TABLE ONLY fleets -- --- Name: forum_access_fbid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: forum_access_fbid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY forum_access @@ -2748,7 +2514,7 @@ ALTER TABLE ONLY forum_access -- --- Name: forum_access_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: forum_access_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY forum_access @@ -2756,7 +2522,7 @@ ALTER TABLE ONLY forum_access -- --- Name: forum_boards_fcid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: forum_boards_fcid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY forum_boards @@ -2764,7 +2530,7 @@ ALTER TABLE ONLY forum_boards -- --- Name: forum_posts_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: forum_posts_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY forum_posts @@ -2772,7 +2538,7 @@ ALTER TABLE ONLY forum_posts -- --- Name: forum_posts_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: forum_posts_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY forum_posts @@ -2780,7 +2546,7 @@ ALTER TABLE ONLY forum_posts -- --- Name: forum_thread_visits_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: forum_thread_visits_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY forum_thread_visits @@ -2788,7 +2554,7 @@ ALTER TABLE ONLY forum_thread_visits -- --- Name: forum_thread_visits_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: forum_thread_visits_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY forum_thread_visits @@ -2796,7 +2562,7 @@ ALTER TABLE ONLY forum_thread_visits -- --- Name: forum_threads_fbid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: forum_threads_fbid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY forum_threads @@ -2804,7 +2570,7 @@ ALTER TABLE ONLY forum_threads -- --- Name: forum_threads_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: forum_threads_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY forum_threads @@ -2812,7 +2578,7 @@ ALTER TABLE ONLY forum_threads -- --- Name: groupmembers_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: groupmembers_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY groupmembers @@ -2820,7 +2586,7 @@ ALTER TABLE ONLY groupmembers -- --- Name: groupmembers_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: groupmembers_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY groupmembers @@ -2828,7 +2594,7 @@ ALTER TABLE ONLY groupmembers -- --- Name: incomings_call_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: incomings_call_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY incomings @@ -2836,7 +2602,7 @@ ALTER TABLE ONLY incomings -- --- Name: incomings_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: incomings_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY incomings @@ -2844,31 +2610,7 @@ ALTER TABLE ONLY incomings -- --- Name: intel_messages_handled_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn --- - -ALTER TABLE ONLY intel_messages - ADD CONSTRAINT intel_messages_handled_by_fkey FOREIGN KEY (handled_by) REFERENCES users(uid) ON UPDATE RESTRICT ON DELETE RESTRICT; - - --- --- Name: intel_messages_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn --- - -ALTER TABLE ONLY intel_messages - ADD CONSTRAINT intel_messages_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE RESTRICT ON DELETE RESTRICT; - - --- --- Name: log_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn --- - -ALTER TABLE ONLY log - ADD CONSTRAINT log_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; - - --- --- Name: planet_data_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: planet_data_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY planet_data @@ -2876,7 +2618,7 @@ ALTER TABLE ONLY planet_data -- --- Name: planet_data_rid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: planet_data_rid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY planet_data @@ -2884,7 +2626,7 @@ ALTER TABLE ONLY planet_data -- --- Name: planet_data_scan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: planet_data_scan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY planet_data @@ -2892,15 +2634,7 @@ ALTER TABLE ONLY planet_data -- --- Name: planet_graphs_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn --- - -ALTER TABLE ONLY planet_graphs - ADD CONSTRAINT planet_graphs_planet_fkey FOREIGN KEY (planet) REFERENCES planets(id); - - --- --- Name: planet_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: planet_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY planet_stats @@ -2908,7 +2642,7 @@ ALTER TABLE ONLY planet_stats -- --- Name: planets_alliance_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: planets_alliance_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY planets @@ -2916,7 +2650,7 @@ ALTER TABLE ONLY planets -- --- Name: planets_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: planets_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY planets @@ -2924,7 +2658,7 @@ ALTER TABLE ONLY planets -- --- Name: raid_access_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: raid_access_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY raid_access @@ -2932,7 +2666,7 @@ ALTER TABLE ONLY raid_access -- --- Name: raid_access_raid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: raid_access_raid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY raid_access @@ -2940,7 +2674,7 @@ ALTER TABLE ONLY raid_access -- --- Name: raid_claims_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: raid_claims_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY raid_claims @@ -2948,7 +2682,7 @@ ALTER TABLE ONLY raid_claims -- --- Name: raid_claims_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: raid_claims_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY raid_claims @@ -2956,7 +2690,7 @@ ALTER TABLE ONLY raid_claims -- --- Name: raid_targets_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: raid_targets_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY raid_targets @@ -2964,7 +2698,7 @@ ALTER TABLE ONLY raid_targets -- --- Name: raid_targets_raid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: raid_targets_raid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY raid_targets @@ -2972,7 +2706,7 @@ ALTER TABLE ONLY raid_targets -- --- Name: scans_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: scans_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY scans @@ -2980,7 +2714,7 @@ ALTER TABLE ONLY scans -- --- Name: scans_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: scans_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY scans @@ -2988,7 +2722,7 @@ ALTER TABLE ONLY scans -- --- Name: users_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: users_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY users @@ -2996,7 +2730,7 @@ ALTER TABLE ONLY users -- --- Name: users_tfid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn +-- Name: users_tfid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY users @@ -3004,13 +2738,12 @@ ALTER TABLE ONLY users -- --- Name: public; Type: ACL; Schema: -; Owner: postgres +-- Name: public; Type: ACL; Schema: -; Owner: - -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM postgres; GRANT ALL ON SCHEMA public TO postgres; -GRANT ALL ON SCHEMA public TO ndawn; GRANT ALL ON SCHEMA public TO PUBLIC; diff --git a/database/search.sql b/database/search.sql deleted file mode 100644 index 6801ca0..0000000 --- a/database/search.sql +++ /dev/null @@ -1,40 +0,0 @@ -ALTER TABLE forum_posts ADD textsearch tsvector; - -UPDATE forum_posts fp SET textsearch = setweight(to_tsvector(coalesce(ft.subject,'')), 'A') || setweight(to_tsvector(coalesce(u.username,'')), 'B') || setweight(to_tsvector(coalesce(fp.message,'')), 'D') FROM forum_threads ft, users u WHERE fp.ftid = ft.ftid AND u.uid = fp.uid; - -CREATE INDEX forum_posts_textsearch_index ON forum_posts USING gin(textsearch); - -/*CREATE OR REPLACE FUNCTION update_forum_post() RETURNS "trigger" -AS $_X$ - my $query = spi_prepare(q{UPDATE forum_posts fp - SET textsearch = setweight(to_tsvector(coalesce(ft.subject,'')), 'A') - || setweight(to_tsvector(coalesce(u.username,'')), 'B') - || setweight(to_tsvector(coalesce(fp.message,'')), 'D') - FROM forum_threads ft, users u - WHERE fp.ftid = ft.ftid AND u.uid = fp.uid AND fp.fpid = $1},'int4'); - spi_exec_prepared($query,$_TD->{new}{fpid}); - spi_freeplan($query); -$_X$ - LANGUAGE plperl; -*/ -CREATE OR REPLACE FUNCTION update_forum_post() RETURNS "trigger" -AS $_X$ -DECLARE - rec RECORD; -BEGIN - SELECT setweight(to_tsvector(coalesce(ft.subject,'')), 'A') - || setweight(to_tsvector(coalesce(u.username,'')), 'B') AS ts - INTO STRICT rec - FROM forum_threads ft, users u - WHERE NEW.ftid = ft.ftid AND u.uid = NEW.uid; - NEW.textsearch := rec.ts - || setweight(to_tsvector(coalesce(NEW.message,'')), 'D'); - return NEW; -END; -$_X$ - LANGUAGE plpgsql; - -CREATE TRIGGER update_forum_post - BEFORE INSERT OR UPDATE ON forum_posts - FOR EACH ROW - EXECUTE PROCEDURE update_forum_post();