· 9 years ago · Jan 07, 2017, 07:50 PM
1-- MySQL Script generated by MySQL Workbench
2-- 12/15/16 22:24:19
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema casegod
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema casegod
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `casegod` DEFAULT CHARACTER SET latin1 ;
18USE `casegod` ;
19
20-- -----------------------------------------------------
21-- Table `casegod`.`bots`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `casegod`.`bots` (
24 `id` BIGINT UNSIGNED NOT NULL,
25 `account_name` VARCHAR(32) CHARACTER SET 'latin1' NOT NULL,
26 `password` VARCHAR(32) CHARACTER SET 'latin1' NOT NULL,
27 `shared_secret` VARCHAR(40) CHARACTER SET 'latin1' NOT NULL,
28 `identity_secret` VARCHAR(40) CHARACTER SET 'latin1' NOT NULL,
29 `flags` TINYINT UNSIGNED NOT NULL DEFAULT 0,
30 PRIMARY KEY (`id`),
31 UNIQUE INDEX (`account_name` ASC));
32
33
34-- -----------------------------------------------------
35-- Table `casegod`.`users`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `casegod`.`users` (
38 `id` BIGINT UNSIGNED NOT NULL,
39 `username` VARCHAR(32) COLLATE 'utf8mb4_unicode_ci' NOT NULL,
40 `access` TINYINT UNSIGNED NOT NULL DEFAULT 1,
41 `flags` TINYINT UNSIGNED NOT NULL DEFAULT 0,
42 `coins` INT UNSIGNED NOT NULL DEFAULT 0,
43 `trade_token` CHAR(8) CHARACTER SET 'latin1' NULL DEFAULT NULL,
44 `trade_url` VARCHAR(256) CHARACTER SET 'latin1' NULL DEFAULT NULL,
45 `avatar_hash` VARCHAR(40) CHARACTER SET 'latin1' NOT NULL,
46 `remember_token` VARCHAR(100) CHARACTER SET 'latin1' NULL,
47 `ref` VARCHAR(32) CHARACTER SET 'latin1' NULL,
48 `ref_rank` VARCHAR(16) CHARACTER SET 'latin1' DEFAULT 'bronze',
49 `withdraw_profit` INT UNSIGNED NOT NULL DEFAULT 0,
50 `total_profit` INT UNSIGNED NOT NULL DEFAULT 0,
51 `create_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
52 `last_activity` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
53 PRIMARY KEY (`id`));
54
55CREATE TABLE IF NOT EXISTS `casegod`.`affiliates` (
56 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
57 `user_id` BIGINT UNSIGNED NOT NULL,
58 `referral_id` BIGINT UNSIGNED NOT NULL,
59 PRIMARY KEY(id)
60);
61
62CREATE TABLE IF NOT EXISTS `casegod`.`affiliate_profit` (
63 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
64 `user_id` BIGINT UNSIGNED NOT NULL,
65 `referral_id` BIGINT UNSIGNED NOT NULL,
66 `profit` INT UNSIGNED NOT NULL,
67 PRIMARY KEY(`id`)
68);
69
70CREATE TABLE IF NOT EXISTS `casegod`.`g2a_transactions`(
71 id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
72 user_id BIGINT UNSIGNED NOT NULL,
73 status VARCHAR(12) NOT NULL,
74 transaction_id VARCHAR(64) NULL,
75 amount INT(10) NOT NULL,
76 order_hash VARCHAR(32) NOT NULL,
77 PRIMARY KEY(id)
78);
79
80-- -----------------------------------------------------
81-- Table `casegod`.`items`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `casegod`.`items` (
84 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
85 `market_hash_name` VARCHAR(64) COLLATE 'utf8mb4_unicode_ci' NOT NULL,
86 `price` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
87 `autoupdate` TINYINT NOT NULL DEFAULT FALSE,
88 `deposit_multiplier` INT UNSIGNED NOT NULL DEFAULT 100,
89 `sell_multiplier` INT UNSIGNED NOT NULL DEFAULT 100,
90 `add_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
91 PRIMARY KEY (`id`),
92 UNIQUE INDEX (`market_hash_name` ASC));
93
94
95-- -----------------------------------------------------
96-- Table `casegod`.`offers`
97-- -----------------------------------------------------
98CREATE TABLE IF NOT EXISTS `casegod`.`offers` (
99 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
100 `user` BIGINT UNSIGNED NOT NULL,
101 `type` INT UNSIGNED NOT NULL DEFAULT 0,
102 `value` INT UNSIGNED NOT NULL DEFAULT 0,
103 `state` TINYINT UNSIGNED NOT NULL DEFAULT 0,
104 `create_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
105 PRIMARY KEY (`id`),
106 INDEX `fk_offers_users1_idx` (`user` ASC),
107 CONSTRAINT `fk_offers_users1`
108 FOREIGN KEY (`user`)
109 REFERENCES `casegod`.`users` (`id`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION);
112
113
114-- -----------------------------------------------------
115-- Table `casegod`.`offers_items`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `casegod`.`offers_items` (
118 `offer` INT UNSIGNED NOT NULL,
119 `item` INT UNSIGNED NOT NULL,
120 PRIMARY KEY (`offer`, `item`),
121 INDEX `fk_offers_items_items_idx` (`item` ASC),
122 INDEX `fk_offers_items_offers_idx` (`offer` ASC),
123 CONSTRAINT `fk_offers_items_offers`
124 FOREIGN KEY (`offer`)
125 REFERENCES `casegod`.`offers` (`id`)
126 ON DELETE NO ACTION
127 ON UPDATE NO ACTION,
128 CONSTRAINT `fk_offers_items_items`
129 FOREIGN KEY (`item`)
130 REFERENCES `casegod`.`items` (`id`)
131 ON DELETE NO ACTION
132 ON UPDATE NO ACTION);
133
134
135-- -----------------------------------------------------
136-- Table `casegod`.`deposit`
137-- -----------------------------------------------------
138CREATE TABLE IF NOT EXISTS `casegod`.`deposit` (
139 `id` BIGINT UNSIGNED NOT NULL,
140 `item` INT UNSIGNED NOT NULL,
141 `bot` BIGINT UNSIGNED NOT NULL,
142 `depositor` BIGINT UNSIGNED NULL,
143 `classid` INT UNSIGNED NULL,
144 `in_trade` BOOLEAN DEFAULT 0,
145 `deposit_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
146 PRIMARY KEY (`id`, `item`, `bot`),
147 INDEX `fk_deposit_items_idx` (`item` ASC),
148 INDEX `fk_deposit_bots_idx` (`bot` ASC),
149 INDEX `fk_deposit_users_idx` (`depositor` ASC),
150 CONSTRAINT `fk_deposit_items`
151 FOREIGN KEY (`item`)
152 REFERENCES `casegod`.`items` (`id`)
153 ON DELETE NO ACTION
154 ON UPDATE NO ACTION,
155 CONSTRAINT `fk_deposit_bots`
156 FOREIGN KEY (`bot`)
157 REFERENCES `casegod`.`bots` (`id`)
158 ON DELETE NO ACTION
159 ON UPDATE NO ACTION,
160 CONSTRAINT `fk_deposit_users`
161 FOREIGN KEY (`depositor`)
162 REFERENCES `casegod`.`users` (`id`)
163 ON DELETE NO ACTION
164 ON UPDATE NO ACTION)
165ENGINE = InnoDB;
166
167
168-- -----------------------------------------------------
169-- Table `casegod`.`containers`
170-- -----------------------------------------------------
171CREATE TABLE IF NOT EXISTS `casegod`.`containers` (
172 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
173 `name` VARCHAR(45) NULL,
174 `price` INT UNSIGNED NULL,
175 `color` VARCHAR(45) NULL,
176 `image` VARCHAR(127) NULL,
177 PRIMARY KEY (`id`),
178 UNIQUE INDEX `name_UNIQUE` (`name` ASC))
179ENGINE = InnoDB;
180
181
182-- -----------------------------------------------------
183-- Table `casegod`.`containers_items`
184-- -----------------------------------------------------
185CREATE TABLE IF NOT EXISTS `casegod`.`containers_items` (
186 `id` INT UNSIGNED NOT NULL,
187 `item` INT UNSIGNED NOT NULL,
188 PRIMARY KEY (`id`, `item`),
189 INDEX `fk_containers_items_items1_idx` (`item` ASC),
190 CONSTRAINT `fk_containers_items_items1`
191 FOREIGN KEY (`item`)
192 REFERENCES `casegod`.`items` (`id`)
193 ON DELETE NO ACTION
194 ON UPDATE NO ACTION)
195ENGINE = InnoDB;
196
197
198-- -----------------------------------------------------
199-- Table `casegod`.`containers_content`
200-- -----------------------------------------------------
201CREATE TABLE IF NOT EXISTS `casegod`.`containers_content` (
202 `container_item` INT UNSIGNED NOT NULL,
203 `container` INT UNSIGNED NOT NULL,
204 `name` VARCHAR(64) NULL,
205 `chance` INT NOT NULL,
206 `image` VARCHAR(127) NULL,
207 `classid` INT UNSIGNED NULL,
208 PRIMARY KEY (`container_item`, `container`),
209 CONSTRAINT `fk_containers_items_containers1`
210 FOREIGN KEY (`container`)
211 REFERENCES `casegod`.`containers` (`id`)
212 ON DELETE NO ACTION
213 ON UPDATE NO ACTION,
214 CONSTRAINT `fk_containers_items_containers_items_qualities1`
215 FOREIGN KEY (`container_item`)
216 REFERENCES `casegod`.`containers_items` (`id`)
217 ON DELETE NO ACTION
218 ON UPDATE NO ACTION)
219ENGINE = InnoDB;
220
221
222-- -----------------------------------------------------
223-- Table `casegod`.`users_containers_statuses`
224-- -----------------------------------------------------
225CREATE TABLE IF NOT EXISTS `casegod`.`users_containers_statuses` (
226 `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
227 `name` VARCHAR(45) NULL,
228 PRIMARY KEY (`id`))
229ENGINE = InnoDB;
230
231
232-- -----------------------------------------------------
233-- Table `casegod`.`users_containers`
234-- -----------------------------------------------------
235CREATE TABLE IF NOT EXISTS `casegod`.`users_containers` (
236 `user` BIGINT UNSIGNED NOT NULL,
237 `container` INT UNSIGNED NOT NULL,
238 `item` INT UNSIGNED NOT NULL,
239 `deposit_item` BIGINT UNSIGNED NULL,
240 `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
241 `open_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
242 PRIMARY KEY (`user`, `container`, `item`, `open_date`),
243 INDEX `fk_users_containers_containers_idx` (`container` ASC),
244 INDEX `fk_users_containers_users_idx` (`user` ASC),
245 INDEX `fk_users_containers_deposit1_idx` (`deposit_item` ASC),
246 INDEX `fk_users_containers_items1_idx` (`item` ASC),
247 INDEX `fk_users_containers_users_containers_statuses1_idx` (`status` ASC),
248 UNIQUE INDEX `deposit_item_UNIQUE` (`deposit_item` ASC),
249 CONSTRAINT `fk_users_containers_users1`
250 FOREIGN KEY (`user`)
251 REFERENCES `casegod`.`users` (`id`)
252 ON DELETE NO ACTION
253 ON UPDATE NO ACTION,
254 CONSTRAINT `fk_users_containers_containers1`
255 FOREIGN KEY (`container`)
256 REFERENCES `casegod`.`containers` (`id`)
257 ON DELETE NO ACTION
258 ON UPDATE NO ACTION,
259 CONSTRAINT `fk_users_containers_deposit1`
260 FOREIGN KEY (`deposit_item`)
261 REFERENCES `casegod`.`deposit` (`id`)
262 ON DELETE NO ACTION
263 ON UPDATE NO ACTION,
264 CONSTRAINT `fk_users_containers_items1`
265 FOREIGN KEY (`item`)
266 REFERENCES `casegod`.`items` (`id`)
267 ON DELETE NO ACTION
268 ON UPDATE NO ACTION,
269 CONSTRAINT `fk_users_containers_users_containers_statuses1`
270 FOREIGN KEY (`status`)
271 REFERENCES `casegod`.`users_containers_statuses` (`id`)
272 ON DELETE NO ACTION
273 ON UPDATE NO ACTION);
274
275
276-- -----------------------------------------------------
277-- Table `casegod`.`transactions_types`
278-- -----------------------------------------------------
279CREATE TABLE IF NOT EXISTS `casegod`.`transactions_types` (
280 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
281 `name` VARCHAR(45) NOT NULL,
282 PRIMARY KEY (`id`))
283ENGINE = InnoDB;
284
285
286-- -----------------------------------------------------
287-- Table `casegod`.`transactions`
288-- -----------------------------------------------------
289CREATE TABLE IF NOT EXISTS `casegod`.`transactions` (
290 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
291 `user` BIGINT UNSIGNED NOT NULL,
292 `type` INT UNSIGNED NOT NULL DEFAULT 1,
293 `change` INT NOT NULL DEFAULT 0,
294 `transaction_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
295 PRIMARY KEY (`id`, `user`, `type`),
296 INDEX `fk_transactions_transaction_types1_idx` (`type` ASC),
297 CONSTRAINT `fk_transactions_users1`
298 FOREIGN KEY (`user`)
299 REFERENCES `casegod`.`users` (`id`)
300 ON DELETE NO ACTION
301 ON UPDATE NO ACTION,
302 CONSTRAINT `fk_transactions_transaction_types1`
303 FOREIGN KEY (`type`)
304 REFERENCES `casegod`.`transactions_types` (`id`)
305 ON DELETE NO ACTION
306 ON UPDATE NO ACTION)
307ENGINE = InnoDB;
308
309
310-- -----------------------------------------------------
311-- Table `casegod`.`steam_offers`
312-- -----------------------------------------------------
313CREATE TABLE IF NOT EXISTS `casegod`.`steam_offers` (
314 `id` INT UNSIGNED NOT NULL,
315 `offer` INT UNSIGNED NOT NULL,
316 `state` TINYINT UNSIGNED NULL,
317 PRIMARY KEY (`id`),
318 INDEX `fk_steam_offers_offers1_idx` (`offer` ASC),
319 CONSTRAINT `fk_steam_offers_offers1`
320 FOREIGN KEY (`offer`)
321 REFERENCES `casegod`.`offers` (`id`)
322 ON DELETE NO ACTION
323 ON UPDATE NO ACTION)
324ENGINE = InnoDB;
325
326
327-- -----------------------------------------------------
328-- Table `casegod`.`transactions_comments`
329-- -----------------------------------------------------
330CREATE TABLE IF NOT EXISTS `casegod`.`transactions_comments` (
331 `transaction` INT UNSIGNED NOT NULL,
332 `comment` VARCHAR(255) NULL,
333 PRIMARY KEY (`transaction`),
334 CONSTRAINT `fk_transactions_comments_transactions1`
335 FOREIGN KEY (`transaction`)
336 REFERENCES `casegod`.`transactions` (`id`)
337 ON DELETE NO ACTION
338 ON UPDATE NO ACTION)
339ENGINE = InnoDB;
340
341
342SET SQL_MODE=@OLD_SQL_MODE;
343SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
344SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;