/deleteolddata.php
PHP | 114 lines | 82 code | 24 blank | 8 comment | 9 complexity | 9eaf6975c4991cac2ee6a528cb4289f9 MD5 | raw file
1<?php 2require_once('global.php'); 3 4if ($oldDataInterval > 0) 5{ 6 # deleting old HAR data 7 $query = sprintf("DELETE FROM har WHERE timestamp < DATE_SUB(now(), INTERVAL '%s' DAY)", 8 mysql_real_escape_string($oldDataInterval) 9 ); 10 11 $result = mysql_query($query); 12 13 if (!$result) { 14 error_log(mysql_error()); 15 exit; 16 } 17 18 # deleting old data for custom metrics 19 $query = sprintf("DELETE FROM metric WHERE timestamp < DATE_SUB(now(), INTERVAL '%s' DAY)", 20 mysql_real_escape_string($oldDataInterval) 21 ); 22 23 $result = mysql_query($query); 24 25 if (!$result) { 26 error_log(mysql_error()); 27 exit; 28 } 29 30 # deleting old data for yslow v2 31 $query = sprintf("DELETE FROM yslow2 WHERE timestamp < DATE_SUB(now(), INTERVAL '%s' DAY)", 32 mysql_real_escape_string($oldDataInterval) 33 ); 34 35 $result = mysql_query($query); 36 37 if (!$result) { 38 error_log(mysql_error()); 39 exit; 40 } 41 42 # deleting old data for pagespeed 43 $query = sprintf("DELETE FROM pagespeed WHERE timestamp < DATE_SUB(now(), INTERVAL '%s' DAY)", 44 mysql_real_escape_string($oldDataInterval) 45 ); 46 47 $result = mysql_query($query); 48 49 if (!$result) { 50 error_log(mysql_error()); 51 exit; 52 } 53 54 # deleting old data for dynatrace 55 $query = sprintf("DELETE FROM dynatrace WHERE timestamp < DATE_SUB(now(), INTERVAL '%s' DAY)", 56 mysql_real_escape_string($oldDataInterval) 57 ); 58 59 $result = mysql_query($query); 60 61 if (!$result) { 62 error_log(mysql_error()); 63 exit; 64 } 65 66 # deleting old URLs 67 $query = 'DELETE urls FROM urls 68 LEFT JOIN (SELECT DISTINCT url_id FROM yslow2) AS y ON urls.id = y.url_id 69 LEFT JOIN (SELECT DISTINCT url_id FROM pagespeed) AS p ON urls.id = p.url_id 70 LEFT JOIN (SELECT DISTINCT url_id FROM dynatrace) AS d ON urls.id = d.url_id 71 LEFT JOIN (SELECT DISTINCT url_id FROM metric) AS m ON urls.id = m.url_id 72 LEFT JOIN (SELECT DISTINCT url_id FROM user_urls) AS uu ON urls.id = uu.url_id 73 WHERE y.url_id IS NULL 74 AND p.url_id IS NULL 75 AND d.url_id IS NULL 76 AND m.url_id IS NULL 77 AND uu.url_id IS NULL'; 78 79 $result = mysql_query($query); 80 81 if (!$result) { 82 error_log(mysql_error()); 83 exit; 84 } 85 86 # resetting last_updated for URLs that have no measurements 87 $query = 'UPDATE urls 88 LEFT JOIN (SELECT DISTINCT url_id FROM yslow2) AS y ON urls.id = y.url_id 89 LEFT JOIN (SELECT DISTINCT url_id FROM pagespeed) AS p ON urls.id = p.url_id 90 LEFT JOIN (SELECT DISTINCT url_id FROM dynatrace) AS d ON urls.id = d.url_id 91 LEFT JOIN (SELECT DISTINCT url_id FROM metric) AS m ON urls.id = m.url_id 92 SET last_update = NULL, yslow2_last_id = NULL, pagespeed_last_id = NULL 93 WHERE y.url_id IS NULL AND p.url_id IS NULL AND m.url_id IS NULL'; 94 95 $result = mysql_query($query); 96 97 if (!$result) { 98 error_log(mysql_error()); 99 exit; 100 } 101 102 # deleting old events 103 $query = sprintf("DELETE FROM event WHERE (end IS NOT NULL AND end < DATE_SUB(now(), INTERVAL '%s' DAY)) OR (start < DATE_SUB(now(), INTERVAL '%s' DAY))", 104 mysql_real_escape_string($oldDataInterval), 105 mysql_real_escape_string($oldDataInterval) 106 ); 107 108 $result = mysql_query($query); 109 110 if (!$result) { 111 error_log(mysql_error()); 112 exit; 113 } 114}