Initial commit

1 files changed, 81 insertions, 0 deletions

diff --git a/install.sql b/install.sql
new file mode 100644
index 0000000..8eb299a
--- /dev/null
+++ b/install.sql
@@ -0,0 +1,81 @@
+CREATE TABLE `events` (
+  `id` int(11) NOT NULL,
+  `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`);