-
Notifications
You must be signed in to change notification settings - Fork 103
/
002_views.sql
144 lines (132 loc) · 3.79 KB
/
002_views.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
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
-- Create all the views.
-- The views are always created because all views are deleted at the start ot the
-- migration process.
-- Conventions:
-- * VIEWs use TitleCase table names and camelCase columns for easy differentiation from tables.
-- The standard utxo view which shows all unspent transaction outputs.
create view "Utxo" as select
address,
value,
tx.hash as "txId",
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 "TransactionOutput" as
select
address,
value,
tx.hash as "txId",
index
from tx
join tx_out
on tx.id = tx_out.tx_id;
create view "TransactionInput" as
select
source_tx_out.address,
source_tx_out.value,
tx.hash as "txId",
source_tx.hash as "sourceTxId",
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 "Meta" as
select
slot_duration as "slotDuration",
start_time as "startTime",
protocol_const as "protocolConst"
from meta
limit 1;
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 id,
block.merkel_root as "merkelRootHash",
block.block_no as number,
previous_block."hash" as "previousBlockId",
next_block."hash" as "nextBlockId",
slot_leader."desciption" as "createdBy",
block.size as size,
-- Even though we have epochNo defined in the Slot view,
-- this is written by the node-client and makes identification
-- of EBBs simpler, as EBBs don't have a slot_no
block.epoch_no as "epochNo",
block.slot_no as "slotNo",
case when block.slot_no > 0
then block.slot_no - (block.epoch_no * (10 * (select protocol_const from meta)))
else 0
end 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 "Transaction" as
select
block.hash as "blockId",
COALESCE(tx.fee, 0) as fee,
tx.hash as id,
cast((select sum("value") from tx_out where tx_id = tx.id) as bigint) as "totalOutput",
block.time as "includedAt"
from
tx
inner join block
on block.id = tx.block;
create view "Epoch" as
select
cast(sum(tx_out.value) as bigint) as output,
count(distinct tx.hash) as "transactionsCount",
block.epoch_no as "number",
min(block.time) as "startedAt",
max(block.time) as "lastBlockTime"
from block
join tx
on tx.block = block.id
join tx_out
on tx_out.tx_id = tx.id
where epoch_no is not null
group by block.epoch_no
order by block.epoch_no;
-- This function plays really nicely with Hasura,
-- and allows us to query the utxo set at any block height
-- https://docs.hasura.io/1.0/graphql/manual/queries/custom-functions.html
CREATE FUNCTION utxo_set_at_block("blockId" hash32type)
RETURNS SETOF "TransactionOutput" AS $$
select
"TransactionOutput".address,
"TransactionOutput".value,
"TransactionOutput"."txId",
"TransactionOutput".index
from tx
join tx_out
on tx.id = tx_out.tx_id
join "TransactionOutput"
on tx.hash = "TransactionOutput"."txId"
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 = "blockId")
$$ LANGUAGE sql STABLE;
create view "Cardano" as
select
number as "blockHeight",
"epochNo" as "currentEpochNo"
from "Block"
where number is not null
order by number desc
limit 1;