database/groupschannels.sql

   1 DROP VIEW usersingroup;
   2 DROP VIEW full_defcalls;
   3 DROP VIEW defcalls;
   4 DROP VIEW users_defprio;
   5 DROP VIEW available_ships;
   6 DROP VIEW ships_home;
   7
   8 ALTER TABLE groups RENAME gid TO id;
   9 ALTER TABLE groups RENAME flag TO gid;
  10
  11 UPDATE groups SET gid = '' WHERE gid IS NULL;
  12
  13 CREATE FUNCTION new_gid(id INTEGER) RETURNS CHAR AS $$
  14         SELECT gid FROM groups WHERE id = $1
  15 $$ LANGUAGE SQL STABLE;
  16
  17 ALTER TABLE channel_flags ADD flag CHAR(1);
  18
  19 UPDATE channel_flags SET flag = 'o' WHERE name = 'auto_op';
  20 UPDATE channel_flags SET flag = 'O' WHERE name = 'op';
  21 UPDATE channel_flags SET flag = 'v' WHERE name = 'auto_voice';
  22 UPDATE channel_flags SET flag = 'V' WHERE name = 'voice';
  23 UPDATE channel_flags SET flag = 'i' WHERE name = 'auto_invite';
  24 UPDATE channel_flags SET flag = 'I' WHERE name = 'invite';
  25
  26 CREATE FUNCTION new_flag(id INTEGER) RETURNS CHAR AS $$
  27         SELECT flag FROM channel_flags WHERE id = $1
  28 $$ LANGUAGE SQL STABLE;
  29
  30 ALTER TABLE channels RENAME name TO channel;
  31 ALTER TABLE channels ALTER channel TYPE citext;
  32
  33 CREATE FUNCTION new_channel(id INTEGER) RETURNS citext AS $$
  34         SELECT channel FROM channels WHERE id = $1
  35 $$ LANGUAGE SQL STABLE;
  36
  37 ALTER TABLE channel_group_flags DROP CONSTRAINT channel_group_flags_group_fkey;
  38 ALTER TABLE channel_group_flags DROP CONSTRAINT channel_group_flags_flag_fkey;
  39 ALTER TABLE channel_group_flags DROP CONSTRAINT channel_group_flags_channel_fkey;
  40 ALTER TABLE group_roles DROP CONSTRAINT group_roles_gid_fkey;
  41 ALTER TABLE groupmembers DROP CONSTRAINT groupmembers_gid_fkey;
  42 ALTER TABLE raid_access DROP CONSTRAINT raid_access_gid_fkey;
  43 ALTER TABLE forum_access DROP CONSTRAINT forum_access_gid_fkey;
  44 ALTER TABLE wiki_namespace_access DROP CONSTRAINT wiki_namespace_access_gid_fkey;
  45
  46 ALTER TABLE groups DROP CONSTRAINT groups_pkey;
  47 ALTER TABLE groups ADD PRIMARY KEY(gid);
  48
  49 ALTER TABLE channel_flags DROP CONSTRAINT channel_flags_pkey;
  50 ALTER TABLE channel_flags ADD PRIMARY KEY(flag);
  51
  52 ALTER TABLE channels DROP CONSTRAINT channels_pkey;
  53 ALTER TABLE channels DROP CONSTRAINT channels_name_key;
  54 ALTER TABLE channels ADD PRIMARY KEY(channel);
  55
  56 ALTER TABLE channel_group_flags RENAME "group" TO gid;
  57 ALTER TABLE channel_group_flags ALTER flag TYPE CHAR(1) USING (new_flag(flag));
  58 ALTER TABLE channel_group_flags ADD FOREIGN KEY (flag) REFERENCES channel_flags(flag) ON UPDATE CASCADE ON DELETE CASCADE;
  59 ALTER TABLE channel_group_flags ALTER gid TYPE CHAR(1) USING (new_gid(gid));
  60 ALTER TABLE channel_group_flags ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
  61 ALTER TABLE channel_group_flags ALTER channel TYPE citext USING (new_channel(channel));
  62 ALTER TABLE channel_group_flags ADD FOREIGN KEY (channel) REFERENCES channels(channel) ON UPDATE CASCADE ON DELETE CASCADE;
  63
  64 ALTER TABLE groupmembers ALTER gid TYPE CHAR(1) USING (new_gid(gid));
  65 ALTER TABLE groupmembers ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
  66
  67 ALTER TABLE group_roles ALTER gid TYPE CHAR(1) USING (new_gid(gid));
  68 ALTER TABLE group_roles ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
  69
  70 ALTER TABLE raid_access ALTER gid TYPE CHAR(1) USING (new_gid(gid));
  71 ALTER TABLE raid_access ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
  72
  73 ALTER TABLE forum_access ALTER gid TYPE CHAR(1) USING (new_gid(gid));
  74 ALTER TABLE forum_access ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
  75
  76 ALTER TABLE wiki_namespace_access ALTER gid TYPE CHAR(1) USING (new_gid(gid));
  77 ALTER TABLE wiki_namespace_access ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
  78
  79 ALTER TABLE groups DROP id RESTRICT;
  80 ALTER TABLE groups DROP attack RESTRICT;
  81 ALTER TABLE channel_flags DROP id RESTRICT;
  82 ALTER TABLE channels DROP id RESTRICT;
  83
  84 DROP FUNCTION new_gid(int);
  85 DROP FUNCTION new_flag(int);
  86 DROP FUNCTION new_channel(int);
  87
  88 DROP INDEX users_pnick_key;
  89 DROP INDEX users_hostmask_key;
  90 DROP INDEX users_username_key;
  91
  92 ALTER TABLE users ALTER username TYPE citext;
  93 ALTER TABLE users ALTER pnick TYPE citext;
  94 ALTER TABLE users ALTER hostmask TYPE citext;
  95
  96 ALTER TABLE users ALTER pnick SET NOT NULL;
  97 ALTER TABLE users ALTER hostmask SET NOT NULL;
  98
  99 ALTER TABLE users ADD UNIQUE(username);
 100 ALTER TABLE users ADD UNIQUE(pnick);
 101 ALTER TABLE users ADD UNIQUE(hostmask);
 102
 103
 104 CREATE OR REPLACE VIEW users_defprio AS
 105 SELECT u.*, (0.2 * (u.attack_points / GREATEST(a.attack, 1::numeric))
 106                 + 0.4 * (u.defense_points / GREATEST(a.defense, 1::numeric))
 107                 + 0.2 * (p.size::numeric / a.size) + 0.05 * (p.score::numeric / a.score)
 108                 + 0.15 * (p.value::numeric / a.value))::numeric(3,2) AS defprio
 109 FROM users u
 110         JOIN current_planet_stats p USING (pid)
 111         , (
 112                 SELECT avg(u.attack_points) AS attack, avg(u.defense_points) AS defense
 113                         ,avg(p.size) AS size, avg(p.score) AS score, avg(p.value) AS value
 114                 FROM users u
 115                         JOIN current_planet_stats p USING (pid)
 116                 WHERE uid IN ( SELECT uid FROM groupmembers WHERE gid = 'M')
 117         ) a;
 118
 119 CREATE OR REPLACE VIEW defcalls AS
 120 SELECT call, status,c.uid, c.landing_tick
 121         ,dc.username AS dc, (c.landing_tick - tick()) AS curreta
 122         ,array_agg(COALESCE(race::text,'')) AS race
 123         ,array_agg(COALESCE(amount,0)) AS amount
 124         ,array_agg(COALESCE(eta,0)) AS eta
 125         ,array_agg(COALESCE(shiptype,'')) AS shiptype
 126         ,array_agg(COALESCE(alliance,'?')) AS alliance
 127         ,array_agg(coords(p2.x,p2.y,p2.z)) AS attackers
 128 FROM calls c
 129         LEFT OUTER JOIN incomings i USING (call)
 130         LEFT OUTER JOIN current_planet_stats p2 USING (pid)
 131         LEFT OUTER JOIN users dc ON c.dc = dc.uid
 132 GROUP BY call,c.uid,dc.username, c.landing_tick, status;
 133
 134 CREATE OR REPLACE VIEW full_defcalls AS
 135 SELECT call,status,x,y,z,pid,landing_tick,dc,curreta
 136         ,defprio, c.race, amount, c.eta, shiptype, c.alliance, attackers
 137         ,COUNT(NULLIF(f.back = f.landing_tick + f.eta - 1, FALSE)) AS fleets
 138 FROM users_defprio u
 139         JOIN current_planet_stats p USING (pid)
 140         JOIN defcalls c USING (uid)
 141         LEFT OUTER JOIN launch_confirmations f USING (pid,landing_tick)
 142 GROUP BY call, x,y,z,pid,landing_tick,dc,curreta,defprio,c.race,amount,c.eta,shiptype,c.alliance,attackers, status
 143 ;
 144
 145
 146 CREATE OR REPLACE FUNCTION change_member() RETURNS trigger
 147     AS $_X$
 148 BEGIN
 149         IF TG_OP = 'INSERT' THEN
 150                 IF NEW.gid = 'M' THEN
 151                         UPDATE planets SET alliance = 'NewDawn' WHERE
 152                                 pid = (SELECT pid FROM users WHERE uid = NEW.uid);
 153                 END IF;
 154         ELSIF TG_OP = 'DELETE' THEN
 155                 IF OLD.gid = 'M' THEN
 156                         UPDATE planets SET alliance = NULL WHERE
 157                                 pid = (SELECT pid FROM users WHERE uid = OLD.uid);
 158                 END IF;
 159         END IF;
 160
 161         return NEW;
 162 END;
 163 $_X$ LANGUAGE plpgsql;
 164
 165 CREATE OR REPLACE FUNCTION update_user_planet() RETURNS trigger AS $_X$
 166 BEGIN
 167         IF COALESCE(NEW.pid <> OLD.pid,TRUE) OR NEW.username <> OLD.username THEN
 168                 UPDATE planets SET nick = NULL WHERE pid = OLD.pid;
 169                 UPDATE planets SET nick = NEW.username WHERE pid = NEW.pid;
 170         END IF;
 171
 172         IF COALESCE(NEW.pid <> OLD.pid,TRUE)
 173                         AND (SELECT TRUE FROM groupmembers WHERE gid = 'M' AND uid = NEW.uid) THEN
 174                 UPDATE planets SET alliance = NULL WHERE pid = OLD.pid;
 175                 UPDATE planets SET alliance = 'NewDawn' WHERE pid = NEW.pid;
 176         END IF;
 177         RETURN NEW;
 178 END;
 179 $_X$ LANGUAGE plpgsql;
 180
 181 DROP FUNCTION groups(int);
 182 CREATE OR REPLACE FUNCTION groups(uid integer) RETURNS SETOF CHAR
 183     AS $_$SELECT gid FROM groupmembers WHERE uid = $1 UNION SELECT ''$_$
 184     LANGUAGE sql STABLE;