Hey. Are we having fun yet or what? I know I'm having fun. I hope you are having fun. This is the last video for this module unless I come up with something else later.

But as it stands, I think this is the last video for this module. We've been learning a lot about how to create tables in SQLite. What are the data types, all that stuff that's available to us, table modifiers, that sort of thing. There's one more thing I wanna show you and that is generated columns. Traditionally, you create a column and you put data in it.

That's pretty straightforward. What if you could create a column and have SQLite put data in it? That's pretty cool. Let's take a look. So we are going to create table gen, short for generated because I don't wanna type, and we're gonna have ID as an integer primary key, and we're gonna have first name as text, and we're gonna have last name as text.

Now, this is a pretty contrived example, but what if you wanted a full name column? You can have a full name column that puts the first and last names together and SQLite will handle putting the first and last names together for you at all times forever until the heat death of the universe. And the way that you do that is you give it your column type and then you say generated always as. And then in here, this is where you put your expression. So we're gonna say concat, first name and then we'll put a space and last name.

And that is going to be our, full name. And I think that is going to be it. And just for giggles, we'll declare this as strict. Great. That worked.

So let's insert into Jen. I should have named it like people or something. Insert into Jen values. Actually, let's just skip. Let's do first name, last name and then for values we'll put Aaron and Francis.

That's me. So select star from Jen. Look at that, Aaron Francis is the full name there at the end. We didn't put that there. SQLite put that there.

In fact, you can't you can't modify that. You can't, you can't insert a value for that. You do not control that column. That column is the purview of SQLite and SQLite alone. And it's just like any other column, select full name from Jen.

It's just a normal column save for the fact that you don't put the data there. Now there are 2 types of generated column. There is a virtual generated column and there is a stored generated column. A virtual generated column is calculated at run time, so it can be a little bit more expensive in terms of CPU and overhead, if you are doing this at runtime. This is pretty cheap, frankly.

I think concat is you're you're probably gonna be fine. A stored generated column is calculated once and then written to the disk as if it's a regular column. So that takes up more space, but it is less CPU when it comes time to query the data back out. So depending on what your generated column is actually doing, that'll that'll depend on or that will dictate whether you want it virtual or stored. You can put an index on either a virtual or a stored generated column and we'll look at that in the indexes section.

So that doesn't apply. It's just a matter of how expensive is it to calculate and how expensive is it to store. Let me show you how to do virtual versus stored. We're gonna drop table gen just for now and get that create statement back. So we're gonna create table gen and we'll say ID is integer, primary key, first name is text, last name is text, full name.

Now, we're down to the good stuff. Text. We did generated always as. Turns out generated always, totally optional. If you just type as, you're good to go.

So generated always as, or in this case as. So we'll do the whole concat, first first name, single quotes, last name. And then, what we can do is we can say stored or we can say virtual. The default is virtual. And so if you don't put anything, it's gonna be virtual.

But if you put stored, it will store it. It will write it to disk. And so if you were to go look inside the database file, you would see Aaron Francis set together somewhere. So that is how you do it. That's it.

And then it operates the exact same way. We can just insert, we can insert and we can select and it's it's it's identical. So you have 2 options, virtual and stored. And it's up to you to decide which one you wanna do. You can put an index on either one.

If it's cheap to calculate, I would do virtual. If it's expensive to calculate, I would do stored. Now what are some of the rules for generated columns? They have to be, if you use a function, it has to be deterministic. So you can't put like a random function in there or like a a now.

You can't put a call to the current time because it has to be deterministic which means it's the same every time you run it. So if you're gonna create a generated column, which you should frankly, these are very very useful for extracting pieces of giant JSON blobs. You can extract just a part of it. And when we get to JSON we are gonna do that. So you should use generated columns wherever you think you need to.

First name, last name, full name, it's kind of okay. Extracting JSON is great. Breaking up an email address into the, the username and the domain, that's great. Doing some sort of math calculation, maybe price plus tax is total price or price minus discount is total price, something like that. Those are all good use cases for generated columns.

If you want to look for more examples, MySQL Postgres, everybody has generated columns and so you can just search for generated columns. It doesn't have to be SQLite specific, but I really love generated columns and this won't be the last time that I talk about them.