DB

Transactional Database Engine Supporting Atomicity, Consistency, Isolation & Durability (ACID), SQL Queries, Concurrency, B-Tree Indexing, Stored with Pages

Key features:

• SQL query interface
• ACID transactions
• Configurable concurrency control
• BTree-based indexing
• Page-based storage engine
• Interactive CLI shell

Installation

Requirements

• Rust 1.70.0 or newer
• Cargo package manager

Building from Source

# Clone the repository
git clone https://github.com/WillKirkmanM/db.git
cd db

# Build with cargo
cargo build --release

# Run the database
./target/release/db_cli --help

Usage

Command Line Interface

DB provides a CLI with the following options:

Usage: db [OPTIONS] [COMMAND]

Options:
  -d, --data-dir <DATA_DIR>             Directory for database files [default: ./db_data]
  -e, --enable-concurrency              Enable concurrency support
  -l, --lock-timeout-ms <TIMEOUT_MS>    Lock timeout in milliseconds [default: 1000]
  -h, --help                            Show help information
  -V, --version                         Show version information

Commands:
  shell                                 Start interactive SQL shell
  execute <SQL>                         Execute a SQL statement
  help                                  Print help information

Interactive Shell

Start the interactive shell with:

./target/release/db_cli shell

This will start a prompt where you can enter SQL commands:

DB Shell
Enter SQL commands or 'exit' to quit
db> CREATE TABLE users (id INTEGER, name STRING, active BOOLEAN)
Success: 0 row(s) affected
db> 

SQL Support

DB supports the following SQL statements:

Data Definition Language (DDL)

-- Create a new table
CREATE TABLE table_name (
    column1 INTEGER,
    column2 STRING,
    column3 FLOAT,
    column4 BOOLEAN
)

-- Create an index on a column
CREATE INDEX ON table_name(column_name)

Data Manipulation Language (DML)

-- Insert data
INSERT INTO table_name VALUES (1, "hello", 3.14, true)

-- Select data
SELECT * FROM table_name
SELECT * FROM table_name WHERE column1 = 1
SELECT * FROM table_name WHERE column2 = "hello" AND column4 = true

-- Update data
UPDATE table_name SET column1 = 42 WHERE column2 = "hello"

-- Delete data
DELETE FROM table_name WHERE column1 = 42

Transaction Control

-- Start a transaction
BEGIN TRANSACTION

-- Commit changes
COMMIT

-- Rollback changes
ROLLBACK

Data Types

DB supports the following data types:

• INTEGER: 64-bit signed integers
• FLOAT: 64-bit floating point numbers
• STRING: UTF-8 encoded text
• BOOLEAN: true/false values
• NULL: null values

Concurrency Control

DB uses a lock-based concurrency control system with deadlock prevention. You can enable concurrency support with the --enable-concurrency flag.

The lock manager supports:

• Shared (read) locks
• Exclusive (write) locks
• Configurable lock timeouts
• Automatic deadlock prevention

Storage Engine

The storage engine uses a page-based approach:

• Fixed-size pages (4KB default)
• B-Tree indexes for efficient lookups
• Serialised with bincode for efficient binary encoding
• File-based persistence

Architecture

DB is built with a layered architecture:

1. SQL Interface: Parses and executes SQL statements
2. Transaction Manager: Ensures ACID compliance
3. Concurrency Manager: Handles concurrent access
4. Index Manager: Manages B-Tree indexes for fast lookups
5. Storage Engine: Handles persistent storage and retrieval

Example Session

$ ./target/release/db_cli -e shell
Starting database with concurrency enabled (timeout: 1000ms)
DB Shell
Enter SQL commands or 'exit' to quit

db> CREATE TABLE users (id INTEGER, name STRING, active BOOLEAN)
Success: 0 row(s) affected

db> INSERT INTO users VALUES (1, "Name1", true)
Success: 1 row(s) affected

db> INSERT INTO users VALUES (2, "Name2", false)
Success: 1 row(s) affected

db> SELECT * FROM users
id | name | active
---+------+-------
Integer(1) | String("Name1") | Boolean(true)
Integer(2) | String("Name2") | Boolean(false)
2 row(s) returned

db> CREATE INDEX ON users(id)
Success: 0 row(s) affected

db> UPDATE users SET active = true WHERE id = 2
Success: 1 row(s) affected

db> SELECT * FROM users WHERE active = true
id | name | active
---+------+-------
Integer(1) | String("Name1") | Boolean(true)
Integer(2) | String("Name2") | Boolean(true)
2 row(s) returned

db> exit