Data Extraction

Loopring v3 is a layer 2 solution using zkRollup. As such extracting trade or other information from Ethereum is harder than layer 1 solutions where most data is directly accessible on-chain, likely via easy to parse events. But even though we are on layer 2, all data is still available on Ethereum.

There are couple of ways volume can be tracked. How you want to access the data depends on who you trust and how the Ethereum data is stored.

1. Relayer API

We provide an API to extract data directly from our exchange, no need to parse any Ethereum data. This is likely the easiest way to access our exchange data, but it means you'll have to trust us to provide correct data.

TODO: Add API

2. SQL Database

If the Ethereum data is parsed and stored into a database, you can easily extract per trade information from Ethereum. As an example you can visit our DuneAnalytics dashboard. By creating an account you can also view the source of each query to learn how this is done.

Here's an example that shows how to run over blocks on an exchange and list all trades that ever happened on the exchange. If you're not using Dune the data is likely stored slightly differently, but the main process functions which decodes all trade information should be usable as is.

DROP FUNCTION decodeFloat;
DROP FUNCTION process;
DROP TYPE trade_struct;

CREATE TYPE trade_struct AS (
    block_timestamp timestamptz,
    tokenA integer,
    fillA double precision,
    tokenB integer,
    fillB double precision,
    accountA integer,
    accountB integer, 
    protocolFeeTakerBips double precision,
    protocolFeeMakerBips double precision
);

CREATE OR REPLACE FUNCTION decodeFloat(data bytea) RETURNS double precision AS $$
DECLARE
    exponent integer;
    mantissa integer;
    value integer;
BEGIN
    value = get_byte(data, 0) * 65536 + get_byte(data, 1) * 256 + get_byte(data, 2);
    exponent = value / 524288;
    mantissa = value - (exponent * 524288);
    return mantissa * POW(10, exponent);
END; $$
LANGUAGE PLPGSQL;


CREATE OR REPLACE FUNCTION process(blockSize integer, data bytea, block_timestamp timestamptz) RETURNS trade_struct[] AS $$
DECLARE
    i integer;
    offsetTokens integer;
    offsetFills integer;
    offsetAccounts integer;
    protocolFeeTakerBips double precision;
    protocolFeeMakerBips double precision;
    tokenA integer;
    symbolA varchar;
    tokenB integer;
    fillA bytea;
    fillB bytea;
    accounts_bytes bytea;
    accountA integer;
    accountB integer;
    trade trade_struct;
    trades trade_struct[];
BEGIN
    offsetTokens = 111 + blockSize * 10;
    offsetFills = 111 + blockSize * 12;
    offsetAccounts = 111 + blockSize * 5;
    
    protocolFeeTakerBips = get_byte(data, 73);
    protocolFeeMakerBips = get_byte(data, 74);
    protocolFeeTakerBips = protocolFeeTakerBips / 100000;
    protocolFeeMakerBips = protocolFeeMakerBips / 100000;
    
    FOR i IN 1 .. blockSize
    LOOP
        fillA = substr(data, offsetFills, 3);
        fillB = substr(data, offsetFills + 3, 3);
        
        tokenA = get_byte(substr(data, offsetTokens, 1), 0);
        tokenB = get_byte(substr(data, offsetTokens + 1, 1), 0);
        
        accounts_bytes = substr(data, offsetAccounts, 5);
        accountA = (get_byte(accounts_bytes, 0) * 65536 + get_byte(accounts_bytes, 1) * 256 + get_byte(accounts_bytes, 2)) / 16;
        accountB = (get_byte(accounts_bytes, 2) * 65536 + get_byte(accounts_bytes, 3) * 256 + get_byte(accounts_bytes, 4)) & (1048576 - 1);
        
        SELECT block_timestamp, 
               tokenA,
               decodeFloat(fillA) as fillA,
               tokenB,
               decodeFloat(fillB) as fillB,
               accountA,
               accountB,
               protocolFeeTakerBips,
               protocolFeeMakerBips
        INTO trade;
        trades = array_append(trades, trade);
        
        offsetFills = offsetFills + 6;
        offsetTokens = offsetTokens + 2;
        offsetAccounts = offsetAccounts + 5;
    END LOOP;
    
    RETURN trades;
END; $$
LANGUAGE PLPGSQL;

WITH trades AS (
    SELECT unnest(process(CAST(b."blockSize" AS INT), b._3, b.call_block_time)) as trade
    FROM loopring."DEXBetaV1_call_commitBlock" b
    WHERE b."blockType" = '0'
), token_table AS (
    SELECT 0 AS "tokenId", '\x0000000000000000000000000000000000000000' AS token, 'ETH' AS "symbol", 
          18 AS decimals, (SELECT price FROM prices.usd WHERE symbol = 'ETH' ORDER BY minute desc LIMIT 1)  as price
    UNION
    SELECT "tokenId", "token", "symbol", "decimals", 
        CASE
            WHEN t."symbol" = 'DAI' THEN '1.0'
            WHEN t."symbol" = 'LRC' THEN '0.0283'
            ELSE (SELECT price FROM prices.usd WHERE symbol = t."symbol" ORDER BY minute desc LIMIT 1)
        END as price
    FROM loopring."DEXBetaV1_evt_TokenRegistered" e
    LEFT JOIN erc20.tokens t ON t.contract_address = e."token"
    WHERE token != '\x0000000000000000000000000000000000000000'
    ORDER BY "tokenId"
)

SELECT (t.trade).block_timestamp as timestamp, 
       (t.trade).accountA as accountA,
       (t.trade).fillA/POW(10, (SELECT "decimals" FROM token_table WHERE "tokenId" = (t.trade).tokenA)) as fillA,
       (SELECT "symbol" FROM token_table WHERE "tokenId" = (t.trade).tokenA) as tokenA,
       '⇄' as arrow,
       (t.trade).accountB as accountB,
       (t.trade).fillB/POW(10, (SELECT "decimals" FROM token_table WHERE "tokenId" = (t.trade).tokenB)) as fillB,
       (SELECT "symbol" FROM token_table WHERE "tokenId" = (t.trade).tokenB) as tokenB
FROM trades t
ORDER BY (t.trade).block_timestamp DESC;

3. Web3 provider

If you have access to Ethereum data via a web3 provider you can use our JS data extraction library. This library allows you to collect all exchange states of all Loopring exchanges by running over all Ethereum transactions which impact the exchanges. Afterwards all data is available in an easy to use JS API: All accounts and balances, all processed transactions,...