forked from repulsiv/repulsiv
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
68 lines (56 loc) · 1.81 KB
/
schema.sql
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
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE users (
id int NOT NULL AUTO_INCREMENT,
token varchar(255) NOT NULL UNIQUE,
userName varchar(255) NOT NULL,
email varchar(255) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE products (
id int NOT NULL AUTO_INCREMENT,
itemId int NOT NULL,
productName text NOT NULL,
salesPrice int NOT NULL,
threshHoldPrice int NOT NULL,
created_at timestamp NOT NULL,
user_id int NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
PRIMARY KEY (id)
);
/*
CREATE TABLE users_products (
id_users int NOT NULL,
id_products int NOT NULL,
PRIMARY KEY (id_users, id_products),
FOREIGN KEY (id_users) REFERENCES users(id),
FOREIGN KEY (id_products) REFERENCES products(id)
);
*/
/* Execute this file from the command line by typing:
* mysql -u root < server/schema.sql
* to create the database and the tables.*/
/* Testing the database
INSERT INTO users (userName, email) VALUES
('Shaital', 'mango@gmail.com'),
(2, 'Nasa', 'raspberry', 'raspberry@gmail.com'),
(3, 'Khizra', 'watermelon', 'watermelon@gmail.com'),
(4, 'Lukas', 'apple', 'apple@gmail.com')
;
-- INSERT INTO products (itemID, productName, salesPrice, threshHoldPrice) VALUES
-- (1234, 'iPad', 20, 10),
-- (2, 2345, 'iWatch', 11),
-- (3, 3456, 'iPhone', 12),
-- (4, 4567, 'iMac', 13)
-- ;
INSERT INTO users (token, userName, email) VALUES (12345, 'Shaital', 'mango@gmail.com');
INSERT INTO products (itemID, productName, salesPrice, threshHoldPrice) VALUES (1234, 'iPad', 20, 10);
INSERT INTO users_products (id_users, id_products) SELECT users.id, products.id FROM users INNER JOIN products ON users.id = products.id;
-- INSERT INTO users_products (`id`, `id_users`, `id_products`)
-- VALUES
-- (1, 1, 2),
-- (2, 2, 3),
-- (3, 3, 4),
-- (4, 4, 1)
-- ;