Database Server

Database Server: Query Execution & Storage

DatabaseServer is a complete database management system implementation built on ConnectionServer. It provides query execution, pluggable storage engines, and MySQL-compatible query parsing.

Position in Architecture

┌──────────────────────────────────────────────────────┐
│  DatabaseServer (Query coordination)                 │  ← YOU ARE HERE
│    ├─ DatabaseEngine (Query execution)               │
│    └─ StorageEngine (Persistence)                    │
│         ├─ JsonStorageEngine                         │
│         ├─ XmlStorageEngine                          │
│         └─ Custom implementations                    │
├──────────────────────────────────────────────────────┤
│  ConnectionServer (TCP transport)                    │
├──────────────────────────────────────────────────────┤
│  Server (abstract base)                              │
└──────────────────────────────────────────────────────┘

Key Files:

connection/database/DatabaseServer.java - Server orchestration
connection/database/DatabaseEngine.java - Query execution
connection/database/StorageEngine.java - Persistence interface
connection/database/json/JsonStorageEngine.java - JSON persistence
connection/database/xml/XmlStorageEngine.java - XML persistence
connection/database/mysql/MySqlServer.java - MySQL implementation

Quick Start

Basic Database Server

import jsi.connection.database.*;
import jsi.connection.database.json.JsonStorageEngine;
import jsi.connection.database.mysql.MySqlServer;

public class MyDatabaseServer {
    public static void main(String[] args) {
        // 1. Create storage engine
        StorageEngine storage = new JsonStorageEngine("./database");
        
        // 2. Create database engine
        DatabaseEngine engine = new DatabaseEngine(storage);
        
        // 3. Create and start server
        DatabaseServer server = new MySqlServer(3306, engine);
        
        System.out.println("Database server started on port 3306");
        server.start();
    }
}

This creates a MySQL-compatible database server with JSON file persistence.

Three-Component Architecture

The database system is split into three independent components:

1. DatabaseServer - Connection Manager

public abstract class DatabaseServer extends ConnectionServer {
    
    private DatabaseEngine databaseEngine;

    public DatabaseServer(int port, DatabaseEngine databaseEngine) {
        super(port);
        this.databaseEngine = databaseEngine;
    }

    @Override
    public QueryResult handleRequest(Request query) {
        if (query instanceof Query q) {
            return databaseEngine.execute(q);
        }
        throw new IllegalArgumentException("Invalid request type");
    }
}

Responsibilities:

Accept TCP connections (inherited from ConnectionServer)
Parse incoming requests into Query objects
Delegate execution to DatabaseEngine
Return QueryResult to client

Key insight: DatabaseServer knows nothing about query execution or storage - it's pure orchestration.

2. DatabaseEngine - Query Executor

public abstract class DatabaseEngine {
    
    private StorageEngine storageEngine;
    
    public DatabaseEngine(StorageEngine storageEngine) { 
        this.storageEngine = storageEngine; 
    }
    
    public QueryResult execute(Query query) {
        OperationType type = query.getQueryType().getOperationType();
        String collection = query.getTargetCollection();
        QueryCondition condition = query.getCondition();
        
        try {
            if (type == OperationType.READ) {
                List<List<Field>> results = storageEngine.read(collection, condition);
                return new QueryResult(true, "Read successful", results);
            } 
            else if (type == OperationType.CREATE || type == OperationType.UPDATE) {
                storageEngine.write(collection, query.getAffectedFields());
                return new QueryResult(true, "Write successful", null);
            } 
            else if (type == OperationType.DELETE) {
                storageEngine.delete(collection, condition);
                return new QueryResult(true, "Delete successful", null);
            }
            
            return new QueryResult(false, "Unsupported operation", null);
        } catch (Exception e) {
            return new QueryResult(false, "Operation failed: " + e.getMessage(), null);
        }
    }
    
    public abstract void beginTransaction();
    public abstract void commit();
    public abstract void rollback();
}

Responsibilities:

Map query types (SELECT, INSERT, etc.) to storage operations
Transaction management (abstract - subclass responsibility)
Error handling and result formatting

Key insight: DatabaseEngine knows nothing about how data is stored - it delegates to StorageEngine.

3. StorageEngine - Persistence Layer

public abstract class StorageEngine {
    
    protected String storagePath;
    
    public StorageEngine(String storagePath) { 
        this.storagePath = storagePath; 
    }
    
    // Core CRUD operations
    public abstract void write(String collection, List<Field> data) 
        throws IOException;
    
    public abstract List<List<Field>> read(String collection, QueryCondition condition) 
        throws IOException;
    
    public abstract void delete(String collection, QueryCondition condition) 
        throws IOException;
    
    // Format conversion (implementation-specific)
    protected abstract String fieldToString(Field field);
    protected abstract String recordToString(List<Field> fields);
    protected abstract String recordsToArray(List<List<Field>> records);
    protected abstract List<List<Field>> arrayToRecords(String array);
}

Responsibilities:

Read/write data from/to persistent storage
Format conversion (objects ↔ storage format)
Query condition evaluation (filtering)

Key insight: StorageEngine knows nothing about query syntax or database semantics - it's pure storage.

Data Model

Field: Column Data

File: connection/database/Field.java

public class Field {
    private String name;      // Column name
    private Object value;     // Column value
    
    public Field(String name, Object value) {
        this.name = name;
        this.value = value;
    }
    
    public String getName() { return name; }
    public Object getValue() { return value; }
}

Example:

Field idField = new Field("id", 123);
Field nameField = new Field("name", "Alice");
Field emailField = new Field("email", "[email protected]");

Record: Row Data

A record is a list of Field objects representing one row:

List<Field> record = Arrays.asList(
    new Field("id", 1),
    new Field("name", "Alice"),
    new Field("email", "[email protected]")
);

Collection: Table Data

A collection (table) is a list of records:

List<List<Field>> users = Arrays.asList(
    Arrays.asList(
        new Field("id", 1),
        new Field("name", "Alice"),
        new Field("email", "[email protected]")
    ),
    Arrays.asList(
        new Field("id", 2),
        new Field("name", "Bob"),
        new Field("email", "[email protected]")
    )
);

Query Model

Query Interface

File: connection/database/query/Query.java

public abstract class Query implements Request {
    
    private String rawQuery;
    
    public Query(String rawQuery) { this.rawQuery = rawQuery; }
    
    // Query components (parsed from rawQuery)
    public abstract QueryType getQueryType();           // SELECT, INSERT, UPDATE, DELETE
    public abstract String getTargetCollection();       // Table name
    public abstract List<Field> getAffectedFields();    // Fields to insert/update
    public abstract QueryCondition getCondition();      // WHERE clause
    
    @Override
    public String serialize() { return rawQuery; }
}

QueryType: Operation Classification

File: connection/database/query/QueryType.java

public interface QueryType {
    
    enum OperationType {
        CREATE,   // INSERT
        READ,     // SELECT
        UPDATE,   // UPDATE
        DELETE    // DELETE
    }
    
    OperationType getOperationType();
}

QueryCondition: WHERE Clause

File: connection/database/query/QueryCondition.java

public class QueryCondition {
    
    // Simple condition: field = value
    private String fieldName;
    private Object value;
    
    // Complex condition: (cond1 AND cond2) OR cond3
    private LogicalOperator logicalOperator;  // AND, OR
    private List<QueryCondition> subConditions;
    
    public boolean isSimpleCondition() { 
        return fieldName != null && value != null; 
    }
    
    public boolean isComplexCondition() { 
        return logicalOperator != null && subConditions != null; 
    }
    
    public enum LogicalOperator { AND, OR }
}

Examples:

// Simple: WHERE name = 'Alice'
QueryCondition simple = new QueryCondition("name", "Alice");

// Complex: WHERE age > 18 AND city = 'NYC'
QueryCondition complex = new QueryCondition(
    QueryCondition.LogicalOperator.AND,
    Arrays.asList(
        new QueryCondition("age", 18),
        new QueryCondition("city", "NYC")
    )
);

QueryResult: Response

File: connection/database/query/QueryResult.java

public class QueryResult implements Response {
    
    private boolean success;
    private String message;
    private List<List<Field>> data;  // Result rows (null for non-SELECT queries)
    
    public QueryResult(boolean success, String message, List<List<Field>> data) {
        this.success = success;
        this.message = message;
        this.data = data;
    }
    
    public boolean isSuccess() { return success; }
    public String getMessage() { return message; }
    public List<List<Field>> getData() { return data; }
    
    @Override
    public String serialize() {
        // Convert to string format for transmission
    }
}

Storage Engines

JsonStorageEngine

File: connection/database/json/JsonStorageEngine.java

Stores data in JSON files, one file per collection.

File format example (database/table_users.json):

[
  {"id": "1", "name": "Alice", "email": "[email protected]"},
  {"id": "2", "name": "Bob", "email": "[email protected]"},
  {"id": "3", "name": "Charlie", "email": "[email protected]"}
]

Key methods:

public class JsonStorageEngine extends StorageEngine {
    
    @Override
    protected String fieldToString(Field field) {
        // Converts: Field("name", "Alice") → "name":"Alice"
        // Handles escaping: " → \", \ → \\, \n → \\n
    }
    
    @Override
    protected String recordToString(List<Field> fields) {
        // Converts: [Field("id", 1), Field("name", "Alice")]
        //        → {"id": 1, "name": "Alice"}
    }
    
    @Override
    protected String recordsToArray(List<List<Field>> records) {
        // Converts: List of records → JSON array string
    }
    
    @Override
    protected List<List<Field>> arrayToRecords(String jsonArray) {
        // Parses JSON array string → List of Field lists
        // Custom parser (no external JSON library)
    }
}

Usage:

StorageEngine storage = new JsonStorageEngine("./database");

// Write operation creates/updates file
storage.write("users", Arrays.asList(
    new Field("id", "1"),
    new Field("name", "Alice")
));

// Read operation loads from file
List<List<Field>> results = storage.read("users", null);

XmlStorageEngine

File: connection/database/xml/XmlStorageEngine.java

Stores data in XML files, one file per collection.

File format example (database/table_users.xml):

<?xml version="1.0" encoding="UTF-8"?>
<collection>
  <record>
    <id>1</id>
    <name>Alice</name>
    <email>[email protected]</email>
  </record>
  <record>
    <id>2</id>
    <name>Bob</name>
    <email>[email protected]</email>
  </record>
</collection>

Key methods:

public class XmlStorageEngine extends StorageEngine {
    
    @Override
    protected String fieldToString(Field field) {
        // Converts: Field("name", "Alice") → <name>Alice</name>
        // Handles escaping: < → &lt;, > → &gt;, & → &amp;
    }
    
    @Override
    protected String recordToString(List<Field> fields) {
        // Converts: List of Fields → <record>...</record>
    }
    
    @Override
    protected String recordsToArray(List<List<Field>> records) {
        // Converts: List of records → XML document
    }
    
    @Override
    protected List<List<Field>> arrayToRecords(String xmlArray) {
        // Parses XML document → List of Field lists
        // Custom parser (no external XML library)
    }
}

Usage:

StorageEngine storage = new XmlStorageEngine("./database");

// Write operation creates/updates XML file
storage.write("users", Arrays.asList(
    new Field("id", "1"),
    new Field("name", "Alice")
));

// Read operation parses XML file
List<List<Field>> results = storage.read("users", null);

Implementing Custom Storage Engines

Create your own storage backend:

public class PostgresStorageEngine extends StorageEngine {
    
    private Connection dbConnection;
    
    public PostgresStorageEngine(String jdbcUrl) {
        super(jdbcUrl);
        // Initialize JDBC connection
    }
    
    @Override
    public void write(String collection, List<Field> data) throws IOException {
        // INSERT INTO collection (field1, field2) VALUES (?, ?)
    }
    
    @Override
    public List<List<Field>> read(String collection, QueryCondition condition) {
        // SELECT * FROM collection WHERE ...
    }
    
    @Override
    public void delete(String collection, QueryCondition condition) {
        // DELETE FROM collection WHERE ...
    }
    
    // Format methods not needed for SQL backends
    @Override
    protected String fieldToString(Field field) { return null; }
    @Override
    protected String recordToString(List<Field> fields) { return null; }
    @Override
    protected String recordsToArray(List<List<Field>> records) { return null; }
    @Override
    protected List<List<Field>> arrayToRecords(String array) { return null; }
}

MySQL Implementation

MySqlServer

File: connection/database/mysql/MySqlServer.java

public class MySqlServer extends DatabaseServer {
    
    public MySqlServer(int port, DatabaseEngine databaseEngine) { 
        super(port, databaseEngine); 
    }

    @Override
    protected Request parseRequest(String input) {
        // Parse MySQL query syntax
        // TODO: Implementation in progress
        throw new UnsupportedOperationException("Unimplemented method 'parseRequest'");
    }
}

Current status: Partial implementation. Query parsing in development.

MySqlQuery

File: connection/database/mysql/query/MySqlQuery.java

Parses MySQL-specific SQL syntax:

public class MySqlQuery extends Query {
    
    public MySqlQuery(String rawQuery) {
        super(rawQuery);
        parseQuery(rawQuery);
    }
    
    private void parseQuery(String sql) {
        // Extract query type (SELECT, INSERT, UPDATE, DELETE)
        // Extract table name
        // Extract fields and conditions
        // Uses regex-based parsing
    }
    
    @Override
    public QueryType getQueryType() { /* ... */ }
    
    @Override
    public String getTargetCollection() { /* ... */ }
    
    @Override
    public List<Field> getAffectedFields() { /* ... */ }
    
    @Override
    public QueryCondition getCondition() { /* ... */ }
}

Example queries:

SELECT * FROM users WHERE id = 123
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')
UPDATE users SET email = '[email protected]' WHERE id = 123
DELETE FROM users WHERE id = 123

MySQL Parsers

Supporting classes:

MySqlConditionParser.java - Parses WHERE clauses
MySqlFieldsExtractor.java - Extracts field names/values
MySqlTableNameExtractor.java - Extracts table names
MySqlValueParser.java - Parses SQL values

Complete Example

Full Database Server Application

import jsi.connection.database.*;
import jsi.connection.database.json.JsonStorageEngine;
import jsi.connection.database.mysql.MySqlServer;
import jsi.connection.database.query.*;

public class DatabaseExample {
    public static void main(String[] args) throws Exception {
        // 1. Create storage (JSON files in ./database directory)
        StorageEngine storage = new JsonStorageEngine("./database");
        
        // 2. Create database engine
        DatabaseEngine engine = new DatabaseEngine(storage);
        
        // 3. Initialize sample data
        createUsersTable(storage);
        
        // 4. Start server
        DatabaseServer server = new MySqlServer(3306, engine);
        System.out.println("Database server running on port 3306");
        server.start();
    }
    
    private static void createUsersTable(StorageEngine storage) throws Exception {
        // Create users table with initial data
        List<List<Field>> users = Arrays.asList(
            Arrays.asList(
                new Field("id", "1"),
                new Field("name", "Alice"),
                new Field("email", "[email protected]")
            ),
            Arrays.asList(
                new Field("id", "2"),
                new Field("name", "Bob"),
                new Field("email", "[email protected]")
            )
        );
        
        // Note: Direct storage manipulation for setup
        // In production, use CREATE TABLE query
        for (List<Field> user : users) {
            storage.write("users", user);
        }
        
        System.out.println("Users table created with " + users.size() + " records");
    }
}

Query Execution Flow

SELECT Query Flow

1. Client sends: "SELECT * FROM users WHERE id = 123"
   ↓
2. MySqlServer.parseRequest() 
   → Creates MySqlQuery object
   ↓
3. DatabaseServer.handleRequest(query)
   → Delegates to DatabaseEngine
   ↓
4. DatabaseEngine.execute(query)
   → Identifies READ operation
   → Calls storageEngine.read("users", condition)
   ↓
5. JsonStorageEngine.read("users", condition)
   → Reads ./database/table_users.json
   → Parses JSON into List<List<Field>>
   → Filters by condition (id = 123)
   → Returns matching records
   ↓
6. DatabaseEngine wraps results in QueryResult
   ↓
7. DatabaseServer serializes QueryResult
   ↓
8. Client receives serialized response

INSERT Query Flow

1. Client sends: "INSERT INTO users (id, name) VALUES ('3', 'Charlie')"
   ↓
2. Parse → MySqlQuery(CREATE, "users", [Field("id", "3"), Field("name", "Charlie")])
   ↓
3. DatabaseEngine.execute()
   → Identifies CREATE operation
   → Calls storageEngine.write("users", fields)
   ↓
4. JsonStorageEngine.write()
   → Reads existing ./database/table_users.json
   → Appends new record
   → Writes updated JSON back to file
   ↓
5. Returns QueryResult(success=true, message="Write successful")

Condition Evaluation

The StorageEngine evaluates WHERE clauses in memory:

private boolean matchesCondition(List<Field> record, QueryCondition condition) {
    if (condition == null) return true;
    
    if (condition.isSimpleCondition()) {
        // Simple: field = value
        return evaluateSimpleCondition(record, condition);
    }
    
    if (condition.isComplexCondition()) {
        // Complex: (cond1 AND cond2) OR cond3
        return evaluateComplexCondition(record, condition);
    }
    
    return true;
}

private boolean evaluateSimpleCondition(List<Field> record, QueryCondition condition) {
    String fieldName = condition.getFieldName();
    Object expectedValue = condition.getValue();
    
    for (Field field : record) {
        if (field.getName().equals(fieldName)) {
            return compareValues(field.getValue(), expectedValue);
        }
    }
    
    return false;
}

private boolean evaluateComplexCondition(List<Field> record, QueryCondition condition) {
    LogicalOperator operator = condition.getLogicalOperator();
    List<QueryCondition> subConditions = condition.getSubConditions();
    
    if (operator == LogicalOperator.AND) {
        // All sub-conditions must be true
        for (QueryCondition sub : subConditions) {
            if (!matchesCondition(record, sub)) return false;
        }
        return true;
    } 
    else if (operator == LogicalOperator.OR) {
        // At least one sub-condition must be true
        for (QueryCondition sub : subConditions) {
            if (matchesCondition(record, sub)) return true;
        }
        return false;
    }
    
    return true;
}

Supported operators:

Equality: field = value
Logical AND: (field1 = value1) AND (field2 = value2)
Logical OR: (field1 = value1) OR (field2 = value2)

Not yet supported:

Comparison operators: >, <, >=, <=, !=
LIKE patterns: field LIKE '%pattern%'
IN clause: field IN (value1, value2)
JOINs across tables

Transactions (Abstract)

The DatabaseEngine defines transaction methods, but leaves implementation to subclasses:

public abstract void beginTransaction();
public abstract void commit();
public abstract void rollback();

Example implementation:

public class TransactionalDatabaseEngine extends DatabaseEngine {
    
    private List<Operation> transactionLog = new ArrayList<>();
    private boolean inTransaction = false;
    
    @Override
    public void beginTransaction() {
        transactionLog.clear();
        inTransaction = true;
    }
    
    @Override
    public QueryResult execute(Query query) {
        QueryResult result = super.execute(query);
        
        if (inTransaction) {
            transactionLog.add(new Operation(query, result));
        }
        
        return result;
    }
    
    @Override
    public void commit() {
        // Make all operations permanent
        inTransaction = false;
        transactionLog.clear();
    }
    
    @Override
    public void rollback() {
        // Undo all operations in reverse order
        for (int i = transactionLog.size() - 1; i >= 0; i--) {
            undoOperation(transactionLog.get(i));
        }
        inTransaction = false;
        transactionLog.clear();
    }
}

Performance Characteristics

Scalability

Storage: Limited by file system and parsing overhead
- JSON/XML: Parses entire file on each query
- No indexing: O(n) scan for every query
Concurrency: Thread-per-connection model
- Each client gets own thread
- No connection pooling
- Limited to ~10,000 concurrent connections

Optimization Opportunities

In-memory caching: Load collections once, keep in memory
Indexing: Build B-tree indexes for fast lookups
Binary storage: Faster than JSON/XML parsing
Connection pooling: Reuse threads
Asynchronous I/O: Non-blocking operations

Database Server

Database Server: Query Execution & Storage

Position in Architecture

Quick Start

Basic Database Server

Three-Component Architecture

1. DatabaseServer - Connection Manager

2. DatabaseEngine - Query Executor

3. StorageEngine - Persistence Layer

Data Model

Field: Column Data

Record: Row Data

Collection: Table Data

Query Model

Query Interface

QueryType: Operation Classification

QueryCondition: WHERE Clause

QueryResult: Response

Storage Engines

JsonStorageEngine

XmlStorageEngine

Implementing Custom Storage Engines

MySQL Implementation

MySqlServer

MySqlQuery

MySQL Parsers

Complete Example

Full Database Server Application

Query Execution Flow

SELECT Query Flow

INSERT Query Flow

Condition Evaluation

Transactions (Abstract)

Performance Characteristics

Scalability

Optimization Opportunities

Related Documentation

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Home

Documentation

Getting Started

Architecture

Components

Quick Links

By Topic

Clone this wiki locally