PageRenderTime 38ms CodeModel.GetById 26ms RepoModel.GetById 1ms app.codeStats 0ms

/deleteolddata.php

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