ipd_tables.sql

DROP TABLE IF EXISTS ipd_users;
DROP TABLE IF EXISTS ipd_associations;
DROP TABLE IF EXISTS ipd_logins;
DROP TABLE IF EXISTS ipd_logouts;

-- The table of usernames and their unique ID
CREATE TABLE ipd_users (
  `id` INT NOT NULL auto_increment,
  `username` VARCHAR(13), 
  PRIMARY KEY(`id`),
  UNIQUE KEY(`username`)
) ENGINE=InnoDB;

-- The table that associates an IP to a unique username ID
-- We keep a tally in 'count' of how many times the username has
-- visited from the IP
CREATE TABLE ipd_associations (
  `ip` INT UNSIGNED NOT NULL,
  `countryCode` CHAR(2) DEFAULT '??',
  `country` VARCHAR(20) DEFAULT 'Unknown',
  `user_id` INT NOT NULL,
  `count` INT,
  FOREIGN KEY (`user_id`) REFERENCES ipd_users (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- We do a lot of lookups by IP. Creating an index is well worth it
CREATE INDEX ip_index ON ipd_associations(`ip`);
-- We also need to make sure that there is only 1 record for an IP to a Username
CREATE UNIQUE INDEX unique_associations ON ipd_associations (`ip`, `user_id`);

-- A table that tracks players connecting to the game
CREATE TABLE ipd_logins (
  `id` INT NOT NULL auto_increment,
  `user_id` INT NOT NULL,
  `when` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES ipd_users (`id`)
) ENGINE=InnoDB;
  
-- A table that tracks players disconnecting from the game
-- Also tracks HOW they were disconnecting. Dest, timeout, normal quit, etc
CREATE TABLE ipd_logouts (
  `id` INT NOT NULL auto_increment,
  `user_id` INT NOT NULL,
  `when` DATETIME NOT NULL,
  `disconnectType` ENUM ('NORMAL', 'TIMEOUT', 'REPLACEMENT', 'DEST', 'UNKNOWN') DEFAULT 'NORMAL',
  PRIMARY KEY (`id`),  
  FOREIGN KEY (`user_id`) REFERENCES ipd_users (`id`)
) ENGINE=InnoDB;