Tableland 🛠 Creating & Writing to Tableland TABLEs in Remix - Dan Buchholz

[Applause] [Music] hi everyone today we're going to walk through an overview of the tableland protocol and then dive into remix and show you how to create a table and write to it all from a smart contract so first off what is table land tableland is a decentralized database network that makes it easy for people to create and write to tables in a web3 native capacity so you can picture table end as sort of this off chain network of validators that are listening to on-chain events which emit sql instructions for what these validators should do so what this allows you to actually implement is cheap ways to store information and smart contracts in this op chain network and then read from that off chain network in things like applications or compose it across nfts or really anything so tableland really opens the door for new experiences to happen in a web 3 native composable way and what this really allows you to do is turn any blockchain into an application database so anything from dynamic nfts to [Music] game state to application data all this can actually be stored in table and tables and then you can use things like different access controls with smart contracts to ensure maybe only a specific address with a particular balance that owns this specific nft can write to a table so there's a lot of creative ways that you can then use table land in general and we do have a number of ways in which you can interact with the network so as noted there's really the foundation of everything comes from a validator network running sqlite and they're the ones that are making mutations to tables creating tables and sharing state across them and all of those instructions are actually coming from smart contracts that are deployed on chains like ethereum polygon optimism and arbitrom so as those events from smart contracts and in particular what we call the registry table and tables smart contract as those events are emitted they basically tell these validators what to do and smart contracts can interact with this registry smart contract or there are other clients that we've built on top of this including our javascript and typescript sdk and a cli tool as well now once a table is created what's actually happening is that it's minted as an erc721 token so the table token exists on all these different networks and we actually have a rendering of what the if you go on any marketplace like testnets.openc that io or just really open c period for the main net deployments you'll see something similar like this if you search table and tables so what this is telling us is that hey there is a table out there owned by a particular eoa or contract where there is a table name that is the one at the top and you can specify any table prefix that you want to make it human readable and then there are some additional numbers that are appended to the end of a prefix that make a table globally unique every table can exist in this globally unique setting and specific to each chain and within this svg metadata rendering there is some information just about the table itself but really when you create a table it includes setting the schema and then inserting data into it and then once you do this you'll notice that rows populate such as the number 1473 represented there is just hey this table has a little over 1400 rows included and the data itself is about a holder which is of type text and a quantity which is a type integer and interestingly this is related to our table and riggs nft so if you're interested in that search table and rigs over on any nft marketplace now before we get started with remix just in case you are interested in some of the other tools here are those resources and then lastly we are opening up a pilot program here shortly and our pilot program is basically a way to allow builders to have some funding to have some high touch experience with the tableland team and build a product build some sort of it could be a hackathon oriented type of ordeal or for someone that actually has a well-baked idea could be a dao it could be like a product that already exists and you're looking for a web3 native database essentially you can insert sort of pun intended tableline into that equation and we'll walk you through that throughout the pilot program so if you're interested feel free to apply the applications are now open and it's a seven week program cool so we're going to start by diving into remix and if you're not familiar with remix it's a great tool for just getting started with contract smart contract deployments and interacting with different chains and what we're going to walk through today is just the very basics of tableland if you haven't already check out our documentation there's a lot of information here on what you can do with table end what we're showing here is our deployed contracts page and this will just make it easier in a moment here once we do some initial setup now if you are looking for a little bit more in-depth information on how to really just build a smart contract from zero to one check out this build a dynamic nft in solidity tutorial on our documentation page so great let's go back to remix and create a smart contract so we're going to first start off by specifying the actual smart contract that we're going to create so let's just call it evm create table that's all and i have some pre-created code that i'll walk you through here now but it's pretty straightforward to actually get started with table end and create and write to tables so up at the top just some basic config information around the open source licensing and pragma solidity and here we've imported a few different contracts so the first one is pretty interesting get to this in a second but erc 721 holder next we have the tableland tables interface and then third we have a strings library and really the the top and bottom one of these imports are important for just interacting with table line and making some of the some of those interactions more straightforward cool so we have our contract and up at the top we've specified some state variables that we're going to be saving so the first one is when we actually deploy this contract we're going to set the address of the tableland tables registry smart contract and this will make it easy when we're actually interacting with that contract itself for creating and inserting data into the tables next we have some information that is specific to tableland so every table comes in sort of this i'll scroll down here but in a format like this prefix chain id table id so here we're saving each of those components as individual state variables not necessarily required to save the prefix itself but i thought it'd be helpful to do it here just based on the way i've designed the contract but note that it's pretty common pattern to at least save the table name and table id sometimes prefix because it's it just really depends on your implementation and how efficient you are in trying to get this contract and then the last piece is just a counter when we show our write statement down at the bottom it's going to leverage this calendar just just increment the value little by little so when someone inserts it into the table it's just using the the updated value next we have a constructor so here we're setting that table prefix we are setting the actual tableland tables registry smart contract address and then we're specifying the counter as zero and there are definitely better ways you can handle this around counters and everything this isn't the most perfect implementation but it's just really the basics to get started and show you how to do things with table and so the next step we're going to do is create this create metadata table and i actually will just rename this as table because it's not necessarily metadata although table line is great as a metadata solution for things like nfts because we can make metadata natively dynamic with on-chain rules and interactions but first what we're going to do is since in our constructor we specified that tableland registry contract we're going to create a table and at the table end registry smart contract and in fact it might be helpful to even look this up in real time but if we look at tableland tables what does this really do so if we scroll down a bit you'll notice two functions that are pretty important to this tutorial in general one is create table so here we're going to specify who the table owner is and what that create table statement is and what this will do is as you see here safe mint it's going to be minting a table token to whoever mints this table and then from there we will be writing to the table itself using this run sql function so taking that caller so that owner of the table typically the table id and then the statement so if you come from a world of sql this is going to be our create table type of statement and then this is a statement like insert into table values and this is really cool because this is all happening on chain and there are other ways that you can get pretty creative with this you'll notice at the bottom of this run sql function there's this get policy and what this does is actually if you want to have more complex access controls like checking ownership of a token or checking balance of a wallet saying hey this person can only insert my t into my table if they have a balance above you know 0.01 each sort of spam production things like that you can leverage the get policy to make sure that only the right people are updating or inserting values into a table and we won't be walking through that today but if you are interested in more of the complex step of acl configurations that's where our table line controller contract comes into play where you can do things like inserts updates deletes where clauses etc and this is where you can get into the the creative nature of the acls and there are some contracts that show you exactly how these type of interactions could be established so check those out if you are interested in them cool so back to remix we have our create table we have the actual statement down here and what it looks like is basically this so create table and when we use the create table type of setup we're going to leverage a prefix underscore chain id to initially create the table and then once it's actually minted this value will have token id appended and this is going to be generated by the table end tables registry smart contract but at the current state you don't know what that token id is so you have to let the registry smart contract essentially tell you what that is upon minting the table so create table prefix chain id and then here we have our schema so this we're saying the first value is the column is going to be called id it's of type int for integer and it's a primary key next we have a column called message and it is of type text awesome now in this next step we have string concat and this basically builds that actual create table statement like it is up here but does it in a solidity way so create table space table prefix underscore strings upgradeable to string block chain id you'll notice in a development environment where we haven't necessarily specified that this is going to be on let's say polygon mumbai or ethereum girlie or even if you're a mainnet if you want to deploy a table there it's going to throw this air don't worry about it doesn't really mean anything for what we're doing at the current state so create table prefix underscore strings tostring and this converts this number into a string and then after that space id and primary key message text boom so that is our create table statement from there we are also going to make sure that we we have the table name saved as a state variable so it takes this table id and then it basically formulates that affirmation prefix chain id table id type of setup so our table name is our prefix underscore strings we're going to also convert that chain id so in this tutorial we're going to deploy on polygon mumbai so you'll notice that this will end up being the polygon mumbai chain id 8000 8001 and then underscore and then we're also going to convert this un256 table id to a string so table and name will also be a string great so let's just review real quick what have we done we in theory we've deployed a contract when we do so we specify these constructor values that basically take care of case this actually in hand with this create table function will take care of everything up here so now we have created a table it'll have been minted by this contract itself and one thing to note before we go into writing to the table is this erc721 holder aspect so in order for a contract to create a table it has to be able to receive an erc in general so you have to make sure they implement this aspect erc 721 holder does this so it has this function that it's implementing called the on erc 721 received and this is really essential a piece to implement in all your contracts if you are interacting directly with the registry smart contract alternatively if you want to do it yourself there are there is just a contract called erc721 receiver and you can do this yourself but this just kind of handles it for you great next up let's write to a table we're going to call tableland run sql that function that we looked at earlier the caller is the address this so the address of the smart contract and again since this smart contract can receive tokens this is really what enables everything to be possible we have the table id and then we have the insert statement itself and the insert statement is going to look like this up at the top so insert into we have that format prefix chain id table id we have the columns that we're going to insert into and then we have the values that we're going to insert into the table so the the values we're going to have this sort of counter that's going to be occurring and after inserting we're going to increment increment the counter itself so it sort of just you don't have to do it in this capacity you could actually just you know if you want to insert any value you don't have to use a counter here but i'm just doing it for simplicity's sake so what we're saying is that insert into this table name we're going to insert the id message values we're going to convert that counter which is a unit 256 and we're going to convert it into a string comma and notice this single quote around the message and this is just a thing with sql that you should be aware of if you aren't as familiar with sql make sure that you wrap any strings any text in these single quotes to make sure that it is sql compliant so id message values that first value comma the message itself close out the single quote close out the values boom good to go so we have our actual statement ready to go and then at the end we're incrementing the counter just so that every id that's inserted ended up being unique and that's actually an important point here because if you recall at our schema we specified that this is the primary key so the primary key should be unique great okay next step let's make sure that everything compiles correctly nice it does so before going on let's just do a quick recap we imported some contracts we in theory set all these state variables or the majority of them through the constructor function we created a table which sets some of those state variables and also information that's relative to the last step which is actually writing to the table and now we are actually ready to deploy now best practice as with any development is to do some testing and dry runs and things like that prior to actually deploying we're just going to be bold and skip brick to the end and just hey let's launch it so we're going to use injected provider and if you have metamask installed that basically is just using the provider via metamask we're going to actually also specify the network so as noted table line is live on a number of different networks we're going to choose polygon mumbai because why not and before doing so if you recall in our constructor we passed this registry address in the deployed contracts portion of the table and documentation you can see all of the smart contract addresses if you're curious you can also even check out what it looks like what the table tokens look like what the contract does how to read and write to it all from this interface itself but we're just going to go ahead and copy this address for polygon mumbai now when we deploy this we're going to paste that address into this deploy section because that is basically passing that address to the constructor variable and now we are ready to deploy so it is rather inexpensive especially on polygon just a fraction of a matic and down at the bottom you'll see and i'll even open this up a bit but you'll see the deployment information and exactly what happened so just to even verify things let's see what it looks like on polygon scan here's the transaction hash so we'll copy it come over here scroll to the top search for this address in particular and often there is a little bit of a lag for really any indexing solution but we can see here that cool good to go contract was deployed and then the next step we're going to do is click down here at the bottom and we're going to interact with that contract first what we're going to do is just validate that our table name is empty and then we are going to actually create the table again note the cost for table creation is rather inexpensive and always think of this in the context of hey if i were to save this data to a state variable what would it cost and if i were to try to mutate this over time what would it cost cable n is significantly cheaper than doing so so you can kind of see a table as this layer that is saving data that could have been in state variables but doing it in a much cheaper capacity based on the way table line is architected with this registry smart contract and events being emitted where that tableland network of validators are the one materializing the events so just a different way to think about things overall nice so we are going to create the table wait for it to actually go through and then similarly with that last example we're gonna wait for the transaction to be confirmed and upon being confirmed we'll see what it looks like on the table and side of things and more specifically let's look at it on polygon scan wait for the indexing to pick up cool so let's dissect this a bit and if you ever are interested you can always see some information down in the decoded logs here there's nothing going to be quite interesting in the scenario maybe if we pull over into this section we can but i don't really want to go into those details at this time let's just take a look at what we've done so here we're saying that within that registry smart contract it's minting a token so it's going from the 0x address to this address and it's minting this token id so 1380 is actually our token now if we're curious what this looks like in the table and realm we can also do this so 1380 let's see what it looks like and this will require us to go back to the actual table and tables smart contract scroll down the bottom find token id or token uri actually 1380. there we go so boom this is our token itself if we want to see what it looks like we can see this real-time svg rendering of the data so that prefix that chain id that table id the network the number of rows we haven't inserted any data quite yet so this is expected it's zero and then the number of columns or the information in particular about the columns so that int primary key message text and we're going to keep this up we're going to come back to this in a moment here now let's validate that it looks good okay perfect just as that svg had showed and note that there are also apis that we can also walk through here today for interacting with the table the svg is just a nice way to see what's happening but now what we're going to do is write to the table so we're going to say well recall that if we scroll down really all this is doing is taking a message and then it's forming forming like everything else that's incorporated into this insert statement so we're going to say hello from tableland right to the table rather low cost compare against writing and mutating some storage variable it would be much more expensive and then confirm as this is confirming or pending so we have this transaction waiting in the transaction pool okay a miner has picked it up it's included in a block great we have this transaction hash we will check it out again on mumbai scan see what's going on indexing boom so now we can actually see what we decoded um see what we actually wrote as part of that message so hello from tableland cool so we know that the event was emitted we know that this interaction has happened now let's finally check make sure that everything was good and that the data was rendered in table cool real time update dynamic nft row is now one and what's cool is that we can also just like query the data itself so rather than just looking at this svg rendering we can also go over to this endpoint here and what's cool is that this is actually fully sql compliant so test net table line network slash query and with this query parameter you can write raw sql statements so we're going to say select all from and we had what was our table name ethonline the chain was polygon mumbai and the token id was 1380 awesome so just mirroring this and marrying this together with this you can see table name chain rows columns information about the columns essentially it's pulling information from here and summarizing it in a nice svg type view dynamic nft capacity great so if you're interested you can continue interacting with that contract i'll i will share this in a right up at a different point in time but appreciate everyone sticking with me throughout this demonstration what we did was create a smart contract that simply interacts with the tableland table registry it allows you to easily create a table and write to a table all using just smart contract interactions and then from here what you can do is really get creative build dynamic experiences that leverage complex ccls and access controls to really open up the door for truly composable cross-chain cheap native experiences to the web3 realm thanks again for sticking with me