-
Notifications
You must be signed in to change notification settings - Fork 0
/
music.sql
261 lines (187 loc) · 9.78 KB
/
music.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
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
DROP DATABASE IF EXISTS music;
CREATE DATABASE music;
GRANT ALL PRIVILEGES ON music.* to 'music'@'localhost';
USE music;
DROP TABLE IF EXISTS user;
CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(32) NOT NULL,
password varchar(35) NOT NULL,
email varchar(128) NOT NULL,
name varchar(64) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY username (username)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `roles`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `roles` (
`roleid` int(11) NOT NULL AUTO_INCREMENT,
`user_role` varchar(32) NOT NULL,
PRIMARY KEY (`roleid`),
UNIQUE KEY `user_role` (`user_role`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
LOCK TABLES `roles` WRITE;
/*!40000 ALTER TABLE `roles` DISABLE KEYS */;
INSERT INTO `roles` VALUES (2,'admin'),(1,'registered');
/*!40000 ALTER TABLE `roles` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `user_roles`
--
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `user_roles`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_roles` (
`userid` int(11) NOT NULL,
`roleid` int(11) NOT NULL,
`username` varchar(32) NOT NULL,
`user_role` varchar(32) NOT NULL,
PRIMARY KEY (`userid`,`roleid`),
KEY `roleid` (`roleid`),
CONSTRAINT `user_roles_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE CASCADE,
CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `roles` (`roleid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user_roles`
--
LOCK TABLES `user_roles` WRITE;
/*!40000 ALTER TABLE `user_roles` DISABLE KEYS */;
INSERT INTO `user_roles` VALUES (1,1,'Rog5','registered'),(2,1,'Andrea95','registered'),(3,1,'Nadimm','registered'),(4,1,'Anaannaa','registered'),(5,2,'admin','admin');
/*!40000 ALTER TABLE `user_roles` ENABLE KEYS */;
UNLOCK TABLES;
DROP TABLE IF EXISTS kind;
CREATE TABLE kind (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS genre;
CREATE TABLE genre (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS artist;
CREATE TABLE artist (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
idgenre1 int(11) NOT NULL,
idgenre2 int(11) NULL,
info varchar(150) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS event;
CREATE TABLE event (
id int(11) NOT NULL AUTO_INCREMENT,
idkind int(11) NOT NULL,
artist varchar(50) NOT NULL,
date datetime DEFAULT NULL,
place varchar(128) NULL,
city varchar(50) NULL,
country varchar(50) NOT NULL,
info varchar(150),
insertdate datetime NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (idkind) REFERENCES kind(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (artist) REFERENCES artist(name) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS follow;
CREATE TABLE follow (
id int(11) NOT NULL AUTO_INCREMENT,
iduser int(11) NOT NULL,
idartist int(11) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (iduser) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (idartist) REFERENCES artist(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS assist;
CREATE TABLE assist (
id int(11) NOT NULL AUTO_INCREMENT,
iduser int(11) NOT NULL,
idevent int(11) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (iduser) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (idevent) REFERENCES event(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO user VALUES (NULL,"Rog5", MD5("test"), "[email protected]", "Roger");
INSERT INTO user VALUES (NULL,"Andrea95", MD5("test"), "[email protected]", "Andrea");
INSERT INTO user VALUES (NULL,"Nadimm", MD5("test"), "[email protected]", "Nadim");
INSERT INTO user VALUES (NULL,"Anaannaa", MD5("test"), "[email protected]", "Anna");
INSERT INTO user VALUES (NULL,"admin", MD5("admin"), "[email protected]", "Administrador");
INSERT INTO artist VALUES (NULL, "Daft Punk", 4, NULL, "Más humanos que nunca");
INSERT INTO artist VALUES (NULL, "Robbie Williams", 2, 1, "Hace gritas a las chicas");
INSERT INTO artist VALUES (NULL, "David Bisbal", 2, NULL, "rizitos");
INSERT INTO artist VALUES (NULL, "The XX", 4, NULL, "Al escuchar el tema intro uno piensa en Person of Interest");
INSERT INTO artist VALUES (NULL, "Florence", 4, 1, "Grupo imprescindible");
INSERT INTO kind VALUES (NULL, "Concert");
INSERT INTO kind VALUES (NULL, "Studio Album Release");
INSERT INTO kind VALUES (NULL, "Videoclip Release");
INSERT INTO genre VALUES (NULL, "Rock");
INSERT INTO genre VALUES (NULL, "Pop");
INSERT INTO genre VALUES (NULL, "Classic");
INSERT INTO genre VALUES (NULL, "Electronic");
INSERT INTO event VALUES (NULL, 1, "Florence", "2013-09-20 22:00:00", "Palau Sant Jordi", "Barcelona", "Catalunya", "Awesome!", NOW());
INSERT INTO event VALUES (NULL, 1, "Robbie Williams", "2013-09-21 22:00:00", "Palau Joventut", "Badalona", "Catalunya", "Awesome++!", NOW());
INSERT INTO event VALUES (NULL, 1, "Daft Punk", "2013-09-21 22:45:00", "Palau Joventut", "Badalona", "Catalunya", "Awesome++!", NOW());
INSERT INTO event VALUES (NULL, 2, "Robbie Williams", "2013-09-20 22:00:00", NULL, NULL, "Catalunya", "Nuevo disco", NOW());
INSERT INTO event VALUES (NULL, 2, "The XX", "2013-09-20 22:00:00", NULL, NULL, "Catalunya", "Nuevo disco", NOW());
INSERT INTO event VALUES (NULL, 2, "Florence", "2013-09-20 22:00:00", NULL, NULL, "Catalunya", "Nuevo disco", NOW());
INSERT INTO event VALUES (NULL, 3, "Florence", "2013-09-20 22:00:00", NULL, NULL, "Spain", "Nuevo video", NOW());
INSERT INTO event VALUES (NULL, 3, "Daft Punk", "2013-09-20 22:00:00", NULL, NULL, "Spain", "Nuevo video", NOW());
INSERT INTO event VALUES (NULL, 3, "The XX", "2013-09-20 22:00:00", NULL, NULL, "Spain", "Nuevo video", NOW());
INSERT INTO follow VALUES (NULL, 1, 1);
INSERT INTO follow VALUES (NULL, 1, 2);
INSERT INTO follow VALUES (NULL, 1, 3);
INSERT INTO follow VALUES (NULL, 1, 5);
INSERT INTO follow VALUES (NULL, 2, 3);
INSERT INTO follow VALUES (NULL, 3, 4);
INSERT INTO follow VALUES (NULL, 4, 1);
INSERT INTO follow VALUES (NULL, 4, 4);
INSERT INTO assist VALUES(NULL,1,1);
INSERT INTO assist VALUES(NULL,1,2);
INSERT INTO assist VALUES(NULL,2,3);
/* Another: working
INSERT INTO artist VALUES (NULL, 'Florence', 4, NULL, 'Grupo imprescindible');
SELECT * FROM artist;
SELECT * FROM artist WHERE name='Florence';
DELETE FROM artist WHERE name ='Florence';
UPDATE artist SET info='Vocalist kissed by fire' WHERE name='Florence';
UPDATE user SET password='admin' WHERE name='admin';
INSERT INTO event VALUES (NULL, 1, 'Florence', '2013-09-20 22:00:00', 'Palau Sant Jordi', 'Barcelona', 'Catalunya', 'Va a ser inolvidable', NOW());
UPDATE event SET date='2014-09-20 22:00:00', place='Palau Joventut', city='Badalona', country='Catalunya', info='new Location' WHERE artist='Florence';
SELECT * FROM event WHERE id = 1 AND artist = 'Florence';
SELECT * FROM event WHERE artist='Florence';
SELECT * FROM event WHERE artist='Florence' AND city='Badalona';
SELECT * FROM event WHERE artist='Florence' AND idkind=1;
SELECT * FROM event WHERE artist='Florence' AND idkind=1 AND city='Badalona';
DELETE FROM event WHERE id=2 and artist='Florence';
SELECT name FROM genre WHERE id=(SELECT idgenre1 FROM artist WHERE id=1);
SELECT name FROM genre WHERE id=1;
SELECT idartist FROM follow WHERE iduser=1;
SELECT id FROM user WHERE username='ubuntu';
SELECT name FROM artist WHERE id=1;
SELECT name FROM artist where id=idartist;
SELECT * FROM event WHERE artist='Florence' AND idkind=1;
SELECT id FROM kind WHERE name ='Concert';
UPDATE user SET password=MD5("test"), email='[email protected]', name='roger3';
INSERT INTO assist VALUES(NULL,1,1);
INSERT INTO assist VALUES(NULL,1,2);
INSERT INTO assist VALUES(NULL,2,3);
SELECT idevent FROM assist WHERE iduser=1;
SELECT follow.idartist, follow.iduser FROM follow INNER JOIN user ON user.id=follow.iduser ORDER BY user.id;
SELECT follow.idartist, follow.iduser from follow inner join user on user.id=follow.iduser where user.id=1;
SELECT * FROM event INNER JOIN artist ON artist.name=event.artist WHERE artist.name=(SELECT name FROM artist WHERE id=1);
SELECT artist.id FROM artist INNER JOIN event on event.artist=artist.name;
SELECT artist.id FROM artist INNER JOIN event on event.artist=artist.name AND event.id=1;
DELETE FROM assist WHERE idevent=1 and iduser=1;
SELECT artist.id, artist.name, artist.idgenre1, artist.idgenre2, artist.info FROM artist INNER JOIN follow on follow.idartist=artist.id and follow.iduser=1 order by name;
SELECT event.id, event.artist, artist.name FROM event INNER JOIN artist ON artist.name=event.artist WHERE artist.name=(SELECT name FROM artist WHERE id=1);
SELECT e.id, a.idevent, e.artist, a.iduser FROM event e INNER JOIN assist a INNER JOIN follow f ON e.id=a.idevent AND a.iduser=f.iduser WHERE f.iduser=1 AND e.artist=(SELECT name FROM artist a INNER JOIN follow f ON a.id = f.idartist WHERE f.iduser=1);
SELECT e.id, a.idevent, e.artist, a.iduser FROM event e INNER JOIN assist a INNER JOIN follow f ON e.id=a.idevent AND a.iduser=f.iduser WHERE f.iduser=1 AND e.artist=(SELECT name FROM artist a WHERE a.id=5);
*/