Filecoin Hackathon Presentations, 5 May 2023

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Tableland - SQL Database on the FEVM - Dan Buchholz - FVM Dataverse Hackathon 2023

Description

Tableland - SQL Database on the FEVM presented by Dan Buchholz at the FVM Dataverse Hackathon Kickoff 2023.

A

uh Yeah I've got Dan here, um I believe if I've got everything in in in the right order. Dan, yes, I, have got down here: great fantastic hi Dan. How you doing.

B

Good, how are you.

A

Yeah I'm good thanks um great yeah. If you want to share your screen along the bottom, you'll see, there's a series of icons about the fifth one, I think along there's a picture of a screen there and um uh you can. You can share your screen. There sounds good.

B

um And I had a few slides if it was helpful, just like to walk through those briefly or I can just dive into the workshop by the way it works. For me,.

A

uh Well, I mean if you want to do a few few slides and just kind of give a bit of a kind of an intro and a background as well. um You know I, haven't sort of yet given any kind of introduction to the to to this Workshop or talk or or even even who you are so.

B

um Sure feel free.

A

To give a a bit of an introduction, you know to yourself and um um about tableland uh for those that um haven't come across tableland, yet cool.

B

Yeah, it sounds good, so, let's just double check cool um yeah, so I'm I'm Dan from the tableland team, part of the the growth side of things so focusing on developer relations and helping developers on board to the protocol and build with tableland. So what is tableland? It's a decentralized cloud database built by and and really powered by, the sqlite engine.

B

So what it allows you to do is build web3 native applications using evm chains for the actual, like rights and permissions associated with all your data that also Leverage The, tableland Database Network, to really facilitate things like queries and actually accessing that information in a way that makes it a lot easier for developers, especially if you've been trying to like fit things into like the web, 3 mold and it just hasn't, worked out very well and that that's really what like our vision, 4K blonde is- is to be like that decentralized, relational database for web3 applications that could be like it's really.

B

A database is a pretty broad solution so, like what could you use tableland for so in like in in like this context, it could be like application data. It could be data directly from just smart contracts and blockchains. It could be data sets or data pipelines, information that you're just trying to sort of like pipe into tables and and use tables to store that information or could, even especially like relative to the fem, really augments some of the the file storage there, especially when you're thinking about the programmability with smart contracts.

B

Now you can really like use tableland in tandem with filecoin to store large data sets, describe them with tables and like metadata, really like focusing table lines focusing more on the smaller pieces of data, whereas, like filecoin focuses on those large pieces of data or even storing things like deal data within tableland tables. So all that can really be powered by a smart contract and and leverage tableland across your entire application. Stack.

B

And then I'll Breeze through these, but just like context for how people store data in web3. This is like really generalized, so it could be like on chain in smart contracts, where you're storing data in mappings or structs or this, and that not all data really belongs in smart contracts. Those challenges with it around like query, features and expensive mutability.

B

You could store information in decentralized file storage, which obviously is great but there's also as really before, especially before the fdm there's some drawbacks with uh just storing data strictly in there, because of just like some of the query features associated with like a CID, for example like how do you know? What's what data is underlying that that CID and the actual, like you know, immutable nature of the? You know, cids in general, like that?

B

That has some drawbacks and that's really where like table I, can help actually describe the underlying data Within, some sort of CID or file that you're storing on filecoin and then give you like native queries to that information. And then some developers in web3 are still using centralized database Solutions.

B

Just because it's like challenging to store data anyways that, like you, you want to be web3 native and want to have this like open sort of permissionless way to store information but until table, and we feel like that really hasn't been an option and that's why people have turned to potentialized database Solutions.

B

So the table end basically comes in trust them like marry all the the best from these different approaches into one solution, which is a this decentralized database built on top of and sort of adjacent to, as well evm chains, so with fevm. Launching that that's obviously like something we're super excited about, and uh we're really just a couple, I think we're just about to launch on mainnet but on Falcon hyperspace good to go there.

B

So that's what I'll be walking through here today and then just like generally, when so, when you think about table and like okay, what can you do with it? Like General flow? You have maybe some application there or maybe you're, just directly, interacting with smart contracts, but you have some sort of interaction. That's triggering a call to a blockchain.

B

So what that? What this is really trying to say is that all the rights and permissions and table creates all that goes through the blockchain. First, it's written to event logs the table and network has a smart contract on these evm chains. That really allows you to like write SQL on chain off chain. There's this table and network of nodes that are running and listening to those on-chain events.

B

So then, at the application layer you can actually read the data, so tableland in this like context, acts as sort of this middleware database to enable you to like scale your actual application in a web 3 native way, and then there's plenty of information on our on our doc site. That's docs.tableand.xyz!

B

This is a quick snapshot just walking through, like some of the more technical specifics for how that flow Works between like the tableland registry and how SQL is emitted from Smart contracts, but we can always dive in at a later point and obviously I'll be on Discord and whatnot. So if uh you're curious, you can always ask me some questions there and then, lastly, so what we'll be walking through today is like actually using tableland within your project.

B

So there's like two ways: you can like there's multiple ways you can take the land, but like two of the most common ones, you can write SQL on the front end or SQL on the blockchain and on the left hand, side won't necessarily be a front end in this in this Workshop, but we'll be using the table on SDK to actually like write some data and read some data, which is pretty cool, but then we'll also be using SQL on the blockchain within our smart contracts, we're like creating tables and writing data. So you.

A

Can have Smart.

B

Contract powered data being written to the this decentralized database and all accessible via the SDK uh for even rights or creates, but also just for for reading, that data strictly and last thing around use cases again pretty broad for really a database like you can store. Everything is database right, so it could be anything from like data does or tooling if you need identity controlled information.

B

So if you want to only control certain rights to data sets or only control, a specific cell based on rules that are programmed in a smart contract, that's like really a super power of tab line, because all access controls are dynamic and defined within a smart contract or it could be application. Data could be something collaboration oriented or on, like the the other side of things, maybe it's nfts or gaming, and just the ability to store that information and control that access accordingly.

B

Cool so I'll pause there and move into the workshop.

A

Yeah this is uh it's: it's yeah, interesting where you get that you know the mix between the immutability and mutability right. It's easy to do one, it's easy to do the other and then table and kind of comes in it's like okay. Sometimes you need a bit of both and here's how we can do it so yeah.

B

Exactly and I think on that like and there's even ways like if you technically, if you want data to be immutable in tableland, you could set up the rules that way, but we just view that a lot of information is not should not be immutable and it like should have immutability and and so like we're really trying to like change that Paradigm, which especially coming from the nft space like certain projects.

B

That's not like exactly my background, but we we've dealt with a lot of nft projects as well as like data and everything, but nfts, maybe have focused on immutability and never changing your pfp, but we think that should be totally different and not much. You know no.

A

B

Really is ever immutable right as as right that you're you're changing.

B

Okay cool, so let's kick this off and we're just gonna. First I created a directory called fvm demo, so if you're following along feel free to do that and then we're just gonna actually just set up a hard hat project and we'll just go through the basics, so nothing fancy here and then from there. uh Basically, what we'll be using is a few different things that the tableland team has built. So we have tableland uh Toby the the dependencies that we'll need here are the tableland evm package.

B

So that's like where you can actually access and use all the tableland smart contracts. You have another package for the SDK and then we also have a hard hat specific plugin. That makes it easier to like build locally with tableland and then I'll also be starting uh installing Dot and environment just to access some local variables and then I'll basically kick off and do like most of this programming with something that we built called local tableland and it's like extremely helpful for local development and what it does is actually so, when you think about tableland.

B

It's like this. This dual prong Network, you have like on-chain interactions and then the tableland network that sits beside it, so it enables like the whole network, is based on an event driven development, so like the table on Smart contract is called the register. Smart contract sits on chain, so you technically like in order to develop locally, you have to have like a node running and actually have deployed the table and register smart contract and then, in order to develop locally, you also have to have like a tableland node that is listening to that event.

B

In order to make like the SQL database accessible, so it'd be a lot of pain to like set that up yourself. So we just do it for you with the. Let me install this uh yeah. So that's what the tableland local uh package does and then last thing we'll do here is just set up a.

B

Dot EMV file I can't remember all right. Stop sharing my screen real quick for that components, but here you'll, just set up a private key for a wallet. I think this one is safe to share, but I'm not going to go into it.

B

B

Let's bring my window back up.

B

Cool so I'm, going to start with the smart contract work and we're going to change this up a bit and just import. uh Oh yeah, there's one dependency also did not install, which is open, Zeppelin and opens up. One is extremely helpful.

A

Dan, could you zoom in just a little bit more on the on the text, just make it a little bit bigger yeah, it's just a little better yeah! That's good thanks cool.

B

Of course, this.

B

Okay, cool so now that we have our dependencies I think that's just an error.

B

Okay, cool so we'll start and just like basically remove all this boilerplate and we're going to rename this contract called we'll call it Fem, and so we import a few different things from the tableland evm package. So we have our tableland deployments, which is like a helper same with the SQL helpers, their utility contracts, to make it easier to develop and then towards the end. We'll actually have like controllers for dictating how information is read and information is written to tableland and then relative to these last two components.

B

We have opens up one contracts for Strings and ERC 720, holder, 721, holder, so strings. This is helpful if you're writing SQL in your smart contract and this ERC 721 holder, there's different ways to implement this, but we're actually going to have our contract own a table and a table and table is an ERC 721 token. So, in order to have this contract own the token, you have to implement some specific methods for that and then we'll also just sort of drop some of these things in here.

B

So uh some some of the like configuration related information.

B

What we're going to want is a table ID and- and this is actually returned via tableland once the table is created, and then this can be just whatever you want, but it defines like a human readable what we call prefix for a table. So when, like you're trying to query a SQL table, it has a name.

B

Part of that name is going to be this prefix and then we'll go ahead and create a set of the Constructor, and this is where we start to introduce some some of the the table line Concepts so in this Constructor you'll see that we have this table and deployments get method called and under the hood table and deployments basically uses the or infers the chain ID based on whatever Network you're on and with that information it automatically directs you to the the right Network.

B

So you don't really have to think about like if you're doing, multi-chain smart contract development, because tableland is on some other chains. This makes it easy, but under the hood, when you use filecoin hyperspace or remain nut, it would automatically just retrieve the table and smart contract deployment for the registry, smart contract and then allow you to actually create and and mutate data.

B

So this create method is saying: okay, go to the table and smart contract on filecoin hyperspace and create a table, and the first parameter here is the address in which you want to Mint the table to so this is saying: Hey I want to actually send this table table to the smart contract, so the smart contract is going to own the table and then the SQL helpers method makes it a little bit easier to actually formulate the the statement itself.

B

But really all that's needed here and what's it's doing under the hood, is like a standard SQL create table statement.

B

So it's you know doing something like that: create table fdm table and then we're defining our uh schema and.

B

uh Here so we're saying that, okay, that you know we have two rows or two columns, an ID, it's an integer, it's the primary key and then we're going to have another column. It's a value. uh The identifier is Val and it's of type text.

B

So what's cool is like when we actually deploy this contract, we're creating the table or minting it to the contract, so the contract owns that table and it has like data that it can write to it. So the next step, we're going to do, is actually just even write data to this table within the Constructor and, of course like if you're developing there are a lot of different ways. You probably want to write your your actual data.

B

Here's just like a cool example of how you can do it within the smart contract, but, as I noted, we'll also just like quickly show how to do that with the actual like table and SDK too.

B

So, with this similar pattern, table and deployments get and now we're calling the mutate method and mutate is telling us that hey, we want to mutate data. We want to write data to this table so who is making this call? It's this address it's the contract. That actually is writing the data. What is the table?

B

Id remember that up here we stored the table ID, it's it's one of the storage variables in the contract and then we're going to call the SQL helpers to insert method which makes it easier to insert data or just like write that that SQL insert statement.

B

So if you are new to SQL SQL, it's like an insert into type of set up where you say, hey, I, want to insert data into this table here are the columns that I want to insert data into, and here are the values that actually that I should be inserting as well so insert into this table. Here are the columns. Here's the table, ID table prefix.

B

All this again like formulates the proper statement that tableland needs and then, as far as the data we're just going to keep it really simple: we're inserting a value of one for the ID and the value of Bobby tables for the value, the column of vowel. So really simple setup.

B

But when again, when we just deploy this, the the actual deployment process will will spin this up and spit it out. Basically, and then lastly, we're just going to drop in a bunch of different helper functions. That'll be useful when I deploy this shortly and.

B

So what I added was a basic function just to retrieve the table name function to set the controller and a controller is the way that you dictate how Rights happen for a specific table.

B

So you can set the controller, you say: hey here's, the person making the call, here's the table ID and then here's the controller contract address and in this scenario, I actually am going to create the can uh the controller as the the contract itself. But it could be a separate contract entirely.

B

So then, in order to do that, I'm just spin all the way up here, I'm going to say uh so, if you like, just sort of reset again contract fpm is tableland controller in order to access those, uh the policy methods that I'll be showing in a second and that's the all about the table and controller and data control access control and then, as noted, that ERC 721 holder to make sure that this is possible, such that the the table can be minted to the contract.

B

So we have our Constructor, create the table. Write data, some helpers to either get the name or that makes more sense. You get the name of the table or set the controller, and then, lastly, the skip policy is is really where the the fun happens. So with the the policy.

B

What happens? Is the tableland registry? Smart contract says like hey, someone wants to mutate data, they try to mutate. It table and registry wants to wants to make sure they have the right permission.

B

So this get policy is where you can Define exactly how you want mutability to to work within your application, and this scenario I'm, keeping it really simple, I'm saying: okay, once I actually set this controller, anyone can insert anyone can update, but nobody can delete and they can only do this for this specific, where Clause, so only if the ID of the road they're trying to update is greater than one, they can add other things like tracks or here I'm, defining the updatable columns, which is just the value column.

B

So it's cool where you could add a ton more before you actually return this policy or have like different control flows, or maybe it's checking ownership of an nft and if so, they have access to insert or update data, but only for these columns or maybe, if they don't have any of that. You return a policy that says false for everything, so Dynamic access controls all on chain all right and the last thing I'm going to change this to fdm, real, quick, so cool. That's our that's our smart contract!

B

It's really straightforward, but it'll get the job done here in a second, when I set up the deploy, Scripts.

B

So here I'm gonna drop in and actually simplify this up a bit. So the deploy script uh is gonna. Take that that fbm, smart contract that's going to deploy it, and then it's going to foreign we're going to take one of the values from this and add it to our hard hat config, which I'll set up here before I. Actually do that and what's unique with like some of the tooling that I mentioned earlier, is that we have this like local tableland setup.

B

So it makes it easy to to spin up that table line Network, as well as that hard hat node and a single script. And then, when you deploy to file coin hyperspace we'll need to establish this network and then, lastly, this contract since I'm developing developing locally. It's this address is going to be deterministic based on what I deploy here, but note that, as you deploy a contract, this contract value will definitely change. So uh when I look at some of the other scripts, that I'll show here be sure to update the contract. Address.

B

All right so now, let's deploy this out so first we're going to spin up the table and node and one terminal.

B

And you'll see, on the left hand, side if I zoom make this a little bit bigger. So first what happens is a hard hat. Node is spun up and then, after that you have information that says validator and that's like the table and validator node that is now running. So this gives you access to write and read data and now I'm going to deploy that contract.

B

Cool so, as I said, make sure you save that that contract address and just to like test this out real quick, we can actually read the data, so we're going to create a file called read.js and drop in a bunch of code. Here.

B

Foreign, okay, cool, and so what this is going to do is just simply uh get information about that table and maybe like a different way. We can even do this is with the uh the table on just rust API.

B

So, if I pull that up real quick.

B

Let's see from here, so if you recall in that in that create statement we had or that, and they got in that Constructor we had actually written data. We said the ID was one with the value of Bobby tables and the uh endpoint that we're hitting right now is a local host. 8080 uh I can drop it in the view here in shortly, but we're making a query a SQL read query directly to a Gateway that that validator is hosting.

B

So as you mutate data, this information will also be updated as we go back to the scripts.

B

uh This was actually what I was uh the address. I was accessing, so you can see it's just some endpoint being hosted locally, making a query statement, and then this is where I was saying like select all from this table that we just deployed. So that was what was happening under the hood and now we can also even like write data.

B

So if you want to write information, this is where it gets interesting, because we can write data using just JavaScript and if I try to do this initially what's going to happen is this will actually fail, because if you recall the the smart contract itself, like the the default rules, is only the owner has fall permissions to do whatever they want. So that's like the default thing that happens when you create a table so right now. The only thing that can write to this table is a smart contract.

B

If I try to run this script and I'll, do it here just to show it as a demonstration, but with right what I'm doing is importing a tableland database establishing that database and then I'm, calling that name method that I wrote on the smart contract just to get that table name and then I'm using JavaScript to write data to the table so I'm using JavaScript under the hood, it's making a smart contract call and making everything happen. Basically.

B

So if I try to do this, it's going to fail and it's going to tell us that I don't have the right ACL permission. So this is good because this user, that I set up with my private key they they don't have authorization.

B

So now what I'm going to do is just create a set controller script.

B

I'll drop that in and what this is doing, is setting the controller of that actual table. So in that smart contract, we had this set controller method where he said: hey here's the table. We want to make the address for the controller of that contract. That's also be the smart contract that we deployed so then. Lastly, if we set the controller write that script or run that script.

B

Everything will be good to go and now we'll see that if we try to write data it'll successfully be written such that we're going to insert some random value called my value, the address being used. This is F39 address which, just to prove it is different than the contract, which initially had the only correct, write permissions and then, if we go back to the endpoint that I was showing there a second ago.

B

This is what it initially was, and now we can see there's a value there, so Dynamic access controls, collaboration, collaboration, SQL on the front, end SQL on the on chain and there's so many things that you can do at this round. Just data ownership collaborative data data Dows application data- you know really the limits are endless there, so I think I got a couple minutes left, so I wanted to pause there and make sure we had time for any questions or comments. Etc yeah.

A

That's that's great Dan thanks a lot: okay, um let's just uh I'm just checking here to see if you've got any questions in the chat, I'm, not sure. If we do here um yeah, it just still blows my mind that you know representing SQL tables as nfts and knowing this is all SQL light. In the background you know just seeing within your JavaScript the sort of usual prepare, you know, bind uh kind of you know, methods that you'd normally see when doing any kind of SLL stuff.

A

um You know doing doing all that in this sort of web three senses. uh um That's that's! That's pretty cool, so um yeah.

B

Definitely yeah, and if for anyone that is new to table and I just pulled up our website, we just revamped it a bit um so tableland.xyz or docs.tableland.xyz. If you are trying to like get started dive into things, I guess I did, share repo or create a repo. I can drop that in the chat um in case yeah. You want to follow along for anything that I did there.

A

Oh yeah I'm just grabbing that link now I'm putting it into the into the chat for others. There um brilliant thanks a lot and um yeah another thing to check out people check out, tablelands rigs, uh uh little nft, demo, game type virtual world thing. Whatever it is, it's it's really cool! That's a fun I always always uh like seeing that um uh you know, which is which is really cool, so yeah.

A

Definitely brilliant! Okay, great well, uh fantastic I! Think we're just up on time, so yeah. If people have any further questions, if you want to ask them in the uh Discord Channel, um so Carlene billion has just put the the link um in the in the chat there as well um and there's a tableland sponsor channel in there as well in the Discord ipfs Discord. So if you've got any further questions, then um you can ask then so brilliant fantastic thanks, very much Dan um uh that was great and um yeah.

A

Let's, uh let's see what see how people start, building and I'm I'm really wanted to check it out too. I haven't had a chance to actually get my hands dirty with tableland, yet but um um I'm really looking forward to playing with it uh myself because uh yeah, it looks really cool.

B

Awesome yeah, let me know if you run into any any issues, questions or anything, but thanks again for having me.

A

Yeah yeah no problem great okay, then so next up we have.