Spaces:
Build error
Build error
File size: 3,739 Bytes
dbce185 | 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 116 117 118 119 120 121 | import sqlite3
import os
DB_PATH = "trailhead.db"
def init_db():
"""Initialize database and create tables if they do not exist."""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS journal_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
lat REAL,
lon REAL,
ele REAL,
cum_dist REAL,
transcript TEXT
);
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS custom_waypoints (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
lat REAL,
lon REAL,
ele REAL,
type TEXT,
name TEXT,
description TEXT
);
""")
conn.commit()
conn.close()
print(f"[database] SQLite database initialized at {DB_PATH}")
def add_journal_entry(lat, lon, ele, cum_dist, transcript):
"""Insert a voice journal log entry."""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("""
INSERT INTO journal_logs (lat, lon, ele, cum_dist, transcript)
VALUES (?, ?, ?, ?, ?)
""", (lat, lon, ele, cum_dist, transcript))
conn.commit()
conn.close()
print(f"[database] Saved journal entry: '{transcript[:30]}...'")
def get_journal_entries():
"""Retrieve all journal entries sorted by timestamp descending."""
conn = sqlite3.connect(DB_PATH)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT id, timestamp, lat, lon, ele, cum_dist, transcript FROM journal_logs ORDER BY timestamp DESC")
rows = cursor.fetchall()
entries = []
for r in rows:
entries.append({
"id": r["id"],
"timestamp": r["timestamp"],
"lat": r["lat"],
"lon": r["lon"],
"ele": r["ele"],
"cum_dist": r["cum_dist"],
"transcript": r["transcript"]
})
conn.close()
return entries
def clear_journal_logs():
"""Delete all journal entries."""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("DELETE FROM journal_logs")
conn.commit()
conn.close()
print("[database] Cleared all journal logs.")
def add_custom_waypoint(lat, lon, ele, wp_type, name, description=""):
"""Insert a tagged custom waypoint."""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("""
INSERT INTO custom_waypoints (lat, lon, ele, type, name, description)
VALUES (?, ?, ?, ?, ?, ?)
""", (lat, lon, ele, wp_type, name, description))
conn.commit()
conn.close()
print(f"[database] Saved custom waypoint '{name}' of type '{wp_type}'")
def get_custom_waypoints():
"""Retrieve all custom waypoints sorted by timestamp descending."""
conn = sqlite3.connect(DB_PATH)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT id, timestamp, lat, lon, ele, type, name, description FROM custom_waypoints ORDER BY timestamp DESC")
rows = cursor.fetchall()
wps = []
for r in rows:
wps.append({
"id": r["id"],
"timestamp": r["timestamp"],
"lat": r["lat"],
"lon": r["lon"],
"ele": r["ele"],
"type": r["type"],
"name": r["name"],
"description": r["description"]
})
conn.close()
return wps
def clear_custom_waypoints():
"""Delete all custom waypoints."""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("DELETE FROM custom_waypoints")
conn.commit()
conn.close()
print("[database] Cleared all custom waypoints.")
|