Spaces:
Runtime error
Runtime error
File size: 9,602 Bytes
ef3133a fcec2b0 ef3133a fcec2b0 ef3133a d06b99c fcec2b0 d06b99c fcec2b0 d06b99c ef3133a 7f8f9a6 fcec2b0 7f8f9a6 ef3133a 7f8f9a6 ef3133a d07af86 ef3133a d06b99c ef3133a d06b99c ef3133a d06b99c ef3133a df236f5 ef3133a fcec2b0 ef3133a 6499080 ef3133a 6499080 ef3133a 6499080 ef3133a d06b99c fcec2b0 ef3133a d06b99c ef3133a fcec2b0 ef3133a 7f8f9a6 fcec2b0 7f8f9a6 df236f5 7f8f9a6 0dbb26a | 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 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 | """
Supabase database implementation for TreeTrack
"""
import json
import logging
from typing import Dict, List, Optional, Any
from supabase_client import get_service_client
logger = logging.getLogger(__name__)
class SupabaseDatabase:
"""Uses service_role client to bypass RLS. Authorization handled by FastAPI."""
def __init__(self):
try:
self.client = get_service_client()
self.connected = True
logger.info("SupabaseDatabase initialized with service_role client")
except ValueError as e:
logger.warning(f"Supabase not configured: {e}")
self.client = None
self.connected = False
def _check_connection(self):
if not self.connected or not self.client:
raise RuntimeError("Database not connected. Please configure Supabase credentials.")
def initialize_database(self) -> bool:
try:
self.client.table('trees').select("id").limit(1).execute()
logger.info("Trees table verified")
self._ensure_telemetry_table()
return True
except Exception as e:
logger.error(f"Failed to verify/initialize database: {e}")
return False
def test_connection(self) -> bool:
from supabase_client import test_supabase_connection
return test_supabase_connection()
async def create_tree(self, tree_data: Dict[str, Any]) -> Dict[str, Any]:
self._check_connection()
try:
result = self.client.table('trees').insert(tree_data).execute()
if result.data:
created_tree = result.data[0]
logger.info(f"Created tree with ID: {created_tree.get('id')}")
return created_tree
else:
raise Exception("No data returned from insert operation")
except Exception as e:
logger.error(f"Error creating tree: {e}")
raise
async def get_trees(self, limit: int = 100, offset: int = 0,
species: str = None, health_status: str = None) -> List[Dict[str, Any]]:
self._check_connection()
try:
query = self.client.table('trees').select("*")
if species:
query = query.ilike('scientific_name', f'%{species}%')
result = query.order('updated_at', desc=True) \
.order('created_at', desc=True) \
.range(offset, offset + limit - 1) \
.execute()
logger.info(f"Retrieved {len(result.data)} trees")
return result.data
except Exception as e:
logger.error(f"Error retrieving trees: {e}")
raise
async def get_tree(self, tree_id: int) -> Optional[Dict[str, Any]]:
self._check_connection()
try:
result = self.client.table('trees') \
.select("*") \
.eq('id', tree_id) \
.execute()
if result.data:
return result.data[0]
return None
except Exception as e:
logger.error(f"Error retrieving tree {tree_id}: {e}")
raise
async def update_tree(self, tree_id: int, tree_data: Dict[str, Any]) -> Dict[str, Any]:
self._check_connection()
try:
update_data = {k: v for k, v in tree_data.items() if k != 'id'}
result = self.client.table('trees') \
.update(update_data) \
.eq('id', tree_id) \
.execute()
if result.data:
updated_tree = result.data[0]
logger.info(f"Updated tree with ID: {tree_id}")
return updated_tree
else:
raise Exception(f"Tree with ID {tree_id} not found")
except Exception as e:
logger.error(f"Error updating tree {tree_id}: {e}")
raise
async def delete_tree(self, tree_id: int) -> bool:
self._check_connection()
try:
result = self.client.table('trees') \
.delete() \
.eq('id', tree_id) \
.execute()
logger.info(f"Deleted tree with ID: {tree_id}")
return True
except Exception as e:
logger.error(f"Error deleting tree {tree_id}: {e}")
raise
def get_tree_count(self) -> int:
try:
result = self.client.table('trees') \
.select("id", count="exact") \
.execute()
return result.count if result.count is not None else 0
except Exception as e:
logger.error(f"Error getting tree count: {e}")
return 0
def get_species_distribution(self, limit: int = 20) -> List[Dict[str, Any]]:
try:
try:
result = self.client.rpc('get_species_distribution', {'record_limit': limit}).execute()
if result.data:
return [{"species": row["species"], "count": row["count"]} for row in result.data]
except Exception as rpc_error:
logger.info(f"RPC not available, using Python aggregation")
result = self.client.table('trees') \
.select("scientific_name") \
.not_.is_('scientific_name', 'null') \
.neq('scientific_name', '') \
.execute()
species_count = {}
for tree in result.data:
species = tree.get('scientific_name', 'Unknown')
species_count[species] = species_count.get(species, 0) + 1
distribution = [
{"species": species, "count": count}
for species, count in species_count.items()
]
distribution.sort(key=lambda x: x['count'], reverse=True)
return distribution[:limit]
except Exception as e:
logger.error(f"Error getting species distribution: {e}")
return []
def get_health_distribution(self) -> List[Dict[str, Any]]:
try:
return []
except Exception as e:
logger.error(f"Error getting health distribution: {e}")
return []
def get_average_measurements(self) -> Dict[str, float]:
try:
result = self.client.table('trees') \
.select("height, width") \
.not_.is_('height', 'null') \
.not_.is_('width', 'null') \
.execute()
if not result.data:
return {"average_height": 0, "average_diameter": 0}
heights = [float(tree['height']) for tree in result.data if tree['height']]
widths = [float(tree['width']) for tree in result.data if tree['width']]
avg_height = sum(heights) / len(heights) if heights else 0
avg_width = sum(widths) / len(widths) if widths else 0
return {
"average_height": round(avg_height, 2),
"average_diameter": round(avg_width, 2)
}
except Exception as e:
logger.error(f"Error getting average measurements: {e}")
return {"average_height": 0, "average_diameter": 0}
def backup_database(self) -> bool:
return True
def restore_database(self) -> bool:
return True
def _ensure_telemetry_table(self) -> None:
try:
self.client.table('telemetry_events').select('id').limit(1).execute()
logger.info("Telemetry table verified")
except Exception as e:
logger.info(f"telemetry_events table not accessible: {e}")
pass
def log_telemetry(self, event: Dict[str, Any]) -> bool:
try:
payload = {
'event_type': event.get('event_type'),
'status': event.get('status'),
'metadata': event.get('metadata'),
'username': (event.get('user') or {}).get('username'),
'role': (event.get('user') or {}).get('role'),
'client': event.get('client'),
'timestamp': event.get('timestamp')
}
self.client.table('telemetry_events').insert(payload).execute()
logger.info("Telemetry event inserted into Supabase")
return True
except Exception as e:
logger.error(f"Failed to log telemetry: {e}")
return False
def get_recent_telemetry(self, limit: int = 100) -> List[Dict[str, Any]]:
try:
result = self.client.table('telemetry_events') \
.select('*') \
.order('timestamp', desc=True) \
.limit(limit) \
.execute()
return result.data or []
except Exception as e:
logger.error(f"Failed to fetch telemetry: {e}")
return []
|