MiniSQL

A full-stack SQL query interface built on top of NITCBase — a custom relational database engine written in C++. MiniSQL extends the original engine with new SQL capabilities and wraps it with a modern web interface, allowing users to run SQL queries through a browser instead of a command line.

---

What is MiniSQL

NITCBase is a teaching RDBMS built from scratch in C++. It simulates a disk-based relational database with its own buffer manager, cache layer, B+ tree indexing, and a SQL-like command interface. MiniSQL extends this engine and builds a complete web interface on top of it.

The project has three layers:

• C++ Engine — the core database engine (extended NITCBase)
• Node.js Bridge — connects the C++ engine to the web
• React Frontend — the browser-based SQL interface

---

Architecture

React Frontend (Vite + Tailwind)
         ↕ HTTP (axios)
Node.js Bridge (Express)
         ↕ stdin / stdout
C++ Engine (NITCBase/MiniSQL)
         ↕ read / write
Binary Disk File (Disk/disk)

C++ Engine Layer (bottom to top)

Disk_Class       — raw disk read/write
Buffer           — StaticBuffer + BlockBuffer (in-memory block pool)
Cache            — OpenRelTable, RelCacheTable, AttrCacheTable
BlockAccess      — linear search, insert record, project
BPlusTree        — index structure for fast attribute lookups
Schema           — DDL: create/delete/rename relations and attributes
Algebra          — DML: insert, select, project, join, print
Frontend         — thin dispatcher to Algebra and Schema
FrontendInterface + RegexHandler — CLI layer, regex-based SQL parser

---

What We Extended

The original NITCBase had 4 SELECT variants — all wrote results INTO a new relation on disk. We added:

5th SELECT — Print to Console

SELECT * FROM relname;
SELECT col1, col2 FROM relname;
SELECT * FROM relname WHERE attr op value;
SELECT col1, col2 FROM relname WHERE attr op value;

Output is pipe-delimited with a header row and row count — parsed by the Node bridge into JSON.

B+ Tree Print

PRINT BPLUSTREE relname.attr;

Prints the B+ tree level by level using BFS traversal, showing internal and leaf nodes at each level.

Files Modified in C++

• Algebra/Algebra.h / Algebra.cpp — print() overloads + printBPlusTree()
• Frontend/Frontend.h / Frontend.cpp — thin dispatchers
• FrontendInterface/RegexHandler.h — new regex patterns
• FrontendInterface/FrontendInterface.cpp — handler implementations

---

Tech Stack

Layer	Technology
Database Engine	C++ (NITCBase)
Bridge Server	Node.js + Express
Frontend	React + Vite + Tailwind CSS
HTTP Client	Axios
Icons	Lucide React

---

Prerequisites

• g++ (C++17 or later)
• Node.js v18 or later
• npm
• readline library (for the C++ engine CLI)

---

Project Structure

MINI_SQL/
├── backend/
│   ├── Codebase/
│   │   ├── Disk/              ← binary disk file
│   │   ├── Files/             ← input/output files
│   │   ├── mycodebase/        ← C++ engine source + binary
│   │   └── XFS_Interface/     ← disk formatting tool
│   ├── index.js               ← Node.js bridge server
│   └── package.json
└── frontend/     ← Vite React app
    └── ├── src/
        │   ├── app/
        │   │   ├── App.jsx
        │   │   └── components/
        │   │       └── BPlusVisualizer.jsx
        │   └── styles/
        │   └── main.jsx
        ├── package.json
        └── vite.config.js

---

Setup & Running

Step 1 — Format the disk (first time only)

cd backend/Codebase/XFS_Interface
./xfs_interface

Inside XFS shell:

fdisk
exit

Step 2 — Build the C++ engine

cd backend/Codebase/mycodebase
make

This produces the codebase binary.

Step 3 — Start the Node bridge

cd backend
node index.js

You should see:

Server on port 3001
Spawning engine...
✅ Engine ready

Step 4 — Start the React frontend

cd frontend
npm install
npm run dev

Open http://localhost:5173 in your browser.

Important: Always use node index.js, not nodemon. Nodemon watches for file changes and will restart the server when the engine writes to the disk file, breaking active queries.

---

Supported SQL Commands

CREATE & DROP TABLE

CREATE TABLE relname(attr1 STR, attr2 NUM);
DROP TABLE relname;

• Attribute types: STR (string, max 16 chars) or NUM (number)
• Max 18 user relations on disk
• Max 10 relations open at once

SELECT — Print to Console

SELECT * FROM relname;
SELECT col1, col2 FROM relname;
SELECT * FROM relname WHERE attr op value;
SELECT col1, col2 FROM relname WHERE attr op value;

Operators: < <= > >= = !=

SELECT INTO — Save to Relation

SELECT * FROM source INTO target;
SELECT col1, col2 FROM source INTO target;
SELECT * FROM source INTO target WHERE attr op value;
SELECT col1, col2 FROM source INTO target WHERE attr op value;

Saves result into a new relation on disk. Use SELECT * FROM target; to view it.

INSERT

INSERT INTO relname VALUES(val1, val2);

Values must match the relation's attribute types and order. String values don't need quotes.

JOIN

SELECT * FROM rel1 JOIN rel2 INTO target WHERE rel1.attr = rel2.attr;
SELECT col1, col2 FROM rel1 JOIN rel2 INTO target WHERE rel1.attr = rel2.attr;

Equi-join only. Result saved to disk — use SELECT * FROM target; to view.

OPEN & CLOSE TABLE

OPEN TABLE relname;
CLOSE TABLE relname;

Relations are auto-opened before queries. Manual open/close is available but not usually needed.

INDEX (B+ Tree)

CREATE INDEX ON relname.attr;
DROP INDEX ON relname.attr;
PRINT BPLUSTREE relname.attr;

Index must exist before printing. Output shows the tree level by level.

ALTER TABLE

ALTER TABLE RENAME relname TO newname;
ALTER TABLE RENAME COLUMN relname oldattr TO newattr;

Renames a relation or attribute. Does not modify data.

---

Testing Guide

Start with a clean database (run fdisk in XFS Interface if needed). Run each block and verify the expected output.

Block 1 — CREATE TABLE

CREATE TABLE Students(name STR, cgpa NUM);

Expected: ✅ Relation Students created successfully · status bar shows 1/18

CREATE TABLE Students(name STR, cgpa NUM);

Expected: ❌ Relation already exists

---

Block 2 — INSERT

INSERT INTO Students VALUES(Alice, 9);

Expected: ✅ Inserted successfully

INSERT INTO Students VALUES(Bob, 8);

INSERT INTO Students VALUES(Charlie, 7);

INSERT INTO Students VALUES(Dave, 9);

Expected: ✅ all three success

---

Block 3 — SELECT

SELECT * FROM Students;

Expected: table with 4 rows — Alice, Bob, Charlie, Dave

SELECT name FROM Students;

Expected: table with only name column, 4 rows

SELECT * FROM Students WHERE cgpa = 9;

Expected: Alice and Dave only

SELECT * FROM Students WHERE cgpa > 7;

Expected: Alice, Bob, Dave

SELECT name FROM Students WHERE cgpa > 8;

Expected: only name column — Alice and Dave

---

Block 4 — SELECT INTO

SELECT * FROM Students INTO TopStudents WHERE cgpa = 9;

Expected: ✅ Selected successfully · status bar shows 2/18

SELECT * FROM TopStudents;

Expected: Alice and Dave

---

Block 5 — INDEX + BPLUSTREE

CREATE INDEX ON Students.cgpa;

Expected: ✅ success

PRINT BPLUSTREE Students.cgpa;

Expected: B+ Tree Structure block showing LEVEL 0, LEVEL 1 etc.

DROP INDEX ON Students.cgpa;

Expected: ✅ success

---

Block 6 — ALTER TABLE

ALTER TABLE RENAME Students TO Pupils;

Expected: ✅ success

SELECT * FROM Pupils;

Expected: same 4 rows

ALTER TABLE RENAME COLUMN Pupils cgpa TO gpa;

Expected: ✅ success

SELECT * FROM Pupils;

Expected: columns now show name and gpa

---

Block 7 — JOIN

CREATE TABLE Courses(name STR, grade NUM);

INSERT INTO Courses VALUES(Alice, 10);

INSERT INTO Courses VALUES(Bob, 9);

SELECT * FROM Pupils JOIN Courses INTO JoinResult WHERE Pupils.name = Courses.name;

SELECT * FROM JoinResult;

Expected: table with Alice and Bob rows merged

---

Block 8 — Multi-Query

Paste all at once in the editor:

CREATE TABLE T1(x NUM);
INSERT INTO T1 VALUES(1);
INSERT INTO T1 VALUES(2);
SELECT * FROM T1;

Expected: 4 labeled results shown — create success, two inserts, then table with 2 rows

---

Block 9 — DROP TABLE (cleanup)

DROP TABLE TopStudents;
DROP TABLE JoinResult;
DROP TABLE Courses;
DROP TABLE T1;
DROP TABLE Pupils;

Expected: ✅ all success · status bar back to 0/18

---

Block 10 — History + Refresh

1. Click History button — should show all queries run so far
2. Hard refresh Ctrl+Shift+R
3. Click History again — queries should still be there

---

Node Bridge — Key Design Decisions

Persistent Process

The C++ engine spawns once when the Node server starts and stays alive. Commands are written to its stdin and responses read from stdout. This preserves state (open relations, buffer) between queries.

Auto-Flush to Disk

NITCBase only writes data to disk when the process exits (EXIT command). After every write operation (CREATE TABLE, INSERT, DROP TABLE, ALTER TABLE, CREATE/DROP INDEX), the bridge automatically sends EXIT, waits for the engine to restart, then continues. This is invisible to the user.

Auto-Open Relations

NITCBase requires OPEN TABLE before querying. The bridge parses every query, extracts relation names, and auto-opens them. When the 10-relation limit is reached, the least recently used relation is closed (LRU eviction).

Command Queue

All commands are serialized through a queue — only one command runs at a time, preventing concurrent stdin writes from corrupting responses.

Multi-Query Support

Multiple semicolon-separated commands in the editor run sequentially, with flush/respawn handled between write operations automatically.

---

Features

• Dark-themed UI — professional dark interface with syntax-aware monospace editor
• Live status bar — shows engine online/offline, open relation count, total relations on disk
• Query history — persists across page refresh using localStorage, capped at 20 entries
• Multi-query — paste multiple commands separated by semicolons and run all at once
• B+ Tree output — PRINT BPLUSTREE renders in a styled monospace block
• B+ Tree visualizer — interactive demo in the User Manual showing how B+ tree splits and grows
• User Manual — complete syntax reference built into the UI
• Save button — manually triggers disk flush (EXIT + respawn)

---

Known Limitations

• Single-user only — all users share the same disk instance. Multi-user support would require per-session disk isolation, which is not supported by the current NITCBase architecture.
• No UPDATE or DELETE — NITCBase does not support single-record update or delete. Deletion works at the block level only.
• No ORDER BY, LIMIT, COUNT, GROUP BY — not implemented in the engine.
• No multi-table JOIN — only two-relation equi-joins supported.
• String values without quotes — the engine does not support quoted strings; write values directly.
• Disk resets on format — running fdisk + format in XFS Interface wipes all data.

---

How NITCBase Works Internally

NITCBase simulates a disk as a single binary file (Disk/disk). The disk is divided into fixed-size blocks (2048 bytes each). A buffer pool keeps recently accessed blocks in memory. Relations and attributes are stored in two system catalogs — RELATIONCAT and ATTRIBUTECAT — which are always open.

When a relation is opened, its metadata is loaded into the relation cache and attribute cache. Queries use BlockAccess to scan records linearly or via B+ tree index. All changes stay in the buffer until EXIT is called, which triggers the StaticBuffer destructor to flush dirty blocks to disk.

---

Author

Built as a personal project to gain hands-on experience with systems programming, database internals, and full-stack development.