As we are continuing our tour through the internals of SQLite, I wanna show you something called virtual tables. And these are extremely interesting. If you don't find these interesting, that that's honestly that that makes a lot of sense. But I think they're interesting and I think they're very cool. Virtual tables allow you to query a table but it's not actually written to your database.

Let me show you what I'm talking about. So, if we load up SQLite 3 and we pop in here, I've been blabbering on and on about the ability to load extensions. Finally, we get to see that loading the CSV extension. That's it. Let's actually get out of here and look at that CSV extension.

So I did download it and compile it for my system. I'll leave some links below on how you can do that if you need to, but let's take a look at this sample CSV. So if we look at sample CSV, that's pretty boring.

It's a it's just a CSV. So So we have item as 1 column, count as the other column. Now, what we are going to do is create a virtual table using that file as the backer. So, hopping back into SQLite 3, load CSV. So now CSV is loaded and what we're gonna do is we're gonna create a temporary table, virtual table.

So create virtual table, t1, using csv where the file name is sample.csv and we have headers in that file and so we've turned on headers equals true. So did it work? I don't know. Let's find out. Select star from t1.

It worked. Let's change the mode to box and run that again and we see that our columns are named correctly. And so now we're using a CSV as if it's a table. And you can see already how this might be extremely powerful. If you get a massive CSV, you can just kinda open it up and query against it which is which is kinda awesome.

Let's keep going. I'm gonna show you another one. So if we clear this out, we can do select star from generate series. You don't have to load any extension for this. 1 to a 100.

And there's some numbers. That's it. This on its own is not incredibly useful, but you can imagine as part of a larger query, this might actually be very useful. It might be useful even to have, count up by fives starting at 1. That looks a little goofy, doesn't it?

Let's clear that out and let's count up by fives starting at 0 going that's a 100's fine. There. That's a lot better. Now you could use this as a part of a larger query. You could do you could use this as a way to calculate some sort of series or offset.

Traditionally, you would do something like this with a CTE but because SQLite has given us this generate series function, why not just use that? So this can become the source against which you join or filter or exclude, something like that. Like I said, when we get to the JSON module of this course, there's JSON tree and JSON each and that's a way to deconstruct big JSON objects and turn them into tables against which you can query. Very cool. There are a lot more virtual tables.

We're not gonna cover all of them. The ones that we will cover in-depth, the full text search. So full text search 5 is implemented as a virtual table and we're definitely gonna cover that because it is super powerful. But now you know that virtual tables exist, you know that they might be useful to generate some sort of data and we'll look at them in-depth more as we continue on.