A tiny and dummy database built by myself.
This project is inspired by sqlite, has a similar architecture with sqlite (but simpler).
tinydb
is the simplest prototype of database, it only contains ONE table (seetable_t
androw_t
in source filetypes.h
), whose schema isid(uint32_t), username(string), email(string)
.id(int)
is the primary key of our table, we will implement index via B+Tree.
There are two version of tinydb
:
main.c
- In this program, I parse the SQL statements by spliting strings, thus the SQL statements are incomplete. And it does NOT supportwhere
keyword.main2.c
- In this, I implement an tiny SQL Parser based on Flex & Bison, thus the SQL statements in this program are mostly like sqlite or MySQL. And it supportswhere
conditions.
For both of them, they have the same format of database file.
This project has only one .c
file. And you can build it by:
make test
./tinydb mydb.db
And it will enter our REPL (Read, Evaluate, Print, Loop) program:
tinydb > insert 1 1 1
Executed.
tinydb > insert 2 2 2
Executed.
tinydb > insert 3 3 3
Executed.
tinydb > update 1 a a
Executed.
tinydb > delete 3
Executed.
tinydb > select
(1, a, a)
(2, 2, 2)
total 2 rows
Executed.
tinydb >
From so far, tinydb
supports these sql statement:
insert
select
delete
update
commit
rollback
And it also support some meta commands (for debugging):
.help
.exit
.constants
.btree
make build
./tinydb mydb.db
In this program, I implement a SQL Parser. It will support SQL statements like:
SELECT * FROM table;
SELECT col1, col2 FROM table;
SELECT * FROM table WHERE conditions;
SELECT col1, col2 FROM table WHERE conditions;
INSERT INTO table VALUES (NUMBER, STRING, STRING);
DELETE FROM table WHERE conditions;
UPDATE table SET col1 = val1, col2 = val2;
COMMIT;
ROLLBACK;
For the above keywords, we can also use lower cases: select, insert, delete, update, ...
Please note that there is a ';'
after each SQL statement.
And the conditions
support operators: =, !=, >=, >, <=, <, AND, OR
, e.g.
SELECT * FROM table WHERE id < 10 OR (id > 100 AND username = 'sinkinben');
The keywords AND, OR
can be and, or
.
Here is an example, showing how to use tinydb
.
tinydb > insert into table values (1, 'sinkinben', 'skb@qq.com');
Executed.
tinydb > select * from table where id=1 and username='sinkinben';
id = 1 username = sinkinben email = skb@qq.com
total 1 rows
Executed.
tinydb > update table set email='sinkinben@outlook.com' where id=1;
Executed.
tinydb > select * from table;
(1, sinkinben, sinkinben@outlook.com)
total 1 rows
Executed.
tinydb > .exit
mv gemfile Gemfile # rename Gemfile
bundle install
bundle exec rspec
Running this testing script, will fill the database file mydb.db
with dummy data (total 6000 rows).