-
Notifications
You must be signed in to change notification settings - Fork 2
/
migrations.php
331 lines (285 loc) · 9.42 KB
/
migrations.php
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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
<?php
// Include config file
include 'config.php';
/* Attempt to connect to MySQL database */
$link = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD);
// Check connection
if($link === false){
die("ERROR: Could not connect. " . $link->connect_error);
}
// Create database bookrental
$sql = "CREATE DATABASE IF NOT EXISTS bookrental";
if (mysqli_query($link, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($link);
}
echo"<br />";
// Use database
$sql = "USE bookrental";
if (mysqli_query($link, $sql)) {
echo "Database changed successfully";
} else {
echo "Error using database: " . mysqli_error($link);
}
echo"<br />";
// TABLES
// Customer
$sql = "CREATE TABLE IF NOT EXISTS customer (
user_id INT NOT NULL AUTO_INCREMENT,
email VARCHAR(40) NOT NULL,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
country VARCHAR(255),
state VARCHAR(255),
city VARCHAR(255),
password VARCHAR(255) NOT NULL,
PRIMARY KEY (user_id),
UNIQUE (email)
);";
if (mysqli_query($link, $sql)) {
echo "Customer user table created successfully";
} else {
echo "Error creating customer table: " . mysqli_error($link);
}
echo"<br />";
// Phone number
$sql = "CREATE TABLE IF NOT EXISTS phone_no (
phone_no VARCHAR(15),
user_id INT NOT NULL,
PRIMARY KEY (phone_no),
FOREIGN KEY (user_id) REFERENCES customer(user_id)
ON DELETE CASCADE
);";
if (mysqli_query($link, $sql)) {
echo "Phone number table created successfully";
} else {
echo "Error creating phone number table: " . mysqli_error($link);
}
echo"<br />";
// Book
$sql = "CREATE TABLE IF NOT EXISTS book (
book_id INT NOT NULL AUTO_INCREMENT,
user_id INT,
title VARCHAR(255) NOT NULL,
cover_image varchar(255) COLLATE utf8_unicode_ci,
info VARCHAR(500),
author VARCHAR(255),
lang VARCHAR(255),
no_of_pages INT DEFAULT 0,
category VARCHAR(255) NOT NULL,
PRIMARY KEY (book_id)
);";
if (mysqli_query($link, $sql)) {
echo "Book table created successfully";
} else {
echo "Error creating book table: " . mysqli_error($link);
}
echo"<br />";
// Cart Items
$sql = "CREATE TABLE IF NOT EXISTS cart_item (
cart_item_id INT AUTO_INCREMENT,
is_ordered BOOLEAN DEFAULT FALSE,
order_id INT,
book_id INT NOT NULL,
user_id INT NOT NULL,
PRIMARY KEY (cart_item_id),
FOREIGN KEY (book_id) REFERENCES book(book_id)
ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES customer(user_id)
ON DELETE CASCADE
);";
if (mysqli_query($link, $sql)) {
echo "Cart item table created successfully";
} else {
echo "Error creating cart item table: " . mysqli_error($link);
}
echo"<br />";
// Book for sale
$sql = "CREATE TABLE IF NOT EXISTS book_for_sale(
price INT NOT NULL,
discount_price INT,
book_id INT NOT NULL,
FOREIGN KEY (book_id) REFERENCES book(book_id)
ON DELETE CASCADE
);";
if (mysqli_query($link, $sql)) {
echo "Book for sale table created successfully";
} else {
echo "Error creating book for sale table: " . mysqli_error($link);
}
echo"<br />";
// Book for rent
$sql = "CREATE TABLE IF NOT EXISTS book_for_rent (
monthly_rate INT NOT NULL,
rating INT DEFAULT 0,
is_available BOOLEAN DEFAULT TRUE,
book_id INT NOT NULL,
FOREIGN KEY (book_id) REFERENCES book(book_id)
ON DELETE CASCADE
);";
if (mysqli_query($link, $sql)) {
echo "Book for rent table created successfully";
} else {
echo "Error creating book for rent table: " . mysqli_error($link);
}
echo"<br />";
// Queue
$sql = "CREATE TABLE IF NOT EXISTS queue (
queue_id INT AUTO_INCREMENT,
book_id INT NOT NULL,
user_id INT NOT NULL,
status ENUM ('Waiting','Pending', 'Cancelled', 'Currently renting', 'Returned'),
date_of_request DATETIME DEFAULT NOW(),
date_granted DATETIME,
date_of_return DATETIME,
PRIMARY KEY(queue_id),
FOREIGN KEY (user_id) REFERENCES customer(user_id),
FOREIGN KEY (book_id) REFERENCES book(book_id)
ON DELETE CASCADE
);";
if (mysqli_query($link, $sql)) {
echo "Queue table created successfully";
} else {
echo "Error creating queue table: " . mysqli_error($link);
}
echo"<br />";
// Notifications
$sql = "CREATE TABLE IF NOT EXISTS notification (
notif_id INT AUTO_INCREMENT,
queue_id INT NOT NULL,
timestamp DATETIME DEFAULT NOW(),
PRIMARY KEY(notif_id),
FOREIGN KEY (queue_id) REFERENCES queue(queue_id)
ON DELETE CASCADE
);";
if (mysqli_query($link, $sql)) {
echo "Notification table created successfully";
} else {
echo "Error creating notification table: " . mysqli_error($link);
}
echo"<br />";
// Order items
$sql = "CREATE TABLE IF NOT EXISTS order_item (
order_id INT AUTO_INCREMENT,
user_id INT NOT NULL,
street VARCHAR(255) NOT NULL,
zipcode VARCHAR(6) NOT NULL,
state VARCHAR(255) NOT NULL,
PRIMARY KEY (order_id),
FOREIGN KEY (user_id) REFERENCES customer(user_id)
ON DELETE CASCADE
);";
if (mysqli_query($link, $sql)) {
echo "Order table created successfully";
} else {
echo "Error creating order items table: " . mysqli_error($link);
}
echo"<br />";
// Constraint
$sql = "ALTER TABLE cart_item
ADD CONSTRAINT FOREIGN KEY (order_id) REFERENCES order_item(order_id);";
if (mysqli_query($link, $sql)) {
echo "Constraint added successfully";
} else {
echo "Error adding constraint: " . mysqli_error($link);
}
echo"<br />";
// Payment
$sql = "CREATE TABLE IF NOT EXISTS payment(
payment_date DATETIME DEFAULT NOW(),
payment_amount FLOAT,
mode_of_payment ENUM ('Net Banking','Cash on Delivery','Credit Card'),
order_id INT,
FOREIGN KEY (order_id) REFERENCES order_item(order_id)
);";
if (mysqli_query($link, $sql)) {
echo "Payment table created successfully";
} else {
echo "Error creating payment table: " . mysqli_error($link);
}
echo"<br />";
// TRIGGERS
// Create notification
$sql= "CREATE TRIGGER IF NOT EXISTS `create_notif`
AFTER UPDATE ON `queue`
FOR EACH ROW begin
if new.status='Pending' and old.status<>'Pending' then
insert into notification (queue_id) values (new.queue_id);
end if;
end";
if (mysqli_query($link, $sql)) {
echo "Notification trigger created successfully";
} else {
echo "Error creating notification trigger: " . mysqli_error($link);
}
// VIEWS
// Book for rent
// $sql= "";
// if (mysqli_query($link, $sql)) {
// echo "Notification trigger created successfully";
// } else {
// echo "Error creating notification trigger: " . mysqli_error($link);
// }
echo"<br />";
// Book for sale
// $sql= "";
// if (mysqli_query($link, $sql)) {
// echo "Book rent view created successfully";
// } else {
// echo "Error creating book rent view: " . mysqli_error($link);
// }
// Book
$sql='CREATE VIEW IF NOT EXISTS book_view AS
SELECT a.book_id, title, info, author, lang, no_of_pages, cover_image, price, type, category, is_available, user_id AS uploaded_by, discount_price
FROM (SELECT book.book_id, title, info, author, lang, no_of_pages, cover_image, monthly_rate AS price, "rent" AS type, category, user_id, 0 AS discount_price
FROM book INNER JOIN book_for_rent
ON book.book_id=book_for_rent.book_id) AS a
INNER JOIN
(SELECT book_for_rent.book_id, IF(c, 0, 1) AS is_available
FROM (SELECT book_id, count(*) AS c
FROM queue
WHERE status="Waiting" or status="Pending" or status="Currently Renting"
GROUP BY book_id) AS t
RIGHT JOIN book_for_rent ON book_for_rent.book_id=t.book_id) AS b
ON a.book_id=b.book_id
UNION
SELECT a.book_id, title, info, author, lang, no_of_pages, cover_image, price, type, category, is_available, user_id AS uploaded_by, discount_price
FROM (SELECT book.book_id, title, info, author, lang, no_of_pages, cover_image, price, "buy" AS type, category, user_id, discount_price
FROM book
RIGHT JOIN book_for_sale
ON book.book_id=book_for_sale.book_id) AS a
INNER JOIN (SELECT book_for_sale.book_id, IF(is_ordered, 0, 1) AS is_available
FROM (select book_id, "1" AS is_ordered
FROM cart_item
WHERE is_ordered=1 GROUP BY book_id) AS t
RIGHT JOIN book_for_sale
ON book_for_sale.book_id=t.book_id) AS b
ON a.book_id=b.book_id
ORDER BY book_id';
if (mysqli_query($link, $sql)) {
echo "Book view created successfully";
} else {
echo "Error creating book view: " . mysqli_error($link);
}
echo"<br />";
// Queue
$sql= "CREATE VIEW IF NOT EXISTS queue_view AS
SELECT queue.user_id, queue.queue_id, status, book.book_id, title, cover_image, customer.first_name, customer.last_name
FROM queue INNER JOIN book ON book.book_id=queue.book_id INNER JOIN customer ON queue.user_id=customer.user_id WHERE status<>'Returned' AND status<>'Cancelled';";
if (mysqli_query($link, $sql)) {
echo "Queue view created successfully";
} else {
echo "Error creating queue view: " . mysqli_error($link);
}
echo"<br />";
// Notification
$sql= "CREATE VIEW IF NOT EXISTS notification_view AS
SELECT notif_id, queue.user_id, queue.queue_id, status, book.book_id, title, cover_image, unix_timestamp(timestamp) as unix_time
FROM notification INNER JOIN queue ON queue.queue_id=notification.queue_id INNER JOIN book ON book.book_id=queue.book_id;";
if (mysqli_query($link, $sql)) {
echo "Notification view created successfully";
} else {
echo "Error creating notification view: " . mysqli_error($link);
}
?>