AS $_$
DECLARE
p RECORD;
- pid INTEGER;
+ id INTEGER;
thread INTEGER;
BEGIN
- SELECT id, race INTO p FROM planets WHERE ruler = _ruler AND planet = _planet;
+ 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 id = p.id;
+ UPDATE planets SET race = _race WHERE pid = p.pid;
END IF;
- pid := p.id;
+ 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 id INTO pid;
+ RETURNING pid INTO id;
END IF;
- RETURN pid;
+ RETURN id;
END;
$_$
LANGUAGE plpgsql;
-DROP FUNCTION findplanetid(character varying, character varying, character varying);
+DROP FUNCTION IF EXISTS findplanetid(character varying, character varying, character varying);
-CREATE OR REPLACE FUNCTION find_alliance_id(alliance text) RETURNS integer
+CREATE OR REPLACE FUNCTION find_alliance_id(alli text) RETURNS integer
AS $_$
DECLARE
- aid INTEGER;
+ id INTEGER;
BEGIN
- SELECT id FROM INTO aid alliances WHERE name = alliance;
+ SELECT aid FROM INTO id alliances WHERE alliance = alli;
IF NOT FOUND THEN
- INSERT INTO alliances(name) VALUES($1)
- RETURNING id INTO aid;
+ INSERT INTO alliances(alliance) VALUES($1)
+ RETURNING aid INTO id;
END IF;
- RETURN aid;
+ RETURN id;
END;
$_$
LANGUAGE plpgsql;
-DROP FUNCTION find_alliance_id(character varying);
+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;
+
--- /dev/null
+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);
}
($self->{planet},$self->{username},$self->{css}) = $dbh->selectrow_array(q{
- SELECT planet,username,css FROM users WHERE uid = ?
+ SELECT pid,username,css FROM users WHERE uid = ?
},undef,$self->{id}) or die $dbh->errstr;
return $self;
$order = "score DESC";
}
my $query = $dbh->prepare(q{
- SELECT DISTINCT a.id,name,COALESCE(s.score,SUM(p.score)) AS score
+ SELECT aid AS id,alliance AS name,COALESCE(s.score,SUM(p.score)) AS score
,COALESCE(s.size,SUM(p.size)) AS size,s.members,count(p.score) AS kmem
,COALESCE(SUM(p.score),-1) AS kscore
,COALESCE(SUM(p.size),-1) AS ksize
,COALESCE(AVG(p.value)::int,-1) AS kvalavg
FROM alliances a
LEFT OUTER JOIN (SELECT * FROM alliance_stats
- WHERE tick = (SELECT max(tick) FROM alliance_stats)) s ON s.id = a.id
- LEFT OUTER JOIN current_planet_stats p ON p.alliance_id = a.id
- GROUP BY a.id,a.name,s.score,s.size,s.members
+ WHERE tick = (SELECT max(tick) FROM alliance_stats)) s USING (aid)
+ LEFT OUTER JOIN current_planet_stats p USING (alliance,aid)
+ GROUP BY aid,alliance,s.score,s.size,s.members
HAVING s.score IS NOT NULL OR count(p.score) > 0
ORDER BY
} . $order);
my ( $self, $c, $id, $order ) = @_;
my $dbh = $c->model;
- my $query = $dbh->prepare(q{SELECT id,name, relationship FROM alliances WHERE id = ?});
+ my $query = $dbh->prepare(q{SELECT aid AS id,alliance AS name, relationship FROM alliances WHERE aid = ?});
my $a = $dbh->selectrow_hashref($query,undef,$id);
$c->stash(a => $a);
$order .= ' DESC' if $order eq 'hit_us';
my $members = $dbh->prepare(q{
- SELECT id, coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
+ SELECT pid AS id, coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
,planet_status,hit_us, sizerank, scorerank, valuerank, xprank
FROM current_planet_stats p
- WHERE p.alliance_id = ?
+ WHERE p.alliance = ?
ORDER BY
} . $order);
- $members->execute($a->{id});
+ $members->execute($a->{name});
$c->stash(members => $members->fetchall_arrayref({}) );
my $ticks = $c->req->param('ticks') || 48;
$query = $dbh->prepare(intelquery q{
o.alliance AS oalliance ,coords(o.x,o.y,o.z) AS ocoords, i.sender
,t.alliance AS talliance,coords(t.x,t.y,t.z) AS tcoords, i.target
- },q{NOT ingal AND (o.alliance_id = $1 OR t.alliance_id = $1)
+ },q{NOT ingal AND (o.alliance = $1 OR t.alliance = $1)
AND (i.mission = 'Defend' OR i.mission = 'AllyDef')
- AND ((( t.alliance_id != o.alliance_id OR t.alliance_id IS NULL OR o.alliance_id IS NULL)))
- AND i.sender NOT IN (SELECT planet FROM users u NATURAL JOIN groupmembers gm WHERE gid = 8 AND planet IS NOT NULL)
- AND NOT (i.back IS NOT NULL AND i.back = i.tick + 4)
+ AND COALESCE( t.alliance != o.alliance, TRUE)
AND i.tick > (tick() - $2)
});
- $query->execute($a->{id}, $ticks);
+ $query->execute($a->{name}, $ticks);
$c->stash(intel => $query->fetchall_arrayref({}) );
}
my ( $self, $c, $id, $order ) = @_;
my $dbh = $c->model;
+ my $query = $dbh->prepare(q{SELECT aid,alliance, relationship FROM alliances WHERE aid = ?});
+ my $a = $dbh->selectrow_hashref($query,undef,$id);
+
$dbh->begin_work;
my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
(SELECT ftid FROM users WHERE uid = $1),$1,$2)
});
if ($c->req->param('crelationship')){
my $value = $c->req->param('relationship');
- $dbh->do(q{UPDATE alliances SET relationship = ? WHERE id =?}
+ $dbh->do(q{UPDATE alliances SET relationship = ? WHERE aid =?}
,undef,$value,$id);
$log->execute($c->user->id
- ,"HC set alliance: $id relationship: $value");
+ ,"HC set alliance: $a->{alliance} ($id) relationship: $value");
}
my $coords = $c->req->param('coords');
- my $findplanet = $dbh->prepare(q{SELECT id FROM current_planet_stats
+ my $findplanet = $dbh->prepare(q{SELECT pid FROM current_planet_stats
WHERE x = ? AND y = ? AND z = ?});
my $addplanet = $dbh->prepare(q{
- UPDATE planets SET alliance_id = $2, nick = coalesce($3,nick)
- WHERE id = $1;
+ UPDATE planets SET alliance = $2, nick = coalesce($3,nick)
+ WHERE pid = $1;
});
my $text = '';
while ($coords =~ m/(\d+):(\d+):(\d+)(?:\s+nick=(\S+))?/g){
my ($planet) = $dbh->selectrow_array($findplanet,undef,$1,$2,$3);
- $addplanet->execute($planet,$id,$4);
+ $addplanet->execute($planet,$a->{alliance},$4);
my $nick = '';
$nick = "(nick $4)" if defined $4;
$text .= "($planet) $1:$2:$3 $nick\n";
}
if ($text){
$log->execute($c->user->id
- ,"HC added the following planets to alliance $id:\n $text");
+ ,"HC added the following planets to alliance $a->{alliance} ($id):\n $text");
}
$dbh->commit;
my ( $self, $c, $id, $order ) = @_;
my $dbh = $c->model;
+ my $query = $dbh->prepare(q{SELECT aid,alliance, relationship FROM alliances WHERE aid = ?});
+ my $a = $dbh->selectrow_hashref($query,undef,$id);
+
$dbh->begin_work;
my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
(SELECT ftid FROM users WHERE uid = $1),$1,$2)
});
- my ($coords) = $dbh->selectrow_array(q{SELECT CONCAT(coords(x,y,z) || ' ')
- FROM current_planet_stats where alliance_id = $1
- },undef,$id);
+ my ($coords) = $dbh->selectrow_array(q{SELECT array_to_string(array_agg(coords(x,y,z)),' ')
+ FROM current_planet_stats where alliance = $1
+ },undef,$a->{alliance});
my $removeplanets = $dbh->prepare(q{
- UPDATE planets SET alliance_id = NULL
- WHERE alliance_id = $1;
+ UPDATE planets SET alliance = NULL
+ WHERE alliance = $1;
});
- $removeplanets->execute($id);
+ $removeplanets->execute($a->{alliance});
$log->execute($c->user->id
- ,"HC cleaned alliance $id :\n\n$coords");
+ ,"HC cleaned alliance $a->{alliance} (id) :\n\n$coords");
$dbh->commit;
$c->res->redirect($c->uri_for('edit',$id));
$order = "resplanet DESC";
}
- my $query = $dbh->prepare(qq{
- SELECT a.id,a.name,a.relationship,s.members,s.score,s.size
- ,r.resources,r.hidden,r.planets
- ,(resources/planets)::bigint AS resplanet
- ,(hidden/planets)::bigint AS hidplanet
- ,(score + (resources / 300) + (hidden / 100))::bigint AS nscore
- ,(SELECT sum(score)::bigint FROM (
-SELECT score + (metal+crystal+eonium)/300 + hidden/100 AS score
-FROM current_planet_stats p
- JOIN current_planet_scans ps ON p.id = ps.planet
-WHERE alliance_id = r.id
-ORDER BY score DESC
-LIMIT 60) a
- ) AS nscore2
- ,(SELECT sum(score)::bigint FROM (
-SELECT 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 score
-FROM current_planet_stats p
- JOIN current_planet_scans ps ON p.id = ps.planet
- LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
-WHERE alliance_id = r.id
-ORDER BY score DESC
-LIMIT 60) a
- ) AS nscore3
- FROM (SELECT alliance_id AS id,sum(metal+crystal+eonium) AS resources
- , sum(hidden) AS hidden, count(*) AS planets
- FROM planets p join current_planet_scans c ON p.id = c.planet
- GROUP by alliance_id
- ) r
- NATURAL JOIN alliances a
- LEFT OUTER JOIN (SELECT *,LEAST(members,60) AS scoremem FROM alliance_stats
- WHERE tick = (SELECT max(tick) FROM alliance_stats)) s ON a.id = s.id
- ORDER BY $order
- });
+ my $query = $dbh->prepare(q{
+SELECT aid AS id,alliance AS name,relationship,members,score,size
+ ,resources,hidden,planets
+ ,(resources/planets)::bigint AS resplanet
+ ,(hidden/planets)::bigint AS hidplanet
+ , nscore, nscore2, nscore3
+FROM alliance_resources
+ORDER BY } . $order
+ );
$query->execute;
- my @alliances;
- while (my $alliance = $query->fetchrow_hashref){
- push @alliances,$alliance;
- }
- $c->stash(alliances => \@alliances);
+ $c->stash(alliances => $query->fetchall_arrayref({}));
}
my ( $self, $c, $type ) = @_;
my $dbh = $c->model;
- my $where = q{open AND c.landing_tick-6 > tick()};
+ my $where = q{open AND landing_tick-6 > tick()};
my $order = q{landing_tick DESC, defprio DESC};
if (defined $type){
if ($type eq 'covered'){
}elsif ($type eq 'uncovered'){
$where = 'not covered';
}elsif ($type eq 'recent'){
- $where = q{c.landing_tick > tick()};
+ $where = q{landing_tick > tick()};
$order = q{x,y,z};
}
}
$c->stash(maxprio => $dbh->selectrow_array($pointlimits,undef,'DEFMAXPRIO'));
my $query = $dbh->prepare(qq{
- SELECT id,coords(x,y,z),planet,landing_tick,dc,curreta,fleets
- ,defprio
- ,array_accum(COALESCE(race::text,'')) AS race
- ,array_accum(COALESCE(amount,0)) AS amount
- ,array_accum(COALESCE(eta,0)) AS eta
- ,array_accum(COALESCE(shiptype,'')) AS shiptype
- ,array_accum(COALESCE(alliance,'?')) AS alliance
- ,array_accum(coords) AS attackers
- FROM (SELECT c.id, p.x,p.y,p.z,p.id AS planet, defprio
- ,u.defense_points, u.attack_points, c.landing_tick
- ,dc.username AS dc, (c.landing_tick - tick()) AS curreta
- ,p2.race, i.amount, i.eta, i.shiptype, p2.alliance
- ,coords(p2.x,p2.y,p2.z), COUNT(DISTINCT f.fid) AS fleets
- FROM calls c
- JOIN users_defprio u ON c.member = u.uid
- JOIN current_planet_stats p ON u.planet = p.id
- LEFT OUTER JOIN incomings i ON i.call = c.id
- LEFT OUTER JOIN current_planet_stats p2 ON i.sender = p2.id
- LEFT OUTER JOIN users dc ON c.dc = dc.uid
- LEFT OUTER JOIN launch_confirmations f ON f.target = u.planet
- AND f.landing_tick = c.landing_tick AND f.back = f.landing_tick + f.eta - 1
- WHERE $where
- GROUP BY c.id, p.x,p.y,p.z,p.id, defprio, c.landing_tick
- ,u.defense_points,u.attack_points,dc.username
- ,p2.race,i.amount,i.eta,i.shiptype,p2.alliance,p2.x,p2.y,p2.z
- ) c
- GROUP BY id, x,y,z,planet,landing_tick, defense_points, attack_points
- ,dc,curreta,fleets, defprio
- ORDER BY $order
+SELECT *, pid AS planet, coords(x,y,z)
+FROM full_defcalls
+WHERE $where
+ORDER BY $order
});
$query->execute;
$c->stash(calls => $query->fetchall_arrayref({}));
my $outgoings = $dbh->prepare(q{
(
- SELECT DISTINCT ON (mission,name) fid,mission,name,tick, NULL AS eta
- ,amount, NULL AS coords, planet, NULL AS back, NULL AS recalled
+ SELECT DISTINCT ON (mission,name) 1 AS type, fid,mission,name,tick, NULL AS eta
+ ,amount, NULL AS coords, pid AS planet, NULL AS back, NULL AS recalled
FROM fleets f
- WHERE planet = $1 AND tick <= $2 AND (
+ WHERE pid = $1 AND tick <= $2 AND (
mission = 'Full fleet'
OR fid IN (SELECT fid FROM fleet_scans)
) AND (
mission = 'Full fleet'
OR tick >= $2 - 12
)
- ORDER BY mission,name,tick DESC
) UNION (
- SELECT fid,mission,name,landing_tick AS tick, eta, amount
- , coords(x,y,z), t.id AS planet, back
+ SELECT 2 AS type, MAX(fid) AS fid,mission,name,landing_tick AS tick, eta, amount
+ , coords(x,y,z), pid AS planet, back
, (back <> landing_tick + eta - 1) AS recalled
- FROM fleets f
- JOIN launch_confirmations USING (fid)
- LEFT OUTER JOIN current_planet_stats t ON target = t.id
- WHERE f.planet = $1 AND back >= $2 AND landing_tick - eta - 12 < $2
+ FROM launch_confirmations
+ JOIN (
+ SELECT fid,amount,name,mission FROM fleets WHERE pid = $1
+ ) f USING (fid)
+ LEFT OUTER JOIN current_planet_stats t USING (pid)
+ WHERE back >= $2 AND landing_tick - eta - 12 < $2
+ GROUP BY mission,name,landing_tick,eta,amount,back,x,y,z,pid
) UNION (
SELECT DISTINCT ON (tick,x,y,z,mission,name,amount)
- NULL as fid, i.mission, i.name, i.tick,eta
- , i.amount, coords(x,y,z), t.id AS planet, back, NULL AS recalled
+ 3 AS type, NULL as fid, i.mission, i.name, i.tick,eta
+ , i.amount, coords(x,y,z), t.pid AS planet, back, NULL AS recalled
FROM intel i
- LEFT OUTER JOIN current_planet_stats t ON i.target = t.id
+ LEFT OUTER JOIN current_planet_stats t ON i.target = t.pid
WHERE uid = -1 AND i.sender = $1 AND i.tick > $2 - 14 AND i.tick < $2 + 14
ORDER BY i.tick,x,y,z,mission,name,amount,back
-)
+) ORDER BY type, mission,name,tick DESC
});
my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships
WHERE fid = ? ORDER BY num
$c->stash(fleets => \@fleets);
- my $defenders = $dbh->prepare(q{
-SELECT DISTINCT ON (x,y,z,s.id,name,amount,back) fid,mission, name, eta
- , amount, coords(x,y,z) AS coords, landing_tick AS tick, f.planet
+ my $defenders = $dbh->prepare(q{
+SELECT DISTINCT ON (x,y,z,pid,name,amount,back) fid,mission, name, eta
+ , amount, coords(x,y,z) AS coords, landing_tick AS tick, pid AS planet
,back, (back <> landing_tick + eta - 1) AS recalled
-FROM launch_confirmations lc
- JOIN fleets f USING (fid)
- LEFT OUTER JOIN current_planet_stats s ON f.planet = s.id
-WHERE target = ? AND landing_tick = ? AND mission = 'Defend'
+FROM fleets f
+ LEFT OUTER JOIN current_planet_stats s USING (pid)
+ JOIN (
+ SELECT fid,back,eta,landing_tick
+ FROM launch_confirmations
+ WHERE pid = $1 AND landing_tick = $2
+ ) lc USING (fid)
+WHERE mission = 'Defend'
ORDER BY x,y,z
});
my $attackers = $dbh->prepare(q{
SELECT coords(p.x,p.y,p.z), p.planet_status, p.race,i.eta,i.amount
- ,i.fleet,i.shiptype,p.relationship,p.alliance,i.id,p.id AS planet
+ ,i.fleet,i.shiptype,p.relationship,p.alliance,i.id,pid AS planet
FROM incomings i
- JOIN current_planet_stats p ON i.sender = p.id
+ JOIN current_planet_stats p USING (pid)
WHERE i.call = ?
ORDER BY p.x,p.y,p.z
});
my $query = $dbh->prepare(q{SELECT username,defense_points,count(id) AS calls
, SUM(fleets) AS fleets, SUM(recalled) AS recalled
,count(NULLIF(fleets,0)) AS defended_calls
- FROM (SELECT username,defense_points,c.id,count(f.target) AS fleets
+ FROM (SELECT username,defense_points,c.id,count(f.back) AS fleets
, count(NULLIF(f.landing_tick + f.eta -1 = f.back,TRUE)) AS recalled
FROM users u JOIN calls c ON c.member = u.uid
LEFT OUTER JOIN (
- SELECT * FROM launch_confirmations JOIN fleets USING (fid)
+ SELECT lc.pid,landing_tick,eta,back
+ FROM launch_confirmations lc JOIN fleets f USING (fid)
WHERE mission = 'Defend'
- ) f ON u.planet = f.target AND c.landing_tick = f.landing_tick
+ ) f USING (pid,landing_tick)
GROUP BY username,defense_points,c.id
) d
GROUP BY username,defense_points ORDER BY fleets DESC, defense_points
my $query = $dbh->prepare(q{
SELECT c.id, coords(p.x,p.y,p.z), c.landing_tick, c.info, covered
,open, dc.username AS dc, u.defense_points,c.member AS uid
- ,u.planet, u.username AS member, u.sms,c.ftid,calc
+ ,p.pid AS planet, u.username AS member, u.sms,c.ftid,calc
FROM calls c
JOIN users u ON c.member = u.uid
+ JOIN current_planet_stats p USING (pid)
LEFT OUTER JOIN users dc ON c.dc = dc.uid
- JOIN current_planet_stats p ON u.planet = p.id
WHERE c.id = ?
});
$call = $dbh->selectrow_hashref($query,undef,$call);
sub marktarget : Local {
my ( $self, $c, $target ) = @_;
my $dbh = $c->model;
- my $update = $dbh->prepare(q{INSERT INTO covop_attacks (uid,id,tick) VALUES(?,?,tick())});
+ my $update = $dbh->prepare(q{INSERT INTO covop_attacks (uid,pid,tick) VALUES(?,?,tick())});
eval{
$update->execute($c->user->id,$target);
};
my $query = $dbh->prepare(q{
SELECT * FROM (
- SELECT *
+ SELECT *, pid AS id
,(2*pvalue::float/cvalue) :: Numeric(4,1) AS max_bank_hack
,max_bank_hack(metal,crystal,eonium,pvalue,cvalue,5) AS hack5
,max_bank_hack(metal,crystal,eonium,pvalue,cvalue,13) AS hack13
- FROM (SELECT p.id,coords(x,y,z),x,y,z,size
+ FROM (SELECT pid,coords(x,y,z),x,y,z,size
,metal + metal_roids * (tick()-ps.tick) * 125 AS metal
,crystal + crystal_roids * (tick()-ps.tick) * 125 AS crystal
,eonium + eonium_roids * (tick()-ps.tick) * 125 AS eonium
, planet_status, relationship,gov,ps.tick AS pstick, ds.tick AS dstick
, p.value AS pvalue, c.value AS cvalue
FROM current_planet_stats p
- LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet
- LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
- CROSS JOIN (SELECT value FROM current_planet_stats WHERE id = $1) c
+ LEFT OUTER JOIN current_planet_scans ps USING (pid)
+ LEFT OUTER JOIN current_development_scans ds USING (pid)
+ CROSS JOIN (SELECT value FROM current_planet_stats WHERE pid = $1) c
) AS foo
- LEFT OUTER JOIN (SELECT id,max(tick) AS lastcovop FROM covop_attacks
- GROUP BY id) co USING (id)
+ LEFT OUTER JOIN (SELECT pid,max(tick) AS lastcovop FROM covop_attacks
+ GROUP BY pid) co USING (pid)
WHERE (metal IS NOT NULL OR distorters IS NOT NULL)
AND (NOT planet_status IN ('Friendly','NAP'))
AND (relationship IS NULL OR NOT relationship IN ('Friendly','NAP'))
my $query = $dbh->prepare(q{SELECT tick,-scorerank AS score,-sizerank AS size
,-valuerank AS value,-xprank AS xp
FROM planets NATUral JOIN planet_stats
- WHERE id = $1 ORDER BY tick ASC
+ WHERE pid = $1 ORDER BY tick ASC
});
$query->execute($planet);
$c->stash(query => $query);
$c->stash->{settings}->{y_min_value} = '0';
my $query = $dbh->prepare(q{SELECT tick,score,size,value,xp*60 AS "xp*60"
FROM planets NATURAL JOIN planet_stats
- WHERE id = $1 ORDER BY tick ASC
+ WHERE pid = $1 ORDER BY tick ASC
});
$query->execute($planet);
$c->stash(query => $query);
my $query = $dbh->prepare(q{SELECT a.tick,a.score/LEAST(members,60) AS NDscore
,a.size/members as NDsize,memsize, memscore
FROM (SELECT tick,size AS memsize,score AS memscore
- FROM planets p JOIN planet_stats ps USING (id) WHERE p.id = $1) p
+ FROM planets p JOIN planet_stats ps USING (pid) WHERE pid = $1) p
JOIN alliance_stats a ON a.tick = p.tick
- WHERE a.id = 1 ORDER BY tick
+ WHERE aid = 1 ORDER BY tick
});
$query->execute($planet);
$c->stash(query => $query);
$c->stash->{settings}->{y2_label} = 'score';
my $query = $dbh->prepare(q{SELECT a.tick,a.score,a.size,memsize, memscore
- FROM (SELECT tick,SUM(size) AS memsize,SUM(score) AS memscore
- FROM planets p JOIN planet_stats ps USING (id)
- WHERE p.alliance_id = $1 GROUP BY tick) p
- JOIN alliance_stats a ON a.tick = p.tick
- WHERE a.id = $1
- AND a.tick > (SELECT max(tick) - 50 FROM alliance_stats)
+ FROM (SELECT tick,aid,SUM(size) AS memsize,SUM(score) AS memscore
+ FROM alliances a
+ JOIN planets p USING (alliance)
+ JOIN planet_stats ps USING (pid)
+ GROUP BY tick,aid ) p
+ JOIN alliance_stats a USING (aid,tick)
+ WHERE aid = $1
+ AND tick > (SELECT max(tick) - 50 FROM alliance_stats)
ORDER BY tick
});
$query->execute($alliance);
my $query = $dbh->prepare(q{SELECT a.tick,a.score/LEAST(members,60) AS score
,a.size/members AS size,memsize, memscore
- FROM (SELECT tick,AVG(size) AS memsize,AVG(score) AS memscore
- FROM planets p JOIN planet_stats ps USING (id) WHERE p.alliance_id = $1
- GROUP BY tick) p
- JOIN alliance_stats a ON a.tick = p.tick
- WHERE a.id = $1
- AND a.tick > (SELECT max(tick) - 50 FROM alliance_stats)
+ FROM (SELECT tick,aid,AVG(size) AS memsize,AVG(score) AS memscore
+ FROM alliances a
+ JOIN planets p USING (alliance)
+ JOIN planet_stats ps USING (pid)
+ GROUP BY tick,aid) p
+ JOIN alliance_stats a USING (aid,tick)
+ WHERE aid = $1
+ AND tick > (SELECT max(tick) - 50 FROM alliance_stats)
ORDER BY tick
});
$query->execute($alliance);
o.alliance AS oalliance ,coords(o.x,o.y,o.z) AS ocoords, i.sender
,t.alliance AS talliance,coords(t.x,t.y,t.z) AS tcoords, i.target
},q{not ingal
- AND ((( t.alliance_id != o.alliance_id OR t.alliance_id IS NULL OR o.alliance_id IS NULL) AND (i.mission = 'Defend' OR i.mission = 'AllyDef' ))
- OR ( t.alliance_id = o.alliance_id AND i.mission = 'Attack'))
- AND i.sender NOT IN (SELECT planet FROM users u NATURAL JOIN groupmembers gm WHERE gid = 8 AND planet IS NOT NULL)
- AND NOT (i.back IS NOT NULL AND i.back = i.tick + 4)
+ AND ((COALESCE( t.alliance != o.alliance,TRUE) AND (i.mission = 'Defend' OR i.mission = 'AllyDef' ))
+ OR ( t.alliance = o.alliance AND i.mission = 'Attack'))
AND i.tick > (tick() - $1)
});
$query->execute($ticks);
while ($coords =~ m/(\d+:\d+:\d+)/g){
push @coords,$1;
}
- my $planets = $dbh->prepare(q{SELECT id,coords(x,y,z), alliance, nick
+ my $planets = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z), alliance, nick
FROM current_planet_stats p
WHERE coords(x,y,z) = ANY($1)
ORDER BY alliance, p.x, p.y, p.z
my $ticks = $c->req->param('ticks') || 48;
$c->stash(showticks => $ticks);
- my $query = $dbh->prepare(q{SELECT id,coords(x,y,z),alliance,nick,channel
- FROM current_planet_stats WHERE channel ILIKE ?
+ my $query = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z),alliance,nick,channel
+ FROM current_planet_stats WHERE channel = $1
ORDER BY alliance,x,y,z
});
$query->execute($p->{channel});
my ( $self, $c, $order ) = @_;
my $dbh = $c->model;
- if ($order ~~ /(alliance|channel)/){
+ if ($order ~~ /(alliance)/){
$order = "lower($1) ASC";
}elsif ($order ~~ /(coords)/){
$order = "x,y,z";
}else{
- $order = 'lower(channel)';
+ $order = 'channel';
}
my $query = $dbh->prepare(q{
-SELECT id,coords(x,y,z),nick,channel,alliance FROM current_planet_stats
+SELECT pid AS id,coords(x,y,z),nick,channel,alliance FROM current_planet_stats
WHERE channel <> '' and channel IS NOT NULL
ORDER BY } . $order
);
});
if ($c->req->param('cnick')){
my $value = html_escape $c->req->param('nick');
- $dbh->do(q{UPDATE planets SET nick = ? WHERE id =?}
+ $dbh->do(q{UPDATE planets SET nick = ? WHERE pid =?}
,undef,$value,$p->{id});
$log->execute($c->user->id,$p->{ftid},"Set nick to: $value");
}
if ($c->req->param('cchannel')){
my $value = html_escape $c->req->param('channel');
- $dbh->do(q{UPDATE planets SET channel = ? WHERE id =?}
+ $dbh->do(q{UPDATE planets SET channel = ? WHERE pid =?}
,undef,$value,$p->{id});
$log->execute($c->user->id,$p->{ftid},"Set channel to: $value");
}
if ($c->req->param('cstatus')){
my $value = $c->req->param('status');
- $dbh->do(q{UPDATE planets SET planet_status = ? WHERE id =?}
+ $dbh->do(q{UPDATE planets SET planet_status = ? WHERE pid =?}
,undef,$value,$p->{id});
$log->execute($c->user->id,$p->{ftid},"Set planet_status to: $value");
}
if ($c->req->param('cgov')){
my $value = $c->req->param('gov');
- $dbh->do(q{UPDATE planets SET gov = ? WHERE id =?}
+ $dbh->do(q{UPDATE planets SET gov = ? WHERE pid =?}
,undef,$value,$p->{id});
$log->execute($c->user->id,$p->{ftid},"Set gov to: $value");
}
if ($c->req->param('calliance')){
my $value = $c->req->param('alliance');
- $dbh->do(q{UPDATE planets SET alliance_id = NULLIF(?,-1) WHERE id =?}
+ $dbh->do(q{UPDATE planets SET alliance = NULLIF(?,'') WHERE pid = ?}
,undef,$value,$p->{id});
- $log->execute($c->user->id,$p->{ftid},"Set alliance_id to: $value");
+ $log->execute($c->user->id,$p->{ftid},"Set alliance to: $value");
}
$dbh->commit;
,undef,$1,$2,$3,$4);
$c->res->redirect($c->uri_for('planet',$planet));
}else{
- my $query = $dbh->prepare(q{SELECT id,coords(x,y,z),nick
+ my $query = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z),nick
FROM current_planet_stats p
WHERE nick ilike $1
});
my ( $self, $c, $id ) = @_;
my $dbh = $c->model;
- my $query = $dbh->prepare(q{SELECT x,y,z,id, nick, alliance,alliance_id
+ my $query = $dbh->prepare(q{SELECT x,y,z,pid AS id, nick, alliance,aid
, planet_status,channel,ftid,gov
FROM current_planet_stats
- WHERE id = $1
+ WHERE pid = $1
});
$query->execute($id);
$c->stash(p => $query->fetchrow_hashref);
my ( $self, $c ) = @_;
my $dbh = $c->model;
- my $query = $dbh->prepare(q{SELECT p.id,coords(x,y,z)
+ my $query = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z)
,ruler, p.planet,race, size, score, value
, xp, sizerank, scorerank, valuerank, xprank, p.value - p.size*200
- COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150
,(metal+crystal+eonium)/100 AS resvalue, planet_status,hit_us
, alliance,relationship,nick
FROM current_planet_stats p
- LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet
- LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
+ LEFT OUTER JOIN current_planet_scans ps USING (pid)
+ LEFT OUTER JOIN current_development_scans ds USING (pid)
WHERE planet_status IN ('Friendly','NAP') order by x,y,z asc
});
$query->execute;
my ($timestamp) = $dbh->selectrow_array(q{SELECT MAX(modified)::timestamp AS modified
FROM raid_targets WHERE raid = $1},undef,$raid->{id});
$c->stash(timestamp => $timestamp);
- $targets = $dbh->prepare(q{SELECT r.id,r.planet FROM raid_targets r
+ $targets = $dbh->prepare(q{SELECT r.id,r.pid FROM raid_targets r
WHERE r.raid = ? AND modified > ?
});
$targets->execute($raid->{id},$from);
}elsif($target){
- $targets = $dbh->prepare(q{SELECT r.id,r.planet FROM raid_targets r
+ $targets = $dbh->prepare(q{SELECT r.id,r.pid FROM raid_targets r
WHERE r.raid = $1 AND r.id = $2
});
$targets->execute($raid->{id},$target);
$c->stash(error => $c->flash->{error});
- $c->stash(u => $dbh->selectrow_hashref(q{SELECT planet,defense_points
+ $c->stash(u => $dbh->selectrow_hashref(q{SELECT pid AS planet,defense_points
,attack_points,scan_points,humor_points
, (attack_points+defense_points+scan_points/20)::NUMERIC(5,1) as total_points
, sms,rank,hostmask,call_if_needed,sms_note,defprio
},undef,$c->user->id)
);
- $c->stash(groups => $dbh->selectrow_array(q{SELECT array_accum(groupname)
+ $c->stash(groups => $dbh->selectrow_array(q{SELECT array_agg(groupname)
FROM groups g NATURAL JOIN groupmembers gm
WHERE uid = $1
},undef,$c->user->id)
);
- $c->stash(p => $dbh->selectrow_hashref(q{SELECT id,x,y,z, ruler, planet,race,
+ $c->stash(p => $dbh->selectrow_hashref(q{SELECT pid AS id,x,y,z, ruler, planet,race,
size, size_gain, size_gain_day,
score,score_gain,score_gain_day,
value,value_gain,value_gain_day,
valuerank,valuerank_gain,valuerank_gain_day,
xprank,xprank_gain,xprank_gain_day
from current_planet_stats_full p
- WHERE id = ?
+ WHERE pid = ?
},undef,$c->user->planet)
);
my $calls = $dbh->prepare(q{
- SELECT id,landing_tick,dc,curreta
- ,array_accum(race::text) AS race
- ,array_accum(amount) AS amount
- ,array_accum(eta) AS eta
- ,array_accum(shiptype) AS shiptype
- ,array_accum(coords) AS attackers
- FROM (SELECT c.id, c.landing_tick
- ,dc.username AS dc, (c.landing_tick - tick()) AS curreta
- ,p2.race, i.amount, i.eta, i.shiptype, p2.alliance
- ,coords(p2.x,p2.y,p2.z)
- FROM calls c
- LEFT OUTER JOIN incomings i ON i.call = c.id
- LEFT OUTER JOIN current_planet_stats p2 ON i.sender = p2.id
- LEFT OUTER JOIN users dc ON c.dc = dc.uid
- WHERE c.member = $1 AND c.landing_tick >= tick()
- GROUP BY c.id, c.landing_tick, dc.username
- ,p2.race,i.amount,i.eta,i.shiptype,p2.alliance,p2.x,p2.y,p2.z
- ) c
- GROUP BY id, landing_tick,dc,curreta
+SELECT * FROM defcalls
+WHERE uid = $1 AND landing_tick >= tick()
});
-
$calls->execute($c->user->id);
$c->stash(calls => $calls->fetchall_arrayref({}) );
$dbh->begin_work;
eval{
my $insert = $dbh->prepare(q{INSERT INTO fleets
- (planet,name,mission,tick,amount)
+ (pid,name,mission,tick,amount)
VALUES (?,'Main','Full fleet',tick(),?) RETURNING fid});
my ($id) = $dbh->selectrow_array($insert,undef
,$c->user->planet,$amount);
FROM raid_claims c
JOIN raid_targets t ON c.target = t.id
JOIN raids r ON t.raid = r.id
- WHERE c.uid = ? AND r.tick+c.wave-1 = ? AND t.planet = ?
+ WHERE c.uid = ? AND r.tick+c.wave-1 = ? AND t.pid = ?
AND r.open AND not r.removed
});
my $finddefensetarget = $dbh->prepare(q{SELECT c.id FROM calls c
JOIN users u ON c.member = u.uid
- WHERE u.planet = $1 AND c.landing_tick = $2
+ WHERE u.pid = $1 AND c.landing_tick = $2
});
my $informDefChannel = $dbh->prepare(q{INSERT INTO defense_missions
(fleet,call) VALUES (?,?)
WHERE uid = ? AND target = ? AND wave = ?
});
my $addfleet = $dbh->prepare(q{INSERT INTO fleets
- (name,mission,planet,tick,amount)
- VALUES ($2,$3,(SELECT planet FROM users WHERE uid = $1),tick(),$4)
+ (name,mission,pid,tick,amount)
+ VALUES ($2,$3,(SELECT pid FROM users WHERE uid = $1),tick(),$4)
RETURNING fid
});
my $addconfirmation = $dbh->prepare(q{INSERT INTO launch_confirmations
- (fid,uid,target,landing_tick,eta,back) VALUES ($1,$2,$3,$4,$5,$6)
+ (fid,uid,pid,landing_tick,eta,back) VALUES ($1,$2,$3,$4,$5,$6)
});
my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (fid,ship,amount)
VALUES (?,?,?)
my $dbh = $c->model;
my $defenders = $dbh->prepare(q{
-SELECT uid,u.planet,username, to_char(NOW() AT TIME ZONE timezone,'HH24:MI') AS time
+SELECT uid,pid AS planet,username, to_char(NOW() AT TIME ZONE timezone,'HH24:MI') AS time
,sms_note, call_if_needed, race, timezone
FROM users u
- JOIN current_planet_stats p ON p.id = u.planet
+ JOIN current_planet_stats p USING (pid)
WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 2)
ORDER BY call_if_needed DESC, LOWER(username)
});
my $query = $dbh->prepare(q{
(
SELECT DISTINCT ON (mission,name) fid,name,tick, NULL AS eta
- ,amount, NULL AS coords, planet AS target, NULL AS back
+ ,amount, NULL AS coords, pid AS target, NULL AS back
,NULL AS recalled, mission
FROM fleets f
- WHERE planet = $2 AND tick <= tick() AND tick >= tick() - 24
+ WHERE pid = $2 AND tick <= tick() AND tick >= tick() - 24
AND name IN ('Main','Advanced Unit') AND mission = 'Full fleet'
ORDER BY mission,name,tick DESC, fid DESC
) UNION (
SELECT fid,name,landing_tick AS tick, eta, amount
- , coords(x,y,z), target, back
+ , coords(x,y,z), lc.pid AS target, back
, (back <> landing_tick + eta - 1) AS recalled
,CASE WHEN landing_tick <= tick() OR (back <> landing_tick + eta - 1)
THEN 'Returning' ELSE mission END AS mission
- FROM fleets f
- JOIN launch_confirmations USING (fid)
- LEFT OUTER JOIN current_planet_stats t ON target = t.id
- WHERE uid = $1 AND f.planet = $2 AND back > tick()
+ FROM launch_confirmations lc
+ LEFT OUTER JOIN current_planet_stats t USING (pid)
+ JOIN fleets f USING (fid)
+ WHERE uid = $1 AND f.pid = $2 AND back > tick()
AND landing_tick - eta - 12 < tick()
)
});
$c->stash(raid => $raid->{id});
my $planet;
if ($c->user->planet){
- my $query = $dbh->prepare("SELECT value, score,x,y FROM current_planet_stats WHERE id = ?");
+ my $query = $dbh->prepare(q{SELECT value, score,x,y FROM current_planet_stats WHERE pid = ?});
$planet = $dbh->selectrow_hashref($query,undef,$c->user->planet);
}
$c->stash(message => parseMarkup($raid->{message}));
$c->stash(landingtick => $raid->{tick});
- my $targetquery = $dbh->prepare(q{SELECT r.id, r.planet, size, score, value
+ my $targetquery = $dbh->prepare(q{SELECT r.id, pid AS planet, size, score, value
, p.x,p.y,p.z, race
, p.value - p.size*200 -
COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150 -
,amps, distorters, light_fac, medium_fac, heavy_fac
,hulls, waves
FROM current_planet_stats p
- JOIN raid_targets r ON p.id = r.planet
- LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet
- LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
+ JOIN raid_targets r USING (pid)
+ LEFT OUTER JOIN current_planet_scans ps USING (pid)
+ LEFT OUTER JOIN current_development_scans ds USING (pid)
WHERE r.raid = $1
AND NOT COALESCE(p.x = $2 AND p.y = $3,False)
ORDER BY size});
my $unitscans = $dbh->prepare(q{
SELECT DISTINCT ON (name) fid, name, tick, amount
FROM fleets
-WHERE planet = ?
+WHERE pid = ?
AND mission = 'Full fleet'
GROUP BY fid,tick,name,amount
ORDER BY name,tick DESC
}
my $targetquery = $dbh->prepare(qq{SELECT r.id,coords(x,y,z),comment,size
- ,score,value,race,planet_status,relationship,r.planet, s.scans
+ ,score,value,race,planet_status,relationship,pid AS planet, s.scans
,COALESCE(max(rc.wave),0) AS waves
FROM raid_targets r
- JOIN current_planet_stats p ON p.id = r.planet
- LEFT OUTER JOIN ( SELECT planet, array_accum(s::text) AS scans
- FROM ( SELECT DISTINCT ON (planet,type) planet,scan_id,type, tick
+ JOIN current_planet_stats p USING (pid)
+ LEFT OUTER JOIN ( SELECT pid, array_agg(s::text) AS scans
+ FROM ( SELECT DISTINCT ON (pid,type) pid,scan_id,type, tick
FROM scans
WHERE tick > tick() - 24
- ORDER BY planet,type ,tick DESC
+ ORDER BY pid,type ,tick DESC
) s
- GROUP BY planet
- ) s ON s.planet = r.planet
+ GROUP BY pid
+ ) s USING (pid)
LEFT OUTER JOIN raid_claims rc ON r.id = rc.target
WHERE r.raid = ?
GROUP BY r.id,x,y,z,comment,size,score,value,race
- ,planet_status,relationship,comment,r.planet, s.scans
+ ,planet_status,relationship,comment,pid, s.scans
,sizerank,scorerank,xprank,valuerank
ORDER BY $order
});
$sizelimit = -1 unless $sizelimit;
my $targets = $c->req->param('targets');
- my $addtarget = $dbh->prepare(qq{INSERT INTO raid_targets(raid,planet) (
- SELECT ?, id FROM current_planet_stats p
+ my $addtarget = $dbh->prepare(q{INSERT INTO raid_targets(raid,pid) (
+ SELECT ?, pid FROM current_planet_stats p
WHERE x = ? AND y = ? AND COALESCE(z = ?,TRUE)
AND p.size > ?
)});
push @errors, $@;
}
}
- if ($c->req->param('alliance') =~ /^(\d+)$/ && $1 != 1){
- my $addtarget = $dbh->prepare(qq{INSERT INTO raid_targets(raid,planet) (
- SELECT ?,id FROM current_planet_stats p WHERE alliance_id = ? AND p.size > ?)
+ if ($c->req->param('alliance') =~ /^(\d+)$/ && $1 ne 'NewDawn'){
+ my $addtarget = $dbh->prepare(q{INSERT INTO raid_targets(raid,pid) (
+ SELECT ?,pid FROM current_planet_stats p WHERE aid= ? AND p.size > ?)
});
eval {
$addtarget->execute($raid,$1,$sizelimit);
- $c->forward('log',[$raid,"BC adding alliance $1 to raid"]);
+ $c->forward('log',[$raid,"BC adding alliance '$1' to raid"]);
};
if ($@ =~ /duplicate key value violates unique constraint "raid_targets_raid_key"/){
push @errors, "A planet from this alliance has already been added to the raid,"
my @gals = $c->req->param('gal');
my @targets = $c->req->param('target');
- my $addtarget = $dbh->prepare(q{INSERT INTO raid_targets(raid,planet) (
- SELECT $1,id FROM current_planet_stats p WHERE (planet_status IN ('','Hostile')
+ my $addtarget = $dbh->prepare(q{INSERT INTO raid_targets(raid,pid) (
+ SELECT $1,pid FROM current_planet_stats p WHERE (planet_status IN ('','Hostile')
AND (relationship IS NULL OR relationship IN ('','Hostile')))
- AND (id = ANY ($2) OR ( size > $4 AND (x,y) IN (
- SELECT x,y FROM current_planet_stats WHERE id = ANY ($3)))
+ AND (pid = ANY ($2) OR ( size > $4 AND (x,y) IN (
+ SELECT x,y FROM current_planet_stats WHERE pid = ANY ($3)))
)
)
});
$order = "p.$order" if $order =~ /rank$/;
my $query = $dbh->prepare(q{
-SELECT p.id, coords(p.x,p.y,p.z),p.x,p.y,p.alliance, p.score, p.value, p.size, p.xp,nfvalue, nfvalue - sum(pa.value) AS nfvalue2, p.race
+SELECT p.pid AS id, coords(p.x,p.y,p.z),p.x,p.y,p.alliance, p.score, p.value, p.size, p.xp,nfvalue, nfvalue - sum(pa.value) AS nfvalue2, p.race
FROM current_planet_stats p
JOIN (SELECT g.x,g.y, sum(p.value) AS nfvalue
FROM galaxies g join current_planet_stats p on g.x = p.x AND g.y = p.y
AND (planet_status IN ('','Hostile')
AND (relationship IS NULL OR relationship IN ('','Hostile')))
GROUP BY g.x,g.y
- ) g ON p.x = g.x AND p.y = g.y
- JOIN current_planet_stats pa ON pa.x = g.x AND pa.y = g.y
+ ) g USING (x,y)
+ JOIN current_planet_stats pa USING (x,y,aid)
WHERE p.x <> 200
- AND p.alliance_id = ANY ($1)
- AND pa.alliance_id = ANY ($1)
+ AND aid = ANY ($1)
AND p.relationship IN ('','Hostile')
-GROUP BY p.id, p.x,p.y,p.z,p.alliance, p.score, p.value, p.size, p.xp, nfvalue,p.race
+GROUP BY p.pid, p.x,p.y,p.z,p.alliance, p.score, p.value, p.size, p.xp, nfvalue,p.race
,p.scorerank,p.valuerank,p.sizerank,p.xprank
ORDER BY
} . $order);
sub listAlliances : Private {
my ($self, $c) = @_;
my @alliances;
- my $query = $c->model->prepare(q{SELECT id,name FROM alliances
+ my $query = $c->model->prepare(q{SELECT aid AS id,alliance AS name FROM alliances
WHERE relationship IN ('','Hostile')
- AND id IN (SELECT alliance_id FROM planets)
- ORDER BY LOWER(name)
+ AND alliance IN (SELECT alliance FROM planets)
+ ORDER BY LOWER(alliance)
});
$query->execute;
- push @alliances,{id => -1, name => ''};
+ push @alliances,{id => '', name => ''};
while (my $ally = $query->fetchrow_hashref){
push @alliances,$ally;
}
$extra_columns = ",planet_status,hit_us, alliance,relationship,nick";
}
- my $query = $dbh->prepare(qq{SELECT id,x,y,z,ruler,planet,race,
+ my $query = $dbh->prepare(qq{SELECT pid AS id,x,y,z,ruler,planet,race,
size, size_gain, size_gain_day,
score,score_gain,score_gain_day,
value,value_gain,value_gain_day,
if ($order =~ /rank$/){
$browse = qq{WHERE $order > ? ORDER BY $order ASC LIMIT 100};
}
- my $query = $dbh->prepare(qq{SELECT a.name,a.id,
+ my $query = $dbh->prepare(qq{SELECT alliance AS name,aid AS id,
size, size_gain, size_gain_day,
score,score_gain,score_gain_day,
avgsize,avgsize_gain,avgsize_gain_day,
FROM raid_claims c
JOIN raid_targets t ON c.target = t.id
JOIN raids r ON t.raid = r.id
- JOIN current_planet_stats p ON t.planet = p.id
+ JOIN current_planet_stats p USING (pid)
WHERE c.uid = $1 AND r.tick+c.wave > tick() AND r.open AND not r.removed
ORDER BY r.tick+c.wave,x,y,z});
$query->execute($c->user->id) or die $dbh->errstr;
sub listAlliances : Private {
my ($self, $c) = @_;
my @alliances;
- push @alliances,{id => -1, name => ''};
- my $query = $c->model->prepare(q{SELECT id,name FROM alliances ORDER BY LOWER(name)});
+ push @alliances,{aid => '', alliance => ''};
+ my $query = $c->model->prepare(q{SELECT aid,alliance FROM alliances ORDER BY LOWER(alliance)});
$query->execute;
while (my $ally = $query->fetchrow_hashref){
push @alliances,$ally;
my $fleetupdate = 0;
if ($c->check_user_roles(qw/member_menu/)){
$fleetupdate = $dbh->selectrow_array(q{
-SELECT tick FROM fleets WHERE planet = ? AND tick > tick() - 24
+SELECT tick FROM fleets WHERE pid = ? AND tick > tick() - 24
AND mission = 'Full fleet' AND name IN ('Main','Advanced Unit');
},undef,$c->user->planet);
$fleetupdate = 0 unless defined $fleetupdate;
SELECT raid,p.tick,x,y,count(*), sum(size) AS size
FROM raids r
JOIN raid_targets rt ON r.id = rt.raid
- JOIN planet_stats p ON p.id = rt.planet AND p.tick = r.tick-12
- WHERE x = $1 and y = $2
+ JOIN planet_stats p USING (pid)
+ WHERE x = $1 and y = $2 AND p.tick = r.tick-12
GROUP BY raid,p.tick,x,y
) AS a
JOIN galaxies g USING (tick,x,y)
};
}
- $query = $dbh->prepare(qq{SELECT DISTINCT ON (x,y,z,p.id)
- p.id,coords(x,y,z), ruler, p.planet,race,
+ $query = $dbh->prepare(qq{SELECT DISTINCT ON (x,y,z,pid)
+ pid AS id,coords(x,y,z), ruler, p.planet,race,
size, size_gain, size_gain_day,
score,score_gain,score_gain_day,
value,value_gain,value_gain_day,
xprank,xprank_gain,xprank_gain_day
$extra_columns
FROM current_planet_stats_full p
- LEFT OUTER JOIN planet_scans ps ON p.id = ps.planet
- LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
+ LEFT OUTER JOIN planet_scans ps USING (pid)
+ LEFT OUTER JOIN current_development_scans ds USING (pid)
WHERE x = ? AND y = ? AND COALESCE(z = ?,TRUE)
- ORDER BY x,y,z,p.id,ps.tick DESC, ps.id DESC, ds.tick DESC, ds.id DESC
+ ORDER BY x,y,z,pid,ps.tick DESC, ps.id DESC, ds.tick DESC, ds.id DESC
});
$query->execute($x,$y,$z);
my ( $self, $c, $id ) = @_;
my $dbh = $c->model;
- my $p = $dbh->selectrow_hashref(q{SELECT id,x,y,z FROM current_planet_stats
- WHERE id = $1},undef,$id);
+ my $p = $dbh->selectrow_hashref(q{SELECT pid AS id,x,y,z FROM current_planet_stats
+ WHERE pid = $1},undef,$id);
$c->detach('/default') unless $p;
my $query = $dbh->prepare(q{
(
SELECT DISTINCT ON (mission,name) fid,mission,name,tick, NULL AS eta
- ,amount, NULL AS coords, planet, NULL AS back
+ ,amount, NULL AS coords, pid AS planet, NULL AS back
FROM fleets f
- WHERE planet = $1 AND tick <= tick() AND (
+ WHERE pid = $1 AND tick <= tick() AND (
fid IN (SELECT fid FROM fleet_scans)
) AND (
mission = 'Full fleet'
) UNION (
SELECT DISTINCT ON (tick,x,y,z,mission,name,amount)
NULL as fid, i.mission, i.name, i.tick,eta
- , i.amount, coords(x,y,z), t.id AS planet, back
+ , i.amount, coords(x,y,z), pid AS planet, back
FROM intel i
- LEFT OUTER JOIN current_planet_stats t ON i.target = t.id
+ LEFT OUTER JOIN current_planet_stats t ON i.target = pid
WHERE uid = -1 AND i.sender = $1 AND i.tick > tick() - 14 AND i.tick < tick() + 14
ORDER BY i.tick,x,y,z,mission,name,amount,back
)
$c->stash(outgoings => \@missions);
$query = $dbh->prepare(q{
- SELECT DISTINCT ON (i.tick,x,y,z,s.id,i.name,i.amount) i.id,i.mission, i.name, i.tick,eta
- , i.amount, coords(x,y,z) AS coords, s.id AS planet
+ SELECT DISTINCT ON (i.tick,x,y,z,pid,i.name,i.amount) i.id,i.mission, i.name, i.tick,eta
+ , i.amount, coords(x,y,z) AS coords, pid AS planet
FROM intel i
LEFT OUTER JOIN (planets
- NATURAL JOIN planet_stats) s ON i.sender = s.id
+ NATURAL JOIN planet_stats) s ON i.sender = pid
AND s.tick = ( SELECT MAX(tick) FROM planet_stats)
WHERE i.uid = -1
AND i.target = ?
AND i.tick > tick() - 3
- ORDER BY i.tick,x,y,z,s.id,i.name,i.amount,i.eta
+ ORDER BY i.tick,x,y,z,pid,i.name,i.amount,i.eta
});
$query->execute($id);
my @incomings;
if ($c->check_user_roles(qw/stats_scans/)){
my $query = $dbh->prepare(q{SELECT type,scan_id, tick FROM scans
- WHERE planet = ? AND tick > tick() - 168
+ WHERE pid = ? AND tick > tick() - 168
ORDER BY tick,type DESC
});
$query->execute($id);
if ($c->check_user_roles(qw/stats_planetdata/)){
$c->stash(planetscan => $dbh->selectrow_hashref(q{SELECT *
- FROM current_planet_scans WHERE planet = $1},undef,$id));
+ FROM current_planet_scans WHERE pid = $1},undef,$id));
$c->stash(devscan => $dbh->selectrow_hashref(q{SELECT *
- FROM current_development_scans WHERE planet = $1},undef,$id));
+ FROM current_development_scans WHERE pid = $1},undef,$id));
}
- my $query = $dbh->prepare(q{SELECT value,value_gain AS gain,tick FROM planet_stats
- WHERE id = ? AND tick > tick() - 24});
+ my $query = $dbh->prepare(q{SELECT value,value_gain AS gain,tick FROM planet_stats
+ WHERE pid = ? AND tick > tick() - 24});
$query->execute($id);
$c->stash(values => $query->fetchall_arrayref({}) );
$query = $dbh->prepare(q{SELECT x,y,z,tick FROM planet_stats
- WHERE id = ? ORDER BY tick ASC});
+ WHERE pid = ? ORDER BY tick ASC});
$query->execute($id);
my @coords;
my $co = {x => 0, y => 0, z => 0};
}elsif (/(\d+)(?: |:)(\d+)/){
$c->res->redirect($c->uri_for('galaxy',$1,$2));
}elsif($c->check_user_roles(qw/stats_find_nick/)) {
- my $query = $dbh->prepare(q{SELECT id,coords(x,y,z),nick
+ my $query = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z),nick
FROM current_planet_stats p
WHERE nick ilike $1
});
my $query = $dbh->prepare(q{SELECT coords(x,y,z), size, score, value,race, COALESCE(nick,'') AS nick
FROM current_planet_stats
- WHERE alliance_id = $1
+ WHERE aid = $1
ORDER BY x,y,z});
$query->execute($ally);
my $column = $1;
my $value = $c->req->param($column);
if ($column eq 'planet'){
+ $column = 'pid';
if ($value eq ''){
$value = undef;
}elsif($value =~ /^(\d+)\D+(\d+)\D+(\d+)$/){
- ($value) = $dbh->selectrow_array(q{SELECT id FROM
+ ($value) = $dbh->selectrow_array(q{SELECT pid FROM
current_planet_stats WHERE x = ? and y = ? and z =?}
,undef,$1,$2,$3);
}
my $dbh = $c->model;
my $query = $dbh->prepare(q{
- SELECT uid,username,hostmask,CASE WHEN u.planet IS NULL THEN '' ELSE coords(x,y,z) END AS planet,attack_points,defense_points,scan_points,humor_points,info, email, sms
- FROM users u LEFT OUTER JOIN current_planet_stats p ON u.planet = p.id
- WHERE uid = ?;
+SELECT uid,username,hostmask,attack_points,defense_points,scan_points,humor_points,info, email, sms
+ ,COALESCE(coords(x,y,z),'') AS planet
+FROM users u LEFT OUTER JOIN current_planet_stats p USING (pid)
+WHERE uid = ?;
});
$user = $dbh->selectrow_hashref($query,undef,$user);
return qq{
SELECT $columns, i.mission, i.tick AS landingtick,MIN(i.eta) AS eta, i.amount, i.ingal, u.username
FROM (intel i NATURAL JOIN users u)
- JOIN current_planet_stats t ON i.target = t.id
- JOIN current_planet_stats o ON i.sender = o.id
-WHERE $where
+ JOIN current_planet_stats t ON i.target = t.pid
+ JOIN current_planet_stats o ON i.sender = o.pid
+WHERE $where
GROUP BY i.tick,i.mission,t.x,t.y,t.z,o.x,o.y,o.z,i.amount,i.ingal,u.username,t.alliance,o.alliance,t.nick,o.nick,i.sender,i.target
ORDER BY i.tick DESC, i.mission};
}
<tr><td>Alliance</td>
<td><select name="alliance">
[% FOR a IN alliances %]
- <option value="[% a.id %]" [% IF a.id == p.alliance_id %]selected[% END %]> [% a.name %]</option>
+ <option value="[% a.alliance %]" [% IF a.aid == p.aid %]selected[% END %]> [% a.alliance %]</option>
[% END %]
</select></td>
<td><input type="checkbox" name="calliance"></td>
-modified = "0000-01-01";
+modified = "0001-01-01";
function parseUpdate(xml){
if ($('timestamp',xml).text())
my $addtarget = $dbh->prepare(q{INSERT INTO raid_targets(raid,planet,comment)
VALUES($1,$2,$3)});
-my $incs = $dbh->prepare(q{SELECT sender,array_accum(i.eta) AS eta,array_accum(amount) AS amount
- ,array_accum(shiptype) AS type,array_accum(fleet) AS name,array_accum(c.landing_tick) AS landing
+my $incs = $dbh->prepare(q{SELECT pid,array_agg(i.eta) AS eta,array_agg(amount) AS amount
+ ,array_agg(shiptype) AS type,array_agg(fleet) AS name,array_agg(c.landing_tick) AS landing
FROM calls c
JOIN incomings i ON i.call = c.id
WHERe NOT c.covered AND c.landing_tick BETWEEN tick() AND tick() + 6
AND c.landing_tick + GREATEST(i.eta,7) > tick() + 10
- GROUP BY sender
+ GROUP BY pid
});
$incs->execute;
my $back = $landing + $eta;
$comment .= "$name: ETA=$eta Amount=$amount Type:'$type' Landing tick=$landing Estimated back:$back\n";
}
- $addtarget->execute($raid,$inc->{sender},$comment);
+ $addtarget->execute($raid,$inc->{pid},$comment);
}
$dbh->commit;
our $dbh = ND::DB::DB();
$dbh->begin_work;
-my $st = $dbh->prepare(q{SELECT uid FROM current_planet_stats p JOIN users u ON p.id = u.planet WHERE alliance_id = 1 ORDER BY score DESC});
+my $st = $dbh->prepare(q{SELECT uid FROM current_planet_stats p JOIN users u USING (pid) WHERE alliance = 'NewDawn' ORDER BY score DESC});
my $update = $dbh->prepare(q{UPDATE users SET rank = ? WHERE uid = ?});
$st->execute;
my $rank = 1;
$alliance->[$i+2] = $rank;
}
}
-my $insert = $dbh->prepare(q{INSERT INTO alliance_stats (tick,id,members,
+my $insert = $dbh->prepare(q{INSERT INTO alliance_stats (tick,aid,members,
size,score,
sizerank,scorerank,
size_gain,score_gain,
members_gain,members_gain_day
) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or die $dbh->errstr;
-my $findalliance = $dbh->prepare(q{SELECT tick,id,members,
+my $findalliance = $dbh->prepare(q{SELECT tick,aid,members,
size, score,
sizerank, scorerank,
size_gain, score_gain,
size_gain_day, score_gain_day,
sizerank_gain_day, scorerank_gain_day,
members_gain,members_gain_day
-FROM alliance_stats WHERE id = $1 AND tick < $2 ORDER BY tick DESC LIMIT 1}) or die $dbh->errstr;
+FROM alliance_stats WHERE aid = $1 AND tick < $2 ORDER BY tick DESC LIMIT 1}) or die $dbh->errstr;
for my $alliance (@alliances) {
}
}
-my $findplanets = $dbh->prepare(q{SELECT tick,id, x, y, z,
- size, score, value, xp,
- sizerank, scorerank, valuerank, xprank,
- size_gain, score_gain, value_gain, xp_gain,
- sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
- size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
+my $findplanets = $dbh->prepare(q{SELECT tick,pid, x, y, z,
+ size, score, value, xp,
+ sizerank, scorerank, valuerank, xprank,
+ size_gain, score_gain, value_gain, xp_gain,
+ sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
+ size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
sizerank_gain_day, scorerank_gain_day, valuerank_gain_day, xprank_gain_day
FROM planet_stats WHERE tick = (SELECT MAX(tick) FROM planet_stats WHERE tick < $1)});
-my $insert = $dbh->prepare(q{INSERT INTO planet_stats(tick,id, x, y, z,
+my $insert = $dbh->prepare(q{INSERT INTO planet_stats(tick,pid, x, y, z,
size, score, value,xp,
sizerank,scorerank,valuerank,xprank,
size_gain, score_gain, value_gain, xp_gain,
while (my @planet = $findplanets->fetchrow){
$oldStats{$planet[1]} = \@planet;
}
+my $intel = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
+ (SELECT ftid FROM planets WHERE pid = $2),$1,$3)});
for my $planet (@planets) {
#print "$planet->[1]\n";
my $oldPlanet = $oldStats{$planet->[1]};
($planet->[3] != $oldPlanet->[3]) or
($planet->[4] != $oldPlanet->[4])){
#print "Planet has moved from $oldPlanet[2]:$oldPlanet[3]:$oldPlanet[4] to $planet->[2]:$planet->[3]:$planet->[4]\n";
- intel_log -3, $planet->[1],"Planet has moved from $oldPlanet->[2]:$oldPlanet->[3]:$oldPlanet->[4] to $planet->[2]:$planet->[3]:$planet->[4] tick $tick";
+ $intel->execute(-3, $planet->[1],"Planet has moved from $oldPlanet->[2]:$oldPlanet->[3]:$oldPlanet->[4] to $planet->[2]:$planet->[3]:$planet->[4] tick $tick");
}
}
#print "@{$oldPlanet}\n";
});
my $oldscan = $dbh->prepare(q{SELECT scan_id FROM scans WHERE scan_id = LOWER(?) AND tick >= tick() - 168});
my $addScan = $dbh->prepare(q{INSERT INTO scans (scan_id,tick,uid) VALUES (LOWER(?),?,?)});
-my $parsedscan = $dbh->prepare(q{UPDATE scans SET tick = ?, type = ?, planet = ?, parsed = TRUE WHERE id = ?});
+my $parsedscan = $dbh->prepare(q{UPDATE scans SET tick = ?, type = ?, pid = ?, parsed = TRUE WHERE id = ?});
my $addpoints = $dbh->prepare(q{UPDATE users SET scan_points = scan_points + ? WHERE uid = ? });
my $delscan = $dbh->prepare(q{DELETE FROM scans WHERE id = ?});
WHERE NOT groupscan AND NOT parsed FOR UPDATE
});
my $findplanet = $dbh->prepare(q{SELECT planetid(?,?,?,?)});
-my $findoldplanet = $dbh->prepare(q{SELECT id FROM planet_stats WHERE x = $1 AND y = $2 AND z = $3 AND tick <= $4 ORDER BY tick DESC LIMIT 1});
+my $findoldplanet = $dbh->prepare(q{SELECT pid FROM planet_stats WHERE x = $1 AND y = $2 AND z = $3 AND tick <= $4 ORDER BY tick DESC LIMIT 1});
my $findcoords = $dbh->prepare(q{SELECT * FROM planetcoords(?,?)});
-my $addfleet = $dbh->prepare(q{INSERT INTO fleets (name,mission,planet,tick,amount) VALUES(?,?,?,?,?) RETURNING fid});
+my $addfleet = $dbh->prepare(q{INSERT INTO fleets (name,mission,pid,tick,amount) VALUES(?,?,?,?,?) RETURNING fid});
my $fleetscan = $dbh->prepare(q{INSERT INTO fleet_scans (fid,id) VALUES(?,?)});
my $addintel = $dbh->prepare(q{INSERT INTO intel (name,mission,sender,target,tick,eta,back,amount,ingal,uid)
VALUES(?,?,?,?,?,?,?,?,?,-1) RETURNING id});
my $intelscan = $dbh->prepare(q{INSERT INTO intel_scans (intel,id) VALUES(?,?)});
my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (fid,ship,amount) VALUES(?,?,?)});
my $addplanetscan = $dbh->prepare(q{INSERT INTO planet_scans
- (id,tick,planet,metal_roids,metal,crystal_roids,crystal,eonium_roids,eonium
+ (id,tick,pid,metal_roids,metal,crystal_roids,crystal,eonium_roids,eonium
,agents,guards,light,medium,heavy,hidden)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)});
sub parse_planet {
my ($scan,$file) = @_;
- my @values = ($scan->{id},$scan->{tick},$scan->{planet});
+ my @values = ($scan->{id},$scan->{tick},$scan->{pid});
$file =~ s/(\d),(\d)/$1$2/g;
while($file =~ m{"center">(Metal|Crystal|Eonium)</td>\D+(\d+)\D+([\d,]+)}g){
while($file =~ m{class="left">Fleet:\s(.*?)</td><td\sclass="right">
Mission:\s(\w+)</td></tr>(.*?)Total\sShips:\s(\d+)}sxg){
- my $id = addfleet($1,$2,$3,$scan->{planet},$scan->{tick},$4);
+ my $id = addfleet($1,$2,$3,$scan->{pid},$scan->{tick},$4);
$fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
}
}
sub parse_unit {
my ($scan,$file) = @_;
- my $id = addfleet($scan->{type},'Full fleet',$file,$scan->{planet},$scan->{tick});
+ my $id = addfleet($scan->{type},'Full fleet',$file,$scan->{pid},$scan->{tick});
$fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
}
my ($sender) = $dbh->selectrow_array($findplanet,undef,$1,$2,$3,$scan->{tick});
($sender) = $dbh->selectrow_array($findoldplanet,undef,$1,$2,$3,$scan->{tick})
if ((not defined $sender) && $4 eq 'Return');
- my $id = addintel($5,$4,$sender,$scan->{planet},$scan->{tick}+$6,$6
+ my $id = addintel($5,$4,$sender,$scan->{pid},$scan->{tick}+$6,$6
,undef,$7, $scan->{x} == $1 && $scan->{y} == $2);
$intelscan->execute($id,$scan->{id});
}
}
my $adddevscan = $dbh->prepare(q{INSERT INTO development_scans
- (id,tick,planet,light_fac,medium_fac,heavy_fac,amps,distorters
+ (id,tick,pid,light_fac,medium_fac,heavy_fac,amps,distorters
,metal_ref,crystal_ref,eonium_ref,reslabs,fincents,seccents
,travel,infra,hulls,waves,extraction,covert,mining,total)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
next;
}
my ($planet) = $dbh->selectrow_array($findplanet,undef,$x,$y,$z,$tick);
- $scan->{planet} = $planet;
+ $scan->{pid} = $planet;
unless ($planet){
$dbh->pg_rollback_to('scans') or die "rollback didn't work";
if ( $x == 0 && $y == 0 && $z == 0 ){
projects / ndwebbie.git / commitdiff