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_dbopens the connection and applies pragmas. - Teardown:
_close_request_dbensures 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:
- Update
schema.sql. - Delete the existing
database.dbfile (Note: this will erase all data). - Restart the application to regenerate the database.