Explore the Tableland Rigs metadata using SQL queries

Exploring the Rigs Tables

A deeper look into the Tableland Rigs, including the technical design behind the project’s metadata and how to query it.

By @Aaron Sutula

💡

Since this post was published, we’ve addressed some challenges in the design presented here. Be sure to read about the changes we made at the updated post: Rigs Data Storage Design Update.

The flexibility and power of web3 relational data and SQL provided by Tableland opens up many possibilities for all sorts of applications, especially NFTs. The Rigs project was the first NFT built on Tableland, so we had to make some decisions about how exactly to store Rigs data in Tableland and then query that data in the form of NFT metadata. The design turned out to be quite simple, but I want to describe it here so that others can dive deeper into the Rigs data, or learn from and use our approach for use in their own projects.

Composability Considerations

In the past, most NFT projects stored metadata in the form of JSON on IPFS or in their own backend server. It may be tempting to to translate this approach directly to Tableland by storing JSON strings within a single column of a Tableland table, but this isn’t the best approach.

(
	token_id integer primary key,
	metadata_json text not null
)

A basic table schema for storing NFT metadata as JSON strings. Not ideal.

Think long term about how your NFT project could be used in the future, either by you or by other developers. You may not even see the details now, but if you want to maximize the opportunities to interoperate with and extend your NFT in the future, considering data composability is key. Storing JSON strings in a single column of a Tableland table greatly restricts data composability because the details of the metadata are hard to query and, in database lingo, join with.

For that reason, it was important that Rigs data is stored in a very SQL-native manner, with each bit of information stored in its own column. This allows easy querying and filtering of data as well as joining the data to new tables for reasons we haven’t yet discovered.

Sparse vs. Dense Tables

The concept of sparse vs. dense tables comes from traditional relational data design.

Let’s say you have a heterogeneous dataset, meaning some properties of your objects are shared among all instances of those objects, but there are many properties that only exist on some objects, but not others.

In a sparse table design, you would store all that data in a single table with one column for each possible data property.

(
	token_id integer primary key,
	description text,
	prop_1 integer,
	prop_2 text,
	prop_3 integer
)

Schema for a table we’ll call sparse_table.

Since the data is heterogeneous, each entry in the table would only contain values in some of the columns leaving other columns empty, thus the description, a “sparse” table.

select * from sparse_table;
+----------+-------------+--------+--------+--------+
| token_id | description | prop_1 | prop_2 | prop_3 |
+----------+-------------+--------+--------+--------+
| 1        | one         | 5      |        |        |
| 2        | two         |        | hello  |        |
| 3        | three       |        |        | 10     |
+----------+-------------+--------+--------+--------+

Results of querying heterogeneous data from sparse_table.

To model the same data with densely populated tables requires using two tables: One for the properties that are shared among all entries, and another to hold the heterogeneous properties.

// Schema of table_1 for holding the common properties
(
	token_id integer,
	description text
)

// Schema of table_2 for holding the various properties
(
	table_1_token_id integer,
	prop text,
	value any
)

Schemas for two tables to hold our heterogeneous data.

The table_1_token_id column implies a one-to-many relationship between table_1 and table_2. In other words, there are one or more entries in table_2 associated with each entry in table_1.

This process of splitting data into many densely populated and related tables is known as data “normalization”. When selecting the same sample data as the sparse_table example, you can see the results:

select * from table_1;
+----------+-------------+
| token_id | description |
+----------+-------------+
| 1        | one         |
| 2        | two         |
| 3        | three       |
+----------+-------------+

select * from table_2;
+------------------+--------+-------+
| table_1_token_id |  prop  | value |
+------------------+--------+-------+
| 1                | prop_1 | 5     |
| 2                | prop_2 | hello |
| 3                | prop_3 | 10    |
+------------------+--------+-------+

Results of querying heterogeneous data from table_1 and table_2.

A great feature of this design is that we can specify any number or property names and the values can be of any supported type.

A single table design is a good choice when your data is homogenous or when there aren’t too many total columns. The Tableland Rigs data is extremely heterogeneous with many possible properties, so we went with a two table design similar to this example.

The Rigs Tables

The data for the Rigs NFT collection is stored in two Tableland tables, rigs_5_28 and rig_attributes_5_27. These tables follow the design of the dense tables pattern described above with rigs_5_28 holding the shared attributes and rig_attributes_5_27 holding the heterogeneous attribute data. Some extra not null and unique constrains were added to ensure data integrity.

// Schema of rigs_5_28
(
	id integer primary key,
	image text,
	image_alpha text,
	thumb text,
	thumb_alpha text,
	animation_url text
)

// Schema of rig_attributes_5_27
(
	rig_id integer not null,
	display_type text,
	trait_type text not null,
	value any not null,
	unique(rig_id, trait_type)
)

Schemas for the two tables holding the Rigs collection data.

You can see from the column names how we were thinking about NFT metadata generation here. For example, the various image-related columns in rigs_5_28 and the display_type, trait_type and values columns in rig_attributes_5_27 map directly to NFT metadata properties described by the Opensea metadata standards.

Query Results as NFT Metadata

How we populated the Rigs tables with the generative Rigs data will be left for a future blog post, but knowing that these tables now hold the Rigs data, the next question is: How do we create NFT metadata from them?

Tableland supports all of the JSON functions provided by SQLite. These functions actually make it quite easy to query Tableland and return NFT metadata-compliant JSON. Using two of these functions, json_object and json_group_array, combined with a normal SQL join clause to combine an individual Rig record with it’s various attribute records, we can write a query that returns NFT metadata JSON:

select json_object(
	'name', 'Rig #' || id,
	'external_url', 'https://tableland.xyz/rigs/' || id,
	'image', image,
	'attributes', json_group_array(
		json_object(
			'display_type', display_type,
			'trait_type', trait_type,
			'value', value
		)
	)
)
from rigs_5_28 join rig_attributes_5_27
	on rigs_5_28.id = rig_attributes_5_27.rig_id
where id = <rig id> 
group by id;

A query that returns Rigs NFT metadata.

The json_object function allows us to build an arbitrary JSON object using values returned from the database where needed, and json_group_array is an aggregate function to build a JSON array of values scoped by the group by id clause. Here, you can see the output of the above query for the Rig #1:

{
	"name": "Rig #1",
	"external_url": "https://tableland.xyz/rigs/1",
	"image": "ipfs://bafybeihvpfpgmkma6segsefd33hnyg66tecztxi2xcjsm2cn6nrkdrdhjy/image.png",
	"attributes": [{
		"display_type": "number",
		"trait_type": "% Original",
		"value": 20
	}, {
		"display_type": "string",
		"trait_type": "Background",
		"value": "Hue Shift High Desert 4"
	}, {
		"display_type": "string",
		"trait_type": "Cab",
		"value": "Midnight Low Vector"
	}, {
		"display_type": "string",
		"trait_type": "Chassis",
		"value": "Dawn Fast Tracking Tread"
	}, {
		"display_type": "string",
		"trait_type": "Fleet",
		"value": "Titans"
	}, {
		"display_type": "string",
		"trait_type": "Mainframe",
		"value": "Dawn Base58 Class"
	}, {
		"display_type": "string",
		"trait_type": "Mod",
		"value": "Dawn Foreign Data Wrapper"
	}, {
		"display_type": "string",
		"trait_type": "Utility Pack",
		"value": "Blaze UTOX"
	}, {
		"display_type": "string",
		"trait_type": "VIN",
		"value": "e9e7caceadc2e5fb4fcbd42c0a6ed0097c709aa01888f70e6d924439ec119ca3"
	}]
}

NFT metadata created by an SQL query to Tableland.

This exact query, with the addition of a few additional column selections omitted here for simplicity, is actually used in the Rigs ERC721 smart contract to implement tokenURI. This is the function that platforms like Opensea and others call to get NFT metadata. There are plenty of details to know about serving NFT metadata from smart contracts and Tableland, so be sure to check out our docs to learn more.

Exploring and Extending Rigs

The rigs_5_28 and rig_attributes_5_27 can be queried as shown above to create NFT metadata, but anyone is free to query and analyze that data in any way and for any reasons they want. Your imagination (and SQL) is the limit!

We’ve created two additional tables of Rigs-related data that could also be interesting to explore and extend from.

Layers

The layers_5_29 table contains mapping information for every Rig attribute value to the corresponding image layer used to render that attribute information in the Rigs image rendering process.

(
	id integer primary key,
	fleet text not null,
	rig_attributes_value text not null,
	position integer not null,
	cid text not null,
	unique(fleet,rig_attributes_value,position)
)

Schema of the layers_5_29 table.

To look up the layer information for a particular attribute from Rig #1, you would query layers_5_29 for the cid and position given a specific fleet (from the Fleet attribute), attribute value. Using the Tableland CLI, it would look something like:

~> tableland read "select cid, position from layers_5_29 where fleet = 'Titans' and rig_attributes_value = 'Midnight Low Vector'" --format tabular
┌─────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬──────────┐
│ (index) │                                                       cid                                                        │ position │
├─────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼──────────┤
│    0    │ 'ipfs://bafybeiepkpr4g4jzqyy6cjaegcxtmngyjqnf4vhekfrhadqappl5nuxvli/Titans/Cab/TheDelica_Midnight_LowVector.png' │    5     │
└─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────┘

The cid and position for a single attribute.

The cid is the cid of the layer image available on IPFS, and position is the z index that should be used when combined with other layers to ensure proper layer ordering.

Layer image for the Titans Midnight Low Vector part.

You could run a slightly more complex query that joins rig_attributes_5_27 to layers_5_29 to return all layer information for Rig #1:

~> tableland read "select cid, position from rig_attributes_5_27 join layers_5_29 on rig_attributes_5_27.value = layers_5_29.rig_attributes_value where fleet = 'Titans' and rig_id = 1 order by position" --format tabular
┌─────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬──────────┐
│ (index) │                                                                cid                                                                │ position │
├─────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼──────────┤
│    0    │         'ipfs://bafybeiepkpr4g4jzqyy6cjaegcxtmngyjqnf4vhekfrhadqappl5nuxvli/Titans/Background/_HueShift_HighDesert4.png'          │    0     │
│    1    │ 'ipfs://bafybeiepkpr4g4jzqyy6cjaegcxtmngyjqnf4vhekfrhadqappl5nuxvli/Titans/Chassis_Bottom/FluxBlaster_Dawn_FastTrackingTread.png' │    1     │
│    2    │         'ipfs://bafybeiepkpr4g4jzqyy6cjaegcxtmngyjqnf4vhekfrhadqappl5nuxvli/Titans/UtilityPack/CyberTrain_Blaze_UTOX.png'         │    3     │
│    3    │       'ipfs://bafybeiepkpr4g4jzqyy6cjaegcxtmngyjqnf4vhekfrhadqappl5nuxvli/Titans/Mainframe/SolarTank_Dawn_Base58Class.png'        │    4     │
│    4    │         'ipfs://bafybeiepkpr4g4jzqyy6cjaegcxtmngyjqnf4vhekfrhadqappl5nuxvli/Titans/Cab/TheDelica_Midnight_LowVector.png'          │    5     │
│    5    │      'ipfs://bafybeiepkpr4g4jzqyy6cjaegcxtmngyjqnf4vhekfrhadqappl5nuxvli/Titans/Mod/SentryBuggy_Dawn_ForeignDataWrapper.png'      │    7     │
└─────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────┘

All layers for Rig #1 ordered by position.

If you stack all those images on top of each other, the result is the Rig #1 image.

What else could you do with this data?

Parts

The parts_5_30 table contains all the “parts” that make up all Rigs NFTs. Think of this as the raw material that the Rigs generative algorithm ran against in order to build every Rig in existence.

(
	id integer primary key,
	fleet text,
	original text,
	type text not null,
	name text not null,
	color text
)

Schema for the parts_5_30 table.

You can explore the data to get a better feel for how it works, and we plan on open sourcing the Rigs repo very soon so you can see the code that builds the Rigs from this data. In the meantime, here’s a little detail on what each column represents:

id - Simply a part id and has no other meaning
fleet - The Fleet associated with the part, this is null for part type of Fleet
original - The name of the original Rig that the part came from, this will be null for part type of Fleet and Background
type - The type of the part, can be Fleet or Background or one of the countless other part types from each original Rig
name - Name of the part
color - Color of the part, will be null for part type of Fleet

Wrapping Up

There’s a lot here, from more traditional ideas around relational data design, to brand new ideas about how to store and generate NFT metadata on a first-of-its-kind platform, Tableland. You should now have some feel for the reasoning, design and implementation of the Tableland Rigs NFT, and hopefully some ideas about how you might explore and extend it, or incorporate these ideas into your own project built on Tableland. Please join our Discord and chime in there with any questions or ideas you have.

← Back

Exploring the Rigs Tables
Composability Considerations
Sparse vs. Dense Tables
The Rigs Tables
Query Results as NFT Metadata
Exploring and Extending Rigs
Layers
Parts
Wrapping Up