Functional Scope (In-Scope)
- Schema Definitions: Build table schema catalogs mapping column names to strict type rules.
- Type-Checked Inserts: Validate incoming row fields against table types before completing inserts.
- Filtered Query Executors: Perform SELECT selections supporting WHERE filters, sorted ORDER BY constraints, and row LIMIT values.
- Transactional Undo Logs: Track row mutations to support basic transaction scopes (BEGIN, COMMIT, ROLLBACK).
Explicit Boundaries (Out-of-Scope)
- No High Disk File Serialization: Bypasses persisting data pages to disk or maintaining WAL logs.
- No Complex SQL Joins (Hash/Merge Joins): Excludes foreign key relationships, multi-table joins, or sub-query parsers.
Clean reference designs demonstrating SQL executors in Java and Python:
// ─── JAVA BLUEPRINT ──────────────────────────────────────────────────────────
import java.util.*;
import java.util.concurrent.*;
import java.util.concurrent.locks.*;
import java.util.function.Predicate;
enum ColumnType { INTEGER, STRING }
class Column {
private final String name;
private final ColumnType type;
private final boolean isPrimaryKey;
public Column(String name, ColumnType type, boolean isPrimaryKey) {
this.name = name;
this.type = type;
this.isPrimaryKey = isPrimaryKey;
}
public String getName() { return name; }
public ColumnType getType() { return type; }
public boolean isPrimaryKey() { return isPrimaryKey; }
}
class Table {
private final String name;
private final Map<String, Column> schema = new LinkedHashMap<>();
private final List<Map<String, Object>> rows = new ArrayList<>();
private final Map<String, Map<Object, List<Map<String, Object>>>> indexes = new ConcurrentHashMap<>();
private final ReentrantReadWriteLock rwLock = new ReentrantReadWriteLock();
private String primaryKeyColumn = null;
public Table(String name) {
this.name = name;
}
public void addColumn(String name, ColumnType type, boolean isPrimaryKey) {
rwLock.writeLock().lock();
try {
Column col = new Column(name, type, isPrimaryKey);
schema.put(name, col);
if (isPrimaryKey) {
this.primaryKeyColumn = name;
// Initialize primary key index
indexes.put(name, new ConcurrentHashMap<>());
}
} finally {
rwLock.writeLock().unlock();
}
}
public void insert(Map<String, Object> row) {
rwLock.writeLock().lock();
try {
validateRow(row);
// Enforce Primary Key uniqueness
if (primaryKeyColumn != null) {
Object pkVal = row.get(primaryKeyColumn);
if (pkVal == null) throw new IllegalArgumentException("Primary Key value cannot be null.");
Map<Object, List<Map<String, Object>>> pkIndex = indexes.get(primaryKeyColumn);
if (pkIndex.containsKey(pkVal)) {
throw new IllegalArgumentException("Duplicate Primary Key exception for value: " + pkVal);
}
}
// Perform insertion
Map<String, Object> rowCopy = new HashMap<>(row);
rows.add(rowCopy);
// Update indices
for (String indexCol : indexes.keySet()) {
Object val = row.get(indexCol);
if (val != null) {
indexes.get(indexCol).computeIfAbsent(val, k -> new CopyOnWriteArrayList<>()).add(rowCopy);
}
}
} finally {
rwLock.writeLock().unlock();
}
}
public void delete(Predicate<Map<String, Object>> predicate) {
rwLock.writeLock().lock();
try {
Iterator<Map<String, Object>> iterator = rows.iterator();
while (iterator.hasNext()) {
Map<String, Object> row = iterator.next();
if (predicate.test(row)) {
iterator.remove();
// Update index maps
for (String indexCol : indexes.keySet()) {
Object val = row.get(indexCol);
if (val != null) {
List<Map<String, Object>> indexedRows = indexes.get(indexCol).get(val);
if (indexedRows != null) {
indexedRows.remove(row);
if (indexedRows.isEmpty()) {
indexes.get(indexCol).remove(val);
}
}
}
}
}
}
} finally {
rwLock.writeLock().unlock();
}
}
public List<Map<String, Object>> select(List<String> projection,
Predicate<Map<String, Object>> predicate,
Comparator<Map<String, Object>> orderComparator,
int limit) {
rwLock.readLock().lock();
try {
List<Map<String, Object>> matched = new ArrayList<>();
for (Map<String, Object> row : rows) {
if (predicate == null || predicate.test(row)) {
matched.add(row);
}
}
if (orderComparator != null) {
matched.sort(orderComparator);
}
List<Map<String, Object>> result = new ArrayList<>();
int count = 0;
for (Map<String, Object> row : matched) {
if (limit > 0 && count >= limit) break;
Map<String, Object> projected = new LinkedHashMap<>();
for (String col : projection) {
if (schema.containsKey(col)) {
projected.put(col, row.get(col));
}
}
result.add(projected);
count++;
}
return result;
} finally {
rwLock.readLock().unlock();
}
}
private void validateRow(Map<String, Object> row) {
for (Map.Entry<String, Column> entry : schema.entrySet()) {
String colName = entry.getKey();
Column col = entry.getValue();
Object value = row.get(colName);
if (value == null && col.isPrimaryKey()) {
throw new IllegalArgumentException("Primary Key column " + colName + " cannot be null.");
}
if (value != null) {
if (col.getType() == ColumnType.INTEGER && !(value instanceof Integer)) {
throw new IllegalArgumentException("Invalid type for column " + colName + ". Expected Integer.");
}
if (col.getType() == ColumnType.STRING && !(value instanceof String)) {
throw new IllegalArgumentException("Invalid type for column " + colName + ". Expected String.");
}
}
}
}
public List<Map<String, Object>> getRawRows() {
rwLock.readLock().lock();
try {
List<Map<String, Object>> copies = new ArrayList<>();
for (Map<String, Object> r : rows) {
copies.add(new HashMap<>(r));
}
return copies;
} finally {
rwLock.readLock().unlock();
}
}
public void restoreRows(List<Map<String, Object>> backup) {
rwLock.writeLock().lock();
try {
rows.clear();
for (Map<String, Object> indexMap : indexes.values()) {
indexMap.clear();
}
for (Map<String, Object> r : backup) {
insert(r);
}
} finally {
rwLock.writeLock().unlock();
}
}
}
// Transaction Session Handler
class Transaction {
private final Database db;
private final Map<String, List<Map<String, Object>>> snapshots = new HashMap<>();
private boolean active = false;
public Transaction(Database db) {
this.db = db;
}
public void begin() {
if (active) throw new IllegalStateException("Transaction already in progress.");
active = true;
snapshots.clear();
for (String tableName : db.getTableNames()) {
Table t = db.getTable(tableName);
snapshots.put(tableName, t.getRawRows());
}
System.out.println("[Tx] Transaction started. Captured catalog snapshot.");
}
public void commit() {
if (!active) throw new IllegalStateException("No active transaction to commit.");
active = false;
snapshots.clear();
System.out.println("[Tx] Transaction committed successfully.");
}
public void rollback() {
if (!active) throw new IllegalStateException("No active transaction to rollback.");
active = false;
for (Map.Entry<String, List<Map<String, Object>>> entry : snapshots.entrySet()) {
Table t = db.getTable(entry.getKey());
if (t != null) {
t.restoreRows(entry.getValue());
}
}
snapshots.clear();
System.out.println("[Tx] Transaction rolled back. Restored original snapshots.");
}
}
class Database {
private final Map<String, Table> tables = new ConcurrentHashMap<>();
public void createTable(String name) {
tables.putIfAbsent(name.toUpperCase(), new Table(name));
}
public Table getTable(String name) {
return tables.get(name.toUpperCase());
}
public Set<String> getTableNames() {
return tables.keySet();
}
}
// Complete Simulation
public class Main {
public static void main(String[] args) {
Database db = new Database();
// 1. Create table and define schema
db.createTable("Users");
Table users = db.getTable("Users");
users.addColumn("id", ColumnType.INTEGER, true);
users.addColumn("name", ColumnType.STRING, false);
users.addColumn("age", ColumnType.INTEGER, false);
// 2. Perform safe type-checked insertions
Map<String, Object> r1 = new HashMap<>();
r1.put("id", 1); r1.put("name", "Alice"); r1.put("age", 25);
users.insert(r1);
Map<String, Object> r2 = new HashMap<>();
r2.put("id", 2); r2.put("name", "Bob"); r2.put("age", 30);
users.insert(r2);
Map<String, Object> r3 = new HashMap<>();
r3.put("id", 3); r3.put("name", "Charlie"); r3.put("age", 22);
users.insert(r3);
// 3. Select with WHERE filter, ORDER BY, and LIMIT
System.out.println("--- Querying Users: age >= 24 ---");
List<Map<String, Object>> result = users.select(
Arrays.asList("id", "name", "age"),
row -> (Integer) row.get("age") >= 24,
(rowA, rowB) -> ((Integer) rowB.get("age")).compareTo((Integer) rowA.get("age")), // Sort age DESC
10
);
for (Map<String, Object> row : result) {
System.out.println(row);
}
// 4. Transaction simulation with ROLLBACK
Transaction tx = new Transaction(db);
tx.begin();
try {
Map<String, Object> r4 = new HashMap<>();
r4.put("id", 4); r4.put("name", "David"); r4.put("age", 28);
users.insert(r4);
System.out.println("Inserted row inside transaction.");
System.out.println("Current count: " + users.getRawRows().size());
// Force a rollback to test logic
throw new RuntimeException("Simulating checkout cancellation");
} catch (Exception e) {
System.out.println("Encountered exception: " + e.getMessage() + ". Performing rollback.");
tx.rollback();
}
System.out.println("Count after rollback: " + users.getRawRows().size());
}
}