---
# System prepended metadata

title: Cosmos-sdk sql port

---

# 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
```