Janelle [WIP]
Janelle (pronounced ya-nel) is a toy SQL database written in Java. The goal of this project is to learn how databases work by building one.
Features
- CLI: User interface for querying the DB.
- Client & Server: A simple TCP-based protocol for communication between the CLI and DB server.
- SQL Parser: For parsing a flavor of SQL called NickSQL (pronounced Nick's QL). Generates abstract syntax trees for basic SQL queries (see Queries section below for more info).
- B+ Tree: Data structure for DB tables.
Demo
...
Requirements
Java 17+
Setup
- Clone repo.
- Build
./gradlew build
. - Run server
./gradlew runServer --console=plain
. - Run client
./gradlew runClient --console=plain
. - Run tests
./gradlew test
.
Queries
-- create table
create table customers (
name string(50) required,
email string(25) nullable,
num_orders int default 0,
voucher_balance float default 0,
has_premium_plan bool default false
);
-- read table
select *
from customers;
describe customers;
-- update table
alter table customers
add column phone_number string;
alter table customers
rename column phone_number to phone;
alter table customers
drop column phone_number;
-- delete table
drop table customers;
-- create rows
insert into customers (name, email, has_premium_plan)
values ("Janelle", "ja@nel.le", true);
-- read rows
select name, email, num_orders
from customers
where (num_orders > 0 and num_orders <= 5) or voucher_balance = 50.00 or (email = "ja@nel.le" and name != null and has_premium_plan = true)
order by num_orders desc;
-- update rows
update customers
set name = "Milan", email = "mi@la.no"
where email = "ja@nel.le";
-- delete rows
delete from customers
where email = "mi@la.no";
CLI commands
.EXIT
: Exit CLI..PING
: Test connection to DB server..TABLES
: List tables in the DB..COLUMNS
: List columns in a table with.COLUMNS {table_name}
e.g.COLUMNS jn_configs
..GENERATE
: Create table and populate it with sample data. Usage -.GENERATE {number_of_records}
e.g.GENERATE 100
.
Not implemented
Things I'd have liked to implement:
- Indexes for faster reads.
- Paging and a shared buffer (i.e. in-memory cache) for minimizing disk hits and thus read-write latency.
- Write-ahead log (WAL) and a check-pointer for crash recovery and data replication.
- Table joins.
- Primary, foreign and unique key constraints.
- Aggregates (like count, sum and average), and other functions.
- Transactions using two-phase locking.