"""Database storage utilities."""
import logging
from contextlib import asynccontextmanager
import aiosqlite
from ..config import config
logger = logging.getLogger(__name__)
@asynccontextmanager
async def get_db_connection():
"""
Get a database connection as a context manager.
Usage:
async with get_db_connection() as db:
await db.execute(...)
"""
conn = await aiosqlite.connect(config.database_path)
try:
yield conn
finally:
await conn.close()
async def init_database():
"""Initialize the database schema."""
logger.info("Initializing database schema")
async with get_db_connection() as db:
# Career domain tables
await db.execute(
"""
CREATE TABLE IF NOT EXISTS applications (
id TEXT PRIMARY KEY,
job_url TEXT NOT NULL,
company TEXT NOT NULL,
title TEXT NOT NULL,
location TEXT,
applied_date TEXT NOT NULL,
status TEXT NOT NULL,
notes TEXT,
next_action TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
)
"""
)
await db.execute(
"""
CREATE TABLE IF NOT EXISTS companies (
id TEXT PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
careers_url TEXT NOT NULL,
priority INTEGER DEFAULT 5,
notes TEXT,
last_checked TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
"""
)
# Create indexes
await db.execute(
"CREATE INDEX IF NOT EXISTS idx_applications_status ON applications(status)"
)
await db.execute(
"CREATE INDEX IF NOT EXISTS idx_applications_company ON applications(company)"
)
await db.execute(
"CREATE INDEX IF NOT EXISTS idx_companies_priority ON companies(priority)"
)
await db.commit()
logger.info("Database schema initialized successfully")
