,1idZddlZddlZddlZddlmZddlmZddlmZm Z m Z m Z ddl m Z ddlmZGdd ZeZeZdS) zu Database module for Python Scheduler Manages SQLite database with job definitions, schedules, and execution history N)datetime)Path)OptionalListDictAny)contextmanager)loggerceZdZdZdZdydededeffd Z dzdededefdZd Ze d Z e d Z e d Z d Z deefdZgdZdefdZdejfdZdedededededef dZdedeeeeffdZdedeeeeffdZd{dededeeeeffd Zdefd!Zdefd"Z d|ded$ed%edefd&Z!d'efd(Z"d'edeeeeffd)Z#d'edeefd*Z$d}d,edeededeeeeffd-Z%d~dedeeeeffd.Z&dded0ed1edefd2Z'd3eded4efd5Z(d3edeeeeffd6Z)d3edeeeeffd7Z*dyd3eded0ed1efd8Z+d3efd9Z,d3edefd:Z-d3efd;Z.dedZ/d{dededeeeeffd?Z0d@edeeeeffdAZ1ddIefdSZ?dIefdTZ@dIedUed$edVeAdef dWZBdXefdYZCdd,ededeeeeffd[ZDdIedeeeeffd\ZEdIed$edVeAdefd]ZFdXefd^ZGdd,edeeeeffd_ZHdIedeeeeffd`ZIdIedefdaZJdedNedbedefdcZKddedeeeeffdeZLddedeeeeffdfZMddefdgZNddefdhZOddediedjedefdkZPdledeeeeffdmZQd~dded,edeeeeffdnZRddedeeeeffdoZSdlefdpZTdIedqedredOed$edVeAdefdsZUdtefduZVd'edved$edwedef dxZWxZXS)SchedulerDatabasez(Singleton database manager for schedulerN remote_path local_pathlocal_runtime_pathc|j2t||_d|j_|jS)NF) _instancesuper__new__ _initialized)clsr rr __class__s C:\PYTHON\Frank\core\database.pyrzSchedulerDatabase.__new__s4 = !GGOOC00CM).CM &}C:\PYTHON\Frank\data\frank.dbc.|jrdS||_|du|_|jr|n||_||_|d|_|jr&t jd|jd|jdSt jd|jdS)NTuDatabase split mode — local: z | remote: zDatabase initialized at )r _remote_path _split_modedb_path_local_runtime_path_ensure_db_existsr info)selfr rrs r__init__zSchedulerDatabase.__init__s    F'&T1%)%5Fzz; $6         C Kf$,ffSWSdff g g g g g KA4<AA B B B B Brc t|jjddn#tt f$rYnwxYw|5}|d|d||| dddn #1swxYwY|j r*| | dSdS)zBCreate local schema; if split_mode, also sync config from remote BT)parentsexist_okzPRAGMA journal_mode=WALzPRAGMA foreign_keys=ONN) rrparentmkdirOSErrorNotImplementedError_get_connectionexecute_create_tablescommitr#_ensure_remote_config_version_table_sync_config_from_remoter!conns rrz#SchedulerDatabase._ensure_db_exists/sF     % + +D4 + H H H H,-    D  ! ! # # t LL2 3 3 3 LL1 2 2 2    % % % KKMMM                  ,  4 4 6 6 6  ) ) + + + + +  , ,s".1AAAB<<CCc#K|jp|j}tj|d}tj|_ |V|dS#|wxYw)zConnection to frank_local.db for backup/runtime data (GUI non-split mode). Falls back to main db_path if local_runtime_path is not set.>@timeoutN)rrsqlite3connectRow row_factoryclose)r!pathr1s r_get_runtime_connectionz)SchedulerDatabase._get_runtime_connectionFsf'74<tT222"; JJJ JJLLLLLDJJLLLLs AA)c#Ktj|jd}tj|_ |V|dS#|wxYw)zEContext manager for database connections (always local in split_mode)r3r4N)r6r7rr8r9r:r0s rr*z!SchedulerDatabase._get_connectionRsYt|T:::"; JJJ JJLLLLLDJJLLLLs A A c#Ktj|jd} |V|dS#|wxYw)zCShort-timeout connection to remote B (UNC path). Raises on failure.@r4N)r6r7rr:r0s r_get_remote_connectionz(SchedulerDatabase._get_remote_connection\sPt0#>>> JJJ JJLLLLLDJJLLLLs 9Ac< |5}|d|d|ddddS#1swxYwYdS#t$r"}t jd|Yd}~dSd}~wwxYw)zECreate config_version table on remote B if not exists (non-blocking).a( CREATE TABLE IF NOT EXISTS config_version ( id INTEGER PRIMARY KEY CHECK (id = 1), version INTEGER NOT NULL DEFAULT 1, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ) z@INSERT OR IGNORE INTO config_version (id, version) VALUES (1, 1)Nz,Could not init remote config_version table: )r@r+r- Exceptionr warning)r!r1es rr.z5SchedulerDatabase._ensure_remote_config_version_tablees O,,.. $  _```                    O O O NM!MM N N N N N N N N N Os:A/?A" A/"A&&A/)A&*A// B9BBreturnc |5}|d}|r|dndcdddS#1swxYwYdS#t$rYdSwxYw)z Read config_version from remote B (single lightweight query, 5s timeout). Returns None if B is unreachable. z/SELECT version FROM config_version WHERE id = 1rN)r@r+fetchonerB)r!r1rows rget_remote_config_versionz+SchedulerDatabase.get_remote_config_versionus  ,,.. ,$ll#TUU^^``!$+s1vv! , , , , , , , , , , , , , , , , , ,   44 s4A#3A A#AA#AA## A10A1)jobs job_groupsjob_group_members schedulesbackup_profilesscheduler_configc  tj|jd} |5}|jD]} |d|}d|jD}|}|d||rYddgt|z}| d|d d|d |d |#t$r$}tj d |d |Yd}~d}~wwxYw|dddn #1swxYwY|n#|wxYwtjdt|jddS#t$r#}tj d|dYd}~dSd}~wwxYw)u Copy config tables from remote B → local DB. Returns True on success, False if B unreachable (uses local cache silently). r?r4zSELECT * FROM cg|] }|d S)r).0ds r z>SchedulerDatabase._sync_config_from_remote..s#E#E#EQAaD#E#E#Erz DELETE FROM ,?z INSERT INTO z (z ) VALUES ()zSkipping table 'z' during remote sync: NzConfig synced from remote B (z tables)TzRemote sync failed: u — using local cacheF)r6r7rr*_CONFIG_TABLESr+ descriptionfetchalljoinlen executemanyrBr rCr-r:r ) r! remote_connlocaltablecursorcolsrowsphrDs rr/z*SchedulerDatabase._sync_config_from_remotesn  !/$*;SIIIK $))++#u!%!4 ` ` `%0%8%89Q%9Q9Q%R%RF#E#E&2D#E#E#ED#)??#4#4D!MM*@*@*@AAA#"%(XXsec$ii.?%@%@ % 1 1$[5$[$[CHHTNN$[$[VX$[$[$[$(!"!"!" )```"N+^e+^+^[\+^+^________`LLNNN###############"!!#### !!#### KZD|d#|d$t jd%|d}d&|D}d'|vr)|d(t jd)d*|vr)|d+t jd,|d-}d.|D}d*|vr+|d/t jd0d1Sd1S)2zCreate all database tablesa CREATE TABLE IF NOT EXISTS jobs ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, description TEXT, job_type TEXT NOT NULL, executable_path TEXT NOT NULL, arguments TEXT, working_directory TEXT, schedule_type TEXT NOT NULL, schedule_config TEXT NOT NULL, enabled BOOLEAN DEFAULT 1, timeout_seconds INTEGER DEFAULT 3600, retry_count INTEGER DEFAULT 0, retry_delay_seconds INTEGER DEFAULT 300, criticality TEXT DEFAULT 'normal', tags TEXT, show_console BOOLEAN DEFAULT 0, show_on_dashboard BOOLEAN DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, created_by TEXT DEFAULT 'user' ) aI CREATE TABLE IF NOT EXISTS job_groups ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, description TEXT, error_handling TEXT DEFAULT 'stop_on_error', created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) a  CREATE TABLE IF NOT EXISTS job_group_members ( id INTEGER PRIMARY KEY AUTOINCREMENT, group_id INTEGER NOT NULL, job_id INTEGER NOT NULL, execution_order INTEGER NOT NULL, FOREIGN KEY (group_id) REFERENCES job_groups(id) ON DELETE CASCADE, FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE, UNIQUE(group_id, job_id), UNIQUE(group_id, execution_order) ) a CREATE TABLE IF NOT EXISTS schedules ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, description TEXT, target_type TEXT NOT NULL, target_id INTEGER NOT NULL, schedule_type TEXT NOT NULL, schedule_config TEXT NOT NULL, enabled BOOLEAN DEFAULT 1, show_on_dashboard BOOLEAN DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, created_by TEXT DEFAULT 'user' ) a" CREATE TABLE IF NOT EXISTS job_executions ( id INTEGER PRIMARY KEY AUTOINCREMENT, job_id INTEGER NOT NULL, group_id INTEGER, status TEXT NOT NULL, start_time DATETIME NOT NULL, end_time DATETIME, duration_seconds REAL, exit_code INTEGER, log_file TEXT, stdout_preview TEXT, stderr_preview TEXT, error_message TEXT, triggered_by TEXT DEFAULT 'scheduler', parent_execution_id INTEGER, FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE, FOREIGN KEY (group_id) REFERENCES job_groups(id) ON DELETE SET NULL ) a CREATE TABLE IF NOT EXISTS notifications ( id INTEGER PRIMARY KEY AUTOINCREMENT, execution_id INTEGER NOT NULL, notification_type TEXT NOT NULL, status TEXT NOT NULL, message TEXT NOT NULL, sent_at DATETIME, error TEXT, FOREIGN KEY (execution_id) REFERENCES job_executions(id) ON DELETE CASCADE ) z CREATE TABLE IF NOT EXISTS scheduler_config ( key TEXT PRIMARY KEY, value TEXT NOT NULL, description TEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ) a' CREATE TABLE IF NOT EXISTS backup_profiles ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, description TEXT, backup_root TEXT NOT NULL, sources TEXT NOT NULL, exclude_patterns TEXT, retention_days INTEGER DEFAULT 30, enabled BOOLEAN DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ) a CREATE TABLE IF NOT EXISTS backup_operations ( id INTEGER PRIMARY KEY AUTOINCREMENT, profile_id INTEGER NOT NULL, operation_type TEXT NOT NULL, backup_name TEXT, status TEXT NOT NULL, start_time DATETIME NOT NULL, end_time DATETIME, files_total INTEGER DEFAULT 0, files_linked INTEGER DEFAULT 0, files_copied INTEGER DEFAULT 0, files_in_store INTEGER DEFAULT 0, total_size_bytes INTEGER DEFAULT 0, error_message TEXT, log_path TEXT, FOREIGN KEY (profile_id) REFERENCES backup_profiles(id) ON DELETE CASCADE ) aq CREATE TABLE IF NOT EXISTS backup_restores ( id INTEGER PRIMARY KEY AUTOINCREMENT, profile_id INTEGER NOT NULL, backup_name TEXT NOT NULL, restore_type TEXT NOT NULL, source_path TEXT, destination_path TEXT NOT NULL, status TEXT NOT NULL, start_time DATETIME NOT NULL, end_time DATETIME, files_restored INTEGER DEFAULT 0, error_message TEXT, FOREIGN KEY (profile_id) REFERENCES backup_profiles(id) ) zNCREATE INDEX IF NOT EXISTS idx_job_executions_job_id ON job_executions(job_id)z[CREATE INDEX IF NOT EXISTS idx_job_executions_start_time ON job_executions(start_time DESC)zNCREATE INDEX IF NOT EXISTS idx_job_executions_status ON job_executions(status)zfCREATE INDEX IF NOT EXISTS idx_job_group_members_group ON job_group_members(group_id, execution_order)zTCREATE INDEX IF NOT EXISTS idx_schedules_target ON schedules(target_type, target_id)zFCREATE INDEX IF NOT EXISTS idx_schedules_enabled ON schedules(enabled)zRCREATE INDEX IF NOT EXISTS idx_backup_ops_profile ON backup_operations(profile_id)zTCREATE INDEX IF NOT EXISTS idx_backup_ops_time ON backup_operations(start_time DESC)zUCREATE INDEX IF NOT EXISTS idx_backup_restores_profile ON backup_restores(profile_id)zPRAGMA table_info(jobs)cg|] }|d SrRrSrHs rrUz4SchedulerDatabase._create_tables..es777c3q6777rhostnamez4ALTER TABLE jobs ADD COLUMN hostname TEXT DEFAULT ''z0UPDATE jobs SET hostname = ? WHERE hostname = ''z(Migrated jobs table: assigned hostname 'z' to existing jobsz"PRAGMA table_info(backup_profiles)cg|] }|d SrhrRrjs rrUz4SchedulerDatabase._create_tables..o:::c!f:::rz?ALTER TABLE backup_profiles ADD COLUMN hostname TEXT DEFAULT ''z;UPDATE backup_profiles SET hostname = ? WHERE hostname = ''z3Migrated backup_profiles table: assigned hostname 'z' to existing profilesz!PRAGMA table_info(job_executions)cg|] }|d SrhrRrjs rrUz4SchedulerDatabase._create_tables..xrmrz>ALTER TABLE job_executions ADD COLUMN hostname TEXT DEFAULT ''z UPDATE job_executions SET hostname = ( SELECT j.hostname FROM jobs j WHERE j.id = job_executions.job_id ) WHERE hostname = '' z=Migrated job_executions table: back-filled hostname from jobscg|] }|d SrhrRrjs rrUz4SchedulerDatabase._create_tables..s<<<3A<<.s;;;#s1v;;;rzDALTER TABLE schedules ADD COLUMN show_on_dashboard BOOLEAN DEFAULT 1z8Migrated schedules table: added show_on_dashboard columnN)r+r[socket gethostnamer r ) r!r1rbcolumnscurrent_hostname bp_columns ex_columns jobs_columns sch_columnss rr,z SchedulerDatabase._create_tablesss     4            $     ,             *     ( efff rsss efff }~~~ klll ]^^^ ijjj klll lmmm78877V__%6%6777 W $ $ LLO P P P%133  LLKN^M` a a a KgCSggg h h hBCC::(9(9::: Z ' ' LLZ [ [ [%133  LLVYiXk l l l KvN^vvv w w wABB::(9(9::: Z ' ' LLY Z Z Z LL    KW X X X788<<&//*;*;<<<  - - LLU V V V KH I I I l 2 2 LLZ [ [ [ KM N N N<==;;):):;;; k 1 1 LL_ ` ` ` KR S S S S S 2 1rnamejob_typeexecutable_path schedule_typeschedule_configc ^|5}|d||d|||d|d|tj||dd|dd|d d |d d |d dtj|dg|dd|dd|dt jf}||j} tj d|d| | cdddS#1swxYwYdS)zCreate a new joba INSERT INTO jobs ( name, description, job_type, executable_path, arguments, working_directory, schedule_type, schedule_config, enabled, timeout_seconds, retry_count, retry_delay_seconds, criticality, tags, show_console, show_on_dashboard, hostname ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) rZ argumentsworking_directoryenabledTtimeout_secondsi retry_countrretry_delay_secondsi, criticalitynormaltagsrpFrqrkz Created job ' ' with ID N) r*r+getjsondumpsrsrtr- lastrowidr r ) r!r{r|r}r~rkwargsr1rbjob_ids r create_jobzSchedulerDatabase.create_jobs ! ! # # t\\# =)) ;'' .// ?++ 9d++ ,d33 =!,, 0#66 =(33 6::fb1122 >511 .55 :v'9';';<<#F4 KKMMM%F K@@@@@ A A A=                  sFF""F&)F&rc|5}|d|f}|}|rt|ndcdddS#1swxYwYdS)z Get job by IDzSELECT * FROM jobs WHERE id = ?Nr*r+rGdict)r!rr1rbrHs rget_jobzSchedulerDatabase.get_jobs  ! ! # # .t\\"CfYOOF//##C #-4999 . . . . . . . . . . . . . . . . . .>A  A$'A$c|5}|d|f}|}|rt|ndcdddS#1swxYwYdS)zGet job by namez!SELECT * FROM jobs WHERE name = ?Nr)r!r{r1rbrHs rget_job_by_namez!SchedulerDatabase.get_job_by_names  ! ! # # .t\\"EwOOF//##C #-4999 . . . . . . . . . . . . . . . . . .rF enabled_onlyrkc|5}g}g}|r|d|*|d|||rdd|nd}|d|d|}d |DcdddS#1swxYwYdS) zFGet all jobs, optionally filtered by hostname ('' = runs on all hosts) enabled = 1Nz(hostname = '' OR hostname = ?)zWHERE  AND zSELECT * FROM jobs  ORDER BY namec,g|]}t|SrRrrjs rrUz2SchedulerDatabase.get_all_jobs..;;;#DII;;;rr*appendr\r+r[r!rrkr1 conditionsparamswhererbs r get_all_jobszSchedulerDatabase.get_all_jobss&  ! ! # # ,>MMMv V V V KKMMM K2&22 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4s AD00D47D4c|d|}|5}|d|f|t jd|d|dddddS#1swxYwYdS)z#Delete job and associated schedulesjobzDELETE FROM jobs WHERE id = ?zDeleted job ID  (and schedule(s))N_delete_schedules_for_targetr*r+r-r r )r!rdeleted_schedulesr1s r delete_jobzSchedulerDatabase.delete_jobs!==eVLL ! ! # # Zt LL86) D D D KKMMM KX&XX8IXXX Y Y Y Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z ZAA??BB schedulerstatus triggered_byc |5}|d||d||dtj||dt jf}||j}tj d|d||cdddS#1swxYwYdS)zCreate execution recordz INSERT INTO job_executions ( job_id, group_id, status, start_time, triggered_by, parent_execution_id, hostname ) VALUES (?, ?, ?, ?, ?, ?, ?) group_id start_timeparent_execution_idzCreated execution z for job N) r<r+rrnowrsrtr-rr debug)r!rrrrr1rb execution_ids rcreate_executionz"SchedulerDatabase.create_executions  ) ) + + t\\#  :&& <88 011"$$  F KKMMM!+L LMlMMVMM N N N#                  sB0CCCrc gd}g}g}|D]6\}}||vr-||d||7|sdS|||5}|dd|d||ddddS#1swxYwYdS)zUpdate execution record) rrend_timeduration_seconds exit_codelog_filestdout_previewstderr_preview error_messagerNzUPDATE job_executions SET rr)rrr<r+r\r-) r!rrrrrrrr1s rupdate_executionz"SchedulerDatabase.update_executions<    "LLNN % %LE5&&%~~~... e$$$  F l###  ) ) + + t LLWdii6H6HWWWY_ ` ` ` KKMMM                  sACCCc|5}|d|f}|}|rt|ndcdddS#1swxYwYdS)zGet execution by IDz)SELECT * FROM job_executions WHERE id = ?Nr<r+rGrr!rr1rbrHs r get_executionzSchedulerDatabase.get_execution-s  ) ) + + .t\\"MP\__F//##C #-4999 . . . . . . . . . . . . . . . . . .rc|5}|d|f}|}|r|dndcdddS#1swxYwYdS)zGet execution statusz.SELECT status FROM job_executions WHERE id = ?rN)r<r+rGrs rget_execution_statusz&SchedulerDatabase.get_execution_status4s  ) ) + + 2t\\"RUaTcddF//##C$'13x==T 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2s7AA A limitc4|5}|r|d||f}n2|r|d||f}n|d|f}d|DcdddS#1swxYwYdS)z6Get recent executions, optionally filtered by hostnameaC SELECT e.*, j.name as job_name, j.show_on_dashboard as show_on_dashboard_value FROM job_executions e JOIN jobs j ON e.job_id = j.id WHERE e.job_id = ? ORDER BY e.start_time DESC LIMIT ? an SELECT e.*, j.name as job_name, j.show_on_dashboard as show_on_dashboard_value FROM job_executions e JOIN jobs j ON e.job_id = j.id WHERE e.hostname = ? OR e.hostname IS NULL OR e.hostname = '' ORDER BY e.start_time DESC LIMIT ? a SELECT e.*, j.name as job_name, j.show_on_dashboard as show_on_dashboard_value FROM job_executions e JOIN jobs j ON e.job_id = j.id ORDER BY e.start_time DESC LIMIT ? c,g|]}t|SrRrrjs rrUz;SchedulerDatabase.get_recent_executions..XrrNr<r+r[)r!rrrkr1rbs rget_recent_executionsz'SchedulerDatabase.get_recent_executions;s   ) ) + + .nrrNr)r!rkr1rbs rget_active_executionsz'SchedulerDatabase.get_active_executionsZs  ) ) + + .rrNr*r+r[)r!rr1rbs rget_job_group_membersz'SchedulerDatabase.get_job_group_memberss  ! ! # #  L L L KKMMM K]X]]=N]]] ^ ^ ^ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _rc|5}|d||f|tjd|d|ddddS#1swxYwYdS)zRemove specific job from groupzm DELETE FROM job_group_members WHERE group_id = ? AND job_id = ? z Removed job z from group Nr)r!rrr1s rremove_job_from_groupz'SchedulerDatabase.remove_job_from_groups  ! ! # # Gt LLF# % % % KKMMM KEvEE8EE F F F  G G G G G G G G G G G G G G G G G GsAA))A-0A-c|5}|d|f|tjd|ddddS#1swxYwYdS)zRemove all jobs from groupz0DELETE FROM job_group_members WHERE group_id = ?zCleared all members from group Nr)r!rr1s rclear_group_membersz%SchedulerDatabase.clear_group_memberss  ! ! # # Ft LLKh[ Y Y Y KKMMM KD(DD E E E F F F F F F F F F F F F F F F F F FAA%%A),A) target_type target_idc P|dvrtd||dkr)||std|dn|dkr)||std|dn]|dkr)||std |dn.|d kr(||std |d|5}|d ||d |||tj ||dd|ddf}| |j } tj d|d| d|d|| cdddS#1swxYwYdS)a Create a schedule for a job or group Args: name: Schedule name target_type: 'job' or 'group' target_id: ID of job or group to execute schedule_type: 'cron', 'interval', 'date', 'manual' schedule_config: Dict with schedule configuration **kwargs: enabled, description, created_by Returns: Schedule ID )rrbackup_profilezInvalid target_type: rzJob z not foundrzGroup rzBackup profile sync_profilez Sync profile z INSERT INTO schedules ( name, description, target_type, target_id, schedule_type, schedule_config, enabled, created_by ) VALUES (?, ?, ?, ?, ?, ?, ?, ?) rZrT created_byuserzCreated schedule 'z' (ID z):  N) ValueErrorrrget_backup_profileget_sync_profiler*r+rrrr-rr r ) r!r{rrr~rrr1rb schedule_ids rcreate_schedulez!SchedulerDatabase.create_schedulesN" @ @ @B[BBCC C %  << ** ? != !=!=!=>>> ? G # #%%i00 A !?)!?!?!?@@@ A , , ,**955 J !H9!H!H!HIII J N * *((33 H !F!F!F!FGGG  ! ! # # t\\#  =)) ?++ 9d++ <00  F KKMMM *K KbTbbbbbbW`bb c c c'                  s&B(FF"Fc|5}d}g}g}|r|d|r-|d||||g|r|dd|zz }|dz }|||}d|D}t |} td|D} td |D} td |D} td |D} td |D}| |z }tj d |d| d| d| d| d|d|d| | dkrJd|D}|D];}tj d|dd|dd|dd|dd <|cd d d S#1swxYwYd S)!z Get all schedules with target information Args: enabled_only: Only return enabled schedules Returns: List of schedule dicts with target_name included a SELECT s.*, CASE s.target_type WHEN 'job' THEN j.name WHEN 'group' THEN g.name WHEN 'backup_profile' THEN bp.name END as target_name, COALESCE(s.show_on_dashboard, j.show_on_dashboard, 1) as show_on_dashboard_value FROM schedules s LEFT JOIN jobs j ON s.target_type = 'job' AND s.target_id = j.id LEFT JOIN job_groups g ON s.target_type = 'group' AND s.target_id = g.id LEFT JOIN backup_profiles bp ON s.target_type = 'backup_profile' AND s.target_id = bp.id z s.enabled = 1a(s.target_type NOT IN ('job', 'backup_profile', 'group') OR (s.target_type = 'job' AND (j.hostname IS NULL OR j.hostname = '' OR j.hostname = ?)) OR (s.target_type = 'backup_profile' AND (bp.hostname IS NULL OR bp.hostname = '' OR bp.hostname = ?)) OR (s.target_type = 'group' AND EXISTS ( SELECT 1 FROM job_group_members jgm JOIN jobs jj ON jgm.job_id = jj.id WHERE jgm.group_id = s.target_id AND (jj.hostname = ? OR jj.hostname IS NULL OR jj.hostname = '') ))) WHERE rz ORDER BY s.namec,g|]}t|SrRrrjs rrUz7SchedulerDatabase.get_all_schedules..<s>>>StCyy>>>rc32K|]}|ddkdVdS)rrriNrRrSrs r z6SchedulerDatabase.get_all_schedules..@s1LL!!M2Be2K2KA2K2K2K2KLLrc32K|]}|ddkdVdS)rrriNrRrs rrz6SchedulerDatabase.get_all_schedules..As1PPAAm4D4O4Oa4O4O4O4OPPrc32K|]}|ddkdVdS)rrriNrRrs rrz6SchedulerDatabase.get_all_schedules..Bs2ZZQQ}5EIY5Y5Yq5Y5Y5Y5YZZrc3*K|]}|d dVdS) target_nameNrirRrs rrz6SchedulerDatabase.get_all_schedules..Cs,#S#S!!M:J:RA:R:R:R:R#S#Src3*K|]}|d dVdS)rriNrRrs rrz6SchedulerDatabase.get_all_schedules..Ds+CCaa lCCCCCCCrzget_all_schedules(enabled_only=z ): total=z, jobs=z , groups=z , backups=z , enabled=z , disabled=z, null_targets=rc"g|] }|d | S)rrRrs rrUz7SchedulerDatabase.get_all_schedules..Ls"!Q!Q!Q-8H8P!8P8P8Prz Schedule idz 'r{z$' has NULL target_name (target_type=rz , target_id=rrXN) r*rextendr\r+r[r]sumr rrC)r!rrkr1rrrrbresultstotal job_count group_count backup_countnull_target_count enabled_countdisabled_countnull_schedulesscheds rget_all_schedulesz#SchedulerDatabase.get_all_scheduless< ! ! # #A t EJF 3!!/222 >!!     x8<=== >W\\*%=%=== ' 'E\\%00F>>FOO,=,=>>>GLLELLwLLLLLIPPPPPPPKZZ'ZZZZZL ##S#Sw#S#S#S S S CC7CCCCCM"]2N Lp<pp!&pp/8ppCNppZfpp#0pp=Kpp\mpp q q q!1$$!Q!QW!Q!Q!Q+jjEN$iuT{$i$ieFm$i$i16}1E$i$iSXYdSe$i$i$ijjjjCA A A A A A A A A A A A A A A A A A sF1GGGr c|5}|d|f}|}|rt|ndcdddS#1swxYwYdS)z*Get schedule by ID with target informationay SELECT s.*, CASE s.target_type WHEN 'job' THEN j.name WHEN 'group' THEN g.name WHEN 'backup_profile' THEN bp.name END as target_name FROM schedules s LEFT JOIN jobs j ON s.target_type = 'job' AND s.target_id = j.id LEFT JOIN job_groups g ON s.target_type = 'group' AND s.target_id = g.id LEFT JOIN backup_profiles bp ON s.target_type = 'backup_profile' AND s.target_id = bp.id WHERE s.id = ? Nr)r!r r1rbrHs r get_schedulezSchedulerDatabase.get_scheduleSs  ! ! # # .t\\ # ! !F//##C #-4999! . . . . . . . . . . . . . . . . . .rc|5}|dkr|d|f}n|dkr|d|f}na|dkr|d|f}nC|dkr|d|f}n%tjd | d d d d S|d ucd d d S#1swxYwYd S) a Validate that a schedule target exists Args: target_type: Type of target ('job', 'group', 'backup_profile', 'sync_profile') target_id: ID of the target Returns: True if target exists, False otherwise rzSELECT 1 FROM jobs WHERE id = ?rz%SELECT 1 FROM job_groups WHERE id = ?rz*SELECT 1 FROM backup_profiles WHERE id = ?rz(SELECT 1 FROM sync_profiles WHERE id = ?zUnknown target_type: NF)r*r+r rCrG)r!rrr1rbs rvalidate_schedule_targetz*SchedulerDatabase.validate_schedule_targetgsa ! ! # # 1te##&G)VV''&MPY|\\ 000&RU^T`aa..&PS\R^__D{DDEEE 1 1 1 1 1 1 1 1??$$D0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1sBC3CCCc x|5}g}g}dD]9}||vr3||d|||:d|vrB|d|tj|d|r|d||dd|d}||||tjd |d d d d S#1swxYwYd S) zUpdate schedule fields)r{rZrrr~rrrzschedule_config = ?rzUPDATE schedules SET rrzUpdated schedule N r*rrrr\r+r-r r )r!r rr1rrrrs rupdate_schedulez!SchedulerDatabase.update_schedules  ! ! # # ?tGF6 1 1F??NNe>>>222MM&-000 F**4555 dj0A)BCCDDD ??@@@ k***Q '0B0BQQQ UF+++  = ==>>>) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?sD D//D36D3c|5}|d|f|tjd|ddddS#1swxYwYdS)z2Delete schedule (does not delete target job/group)z"DELETE FROM schedules WHERE id = ?zDeleted schedule Nr)r!r r1s rdelete_schedulez!SchedulerDatabase.delete_schedules  ! ! # # ;t LL= ~ N N N KKMMM K9K99 : : : ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ;rc |5}|d||f}|}|rd|D}|d||f|t jdt |d|d|dd|t |cd d d S d d d d S#1swxYwYd S) a Delete all schedules associated with a specific target Args: target_type: Type of target ('job', 'group', 'backup_profile', 'sync_profile') target_id: ID of the target entity Returns: Number of schedules deleted zt SELECT id, name FROM schedules WHERE target_type = ? AND target_id = ? cg|] }|d S)r{rR)rSss rrUzBSchedulerDatabase._delete_schedules_for_target..s!?!?!?!F)!?!?!?rzw DELETE FROM schedules WHERE target_type = ? AND target_id = ? zDeleted z schedule(s) for rz: rNr)r*r+r[r-r r r]r\)r!rrr1rbrMschedule_namess rrz.SchedulerDatabase._delete_schedules_for_targetsw ! ! # # t\\#y)++F))I &!?!?Y!?!?!? "9-///   ~s9~~~~ ~~V_~~cgclclm{c|c|~~9~~#        &'                  sB5C%C%%C),C) backup_rootsourcesc |5}|d||d|tj|tj|dg|dd|ddf}||j}tjd|d ||cd d d S#1swxYwYd S) zCreate a new backup profilez INSERT INTO backup_profiles ( name, description, backup_root, sources, exclude_patterns, retention_days, enabled ) VALUES (?, ?, ?, ?, ?, ?, ?) rZexclude_patternsretention_daysrTzCreated backup profile 'rN) r*r+rrrr-rr r )r!r{r6r7rr1rb profile_ids rcreate_backup_profilez'SchedulerDatabase.create_backup_profiles0  ! ! # # t\\#  =)) 7## 6::&8"==>> +R00 9d++  F KKMMM)J KO4OO:OO P P P%                  CC))C-0C-r<c|5}|d|f}|}|rt|ndcdddS#1swxYwYdS)zGet backup profile by IDz*SELECT * FROM backup_profiles WHERE id = ?Nrr!r<r1rbrHs rr z$SchedulerDatabase.get_backup_profile  ! ! # # .t\\"NQ[P]^^F//##C #-4999 . . . . . . . . . . . . . . . . . .rc|5}g}g}|r|d|r*|d|||rdd|znd}|d|d|}d|Dcd d d S#1swxYwYd S) z8Get all backup profiles, optionally filtered by hostnamerz3(hostname = ? OR hostname IS NULL OR hostname = '')rrrzSELECT * FROM backup_profilesrc,g|]}t|SrRrrjs rrUz=SchedulerDatabase.get_all_backup_profiles..rrNrrs rget_all_backup_profilesz)SchedulerDatabase.get_all_backup_profiless%  ! ! # # HPYj!9!999bE\\"W%"W"W"WY_``F;;):):;;; < < < < < < < < < < < < < < < < < >>222MM&-000F""}--- dj ):;;<<<!V++5666 dj0B)CDDEEE D?@@@ j)))Wdii6H6HWWW UF+++  BjBBCCC/ D D D D D D D D D D D D D D D D D DsEE55E9<E9c|d|}|5}|d|f|t jd|d|dddddS#1swxYwYdS)z.Delete backup profile and associated schedulesrz(DELETE FROM backup_profiles WHERE id = ?zDeleted backup profile rrNrr!r<rr1s rdelete_backup_profilez'SchedulerDatabase.delete_backup_profiles!==>NPZ[[ ! ! # # ft LLCj] S S S KKMMM Kd*ddDUddd e e e f f f f f f f f f f f f f f f f f fr source_pathdestination_pathc |5}|d||d|||dd|dd|ddf}||j}t jd |d ||cd d d S#1swxYwYd S) zCreate a new sync profilez INSERT INTO sync_profiles ( name, description, source_path, destination_path, exclude_patterns, protect_excluded, enabled ) VALUES (?, ?, ?, ?, ?, ?, ?) rZr9rprotect_excludedFrTzCreated sync profile 'rNr*r+rr-rr r )r!r{rKrLrr1rbr<s rcreate_sync_profilez%SchedulerDatabase.create_sync_profiles  ! ! # # t\\#  =))  -r22 -u55 9d++  F KKMMM)J KMMMMM N N N%                  B#CC  C c|5}|d|f}|}|rt|ndcdddS#1swxYwYdS)zGet sync profile by IDz(SELECT * FROM sync_profiles WHERE id = ?Nrr@s rr z"SchedulerDatabase.get_sync_profile+s  ! ! # # .t\\"Lzm\\F//##C #-4999 . . . . . . . . . . . . . . . . . .rc|5}|r|d}n|d}d|DcdddS#1swxYwYdS)zGet all sync profilesz;SELECT * FROM sync_profiles WHERE enabled = 1 ORDER BY namez)SELECT * FROM sync_profiles ORDER BY namec,g|]}t|SrRrrjs rrUz;SchedulerDatabase.get_all_sync_profiles..9rrNrr!rr1rbs rget_all_sync_profilesz'SchedulerDatabase.get_all_sync_profiles2s  ! ! # # >>222MM&-000 B?@@@ j)))UDIIg4F4FUUU UF+++  @J@@AAA! B B B B B B B B B B B B B B B B B Br>c|d|}|5}|d|f|t jd|d|dddddS#1swxYwYdS)z,Delete sync profile and associated schedulesrz&DELETE FROM sync_profiles WHERE id = ?zDeleted sync profile rrNrrIs rdelete_sync_profilez%SchedulerDatabase.delete_sync_profileOs!==njYY ! ! # # dt LLAJ= Q Q Q KKMMM Kb bbBSbbb c c c d d d d d d d d d d d d d d d d d droperation_typerc (|5}|d|||d||f}||j}t jd|d||cdddS#1swxYwYdS)zCreate backup operation recordz INSERT INTO backup_operations ( profile_id, operation_type, backup_name, status, start_time ) VALUES (?, ?, ?, ?, ?) backup_namezCreated backup operation for profile N)r*r+rr-rr r) r!r<r[rrrr1rb operation_ids rcreate_backup_operationz)SchedulerDatabase.create_backup_operation\s ! ! # # t\\#  =))   F KKMMM!+L L\\\\PZ\\ ] ] ]!                  sA%BB B r_c |5}g}g}dD]9}||vr3||d|||:|rX||dd|d}||||ddddS#1swxYwYdS)zUpdate backup operation record) rrr] files_total files_linked files_copiedfiles_in_storetotal_size_bytesrlog_pathrzUPDATE backup_operations SET rrNr*rr\r+r-r!r_rr1rrrrs rupdate_backup_operationz)SchedulerDatabase.update_backup_operationqs"  ! ! # # tGFJ 1 1F??NNe>>>222MM&-000  l+++Y '8J8JYYY UF+++                   rc|5}|r|d||f}n|d|f}d|DcdddS#1swxYwYdS)zIGet recent backup operations, optionally filtered by hostname via profilea1 SELECT bo.* FROM backup_operations bo JOIN backup_profiles bp ON bo.profile_id = bp.id WHERE bp.hostname = ? OR bp.hostname IS NULL OR bp.hostname = '' ORDER BY bo.start_time DESC LIMIT ? z SELECT * FROM backup_operations ORDER BY start_time DESC LIMIT ? c,g|]}t|SrRrrjs rrUzBSchedulerDatabase.get_recent_backup_operations..rrNr)r!rrkr1rbs rget_recent_backup_operationsz.SchedulerDatabase.get_recent_backup_operationss  ) ) + + >>222MM&-000  l+++Wdii6H6HWWW UF+++                   rc|5}|d|f}d|DcdddS#1swxYwYdS)zGet recent sync operationsz| SELECT * FROM sync_operations ORDER BY start_time DESC LIMIT ? c,g|]}t|SrRrrjs rrUz@SchedulerDatabase.get_recent_sync_operations..rrNr)r!rr1rbs rget_recent_sync_operationsz,SchedulerDatabase.get_recent_sync_operationss  ! ! # # . rrNrrUs rget_all_project_configsz)SchedulerDatabase.get_all_project_configss  ! ! # # >>222MM&-000 D?@@@ j)))Wdii6H6HWWW UF+++  BjBBCCC! D D D D D D D D D D D D D D D D D Dr>c|5}|d|f|tjd|ddddS#1swxYwYdS)z;Delete project configuration (CASCADE deletes all versions)z(DELETE FROM project_configs WHERE id = ?zDeleted project config Nr)r!rr1s rdelete_project_configz'SchedulerDatabase.delete_project_configs  ! ! # # @t LLCj] S S S KKMMM K>*>> ? ? ? @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @r version_name zip_filenamec |5}|d|||d||dd|dd|ddf}||j}t jd|d ||cd d d S#1swxYwYd S) z#Create a new project version recordz INSERT INTO project_versions ( project_id, version_name, description, zip_filename, size_bytes, files_count, created_by ) VALUES (?, ?, ?, ?, ?, ?, ?) rZ size_bytesr files_countrrzCreated version 'z' for project NrO)r!rrrrr1rb version_ids rcreate_project_versionz(SchedulerDatabase.create_project_version(s ! ! # # t\\#  =)) <++ =!,, <00  F KKMMM)J KTLTT TT U U U%                  rQrc|5}|d|f}|}|rt|ndcdddS#1swxYwYdS)zGet project version by IDz+SELECT * FROM project_versions WHERE id = ?Nr)r!rr1rbrHs rget_project_versionz%SchedulerDatabase.get_project_version?s  ! ! # # .t\\"OR\Q^__F//##C #-4999 . . . . . . . . . . . . . . . . . .rc|5}|r|d||f}n|d|f}d|DcdddS#1swxYwYdS)zGGet all versions for a project, ordered by creation date (newest first)z SELECT * FROM project_versions WHERE project_id = ? ORDER BY created_at DESC LIMIT ? z SELECT * FROM project_versions WHERE project_id = ? ORDER BY created_at DESC c,g|]}t|SrRrrjs rrUz:SchedulerDatabase.get_project_versions..VrrNr)r!rrr1rbs rget_project_versionsz&SchedulerDatabase.get_project_versionsFs  ! ! # # >>222MM&-000  j)))Wdii6H6HWWW UF+++                   rnotification_typemessagec L|5}|d|||||d|dkrtjnd|df}||jcdddS#1swxYwYdS)zCreate notification recordz INSERT INTO notifications (execution_id, notification_type, status, message, sent_at, error) VALUES (?, ?, ?, ?, ?, ?) sent_atsentNerror)r*r+rrrr-r)r!rrrrrr1rbs rcreate_notificationz%SchedulerDatabase.create_notifications  ! ! # # $t\\#! 9&8H8HhlnnndSS 7##   F KKMMM# $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $sA7BB B)NNN)rNN)FN)r)rNN)N)Nr)F)rkN)rk)Y__name__ __module__ __qualname____doc__rstrrr"rr r<r*r@r.rintrIrYboolr/r6 Connectionr,rrrrrrrrrrrrrrrrrrrrrrrrr r(r*r,r/r1rrr=r rDrGrJrPr rVrXrZrr`rjrnrprrrwrzr|rrrrrrrrrrrrrr __classcell__)rs@rr r s* 22I##Z] +KCGCCCC C=@CCCC(,,,.  ^ ^^OOO 8C=    N $@kT7#5kTkTkTkT^!s!c!C!"%!8<!KN!!!!F.c.htCH~&>.....C.HT#s(^,D.... < < < 2J....22#2222<<3<6J < < < <===C=S=il====. _ _ _ _ _GcG3GGGGFCFFFF4C4c4c4&)4<@4OR4444lKKdKcKUYZ^_bdg_gZhUiKKKKZ..c3h0H....(1C1C1D11114?3????0;3;;;;PSD#C$]`,.S.Xd38n5M.... < :R . . . .6S6T6666#CSVeh*.S.Xd38n5M....<A..c.htCH~6N....<$MP$$$$,&$$$UX$cf$ux$$$$$$$$rr )rr6rrsrpathlibrtypingrrrr contextlibr logurur r nDatabaseDatabaserRrrrs  ,,,,,,,,,,,,%%%%%%U$U$U$U$U$U$U$U$l,   r