# Tendermint Postgres indexer

## Dependencies

* Tendermint v0.34.15 or higher
* Postgres database 14
* Docker

### Setting up Postgres database

This repo will create a postgres docker image with tendermint events schema.

You can find tendermint events schema [Here](https://github.com/tendermint/tendermint/blob/v0.34.21/state/indexer/sink/psql/schema.sql).


Note: Above schema also creates three `VIEWS`.
* `block_events`
* `event_attributes`
* `tx_events`

git clone https://github.com/aleem1314/postgres-indexer-example.git

Let's build docker image and start postgres server

$ docker build -t "regen-events-indexer" .
$ docker run -it -d -p 5432:5432 -e POSTGRES_PASSWORD=postgres -v ./pg-volume:/var/lib/postgresql/data regen-events-indexer:latest

### Setup Regen single node

Clone regen-ledger repo and build binary

$ git clone https://github.com/regen-network/regen-ledger.git

$ git checkout main

$ make build

Setting up a single node

$ cd build

$ ./regen -h #lists all the commands available

Create few accounts testing purpose

$ ./regen keys add key1 --keyring-backend test
$ ./regen keys add key2 --keyring-backend test
$ ./regen keys add key2 --keyring-backend test

Setup a local testnet

# Initialize the testnet with chain-id and some validator name
# This will create the configurations required for the testnet in daemon home directory, for `regen` it's ~/.regen 
$ ./regen init --chain-id testnet myvalidator

# Add some genesis accounts and tokens. We can do this via keyname (if the exists on your local machine) or with address
$ ./regen add-genesis-account validator-key 1000000000stake
$ ./regen add-genesis-account $(./regen keys show mykey1 -a) 10000000000stake


# Create gentx (create validator genesis transaction)
$ ./regen gentx validator-key 100000000stake --chain-id testnet

# Collect gentxs
$ ./regen collect-gentxs

Before starting the network let's configure postgres indexer.

## Postgres indexer configuration

To enable postgres indexer, go to `[tx_index]` section in `config.toml` file(you can find `config.toml` file in `.regen/config` directory).

* Change `indexer` type to `psql`.
* And set postgres connection URL `psql-conn`.

indexer = "psql"

psql-conn = "postgresql://postgres:postgres@127.0.0.1:5432/regen_events?sslmode=disable"

Start regen node

$ ./regen start

NOTE: Searching is not enabled for the psql indexer type via Tendermint's RPC - following queries will fail.

* `TxByEvents` rpc call or tx-by-events REST endpoint `/cosmos/tx/v1beta1/txs?events=message.action='/regen.ecocredit.v1.MsgCreateBatch'`.
* `TxByHash` rpc call or tx-by-hash REST endpoint `/cosmos/tx/v1beta1/tx/<tx-hash>`.

### Performing SQL queries

login into postgres container

$ docker exec -tiu postgres <container-id>  psql

change database to `regen_events`

$ \c regen_events

List all tables

$ \dt

# This should return following output

           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | attributes | table | postgres
 public | blocks     | table | postgres
 public | events     | table | postgres
 public | tx_results | table | postgres
(4 rows)

To get all the events by `ecocredit` module

$ SELECT * FROM event_attributes WHERE tx_id IN (
	SELECT tx_id FROM event_attributes WHERE type LIKE '%.ecocredit.%' group by tx_id)

To get all the events by `data` module

$ SELECT * FROM event_attributes WHERE tx_id IN (
	SELECT tx_id FROM event_attributes WHERE type LIKE '%.data.%' group by tx_id)

To query all block events (those having tx_id NULL)

SELECT blocks.rowid as block_id, height, chain_id, type, key, composite_key, value
  FROM blocks JOIN event_attributes ON (blocks.rowid = event_attributes.block_id)
  WHERE event_attributes.tx_id IS NULL; 

To query transaction ids by module (example: bank)

select distinct tx_id from attributes join events on events.rowid=attributes.event_id where attributes.value like '%bank%'