Title: Building a Basic Relational Database Engine with Test-Driven Development
Objective: In this assignment, you will learn how to design and implement a simple relational database engine using JavaScript and Test-Driven Development (TDD).
Overview:
- Creating tables
- Inserting records
- Querying records
- Updating records
- Deleting records
- Support for data types and type constraints
- Support for unique constraints and other types of constraints
- Indexes for faster query execution
- Joins for querying related records from multiple tables
- Transactions for handling multiple operations atomically
- A query language (like SQL) and parser for executing queries and commands
- Persistence to store the data on disk
Before you start: Make sure you understand the basics of TDD. Write a test for each feature before writing the code to implement that feature.
Task 1: Creating Tables
Test case 1.1: Create a test that checks if a new table can be created with a specified name and schema.
const databaseEngine = new DatabaseEngine();
databaseEngine.createTable("users", {
id: "integer",
name: "string",
age: "integer"
});
const table = databaseEngine.getTable("users");
assert(table !== undefined, "Table 'users' should exist");
Task 2: Inserting Records
Test case 2.1: Create a test that checks if a record can be inserted into a table.
const users = databaseEngine.getTable("users");
const record = { id: 1, name: "Alice", age: 30 };
users.insert(record);
const retrievedRecord = users.getRecord(1);
assert.deepStrictEqual(retrievedRecord, record, "Inserted record should match the retrieved record");
Task 3: Querying Records
Test case 3.1: Create a test that checks if records can be queried based on a single condition.
const users = databaseEngine.getTable("users");
users.insert({ id: 1, name: "Alice", age: 30 });
users.insert({ id: 2, name: "Bob", age: 25 });
const results = users.query({ age: { $gt: 28 } });
assert(results.length === 1, "There should be one result");
assert(results[0].name === "Alice", "The result should be Alice");
Task 4: Updating Records
Test case 4.1: Update a single record
const users = databaseEngine.getTable("users");
users.insert({ id: 1, name: "Alice", age: 30 });
users.update({ id: 1 }, { name: "Alicia" });
const updatedRecord = users.getRecord(1);
assert(updatedRecord.name === "Alicia", "The updated name should be 'Alicia'");
Test case 4.2: Update multiple records
const users = databaseEngine.getTable("users");
users.insert({ id: 1, name: "Alice", age: 30 });
users.insert({ id: 2, name: "Bob", age: 25 });
users.update({ age: { $gt: 20 } }, { age: 40 });
const results = users.query({ age: 40 });
assert(results.length === 2, "Two records should be updated");
Task 5: Deleting Records
Test case 5.1: Delete a single record
const users = databaseEngine.getTable("users");
users.insert({ id: 1, name: "Alice", age: 30 });
users.delete({ id: 1 });
const deletedRecord = users.getRecord(1);
assert(deletedRecord === undefined, "The record should be deleted");
Test case 5.2: Delete multiple records
const users = databaseEngine.getTable("users");
users.insert({ id: 1, name: "Alice", age: 30 });
users.insert({ id: 2, name: "Bob", age: 25 });
users.delete({ age: { $lt: 30 } });
const results = users.query({ age: { $lt: 30 } });
assert(results.length === 0, "All records with age < 30 should be deleted");
Task 6: Support for Data Types and Type Constraints
Test case 6.1: Check data types
const users = databaseEngine.getTable("users");
assert.throws(() => {
users.insert({ id: "one", name: "Alice", age: 30 });
}, "Inserting a record with the wrong data type should throw an error");
Test case 6.2: Check type constraints (e.g., non-null constraint)
const users = databaseEngine.getTable("users");
assert.throws(() => {
users.insert({ id: null, name: "Alice", age: 30 });
}, "Inserting a record with a null value for a non-null constraint should throw an error");
Task 7: Support for Unique Constraints and Other Types of Constraints
Test case 7.1: Check unique constraints
const users = databaseEngine.getTable("users");
users.insert({ id: 1, name: "Alice", age: 30 });
assert.throws(() => {
users.insert({ id: 1, name: "Bob", age: 25 });
}, "Inserting a record with a duplicate unique value should throw an error");
Test case 7.2: Check other types of constraints (e.g., check constraint)
const users = databaseEngine.getTable("users");
assert.throws(() => {
users.insert({ id: 1, name: "Alice", age: -5 });
}, "Inserting a record that violates a check constraint should throw an error");
Task 8: Indexes for Faster Query Execution
Test case 8.1: Create index on a table
const users = databaseEngine.getTable("users");
users.createIndex("age_index", "age");
const index = users.getIndex("age_index");
assert(index !== undefined, "The index 'age_index' should exist");
Test case 8.2: Query with index
const users = databaseEngine.getTable("users");
users.insert({ id: 1, name: "Alice", age: 30 });
users.insert({ id: 2, name: "Bob", age: 25 });
users.createIndex("age_index", "age");
const startTime = performance.now();
const results = users.query({ age: { $gt: 28 } });
const endTime = performance.now();
const elapsedTime = endTime - startTime;
assert(results.length === 1, "There should be one result");
assert(results[0].name === "Alice", "The result should be Alice");
assert(elapsedTime < 50, "The query should execute faster with an index");
Task 9: Joins for Querying Related Records from Multiple Tables
Test case 9.1: Perform inner join
const users = databaseEngine.getTable("users");
const orders = databaseEngine.getTable("orders");
users.insert({ id: 1, name: "Alice", age: 30 });
orders.insert({ id: 1, user_id: 1, total: 50 });
const results = databaseEngine.innerJoin(users, orders, "id", "user_id");
assert(results.length === 1, "There should be one result");
assert(results[0].user_name === "Alice", "The result should have a 'user_name' field with value 'Alice'");
Test case 9.2: Perform outer join
const users = databaseEngine.getTable("users");
const orders = databaseEngine.getTable("orders");
users.insert({ id: 1, name: "Alice", age: 30 });
orders.insert({ id: 1, user_id: 1, total: 50 });
orders.insert({ id: 2, user_id: 2, total: 30 });
const results = databaseEngine.outerJoin(users, orders, "id", "user_id");
assert(results.length === 2, "There should be two results");
assert(results[1].user_name === undefined, "The second result should have an undefined 'user_name' field");
Task 10: Transactions for Handling Multiple Operations Atomically
Test case 10.1: Start and commit a transaction
const users = databaseEngine.getTable("users");
databaseEngine.beginTransaction();
users.insert({ id: 1, name: "Alice", age: 30 });
users.insert({ id: 2, name: "Bob", age: 25 });
databaseEngine.commit();
const results = users.query({});
assert(results.length === 2, "There should be two records after committing the transaction");
Test case 10.2: Start and rollback a transaction
const users = databaseEngine.getTable("users");
databaseEngine.beginTransaction();
users.insert({ id: 1, name: "Alice", age: 30 });
users.insert({ id: 2, name: "Bob", age: 25 });
databaseEngine.rollback();
const results = users.query({});
assert(results.length === 0, "There should be no records after rolling back the transaction");
Task 11: A Query Language (like SQL) and Parser for Executing Queries and Commands
Test case 11.1: Execute a valid SQL query
const sql = "SELECT * FROM users WHERE age > 28;";
const results = databaseEngine.execute(sql);
assert(results.length === 1, "There should be one result");
assert(results[0].name === "Alice", "The result should be Alice");
Test case 11.2: Execute a valid SQL command
const sql = "INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30);";
databaseEngine.execute(sql);
const insertedRecord = databaseEngine.getTable("users").getRecord(1);
assert(insertedRecord.name === "Alice", "The inserted record should have the name 'Alice'");
Task 12: Persistence to Store the Data on Disk
Test case 12.1: Save the database state to disk
const users = databaseEngine.getTable("users");
users.insert({ id: 1, name: "Alice", age: 30 });
const saveResult = databaseEngine.saveToFile("database_state.json");
assert(saveResult, "The save operation should be successful");
Test case 12.2: Load the database state from disk
const newDatabaseEngine = new DatabaseEngine();
newDatabaseEngine.loadFromFile("database_state.json");
const users = newDatabaseEngine.getTable("users");
const alice = users.getRecord(1);
assert(alice !== undefined, "The 'users' table should have a record with id 1 after loading");
assert(alice.name === "Alice", "The record should have the name 'Alice'");
After completing all tasks, you should have a working basic relational database engine implemented using JavaScript and Test-Driven Development methodology. Remember to refactor your code and improve test coverage as needed to ensure a robust and maintainable implementation.