135 lines
6.9 KiB
Python
135 lines
6.9 KiB
Python
#!/usr/bin/env python3
|
|
"""Fix mega enrichment: add slug to vendors and 10Gtek transceivers."""
|
|
import subprocess, os, uuid, re, time
|
|
|
|
def esc(v):
|
|
return str(v).replace("'", "''")
|
|
|
|
def slugify(s):
|
|
return re.sub(r"[^a-z0-9]+", "-", s.lower()).strip("-")
|
|
|
|
def run_sql(sql):
|
|
r = subprocess.run(
|
|
["psql", "-h", "localhost", "-p", "5433", "-U", "tip", "-d", "transceiver_db", "-c", sql],
|
|
capture_output=True, text=True,
|
|
env={**os.environ, "PGPASSWORD": "tip_prod_2026"}
|
|
)
|
|
if "ERROR" in r.stderr:
|
|
print(f"ERR: {r.stderr.strip()[:200]}")
|
|
return r
|
|
|
|
def query_val(sql):
|
|
r = subprocess.run(
|
|
["psql", "-h", "localhost", "-p", "5433", "-U", "tip", "-d", "transceiver_db", "-t", "-A", "-c", sql],
|
|
capture_output=True, text=True,
|
|
env={**os.environ, "PGPASSWORD": "tip_prod_2026"}
|
|
)
|
|
return r.stdout.strip()
|
|
|
|
VENDORS = [
|
|
("10Gtek", "manufacturer", "https://www.10gtek.com", "CN"),
|
|
("FS.COM", "manufacturer", "https://www.fs.com", "CN"),
|
|
("ProLabs", "manufacturer", "https://www.prolabs.com", "GB"),
|
|
("Champion ONE", "manufacturer", "https://www.championone.com", "US"),
|
|
("Axiom Memory", "manufacturer", "https://www.axiomupgrades.com", "US"),
|
|
("Approved Networks", "manufacturer", "https://www.approvednetworks.com", "US"),
|
|
("AddOn Networks", "manufacturer", "https://www.addonnetworks.com", "US"),
|
|
("FluxLight", "manufacturer", "https://fluxlight.com", "US"),
|
|
("NADDOD", "manufacturer", "https://www.naddod.com", "CN"),
|
|
("Innolight", "manufacturer", "https://www.innolight.com", "CN"),
|
|
("Eoptolink", "manufacturer", "https://www.eoptolink.com", "CN"),
|
|
("Hisense Broadband", "manufacturer", "https://www.hisense-broadband.com", "CN"),
|
|
("Source Photonics", "manufacturer", "https://www.sourcephotonics.com", "US"),
|
|
("Lumentum", "manufacturer", "https://www.lumentum.com", "US"),
|
|
("II-VI/Coherent", "manufacturer", "https://www.coherent.com", "US"),
|
|
("Broadcom/Avago", "manufacturer", "https://www.broadcom.com", "US"),
|
|
("Intel", "manufacturer", "https://www.intel.com", "US"),
|
|
("Mellanox", "manufacturer", "https://network.nvidia.com", "IL"),
|
|
("Finisar", "manufacturer", "https://www.coherent.com", "US"),
|
|
("Molex", "manufacturer", "https://www.molex.com", "US"),
|
|
("Oplink", "manufacturer", "https://www.oplink.com", "US"),
|
|
("MACOM", "manufacturer", "https://www.macom.com", "US"),
|
|
("Accelink", "manufacturer", "https://www.accelink.com", "CN"),
|
|
("HG Genuine", "manufacturer", "https://www.hggenuine.com", "CN"),
|
|
("Gigalight", "manufacturer", "https://www.gigalight.com", "CN"),
|
|
("QSFPTEK", "manufacturer", "https://www.qsfptek.com", "CN"),
|
|
("Edgeium", "manufacturer", "https://edgeium.com", "US"),
|
|
("Precision OT", "manufacturer", "https://www.precisionot.com", "US"),
|
|
("SintronTech/Optech", "manufacturer", "https://sintrontech.com", "TW"),
|
|
("Optcore", "manufacturer", "https://www.optcore.net", "CN"),
|
|
("Hummingbird Networks", "manufacturer", "https://www.hummingbirdnetworks.com", "US"),
|
|
]
|
|
|
|
print(f"Inserting {len(VENDORS)} vendors with slugs...")
|
|
ok = 0
|
|
for name, vtype, url, country in VENDORS:
|
|
vid = str(uuid.uuid4())
|
|
slug = slugify(name)
|
|
sql = (f"INSERT INTO vendors (id, name, slug, type, website, country) "
|
|
f"VALUES ('{vid}', '{esc(name)}', '{slug}', '{vtype}', '{esc(url)}', '{country}') "
|
|
f"ON CONFLICT (name) DO NOTHING;")
|
|
r = run_sql(sql)
|
|
if "ERROR" not in r.stderr:
|
|
ok += 1
|
|
print(f"Vendors inserted: {ok}/{len(VENDORS)}")
|
|
|
|
# Get 10Gtek vendor ID
|
|
v10g = query_val("SELECT id FROM vendors WHERE name='10Gtek';")
|
|
print(f"10Gtek vendor ID: {v10g}")
|
|
|
|
if v10g:
|
|
PRODUCTS = [
|
|
("ASF-GE-T", "1000BASE-T SFP", 1, None, "100m", "RJ-45", "CAT5e", "COM", 1.0),
|
|
("ASF85-24-X2-D", "1000BASE-SX SFP", 1, "850nm", "550m", "LC Duplex", "MMF", "COM", 0.8),
|
|
("ASF13-24-20-D", "1000BASE-LX SFP", 1, "1310nm", "20km", "LC Duplex", "SMF", "COM", 0.8),
|
|
("ASF13-24-40-D", "1000BASE-LHX SFP", 1, "1310nm", "40km", "LC Duplex", "SMF", "COM", 1.0),
|
|
("ASF15-24-40-D", "1000BASE-EX SFP", 1, "1550nm", "40km", "LC Duplex", "SMF", "COM", 1.0),
|
|
("ASF15-24-80-D", "1000BASE-ZX SFP", 1, "1550nm", "80km", "LC Duplex", "SMF", "COM", 1.5),
|
|
("ASF15-24-100-D", "1000BASE-EZX-100", 1, "1550nm", "100km", "LC Duplex", "SMF", "COM", 2.0),
|
|
("ASF15-24-120-D", "1000BASE-EZX-120", 1, "1550nm", "120km", "LC Duplex", "SMF", "COM", 2.5),
|
|
("AMQ28-SR4-M1", "100G QSFP28 SR4", 100, "850nm", "100m", "MPO-12", "MMF OM4", "COM", 2.5),
|
|
("ALQ28-IR4-02", "100G QSFP28 IR4", 100, "1310nm", "2km", "MPO-12", "SMF", "COM", 3.0),
|
|
("ALQ28-CW4-02", "100G QSFP28 CWDM4", 100, "CWDM4", "2km", "LC Duplex", "SMF", "COM", 3.0),
|
|
("ALQ28-LR4-10", "100G QSFP28 LR4", 100, "LWDM4", "10km", "LC Duplex", "SMF", "COM", 3.5),
|
|
("ALQ28-LR4-20", "100G QSFP28 ELR4+", 100, "LWDM4", "20km", "LC Duplex", "SMF", "COM", 4.0),
|
|
("ALQ28-ER4-30", "100G QSFP28 ER4", 100, "LWDM4", "30km", "LC Duplex", "SMF", "COM", 4.5),
|
|
("AZS85-S28-M1", "25G SFP28 SR", 25, "850nm", "100m", "LC Duplex", "MMF OM4", "COM", 1.0),
|
|
("AZS13-S28-02", "25G SFP28 IR", 25, "1310nm", "2km", "LC Duplex", "SMF", "COM", 1.5),
|
|
("AZS13-S28-10", "25G SFP28 LR", 25, "1310nm", "10km", "LC Duplex", "SMF", "COM", 1.5),
|
|
("AZS13-S28-20", "25G SFP28 LR20", 25, "1310nm", "20km", "LC Duplex", "SMF", "COM", 2.0),
|
|
("AZS13-S28-40", "25G SFP28 ER", 25, "1310nm", "40km", "LC Duplex", "SMF", "COM", 2.0),
|
|
]
|
|
ok = 0
|
|
for pn, name, speed, wl, reach, conn, fiber, temp, power in PRODUCTS:
|
|
tid = str(uuid.uuid4())
|
|
slug = slugify(f"10gtek-{pn}")
|
|
wl_v = f"'{wl}'" if wl else "NULL"
|
|
sql = (f"INSERT INTO transceivers (id, vendor_id, slug, part_number, standard_name, "
|
|
f"speed_gbps, wavelengths, reach_label, connector, fiber_type, temp_range, "
|
|
f"power_consumption_w, product_page_url) VALUES ("
|
|
f"'{tid}', '{v10g}', '{slug}', '{esc(pn)}', '{esc(name)}', "
|
|
f"{speed}, {wl_v}, '{esc(reach)}', '{esc(conn)}', '{esc(fiber)}', '{temp}', "
|
|
f"{power}, 'https://www.10gtek.com') ON CONFLICT DO NOTHING;")
|
|
r = run_sql(sql)
|
|
if "ERROR" not in r.stderr:
|
|
ok += 1
|
|
print(f"10Gtek transceivers inserted: {ok}/{len(PRODUCTS)}")
|
|
|
|
# Restart API
|
|
subprocess.run(["bash", "-c", "cd /opt/tip && pm2 restart tip-api"], capture_output=True)
|
|
|
|
# Final counts
|
|
for q in [
|
|
"SELECT 'vendors=' || count(*) FROM vendors",
|
|
"SELECT 'transceivers=' || count(*) FROM transceivers",
|
|
"SELECT 'market_data=' || count(*) FROM market_data",
|
|
"SELECT 'form_factors=' || count(*) FROM form_factors",
|
|
"SELECT 'lifecycle=' || count(*) FROM technology_lifecycle",
|
|
"SELECT 'dwdm=' || count(*) FROM dwdm_channels",
|
|
"SELECT 'cwdm=' || count(*) FROM cwdm_channels",
|
|
"SELECT 'compat=' || count(*) FROM compatibility",
|
|
]:
|
|
print(query_val(q))
|
|
|
|
print(f"{time.strftime('%H:%M:%S')}: FIX DONE")
|