-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.sql
136 lines (132 loc) · 9.24 KB
/
database.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
create table players(
username varchar(255) primary key,
email varchar(255) not null,
password_hash varchar(255) not null
);
create table games(
id auto_increment primary key,
player1 varchar(255) not null,
player2 varchar(255) not null,
player3 varchar(255) not null,
winner varchar(255) not null,
winner_goal varchar(255) not null,
match_year int not null,
match_month int not null,
match_day int not null,
foreign key(player1) references players(username) constraint player1_fk,
foreign key(player2) references players(username) constraint player2_fk,
foreign key(player3) references players(username) constraint player3_fk,
foreign key(winner) references players(username) constraint winner_fk,
check(player1 != player2 and player1 != player3 and player2 != player3) constraint players_different,
check(winner = player1 or winner = player2 or winner = player3) constraint winner_in_players,
check(winner_goal in ('asia', 'africa', 'europe', 'north-america', 'south-america', 'oceania')) constraint winner_goal_valid,
check(match_month between 1 and 12) constraint match_month_valid,
check(match_day between 1 and 31) constraint match_day_valid
);
insert into players(username, email, password_hash) values
('cristian', 'cristian@email.com', '$2b$12$DZUa.I0rNkS6e.dCp.OnmeDODDk8No.2rkhn2lWU0s0JME4Ah/I6O'),
('gieri', 'gieri@email.com', '$2b$12$jrFPXMFZIPP4n3/L/N4glOW3/asgMLEcGxKZyC8YGAcgViVTAZrpK'),
('pietro', 'pietro@email.com', '$2b$12$n/5L4rJbKXj.DAJjgMONGOktzePquyRt/4Zx8KXD1LqqubFHhAuGO'),
('amin', 'amin@email.com', '$2b$12$kH5p0.IBI3WdKPqvX9JHS.cpgocDrAoT0/2KIHsAel7pvWmjtNCY2'),
('leonardo', 'leonardo@email.com', '$2b$12$kuhB4WrX5FTv.VEJ8Fe1i.FFsdKsjZxxMrkh/e5QDNAAaxZC6NDh2'),
('gabbo', 'gabbo@gmail.com', '$2b$12$Ah9Ke.rHg9aVVFzgf95N9unvb1MeOGocxNwXA5RdJG4AnofXd2CJS'),
('cri', 'cri@email.com', '$2b$12$IZwCHhXPHv8hbvnTDweW3OQ47kdqXgAsA/6vtg2WB11t1jl4G3oyK'),
('giuseppe', 'giuseppe@gmail.com', '$2b$12$k0TSE3MXbM7pkvG.6abyHeRWkIso1C1bLwb94hI3zi6Hww2MtXn6C'),
('federico', 'federico@gmail.com', '$2b$12$qUL1apy4Lq/twRt9moyOguqFH4Q9kwxR3C8maDtH/dVD9s1uEGLu2'),
('alberto', 'alberto@email.com', '$2b$12$Ule.f77PYLI/94FyQ3qQpuRfTjDU.lh51HT9xXFJgWeoMrFmMmwqC'),
('edoardo', 'edoardo@email.com', '$2b$12$Gl.qe5ZMxOZ/ivbQbA6hV.oFISJDspvwxl8VF/TYfYfnfPq8gFyEm'),
('giorgio', 'giorgio@email.com', '$2b$12$E0ttNngct8J4H7MTWYQt0O1IrkX8BVBwsNI3Fyq9XRwOJ3tnwUZfm'),
('emanuel', 'emanuel@email.com', '$2b$12$29BPrtSyOOAzC8233myOQuoZcADPrmsRWlZfoUjawUDShJWqUx.Aq'),
('letizia', 'letizia@email.com', '$2b$12$u72K1qqZQByaONROckTx6.Drcbzz3YPHh5TPB27.jxsrmxwSaMwKu'),
('francesca', 'francesca@email.com', '$2b$12$Gc1MyerdoX8oEGFuG1njf.WfNKSavrqNEaVq9SwodVGpNYUM.kYiG'),
('ermenegildo', 'ermenegildo@email.com', '$2b$12$8mYWdTF2d04/YFtN0P8e6.oYpJ5rRvwyINJpSQB3cf3ZoKIG35fZa'),
('gianfranco', 'gianfranco@email.com', '$2b$12$jfvCNNd/d8SmjYqZmmNgA.YzYJ.iilo61geFegba.KCtaIYmcGcBq'),
('maria', 'maria@email.com', '$2b$12$0Ban.pNYZjzcB5Zhn7o/2.3htU.qSlr13qWx3pmhkKGlyD1/RAG/u'),
('mario', 'mario@email.com', '$2b$12$ntONnCr4tGTlwsK5PlB4l.VtJTCQQDLX8yz7g3kbLQ.xEWQGfdKna'),
('luigi', 'luigi@email.com', '$2b$12$NCfvMbIHaBVSEfecMPbxi.XQquLzEpwtCUiH6jSF6tOJzNxkNy8Ye');
insert into games(id,player1, player2, player3, winner, winner_goal,match_year,match_month,match_day) values
(1, 'cristian', 'gieri', 'pietro', 'cristian', 'asia',2024,01,01),
(2, 'amin', 'leonardo', 'gabbo', 'leonardo', 'africa',2024,01,01),
(3, 'cri', 'giuseppe', 'federico', 'giuseppe', 'europe',2024,01,01),
(4, 'alberto', 'edoardo', 'giorgio', 'edoardo', 'north-america',2024,01,01),
(5, 'emanuel', 'letizia', 'francesca', 'letizia', 'south-america',2024,01,01),
(6, 'ermenegildo', 'gianfranco', 'maria', 'gianfranco', 'oceania',2024,01,01),
(7, 'mario', 'luigi', 'cristian', 'mario', 'north-america',2024,01,01),
(8, 'gieri', 'pietro', 'amin', 'amin', 'asia',2024,01,01),
(9, 'leonardo', 'gabbo', 'cri', 'cri', 'africa',2024,01,01),
(10, 'giuseppe', 'federico', 'alberto', 'federico', 'europe',2024,01,01),
(11, 'edoardo', 'giorgio', 'emanuel', 'giorgio', 'north-america',2024,01,01),
(12, 'letizia', 'francesca', 'ermenegildo', 'francesca', 'south-america',2024,01,01),
(13, 'gianfranco', 'maria', 'mario', 'maria', 'oceania',2024,01,01),
(14, 'luigi', 'cristian', 'gieri', 'luigi', 'north-america',2024,01,01),
(15, 'pietro', 'amin', 'leonardo', 'pietro', 'asia',2024,01,01),
(16, 'gabbo', 'cri', 'giuseppe', 'gabbo', 'africa',2024,01,01),
(17, 'federico', 'alberto', 'edoardo', 'alberto', 'europe',2024,01,01),
(18, 'giorgio', 'emanuel', 'letizia', 'emanuel', 'north-america',2024,01,01),
(19, 'francesca', 'ermenegildo', 'gianfranco', 'ermenegildo', 'south-america',2024,01,01),
(20, 'maria', 'mario', 'luigi', 'mario', 'oceania',2024,01,01),
(21, 'cristian', 'gieri', 'pietro', 'cristian', 'north-america',2024,01,01),
(22, 'amin', 'leonardo', 'gabbo', 'amin', 'asia',2024,01,01),
(23, 'cri', 'giuseppe', 'federico', 'giuseppe', 'africa',2024,01,01),
(24, 'alberto', 'edoardo', 'giorgio', 'giorgio', 'europe',2024,01,01),
(25, 'emanuel', 'letizia', 'francesca', 'emanuel', 'north-america',2024,01,01),
(26, 'ermenegildo', 'gianfranco', 'maria', 'gianfranco', 'south-america',2024,01,01),
(27, 'mario', 'luigi', 'cristian', 'mario', 'oceania',2024,01,01),
(28, 'gieri', 'pietro', 'amin', 'gieri', 'north-america',2024,01,01),
(29, 'leonardo', 'gabbo', 'cri', 'gabbo', 'asia',2024,01,01),
(30, 'giuseppe', 'federico', 'alberto', 'giuseppe', 'africa',2024,01,01),
(31, 'edoardo', 'giorgio', 'emanuel', 'giorgio', 'europe',2024,01,01),
(32, 'letizia', 'francesca', 'ermenegildo', 'letizia', 'north-america',2024,01,01),
(33, 'gianfranco', 'maria', 'mario', 'mario', 'south-america',2024,01,01),
(34, 'luigi', 'cristian', 'gieri', 'luigi', 'oceania',2024,01,01),
(35, 'pietro', 'amin', 'leonardo', 'pietro', 'north-america',2024,01,01),
(36, 'gabbo', 'cri', 'giuseppe', 'gabbo', 'asia',2024,01,01),
(37, 'federico', 'alberto', 'edoardo', 'federico', 'africa',2024,01,01),
(38, 'giorgio', 'emanuel', 'letizia', 'giorgio', 'europe',2024,01,01),
(39, 'francesca', 'ermenegildo', 'gianfranco', 'gianfranco', 'north-america',2024,01,01),
(40, 'maria', 'mario', 'luigi', 'mario', 'south-america',2024,01,01),
(41, 'cristian', 'gieri', 'pietro', 'cristian', 'oceania',2024,01,01),
(42, 'amin', 'leonardo', 'gabbo', 'amin', 'south-america',2024,01,01),
(43, 'cri', 'giuseppe', 'federico', 'giuseppe', 'asia',2024,01,01),
(44, 'cristian', 'amin', 'cri', 'amin', 'south-america',2024,01,01),
(45, 'gieri', 'leonardo', 'giuseppe', 'leonardo', 'asia',2024,01,01),
(46, 'pietro', 'gabbo', 'alberto', 'gabbo', 'africa',2024,01,01),
(47, 'amin', 'cri', 'edoardo', 'cri', 'europe',2024,01,01),
(48, 'leonardo', 'giuseppe', 'giorgio', 'giuseppe', 'north-america',2024,01,01),
(49, 'gabbo', 'alberto', 'emanuel', 'alberto', 'south-america',2024,01,01),
(50, 'cri', 'edoardo', 'letizia', 'edoardo', 'oceania',2024,01,01),
(51, 'giuseppe', 'giorgio', 'ermenegildo', 'giorgio', 'south-america',2024,01,01),
(52, 'alberto', 'emanuel', 'gianfranco', 'emanuel', 'asia',2024,01,01),
(53, 'edoardo', 'letizia', 'maria', 'letizia', 'africa',2024,01,01),
(54, 'giorgio', 'ermenegildo', 'mario', 'ermenegildo', 'europe',2024,01,01),
(55, 'emanuel', 'gianfranco', 'luigi', 'gianfranco', 'north-america',2024,01,01),
(56, 'letizia', 'maria', 'cristian', 'maria', 'south-america',2024,01,01),
(57, 'ermenegildo', 'mario', 'gieri', 'mario', 'oceania',2024,01,01),
(58, 'gianfranco', 'luigi', 'pietro', 'luigi', 'europe',2024,01,01),
(59, 'maria', 'cristian', 'amin', 'cristian', 'asia',2024,01,01),
(60, 'mario', 'gieri', 'leonardo', 'gieri', 'africa',2024,01,01),
(61, 'luigi', 'pietro', 'gabbo', 'pietro', 'europe',2024,01,01),
(62, 'cristian', 'amin', 'cri', 'amin', 'north-america',2024,01,01),
(63, 'gieri', 'leonardo', 'giuseppe', 'leonardo', 'south-america',2024,01,01),
(64, 'pietro', 'gabbo', 'alberto', 'gabbo', 'oceania',2024,01,01),
(65, 'amin', 'cri', 'edoardo', 'cri', 'europe',2024,01,01),
(66, 'leonardo', 'giuseppe', 'giorgio', 'giuseppe', 'asia',2024,01,01),
(67, 'gabbo', 'alberto', 'emanuel', 'alberto', 'africa',2024,01,01),
(68, 'cri', 'edoardo', 'letizia', 'edoardo', 'europe',2024,01,01),
(69, 'giuseppe', 'giorgio', 'ermenegildo', 'giorgio', 'north-america',2024,01,01),
(70, 'alberto', 'emanuel', 'gianfranco', 'emanuel', 'south-america',2024,01,01),
(71, 'edoardo', 'letizia', 'maria', 'letizia', 'oceania',2024,01,01),
(72, 'giorgio', 'ermenegildo', 'mario', 'ermenegildo', 'europe',2024,01,01),
(73, 'emanuel', 'gianfranco', 'luigi', 'gianfranco', 'asia',2024,01,01),
(74, 'letizia', 'maria', 'cristian', 'maria', 'africa',2024,01,01),
(75, 'ermenegildo', 'mario', 'gieri', 'mario', 'europe',2024,01,01),
(76, 'gianfranco', 'luigi', 'pietro', 'luigi', 'north-america',2024,01,01),
(77, 'maria', 'cristian', 'amin', 'cristian', 'south-america',2024,01,01),
(78, 'mario', 'gieri', 'leonardo', 'gieri', 'oceania',2024,01,01),
(79, 'luigi', 'pietro', 'gabbo', 'pietro', 'europe',2024,01,01),
(80, 'cristian', 'amin', 'cri', 'amin', 'asia',2024,01,01),
(81, 'gieri', 'leonardo', 'giuseppe', 'leonardo', 'africa',2024,01,01),
(82, 'pietro', 'gabbo', 'alberto', 'gabbo', 'europe',2024,01,01),
(83, 'amin', 'cri', 'edoardo', 'cri', 'north-america',2024,01,01),
(84, 'leonardo', 'giuseppe', 'giorgio', 'giuseppe', 'south-america',2024,01,01),
(85, 'giorgio', 'cristian', 'amin', 'cristian', 'north-america',2024,01,01);