SQL Examples
Findora SQL CLI renders complex queries effectively. This page showcases some of the possibilities. You can perform queries via REST API or SQL CLI. To run SQL CLI, simply issue this command:
finsql
Optionally, you can connect to a different host (rather than localhost) with -h flag, and a different port (rather than 9200) with -p flag. Like most other SQL CLIs, you don't need to use these flags with a default installation.
When you fire up the SQL CLI, it shows you a list of all available tables. Tables ending in _flat store consensus data along with EVM block info (if available), and tables ending in _tx store EVM transactions data. All columns of all tables are indexed which means any query will be executed lightning fast. As Findexer is just an indexer, and it represents Findora's blockchain data, you CANNOT UPDATE or INSERT data. You can only issue read-only commands i.e. SELECT, DESC, and SHOW.
It's necessary to note that the underlying technology is NOT a SQL database. However, you can think it's a database, and treat it like one! The underlying technology here is Elasticsearch. Let's get started:
DESC forge_flat
You should have noticed that all TEXT columns have a brother column with the same name but having .keyword suffix. Please remember that keyword columns are used for filtering and normal text columns are used for searching. You can do LIKE %something% with a normal text column (and it's very fast), but you can't do it with a keyword column. On the other hand, you can do GROUP BY with a keyword column, but not with a normal text column. That's probably the only big difference between other SQLs and this one. To learn more about Elasticsearch SQL commands visit the official docs. Now let's see some examples:
SELECT COUNT(*) FROM forge_tx WHERE value > 100000000000000000000
SELECT AVG(gasPrice) FROM forge_tx WHERE timestamp > TODAY()
SELECT utxo_proposer_address.keyword, COUNT(*) C FROM forge_flat GROUP BY utxo_proposer_address.keyword ORDER BY C DESC LIMIT 10
SELECT COUNT(*) FROM forge_flat WHERE timestamp > TODAY()