cstr/conceptnet_normalized
1
1import gradio as gr2import sqlite33import pandas as pd4from huggingface_hub import hf_hub_download5import os6import time7import json8from typing import Dict, List, Optional9from collections import defaultdict10 11# ===== CONFIGURATION =====12# 1. Point to the NEW normalized database (fixed)13TARGET_LANGUAGES = ['en', 'fr', 'it', 'de', 'es', 'ar', 'fa', 'grc', 'he', 'la', 'hbo']14NORMALIZED_REPO_ID = "cstr/conceptnet-normalized-multi"15NORMALIZED_DB_FILE = "conceptnet_normalized.db"16 17CONCEPTNET_BASE = "http://conceptnet.io"18# =========================19 20# --- All relations MUST be full URLs ---21# This dictionary is now our primary way to map names to relation IDs22CONCEPTNET_RELATIONS: Dict[str, str] = {23 "RelatedTo": f"{CONCEPTNET_BASE}/r/RelatedTo",24 "IsA": f"{CONCEPTNET_BASE}/r/IsA",25 "InstanceOf": f"{CONCEPTNET_BASE}/r/InstanceOf", 26 "PartOf": f"{CONCEPTNET_BASE}/r/PartOf",27 "HasA": f"{CONCEPTNET_BASE}/r/HasA",28 "UsedFor": f"{CONCEPTNET_BASE}/r/UsedFor",29 "CapableOf": f"{CONCEPTNET_BASE}/r/CapableOf",30 "AtLocation": f"{CONCEPTNET_BASE}/r/AtLocation",31 "Causes": f"{CONCEPTNET_BASE}/r/Causes",32 "HasSubevent": f"{CONCEPTNET_BASE}/r/HasSubevent",33 "HasFirstSubevent": f"{CONCEPTNET_BASE}/r/HasFirstSubevent",34 "HasLastSubevent": f"{CONCEPTNET_BASE}/r/HasLastSubevent",35 "HasPrerequisite": f"{CONCEPTNET_BASE}/r/HasPrerequisite",36 "HasProperty": f"{CONCEPTNET_BASE}/r/HasProperty",37 "MotivatedByGoal": f"{CONCEPTNET_BASE}/r/MotivatedByGoal",38 "ObstructedBy": f"{CONCEPTNET_BASE}/r/ObstructedBy",39 "Desires": f"{CONCEPTNET_BASE}/r/Desires",40 "CreatedBy": f"{CONCEPTNET_BASE}/r/CreatedBy",41 "Synonym": f"{CONCEPTNET_BASE}/r/Synonym",42 "Antonym": f"{CONCEPTNET_BASE}/r/Antonym",43 "DistinctFrom": f"{CONCEPTNET_BASE}/r/DistinctFrom",44 "DerivedFrom": f"{CONCEPTNET_BASE}/r/DerivedFrom",45 "SymbolOf": f"{CONCEPTNET_BASE}/r/SymbolOf",46 "DefinedAs": f"{CONCEPTNET_BASE}/r/DefinedAs",47 "MannerOf": f"{CONCEPTNET_BASE}/r/MannerOf",48 "LocatedNear": f"{CONCEPTNET_BASE}/r/LocatedNear",49 "HasContext": f"{CONCEPTNET_BASE}/r/HasContext",50 "SimilarTo": f"{CONCEPTNET_BASE}/r/SimilarTo",51 "EtymologicallyRelatedTo": f"{CONCEPTNET_BASE}/r/EtymologicallyRelatedTo",52 "EtymologicallyDerivedFrom": f"{CONCEPTNET_BASE}/r/EtymologicallyDerivedFrom",53 "CausesDesire": f"{CONCEPTNET_BASE}/r/CausesDesire",54 "MadeOf": f"{CONCEPTNET_BASE}/r/MadeOf",55 "ReceivesAction": f"{CONCEPTNET_BASE}/r/ReceivesAction",56 "ExternalURL": f"{CONCEPTNET_BASE}/r/ExternalURL",57 "NotDesires": f"{CONCEPTNET_BASE}/r/NotDesires",58 "NotUsedFor": f"{CONCEPTNET_BASE}/r/NotUsedFor",59 "NotCapableOf": f"{CONCEPTNET_BASE}/r/NotCapableOf",60 "NotHasProperty": f"{CONCEPTNET_BASE}/r/NotHasProperty",61}62# =========================63 64print(f"🌍 Languages: {', '.join([l.upper() for l in TARGET_LANGUAGES])}")65print(f"📚 Relations: {len(CONCEPTNET_RELATIONS)} relations loaded")66 67def log_progress(message, level="INFO"):68 """Simple logger with timestamp and emoji prefix."""69 timestamp = time.strftime("%H:%M:%S")70 prefix = {"INFO": "ℹ️ ", "SUCCESS": "✅", "ERROR": "❌", "WARN": "⚠️ ", "DEBUG": "🔍"}.get(level, "")71 print(f"[{timestamp}] {prefix} {message}")72 73def download_normalized_database():74 """Download the NEW normalized database from HF Hub."""75 log_progress(f"Downloading/Verifying {NORMALIZED_DB_FILE}...", "INFO")76 try:77 # This will download or use cache78 return hf_hub_download(79 repo_id=NORMALIZED_REPO_ID,80 filename=NORMALIZED_DB_FILE,81 repo_type="dataset"82 )83 except Exception as e:84 log_progress(f"Failed to download DB: {e}", "ERROR")85 return None86 87DB_PATH = download_normalized_database()88 89if not DB_PATH:90 log_progress("DATABASE NOT FOUND. App will not function.", "ERROR")91else:92 log_progress(f"Database loaded from: {DB_PATH}", "SUCCESS")93 94def get_db_connection():95 """Get a thread-safe, read-only connection to the SQLite database."""96 if not DB_PATH:97 raise Exception("Database path is not set. Cannot create connection.")98 # Connect in read-only mode99 db_uri = f"file:{DB_PATH}?mode=ro"100 conn = sqlite3.connect(db_uri, uri=True, check_same_thread=False)101 conn.execute("PRAGMA cache_size = -256000") # 256MB cache102 conn.execute("PRAGMA temp_store = MEMORY")103 return conn104 105def node_url_to_label(url: str) -> str:106 """Extract the term from ConceptNet URL: http://conceptnet.io/c/{lang}/{term}/..."""107 try:108 parts = url.split('/')109 # Term is ALWAYS at index 5110 if len(parts) >= 6 and parts[3] == 'c':111 return parts[5].replace('_', ' ')112 except:113 pass114 return url # Fallback to full URL if parsing fails115 116def get_semantic_profile(word: str, lang: str = 'en', selected_relations: List[str] = None, progress=gr.Progress()):117 """118 --- REWRITTEN FOR NORMALIZED DB ---119 Get semantic profile for a word.120 This function is now extremely fast, running 4 queries total instead of 2N.121 """122 log_progress(f"Profile: {word} ({lang})", "INFO")123 124 if not word or lang not in TARGET_LANGUAGES:125 yield "⚠️ Invalid input"126 return127 128 if not DB_PATH:129 yield "❌ **Error:** Database file not found."130 return131 132 # Set default relations if none are selected133 if selected_relations is None or len(selected_relations) == 0:134 selected_relations = [135 "IsA", "RelatedTo", "PartOf", "HasA", "UsedFor", 136 "CapableOf", "Synonym", "Antonym"137 ]138 139 word = word.strip().lower().replace(' ', '_')140 exact_path = f"{CONCEPTNET_BASE}/c/{lang}/{word}"141 142 output_md = f"# 🧠 Semantic Profile: '{word}' ({lang.upper()})\n\n"143 144 try:145 with get_db_connection() as conn:146 cursor = conn.cursor()147 progress(0, desc="Starting...")148 yield output_md149 150 # === STEP 1: Find Node PKs ===151 progress(0.05, desc="Finding nodes...")152 153 cursor.execute("SELECT node_pk, node_url FROM node_norm WHERE node_url = ?", (exact_path,))154 exact_node = cursor.fetchone()155 156 node_pks = []157 nodes_found = []158 159 if exact_node:160 log_progress(f"Found exact node: {exact_node[1]}", "SUCCESS")161 node_pks = [exact_node[0]]162 nodes_found = [(exact_node[1], node_url_to_label(exact_node[1]))]163 else:164 log_progress(f"No exact node, falling back to LIKE...", "WARN")165 like_path = f"{exact_path}%"166 cursor.execute("SELECT node_pk, node_url FROM node_norm WHERE node_url LIKE ? LIMIT 5", (like_path,))167 nodes = cursor.fetchall()168 if not nodes:169 yield f"# 🧠 '{word}'\n\n⚠️ Not found"170 return171 node_pks = [n[0] for n in nodes]172 nodes_found = [(n[1], node_url_to_label(n[1])) for n in nodes]173 174 for node_url, label in nodes_found[:3]:175 output_md += f"**Node:** `{node_url}` → **{label}**\n"176 output_md += "\n"177 yield output_md178 179 # === STEP 2: Find Relation PKs ===180 progress(0.15, desc="Finding relations...")181 182 rel_urls_to_query = tuple(CONCEPTNET_RELATIONS[name] for name in selected_relations if name in CONCEPTNET_RELATIONS)183 if not rel_urls_to_query:184 output_md += "⚠️ No valid relations selected."185 yield output_md186 return187 188 rel_placeholders = ','.join(['?'] * len(rel_urls_to_query))189 cursor.execute(f"SELECT rel_pk, rel_url FROM rel_norm WHERE rel_url IN ({rel_placeholders})", rel_urls_to_query)190 191 # Create lookup maps192 rel_pk_to_name = {}193 rel_name_to_pk = {}194 rel_name_to_url = {}195 for pk, url in cursor.fetchall():196 # Find the 'short name' (e.g., 'IsA') from the full URL197 for name, url_val in CONCEPTNET_RELATIONS.items():198 if url_val == url:199 rel_pk_to_name[pk] = name200 rel_name_to_pk[name] = pk201 rel_name_to_url[name] = url202 break203 204 rel_pks_to_query = tuple(rel_pk_to_name.keys())205 node_pk_placeholders = ','.join(['?'] * len(node_pks))206 rel_pk_placeholders = ','.join(['?'] * len(rel_pks_to_query))207 208 # Buckets for results209 outgoing_results = defaultdict(list)210 incoming_results = defaultdict(list)211 212 # === STEP 3: Run ONE query for ALL outgoing edges ===213 progress(0.4, desc="Querying outgoing edges...")214 sql_out = f"""215 SELECT216 e.rel_fk, n_end.node_url, e.weight217 FROM edge_norm e218 JOIN node_norm n_end ON e.end_fk = n_end.node_pk219 WHERE220 e.start_fk IN ({node_pk_placeholders})221 AND e.rel_fk IN ({rel_pk_placeholders})222 ORDER BY e.weight DESC223 LIMIT 200 224 """225 cursor.execute(sql_out, (*node_pks, *rel_pks_to_query))226 227 for rel_pk, node_url, weight in cursor.fetchall():228 rel_name = rel_pk_to_name.get(rel_pk)229 if rel_name and len(outgoing_results[rel_name]) < 7:230 outgoing_results[rel_name].append((node_url_to_label(node_url), weight))231 232 # === STEP 4: Run ONE query for ALL incoming edges ===233 progress(0.7, desc="Querying incoming edges...")234 sql_in = f"""235 SELECT236 e.rel_fk, n_start.node_url, e.weight237 FROM edge_norm e238 JOIN node_norm n_start ON e.start_fk = n_start.node_pk239 WHERE240 e.end_fk IN ({node_pk_placeholders})241 AND e.rel_fk IN ({rel_pk_placeholders})242 ORDER BY e.weight DESC243 LIMIT 200244 """245 cursor.execute(sql_in, (*node_pks, *rel_pks_to_query))246 247 for rel_pk, node_url, weight in cursor.fetchall():248 rel_name = rel_pk_to_name.get(rel_pk)249 if rel_name and len(incoming_results[rel_name]) < 7:250 incoming_results[rel_name].append((node_url_to_label(node_url), weight))251 252 # === STEP 5: Format results as Markdown ===253 progress(0.9, desc="Formatting results...")254 total = 0255 for rel_name in selected_relations:256 if rel_name not in rel_name_to_pk:257 continue # Skip if this relation wasn't in the DB258 259 output_md += f"## {rel_name}\n\n"260 found = False261 262 out_edges = outgoing_results.get(rel_name, [])263 for label, weight in out_edges:264 output_md += f"- **{word}** {rel_name} → *{label}* `[{weight:.3f}]`\n"265 found = True266 total += 1267 268 in_edges = incoming_results.get(rel_name, [])269 for label, weight in in_edges:270 output_md += f"- *{label}* {rel_name} → **{word}** `[{weight:.3f}]`\n"271 found = True272 total += 1273 274 if not found:275 output_md += "*No results*\n"276 277 output_md += "\n"278 yield output_md # Yield after each relation is formatted279 280 output_md += f"---\n**Total relations:** {total}\n"281 log_progress(f"Profile complete: {total} relations", "SUCCESS")282 progress(1.0, desc="✅ Complete!")283 yield output_md284 285 except Exception as e:286 log_progress(f"Error: {e}", "ERROR")287 import traceback288 traceback.print_exc()289 yield f"**❌ Error:** {e}"290 291def run_query(start_node, start_lang, relation, end_node, end_lang, limit, progress=gr.Progress()):292 """293 Query builder using fast integer joins.294 """295 log_progress(f"Query: start={start_node} ({start_lang}), rel={relation}, end={end_node} ({end_lang})", "INFO")296 progress(0, desc="Building...")297 298 if not DB_PATH:299 return pd.DataFrame(), "❌ **Error:** Database file not found."300 301 # This is the new, fast query302 query = """303 SELECT304 n_start.node_url AS start_url,305 r.rel_url AS relation_url,306 n_end.node_url AS end_url,307 e.weight308 FROM edge_norm e309 JOIN node_norm n_start ON e.start_fk = n_start.node_pk310 JOIN node_norm n_end ON e.end_fk = n_end.node_pk311 JOIN rel_norm r ON e.rel_fk = r.rel_pk312 """313 314 params = []315 where_clauses = []316 317 try:318 with get_db_connection() as conn:319 progress(0.3, desc="Adding filters...")320 321 # Start node - USE start_lang322 if start_node and start_node.strip():323 if start_node.startswith('http://'):324 pattern = f"{start_node}%"325 else:326 pattern = f"{CONCEPTNET_BASE}/c/{start_lang}/{start_node.strip().lower().replace(' ', '_')}%"327 where_clauses.append("n_start.node_url LIKE ?")328 params.append(pattern)329 330 # Relation331 if relation and relation.strip():332 rel_value = CONCEPTNET_RELATIONS.get(relation.strip())333 if rel_value:334 where_clauses.append("r.rel_url = ?")335 params.append(rel_value)336 337 # End node - USE end_lang338 if end_node and end_node.strip():339 if end_node.startswith('http://'):340 pattern = f"{end_node}%"341 else:342 pattern = f"{CONCEPTNET_BASE}/c/{end_lang}/{end_node.strip().lower().replace(' ', '_')}%"343 where_clauses.append("n_end.node_url LIKE ?")344 params.append(pattern)345 346 if where_clauses:347 query += " WHERE " + " AND ".join(where_clauses)348 349 query += " ORDER BY e.weight DESC LIMIT ?"350 params.append(limit)351 352 progress(0.6, desc="Executing...")353 354 start_time = time.time()355 df = pd.read_sql_query(query, conn, params=params)356 elapsed = time.time() - start_time357 358 log_progress(f"Query done: {len(df)} rows in {elapsed:.2f}s", "SUCCESS")359 progress(1.0, desc="Done!")360 361 if df.empty:362 return pd.DataFrame(), f"⚠️ No results ({elapsed:.2f}s)"363 364 # Add user-friendly labels from the URLs365 df['start_label'] = df['start_url'].apply(node_url_to_label)366 df['end_label'] = df['end_url'].apply(node_url_to_label)367 df['relation'] = df['relation_url'].apply(lambda x: x.split('/')[-1])368 369 # Reorder columns370 df = df[['start_label', 'relation', 'end_label', 'weight', 'start_url', 'end_url', 'relation_url']]371 372 return df, f"✅ {len(df)} results in {elapsed:.2f}s"373 374 except Exception as e:375 log_progress(f"Error: {e}", "ERROR")376 import traceback377 traceback.print_exc()378 return pd.DataFrame(), f"❌ {e}"379 380def run_raw_query(sql_query):381 """Execute a raw SELECT SQL query against the normalized DB."""382 if not sql_query.strip().upper().startswith("SELECT"):383 return pd.DataFrame(), "❌ Only SELECT queries are allowed."384 385 if not DB_PATH:386 return pd.DataFrame(), "❌ **Error:** Database file not found."387 388 try:389 with get_db_connection() as conn:390 start = time.time()391 df = pd.read_sql_query(sql_query, conn)392 elapsed = time.time() - start393 return df, f"✅ {len(df)} rows in {elapsed:.3f}s"394 except Exception as e:395 return pd.DataFrame(), f"❌ {e}"396 397def get_schema_info():398 """399 --- REWRITTEN FOR NORMALIZED DB ---400 Get schema information for the new database.401 """402 if not DB_PATH:403 return "❌ **Error:** Database file not found."404 405 md = f"# 📚 Schema (Normalized)\n\n"406 md += f"**Repo:** [{NORMALIZED_REPO_ID}](https://huggingface.co/datasets/{NORMALIZED_REPO_ID})\n\n"407 md += "**Schema:** Text URLs (`node_norm`, `rel_norm`) are stored once. The `edge_norm` table uses fast integer keys (`_fk`) for joins.\n\n"408 409 try:410 with get_db_connection() as conn:411 cursor = conn.cursor()412 413 md += "## Tables & Row Counts\n\n"414 # Use the new table names415 for table in ["node_norm", "rel_norm", "edge_norm"]:416 cursor.execute(f"SELECT COUNT(*) FROM {table}")417 md += f"- **{table}:** {cursor.fetchone()[0]:,} rows\n"418 419 md += "\n## Indices\n\n"420 cursor.execute("SELECT name, sql FROM sqlite_master WHERE type='index' AND sql IS NOT NULL")421 for name, sql in cursor.fetchall():422 md += f"- **{name}:** `{sql}`\n"423 424 md += "\n## Common Relations (from `rel_norm`)\n\n"425 # Query the new relation table426 cursor.execute("SELECT rel_url FROM rel_norm ORDER BY rel_url LIMIT 20")427 for (rel_url,) in cursor.fetchall():428 label = rel_url.split('/')[-1]429 md += f"- **{label}:** `{rel_url}`\n"430 431 except Exception as e:432 md += f"\n**❌ Error:** {e}\n"433 434 return md435 436# ===== Build Gradio UI (Mostly Unchanged) =====437with gr.Blocks(title="ConceptNet Explorer", theme=gr.themes.Soft()) as demo:438 gr.Markdown("# 🧠 ConceptNet Explorer (Normalized v2)")439 gr.Markdown(f"**Repo:** `{NORMALIZED_REPO_ID}` | **Languages:** {', '.join([l.upper() for l in TARGET_LANGUAGES])}")440 441 if not DB_PATH:442 gr.Markdown("## ❌ ERROR: DATABASE FILE NOT FOUND")443 gr.Markdown(f"This app cannot start because `{NORMALIZED_DB_FILE}` could not be downloaded from `{NORMALIZED_REPO_ID}`. Please check the logs.")444 445 else:446 with gr.Tabs():447 with gr.TabItem("🔍 Semantic Profile"):448 gr.Markdown("**Explore semantic relations for any word. Runs on the fast normalized DB.**")449 450 with gr.Row():451 word_input = gr.Textbox(label="Word", placeholder="e.g., dog, hund, perro", value="dog", scale=3)452 lang_input = gr.Dropdown(choices=TARGET_LANGUAGES, value="en", label="Language", scale=1)453 454 with gr.Accordion("Select Relations (fewer = faster)", open=False):455 relation_input = gr.CheckboxGroup(456 choices=list(CONCEPTNET_RELATIONS.keys()), 457 label="Relations to Query", 458 value=["IsA", "RelatedTo", "PartOf", "HasA", "UsedFor", "CapableOf", "Synonym", "Antonym", "AtLocation", "HasProperty"]459 )460 461 semantic_btn = gr.Button("🔍 Get Semantic Profile", variant="primary", size="lg")462 semantic_output = gr.Markdown(value="Click the button to get the semantic profile.")463 464 gr.Examples(465 examples=[["dog", "en"], ["hund", "de"], ["perro", "es"], ["chat", "fr"], ["knowledge", "en"]],466 inputs=[word_input, lang_input],467 label="Examples"468 )469 470 with gr.TabItem("⚡ Query Builder"):471 gr.Markdown("**Build custom relationship queries (now using fast integer joins).**")472 473 with gr.Row():474 start_input = gr.Textbox(label="Start Node (word)", placeholder="dog (optional)")475 start_lang = gr.Dropdown(choices=TARGET_LANGUAGES, value="en", label="Start Lang", scale=1)476 rel_input = gr.Dropdown(477 choices=[""] + list(CONCEPTNET_RELATIONS.keys()), 478 label="Relation (name)", 479 value="IsA",480 info="Leave blank to query all relations"481 )482 end_input = gr.Textbox(label="End Node (word)", placeholder="(optional)")483 end_lang = gr.Dropdown(choices=TARGET_LANGUAGES, value="en", label="End Lang", scale=1)484 485 limit_slider = gr.Slider(label="Limit", minimum=1, maximum=500, value=50, step=1)486 query_btn = gr.Button("▶️ Run Query", variant="primary", size="lg")487 488 status_output = gr.Markdown()489 results_output = gr.DataFrame(wrap=True) # Height bug is still fixed490 491 with gr.TabItem("💻 Raw SQL"):492 gr.Markdown("**Execute custom `SELECT` SQL queries against the *new normalized schema*.**")493 494 # --- UPDATED Example Query ---495 new_example_sql = f"""SELECT496 n_start.node_url,497 r.rel_url,498 n_end.node_url,499 e.weight500FROM edge_norm e501JOIN node_norm n_start ON e.start_fk = n_start.node_pk502JOIN node_norm n_end ON e.end_fk = n_end.node_pk503JOIN rel_norm r ON e.rel_fk = r.rel_pk504WHERE n_start.node_url = '{CONCEPTNET_BASE}/c/en/dog'505 AND r.rel_url = '{CONCEPTNET_BASE}/r/IsA'506ORDER BY e.weight DESC507LIMIT 10508"""509 raw_sql_input = gr.Textbox(510 label="SQL Query",511 value=new_example_sql,512 lines=13,513 elem_classes=["font-mono"]514 )515 516 raw_btn = gr.Button("▶️ Execute")517 raw_status = gr.Markdown()518 raw_results = gr.DataFrame() # Height bug is still fixed519 520 with gr.TabItem("📊 Schema"):521 gr.Markdown("**View database schema, tables, and indices for the *new normalized DB*.**")522 schema_btn = gr.Button("📊 Load Schema Info")523 schema_output = gr.Markdown()524 525 # --- Button Click Handlers (All API names preserved) ---526 semantic_btn.click(527 get_semantic_profile, 528 inputs=[word_input, lang_input, relation_input], 529 outputs=semantic_output,530 api_name="get_semantic_profile"531 )532 533 query_btn.click(534 run_query, 535 inputs=[start_input, start_lang, rel_input, end_input, end_lang, limit_slider], 536 outputs=[results_output, status_output],537 api_name="run_query"538 )539 540 raw_btn.click(541 run_raw_query, 542 inputs=raw_sql_input, 543 outputs=[raw_results, raw_status],544 api_name="run_raw_query"545 )546 547 demo.load(548 get_schema_info, 549 None, 550 schema_output,551 api_name="get_schema"552 )553 schema_btn.click(554 get_schema_info, 555 None, 556 schema_output,557 api_name="get_schema"558 )559 560if __name__ == "__main__":561 if DB_PATH:562 log_progress("APP READY! (Normalized DB)", "SUCCESS")563 else:564 log_progress("APP LAUNCHING WITH ERRORS (DB NOT FOUND)", "ERROR")565 demo.launch(ssr_mode=False)566