mbishu's picture
Update product database and vector store management with new features and optimizations
fe2866c
Raw History Blame Contribute Delete
11.6 kB
"""Database models and persistence helpers for product metadata."""
from __future__ import annotations
import sqlite3
from dataclasses import dataclass
from typing import Any, Dict, List, Optional
from datetime import datetime
import json
@dataclass
class Product:
"""Domain model representing a product stored in the catalog database."""
id: str
title: str
description: str
price: float
currency: str
category: str
brand: str
image_url: str
availability: str
rating: Optional[float] = None
review_count: Optional[int] = None
created_at: Optional[datetime] = None
updated_at: Optional[datetime] = None
metadata: Optional[Dict[str, Any]] = None
def to_dict(self) -> Dict[str, Any]:
"""Serialize the product to a JSON-friendly dictionary."""
def _serialize_timestamp(value: Optional[datetime]) -> Optional[str]:
if value is None:
return None
if isinstance(value, datetime):
return value.isoformat()
# SQLite returns ISO-formatted strings; pass through unchanged
if isinstance(value, str):
return value
return str(value)
return {
"id": self.id,
"title": self.title,
"description": self.description,
"price": self.price,
"currency": self.currency,
"category": self.category,
"brand": self.brand,
"image_url": self.image_url,
"availability": self.availability,
"rating": self.rating,
"review_count": self.review_count,
"created_at": _serialize_timestamp(self.created_at),
"updated_at": _serialize_timestamp(self.updated_at),
"metadata": self.metadata or {},
}
class ProductDatabase:
"""SQLite-backed data access layer used by the API and vector store manager."""
def __init__(self, db_path: str):
self.db_path = db_path
self.init_database()
def init_database(self) -> None:
"""Ensure required tables and indexes exist."""
with sqlite3.connect(self.db_path) as conn:
conn.execute(
"""
CREATE TABLE IF NOT EXISTS products (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
description TEXT,
price REAL NOT NULL,
currency TEXT DEFAULT 'XAF',
category TEXT NOT NULL,
brand TEXT,
image_url TEXT NOT NULL,
availability TEXT DEFAULT 'in_stock',
rating REAL,
review_count INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
metadata TEXT
)
"""
)
conn.execute(
"""
CREATE TABLE IF NOT EXISTS categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
parent_id INTEGER,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES categories (id)
)
"""
)
conn.execute(
"""
CREATE TABLE IF NOT EXISTS brands (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
description TEXT,
logo_url TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"""
)
conn.execute("CREATE INDEX IF NOT EXISTS idx_products_category ON products(category)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_products_brand ON products(brand)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_products_price ON products(price)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_products_rating ON products(rating)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_products_availability ON products(availability)")
conn.commit()
def add_product(self, product: Product) -> None:
"""Insert or replace a product record."""
with sqlite3.connect(self.db_path) as conn:
conn.execute(
"""
INSERT OR REPLACE INTO products
(id, title, description, price, currency, category, brand, image_url,
availability, rating, review_count, metadata, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
""",
(
product.id,
product.title,
product.description,
product.price,
product.currency,
product.category,
product.brand,
product.image_url,
product.availability,
product.rating,
product.review_count,
json.dumps(product.metadata) if product.metadata else None,
),
)
conn.commit()
def get_product(self, product_id: str) -> Optional[Product]:
"""Fetch a product by identifier."""
with sqlite3.connect(self.db_path) as conn:
conn.row_factory = sqlite3.Row
cursor = conn.execute("SELECT * FROM products WHERE id = ?", (product_id,))
row = cursor.fetchone()
if row:
return self._row_to_product(row)
return None
def get_products(
self,
limit: int = 100,
offset: int = 0,
category: Optional[str] = None,
brand: Optional[str] = None,
min_price: Optional[float] = None,
max_price: Optional[float] = None,
availability: Optional[str] = None,
) -> List[Product]:
"""Return paginated products with optional filters."""
query = "SELECT * FROM products WHERE 1=1"
params = []
if category:
query += " AND category = ?"
params.append(category)
if brand:
query += " AND brand = ?"
params.append(brand)
if min_price is not None:
query += " AND price >= ?"
params.append(min_price)
if max_price is not None:
query += " AND price <= ?"
params.append(max_price)
if availability:
query += " AND availability = ?"
params.append(availability)
query += " ORDER BY created_at DESC LIMIT ? OFFSET ?"
params.extend([limit, offset])
with sqlite3.connect(self.db_path) as conn:
conn.row_factory = sqlite3.Row
cursor = conn.execute(query, params)
rows = cursor.fetchall()
return [self._row_to_product(row) for row in rows]
def get_all_products(self) -> List[Product]:
"""Return all products (used for initial embedding generation)."""
with sqlite3.connect(self.db_path) as conn:
conn.row_factory = sqlite3.Row
cursor = conn.execute("SELECT * FROM products ORDER BY id")
rows = cursor.fetchall()
return [self._row_to_product(row) for row in rows]
def get_categories(self) -> List[str]:
"""Return the unique category values."""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.execute("SELECT DISTINCT category FROM products ORDER BY category")
return [row[0] for row in cursor.fetchall()]
def get_brands(self) -> List[str]:
"""Return the unique brand values."""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.execute("SELECT DISTINCT brand FROM products WHERE brand IS NOT NULL ORDER BY brand")
return [row[0] for row in cursor.fetchall()]
def search_products(self, query: str, limit: int = 20) -> List[Product]:
"""Simple LIKE-based text search used for admin tooling."""
with sqlite3.connect(self.db_path) as conn:
conn.row_factory = sqlite3.Row
cursor = conn.execute(
"""
SELECT * FROM products
WHERE title LIKE ? OR description LIKE ?
ORDER BY created_at DESC LIMIT ?
""",
(f"%{query}%", f"%{query}%", limit),
)
rows = cursor.fetchall()
return [self._row_to_product(row) for row in rows]
def delete_product(self, product_id: str) -> bool:
"""Delete a product by ID. Returns True if a row was removed."""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.execute("DELETE FROM products WHERE id = ?", (product_id,))
conn.commit()
return cursor.rowcount > 0
def update_product(self, product: Product) -> bool:
"""Persist updates to an existing product record."""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.execute(
"""
UPDATE products SET
title = ?,
description = ?,
price = ?,
currency = ?,
category = ?,
brand = ?,
image_url = ?,
availability = ?,
rating = ?,
review_count = ?,
metadata = ?,
updated_at = CURRENT_TIMESTAMP
WHERE id = ?
""",
(
product.title,
product.description,
product.price,
product.currency,
product.category,
product.brand,
product.image_url,
product.availability,
product.rating,
product.review_count,
json.dumps(product.metadata) if product.metadata else None,
product.id,
),
)
conn.commit()
return cursor.rowcount > 0
def get_product_count(self) -> int:
"""Return the total number of catalog entries."""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.execute("SELECT COUNT(*) FROM products")
result = cursor.fetchone()
return int(result[0]) if result else 0
def _row_to_product(self, row: sqlite3.Row) -> Product:
"""Convert a SQLite row into a Product dataclass instance."""
metadata = json.loads(row["metadata"]) if row["metadata"] else None
return Product(
id=row["id"],
title=row["title"],
description=row["description"] or "",
price=row["price"],
currency=row["currency"],
category=row["category"],
brand=row["brand"] or "",
image_url=row["image_url"],
availability=row["availability"],
rating=row["rating"],
review_count=row["review_count"],
created_at=row["created_at"],
updated_at=row["updated_at"],
metadata=metadata,
)