db.sql

   1 SET storage_engine=INNODB;
   2 SET character_set_database=utf8;
   3
   4 CREATE TABLE rooms (
   5     `id` int not null auto_increment,
   6     `x` int not null,
   7     `y` int not null,
   8     `state` boolean not null,
   9     `description` text,
  10     PRIMARY KEY (`id`),
  11     INDEX `coordinates` (`x`,`y`)
  12 );
  13
  14 CREATE TABLE `players` (
  15     `id` int not null auto_increment,
  16     `name` varchar(32) not null,
  17     `room` int not null,
  18     `last_active` timestamp default current_timestamp,
  19     PRIMARY KEY (`id`),
  20     CONSTRAINT `location` FOREIGN KEY (`room`) REFERENCES `rooms` (`id`)
  21 );
  22
  23 CREATE TABLE `messages` (
  24     `id` int not null auto_increment,
  25     `message` text not null,
  26     `room` int not null,
  27     `type` ENUM('say', 'tell', 'yell') not null,
  28     `destination` int,
  29     `source` int,
  30     `sent` timestamp default current_timestamp,
  31     PRIMARY KEY (`id`),
  32     CONSTRAINT `origin` FOREIGN KEY (`source`) REFERENCES `players` (`id`)
  33         ON DELETE CASCADE,
  34     CONSTRAINT `addressable` FOREIGN KEY (`destination`) REFERENCES `players` (`id`)
  35         ON DELETE CASCADE,
  36     CONSTRAINT `place` FOREIGN KEY (`room`) REFERENCES `rooms` (`id`)
  37 );
  38
  39 CREATE EVENT `purge_inactive_players`
  40     ON SCHEDULE EVERY 1 MINUTE STARTS now()
  41     DO DELETE FROM `players` WHERE `last_active` < now() - interval 5 minute;
  42
  43 CREATE EVENT `purge_old_messages`
  44     ON SCHEDULE EVERY 1 MINUTE STARTS now()
  45     DO DELETE FROM `messages` WHERE `sent` < now() - interval 5 minute;