Database Engine & Concurrency

The database layer of Atslegas is built on a custom SQLite wrapper located in db_engine.py. This engine is designed to handle the high-concurrency demands of a multi-user web application while leveraging the simplicity and portability of a single-file database.

SQLite Configuration & WAL Mode

To ensure that the application remains responsive during simultaneous read and write operations, the database engine implements Write-Ahead Logging (WAL). Unlike standard rollback journals, WAL mode allows multiple readers to operate concurrently with a single writer, significantly reducing "Database is locked" errors.

The engine automatically applies the following performance tunings upon initialization:

• Journal Mode (WAL): Enabled via PRAGMA journal_mode = WAL;.
• Synchronous Mode: Optimized for performance while maintaining ACID compliance.
• Busy Timeout: Set to 5000ms (PRAGMA busy_timeout = 5000;), ensuring that the database waits for a lock to clear before throwing an exception.

Concurrency Handling

While WAL mode improves throughput, SQLite still requires a table-level lock for writes. The DB engine provides a robust retry mechanism to handle peak load scenarios gracefully.

Write Retries with Exponential Backoff

The execute_write_with_retry method is used for INSERT, UPDATE, and DELETE operations. If the database is busy, the engine will automatically retry the operation up to 5 times using an exponential backoff strategy.

# Example of the internal retry logic
cur = db.execute_write_with_retry(
    "UPDATE user SET password = ? WHERE user_id = ?",
    (new_password, user_id)
)

Connection Management

Atslegas manages database connections using two primary patterns to ensure thread safety and resource efficiency:

1. Flask Request Lifecycle

In the api_server.py blueprint, connections are opened per-request and stored in Flask’s g (global) object. This ensures that every API call has a dedicated, short-lived connection that is automatically closed at the end of the request.

• Initialization: _open_request_db opens the connection and applies pragmas.
• Teardown: _close_request_db ensures the connection is closed, even if an error occurs during the request.

2. Context Manager

For standalone scripts or long-running tasks, the get_connection context manager provides a fresh, thread-safe connection.

with database.get_connection() as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM user")
    results = cursor.fetchall()
# Connection is automatically closed here

Database Helpers (ORM-like Interface)

The engine organizes business logic into nested classes (e.g., User, UserData) that act as a lightweight Object-Relational Mapper (ORM). These classes abstract away the raw SQL for common operations.

Example: User Management

The User helper provides standard methods for interacting with the authentication table:

| Method | Description | | :--- | :--- | | add(username, password, salt, conn) | Creates a new user record. | | find(user_id, username, conn) | Retrieves a user by ID or username. Returns a status code (0 for success, 1 for not found). | | list(conn) | Returns all user records. |

Usage Example:

# Initialize the helper
user_helper = database.User(database)

# Search for a user using an existing connection
status, user_record = user_helper.find(username="root", conn=g.db_conn)

if status == 0:
    print(f"User ID: {user_record[0][0]}")

Schema Initialization

Database structure is managed via a schema.sql file. On the first run, main.py calls database.create_db(schema_file), which executes the script to build tables, indexes, and initial constraints.

To modify the database structure:

1. Update schema.sql.
2. Delete the existing database.db file (Note: this will erase all data).
3. Restart the application to regenerate the database.