-
Notifications
You must be signed in to change notification settings - Fork 0
/
data.sql
187 lines (187 loc) · 3.8 KB
/
data.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
DROP DATABASE IF EXISTS php_vpn;
CREATE DATABASE IF NOT EXISTS php_vpn;
USE php_vpn;
SELECT 'CREATING DATABASE STRUCTURE' AS 'INFO';
DROP TABLE IF EXISTS users,
rols,
VPN;
-- ------------------ --
-- CREATE TABLE users --
-- ------------------ --
CREATE TABLE users(
user_id INT AUTO_INCREMENT NOT NULL,
user_name VARCHAR(50) NOT NULL,
user_surname VARCHAR(50) NOT NULL,
user_email VARCHAR(50) NOT NULL,
user_phone INT(9) NOT NULL,
user_pass VARCHAR(100) NOT NULL,
user_rol INT(11) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(user_id)
);
-- Insert into TABLE
INSERT INTO users(
user_name,
user_surname,
user_email,
user_phone,
user_pass,
user_rol
)
VALUES(
'John',
'Smith',
604978305,
'$2y$10$JFuupR/vZBQwugUhzZzFTOKZLx4.ufOpONnaPbq2m5O2yWhFHKEVm',
1
),
(
'Robert',
'Smith',
655978350,
'$2y$10$apjtN/2avA9kYnna01HuROQ0g/AxpQZYRjQKR4sHq8.LMEtAQWeb6',
2
);
-- ------------------ --
-- CREATE TABLE rols --
-- ------------------ --
CREATE TABLE rols(
user_id INT NOT NULL,
rols VARCHAR(50) NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
PRIMARY KEY(user_id, rols)
);
-- Insert into TABLE
INSERT INTO rols (user_id, rols)
VALUES (1, 'Administrator'),
(2, 'Client');
-- ---------------- --
-- CREATE TABLE VPN --
-- ---------------- --
CREATE TABLE VPN(
vpn_id INT AUTO_INCREMENT NOT NULL,
vpn_country VARCHAR(15) NOT NULL,
vpn_city VARCHAR(15) NOT NULL,
vpn_ip_address VARCHAR(15) NOT NULL,
vpn_ip_route VARCHAR(15) NOT NULL,
vpn_isp VARCHAR(40) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(vpn_id)
);
-- Insert into TABLE
INSERT INTO VPN(
vpn_country,
vpn_city,
vpn_ip_address,
vpn_ip_route,
vpn_isp
)
VALUES(
'Germany',
'Frankfurt',
'157.230.20.59',
'14061',
'DigitalOcean LLC'
),
(
'Netherlands',
'Amsterdam',
'165.232.82.219',
'14061',
'DigitalOcean LLC'
),
(
'England',
'London',
'167.71.140.172',
'14061',
'DigitalOcean LLC'
),
(
'Finland',
'Helsinki',
'194.34.132.106',
'14061',
'Oy Creanova Ltd.'
),
(
'Greece',
'Tiraspol',
'195.146.4.42',
'201924',
'NAPRI s.r.o.'
),
(
'Nigeria',
'Lagos',
'104.166.144.87',
'21859',
'Zenlayer Inc'
),
(
'United States',
'Clifton',
'104.154.2.394',
'14061',
'DigitalOcean LLC'
),
(
'United States',
'California',
'102.154.2.394',
'14061',
'DigitalOcean LLC'
),
(
'United States',
'Los Angeles',
'102.154.2.394',
'15430',
'DigitalOcean LLC'
),
(
'United States',
'Miami',
'104.154.2.394',
'14061',
'DigitalOcean LLC'
),
(
'United States',
'Chicage',
'124.154.26.394',
'14061',
'DigitalOcean LLC'
),
(
'Peru',
'Lima',
'200.25.45.232',
'7195',
'EdgeUno ID'
),
(
'Argentina',
'Buenos Aires',
'200.25.50.69',
'7195',
'EdgeUno ID'
),
(
'Colombia',
'Cartagena',
'123.654.374.82',
'7195',
'CFibra informatica S.A'
);
-- ---------------- --
-- SELECT LEFT JOIN --
-- ---------------- --
SELECT u.user_id,
u.user_email,
u.user_pass,
r.rols
FROM users u
LEFT JOIN rols r ON u.user_rol = r.user_id;