ipd_views.sql

DROP VIEW IF EXISTS ipd_ips;
DROP VIEW IF EXISTS ipd_dested_players;
DROP VIEW IF EXISTS ipd_timeout_events;
DROP VIEW IF EXISTS ipd_player_replace_events;
DROP VIEW IF EXISTS ipd_quit_events;
DROP VIEW IF EXISTS ipd_in_out;
DROP VIEW IF EXISTS ipd_user_logouts;
DROP VIEW IF EXISTS ipd_user_logins;
DROP VIEW IF EXISTS ipd_pretty_associations;
DROP VIEW IF EXISTS ipd_ip_popularity;
  
-- A view of every unique IP that has visited Dune
CREATE VIEW ipd_ips AS SELECT DISTINCT ip FROM ipd_associations;

-- A view of only the DEST game disconnects
-- These occur when a player is forcefully disconnected by an administrator
CREATE VIEW ipd_dested_players AS 
  SELECT ipd_users.username, ipd_logouts.when, ipd_logouts.disconnectType 
  FROM ipd_logouts LEFT JOIN ipd_users ON ipd_users.id = ipd_logouts.user_id 
  WHERE ipd_logouts.disconnectType = 'DEST';

-- A view of only the TIMEOUT game disconnects
-- These occur after being linkdead too long
CREATE VIEW ipd_timeout_events AS 
  SELECT ipd_users.username, ipd_logouts.when, ipd_logouts.disconnectType 
  FROM ipd_logouts LEFT JOIN ipd_users ON ipd_users.id = ipd_logouts.user_id 
  WHERE ipd_logouts.disconnectType = 'TIMEOUT';

-- A view of only REPLACEMENT game disconnects
-- These occur when a player is replaced by another login to their account
CREATE VIEW ipd_player_replace_events AS 
  SELECT ipd_users.username, ipd_logouts.when, ipd_logouts.disconnectType 
  FROM ipd_logouts LEFT JOIN ipd_users ON ipd_users.id = ipd_logouts.user_id 
  WHERE ipd_logouts.disconnectType = 'REPLACEMENT';

-- A view of only the QUIT game disconnects
-- These are considered the 'normal' disconnects when a player chooses to end
-- their session.
CREATE VIEW ipd_quit_events AS 
  SELECT ipd_users.username, ipd_logouts.when, ipd_logouts.disconnectType 
  FROM ipd_logouts LEFT JOIN ipd_users ON ipd_users.id = ipd_logouts.user_id 
  WHERE ipd_logouts.disconnectType = 'NORMAL';

-- A view of full "sessions". I.e. a player login time and then their logout time.
-- Note: crashes and other events that cause no logout event to register may make
-- the next login session appear longer. These "ghost sessions" are hard to avoid
-- without session ids and a lot of work.
CREATE VIEW ipd_in_out AS
 SELECT ipd_users.username, ipd_logins.when AS `in`, ipd_logouts.when AS `out`, 
	TIMEDIFF(ipd_logouts.when,ipd_logins.when) AS `duration` 
 FROM ipd_users
 LEFT JOIN ipd_logins ON ipd_users.id = ipd_logins.user_id
 LEFT JOIN ipd_logouts ON ipd_users.id = ipd_logouts.user_id
 WHERE ipd_logouts.id = (
   SELECT id FROM ipd_logouts WHERE ipd_logouts.when > ipd_logins.when AND 
				    ipd_logouts.user_id = ipd_logins.user_id 
   ORDER BY ipd_logouts.when ASC LIMIT 1
 ) ORDER BY username;

-- A view of username -> logout date that has the user_id replaced with the
-- actual user's username
CREATE VIEW ipd_user_logouts AS
  SELECT ipd_users.username, ipd_logouts.when
  FROM ipd_logouts LEFT JOIN ipd_users ON ipd_users.id = ipd_logouts.user_id
  ORDER BY username, `when` DESC;

-- A view of username -> login date that has the user_id replaced with the
-- actual user's username
CREATE VIEW ipd_user_logins AS
  SELECT ipd_users.username, ipd_logins.when
  FROM ipd_logins LEFT JOIN ipd_users ON ipd_users.id = ipd_logins.user_id
  ORDER BY username, `when` DESC;

-- A view of the IP -> username associations where the IP has been converted to
-- dotted quad and the user_id has been replaced by the user's actual username.
CREATE VIEW ipd_pretty_associations AS
  SELECT INET_NTOA(ipd_associations.ip) AS `ip`, ipd_users.username, ipd_associations.count
  FROM ipd_associations LEFT JOIN ipd_users ON ipd_users.id = ipd_associations.user_id;

-- A view of IP -> # of characters that have used it
CREATE VIEW ipd_ip_popularity AS
  SELECT INET_NTOA(ip) as `ip`, COUNT(DISTINCT ipd_associations.user_id) AS `characters`
  FROM ipd_associations GROUP BY ip;