Design and Data Migration using lookup tables

Purpose

The purpose of this document is to describe the table design and table migration strategies which may be of benefit to FIO getters that are limited in the number of records they can hold. we focus on the use of in state lookup tables

Approaches

1. Use table scope to implement groupings of table rows that can be queried without issues.

2. Create off chain centralized services to provide read access for the row limited information.

3. Create on chain lookup tables which can be used to enrich the table lookup to include more detailed search criteria.

We will focus on item 3 here

Overview of approach

provide a system level implementation of “system paging” for indexes suffering issues

We will create a lookup table which will have an internal page number for each of these multitude of records in the problem table. in order to migrate to using this, we must populate the lookup table (so there will be one record in the lookup table for each of the multitude of records each record with a page number which groups the records into a set of manageable records per page). The page size must be limited to some reasonable number of records (perhaps as few as 300) within the multitude of records.

To read data using the lookup table, We will first determine the pages of interest using the offset and limit passed in, we will then query the lookup table for items in each page. We will get a page of records at a time, then we will get these records from the problematic table by their primary index and return them in the results. This enables the use of offset and limit without experiencing the issues we have with our present implementation of paging in the FIO getters. the result is that limit and offset should ALWAYS work as expected, no matter the number of records associated with a given index.

one lookup table can be made per problematic table, or one table can be made to be used FIO wide for all tables.

it is recommended that we prototype this to gain confidence that the proposed solution will work robustly for any number of records contained in a secondary index.

Detailed example

We will integrate the lookup table for the desired tables everywhere a record is inserted or removed.

    the following pattern may only be applied when the table in question has a primary index id (a one up for each row in the table).

     we will define a FIO read block size as a constant in the contracts.

this will be tunable for the protocol in state (IE we can increase this read block size when necessary, but we cannot decrease it!)

we will define a FIO state table holding the current block number for each integrated index

table name readblocks

description – this table holds the latest block number in use for a given table and index, and holds the number of rows that are in the present block. the table is used when we do inserts into tables which are integrated with the lookup table.

columns

id

tablename – name of the state table

index name – index name being added to lookup table.

lookup hash - table name plus index name hashed.

current block – current block number being used

rowsinblock – number of rows in the present block

primary key

id

secondary

lookup hash

we will read this table when we want to add rows into a table, we will lookup the block number and remaining records that can go in a block, insert desired rows into the table, update the lookup table, and finally update the current block and rows in block.

     we will make a lookup table

name rdblklookup

description --

this table holds table info by index grouped by a read block (so we have a fixed sized grouping of items being indexed for paging in FIO for any index)

               id -- id of row

             index name -- the string rep of the index value “nftsbyfioaddress”

             index name hash

             table name -- state table to use “nfts”

             table name hash

             read block -- the read block for this entry

             read block hash -- (table name +index name + read block) hashed

             tableid -- the primary index of the table in table name for the desired row.

       indexes are

         primary

            id

         secondary

            index name hash

            table name hash

            read block hash

        we will add a new action for each new table added to the lookup table to perform inserts.

block insert nft(with necessary parameters)

          this checks the readblocks table, performs inserts, updates the lookup table as necessary, then updates the read blocks table to reflect the resulting number of rows and current block number.

      once we have this lookup table populated, getters may now use paging as follows

        read the fio read block size from state,

        compute the start block of the offset for the table roundup(offset/read block size)

        compute the end block of the limit specified roundup(limit/read block offset)

      the getter can enforce the "sensible"use of limit and offset, throw an error when it is not sensible

      (IE the getter must return less than 1500 records)

     now to integrate the lookup table completely we must modify the logic whenever a row is inserted into the table in question to use the block insert nft.

once integrated getters will NEVER have an opportunity to return incorrect info, or fail to work when the number of records becomes larger, just use the paging on the client.

things to think about --

support for removal of records --

we need a block remove nft action as well.

if we remove records it becomes possible some blocks may become empty, this is fine, we just need to be sure we dont fail when no records found for a block less than the current block for a given table and index.