/
up.sql
117 lines (110 loc) · 3.06 KB
/
up.sql
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
create view "Block" as
select
cast(coalesce((select sum(tx.fee) from tx where tx.block = block.id), 0) as integer) as "fees",
block.hash as hash,
block.merkel_root as "merkelRoot",
block.block_no as number,
previous_block."hash" as "previousBlockHash",
next_block."hash" as "nextBlockHash",
slot_leader."description" as "createdBy",
block.size as size,
block.tx_count as "transactionsCount",
block.epoch_no as "epochNo",
block.slot_no as "slotNo",
block.slot_no - (block.epoch_no * (10 * (select protocol_const from meta))) as "slotWithinEpoch",
block.time as "createdAt"
from block
left outer join block as previous_block
on block.previous = previous_block.id
left outer join block as next_block
on next_block.previous = block.id
left outer join slot_leader
on block.slot_leader = slot_leader.id;
create view "Cardano" as
select
number as "blockHeight",
"epochNo" as "currentEpochNo",
(select slot_duration from meta) as "slotDuration",
(select start_time from meta) as "startTime",
(select protocol_const from meta) as "protocolConst",
(select network_name from meta) as "networkName"
from "Block"
where number is not null
order by number desc
limit 1;
create view "Epoch" as
select
epoch.out_sum as "output",
epoch.no as "number",
epoch.tx_count as "transactionsCount",
epoch.start_time as "startedAt",
epoch.end_time as "lastBlockTime",
epoch.blk_count as "blocksCount"
from epoch;
create view "Transaction" as
select
block.hash as "blockHash",
coalesce(tx.fee, 0) as fee,
tx.hash as hash,
cast((select sum("value") from tx_out where tx_id = tx.id) as bigint) as "totalOutput",
tx.size,
block.time as "includedAt"
from
tx
inner join block
on block.id = tx.block;
create view "Utxo" as select
address,
value,
tx.hash as "txHash",
index
from tx
join tx_out
on tx.id = tx_out.tx_id
left outer join tx_in
on tx_out.tx_id = tx_in.tx_out_id
and tx_out.index = tx_in.tx_out_index
where tx_in.tx_in_id is null;
create view "TransactionInput" as
select
source_tx_out.address,
source_tx_out.value,
tx.hash as "txHash",
source_tx.hash as "sourceTxHash",
tx_in.tx_out_index as "sourceTxIndex"
from
tx
join tx_in
on tx_in.tx_in_id = tx.id
join tx_out as source_tx_out
on tx_in.tx_out_id = source_tx_out.tx_id
and tx_in.tx_out_index = source_tx_out.index
join tx as source_tx
on source_tx_out.tx_id = source_tx.id;
create view "TransactionOutput" as
select
address,
value,
tx.hash as "txHash",
index
from tx
join tx_out
on tx.id = tx_out.tx_id;
create function utxo_set_at_block("hash" hash32type)
returns setof "TransactionOutput" AS $$
select
"TransactionOutput".address,
"TransactionOutput".value,
"TransactionOutput"."txHash",
"TransactionOutput".index
from tx
join tx_out
on tx.id = tx_out.tx_id
join "TransactionOutput"
on tx.hash = "TransactionOutput"."txHash"
left outer join tx_in
on tx_out.tx_id = tx_in.tx_out_id
and tx_out.index = tx_in.tx_out_index
where tx_in.tx_in_id is null
and tx.block <= (select id from block where hash = "hash")
$$ language sql stable;