50 lines
3.6 KiB
Markdown
50 lines
3.6 KiB
Markdown
# MyDB
|
|
|
|
I have no clue how databases work
|
|
It has been so long since my DB course, all I know is that they use B-Trees to store data
|
|
I'm gonna attempt to write my own DB anyway
|
|
|
|
## File format
|
|
|
|
### Train of thought
|
|
|
|
As far as I'm concerned, the most important thing is probably the way the database gets stored. I don't want it to be in memory so I will have to create a file (or more) on the filesystem in which all the data is going to be stored.
|
|
As far as I know, sqlite stores databases in a single file, I'm not sure about others like postgresql, mysql/mariadb, oracledb, etc.
|
|
Maybe having a file per table instead of per database is easier to set up, but it might be just as hard, and doing IO on multiple files might be bad for performance.
|
|
|
|
### Format
|
|
|
|
#### Header
|
|
|
|
| Offset | Length | Description | Example |
|
|
|---------|---------|-----------------------------------|----------------------------------------------------|
|
|
| 0 | 16 | Engine Version | MyDB-0.0.1 |
|
|
| 16 | 14 | Db Version | YYYYMMDDhhmmss |
|
|
| 30 | 64 | Table of Tables offset | Default: 512 |
|
|
| 94 | 64 | Table of Users offset | |
|
|
| 158 | 64 | Table of user to table | |
|
|
| 222 | 1 | Page Size as power of 2 | Default: 14 == 16,384 bytes<br>max 2<sup>255</sup> |
|
|
| 223 | 289 | Nothing | |
|
|
| 512 | 16284 | Default offset of table of tables | |
|
|
|
|
The table of tables contains the names and offsets in the file of all tables, or rather the first page of all tables.
|
|
Each table name can have a length of 32 bytes, the offset is a 64 byte integer and there is a 32 byte id so each row in this table is 128 bytes long.
|
|
|
|
The table of users contain the username and encrypted password of a user. Each username and password can have a max length of 32 and 60 bytes and there is a 32 byte id so 124 bytes per row.
|
|
|
|
The user to table table contains the read and write access of each user for each table. It uses the table of tables id, the table of users id and a byte for read access and a byte for write access. So 66 bytes per row.
|
|
|
|
Each table takes up at least one default page worth of space. Default page means that page sizes can vary depending on the table. A table with columns using up a ton of space might need more space per row than the default page size, there it wouldn't make much sense (I think) to have a static page size
|
|
|
|
#### Page
|
|
|
|
| Offset | Length | Description | Example |
|
|
| (from page offset) | | | |
|
|
|--------------------|--------|---------------------------------------|----------------------------------------------------|
|
|
| 0 | 64 | Number of pages | Default: 1 |
|
|
| 64 | 1024 | max 32 columns * 32 bytes per name | |
|
|
| 1088 | 64 | 1 byte per column to set as indexed | |
|
|
|
|
## SQL Interpreter
|
|
|
|
todo!() |