CODEWITHSUNDEEP
sqlite
user7037473
user7037473

Reputation:

How does Sqlite Database Stores Tables?

I am new to Sqlite, actually new to the whole SQL concept and I confused on how these data tables are stored inside Database, let me try to demonstrate what I think:

I think this:

Database Database_name
{
   Table table_name;
   Table another_table_name;
   /*and the table creation continues like this*/
}

but I know about Dataset, so I wondering if its stored like this:

Database Database_name
{
   Dataset dataset_name
   {
     Table table_name;
     Table another_table_name;
   }
   Dataset dataset_name2
   {
     Table table_name2;
     Table another_table_name2;
   }
}

so what is the actual way in which its stored, better explain it using Database Dataset and Tables, as I demonstrated

Upvotes: 6

Views: 5049

Answers (1)

Schwern
Schwern

Reputation: 164639

tl;dr: Each SQLite database is a set of files, one for the database, and others for things like transactions. In the database file, each table is a B-tree with one row per node. A node's key is the rowid and the value is the types and sizes of each column, plus the value of each column for that row.

Each node of the B-tree is a page in the database file. Every page is the same size. If a row is too big for a page, it can overflow into another page.

The SQLite database file format is documented. There's two files, the database file and the rollback journal. The rollback journal contains information needed for transactions.

The database file starts with a header that has information about the database like its encoding, and the rest is split up onto "pages", fixed sized blocks of disk space. This allows SQLite to find specific things in the file quickly without reading the whole thing, it knows where each page starts in the file and it can quickly seek to that location.

They can be...

  1. The Lock-Byte Page (it's a thing for Windows mandatory file locking).
  2. Linked-list of free pages, like rows that have been deleted and are ready to be reused.
  3. Overflow if the data in a row is too big for one page.
  4. A pointer map to make vacuuming more efficient.
  5. Pages in a B-Tree that hold the tables and indexes and all that.

You care about that last one, the B-tree. B-trees let you store key/value pairs that are fast to look up individual pairs, fast to read the whole list in order, fast to insert, fast to delete, and efficient on space.

Each table is a B-tree scattered around the file in pages. Each entry in the tree has a key, the 64 bit rowid, and the value is the row. The row is represented as a header which describes how big each field is in that row, and byte array of all the columns in the order they're declared.

So let's say you have:

create table user (
    id integer primary key,
    name text,
    age integer
);

And you insert into user (name, age) values ("Yarrow Hock", 41) which gets an id of 12345. That would be stored in the b-tree with the key 12345. The value starts with a header describing what's in the column.

0, 35, 4

0 is for the integer primary key. 0 says it's null. Since it's the key it doesn't need to be stored again in the value.

35 says the next field is text and has a length of 11 (assuming you're using UTF-8 which you probably are). How's that? It's (length * 2) + 13 or (11 * 2) + 13. It's done like this to ensure the field is always odd and it's over 12. Even fields over 12 are binary. Anything under 12 is an integer or float or something fixed width.

And 4 says the last field is a 32-bit integer.

Finally, the data for all the columns are put together into one byte array. Here's what it would look like in hex.

59 61 72 72 6f 77 20 48 6f 63 6b 00 00 00 29

Using the header, SQLite knows that the name field starts at data[0] and is 11 bytes long. That's the 59 61 72 72 6f 77 20 48 6f 63 6b part. The age field starts at data[11] and is 4 bytes, that's the 00 00 00 29 part.

The complete database schema for all tables, indexes, triggers, and views is stored in a special table called sqlite_master that looks like this.

create table sqlite_master(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

It's stored just like a regular table, each table, index, trigger, or view is one row.

There's plenty of other stuff in there, and you can read the docs to learn more, but that's the basics of how SQLite stores information. I'd advise you to look into B-trees, they're great.

Upvotes: 17

Related Questions