From 76887e53c27118fe04ae8fe75dce610a398b5853 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sun, 27 Mar 2016 21:39:19 +0200 Subject: [PATCH] Clean up database directory --- database/birthday.sql | 5 - database/covop.sql | 24 - .../{wiki-access.sql => data/base_data.sql} | 10 + database/{ => data}/group_roles.sql | 10 - database/defenders.sql | 2 - database/develscan.sql | 37 - database/email.sql | 7 - database/fleets.sql | 77 - database/forum.sql | 37 - database/fractional_points.sql | 5 - database/functions/calls.sql | 12 + .../covop_alert.sql} | 0 database/{ => functions}/def_leeches.sql | 0 database/{ => functions}/endtick.sql | 0 database/functions/forum_posts.sql | 19 + database/functions/groupmembers.sql | 38 + database/functions/index.sql | 2 + .../max_bank_hack.sql} | 0 .../{noperl.sql => functions/planets.sql} | 13 +- .../{claims.sql => functions/raid_claims.sql} | 11 - database/functions/raids.sql | 13 + .../unread_posts.sql} | 9 - database/functions/users.sql | 28 + database/functions/wiki_pages.sql | 16 + database/groupschannels.sql | 184 - database/ircrequest.sql | 7 - database/login.sql | 9 - database/ndawn.sql | 4654 +++++++++++------ database/planets.sql | 188 - database/planettags.sql | 11 - database/r38fleets.sql | 1 - database/r55_dev.sql | 2 - database/raids.sql | 25 - database/scan_requests.sql | 14 - database/scanid.sql | 1 - database/scans.sql | 74 - database/shipid.sql | 2 - database/shipname.sql | 1 - database/sms.sql | 23 - database/timezone.sql | 1 - database/triggers.sql | 85 - database/views/alliances_resources.sql | 32 + database/{ => views}/available_ships.sql | 0 database/views/current_planet_stats.sql | 40 + database/{calls.sql => views/defcalls.sql} | 35 - database/{intel.sql => views/full_intel.sql} | 3 +- database/views/scans.sql | 19 + .../{defprio.sql => views/users_defprio.sql} | 9 +- database/wiki.sql | 75 - 49 files changed, 3349 insertions(+), 2521 deletions(-) delete mode 100644 database/birthday.sql delete mode 100644 database/covop.sql rename database/{wiki-access.sql => data/base_data.sql} (80%) rename database/{ => data}/group_roles.sql (96%) delete mode 100644 database/defenders.sql delete mode 100644 database/develscan.sql delete mode 100644 database/email.sql delete mode 100644 database/fleets.sql delete mode 100644 database/forum.sql delete mode 100644 database/fractional_points.sql create mode 100644 database/functions/calls.sql rename database/{covopalert.sql => functions/covop_alert.sql} (100%) rename database/{ => functions}/def_leeches.sql (100%) rename database/{ => functions}/endtick.sql (100%) create mode 100644 database/functions/forum_posts.sql create mode 100644 database/functions/groupmembers.sql create mode 100644 database/functions/index.sql rename database/{covophack.sql => functions/max_bank_hack.sql} (100%) rename database/{noperl.sql => functions/planets.sql} (65%) rename database/{claims.sql => functions/raid_claims.sql} (72%) create mode 100644 database/functions/raids.sql rename database/{priv_threads.sql => functions/unread_posts.sql} (70%) create mode 100644 database/functions/users.sql create mode 100644 database/functions/wiki_pages.sql delete mode 100644 database/groupschannels.sql delete mode 100644 database/ircrequest.sql delete mode 100644 database/login.sql delete mode 100644 database/planets.sql delete mode 100644 database/planettags.sql delete mode 100644 database/r38fleets.sql delete mode 100644 database/r55_dev.sql delete mode 100644 database/raids.sql delete mode 100644 database/scan_requests.sql delete mode 100644 database/scanid.sql delete mode 100644 database/scans.sql delete mode 100644 database/shipid.sql delete mode 100644 database/shipname.sql delete mode 100644 database/sms.sql delete mode 100644 database/timezone.sql delete mode 100644 database/triggers.sql create mode 100644 database/views/alliances_resources.sql rename database/{ => views}/available_ships.sql (100%) create mode 100644 database/views/current_planet_stats.sql rename database/{calls.sql => views/defcalls.sql} (54%) rename database/{intel.sql => views/full_intel.sql} (90%) create mode 100644 database/views/scans.sql rename database/{defprio.sql => views/users_defprio.sql} (56%) delete mode 100644 database/wiki.sql diff --git a/database/birthday.sql b/database/birthday.sql deleted file mode 100644 index 33b2b1e..0000000 --- a/database/birthday.sql +++ /dev/null @@ -1,5 +0,0 @@ -ALTER TABLE users ADD birthday DATE; - -CREATE FUNCTION mmdd(d date) RETURNS text AS $SQL$ SELECT to_char($1,'MM-DD') $SQL$ LANGUAGE SQL IMMUTABLE; - -CREATE INDEX users_birthday_index ON users (mmdd(birthday)) WHERE birthday IS NOT NULL; diff --git a/database/covop.sql b/database/covop.sql deleted file mode 100644 index 67d9257..0000000 --- a/database/covop.sql +++ /dev/null @@ -1,24 +0,0 @@ -/* -INSERT INTO planet_data_types (category,name) values('planet','Agents'); -INSERT INTO planet_data_types (category,name) values('planet','Security Guards'); - */ - -DROP VIEW planet_scans; - -CREATE OR REPLACE 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 - ,h.hidden, fl.light, fm.medium, fh.heavy, a.agents, g.guards - 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) - JOIN (SELECT planet_data.scan AS id, planet_data.amount AS hidden FROM planet_data WHERE (planet_data.rid = 25)) h USING (id) - LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS light FROM planet_data WHERE (planet_data.rid = 26)) fl USING (id) - LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS medium FROM planet_data WHERE (planet_data.rid = 27)) fm USING (id) - LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS heavy FROM planet_data WHERE (planet_data.rid = 28)) fh USING (id) - LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS agents FROM planet_data WHERE (planet_data.rid = 29)) a USING (id) - LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS guards FROM planet_data WHERE (planet_data.rid = 30)) g USING (id) - ORDER BY s.planet, s.tick DESC, s.id DESC; diff --git a/database/wiki-access.sql b/database/data/base_data.sql similarity index 80% rename from database/wiki-access.sql rename to database/data/base_data.sql index ff63a0a..248d9b8 100644 --- a/database/wiki-access.sql +++ b/database/data/base_data.sql @@ -1,3 +1,12 @@ +INSERT INTO wiki_namespaces VALUES (''); +INSERT INTO wiki_namespaces VALUES ('Members'); +INSERT INTO wiki_namespaces VALUES ('HC'); +INSERT INTO wiki_namespaces VALUES ('Tech'); +INSERT INTO wiki_namespaces VALUES ('Info'); + +INSERT INTO wiki_pages (name,namespace) VALUES('Main','Info'); +INSERT INTO wiki_page_revisions (wpid,text,comment,uid) VALUES(1,'Welcome to the main page!', 'First revision', 1); + INSERT INTO wiki_namespace_access (namespace,gid,edit,post,moderate) VALUES ('',-1,FALSE,FALSE,FALSE); INSERT INTO wiki_namespace_access (namespace,gid,edit,post,moderate) VALUES ('',11,TRUE,TRUE,FALSE); INSERT INTO wiki_namespace_access (namespace,gid,edit,post,moderate) VALUES ('',1,TRUE,TRUE,TRUE); @@ -18,3 +27,4 @@ INSERT INTO wiki_namespace_access (namespace,gid,edit,post,moderate) VALUES ('Te INSERT INTO wiki_namespace_access (namespace,gid,edit,post,moderate) VALUES ('Tech',2,TRUE,TRUE,FALSE); INSERT INTO wiki_namespace_access (namespace,gid,edit,post,moderate) VALUES ('Tech',1,TRUE,TRUE,TRUE); INSERT INTO wiki_namespace_access (namespace,gid,edit,post,moderate) VALUES ('Tech',3,TRUE,TRUE,TRUE); + diff --git a/database/group_roles.sql b/database/data/group_roles.sql similarity index 96% rename from database/group_roles.sql rename to database/data/group_roles.sql index 45118ae..1496d77 100644 --- a/database/group_roles.sql +++ b/database/data/group_roles.sql @@ -1,13 +1,3 @@ -CREATE TABLE roles ( - role VARCHAR(32) UNIQUE NOT NULL -); - -CREATE TABLE group_roles ( - gid INTEGER REFERENCES groups(gid), - role VARCHAR(32) REFERENCES roles(role), - PRIMARY KEY(gid,role) -); - INSERT INTO roles VALUES('member_menu'); INSERT INTO roles VALUES('hc_menu'); INSERT INTO roles VALUES('bc_menu'); diff --git a/database/defenders.sql b/database/defenders.sql deleted file mode 100644 index 6e6d18e..0000000 --- a/database/defenders.sql +++ /dev/null @@ -1,2 +0,0 @@ -ALTER TABLE users ADD COLUMN call_if_needed BOOLEAN NOT NULL DEFAULT FALSE; -ALTER TABLE users ADD COLUMN sms_note TEXT NOT NULL DEFAULT ''; diff --git a/database/develscan.sql b/database/develscan.sql deleted file mode 100644 index 38eeaee..0000000 --- a/database/develscan.sql +++ /dev/null @@ -1,37 +0,0 @@ -DROP VIEW current_structure_scans; -DROP VIEW current_tech_scans; -DROP TABLE structure_scans; -DROP TABLE tech_scans; - -CREATE TABLE development_scans ( - id integer PRIMARY KEY REFERENCES scans(id), - planet integer NOT NULL REFERENCES planets(id), - 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 -); - -CREATE OR REPLACE VIEW current_development_scans AS -SELECT DISTINCT ON (planet) ds.* -FROM development_scans ds -ORDER BY planet, tick DESC, id DESC; - -CREATE INDEX development_scans_planet_index ON development_scans(planet,tick); diff --git a/database/email.sql b/database/email.sql deleted file mode 100644 index c1e8a84..0000000 --- a/database/email.sql +++ /dev/null @@ -1,7 +0,0 @@ -DROP TABLE IF EXISTS email_change; -CREATE TABLE email_change ( - id TEXT PRIMARY KEY DEFAULT (md5((now() + random() * interval '100 year')::text)), - uid INTEGER NOT NULL REFERENCES users(uid), - email TEXT NOT NULL, - confirmed BOOLEAN NOT NULL DEFAULT false -); diff --git a/database/fleets.sql b/database/fleets.sql deleted file mode 100644 index 549c2e9..0000000 --- a/database/fleets.sql +++ /dev/null @@ -1,77 +0,0 @@ -CREATE TABLE intel ( - id SERIAL PRIMARY KEY, - uid INTEGER NOT NULL REFERENCES users(uid), - sender INTEGER NOT NULL REFERENCES planets(id), - target INTEGER NOT NULL REFERENCES planets(id), - mission TEXT NOT NULL, - name TEXT NOT NULL, - amount INTEGER, - tick INTEGER NOT NULL, - eta INTEGER NOT NULL, - back INTEGER, - ingal BOOLEAN NOT NULL -); - -INSERT INTO intel (id,uid,sender,target,mission,name,amount,tick,eta,back,ingal) - (SELECT id,uid,sender,target,mission,name,amount,tick,eta,back,ingal FROM fleets - WHERE target IS NOT NULL); - -ALTER TABLE fleets RENAME COLUMN id TO fid; -ALTER TABLE fleet_ships RENAME COLUMN id TO fid; -ALTER TABLE fleet_scans RENAME COLUMN id TO fid; -ALTER TABLE fleet_scans RENAME COLUMN scan TO id; - -CREATE TABLE launch_confirmations ( - fid INTEGER PRIMARY KEY REFERENCES fleets(fid), - uid INTEGER NOT NULL REFERENCES users(uid), - target INTEGER NOT NULL REFERENCES planets(id), - landing_tick INTEGER NOT NULL, - eta INTEGER NOT NULL, - back INTEGER NOT NULL -); - -INSERT INTO launch_confirmations (fid,uid,target,eta,back,landing_tick) ( - SELECT fid,uid,target,eta,back,tick AS landing_tick FROM fleets - WHERE fid IN (select fid FROM fleet_ships) - AND uid <> -1 AND back IS NOT NULL AND target IS NOT NULL -); - -CREATE TABLE full_fleets ( - fid INTEGER PRIMARY KEY REFERENCES fleets(fid), - uid INTEGER NOT NULL REFERENCES users(uid) -); - -INSERT INTO full_fleets (fid,uid) ( - SELECT fid,uid FROM fleets WHERE fid IN (select fid FROM fleet_ships) - AND uid <> -1 AND mission = 'Full fleet' AND name = 'Main' -); - -ALTER TABLE fleets DROP COLUMN target; -ALTER TABLE fleets DROP COLUMN eta; -ALTER TABLE fleets DROP COLUMN back; -ALTER TABLE fleets DROP COLUMN ingal; -ALTER TABLE fleets DROP COLUMN uid; - -ALTER TABLE fleets RENAME COLUMN sender TO planet; - -CREATE TABLE intel_scans ( - id INTEGER REFERENCES scans(id), - intel INTEGER REFERENCES intel(id), - PRIMARY KEY (id,intel) -); - -INSERT INTO intel_scans (id,intel) ( - SELECT id,fid FROM fleet_scans WHERE id IN ( - SELECT id from scans where type in ('News','Jumpgate') - ) -); - -DELETE FROM fleet_scans WHERE id IN ( - SELECT id from scans where type in ('News','Jumpgate') -); - -DELETE FROM fleets WHERE fid NOT IN ( - SELECT fid FROM fleet_scans - UNION SELECT fid FROM full_fleets - UNION SELECT fid FROM launch_confirmations -); diff --git a/database/forum.sql b/database/forum.sql deleted file mode 100644 index 2cb34b7..0000000 --- a/database/forum.sql +++ /dev/null @@ -1,37 +0,0 @@ - -ALTER TABLE forum_threads ADD posts INTEGER NOT NULL DEFAULT 0; -ALTER TABLE forum_threads ADD mtime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(); -ALTER TABLE forum_threads ADD ctime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(); - - -CREATE OR REPLACE FUNCTION update_forum_thread_posts() RETURNS trigger - 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; -$$ - LANGUAGE plpgsql; - - -CREATE TRIGGER update_forum_thread_posts - AFTER INSERT OR UPDATE OR DELETE ON forum_posts - FOR EACH ROW - EXECUTE PROCEDURE update_forum_thread_posts(); - -UPDATE forum_threads ft SET posts = p.posts, mtime = p.time, ctime = p.ctime - FROM (SELECT ftid, count(fpid) AS posts, max(time) AS time, min(time) AS ctime - FROM forum_posts group by ftid) p - WHERE p.ftid = ft.ftid; - diff --git a/database/fractional_points.sql b/database/fractional_points.sql deleted file mode 100644 index 6c9b489..0000000 --- a/database/fractional_points.sql +++ /dev/null @@ -1,5 +0,0 @@ -ALTER TABLE users ALTER COLUMN defense_points TYPE NUMERIC(4,1); -ALTER TABLE users ALTER COLUMN attack_points TYPE NUMERIC(3,0); -UPDATE users set humor_points = -100 where humor_points < -100; -ALTER TABLE users ALTER COLUMN humor_points TYPE NUMERIC(3,0); -ALTER TABLE users ALTER COLUMN scan_points TYPE NUMERIC(5,0); diff --git a/database/functions/calls.sql b/database/functions/calls.sql new file mode 100644 index 0000000..69821be --- /dev/null +++ b/database/functions/calls.sql @@ -0,0 +1,12 @@ +CREATE OR REPLACE FUNCTION add_call() RETURNS trigger + AS $_X$ +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; +$_X$ LANGUAGE plpgsql; diff --git a/database/covopalert.sql b/database/functions/covop_alert.sql similarity index 100% rename from database/covopalert.sql rename to database/functions/covop_alert.sql diff --git a/database/def_leeches.sql b/database/functions/def_leeches.sql similarity index 100% rename from database/def_leeches.sql rename to database/functions/def_leeches.sql diff --git a/database/endtick.sql b/database/functions/endtick.sql similarity index 100% rename from database/endtick.sql rename to database/functions/endtick.sql diff --git a/database/functions/forum_posts.sql b/database/functions/forum_posts.sql new file mode 100644 index 0000000..1f31080 --- /dev/null +++ b/database/functions/forum_posts.sql @@ -0,0 +1,19 @@ +CREATE OR REPLACE FUNCTION update_forum_thread_posts() RETURNS trigger + 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; +$$ + LANGUAGE plpgsql; diff --git a/database/functions/groupmembers.sql b/database/functions/groupmembers.sql new file mode 100644 index 0000000..b7ce683 --- /dev/null +++ b/database/functions/groupmembers.sql @@ -0,0 +1,38 @@ +CREATE OR REPLACE FUNCTION change_member() RETURNS trigger + AS $_X$ +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; +$_X$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION update_user_planet() RETURNS trigger AS $_X$ +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; +$_X$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION groups(uid integer) RETURNS SETOF CHAR + AS $_$SELECT gid FROM groupmembers WHERE uid = $1 UNION SELECT ''$_$ + LANGUAGE sql STABLE; diff --git a/database/functions/index.sql b/database/functions/index.sql new file mode 100644 index 0000000..115a65c --- /dev/null +++ b/database/functions/index.sql @@ -0,0 +1,2 @@ +CREATE FUNCTION mmdd(d date) RETURNS text AS $SQL$ SELECT to_char($1,'MM-DD') $SQL$ LANGUAGE SQL IMMUTABLE; + diff --git a/database/covophack.sql b/database/functions/max_bank_hack.sql similarity index 100% rename from database/covophack.sql rename to database/functions/max_bank_hack.sql diff --git a/database/noperl.sql b/database/functions/planets.sql similarity index 65% rename from database/noperl.sql rename to database/functions/planets.sql index 48fcbe3..2276518 100644 --- a/database/noperl.sql +++ b/database/functions/planets.sql @@ -1,3 +1,11 @@ +CREATE OR REPLACE FUNCTION planetid(x integer, y integer, z integer, tick integer) RETURNS integer + 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$_$ + LANGUAGE sql STABLE; + +CREATE OR REPLACE 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 pid = $1 AND (tick >= $2 OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$ + LANGUAGE sql STABLE; + CREATE OR REPLACE FUNCTION find_planet_id(_ruler text, _planet text, _race race) RETURNS integer AS $_$ DECLARE @@ -22,8 +30,6 @@ END; $_$ LANGUAGE plpgsql; -DROP FUNCTION IF EXISTS findplanetid(character varying, character varying, character varying); - CREATE OR REPLACE FUNCTION find_alliance_id(alli text) RETURNS integer AS $_$ DECLARE @@ -39,11 +45,8 @@ END; $_$ LANGUAGE plpgsql; -DROP FUNCTION IF EXISTS find_alliance_id(character varying); - CREATE OR REPLACE FUNCTION coords(x integer, y integer, z integer) RETURNS text AS $_$ SELECT $1 || ':' || $2 || ':' || $3 $_$ LANGUAGE sql IMMUTABLE; - diff --git a/database/claims.sql b/database/functions/raid_claims.sql similarity index 72% rename from database/claims.sql rename to database/functions/raid_claims.sql index 3155575..2daac29 100644 --- a/database/claims.sql +++ b/database/functions/raid_claims.sql @@ -1,9 +1,3 @@ -DROP TRIGGER IF EXISTS update_target ON raid_claims; -DROP TRIGGER IF EXISTS unclaim_target ON raid_claims; - -DROP FUNCTION IF EXISTS updated_target(); -DROP FUNCTION IF EXISTS unclaim_target(); - CREATE OR REPLACE FUNCTION updated_claim() RETURNS trigger AS $_X$ DECLARE @@ -39,8 +33,3 @@ BEGIN END; $_X$ LANGUAGE plpgsql; - -CREATE TRIGGER updated_claim - AFTER INSERT OR DELETE OR UPDATE ON raid_claims - FOR EACH ROW EXECUTE PROCEDURE updated_claim(); - diff --git a/database/functions/raids.sql b/database/functions/raids.sql new file mode 100644 index 0000000..f01582d --- /dev/null +++ b/database/functions/raids.sql @@ -0,0 +1,13 @@ +CREATE OR REPLACE FUNCTION add_raid() RETURNS trigger +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; +$$ + LANGUAGE plpgsql; + diff --git a/database/priv_threads.sql b/database/functions/unread_posts.sql similarity index 70% rename from database/priv_threads.sql rename to database/functions/unread_posts.sql index 8ab114f..9c0023a 100644 --- a/database/priv_threads.sql +++ b/database/functions/unread_posts.sql @@ -1,12 +1,3 @@ -INSERT INTO forum_categories (fcid,category) VALUES(-1000, 'Private'); -INSERT INTO forum_boards (fcid,fbid,board) VALUES(-1000, -1999, 'Private'); - -CREATE TABLE forum_priv_access ( - uid INTEGER REFERENCES users(uid), - ftid INTEGER REFERENCES forum_threads(ftid), - PRIMARY KEY(uid,ftid) -); - CREATE OR REPLACE FUNCTION unread_posts(IN uid int, OUT unread int, OUT "new" int) AS $SQL$ SELECT count(*)::int AS unread diff --git a/database/functions/users.sql b/database/functions/users.sql new file mode 100644 index 0000000..2460cc1 --- /dev/null +++ b/database/functions/users.sql @@ -0,0 +1,28 @@ +CREATE OR REPLACE FUNCTION update_user_planet() RETURNS trigger AS $_X$ +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 = 2 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; +$_X$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION add_user() RETURNS trigger + AS $_X$ +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; +$_X$ LANGUAGE plpgsql; diff --git a/database/functions/wiki_pages.sql b/database/functions/wiki_pages.sql new file mode 100644 index 0000000..de5d6b6 --- /dev/null +++ b/database/functions/wiki_pages.sql @@ -0,0 +1,16 @@ +CREATE OR REPLACE FUNCTION update_wiki_page() RETURNS trigger +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; +$$ LANGUAGE plpgsql; + diff --git a/database/groupschannels.sql b/database/groupschannels.sql deleted file mode 100644 index 0a8834f..0000000 --- a/database/groupschannels.sql +++ /dev/null @@ -1,184 +0,0 @@ -DROP VIEW usersingroup; -DROP VIEW full_defcalls; -DROP VIEW defcalls; -DROP VIEW users_defprio; -DROP VIEW available_ships; -DROP VIEW ships_home; - -ALTER TABLE groups RENAME gid TO id; -ALTER TABLE groups RENAME flag TO gid; - -UPDATE groups SET gid = '' WHERE gid IS NULL; - -CREATE FUNCTION new_gid(id INTEGER) RETURNS CHAR AS $$ - SELECT gid FROM groups WHERE id = $1 -$$ LANGUAGE SQL STABLE; - -ALTER TABLE channel_flags ADD flag CHAR(1); - -UPDATE channel_flags SET flag = 'o' WHERE name = 'auto_op'; -UPDATE channel_flags SET flag = 'O' WHERE name = 'op'; -UPDATE channel_flags SET flag = 'v' WHERE name = 'auto_voice'; -UPDATE channel_flags SET flag = 'V' WHERE name = 'voice'; -UPDATE channel_flags SET flag = 'i' WHERE name = 'auto_invite'; -UPDATE channel_flags SET flag = 'I' WHERE name = 'invite'; - -CREATE FUNCTION new_flag(id INTEGER) RETURNS CHAR AS $$ - SELECT flag FROM channel_flags WHERE id = $1 -$$ LANGUAGE SQL STABLE; - -ALTER TABLE channels RENAME name TO channel; -ALTER TABLE channels ALTER channel TYPE citext; - -CREATE FUNCTION new_channel(id INTEGER) RETURNS citext AS $$ - SELECT channel FROM channels WHERE id = $1 -$$ LANGUAGE SQL STABLE; - -ALTER TABLE channel_group_flags DROP CONSTRAINT channel_group_flags_group_fkey; -ALTER TABLE channel_group_flags DROP CONSTRAINT channel_group_flags_flag_fkey; -ALTER TABLE channel_group_flags DROP CONSTRAINT channel_group_flags_channel_fkey; -ALTER TABLE group_roles DROP CONSTRAINT group_roles_gid_fkey; -ALTER TABLE groupmembers DROP CONSTRAINT groupmembers_gid_fkey; -ALTER TABLE raid_access DROP CONSTRAINT raid_access_gid_fkey; -ALTER TABLE forum_access DROP CONSTRAINT forum_access_gid_fkey; -ALTER TABLE wiki_namespace_access DROP CONSTRAINT wiki_namespace_access_gid_fkey; - -ALTER TABLE groups DROP CONSTRAINT groups_pkey; -ALTER TABLE groups ADD PRIMARY KEY(gid); - -ALTER TABLE channel_flags DROP CONSTRAINT channel_flags_pkey; -ALTER TABLE channel_flags ADD PRIMARY KEY(flag); - -ALTER TABLE channels DROP CONSTRAINT channels_pkey; -ALTER TABLE channels DROP CONSTRAINT channels_name_key; -ALTER TABLE channels ADD PRIMARY KEY(channel); - -ALTER TABLE channel_group_flags RENAME "group" TO gid; -ALTER TABLE channel_group_flags ALTER flag TYPE CHAR(1) USING (new_flag(flag)); -ALTER TABLE channel_group_flags ADD FOREIGN KEY (flag) REFERENCES channel_flags(flag) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE channel_group_flags ALTER gid TYPE CHAR(1) USING (new_gid(gid)); -ALTER TABLE channel_group_flags ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; -ALTER TABLE channel_group_flags ALTER channel TYPE citext USING (new_channel(channel)); -ALTER TABLE channel_group_flags ADD FOREIGN KEY (channel) REFERENCES channels(channel) ON UPDATE CASCADE ON DELETE CASCADE; - -ALTER TABLE groupmembers ALTER gid TYPE CHAR(1) USING (new_gid(gid)); -ALTER TABLE groupmembers ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; - -ALTER TABLE group_roles ALTER gid TYPE CHAR(1) USING (new_gid(gid)); -ALTER TABLE group_roles ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; - -ALTER TABLE raid_access ALTER gid TYPE CHAR(1) USING (new_gid(gid)); -ALTER TABLE raid_access ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; - -ALTER TABLE forum_access ALTER gid TYPE CHAR(1) USING (new_gid(gid)); -ALTER TABLE forum_access ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; - -ALTER TABLE wiki_namespace_access ALTER gid TYPE CHAR(1) USING (new_gid(gid)); -ALTER TABLE wiki_namespace_access ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; - -ALTER TABLE groups DROP id RESTRICT; -ALTER TABLE groups DROP attack RESTRICT; -ALTER TABLE channel_flags DROP id RESTRICT; -ALTER TABLE channels DROP id RESTRICT; - -DROP FUNCTION new_gid(int); -DROP FUNCTION new_flag(int); -DROP FUNCTION new_channel(int); - -DROP INDEX users_pnick_key; -DROP INDEX users_hostmask_key; -DROP INDEX users_username_key; - -ALTER TABLE users ALTER username TYPE citext; -ALTER TABLE users ALTER pnick TYPE citext; -ALTER TABLE users ALTER hostmask TYPE citext; - -ALTER TABLE users ALTER pnick SET NOT NULL; -ALTER TABLE users ALTER hostmask SET NOT NULL; - -ALTER TABLE users ADD UNIQUE(username); -ALTER TABLE users ADD UNIQUE(pnick); -ALTER TABLE users ADD UNIQUE(hostmask); - - -CREATE OR REPLACE VIEW users_defprio AS -SELECT u.*, (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 - JOIN current_planet_stats p USING (pid) - , ( - SELECT avg(u.attack_points) AS attack, avg(u.defense_points) AS defense - ,avg(p.size) AS size, avg(p.score) AS score, avg(p.value) AS value - FROM users u - JOIN current_planet_stats p USING (pid) - WHERE uid IN ( SELECT uid FROM groupmembers WHERE gid = 'M') - ) a; - -CREATE OR REPLACE VIEW defcalls AS -SELECT call, status,c.uid, c.landing_tick - ,dc.username AS dc, (c.landing_tick - tick()) AS curreta - ,array_agg(COALESCE(race::text,'')) AS race - ,array_agg(COALESCE(amount,0)) AS amount - ,array_agg(COALESCE(eta,0)) AS eta - ,array_agg(COALESCE(shiptype,'')) AS shiptype - ,array_agg(COALESCE(alliance,'?')) AS alliance - ,array_agg(coords(p2.x,p2.y,p2.z)) AS attackers -FROM calls c - LEFT OUTER JOIN incomings i USING (call) - LEFT OUTER JOIN current_planet_stats p2 USING (pid) - LEFT OUTER JOIN users dc ON c.dc = dc.uid -GROUP BY call,c.uid,dc.username, c.landing_tick, status; - -CREATE OR REPLACE VIEW full_defcalls AS -SELECT call,status,x,y,z,pid,landing_tick,dc,curreta - ,defprio, c.race, amount, c.eta, shiptype, c.alliance, 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 OUTER JOIN launch_confirmations f USING (pid,landing_tick) -GROUP BY call, x,y,z,pid,landing_tick,dc,curreta,defprio,c.race,amount,c.eta,shiptype,c.alliance,attackers, status -; - - -CREATE OR REPLACE FUNCTION change_member() RETURNS trigger - AS $_X$ -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; -$_X$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION update_user_planet() RETURNS trigger AS $_X$ -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; -$_X$ LANGUAGE plpgsql; - -DROP FUNCTION groups(int); -CREATE OR REPLACE FUNCTION groups(uid integer) RETURNS SETOF CHAR - AS $_$SELECT gid FROM groupmembers WHERE uid = $1 UNION SELECT ''$_$ - LANGUAGE sql STABLE; diff --git a/database/ircrequest.sql b/database/ircrequest.sql deleted file mode 100644 index 88abeff..0000000 --- a/database/ircrequest.sql +++ /dev/null @@ -1,7 +0,0 @@ -CREATE TABLE irc_requests ( - id SERIAL PRIMARY KEY, - channel TEXT NOT NULL, - message TEXT NOT NULL, - sent BOOL NOT NULL DEFAULT FALSE, - uid INTEGER NOT NULL REFERENCES users(uid) -); diff --git a/database/login.sql b/database/login.sql deleted file mode 100644 index 97de98c..0000000 --- a/database/login.sql +++ /dev/null @@ -1,9 +0,0 @@ -CREATE TABLE session_log ( - uid INTEGER NOT NULL REFERENCES users(uid), - time TIMESTAMP WITH TIME ZONE NOT NULL, - ip INET NOT NULL, - country CHAR(2) NOT NULL, - session TEXT NOT NULL, - remember BOOL NOT NULL, - PRIMARY KEY(uid,time,ip) -); diff --git a/database/ndawn.sql b/database/ndawn.sql index 4096f3c..40722c2 100644 --- a/database/ndawn.sql +++ b/database/ndawn.sql @@ -2,59 +2,63 @@ -- 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: plperl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: - +-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- -CREATE PROCEDURAL LANGUAGE plperl; +CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- --- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: - +-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- -CREATE PROCEDURAL LANGUAGE plpgsql; +COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; -SET search_path = public, pg_catalog; +-- +-- Name: citext; Type: EXTENSION; Schema: -; Owner: +-- -SET default_tablespace = ''; +CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public; -SET default_with_oids = false; -- --- Name: alliance_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: EXTENSION citext; Type: COMMENT; Schema: -; Owner: -- -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 -); +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: - +-- Name: ead_status; Type: TYPE; Schema: public; Owner: ndawn -- CREATE TYPE ead_status AS ENUM ( @@ -65,2788 +69,4342 @@ CREATE TYPE ead_status AS ENUM ( ); +ALTER TYPE ead_status OWNER TO ndawn; + -- --- Name: alliances; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: governments; Type: TYPE; Schema: public; Owner: ndawn -- -CREATE TABLE alliances ( - id integer NOT NULL, - name character varying NOT NULL, - relationship ead_status DEFAULT ''::ead_status NOT NULL +CREATE TYPE governments AS ENUM ( + '', + 'Feu', + 'Dic', + 'Dem', + 'Uni' ); +ALTER TYPE governments OWNER TO ndawn; + -- --- Name: calls; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: race; Type: TYPE; 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 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 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; + + -- --- Name: channel_flags; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: sms; Type: TABLE; Schema: public; Owner: ndawn -- -CREATE TABLE channel_flags ( +CREATE TABLE sms ( id integer NOT NULL, - name text 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: channel_group_flags; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: sms_id_seq; Type: SEQUENCE; Schema: public; Owner: ndawn -- -CREATE TABLE channel_group_flags ( - channel integer NOT NULL, - "group" integer NOT NULL, - flag integer NOT NULL -); +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: channels; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: sms_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ndawn -- -CREATE TABLE channels ( - id integer NOT NULL, - name text NOT NULL, - description text NOT NULL -); +ALTER SEQUENCE sms_id_seq OWNED BY sms.id; -- --- Name: covop_attacks; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: smslist; Type: TABLE; Schema: public; Owner: ndawn -- -CREATE TABLE covop_attacks ( - uid integer NOT NULL, - tick integer NOT NULL, - id integer NOT NULL +CREATE TABLE smslist ( + nick text NOT NULL, + sms text NOT NULL, + info text ); +ALTER TABLE smslist OWNER TO ndawn; + -- --- Name: governments; Type: TYPE; Schema: public; Owner: - +-- Name: table_updates; Type: VIEW; Schema: public; Owner: ndawn -- -CREATE TYPE governments AS ENUM ( - '', - 'Feu', - 'Dic', - 'Dem', - 'Uni' -); +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: race; Type: TYPE; Schema: public; Owner: - +-- Name: users_uid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn -- -CREATE TYPE race AS ENUM ( - 'Ter', - 'Cat', - 'Xan', - 'Zik', - 'Etd' -); +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: planet_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: users_uid_seq; Type: SEQUENCE OWNED BY; 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 -); +ALTER SEQUENCE users_uid_seq OWNED BY users.uid; -- --- Name: planets; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_namespace_access; Type: TABLE; Schema: public; Owner: ndawn -- -CREATE TABLE planets ( - id integer NOT NULL, - ruler character varying NOT NULL, - planet character varying NOT NULL, - race race NOT NULL, - 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 NOT NULL, - gov governments DEFAULT ''::governments NOT NULL +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: current_planet_stats; Type: VIEW; Schema: public; Owner: - +-- Name: wiki_namespaces; 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, p.gov 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_namespaces ( + namespace character varying(16) NOT NULL +); + +ALTER TABLE wiki_namespaces OWNER TO ndawn; -- --- Name: current_planet_stats_full; Type: VIEW; Schema: public; Owner: - +-- Name: wiki_page_access; Type: TABLE; 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, p.gov FROM (((SELECT planet_stats.id, planet_stats.tick, planet_stats.x, planet_stats.y, planet_stats.z, planet_stats.size, planet_stats.score, planet_stats.value, planet_stats.xp, planet_stats.sizerank, planet_stats.scorerank, planet_stats.valuerank, planet_stats.xprank, planet_stats.size_gain, planet_stats.score_gain, planet_stats.value_gain, planet_stats.xp_gain, planet_stats.sizerank_gain, planet_stats.scorerank_gain, planet_stats.valuerank_gain, planet_stats.xprank_gain, planet_stats.size_gain_day, planet_stats.score_gain_day, planet_stats.value_gain_day, planet_stats.xp_gain_day, planet_stats.sizerank_gain_day, planet_stats.scorerank_gain_day, planet_stats.valuerank_gain_day, planet_stats.xprank_gain_day FROM planet_stats WHERE (planet_stats.tick = (SELECT max(planet_stats.tick) AS max FROM planet_stats))) ps NATURAL JOIN planets p) LEFT JOIN alliances ON ((alliances.id = p.alliance_id))); +CREATE TABLE 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: defense_missions; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_page_revisions; 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 +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: defense_requests; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_page_revisions_wprev_seq; Type: SEQUENCE; 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 -); +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: dumps; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_page_revisions_wprev_seq; Type: SEQUENCE OWNED BY; 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 SEQUENCE wiki_page_revisions_wprev_seq OWNED BY wiki_page_revisions.wprev; -- --- Name: fleet_scans; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_pages; Type: TABLE; Schema: public; Owner: ndawn -- -CREATE TABLE fleet_scans ( - id integer NOT NULL, - scan integer NOT NULL +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: fleet_ships; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_pages_wpid_seq; Type: SEQUENCE; Schema: public; Owner: ndawn -- -CREATE TABLE fleet_ships ( - id integer NOT NULL, - ship text NOT NULL, - amount integer NOT NULL, - num integer NOT NULL -); +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: fleets; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wiki_pages_wpid_seq; Type: SEQUENCE OWNED BY; 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 SEQUENCE wiki_pages_wpid_seq OWNED BY wiki_pages.wpid; -- --- Name: forum_access; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: aid; 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 alliances ALTER COLUMN aid SET DEFAULT nextval('alliances_id_seq'::regclass); -- --- Name: forum_boards; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: call; 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 calls ALTER COLUMN call SET DEFAULT nextval('calls_id_seq'::regclass); -- --- Name: forum_categories; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: num; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE forum_categories ( - fcid integer NOT NULL, - category text NOT NULL -); +ALTER TABLE ONLY fleet_ships ALTER COLUMN num SET DEFAULT nextval('fleet_ships_num_seq'::regclass); -- --- Name: forum_posts; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: fid; 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 fleets ALTER COLUMN fid SET DEFAULT nextval('fleets_id_seq'::regclass); -- --- Name: forum_thread_visits; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: fbid; 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 forum_boards ALTER COLUMN fbid SET DEFAULT nextval('forum_boards_fbid_seq'::regclass); -- --- Name: forum_threads; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: fcid; 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 forum_categories ALTER COLUMN fcid SET DEFAULT nextval('forum_categories_fcid_seq'::regclass); -- --- Name: galaxies; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: fpid; 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 forum_posts ALTER COLUMN fpid SET DEFAULT nextval('forum_posts_fpid_seq'::regclass); -- --- Name: graphs; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: ftid; 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 forum_threads ALTER COLUMN ftid SET DEFAULT nextval('forum_threads_ftid_seq'::regclass); -- --- Name: groupmembers; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: inc; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE groupmembers ( - gid integer NOT NULL, - uid integer NOT NULL -); +ALTER TABLE ONLY incomings ALTER COLUMN inc SET DEFAULT nextval('incomings_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 intel ALTER COLUMN id SET DEFAULT nextval('intel_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 irc_requests ALTER COLUMN id SET DEFAULT nextval('irc_requests_id_seq'::regclass); -- --- Name: misc; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: pid; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE misc ( - id text NOT NULL, - value text -); +ALTER TABLE ONLY planets ALTER COLUMN pid SET DEFAULT nextval('planets_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 bigint NOT NULL -); +ALTER TABLE ONLY raid_targets ALTER COLUMN id SET DEFAULT nextval('raid_targets_id_seq'::regclass); -- --- Name: planet_data_types; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; 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 raids ALTER COLUMN id SET DEFAULT nextval('raids_id_seq'::regclass); -- --- Name: scans; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; 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 scan_requests ALTER COLUMN id SET DEFAULT nextval('scan_requests_id_seq'::regclass); -- --- Name: planet_scans; Type: VIEW; Schema: public; Owner: - +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE OR REPLACE 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, h.hidden, fl.light, fm.medium, fh.heavy - 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) - JOIN (SELECT planet_data.scan AS id, planet_data.amount AS hidden FROM planet_data WHERE (planet_data.rid = 25)) h USING (id) - LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS light FROM planet_data WHERE (planet_data.rid = 26)) fl USING (id) - LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS medium FROM planet_data WHERE (planet_data.rid = 27)) fm USING (id) - LEFT OUTER JOIN (SELECT planet_data.scan AS id, planet_data.amount AS heavy FROM planet_data WHERE (planet_data.rid = 28)) fh USING (id) - ORDER BY s.planet, s.tick DESC, s.id DESC; +ALTER TABLE ONLY scans ALTER COLUMN id SET DEFAULT nextval('scans_id_seq'::regclass); -- --- Name: raid_access; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn -- -CREATE TABLE raid_access ( - raid integer NOT NULL, - gid integer NOT NULL -); +ALTER TABLE ONLY ship_stats ALTER COLUMN id SET DEFAULT nextval('ship_stats_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: ndawn +-- + +ALTER TABLE ONLY sms ALTER COLUMN id SET DEFAULT nextval('sms_id_seq'::regclass); -- --- Name: raid_claims; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: uid; Type: DEFAULT; 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 users ALTER COLUMN uid SET DEFAULT nextval('users_uid_seq'::regclass); -- --- Name: raid_targets; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wprev; Type: DEFAULT; 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 wiki_page_revisions ALTER COLUMN wprev SET DEFAULT nextval('wiki_page_revisions_wprev_seq'::regclass); -- --- Name: raids; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: wpid; Type: DEFAULT; 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 wiki_pages ALTER COLUMN wpid SET DEFAULT nextval('wiki_pages_wpid_seq'::regclass); -- --- Name: ship_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: accounts_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 users + ADD CONSTRAINT accounts_pkey PRIMARY KEY (uid); -- --- Name: smslist; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: alliance_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE TABLE smslist ( - nick text NOT NULL, - sms text NOT NULL, - info text -); +ALTER TABLE ONLY alliance_stats + ADD CONSTRAINT alliance_stats_pkey PRIMARY KEY (aid, tick); -- --- Name: structure_scans; Type: VIEW; Schema: public; Owner: - +-- Name: alliances_name_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 alliances + ADD CONSTRAINT alliances_name_key UNIQUE (alliance); -- --- Name: users; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- Name: alliances_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 alliances + ADD CONSTRAINT alliances_pkey PRIMARY KEY (aid); -- --- Name: usersingroup; Type: VIEW; Schema: public; Owner: - +-- Name: available_planet_tags_pkey; 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 available_planet_tags + ADD CONSTRAINT available_planet_tags_pkey PRIMARY KEY (tag); -- --- Name: add_call(); Type: FUNCTION; Schema: public; Owner: - +-- Name: call_statuses_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 call_statuses + ADD CONSTRAINT call_statuses_pkey PRIMARY KEY (status); -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: calls_member_key; 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 calls + ADD CONSTRAINT calls_member_key UNIQUE (uid, landing_tick); -- --- Name: coords(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: - +-- Name: calls_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 calls + ADD CONSTRAINT calls_pkey PRIMARY KEY (call); -- --- Name: covop_alert(integer, integer, governments, integer); Type: FUNCTION; Schema: public; Owner: - +-- Name: channel_flags_name_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE FUNCTION covop_alert(secs integer, strucs integer, gov governments, population integer) RETURNS integer - AS $_$ - SELECT (70*(1.0+LEAST(COALESCE($1::float/$2,$4),0.30)*2 + - (CASE $3 - WHEN 'Dic' THEN 0.20 - WHEN 'Feu' THEN -0.20 - WHEN 'Uni' THEN -0.10 - ELSE 0 - END) + $4/100.0))::integer; -$_$ - LANGUAGE sql IMMUTABLE; +ALTER TABLE ONLY channel_flags + ADD CONSTRAINT channel_flags_name_key UNIQUE (name); -- --- Name: find_alliance_id(character varying); Type: FUNCTION; Schema: public; Owner: - +-- Name: channel_flags_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','race','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','race','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.0*15*$4/$5,$1*0.10, 15*7500.0)::integer - + LEAST(2000.0*15*$4/$5,$2*0.10, 15*7500.0)::integer - + LEAST(2000.0*15*$4/$5,$3*0.10, 15*7500.0)::integer -$_$ - LANGUAGE sql IMMUTABLE; +ALTER TABLE ONLY channel_flags + ADD CONSTRAINT channel_flags_pkey PRIMARY KEY (flag); -- --- Name: old_claim(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: - +-- Name: channel_group_flags_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 channel_group_flags + ADD CONSTRAINT channel_group_flags_pkey PRIMARY KEY (channel, gid, flag); -- --- Name: planetcoords(integer, integer); Type: FUNCTION; Schema: public; Owner: - +-- Name: channels_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 channels + ADD CONSTRAINT channels_pkey PRIMARY KEY (channel); -- --- Name: planetid(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: - +-- Name: clickatell_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 clickatell + ADD CONSTRAINT clickatell_pkey PRIMARY KEY (api_id, username); -- --- Name: populate_ticks(); Type: FUNCTION; Schema: public; Owner: - +-- Name: covop_attacks_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 covop_attacks + ADD CONSTRAINT covop_attacks_pkey PRIMARY KEY (pid, tick, uid); -- --- Name: tick(); Type: FUNCTION; Schema: public; Owner: - +-- Name: defense_missions_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 defense_missions + ADD CONSTRAINT defense_missions_pkey PRIMARY KEY (fleet); -- --- Name: unclaim_target(); Type: FUNCTION; Schema: public; Owner: - +-- Name: development_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 development_scans + ADD CONSTRAINT development_scans_pkey PRIMARY KEY (id); -- --- Name: update_forum_post(); Type: FUNCTION; Schema: public; Owner: - +-- Name: dumps_pkey; 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 dumps + ADD CONSTRAINT dumps_pkey PRIMARY KEY (tick, type, modified); -- --- Name: update_user_planet(); Type: FUNCTION; Schema: public; Owner: - +-- Name: email_change_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 email_change + ADD CONSTRAINT email_change_pkey PRIMARY KEY (id); -- --- Name: updated_target(); Type: FUNCTION; Schema: public; Owner: - +-- Name: fleet_scans_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 fleet_scans + ADD CONSTRAINT fleet_scans_pkey PRIMARY KEY (fid); -- --- Name: concat(text); Type: AGGREGATE; Schema: public; Owner: - +-- Name: fleet_ships_num_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE AGGREGATE concat(text) ( - SFUNC = textcat, - STYPE = text, - INITCOND = '' -); +ALTER TABLE ONLY fleet_ships + ADD CONSTRAINT fleet_ships_num_key UNIQUE (num); -- --- Name: alliances_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: fleet_ships_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE alliances_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY fleet_ships + ADD CONSTRAINT fleet_ships_pkey PRIMARY KEY (fid, ship); -- --- Name: alliances_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: fleets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE alliances_id_seq OWNED BY alliances.id; +ALTER TABLE ONLY fleets + ADD CONSTRAINT fleets_pkey PRIMARY KEY (fid); -- --- Name: calls_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: forum_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE calls_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY forum_access + ADD CONSTRAINT forum_access_pkey PRIMARY KEY (fbid, gid); -- --- Name: calls_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: forum_boards_fcid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE calls_id_seq OWNED BY calls.id; +ALTER TABLE ONLY forum_boards + ADD CONSTRAINT forum_boards_fcid_key UNIQUE (fcid, board); -- --- Name: channel_flags_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: forum_boards_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_boards + ADD CONSTRAINT forum_boards_pkey PRIMARY KEY (fbid); -- --- Name: channel_flags_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: forum_categories_category_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE channel_flags_id_seq OWNED BY channel_flags.id; +ALTER TABLE ONLY forum_categories + ADD CONSTRAINT forum_categories_category_key UNIQUE (category); -- --- Name: channels_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: forum_categories_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_categories + ADD CONSTRAINT forum_categories_pkey PRIMARY KEY (fcid); -- --- Name: channels_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: forum_posts_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE channels_id_seq OWNED BY channels.id; +ALTER TABLE ONLY forum_posts + ADD CONSTRAINT forum_posts_pkey PRIMARY KEY (fpid); -- --- Name: defense_requests_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: forum_priv_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE defense_requests_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY forum_priv_access + ADD CONSTRAINT forum_priv_access_pkey PRIMARY KEY (uid, ftid); -- --- Name: defense_requests_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: forum_thread_visits_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE defense_requests_id_seq OWNED BY defense_requests.id; +ALTER TABLE ONLY forum_thread_visits + ADD CONSTRAINT forum_thread_visits_pkey PRIMARY KEY (uid, ftid); -- --- Name: fleet_ships_num_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: forum_threads_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 forum_threads + ADD CONSTRAINT forum_threads_pkey PRIMARY KEY (ftid); -- --- Name: fleet_ships_num_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: full_fleets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE fleet_ships_num_seq OWNED BY fleet_ships.num; +ALTER TABLE ONLY full_fleets + ADD CONSTRAINT full_fleets_pkey PRIMARY KEY (fid); -- --- Name: fleets_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: galaxies_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE fleets_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY galaxies + ADD CONSTRAINT galaxies_pkey PRIMARY KEY (tick, x, y); -- --- Name: fleets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: group_roles_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE fleets_id_seq OWNED BY fleets.id; +ALTER TABLE ONLY group_roles + ADD CONSTRAINT group_roles_pkey PRIMARY KEY (gid, role); -- --- Name: forum_boards_fbid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: groupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE forum_boards_fbid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY groupmembers + ADD CONSTRAINT groupmembers_pkey PRIMARY KEY (gid, uid); -- --- Name: forum_boards_fbid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: groups_groupname_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE forum_boards_fbid_seq OWNED BY forum_boards.fbid; +ALTER TABLE ONLY groups + ADD CONSTRAINT groups_groupname_key UNIQUE (groupname); -- --- Name: forum_categories_fcid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: groups_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 groups + ADD CONSTRAINT groups_pkey PRIMARY KEY (gid); -- --- Name: forum_categories_fcid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: incomings_call_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE forum_categories_fcid_seq OWNED BY forum_categories.fcid; +ALTER TABLE ONLY incomings + ADD CONSTRAINT incomings_call_key UNIQUE (call, pid, fleet); -- --- Name: forum_posts_fpid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: incomings_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 incomings + ADD CONSTRAINT incomings_pkey PRIMARY KEY (inc); -- --- Name: forum_posts_fpid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: intel_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE forum_posts_fpid_seq OWNED BY forum_posts.fpid; +ALTER TABLE ONLY intel + ADD CONSTRAINT intel_pkey PRIMARY KEY (id); -- --- Name: forum_threads_ftid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: intel_scans_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 intel_scans + ADD CONSTRAINT intel_scans_pkey PRIMARY KEY (id, intel); -- --- Name: forum_threads_ftid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: irc_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE forum_threads_ftid_seq OWNED BY forum_threads.ftid; +ALTER TABLE ONLY irc_requests + ADD CONSTRAINT irc_requests_pkey PRIMARY KEY (id); -- --- Name: groups_gid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: last_smokes_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE groups_gid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY last_smokes + ADD CONSTRAINT last_smokes_pkey PRIMARY KEY (nick); -- --- Name: groups_gid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: launch_confirmations_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE groups_gid_seq OWNED BY groups.gid; +ALTER TABLE ONLY launch_confirmations + ADD CONSTRAINT launch_confirmations_pkey PRIMARY KEY (fid); -- --- Name: incomings_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: misc_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE incomings_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY misc + ADD CONSTRAINT misc_pkey PRIMARY KEY (id); -- --- Name: incomings_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: planet_scans_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE incomings_id_seq OWNED BY incomings.id; +ALTER TABLE ONLY planet_scans + ADD CONSTRAINT planet_scans_pkey PRIMARY KEY (id); -- --- Name: planet_data_types_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: planet_stats_id_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 planet_stats + ADD CONSTRAINT planet_stats_id_key UNIQUE (pid, tick); -- --- Name: planet_data_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: planet_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE planet_data_types_id_seq OWNED BY planet_data_types.id; +ALTER TABLE ONLY planet_stats + ADD CONSTRAINT planet_stats_pkey PRIMARY KEY (tick, x, y, z); -- --- Name: planets_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: planet_tags_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE planets_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY planet_tags + ADD CONSTRAINT planet_tags_pkey PRIMARY KEY (pid, uid, tag); -- --- Name: planets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: planets_ftid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE planets_id_seq OWNED BY planets.id; +ALTER TABLE ONLY planets + ADD CONSTRAINT planets_ftid_key UNIQUE (ftid); -- --- Name: raid_targets_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: planets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE raid_targets_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY planets + ADD CONSTRAINT planets_pkey PRIMARY KEY (pid); -- --- Name: raid_targets_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: planets_ruler_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE raid_targets_id_seq OWNED BY raid_targets.id; +ALTER TABLE ONLY planets + ADD CONSTRAINT planets_ruler_key UNIQUE (ruler, planet); -- --- Name: raids_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: raid_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE raids_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: raids_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: raid_claims_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE raids_id_seq OWNED BY raids.id; +ALTER TABLE ONLY raid_claims + ADD CONSTRAINT raid_claims_pkey PRIMARY KEY (target, uid, wave); -- --- Name: scans_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: raid_targets_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE scans_id_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY raid_targets + ADD CONSTRAINT raid_targets_pkey PRIMARY KEY (id); -- --- Name: scans_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: raid_targets_raid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE scans_id_seq OWNED BY scans.id; +ALTER TABLE ONLY raid_targets + ADD CONSTRAINT raid_targets_raid_key UNIQUE (raid, pid); -- --- Name: test; Type: SEQUENCE; Schema: public; Owner: - +-- Name: raids_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE test - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY raids + ADD CONSTRAINT raids_pkey PRIMARY KEY (id); + + +-- +-- Name: roles_role_key; Type: CONSTRAINT; Schema: public; Owner: ndawn +-- + +ALTER TABLE ONLY roles + ADD CONSTRAINT roles_role_key UNIQUE (role); -- --- Name: users_uid_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: scan_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE SEQUENCE users_uid_seq - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; +ALTER TABLE ONLY scan_requests + ADD CONSTRAINT scan_requests_pkey PRIMARY KEY (id); -- --- Name: users_uid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- Name: scan_requests_tick_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER SEQUENCE users_uid_seq OWNED BY users.uid; +ALTER TABLE ONLY scan_requests + ADD CONSTRAINT scan_requests_tick_key UNIQUE (tick, pid, type, uid); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: scans_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE alliances ALTER COLUMN id SET DEFAULT nextval('alliances_id_seq'::regclass); +ALTER TABLE ONLY scans + ADD CONSTRAINT scans_pkey PRIMARY KEY (id); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: scans_scan_id_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE calls ALTER COLUMN id SET DEFAULT nextval('calls_id_seq'::regclass); +ALTER TABLE ONLY scans + ADD CONSTRAINT scans_scan_id_key UNIQUE (scan_id, tick, groupscan); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: session_log_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE channel_flags ALTER COLUMN id SET DEFAULT nextval('channel_flags_id_seq'::regclass); +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 channels ALTER COLUMN id SET DEFAULT nextval('channels_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 defense_requests ALTER COLUMN id SET DEFAULT nextval('defense_requests_id_seq'::regclass); +ALTER TABLE ONLY ship_stats + ADD CONSTRAINT ship_stats_pkey PRIMARY KEY (ship); -- --- Name: num; Type: DEFAULT; Schema: public; Owner: - +-- Name: sms_msgid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE fleet_ships ALTER COLUMN num SET DEFAULT nextval('fleet_ships_num_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 fleets ALTER COLUMN id SET DEFAULT nextval('fleets_id_seq'::regclass); +ALTER TABLE ONLY sms + ADD CONSTRAINT sms_pkey PRIMARY KEY (id); -- --- Name: fbid; Type: DEFAULT; Schema: public; Owner: - +-- Name: smslist_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE forum_boards ALTER COLUMN fbid SET DEFAULT nextval('forum_boards_fbid_seq'::regclass); +ALTER TABLE ONLY smslist + ADD CONSTRAINT smslist_pkey PRIMARY KEY (sms); -- --- Name: fcid; Type: DEFAULT; Schema: public; Owner: - +-- Name: ticks_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE forum_categories ALTER COLUMN fcid SET DEFAULT nextval('forum_categories_fcid_seq'::regclass); +ALTER TABLE ONLY ticks + ADD CONSTRAINT ticks_pkey PRIMARY KEY (t); -- --- Name: fpid; Type: DEFAULT; Schema: public; Owner: - +-- Name: users_hostmask_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_hostmask_key UNIQUE (hostmask); -- --- Name: ftid; Type: DEFAULT; Schema: public; Owner: - +-- Name: users_planet_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_planet_key UNIQUE (pid); -- --- Name: gid; Type: DEFAULT; Schema: public; Owner: - +-- Name: users_pnick_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE groups ALTER COLUMN gid SET DEFAULT nextval('groups_gid_seq'::regclass); +ALTER TABLE ONLY users + ADD CONSTRAINT users_pnick_key UNIQUE (pnick); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: users_tfid_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE incomings ALTER COLUMN id SET DEFAULT nextval('incomings_id_seq'::regclass); +ALTER TABLE ONLY users + ADD CONSTRAINT users_tfid_key UNIQUE (ftid); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: users_username_key; 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 users + ADD CONSTRAINT users_username_key UNIQUE (username); -- --- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- Name: wiki_namespace_access_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE planets ALTER COLUMN id SET DEFAULT nextval('planets_id_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 raid_targets ALTER COLUMN id SET DEFAULT nextval('raid_targets_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 raids ALTER COLUMN id SET DEFAULT nextval('raids_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 scans ALTER COLUMN id SET DEFAULT nextval('scans_id_seq'::regclass); +ALTER TABLE ONLY wiki_page_revisions + ADD CONSTRAINT wiki_page_revisions_pkey PRIMARY KEY (wprev); -- --- Name: uid; Type: DEFAULT; Schema: public; Owner: - +-- Name: wiki_pages_namespace_key; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE users ALTER COLUMN uid SET DEFAULT nextval('users_uid_seq'::regclass); +ALTER TABLE ONLY wiki_pages + ADD CONSTRAINT wiki_pages_namespace_key UNIQUE (namespace, name); -- --- Name: accounts_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: wiki_pages_pkey; Type: CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY users - ADD CONSTRAINT accounts_pkey PRIMARY KEY (uid); +ALTER TABLE ONLY wiki_pages + ADD CONSTRAINT wiki_pages_pkey PRIMARY KEY (wpid); -- --- Name: alliance_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: development_scans_planet_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY alliance_stats - ADD CONSTRAINT alliance_stats_pkey PRIMARY KEY (id, tick); +CREATE INDEX development_scans_planet_index ON development_scans USING btree (pid, tick); -- --- Name: alliances_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleets_sender_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY alliances - ADD CONSTRAINT alliances_name_key UNIQUE (name); +CREATE INDEX fleets_sender_index ON fleets USING btree (pid); -- --- Name: alliances_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_posts_ftid_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY alliances - ADD CONSTRAINT alliances_pkey PRIMARY KEY (id); +CREATE INDEX forum_posts_ftid_index ON forum_posts USING btree (ftid); -- --- Name: calls_member_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_posts_textsearch_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY calls - ADD CONSTRAINT calls_member_key UNIQUE (member, landing_tick); +CREATE INDEX forum_posts_textsearch_index ON forum_posts USING gin (textsearch); -- --- Name: calls_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: groupmembers_uid_key; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY calls - ADD CONSTRAINT calls_pkey PRIMARY KEY (id); +CREATE INDEX groupmembers_uid_key ON groupmembers USING btree (uid); -- --- Name: channel_flags_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: intel_tick_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channel_flags - ADD CONSTRAINT channel_flags_name_key UNIQUE (name); +CREATE INDEX intel_tick_index ON intel USING btree (tick); -- --- Name: channel_flags_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planet_scans_planet_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channel_flags - ADD CONSTRAINT channel_flags_pkey PRIMARY KEY (id); +CREATE INDEX planet_scans_planet_index ON planet_scans USING btree (pid, tick); -- --- Name: channel_group_flags_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planet_stats_scorerank_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_scorerank_index ON planet_stats USING btree (tick, scorerank); -- --- Name: channels_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planet_stats_sizerank_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channels - ADD CONSTRAINT channels_name_key UNIQUE (name); +CREATE INDEX planet_stats_sizerank_index ON planet_stats USING btree (tick, sizerank); -- --- Name: channels_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planet_stats_valuerank_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY channels - ADD CONSTRAINT channels_pkey PRIMARY KEY (id); +CREATE INDEX planet_stats_valuerank_index ON planet_stats USING btree (tick, valuerank); -- --- Name: covop_attacks_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planet_stats_xprank_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY covop_attacks - ADD CONSTRAINT covop_attacks_pkey PRIMARY KEY (id, tick, uid); +CREATE INDEX planet_stats_xprank_index ON planet_stats USING btree (tick, xprank); -- --- Name: defense_missions_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: planets_alliance_id_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY defense_missions - ADD CONSTRAINT defense_missions_pkey PRIMARY KEY (fleet); +CREATE INDEX planets_alliance_id_index ON planets USING btree (alliance); -- --- Name: defense_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: scan_requests_time_not_sent_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY defense_requests - ADD CONSTRAINT defense_requests_pkey PRIMARY KEY (id); +CREATE INDEX scan_requests_time_not_sent_index ON scan_requests USING btree ("time") WHERE (NOT sent); -- --- Name: dumps_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: scans_not_parsed_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY dumps - ADD CONSTRAINT dumps_pkey PRIMARY KEY (tick, type, modified); +CREATE INDEX scans_not_parsed_index ON scans USING btree (groupscan) WHERE (NOT parsed); -- --- Name: fleet_scans_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: scans_planet_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleet_scans - ADD CONSTRAINT fleet_scans_pkey PRIMARY KEY (id); +CREATE INDEX scans_planet_index ON scans USING btree (pid, type, tick); -- --- Name: fleet_ships_num_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: sms_status_msgid_idx; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleet_ships - ADD CONSTRAINT fleet_ships_num_key UNIQUE (num); +CREATE INDEX sms_status_msgid_idx ON sms USING btree (status) WHERE (msgid IS NULL); -- --- Name: fleet_ships_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: smslist_nick_key; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleet_ships - ADD CONSTRAINT fleet_ships_pkey PRIMARY KEY (id, ship); +CREATE UNIQUE INDEX smslist_nick_key ON smslist USING btree (lower(nick)); -- --- Name: fleets_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: users_birthday_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY fleets - ADD CONSTRAINT fleets_pkey PRIMARY KEY (id); +CREATE INDEX users_birthday_index ON users USING btree (mmdd(birthday)) WHERE (birthday IS NOT NULL); -- --- Name: forum_access_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: wiki_pages_textsearch_index; Type: INDEX; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_access - ADD CONSTRAINT forum_access_pkey PRIMARY KEY (fbid, gid); +CREATE INDEX wiki_pages_textsearch_index ON wiki_pages USING gin (textsearch); -- --- Name: forum_boards_fcid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: add_call; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_boards - ADD CONSTRAINT forum_boards_fcid_key UNIQUE (fcid, board); +CREATE TRIGGER add_call BEFORE INSERT ON calls FOR EACH ROW EXECUTE PROCEDURE add_call(); -- --- Name: forum_boards_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: add_raid; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_boards - ADD CONSTRAINT forum_boards_pkey PRIMARY KEY (fbid); +CREATE TRIGGER add_raid BEFORE INSERT ON raids FOR EACH ROW EXECUTE PROCEDURE add_raid(); -- --- Name: forum_categories_category_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: add_remove_member; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_categories - ADD CONSTRAINT forum_categories_category_key UNIQUE (category); +CREATE TRIGGER add_remove_member AFTER INSERT OR DELETE ON groupmembers FOR EACH ROW EXECUTE PROCEDURE change_member(); -- --- Name: forum_categories_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: add_user; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_categories - ADD CONSTRAINT forum_categories_pkey PRIMARY KEY (fcid); +CREATE TRIGGER add_user BEFORE INSERT ON users FOR EACH ROW EXECUTE PROCEDURE add_user(); -- --- Name: forum_posts_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: update_forum_post; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_posts - ADD CONSTRAINT forum_posts_pkey PRIMARY KEY (fpid); +CREATE TRIGGER update_forum_post BEFORE INSERT OR UPDATE ON forum_posts FOR EACH ROW EXECUTE PROCEDURE update_forum_post(); -- --- Name: forum_thread_visits_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: update_forum_thread_posts; 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_forum_thread_posts AFTER INSERT OR DELETE OR UPDATE ON forum_posts FOR EACH ROW EXECUTE PROCEDURE update_forum_thread_posts(); -- --- Name: forum_threads_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: update_planet; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY forum_threads - ADD CONSTRAINT forum_threads_pkey PRIMARY KEY (ftid); +CREATE TRIGGER update_planet AFTER UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_user_planet(); -- --- Name: galaxies_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: update_wiki_page; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY galaxies - ADD CONSTRAINT galaxies_pkey PRIMARY KEY (x, y, tick); +CREATE TRIGGER update_wiki_page BEFORE UPDATE ON wiki_pages FOR EACH ROW EXECUTE PROCEDURE update_wiki_page(); -- --- Name: graphs_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: updated_claim; Type: TRIGGER; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY graphs - ADD CONSTRAINT graphs_pkey PRIMARY KEY (type, id); +CREATE TRIGGER updated_claim AFTER INSERT OR DELETE OR UPDATE ON raid_claims FOR EACH ROW EXECUTE PROCEDURE updated_claim(); -- --- Name: groupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: alliance_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY groupmembers - ADD CONSTRAINT groupmembers_pkey PRIMARY KEY (gid, uid); +ALTER TABLE ONLY alliance_stats + ADD CONSTRAINT alliance_stats_id_fkey FOREIGN KEY (aid) REFERENCES alliances(aid); -- --- Name: groups_groupname_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: calls_dc_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_dc_fkey FOREIGN KEY (dc) REFERENCES users(uid) ON UPDATE SET NULL ON DELETE SET NULL; -- --- Name: groups_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: calls_ftid_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_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid); -- --- Name: incomings_call_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: calls_member_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY incomings - ADD CONSTRAINT incomings_call_key UNIQUE (call, sender, fleet); +ALTER TABLE ONLY calls + ADD CONSTRAINT calls_member_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: incomings_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: calls_status_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY incomings - ADD CONSTRAINT incomings_pkey PRIMARY KEY (id); +ALTER TABLE ONLY calls + ADD CONSTRAINT calls_status_fkey FOREIGN KEY (status) REFERENCES call_statuses(status); -- --- Name: misc_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: channel_group_flags_channel_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_channel_fkey FOREIGN KEY (channel) REFERENCES channels(channel) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_data_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: channel_group_flags_flag_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 channel_group_flags + ADD CONSTRAINT channel_group_flags_flag_fkey FOREIGN KEY (flag) REFERENCES channel_flags(flag) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_data_types_category_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: channel_group_flags_gid_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 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_types_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: covop_attacks_id_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 covop_attacks + ADD CONSTRAINT covop_attacks_id_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: planet_stats_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: covop_attacks_uid_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 covop_attacks + ADD CONSTRAINT covop_attacks_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: planet_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: defense_missions_call_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 defense_missions + ADD CONSTRAINT defense_missions_call_fkey FOREIGN KEY (call) REFERENCES calls(call) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planets_ftid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: defense_missions_fleet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planets - ADD CONSTRAINT planets_ftid_key UNIQUE (ftid); +ALTER TABLE ONLY defense_missions + ADD CONSTRAINT defense_missions_fleet_fkey FOREIGN KEY (fleet) REFERENCES fleets(fid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planets_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: development_scans_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY planets - ADD CONSTRAINT planets_pkey PRIMARY KEY (id); +ALTER TABLE ONLY development_scans + ADD CONSTRAINT development_scans_id_fkey FOREIGN KEY (id) REFERENCES scans(id); -- --- Name: planets_ruler_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_ruler_key UNIQUE (ruler, planet); +ALTER TABLE ONLY development_scans + ADD CONSTRAINT development_scans_planet_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: raid_access_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: email_change_uid_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 email_change + ADD CONSTRAINT email_change_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: raid_claims_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleet_scans_id_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_scans + ADD CONSTRAINT fleet_scans_id_fkey FOREIGN KEY (fid) REFERENCES fleets(fid) ON DELETE CASCADE; -- --- Name: raid_targets_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleet_scans_scan_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_scans + ADD CONSTRAINT fleet_scans_scan_fkey FOREIGN KEY (id) REFERENCES scans(id); -- --- Name: raid_targets_raid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleet_ships_fleet_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 fleet_ships + ADD CONSTRAINT fleet_ships_fleet_fkey FOREIGN KEY (fid) REFERENCES fleets(fid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: raids_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleet_ships_ship_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY raids - ADD CONSTRAINT raids_pkey PRIMARY KEY (id); +ALTER TABLE ONLY fleet_ships + ADD CONSTRAINT fleet_ships_ship_fkey FOREIGN KEY (ship) REFERENCES ship_stats(ship); -- --- Name: scans_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: fleets_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY scans - ADD CONSTRAINT scans_pkey PRIMARY KEY (id); +ALTER TABLE ONLY fleets + ADD CONSTRAINT fleets_sender_fkey FOREIGN KEY (pid) REFERENCES planets(pid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: scans_scan_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_access_fbid_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_access + ADD CONSTRAINT forum_access_fbid_fkey FOREIGN KEY (fbid) REFERENCES forum_boards(fbid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: ship_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_access_gid_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_access + ADD CONSTRAINT forum_access_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: smslist_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_boards_fcid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY smslist - ADD CONSTRAINT smslist_pkey PRIMARY KEY (sms); +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: users_planet_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_posts_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY users - ADD CONSTRAINT users_planet_key UNIQUE (planet); +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: users_tfid_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- Name: forum_posts_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY users - ADD CONSTRAINT users_tfid_key UNIQUE (ftid); +ALTER TABLE ONLY forum_posts + ADD CONSTRAINT forum_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: fleets_ingal_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: forum_priv_access_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX fleets_ingal_index ON fleets USING btree (ingal); +ALTER TABLE ONLY forum_priv_access + ADD CONSTRAINT forum_priv_access_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON DELETE CASCADE; -- --- Name: fleets_mission_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: forum_priv_access_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX fleets_mission_index ON fleets USING btree (mission); +ALTER TABLE ONLY forum_priv_access + ADD CONSTRAINT forum_priv_access_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: fleets_sender_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: forum_thread_visits_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX fleets_sender_index ON fleets USING btree (sender); +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_target_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: forum_thread_visits_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX fleets_target_index ON fleets USING btree (target); +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_tick_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: forum_threads_fbid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX fleets_tick_index ON fleets USING btree (tick); +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: forum_access_gid_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: forum_threads_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX forum_access_gid_index ON forum_access USING btree (gid); +ALTER TABLE ONLY forum_threads + ADD CONSTRAINT forum_threads_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE RESTRICT; -- --- Name: forum_posts_ftid_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: full_fleets_fid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX forum_posts_ftid_index ON forum_posts USING btree (ftid); +ALTER TABLE ONLY full_fleets + ADD CONSTRAINT full_fleets_fid_fkey FOREIGN KEY (fid) REFERENCES fleets(fid); -- --- Name: forum_posts_textsearch_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: full_fleets_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX forum_posts_textsearch_index ON forum_posts USING gin (textsearch); +ALTER TABLE ONLY full_fleets + ADD CONSTRAINT full_fleets_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: forum_posts_time_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: group_roles_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX forum_posts_time_index ON forum_posts USING btree ("time"); +ALTER TABLE ONLY group_roles + ADD CONSTRAINT group_roles_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: group_roles_role_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX groupmembers_uid_key ON groupmembers USING btree (uid); +ALTER TABLE ONLY group_roles + ADD CONSTRAINT group_roles_role_fkey FOREIGN KEY (role) REFERENCES roles(role); -- --- Name: planet_data_id_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: groupmembers_gid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_data_id_index ON planet_data USING btree (id); +ALTER TABLE ONLY groupmembers + ADD CONSTRAINT groupmembers_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_stats_score_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: groupmembers_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_score_index ON planet_stats USING btree (tick, score); +ALTER TABLE ONLY groupmembers + ADD CONSTRAINT groupmembers_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_stats_scorerank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: incomings_call_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_scorerank_index ON planet_stats USING btree (tick, scorerank); +ALTER TABLE ONLY incomings + ADD CONSTRAINT incomings_call_fkey FOREIGN KEY (call) REFERENCES calls(call) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_stats_size_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: incomings_sender_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_size_index ON planet_stats USING btree (tick, size); +ALTER TABLE ONLY incomings + ADD CONSTRAINT incomings_sender_fkey FOREIGN KEY (pid) REFERENCES planets(pid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: planet_stats_sizerank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: intel_scans_id_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_scans + ADD CONSTRAINT intel_scans_id_fkey FOREIGN KEY (id) REFERENCES scans(id); -- --- Name: planet_stats_value_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: intel_scans_intel_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_scans + ADD CONSTRAINT intel_scans_intel_fkey FOREIGN KEY (intel) REFERENCES intel(id) ON DELETE CASCADE; -- --- Name: planet_stats_valuerank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: intel_sender_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_sender_fkey FOREIGN KEY (sender) REFERENCES planets(pid); -- --- Name: planet_stats_xprank_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: intel_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planet_stats_xprank_index ON planet_stats USING btree (tick, xprank); +ALTER TABLE ONLY intel + ADD CONSTRAINT intel_target_fkey FOREIGN KEY (target) REFERENCES planets(pid); -- --- Name: planets_alliance_id_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: intel_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planets_alliance_id_index ON planets USING btree (alliance_id); +ALTER TABLE ONLY intel + ADD CONSTRAINT intel_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: planets_nick_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: irc_requests_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX planets_nick_index ON planets USING btree (nick); +ALTER TABLE ONLY irc_requests + ADD CONSTRAINT irc_requests_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: raid_targets_modified_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: launch_confirmations_fid_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_fid_fkey FOREIGN KEY (fid) REFERENCES fleets(fid); -- --- Name: scans_parsed_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: launch_confirmations_target_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE INDEX scans_parsed_index ON scans USING btree (parsed); +ALTER TABLE ONLY launch_confirmations + ADD CONSTRAINT launch_confirmations_target_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: smslist_nick_key; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: launch_confirmations_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE UNIQUE INDEX smslist_nick_key ON smslist USING btree (lower(nick)); +ALTER TABLE ONLY launch_confirmations + ADD CONSTRAINT launch_confirmations_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: users_hostmask_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: planet_scans_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_scans + ADD CONSTRAINT planet_scans_id_fkey FOREIGN KEY (id) REFERENCES scans(id); -- --- Name: users_pnick_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: planet_scans_planet_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE UNIQUE INDEX users_pnick_index ON users USING btree (lower(pnick)); +ALTER TABLE ONLY planet_scans + ADD CONSTRAINT planet_scans_planet_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: users_username_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- Name: planet_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -CREATE UNIQUE INDEX users_username_index ON users USING btree (lower(username)); +ALTER TABLE ONLY planet_stats + ADD CONSTRAINT planet_stats_id_fkey FOREIGN KEY (pid) REFERENCES planets(pid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: add_call; Type: TRIGGER; Schema: public; Owner: - +-- Name: planet_tags_pid_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_pid_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: add_remove_member; Type: TRIGGER; Schema: public; Owner: - +-- Name: planet_tags_tag_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 planet_tags + ADD CONSTRAINT planet_tags_tag_fkey FOREIGN KEY (tag) REFERENCES available_planet_tags(tag); -- --- Name: add_user; Type: TRIGGER; Schema: public; Owner: - +-- Name: planet_tags_uid_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 planet_tags + ADD CONSTRAINT planet_tags_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: unclaim_target; Type: TRIGGER; Schema: public; Owner: - +-- Name: planets_alliance_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 planets + ADD CONSTRAINT planets_alliance_fkey FOREIGN KEY (alliance) REFERENCES alliances(alliance) ON UPDATE CASCADE ON DELETE SET NULL; -- --- Name: update_forum_post; Type: TRIGGER; Schema: public; Owner: - +-- Name: planets_ftid_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 planets + ADD CONSTRAINT planets_ftid_fkey FOREIGN KEY (ftid) REFERENCES forum_threads(ftid) ON UPDATE RESTRICT ON DELETE RESTRICT; -- --- Name: update_planet; Type: TRIGGER; Schema: public; Owner: - +-- Name: raid_access_gid_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_access + ADD CONSTRAINT raid_access_gid_fkey FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: update_target; Type: TRIGGER; Schema: public; Owner: - +-- Name: raid_access_raid_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_access + ADD CONSTRAINT raid_access_raid_fkey FOREIGN KEY (raid) REFERENCES raids(id) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: alliance_stats_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: raid_claims_target_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_claims + ADD CONSTRAINT raid_claims_target_fkey FOREIGN KEY (target) REFERENCES raid_targets(id) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: calls_dc_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: raid_claims_uid_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_claims + ADD CONSTRAINT raid_claims_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: calls_ftid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: raid_targets_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 raid_targets + ADD CONSTRAINT raid_targets_planet_fkey FOREIGN KEY (pid) REFERENCES planets(pid) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: calls_member_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_member_fkey FOREIGN KEY (member) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY raid_targets + ADD CONSTRAINT raid_targets_raid_fkey FOREIGN KEY (raid) REFERENCES raids(id) ON UPDATE CASCADE ON DELETE CASCADE; -- --- Name: channel_group_flags_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: scan_requests_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 scan_requests + ADD CONSTRAINT scan_requests_planet_fkey FOREIGN KEY (pid) REFERENCES planets(pid); -- --- Name: channel_group_flags_flag_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: scan_requests_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 scan_requests + ADD CONSTRAINT scan_requests_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); -- --- Name: channel_group_flags_group_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_group_fkey FOREIGN KEY ("group") REFERENCES groups(gid) 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: covop_attacks_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: scans_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY covop_attacks - ADD CONSTRAINT covop_attacks_id_fkey FOREIGN KEY (id) REFERENCES planets(id); +ALTER TABLE ONLY scans + ADD CONSTRAINT scans_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid) ON UPDATE RESTRICT ON DELETE RESTRICT; -- --- Name: covop_attacks_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: session_log_uid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ndawn -- -ALTER TABLE ONLY covop_attacks - ADD CONSTRAINT covop_attacks_uid_fkey FOREIGN KEY (uid) REFERENCES users(uid); +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; -- diff --git a/database/planets.sql b/database/planets.sql deleted file mode 100644 index 1bb5ef9..0000000 --- a/database/planets.sql +++ /dev/null @@ -1,188 +0,0 @@ -DROP VIEW users_defprio; -DROP VIEW current_planet_stats; -DROP VIEW current_planet_stats_full; -DROP VIEW current_planet_scans; -DROP VIEW current_development_scans; - - -ALTER TABLE planets ALTER ruler TYPE text; -ALTER TABLE planets ALTER planet TYPE text; -ALTER TABLE planets ALTER nick TYPE citext; -ALTER TABLE planets ALTER channel TYPE citext; -ALTER TABLE alliances RENAME name TO alliance; -ALTER TABLE alliances ALTER alliance TYPE text; -ALTER TABLE alliances RENAME id TO aid; -ALTER TABLE alliance_stats RENAME id TO aid; - -CREATE FUNCTION alliance_name(id INTEGER) RETURNS TEXT AS $$ - SELECT alliance FROM alliances WHERE aid = $1 -$$ LANGUAGE SQL STABLE; - -ALTER TABLE planets RENAME id TO pid; -ALTER TABLE planets RENAME alliance_id TO alliance; -ALTER TABLE planetS DROP CONSTRAINT planets_alliance_id_fkey; -ALTER TABLE planets ALTER alliance TYPE text USING alliance_name(alliance); -ALTER TABLE planetS ADD FOREIGN KEY (alliance) REFERENCES alliances(alliance) ON UPDATE CASCADE ON DELETE SET NULL; - -DROP FUNCTION alliance_name(INTEGER); - -ALTER TABLE planet_stats RENAME id TO pid; -ALTER TABLE users RENAME planet TO pid; -ALTER TABLE fleets RENAME planet TO pid; -ALTER TABLE launch_confirmations RENAME target TO pid; -ALTER TABLE development_scans RENAME planet TO pid; -ALTER TABLE planet_scans RENAME planet TO pid; -ALTER TABLE raid_targets RENAME planet TO pid; -ALTER TABLE scan_requests RENAME planet TO pid; -ALTER TABLE scans RENAME planet TO pid; -ALTER TABLE covop_attacks RENAME id TO pid; -ALTER TABLE incomings RENAME sender TO pid; - -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 - ,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 pid, tick, x, y, z, size, score, value, xp, sizerank, scorerank, valuerank, xprank - FROM planet_stats - WHERE tick = ( SELECT max(tick) AS max FROM planet_stats) - ) ps - NATURAL JOIN planets p - LEFT JOIN alliances a USING (alliance); - -CREATE OR REPLACE VIEW users_defprio AS -SELECT u.*, (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 - JOIN current_planet_stats p USING (pid) - , ( - SELECT avg(u.attack_points) AS attack, avg(u.defense_points) AS defense - ,avg(p.size) AS size, avg(p.score) AS score, avg(p.value) AS value - FROM users u - JOIN current_planet_stats p USING (pid) - WHERE uid IN ( SELECT uid FROM groupmembers WHERE gid = 2) - ) a; - -CREATE OR REPLACE VIEW current_planet_stats_full AS -SELECT * -FROM planets p - NATURAL JOIN ( - SELECT * - FROM planet_stats - WHERE tick = ( SELECT max(tick) AS max FROM planet_stats) - ) ps - LEFT JOIN alliances USING (alliance); - -CREATE OR REPLACE FUNCTION change_member() RETURNS trigger - AS $_X$ -BEGIN - IF TG_OP = 'INSERT' THEN - IF NEW.gid = 2 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 = 2 THEN - UPDATE planets SET alliance = NULL WHERE - pid = (SELECT pid FROM users WHERE uid = OLD.uid); - END IF; - END IF; - - return NEW; -END; -$_X$ LANGUAGE plpgsql; - -CREATE OR REPLACE VIEW current_planet_scans AS -SELECT DISTINCT ON (pid) ps.* -FROM planet_scans ps -ORDER BY pid, tick DESC, id DESC; - -CREATE OR REPLACE VIEW current_development_scans AS -SELECT DISTINCT ON (pid) ds.* -FROM development_scans ds -ORDER BY pid, tick DESC, id DESC; - -CREATE OR REPLACE FUNCTION update_user_planet() RETURNS trigger AS $_X$ -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 = 2 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; -$_X$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION planetid(x integer, y integer, z integer, tick integer) RETURNS integer - 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$_$ - LANGUAGE sql STABLE; - -CREATE OR REPLACE 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 pid = $1 AND (tick >= $2 OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$ - LANGUAGE sql STABLE; - -CREATE OR REPLACE VIEW alliance_resources AS -WITH planet_estimates AS ( - SELECT ps.tick, alliance, hidden,size,score,(metal+crystal+eonium) AS resources - ,score + (metal+crystal+eonium)/300 + hidden/100 AS nscore2 - ,score + (metal+crystal+eonium)/300 + hidden/100 + (endtick()-tick())*( - 250*size + COALESCE(metal_ref + crystal_ref + eonium_ref,7)* 1000 - + CASE extraction WHEN 0 THEN 3000 WHEN 1 THEN 11500 ELSE COALESCE(extraction,3)*3000*3 END - )*(1.35+0.005*COALESCE(fincents,20))/100 AS nscore3 - FROM current_planet_stats p - JOIN current_planet_scans ps USING (pid) - LEFT OUTER JOIN current_development_scans ds USING (pid) -), planet_ranks AS ( - SELECT *, RANK() OVER(PARTITION BY alliance ORDER BY score DESC) AS rank FROM planet_estimates -), top_planets AS ( - SELECT alliance, sum(resources) AS resources, sum(hidden) AS hidden - ,sum(nscore2)::bigint AS nscore2, sum(nscore3)::bigint AS nscore3 - ,count(*) AS planets, sum(score) AS score, sum(size) AS size - ,avg(tick)::int AS avgtick - FROM planet_ranks WHERE rank <= 60 - GROUP BY alliance -) -SELECT aid,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 + (resources / 300) + (hidden / 100))::bigint AS nscore - ,nscore2, nscore3, avgtick -FROM alliances a - JOIN top_planets r USING (alliance) - LEFT OUTER JOIN (SELECT aid,score,size,members FROM alliance_stats - WHERE tick = (SELECT max(tick) FROM alliance_stats)) s USING (aid) -; - -CREATE OR REPLACE VIEW defcalls AS -SELECT c.id,c.member AS uid, c.landing_tick, covered, open - ,dc.username AS dc, (c.landing_tick - tick()) AS curreta - ,array_agg(COALESCE(race::text,'')) AS race - ,array_agg(COALESCE(amount,0)) AS amount - ,array_agg(COALESCE(eta,0)) AS eta - ,array_agg(COALESCE(shiptype,'')) AS shiptype - ,array_agg(COALESCE(alliance,'?')) AS alliance - ,array_agg(coords(p2.x,p2.y,p2.z)) AS attackers -FROM calls c - LEFT OUTER JOIN incomings i ON c.id = i.call - LEFT OUTER JOIN current_planet_stats p2 USING (pid) - LEFT OUTER JOIN users dc ON c.dc = dc.uid -GROUP BY c.id,c.member,dc.username, c.landing_tick, covered, open; - -CREATE OR REPLACE VIEW full_defcalls AS -SELECT id,covered,open,x,y,z,pid,landing_tick,dc,curreta - ,defprio, c.race, amount, c.eta, shiptype, c.alliance, 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 OUTER JOIN launch_confirmations f USING (pid,landing_tick) -GROUP BY id, x,y,z,pid,landing_tick,dc,curreta,defprio,c.race,amount,c.eta,shiptype,c.alliance,attackers, covered, open -; - -DROP AGGREGATE array_accum(anyelement); diff --git a/database/planettags.sql b/database/planettags.sql deleted file mode 100644 index 1011fe0..0000000 --- a/database/planettags.sql +++ /dev/null @@ -1,11 +0,0 @@ -CREATE TABLE available_planet_tags ( - tag CITEXT PRIMARY KEY -); - -CREATE TABLE planet_tags ( - pid INTEGER NOT NULL REFERENCES planets (pid) - ,tag CITEXT NOT NULL REFERENCES available_planet_tags (tag) - ,uid INTEGER REFERENCES users (uid) - ,time TIMESTAMPTZ NOT NULL DEFAULT NOW() - ,PRIMARY KEY (pid,uid,tag) -); diff --git a/database/r38fleets.sql b/database/r38fleets.sql deleted file mode 100644 index 861ecb0..0000000 --- a/database/r38fleets.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE launch_confirmations ADD COLUMN num INT NOT NULL; diff --git a/database/r55_dev.sql b/database/r55_dev.sql deleted file mode 100644 index 0b8368f..0000000 --- a/database/r55_dev.sql +++ /dev/null @@ -1,2 +0,0 @@ -ALTER TABLE development_scans ADD COLUMN milcents INT NOT NULL; -ALTER TABLE development_scans ADD COLUMN structdefs INT NOT NULL; diff --git a/database/raids.sql b/database/raids.sql deleted file mode 100644 index 57de47f..0000000 --- a/database/raids.sql +++ /dev/null @@ -1,25 +0,0 @@ -INSERT INTO forum_boards (fcid,fbid,board) VALUES(7,-5,'Raid logs'); -INSERT INTO forum_access (fbid,gid) VALUES(-5,1); -INSERT INTO forum_access (fbid,gid) VALUES(-5,3); - -ALTER TABLE raids ADD COLUMN ftid INTEGER; - - -CREATE OR REPLACE FUNCTION add_raid() RETURNS trigger -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; -$$ - LANGUAGE plpgsql; - - -CREATE TRIGGER add_raid - BEFORE INSERT ON raids - FOR EACH ROW - EXECUTE PROCEDURE add_raid(); diff --git a/database/scan_requests.sql b/database/scan_requests.sql deleted file mode 100644 index 32e1b8b..0000000 --- a/database/scan_requests.sql +++ /dev/null @@ -1,14 +0,0 @@ -CREATE TABLE scan_requests ( - id SERIAL PRIMARY KEY, - uid INTEGER NOT NULL REFERENCES users(uid), - planet INTEGER NOT NULL REFERENCES planets(id), - type TEXT NOT NULL, - nick TEXT NOT NULL, - tick INTEGER NOT NULL DEFAULT tick(), - time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), - sent BOOL NOT NULL DEFAULT FALSE, - UNIQUE (tick,planet,type,uid) -); - -CREATE INDEX scan_requests_time_not_sent_index ON scan_requests(time) WHERE NOT sent; - diff --git a/database/scanid.sql b/database/scanid.sql deleted file mode 100644 index 3afb6db..0000000 --- a/database/scanid.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE scans ALTER scan_id TYPE text; diff --git a/database/scans.sql b/database/scans.sql deleted file mode 100644 index bbf0da9..0000000 --- a/database/scans.sql +++ /dev/null @@ -1,74 +0,0 @@ -DROP VIEW planet_scans; -DROP VIEW structure_scans; - -CREATE TABLE planet_scans ( - id integer PRIMARY KEY REFERENCES scans(id), - planet integer NOT NULL REFERENCES planets(id), - 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 -); - - -CREATE TABLE structure_scans ( - id integer PRIMARY KEY REFERENCES scans(id), - planet integer NOT NULL REFERENCES planets(id), - 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 -); - -CREATE TABLE tech_scans ( - id integer PRIMARY KEY REFERENCES scans(id), - planet integer NOT NULL REFERENCES planets(id), - tick 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 -); - -CREATE OR REPLACE VIEW current_planet_scans AS -SELECT DISTINCT ON (planet) ps.* -FROM planet_scans ps -ORDER BY planet, tick DESC, id DESC; - -CREATE OR REPLACE VIEW current_structure_scans AS -SELECT DISTINCT ON (planet) ss.* -FROM structure_scans ss -ORDER BY planet, tick DESC, id DESC; - -CREATE OR REPLACE VIEW current_tech_scans AS -SELECT DISTINCT ON (planet) ts.* -FROM tech_scans ts -ORDER BY planet, tick DESC, id DESC; - -CREATE INDEX planet_scans_planet_index ON planet_scans(planet,tick); -CREATE INDEX structure_scans_planet_index ON structure_scans(planet,tick); -CREATE INDEX tech_scans_planet_index ON tech_scans(planet,tick); - -DROP TABLE planet_data; -DROP TABLE planet_data_types; diff --git a/database/shipid.sql b/database/shipid.sql deleted file mode 100644 index 6b6e81e..0000000 --- a/database/shipid.sql +++ /dev/null @@ -1,2 +0,0 @@ -ALTER TABLE ship_stats ADD id SERIAL UNIQUE NOT NULL; -UPDATE ship_stats SET id = id - 3; diff --git a/database/shipname.sql b/database/shipname.sql deleted file mode 100644 index 7a6144f..0000000 --- a/database/shipname.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE ship_stats RENAME COLUMN name TO ship; diff --git a/database/sms.sql b/database/sms.sql deleted file mode 100644 index f1d866a..0000000 --- a/database/sms.sql +++ /dev/null @@ -1,23 +0,0 @@ -DROP TABLE IF EXISTS sms; -CREATE TABLE sms ( - id SERIAL PRIMARY KEY, - msgid TEXT UNIQUE, - uid INTEGER NOT NULL REFERENCES users(uid), - status TEXT NOT NULL DEFAULT 'Waiting', - number TEXT NOT NULL, - message VARCHAR(140) NOT NULL, - cost NUMERIC(4,2) NOT NULL DEFAULT 0, - time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() -); - -CREATE INDEX sms_status_msgid_idx ON sms (status) WHERE msgid IS NULL; - -DROP TABLE IF EXISTS clickatell; - -CREATE TABLE clickatell ( - api_id TEXT NOT NULL, - username TEXT NOT NULL, - password TEXT NOT NULL, - PRIMARY KEY (api_id, username) -); - diff --git a/database/timezone.sql b/database/timezone.sql deleted file mode 100644 index b196e9b..0000000 --- a/database/timezone.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE users ADD COLUMN timezone TEXT NOT NULL DEFAULT 'GMT'; diff --git a/database/triggers.sql b/database/triggers.sql deleted file mode 100644 index 9e9fd56..0000000 --- a/database/triggers.sql +++ /dev/null @@ -1,85 +0,0 @@ -/* Some generic cleanup */ - -ALTER TABLE forum_posts ALTER textsearch SET NOT NULL; - -DROP FUNCTION IF EXISTS add_intel(integer,integer,integer,integer,integer,integer,integer,integer,integer,character varying,integer); -DROP FUNCTION IF EXISTS add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying); -DROP FUNCTION IF EXISTS add_intel2(integer, integer, integer, integer, integer, integer, integer, integer, integer, character varying, character varying); -DROP FUNCTION IF EXISTS 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); -DROP FUNCTION IF EXISTS calc_rank(integer); -DROP FUNCTION IF EXISTS calc_rank3(integer); -DROP FUNCTION IF EXISTS calculate_rankings(integer); -DROP FUNCTION IF EXISTS covop_alert(integer, integer, governments, integer); -DROP FUNCTION IF EXISTS covop_alert(bigint, integer, governments, integer); -DROP FUNCTION IF EXISTS max_bank_hack(integer,integer,integer,integer,integer); -DROP FUNCTION IF EXISTS max_bank_hack(bigint,bigint,bigint,integer,integer); -DROP FUNCTION IF EXISTS populate_ticks(); - - -/* Updating old triggers */ -ALTER TABLE users DROP COLUMN last_forum_visit; - -CREATE OR REPLACE FUNCTION update_user_planet() RETURNS trigger AS $_X$ -BEGIN - IF COALESCE(NEW.planet <> OLD.planet,TRUE) OR NEW.username <> OLD.username THEN - UPDATE planets SET nick = NULL WHERE id = OLD.planet; - UPDATE planets SET nick = NEW.username WHERE id = NEW.planet; - END IF; - - IF COALESCE(NEW.planet <> OLD.planet,TRUE) - AND (SELECT TRUE FROM groupmembers WHERE gid = 2 AND uid = NEW.uid) THEN - UPDATE planets SET alliance_id = NULL WHERE id = OLD.planet; - UPDATE planets SET alliance_id = 1 WHERE id = NEW.planet; - END IF; - RETURN NEW; -END; -$_X$ LANGUAGE plpgsql; - -ALTER TABLE users ALTER ftid SET NOT NULL; - -CREATE OR REPLACE FUNCTION add_user() RETURNS trigger - AS $_X$ -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; -$_X$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION change_member() RETURNS trigger - AS $_X$ -BEGIN - IF TG_OP = 'INSERT' THEN - IF NEW.gid = 2 THEN - UPDATE planets SET alliance_id = 1 WHERE - id = (SELECT planet FROM users WHERE uid = NEW.uid); - END IF; - ELSIF TG_OP = 'DELETE' THEN - IF OLD.gid = 2 THEN - UPDATE planets SET alliance_id = NULL WHERE - id = (SELECT planet FROM users WHERE uid = OLD.uid); - END IF; - END IF; - - return NEW; -END; -$_X$ LANGUAGE plpgsql; - -ALTER TABLE calls ALTER ftid SET NOT NULL; - -CREATE OR REPLACE FUNCTION add_call() RETURNS trigger - AS $_X$ -DECLARE - thread INTEGER; -BEGIN - INSERT INTO forum_threads (fbid,subject,uid) - VALUES(-3,NEW.member || ': ' || NEW.landing_tick,-3) RETURNING ftid - INTO STRICT thread; - NEW.ftid = thread; - RETURN NEW; -END; -$_X$ LANGUAGE plpgsql; diff --git a/database/views/alliances_resources.sql b/database/views/alliances_resources.sql new file mode 100644 index 0000000..74ecb52 --- /dev/null +++ b/database/views/alliances_resources.sql @@ -0,0 +1,32 @@ +CREATE OR REPLACE VIEW alliance_resources AS +WITH planet_estimates AS ( + SELECT ps.tick, alliance, hidden,size,score,(metal+crystal+eonium) AS resources + ,score + (metal+crystal+eonium)/300 + hidden/100 AS nscore2 + ,score + (metal+crystal+eonium)/300 + hidden/100 + (endtick()-tick())*( + 250*size + COALESCE(metal_ref + crystal_ref + eonium_ref,7)* 1000 + + CASE extraction WHEN 0 THEN 3000 WHEN 1 THEN 11500 ELSE COALESCE(extraction,3)*3000*3 END + )*(1.35+0.005*COALESCE(fincents,20))/100 AS nscore3 + FROM current_planet_stats p + JOIN current_planet_scans ps USING (pid) + LEFT OUTER JOIN current_development_scans ds USING (pid) +), planet_ranks AS ( + SELECT *, RANK() OVER(PARTITION BY alliance ORDER BY score DESC) AS rank FROM planet_estimates +), top_planets AS ( + SELECT alliance, sum(resources) AS resources, sum(hidden) AS hidden + ,sum(nscore2)::bigint AS nscore2, sum(nscore3)::bigint AS nscore3 + ,count(*) AS planets, sum(score) AS score, sum(size) AS size + ,avg(tick)::int AS avgtick + FROM planet_ranks WHERE rank <= 60 + GROUP BY alliance +) +SELECT aid,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 + (resources / 300) + (hidden / 100))::bigint AS nscore + ,nscore2, nscore3, avgtick +FROM alliances a + JOIN top_planets r USING (alliance) + LEFT OUTER JOIN (SELECT aid,score,size,members FROM alliance_stats + WHERE tick = (SELECT max(tick) FROM alliance_stats)) s USING (aid) +; + diff --git a/database/available_ships.sql b/database/views/available_ships.sql similarity index 100% rename from database/available_ships.sql rename to database/views/available_ships.sql diff --git a/database/views/current_planet_stats.sql b/database/views/current_planet_stats.sql new file mode 100644 index 0000000..f8ff556 --- /dev/null +++ b/database/views/current_planet_stats.sql @@ -0,0 +1,40 @@ +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 + ,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 pid, tick, x, y, z, size, score, value, xp, sizerank, scorerank, valuerank, xprank + FROM planet_stats + WHERE tick = ( SELECT max(tick) AS max FROM planet_stats) + ) ps + NATURAL JOIN planets p + LEFT JOIN alliances a USING (alliance); + +CREATE OR REPLACE VIEW current_planet_stats_full AS +SELECT * +FROM planets p + NATURAL JOIN ( + SELECT * + FROM planet_stats + WHERE tick = ( SELECT max(tick) AS max FROM planet_stats) + ) ps + LEFT JOIN alliances USING (alliance); + +CREATE OR REPLACE FUNCTION change_member() RETURNS trigger + AS $_X$ +BEGIN + IF TG_OP = 'INSERT' THEN + IF NEW.gid = 2 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 = 2 THEN + UPDATE planets SET alliance = NULL WHERE + pid = (SELECT pid FROM users WHERE uid = OLD.uid); + END IF; + END IF; + + return NEW; +END; +$_X$ LANGUAGE plpgsql; + diff --git a/database/calls.sql b/database/views/defcalls.sql similarity index 54% rename from database/calls.sql rename to database/views/defcalls.sql index 0cd2410..df4860f 100644 --- a/database/calls.sql +++ b/database/views/defcalls.sql @@ -1,25 +1,3 @@ -DROP VIEW IF EXISTS full_defcalls; -DROP VIEW IF EXISTS defcalls; - -CREATE TABLE call_statuses ( - status TEXT PRIMARY KEY -); - -INSERT INTO call_statuses VALUES('Open'),('Covered'),('Ignored'); - -ALTER TABLE calls ADD COLUMN status TEXT NOT NULL REFERENCES call_statuses(status) -DEFAULT 'Open'; - -UPDATE calls SET status = (CASE WHEN covered THEN 'Covered' WHEN NOT OPEN THEN - 'Ignored' ELSE 'Open' END); - -ALTER TABLE calls DROP COLUMN open; -ALTER TABLE calls DROP COLUMN covered; -ALTER TABLE calls DROP COLUMN shiptypes; -ALTER TABLE calls RENAME id TO call; -ALTER TABLE calls RENAME member TO uid; -ALTER TABLE incomings RENAME id TO inc; - CREATE OR REPLACE VIEW defcalls AS SELECT call, status,c.uid, c.landing_tick ,dc.username AS dc, (c.landing_tick - tick()) AS curreta @@ -45,16 +23,3 @@ FROM users_defprio u LEFT OUTER JOIN launch_confirmations f USING (pid,landing_tick) GROUP BY call, x,y,z,pid,landing_tick,dc,curreta,defprio,c.race,amount,c.eta,shiptype,c.alliance,attackers, status ; - -CREATE OR REPLACE FUNCTION add_call() RETURNS trigger - AS $_X$ -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; -$_X$ LANGUAGE plpgsql; diff --git a/database/intel.sql b/database/views/full_intel.sql similarity index 90% rename from database/intel.sql rename to database/views/full_intel.sql index 38098fc..807c474 100644 --- a/database/intel.sql +++ b/database/views/full_intel.sql @@ -1,5 +1,4 @@ -DROP VIEW IF EXISTS full_intel; -CREATE VIEW full_intel AS +CREATE OR REPLACE 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 diff --git a/database/views/scans.sql b/database/views/scans.sql new file mode 100644 index 0000000..c0484c5 --- /dev/null +++ b/database/views/scans.sql @@ -0,0 +1,19 @@ +CREATE OR REPLACE VIEW current_development_scans AS +SELECT DISTINCT ON (pid) ds.* +FROM development_scans ds +ORDER BY pid, tick DESC, id DESC; + +CREATE OR REPLACE VIEW current_planet_scans AS +SELECT DISTINCT ON (planet) ps.* +FROM planet_scans ps +ORDER BY planet, tick DESC, id DESC; + +CREATE OR REPLACE VIEW current_structure_scans AS +SELECT DISTINCT ON (planet) ss.* +FROM structure_scans ss +ORDER BY planet, tick DESC, id DESC; + +CREATE OR REPLACE VIEW current_tech_scans AS +SELECT DISTINCT ON (planet) ts.* +FROM tech_scans ts +ORDER BY planet, tick DESC, id DESC; diff --git a/database/defprio.sql b/database/views/users_defprio.sql similarity index 56% rename from database/defprio.sql rename to database/views/users_defprio.sql index 5011720..107e19b 100644 --- a/database/defprio.sql +++ b/database/views/users_defprio.sql @@ -4,10 +4,11 @@ SELECT u.*, (0.2 * (u.attack_points / GREATEST(a.attack, 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) + JOIN current_planet_stats p USING (pid) , ( - SELECT avg(u.attack_points) AS attack, avg(u.defense_points) AS defense, avg(p.size) AS size, avg(p.score) AS score, avg(p.value) AS value - FROM users u + SELECT avg(u_1.attack_points) AS attack, avg(u_1.defense_points) AS defense + ,avg(p.size) AS size, avg(p.score) AS score, avg(p.value) AS value + FROM users u_1 JOIN current_planet_stats p USING (pid) - WHERE u.uid IN ( SELECT groupmembers.uid FROM groupmembers WHERE groupmembers.gid = 'M') + WHERE u_1.uid IN ( SELECT groupmembers.uid FROM groupmembers WHERE groupmembers.gid = 'M') ) a; diff --git a/database/wiki.sql b/database/wiki.sql deleted file mode 100644 index 2ec29f6..0000000 --- a/database/wiki.sql +++ /dev/null @@ -1,75 +0,0 @@ -CREATE TABLE wiki_namespaces ( - namespace VARCHAR(16) PRIMARY KEY -); - -CREATE TABLE wiki_pages ( - wpid SERIAL PRIMARY KEY, - name VARCHAR(255) NOT NULL, - namespace TExt NOT NULL REFERENCES wiki_namespaces(namespace) DEFAULT '', - textsearch tsvector NOT NULL DEFAULT to_tsvector(''), - time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), - UNIQUE(namespace,name) -); - -CREATE INDEX wiki_pages_textsearch_index ON wiki_pages USING gin (textsearch); - -CREATE TABLE wiki_page_revisions ( - wpid INTEGER REFERENCES wiki_pages(wpid), - wprev SERIAL NOT NULL PRIMARY KEY, - parent INTEGER REFERENCES wiki_page_revisions(wprev), - text TEXT NOT NULL, - comment TEXT NOT NULL, - time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), - uid INTEGER REFERENCES users(uid) -); - -ALTER TABLE wiki_pages ADD COLUMN wprev INTEGER REFERENCES wiki_page_revisions(wprev); - -CREATE OR REPLACE FUNCTION update_wiki_page() RETURNS trigger -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; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER update_wiki_page - BEFORE UPDATE ON wiki_pages - FOR EACH ROW - EXECUTE PROCEDURE update_wiki_page(); - -CREATE TABLE wiki_namespace_access ( - namespace TEXT NOT NULL REFERENCES wiki_namespaces(namespace), - gid INTEGER NOT NULL REFERENCES groups(gid), - edit BOOL NOT NULL DEFAULT FALSE, - post BOOL NOT NULL DEFAULT FALSE, - moderate BOOL NOT NULL DEFAULT FALSE, - PRIMARY KEY(gid,namespace) -); - -CREATE TABLE wiki_page_access ( - wpid INTEGER NOT NULL REFERENCES wiki_pages(wpid), - uid INTEGER NOT NULL REFERENCES users(uid), - edit BOOL NOT NULL DEFAULT FALSE, - moderate BOOL NOT NULL DEFAULT FALSE, - PRIMARY KEY(uid,wpid) -); - -INSERT INTO wiki_namespaces VALUES (''); -INSERT INTO wiki_namespaces VALUES ('Members'); -INSERT INTO wiki_namespaces VALUES ('HC'); -INSERT INTO wiki_namespaces VALUES ('Tech'); -INSERT INTO wiki_namespaces VALUES ('Info'); - -INSERT INTO wiki_pages (name,namespace) VALUES('Main','Info'); -INSERT INTO wiki_page_revisions (wpid,text,comment,uid) VALUES(1,'Welcome to the main page!', 'First revision', 1); -UPDATE wiki_pages set wprev = 1 WHERE wpid = 1; - -- 2.39.2