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", "[email protected]", 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 = "[email protected]" and name != null and has_premium_plan = true)
  order by num_orders desc;

  -- update rows
  update customers
  set name = "Milan", email = "[email protected]"
  where email = "[email protected]";

  -- delete rows
  delete from customers
  where email = "[email protected]";

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.