23ccd1362ef4916c5760d3783ad9ee799d6133c2
[mudd.git] / 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;