· 8 years ago · Apr 12, 2018, 01:28 PM
1-- phpMyAdmin SQL Dump
2-- version 4.7.7
3-- https://www.phpmyadmin.net/
4--
5-- Host: localhost
6-- Erstellungszeit: 12. Apr 2018 um 15:26
7-- Server-Version: 10.0.34-MariaDB-0ubuntu0.16.04.1
8-- PHP-Version: 7.0.28-0ubuntu0.16.04.1
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET AUTOCOMMIT = 0;
12START TRANSACTION;
13SET time_zone = "+00:00";
14
15--
16-- Datenbank: `gateway`
17--
18
19-- --------------------------------------------------------
20
21--
22-- Tabellenstruktur für Tabelle `accounts`
23--
24
25DROP TABLE IF EXISTS `accounts`;
26CREATE TABLE `accounts` (
27 `id` int(11) NOT NULL,
28 `name` varchar(128) NOT NULL,
29 `ip` varchar(16) NOT NULL,
30 `created` datetime NOT NULL,
31 `referrer` varchar(128) DEFAULT NULL
32) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
33
34-- --------------------------------------------------------
35
36--
37-- Tabellenstruktur für Tabelle `accounts_location`
38--
39
40DROP TABLE IF EXISTS `accounts_location`;
41CREATE TABLE `accounts_location` (
42 `ip` varchar(15) NOT NULL,
43 `country_code` char(2) NOT NULL,
44 `country` varchar(64) NOT NULL,
45 `city` varchar(64) CHARACTER SET utf8 NOT NULL
46) ENGINE=InnoDB DEFAULT CHARSET=latin1;
47
48-- --------------------------------------------------------
49
50--
51-- Tabellenstruktur für Tabelle `coins`
52--
53
54DROP TABLE IF EXISTS `coins`;
55CREATE TABLE `coins` (
56 `id` int(11) NOT NULL,
57 `coin` varchar(64) NOT NULL,
58 `connections` int(11) DEFAULT NULL,
59 `block` int(11) NOT NULL,
60 `last_block_update` datetime NOT NULL,
61 `blocktime` int(11) NOT NULL DEFAULT '0',
62 `status` int(11) NOT NULL DEFAULT '0',
63 `last_updated` datetime NOT NULL
64) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
65
66-- --------------------------------------------------------
67
68--
69-- Tabellenstruktur für Tabelle `coins_transactions`
70--
71
72DROP TABLE IF EXISTS `coins_transactions`;
73CREATE TABLE `coins_transactions` (
74 `id` int(11) NOT NULL,
75 `coin` varchar(12) NOT NULL,
76 `type` varchar(12) NOT NULL,
77 `amount` decimal(16,8) NOT NULL,
78 `address` varchar(64) NOT NULL,
79 `txid` varchar(256) NOT NULL,
80 `fee` decimal(16,8) NOT NULL,
81 `confirmations` int(11) DEFAULT NULL,
82 `timestamp` datetime NOT NULL,
83 `created` datetime NOT NULL
84) ENGINE=InnoDB DEFAULT CHARSET=latin1;
85
86-- --------------------------------------------------------
87
88--
89-- Tabellenstruktur für Tabelle `deposits`
90--
91
92DROP TABLE IF EXISTS `deposits`;
93CREATE TABLE `deposits` (
94 `id` int(11) NOT NULL,
95 `coin` varchar(6) NOT NULL,
96 `address` varchar(128) NOT NULL,
97 `username` varchar(256) NOT NULL,
98 `asset` varchar(128) NOT NULL,
99 `done` int(11) NOT NULL,
100 `created` datetime NOT NULL
101) ENGINE=InnoDB DEFAULT CHARSET=utf8;
102
103-- --------------------------------------------------------
104
105--
106-- Stellvertreter-Struktur des Views `deposits_failed`
107-- (Siehe unten für die tatsächliche Ansicht)
108--
109DROP VIEW IF EXISTS `deposits_failed`;
110CREATE TABLE `deposits_failed` (
111`id` int(11)
112,`coin` varchar(6)
113,`amount` decimal(32,16)
114,`username` varchar(256)
115,`txid` varchar(128)
116,`asset_issued` int(11)
117,`created` datetime
118);
119
120-- --------------------------------------------------------
121
122--
123-- Tabellenstruktur für Tabelle `deposits_transactions`
124--
125
126DROP TABLE IF EXISTS `deposits_transactions`;
127CREATE TABLE `deposits_transactions` (
128 `id` int(11) NOT NULL,
129 `coin` varchar(6) NOT NULL,
130 `deposit_id` int(11) NOT NULL,
131 `amount` decimal(32,16) NOT NULL,
132 `tx` varchar(256) NOT NULL,
133 `asset_issued` int(11) DEFAULT NULL,
134 `re_issue` int(11) NOT NULL DEFAULT '0',
135 `memo` varchar(256) DEFAULT NULL,
136 `fee` decimal(16,8) NOT NULL,
137 `created` datetime NOT NULL
138) ENGINE=InnoDB DEFAULT CHARSET=utf8;
139
140-- --------------------------------------------------------
141
142--
143-- Stellvertreter-Struktur des Views `deposits_transactions_full`
144-- (Siehe unten für die tatsächliche Ansicht)
145--
146DROP VIEW IF EXISTS `deposits_transactions_full`;
147CREATE TABLE `deposits_transactions_full` (
148`id` int(11)
149,`coin` varchar(6)
150,`deposit_id` int(11)
151,`amount` decimal(32,16)
152,`tx` varchar(256)
153,`asset_issued` int(11)
154,`re_issue` int(11)
155,`memo` varchar(256)
156,`created` datetime
157,`address` varchar(128)
158,`username` varchar(256)
159);
160
161-- --------------------------------------------------------
162
163--
164-- Tabellenstruktur für Tabelle `filled_orders`
165--
166
167DROP TABLE IF EXISTS `filled_orders`;
168CREATE TABLE `filled_orders` (
169 `id` int(11) NOT NULL,
170 `base` varchar(128) NOT NULL,
171 `quote` varchar(128) NOT NULL,
172 `fill_id` varchar(16) NOT NULL,
173 `order_id` varchar(16) NOT NULL,
174 `account_id` varchar(16) NOT NULL,
175 `account_name` varchar(128) NOT NULL,
176 `pays` decimal(16,8) NOT NULL,
177 `payAsset` varchar(128) NOT NULL,
178 `receives` decimal(16,8) NOT NULL,
179 `receiveAsset` varchar(128) NOT NULL,
180 `fee` decimal(16,8) NOT NULL,
181 `feeAsset` varchar(128) NOT NULL,
182 `time` datetime NOT NULL
183) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
184
185-- --------------------------------------------------------
186
187--
188-- Tabellenstruktur für Tabelle `maker_daily_stats`
189--
190
191DROP TABLE IF EXISTS `maker_daily_stats`;
192CREATE TABLE `maker_daily_stats` (
193 `id` int(11) NOT NULL,
194 `maker` varchar(64) NOT NULL,
195 `maker_fee` decimal(16,8) NOT NULL,
196 `maker_fee_asset` varchar(64) NOT NULL,
197 `taker_fee` decimal(16,8) NOT NULL,
198 `taker_fee_asset` varchar(64) NOT NULL,
199 `paid` int(11) DEFAULT NULL,
200 `paid_date` datetime DEFAULT NULL,
201 `date` date DEFAULT NULL,
202 `created` datetime NOT NULL
203) ENGINE=InnoDB DEFAULT CHARSET=latin1;
204
205-- --------------------------------------------------------
206
207--
208-- Stellvertreter-Struktur des Views `maker_taker_orders`
209-- (Siehe unten für die tatsächliche Ansicht)
210--
211DROP VIEW IF EXISTS `maker_taker_orders`;
212CREATE TABLE `maker_taker_orders` (
213`buyer` varchar(128)
214,`seller` varchar(128)
215,`receives` decimal(16,8)
216,`pays` decimal(16,8)
217,`price` decimal(25,8)
218,`base` varchar(128)
219,`quote` varchar(128)
220,`time` datetime
221,`maker` varchar(128)
222,`maker_fee` decimal(16,8)
223,`maker_fee_asset` varchar(128)
224,`taker_fee` decimal(16,8)
225,`taker_fee_asset` varchar(128)
226);
227
228-- --------------------------------------------------------
229
230--
231-- Tabellenstruktur für Tabelle `orders`
232--
233
234DROP TABLE IF EXISTS `orders`;
235CREATE TABLE `orders` (
236 `id` int(11) NOT NULL,
237 `base` varchar(64) NOT NULL,
238 `quote` varchar(64) NOT NULL,
239 `order_id` varchar(64) NOT NULL,
240 `account_id` varchar(128) NOT NULL,
241 `account_name` varchar(128) NOT NULL,
242 `expiration` datetime NOT NULL,
243 `created` datetime NOT NULL
244) ENGINE=InnoDB DEFAULT CHARSET=latin1;
245
246-- --------------------------------------------------------
247
248--
249-- Stellvertreter-Struktur des Views `orders_bco_btc`
250-- (Siehe unten für die tatsächliche Ansicht)
251--
252DROP VIEW IF EXISTS `orders_bco_btc`;
253CREATE TABLE `orders_bco_btc` (
254`buyer` varchar(128)
255,`seller` varchar(128)
256,`receives` decimal(16,8)
257,`pays` decimal(16,8)
258,`price` decimal(25,8)
259,`base` varchar(128)
260,`quote` varchar(128)
261,`time` datetime
262);
263
264-- --------------------------------------------------------
265
266--
267-- Stellvertreter-Struktur des Views `orders_volume_raw`
268-- (Siehe unten für die tatsächliche Ansicht)
269--
270DROP VIEW IF EXISTS `orders_volume_raw`;
271CREATE TABLE `orders_volume_raw` (
272`count` bigint(21)
273,`account_name` varchar(128)
274,`receives` decimal(38,8)
275,`pays` decimal(38,8)
276,`fee` decimal(38,8)
277,`asset` varchar(128)
278);
279
280-- --------------------------------------------------------
281
282--
283-- Stellvertreter-Struktur des Views `profits`
284-- (Siehe unten für die tatsächliche Ansicht)
285--
286DROP VIEW IF EXISTS `profits`;
287CREATE TABLE `profits` (
288`date` date
289,`asset` varchar(128)
290,`profit` decimal(38,8)
291);
292
293-- --------------------------------------------------------
294
295--
296-- Tabellenstruktur für Tabelle `queue_deposit`
297--
298
299DROP TABLE IF EXISTS `queue_deposit`;
300CREATE TABLE `queue_deposit` (
301 `id` int(11) NOT NULL,
302 `coin` varchar(32) NOT NULL,
303 `deposit_transaction_id` int(11) NOT NULL,
304 `username` varchar(256) NOT NULL,
305 `address` varchar(128) NOT NULL,
306 `txid` varchar(256) NOT NULL,
307 `amount` decimal(32,8) NOT NULL,
308 `asset` varchar(128) NOT NULL,
309 `status` int(11) NOT NULL,
310 `note` varchar(32) DEFAULT NULL,
311 `created` datetime NOT NULL
312) ENGINE=InnoDB DEFAULT CHARSET=utf8;
313
314-- --------------------------------------------------------
315
316--
317-- Tabellenstruktur für Tabelle `staking_daily_stats`
318--
319
320DROP TABLE IF EXISTS `staking_daily_stats`;
321CREATE TABLE `staking_daily_stats` (
322 `id` int(11) NOT NULL,
323 `staker` varchar(128) NOT NULL,
324 `ratio` decimal(16,8) NOT NULL,
325 `amount` decimal(32,16) NOT NULL,
326 `asset` varchar(16) NOT NULL,
327 `total` decimal(16,8) NOT NULL,
328 `payout` decimal(16,12) NOT NULL,
329 `date` date NOT NULL,
330 `paid` int(11) DEFAULT '0',
331 `real_amount` decimal(32,16) NOT NULL,
332 `real_asset` varchar(16) NOT NULL,
333 `created` datetime NOT NULL
334) ENGINE=InnoDB DEFAULT CHARSET=utf8;
335
336-- --------------------------------------------------------
337
338--
339-- Tabellenstruktur für Tabelle `staking_daily_stats_archive`
340--
341
342DROP TABLE IF EXISTS `staking_daily_stats_archive`;
343CREATE TABLE `staking_daily_stats_archive` (
344 `id` int(11) NOT NULL,
345 `staker` varchar(128) NOT NULL,
346 `ratio` decimal(16,8) NOT NULL,
347 `amount` decimal(32,16) NOT NULL,
348 `asset` varchar(16) NOT NULL,
349 `total` decimal(16,8) NOT NULL,
350 `payout` decimal(16,12) NOT NULL,
351 `date` date NOT NULL,
352 `paid` int(11) DEFAULT '0',
353 `real_amount` decimal(32,16) NOT NULL,
354 `real_asset` varchar(16) NOT NULL,
355 `created` datetime NOT NULL
356) ENGINE=InnoDB DEFAULT CHARSET=utf8;
357
358-- --------------------------------------------------------
359
360--
361-- Tabellenstruktur für Tabelle `staking_payouts`
362--
363
364DROP TABLE IF EXISTS `staking_payouts`;
365CREATE TABLE `staking_payouts` (
366 `id` int(11) NOT NULL,
367 `staker` varchar(256) NOT NULL,
368 `amount` decimal(32,8) NOT NULL,
369 `asset` varchar(32) NOT NULL,
370 `memo` varchar(512) NOT NULL,
371 `status` int(11) NOT NULL,
372 `created` datetime NOT NULL
373) ENGINE=InnoDB DEFAULT CHARSET=latin1;
374
375-- --------------------------------------------------------
376
377--
378-- Tabellenstruktur für Tabelle `staking_positions`
379--
380
381DROP TABLE IF EXISTS `staking_positions`;
382CREATE TABLE `staking_positions` (
383 `id` int(11) NOT NULL,
384 `vesting_id` int(11) NOT NULL,
385 `owner` text NOT NULL,
386 `amount` decimal(16,8) NOT NULL,
387 `amount_with_bonus` decimal(16,8) NOT NULL,
388 `duration` int(11) NOT NULL,
389 `months` int(11) NOT NULL,
390 `weight` int(11) NOT NULL,
391 `active` int(11) NOT NULL,
392 `startTime` datetime NOT NULL,
393 `created` datetime NOT NULL,
394 `updated` datetime NOT NULL,
395 `inactive_date` datetime DEFAULT NULL
396) ENGINE=InnoDB DEFAULT CHARSET=utf8;
397
398-- --------------------------------------------------------
399
400--
401-- Tabellenstruktur für Tabelle `stats_assets`
402--
403
404DROP TABLE IF EXISTS `stats_assets`;
405CREATE TABLE `stats_assets` (
406 `id` int(11) NOT NULL,
407 `asset` varchar(32) NOT NULL,
408 `feepool` decimal(16,8) NOT NULL,
409 `profit` decimal(16,8) NOT NULL,
410 `supply` decimal(32,8) NOT NULL,
411 `real_supply` decimal(32,8) DEFAULT NULL,
412 `date` date NOT NULL,
413 `marketprofit` decimal(16,8) DEFAULT NULL,
414 `updated` datetime NOT NULL
415) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
416
417-- --------------------------------------------------------
418
419--
420-- Tabellenstruktur für Tabelle `stats_base`
421--
422
423DROP TABLE IF EXISTS `stats_base`;
424CREATE TABLE `stats_base` (
425 `id` int(11) NOT NULL,
426 `bts_balance` decimal(16,8) NOT NULL,
427 `vesting_balance` decimal(16,8) NOT NULL,
428 `todays_signups` int(11) NOT NULL,
429 `bts_btc` decimal(16,8) NOT NULL,
430 `date` date NOT NULL,
431 `updated` datetime NOT NULL
432) ENGINE=InnoDB DEFAULT CHARSET=utf8;
433
434-- --------------------------------------------------------
435
436--
437-- Tabellenstruktur für Tabelle `stats_markets`
438--
439
440DROP TABLE IF EXISTS `stats_markets`;
441CREATE TABLE `stats_markets` (
442 `id` int(11) NOT NULL,
443 `base` varchar(32) NOT NULL,
444 `quote` varchar(32) NOT NULL,
445 `last` decimal(16,8) NOT NULL,
446 `volume` decimal(16,8) NOT NULL,
447 `ask` decimal(65,8) NOT NULL,
448 `bid` decimal(16,8) NOT NULL,
449 `date` date NOT NULL,
450 `updated` datetime NOT NULL
451) ENGINE=InnoDB DEFAULT CHARSET=utf8;
452
453-- --------------------------------------------------------
454
455--
456-- Tabellenstruktur für Tabelle `system`
457--
458
459DROP TABLE IF EXISTS `system`;
460CREATE TABLE `system` (
461 `asset_type` varchar(32) NOT NULL,
462 `asset_name` varchar(32) NOT NULL,
463 `value_type` tinyint(4) NOT NULL,
464 `value_name` varchar(32) NOT NULL,
465 `value_str` varchar(32) NOT NULL,
466 `value_int` int(11) NOT NULL,
467 `value_float` double NOT NULL,
468 `status` tinyint(4) NOT NULL,
469 `last_ok` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
470 `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
471) ENGINE=InnoDB DEFAULT CHARSET=cp1250;
472
473-- --------------------------------------------------------
474
475--
476-- Tabellenstruktur für Tabelle `transactions`
477--
478
479DROP TABLE IF EXISTS `transactions`;
480CREATE TABLE `transactions` (
481 `id` int(11) NOT NULL,
482 `type` varchar(64) NOT NULL,
483 `txid` varchar(128) NOT NULL,
484 `from_id` varchar(64) NOT NULL,
485 `to_id` varchar(64) NOT NULL,
486 `from_name` varchar(128) NOT NULL,
487 `to_name` varchar(128) NOT NULL,
488 `block` int(11) NOT NULL,
489 `amount` decimal(16,8) NOT NULL,
490 `asset` varchar(128) NOT NULL,
491 `name` varchar(128) NOT NULL,
492 `time` datetime NOT NULL
493) ENGINE=InnoDB DEFAULT CHARSET=utf8;
494
495-- --------------------------------------------------------
496
497--
498-- Tabellenstruktur für Tabelle `transactions_fuckup`
499--
500
501DROP TABLE IF EXISTS `transactions_fuckup`;
502CREATE TABLE `transactions_fuckup` (
503 `id` int(11) NOT NULL,
504 `type` varchar(64) NOT NULL,
505 `txid` varchar(128) NOT NULL,
506 `from_id` varchar(64) NOT NULL,
507 `to_id` varchar(64) NOT NULL,
508 `from_name` varchar(128) NOT NULL,
509 `to_name` varchar(128) NOT NULL,
510 `block` int(11) NOT NULL,
511 `amount` decimal(16,8) NOT NULL,
512 `asset` varchar(128) NOT NULL,
513 `name` varchar(128) NOT NULL,
514 `time` datetime NOT NULL
515) ENGINE=InnoDB DEFAULT CHARSET=utf8;
516
517-- --------------------------------------------------------
518
519--
520-- Tabellenstruktur für Tabelle `tx`
521--
522
523DROP TABLE IF EXISTS `tx`;
524CREATE TABLE `tx` (
525 `txid` char(64) NOT NULL
526) ENGINE=InnoDB DEFAULT CHARSET=latin1;
527
528-- --------------------------------------------------------
529
530--
531-- Tabellenstruktur für Tabelle `withdrawal_transactions`
532--
533
534DROP TABLE IF EXISTS `withdrawal_transactions`;
535CREATE TABLE `withdrawal_transactions` (
536 `id` int(11) NOT NULL,
537 `coin` varchar(12) NOT NULL,
538 `txid` varchar(128) NOT NULL,
539 `tx_from` varchar(128) NOT NULL,
540 `tx_to` varchar(128) NOT NULL,
541 `from_name` varchar(128) DEFAULT NULL,
542 `to_name` varchar(128) DEFAULT NULL,
543 `amount` decimal(32,8) NOT NULL,
544 `amount_fees` decimal(32,8) DEFAULT NULL,
545 `fee` decimal(16,8) NOT NULL DEFAULT '0.00000000',
546 `asset` varchar(128) NOT NULL,
547 `memo` varchar(256) NOT NULL,
548 `outgoingTxId` varchar(256) DEFAULT NULL,
549 `error_message` varchar(256) DEFAULT NULL,
550 `error` int(11) NOT NULL,
551 `burned` int(11) DEFAULT NULL,
552 `sent` int(11) DEFAULT NULL,
553 `retried_count` int(11) NOT NULL DEFAULT '0',
554 `created` datetime NOT NULL
555) ENGINE=InnoDB DEFAULT CHARSET=utf8;
556
557-- --------------------------------------------------------
558
559--
560-- Struktur des Views `deposits_failed`
561--
562DROP TABLE IF EXISTS `deposits_failed`;
563
564CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `deposits_failed` AS select `de`.`id` AS `id`,`d`.`coin` AS `coin`,`de`.`amount` AS `amount`,`d`.`username` AS `username`,`t`.`txid` AS `txid`,`de`.`asset_issued` AS `asset_issued`,`d`.`created` AS `created` from ((`deposits_transactions` `de` join `deposits` `d` on((`d`.`id` = `de`.`deposit_id`))) left join `transactions` `t` on(((`t`.`type` = 'issue') and (`t`.`asset` = concat('bridge.',lcase(`d`.`coin`))) and (round(`t`.`amount`,2) = round(`de`.`amount`,2)) and (`d`.`username` = `t`.`to_name`) and (cast(`de`.`created` as date) = cast(`t`.`time` as date))))) where (`de`.`asset_issued` = 0) order by `d`.`created` desc ;
565
566-- --------------------------------------------------------
567
568--
569-- Struktur des Views `deposits_transactions_full`
570--
571DROP TABLE IF EXISTS `deposits_transactions_full`;
572
573CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `deposits_transactions_full` AS select `t`.`id` AS `id`,`t`.`coin` AS `coin`,`t`.`deposit_id` AS `deposit_id`,`t`.`amount` AS `amount`,`t`.`tx` AS `tx`,`t`.`asset_issued` AS `asset_issued`,`t`.`re_issue` AS `re_issue`,`t`.`memo` AS `memo`,`t`.`created` AS `created`,`d`.`address` AS `address`,`d`.`username` AS `username` from (`deposits_transactions` `t` join `deposits` `d` on((`t`.`deposit_id` = `d`.`id`))) ;
574
575-- --------------------------------------------------------
576
577--
578-- Struktur des Views `maker_taker_orders`
579--
580DROP TABLE IF EXISTS `maker_taker_orders`;
581
582CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `maker_taker_orders` AS select `o2`.`account_name` AS `buyer`,`o1`.`account_name` AS `seller`,`o1`.`receives` AS `receives`,`o1`.`pays` AS `pays`,round((`o1`.`receives` / `o1`.`pays`),8) AS `price`,`o1`.`base` AS `base`,`o1`.`quote` AS `quote`,`o1`.`time` AS `time`,(case when (cast(substr(`o1`.`order_id`,5,8) as unsigned) > cast(substr(`o2`.`order_id`,5,8) as unsigned)) then `o2`.`account_name` when (cast(substr(`o2`.`order_id`,5,8) as unsigned) > cast(substr(`o1`.`order_id`,5,8) as unsigned)) then `o1`.`account_name` end) AS `maker`,(case when (cast(substr(`o1`.`order_id`,5,8) as unsigned) > cast(substr(`o2`.`order_id`,5,8) as unsigned)) then `o2`.`fee` when (cast(substr(`o2`.`order_id`,5,8) as unsigned) > cast(substr(`o1`.`order_id`,5,8) as unsigned)) then `o1`.`fee` end) AS `maker_fee`,(case when (cast(substr(`o1`.`order_id`,5,8) as unsigned) > cast(substr(`o2`.`order_id`,5,8) as unsigned)) then `o2`.`feeAsset` when (cast(substr(`o2`.`order_id`,5,8) as unsigned) > cast(substr(`o1`.`order_id`,5,8) as unsigned)) then `o1`.`feeAsset` end) AS `maker_fee_asset`,(case when (cast(substr(`o1`.`order_id`,5,8) as unsigned) > cast(substr(`o2`.`order_id`,5,8) as unsigned)) then `o1`.`fee` when (cast(substr(`o2`.`order_id`,5,8) as unsigned) > cast(substr(`o1`.`order_id`,5,8) as unsigned)) then `o2`.`fee` end) AS `taker_fee`,(case when (cast(substr(`o1`.`order_id`,5,8) as unsigned) > cast(substr(`o2`.`order_id`,5,8) as unsigned)) then `o1`.`feeAsset` when (cast(substr(`o2`.`order_id`,5,8) as unsigned) > cast(substr(`o1`.`order_id`,5,8) as unsigned)) then `o2`.`feeAsset` end) AS `taker_fee_asset` from (`filled_orders` `o1` join `filled_orders` `o2` on(((`o1`.`pays` = `o2`.`receives`) and (`o1`.`time` = `o2`.`time`)))) order by `o1`.`time` desc limit 9000000 ;
583
584-- --------------------------------------------------------
585
586--
587-- Struktur des Views `orders_bco_btc`
588--
589DROP TABLE IF EXISTS `orders_bco_btc`;
590
591CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `orders_bco_btc` AS select `o2`.`account_name` AS `buyer`,`o1`.`account_name` AS `seller`,`o1`.`receives` AS `receives`,`o1`.`pays` AS `pays`,round((`o1`.`receives` / `o1`.`pays`),8) AS `price`,`o1`.`base` AS `base`,`o1`.`quote` AS `quote`,`o1`.`time` AS `time` from (`filled_orders` `o1` join `filled_orders` `o2` on(((`o1`.`pays` = `o2`.`receives`) and (`o1`.`time` = `o2`.`time`)))) where ((`o1`.`base` = 'BRIDGE.BCO') and (`o1`.`quote` = 'BRIDGE.BTC')) order by `o1`.`time` desc limit 250 ;
592
593-- --------------------------------------------------------
594
595--
596-- Struktur des Views `orders_volume_raw`
597--
598DROP TABLE IF EXISTS `orders_volume_raw`;
599
600CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `orders_volume_raw` AS (select count(0) AS `count`,`filled_orders`.`account_name` AS `account_name`,sum(`filled_orders`.`receives`) AS `receives`,sum(`filled_orders`.`pays`) AS `pays`,sum(`filled_orders`.`fee`) AS `fee`,`filled_orders`.`receiveAsset` AS `asset` from `filled_orders` where ((`filled_orders`.`receiveAsset` = 'BRIDGE.BTC') and (`filled_orders`.`base` = 'BRIDGE.BCO')) group by `filled_orders`.`account_name`) union (select count(0) AS `count`,`filled_orders`.`account_name` AS `account_name`,sum(`filled_orders`.`receives`) AS `receives`,sum(`filled_orders`.`pays`) AS `pays`,sum(`filled_orders`.`fee`) AS `fee`,`filled_orders`.`receiveAsset` AS `asset` from `filled_orders` where ((`filled_orders`.`receiveAsset` = 'BRIDGE.BCO') and (`filled_orders`.`base` = 'BRIDGE.BTC')) group by `filled_orders`.`account_name`) order by `count` desc ;
601
602-- --------------------------------------------------------
603
604--
605-- Struktur des Views `profits`
606--
607DROP TABLE IF EXISTS `profits`;
608
609CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `profits` AS select cast(`filled_orders`.`time` as date) AS `date`,`filled_orders`.`feeAsset` AS `asset`,sum(`filled_orders`.`fee`) AS `profit` from `filled_orders` where (`filled_orders`.`feeAsset` <> 'BTS') group by cast(`filled_orders`.`time` as date),`filled_orders`.`feeAsset` order by cast(`filled_orders`.`time` as date) desc ;
610
611--
612-- Indizes der exportierten Tabellen
613--
614
615--
616-- Indizes für die Tabelle `accounts`
617--
618ALTER TABLE `accounts`
619 ADD PRIMARY KEY (`id`),
620 ADD UNIQUE KEY `name` (`name`);
621
622--
623-- Indizes für die Tabelle `accounts_location`
624--
625ALTER TABLE `accounts_location`
626 ADD PRIMARY KEY (`ip`),
627 ADD KEY `country_code` (`country_code`);
628
629--
630-- Indizes für die Tabelle `coins`
631--
632ALTER TABLE `coins`
633 ADD PRIMARY KEY (`id`),
634 ADD UNIQUE KEY `coin` (`coin`);
635
636--
637-- Indizes für die Tabelle `coins_transactions`
638--
639ALTER TABLE `coins_transactions`
640 ADD PRIMARY KEY (`id`),
641 ADD UNIQUE KEY `coin` (`coin`,`type`,`amount`,`address`,`txid`) USING BTREE,
642 ADD KEY `coin_2` (`coin`),
643 ADD KEY `address` (`address`),
644 ADD KEY `txid` (`txid`);
645
646--
647-- Indizes für die Tabelle `deposits`
648--
649ALTER TABLE `deposits`
650 ADD PRIMARY KEY (`id`),
651 ADD KEY `username` (`username`(255)),
652 ADD KEY `address` (`address`),
653 ADD KEY `coin` (`coin`);
654
655--
656-- Indizes für die Tabelle `deposits_transactions`
657--
658ALTER TABLE `deposits_transactions`
659 ADD PRIMARY KEY (`id`),
660 ADD UNIQUE KEY `tx` (`tx`(128),`amount`,`deposit_id`) USING BTREE,
661 ADD KEY `coin` (`coin`),
662 ADD KEY `asset_issued` (`asset_issued`,`coin`) USING BTREE;
663
664--
665-- Indizes für die Tabelle `filled_orders`
666--
667ALTER TABLE `filled_orders`
668 ADD PRIMARY KEY (`id`),
669 ADD UNIQUE KEY `fill_id` (`fill_id`,`order_id`),
670 ADD KEY `time` (`time`),
671 ADD KEY `quote` (`quote`),
672 ADD KEY `base` (`base`);
673
674--
675-- Indizes für die Tabelle `maker_daily_stats`
676--
677ALTER TABLE `maker_daily_stats`
678 ADD PRIMARY KEY (`id`),
679 ADD UNIQUE KEY `maker` (`maker`,`maker_fee_asset`,`taker_fee_asset`,`date`);
680
681--
682-- Indizes für die Tabelle `orders`
683--
684ALTER TABLE `orders`
685 ADD PRIMARY KEY (`id`),
686 ADD UNIQUE KEY `order_id` (`order_id`);
687
688--
689-- Indizes für die Tabelle `queue_deposit`
690--
691ALTER TABLE `queue_deposit`
692 ADD UNIQUE KEY `id` (`id`),
693 ADD UNIQUE KEY `deposit_transaction_id` (`deposit_transaction_id`,`username`(128),`address`,`txid`(128),`amount`,`coin`(16)) USING BTREE;
694
695--
696-- Indizes für die Tabelle `staking_daily_stats`
697--
698ALTER TABLE `staking_daily_stats`
699 ADD PRIMARY KEY (`id`),
700 ADD UNIQUE KEY `staker` (`staker`,`amount`,`asset`,`date`),
701 ADD KEY `paid` (`paid`),
702 ADD KEY `date` (`date`),
703 ADD KEY `paid_2` (`paid`,`date`),
704 ADD KEY `asset` (`asset`),
705 ADD KEY `asset_2` (`asset`,`staker`(64));
706
707--
708-- Indizes für die Tabelle `staking_daily_stats_archive`
709--
710ALTER TABLE `staking_daily_stats_archive`
711 ADD PRIMARY KEY (`id`),
712 ADD UNIQUE KEY `staker` (`staker`,`amount`,`asset`,`date`),
713 ADD KEY `paid` (`paid`),
714 ADD KEY `date` (`date`),
715 ADD KEY `paid_2` (`paid`,`date`),
716 ADD KEY `asset` (`asset`),
717 ADD KEY `asset_2` (`asset`,`staker`(64));
718
719--
720-- Indizes für die Tabelle `staking_payouts`
721--
722ALTER TABLE `staking_payouts`
723 ADD PRIMARY KEY (`id`),
724 ADD KEY `staker` (`staker`,`amount`,`asset`,`memo`);
725
726--
727-- Indizes für die Tabelle `staking_positions`
728--
729ALTER TABLE `staking_positions`
730 ADD PRIMARY KEY (`id`),
731 ADD UNIQUE KEY `vesting_id` (`vesting_id`);
732
733--
734-- Indizes für die Tabelle `stats_assets`
735--
736ALTER TABLE `stats_assets`
737 ADD PRIMARY KEY (`id`),
738 ADD UNIQUE KEY `asset` (`asset`,`date`),
739 ADD KEY `updated` (`updated`);
740
741--
742-- Indizes für die Tabelle `stats_base`
743--
744ALTER TABLE `stats_base`
745 ADD PRIMARY KEY (`id`),
746 ADD UNIQUE KEY `date` (`date`);
747
748--
749-- Indizes für die Tabelle `stats_markets`
750--
751ALTER TABLE `stats_markets`
752 ADD PRIMARY KEY (`id`) USING BTREE,
753 ADD UNIQUE KEY `date` (`date`,`base`,`quote`);
754
755--
756-- Indizes für die Tabelle `system`
757--
758ALTER TABLE `system`
759 ADD PRIMARY KEY (`asset_type`,`asset_name`,`value_name`,`value_type`) USING BTREE;
760
761--
762-- Indizes für die Tabelle `transactions`
763--
764ALTER TABLE `transactions`
765 ADD PRIMARY KEY (`id`),
766 ADD UNIQUE KEY `txid` (`txid`);
767
768--
769-- Indizes für die Tabelle `tx`
770--
771ALTER TABLE `tx`
772 ADD PRIMARY KEY (`txid`);
773
774--
775-- Indizes für die Tabelle `withdrawal_transactions`
776--
777ALTER TABLE `withdrawal_transactions`
778 ADD PRIMARY KEY (`id`),
779 ADD UNIQUE KEY `txid` (`txid`,`coin`),
780 ADD UNIQUE KEY `txid_2` (`txid`);
781
782--
783-- AUTO_INCREMENT für exportierte Tabellen
784--
785
786--
787-- AUTO_INCREMENT für Tabelle `accounts`
788--
789ALTER TABLE `accounts`
790 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
791
792--
793-- AUTO_INCREMENT für Tabelle `coins`
794--
795ALTER TABLE `coins`
796 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
797
798--
799-- AUTO_INCREMENT für Tabelle `coins_transactions`
800--
801ALTER TABLE `coins_transactions`
802 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
803
804--
805-- AUTO_INCREMENT für Tabelle `deposits`
806--
807ALTER TABLE `deposits`
808 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
809
810--
811-- AUTO_INCREMENT für Tabelle `deposits_transactions`
812--
813ALTER TABLE `deposits_transactions`
814 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
815
816--
817-- AUTO_INCREMENT für Tabelle `filled_orders`
818--
819ALTER TABLE `filled_orders`
820 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
821
822--
823-- AUTO_INCREMENT für Tabelle `maker_daily_stats`
824--
825ALTER TABLE `maker_daily_stats`
826 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
827
828--
829-- AUTO_INCREMENT für Tabelle `orders`
830--
831ALTER TABLE `orders`
832 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
833
834--
835-- AUTO_INCREMENT für Tabelle `queue_deposit`
836--
837ALTER TABLE `queue_deposit`
838 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
839
840--
841-- AUTO_INCREMENT für Tabelle `staking_daily_stats`
842--
843ALTER TABLE `staking_daily_stats`
844 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
845
846--
847-- AUTO_INCREMENT für Tabelle `staking_daily_stats_archive`
848--
849ALTER TABLE `staking_daily_stats_archive`
850 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
851
852--
853-- AUTO_INCREMENT für Tabelle `staking_payouts`
854--
855ALTER TABLE `staking_payouts`
856 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
857
858--
859-- AUTO_INCREMENT für Tabelle `staking_positions`
860--
861ALTER TABLE `staking_positions`
862 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
863
864--
865-- AUTO_INCREMENT für Tabelle `stats_assets`
866--
867ALTER TABLE `stats_assets`
868 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
869
870--
871-- AUTO_INCREMENT für Tabelle `stats_base`
872--
873ALTER TABLE `stats_base`
874 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
875
876--
877-- AUTO_INCREMENT für Tabelle `stats_markets`
878--
879ALTER TABLE `stats_markets`
880 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
881
882--
883-- AUTO_INCREMENT für Tabelle `transactions`
884--
885ALTER TABLE `transactions`
886 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
887
888--
889-- AUTO_INCREMENT für Tabelle `withdrawal_transactions`
890--
891ALTER TABLE `withdrawal_transactions`
892 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
893COMMIT;