ipd_procedures.sql

DROP PROCEDURE IF EXISTS log_disconnect;
DROP PROCEDURE IF EXISTS log_connection;

DELIMITER //
-- The log_connection() procedure is called when a user connects to the game.
-- It accepts the username that is connecting, and the ip they are connecting 
-- from. It then adds the user if their username is new, associates the 
-- username with the IP making sure to update the count of how many times 
-- they've connected from that IP. Finally it adds a login for this user at 
-- the present time/date.
--
-- This procedure will additionally attempt to note the country the IP is 
-- located in by means of the geo_ip_country table. This query is slow(ish) for
-- large number of procedure calls. fast_log_connection() should be used when
-- a great number of connections are being processed at once (i.e. a data set
-- import).
CREATE PROCEDURE log_connection(IN user VARCHAR(13), IN user_ip VARCHAR(15))
BEGIN
  DECLARE user_id INT;
  DECLARE ip_int INT UNSIGNED;
  DECLARE ip_country VARCHAR(20);
  DECLARE ip_country_code CHAR(2);
  
  -- Convert the string ip to a packed int
  SELECT INET_ATON(user_ip) INTO ip_int;

  -- Try to find if the username is already stored
  SELECT id INTO user_id FROM ipd_users WHERE username = user;

  -- If it isn't already stored, then we need to add it
  IF ISNULL(user_id) THEN
    INSERT INTO ipd_users (username) VALUES (user);
    SELECT LAST_INSERT_ID() into user_id;
  END IF;

  -- Find the IP's country and country code
  SELECT countryName, countryCode INTO ip_country, ip_country_code 
    FROM geo_ip_country WHERE ip_int BETWEEN beginNum AND endNum;
 
  -- Add the association, or update the count if it's already there
  INSERT INTO ipd_associations 
    (`ip`, `country`, `countryCode`, `user_id`, `count`) 
    VALUES (ip_int, ip_country, ip_country_code, user_id, 1) 
    ON DUPLICATE KEY UPDATE count = count + 1;

  -- Add a login event
  INSERT INTO ipd_logins (`user_id`, `when`) VALUES (user_id, NOW());
END //
DELIMITER ;

DELIMITER //
-- The log_disconnect() procedure is called when a user exits the game.
-- It accepts the username that is exiting, the IP they were connected from 
-- when they exit, and the reason they are exiting. It creates an entry in
-- the ipd_logouts table with the information.
CREATE PROCEDURE log_disconnect(
  IN user VARCHAR(13), 
  IN user_ip VARCHAR(15), 
  IN how ENUM ('NORMAL', 'TIMEOUT', 'REPLACEMENT', 'DEST', 'UNKNOWN'))
BEGIN
  DECLARE user_id INT;
  DECLARE ip_int INT UNSIGNED;
  
  -- Convert the string ip to a packed int
  SELECT INET_ATON(user_ip) INTO ip_int;

  -- Try to find if the username is already stored
  SELECT id INTO user_id FROM ipd_users WHERE username = user;

  -- If it isn't already stored, then we need to add it
  IF ISNULL(user_id) THEN
    INSERT INTO ipd_users (username) VALUES (user);
    SELECT LAST_INSERT_ID() into user_id;
  END IF;
 
  -- Add a logout event
  INSERT INTO ipd_logouts (`user_id`, `when`, `disconnectType`) 
    VALUES (user_id, NOW(), how);
END //
DELIMITER ;

DELIMITER //
-- A faster version of log_connection that doesn't attempt to associate a 
-- country with the IP to name association. This procedure should only be
-- used for large imports when you want to fill the countries in afterwards
-- (or not at all).
CREATE PROCEDURE fast_log_connection(IN user VARCHAR(13), IN user_ip VARCHAR(15))
BEGIN
  DECLARE user_id INT;
  DECLARE ip_int INT UNSIGNED;
  
  -- Convert the string ip to a packed int
  SELECT INET_ATON(user_ip) INTO ip_int;

  -- Try to find if the username is already stored
  SELECT id INTO user_id FROM ipd_users WHERE username = user;

  -- If it isn't already stored, then we need to add it
  IF ISNULL(user_id) THEN
    INSERT INTO ipd_users (username) VALUES (user);
    SELECT LAST_INSERT_ID() into user_id;
  END IF;

  -- Add the association, or update the count if it's already there
  INSERT INTO ipd_associations 
    (`ip`, `country`, `countryCode`, `user_id`, `count`) 
    VALUES (ip_int, 'Unknown', '??', user_id, 1) 
    ON DUPLICATE KEY UPDATE count = count + 1;

  -- Add a login event
  INSERT INTO ipd_logins (`user_id`, `when`) VALUES (user_id, NOW());
END //
DELIMITER ;