/deleteolddata.php

  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}