#!/usr/bin/env python3 """ Schnelle Datenbank-Reparatur für kritische Fehler """ import sqlite3 import os import sys from datetime import datetime # Pfad zur App hinzufügen sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__)))) try: from config.settings import DATABASE_PATH except ImportError: # Fallback falls Import fehlschlägt DATABASE_PATH = "database/myp.db" def quick_fix_database(): """Behebt die kritischsten Datenbankprobleme sofort""" print("🔧 Starte schnelle Datenbank-Reparatur...") if not os.path.exists(DATABASE_PATH): print(f"❌ Datenbankdatei nicht gefunden: {DATABASE_PATH}") return False try: # Backup erstellen backup_path = f"{DATABASE_PATH}.emergency_backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}" import shutil shutil.copy2(DATABASE_PATH, backup_path) print(f"✅ Emergency-Backup erstellt: {backup_path}") # Verbindung zur Datenbank conn = sqlite3.connect(DATABASE_PATH) cursor = conn.cursor() print("🔧 Repariere Datenbank-Schema...") # 1. Fehlende Spalte duration_minutes zu guest_requests hinzufügen try: cursor.execute("ALTER TABLE guest_requests ADD COLUMN duration_minutes INTEGER") print("✅ Spalte duration_minutes zu guest_requests hinzugefügt") except sqlite3.OperationalError as e: if "duplicate column name" in str(e).lower(): print("ℹ️ Spalte duration_minutes bereits vorhanden") else: print(f"⚠️ Fehler bei duration_minutes: {e}") # 2. Fehlende Spalten zu users hinzufügen user_columns = [ ("username", "VARCHAR(100) UNIQUE"), ("updated_at", "DATETIME DEFAULT CURRENT_TIMESTAMP"), ("department", "VARCHAR(100)"), ("position", "VARCHAR(100)"), ("phone", "VARCHAR(50)"), ("bio", "TEXT") ] for column_name, column_def in user_columns: try: cursor.execute(f"ALTER TABLE users ADD COLUMN {column_name} {column_def}") print(f"✅ Spalte {column_name} zu users hinzugefügt") except sqlite3.OperationalError as e: if "duplicate column name" in str(e).lower(): print(f"ℹ️ Spalte {column_name} bereits vorhanden") else: print(f"⚠️ Fehler bei {column_name}: {e}") # 3. Fehlende Spalten zu printers hinzufügen printer_columns = [ ("plug_username", "VARCHAR(100) DEFAULT 'admin'"), ("plug_password", "VARCHAR(100) DEFAULT 'admin'"), ("last_checked", "DATETIME") ] for column_name, column_def in printer_columns: try: cursor.execute(f"ALTER TABLE printers ADD COLUMN {column_name} {column_def}") print(f"✅ Spalte {column_name} zu printers hinzugefügt") except sqlite3.OperationalError as e: if "duplicate column name" in str(e).lower(): print(f"ℹ️ Spalte {column_name} bereits vorhanden") else: print(f"⚠️ Fehler bei {column_name}: {e}") # 4. Username für bestehende User setzen (falls NULL) try: cursor.execute("UPDATE users SET username = email WHERE username IS NULL") updated_users = cursor.rowcount if updated_users > 0: print(f"✅ Username für {updated_users} Benutzer gesetzt") except Exception as e: print(f"⚠️ Fehler beim Setzen der Usernames: {e}") # 5. Drucker-Daten nachtragen print("🖨️ Trage Drucker nach...") # Prüfen ob bereits Drucker vorhanden sind cursor.execute("SELECT COUNT(*) FROM printers") printer_count = cursor.fetchone()[0] if printer_count == 0: # Standard-Drucker hinzufügen printers_to_add = [ { 'name': 'Printer 1', 'model': 'P115', 'location': 'Werk 040 - Berlin - TBA', 'ip_address': '192.168.0.100', 'mac_address': '98:254A:E1:2001', 'plug_ip': '192.168.0.100', 'plug_username': 'admin', 'plug_password': 'admin', 'status': 'offline', 'active': 1 }, { 'name': 'Printer 2', 'model': 'P115', 'location': 'Werk 040 - Berlin - TBA', 'ip_address': '192.168.0.101', 'mac_address': '98:254A:E1:2002', 'plug_ip': '192.168.0.101', 'plug_username': 'admin', 'plug_password': 'admin', 'status': 'offline', 'active': 1 }, { 'name': 'Printer 3', 'model': 'P115', 'location': 'Werk 040 - Berlin - TBA', 'ip_address': '192.168.0.102', 'mac_address': '98:254A:E1:2003', 'plug_ip': '192.168.0.102', 'plug_username': 'admin', 'plug_password': 'admin', 'status': 'offline', 'active': 1 }, { 'name': 'Printer 4', 'model': 'P115', 'location': 'Werk 040 - Berlin - TBA', 'ip_address': '192.168.0.103', 'mac_address': '98:254A:E1:2004', 'plug_ip': '192.168.0.103', 'plug_username': 'admin', 'plug_password': 'admin', 'status': 'offline', 'active': 1 }, { 'name': 'Printer 5', 'model': 'P115', 'location': 'Werk 040 - Berlin - TBA', 'ip_address': '192.168.0.104', 'mac_address': '98:254A:E1:2005', 'plug_ip': '192.168.0.104', 'plug_username': 'admin', 'plug_password': 'admin', 'status': 'offline', 'active': 1 }, { 'name': 'Printer 6', 'model': 'P115', 'location': 'Werk 040 - Berlin - TBA', 'ip_address': '192.168.0.106', 'mac_address': '98:254A:E1:2006', 'plug_ip': '192.168.0.106', 'plug_username': 'admin', 'plug_password': 'admin', 'status': 'offline', 'active': 1 } ] for printer in printers_to_add: try: cursor.execute(""" INSERT INTO printers (name, model, location, ip_address, mac_address, plug_ip, plug_username, plug_password, status, active, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """, ( printer['name'], printer['model'], printer['location'], printer['ip_address'], printer['mac_address'], printer['plug_ip'], printer['plug_username'], printer['plug_password'], printer['status'], printer['active'], datetime.now() )) print(f"✅ Drucker {printer['name']} hinzugefügt") except Exception as e: print(f"⚠️ Fehler beim Hinzufügen von {printer['name']}: {e}") else: print(f"ℹ️ {printer_count} Drucker bereits vorhanden") # 6. Optimierungen print("🔧 Führe Datenbankoptimierungen durch...") try: # Indizes erstellen indices = [ "CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)", "CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)", "CREATE INDEX IF NOT EXISTS idx_jobs_user_id ON jobs(user_id)", "CREATE INDEX IF NOT EXISTS idx_jobs_printer_id ON jobs(printer_id)", "CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status)", "CREATE INDEX IF NOT EXISTS idx_guest_requests_status ON guest_requests(status)" ] for index_sql in indices: cursor.execute(index_sql) # Statistiken aktualisieren cursor.execute("ANALYZE") print("✅ Datenbankoptimierungen abgeschlossen") except Exception as e: print(f"⚠️ Fehler bei Optimierungen: {e}") # Änderungen speichern conn.commit() conn.close() print("✅ Schnelle Datenbank-Reparatur erfolgreich abgeschlossen!") return True except Exception as e: print(f"❌ Kritischer Fehler bei der Reparatur: {str(e)}") return False if __name__ == "__main__": quick_fix_database()