cbc8b3c3a5c0b1fd0970833efbce9a59792794e7
[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 CONSTRAINT `addressable` FOREIGN KEY (`destination`) REFERENCES `players` (`id`),
34 CONSTRAINT `place` FOREIGN KEY (`room`) REFERENCES `rooms` (`id`)
35 );
36
37 CREATE EVENT `purge_inactive_players`
38 ON SCHEDULE EVERY 1 MINUTE
39 DO DELETE FROM `players` WHERE `last_active` < now() - interval 5 minute;
40
41 CREATE EVENT `purge_old_messages`
42 ON SCHEDULE EVERY 1 MINUTE
43 DO DELETE FROM `messages` WHERE `sent` < now() - interval 5 minute;