SQLite Challenge Question

[devns8]

Hello All,

I’m hitting a sticking point doing the “Build your Own SQLite” challenge on codecrafters. I’m doing this in Zig, although that is irrelevant to my issue and subsequent question.

The problem I’m stuck on is “Print number of tables”

Now, I’ve read the sqlite documentation and so I know that after the first 100 bytes in the file which is the DB header, you have page 1 of the database and this is the sqlite_schema table. This table will hold a reference to every table in the database. No where in the sqlite documentation do they say they every store the number of tables in the DB or the number of rows in a table, so It seemed to me at first, we would have to iterate the sqlite_schema table rows and count up all the tables manually.

So I was super confused when I looked at the code examples of already completed challenges and found people reading a simple scalar value to find the number of tables in a sqlite database.

All of the examples use the same strategy. Read the first 100 bytes, then seek 2 more bytes forward and read in a u16. Parsing this u16 wiill supposedly give you the number of tables in a database. Code below

        // The header is 100 bytes long https://www.sqlite.org/fileformat.html#the_database_header

        // After the header, we have the actual beginning of the first page that is the root page of the table b-tree for

        // the sqlite_schema table https://www.sqlite.org/fileformat.html#storage_of_the_sql_database_schema

        var page_type: [1]u8 = undefined;

        _ = try file.seekTo(100);

        _ = try file.read(&page_type);

        // We assert that the page is a table _leaf_ page, meaning we can only count the cells to get the table count

        std.debug.assert(page_type[0] == 0x0D);

        var buffer: [2]u8 = undefined;

        _ = try file.seekBy(2);

        _ = try file.read(&buffer);

        const table_count = std.mem.readInt(u16, &buffer, .Big);

I understand the code, but for the life of me can not find any references stating that the number of tables or rows in a table is directly present in the database file.

Any help with this would be greatly appreciated

[rohitpaulk]

We’re working on better instructions for this stage – essentially in this stage it’s okay to assume that (a) The sqlite_schema table is small enough to fit in a single page (b) all rows in sqlite_schema are indeed of type == ‘table’. With these two assumptions, the count ends up being equal to the “number of cells” in the first page, which is readily available:

[devns8]

Ahhh Ok. Understood now. Yes, some better explanation or simply some qualifying hints would greatly improve the clarity of this challenge.

Thanks again for the answer and your time.