florify-api/tables.sql at master · ericmackay/florify-api · GitHub

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
DROP DATABASE IF EXISTS florify;

CREATE DATABASE florify;

USE florify;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL UNIQUE,
  phone VARCHAR(20),
  password VARCHAR(60) NOT NULL,
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  userId INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  token VARCHAR(100) NOT NULL UNIQUE,
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE plants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  userId INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  nickname VARCHAR(50) NOT NULL,
  name VARCHAR(100),
  description VARCHAR(200),
  maxtemp INT,
  mintemp INT,
  maxph INT,
  minph INT,
  maxhum INT,
  minhum INT,
  maxlux INT,
  minlux INT,
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE plantInfo (
  name VARCHAR(100) NOT NULL UNIQUE,
  imageUrl VARCHAR(1000),
  maxTemp INT,
  minTemp INT,
  maxPh INT,
  minPh INT,
  maxHum INT,
  minHum INT,
  maxLux INT,
  minLux INT,
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  plantId INT NOT NULL REFERENCES plants (id) ON DELETE CASCADE,
  type VARCHAR(20) NOT NULL,
  reading INT NOT NULL,
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
);