rkSZbFdu

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 users (
14  userid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
15  email VARCHAR(255) NOT NULL,
16  pass VARCHAR(255) NOT NULL
17) ENGINE=InnoDB;
18
19CREATE TABLE IF NOT EXISTS events (
20  eventid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
21  userid INT NOT NULL,
22  eventname VARCHAR(255),
23  location VARCHAR(255),
24  startdate DATETIME,
25  enddate DATETIME,
26  eventtype INT,
27  FOREIGN KEY (userid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE
28) ENGINE=InnoDB;
29
30
31CREATE TABLE IF NOT EXISTS alerts (
32  alertid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
33  eventid INT NOT NULL,
34  alerttime DATETIME,
35  alerttype VARCHAR(255),
36  FOREIGN KEY (eventid) REFERENCES events(eventid) ON UPDATE CASCADE ON DELETE CASCADE
37) ENGINE=InnoDB;
38
39CREATE TABLE IF NOT EXISTS types (
40  typeid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
41  typename VARCHAR(255) NOT NULL
42) ENGINE=InnoDB;
43
44CREATE TABLE IF NOT EXISTS events_types (
45  eventtypeid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
46  eventid INT NOT NULL,
47  typeid INT NOT NULL,
48  FOREIGN KEY (eventid) REFERENCES events(eventid) ON UPDATE CASCADE ON DELETE CASCADE,
49  FOREIGN KEY (typeid) REFERENCES types(typeid) ON UPDATE CASCADE ON DELETE CASCADE
50)