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. However, even though we are on layer 2, all data is still available on Ethereum.

There are couple of ways Loopring exchange data can be accessed. 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 get the data directly from our servers, no need to parse any Ethereum data. This is likely the easiest way to access the exchange data, but it means you'll have to trust exchange to provide correct data.

If you want access to this API, please contact us.

2. SQL Database

If the Ethereum data is parsed and stored into a database, you can easily extract most data 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 achieved.

Here's some example code that shows how to run over all blocks for an exchange and list all trades that ever happened. If you're not using Dune the data is likely stored a bit 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 API.

// Initialize the explorer with a web3 provider
const explorer = new Explorer();
await explorer.initialize(web3, universalRegistry.address);

// Fetch all data from the web3 provider
await explorer.sync(await web3.eth.getBlockNumber());

// Get a specific exchange
const exchange = this.explorer.getExchangeById(exchangeId);

// Get account state (owner, balances, ...)
const numAccounts = exchange.getNumAccounts();
const account = exchange.getAccount(accountID);

// Blocks
const numBlocks = exchange.getNumBlocks();
const block = exchange.getBlock(blockIdx);

// Processed requests/transactions
const numRequests = exchange.getNumProcessedRequests();
const requestsInRange = getProcessedRequests(startIdx, count);

// ... and much more