X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=blobdiff_plain;f=database%2Fndawn.sql;h=177a3f9139ea61b381ef72492e0bfc3082bbcdcb;hp=2c19924f1bbad3560fdb15f2d76b0fcdebe67b2d;hb=5e6cbd6690cb6007e3cadd5b93e93b8b12a09f56;hpb=8cb5da28719b71cf27b13049dc0d0892652b9a1c diff --git a/database/ndawn.sql b/database/ndawn.sql index 2c19924..177a3f9 100644 --- a/database/ndawn.sql +++ b/database/ndawn.sql @@ -2,2760 +2,4403 @@ -- PostgreSQL database dump -- +-- Dumped from database version 9.5.1 +-- Dumped by pg_dump version 9.5.1 + +SET statement_timeout = 0; +SET lock_timeout = 0; SET client_encoding = 'UTF8'; -SET standard_conforming_strings = off; +SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -SET escape_string_warning = off; +SET row_security = off; + +-- +-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: +-- + +CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; + + +-- +-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: +-- + +COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; + + +-- +-- Name: citext; Type: EXTENSION; Schema: -; Owner: +-- + +CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public; + + +-- +-- Name: EXTENSION citext; Type: COMMENT; Schema: -; Owner: +-- + +COMMENT ON EXTENSION citext IS 'data type for case-insensitive character strings'; + + +-- +-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: +-- + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + + +-- +-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: +-- + +COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams'; + + +SET search_path = public, pg_catalog; + +-- +-- Name: ead_status; Type: TYPE; Schema: public; Owner: ndawn +-- + +CREATE TYPE ead_status AS ENUM ( + '', + 'NAP', + 'Friendly', + 'Hostile' +); + + +ALTER TYPE ead_status OWNER TO ndawn; + +-- +-- Name: governments; Type: TYPE; Schema: public; Owner: ndawn +-- + +CREATE TYPE governments AS ENUM ( + '', + 'Feu', + 'Dic', + 'Dem', + 'Uni' +); + + +ALTER TYPE governments OWNER TO ndawn; + +-- +-- Name: race; Type: TYPE; Schema: public; Owner: ndawn +-- + +CREATE TYPE race AS ENUM ( + 'Ter', + 'Cat', + 'Xan', + 'Zik', + 'Etd' +); + + +ALTER TYPE race OWNER TO ndawn; + +-- +-- Name: add_call(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION add_call() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + thread INTEGER; +BEGIN + INSERT INTO forum_threads (fbid,subject,uid) + VALUES(-3,NEW.uid || ': ' || NEW.landing_tick,-3) RETURNING ftid + INTO STRICT thread; + NEW.ftid = thread; + RETURN NEW; +END; +$$; + + +ALTER FUNCTION public.add_call() OWNER TO ndawn; + +-- +-- Name: add_raid(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION add_raid() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + rec RECORD; +BEGIN + INSERT INTO forum_threads (ftid,fbid,subject,uid) VALUES + (DEFAULT,-5,'Raid ' || NEW.id,-3) RETURNING ftid INTO rec; + NEW.ftid := rec.ftid; + return NEW; +END; +$$; + + +ALTER FUNCTION public.add_raid() OWNER TO ndawn; + +-- +-- Name: add_user(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION add_user() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + thread INTEGER; +BEGIN + INSERT INTO forum_threads (fbid,subject,uid) + VALUES(-1,NEW.uid || ': ' || NEW.username,-3) RETURNING ftid + INTO STRICT thread; + NEW.ftid = thread; + RETURN NEW; +END; +$$; + + +ALTER FUNCTION public.add_user() OWNER TO ndawn; + +-- +-- Name: change_member(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION change_member() RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + IF TG_OP = 'INSERT' THEN + IF NEW.gid = 'M' THEN + UPDATE planets SET alliance = 'NewDawn' WHERE + pid = (SELECT pid FROM users WHERE uid = NEW.uid); + END IF; + ELSIF TG_OP = 'DELETE' THEN + IF OLD.gid = 'M' THEN + UPDATE planets SET alliance = NULL WHERE + pid = (SELECT pid FROM users WHERE uid = OLD.uid); + END IF; + END IF; + + return NEW; +END; +$$; + + +ALTER FUNCTION public.change_member() OWNER TO ndawn; + +-- +-- Name: coords(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION coords(x integer, y integer, z integer) RETURNS text + LANGUAGE sql IMMUTABLE + AS $_$ +SELECT $1 || ':' || $2 || ':' || $3 +$_$; + + +ALTER FUNCTION public.coords(x integer, y integer, z integer) OWNER TO ndawn; + +-- +-- Name: covop_alert(integer, integer, integer, integer, governments, integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION covop_alert(secs integer, strucs integer, roids integer, guards integer, gov governments, population integer) RETURNS integer + LANGUAGE sql IMMUTABLE + AS $_$ + SELECT ((50 + COALESCE($4*5.0/($3+1.0),$6)) + * (1.0+2*LEAST(COALESCE($1::float/CASE $2 + WHEN 0 THEN 1 ELSE $2 END,$6),0.30) + + (CASE $5 + WHEN 'Dic' THEN 0.20 + WHEN 'Feu' THEN -0.20 + WHEN 'Uni' THEN -0.10 + ELSE 0 + END) + $6/100.0 + ))::integer; +$_$; + + +ALTER FUNCTION public.covop_alert(secs integer, strucs integer, roids integer, guards integer, gov governments, population integer) OWNER TO ndawn; + +-- +-- Name: endtick(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION endtick() RETURNS integer + LANGUAGE sql STABLE SECURITY DEFINER + AS $$SELECT value::integer FROM misc WHERE id = 'ENDTICK'$$; + + +ALTER FUNCTION public.endtick() OWNER TO ndawn; + +-- +-- Name: find_alliance_id(text); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION find_alliance_id(alli text) RETURNS integer + LANGUAGE plpgsql + AS $_$ +DECLARE + id INTEGER; +BEGIN + SELECT aid FROM INTO id alliances WHERE alliance = alli; + IF NOT FOUND THEN + INSERT INTO alliances(alliance) VALUES($1) + RETURNING aid INTO id; + END IF; + RETURN id; +END; +$_$; + + +ALTER FUNCTION public.find_alliance_id(alli text) OWNER TO ndawn; + +-- +-- Name: find_planet_id(text, text, race); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION find_planet_id(_ruler text, _planet text, _race race) RETURNS integer + LANGUAGE plpgsql + AS $$ +DECLARE + p RECORD; + id INTEGER; + thread INTEGER; +BEGIN + SELECT pid, race INTO p FROM planets WHERE ruler = _ruler AND planet = _planet; + IF FOUND THEN + IF _race <> p.race THEN + UPDATE planets SET race = _race WHERE pid = p.pid; + END IF; + id := p.pid; + ELSE + INSERT INTO forum_threads (fbid,subject,uid) VALUES(-2, _ruler || ' OF ' || _planet, -3) + RETURNING ftid INTO thread; + INSERT INTO planets(ruler,planet,race,ftid) VALUES(_ruler,_planet,_race,thread) + RETURNING pid INTO id; + END IF; + RETURN id; +END; +$$; + + +ALTER FUNCTION public.find_planet_id(_ruler text, _planet text, _race race) OWNER TO ndawn; + +-- +-- Name: groups(integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION groups(uid integer) RETURNS SETOF character + LANGUAGE sql STABLE + AS $_$SELECT gid FROM groupmembers WHERE uid = $1 UNION SELECT ''$_$; + + +ALTER FUNCTION public.groups(uid integer) OWNER TO ndawn; + +-- +-- Name: max_bank_hack(bigint, bigint, bigint, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION max_bank_hack(metal bigint, crystal bigint, eonium bigint, tvalue integer, value integer, agents integer) RETURNS integer + LANGUAGE sql IMMUTABLE + AS $_$ +SELECT LEAST(2000.0*$6*$4/$5, $1*0.10, $6*10000.0)::integer + + LEAST(2000.0*$6*$4/$5, $2*0.10, $6*10000.0)::integer + + LEAST(2000.0*$6*$4/$5, $3*0.10, $6*10000.0)::integer +$_$; + + +ALTER FUNCTION public.max_bank_hack(metal bigint, crystal bigint, eonium bigint, tvalue integer, value integer, agents integer) OWNER TO ndawn; + +-- +-- Name: mmdd(date); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION mmdd(d date) RETURNS text + LANGUAGE sql IMMUTABLE + AS $_$ SELECT to_char($1,'MM-DD') $_$; + + +ALTER FUNCTION public.mmdd(d date) OWNER TO ndawn; + +-- +-- Name: old_claim(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION old_claim("timestamp" timestamp with time zone) RETURNS boolean + LANGUAGE sql IMMUTABLE + AS $_$SELECT NOW() - '10 minutes'::INTERVAL > $1;$_$; + + +ALTER FUNCTION public.old_claim("timestamp" timestamp with time zone) OWNER TO ndawn; + +-- +-- Name: planetcoords(integer, integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) RETURNS record + LANGUAGE sql STABLE + AS $_$SELECT x,y,z FROM planet_stats WHERE pid = $1 AND (tick >= $2 OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$; + + +ALTER FUNCTION public.planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) OWNER TO ndawn; + +-- +-- Name: planetid(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION planetid(x integer, y integer, z integer, tick integer) RETURNS integer + LANGUAGE sql STABLE + AS $_$SELECT pid 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$_$; + + +ALTER FUNCTION public.planetid(x integer, y integer, z integer, tick integer) OWNER TO ndawn; + +-- +-- Name: tick(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION tick() RETURNS integer + LANGUAGE sql STABLE SECURITY DEFINER + AS $$SELECT value::integer FROM misc WHERE id = 'TICK'$$; + + +ALTER FUNCTION public.tick() OWNER TO ndawn; + +-- +-- Name: unread_posts(integer); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION unread_posts(uid integer, OUT unread integer, OUT new integer) RETURNS record + LANGUAGE sql STABLE + AS $_$ +SELECT count(*)::int AS unread + ,count(NULLIF(fp.time > (SELECT max(time) FROM forum_thread_visits WHERE uid = $1),FALSE))::int AS new +FROM( + SELECT ftid, ftv.time + FROM forum_threads ft + LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) + ftv USING (ftid) + WHERE COALESCE(ft.mtime > ftv.time,TRUE) + AND ((fbid > 0 AND + fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1))) + ) OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1) + ) + ) ft + JOIN forum_posts fp USING (ftid) +WHERE COALESCE(fp.time > ft.time, TRUE) +$_$; + + +ALTER FUNCTION public.unread_posts(uid integer, OUT unread integer, OUT new integer) OWNER TO ndawn; + +-- +-- Name: update_forum_post(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION update_forum_post() RETURNS trigger + LANGUAGE plpgsql + 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; +$$; + + +ALTER FUNCTION public.update_forum_post() OWNER TO ndawn; + +-- +-- Name: update_forum_thread_posts(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION update_forum_thread_posts() RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + + IF TG_OP = 'INSERT' THEN + UPDATE forum_threads SET posts = posts + 1, mtime = GREATEST(NEW.time,mtime) + WHERE ftid = NEW.ftid; + ELSIF TG_OP = 'DELETE' THEN + UPDATE forum_threads SET posts = posts - 1 WHERE ftid = OLD.ftid; + ELSIF TG_OP = 'UPDATE' AND NEW.ftid <> OLD.ftid THEN + UPDATE forum_threads SET posts = posts - 1 WHERE ftid = OLD.ftid; + UPDATE forum_threads SET posts = posts + 1, mtime = GREATEST(NEW.time,mtime) + WHERE ftid = NEW.ftid; + END IF; + + return NEW; +END; +$$; + + +ALTER FUNCTION public.update_forum_thread_posts() OWNER TO ndawn; + +-- +-- Name: update_user_planet(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION update_user_planet() RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + IF COALESCE(NEW.pid <> OLD.pid,TRUE) OR NEW.username <> OLD.username THEN + UPDATE planets SET nick = NULL WHERE pid = OLD.pid; + UPDATE planets SET nick = NEW.username WHERE pid = NEW.pid; + END IF; + + IF COALESCE(NEW.pid <> OLD.pid,TRUE) + AND (SELECT TRUE FROM groupmembers WHERE gid = 'M' AND uid = NEW.uid) THEN + UPDATE planets SET alliance = NULL WHERE pid = OLD.pid; + UPDATE planets SET alliance = 'NewDawn' WHERE pid = NEW.pid; + END IF; + RETURN NEW; +END; +$$; + + +ALTER FUNCTION public.update_user_planet() OWNER TO ndawn; + +-- +-- Name: update_wiki_page(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION update_wiki_page() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + rec RECORD; +BEGIN + SELECT setweight(to_tsvector(wpr.text), 'D') AS ts + INTO STRICT rec + FROM wiki_page_revisions wpr + WHERE NEW.wprev = wpr.wprev; + NEW.textsearch := rec.ts + || setweight(to_tsvector(NEW.namespace || ':' || NEW.name), 'A'); + NEW.time = NOW(); + return NEW; +END; +$$; + + +ALTER FUNCTION public.update_wiki_page() OWNER TO ndawn; + +-- +-- Name: updated_claim(); Type: FUNCTION; Schema: public; Owner: ndawn +-- + +CREATE FUNCTION updated_claim() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + target INTEGER; +BEGIN + CASE TG_OP + WHEN 'INSERT' THEN + target := NEW.target; + WHEN 'UPDATE' THEN + target := NEW.target; + IF NEW.launched AND NOT OLD.launched THEN + UPDATE users + SET attack_points = attack_points + 1 + WHERE uid = OLD.uid; + + INSERT INTO forum_posts (ftid,uid,message) + VALUES((SELECT ftid FROM users WHERE uid = NEW.uid),NEW.uid + ,'Gave attack point for confirmation of attack on target ' + || NEW.target || ', wave ' || NEW.wave + ); + END IF; + WHEN 'DELETE' THEN + target := OLD.target; + + IF OLD.launched THEN + UPDATE users + SET attack_points = attack_points - 1 + WHERE uid = OLD.uid; + END IF; + END CASE; + UPDATE raid_targets SET modified = NOW() WHERE id = target; + RETURN NEW; +END; +$$; + + +ALTER FUNCTION public.updated_claim() OWNER TO ndawn; + +-- +-- Name: concat(text); Type: AGGREGATE; Schema: public; Owner: ndawn +-- + +CREATE AGGREGATE concat(text) ( + SFUNC = textcat, + STYPE = text, + INITCOND = '' +); + + +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 +-- + +CREATE TABLE alliance_stats ( + aid 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 TABLE alliance_stats OWNER TO ndawn; + +-- +-- Name: alliances; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE alliances ( + aid integer NOT NULL, + alliance text NOT NULL, + relationship ead_status DEFAULT ''::ead_status NOT NULL +); + + +ALTER TABLE alliances OWNER TO ndawn; + +-- +-- Name: development_scans; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE development_scans ( + id integer NOT NULL, + pid integer NOT NULL, + tick integer NOT NULL, + light_fac integer NOT NULL, + medium_fac integer NOT NULL, + heavy_fac integer NOT NULL, + amps integer NOT NULL, + distorters integer NOT NULL, + metal_ref integer NOT NULL, + crystal_ref integer NOT NULL, + eonium_ref integer NOT NULL, + reslabs integer NOT NULL, + fincents integer NOT NULL, + seccents integer NOT NULL, + total integer NOT NULL, + travel integer NOT NULL, + infra integer NOT NULL, + hulls integer NOT NULL, + waves integer NOT NULL, + extraction integer NOT NULL, + covert integer NOT NULL, + mining integer NOT NULL, + milcents integer NOT NULL, + structdefs integer NOT NULL +); + + +ALTER TABLE development_scans OWNER TO ndawn; + +-- +-- Name: current_development_scans; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW current_development_scans AS + SELECT DISTINCT ON (ds.pid) ds.id, + ds.pid, + ds.tick, + ds.light_fac, + ds.medium_fac, + ds.heavy_fac, + ds.amps, + ds.distorters, + ds.metal_ref, + ds.crystal_ref, + ds.eonium_ref, + ds.reslabs, + ds.fincents, + ds.seccents, + ds.total, + ds.travel, + ds.infra, + ds.hulls, + ds.waves, + ds.extraction, + ds.covert, + ds.mining + FROM development_scans ds + ORDER BY ds.pid, ds.tick DESC, ds.id DESC; + + +ALTER TABLE current_development_scans OWNER TO ndawn; + +-- +-- Name: planet_scans; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE planet_scans ( + id integer NOT NULL, + pid integer NOT NULL, + tick integer NOT NULL, + metal bigint NOT NULL, + crystal bigint NOT NULL, + eonium bigint NOT NULL, + hidden bigint NOT NULL, + metal_roids integer NOT NULL, + crystal_roids integer NOT NULL, + eonium_roids integer NOT NULL, + agents integer NOT NULL, + guards integer NOT NULL, + light text NOT NULL, + medium text NOT NULL, + heavy text NOT NULL +); + + +ALTER TABLE planet_scans OWNER TO ndawn; + +-- +-- Name: current_planet_scans; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW current_planet_scans AS + SELECT DISTINCT ON (ps.pid) ps.id, + ps.pid, + ps.tick, + ps.metal, + ps.crystal, + ps.eonium, + ps.hidden, + ps.metal_roids, + ps.crystal_roids, + ps.eonium_roids, + ps.agents, + ps.guards, + ps.light, + ps.medium, + ps.heavy + FROM planet_scans ps + ORDER BY ps.pid, ps.tick DESC, ps.id DESC; + + +ALTER TABLE current_planet_scans OWNER TO ndawn; + +-- +-- Name: planet_stats; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE planet_stats ( + pid 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 TABLE planet_stats OWNER TO ndawn; + +-- +-- Name: planets; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE planets ( + pid integer NOT NULL, + ruler text NOT NULL, + planet text NOT NULL, + race race NOT NULL, + nick citext, + planet_status ead_status DEFAULT ''::ead_status NOT NULL, + hit_us integer DEFAULT 0 NOT NULL, + alliance text, + channel citext, + ftid integer NOT NULL, + gov governments DEFAULT ''::governments NOT NULL +) +WITH (fillfactor='50'); + +ALTER TABLE planets ADD COLUMN id text UNIQUE NOT NULL; + + +ALTER TABLE planets OWNER TO ndawn; + +-- +-- Name: current_planet_stats; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW current_planet_stats AS + SELECT p.pid, + p.nick, + p.planet_status, + p.hit_us, + ps.x, + ps.y, + ps.z, + p.ruler, + p.planet, + p.race, + p.alliance, + a.relationship, + a.aid, + p.channel, + p.ftid, + p.gov, + ps.size, + ps.score, + ps.value, + ps.xp, + ps.sizerank, + ps.scorerank, + ps.valuerank, + ps.xprank + FROM ((( SELECT planet_stats.pid, + 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_1.tick) AS max + FROM planet_stats planet_stats_1))) ps + JOIN planets p USING (pid)) + LEFT JOIN alliances a USING (alliance)); + + +ALTER TABLE current_planet_stats OWNER TO ndawn; + +-- +-- Name: alliance_resources; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW alliance_resources AS + WITH planet_estimates AS ( + SELECT ps.tick, + p.alliance, + ps.hidden, + p.size, + p.score, + ((ps.metal + ps.crystal) + ps.eonium) AS resources, + ((p.score + (((ps.metal + ps.crystal) + ps.eonium) / 300)) + (ps.hidden / 100)) AS nscore2, + ((((p.score + (((ps.metal + ps.crystal) + ps.eonium) / 300)) + (ps.hidden / 100)))::numeric + (((((endtick() - tick()) * (((250 * p.size) + (COALESCE(((ds.metal_ref + ds.crystal_ref) + ds.eonium_ref), 7) * 1000)) + + CASE ds.extraction + WHEN 0 THEN 3000 + WHEN 1 THEN 11500 + ELSE ((COALESCE(ds.extraction, 3) * 3000) * 3) + END)))::numeric * (1.35 + (0.005 * (COALESCE(ds.fincents, 20))::numeric))) / (100)::numeric)) AS nscore3 + FROM ((current_planet_stats p + JOIN current_planet_scans ps USING (pid)) + LEFT JOIN current_development_scans ds USING (pid)) + ), planet_ranks AS ( + SELECT planet_estimates.tick, + planet_estimates.alliance, + planet_estimates.hidden, + planet_estimates.size, + planet_estimates.score, + planet_estimates.resources, + planet_estimates.nscore2, + planet_estimates.nscore3, + rank() OVER (PARTITION BY planet_estimates.alliance ORDER BY planet_estimates.score DESC) AS rank + FROM planet_estimates + ), top_planets AS ( + SELECT planet_ranks.alliance, + sum(planet_ranks.resources) AS resources, + sum(planet_ranks.hidden) AS hidden, + (sum(planet_ranks.nscore2))::bigint AS nscore2, + (sum(planet_ranks.nscore3))::bigint AS nscore3, + count(*) AS planets, + sum(planet_ranks.score) AS score, + sum(planet_ranks.size) AS size, + (avg(planet_ranks.tick))::integer AS avgtick + FROM planet_ranks + WHERE (planet_ranks.rank <= 60) + GROUP BY planet_ranks.alliance + ) + SELECT a.aid, + a.alliance, + a.relationship, + s.members, + r.planets, + s.score, + r.score AS topscore, + s.size, + r.size AS topsize, + r.resources, + r.hidden, + ((((s.score)::numeric + (r.resources / (300)::numeric)) + (r.hidden / (100)::numeric)))::bigint AS nscore, + r.nscore2, + r.nscore3, + r.avgtick + FROM ((alliances a + JOIN top_planets r USING (alliance)) + LEFT JOIN ( SELECT alliance_stats.aid, + alliance_stats.score, + alliance_stats.size, + alliance_stats.members + FROM alliance_stats + WHERE (alliance_stats.tick = ( SELECT max(alliance_stats_1.tick) AS max + FROM alliance_stats alliance_stats_1))) s USING (aid)); + + +ALTER TABLE alliance_resources OWNER TO ndawn; + +-- +-- Name: alliances_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE alliances_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE alliances_id_seq OWNER TO ndawn; + +-- +-- Name: alliances_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE alliances_id_seq OWNED BY alliances.aid; + + +-- +-- Name: available_planet_tags; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE available_planet_tags ( + tag citext NOT NULL +); + + +ALTER TABLE available_planet_tags OWNER TO ndawn; + +-- +-- Name: fleet_ships; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE fleet_ships ( + fid integer NOT NULL, + ship text NOT NULL, + amount integer NOT NULL, + num integer NOT NULL +); + + +ALTER TABLE fleet_ships OWNER TO ndawn; + +-- +-- Name: fleets; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE fleets ( + mission text NOT NULL, + tick integer NOT NULL, + fid integer NOT NULL, + pid integer NOT NULL, + amount integer NOT NULL, + name text NOT NULL +); + + +ALTER TABLE fleets OWNER TO ndawn; + +-- +-- Name: launch_confirmations; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE launch_confirmations ( + fid integer NOT NULL, + uid integer NOT NULL, + pid integer NOT NULL, + landing_tick integer NOT NULL, + eta integer NOT NULL, + back integer NOT NULL, + num integer NOT NULL +) +WITH (fillfactor='75'); + + +ALTER TABLE launch_confirmations OWNER TO ndawn; + +-- +-- Name: ticks; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE ticks ( + t integer NOT NULL +); + + +ALTER TABLE ticks OWNER TO ndawn; + +-- +-- Name: users; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE users ( + uid integer NOT NULL, + username citext NOT NULL, + pid integer, + password text, + attack_points numeric(3,0) DEFAULT 0 NOT NULL, + defense_points numeric(4,1) DEFAULT 0 NOT NULL, + scan_points numeric(5,0) DEFAULT 0 NOT NULL, + humor_points numeric(3,0) DEFAULT 0 NOT NULL, + hostmask citext NOT NULL, + sms text, + rank integer, + laston timestamp with time zone, + ftid integer NOT NULL, + css text, + email text, + pnick citext NOT NULL, + info text, + birthday date, + timezone text DEFAULT 'GMT'::text NOT NULL, + call_if_needed boolean DEFAULT false NOT NULL, + sms_note text DEFAULT ''::text NOT NULL +) +WITH (fillfactor='50'); + + +ALTER TABLE users OWNER TO ndawn; + +-- +-- Name: ships_home; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW ships_home AS + SELECT f.tick, + u.uid, + u.username, + u.pid, + f.ship, + COALESCE((f.amount - o.amount), (f.amount)::bigint) AS amount, + COALESCE(o.fleets, (3)::bigint) AS fleets + FROM ((users u + JOIN ( SELECT f_1.t AS tick, + f_1.pid, + fs.ship, + fs.amount + FROM (( SELECT DISTINCT ON (ticks.t, f_2.pid, f_2.mission) ticks.t, + f_2.pid, + f_2.mission, + f_2.fid + FROM (ticks + CROSS JOIN fleets f_2) + WHERE ((f_2.tick <= ticks.t) AND (f_2.name = ANY (ARRAY['Main'::text, 'Advanced Unit'::text])) AND (f_2.mission = 'Full fleet'::text)) + ORDER BY ticks.t, f_2.pid, f_2.mission, f_2.tick DESC, f_2.fid DESC) f_1 + JOIN fleet_ships fs USING (fid))) f USING (pid)) + LEFT JOIN ( SELECT ticks.t AS tick, + f_1.pid, + fs.ship, + sum(fs.amount) AS amount, + (3 - count(DISTINCT f_1.fid)) AS fleets + FROM (((ticks + CROSS JOIN fleets f_1) + JOIN ( SELECT launch_confirmations.landing_tick, + launch_confirmations.fid, + launch_confirmations.back, + launch_confirmations.eta + FROM launch_confirmations) lc USING (fid)) + JOIN fleet_ships fs USING (fid)) + WHERE ((lc.back > ticks.t) AND (((lc.landing_tick - lc.eta) - 12) < ticks.t)) + GROUP BY ticks.t, f_1.pid, fs.ship) o USING (tick, pid, ship)) + WHERE (COALESCE((f.amount - o.amount), (f.amount)::bigint) > 0); + + +ALTER TABLE ships_home OWNER TO ndawn; + +-- +-- Name: available_ships; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW available_ships AS + SELECT ships_home.uid, + ships_home.username, + ships_home.pid, + ships_home.ship, + ships_home.amount, + ships_home.fleets + FROM ships_home + WHERE (ships_home.tick = tick()); + + +ALTER TABLE available_ships OWNER TO ndawn; + +-- +-- Name: call_statuses; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE call_statuses ( + status text NOT NULL +); + + +ALTER TABLE call_statuses OWNER TO ndawn; + +-- +-- Name: calls; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE calls ( + call integer NOT NULL, + uid integer NOT NULL, + dc integer, + landing_tick integer NOT NULL, + info text NOT NULL, + ftid integer NOT NULL, + calc text DEFAULT ''::text NOT NULL, + status text DEFAULT 'Open'::text NOT NULL +); + + +ALTER TABLE calls OWNER TO ndawn; + +-- +-- Name: calls_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE calls_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE calls_id_seq OWNER TO ndawn; + +-- +-- Name: calls_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE calls_id_seq OWNED BY calls.call; + + +-- +-- Name: channel_flags; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE channel_flags ( + name text NOT NULL, + flag character(1) NOT NULL +); + + +ALTER TABLE channel_flags OWNER TO ndawn; + +-- +-- Name: channel_group_flags; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE channel_group_flags ( + channel citext NOT NULL, + gid character(1) NOT NULL, + flag character(1) NOT NULL +); + + +ALTER TABLE channel_group_flags OWNER TO ndawn; + +-- +-- Name: channels; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE channels ( + channel citext NOT NULL, + description text NOT NULL +); + + +ALTER TABLE channels OWNER TO ndawn; + +-- +-- Name: clickatell; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE clickatell ( + api_id text NOT NULL, + username text NOT NULL, + password text NOT NULL +); + + +ALTER TABLE clickatell OWNER TO ndawn; + +-- +-- Name: covop_attacks; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE covop_attacks ( + uid integer NOT NULL, + tick integer NOT NULL, + pid integer NOT NULL +); + + +ALTER TABLE covop_attacks OWNER TO ndawn; + +-- +-- Name: current_planet_stats_full; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW current_planet_stats_full AS + SELECT p.alliance, + p.pid, + p.ruler, + p.planet, + p.race, + p.nick, + p.planet_status, + p.hit_us, + p.channel, + p.ftid, + p.gov, + ps.tick, + ps.x, + ps.y, + ps.z, + ps.size, + ps.score, + ps.value, + ps.xp, + ps.sizerank, + ps.scorerank, + ps.valuerank, + ps.xprank, + 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, + alliances.aid, + alliances.relationship + FROM ((planets p + JOIN ( SELECT planet_stats.pid, + 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_1.tick) AS max + FROM planet_stats planet_stats_1))) ps USING (pid)) + LEFT JOIN alliances USING (alliance)); + + +ALTER TABLE current_planet_stats_full OWNER TO ndawn; + +-- +-- Name: ship_stats; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE ship_stats ( + ship 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, + id integer NOT NULL +); + + +ALTER TABLE ship_stats OWNER TO ndawn; + +-- +-- Name: def_leeches; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW def_leeches AS + WITH f AS ( + SELECT lc.uid, + lc.fid, + lc.pid, + f.pid AS fpid, + lc.landing_tick, + lc.eta, + lc.back, + sum((((fs.amount * ((s.metal + s.crystal) + s.eonium)))::numeric / 100.0)) AS value + FROM (((launch_confirmations lc + JOIN fleets f USING (fid)) + JOIN fleet_ships fs USING (fid)) + JOIN ship_stats s ON ((fs.ship = s.ship))) + WHERE (f.mission = 'Defend'::text) + GROUP BY lc.uid, lc.fid, lc.pid, f.pid, lc.landing_tick, lc.eta, lc.back + ), f2 AS ( + SELECT f.uid, + sum((f.value / (COALESCE(p.value, ( SELECT planet_stats.value + FROM planet_stats + WHERE ((planet_stats.pid = f.fpid) AND (planet_stats.tick = (c.landing_tick - f.eta))) + ORDER BY planet_stats.tick DESC + LIMIT 1)))::numeric)) AS sent_value + FROM (((calls c + JOIN users u USING (uid)) + JOIN f USING (pid, landing_tick)) + LEFT JOIN ( SELECT planet_stats.pid AS fpid, + planet_stats.value, + planet_stats.tick AS landing_tick + FROM planet_stats) p USING (fpid, landing_tick)) + GROUP BY f.uid + ) + SELECT d.uid, + d.username, + d.defense_points, + count(d.call) AS calls, + sum(d.fleets) AS fleets, + sum(d.recalled) AS recalled, + count(NULLIF(d.fleets, 0)) AS defended_calls, + (sum(d.value))::numeric(4,2) AS value, + (f2.sent_value)::numeric(4,2) AS sent_value + FROM (( SELECT u.uid, + u.username, + u.defense_points, + c.call, + count(f.back) AS fleets, + count(NULLIF((((f.landing_tick + f.eta) - 1) = f.back), true)) AS recalled, + sum((f.value / (COALESCE(p.value, ( SELECT planet_stats.value + FROM planet_stats + WHERE ((planet_stats.pid = f.pid) AND (planet_stats.tick = (c.landing_tick - f.eta))) + ORDER BY planet_stats.tick DESC + LIMIT 1)))::numeric)) AS value + FROM (((users u + JOIN calls c USING (uid)) + LEFT JOIN f USING (pid, landing_tick)) + LEFT JOIN ( SELECT planet_stats.pid, + planet_stats.value, + planet_stats.tick AS landing_tick + FROM planet_stats) p USING (pid, landing_tick)) + GROUP BY u.uid, u.username, u.defense_points, c.call) d + LEFT JOIN f2 USING (uid)) + GROUP BY d.uid, d.username, d.defense_points, f2.sent_value; + + +ALTER TABLE def_leeches OWNER TO ndawn; + +-- +-- Name: incomings; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE incomings ( + call integer NOT NULL, + pid integer NOT NULL, + eta integer NOT NULL, + amount integer NOT NULL, + fleet text NOT NULL, + shiptype text DEFAULT '?'::text NOT NULL, + inc integer NOT NULL +); + + +ALTER TABLE incomings OWNER TO ndawn; + +-- +-- Name: defcalls; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW defcalls AS + SELECT c.call, + c.status, + c.uid, + c.landing_tick, + dc.username AS dc, + (c.landing_tick - tick()) AS curreta, + array_agg(COALESCE((p2.race)::text, ''::text)) AS race, + array_agg(COALESCE(i.amount, 0)) AS amount, + array_agg(COALESCE(i.eta, 0)) AS eta, + array_agg(COALESCE(i.shiptype, ''::text)) AS shiptype, + array_agg(COALESCE(p2.alliance, '?'::text)) AS alliance, + array_agg(coords(p2.x, p2.y, p2.z)) AS attackers + FROM (((calls c + LEFT JOIN incomings i USING (call)) + LEFT JOIN current_planet_stats p2 USING (pid)) + LEFT JOIN users dc ON ((c.dc = dc.uid))) + GROUP BY c.call, c.uid, dc.username, c.landing_tick, c.status; + + +ALTER TABLE defcalls OWNER TO ndawn; + +-- +-- Name: defense_missions; Type: TABLE; Schema: public; Owner: ndawn +-- + +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 TABLE defense_missions OWNER TO ndawn; + +-- +-- Name: dumps; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE dumps ( + tick integer NOT NULL, + type text NOT NULL, + dump text NOT NULL, + modified integer DEFAULT 0 NOT NULL +); + + +ALTER TABLE dumps OWNER TO ndawn; + +-- +-- Name: email_change; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE email_change ( + id text DEFAULT md5(((now() + (random() * '100 years'::interval)))::text) NOT NULL, + uid integer NOT NULL, + email text NOT NULL, + confirmed boolean DEFAULT false NOT NULL +); + + +ALTER TABLE email_change OWNER TO ndawn; + +-- +-- Name: fleet_scans; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE fleet_scans ( + fid integer NOT NULL, + id integer NOT NULL +); + + +ALTER TABLE fleet_scans OWNER TO ndawn; + +-- +-- Name: fleet_ships_num_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE fleet_ships_num_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE fleet_ships_num_seq OWNER TO ndawn; + +-- +-- Name: fleet_ships_num_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE fleet_ships_num_seq OWNED BY fleet_ships.num; + + +-- +-- Name: fleets_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE fleets_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE fleets_id_seq OWNER TO ndawn; + +-- +-- Name: fleets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE fleets_id_seq OWNED BY fleets.fid; + + +-- +-- Name: forum_access; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE forum_access ( + fbid integer NOT NULL, + gid character(1) NOT NULL, + post boolean DEFAULT false NOT NULL, + moderate boolean DEFAULT false NOT NULL +); + + +ALTER TABLE forum_access OWNER TO ndawn; + +-- +-- Name: forum_boards; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE forum_boards ( + fbid integer NOT NULL, + fcid integer NOT NULL, + board text NOT NULL +); + + +ALTER TABLE forum_boards OWNER TO ndawn; + +-- +-- Name: forum_boards_fbid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE forum_boards_fbid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE forum_boards_fbid_seq OWNER TO ndawn; + +-- +-- Name: forum_boards_fbid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE forum_boards_fbid_seq OWNED BY forum_boards.fbid; + + +-- +-- Name: forum_categories; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE forum_categories ( + fcid integer NOT NULL, + category text NOT NULL +); + + +ALTER TABLE forum_categories OWNER TO ndawn; + +-- +-- Name: forum_categories_fcid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE forum_categories_fcid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE forum_categories_fcid_seq OWNER TO ndawn; + +-- +-- Name: forum_categories_fcid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE forum_categories_fcid_seq OWNED BY forum_categories.fcid; + + +-- +-- Name: forum_posts; Type: TABLE; Schema: public; Owner: ndawn +-- + +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 NOT NULL +); + + +ALTER TABLE forum_posts OWNER TO ndawn; + +-- +-- Name: forum_posts_fpid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE forum_posts_fpid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE forum_posts_fpid_seq OWNER TO ndawn; + +-- +-- Name: forum_posts_fpid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE forum_posts_fpid_seq OWNED BY forum_posts.fpid; + + +-- +-- Name: forum_priv_access; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE forum_priv_access ( + uid integer NOT NULL, + ftid integer NOT NULL +); + + +ALTER TABLE forum_priv_access OWNER TO ndawn; + +-- +-- Name: forum_thread_visits; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE forum_thread_visits ( + uid integer NOT NULL, + ftid integer NOT NULL, + "time" timestamp with time zone DEFAULT now() NOT NULL +) +WITH (fillfactor='50'); + + +ALTER TABLE forum_thread_visits OWNER TO ndawn; + +-- +-- Name: forum_threads; Type: TABLE; Schema: public; Owner: ndawn +-- + +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, + posts integer DEFAULT 0 NOT NULL, + mtime timestamp with time zone DEFAULT now() NOT NULL, + ctime timestamp with time zone DEFAULT now() NOT NULL +) +WITH (fillfactor='50'); + + +ALTER TABLE forum_threads OWNER TO ndawn; + +-- +-- Name: forum_threads_ftid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE forum_threads_ftid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE forum_threads_ftid_seq OWNER TO ndawn; + +-- +-- Name: forum_threads_ftid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE forum_threads_ftid_seq OWNED BY forum_threads.ftid; + + +-- +-- Name: groupmembers; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE groupmembers ( + gid character(1) NOT NULL, + uid integer NOT NULL +); + + +ALTER TABLE groupmembers OWNER TO ndawn; + +-- +-- Name: users_defprio; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW users_defprio AS + SELECT u.uid, + u.username, + u.pid, + u.password, + u.attack_points, + u.defense_points, + u.scan_points, + u.humor_points, + u.hostmask, + u.sms, + u.rank, + u.laston, + u.ftid, + u.css, + u.email, + u.pnick, + u.info, + u.birthday, + u.timezone, + u.call_if_needed, + u.sms_note, + ((((((0.2 * (u.attack_points / GREATEST(a.attack, (1)::numeric))) + (0.4 * (u.defense_points / GREATEST(a.defense, (1)::numeric)))) + (0.2 * ((p.size)::numeric / a.size))) + (0.05 * ((p.score)::numeric / a.score))) + (0.15 * ((p.value)::numeric / a.value))))::numeric(3,2) AS defprio + FROM (users u + LEFT JOIN current_planet_stats p USING (pid)), + ( SELECT avg(u_1.attack_points) AS attack, + avg(u_1.defense_points) AS defense, + avg(p_1.size) AS size, + avg(p_1.score) AS score, + avg(p_1.value) AS value + FROM (users u_1 + JOIN current_planet_stats p_1 USING (pid)) + WHERE (u_1.uid IN ( SELECT groupmembers.uid + FROM groupmembers + WHERE (groupmembers.gid = 'M'::bpchar)))) a; + + +ALTER TABLE users_defprio OWNER TO ndawn; + +-- +-- Name: full_defcalls; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW full_defcalls AS + SELECT c.call, + c.status, + p.x, + p.y, + p.z, + u.pid, + c.landing_tick, + c.dc, + c.curreta, + u.defprio, + c.race, + c.amount, + c.eta, + c.shiptype, + c.alliance, + c.attackers, + count(NULLIF((f.back = ((f.landing_tick + f.eta) - 1)), false)) AS fleets + FROM (((users_defprio u + JOIN current_planet_stats p USING (pid)) + JOIN defcalls c USING (uid)) + LEFT JOIN launch_confirmations f USING (pid, landing_tick)) + GROUP BY c.call, p.x, p.y, p.z, u.pid, c.landing_tick, c.dc, c.curreta, u.defprio, c.race, c.amount, c.eta, c.shiptype, c.alliance, c.attackers, c.status; + + +ALTER TABLE full_defcalls OWNER TO ndawn; + +-- +-- Name: full_fleets; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE full_fleets ( + fid integer NOT NULL, + uid integer NOT NULL +); + + +ALTER TABLE full_fleets OWNER TO ndawn; + +-- +-- Name: intel; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE intel ( + id integer NOT NULL, + uid integer NOT NULL, + sender integer NOT NULL, + target integer NOT NULL, + mission text NOT NULL, + name text NOT NULL, + amount integer, + tick integer NOT NULL, + eta integer NOT NULL, + back integer, + ingal boolean NOT NULL +); + + +ALTER TABLE intel OWNER TO ndawn; + +-- +-- Name: full_intel; Type: VIEW; Schema: public; Owner: ndawn +-- + +CREATE VIEW full_intel AS + SELECT s.alliance AS salliance, + coords(s.x, s.y, s.z) AS scoords, + i.sender, + s.nick AS snick, + t.alliance AS talliance, + coords(t.x, t.y, t.z) AS tcoords, + i.target, + t.nick AS tnick, + i.mission, + i.tick, + min(i.eta) AS eta, + i.amount, + i.ingal, + i.uid, + u.username + FROM (((intel i + JOIN users u USING (uid)) + JOIN current_planet_stats t ON ((i.target = t.pid))) + JOIN current_planet_stats s ON ((i.sender = s.pid))) + GROUP BY i.tick, i.mission, t.x, t.y, t.z, s.x, s.y, s.z, i.amount, i.ingal, u.username, i.uid, t.alliance, s.alliance, t.nick, s.nick, i.sender, i.target; + + +ALTER TABLE full_intel OWNER TO ndawn; + +-- +-- Name: galaxies; Type: TABLE; Schema: public; Owner: ndawn +-- + +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 +); + + +ALTER TABLE galaxies OWNER TO ndawn; + +-- +-- Name: group_roles; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE group_roles ( + gid character(1) NOT NULL, + role character varying(32) NOT NULL +); + + +ALTER TABLE group_roles OWNER TO ndawn; + +-- +-- Name: groups; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE groups ( + groupname text NOT NULL, + gid character(1) NOT NULL +); + + +ALTER TABLE groups OWNER TO ndawn; + +-- +-- Name: incomings_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE incomings_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE 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.inc; + + +-- +-- Name: intel_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE intel_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE intel_id_seq OWNER TO ndawn; + +-- +-- Name: intel_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE intel_id_seq OWNED BY intel.id; + + +-- +-- Name: intel_scans; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE intel_scans ( + id integer NOT NULL, + intel integer NOT NULL +); + + +ALTER TABLE intel_scans OWNER TO ndawn; + +-- +-- Name: irc_requests; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE irc_requests ( + id integer NOT NULL, + channel text NOT NULL, + message text NOT NULL, + sent boolean DEFAULT false NOT NULL, + uid integer NOT NULL +); + + +ALTER TABLE irc_requests OWNER TO ndawn; + +-- +-- Name: irc_requests_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE irc_requests_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE irc_requests_id_seq OWNER TO ndawn; + +-- +-- Name: irc_requests_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE irc_requests_id_seq OWNED BY irc_requests.id; + + +-- +-- Name: last_smokes; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE last_smokes ( + nick citext NOT NULL, + "time" timestamp with time zone NOT NULL +); + + +ALTER TABLE last_smokes OWNER TO ndawn; + +-- +-- Name: misc; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE misc ( + id text NOT NULL, + value text +); + + +ALTER TABLE misc OWNER TO ndawn; + +-- +-- Name: planet_tags; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE planet_tags ( + pid integer NOT NULL, + tag citext NOT NULL, + uid integer NOT NULL, + "time" timestamp with time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE planet_tags OWNER TO ndawn; + +-- +-- Name: planets_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE planets_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE planets_id_seq OWNER TO ndawn; + +-- +-- Name: planets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE planets_id_seq OWNED BY planets.pid; + + +-- +-- Name: raid_access; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE raid_access ( + raid integer NOT NULL, + gid character(1) NOT NULL +); + + +ALTER TABLE raid_access OWNER TO ndawn; + +-- +-- Name: raid_claims; Type: TABLE; Schema: public; Owner: ndawn +-- + +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 +) +WITH (fillfactor='50'); + + +ALTER TABLE raid_claims OWNER TO ndawn; + +-- +-- Name: raid_targets; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE raid_targets ( + id integer NOT NULL, + raid integer NOT NULL, + pid integer NOT NULL, + comment text, + modified timestamp with time zone DEFAULT now() NOT NULL +) +WITH (fillfactor='50'); + + +ALTER TABLE raid_targets OWNER TO ndawn; + +-- +-- Name: raid_targets_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE raid_targets_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE raid_targets_id_seq OWNER TO ndawn; + +-- +-- Name: raid_targets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE raid_targets_id_seq OWNED BY raid_targets.id; + + +-- +-- Name: raids; Type: TABLE; Schema: public; Owner: ndawn +-- + +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, + ftid integer NOT NULL +); + + +ALTER TABLE raids OWNER TO ndawn; + +-- +-- Name: raids_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE raids_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE raids_id_seq OWNER TO ndawn; + +-- +-- Name: raids_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE raids_id_seq OWNED BY raids.id; + + +-- +-- Name: roles; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE roles ( + role character varying(32) NOT NULL +); + + +ALTER TABLE roles OWNER TO ndawn; + +-- +-- Name: scan_requests; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE scan_requests ( + id integer NOT NULL, + uid integer NOT NULL, + pid integer NOT NULL, + type text NOT NULL, + nick text NOT NULL, + tick integer DEFAULT tick() NOT NULL, + "time" timestamp with time zone DEFAULT now() NOT NULL, + sent boolean DEFAULT false NOT NULL +); + + +ALTER TABLE scan_requests OWNER TO ndawn; + +-- +-- Name: scan_requests_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE scan_requests_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE scan_requests_id_seq OWNER TO ndawn; + +-- +-- Name: scan_requests_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE scan_requests_id_seq OWNED BY scan_requests.id; + + +-- +-- Name: scans; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE scans ( + tick integer NOT NULL, + scan_id text NOT NULL, + pid integer, + type text, + uid integer DEFAULT '-1'::integer NOT NULL, + groupscan boolean DEFAULT false NOT NULL, + parsed boolean DEFAULT false NOT NULL, + id integer NOT NULL +); + + +ALTER TABLE scans OWNER TO ndawn; + +-- +-- Name: scans_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE scans_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE scans_id_seq OWNER TO ndawn; + +-- +-- Name: scans_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE scans_id_seq OWNED BY scans.id; + + +-- +-- Name: session_log; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE session_log ( + uid integer NOT NULL, + "time" timestamp with time zone NOT NULL, + ip inet NOT NULL, + country character(2) NOT NULL, + session text NOT NULL, + remember boolean NOT NULL +); + + +ALTER TABLE session_log OWNER TO ndawn; + +-- +-- Name: ship_stats_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE ship_stats_id_seq + START WITH 0 + INCREMENT BY 1 + MINVALUE 0 + NO MAXVALUE + CACHE 1; + + +ALTER TABLE ship_stats_id_seq OWNER TO ndawn; + +-- +-- Name: ship_stats_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn +-- + +ALTER SEQUENCE ship_stats_id_seq OWNED BY ship_stats.id; + + +-- +-- Name: sms; Type: TABLE; Schema: public; Owner: ndawn +-- + +CREATE TABLE sms ( + id integer NOT NULL, + msgid text, + uid integer NOT NULL, + status text DEFAULT 'Waiting'::text NOT NULL, + number text NOT NULL, + message character varying(140) NOT NULL, + cost numeric(4,2) DEFAULT 0 NOT NULL, + "time" timestamp with time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE sms OWNER TO ndawn; + +-- +-- Name: sms_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn +-- + +CREATE SEQUENCE sms_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE sms_id_seq OWNER TO ndawn; -- --- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: - +-- Name: sms_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn -- -CREATE PROCEDURAL LANGUAGE plperl; +ALTER SEQUENCE sms_id_seq OWNED BY sms.id; -- --- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: - +-- Name: smslist; Type: TABLE; Schema: public; Owner: ndawn -- -CREATE PROCEDURAL LANGUAGE plpgsql; - - -SET search_path = public, pg_catalog; +CREATE TABLE smslist ( + nick text NOT NULL, + sms text NOT NULL, + info text +); -SET default_tablespace = ''; -SET default_with_oids = false; +ALTER TABLE smslist OWNER TO ndawn; -- --- Name: alliance_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: table_updates; Type: VIEW; Schema: public; Owner: ndawn -- -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 -); +CREATE VIEW table_updates AS + SELECT t.schemaname, + t.relname, + c.reloptions, + t.n_tup_upd, + t.n_tup_hot_upd, + CASE + WHEN (t.n_tup_upd > 0) THEN ((((t.n_tup_hot_upd)::numeric / (t.n_tup_upd)::numeric) * 100.0))::numeric(5,2) + ELSE NULL::numeric + END AS hot_ratio + FROM (pg_stat_all_tables t + JOIN (pg_class c + JOIN pg_namespace n ON ((c.relnamespace = n.oid))) ON (((n.nspname = t.schemaname) AND (c.relname = t.relname) AND (t.n_tup_upd > 0)))); + +ALTER TABLE table_updates OWNER TO ndawn; -- --- Name: ead_status; Type: TYPE; Schema: public; Owner: - +-- Name: users_uid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn -- -CREATE TYPE ead_status AS ENUM ( - '', - 'NAP', - 'Friendly', - 'Hostile' -); +CREATE SEQUENCE users_uid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; +ALTER TABLE users_uid_seq OWNER TO ndawn; + -- --- Name: alliances; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: users_uid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn -- -CREATE TABLE alliances ( - id integer NOT NULL, - name character varying NOT NULL, - relationship ead_status DEFAULT ''::ead_status NOT NULL -); +ALTER SEQUENCE users_uid_seq OWNED BY users.uid; -- --- Name: calls; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_namespace_access; Type: TABLE; Schema: public; Owner: ndawn -- -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 wiki_namespace_access ( + namespace text NOT NULL, + gid character(1) NOT NULL, + edit boolean DEFAULT false NOT NULL, + post boolean DEFAULT false NOT NULL, + moderate boolean DEFAULT false NOT NULL ); +ALTER TABLE wiki_namespace_access OWNER TO ndawn; + -- --- Name: channel_flags; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_namespaces; Type: TABLE; Schema: public; Owner: ndawn -- -CREATE TABLE channel_flags ( - id integer NOT NULL, - name text NOT NULL +CREATE TABLE wiki_namespaces ( + namespace character varying(16) NOT NULL ); +ALTER TABLE wiki_namespaces OWNER TO ndawn; + -- --- Name: channel_group_flags; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_page_access; Type: TABLE; Schema: public; Owner: ndawn -- -CREATE TABLE channel_group_flags ( - channel integer NOT NULL, - "group" integer NOT NULL, - flag integer NOT NULL +CREATE TABLE wiki_page_access ( + wpid integer NOT NULL, + uid integer NOT NULL, + edit boolean DEFAULT false NOT NULL, + moderate boolean DEFAULT false NOT NULL ); +ALTER TABLE wiki_page_access OWNER TO ndawn; + -- --- Name: channels; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_page_revisions; Type: TABLE; Schema: public; Owner: ndawn -- -CREATE TABLE channels ( - id integer NOT NULL, - name text NOT NULL, - description text NOT NULL +CREATE TABLE wiki_page_revisions ( + wpid integer, + wprev integer NOT NULL, + parent integer, + text text NOT NULL, + comment text NOT NULL, + "time" timestamp with time zone DEFAULT now() NOT NULL, + uid integer ); +ALTER TABLE wiki_page_revisions OWNER TO ndawn; + -- --- Name: planet_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_page_revisions_wprev_seq; Type: SEQUENCE; Schema: public; Owner: ndawn -- -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 -); +CREATE SEQUENCE wiki_page_revisions_wprev_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; +ALTER TABLE wiki_page_revisions_wprev_seq OWNER TO ndawn; + -- --- Name: planets; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_page_revisions_wprev_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn -- -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 ead_status DEFAULT ''::ead_status NOT NULL, - hit_us integer DEFAULT 0 NOT NULL, - alliance_id integer, - channel text, - ftid integer -); +ALTER SEQUENCE wiki_page_revisions_wprev_seq OWNED BY wiki_page_revisions.wprev; -- --- Name: current_planet_stats; Type: VIEW; Schema: public; Owner: - +-- Name: wiki_pages; Type: TABLE; Schema: public; Owner: ndawn -- -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 wiki_pages ( + wpid integer NOT NULL, + name character varying(255) NOT NULL, + namespace text DEFAULT ''::text NOT NULL, + textsearch tsvector DEFAULT to_tsvector(''::text) NOT NULL, + wprev integer, + "time" timestamp with time zone DEFAULT now() NOT NULL +); + +ALTER TABLE wiki_pages OWNER TO ndawn; -- --- Name: current_planet_stats_full; Type: VIEW; Schema: public; Owner: - +-- Name: wiki_pages_wpid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn -- -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 SEQUENCE wiki_pages_wpid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; +ALTER TABLE wiki_pages_wpid_seq OWNER TO ndawn; + -- --- Name: defense_missions; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_pages_wpid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn -- -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 SEQUENCE wiki_pages_wpid_seq OWNED BY wiki_pages.wpid; -- --- Name: defense_requests; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: aid; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE defense_requests ( - id integer NOT NULL, - uid integer NOT NULL, - message text NOT NULL, - sent boolean DEFAULT false NOT NULL -); +ALTER TABLE ONLY alliances ALTER COLUMN aid SET DEFAULT nextval('alliances_id_seq'::regclass); -- --- Name: dumps; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: call; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE dumps ( - tick integer NOT NULL, - type text NOT NULL, - dump text NOT NULL, - modified integer DEFAULT 0 NOT NULL -); +ALTER TABLE ONLY calls ALTER COLUMN call SET DEFAULT nextval('calls_id_seq'::regclass); -- --- Name: fleet_scans; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: num; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE fleet_scans ( - id integer NOT NULL, - scan integer NOT NULL -); +ALTER TABLE ONLY fleet_ships ALTER COLUMN num SET DEFAULT nextval('fleet_ships_num_seq'::regclass); -- --- Name: fleet_ships; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: fid; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE fleet_ships ( - id integer NOT NULL, - ship text NOT NULL, - amount integer NOT NULL, - num integer NOT NULL -); +ALTER TABLE ONLY fleets ALTER COLUMN fid SET DEFAULT nextval('fleets_id_seq'::regclass); -- --- Name: fleets; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: fbid; Type: DEFAULT; Schema: public; Owner: ndawn -- -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 TABLE ONLY forum_boards ALTER COLUMN fbid SET DEFAULT nextval('forum_boards_fbid_seq'::regclass); -- --- Name: forum_access; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: fcid; Type: DEFAULT; Schema: public; Owner: ndawn -- -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 TABLE ONLY forum_categories ALTER COLUMN fcid SET DEFAULT nextval('forum_categories_fcid_seq'::regclass); -- --- Name: forum_boards; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: fpid; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE forum_boards ( - fbid integer NOT NULL, - fcid integer NOT NULL, - board text NOT NULL -); +ALTER TABLE ONLY forum_posts ALTER COLUMN fpid SET DEFAULT nextval('forum_posts_fpid_seq'::regclass); -- --- Name: forum_categories; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: ftid; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE forum_categories ( - fcid integer NOT NULL, - category text NOT NULL -); +ALTER TABLE ONLY forum_threads ALTER COLUMN ftid SET DEFAULT nextval('forum_threads_ftid_seq'::regclass); -- --- Name: forum_posts; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: inc; Type: DEFAULT; Schema: public; Owner: ndawn -- -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 TABLE ONLY incomings ALTER COLUMN inc SET DEFAULT nextval('incomings_id_seq'::regclass); -- --- Name: forum_thread_visits; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE forum_thread_visits ( - uid integer NOT NULL, - ftid integer NOT NULL, - "time" timestamp with time zone DEFAULT now() NOT NULL -); +ALTER TABLE ONLY intel ALTER COLUMN id SET DEFAULT nextval('intel_id_seq'::regclass); -- --- Name: forum_threads; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn -- -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 TABLE ONLY irc_requests ALTER COLUMN id SET DEFAULT nextval('irc_requests_id_seq'::regclass); -- --- Name: galaxies; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: pid; Type: DEFAULT; Schema: public; Owner: ndawn -- -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 -); +ALTER TABLE ONLY planets ALTER COLUMN pid SET DEFAULT nextval('planets_id_seq'::regclass); -- --- Name: graphs; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn -- -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 -); +ALTER TABLE ONLY raid_targets ALTER COLUMN id SET DEFAULT nextval('raid_targets_id_seq'::regclass); -- --- Name: groupmembers; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE groupmembers ( - gid integer NOT NULL, - uid integer NOT NULL -); +ALTER TABLE ONLY raids ALTER COLUMN id SET DEFAULT nextval('raids_id_seq'::regclass); -- --- Name: groups; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE groups ( - gid integer NOT NULL, - groupname text NOT NULL, - flag character(1), - attack boolean DEFAULT false NOT NULL -); +ALTER TABLE ONLY scan_requests ALTER COLUMN id SET DEFAULT nextval('scan_requests_id_seq'::regclass); -- --- Name: incomings; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn -- -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 ONLY scans ALTER COLUMN id SET DEFAULT nextval('scans_id_seq'::regclass); -- --- Name: misc; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE misc ( - id text NOT NULL, - value text -); +ALTER TABLE ONLY ship_stats ALTER COLUMN id SET DEFAULT nextval('ship_stats_id_seq'::regclass); -- --- Name: planet_data; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE planet_data ( - id integer NOT NULL, - scan integer NOT NULL, - tick integer NOT NULL, - rid integer NOT NULL, - amount integer NOT NULL -); +ALTER TABLE ONLY sms ALTER COLUMN id SET DEFAULT nextval('sms_id_seq'::regclass); -- --- Name: planet_data_types; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: uid; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE planet_data_types ( - id integer NOT NULL, - category text NOT NULL, - name text NOT NULL -); +ALTER TABLE ONLY users ALTER COLUMN uid SET DEFAULT nextval('users_uid_seq'::regclass); -- --- Name: scans; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wprev; Type: DEFAULT; Schema: public; Owner: ndawn -- -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 ONLY wiki_page_revisions ALTER COLUMN wprev SET DEFAULT nextval('wiki_page_revisions_wprev_seq'::regclass); -- --- Name: planet_scans; Type: VIEW; Schema: public; Owner: - +-- Name: wpid; Type: DEFAULT; Schema: public; Owner: ndawn -- -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 ONLY wiki_pages ALTER COLUMN wpid SET DEFAULT nextval('wiki_pages_wpid_seq'::regclass); -- --- Name: raid_access; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: accounts_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE TABLE raid_access ( - raid integer NOT NULL, - gid integer NOT NULL -); +ALTER TABLE ONLY users + ADD CONSTRAINT accounts_pkey PRIMARY KEY (uid); -- --- Name: raid_claims; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: alliance_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 TABLE ONLY alliance_stats + ADD CONSTRAINT alliance_stats_pkey PRIMARY KEY (aid, tick); -- --- Name: raid_targets; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: alliances_name_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 -); +ALTER TABLE ONLY alliances + ADD CONSTRAINT alliances_name_key UNIQUE (alliance); -- --- Name: raids; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: alliances_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 -); +ALTER TABLE ONLY alliances + ADD CONSTRAINT alliances_pkey PRIMARY KEY (aid); -- --- Name: ship_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: available_planet_tags_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 ONLY available_planet_tags + ADD CONSTRAINT available_planet_tags_pkey PRIMARY KEY (tag); -- --- Name: smslist; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: call_statuses_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE TABLE smslist ( - nick text NOT NULL, - sms text NOT NULL, - info text -); +ALTER TABLE ONLY call_statuses + ADD CONSTRAINT call_statuses_pkey PRIMARY KEY (status); -- --- Name: structure_scans; Type: VIEW; Schema: public; Owner: - +-- Name: calls_member_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 ONLY calls + ADD CONSTRAINT calls_member_key UNIQUE (uid, landing_tick); -- --- Name: users; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: calls_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 TABLE ONLY calls + ADD CONSTRAINT calls_pkey PRIMARY KEY (call); -- --- Name: usersingroup; Type: VIEW; Schema: public; Owner: - +-- Name: channel_flags_name_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE VIEW usersingroup AS - SELECT groups.gid, groups.groupname, users.uid, users.username FROM ((users NATURAL JOIN groupmembers) NATURAL JOIN groups); +ALTER TABLE ONLY channel_flags + ADD CONSTRAINT channel_flags_name_key UNIQUE (name); -- --- Name: add_call(); Type: FUNCTION; Schema: public; Owner: - +-- Name: channel_flags_pkey; Type: CONSTRAINT; 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; - - --- --- Name: add_intel(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, integer); Type: FUNCTION; Schema: public; 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; - - --- --- Name: add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying); Type: FUNCTION; Schema: public; Owner: - --- - -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; - - --- --- Name: add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying); Type: FUNCTION; Schema: public; Owner: - --- - -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; - - --- --- Name: add_intel4(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, integer); Type: FUNCTION; Schema: public; Owner: - --- - -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; - - --- --- Name: add_user(); Type: FUNCTION; Schema: public; Owner: - --- +ALTER TABLE ONLY channel_flags + ADD CONSTRAINT channel_flags_pkey PRIMARY KEY (flag); + -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; - - --- --- Name: calc_rank(integer); Type: FUNCTION; Schema: public; Owner: - --- - -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; - - --- --- Name: calc_rank3(integer); Type: FUNCTION; Schema: public; Owner: - --- - -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; - - --- --- Name: calculate_rankings(integer); Type: FUNCTION; Schema: public; Owner: - --- - -CREATE FUNCTION calculate_rankings(integer) RETURNS void - AS $_$my ($tick) = @_; -spi_exec_query("DELETE FROM rankings WHERE tick = $tick"); -my $rv = spi_exec_query("SELECT id, score FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY score DESC"); -my $status = $rv->{status}; -my $nrows = $rv->{processed}; -my $id; -for ($row = 1; $row <= $nrows; ++$row ){ -$id = $rv->{rows}[$row-1]->{id}; -spi_exec_query("INSERT INTO rankings (id,tick,score) VALUES($id,$tick,$row)"); -} - -my $rv = spi_exec_query("SELECT id, value FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY value DESC"); -my $status = $rv->{status}; -my $nrows = $rv->{processed}; -my $id; -for ($row = 1; $row <= $nrows; ++$row ){ -$id = $rv->{rows}[$row-1]->{id}; -spi_exec_query("UPDATE rankings SET value = $row WHERE id = $id AND tick = $tick"); -} - -my $rv = spi_exec_query("SELECT id, size FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY size DESC"); -my $status = $rv->{status}; -my $nrows = $rv->{processed}; -my $id; -for ($row = 1; $row <= $nrows; ++$row ){ -$id = $rv->{rows}[$row-1]->{id}; -spi_exec_query("UPDATE rankings SET size = $row WHERE id = $id AND tick = $tick"); -} - -my $rv = spi_exec_query("SELECT id, (score-value) as xp FROM planets NATURAL JOIN planet_stats WHERE tick = $tick ORDER BY xp DESC"); -my $status = $rv->{status}; -my $nrows = $rv->{processed}; -my $id; -for ($row = 1; $row <= $nrows; ++$row ){ -$id = $rv->{rows}[$row-1]->{id}; -spi_exec_query("UPDATE rankings SET xp = $row WHERE id = $id AND tick = $tick"); -} -$_$ - LANGUAGE plperl; - - --- --- Name: change_member(); Type: FUNCTION; Schema: public; Owner: - +-- +-- Name: channel_group_flags_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE FUNCTION change_member() RETURNS trigger - AS $_X$if ($_TD->{event} eq 'INSERT' && $_TD->{new}{gid} == 2){ - $rv = spi_exec_query("SELECT * FROM users WHERE uid = $_TD->{new}{uid};"); - if ($rv->{rows}[0]->{planet}){ - spi_exec_query("UPDATE planets SET alliance_id = 1 WHERE id = $rv->{rows}[0]->{planet};"); - } -} -if ($_TD->{event} eq 'DELETE' && $_TD->{old}{gid} == 2){ - $rv = spi_exec_query("SELECT * FROM users WHERE uid = $_TD->{old}{uid};"); - if ($rv->{rows}[0]->{planet}){ - spi_exec_query("UPDATE planets SET alliance_id = NULL WHERE id = $rv->{rows}[0]{planet};"); - } -} -return;$_X$ - LANGUAGE plperl; +ALTER TABLE ONLY channel_group_flags + ADD CONSTRAINT channel_group_flags_pkey PRIMARY KEY (channel, gid, flag); -- --- Name: coords(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: - +-- Name: channels_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE FUNCTION coords(x integer, y integer, z integer) RETURNS text - AS $_$my ($x,$y,$z) = @_; -return "$x:$y:$z";$_$ - LANGUAGE plperl IMMUTABLE; +ALTER TABLE ONLY channels + ADD CONSTRAINT channels_pkey PRIMARY KEY (channel); -- --- Name: find_alliance_id(character varying); Type: FUNCTION; Schema: public; Owner: - +-- Name: clickatell_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE FUNCTION find_alliance_id(character varying) RETURNS integer - AS $_$ -my ($name) = @_; -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) VALUES($1,$2)','int4','varchar'); - $rv = spi_exec_prepared($query,$id,$name); - spi_freeplan($query); - if (rv->{status} != SPI_OK_INSERT){ - return; - } -} -return $id; -$_$ - LANGUAGE plperl; - - --- --- 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; - - --- --- Name: groups(integer); Type: FUNCTION; Schema: public; Owner: - --- - -CREATE FUNCTION groups(uid integer) RETURNS SETOF integer - AS $_$SELECT gid FROM groupmembers WHERE uid = $1 UNION SELECT -1$_$ - LANGUAGE sql STABLE; - - --- --- Name: max_bank_hack(integer, integer, integer, integer, integer); Type: FUNCTION; 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; - - --- --- Name: old_claim(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: - +ALTER TABLE ONLY clickatell + ADD CONSTRAINT clickatell_pkey PRIMARY KEY (api_id, username); + + +-- +-- Name: covop_attacks_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE FUNCTION old_claim("timestamp" timestamp with time zone) RETURNS boolean - AS $_$SELECT NOW() - '10 minutes'::INTERVAL > $1;$_$ - LANGUAGE sql IMMUTABLE; +ALTER TABLE ONLY covop_attacks + ADD CONSTRAINT covop_attacks_pkey PRIMARY KEY (pid, tick, uid); -- --- Name: planetcoords(integer, integer); Type: FUNCTION; Schema: public; Owner: - +-- Name: defense_missions_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 ONLY defense_missions + ADD CONSTRAINT defense_missions_pkey PRIMARY KEY (fleet); -- --- Name: planetid(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: - +-- Name: development_scans_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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; +ALTER TABLE ONLY development_scans + ADD CONSTRAINT development_scans_pkey PRIMARY KEY (id); -- --- Name: populate_ticks(); Type: FUNCTION; Schema: public; Owner: - +-- Name: dumps_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 ONLY dumps + ADD CONSTRAINT dumps_pkey PRIMARY KEY (tick, type, modified); -- --- Name: tick(); Type: FUNCTION; Schema: public; Owner: - +-- Name: email_change_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE FUNCTION tick() RETURNS integer - AS $$SELECT value::integer FROM misc WHERE id = 'TICK'$$ - LANGUAGE sql STABLE; +ALTER TABLE ONLY email_change + ADD CONSTRAINT email_change_pkey PRIMARY KEY (id); -- --- Name: unclaim_target(); Type: FUNCTION; Schema: public; Owner: - +-- Name: fleet_scans_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 ONLY fleet_scans + ADD CONSTRAINT fleet_scans_pkey PRIMARY KEY (fid); -- --- Name: update_forum_post(); Type: FUNCTION; Schema: public; Owner: - +-- Name: fleet_ships_num_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 ONLY fleet_ships + ADD CONSTRAINT fleet_ships_num_key UNIQUE (num); -- --- Name: update_user_planet(); Type: FUNCTION; Schema: public; Owner: - +-- Name: fleet_ships_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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 ONLY fleet_ships + ADD CONSTRAINT fleet_ships_pkey PRIMARY KEY (fid, ship); -- --- Name: updated_target(); Type: FUNCTION; Schema: public; Owner: - +-- Name: fleets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -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; +ALTER TABLE ONLY fleets + ADD CONSTRAINT fleets_pkey PRIMARY KEY (fid); -- --- Name: concat(text); Type: AGGREGATE; Schema: public; Owner: - +-- Name: forum_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE AGGREGATE concat(text) ( - SFUNC = textcat, - STYPE = text, - INITCOND = '' -); +ALTER TABLE ONLY forum_access + ADD CONSTRAINT forum_access_pkey PRIMARY KEY (fbid, gid); -- --- Name: alliances_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: forum_boards_fcid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE alliances_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY forum_boards + ADD CONSTRAINT forum_boards_fcid_key UNIQUE (fcid, board); -- --- Name: alliances_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: forum_boards_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE alliances_id_seq OWNED BY alliances.id; +ALTER TABLE ONLY forum_boards + ADD CONSTRAINT forum_boards_pkey PRIMARY KEY (fbid); -- --- Name: calls_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: forum_categories_category_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE calls_id_seq - START WITH 1 - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY forum_categories + ADD CONSTRAINT forum_categories_category_key UNIQUE (category); -- --- Name: calls_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: forum_categories_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE calls_id_seq OWNED BY calls.id; +ALTER TABLE ONLY forum_categories + ADD CONSTRAINT forum_categories_pkey PRIMARY KEY (fcid); -- --- Name: channel_flags_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: forum_posts_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE channel_flags_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY forum_posts + ADD CONSTRAINT forum_posts_pkey PRIMARY KEY (fpid); -- --- Name: channel_flags_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: forum_priv_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE channel_flags_id_seq OWNED BY channel_flags.id; +ALTER TABLE ONLY forum_priv_access + ADD CONSTRAINT forum_priv_access_pkey PRIMARY KEY (uid, ftid); -- --- Name: channels_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: forum_thread_visits_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE channels_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY forum_thread_visits + ADD CONSTRAINT forum_thread_visits_pkey PRIMARY KEY (uid, ftid); -- --- Name: channels_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: forum_threads_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE channels_id_seq OWNED BY channels.id; +ALTER TABLE ONLY forum_threads + ADD CONSTRAINT forum_threads_pkey PRIMARY KEY (ftid); -- --- Name: defense_requests_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: full_fleets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE defense_requests_id_seq - START WITH 1 - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY full_fleets + ADD CONSTRAINT full_fleets_pkey PRIMARY KEY (fid); -- --- Name: defense_requests_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: galaxies_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE defense_requests_id_seq OWNED BY defense_requests.id; +ALTER TABLE ONLY galaxies + ADD CONSTRAINT galaxies_pkey PRIMARY KEY (tick, x, y); -- --- Name: fleet_ships_num_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: group_roles_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE fleet_ships_num_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY group_roles + ADD CONSTRAINT group_roles_pkey PRIMARY KEY (gid, role); -- --- Name: fleet_ships_num_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: groupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE fleet_ships_num_seq OWNED BY fleet_ships.num; +ALTER TABLE ONLY groupmembers + ADD CONSTRAINT groupmembers_pkey PRIMARY KEY (gid, uid); -- --- Name: fleets_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: groups_groupname_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE fleets_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY groups + ADD CONSTRAINT groups_groupname_key UNIQUE (groupname); -- --- Name: fleets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: groups_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE fleets_id_seq OWNED BY fleets.id; +ALTER TABLE ONLY groups + ADD CONSTRAINT groups_pkey PRIMARY KEY (gid); -- --- Name: forum_boards_fbid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: incomings_call_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE forum_boards_fbid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY incomings + ADD CONSTRAINT incomings_call_key UNIQUE (call, pid, fleet); -- --- Name: forum_boards_fbid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: incomings_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE forum_boards_fbid_seq OWNED BY forum_boards.fbid; +ALTER TABLE ONLY incomings + ADD CONSTRAINT incomings_pkey PRIMARY KEY (inc); -- --- Name: forum_categories_fcid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: intel_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE forum_categories_fcid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY intel + ADD CONSTRAINT intel_pkey PRIMARY KEY (id); -- --- Name: forum_categories_fcid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: intel_scans_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE forum_categories_fcid_seq OWNED BY forum_categories.fcid; +ALTER TABLE ONLY intel_scans + ADD CONSTRAINT intel_scans_pkey PRIMARY KEY (id, intel); -- --- Name: forum_posts_fpid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: irc_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE forum_posts_fpid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY irc_requests + ADD CONSTRAINT irc_requests_pkey PRIMARY KEY (id); -- --- Name: forum_posts_fpid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: last_smokes_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE forum_posts_fpid_seq OWNED BY forum_posts.fpid; +ALTER TABLE ONLY last_smokes + ADD CONSTRAINT last_smokes_pkey PRIMARY KEY (nick); -- --- Name: forum_threads_ftid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: launch_confirmations_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE forum_threads_ftid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY launch_confirmations + ADD CONSTRAINT launch_confirmations_pkey PRIMARY KEY (fid); -- --- Name: forum_threads_ftid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: misc_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE forum_threads_ftid_seq OWNED BY forum_threads.ftid; +ALTER TABLE ONLY misc + ADD CONSTRAINT misc_pkey PRIMARY KEY (id); -- --- Name: groups_gid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: planet_scans_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE groups_gid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY planet_scans + ADD CONSTRAINT planet_scans_pkey PRIMARY KEY (id); -- --- Name: groups_gid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: planet_stats_id_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE groups_gid_seq OWNED BY groups.gid; +ALTER TABLE ONLY planet_stats + ADD CONSTRAINT planet_stats_id_key UNIQUE (pid, tick); -- --- Name: incomings_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: planet_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE incomings_id_seq - START WITH 1 - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY planet_stats + ADD CONSTRAINT planet_stats_pkey PRIMARY KEY (tick, x, y, z); -- --- Name: incomings_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: planet_tags_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE incomings_id_seq OWNED BY incomings.id; +ALTER TABLE ONLY planet_tags + ADD CONSTRAINT planet_tags_pkey PRIMARY KEY (pid, uid, tag); -- --- Name: planet_data_types_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: planets_ftid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE planet_data_types_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY planets + ADD CONSTRAINT planets_ftid_key UNIQUE (ftid); -- --- Name: planet_data_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: planets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE planet_data_types_id_seq OWNED BY planet_data_types.id; +ALTER TABLE ONLY planets + ADD CONSTRAINT planets_pkey PRIMARY KEY (pid); -- --- Name: planets_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: raid_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE planets_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY raid_access + ADD CONSTRAINT raid_access_pkey PRIMARY KEY (raid, gid); -- --- Name: planets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: raid_claims_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE planets_id_seq OWNED BY planets.id; +ALTER TABLE ONLY raid_claims + ADD CONSTRAINT raid_claims_pkey PRIMARY KEY (target, uid, wave); -- --- Name: raid_targets_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: raid_targets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE raid_targets_id_seq - START WITH 1 - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY raid_targets + ADD CONSTRAINT raid_targets_pkey PRIMARY KEY (id); -- --- Name: raid_targets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: raid_targets_raid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE raid_targets_id_seq OWNED BY raid_targets.id; +ALTER TABLE ONLY raid_targets + ADD CONSTRAINT raid_targets_raid_key UNIQUE (raid, pid); -- --- Name: raids_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: raids_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE raids_id_seq - START WITH 1 - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY raids + ADD CONSTRAINT raids_pkey PRIMARY KEY (id); -- --- Name: raids_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: roles_role_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE raids_id_seq OWNED BY raids.id; +ALTER TABLE ONLY roles + ADD CONSTRAINT roles_role_key UNIQUE (role); -- --- Name: scans_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: scan_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE scans_id_seq - START WITH 1 - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY scan_requests + ADD CONSTRAINT scan_requests_pkey PRIMARY KEY (id); -- --- Name: scans_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: scan_requests_tick_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE scans_id_seq OWNED BY scans.id; +ALTER TABLE ONLY scan_requests + ADD CONSTRAINT scan_requests_tick_key UNIQUE (tick, pid, type, uid); -- --- Name: test; Type: SEQUENCE; Schema: public; Owner: - +-- Name: scans_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE test - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY scans + ADD CONSTRAINT scans_pkey PRIMARY KEY (id); -- --- Name: users_uid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: scans_scan_id_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE users_uid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY scans + ADD CONSTRAINT scans_scan_id_key UNIQUE (scan_id, tick, groupscan); -- --- Name: users_uid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: session_log_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE users_uid_seq OWNED BY users.uid; +ALTER TABLE ONLY session_log + ADD CONSTRAINT session_log_pkey PRIMARY KEY (uid, "time", ip); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: ship_stats_id_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE alliances ALTER COLUMN id SET DEFAULT nextval('alliances_id_seq'::regclass); +ALTER TABLE ONLY ship_stats + ADD CONSTRAINT ship_stats_id_key UNIQUE (id); + +ALTER TABLE ship_stats CLUSTER ON ship_stats_id_key; -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: ship_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE calls ALTER COLUMN id SET DEFAULT nextval('calls_id_seq'::regclass); +ALTER TABLE ONLY ship_stats + ADD CONSTRAINT ship_stats_pkey PRIMARY KEY (ship); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: sms_msgid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE channel_flags ALTER COLUMN id SET DEFAULT nextval('channel_flags_id_seq'::regclass); +ALTER TABLE ONLY sms + ADD CONSTRAINT sms_msgid_key UNIQUE (msgid); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: sms_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE channels ALTER COLUMN id SET DEFAULT nextval('channels_id_seq'::regclass); +ALTER TABLE ONLY sms + ADD CONSTRAINT sms_pkey PRIMARY KEY (id); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: smslist_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE defense_requests ALTER COLUMN id SET DEFAULT nextval('defense_requests_id_seq'::regclass); +ALTER TABLE ONLY smslist + ADD CONSTRAINT smslist_pkey PRIMARY KEY (sms); -- --- Name: num; Type: DEFAULT; Schema: public; Owner: - +-- Name: ticks_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE fleet_ships ALTER COLUMN num SET DEFAULT nextval('fleet_ships_num_seq'::regclass); +ALTER TABLE ONLY ticks + ADD CONSTRAINT ticks_pkey PRIMARY KEY (t); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: users_hostmask_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE fleets ALTER COLUMN id SET DEFAULT nextval('fleets_id_seq'::regclass); +ALTER TABLE ONLY users + ADD CONSTRAINT users_hostmask_key UNIQUE (hostmask); -- --- Name: fbid; Type: DEFAULT; Schema: public; Owner: - +-- Name: users_planet_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE forum_boards ALTER COLUMN fbid SET DEFAULT nextval('forum_boards_fbid_seq'::regclass); +ALTER TABLE ONLY users + ADD CONSTRAINT users_planet_key UNIQUE (pid); -- --- Name: fcid; Type: DEFAULT; Schema: public; Owner: - +-- Name: users_pnick_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE forum_categories ALTER COLUMN fcid SET DEFAULT nextval('forum_categories_fcid_seq'::regclass); +ALTER TABLE ONLY users + ADD CONSTRAINT users_pnick_key UNIQUE (pnick); -- --- Name: fpid; Type: DEFAULT; Schema: public; Owner: - +-- Name: users_tfid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE forum_posts ALTER COLUMN fpid SET DEFAULT nextval('forum_posts_fpid_seq'::regclass); +ALTER TABLE ONLY users + ADD CONSTRAINT users_tfid_key UNIQUE (ftid); -- --- Name: ftid; Type: DEFAULT; Schema: public; Owner: - +-- Name: users_username_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE forum_threads ALTER COLUMN ftid SET DEFAULT nextval('forum_threads_ftid_seq'::regclass); +ALTER TABLE ONLY users + ADD CONSTRAINT users_username_key UNIQUE (username); -- --- Name: gid; Type: DEFAULT; Schema: public; Owner: - +-- Name: wiki_namespace_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE groups ALTER COLUMN gid SET DEFAULT nextval('groups_gid_seq'::regclass); +ALTER TABLE ONLY wiki_namespace_access + ADD CONSTRAINT wiki_namespace_access_pkey PRIMARY KEY (gid, namespace); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: wiki_namespaces_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE incomings ALTER COLUMN id SET DEFAULT nextval('incomings_id_seq'::regclass); +ALTER TABLE ONLY wiki_namespaces + ADD CONSTRAINT wiki_namespaces_pkey PRIMARY KEY (namespace); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: wiki_page_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE planet_data_types ALTER COLUMN id SET DEFAULT nextval('planet_data_types_id_seq'::regclass); +ALTER TABLE ONLY wiki_page_access + ADD CONSTRAINT wiki_page_access_pkey PRIMARY KEY (uid, wpid); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: wiki_page_revisions_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE planets ALTER COLUMN id SET DEFAULT nextval('planets_id_seq'::regclass); +ALTER TABLE ONLY wiki_page_revisions + ADD CONSTRAINT wiki_page_revisions_pkey PRIMARY KEY (wprev); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: wiki_pages_namespace_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE raid_targets ALTER COLUMN id SET DEFAULT nextval('raid_targets_id_seq'::regclass); +ALTER TABLE ONLY wiki_pages + ADD CONSTRAINT wiki_pages_namespace_key UNIQUE (namespace, name); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: wiki_pages_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE raids ALTER COLUMN id SET DEFAULT nextval('raids_id_seq'::regclass); +ALTER TABLE ONLY wiki_pages + ADD CONSTRAINT wiki_pages_pkey PRIMARY KEY (wpid); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: development_scans_planet_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE scans ALTER COLUMN id SET DEFAULT nextval('scans_id_seq'::regclass); +CREATE INDEX development_scans_planet_index ON development_scans USING btree (pid, tick); -- --- Name: uid; Type: DEFAULT; Schema: public; Owner: - +-- Name: fleets_sender_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE users ALTER COLUMN uid SET DEFAULT nextval('users_uid_seq'::regclass); +CREATE INDEX fleets_sender_index ON fleets USING btree (pid); -- --- Name: accounts_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_posts_ftid_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY users - ADD CONSTRAINT accounts_pkey PRIMARY KEY (uid); +CREATE INDEX forum_posts_ftid_index ON forum_posts USING btree (ftid); -- --- Name: alliance_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_posts_textsearch_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY alliance_stats - ADD CONSTRAINT alliance_stats_pkey PRIMARY KEY (id, tick); +CREATE INDEX forum_posts_textsearch_index ON forum_posts USING gin (textsearch); -- --- Name: alliances_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: groupmembers_uid_key; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY alliances - ADD CONSTRAINT alliances_name_key UNIQUE (name); +CREATE INDEX groupmembers_uid_key ON groupmembers USING btree (uid); -- --- Name: alliances_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: intel_tick_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY alliances - ADD CONSTRAINT alliances_pkey PRIMARY KEY (id); +CREATE INDEX intel_tick_index ON intel USING btree (tick); -- --- Name: calls_member_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planet_scans_planet_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY calls - ADD CONSTRAINT calls_member_key UNIQUE (member, landing_tick); +CREATE INDEX planet_scans_planet_index ON planet_scans USING btree (pid, tick); -- --- Name: calls_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planet_stats_scorerank_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY calls - ADD CONSTRAINT calls_pkey PRIMARY KEY (id); +CREATE INDEX planet_stats_scorerank_index ON planet_stats USING btree (tick, scorerank); -- --- Name: channel_flags_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planet_stats_sizerank_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channel_flags - ADD CONSTRAINT channel_flags_name_key UNIQUE (name); +CREATE INDEX planet_stats_sizerank_index ON planet_stats USING btree (tick, sizerank); -- --- Name: channel_flags_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planet_stats_valuerank_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channel_flags - ADD CONSTRAINT channel_flags_pkey PRIMARY KEY (id); +CREATE INDEX planet_stats_valuerank_index ON planet_stats USING btree (tick, valuerank); -- --- Name: channel_group_flags_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planet_stats_xprank_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channel_group_flags - ADD CONSTRAINT channel_group_flags_pkey PRIMARY KEY (channel, "group", flag); +CREATE INDEX planet_stats_xprank_index ON planet_stats USING btree (tick, xprank); -- --- Name: channels_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planets_alliance_id_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channels - ADD CONSTRAINT channels_name_key UNIQUE (name); +CREATE INDEX planets_alliance_id_index ON planets USING btree (alliance); -- --- Name: channels_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: scan_requests_time_not_sent_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channels - ADD CONSTRAINT channels_pkey PRIMARY KEY (id); +CREATE INDEX scan_requests_time_not_sent_index ON scan_requests USING btree ("time") WHERE (NOT sent); -- --- Name: defense_missions_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: scans_not_parsed_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY defense_missions - ADD CONSTRAINT defense_missions_pkey PRIMARY KEY (fleet); +CREATE INDEX scans_not_parsed_index ON scans USING btree (groupscan) WHERE (NOT parsed); -- --- Name: defense_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: scans_planet_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY defense_requests - ADD CONSTRAINT defense_requests_pkey PRIMARY KEY (id); +CREATE INDEX scans_planet_index ON scans USING btree (pid, type, tick); -- --- Name: dumps_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: sms_status_msgid_idx; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY dumps - ADD CONSTRAINT dumps_pkey PRIMARY KEY (tick, type, modified); +CREATE INDEX sms_status_msgid_idx ON sms USING btree (status) WHERE (msgid IS NULL); -- --- Name: fleet_scans_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: smslist_nick_key; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleet_scans - ADD CONSTRAINT fleet_scans_pkey PRIMARY KEY (id); +CREATE UNIQUE INDEX smslist_nick_key ON smslist USING btree (lower(nick)); -- --- Name: fleet_ships_num_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: users_birthday_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleet_ships - ADD CONSTRAINT fleet_ships_num_key UNIQUE (num); +CREATE INDEX users_birthday_index ON users USING btree (mmdd(birthday)) WHERE (birthday IS NOT NULL); -- --- Name: fleet_ships_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: wiki_pages_textsearch_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleet_ships - ADD CONSTRAINT fleet_ships_pkey PRIMARY KEY (id, ship); +CREATE INDEX wiki_pages_textsearch_index ON wiki_pages USING gin (textsearch); -- --- Name: fleets_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: add_call; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleets - ADD CONSTRAINT fleets_pkey PRIMARY KEY (id); +CREATE TRIGGER add_call BEFORE INSERT ON calls FOR EACH ROW EXECUTE PROCEDURE add_call(); -- --- Name: forum_access_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: add_raid; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_access - ADD CONSTRAINT forum_access_pkey PRIMARY KEY (fbid, gid); +CREATE TRIGGER add_raid BEFORE INSERT ON raids FOR EACH ROW EXECUTE PROCEDURE add_raid(); -- --- Name: forum_boards_fcid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: add_remove_member; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_boards - ADD CONSTRAINT forum_boards_fcid_key UNIQUE (fcid, board); +CREATE TRIGGER add_remove_member AFTER INSERT OR DELETE ON groupmembers FOR EACH ROW EXECUTE PROCEDURE change_member(); -- --- Name: forum_boards_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: add_user; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_boards - ADD CONSTRAINT forum_boards_pkey PRIMARY KEY (fbid); +CREATE TRIGGER add_user BEFORE INSERT ON users FOR EACH ROW EXECUTE PROCEDURE add_user(); -- --- Name: forum_categories_category_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: update_forum_post; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_categories - ADD CONSTRAINT forum_categories_category_key UNIQUE (category); +CREATE TRIGGER update_forum_post BEFORE INSERT OR UPDATE ON forum_posts FOR EACH ROW EXECUTE PROCEDURE update_forum_post(); -- --- Name: forum_categories_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: update_forum_thread_posts; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_categories - ADD CONSTRAINT forum_categories_pkey PRIMARY KEY (fcid); +CREATE TRIGGER update_forum_thread_posts AFTER INSERT OR DELETE OR UPDATE ON forum_posts FOR EACH ROW EXECUTE PROCEDURE update_forum_thread_posts(); -- --- Name: forum_posts_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: update_planet; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_posts - ADD CONSTRAINT forum_posts_pkey PRIMARY KEY (fpid); +CREATE TRIGGER update_planet AFTER UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_user_planet(); -- --- Name: forum_thread_visits_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: update_wiki_page; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_thread_visits - ADD CONSTRAINT forum_thread_visits_pkey PRIMARY KEY (uid, ftid); +CREATE TRIGGER update_wiki_page BEFORE UPDATE ON wiki_pages FOR EACH ROW EXECUTE PROCEDURE update_wiki_page(); -- --- Name: forum_threads_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: updated_claim; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_threads - ADD CONSTRAINT forum_threads_pkey PRIMARY KEY (ftid); +CREATE TRIGGER updated_claim AFTER INSERT OR DELETE OR UPDATE ON raid_claims FOR EACH ROW EXECUTE PROCEDURE updated_claim(); -- --- Name: galaxies_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: alliance_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY galaxies - ADD CONSTRAINT galaxies_pkey PRIMARY KEY (x, y, tick); +ALTER TABLE ONLY alliance_stats + ADD CONSTRAINT alliance_stats_id_fkey FOREIGN KEY (aid) REFERENCES alliances(aid); -- --- Name: graphs_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: calls_dc_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY graphs - ADD CONSTRAINT graphs_pkey PRIMARY KEY (type, id); +ALTER TABLE ONLY calls + ADD CONSTRAINT calls_dc_fkey FOREIGN KEY (dc) REFERENCES users(uid) ON UPDATE SET NULL ON DELETE SET NULL; -- --- Name: groupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: calls_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY groupmembers - ADD CONSTRAINT groupmembers_pkey PRIMARY KEY (gid, uid); +ALTER TABLE ONLY calls + ADD CONSTRAINT calls_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid); -- --- Name: groups_groupname_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: calls_member_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY groups - ADD CONSTRAINT groups_groupname_key UNIQUE (groupname); +ALTER TABLE ONLY calls + ADD CONSTRAINT calls_member_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: groups_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: calls_status_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY groups - ADD CONSTRAINT groups_pkey PRIMARY KEY (gid); +ALTER TABLE ONLY calls + ADD CONSTRAINT calls_status_fkey FOREIGN KEY (status) REFERENCES call_statuses(status); -- --- Name: incomings_call_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: channel_group_flags_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY incomings - ADD CONSTRAINT incomings_call_key UNIQUE (call, sender, fleet); +ALTER TABLE ONLY channel_group_flags + ADD CONSTRAINT channel_group_flags_channel_fkey FOREIGN KEY (channel) REFERENCES channels(channel) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: incomings_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: channel_group_flags_flag_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY incomings - ADD CONSTRAINT incomings_pkey PRIMARY KEY (id); +ALTER TABLE ONLY channel_group_flags + ADD CONSTRAINT channel_group_flags_flag_fkey FOREIGN KEY (flag) REFERENCES channel_flags(flag) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: misc_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: channel_group_flags_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY misc - ADD CONSTRAINT misc_pkey PRIMARY KEY (id); +ALTER TABLE ONLY channel_group_flags + ADD CONSTRAINT channel_group_flags_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_data_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: covop_attacks_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planet_data - ADD CONSTRAINT planet_data_pkey PRIMARY KEY (rid, scan); +ALTER TABLE ONLY covop_attacks + ADD CONSTRAINT covop_attacks_id_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: planet_data_types_category_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: covop_attacks_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planet_data_types - ADD CONSTRAINT planet_data_types_category_key UNIQUE (category, name); +ALTER TABLE ONLY covop_attacks + ADD CONSTRAINT covop_attacks_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: planet_data_types_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: defense_missions_call_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planet_data_types - ADD CONSTRAINT planet_data_types_pkey PRIMARY KEY (id); +ALTER TABLE ONLY defense_missions + ADD CONSTRAINT defense_missions_call_fkey FOREIGN KEY (call) REFERENCES calls(call) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_stats_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: defense_missions_fleet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planet_stats - ADD CONSTRAINT planet_stats_id_key UNIQUE (id, tick); +ALTER TABLE ONLY defense_missions + ADD CONSTRAINT defense_missions_fleet_fkey FOREIGN KEY (fleet) REFERENCES fleets(fid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: development_scans_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planet_stats - ADD CONSTRAINT planet_stats_pkey PRIMARY KEY (tick, x, y, z); +ALTER TABLE ONLY development_scans + ADD CONSTRAINT development_scans_id_fkey FOREIGN KEY (id) REFERENCES scans(id); -- --- Name: planets_ftid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: development_scans_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planets - ADD CONSTRAINT planets_ftid_key UNIQUE (ftid); +ALTER TABLE ONLY development_scans + ADD CONSTRAINT development_scans_planet_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: planets_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: email_change_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planets - ADD CONSTRAINT planets_pkey PRIMARY KEY (id); +ALTER TABLE ONLY email_change + ADD CONSTRAINT email_change_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: planets_ruler_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleet_scans_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planets - ADD CONSTRAINT planets_ruler_key UNIQUE (ruler, planet); +ALTER TABLE ONLY fleet_scans + ADD CONSTRAINT fleet_scans_id_fkey FOREIGN KEY (fid) REFERENCES fleets(fid) ON DELETE CASCADE; -- --- Name: raid_access_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleet_scans_scan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raid_access - ADD CONSTRAINT raid_access_pkey PRIMARY KEY (raid, gid); +ALTER TABLE ONLY fleet_scans + ADD CONSTRAINT fleet_scans_scan_fkey FOREIGN KEY (id) REFERENCES scans(id); -- --- Name: raid_claims_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleet_ships_fleet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raid_claims - ADD CONSTRAINT raid_claims_pkey PRIMARY KEY (target, uid, wave); +ALTER TABLE ONLY fleet_ships + ADD CONSTRAINT fleet_ships_fleet_fkey FOREIGN KEY (fid) REFERENCES fleets(fid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: raid_targets_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleet_ships_ship_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raid_targets - ADD CONSTRAINT raid_targets_pkey PRIMARY KEY (id); +ALTER TABLE ONLY fleet_ships + ADD CONSTRAINT fleet_ships_ship_fkey FOREIGN KEY (ship) REFERENCES ship_stats(ship); -- --- Name: raid_targets_raid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleets_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raid_targets - ADD CONSTRAINT raid_targets_raid_key UNIQUE (raid, planet); +ALTER TABLE ONLY fleets + ADD CONSTRAINT fleets_sender_fkey FOREIGN KEY (pid) REFERENCES planets(pid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: raids_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_access_fbid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raids - ADD CONSTRAINT raids_pkey PRIMARY KEY (id); +ALTER TABLE ONLY forum_access + ADD CONSTRAINT forum_access_fbid_fkey FOREIGN KEY (fbid) REFERENCES forum_boards(fbid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: scans_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_access_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY scans - ADD CONSTRAINT scans_pkey PRIMARY KEY (id); +ALTER TABLE ONLY forum_access + ADD CONSTRAINT forum_access_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: scans_scan_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_boards_fcid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY scans - ADD CONSTRAINT scans_scan_id_key UNIQUE (scan_id, tick, groupscan); +ALTER TABLE ONLY forum_boards + ADD CONSTRAINT forum_boards_fcid_fkey FOREIGN KEY (fcid) REFERENCES forum_categories(fcid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: ship_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_posts_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY ship_stats - ADD CONSTRAINT ship_stats_pkey PRIMARY KEY (name); +ALTER TABLE ONLY forum_posts + ADD CONSTRAINT forum_posts_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: smslist_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_posts_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY smslist - ADD CONSTRAINT smslist_pkey PRIMARY KEY (sms); +ALTER TABLE ONLY forum_posts + ADD CONSTRAINT forum_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: users_planet_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_priv_access_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY users - ADD CONSTRAINT users_planet_key UNIQUE (planet); +ALTER TABLE ONLY forum_priv_access + ADD CONSTRAINT forum_priv_access_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON DELETE CASCADE; -- --- Name: users_tfid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_priv_access_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY users - ADD CONSTRAINT users_tfid_key UNIQUE (ftid); +ALTER TABLE ONLY forum_priv_access + ADD CONSTRAINT forum_priv_access_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: fleets_ingal_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: forum_thread_visits_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX fleets_ingal_index ON fleets USING btree (ingal); +ALTER TABLE ONLY forum_thread_visits + ADD CONSTRAINT forum_thread_visits_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: fleets_mission_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: forum_thread_visits_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX fleets_mission_index ON fleets USING btree (mission); +ALTER TABLE ONLY forum_thread_visits + ADD CONSTRAINT forum_thread_visits_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: fleets_sender_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: forum_threads_fbid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX fleets_sender_index ON fleets USING btree (sender); +ALTER TABLE ONLY forum_threads + ADD CONSTRAINT forum_threads_fbid_fkey FOREIGN KEY (fbid) REFERENCES forum_boards(fbid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: fleets_target_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: forum_threads_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX fleets_target_index ON fleets USING btree (target); +ALTER TABLE ONLY forum_threads + ADD CONSTRAINT forum_threads_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE RESTRICT; -- --- Name: fleets_tick_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: full_fleets_fid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX fleets_tick_index ON fleets USING btree (tick); +ALTER TABLE ONLY full_fleets + ADD CONSTRAINT full_fleets_fid_fkey FOREIGN KEY (fid) REFERENCES fleets(fid); -- --- Name: forum_access_gid_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: full_fleets_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX forum_access_gid_index ON forum_access USING btree (gid); +ALTER TABLE ONLY full_fleets + ADD CONSTRAINT full_fleets_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: forum_posts_ftid_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: group_roles_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX forum_posts_ftid_index ON forum_posts USING btree (ftid); +ALTER TABLE ONLY group_roles + ADD CONSTRAINT group_roles_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: forum_posts_textsearch_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: group_roles_role_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX forum_posts_textsearch_index ON forum_posts USING gin (textsearch); +ALTER TABLE ONLY group_roles + ADD CONSTRAINT group_roles_role_fkey FOREIGN KEY (role) REFERENCES roles(role); -- --- Name: forum_posts_time_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: groupmembers_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX forum_posts_time_index ON forum_posts USING btree ("time"); +ALTER TABLE ONLY groupmembers + ADD CONSTRAINT groupmembers_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: groupmembers_uid_key; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: groupmembers_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX groupmembers_uid_key ON groupmembers USING btree (uid); +ALTER TABLE ONLY groupmembers + ADD CONSTRAINT groupmembers_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_data_id_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: incomings_call_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_data_id_index ON planet_data USING btree (id); +ALTER TABLE ONLY incomings + ADD CONSTRAINT incomings_call_fkey FOREIGN KEY (call) REFERENCES calls(call) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_stats_score_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: incomings_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_score_index ON planet_stats USING btree (tick, score); +ALTER TABLE ONLY incomings + ADD CONSTRAINT incomings_sender_fkey FOREIGN KEY (pid) REFERENCES planets(pid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_stats_scorerank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: intel_scans_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_scorerank_index ON planet_stats USING btree (tick, scorerank); +ALTER TABLE ONLY intel_scans + ADD CONSTRAINT intel_scans_id_fkey FOREIGN KEY (id) REFERENCES scans(id); -- --- Name: planet_stats_size_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: intel_scans_intel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_size_index ON planet_stats USING btree (tick, size); +ALTER TABLE ONLY intel_scans + ADD CONSTRAINT intel_scans_intel_fkey FOREIGN KEY (intel) REFERENCES intel(id) ON DELETE CASCADE; -- --- Name: planet_stats_sizerank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: intel_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_sizerank_index ON planet_stats USING btree (tick, sizerank); +ALTER TABLE ONLY intel + ADD CONSTRAINT intel_sender_fkey FOREIGN KEY (sender) REFERENCES planets(pid); -- --- Name: planet_stats_value_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: intel_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_value_index ON planet_stats USING btree (tick, value); +ALTER TABLE ONLY intel + ADD CONSTRAINT intel_target_fkey FOREIGN KEY (target) REFERENCES planets(pid); -- --- Name: planet_stats_valuerank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: intel_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_valuerank_index ON planet_stats USING btree (tick, valuerank); +ALTER TABLE ONLY intel + ADD CONSTRAINT intel_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: planet_stats_xprank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: irc_requests_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_xprank_index ON planet_stats USING btree (tick, xprank); +ALTER TABLE ONLY irc_requests + ADD CONSTRAINT irc_requests_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: planets_alliance_id_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: launch_confirmations_fid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planets_alliance_id_index ON planets USING btree (alliance_id); +ALTER TABLE ONLY launch_confirmations + ADD CONSTRAINT launch_confirmations_fid_fkey FOREIGN KEY (fid) REFERENCES fleets(fid); -- --- Name: planets_nick_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: launch_confirmations_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planets_nick_index ON planets USING btree (nick); +ALTER TABLE ONLY launch_confirmations + ADD CONSTRAINT launch_confirmations_target_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: raid_targets_modified_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: launch_confirmations_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX raid_targets_modified_index ON raid_targets USING btree (modified); +ALTER TABLE ONLY launch_confirmations + ADD CONSTRAINT launch_confirmations_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: scans_parsed_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: planet_scans_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX scans_parsed_index ON scans USING btree (parsed); +ALTER TABLE ONLY planet_scans + ADD CONSTRAINT planet_scans_id_fkey FOREIGN KEY (id) REFERENCES scans(id); -- --- Name: smslist_nick_key; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: planet_scans_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE UNIQUE INDEX smslist_nick_key ON smslist USING btree (lower(nick)); +ALTER TABLE ONLY planet_scans + ADD CONSTRAINT planet_scans_planet_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: users_hostmask_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: planet_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE UNIQUE INDEX users_hostmask_index ON users USING btree (lower(hostmask)); +ALTER TABLE ONLY planet_stats + ADD CONSTRAINT planet_stats_id_fkey FOREIGN KEY (pid) REFERENCES planets(pid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: users_pnick_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: planet_tags_pid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE UNIQUE INDEX users_pnick_index ON users USING btree (lower(pnick)); +ALTER TABLE ONLY planet_tags + ADD CONSTRAINT planet_tags_pid_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: users_username_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: planet_tags_tag_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE UNIQUE INDEX users_username_index ON users USING btree (lower(username)); +ALTER TABLE ONLY planet_tags + ADD CONSTRAINT planet_tags_tag_fkey FOREIGN KEY (tag) REFERENCES available_planet_tags(tag); -- --- Name: add_call; Type: TRIGGER; Schema: public; Owner: - +-- Name: planet_tags_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE TRIGGER add_call - BEFORE INSERT ON calls - FOR EACH ROW - EXECUTE PROCEDURE add_call(); +ALTER TABLE ONLY planet_tags + ADD CONSTRAINT planet_tags_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: add_remove_member; Type: TRIGGER; Schema: public; Owner: - +-- Name: planets_alliance_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE TRIGGER add_remove_member - AFTER INSERT OR DELETE ON groupmembers - FOR EACH ROW - EXECUTE PROCEDURE change_member(); +ALTER TABLE ONLY planets + ADD CONSTRAINT planets_alliance_fkey FOREIGN KEY (alliance) REFERENCES alliances(alliance) ON UPDATE CASCADE ON DELETE SET NULL; -- --- Name: add_user; Type: TRIGGER; Schema: public; Owner: - +-- Name: planets_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE TRIGGER add_user - BEFORE INSERT ON users - FOR EACH ROW - EXECUTE PROCEDURE add_user(); +ALTER TABLE ONLY planets + ADD CONSTRAINT planets_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON UPDATE RESTRICT ON DELETE RESTRICT; -- --- Name: unclaim_target; Type: TRIGGER; Schema: public; Owner: - +-- Name: raid_access_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE TRIGGER unclaim_target - AFTER DELETE ON raid_claims - FOR EACH ROW - EXECUTE PROCEDURE unclaim_target(); +ALTER TABLE ONLY raid_access + ADD CONSTRAINT raid_access_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: update_forum_post; Type: TRIGGER; Schema: public; Owner: - +-- Name: raid_access_raid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE TRIGGER update_forum_post - BEFORE INSERT OR UPDATE ON forum_posts - FOR EACH ROW - EXECUTE PROCEDURE update_forum_post(); +ALTER TABLE ONLY raid_access + ADD CONSTRAINT raid_access_raid_fkey FOREIGN KEY (raid) REFERENCES raids(id) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: update_planet; Type: TRIGGER; Schema: public; Owner: - +-- Name: raid_claims_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE TRIGGER update_planet - AFTER UPDATE ON users - FOR EACH ROW - EXECUTE PROCEDURE update_user_planet(); +ALTER TABLE ONLY raid_claims + ADD CONSTRAINT raid_claims_target_fkey FOREIGN KEY (target) REFERENCES raid_targets(id) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: update_target; Type: TRIGGER; Schema: public; Owner: - +-- Name: raid_claims_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE TRIGGER update_target - AFTER INSERT OR DELETE OR UPDATE ON raid_claims - FOR EACH ROW - EXECUTE PROCEDURE updated_target(); +ALTER TABLE ONLY raid_claims + ADD CONSTRAINT raid_claims_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: alliance_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: raid_targets_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY alliance_stats - ADD CONSTRAINT alliance_stats_id_fkey FOREIGN KEY (id) REFERENCES alliances(id); +ALTER TABLE ONLY raid_targets + ADD CONSTRAINT raid_targets_planet_fkey FOREIGN KEY (pid) REFERENCES planets(pid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: calls_dc_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: raid_targets_raid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY calls - ADD CONSTRAINT calls_dc_fkey FOREIGN KEY (dc) REFERENCES users(uid) ON UPDATE SET NULL ON DELETE SET NULL; +ALTER TABLE ONLY raid_targets + ADD CONSTRAINT raid_targets_raid_fkey FOREIGN KEY (raid) REFERENCES raids(id) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: calls_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: scan_requests_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY calls - ADD CONSTRAINT calls_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid); +ALTER TABLE ONLY scan_requests + ADD CONSTRAINT scan_requests_planet_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: calls_member_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: scan_requests_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY calls - ADD CONSTRAINT calls_member_fkey FOREIGN KEY (member) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY scan_requests + ADD CONSTRAINT scan_requests_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: channel_group_flags_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: scans_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channel_group_flags - ADD CONSTRAINT channel_group_flags_channel_fkey FOREIGN KEY (channel) REFERENCES channels(id) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY scans + ADD CONSTRAINT scans_planet_fkey FOREIGN KEY (pid) REFERENCES planets(pid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: channel_group_flags_flag_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: scans_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channel_group_flags - ADD CONSTRAINT channel_group_flags_flag_fkey FOREIGN KEY (flag) REFERENCES channel_flags(id) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY scans + ADD CONSTRAINT scans_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE RESTRICT ON DELETE RESTRICT; -- --- Name: channel_group_flags_group_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: session_log_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channel_group_flags - ADD CONSTRAINT channel_group_flags_group_fkey FOREIGN KEY ("group") REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY session_log + ADD CONSTRAINT session_log_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: defense_missions_call_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: sms_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY defense_missions - ADD CONSTRAINT defense_missions_call_fkey FOREIGN KEY (call) REFERENCES calls(id) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY sms + ADD CONSTRAINT sms_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: defense_missions_fleet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: users_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY defense_missions - ADD CONSTRAINT defense_missions_fleet_fkey FOREIGN KEY (fleet) REFERENCES fleets(id) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY users + ADD CONSTRAINT users_planet_fkey FOREIGN KEY (pid) REFERENCES planets(pid) ON UPDATE SET NULL ON DELETE SET NULL; -- --- Name: defense_requests_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: users_tfid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY defense_requests - ADD CONSTRAINT defense_requests_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY users + ADD CONSTRAINT users_tfid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON UPDATE SET NULL ON DELETE SET NULL; -- --- Name: fleet_scans_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: wiki_namespace_access_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleet_scans - ADD CONSTRAINT fleet_scans_id_fkey FOREIGN KEY (id) REFERENCES fleets(id) ON DELETE CASCADE; +ALTER TABLE ONLY wiki_namespace_access + ADD CONSTRAINT wiki_namespace_access_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: fleet_scans_scan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: wiki_namespace_access_namespace_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleet_scans - ADD CONSTRAINT fleet_scans_scan_fkey FOREIGN KEY (scan) REFERENCES scans(id); +ALTER TABLE ONLY wiki_namespace_access + ADD CONSTRAINT wiki_namespace_access_namespace_fkey FOREIGN KEY (namespace) REFERENCES wiki_namespaces(namespace); -- --- Name: fleet_ships_fleet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: wiki_page_access_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleet_ships - ADD CONSTRAINT fleet_ships_fleet_fkey FOREIGN KEY (id) REFERENCES fleets(id) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY wiki_page_access + ADD CONSTRAINT wiki_page_access_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: fleet_ships_ship_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: wiki_page_access_wpid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleet_ships - ADD CONSTRAINT fleet_ships_ship_fkey FOREIGN KEY (ship) REFERENCES ship_stats(name); +ALTER TABLE ONLY wiki_page_access + ADD CONSTRAINT wiki_page_access_wpid_fkey FOREIGN KEY (wpid) REFERENCES wiki_pages(wpid); -- --- Name: fleets_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: wiki_page_revisions_parent_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleets - ADD CONSTRAINT fleets_sender_fkey FOREIGN KEY (sender) REFERENCES planets(id) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY wiki_page_revisions + ADD CONSTRAINT wiki_page_revisions_parent_fkey FOREIGN KEY (parent) REFERENCES wiki_page_revisions(wprev); -- --- Name: fleets_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: wiki_page_revisions_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleets - ADD CONSTRAINT fleets_target_fkey FOREIGN KEY (target) REFERENCES planets(id) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY wiki_page_revisions + ADD CONSTRAINT wiki_page_revisions_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: fleets_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: wiki_page_revisions_wpid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleets - ADD CONSTRAINT fleets_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY wiki_page_revisions + ADD CONSTRAINT wiki_page_revisions_wpid_fkey FOREIGN KEY (wpid) REFERENCES wiki_pages(wpid); -- --- Name: forum_access_fbid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: wiki_pages_namespace_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_access - ADD CONSTRAINT forum_access_fbid_fkey FOREIGN KEY (fbid) REFERENCES forum_boards(fbid) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY wiki_pages + ADD CONSTRAINT wiki_pages_namespace_fkey FOREIGN KEY (namespace) REFERENCES wiki_namespaces(namespace); -- --- Name: forum_access_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: wiki_pages_wprev_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_access - ADD CONSTRAINT forum_access_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY wiki_pages + ADD CONSTRAINT wiki_pages_wprev_fkey FOREIGN KEY (wprev) REFERENCES wiki_page_revisions(wprev); -- --- Name: forum_boards_fcid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: public; Type: ACL; Schema: -; Owner: postgres -- -ALTER TABLE ONLY forum_boards - ADD CONSTRAINT forum_boards_fcid_fkey FOREIGN KEY (fcid) REFERENCES forum_categories(fcid) ON UPDATE CASCADE ON DELETE CASCADE; +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 PUBLIC; -- --- Name: forum_posts_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: coords(integer, integer, integer); Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_posts - ADD CONSTRAINT forum_posts_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON FUNCTION coords(x integer, y integer, z integer) FROM PUBLIC; +REVOKE ALL ON FUNCTION coords(x integer, y integer, z integer) FROM ndawn; +GRANT ALL ON FUNCTION coords(x integer, y integer, z integer) TO ndawn; +GRANT ALL ON FUNCTION coords(x integer, y integer, z integer) TO PUBLIC; +GRANT ALL ON FUNCTION coords(x integer, y integer, z integer) TO intel; -- --- Name: forum_posts_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: endtick(); Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_posts - ADD CONSTRAINT forum_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON FUNCTION endtick() FROM PUBLIC; +REVOKE ALL ON FUNCTION endtick() FROM ndawn; +GRANT ALL ON FUNCTION endtick() TO ndawn; +GRANT ALL ON FUNCTION endtick() TO PUBLIC; +GRANT ALL ON FUNCTION endtick() TO intel; -- --- Name: forum_thread_visits_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: planetcoords(integer, integer); Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_thread_visits - ADD CONSTRAINT forum_thread_visits_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON FUNCTION planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) FROM PUBLIC; +REVOKE ALL ON FUNCTION planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) FROM ndawn; +GRANT ALL ON FUNCTION planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) TO ndawn; +GRANT ALL ON FUNCTION planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) TO PUBLIC; +GRANT ALL ON FUNCTION planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) TO intel; -- --- Name: forum_thread_visits_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: planetid(integer, integer, integer, integer); Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_thread_visits - ADD CONSTRAINT forum_thread_visits_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON FUNCTION planetid(x integer, y integer, z integer, tick integer) FROM PUBLIC; +REVOKE ALL ON FUNCTION planetid(x integer, y integer, z integer, tick integer) FROM ndawn; +GRANT ALL ON FUNCTION planetid(x integer, y integer, z integer, tick integer) TO ndawn; +GRANT ALL ON FUNCTION planetid(x integer, y integer, z integer, tick integer) TO PUBLIC; +GRANT ALL ON FUNCTION planetid(x integer, y integer, z integer, tick integer) TO intel; -- --- Name: forum_threads_fbid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: tick(); Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_threads - ADD CONSTRAINT forum_threads_fbid_fkey FOREIGN KEY (fbid) REFERENCES forum_boards(fbid) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON FUNCTION tick() FROM PUBLIC; +REVOKE ALL ON FUNCTION tick() FROM ndawn; +GRANT ALL ON FUNCTION tick() TO ndawn; +GRANT ALL ON FUNCTION tick() TO PUBLIC; +GRANT ALL ON FUNCTION tick() TO intel; -- --- Name: forum_threads_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: alliance_stats; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_threads - ADD CONSTRAINT forum_threads_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE RESTRICT; +REVOKE ALL ON TABLE alliance_stats FROM PUBLIC; +REVOKE ALL ON TABLE alliance_stats FROM ndawn; +GRANT ALL ON TABLE alliance_stats TO ndawn; +GRANT SELECT ON TABLE alliance_stats TO intel; -- --- Name: groupmembers_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: alliances; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY groupmembers - ADD CONSTRAINT groupmembers_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE alliances FROM PUBLIC; +REVOKE ALL ON TABLE alliances FROM ndawn; +GRANT ALL ON TABLE alliances TO ndawn; +GRANT SELECT ON TABLE alliances TO intel; -- --- Name: groupmembers_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: development_scans; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY groupmembers - ADD CONSTRAINT groupmembers_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE development_scans FROM PUBLIC; +REVOKE ALL ON TABLE development_scans FROM ndawn; +GRANT ALL ON TABLE development_scans TO ndawn; +GRANT SELECT ON TABLE development_scans TO intel; -- --- Name: incomings_call_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: current_development_scans; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY incomings - ADD CONSTRAINT incomings_call_fkey FOREIGN KEY (call) REFERENCES calls(id) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE current_development_scans FROM PUBLIC; +REVOKE ALL ON TABLE current_development_scans FROM ndawn; +GRANT ALL ON TABLE current_development_scans TO ndawn; +GRANT SELECT ON TABLE current_development_scans TO intel; -- --- Name: incomings_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: planet_scans; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY incomings - ADD CONSTRAINT incomings_sender_fkey FOREIGN KEY (sender) REFERENCES planets(id) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE planet_scans FROM PUBLIC; +REVOKE ALL ON TABLE planet_scans FROM ndawn; +GRANT ALL ON TABLE planet_scans TO ndawn; +GRANT SELECT ON TABLE planet_scans TO intel; -- --- Name: planet_data_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: current_planet_scans; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planet_data - ADD CONSTRAINT planet_data_id_fkey FOREIGN KEY (id) REFERENCES planets(id); +REVOKE ALL ON TABLE current_planet_scans FROM PUBLIC; +REVOKE ALL ON TABLE current_planet_scans FROM ndawn; +GRANT ALL ON TABLE current_planet_scans TO ndawn; +GRANT SELECT ON TABLE current_planet_scans TO intel; -- --- Name: planet_data_rid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: planet_stats; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planet_data - ADD CONSTRAINT planet_data_rid_fkey FOREIGN KEY (rid) REFERENCES planet_data_types(id); +REVOKE ALL ON TABLE planet_stats FROM PUBLIC; +REVOKE ALL ON TABLE planet_stats FROM ndawn; +GRANT ALL ON TABLE planet_stats TO ndawn; +GRANT SELECT ON TABLE planet_stats TO intel; -- --- Name: planet_data_scan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: planets; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planet_data - ADD CONSTRAINT planet_data_scan_fkey FOREIGN KEY (scan) REFERENCES scans(id); +REVOKE ALL ON TABLE planets FROM PUBLIC; +REVOKE ALL ON TABLE planets FROM ndawn; +GRANT ALL ON TABLE planets TO ndawn; +GRANT SELECT ON TABLE planets TO intel; -- --- Name: planet_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: current_planet_stats; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planet_stats - ADD CONSTRAINT planet_stats_id_fkey FOREIGN KEY (id) REFERENCES planets(id) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE current_planet_stats FROM PUBLIC; +REVOKE ALL ON TABLE current_planet_stats FROM ndawn; +GRANT ALL ON TABLE current_planet_stats TO ndawn; +GRANT SELECT ON TABLE current_planet_stats TO intel; -- --- Name: planets_alliance_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: alliance_resources; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planets - ADD CONSTRAINT planets_alliance_id_fkey FOREIGN KEY (alliance_id) REFERENCES alliances(id) ON UPDATE SET NULL ON DELETE SET NULL; +REVOKE ALL ON TABLE alliance_resources FROM PUBLIC; +REVOKE ALL ON TABLE alliance_resources FROM ndawn; +GRANT ALL ON TABLE alliance_resources TO ndawn; +GRANT SELECT ON TABLE alliance_resources TO intel; -- --- Name: planets_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: available_planet_tags; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planets - ADD CONSTRAINT planets_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON UPDATE RESTRICT ON DELETE RESTRICT; +REVOKE ALL ON TABLE available_planet_tags FROM PUBLIC; +REVOKE ALL ON TABLE available_planet_tags FROM ndawn; +GRANT ALL ON TABLE available_planet_tags TO ndawn; +GRANT SELECT ON TABLE available_planet_tags TO intel; -- --- Name: raid_access_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: fleet_ships; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raid_access - ADD CONSTRAINT raid_access_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE fleet_ships FROM PUBLIC; +REVOKE ALL ON TABLE fleet_ships FROM ndawn; +GRANT ALL ON TABLE fleet_ships TO ndawn; +GRANT SELECT ON TABLE fleet_ships TO intel; -- --- Name: raid_access_raid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: fleets; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raid_access - ADD CONSTRAINT raid_access_raid_fkey FOREIGN KEY (raid) REFERENCES raids(id) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE fleets FROM PUBLIC; +REVOKE ALL ON TABLE fleets FROM ndawn; +GRANT ALL ON TABLE fleets TO ndawn; +GRANT SELECT ON TABLE fleets TO intel; -- --- Name: raid_claims_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: launch_confirmations; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raid_claims - ADD CONSTRAINT raid_claims_target_fkey FOREIGN KEY (target) REFERENCES raid_targets(id) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE launch_confirmations FROM PUBLIC; +REVOKE ALL ON TABLE launch_confirmations FROM ndawn; +GRANT ALL ON TABLE launch_confirmations TO ndawn; +GRANT SELECT ON TABLE launch_confirmations TO intel; -- --- Name: raid_claims_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: current_planet_stats_full; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raid_claims - ADD CONSTRAINT raid_claims_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE current_planet_stats_full FROM PUBLIC; +REVOKE ALL ON TABLE current_planet_stats_full FROM ndawn; +GRANT ALL ON TABLE current_planet_stats_full TO ndawn; +GRANT SELECT ON TABLE current_planet_stats_full TO intel; -- --- Name: raid_targets_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: fleet_scans; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raid_targets - ADD CONSTRAINT raid_targets_planet_fkey FOREIGN KEY (planet) REFERENCES planets(id) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE fleet_scans FROM PUBLIC; +REVOKE ALL ON TABLE fleet_scans FROM ndawn; +GRANT ALL ON TABLE fleet_scans TO ndawn; +GRANT SELECT ON TABLE fleet_scans TO intel; -- --- Name: raid_targets_raid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: intel; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raid_targets - ADD CONSTRAINT raid_targets_raid_fkey FOREIGN KEY (raid) REFERENCES raids(id) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE intel FROM PUBLIC; +REVOKE ALL ON TABLE intel FROM ndawn; +GRANT ALL ON TABLE intel TO ndawn; +GRANT SELECT ON TABLE intel TO intel; -- --- Name: scans_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: full_intel; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY scans - ADD CONSTRAINT scans_planet_fkey FOREIGN KEY (planet) REFERENCES planets(id) ON UPDATE CASCADE ON DELETE CASCADE; +REVOKE ALL ON TABLE full_intel FROM PUBLIC; +REVOKE ALL ON TABLE full_intel FROM ndawn; +GRANT ALL ON TABLE full_intel TO ndawn; +GRANT SELECT ON TABLE full_intel TO intel; -- --- Name: scans_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: galaxies; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY scans - ADD CONSTRAINT scans_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE RESTRICT ON DELETE RESTRICT; +REVOKE ALL ON TABLE galaxies FROM PUBLIC; +REVOKE ALL ON TABLE galaxies FROM ndawn; +GRANT ALL ON TABLE galaxies TO ndawn; +GRANT SELECT ON TABLE galaxies TO intel; -- --- Name: users_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: intel_scans; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY users - ADD CONSTRAINT users_planet_fkey FOREIGN KEY (planet) REFERENCES planets(id) ON UPDATE SET NULL ON DELETE SET NULL; +REVOKE ALL ON TABLE intel_scans FROM PUBLIC; +REVOKE ALL ON TABLE intel_scans FROM ndawn; +GRANT ALL ON TABLE intel_scans TO ndawn; +GRANT SELECT ON TABLE intel_scans TO intel; -- --- Name: users_tfid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: planet_tags; Type: ACL; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY users - ADD CONSTRAINT users_tfid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON UPDATE SET NULL ON DELETE SET NULL; +REVOKE ALL ON TABLE planet_tags FROM PUBLIC; +REVOKE ALL ON TABLE planet_tags FROM ndawn; +GRANT ALL ON TABLE planet_tags TO ndawn; +GRANT SELECT ON TABLE planet_tags TO intel; -- --- Name: public; Type: ACL; Schema: -; Owner: - +-- Name: scans; Type: ACL; Schema: public; Owner: ndawn -- -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 PUBLIC; +REVOKE ALL ON TABLE scans FROM PUBLIC; +REVOKE ALL ON TABLE scans FROM ndawn; +GRANT ALL ON TABLE scans TO ndawn; +GRANT SELECT ON TABLE scans TO intel; --