Spaces:
Running
Running
File size: 5,216 Bytes
5aa2bd9 53febb9 5aa2bd9 53febb9 5aa2bd9 41136f2 5aa2bd9 41136f2 5aa2bd9 53febb9 5aa2bd9 53febb9 5aa2bd9 41136f2 5aa2bd9 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 | from sqlalchemy import create_engine, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import os
from app.config import settings
DATABASE_URL = settings.database_url or os.getenv("DATABASE_URL", "sqlite:///./amazon_intel.db")
if DATABASE_URL.startswith("sqlite"):
engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False})
else:
engine = create_engine(DATABASE_URL, pool_pre_ping=True, pool_recycle=300)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()
def migrate_schema():
"""Add columns when schema evolves (SQLite + PostgreSQL)."""
user_alters_sqlite = [
"ALTER TABLE users ADD COLUMN token_version INTEGER DEFAULT 0",
"ALTER TABLE users ADD COLUMN auth_provider VARCHAR(20) DEFAULT 'email'",
"ALTER TABLE users ADD COLUMN firebase_uid VARCHAR(128)",
]
user_alters_pg = [
"ALTER TABLE users ADD COLUMN IF NOT EXISTS token_version INTEGER DEFAULT 0",
"ALTER TABLE users ADD COLUMN IF NOT EXISTS auth_provider VARCHAR(20) DEFAULT 'email'",
"ALTER TABLE users ADD COLUMN IF NOT EXISTS firebase_uid VARCHAR(128)",
]
sqlite_product_alters = [
"ALTER TABLE products ADD COLUMN seller_count INTEGER",
"ALTER TABLE products ADD COLUMN buy_box_winner VARCHAR(255)",
"ALTER TABLE products ADD COLUMN buy_box_price NUMERIC(10,2)",
"ALTER TABLE products ADD COLUMN buy_box_is_fba BOOLEAN DEFAULT 1",
"ALTER TABLE products ADD COLUMN is_amazon_sold BOOLEAN DEFAULT 0",
"ALTER TABLE products ADD COLUMN last_data_source VARCHAR(20) DEFAULT 'cached'",
"ALTER TABLE products ADD COLUMN upc VARCHAR(20)",
"ALTER TABLE products ADD COLUMN has_buy_box BOOLEAN DEFAULT 1",
"ALTER TABLE products ADD COLUMN package_weight_lbs NUMERIC(8,3)",
"ALTER TABLE products ADD COLUMN other_sellers_json TEXT",
"ALTER TABLE products ADD COLUMN offers_source VARCHAR(30)",
"ALTER TABLE products ADD COLUMN marketplace VARCHAR(8) DEFAULT 'us'",
"ALTER TABLE products ADD COLUMN currency VARCHAR(8) DEFAULT 'USD'",
]
pg_product_alters = [
"ALTER TABLE products ADD COLUMN IF NOT EXISTS upc VARCHAR(20)",
"ALTER TABLE products ADD COLUMN IF NOT EXISTS has_buy_box BOOLEAN DEFAULT TRUE",
"ALTER TABLE products ADD COLUMN IF NOT EXISTS package_weight_lbs NUMERIC(8,3)",
"ALTER TABLE products ADD COLUMN IF NOT EXISTS other_sellers_json TEXT",
"ALTER TABLE products ADD COLUMN IF NOT EXISTS offers_source VARCHAR(30)",
"ALTER TABLE products ADD COLUMN IF NOT EXISTS marketplace VARCHAR(8) DEFAULT 'us'",
"ALTER TABLE products ADD COLUMN IF NOT EXISTS currency VARCHAR(8) DEFAULT 'USD'",
]
is_pg = DATABASE_URL.startswith("postgresql")
with engine.connect() as conn:
for sql in (user_alters_pg if is_pg else user_alters_sqlite):
try:
conn.execute(text(sql))
conn.commit()
except Exception:
pass
# Google-only accounts may have no password
if is_pg:
try:
conn.execute(text("ALTER TABLE users ALTER COLUMN password_hash DROP NOT NULL"))
conn.commit()
except Exception:
pass
if DATABASE_URL.startswith("sqlite"):
for sql in sqlite_product_alters:
try:
conn.execute(text(sql))
conn.commit()
except Exception:
pass
if is_pg:
for sql in pg_product_alters:
try:
conn.execute(text(sql))
conn.commit()
except Exception:
pass
# Prefer (asin, marketplace) uniqueness so US + AE can coexist
for sql in (
"UPDATE products SET marketplace = 'us' WHERE marketplace IS NULL",
"UPDATE products SET currency = 'USD' WHERE currency IS NULL AND (marketplace = 'us' OR marketplace IS NULL)",
"ALTER TABLE products DROP CONSTRAINT IF EXISTS products_asin_key",
"ALTER TABLE products DROP CONSTRAINT IF EXISTS ix_products_asin",
"CREATE UNIQUE INDEX IF NOT EXISTS uq_products_asin_marketplace ON products (asin, marketplace)",
):
try:
conn.execute(text(sql))
conn.commit()
except Exception:
pass
else:
for sql in (
"UPDATE products SET marketplace = 'us' WHERE marketplace IS NULL",
"UPDATE products SET currency = 'USD' WHERE currency IS NULL",
):
try:
conn.execute(text(sql))
conn.commit()
except Exception:
pass
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
|