-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathanalytics.ddl
330 lines (282 loc) · 10.8 KB
/
analytics.ddl
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
DROP TABLE pdexanalytics.trades;
DROP TABLE pdexanalytics.exchange_alltime;
DROP TABLE pdexanalytics.exchange_hourly;
DROP TABLE pdexanalytics.exchange_24h;
DROP TABLE pdexanalytics.markets_24h;
DROP TABLE pdexanalytics.assets_24h;
DROP TABLE pdexanalytics.exchange_daily;
DROP TABLE pdexanalytics.markets_daily;
DROP TABLE pdexanalytics.assets_daily;
DROP TABLE pdexanalytics.assets_hourly;
DROP TABLE pdexanalytics.markets;
DROP TABLE pdexanalytics.assets;
CREATE TABLE pdexanalytics.assets
(
asset_id varchar(64) not null,
symbol varchar(60) not null,
name varchar(60) not null,
price decimal(20,10),
tvl decimal(12,2),
balance decimal(16,4)
is_active bit not null,
PRIMARY KEY (asset_id)
);
ALTER TABLE assets ADD INDEX symbol_index (symbol);
CREATE TABLE pdexanalytics.markets
(
base_asset_id varchar(64) not null,
quote_asset_id varchar(64) not null,
is_active bit not null,
PRIMARY KEY (base_asset_id, quote_asset_id),
FOREIGN KEY (base_asset_id) REFERENCES assets(asset_id),
FOREIGN KEY (quote_asset_id) REFERENCES assets(asset_id)
);
CREATE TABLE pdexanalytics.trades
(
trade_id bigint not null,
base_asset_id varchar(64) not null,
quote_asset_id varchar(64) not null,
price decimal(20,10) not null,
quantity decimal(20,10) not null,
volume decimal(12,2),
timestamp datetime not null,
PRIMARY KEY (trade_id),
FOREIGN KEY (base_asset_id) REFERENCES assets(asset_id),
FOREIGN KEY (quote_asset_id) REFERENCES assets(asset_id)
);
ALTER TABLE trades ADD INDEX timestamp_index (timestamp);
ALTER TABLE trades add INDEX market_index (base_asset_id, quote_asset_id);
CREATE TABLE pdexanalytics.exchange_daily
(
stat_date date not null,
tvl decimal(12,2),
volume decimal(12,2),
users int,
trades int,
total_staked int,
staked_tvl decimal(12,2),
total_holders int,
total_stakers int,
PRIMARY KEY (stat_date)
);
CREATE TABLE pdexanalytics.markets_hourly
(
stat_time timestamp not null,
base_asset_id varchar(64) not null,
quote_asset_id varchar(64) not null,
price decimal(20,10) null,
PRIMARY KEY (stat_time, base_asset_id, quote_asset_id),
FOREIGN KEY (base_asset_id) REFERENCES assets(asset_id),
FOREIGN KEY (quote_asset_id) REFERENCES assets(asset_id)
);
ALTER TABLE markets_hourly ADD INDEX market_pair_index (base_asset_id, quote_asset_id);
CREATE TABLE pdexanalytics.markets_daily
(
stat_date date not null,
base_asset_id varchar(64) not null,
quote_asset_id varchar(64) not null,
volume decimal(12,2),
trades int,
PRIMARY KEY (stat_date, base_asset_id, quote_asset_id),
FOREIGN KEY (base_asset_id) REFERENCES assets(asset_id),
FOREIGN KEY (quote_asset_id) REFERENCES assets(asset_id)
);
ALTER TABLE markets_daily ADD INDEX market_pair_index (base_asset_id, quote_asset_id);
CREATE TABLE pdexanalytics.assets_daily
(
stat_date date not null,
asset_id varchar(64) not null,
tvl decimal(12,2),
price decimal(20,10) not null,
volume decimal(12,2),
trades int,
balance decimal(16,4)
PRIMARY KEY (stat_date, asset_id)
);
ALTER TABLE assets_daily ADD INDEX asset_index (asset_id);
CREATE TABLE pdexanalytics.assets_hourly
(
stat_time timestamp not null,
asset_id varchar(64) not null,
tvl decimal(12,2),
price decimal(20,10) not null,
balance decimal(16,4),
PRIMARY KEY (stat_time, asset_id)
);
ALTER TABLE assets_hourly ADD INDEX asset_index (asset_id);
CREATE TABLE pdexanalytics.exchange_hourly
(
stat_time timestamp not null,
tvl decimal(12,2),
volume decimal(12,2),
users int,
trades int,
total_staked int default null,
staked_tvl decimal(12,2) default null,
total_holders int default null,
total_stakers int default null,
PRIMARY KEY (stat_time)
);
CREATE TABLE pdexanalytics.exchange_24h
(
tvl decimal(12,2),
volume decimal(12,2),
users int,
trades int,
total_staked int default null,
staked_tvl decimal(12,2) default null,
total_holders int default null,
total_stakers int default null,
previous_tvl decimal(12,2),
previous_volume decimal(12,2),
previous_users int,
previous_trades int,
previous_total_staked int default null,
previous_staked_tvl decimal(12,2) default null,
previous_total_holders int default null,
previous_total_stakers int default null
);
INSERT INTO pdexanalytics.exchange_24h () VALUES();
CREATE TABLE pdexanalytics.assets_24h
(
asset_id varchar(64) not null,
tvl decimal(12,2),
price decimal(20,10) not null,
balance decimal(16,4) default null,
volume decimal(12,2),
trades int,
previous_tvl decimal(12,2),
previous_price decimal(20,10) not null,
previous_balance decimal(16,4) default null,
previous_volume decimal(12,2),
previous_trades int,
PRIMARY KEY (asset_id)
);
CREATE TABLE pdexanalytics.markets_24h
(
base_asset_id varchar(64) not null,
quote_asset_id varchar(64) not null,
volume decimal(12,2),
trades int,
previous_volume decimal(12,2),
previous_trades int,
PRIMARY KEY (base_asset_id, quote_asset_id),
FOREIGN KEY (base_asset_id) REFERENCES assets(asset_id),
FOREIGN KEY (quote_asset_id) REFERENCES assets(asset_id)
);
alter table exchange_daily add treasury_balance int null;
alter table exchange_daily add treasury_tvl decimal(12,2) null;
alter table exchange_hourly add treasury_balance int null;
alter table exchange_hourly add treasury_tvl decimal(12,2) null;
alter table exchange_24h add treasury_balance int null;
alter table exchange_24h add treasury_tvl decimal(12,2) null;
alter table exchange_24h add previous_treasury_balance int null;
alter table exchange_24h add previous_treasury_tvl decimal(12,2) null;
alter table exchange_daily add total_issuance int null;
alter table exchange_hourly add total_issuance int null;
alter table exchange_24h add total_issuance int null;
alter table exchange_24h add previous_total_issuance int null;
alter table trades modify column volume decimal(18,6);
alter table exchange_daily modify column volume decimal(18,6);
alter table markets_daily modify column volume decimal(18,6);
alter table assets_daily modify column volume decimal(18,6);
alter table exchange_hourly modify column volume decimal(18,6);
alter table exchange_24h modify column volume decimal(18,6);
alter table assets_24h modify column volume decimal(18,6);
alter table markets_24h modify column volume decimal(18,6);
alter table exchange_24h modify column previous_volume decimal(18,6);
alter table assets_24h modify column previous_volume decimal(18,6);
alter table markets_24h modify column previous_volume decimal(18,6);
alter table trades add column m_id varchar(100);
alter table trades add column t_id varchar(100);
alter table trades add column m_cid varchar(100);
alter table trades add column t_cid varchar(100);
alter table trades add column m_side varchar(6);
alter table trades add column t_side varchar(6);
alter table trades add column trade_oid varchar(100);
alter table exchange_daily add new_users int null;
alter table exchange_hourly add new_users int null;
alter table exchange_24h add new_users int null;
alter table exchange_24h add previous_new_users int null;
alter table assets add fees decimal(18,9) null;
alter table assets add fees_value decimal(12,2) null;
alter table assets_daily add fees decimal(18,9) null;
alter table assets_hourly add fees decimal(18,9) null;
alter table assets_24h add fees decimal(18,9) null;
alter table assets_24h add previous_fees decimal(18,9) null;
alter table assets_daily add fees_value decimal(12,2) null;
alter table assets_hourly add fees_value decimal(12,2) null;
alter table assets_24h add fees_value decimal(12,2) null;
alter table assets_24h add previous_fees_value decimal(12,2) null;
alter table exchange_daily add total_fees decimal(12,2) null;
alter table exchange_hourly add total_fees decimal(12,2) null;
alter table exchange_24h add total_fees decimal(12,2) null;
alter table exchange_24h add previous_total_fees decimal(12,2) null;
alter table assets add new_fees decimal(18,9) null;
alter table assets add new_fees_value decimal(12,2) null;
alter table assets_daily add new_fees decimal(18,9) null;
alter table assets_hourly add new_fees decimal(18,9) null;
alter table assets_24h add new_fees decimal(18,9) null;
alter table assets_24h add previous_new_fees decimal(18,9) null;
alter table assets_daily add new_fees_value decimal(12,2) null;
alter table assets_hourly add new_fees_value decimal(12,2) null;
alter table assets_24h add new_fees_value decimal(12,2) null;
alter table assets_24h add previous_new_fees_value decimal(12,2) null;
alter table exchange_daily add total_fees decimal(12,2) null;
alter table exchange_hourly add total_fees decimal(12,2) null;
alter table exchange_24h add total_fees decimal(12,2) null;
alter table exchange_24h add previous_total_fees decimal(12,2) null;
alter table exchange_daily add new_total_fees decimal(12,2) null;
alter table exchange_hourly add new_total_fees decimal(12,2) null;
alter table exchange_24h add new_total_fees decimal(12,2) null;
alter table exchange_24h add previous_new_total_fees decimal(12,2) null;
CREATE TABLE pdexanalytics.exchange_alltime
(
volume decimal(18,2),
trades int,
total_fees decimal(18,2)
);
insert into exchange_alltime (volume, trades, total_fees) values (0,0,0);
alter table trades add volume_quote decimal(20,10) null;
alter table markets_daily add column volume_quote decimal(20,10) null;
alter table markets_24h add column volume_quote decimal(20,10) null;
alter table markets_24h add column previous_volume_quote decimal(20,10) null;
alter table markets_daily add column volume_base decimal(20,10) null;
alter table markets_24h add column volume_base decimal(20,10) null;
alter table markets_24h add column previous_volume_base decimal(20,10) null;
alter table pdexanalytics.markets add price decimal(20,10) null;
alter table pdexanalytics.markets_24h add price_high decimal(20,10) null;
alter table pdexanalytics.markets_24h add price_low decimal(20,10) null;
alter table pdexanalytics.markets_24h add price_24h decimal(20,10) null;
CREATE TABLE pdexanalytics.orderbook_lastupdate
(
last_update timestamp
);
INSERT INTO pdexanalytics.orderbook_lastupdate () VALUES();
CREATE TABLE pdexanalytics.orderbook
(
stid varchar(100) not null,
base_asset_id varchar(64) not null,
quote_asset_id varchar(64) not null,
price decimal(20,10) not null,
quantity decimal(20,10) not null,
side varchar(6) not null,
primary key(stid)
);
ALTER TABLE orderbook add INDEX full_orderbook_index (base_asset_id, quote_asset_id);
ALTER TABLE orderbook add INDEX orderbook_side_index (base_asset_id, quote_asset_id, side);
CREATE TABLE pdexanalytics.metadata
(
data_name varchar(64) not null,
data_value varchar(60) not null,
PRIMARY KEY (data_name)
);
CREATE TABLE pdexanalytics.fee_withdrawals
(
transfer_id bigint not null,
asset_symbol varchar(64) not null,
amount decimal(20,10) not null,
PRIMARY KEY (transfer_id)
);
ALTER TABLE fee_withdrawals add INDEX asset_symbol_idx (asset_symbol);
alter table orderbook drop primary key, add primary key(base_asset_id, quote_asset_id, price, side);