Download core/database.py from Surajnitp/flowmarg: direct link, hf CLI and curl.
- Browser
- Download file 12.9 kB
-
https://huggingface.co/spaces/Surajnitp/flowmarg/resolve/main/core/database.py
- Command line
-
hf download hf://spaces/Surajnitp/flowmarg/core/database.py
-
curl -L -o database.py https://huggingface.co/spaces/Surajnitp/flowmarg/resolve/main/core/database.py
12.9 kB
| """ | |
| SQLite database wrapper for structured track design data with traceability. | |
| """ | |
| import sqlite3 | |
| from pathlib import Path | |
| from typing import Any, Dict, List, Optional | |
| import pandas as pd | |
| DB_PATH = Path(__file__).parent.parent / "data" / "track_design.db" | |
| def get_connection(): | |
| DB_PATH.parent.mkdir(parents=True, exist_ok=True) | |
| conn = sqlite3.connect(str(DB_PATH)) | |
| conn.row_factory = sqlite3.Row | |
| return conn | |
| def init_db(): | |
| conn = get_connection() | |
| cursor = conn.cursor() | |
| cursor.executescript( | |
| """ | |
| CREATE TABLE IF NOT EXISTS chainages ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| alignment TEXT NOT NULL, | |
| chainage REAL NOT NULL, | |
| track_no INTEGER, | |
| type TEXT, | |
| level REAL, | |
| radius REAL, | |
| cant REAL, | |
| grade REAL, | |
| source_file TEXT, | |
| standard_clause TEXT, | |
| UNIQUE(alignment, chainage, type) | |
| ); | |
| CREATE TABLE IF NOT EXISTS survey_features ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| layer TEXT, | |
| name TEXT, | |
| category TEXT, | |
| chainage REAL, | |
| clearance REAL, | |
| height REAL, | |
| description TEXT, | |
| geometry_json TEXT, | |
| source_file TEXT | |
| ); | |
| CREATE TABLE IF NOT EXISTS drawings ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| drawing_number TEXT UNIQUE, | |
| revision TEXT, | |
| title TEXT, | |
| status TEXT, | |
| checker TEXT, | |
| approver TEXT, | |
| chainage_start REAL, | |
| chainage_end REAL, | |
| issue_date TEXT, | |
| notes TEXT, | |
| source_file TEXT | |
| ); | |
| CREATE TABLE IF NOT EXISTS psr ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| track_no INTEGER, | |
| speed_mph INTEGER, | |
| category TEXT, | |
| radius_min REAL, | |
| cant_def REAL, | |
| source_file TEXT, | |
| UNIQUE(track_no, speed_mph, category) | |
| ); | |
| CREATE TABLE IF NOT EXISTS project_metadata ( | |
| key TEXT PRIMARY KEY, | |
| value TEXT | |
| ); | |
| CREATE TABLE IF NOT EXISTS provenance ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| entity_type TEXT NOT NULL, | |
| entity_id TEXT, | |
| source_file TEXT, | |
| chainage REAL, | |
| standard_clause TEXT, | |
| created_at TEXT DEFAULT CURRENT_TIMESTAMP | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_chainages_alignment ON chainages(alignment); | |
| CREATE INDEX IF NOT EXISTS idx_chainages_chainage ON chainages(chainage); | |
| CREATE INDEX IF NOT EXISTS idx_chainages_ch ON chainages(chainage); | |
| CREATE INDEX IF NOT EXISTS idx_chainages_type ON chainages(type); | |
| CREATE INDEX IF NOT EXISTS idx_survey_ch ON survey_features(chainage); | |
| CREATE INDEX IF NOT EXISTS idx_provenance_ch ON provenance(chainage); | |
| """ | |
| ) | |
| for stmt in ( | |
| "ALTER TABLE chainages ADD COLUMN source_file TEXT", | |
| "ALTER TABLE chainages ADD COLUMN standard_clause TEXT", | |
| "ALTER TABLE survey_features ADD COLUMN source_file TEXT", | |
| "ALTER TABLE drawings ADD COLUMN source_file TEXT", | |
| "ALTER TABLE psr ADD COLUMN source_file TEXT", | |
| ): | |
| try: | |
| cursor.execute(stmt) | |
| except sqlite3.OperationalError: | |
| pass | |
| conn.commit() | |
| conn.close() | |
| def log_provenance( | |
| entity_type: str, | |
| entity_id: str = None, | |
| source_file: str = None, | |
| chainage: float = None, | |
| standard_clause: str = None, | |
| ): | |
| conn = get_connection() | |
| conn.execute( | |
| """ | |
| INSERT INTO provenance (entity_type, entity_id, source_file, chainage, standard_clause) | |
| VALUES (?, ?, ?, ?, ?) | |
| """, | |
| (entity_type, entity_id, source_file, chainage, standard_clause), | |
| ) | |
| conn.commit() | |
| conn.close() | |
| insert_provenance = log_provenance | |
| def insert_chainages( | |
| df: pd.DataFrame, | |
| type_label: str, | |
| alignment: str = None, | |
| source_file: str = None, | |
| standard_clause: str = "NR/L2/TRK/2500", | |
| ): | |
| if df is None or df.empty: | |
| return | |
| conn = get_connection() | |
| cursor = conn.cursor() | |
| def _insert_row(align: str, row): | |
| ch = row.get("datum_chainage") | |
| if ch is None or pd.isna(ch): | |
| return | |
| radius = row.get("center_radius_m") or row.get("offset_radius_m") | |
| cant = row.get("center_cant_m") or row.get("offset_cant_m") | |
| level = row.get("level_m") | |
| grade = row.get("center_grade") | |
| cursor.execute( | |
| """ | |
| INSERT OR REPLACE INTO chainages | |
| (alignment, chainage, type, level, radius, cant, grade, source_file, standard_clause) | |
| VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) | |
| """, | |
| ( | |
| align, | |
| float(ch), | |
| type_label, | |
| None if level is None or pd.isna(level) else float(level), | |
| None if radius is None or pd.isna(radius) else float(radius), | |
| None if cant is None or pd.isna(cant) else float(cant), | |
| None if grade is None or pd.isna(grade) else float(grade), | |
| source_file, | |
| standard_clause, | |
| ), | |
| ) | |
| cursor.execute( | |
| """ | |
| INSERT INTO provenance (entity_type, entity_id, source_file, chainage, standard_clause) | |
| VALUES (?, ?, ?, ?, ?) | |
| """, | |
| ("chainage", f"{align}:{type_label}:{ch}", source_file, float(ch), standard_clause), | |
| ) | |
| if alignment is None and "offset_alignment" in df.columns: | |
| for align, group in df.groupby("offset_alignment"): | |
| for _, row in group.iterrows(): | |
| _insert_row(str(align), row) | |
| else: | |
| align = alignment or "Unknown" | |
| for _, row in df.iterrows(): | |
| _insert_row(align, row) | |
| conn.commit() | |
| conn.close() | |
| def insert_survey_features(features: List[Dict], source_file: str = None): | |
| if not features: | |
| return | |
| conn = get_connection() | |
| cursor = conn.cursor() | |
| for feat in features: | |
| cursor.execute( | |
| """ | |
| INSERT INTO survey_features | |
| (layer, name, category, chainage, clearance, height, description, geometry_json, source_file) | |
| VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) | |
| """, | |
| ( | |
| feat.get("layer"), | |
| feat.get("name"), | |
| feat.get("category"), | |
| feat.get("chainage"), | |
| feat.get("properties", {}).get("clearance") if isinstance(feat.get("properties"), dict) else None, | |
| feat.get("properties", {}).get("height") if isinstance(feat.get("properties"), dict) else None, | |
| feat.get("description", ""), | |
| str(feat.get("geometry", {})), | |
| source_file or feat.get("source_file"), | |
| ), | |
| ) | |
| if feat.get("chainage") is not None: | |
| cursor.execute( | |
| """ | |
| INSERT INTO provenance (entity_type, entity_id, source_file, chainage, standard_clause) | |
| VALUES (?, ?, ?, ?, ?) | |
| """, | |
| ( | |
| "survey", | |
| feat.get("name"), | |
| source_file, | |
| feat.get("chainage"), | |
| "survey/DXF", | |
| ), | |
| ) | |
| conn.commit() | |
| conn.close() | |
| def insert_drawing_metadata(drawing_data: Dict): | |
| conn = get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute( | |
| """ | |
| INSERT OR REPLACE INTO drawings | |
| (drawing_number, revision, title, status, checker, approver, | |
| chainage_start, chainage_end, issue_date, notes, source_file) | |
| VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) | |
| """, | |
| ( | |
| drawing_data.get("number"), | |
| drawing_data.get("revision"), | |
| drawing_data.get("title"), | |
| drawing_data.get("status"), | |
| drawing_data.get("checker"), | |
| drawing_data.get("approver"), | |
| drawing_data.get("chainage_start"), | |
| drawing_data.get("chainage_end"), | |
| drawing_data.get("issue_date"), | |
| drawing_data.get("notes"), | |
| drawing_data.get("source_file"), | |
| ), | |
| ) | |
| cursor.execute( | |
| """ | |
| INSERT INTO provenance (entity_type, entity_id, source_file, chainage, standard_clause) | |
| VALUES (?, ?, ?, ?, ?) | |
| """, | |
| ( | |
| "drawing", | |
| drawing_data.get("number") or drawing_data.get("title"), | |
| drawing_data.get("source_file"), | |
| drawing_data.get("chainage_start"), | |
| "drawing review", | |
| ), | |
| ) | |
| conn.commit() | |
| conn.close() | |
| def insert_psr_data( | |
| track_no: int, | |
| speed: int, | |
| category: str, | |
| radius_min: float, | |
| cant_def: float = 110, | |
| source_file: str = "manual", | |
| ): | |
| conn = get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute( | |
| """ | |
| INSERT OR REPLACE INTO psr (track_no, speed_mph, category, radius_min, cant_def, source_file) | |
| VALUES (?, ?, ?, ?, ?, ?) | |
| """, | |
| (track_no, speed, category, radius_min, cant_def, source_file), | |
| ) | |
| cursor.execute( | |
| """ | |
| INSERT INTO provenance (entity_type, entity_id, source_file, chainage, standard_clause) | |
| VALUES (?, ?, ?, ?, ?) | |
| """, | |
| ("psr", str(track_no), source_file, None, "NR/L2/TRK/2500 Section 5"), | |
| ) | |
| conn.commit() | |
| conn.close() | |
| def query_chainages( | |
| alignment: str = None, | |
| type_label: str = None, | |
| ch_start: float = None, | |
| ch_end: float = None, | |
| ) -> pd.DataFrame: | |
| conn = get_connection() | |
| query = "SELECT * FROM chainages WHERE 1=1" | |
| params: List[Any] = [] | |
| if alignment: | |
| query += " AND alignment = ?" | |
| params.append(alignment) | |
| if type_label: | |
| query += " AND type = ?" | |
| params.append(type_label) | |
| if ch_start is not None: | |
| query += " AND chainage >= ?" | |
| params.append(ch_start) | |
| if ch_end is not None: | |
| query += " AND chainage <= ?" | |
| params.append(ch_end) | |
| df = pd.read_sql_query(query, conn, params=params) | |
| conn.close() | |
| return df | |
| def query_survey_features( | |
| category: str = None, ch_start: float = None, ch_end: float = None | |
| ) -> pd.DataFrame: | |
| conn = get_connection() | |
| query = "SELECT * FROM survey_features WHERE 1=1" | |
| params: List[Any] = [] | |
| if category: | |
| query += " AND category = ?" | |
| params.append(category) | |
| if ch_start is not None: | |
| query += " AND chainage >= ?" | |
| params.append(ch_start) | |
| if ch_end is not None: | |
| query += " AND chainage <= ?" | |
| params.append(ch_end) | |
| df = pd.read_sql_query(query, conn, params=params) | |
| conn.close() | |
| return df | |
| def get_project_metadata(key: str) -> Optional[str]: | |
| conn = get_connection() | |
| row = conn.execute("SELECT value FROM project_metadata WHERE key = ?", (key,)).fetchone() | |
| conn.close() | |
| return row[0] if row else None | |
| def set_project_metadata(key: str, value: str): | |
| conn = get_connection() | |
| conn.execute( | |
| "INSERT OR REPLACE INTO project_metadata (key, value) VALUES (?, ?)", (key, str(value)) | |
| ) | |
| conn.commit() | |
| conn.close() | |
| def clear_project_data(): | |
| conn = get_connection() | |
| cursor = conn.cursor() | |
| for table in ( | |
| "chainages", | |
| "survey_features", | |
| "drawings", | |
| "psr", | |
| "project_metadata", | |
| "provenance", | |
| ): | |
| cursor.execute(f"DELETE FROM {table}") | |
| conn.commit() | |
| conn.close() | |
| def query_geometry(alignment: str = None) -> pd.DataFrame: | |
| """Query geometry data for long sections (levels, grades, radii).""" | |
| conn = get_connection() | |
| try: | |
| query = "SELECT chainage, level, radius, cant, grade FROM chainages WHERE 1=1" | |
| params = [] | |
| if alignment: | |
| query += " AND alignment = ?" | |
| params.append(alignment) | |
| query += " ORDER BY chainage" | |
| return pd.read_sql_query(query, conn, params=params) | |
| except Exception: | |
| return pd.DataFrame() | |
| finally: | |
| conn.close() | |
| init_db() | |
| def query_chainage_at(chainage: float, alignment: str = None) -> dict: | |
| """Fetch a single chainage row exactly (or nearest) from SQLite.""" | |
| conn = get_connection() | |
| query = "SELECT * FROM chainages WHERE ABS(chainage - ?) < 0.01" | |
| params = [chainage] | |
| if alignment: | |
| query += " AND alignment = ?" | |
| params.append(alignment) | |
| cur = conn.execute(query, params) | |
| row = cur.fetchone() | |
| conn.close() | |
| if row: | |
| # Convert sqlite3.Row to dict | |
| return {k: row[k] for k in row.keys()} | |
| return None | |