-CREATE TABLE ticks (t INTEGER PRIMARY KEY);
+/*CREATE TABLE ticks (t INTEGER PRIMARY KEY);
INSERT INTO ticks (SELECT * FROM generate_series(0,10000));
+*/
CREATE OR REPLACE VIEW ships_home AS
-SELECT tick, uid,username, planet, ship
+SELECT tick, uid,username, pid, ship
, COALESCE(f.amount - o.amount,f.amount) AS amount
, COALESCE(fleets,3) AS fleets
FROM users u JOIN (
- SELECT t AS tick, planet,ship,amount
+ SELECT t AS tick, pid,ship,amount
FROM (
- SELECT DISTINCT ON (t,planet,mission) t,planet,mission, fid
+ SELECT DISTINCT ON (t,pid,mission) t,pid,mission, fid
FROM ticks
CROSS JOIN fleets f
WHERE tick <= t
AND name IN ('Main','Advanced Unit')
AND mission = 'Full fleet'
- ORDER BY t,planet,mission,tick DESC, fid DESC
+ ORDER BY t,pid,mission,tick DESC, fid DESC
) f
JOIN fleet_ships fs USING (fid)
-) f USING (planet) LEFT OUTER JOIN (
- SELECT t AS tick, planet, ship, SUM(fs.amount) AS amount
+) f USING (pid) LEFT OUTER JOIN (
+ SELECT t AS tick, pid, ship, SUM(fs.amount) AS amount
, 3 - COUNT(DISTINCT fid) AS fleets
FROM ticks
CROSS JOIN fleets f
- JOIN launch_confirmations USING (fid)
+ JOIN (SELECT landing_tick, fid, back, eta FROM launch_confirmations) lc USING (fid)
JOIN fleet_ships fs USING (fid)
WHERE back > t
AND landing_tick - eta - 12 < t
- GROUP BY t,planet,ship
-) o USING (tick,planet,ship)
+ GROUP BY t,pid,ship
+) o USING (tick,pid,ship)
WHERE COALESCE(f.amount - o.amount,f.amount) > 0;
CREATE OR REPLACE VIEW available_ships AS
-SELECT uid,username, planet, ship, amount, fleets
+SELECT uid,username, pid, ship, amount, fleets
FROM ships_home
WHERE tick = tick();
--- /dev/null
+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;
if (exists $authinfo->{id}){
$self->{id} = $dbh->selectrow_array(q{
- SELECT uid FROM users WHERE lower(username) = lower(?)
+ SELECT uid FROM users WHERE username = ?
},undef,$authinfo->{id});
}elsif (exists $authinfo->{uid}){
$self->{id} = $authinfo->{uid};
}
my $available = $dbh->prepare(q{
-SELECT ship,amount from ships_home WHERE planet = $1 AND tick = $2
+SELECT ship,amount from ships_home WHERE pid = $1 AND tick = $2
});
$available->execute($call->{pid}, $call->{landing_tick});
my $fleet = {fid => $call->{member}, mission => 'Available'
my $query = $dbh->prepare(q{SELECT uid,username FROM users
WHERE uid > 0 AND uid IN (SELECT uid FROM groupmembers)
- ORDER BY LOWER(username)});
+ ORDER BY username});
$query->execute;
$c->stash(users => $query->fetchall_arrayref({}) );
JOIN raid_targets rt ON r.id = rt.raid) rt ON rt.pid = i.target
AND (rt.tick + 12) > i.tick AND rt.tick <= i.tick
LEFT OUTER JOIN raid_claims rc ON rt.id = rc.target AND rc.uid = u.uid AND i.tick = rt.tick + rc.wave - 1
- WHERE gm.gid = 2
+ WHERE gm.gid = 'M'
GROUP BY u.uid,u.username,u.attack_points, u.defense_points,n.tick
ORDER BY }. " $order DESC" );
$query->execute;
,(attack_points+defense_points+scan_points/20)::NUMERIC(4,0) as total_points
, count(NULLIF(rc.launched,FALSE)) AS raid_points
FROM users_defprio u LEFT OUTER JOIN raid_claims rc USING (uid)
-WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 2)
+WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
GROUP BY username,defense_points,attack_points,scan_points,humor_points,defprio
ORDER BY } . "$order $limit"
);
,sms_note, call_if_needed, race, timezone
FROM users u
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)
+WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
+ORDER BY call_if_needed DESC, username
});
$defenders->execute;
my $available = $dbh->prepare(q{
-SELECT ship,amount FROM available_ships WHERE planet = $1
+SELECT ship,amount FROM available_ships WHERE pid = $1
});
my @members;
$c->stash(errors => $c->flash->{errors});
- my $groups = $dbh->prepare(q{SELECT g.gid,g.groupname,raid FROM groups g LEFT OUTER JOIN (SELECT gid,raid FROM raid_access WHERE raid = ?) AS ra ON g.gid = ra.gid WHERE g.attack});
+ my $groups = $dbh->prepare(q{
+SELECT g.gid,g.groupname,raid
+FROM groups g
+ LEFT OUTER JOIN (SELECT gid,raid FROM raid_access WHERE raid = ?) AS ra USING (gid)
+WHERE gid IN (SELECT gid FROM group_roles WHERE role = 'attack_menu')
+ });
$groups->execute($raid ? $raid->{id} : undef);
my @addgroups;
$c->forward('log',[$raid, 'BC updated raid']);
- my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE attack});
- my $delgroup = $dbh->prepare(q{DELETE FROM raid_access WHERE raid = ? AND gid = ?});
- my $addgroup = $dbh->prepare(q{INSERT INTO raid_access (raid,gid) VALUES(?,?)});
+ my $delgroups = $dbh->prepare(q{DELETE FROM raid_access WHERE raid = $1 AND gid = ANY($2)});
+ my $addgroups = $dbh->prepare(q{INSERT INTO raid_access (raid,gid) VALUES($1,unnest($2::text[]))});
- $groups->execute();
- while (my $group = $groups->fetchrow_hashref){
- my $query;
- next unless defined $c->req->param($group->{gid});
- my $command = $c->req->param($group->{gid});
- if ( $command eq 'remove'){
- $query = $delgroup;
- }elsif($command eq 'add'){
- $query = $addgroup;
- }
- if ($query){
- $query->execute($raid,$group->{gid});
- $c->forward('log',[$raid, "BC '$command' access for $group->{gid} ($group->{groupname})"]);
- }
+ if ($c->req->param('add_group')){
+ my @groups = $c->req->param('add_group');
+ warn "GROUPS!!!!: @groups";
+ $addgroups->execute($raid,\@groups);
+ $c->forward('log',[$raid, "BC added access to groups: @groups"]);
+ }
+ if ($c->req->param('remove_group')){
+ my @groups = $c->req->param('remove_group');
+ $delgroups->execute($raid,\@groups);
+ $c->forward('log',[$raid, "BC removed access for groups: @groups"]);
}
$dbh->commit;
$addtarget->execute($raid,\@targets,\@gals,$c->req->param('sizelimit'));
$c->forward('log',[$raid,"BC added planets (@targets) and the gals for (@gals)"]);
}
- $dbh->do(q{INSERT INTO raid_access (raid,gid) VALUES(?,2)}
+ $dbh->do(q{INSERT INTO raid_access (raid,gid) VALUES(?,'M')}
,undef,$raid);
$dbh->commit;
my ( $self, $c ) = @_;
my $dbh = $c->model;
- my $query = $dbh->prepare(qq{SELECT u.uid,username,TRIM(',' FROM concat(g.groupname||',')) AS groups
- FROM users u LEFT OUTER JOIN (groupmembers gm NATURAL JOIN groups g) ON gm.uid = u.uid
- WHERE u.uid > 0
+ my $query = $dbh->prepare(qq{SELECT uid,username,array_to_string(array_agg(g.groupname),', ') AS groups
+ FROM users u LEFT OUTER JOIN (groupmembers gm NATURAL JOIN groups g) USING (uid)
+ WHERE uid > 0
GROUP BY u.uid,username
- ORDER BY lower(username)});
+ ORDER BY username});
$query->execute;
my @users;
$c->forward('findUser');
$user = $c->stash->{u};
- my $groups = $dbh->prepare(q{SELECT g.gid,g.groupname,uid
- FROM groups g
- LEFT OUTER JOIN (SELECT gid,uid FROM groupmembers WHERE uid = ?)
- AS gm ON g.gid = gm.gid
+ my $groups = $dbh->prepare(q{
+SELECT g.gid,g.groupname,uid
+FROM groups g
+ LEFT OUTER JOIN (SELECT gid,uid FROM groupmembers WHERE uid = ?)
+ AS gm USING(gid)
+WHERE gid <> ''
});
$groups->execute($user->{uid});
(SELECT ftid FROM users WHERE uid = $1),$1,$2)
});
- my $delgroup = $dbh->prepare(q{DELETE FROM groupmembers WHERE uid = ? AND gid = ?});
- my $addgroup = $dbh->prepare(q{INSERT INTO groupmembers (uid,gid) VALUES(?,?)});
+ my $delgroups = $dbh->prepare(q{DELETE FROM groupmembers WHERE uid = $1 AND gid = ANY($2) });
+ my $addgroups = $dbh->prepare(q{INSERT INTO groupmembers (uid,gid) (SELECT $1,unnest($2::text[]))});
for my $param ($c->req->param()){
if ($param =~ /^c:(planet|\w+_points|hostmask|info|username|email|sms)$/){
my $column = $1;
$dbh->do(qq{UPDATE users SET $column = ? WHERE uid = ? }
,undef,$value,$user->{uid});
$log->execute($c->user->id,"HC changed $column from $user->{$column} to $value for user: $user->{uid} ($user->{username})");
- }elsif ($param =~ /^gr:(\d+)$/){
- my $query;
- if ($c->req->param($param) eq 'remove'){
- $query = $delgroup;
- }elsif($c->req->param($param) eq 'add'){
- $query = $addgroup;
- }
- if ($query){
- $query->execute($user->{uid},$1);
- my ($action,$a2) = ('added','to');
- ($action,$a2) = ('removed','from') if $c->req->param($param) eq 'remove';
- $log->execute($c->user->id,"HC $action user: $user->{uid} ($user->{username}) $a2 group: $1");
- }
+ }elsif ($param eq 'add_group'){
+ my @groups = $c->req->param($param);
+ $addgroups->execute($user->{uid},\@groups);
+ $log->execute($c->user->id,"HC added user: $user->{uid} ($user->{username}) to groups: @groups");
+ }elsif ($param eq 'remove_group'){
+ my @groups = $c->req->param($param);
+ $delgroups->execute($user->{uid},\@groups);
+ $log->execute($c->user->id,"HC removed user: $user->{uid} ($user->{username}) from groups: @groups");
}
}
$dbh->commit;
$c->stash(subject => $c->flash->{subject});
$c->stash(message => $c->flash->{message});
- my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE gid > 0 ORDER BY gid});
+ my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE gid <> '' ORDER BY gid});
$groups->execute;
my @groups;
push @groups,{gid => -1, groupname => 'Pick a group'};
<tr><th>Group</th><th>Remove</th></tr>
[% FOR g IN removegroups %]
<tr><td>[% g.groupname %]</td><td><input type="checkbox"
- name="[% g.gid %]" value="remove"></td>
+ name="remove_group" value="[% g.gid %]"></td>
</tr>
[% END %]
</table>
<tr><th>Group</th><th>Add</th></tr>
[% FOR g IN addgroups %]
<tr><td>[% g.groupname %]</td><td><input type="checkbox"
- name="[% g.gid %]" value="add"></td>
+ name="add_group" value="[% g.gid %]"></td>
</tr>
[% END %]
</table>
<table>
<tr><th>Group</th><th>Remove</th></tr>
[% FOR g IN membergroups %]
- <tr><td>[% g.groupname %]</td><td><input type="checkbox" name="gr:[% g.gid %]" value="remove"></td></tr>
+ <tr><td>[% g.groupname %]</td><td><input type="checkbox" name="remove_group" value="[% g.gid %]"></td></tr>
[% END %]
</table>
[% END %]
<table>
<tr><th>Group</th><th>Add</th></tr>
[% FOR g IN othergroups %]
- <tr><td>[% g.groupname %]</td><td><input type="checkbox" name="gr:[% g.gid %]" value="add"></td></tr>
+ <tr><td>[% g.groupname %]</td><td><input type="checkbox" name="add_group" value="[% g.gid %]"></td></tr>
[% END %]
</table>
[% END %]
projects / ndwebbie.git / commitdiff