# Cosmos-sdk sql port
1. основная вью с эвентами всех транзакий
```sql=
CREATE MATERIALIZED VIEW IF NOT EXISTS spacebox.txs_result_events
ENGINE = MergeTree() ORDER BY (height, type)
POPULATE AS SELECT * FROM (
SELECT
height,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(txs_results))), 'events'))), 'type') as type,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(txs_results))), 'events'))), 'attributes') as attributes
FROM spacebox.raw_block_results
);
```
2. вью с бегин и энд блокерами
```sql=
CREATE MATERIALIZED VIEW IF NOT EXISTS spacebox.begin_block_events
ENGINE = MergeTree() ORDER BY (height, type)
POPULATE AS SELECT * FROM (
SELECT
height,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(begin_block_events))), 'type') as type,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(begin_block_events))), 'attributes') as attributes
FROM spacebox.raw_block_results
);
```
3. вьюха для дебетов и кредетов. Нужно на все ивенты! Транзакции, бегин и энд блоки
```sql=
CREATE MATERIALIZED VIEW IF NOT EXISTS spacebox.end_debts_and_credts
ENGINE = MergeTree() ORDER BY (height, address)
POPULATE AS SELECT * FROM (
SELECT
height,
type,
FROM_BASE64(JSONExtractString(JSONExtractArrayRaw(attributes)[1], 'value')) as address,
arrayJoin(splitByChar(',', FROM_BASE64(JSONExtractString(JSONExtractArrayRaw(attributes)[2], 'value')))) as coins,
if(type = 'coin_received',
toInt128OrZero(EXTRACT(coins, '^(\\d+)')),
- toInt128OrZero(EXTRACT(coins, '^(\\d+)'))
) as amount,
EXTRACT(coins, '^\\d+(.*)') as denom
FROM spacebox.end_block_events
WHERE type = 'coin_received' OR type = 'coin_spent'
);
```
4. Проверка адреса
```sql=
SELECT
height,
amount,
denom,
sum(amount) OVER (ORDER BY height) as sum
FROM (
SELECT * FROM spacebox.end_debts_and_credts
WHERE address = 'cosmos14nzyt8wmx4g6zkeluelukamgsh5v4xgnmeq9y4' AND denom = 'uatom'
UNION ALL
SELECT * FROM spacebox.begin_debts_and_credts
WHERE address = 'cosmos14nzyt8wmx4g6zkeluelukamgsh5v4xgnmeq9y4' AND denom = 'uatom'
UNION ALL
SELECT * FROM spacebox.txs_debts_and_credts
WHERE address = 'cosmos14nzyt8wmx4g6zkeluelukamgsh5v4xgnmeq9y4' AND denom = 'uatom'
)
```
5. All messages
```sql=
CREATE MATERIALIZED VIEW IF NOT EXISTS spacebox.messages
ENGINE = MergeTree ORDER BY (timestamp, height, signer, type, txhash)
POPULATE AS SELECT * FROM (
SELECT
`timestamp`,
height,
signer,
txhash,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(JSONExtractString(tx, 'body'),'messages'))), '@type') as type,
arrayJoin(JSONExtractArrayRaw(JSONExtractString(JSONExtractString(tx, 'body'),'messages'))) as messages
FROM spacebox.raw_transaction
WHERE code = 0
);
```
6. Для нейтрона нужно было добавить варианты, где индекс у токена и адреса не однозначный, возможно для космоса так же
```sql=
CREATE MATERIALIZED VIEW IF NOT EXISTS spacebox.txs_debts_and_credts
ENGINE = MergeTree ORDER BY (height, address)
POPULATE AS SELECT * FROM (
SELECT
height,
type,
`attributes`,
if (
JSONExtractString(JSONExtractArrayRaw(attributes)[1], 'key') != 'amount',
JSONExtractString(JSONExtractArrayRaw(attributes)[1], 'value'),
JSONExtractString(JSONExtractArrayRaw(attributes)[2], 'value')
) as address,
if (
JSONExtractString(JSONExtractArrayRaw(attributes)[1], 'key') != 'amount',
arrayJoin(splitByChar(',', JSONExtractString(JSONExtractArrayRaw(attributes)[2], 'value'))),
arrayJoin(splitByChar(',', JSONExtractString(JSONExtractArrayRaw(attributes)[1], 'value')))
) as coins,
if(
type = 'coin_received',
toInt128OrZero(EXTRACT(coins, '^(\\d+)')),
- toInt128OrZero(EXTRACT(coins, '^(\\d+)'))
) as amount,
EXTRACT(coins, '^\\d+(.*)') as denom
FROM spacebox.txs_result_events
WHERE type = 'coin_received' OR type = 'coin_spent'
);
```
---
# Эксперимент с таблицами. Нейтрон
1. создал таблицу
```sql=
CREATE TABLE spacebox.events
(
`height` Int64,
`type` String,
`attributes` String
)
ENGINE = MergeTree()
ORDER BY (height, type)
```
2. Стопнул краулер. НАВЕРНОЕ НУЖНО УБЕДИТЬСЯ, ЧТО КАФКА ПУСТАЯ
4. закинул эвенты транз, энд и бегинблоков тремя запросами
```sql=
INSERT INTO spacebox.events
SELECT
height,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(txs_results))), 'events'))), 'type') as type,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(txs_results))), 'events'))), 'attributes') as attributes
FROM spacebox.raw_block_results
INSERT INTO spacebox.events
SELECT
height,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(begin_block_events))), 'type') as type,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(begin_block_events))), 'attributes') as attributes
FROM spacebox.raw_block_results
INSERT INTO spacebox.events
SELECT
height,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(end_block_events))), 'type') as type,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(end_block_events))), 'attributes') as attributes
FROM spacebox.raw_block_results
```
ПОСЛЕ ИНСЕРТОВ ДОЖДАТЬСЯ ПОКА ДАТА ПЕРЕНЕСЕТСЯ. 11.26 начал первую таблицу
4. создал вьюхи на запись
```sql=
CREATE MATERIALIZED VIEW IF NOT EXISTS spacebox.writing_txs_results
TO spacebox.events
AS SELECT * FROM (
SELECT
height,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(txs_results))), 'events'))), 'type') as type,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(txs_results))), 'events'))), 'attributes') as attributes
FROM spacebox.raw_block_results
)
CREATE MATERIALIZED VIEW IF NOT EXISTS spacebox.writing_begin_block_events
TO spacebox.events
AS SELECT * FROM (
SELECT
height,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(begin_block_events))), 'type') as type,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(begin_block_events))), 'attributes') as attributes
FROM spacebox.raw_block_results
)
CREATE MATERIALIZED VIEW IF NOT EXISTS spacebox.writing_end_block_events
TO spacebox.events
AS SELECT * FROM (
SELECT
height,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(end_block_events))), 'type') as type,
JSONExtractString(arrayJoin(JSONExtractArrayRaw(JSONExtractString(end_block_events))), 'attributes') as attributes
FROM spacebox.raw_block_results
)
```
все свапы
```sql=
WITH wasm_events AS (
SELECT * FROM spacebox.events
WHERE `type` = 'wasm'
)
SELECT
height,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = '_contract_address', JSONExtractArrayRaw(`attributes`))[1], 'value') AS contract_address,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'action', JSONExtractArrayRaw(`attributes`))[1], 'value') AS action,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'ask_asset', JSONExtractArrayRaw(`attributes`))[1], 'value') AS ask_asset,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'commission_amount', JSONExtractArrayRaw(`attributes`))[1], 'value') AS commission_amount,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'fee_share_amount', JSONExtractArrayRaw(`attributes`))[1], 'value') AS fee_share_amount,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'maker_fee_amount', JSONExtractArrayRaw(`attributes`))[1], 'value') AS maker_fee_amount,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'offer_amount', JSONExtractArrayRaw(`attributes`))[1], 'value') AS offer_amount,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'offer_asset', JSONExtractArrayRaw(`attributes`))[1], 'value') AS offer_asset,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'receiver', JSONExtractArrayRaw(`attributes`))[1], 'value') AS receiver,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'return_amount', JSONExtractArrayRaw(`attributes`))[1], 'value') AS return_amount,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'sender', JSONExtractArrayRaw(`attributes`))[1], 'value') AS sender,
JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'spread_amount', JSONExtractArrayRaw(`attributes`))[1], 'value') AS spread_amount
FROM wasm_events
WHERE JSONExtractString(arrayFilter(x -> JSONExtractString(x, 'key') = 'action', JSONExtractArrayRaw(`attributes`))[1], 'value') = 'swap'
ORDER BY height
```
MESSAGES CHECK TO_GRAFANA
```sql=
SELECT count(*) FROM (
SELECT height, msgs.height as _height FROM (
SELECT height FROM spacebox.raw_transaction
where code = 0
GROUP BY height
) as txs
LEFT JOIN (select height from spacebox.message GROUP BY height ) as msgs ON txs.height = msgs.height
)
WHERE height = 0
```
Sign in with Wallet
Connect another wallet