+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 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 NOT NULL,
+ 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;
+
+
projects / ndwebbie.git / commitdiff