0dE7Ex8a

1CREATE DATABASE IF NOT EXISTS awesome_calendar
2        CHARACTER SET utf8
3        DEFAULT CHARACTER SET utf8
4        COLLATE utf8_general_ci
5        DEFAULT COLLATE utf8_general_ci
6        ;
7
8USE awesome_calendar;
9
10GRANT ALL PRIVILEGES ON awesome_calendar.* to 'awecaluser'@'localhost'
11        IDENTIFIED BY 'password';
12
13CREATE TABLE IF NOT EXISTS events (
14  eventid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
15  userid INT NOT NULL,
16  eventname VARCHAR(255),
17  location VARCHAR(255),
18  startdate DATETIME,
19  enddate DATETIME,
20  eventtype INT,
21  FOREIGN KEY (userid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE SET NULL
22) ENGINE=InnoDB;
23
24CREATE TABLE IF NOT EXISTS alerts (
25  alertid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
26  eventid INT NOT NULL,
27  alerttime DATETIME,
28  alerttype VARCHAR(255),
29  FOREIGN KEY (eventid) REFERENCES events(eventid) ON UPDATE CASCADE ON DELETE SET NULL
30) ENGINE=InnoDB;
31
32CREATE TABLE IF NOT EXISTS users (
33  userid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
34  email VARCHAR(255) NOT NULL,
35  pass VARCHAR(255) NOT NULL,
36) ENGINE=InnoDB;
37
38CREATE TABLE IF NOT EXISTS events_types (
39  eventtypeid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
40  eventid INT NOT NULL,
41  typeid INT NOT NULL,
42  FOREIGN KEY (eventid) REFERENCES events(eventid) ON UPDATE CASCADE ON DELETE SET NULL,
43  FOREIGN KEY (typeid) REFERENCES types(typeid) ON UPDATE CASCADE ON DELETE SET NULL
44}
45
46CREATE TABLE IF NOT EXISTS types (
47  typeid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
48  typename VARCHAR(255) NOT NULL
49) ENGINE=InnoDB;