/* Cleaning backups Equbay Kiflay January 2020 */ /* Cleaning file backups */ /* Clean all after a year. */ UPDATE backups SET delete_pending=1 WHERE NOT delete_pending AND (CAST(strftime('%Y',datetime('now')) AS integer) - CAST(strftime('%Y',backuptime) AS integer))*360+ (CAST(strftime('%m',datetime('now')) AS integer) - CAST(strftime('%m',backuptime) AS integer))*30+ (CAST(strftime('%d',datetime('now')) AS integer) - CAST(strftime('%d',backuptime) AS integer)) >366; /* older than a year */ /* Clean dailys after 2 months. Retain monthly backups */ UPDATE backups SET delete_pending=1 WHERE NOT delete_pending AND id NOT IN ( SELECT CASE strftime('%Y%m%d',backuptime) WHEN max(strftime('%Y%m%d',backuptime)) THEN id END FROM backups GROUP BY clientid,strftime('%Y%m',backuptime) ) AND (CAST(strftime('%Y',datetime('now')) AS integer) - CAST(strftime('%Y',backuptime) AS integer))*360+ (CAST(strftime('%m',datetime('now')) AS integer) - CAST(strftime('%m',backuptime) AS integer))*30+ (CAST(strftime('%d',datetime('now')) AS integer) - CAST(strftime('%d',backuptime) AS integer)) >60; /* older than 60 days */ /* Clean intraday after 2 days. Retain daily backups. */ UPDATE backups SET delete_pending=1 WHERE NOT delete_pending AND id NOT IN ( SELECT CASE strftime('%Y%m%d%H%M',backuptime) WHEN max(strftime('%Y%m%d%H%M',backuptime)) THEN id END FROM backups GROUP BY clientid,strftime('%Y%m%d',backuptime) ) AND (CAST(strftime('%Y',datetime('now')) AS integer) - CAST(strftime('%Y',backuptime) AS integer))*360+ (CAST(strftime('%m',datetime('now')) AS integer) - CAST(strftime('%m',backuptime) AS integer))*30+ (CAST(strftime('%d',datetime('now')) AS integer) - CAST(strftime('%d',backuptime) AS integer)) >2; /* older than 2 days */ /* Cleaning of Image backups*/ /* Clean everything after a year */ UPDATE backup_images SET delete_pending=1 WHERE id NOT IN ( SELECT a.id FROM backup_images a INNER JOIN backup_images b ON a.id=b.incremental_ref /* basis */ UNION SELECT assoc_id FROM assoc_images WHERE img_id in (SELECT a.id FROM backup_images a INNER JOIN backup_images b ON a.id=b.incremental_ref) /* associate of basis */ ) AND NOT delete_pending AND (CAST(strftime('%Y',datetime('now')) AS integer) - CAST(strftime('%Y',backuptime) AS integer))*360+ (CAST(strftime('%m',datetime('now')) AS integer) - CAST(strftime('%m',backuptime) AS integer))*30+ (CAST(strftime('%d',datetime('now')) AS integer) - CAST(strftime('%d',backuptime) AS integer)) >366; /* older than a year */ /* Clean dailys after 2 months. Retain monthly backups */ UPDATE backup_images SET delete_pending=1 WHERE id NOT IN ( SELECT a.id FROM backup_images a INNER JOIN backup_images b ON a.id=b.incremental_ref /* basis */ UNION SELECT assoc_id FROM assoc_images WHERE img_id in (SELECT a.id FROM backup_images a INNER JOIN backup_images b ON a.id=b.incremental_ref) /* associate of basis */ UNION SELECT CASE strftime('%Y%m%d',backuptime) WHEN max(strftime('%Y%m%d',backuptime)) THEN id END FROM backup_images GROUP BY clientid,strftime('%Y%m',backuptime) /* monthly bac kups to retain */ ) AND NOT delete_pending AND (CAST(strftime('%Y',datetime('now')) AS integer) - CAST(strftime('%Y',backuptime) AS integer))*360+ (CAST(strftime('%m',datetime('now')) AS integer) - CAST(strftime('%m',backuptime) AS integer))*30+ (CAST(strftime('%d',datetime('now')) AS integer) - CAST(strftime('%d',backuptime) AS integer)) >60; /* older than 60 days */ /* Clean dailys after 2 weeks. Retain weeklys. */ /* Clean intraday after 2 days. Retain daily backups. */ UPDATE backup_images SET delete_pending=1 WHERE id NOT IN ( SELECT a.id FROM backup_images a INNER JOIN backup_images b ON a.id=b.incremental_ref /* basis */ UNION SELECT assoc_id FROM assoc_images WHERE img_id in (SELECT a.id FROM backup_images a INNER JOIN backup_images b ON a.id=b.incremental_ref) /* associate of basis */ UNION SELECT CASE strftime('%Y%m%d%H%M',backuptime) WHEN max(strftime('%Y%m%d%H%M',backuptime)) THEN id END FROM backup_images GROUP BY clientid,strftime('%Y%m%d',backuptime) /* daily backups to retain */ ) AND NOT delete_pending AND (CAST(strftime('%Y',datetime('now')) AS integer) - CAST(strftime('%Y',backuptime) AS integer))*360+ (CAST(strftime('%m',datetime('now')) AS integer) - CAST(strftime('%m',backuptime) AS integer))*30+ (CAST(strftime('%d',datetime('now')) AS integer) - CAST(strftime('%d',backuptime) AS integer)) >2; /* older than 2 days */