· 6 years ago · Nov 14, 2019, 10:38 AM
1DROP DATABASE IF EXISTS service_community;
2CREATE DATABASE service_community CHARACTER SET 'utf8';
3USE service_community;
4
5-- Table users
6
7CREATE TABLE users (
8 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
9 mail VARCHAR(30) UNIQUE NOT NULL,
10 passweird VARCHAR(20) NOT NULL,
11 nickname VARCHAR(20) UNIQUE NOT NULL,
12 adress VARCHAR(60),
13 zip_code VARCHAR(15),
14 city VARCHAR(40),
15 country VARCHAR(30),
16 cellphone VARCHAR(20),
17 phone VARCHAR(20),
18 date_inscription DATE,
19 PRIMARY KEY (id)
20)
21ENGINE = INNODB;
22
23-- Table services
24
25CREATE TABLE services (
26 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
27 id_user_from INT UNSIGNED NOT NULL,
28 name VARCHAR(20) NOT NULL,
29 description TEXT NOT NULL,
30 adress VARCHAR(30) NOT NULL,
31 zip_code VARCHAR(15) NOT NULL,
32 city VARCHAR(40) NOT NULL,
33 country VARCHAR(30) NOT NULL,
34 date_time DATETIME NOT NULL,
35 additional_information TEXT,
36 PRIMARY KEY (id)
37)
38ENGINE = INNODB;
39
40-- Table services-users
41
42CREATE TABLE services_users (
43 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
44 id_service INT UNSIGNED NOT NULL,
45 id_user INT UNSIGNED NOT NULL,
46 date_time_inscription DATETIME NOT NULL,
47 PRIMARY KEY (id),
48 CONSTRAINT fk_service_id FOREIGN KEY (id_service) REFERENCES services(id) ON DELETE CASCADE
49)
50ENGINE = INNODB;
51
52-- Table messages
53
54CREATE TABLE messages (
55 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
56 id_user_from INT UNSIGNED NOT NULL,
57 id_user_to INT UNSIGNED NOT NULL,
58 content TEXT NOT NULL,
59 date_time_sended DATETIME NOT NULL,
60 PRIMARY KEY (id)
61)
62ENGINE = INNODB;
63
64-- Users creation
65
66INSERT INTO users (mail, passweird, nickname)
67VALUE ('testytasty@toast.com', 'apassweird', 'Tasty'),
68 ('unmail@free.ru', 'aweirdpassweird', 'Toast'),
69 ('thetoast@toaster.fr', '1234', 'John'),
70 ('amail@mail.com', '4256', 'Banana'),
71 ('thisisnotamail@mail.com', '6969', 'Caraba'),
72 ('banana@gree.com', '666', 'Veratisen'),
73 ('stupefix@snape.avada', '934', 'Giiny'),
74 ('alohomora@severus.keda', 'crucio', 'Lockhart'),
75 ('potterXdraco@tyrion.com', '0000', 'Sprout'),
76 ('slughornXjedusor@granger.ron', 'mangetesmangesmorts', 'Dumblehumbledore');
77
78-- Services creation
79
80INSERT INTO services (id_user_from, name, description, adress, zip_code, city, country, date_time)
81VALUES (1, 'Gatooooo', 'les gatoooo c bon', '17 rue du gatooooo', '64700', 'Gatocity', 'Gatoland', '2019-12-12 15:55:42'),
82 (2, 'Chocoooo', 'les chocooo ossi', '17 rue du chocoooo', '64700', 'Gatocity', 'Gatoland', '2019-11-12 15:55:43'),
83 (2, 'plantation', 'Ca ne pousse pas...', '15 rue de la decolonisation', '55000', 'uiparcist', 'France', '2019-10-12 15:55:44'),
84 (4, 'barber shop', 'Axel a trop de poil', '13 rue du tatrodepoil', '77230', '5th district', 'Jackson-City', '2019-09-12 15:55:45'),
85 (5, 'massages', 'finition body body', '1 rue de la rue', '75000', 'Paris', 'France', '2016-10-06 15:55:46'),
86 (6, 'massages2', 'finition hehe boi', '2 rue de la rue', '78900', 'Paris', 'France', '2016-10-06 15:55:47'),
87 (7, 'barber shop', 'Axel a trop de poil', '13 rue du tatrodepoil', '78900', '5th district', 'Jackson-City', '2019-09-12 15:55:48'),
88 (8, 'barber shop', 'Axel a trop de poil', '13 rue du tatrodepoil', '78900', '5th district', 'Jackson-City', '2019-09-12 15:55:49'),
89 (9, 'barber shop', 'Axel a trop de poil', '13 rue du tatrodepoil', '78900', '5th district', 'Jackson-City', '2019-09-12 15:55:50'),
90 (10, 'barber shop', 'Axel a trop de poil', '13 rue du tatrodepoil', '78900', '5th district', 'Jackson-City', '2019-09-12 15:55:51');
91
92-- Messages creation
93
94INSERT INTO messages (id_user_from, id_user_to, content, date_time_sended)
95VALUE (1, 2, 'This is a message.', '2016-10-06 15:55:45'),
96 (4, 3, 'This is a message.', '2016-10-06 15:56:45'),
97 (9, 2, 'This is a message.', '2016-10-06 15:57:45'),
98 (5, 9, 'This is a message.', '2016-10-06 15:58:45'),
99 (1, 4, 'This is a message.', '2016-10-06 15:54:45'),
100 (8, 7, 'This is a message.', '2016-10-06 15:30:45'),
101 (3, 2, 'This is a message.', '2016-10-06 16:54:45'),
102 (6, 4, 'This is a message.', '2016-10-06 13:54:45'),
103 (5, 9, 'This is a message.', '2016-10-06 12:54:45'),
104 (3, 7, 'This is a message.', '2016-10-06 11:54:45'),
105 (9, 6, 'This is a message.', '2016-10-06 09:11:45'),
106 (9, 1, 'This is a message.', '2016-10-06 01:54:00'),
107 (6, 4, 'This is a message.', '2016-10-06 13:44:45'),
108 (3, 7, 'This is a message.', '2016-10-06 19:54:45'),
109 (2, 5, 'This is a message.', '2016-10-06 10:15:45'),
110 (3, 4, 'This is a message.', '2016-10-06 17:33:45'),
111 (8, 5, 'This is a message.', '2016-10-06 13:52:45'),
112 (4, 9, 'This is a message.', '2016-10-06 14:54:45'),
113 (2, 4, 'This is a message.', '2016-10-06 16:50:45'),
114 (3, 7, 'This is a message.', '2016-10-06 18:53:45'),
115 (4, 9, 'This is a message.', '2016-10-06 19:13:45'),
116 (3, 10, 'This is a message.', '2016-10-06 15:34:45'),
117 (1, 10, 'This is a message.', '2016-10-06 13:56:45'),
118 (10, 1, 'This is a message.', '2016-10-06 14:49:45'),
119 (10, 3, 'This is a message.', '2016-10-06 20:01:45');
120
121-- Services inscription
122
123INSERT INTO services_users (id_service, id_user, date_time_inscription)
124VALUE (1, 2, '2016-10-07 17:25:00'),
125 (8, 3, '2016-10-07 12:48:00'),
126 (1, 3, '2016-10-07 17:34:00'),
127 (4, 7, '2016-10-07 19:30:00'),
128 (9, 4, '2016-10-07 21:37:00'),
129 (8, 6, '2016-10-07 03:42:00'),
130 (8, 10, '2016-10-07 06:27:00'),
131 (7, 6, '2016-10-07 20:56:00'),
132 (3, 9, '2016-10-07 16:15:00'),
133 (4, 3, '2016-10-07 15:11:00'),
134 (3, 10, '2016-10-07 13:36:00'),
135 (3, 7, '2016-10-07 12:39:00');
136
137-- Update of profiles
138
139UPDATE users
140SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
141WHERE id = 1;
142
143UPDATE users
144SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
145WHERE id = 2;
146
147UPDATE users
148SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
149WHERE id = 3;
150
151UPDATE users
152SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
153WHERE id = 4;
154
155UPDATE users
156SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
157WHERE id = 5;
158
159UPDATE users
160SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
161WHERE id = 6;
162
163UPDATE users
164SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
165WHERE id = 7;
166
167UPDATE users
168SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
169WHERE id = 8;
170
171UPDATE users
172SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
173WHERE id = 9;
174
175UPDATE users
176SET adress = '4 Privet drive', zip_code = '93666', city = 'Little Whinging', country = 'Sweden', cellphone = '06666666', phone = '011111111', date_inscription= '2015-05-15'
177WHERE id = 10;
178
179-- Display all conversations from one user (here 10)
180
181SELECT * FROM messages
182WHERE id_user_from = 10
183OR id_user_to = 10
184ORDER BY date_time_sended DESC;
185
186-- Display all messages between two users
187
188SELECT * FROM messages
189WHERE (id_user_from = 10 AND id_user_to = 3)
190OR (id_user_from = 3 AND id_user_to = 10)
191ORDER BY date_time_sended DESC;
192
193-- Display services except ones with subscribers and ones who are aleady passed, assuming today is 2019-09-13
194
195SELECT * FROM services
196WHERE id NOT IN (SELECT id_service FROM services_users) AND date_time > "2019-09-13 12:00:00";
197
198-- Display all details of a services
199
200SELECT * from services
201WHERE id = 5
202ORDER BY date_time DESC;
203
204-- Delete one service, testing if it deletes services subscriptions
205
206DELETE FROM services
207WHERE id = 9