Nodely extended schema

v_asset - decoded address asset view

CREATE OR REPLACE VIEW v_asset AS
SELECT
  index as asset_id
  ,creator_addr
  ,AddressBin2Txt(creator_addr) creator
  ,deleted
  ,created_at
  ,closed_at
  ,AddressBin2Txt(decode(params ->> 'c', 'base64')) clawback
  ,AddressBin2Txt(decode(params ->> 'f', 'base64')) freeze
  ,AddressBin2Txt(decode(params ->> 'm', 'base64')) manager
  ,AddressBin2Txt(decode(params ->> 'r', 'base64')) reserve
  ,CAST(params ->> 't' as NUMERIC(20,0)) total
  ,params ->> 'dc' as decimals
  ,params ->> 'am' as metadata
  ,params ->> 'au' as url
  ,params ->> 'an' as name
  ,params ->> 'un' as unit
  ,params ->> 'df' as frozen
FROM
  asset

DROP TABLE IF EXISTS "x_asset_meta";
CREATE TABLE "x_asset_meta" (
   asset  bigint primary key not NULL,
   meta jsonb,
   updated_at timestamp
);

create or replace view xv_asset_refresh as (
select index as asset, params#>>'{au}' as au, AddressBin2Txt(decode(params#>>'{r}','base64')) as r, params#>>'{an}' as name, params#>>'{un}' as un
from asset where index > 0 and (params#>>'{au}' like '%ipfs%')
and index not in (select asset from x_asset_meta where cached_at is not null)
);

create or replace view xv_asset_cids as
with cids as (select xam.asset asset,a.created_at,jsonb_path_query(meta#>'{extra}','$.*') as c from x_asset_meta xam join asset a on xam.asset = a."index")
select
    asset
    ,created_at
  ,c#>>'{cid}' cid
  ,(c#>>'{bytes}')::bigint bytes
  ,c#>>'{mimetype}' mt
from cids;

create materialized view xv_asset_stats as
select
    realtime::date as ts
  ,sum((meta#>>'{extra,vid,bytes}')::bigint)/1024/1024/1024 vid_gb
  ,sum((meta#>>'{extra,img,bytes}')::bigint)/1024/1024/1024 img_gb
  ,sum((meta#>>'{extra,aud,bytes}')::bigint)/1024/1024/1024 aud_gb
  ,sum((meta#>>'{extra,json,bytes}')::bigint)/1024/1024/1024 json_gb
  ,sum(abs((meta#>>'{extra,oth,bytes}')::bigint))/1024/1024/1024 oth_gb
  ,count(meta#>>'{extra,vid,bytes}') vid
  ,count(meta#>>'{extra,img,bytes}') img
  ,count(meta#>>'{extra,aud,bytes}') aud
  ,count(meta#>>'{extra,json,bytes}') json
  ,count(meta#>>'{extra,oth,bytes}') oth
from
  x_asset_meta xam
  join asset a on xam.asset = a."index"
  join block_header bh on a.created_at = bh.round
group by 1;

create index xv_as_ts on xv_asset_stats(ts);

SELECT cron.schedule('ipfs-asa-stats-hourly','1 * * * *', $$refresh materialized view xv_asset_stats$$);

create materialized view xv_asset_mt_stats as (
with cidrn as (
select *, row_number() over(partition by cid) rn from xv_asset_cids xac
) select mt as mediatype,count(*) count,sum(bytes)/1024/1024/1024 gb from cidrn where rn = 1
group by mt having count(*) > 20 order by 2 desc
)

SELECT cron.schedule('ipfs-asa-mt-stats-hourly','5 * * * *', $$refresh materialized view xv_asset_mt_stats$$);