Timed Contract Function Calls

Computing the current state of a contract based on historical transactions can become incredibly complicated. For example when calculating:

• Total supply of a coin
• Number of NFTs minted
• The history of any value within a contract
• Values over time to create charts or UIs

To simplify obtaining this data, Sort executes read-only contract methods periodically, storing results in the contract_method_execution and contract_method_execution_result tables. Let's take a look at an example.

To find a single contract method execution, run the following query (feel free to open this page and paste in the query).

SELECT * 
FROM ethereum.contract_method_execution cme 
WHERE cme.contract_address = '0x00000000006c3852cbef3e08e8df289169ede581'
ORDER BY cme.timestamp
LIMIT 1

Note the result column which is a JSONB data type. Sometimes JSONB meets your needs but you may prefer to extract a specific field out of the JSONB result for use in your query. To do this quickly, add a join of the contract_method_execution_result table.

SELECT cmer.path, cmer.bool_val, cmer.num_val, cmer.bin_val, cmer.str_val 
FROM ethereum.contract_method_execution cme 
  JOIN ethereum.contract_method_execution_result cmer ON cmer.contract_method_execution_id = cme.id
WHERE cme.contract_address = '0x00000000006c3852cbef3e08e8df289169ede581'
AND cme.id = '0c60610a-fb5d-4b67-b285-bc93b5f94162' -- the id from the previous query result

Observe a unique contract_method_execution_result row exists for each JSONPath of the original contract_method_execution.result field. The type of extracted value determines in which column the value is stored.

📘

Note: The zero indexed JSONB properties (0, 1, 2) are excluded as redundant.

So let's rewrite our query to extract the version changes over time.

SELECT cme.timestamp, cmer.str_val
FROM ethereum.contract_method_execution cme 
JOIN ethereum.contract_method_execution_result cmer ON cmer.contract_method_execution_id = cme.id
WHERE cme.contract_address = '0x00000000006c3852cbef3e08e8df289169ede581'
AND cmer.path = 'version'
ORDER BY cme.timestamp DESC
LIMIT 100