· 7 years ago · Oct 11, 2018, 10:26 PM
1
2
3SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
4SET AUTOCOMMIT = 0;
5START TRANSACTION;
6SET time_zone = "+00:00";
7
8
9/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
10/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
11/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
12/*!40101 SET NAMES utf8mb4 */;
13
14--
15-- Database: `emulous`
16--
17CREATE DATABASE IF NOT EXISTS `emulous` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
18USE `emulous`;
19
20-- --------------------------------------------------------
21
22--
23-- Table structure for table `asn_blacklist`
24--
25
26CREATE TABLE `asn_blacklist` (
27 `asn` int(11) NOT NULL,
28 `isp` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
29 `reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
30) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
31
32-- --------------------------------------------------------
33
34--
35-- Table structure for table `chat_logs`
36--
37
38CREATE TABLE `chat_logs` (
39 `id` bigint(20) NOT NULL,
40 `user_id` bigint(20) NOT NULL,
41 `session_id` bigint(20) NOT NULL,
42 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
43 `message` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL
44) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
45
46-- --------------------------------------------------------
47
48--
49-- Table structure for table `command_logs`
50--
51
52CREATE TABLE `command_logs` (
53 `id` bigint(20) NOT NULL,
54 `user_id` bigint(20) NOT NULL,
55 `session_id` bigint(20) NOT NULL,
56 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
57 `command` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
58 `args` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL
59) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
60
61
62--
63-- Table structure for table `ip_lookup`
64--
65
66CREATE TABLE `ip_lookup` (
67 `id` bigint(20) NOT NULL,
68 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
69 `ip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
70 `asn` int(11) NOT NULL,
71 `isp` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
72 `city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
73 `country` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
74 `region` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
75 `timezone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
76) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
77
78-- --------------------------------------------------------
79
80--
81-- Table structure for table `session_auth`
82--
83
84CREATE TABLE `session_auth` (
85 `id` bigint(20) NOT NULL,
86 `issued` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
87 `expires` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
88 `user_id` bigint(20) NOT NULL,
89 `token` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL
90) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
91
92-- --------------------------------------------------------
93
94--
95-- Table structure for table `session_logs`
96--
97
98CREATE TABLE `session_logs` (
99 `id` bigint(20) NOT NULL,
100 `start` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
101 `finish` timestamp NULL DEFAULT NULL,
102 `user_id` bigint(20) NOT NULL,
103 `ip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
104 `isp` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
105 `asn` int(11) NOT NULL,
106 `country` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
107 `region` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
108 `city` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
109 `response` smallint(6) NOT NULL,
110 `duration` bigint(20) NOT NULL DEFAULT '0'
111) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
112
113
114-- --------------------------------------------------------
115
116--
117-- Table structure for table `session_token`
118--
119
120CREATE TABLE `session_token` (
121 `id` bigint(20) NOT NULL,
122 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
123 `expires` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
124 `token` varchar(64) NOT NULL
125) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
126
127-- --------------------------------------------------------
128
129--
130-- Table structure for table `users`
131--
132
133CREATE TABLE `users` (
134 `id` bigint(20) NOT NULL,
135 `name` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
136 `permission` mediumint(9) NOT NULL DEFAULT '0',
137 `usergroup` tinyint(4) NOT NULL DEFAULT '0',
138 `lastseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
139 `country` varchar(55) COLLATE utf8mb4_unicode_ci NOT NULL,
140 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
141 `ip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
142 `passhash` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
143 `playtime` bigint(20) NOT NULL DEFAULT '0',
144 `isp` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
145 `isp_asn` int(11) NOT NULL,
146 `region` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
147 `city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
148) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
149
150
151--
152-- Indexes for table `asn_blacklist`
153--
154ALTER TABLE `asn_blacklist`
155 ADD PRIMARY KEY (`asn`),
156 ADD UNIQUE KEY `asn_UNIQUE` (`asn`);
157
158--
159-- Indexes for table `chat_logs`
160--
161ALTER TABLE `chat_logs`
162 ADD PRIMARY KEY (`id`),
163 ADD UNIQUE KEY `id_UNIQUE` (`id`),
164 ADD KEY `fk_chat_logs_user_id_idx` (`user_id`),
165 ADD KEY `fk_chat_session_id_idx` (`session_id`);
166
167--
168-- Indexes for table `command_logs`
169--
170ALTER TABLE `command_logs`
171 ADD PRIMARY KEY (`id`),
172 ADD UNIQUE KEY `id_UNIQUE` (`id`),
173 ADD KEY `fk_comlogs_user_id_idx` (`user_id`),
174 ADD KEY `fk_command_session_id_idx` (`session_id`);
175
176--
177-- Indexes for table `ip_lookup`
178--
179ALTER TABLE `ip_lookup`
180 ADD PRIMARY KEY (`id`),
181 ADD UNIQUE KEY `id_UNIQUE` (`id`),
182 ADD UNIQUE KEY `address_UNIQUE` (`ip`);
183
184--
185-- Indexes for table `session_auth`
186--
187ALTER TABLE `session_auth`
188 ADD PRIMARY KEY (`id`),
189 ADD UNIQUE KEY `id_UNIQUE` (`id`);
190
191--
192-- Indexes for table `session_logs`
193--
194ALTER TABLE `session_logs`
195 ADD PRIMARY KEY (`id`),
196 ADD UNIQUE KEY `id_UNIQUE` (`id`),
197 ADD KEY `fk_session_logs_user_id_idx` (`user_id`),
198 ADD KEY `fk_session_logs_ip_id_idx` (`asn`);
199
200--
201-- Indexes for table `session_token`
202--
203ALTER TABLE `session_token`
204 ADD PRIMARY KEY (`id`),
205 ADD UNIQUE KEY `id_UNIQUE` (`id`);
206
207--
208-- Indexes for table `users`
209--
210ALTER TABLE `users`
211 ADD PRIMARY KEY (`id`),
212 ADD UNIQUE KEY `id_UNIQUE` (`id`),
213 ADD UNIQUE KEY `name_UNIQUE` (`name`);
214
215--
216-- AUTO_INCREMENT for dumped tables
217--
218
219--
220-- AUTO_INCREMENT for table `chat_logs`
221--
222ALTER TABLE `chat_logs`
223 MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
224
225--
226-- AUTO_INCREMENT for table `command_logs`
227--
228ALTER TABLE `command_logs`
229 MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
230
231--
232-- AUTO_INCREMENT for table `ip_lookup`
233--
234ALTER TABLE `ip_lookup`
235 MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
236
237--
238-- AUTO_INCREMENT for table `session_auth`
239--
240ALTER TABLE `session_auth`
241 MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
242
243--
244-- AUTO_INCREMENT for table `session_logs`
245--
246ALTER TABLE `session_logs`
247 MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;
248
249--
250-- AUTO_INCREMENT for table `session_token`
251--
252ALTER TABLE `session_token`
253 MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
254
255--
256-- AUTO_INCREMENT for table `users`
257--
258ALTER TABLE `users`
259 MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;
260
261--
262-- Constraints for dumped tables
263--
264
265--
266-- Constraints for table `chat_logs`
267--
268ALTER TABLE `chat_logs`
269 ADD CONSTRAINT `fk_chat_session_id` FOREIGN KEY (`session_id`) REFERENCES `session_logs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
270 ADD CONSTRAINT `fk_chat_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
271
272--
273-- Constraints for table `command_logs`
274--
275ALTER TABLE `command_logs`
276 ADD CONSTRAINT `fk_command_session_id` FOREIGN KEY (`session_id`) REFERENCES `session_logs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
277 ADD CONSTRAINT `fk_command_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
278
279--
280-- Constraints for table `session_logs`
281--
282ALTER TABLE `session_logs`
283 ADD CONSTRAINT `fk_session_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
284--
285-- Database: `emulous_store`
286--
287CREATE DATABASE IF NOT EXISTS `emulous_store` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
288USE `emulous_store`;
289
290-- --------------------------------------------------------
291
292--
293-- Table structure for table `categories`
294--
295
296CREATE TABLE `categories` (
297 `cid` int(11) NOT NULL,
298 `cat_title` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
299 `zindex` int(11) NOT NULL DEFAULT '0'
300) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
301
302-- --------------------------------------------------------
303
304--
305-- Table structure for table `payments`
306--
307
308CREATE TABLE `payments` (
309 `id` int(11) NOT NULL,
310 `item_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
311 `item_number` int(11) NOT NULL,
312 `status` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
313 `amount` double NOT NULL,
314 `quantity` int(11) NOT NULL DEFAULT '0',
315 `currency` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
316 `buyer` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
317 `receiver` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
318 `dateline` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
319 `player_name` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
320 `claimed` tinyint(4) NOT NULL DEFAULT '0'
321) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
322
323-- --------------------------------------------------------
324
325--
326-- Table structure for table `products`
327--
328
329CREATE TABLE `products` (
330 `item_id` int(11) NOT NULL,
331 `item_name` varchar(255) CHARACTER SET utf8 NOT NULL,
332 `category` int(11) NOT NULL,
333 `item_price` double NOT NULL,
334 `item_discount` double NOT NULL,
335 `image_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
336 `description` varchar(255) CHARACTER SET utf8 DEFAULT NULL
337) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
338
339-- --------------------------------------------------------
340
341--
342-- Table structure for table `used_hashes`
343--
344
345CREATE TABLE `used_hashes` (
346 `id` int(11) NOT NULL,
347 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
348 `hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL
349) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
350
351--
352-- Indexes for dumped tables
353--
354
355--
356-- Indexes for table `categories`
357--
358ALTER TABLE `categories`
359 ADD PRIMARY KEY (`cid`),
360 ADD UNIQUE KEY `cid_UNIQUE` (`cid`);
361
362--
363-- Indexes for table `payments`
364--
365ALTER TABLE `payments`
366 ADD PRIMARY KEY (`id`),
367 ADD UNIQUE KEY `id_UNIQUE` (`id`),
368 ADD KEY `fkey_product_id_idx` (`item_number`);
369
370--
371-- Indexes for table `products`
372--
373ALTER TABLE `products`
374 ADD PRIMARY KEY (`item_id`),
375 ADD UNIQUE KEY `item_id_UNIQUE` (`item_id`),
376 ADD KEY `fkey_category_id_idx` (`category`);
377
378--
379-- Indexes for table `used_hashes`
380--
381ALTER TABLE `used_hashes`
382 ADD PRIMARY KEY (`id`),
383 ADD UNIQUE KEY `id_UNIQUE` (`id`);
384
385--
386-- AUTO_INCREMENT for dumped tables
387--
388
389--
390-- AUTO_INCREMENT for table `categories`
391--
392ALTER TABLE `categories`
393 MODIFY `cid` int(11) NOT NULL AUTO_INCREMENT;
394
395--
396-- AUTO_INCREMENT for table `payments`
397--
398ALTER TABLE `payments`
399 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
400
401--
402-- AUTO_INCREMENT for table `products`
403--
404ALTER TABLE `products`
405 MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT;
406
407--
408-- AUTO_INCREMENT for table `used_hashes`
409--
410ALTER TABLE `used_hashes`
411 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
412
413--
414-- Constraints for dumped tables
415--
416
417--
418-- Constraints for table `payments`
419--
420ALTER TABLE `payments`
421 ADD CONSTRAINT `fkey_product_id` FOREIGN KEY (`item_number`) REFERENCES `products` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE;
422
423--
424-- Constraints for table `products`
425--
426ALTER TABLE `products`
427 ADD CONSTRAINT `fkey_category_id` FOREIGN KEY (`category`) REFERENCES `categories` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE;
428--
429-- Database: `emulous_vote`
430--
431CREATE DATABASE IF NOT EXISTS `emulous_vote` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
432USE `emulous_vote`;
433
434-- --------------------------------------------------------
435
436--
437-- Table structure for table `fx_sites`
438--
439
440CREATE TABLE `fx_sites` (
441 `id` int(11) NOT NULL,
442 `title` varchar(255) NOT NULL,
443 `url` varchar(255) NOT NULL,
444 `site_id` varchar(255) NOT NULL,
445 `active` tinyint(4) NOT NULL DEFAULT '1'
446) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
447
448-- --------------------------------------------------------
449
450--
451-- Table structure for table `fx_votes`
452--
453
454CREATE TABLE `fx_votes` (
455 `id` int(11) NOT NULL,
456 `username` varchar(12) NOT NULL,
457 `callback_date` timestamp NULL DEFAULT NULL,
458 `started` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
459 `ip_address` varchar(45) NOT NULL,
460 `site_id` int(11) NOT NULL,
461 `uid` varchar(255) NOT NULL,
462 `claimed` tinyint(4) NOT NULL DEFAULT '0'
463) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
464
465--
466-- Indexes for dumped tables
467--
468
469--
470-- Indexes for table `fx_sites`
471--
472ALTER TABLE `fx_sites`
473 ADD PRIMARY KEY (`id`),
474 ADD UNIQUE KEY `id_UNIQUE` (`id`);
475
476--
477-- Indexes for table `fx_votes`
478--
479ALTER TABLE `fx_votes`
480 ADD PRIMARY KEY (`id`),
481 ADD UNIQUE KEY `id_UNIQUE` (`id`),
482 ADD KEY `fkey_site_id_idx` (`site_id`);
483
484--
485-- AUTO_INCREMENT for dumped tables
486--
487
488--
489-- AUTO_INCREMENT for table `fx_sites`
490--
491ALTER TABLE `fx_sites`
492 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
493
494--
495-- AUTO_INCREMENT for table `fx_votes`
496--
497ALTER TABLE `fx_votes`
498 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
499
500--
501-- Constraints for dumped tables
502--
503
504--
505-- Constraints for table `fx_votes`
506--
507ALTER TABLE `fx_votes`
508 ADD CONSTRAINT `fkey_site_id` FOREIGN KEY (`site_id`) REFERENCES `fx_sites` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
509COMMIT;
510
511/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
512/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
513/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;