hEs1VnC2

1DROP PROCEDURE IF EXISTS log_disconnect;
2DROP PROCEDURE IF EXISTS log_connection;
3
4DELIMITER //
5-- The log_connection() procedure is called when a user connects to the game.
6-- It accepts the username that is connecting, and the ip they are connecting 
7-- from. It then adds the user if their username is new, associates the 
8-- username with the IP making sure to update the count of how many times 
9-- they've connected from that IP. Finally it adds a login for this user at 
10-- the present time/date.
11--
12-- This procedure will additionally attempt to note the country the IP is 
13-- located in by means of the geo_ip_country table. This query is slow(ish) for
14-- large number of procedure calls. fast_log_connection() should be used when
15-- a great number of connections are being processed at once (i.e. a data set
16-- import).
17CREATE PROCEDURE log_connection(IN user VARCHAR(13), IN user_ip VARCHAR(15))
18BEGIN
19  DECLARE user_id INT;
20  DECLARE ip_int INT UNSIGNED;
21  DECLARE ip_country VARCHAR(20);
22  DECLARE ip_country_code CHAR(2);
23  
24  -- Convert the string ip to a packed int
25  SELECT INET_ATON(user_ip) INTO ip_int;
26
27  -- Try to find if the username is already stored
28  SELECT id INTO user_id FROM ipd_users WHERE username = user;
29
30  -- If it isn't already stored, then we need to add it
31  IF ISNULL(user_id) THEN
32    INSERT INTO ipd_users (username) VALUES (user);
33    SELECT LAST_INSERT_ID() into user_id;
34  END IF;
35
36  -- Find the IP's country and country code
37  SELECT countryName, countryCode INTO ip_country, ip_country_code 
38    FROM geo_ip_country WHERE ip_int BETWEEN beginNum AND endNum;
39 
40  -- Add the association, or update the count if it's already there
41  INSERT INTO ipd_associations 
42    (`ip`, `country`, `countryCode`, `user_id`, `count`) 
43    VALUES (ip_int, ip_country, ip_country_code, user_id, 1) 
44    ON DUPLICATE KEY UPDATE count = count + 1;
45
46  -- Add a login event
47  INSERT INTO ipd_logins (`user_id`, `when`) VALUES (user_id, NOW());
48END //
49DELIMITER ;
50
51DELIMITER //
52-- The log_disconnect() procedure is called when a user exits the game.
53-- It accepts the username that is exiting, the IP they were connected from 
54-- when they exit, and the reason they are exiting. It creates an entry in
55-- the ipd_logouts table with the information.
56CREATE PROCEDURE log_disconnect(
57  IN user VARCHAR(13), 
58  IN user_ip VARCHAR(15), 
59  IN how ENUM ('NORMAL', 'TIMEOUT', 'REPLACEMENT', 'DEST', 'UNKNOWN'))
60BEGIN
61  DECLARE user_id INT;
62  DECLARE ip_int INT UNSIGNED;
63  
64  -- Convert the string ip to a packed int
65  SELECT INET_ATON(user_ip) INTO ip_int;
66
67  -- Try to find if the username is already stored
68  SELECT id INTO user_id FROM ipd_users WHERE username = user;
69
70  -- If it isn't already stored, then we need to add it
71  IF ISNULL(user_id) THEN
72    INSERT INTO ipd_users (username) VALUES (user);
73    SELECT LAST_INSERT_ID() into user_id;
74  END IF;
75 
76  -- Add a logout event
77  INSERT INTO ipd_logouts (`user_id`, `when`, `disconnectType`) 
78    VALUES (user_id, NOW(), how);
79END //
80DELIMITER ;
81
82DELIMITER //
83-- A faster version of log_connection that doesn't attempt to associate a 
84-- country with the IP to name association. This procedure should only be
85-- used for large imports when you want to fill the countries in afterwards
86-- (or not at all).
87CREATE PROCEDURE fast_log_connection(IN user VARCHAR(13), IN user_ip VARCHAR(15))
88BEGIN
89  DECLARE user_id INT;
90  DECLARE ip_int INT UNSIGNED;
91  
92  -- Convert the string ip to a packed int
93  SELECT INET_ATON(user_ip) INTO ip_int;
94
95  -- Try to find if the username is already stored
96  SELECT id INTO user_id FROM ipd_users WHERE username = user;
97
98  -- If it isn't already stored, then we need to add it
99  IF ISNULL(user_id) THEN
100    INSERT INTO ipd_users (username) VALUES (user);
101    SELECT LAST_INSERT_ID() into user_id;
102  END IF;
103
104  -- Add the association, or update the count if it's already there
105  INSERT INTO ipd_associations 
106    (`ip`, `country`, `countryCode`, `user_id`, `count`) 
107    VALUES (ip_int, 'Unknown', '??', user_id, 1) 
108    ON DUPLICATE KEY UPDATE count = count + 1;
109
110  -- Add a login event
111  INSERT INTO ipd_logins (`user_id`, `when`) VALUES (user_id, NOW());
112END //
113DELIMITER ;