Skip to content

Latest commit

 

History

History
88 lines (71 loc) · 2.56 KB

README.md

File metadata and controls

88 lines (71 loc) · 2.56 KB
Raw

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.