mbishu's picture
Update backend: add new services, update dependencies, and improve configuration
44094a0
Raw History Blame Contribute Delete
29.9 kB
"""
Unified database adapters for connecting to various customer database systems.
Used by both SDK and API.
"""
import sqlite3
from abc import ABC, abstractmethod
from typing import List, Dict, Any, Optional, Iterator
import pandas as pd
from ...database.models import Product
class DatabaseError(Exception):
"""Database operation error."""
pass
class DatabaseConfig:
"""Configuration for database connection."""
def __init__(
self,
connection_string: str,
table_name: str = "products",
database_name: Optional[str] = None,
id_column: str = "id",
title_column: str = "title",
image_url_column: str = "image_url",
category_column: Optional[str] = None,
brand_column: Optional[str] = None,
price_column: Optional[str] = None,
rating_column: Optional[str] = None,
description_column: Optional[str] = None
):
self.connection_string = connection_string
self.table_name = table_name
self.database_name = database_name
self.id_column = id_column
self.title_column = title_column
self.image_url_column = image_url_column
self.category_column = category_column
self.brand_column = brand_column
self.price_column = price_column
self.rating_column = rating_column
self.description_column = description_column
class DatabaseAdapter(ABC):
"""Abstract base class for database adapters."""
def __init__(self, config: DatabaseConfig):
self.config = config
@abstractmethod
def connect(self) -> None:
"""Establish database connection."""
pass
@abstractmethod
def disconnect(self) -> None:
"""Close database connection."""
pass
@abstractmethod
def get_all_products(self) -> Iterator[Product]:
"""Get all products from the database."""
pass
@abstractmethod
def get_product_count(self) -> int:
"""Get total number of products."""
pass
@abstractmethod
def get_product_by_id(self, product_id: str) -> Optional[Product]:
"""Get a specific product by ID."""
pass
# Default implementations for read-only helpers. Subclasses can override for efficiency.
def get_products_filtered(
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 (in-memory fallback)."""
if limit <= 0:
return []
offset = max(offset, 0)
def _matches(product: Product) -> bool:
if category and product.category != category:
return False
if brand and product.brand != brand:
return False
if min_price is not None and product.price < float(min_price):
return False
if max_price is not None and product.price > float(max_price):
return False
if availability and product.availability != availability:
return False
return True
results: List[Product] = []
skipped = 0
for product in self.get_all_products():
if not _matches(product):
continue
if skipped < offset:
skipped += 1
continue
results.append(product)
if len(results) >= limit:
break
return results
def search_products(self, query: str, limit: int = 20) -> List[Product]:
"""Simple LIKE-based search using in-memory filtering as fallback."""
if not query or limit <= 0:
return []
q = query.casefold()
matches: List[Product] = []
for product in self.get_all_products():
haystack = f"{product.title} {product.description or ''} {product.brand or ''}"
if q in haystack.casefold():
matches.append(product)
if len(matches) >= limit:
break
return matches
def get_categories(self) -> List[str]:
"""Return distinct categories."""
categories = {prod.category for prod in self.get_all_products() if getattr(prod, "category", None)}
return sorted(categories)
def get_brands(self) -> List[str]:
"""Return distinct brands."""
brands = {prod.brand for prod in self.get_all_products() if getattr(prod, "brand", None)}
return sorted(brands)
class MongoDBAdapter(DatabaseAdapter):
"""MongoDB adapter for document-store catalogs."""
def __init__(self, config: DatabaseConfig):
super().__init__(config)
self.client = None
self.database = None
self.collection = None
def connect(self) -> None:
"""Connect to MongoDB."""
try:
from pymongo import MongoClient
except ImportError as exc: # pragma: no cover - import error path
raise DatabaseError(
"pymongo is required for MongoDB support. Install with: pip install pymongo"
) from exc
self.client = MongoClient(self.config.connection_string)
db_name = self.config.database_name
if not db_name:
try:
default_db = self.client.get_default_database()
db_name = default_db.name if default_db else None
except Exception:
db_name = None
if not db_name:
raise DatabaseError(
"MongoDB connection requires database_name in config or connection string"
)
self.database = self.client[db_name]
collection_name = self.config.table_name or "products"
self.collection = self.database[collection_name]
def disconnect(self) -> None:
"""Close MongoDB connection."""
if self.client:
self.client.close()
self.client = None
self.database = None
self.collection = None
def _ensure_collection(self):
if self.collection is None:
raise DatabaseError("MongoDB collection not initialized")
def _doc_to_product(self, doc: Dict[str, Any]) -> Product:
"""Convert MongoDB document to Product."""
id_field = self.config.id_column or "_id"
raw_id = doc.get(id_field) or doc.get("_id")
product_id = str(raw_id) if raw_id is not None else ""
def _get_value(field: Optional[str], fallback_key: str) -> Any:
if field and field in doc:
return doc.get(field)
return doc.get(fallback_key)
def _coerce_float(value: Any, default: Optional[float] = 0.0) -> Optional[float]:
try:
if value is None:
return default
return float(value)
except (TypeError, ValueError):
return default
def _coerce_int(value: Any) -> Optional[int]:
try:
if value is None:
return None
return int(value)
except (TypeError, ValueError):
return None
metadata_value = doc.get("metadata")
if metadata_value is None:
metadata_value = {}
elif not isinstance(metadata_value, dict):
metadata_value = {"raw": metadata_value}
return Product(
id=product_id,
title=str(_get_value(self.config.title_column, "title") or ""),
description=str(_get_value(self.config.description_column, "description") or ""),
price=float(_coerce_float(_get_value(self.config.price_column, "price"), 0.0) or 0.0),
currency=str(doc.get("currency", "USD")),
category=str(_get_value(self.config.category_column, "category") or ""),
brand=str(_get_value(self.config.brand_column, "brand") or ""),
image_url=str(_get_value(self.config.image_url_column, "image_url") or ""),
availability=str(doc.get("availability", "in_stock")),
rating=_coerce_float(doc.get("rating"), None),
review_count=_coerce_int(doc.get("review_count")),
metadata=metadata_value,
)
def get_all_products(self) -> Iterator[Product]:
self._ensure_collection()
cursor = self.collection.find({})
for doc in cursor:
yield self._doc_to_product(doc)
def get_product_count(self) -> int:
self._ensure_collection()
return self.collection.count_documents({})
def get_product_by_id(self, product_id: str) -> Optional[Product]:
self._ensure_collection()
id_field = self.config.id_column or "_id"
query: Dict[str, Any] = {}
if id_field == "_id":
try:
from bson import ObjectId # type: ignore
query["_id"] = ObjectId(product_id)
except Exception:
query["_id"] = product_id
else:
query[id_field] = product_id
doc = self.collection.find_one(query)
if not doc:
return None
return self._doc_to_product(doc)
def get_products_filtered(
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]:
self._ensure_collection()
query: Dict[str, Any] = {}
def _set_filter(field: Optional[str], fallback: str, value: Any) -> None:
if value is None:
return
field_name = field or fallback
query[field_name] = value
_set_filter(self.config.category_column, "category", category)
_set_filter(self.config.brand_column, "brand", brand)
_set_filter(None, "availability", availability)
price_field = self.config.price_column or "price"
price_clause: Dict[str, Any] = {}
if min_price is not None:
price_clause["$gte"] = float(min_price)
if max_price is not None:
price_clause["$lte"] = float(max_price)
if price_clause:
query[price_field] = price_clause
sort_field = self.config.id_column or "_id"
cursor = (
self.collection.find(query)
.sort(sort_field, -1)
.skip(max(offset, 0))
.limit(max(limit, 0))
)
return [self._doc_to_product(doc) for doc in cursor]
def search_products(self, query: str, limit: int = 20) -> List[Product]:
self._ensure_collection()
if not query:
return []
regex = {"$regex": query, "$options": "i"}
search_fields = [
self.config.title_column or "title",
self.config.description_column or "description",
self.config.brand_column or "brand",
"category",
]
filters = {"$or": [{field: regex} for field in search_fields]}
cursor = self.collection.find(filters).limit(max(limit, 0))
return [self._doc_to_product(doc) for doc in cursor]
def get_categories(self) -> List[str]:
self._ensure_collection()
field = self.config.category_column or "category"
categories = self.collection.distinct(field)
return sorted(filter(None, categories))
def get_brands(self) -> List[str]:
self._ensure_collection()
field = self.config.brand_column or "brand"
brands = self.collection.distinct(field)
return sorted(filter(None, brands))
class SQLiteAdapter(DatabaseAdapter):
"""SQLite database adapter."""
def __init__(self, config: DatabaseConfig):
super().__init__(config)
self.connection: Optional[sqlite3.Connection] = None
def connect(self) -> None:
"""Connect to SQLite database."""
try:
# Parse SQLite connection string to extract file path
conn_str = self.config.connection_string
if conn_str.startswith("sqlite:///"):
# Remove sqlite:/// prefix
db_path = conn_str.replace("sqlite:///", "")
elif conn_str.startswith("sqlite://"):
# Handle sqlite:// (without trailing /)
db_path = conn_str.replace("sqlite://", "")
else:
# Already a file path
db_path = conn_str
# Ensure path exists (create directory if needed)
from pathlib import Path
db_path_obj = Path(db_path)
if not db_path_obj.parent.exists():
db_path_obj.parent.mkdir(parents=True, exist_ok=True)
# Enable SQLite thread-safe mode (check_same_thread=False allows connections from different threads)
# SQLite handles thread safety internally when using this mode
self.connection = sqlite3.connect(db_path, check_same_thread=False)
self.connection.row_factory = sqlite3.Row
except sqlite3.Error as e:
raise DatabaseError(f"Failed to connect to SQLite database: {e}")
def disconnect(self) -> None:
"""Close SQLite connection."""
if self.connection:
self.connection.close()
self.connection = None
def get_all_products(self) -> Iterator[Product]:
"""Get all products from SQLite database."""
if not self.connection:
raise DatabaseError("Database not connected")
try:
query = f"""
SELECT
{self.config.id_column} as id,
{self.config.title_column} as title,
{self.config.image_url_column} as image_url,
{self.config.category_column or 'NULL'} as category,
{self.config.brand_column or 'NULL'} as brand,
{self.config.price_column or 'NULL'} as price,
{self.config.rating_column or 'NULL'} as rating,
{self.config.description_column or 'NULL'} as description,
COALESCE(currency, 'USD') as currency,
COALESCE(availability, 'in_stock') as availability
FROM {self.config.table_name}
"""
cursor = self.connection.execute(query)
for row in cursor:
# SQLite Row supports dict-like access but not .get() method
# Check if keys exist using 'key' in row.keys()
row_keys = row.keys()
yield Product(
id=str(row['id']),
title=str(row['title']),
description=row['description'] if row['description'] else '',
price=float(row['price']) if row['price'] else 0.0,
currency=row['currency'] if 'currency' in row_keys else 'USD',
category=row['category'] if row['category'] else '',
brand=row['brand'] if row['brand'] else '',
image_url=row['image_url'] if row['image_url'] else '',
availability=row['availability'] if 'availability' in row_keys else 'in_stock',
rating=float(row['rating']) if row['rating'] else None,
review_count=int(row['review_count']) if 'review_count' in row_keys and row['review_count'] else None
)
except sqlite3.Error as e:
raise DatabaseError(f"Failed to fetch products: {e}")
def get_product_count(self) -> int:
"""Get total number of products."""
if not self.connection:
raise DatabaseError("Database not connected")
try:
cursor = self.connection.execute(f"SELECT COUNT(*) FROM {self.config.table_name}")
return cursor.fetchone()[0]
except sqlite3.Error as e:
raise DatabaseError(f"Failed to get product count: {e}")
def get_product_by_id(self, product_id: str) -> Optional[Product]:
"""Get a specific product by ID."""
if not self.connection:
raise DatabaseError("Database not connected")
try:
query = f"""
SELECT
{self.config.id_column} as id,
{self.config.title_column} as title,
{self.config.image_url_column} as image_url,
{self.config.category_column or 'NULL'} as category,
{self.config.brand_column or 'NULL'} as brand,
{self.config.price_column or 'NULL'} as price,
{self.config.rating_column or 'NULL'} as rating,
{self.config.description_column or 'NULL'} as description,
COALESCE(currency, 'USD') as currency,
COALESCE(availability, 'in_stock') as availability
FROM {self.config.table_name}
WHERE {self.config.id_column} = ?
"""
cursor = self.connection.execute(query, (product_id,))
row = cursor.fetchone()
if not row:
return None
# SQLite Row supports dict-like access but not .get() method
row_keys = row.keys()
return Product(
id=str(row['id']),
title=str(row['title']),
description=row['description'] if row['description'] else '',
price=float(row['price']) if row['price'] else 0.0,
currency=row['currency'] if 'currency' in row_keys else 'USD',
category=row['category'] if row['category'] else '',
brand=row['brand'] if row['brand'] else '',
image_url=row['image_url'] if row['image_url'] else '',
availability=row['availability'] if 'availability' in row_keys else 'in_stock',
rating=float(row['rating']) if row['rating'] else None,
review_count=int(row['review_count']) if 'review_count' in row_keys and row['review_count'] else None
)
except sqlite3.Error as e:
raise DatabaseError(f"Failed to fetch product {product_id}: {e}")
class PostgreSQLAdapter(DatabaseAdapter):
"""PostgreSQL database adapter."""
def __init__(self, config: DatabaseConfig):
super().__init__(config)
self.connection = None
def connect(self) -> None:
"""Connect to PostgreSQL database."""
try:
import psycopg2
self.connection = psycopg2.connect(self.config.connection_string)
except ImportError:
raise DatabaseError("psycopg2 is required for PostgreSQL support. Install with: pip install psycopg2-binary")
except Exception as e:
raise DatabaseError(f"Failed to connect to PostgreSQL database: {e}")
def disconnect(self) -> None:
"""Close PostgreSQL connection."""
if self.connection:
self.connection.close()
self.connection = None
def get_all_products(self) -> Iterator[Product]:
"""Get all products from PostgreSQL database."""
if not self.connection:
raise DatabaseError("Database not connected")
try:
query = f"""
SELECT
{self.config.id_column} as id,
{self.config.title_column} as title,
{self.config.image_url_column} as image_url,
{self.config.category_column or 'NULL'} as category,
{self.config.brand_column or 'NULL'} as brand,
{self.config.price_column or 'NULL'} as price,
{self.config.rating_column or 'NULL'} as rating,
{self.config.description_column or 'NULL'} as description,
COALESCE(currency, 'USD') as currency,
COALESCE(availability, 'in_stock') as availability
FROM {self.config.table_name}
"""
cursor = self.connection.cursor()
cursor.execute(query)
for row in cursor.fetchall():
yield Product(
id=str(row[0]),
title=str(row[1]),
description=row[7] if row[7] else '',
price=float(row[5]) if row[5] else 0.0,
currency=row[8] if len(row) > 8 and row[8] else 'USD',
category=row[3] if row[3] else '',
brand=row[4] if row[4] else '',
image_url=row[2] if row[2] else '',
availability=row[9] if len(row) > 9 and row[9] else 'in_stock',
rating=float(row[6]) if row[6] else None
)
except Exception as e:
raise DatabaseError(f"Failed to fetch products: {e}")
def get_product_count(self) -> int:
"""Get total number of products."""
if not self.connection:
raise DatabaseError("Database not connected")
try:
cursor = self.connection.cursor()
cursor.execute(f"SELECT COUNT(*) FROM {self.config.table_name}")
return cursor.fetchone()[0]
except Exception as e:
raise DatabaseError(f"Failed to get product count: {e}")
def get_product_by_id(self, product_id: str) -> Optional[Product]:
"""Get a specific product by ID."""
if not self.connection:
raise DatabaseError("Database not connected")
try:
query = f"""
SELECT
{self.config.id_column} as id,
{self.config.title_column} as title,
{self.config.image_url_column} as image_url,
{self.config.category_column or 'NULL'} as category,
{self.config.brand_column or 'NULL'} as brand,
{self.config.price_column or 'NULL'} as price,
{self.config.rating_column or 'NULL'} as rating,
{self.config.description_column or 'NULL'} as description,
COALESCE(currency, 'USD') as currency,
COALESCE(availability, 'in_stock') as availability
FROM {self.config.table_name}
WHERE {self.config.id_column} = %s
"""
cursor = self.connection.cursor()
cursor.execute(query, (product_id,))
row = cursor.fetchone()
if not row:
return None
return Product(
id=str(row[0]),
title=str(row[1]),
description=row[7] if row[7] else '',
price=float(row[5]) if row[5] else 0.0,
currency=row[8] if len(row) > 8 and row[8] else 'USD',
category=row[3] if row[3] else '',
brand=row[4] if row[4] else '',
image_url=row[2] if row[2] else '',
availability=row[9] if len(row) > 9 and row[9] else 'in_stock',
rating=float(row[6]) if row[6] else None
)
except Exception as e:
raise DatabaseError(f"Failed to fetch product {product_id}: {e}")
class MySQLAdapter(DatabaseAdapter):
"""MySQL database adapter."""
def __init__(self, config: DatabaseConfig):
super().__init__(config)
self.connection = None
def connect(self) -> None:
"""Connect to MySQL database."""
try:
import mysql.connector
self.connection = mysql.connector.connect(self.config.connection_string)
except ImportError:
raise DatabaseError("mysql-connector-python is required for MySQL support. Install with: pip install mysql-connector-python")
except Exception as e:
raise DatabaseError(f"Failed to connect to MySQL database: {e}")
def disconnect(self) -> None:
"""Close MySQL connection."""
if self.connection:
self.connection.close()
self.connection = None
def get_all_products(self) -> Iterator[Product]:
"""Get all products from MySQL database."""
if not self.connection:
raise DatabaseError("Database not connected")
try:
query = f"""
SELECT
{self.config.id_column} as id,
{self.config.title_column} as title,
{self.config.image_url_column} as image_url,
{self.config.category_column or 'NULL'} as category,
{self.config.brand_column or 'NULL'} as brand,
{self.config.price_column or 'NULL'} as price,
{self.config.rating_column or 'NULL'} as rating,
{self.config.description_column or 'NULL'} as description,
COALESCE(currency, 'USD') as currency,
COALESCE(availability, 'in_stock') as availability
FROM {self.config.table_name}
"""
cursor = self.connection.cursor(dictionary=True)
cursor.execute(query)
for row in cursor.fetchall():
yield Product(
id=str(row['id']),
title=str(row['title']),
description=row['description'] if row['description'] else '',
price=float(row['price']) if row['price'] else 0.0,
currency=row.get('currency', 'USD'),
category=row['category'] if row['category'] else '',
brand=row['brand'] if row['brand'] else '',
image_url=row['image_url'] if row['image_url'] else '',
availability=row.get('availability', 'in_stock'),
rating=float(row['rating']) if row['rating'] else None
)
except Exception as e:
raise DatabaseError(f"Failed to fetch products: {e}")
def get_product_count(self) -> int:
"""Get total number of products."""
if not self.connection:
raise DatabaseError("Database not connected")
try:
cursor = self.connection.cursor()
cursor.execute(f"SELECT COUNT(*) FROM {self.config.table_name}")
return cursor.fetchone()[0]
except Exception as e:
raise DatabaseError(f"Failed to get product count: {e}")
def get_product_by_id(self, product_id: str) -> Optional[Product]:
"""Get a specific product by ID."""
if not self.connection:
raise DatabaseError("Database not connected")
try:
query = f"""
SELECT
{self.config.id_column} as id,
{self.config.title_column} as title,
{self.config.image_url_column} as image_url,
{self.config.category_column or 'NULL'} as category,
{self.config.brand_column or 'NULL'} as brand,
{self.config.price_column or 'NULL'} as price,
{self.config.rating_column or 'NULL'} as rating,
{self.config.description_column or 'NULL'} as description,
COALESCE(currency, 'USD') as currency,
COALESCE(availability, 'in_stock') as availability
FROM {self.config.table_name}
WHERE {self.config.id_column} = %s
"""
cursor = self.connection.cursor(dictionary=True)
cursor.execute(query, (product_id,))
row = cursor.fetchone()
if not row:
return None
return Product(
id=str(row['id']),
title=str(row['title']),
description=row['description'] if row['description'] else '',
price=float(row['price']) if row['price'] else 0.0,
currency=row.get('currency', 'USD'),
category=row['category'] if row['category'] else '',
brand=row['brand'] if row['brand'] else '',
image_url=row['image_url'] if row['image_url'] else '',
availability=row.get('availability', 'in_stock'),
rating=float(row['rating']) if row['rating'] else None
)
except Exception as e:
raise DatabaseError(f"Failed to fetch product {product_id}: {e}")
def create_database_adapter(config: DatabaseConfig) -> DatabaseAdapter:
"""Factory function to create appropriate database adapter."""
connection_string = config.connection_string.lower()
if connection_string.startswith('sqlite'):
return SQLiteAdapter(config)
elif connection_string.startswith('postgresql') or connection_string.startswith('postgres'):
return PostgreSQLAdapter(config)
elif connection_string.startswith('mysql'):
return MySQLAdapter(config)
elif connection_string.startswith('mongodb'):
return MongoDBAdapter(config)
else:
raise DatabaseError(f"Unsupported database type in connection string: {config.connection_string}")