How does SQLITE DB saves data of multiple tables in a single file?

Question

I am working on a project to create a simplified version of SQLite Database. I got stuck when trying to figure out how does it manages to store data of multiple tables with different schema, in a single file. I suppose it should be using some indexes to map the data of different tables. Can someone shed more light on how its actually done? Thanks.

Edit: I suppose there is already an explanation in the docs, but looking for some easier way to understand it better and faster.

Answer 1

The schema is the list of all entities (tables, views etc) (the database as a whole) rather than a database existing of many schemas on a per entity basis.

Data itself is stored in pages each page being owned by an entity. It is these blocks that are saved.

• The default page size is 4k. You will notice that the file size will always be a mutliple of 4K. You could also, with experimentation create a database with some tables, note it's size, then add some data, and if the added data does not require another page, see that the size of the file is the same. This demonstrating how it's all about pages rather than a linear/contiguos stream of data.

It, the schema, is saved in a table called sqlite_master. This table has columns :-

• type (the type e.g. table etc),
• name (the name given to the entity),
• tbl_name (the tale to which the entity applies )
• root page (the map to the first page)

• sql (the SQL used to generate the entity, if any)

  • note that another schema, sqlite_temp_master, may also exist if there are temporary tables.

For example :-

Using SELECT * FROM sqlite_master; could result in something like :-

2.6. Storage Of The SQL Database Schema