Video Transcript
A SQLite database is a single file. That's kinda one of the most famous things about SQLite is it's just a file. Right? But inside that file so we have this one file. If you crack open this file inside the file, you'll find a lot of equal size chunks of data.
And those chunks of data are called pages. So anywhere you hear something about singleite pages, database pages, b tree pages, it just means an equally sized chunk of data. And that is how the data is arranged inside the file. There are a few other pages that are primary internal. One would be coordinating the locks.
And so when multiple processes are trying to read or write from the file, there's a system of gaining and sharing locks which we'll cover a lot about. So there's a lock page. Whenever you delete data in SQLite, it deletes it out of the page, but it doesn't move everything, it doesn't compress everything down. So you could end up with some free pages in the middle. And so there is a page that actually keeps track of, hey, where where are the free spots that I can go write extra data into later?
The size of these pages is configurable, anywhere from 512 bytes all the way up to 65 536 bytes. And that information itself is stored in a page that keeps track of settings. So it keeps track of the page size, the version number, the mode for reading and writing, whether rollback or wall mode. So a lot of information is written at the very front to make sure that SQLite, the library, is interacting correctly with the SQLite database that you're working with. Let's go ahead and crack open a SQLite database.
So we've got test. SQLite here. You can just open it with SQLite by just passing the file name there. And we're gonna hit the tables and see that there's one table called users. So select star from users, that gives us the one user which is me.
So we know that SQLite can support up to 1 user. I'm just kidding. Let's keep going. If we get out of here, let's open this up in not table plus but sublime text and we see this. This is the database.
This is the entire thing. You see there's a bunch of stuff. Oh, I think that there's some real data. There's a bunch of stuff that is just basically placeholders at this point because a there's not much data in there and the pages are of a certain fixed size. But if you come up here to the very beginning, a little bit of trivia, you will this magic string at the top of every SQLite 3 file.
What does that magic string say? If we were to pass that magic string in and format it as ASCII you see SQLite format 3. So just a little bit of trivia there. After that, you start to see some of the settings. I'm not gonna pretend to have these memorized.
I think this might be page size. This might be journal mode. I don't really remember. But this is where some of those settings are stored. The most important thing you don't need to read this.
I can't read this. This is this is not readable by a human. The most important thing for you to remember is that inside of this database file are several equal sized chunks called pages. This is going to be very important for the rest of the course. My greatest fear, not my greatest fear, one of my great fears is that I teach you things and you're like, well, yeah, I guess I guess that's true.
I want you to learn things. And so learning that the pages exist, that is going to help us when we start talking about the different modes of how to make SQLite more performant using something like the write ahead log. It's gonna make a lot more sense. So, you don't need to know all of this. I find this stuff interesting.
Just remember that there are pages inside of the file.