forked from AracelyCondoriT/ingsoft
-
Notifications
You must be signed in to change notification settings - Fork 1
/
MING_create_V3.sql
440 lines (390 loc) · 13.9 KB
/
MING_create_V3.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
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
CREATE DATABASE ming DEFAULT CHARACTER SET 'utf8' DEFAULT COLLATE utf8_general_ci;
-- Created by Vertabelo (http://vertabelo.com)
-- Last modification date: 2020-11-14 21:49:39.527
-- tables
-- Table: countries
CREATE TABLE countries (
id_country int NOT NULL AUTO_INCREMENT,
name varchar(40) NULL,
status int NOT NULL COMMENT 'countrie''''s status',
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT countries_pk PRIMARY KEY (id_country)
) COMMENT 'User''''s countries';
-- Table: developers
CREATE TABLE developers (
id_developer int NOT NULL AUTO_INCREMENT,
id_publisher int NOT NULL,
developer varchar(50) NOT NULL COMMENT 'Nombre de la desarrollada que es parte del Publisher:
Por ejemplo:
....',
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT developers_pk PRIMARY KEY (id_developer)
);
-- Table: directx
CREATE TABLE directx (
id_directx int NOT NULL AUTO_INCREMENT,
version varchar(30) NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT directx_pk PRIMARY KEY (id_directx)
);
-- Table: esrb
CREATE TABLE esrb (
id_esrb int NOT NULL AUTO_INCREMENT,
esrb varchar(20) NULL,
logo_path varchar(200) NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT esrb_pk PRIMARY KEY (id_esrb)
);
-- Table: game_requirements
CREATE TABLE game_requirements (
id_game_requirement int NOT NULL AUTO_INCREMENT,
id_game int NOT NULL,
id_operating_system int NOT NULL,
processor varchar(25) NOT NULL,
memory varchar(10) NOT NULL,
graphics varchar(50) NOT NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT game_requirements_pk PRIMARY KEY (id_game_requirement)
);
-- Table: games
CREATE TABLE games (
id_game int NOT NULL AUTO_INCREMENT,
id_esrb int NOT NULL,
id_developer int NOT NULL,
name varchar(75) NOT NULL,
description text NULL,
size varchar(10) NULL,
players int NULL,
release_date date NULL,
color varchar(10) NULL COMMENT 'Tiene la siguiente estructura:
"#464646"',
highlight int NULL COMMENT '0 = not highlighted
1 = highlighted',
download_path varchar(200) NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT games_pk PRIMARY KEY (id_game)
);
-- Table: games_directx
CREATE TABLE games_directx (
id_games_direct_x int NOT NULL AUTO_INCREMENT,
id_game int NOT NULL,
id_directx int NOT NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT games_directx_pk PRIMARY KEY (id_games_direct_x)
);
-- Table: games_os
CREATE TABLE games_os (
id_games_os int NOT NULL AUTO_INCREMENT,
id_game int NOT NULL,
id_operating_system int NOT NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT games_os_pk PRIMARY KEY (id_games_os)
);
-- Table: genres
CREATE TABLE genres (
id_genre int NOT NULL AUTO_INCREMENT,
genre varchar(25) NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT genres_pk PRIMARY KEY (id_genre)
);
-- Table: genres_games
CREATE TABLE genres_games (
id_genre_game int NOT NULL AUTO_INCREMENT,
id_game int NOT NULL,
id_genre int NOT NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT genres_games_pk PRIMARY KEY (id_genre_game)
);
-- Table: h_games
CREATE TABLE h_games (
h_id_game int NOT NULL AUTO_INCREMENT,
id_game int NOT NULL,
id_esrb int NOT NULL,
id_developer int NOT NULL,
name varchar(75) NOT NULL,
description text NULL,
size varchar(10) NULL,
players int NULL,
release_date date NULL,
color varchar(10) NULL,
highlight int NULL,
download_path varchar(200) NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT h_games_pk PRIMARY KEY (h_id_game)
);
-- Table: languages
CREATE TABLE languages (
id_language int NOT NULL AUTO_INCREMENT,
language varchar(15) NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT languages_pk PRIMARY KEY (id_language)
);
-- Table: languages_games
CREATE TABLE languages_games (
id_languages_games int NOT NULL AUTO_INCREMENT,
id_game int NOT NULL,
id_language int NOT NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT languages_games_pk PRIMARY KEY (id_languages_games)
);
-- Table: library
CREATE TABLE library (
id_library int NOT NULL AUTO_INCREMENT,
id_user int NOT NULL,
id_game int NOT NULL,
download int NOT NULL,
status int NOT NULL COMMENT '0 = deleted
1 = active',
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT library_pk PRIMARY KEY (id_library)
);
-- Table: operating_system
CREATE TABLE operating_system (
id_operating_system int NOT NULL AUTO_INCREMENT,
operating_system varchar(50) NOT NULL,
logo_path varchar(200) NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT operating_system_pk PRIMARY KEY (id_operating_system)
);
-- Table: order_details
CREATE TABLE order_details (
id_order_details int NOT NULL AUTO_INCREMENT,
id_game int NOT NULL,
id_order int NOT NULL,
price numeric(10,6) NOT NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT order_details_pk PRIMARY KEY (id_order_details)
);
-- Table: orders
CREATE TABLE orders (
id_order int NOT NULL AUTO_INCREMENT,
id_user int NOT NULL,
date timestamp NOT NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT orders_pk PRIMARY KEY (id_order)
);
-- Table: photos
CREATE TABLE photos (
id_photos int NOT NULL AUTO_INCREMENT,
id_game int NOT NULL,
photo_path varchar(200) NULL,
type int NULL COMMENT 'Types
1 = Banner
2 = Normal Photo',
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT photos_pk PRIMARY KEY (id_photos)
);
-- Table: price
CREATE TABLE price (
id_price int NOT NULL AUTO_INCREMENT,
id_game int NOT NULL,
price numeric(10,6) NOT NULL,
date timestamp NULL,
sale int NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT price_pk PRIMARY KEY (id_price)
);
-- Table: publisher_earnings
CREATE TABLE publisher_earnings (
id_publisher_earning int NOT NULL AUTO_INCREMENT,
id_publisher int NOT NULL,
amount numeric(10,6) NOT NULL COMMENT 'Cantidad desembolsada',
expenditure_date timestamp NOT NULL COMMENT 'Fecha de desembolso',
card varchar(100) NOT NULL,
status int NOT NULL,
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT publisher_earnings_pk PRIMARY KEY (id_publisher_earning)
);
-- Table: publishers
CREATE TABLE publishers (
id_publisher int NOT NULL AUTO_INCREMENT,
id_user int NOT NULL,
publisher varchar(50) NOT NULL COMMENT 'Nombre del publisher',
paypal_mail varchar(100) NULL,
status int NOT NULL COMMENT '0 = deleted
1 = active',
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT publishers_pk PRIMARY KEY (id_publisher)
);
-- Table: transactions
CREATE TABLE transactions (
tx_id int NOT NULL AUTO_INCREMENT,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT transactions_pk PRIMARY KEY (tx_id)
);
-- Table: users
CREATE TABLE users (
id_user int NOT NULL AUTO_INCREMENT,
id_country int NOT NULL,
username varchar(20) NOT NULL,
password varchar(35) NOT NULL,
email varchar(100) NOT NULL,
user_type int NOT NULL COMMENT 'Existen diferentes tipos de usuarios.
0: user
1: publisher/developer
2: admin',
photo_path varchar(200) NULL,
name varchar(40) NULL,
lastname varchar(50) NULL,
alias varchar(50) NULL,
status int NOT NULL COMMENT '0 = deleted
1 = active',
tx_id int NOT NULL,
tx_host varchar(100) NOT NULL,
tx_user_id int NOT NULL,
tx_date timestamp NOT NULL,
CONSTRAINT users_pk PRIMARY KEY (id_user)
) COMMENT 'All the users';
-- foreign keys
-- Reference: developers_publishers (table: developers)
ALTER TABLE developers ADD CONSTRAINT developers_publishers FOREIGN KEY developers_publishers (id_publisher)
REFERENCES publishers (id_publisher);
-- Reference: game_requirements_games (table: game_requirements)
ALTER TABLE game_requirements ADD CONSTRAINT game_requirements_games FOREIGN KEY game_requirements_games (id_game)
REFERENCES games (id_game);
-- Reference: game_requirements_operating_system (table: game_requirements)
ALTER TABLE game_requirements ADD CONSTRAINT game_requirements_operating_system FOREIGN KEY game_requirements_operating_system (id_operating_system)
REFERENCES operating_system (id_operating_system);
-- Reference: games_developers (table: games)
ALTER TABLE games ADD CONSTRAINT games_developers FOREIGN KEY games_developers (id_developer)
REFERENCES developers (id_developer);
-- Reference: games_directx_directx (table: games_directx)
ALTER TABLE games_directx ADD CONSTRAINT games_directx_directx FOREIGN KEY games_directx_directx (id_directx)
REFERENCES directx (id_directx);
-- Reference: games_directx_games (table: games_directx)
ALTER TABLE games_directx ADD CONSTRAINT games_directx_games FOREIGN KEY games_directx_games (id_game)
REFERENCES games (id_game);
-- Reference: games_esrb (table: games)
ALTER TABLE games ADD CONSTRAINT games_esrb FOREIGN KEY games_esrb (id_esrb)
REFERENCES esrb (id_esrb);
-- Reference: games_os_games (table: games_os)
ALTER TABLE games_os ADD CONSTRAINT games_os_games FOREIGN KEY games_os_games (id_game)
REFERENCES games (id_game);
-- Reference: games_os_operating_system (table: games_os)
ALTER TABLE games_os ADD CONSTRAINT games_os_operating_system FOREIGN KEY games_os_operating_system (id_operating_system)
REFERENCES operating_system (id_operating_system);
-- Reference: genres_games_games (table: genres_games)
ALTER TABLE genres_games ADD CONSTRAINT genres_games_games FOREIGN KEY genres_games_games (id_game)
REFERENCES games (id_game);
-- Reference: genres_games_genres (table: genres_games)
ALTER TABLE genres_games ADD CONSTRAINT genres_games_genres FOREIGN KEY genres_games_genres (id_genre)
REFERENCES genres (id_genre);
-- Reference: languages_games_games (table: languages_games)
ALTER TABLE languages_games ADD CONSTRAINT languages_games_games FOREIGN KEY languages_games_games (id_game)
REFERENCES games (id_game);
-- Reference: languages_games_languages (table: languages_games)
ALTER TABLE languages_games ADD CONSTRAINT languages_games_languages FOREIGN KEY languages_games_languages (id_language)
REFERENCES languages (id_language);
-- Reference: library_games (table: library)
ALTER TABLE library ADD CONSTRAINT library_games FOREIGN KEY library_games (id_game)
REFERENCES games (id_game);
-- Reference: library_users (table: library)
ALTER TABLE library ADD CONSTRAINT library_users FOREIGN KEY library_users (id_user)
REFERENCES users (id_user);
-- Reference: order_details_games (table: order_details)
ALTER TABLE order_details ADD CONSTRAINT order_details_games FOREIGN KEY order_details_games (id_game)
REFERENCES games (id_game);
-- Reference: order_details_orders (table: order_details)
ALTER TABLE order_details ADD CONSTRAINT order_details_orders FOREIGN KEY order_details_orders (id_order)
REFERENCES orders (id_order);
-- Reference: orders_users (table: orders)
ALTER TABLE orders ADD CONSTRAINT orders_users FOREIGN KEY orders_users (id_user)
REFERENCES users (id_user);
-- Reference: photos_games (table: photos)
ALTER TABLE photos ADD CONSTRAINT photos_games FOREIGN KEY photos_games (id_game)
REFERENCES games (id_game);
-- Reference: price_games (table: price)
ALTER TABLE price ADD CONSTRAINT price_games FOREIGN KEY price_games (id_game)
REFERENCES games (id_game);
-- Reference: publisher_earnings_publishers (table: publisher_earnings)
ALTER TABLE publisher_earnings ADD CONSTRAINT publisher_earnings_publishers FOREIGN KEY publisher_earnings_publishers (id_publisher)
REFERENCES publishers (id_publisher);
-- Reference: publishers_users (table: publishers)
ALTER TABLE publishers ADD CONSTRAINT publishers_users FOREIGN KEY publishers_users (id_user)
REFERENCES users (id_user);
-- Reference: users_countries (table: users)
ALTER TABLE users ADD CONSTRAINT users_countries FOREIGN KEY users_countries (id_country)
REFERENCES countries (id_country);
-- End of file.