Useful `graph-node` views

views.sql

--
-- subgraphs schema
--
drop schema if exists meta cascade;
create schema meta;

create view meta.subgraphs as
select id,
       data->'name'->>'data' as name,
       (data->'createdAt'->>'data')::int as created_at,
       data->'currentVersion'->>'data' as current_version,
       data->'pendingVersion'->>'data' as pending_version
 from entities
where subgraph = 'subgraphs'
  and entity='Subgraph';

create view meta.manifests as
select id,
	   data->'schema'->>'data' as schema
 from entities
where subgraph = 'subgraphs'
  and entity='SubgraphManifest';

create view meta.assignments
as select id,
          (data->'cost'->>'data')::int as cost,
          data->'nodeId' ->> 'data' as node_id
from entities
where subgraph = 'subgraphs'
  and entity = 'SubgraphDeploymentAssignment';

create view meta.deployments as
select id,
 (data->'failed'->>'data')::bool as failed,
 (data->'synced'->>'data')::bool as synced,
 data->'manifest'->>'data' as manifest,
 data->'latestEthereumBlockHash'->>'data' as latest_ethereum_block_hash,
 (data->'totalEthereumBlocksCount' ->> 'data')::int
    as total_ethereum_blocks_count,
 (data->'latestEthereumBlockNumber' ->> 'data')::int
    as latest_ethereum_block_number
from entities
where subgraph = 'subgraphs' and entity = 'SubgraphDeployment';

create view meta.versions as
select id,
	   data->'subgraph'->>'data' as subgraph,
	   (data->'createdAt'->>'data')::int as created_at,
	   data->'deployment'->>'data' as deployment
 from entities
where subgraph = 'subgraphs'
  and entity='SubgraphVersion';

create view meta.eth_contract_abi as
select id,
       data->'file'->>'data' as file,
       data->'name'->>'data' as name
 from entities
where subgraph = 'subgraphs'
  and entity='EthereumContractAbi';

create view meta.eth_contract_data_source as
select id,
       data->'kind'->>'data' as kind,
       data->'name'->>'data' as name,
       data->'network'->>'data' as network,
       data->'source'->>'data' as source,
       data->'mapping'->>'data' as mapping,
       data
 from entities
where subgraph = 'subgraphs'
  and entity='EthereumContractDataSource';

create view meta.eth_contract_event_handler as
select id,
       data->'event'->>'data' as event,
       data->'handler'->>'data' as handler
 from entities
where subgraph = 'subgraphs'
  and entity='EthereumContractEventHandler';