forked from decred/dcrdata
-
Notifications
You must be signed in to change notification settings - Fork 0
/
stakestmts.go
222 lines (196 loc) · 7.06 KB
/
stakestmts.go
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
package internal
const (
// Tickets
CreateTicketsTable = `CREATE TABLE IF NOT EXISTS tickets (
id SERIAL PRIMARY KEY,
tx_hash TEXT NOT NULL,
block_hash TEXT NOT NULL,
block_height INT4,
purchase_tx_db_id INT8,
stakesubmission_address TEXT,
is_multisig BOOLEAN,
is_split BOOLEAN,
num_inputs INT2,
price FLOAT8,
fee FLOAT8,
spend_type INT2,
pool_status INT2,
spend_height INT4,
spend_tx_db_id INT8
);`
// Insert
insertTicketRow0 = `INSERT INTO tickets (
tx_hash, block_hash, block_height, purchase_tx_db_id,
stakesubmission_address, is_multisig, is_split,
num_inputs, price, fee, spend_type, pool_status)
VALUES (
$1, $2, $3, $4,
$5, $6, $7,
$8, $9, $10, $11, $12) `
insertTicketRow = insertTicketRow0 + `RETURNING id;`
// insertTicketRowChecked = insertTicketRow0 + `ON CONFLICT (tx_hash, block_hash) DO NOTHING RETURNING id;`
upsertTicketRow = insertTicketRow0 + `ON CONFLICT (tx_hash, block_hash) DO UPDATE
SET tx_hash = $1, block_hash = $2 RETURNING id;`
insertTicketRowReturnId = `WITH ins AS (` +
insertTicketRow0 +
`ON CONFLICT (tx_hash, block_hash) DO UPDATE
SET tx_hash = NULL WHERE FALSE
RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM tickets
WHERE tx_hash = $1 AND block_hash = $2
LIMIT 1;`
SelectTicketsInBlock = `SELECT * FROM tickets WHERE block_hash = $1;`
SelectTicketsTxDbIDsInBlock = `SELECT purchase_tx_db_id FROM tickets WHERE block_hash = $1;`
SelectTicketsForAddress = `SELECT * FROM tickets WHERE stakesubmission_address = $1;`
SelectTicketsForPriceAtLeast = `SELECT * FROM tickets WHERE price >= $1;`
SelectTicketsForPriceAtMost = `SELECT * FROM tickets WHERE price <= $1;`
SelectTicketIDHeightByHash = `SELECT id, block_height FROM tickets WHERE tx_hash = $1;`
SelectTicketIDByHash = `SELECT id FROM tickets WHERE tx_hash = $1;`
SelectTicketStatusByHash = `SELECT id, spend_type, pool_status FROM tickets WHERE tx_hash = $1;`
SelectUnspentTickets = `SELECT id, tx_hash FROM tickets WHERE spend_type = 0 OR spend_type = -1;`
// Update
SetTicketSpendingInfoForHash = `UPDATE tickets
SET spend_type = $5, spend_height = $3, spend_tx_db_id = $4, pool_status = $6
WHERE tx_hash = $1 and block_hash = $2;`
SetTicketSpendingInfoForTicketDbID = `UPDATE tickets
SET spend_type = $4, spend_height = $2, spend_tx_db_id = $3, pool_status = $5
WHERE id = $1;`
SetTicketSpendingInfoForTxDbID = `UPDATE tickets
SET spend_type = $4, spend_height = $2, spend_tx_db_id = $3, pool_status = $5
WHERE purchase_tx_db_id = $1;`
SetTicketPoolStatusForTicketDbID = `UPDATE tickets SET pool_status = $2 WHERE id = $1;`
SetTicketPoolStatusForHash = `UPDATE tickets SET pool_status = $2 WHERE tx_hash = $1;`
// Index
IndexTicketsTableOnHashes = `CREATE UNIQUE INDEX uix_ticket_hashes_index
ON tickets(tx_hash, block_hash);`
DeindexTicketsTableOnHashes = `DROP INDEX uix_ticket_hashes_index;`
IndexTicketsTableOnTxDbID = `CREATE UNIQUE INDEX uix_ticket_ticket_db_id
ON tickets(purchase_tx_db_id);`
DeindexTicketsTableOnTxDbID = `DROP INDEX uix_ticket_ticket_db_id;`
DeleteTicketsDuplicateRows = `DELETE FROM tickets
WHERE id IN (SELECT id FROM (
SELECT id, ROW_NUMBER()
OVER (partition BY tx_hash, block_hash ORDER BY id) AS rnum
FROM tickets) t
WHERE t.rnum > 1);`
// Votes
CreateVotesTable = `CREATE TABLE IF NOT EXISTS votes (
id SERIAL PRIMARY KEY,
height INT4,
tx_hash TEXT NOT NULL,
block_hash TEXT NOT NULL,
candidate_block_hash TEXT NOT NULL,
version INT2,
vote_bits INT2,
block_valid BOOLEAN,
ticket_hash TEXT,
ticket_tx_db_id INT8,
ticket_price FLOAT8,
vote_reward FLOAT8
);`
// Insert
insertVoteRow0 = `INSERT INTO votes (
height, tx_hash,
block_hash, candidate_block_hash,
version, vote_bits, block_valid,
ticket_hash, ticket_tx_db_id, ticket_price, vote_reward)
VALUES (
$1, $2,
$3, $4,
$5, $6, $7,
$8, $9, $10, $11) `
insertVoteRow = insertVoteRow0 + `RETURNING id;`
// insertVoteRowChecked = insertVoteRow0 + `ON CONFLICT (tx_hash, block_hash) DO NOTHING RETURNING id;`
upsertVoteRow = insertVoteRow0 + `ON CONFLICT (tx_hash, block_hash) DO UPDATE
SET tx_hash = $2, block_hash = $3 RETURNING id;`
insertVoteRowReturnId = `WITH ins AS (` +
insertVoteRow0 +
`ON CONFLICT (tx_hash, block_hash) DO UPDATE
SET tx_hash = NULL WHERE FALSE
RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM votes
WHERE tx_hash = $2 AND block_hash = $3
LIMIT 1;`
SelectAllVoteDbIDsHeightsTicketHashes = `SELECT id, height, ticket_hash FROM votes;`
SelectAllVoteDbIDsHeightsTicketDbIDs = `SELECT id, height, ticket_tx_db_id FROM votes;`
// Index
IndexVotesTableOnHashes = `CREATE UNIQUE INDEX uix_votes_hashes_index
ON votes(tx_hash, block_hash);`
DeindexVotesTableOnHashes = `DROP INDEX uix_votes_hashes_index;`
IndexVotesTableOnCandidate = `CREATE INDEX uix_votes_candidate_block
ON votes(candidate_block_hash);`
DeindexVotesTableOnCandidate = `DROP INDEX uix_votes_candidate_block;`
IndexVotesTableOnVoteVersion = `CREATE INDEX uix_votes_vote_version
ON votes(version);`
DeindexVotesTableOnVoteVersion = `DROP INDEX uix_votes_vote_version;`
DeleteVotesDuplicateRows = `DELETE FROM votes
WHERE id IN (SELECT id FROM (
SELECT id, ROW_NUMBER()
OVER (partition BY tx_hash, block_hash ORDER BY id) AS rnum
FROM votes) t
WHERE t.rnum > 1);`
// Misses
CreateMissesTable = `CREATE TABLE IF NOT EXISTS misses (
id SERIAL PRIMARY KEY,
height INT4,
block_hash TEXT NOT NULL,
candidate_block_hash TEXT NOT NULL,
ticket_hash TEXT NOT NULL
);`
// Insert
insertMissRow0 = `INSERT INTO misses (
height, block_hash, candidate_block_hash, ticket_hash)
VALUES (
$1, $2, $3, $4) `
insertMissRow = insertMissRow0 + `RETURNING id;`
// insertVoteRowChecked = insertMissRow0 + `ON CONFLICT (ticket_hash, block_hash) DO NOTHING RETURNING id;`
upsertMissRow = insertMissRow0 + `ON CONFLICT (ticket_hash, block_hash) DO UPDATE
SET ticket_hash = $4, block_hash = $2 RETURNING id;`
insertMissRowReturnId = `WITH ins AS (` +
insertMissRow0 +
`ON CONFLICT (ticket_hash, block_hash) DO UPDATE
SET ticket_hash = NULL WHERE FALSE
RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM misses
WHERE ticket_hash = $4 AND block_hash = $2
LIMIT 1;`
SelectMissesInBlock = `SELECT ticket_hash FROM misses WHERE block_hash = $1;`
// Index
IndexMissesTableOnHashes = `CREATE UNIQUE INDEX uix_misses_hashes_index
ON misses(ticket_hash, block_hash);`
DeindexMissesTableOnHashes = `DROP INDEX uix_misses_hashes_index;`
DeleteMissesDuplicateRows = `DELETE FROM misses
WHERE id IN (SELECT id FROM (
SELECT id, ROW_NUMBER()
OVER (partition BY ticket_hash, block_hash ORDER BY id) AS rnum
FROM misses) t
WHERE t.rnum > 1);`
// Revokes?
)
func MakeTicketInsertStatement(checked bool) string {
if checked {
return upsertTicketRow
}
return insertTicketRow
}
func MakeVoteInsertStatement(checked bool) string {
if checked {
return upsertVoteRow
}
return insertVoteRow
}
func MakeMissInsertStatement(checked bool) string {
if checked {
return upsertMissRow
}
return insertMissRow
}