e5iAtdZddlZddlmZddlmZddlmZddlmZm Z m Z ddl m Z dd l mZGd d Zy) zDatabase management module for DaemonControl. This module provides a singleton DatabaseManager class that handles all SQLite database operations for job scheduling and execution tracking. N)contextmanagerdatetime)Path)DictListOptional) ConfigManager) setup_loggerceZdZUdZdZeded<d&fd Zd'dZd'dZ de jddfd Z de jddfd Z ed Zdefd Zdeefd Z d(dedededeedededeedefdZd)dededefdZ d*dedeedeedeedeedeeddfdZ d+deded ed!efd"Zdefd#Zd,d$efd%ZxZS)-DatabaseManagerzSingleton SQLite database manager. Manages the SQLite database for jobs, schedules, and execution history. Uses WAL mode for better concurrency and enforces foreign key constraints. N _instancereturnc\|jt| ||_|jS)z4Ensure only one instance exists (singleton pattern).)rsuper__new__)cls __class__s 7/mnt/ssd/data/python-lab/DaemonControl/core/database.pyrzDatabaseManager.__new__s' == !GOC0CM}}ct|dryt|_td|_|jj dd}t |j|_|jjjdd|jd|_ |jjd|jy)zInitialize the database manager. Creates database file and schema if they don't exist. Only initializes once due to singleton pattern. _initializedNdatabasepathT)parentsexist_okzDatabase initialized at )hasattrr _configr _loggergetr expanduser_db_pathparentmkdir_init_databaserinfo)self db_path_strs r__init__zDatabaseManager.__init__ s 4 ( $ #J/ ll&&z6: [)446  ""4$"?   4T]]ODErc|j5}|jd|jd|j|dddy#1swYyxYw)z1Initialize database connection and create schema.zPRAGMA journal_mode=WALzPRAGMA foreign_keys=ONN)get_connectionexecute_create_schema)r(conns rr&zDatabaseManager._init_database:sO  " &d LL2 3 LL1 2    % & & &s 4AAr/ch|jd|jd|jd|jd|jd|jd|jd|jd|jd |jd |jd |jd |jd }|j4|jddtjj f|j |y)znCreate database schema if it doesn't exist. Args: conn: SQLite connection object z CREATE TABLE IF NOT EXISTS schema_version ( version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL ) a CREATE TABLE IF NOT EXISTS jobs ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, job_type TEXT NOT NULL, executable_path TEXT NOT NULL, working_directory TEXT, timeout INTEGER DEFAULT 3600, enabled INTEGER DEFAULT 1, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, description TEXT ) zL CREATE INDEX IF NOT EXISTS idx_jobs_name ON jobs(name) zR CREATE INDEX IF NOT EXISTS idx_jobs_enabled ON jobs(enabled) a CREATE TABLE IF NOT EXISTS schedules ( id INTEGER PRIMARY KEY AUTOINCREMENT, job_id INTEGER NOT NULL, cron_expression TEXT NOT NULL, enabled INTEGER DEFAULT 1, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE ) zZ CREATE INDEX IF NOT EXISTS idx_schedules_job_id ON schedules(job_id) a CREATE TABLE IF NOT EXISTS executions ( id INTEGER PRIMARY KEY AUTOINCREMENT, job_id INTEGER NOT NULL, status TEXT NOT NULL, start_time TEXT NOT NULL, end_time TEXT, exit_code INTEGER, log_output TEXT, error_message TEXT, created_at TEXT NOT NULL, FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE ) z\ CREATE INDEX IF NOT EXISTS idx_executions_job_id ON executions(job_id) z\ CREATE INDEX IF NOT EXISTS idx_executions_status ON executions(status) zd CREATE INDEX IF NOT EXISTS idx_executions_start_time ON executions(start_time) aT CREATE TABLE IF NOT EXISTS fleet_status ( id INTEGER PRIMARY KEY AUTOINCREMENT, check_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, check_type TEXT NOT NULL, status TEXT NOT NULL, data TEXT, message TEXT ) z CREATE INDEX IF NOT EXISTS idx_fleet_status_type_time ON fleet_status(check_type, check_time DESC) z4SELECT version FROM schema_version WHERE version = 1N>INSERT INTO schema_version (version, applied_at) VALUES (?, ?)r )r-fetchonernow isoformat_run_migrations)r(r/cursors rr.zDatabaseManager._create_schemaEs:                             TU ??  $ LLPHLLN,,./  T"rcl|jd}|j}|d|dnd}|dkr|jjd |jd}|j Dcgc]}|d }}d|vr,|jd |jjd |jd dt j jf|jjd yycc}w#t$r$}|jjd |d}~wwxYw)zlRun database migrations to update schema. Args: conn: SQLite connection object z'SELECT MAX(version) FROM schema_versionrNz(Running migration to schema version 2...zPRAGMA table_info(executions)r log_file_pathzz ALTER TABLE executions ADD COLUMN log_file_path TEXT z.Added log_file_path column to executions tabler1zSchema migrated to version 2zMigration to version 2 failed: ) r-r2r r'fetchallrr3r4 Exceptionerror)r(r/r6resultcurrent_versionrowcolumnses rr5zDatabaseManager._run_migrationss'GH"'-ay'<&)! Q  LL  H I &EF-3__->?c3q6??"'1LL"LL%%&VW T 0023 !!"@A'  @  ""%DQC#HI s+#D2 D>BDD D3D..D3c#|Ktjt|j}tj|_ ||j  |jy#t$r4}|j|jjd|d}~wwxYw#|jwxYww)alContext manager for database connections. Automatically commits on success and rolls back on exception. Ensures connections are properly closed. Yields: sqlite3.Connection: Database connection Example: >>> with db.get_connection() as conn: ... conn.execute("INSERT INTO jobs ...") zDatabase error: N) sqlite3connectstrr#Row row_factorycommitr;rollbackr r<close)r(r/rAs rr,zDatabaseManager.get_connectionss4==12";; J KKM JJL   MMO LL  !1!5 6   JJLs5>B<A'B<' B$0/BB$$B''B99B<c|j5}|jd}|j}|d|dndcdddS#1swYyxYw)z`Get current schema version. Returns: Current schema version number z2SELECT MAX(version) as version FROM schema_versionversionNr)r,r-r2)r(r/r6r?s rget_schema_versionz"DatabaseManager.get_schema_versionsX  " Gd\\"VWF//#C%(^%?3y>Q G G Gs -AAc|j5}|jd}g}|jD]}|jt ||cdddS#1swYyxYw)zGet all enabled jobs with their schedules. Returns: List of job dictionaries with schedule information a' SELECT j.id, j.name, j.job_type, j.executable_path, j.working_directory, j.timeout, j.description, s.id as schedule_id, s.cron_expression, s.enabled as schedule_enabled FROM jobs j LEFT JOIN schedules s ON j.id = s.job_id WHERE j.enabled = 1 ORDER BY j.name N)r,r-r:appenddict)r(r/r6jobsr?s rget_enabled_jobsz DatabaseManager.get_enabled_jobssl  " d\\#F$D( ' DI& '/   s AAA(namejob_typeexecutable_pathworking_directorytimeoutenabled descriptionc .tjj}|j5} | j d||||||rdnd|||f } | j } |j jd|d| | cdddS#1swYyxYw)a Create a new job. Args: name: Unique job name job_type: Type of job ('script' or 'python_module') executable_path: Path to executable or module working_directory: Working directory for execution timeout: Execution timeout in seconds enabled: Whether job is enabled description: Job description Returns: ID of created job Raises: sqlite3.IntegrityError: If job name already exists z INSERT INTO jobs ( name, job_type, executable_path, working_directory, timeout, enabled, created_at, updated_at, description ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) r rz Created job 'z ' with ID N)rr3r4r,r- lastrowidr r') r(rSrTrUrVrWrXrYr3r/r6job_ids r create_jobzDatabaseManager.create_jobs6lln&&(  " d\\# h1Bg1c3  F%%F LL   dV:fXF G   s AB  Br\statusctjj}|j5}|j d||||f}|j }|j jd|d||cdddS#1swYyxYw)zCreate execution record. Args: job_id: ID of job being executed status: Initial status ('queued', 'running', etc.) Returns: ID of created execution record z INSERT INTO executions ( job_id, status, start_time, created_at ) VALUES (?, ?, ?, ?) zCreated execution z for job N)rr3r4r,r-r[r debug)r(r\r^r3r/r6 execution_ids rcreate_executionz DatabaseManager.create_executionHslln&&(  " d\\#&#s+ -F "++L LL  !3L>6(S T s ABB raend_time exit_code log_output error_messagecpg}g}|"|jd|j||"|jd|j||"|jd|j||"|jd|j||"|jd|j||sy|j|ddj|d } |j5} | j| ||jj d |dddy#1swYyxYw) a?Update execution record. Args: execution_id: ID of execution to update status: New status end_time: Execution end time (ISO 8601) exit_code: Process exit code log_output: Captured log output error_message: Error message if failed Nz status = ?z end_time = ?z exit_code = ?zlog_output = ?zerror_message = ?zUPDATE executions SET z, z WHERE id = ?zUpdated execution )rOjoinr,r-r r`) r(rar^rcrdrerfupdatesparamsqueryr/s rupdate_executionz DatabaseManager.update_execution_s(   NN< ( MM& !   NN> * MM( #  NN? + MM) $  ! NN+ , MM* %  $ NN. / MM- (  l#(7);(B C D D Ds 21D,,D5 check_typedatamessagec|j5}|jd||||fddd|jjd|d|y#1swY+xYw)a?Save fleet monitoring status to database. Args: check_type: Type of check ('docker', 'tunnel', 'backup', 'disk', 'overall') status: Status ('healthy', 'warning', 'critical', 'unknown') data: JSON string with detailed data message: Human-readable message z~ INSERT INTO fleet_status (check_type, status, data, message) VALUES (?, ?, ?, ?) NzSaved fleet status: z = r,r-r r`)r(rmr^rnror/s rsave_fleet_statusz!DatabaseManager.save_fleet_statussh " 6d LLfdG4 6 6 1*SIJ  6 6s AAcddl}i}dD]}|j5}|jd|f}|j}dddrtddlm}|j |d}|j |z j} |d|dr|j|dnd|d |dt| d ||<d dd ddd ||<|S#1swYxYw) ayGet latest status for all check types. Returns: dict: Latest status for each check type { 'docker': {'status': 'healthy', 'data': {...}, 'message': '...', 'age_seconds': 123}, 'tunnel': {...}, 'backup': {...}, 'disk': {...}, 'overall': {...} } rN)dockertunnelbackupdiskoverallz SELECT status, data, message, check_time FROM fleet_status WHERE check_type = ? ORDER BY check_time DESC LIMIT 1 r check_timer^rnro)r^rnrory age_secondsunknownzNo data available yet) jsonr,r-r2r fromisoformatutcnow total_secondsloadsint) r(r|r=rmr/r6r?rryrzs rget_latest_fleet_statusz'DatabaseManager.get_latest_fleet_statuss  K J$$& ($' !] $oo' (-3X33C 4EF .x0:=LLN "(m7:6{DJJs6{3"9~"%l"3#&{#3 &z"( 6"&#' &z"5 D C ( (s $C  C keep_hoursc|j5}|jd|fddd|jjd|dy#1swY)xYw)z|Delete old fleet status records. Args: keep_hours: Keep records from last N hours (default 24) z DELETE FROM fleet_status WHERE check_time < datetime('now', '-' || ? || ' hours') Nz#Cleaned up fleet status older than z hoursrq)r(rr/s rcleanup_old_fleet_statusz(DatabaseManager.cleanup_old_fleet_statuss]  " d LL  @ FST   s A  A)rr)rN)NiTN)queued)NNNNN)NN))__name__ __module__ __qualname____doc__rr __annotations__rr*r&rC Connectionr.r5rr,rrMrrrRrEboolr]rbrlrrrPrr __classcell__)rs@rrrs .2Ix)*1 F4 &m#7#5#5m#$m#^"G$6$6"4"H2 GC G$t*H,0%)*** * $C= *  **c]* *X s C s 4!%"&#'$('+3D3D 3D3- 3D C= 3D SM 3D }3D 3Dt;?KCKK"K47K"33j U3 Urr)rrC contextlibrrpathlibrtypingrrr configr loggerr rrrrs2 %''! XUXUr