spb/fabri-ka Public
Agrégateur de produits québécois — www.fabri-ka.com
HTML 57.9%
Python 18.6%
TypeScript 15.6%
CSS 7.8%
1# -----------------------------------------------------------------------------2# Fabri-Ka — Agrégateur de produits québécois3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# db.py : persistance SQLite — upsert avec hash de contenu, cycle de vie5# avec délai de grâce, FTS5 pour la recherche plein-texte.6# -----------------------------------------------------------------------------7from __future__ import annotations89import json10import sqlite311import time12from pathlib import Path1314from .schema import Product1516DB_PATH = Path(__file__).resolve().parent.parent / "data" / "fabrika.db"1718# Nombre de syncs consécutives où un produit doit être absent avant désactivation19MISS_GRACE = 22021_SCHEMA = """22CREATE TABLE IF NOT EXISTS stores (23 id TEXT PRIMARY KEY, -- domaine canonique24 name TEXT,25 url TEXT,26 platform TEXT,27 catalog_endpoint TEXT,28 city TEXT,29 region TEXT,30 origin_class TEXT, -- A|B|C|D|E31 origin_confidence REAL,32 origin_evidence TEXT,33 categories TEXT, -- JSON34 socials TEXT, -- JSON35 discovery_sources TEXT, -- JSON36 language TEXT,37 enabled INTEGER DEFAULT 1,38 last_sync REAL,39 last_status TEXT,40 product_count INTEGER DEFAULT 041);42CREATE TABLE IF NOT EXISTS products (43 uid TEXT PRIMARY KEY,44 store_id TEXT NOT NULL,45 external_id TEXT NOT NULL,46 url TEXT,47 title TEXT,48 description TEXT,49 price REAL,50 price_max REAL,51 compare_at_price REAL,52 currency TEXT,53 images TEXT, -- JSON54 category TEXT,55 product_type TEXT,56 tags TEXT, -- JSON57 vendor TEXT,58 available INTEGER,59 content_hash TEXT,60 first_seen REAL,61 last_seen REAL,62 active INTEGER DEFAULT 1,63 miss_count INTEGER DEFAULT 064);65CREATE INDEX IF NOT EXISTS idx_products_store ON products(store_id);66CREATE INDEX IF NOT EXISTS idx_products_cat ON products(category);67CREATE INDEX IF NOT EXISTS idx_products_active ON products(active);68CREATE TABLE IF NOT EXISTS sync_log (69 ts REAL, store_id TEXT, found INTEGER, added INTEGER,70 updated INTEGER, removed INTEGER, status TEXT, message TEXT71);72CREATE VIRTUAL TABLE IF NOT EXISTS products_fts USING fts5(73 uid UNINDEXED, title, description, tags, vendor, store_name,74 tokenize = 'unicode61 remove_diacritics 2'75);76"""777879def connect() -> sqlite3.Connection:80 DB_PATH.parent.mkdir(parents=True, exist_ok=True)81 con = sqlite3.connect(DB_PATH, timeout=60)82 con.execute("PRAGMA busy_timeout=60000")83 con.row_factory = sqlite3.Row84 con.executescript(_SCHEMA)85 return con868788def upsert_store(con: sqlite3.Connection, s: dict) -> None:89 con.execute("""90 INSERT INTO stores (id, name, url, platform, catalog_endpoint, city, region,91 origin_class, origin_confidence, origin_evidence,92 categories, socials, discovery_sources, language, enabled)93 VALUES (:id,:name,:url,:platform,:catalog_endpoint,:city,:region,94 :origin_class,:origin_confidence,:origin_evidence,95 :categories,:socials,:discovery_sources,:language,:enabled)96 ON CONFLICT(id) DO UPDATE SET97 name=excluded.name, url=excluded.url, platform=excluded.platform,98 catalog_endpoint=excluded.catalog_endpoint, city=excluded.city,99 region=excluded.region, origin_class=excluded.origin_class,100 origin_confidence=excluded.origin_confidence,101 origin_evidence=excluded.origin_evidence,102 categories=excluded.categories, socials=excluded.socials,103 discovery_sources=excluded.discovery_sources,104 language=excluded.language, enabled=excluded.enabled105 """, {106 "id": s["id"], "name": s.get("name") or s["id"], "url": s.get("url") or f"https://{s['id']}",107 "platform": s.get("platform") or "", "catalog_endpoint": s.get("catalog_endpoint") or "",108 "city": s.get("city") or "", "region": s.get("region") or "",109 "origin_class": s.get("origin_class") or "E",110 "origin_confidence": s.get("origin_confidence") or 0.0,111 "origin_evidence": s.get("origin_evidence") or "",112 "categories": json.dumps(s.get("categories") or [], ensure_ascii=False),113 "socials": json.dumps(s.get("socials") or [], ensure_ascii=False),114 "discovery_sources": json.dumps(s.get("discovery_sources") or [], ensure_ascii=False),115 "language": s.get("language") or "", "enabled": 1 if s.get("enabled", True) else 0,116 })117118119def sync_products(con: sqlite3.Connection, store_id: str, store_name: str,120 products: list[Product]) -> tuple[int, int, int]:121 """Diff complet d'une boutique : upsert + désactivation avec délai de grâce.122123 Retourne (ajoutés, modifiés, retirés)."""124 now = time.time()125 existing = {r["uid"]: (r["content_hash"], r["active"], r["miss_count"])126 for r in con.execute("SELECT uid, content_hash, active, miss_count "127 "FROM products WHERE store_id=?", (store_id,))}128 seen, added, updated = set(), 0, 0129 for p in products:130 p.finalize()131 uid, chash = p.uid, p.content_hash()132 if uid in seen: # doublon dans la même source133 continue134 seen.add(uid)135 row = p.to_row()136 row.update(images=json.dumps(p.images), tags=json.dumps(p.tags, ensure_ascii=False),137 content_hash=chash, last_seen=now,138 available=None if p.available is None else int(p.available))139 if uid not in existing:140 row["first_seen"] = now141 con.execute("""INSERT INTO products (uid, store_id, external_id, url, title,142 description, price, price_max, compare_at_price, currency, images, category,143 product_type, tags, vendor, available, content_hash, first_seen, last_seen,144 active, miss_count) VALUES (:uid,:store_id,:external_id,:url,:title,145 :description,:price,:price_max,:compare_at_price,:currency,:images,:category,146 :product_type,:tags,:vendor,:available,:content_hash,:first_seen,:last_seen,1,0)""", row)147 con.execute("INSERT INTO products_fts (uid, title, description, tags, vendor, store_name) "148 "VALUES (?,?,?,?,?,?)",149 (uid, p.title, p.description, " ".join(p.tags), p.vendor, store_name))150 added += 1151 else:152 old_hash, old_active, _ = existing[uid]153 if old_hash != chash or not old_active:154 con.execute("""UPDATE products SET url=:url, title=:title, description=:description,155 price=:price, price_max=:price_max, compare_at_price=:compare_at_price,156 currency=:currency, images=:images, category=:category,157 product_type=:product_type, tags=:tags, vendor=:vendor,158 available=:available, content_hash=:content_hash, last_seen=:last_seen,159 active=1, miss_count=0 WHERE uid=:uid""", row)160 con.execute("DELETE FROM products_fts WHERE uid=?", (uid,))161 con.execute("INSERT INTO products_fts (uid, title, description, tags, vendor, store_name) "162 "VALUES (?,?,?,?,?,?)",163 (uid, p.title, p.description, " ".join(p.tags), p.vendor, store_name))164 updated += 1165 else:166 con.execute("UPDATE products SET last_seen=?, miss_count=0 WHERE uid=?", (now, uid))167168 removed = 0169 for uid, (_, active, miss) in existing.items():170 if uid in seen or not active:171 continue172 if miss + 1 >= MISS_GRACE:173 con.execute("UPDATE products SET active=0, miss_count=? WHERE uid=?", (miss + 1, uid))174 con.execute("DELETE FROM products_fts WHERE uid=?", (uid,))175 removed += 1176 else:177 con.execute("UPDATE products SET miss_count=? WHERE uid=?", (miss + 1, uid))178179 con.execute("UPDATE stores SET last_sync=?, product_count="180 "(SELECT COUNT(*) FROM products WHERE store_id=? AND active=1) WHERE id=?",181 (now, store_id, store_id))182 return added, updated, removed183184185def log_sync(con, store_id, found, added, updated, removed, status, message=""):186 con.execute("INSERT INTO sync_log VALUES (?,?,?,?,?,?,?,?)",187 (time.time(), store_id, found, added, updated, removed, status, message[:300]))188 con.execute("UPDATE stores SET last_status=? WHERE id=?", (status, store_id))189