1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
CREATE TABLE `events` (
`id` int(11) NOT NULL,
`sequence` tinyint(4) NOT NULL DEFAULT '0',
`added_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`title` text NOT NULL,
`location` text NOT NULL,
`start_date` date NOT NULL,
`end_date` date NOT NULL,
`source` int(11) NOT NULL,
`added_by` int(11) NOT NULL,
`description` mediumtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `event_keyword` (
`event` int(11) NOT NULL,
`keyword` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `keywords` (
`id` int(11) NOT NULL,
`keyword` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `sources` (
`id` int(11) NOT NULL,
`source` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`name` mediumtext NOT NULL,
`email` varchar(255) NOT NULL,
`pgp_fingerprint` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `events`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `title_2` (`title`(255),`start_date`),
ADD KEY `start_date` (`start_date`),
ADD KEY `end_date` (`end_date`),
ADD KEY `source` (`source`),
ADD KEY `added_by` (`added_by`);
ALTER TABLE `events` ADD FULLTEXT KEY `location` (`location`);
ALTER TABLE `events` ADD FULLTEXT KEY `title` (`title`);
ALTER TABLE `events` ADD FULLTEXT KEY `description` (`description`);
ALTER TABLE `event_keyword`
ADD UNIQUE KEY `event_2` (`event`,`keyword`),
ADD KEY `event` (`event`),
ADD KEY `keyword` (`keyword`);
ALTER TABLE `keywords`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `keyword` (`keyword`(127)) USING BTREE;
ALTER TABLE `keywords` ADD FULLTEXT KEY `keyword_2` (`keyword`);
ALTER TABLE `sources`
ADD PRIMARY KEY (`id`);
ALTER TABLE `sources` ADD FULLTEXT KEY `source` (`source`);
ALTER TABLE `users`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `email` (`email`),
ADD UNIQUE KEY `name` (`name`(127));
ALTER TABLE `events`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `keywords`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `sources`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `events`
ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`),
ADD CONSTRAINT `events_ibfk_2` FOREIGN KEY (`source`) REFERENCES `sources` (`id`);
ALTER TABLE `event_keyword`
ADD CONSTRAINT `event_keyword_ibfk_1` FOREIGN KEY (`event`) REFERENCES `events` (`id`),
ADD CONSTRAINT `event_keyword_ibfk_2` FOREIGN KEY (`keyword`) REFERENCES `keywords` (`id`);
|
