First P2E game on Tableland (1)

We say that blockchain is a distributed ledger as we think of it as a decentralized database.
And there are many decentralized apps on top of it.

And we all know that it is not always easy to query data from the blockchain and that's where many database layer services like The Graph come into play.

But there are still inefficiencies in terms of performance and latency.
And that's why we sometimes in the need of own indexer for nft collections.

Today, I'd like to introudce Tableland Network, a decentralized web3 protocol for structured relational data, starting with Ethereum (EVM) and EVM-compatible L2s.
https://docs.tableland.xyz/what-is-tableland

The first time, I checked their doc, I was so excited about this one since it will just make developers' lives way easier.

Simply they have their own relational database and then mint NFTs representing privileges to the tables.
And again its accessible from both on-chain and off-chain.

You can check the first nft collection storing metadata on tableland tables.
https://opensea.io/collection/tableland-rigs

Of all the advantages Tableland will bring, I think the fact that it lets us build dynamic metadata would be the one we have to consider first.

Okay, I know that you can walk through all the technical details and some approaches yourself from the doc above.
So I will remain lots of things on you and skip to the P2E game I built on top of the tableland, pretty assume that this will attract you since it's the very first approach yet.

What I was trying to build for KittyKart
(https://kittyinu.com/

https://twitter.com/KittyKartRacing?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Eauthor

https://playtoearn.net/blockchaingame/kittykart)

1. NFT collections (Kart, Assets) There are 2 NFT collections, Kart and Asset. Kart is the automotive in the game which you will use to race others, and assets are the part of Kart. You can replace any assets you want in your kart for the better performance of the kart. FYI, assets can hold several traits, for example, you can think of an asset

{
    "engine": "v8",
    "color": "violet"
}

1. Autobody shop It's the place where users can apply various assets to the Kart, Technically once one asset is applied to the kart, the metadata of the kart will be updated accordingly.

First and foremost, we have to design the tables for nft collections, and create tables on smart contracts. (FYI, table creator will have admin privilege of that table, in our case smart contracts)

Here are the code piece for creating tables.

/**
   * @dev create table in TableLand
   * @param _registry The registry address
   */
  function createMetadataTable(address _registry) external payable onlyOwner returns (uint256) {
    /*
     * registry if the address of the Tableland registry. You can always find those
     * here https://github.com/tablelandnetwork/evm-tableland#currently-supported-chains
     */
    tableland = ITablelandTables(_registry);

    metadataTableId = tableland.createTable(
      address(this),
      /*
       *  CREATE TABLE prefix_chainId (
       *    int id,
       *    string name,
       *    string description,
       *    string image,
       *    string background_color,
       *    string external_url,
       *    string animation_url,
       *    string owner,
       *  );
       */
      string.concat(
        "CREATE TABLE ",
        tablePrefix,
        "_",
        StringsUpgradeable.toString(block.chainid),
        " (id int, name text, description text, image text, background_color text, external_url text, animation_url text, owner text);"
      )
    );

    metadataTable = string.concat(
      tablePrefix,
      "_",
      StringsUpgradeable.toString(block.chainid),
      "_",
      StringsUpgradeable.toString(metadataTableId)
    );

    emit CreateMetadataTable(metadataTable, metadataTableId);

    return metadataTableId;
  }

Now it's time to build tokenURI for public access to metadata. (again tableland tables are accessible by public off-chain)

/**
   * @dev tokenURI is an example of how to turn a row in your table back into
   * erc721 compliant metadata JSON. Here, we do a simple SELECT statement
   * with function that converts the result into json.
   */
  function tokenURI(uint256 _tokenId) public view virtual override returns (string memory) {
    require(_exists(_tokenId), "KittyKartGoKart: URI query for nonexistent token");
    string memory base = _baseURI();

    return
      string.concat(
        base,
        "SELECT%20json_object(%27id%27,id,%27name%27,name,%27description%27,description",
        ",%27image%27,image,%27background_color%27,background_color,%27external_url%27,external_url,%27animation_url%27,animation_url",
        ",%27attributes%27,json_group_array(json_object(%27display_type%27,display_type",
        ",%27trait_type%27,trait_type,%27value%27,value)))",
        "%20FROM%20",
        metadataTable,
        "%20LEFT%20JOIN%20",
        assetAttributeTable,
        "%20ON%20(",
        metadataTable,
        ".id=",
        assetAttributeTable,
        ".kart_id%20AND%20",
        assetAttributeTable,
        ".in_use=1)",
        "%20WHERE%20id=",
        StringsUpgradeable.toString(_tokenId),
        "%20GROUP%20BY%20id",
        "&mode=list"
      );
  }

This is the basic example of token URI.

https://testnet.tableland.network/query?s=SELECT%20json_object(%27id%27,id,%27name%27,name,%27description%27,description,%27image%27,image,%27external_url%27,external_url,%27animation_url%27,animation_url,%27attributes%27,json_group_array(json_object(%27display_type%27,display_type,%27trait_type%27,trait_type,%27value%27,value)))%20FROM%20kitty_kart_test_5_700%20LEFT%20JOIN%20kitty_asset_test_attribute_5_702%20ON%20(kitty_kart_test_5_700.id=kitty_asset_test_attribute_5_702.kart_id%20AND%20kitty_asset_test_attribute_5_702.in_use=1)%20WHERE%20id=1%20GROUP%20BY%20id&mode=list

You will ask how to mint NFT and insert metadata into the tableland tables.
No worries, I have this to show you.

  /**
   * @dev Its free mint for test
   * @param _quantity The quantity value to mint
   */
  function publicMint(uint256 _quantity) external nonContract nonReentrant {
    uint256 tokenId = _nextTokenId();
    for (uint256 i = 0; i < _quantity; i++) {
      tableland.runSQL(
        address(this),
        metadataTableId,
        string.concat(
          "INSERT INTO ",
          metadataTable,
          " (id, name, description, image, external_url, animation_url) VALUES (",
          StringsUpgradeable.toString(tokenId + i),
          ", '#",
          StringsUpgradeable.toString(tokenId + i),
          "', '",
          description,
          "', '",
          defaultImage,
          "', '",
          externalURL,
          "', '",
          defaultAnimationURL,
          "');"
        )
      );
    }
    _mint(msg.sender, _quantity);

    emit Mint(msg.sender, _quantity);
  }

Now you will be clear about how to play with tableland tables, and yes it is almost the same as normal interaction with relational databases with SQL queries.
One exception here is tableland can't accept sub queries, which is a barrier of tabeland adaption in cases, but this shouldn't be a blocker.

time to move on to autobody shop?

You will have a basic idea of it, since you are very familiar with database manipulations,
and you are right, it is all about updating existing kart metadata on the tableland.

but how?

Am going to leave you alone to think about this. (heading to Khalifa International Stadium for Canada vs Croatia match)

See you next time.