TinySQL - A Simple Database Management System

TinySQL is a simple but fast database management system. TinySQL is self-contained and doesn't require external libraries except standard libraries. TinySQL must be compiled using C++17, GCC 8.0+, CMake 3.0+.

We used some source files of the project: hsql as our SQL Parser. We deeply appreciate their work!

Some important facts

1. All the files are store in the same directory of the executable tinySQL, under the folder DB_DATAFILES. The file structure:

DB_DATAFILES
|    |
    INDEX
        ...(Index files, binary)
|    |
    RECORDS
        ...(Table files, binary)
|    |
    SCHEMAS
        ...(Schemas of each table, binary)

1. The database creates an index using B-plus tree on the primary key. Other indeces can be added manually by user.
2. You can exit tinySQL by input an EOF (ctrl-D on linux), ctrl-c(terminate) easily causes loss of data.

Supported Queries

TinySQL supports the following standard SQL queries:

1. CREATE TABLE , CREATE INDEX
2. DROP TABLE, DROP INDEX
3. INSERT
4. SELECT (don't support sub-queries, support logical operands like AND)
5. DELETE (don't support sub-queries, support logical operands like AND)

and the following non-standard SQL queries:

1. SHOW TABLES; : Display all the tables stored.
2. INPUT FILE; : Input a text file containing SQL queries. Has to be in uppercase.

supported data types: char (<=20 characters) varchar (<=20 characters) int decimal float

Examples:

Create Table and Insert Value

create table student(
    name varchar(10),
    id char(5),
    age int,
    score float,
    primary key(id)
);
 
insert into student 
values('John', '35124', 21, 89.3);

Output:

Success.
Executed 1 queries in 1.356 ms.
Success.
Executed 1 queries in 0.459 ms

Drop Table

drop table student;

Output:

Success.
Executed 1 queries in 0.155 ms.

Read File

READ FILE;
q2.txt 

q2.txt is an example query file, 10000 queries, create a customer table. Check the details by show tables. Output:

Success.
... # (9999 lines, some lines contains an error message, for conflict primary key tests.)
File query successfully executed!
Executed 10000 queries in 256.496 ms.

Select

select * from Customers
where salary <= 1.3;

Output: (based on the file input above)

|          id     |        name     |    postcode     |      salary
------------------
 
|       00568     |  Portsmouth     |     SL8 2JK     |      1.0056
|       01338     |  Springdale     |     LE1 6GU     |      1.1784
|       06205     |     Garland     |     TN3 7LL     |      1.0694
|       09299     |   Bethlehem     |     MK2 2RN     |       1.118
|       11007     | Bakersfield     |    CV15 2YO     |      1.2394
|       12276     |       Miami     |     HP9 4BM     |      1.2697
|       16750     | Chula Vista     |     TN2 1KP     |      1.0895
|       16908     |       Fargo     |     OX0 3ZJ     |      1.2746
|       17649     |  Santa Cruz     |    DE49 6ZS     |      1.2227
|       17842     |  Greenville     |    TN73 0SS     |      1.1374
|       23311     |  Fort Wayne     |    SL85 1WK     |      1.0926
|       26353     |     Hampton     |    SA43 1DM     |        1.03
|       27788     |      Pomona     |    UB39 7XH     |        1.23
|       30493     |    Bellevue     |    SO89 4TM     |      1.1235
|       31982     |     Jackson     |    DG03 7AW     |      1.0659
|       32078     |   Rochester     |    HA20 1NH     |      1.2737
|       37785     |      Howell     |    DD52 1HD     |      1.0336
|       41331     |Thousand Oaks     |     NP2 7GA     |       1.227
|       42609     |     McAllen     |    ME90 3AX     |      1.2684
|       47502     |   Champaign     |     KY8 7IN     |      1.0419
|       49408     |Santa Clarita     |     EN2 5XL     |      1.1861
|       57187     |Cedar Rapids     |    RG64 7HU     |      1.1383
|       60497     |Gulfport-Biloxi     |    SW82 3SK     |      1.2579
|       63880     |  Evansville     |     NN0 3PT     |      1.1445
|       66805     |    Palmdale     |     WA5 6MU     |      1.2375
|       73228     |   Lafayette     |    TQ71 3HP     |      1.1327
|       73272     |Moreno Valley     |     WD0 9UX     |      1.0408
|       78404     |Grand Prairie     |     TF5 9UF     |      1.0863
|       82160     |     Hampton     |    UB80 6AC     |      1.1697
|       84923     |   Henderson     |    YO52 7HI     |      1.0305
|       86753     |  Washington     |     HG9 4TP     |      1.0345
Output 31 records.
 
Success.
Executed 1 queries in 11.707 ms.

Delete

delete from Customers
where salary <= 1.2;
 
select * from Customers
where salary <= 1.3;

Output of select: (based on the file input above, salaries between 1.2 and 1.3 are gone.)

|          id     |        name     |    postcode     |      salary
------------------
 
|       12276     |       Miami     |     HP9 4BM     |      1.2697
|       27788     |      Pomona     |    UB39 7XH     |        1.23
|       41331     |Thousand Oaks     |     NP2 7GA     |       1.227
|       60497     |Gulfport-Biloxi     |    SW82 3SK     |      1.2579
Output 4 records.
Success.
Executed 1 queries in 5.8 ms.

Create Index

create index customer_name on Customers(name);
drop index customer_name;

Thanks for reading! If you use our code or SQL_PARSER, please reference the github repository in your project.