-
Notifications
You must be signed in to change notification settings - Fork 0
/
db.py
229 lines (196 loc) · 8.75 KB
/
db.py
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
import os
import psycopg
import dotenv
def connect_db() -> psycopg.Connection:
host=os.environ.get('DB_HOST')
user=os.environ.get('DB_USER')
password=os.environ.get('DB_PASSWORD')
database=os.environ.get('DB_DATABASE')
port=int(os.environ.get('DB_PORT', '5432'))
conn = psycopg.connect(
host=host,
user=user,
password=password,
dbname=database,
port=port
)
conn.autocommit = False
return conn
def main():
dotenv.load_dotenv()
print('Loading db schema...')
conn = connect_db()
curr = conn.cursor()
create_schema(curr)
conn.commit()
print('Done!')
def create_schema(curr: psycopg.cursor):
curr.execute(
'''
CREATE TABLE IF NOT EXISTS all_swaps (
id SERIAL PRIMARY KEY,
exchange_type TEXT NOT NULL,
timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
timestamp_unixtime INTEGER NOT NULL,
block_number INTEGER NOT NULL,
txn_idx INTEGER NOT NULL,
txn_hash TEXT NOT NULL,
block_log_index INTEGER NOT NULL,
seen_in_mempool BOOLEAN NOT NULL,
pool TEXT NOT NULL,
sender TEXT NOT NULL,
recipient TEXT NOT NULL,
origin TEXT NOT NULL,
token0_symbol TEXT NOT NULL,
token1_symbol TEXT NOT NULL,
token0_address TEXT NOT NULL,
token1_address TEXT NOT NULL,
amount0 DECIMAL,
amount1 DECIMAL,
amount_usd DECIMAL,
slippage DECIMAL,
slippage_protector_triggered BOOLEAN,
success BOOLEAN,
fail_reason TEXT,
gas_used_eth DECIMAL,
gas_used_usd DECIMAL
);
CREATE INDEX IF NOT EXISTS idx_all_swaps_bn ON all_swaps (block_number);
CREATE INDEX IF NOT EXISTS idx_all_swaps_exchange_type ON all_swaps (exchange_type);
CREATE INDEX IF NOT EXISTS idx_all_swaps_txn_hash ON all_swaps (txn_hash);
CREATE INDEX IF NOT EXISTS idx_all_swaps_ts ON all_swaps (timestamp);
CREATE TABLE IF NOT EXISTS likely_bots (
id SERIAL PRIMARY KEY,
address TEXT NOT NULL,
explanation TEXT DEFAULT ''
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_likely_bots_address ON likely_bots (address);
CREATE TABLE IF NOT EXISTS transaction_timestamps (
id SERIAL PRIMARY KEY,
transaction_hash TEXT NOT NULL,
timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL
);
CREATE INDEX IF NOT EXISTS tts_transaction_hash
ON transaction_timestamps (transaction_hash);
CREATE TABLE IF NOT EXISTS uniswap_v2_router_2_txns (
id SERIAL NOT NULL,
txn_hash BYTEA NOT NULL,
block_number INTEGER NOT NULL,
failed BOOLEAN NOT NULL,
txn_idx INTEGER NOT NULL,
from_address BYTEA NOT NULL,
to_address BYTEA NOT NULL,
exact_input BOOLEAN NOT NULL,
input_token BYTEA NOT NULL,
output_token BYTEA NOT NULL,
amount_input DECIMAL,
amount_output DECIMAL,
amount_bound DECIMAL,
supports_fee_on_transfer BOOLEAN NOT NULL,
pool BYTEA NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_uv2_r2_txns_bn ON uniswap_v2_router_2_txns (block_number);
CREATE TABLE IF NOT EXISTS uniswap_v3_router_2_txns (
id SERIAL NOT NULL,
txn_hash BYTEA NOT NULL,
block_number INTEGER NOT NULL,
failed BOOLEAN NOT NULL,
txn_idx INTEGER NOT NULL,
from_address BYTEA NOT NULL,
to_address BYTEA NOT NULL,
exact_input BOOLEAN NOT NULL,
input_token BYTEA NOT NULL,
output_token BYTEA NOT NULL,
amount_input DECIMAL,
amount_output DECIMAL,
amount_bound DECIMAL,
supports_fee_on_transfer BOOLEAN NOT NULL,
pool BYTEA NOT NULL,
pool_is_v2 BOOLEAN NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_uv3_r2_txns_bn ON uniswap_v3_router_2_txns (block_number);
CREATE TABLE IF NOT EXISTS uniswap_universal_router_txns (
id SERIAL NOT NULL,
txn_hash BYTEA NOT NULL,
block_number INTEGER NOT NULL,
failed BOOLEAN NOT NULL,
txn_idx INTEGER NOT NULL,
from_address BYTEA NOT NULL,
to_address BYTEA NOT NULL,
exact_input BOOLEAN NOT NULL,
input_token BYTEA NOT NULL,
output_token BYTEA NOT NULL,
amount_input DECIMAL,
amount_output DECIMAL,
amount_bound DECIMAL,
supports_fee_on_transfer BOOLEAN NOT NULL,
pool BYTEA NOT NULL,
pool_is_v2 BOOLEAN NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_uni_u_txns_bn ON uniswap_universal_router_txns (block_number);
CREATE TABLE IF NOT EXISTS sushiswap_router_txns (
id SERIAL NOT NULL,
txn_hash BYTEA NOT NULL,
block_number INTEGER NOT NULL,
failed BOOLEAN NOT NULL,
txn_idx INTEGER NOT NULL,
from_address BYTEA NOT NULL,
to_address BYTEA NOT NULL,
exact_input BOOLEAN NOT NULL,
input_token BYTEA NOT NULL,
output_token BYTEA NOT NULL,
amount_input DECIMAL,
amount_output DECIMAL,
amount_bound DECIMAL,
supports_fee_on_transfer BOOLEAN NOT NULL,
pool BYTEA NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sushi_txns_bn ON sushiswap_router_txns (block_number);
CREATE TABLE IF NOT EXISTS slippage_results (
id SERIAL NOT NULL PRIMARY KEY,
block_number INTEGER NOT NULL,
transaction_index INTEGER NOT NULL,
transaction_hash TEXT NOT NULL,
mempool_arrival_time TIMESTAMP NOT NULL,
queue_time_seconds FLOAT NOT NULL,
sender TEXT NOT NULL,
recipient TEXT NOT NULL,
router TEXT NOT NULL,
pool TEXT NOT NULL,
token_in TEXT NOT NULL,
token_out TEXT NOT NULL,
exact_in BOOLEAN NOT NULL,
amount_in DECIMAL,
amount_out DECIMAL,
amount_in_usd DECIMAL,
amount_out_usd DECIMAL,
boundary_amount DECIMAL NOT NULL,
expected_amount DECIMAL,
success BOOLEAN NOT NULL,
fail_reason TEXT DEFAULT '',
slippage DOUBLE PRECISION NOT NULL,
slippage_protector_triggered BOOLEAN NOT NULL,
gas_used_eth DECIMAL,
gas_used_usd DECIMAL
);
CREATE INDEX IF NOT EXISTS idx_slippage_results_bn ON slippage_results (block_number);
CREATE TABLE IF NOT EXISTS block_timestamps (
number INTEGER PRIMARY KEY,
timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL
);
CREATE INDEX IF NOT EXISTS bt_timestamp ON block_timestamps (timestamp);
CREATE TABLE IF NOT EXISTS selected_tokens (
id SERIAL NOT NULL PRIMARY KEY,
address BYTEA NOT NULL,
tvl_rank INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS st_address ON selected_tokens USING HASH (address);
CREATE TABLE IF NOT EXISTS consistent_pools (
id SERIAL NOT NULL PRIMARY KEY,
address TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS cp_address ON consistent_pools USING HASH (address);
'''
)
if __name__ == '__main__':
main()