newtowndogs/setup.sql at main · mrao2/newtowndogs · 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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
CREATE TABLE IF NOT EXISTS `login_app` (
  id int AUTO_INCREMENT ,
  username varchar(45),
  hashed_password varchar(128),
  email varchar(100) DEFAULT NULL,
  first_name varchar(45),
  last_name varchar(45),
  phone int,
  UNIQUE KEY username_UNIQUE (username),
  UNIQUE KEY phone_UNIQUE (phone),
  PRIMARY KEY (id),
  INDEX (id),
  INDEX (first_name),
  INDEX (last_name),
  INDEX (email),
  INDEX (username)
);

CREATE TABLE IF NOT EXISTS `user_profile` (
    id INTEGER,
    first_name varchar(45),
    last_name varchar(45),
    email VARCHAR (100),
    username varchar(45),
    ownerAddress VARCHAR (100),
    ownerCity VARCHAR (30),
    ownerState VARCHAR (2),
    ownerZip INTEGER,
    ownerEmail VARCHAR (50),
    dogName VARCHAR (25),
    dogBreed VARCHAR (30),
    dogGender VARCHAR (6),
    dogColor VARCHAR (30),
    dogBirthdate VARCHAR (5),
    dogAllergies VARCHAR (200),
    dogWeight INTEGER (3),
    dogFriendly BOOLEAN,
    amtWalks INTEGER (2),
    amtMeals INTEGER (2),
    dogPottyTrained BOOLEAN,
    dogFixed BOOLEAN,
    INDEX (id),
    INDEX (first_name),
    INDEX (last_name),
    INDEX (email),
    INDEX (username),
    FOREIGN KEY (id) REFERENCES login_app(id),
    FOREIGN KEY (first_name) REFERENCES login_app(first_name),
    FOREIGN KEY (last_name) REFERENCES login_app(last_name),
    FOREIGN KEY (email) REFERENCES login_app(email),
    FOREIGN KEY (username) REFERENCES login_app(username)
);

CREATE TABLE IF NOT EXISTS blogs (
  BlogId INT AUTO_INCREMENT PRIMARY KEY,
  Author VARCHAR(255),
  Title VARCHAR(255),
  Body VARCHAR(4000),
  Likes VARCHAR(255),
  Category VARCHAR(255),
  Comments VARCHAR(255),
  username VARCHAR (45),
   INDEX (username),
   INDEX (BlogId),
  FOREIGN KEY (username) REFERENCES login_app(username)
);

CREATE TABLE IF NOT EXISTS appointments (
	 appointment_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
     id INT,
     start_time VARCHAR(255) NOT NULL,
	 end_time VARCHAR(255),
	 start_date VARCHAR(255) NOT NULL,
	 end_date VARCHAR(255),
     description VARCHAR(255),
	 is_consultation TINYINT,
    is_confirmed TINYINT,
    is_rejected TINYINT,
     first_name VARCHAR(255),
     last_name VARCHAR(255),
     email VARCHAR(255),
     username VARCHAR(255),
    INDEX (id),
    -- INDEX (first_name),
    -- INDEX (last_name),
    -- INDEX (email),
    INDEX (username),
    FOREIGN KEY (id) REFERENCES login_app(id),
    -- FOREIGN KEY (first_name) REFERENCES login_app(first_name),
    -- FOREIGN KEY (last_name) REFERENCES login_app(last_name),
    -- FOREIGN KEY (email) REFERENCES login_app(email),
    FOREIGN KEY (username) REFERENCES login_app(username)
);

CREATE TABLE IF NOT EXISTS homepage (
  title VARCHAR(255) NOT NULL,
  description VARCHAR(255) NOT NULL,
  breed VARCHAR (255)
);

CREATE TABLE IF NOT EXISTS comments (
  CommentId INT AUTO_INCREMENT PRIMARY KEY,
  BlogId INT,
  Comment_Author VARCHAR(255),
  Comment_Body VARCHAR(4000),
  username VARCHAR (45),
   INDEX (username),
   INDEX (BlogId),
  FOREIGN KEY (username) REFERENCES login_app(username),
  FOREIGN KEY (BlogId) REFERENCES blogs(BlogId) ON DELETE CASCADE
);


CREATE TABLE IF NOT EXISTS images (
  ImageId INT AUTO_INCREMENT PRIMARY KEY,
  BlogId INT UNIQUE,
  Content_Type VARCHAR (255),
  Image_Data LONGBLOB,
  username VARCHAR (45),
   INDEX (username),
   INDEX (BlogId),
  FOREIGN KEY (username) REFERENCES login_app(username),
  FOREIGN KEY (BlogId) REFERENCES blogs(BlogId) ON DELETE CASCADE
);