-
Notifications
You must be signed in to change notification settings - Fork 6
/
txstmts.go
116 lines (103 loc) · 3.97 KB
/
txstmts.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
package internal
const (
// Insert
insertTxRow0 = `INSERT INTO transactions (
block_hash, block_height, block_time, time,
tx_type, version, tree, tx_hash, block_index,
lock_time, expiry, size, spent, sent, fees,
num_vin, vins, vin_db_ids,
num_vout, vouts, vout_db_ids)
VALUES (
$1, $2, $3, $4,
$5, $6, $7, $8, $9,
$10, $11, $12, $13, $14, $15,
$16, $17, $18,
$19, $20, $21) `
insertTxRow = insertTxRow0 + `RETURNING id;`
insertTxRowChecked = insertTxRow0 + `ON CONFLICT (tx_hash, block_hash) DO NOTHING RETURNING id;`
upsertTxRow = insertTxRow0 + `ON CONFLICT (tx_hash, block_hash) DO UPDATE
SET block_hash = $1, block_index = $2, tree = $3 RETURNING id;`
insertTxRowReturnId = `WITH ins AS (` +
insertTxRow0 +
`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 blocks
WHERE tx_hash = $3 AND block_hash = $1
LIMIT 1;`
CreateTransactionTable = `CREATE TABLE IF NOT EXISTS transactions (
id SERIAL8 PRIMARY KEY,
/*block_db_id INT4,*/
block_hash TEXT,
block_height INT8,
block_time INT8,
time INT8,
tx_type INT4,
version INT4,
tree INT2,
tx_hash TEXT,
block_index INT4,
lock_time INT4,
expiry INT4,
size INT4,
spent INT8,
sent INT8,
fees INT8,
num_vin INT4,
vins JSONB,
vin_db_ids INT8[],
num_vout INT4,
vouts vout_t[],
vout_db_ids INT8[]
);`
SelectTxByHash = `SELECT id, block_hash, block_index, tree FROM transactions WHERE tx_hash = $1;`
SelectTxsByBlockHash = `SELECT id, tx_hash, block_index, tree FROM transactions WHERE block_hash = $1;`
SelectFullTxByHash = `SELECT id, block_hash, block_height, block_time,
time, tx_type, version, tree, tx_hash, block_index, lock_time, expiry,
size, spent, sent, fees, num_vin, vin_db_ids, num_vout, vout_db_ids
FROM transactions WHERE tx_hash = $1;`
SelectRegularTxByHash = `SELECT id, block_hash, block_index FROM transactions WHERE tx_hash = $1 and tree=0;`
SelectStakeTxByHash = `SELECT id, block_hash, block_index FROM transactions WHERE tx_hash = $1 and tree=1;`
IndexTransactionTableOnBlockIn = `CREATE UNIQUE INDEX uix_tx_block_in
ON transactions(block_hash, block_index, tree)
;` // STORING (tx_hash, block_hash)
DeindexTransactionTableOnBlockIn = `DROP INDEX uix_tx_block_in;`
IndexTransactionTableOnHashes = `CREATE UNIQUE INDEX uix_tx_hashes
ON transactions(tx_hash, block_hash)
;` // STORING (block_hash, block_index, tree)
DeindexTransactionTableOnHashes = `DROP INDEX uix_tx_hashes;`
//SelectTxByPrevOut = `SELECT * FROM transactions WHERE vins @> json_build_array(json_build_object('prevtxhash',$1)::jsonb)::jsonb;`
//SelectTxByPrevOut = `SELECT * FROM transactions WHERE vins #>> '{"prevtxhash"}' = '$1';`
//SelectTxsByPrevOutTx = `SELECT * FROM transactions WHERE vins @> json_build_array(json_build_object('prevtxhash',$1::TEXT)::jsonb)::jsonb;`
// '[{"prevtxhash":$1}]'
// RetrieveVoutValues = `WITH voutsOnly AS (
// SELECT unnest((vouts)) FROM transactions WHERE id = $1
// ) SELECT v.* FROM voutsOnly v;`
// RetrieveVoutValues = `SELECT vo.value
// FROM transactions txs, unnest(txs.vouts) vo
// WHERE txs.id = $1;`
// RetrieveVoutValue = `SELECT vouts[$2].value FROM transactions WHERE id = $1;`
RetrieveVoutDbIDs = `SELECT unnest(vout_db_ids) FROM transactions WHERE id = $1;`
RetrieveVoutDbID = `SELECT vout_db_ids[$2] FROM transactions WHERE id = $1;`
)
// func makeTxInsertStatement(voutDbIDs, vinDbIDs []uint64, vouts []*dbtypes.Vout, checked bool) string {
// voutDbIDsBIGINT := makeARRAYOfBIGINTs(voutDbIDs)
// vinDbIDsBIGINT := makeARRAYOfBIGINTs(vinDbIDs)
// voutCompositeARRAY := makeARRAYOfVouts(vouts)
// var insert string
// if checked {
// insert = insertTxRowChecked
// } else {
// insert = insertTxRow
// }
// return fmt.Sprintf(insert, voutDbIDsBIGINT, voutCompositeARRAY, vinDbIDsBIGINT)
// }
func MakeTxInsertStatement(checked bool) string {
if checked {
return insertTxRowChecked
}
return insertTxRow
}