ArchiveTableDao.php — Matomo Analytics – Powerful, Privacy-First Insights for WordPress 5.0.1

matomo / app / core / DataAccess / ArchiveTableDao.php

matomo / app / core / DataAccess Last commit date

LogQueryBuilder 2 years ago Actions.php 2 years ago ArchiveSelector.php 2 years ago ArchiveTableCreator.php 2 years ago ArchiveTableDao.php 2 years ago ArchiveWriter.php 2 years ago ArchivingDbAdapter.php 2 years ago LogAggregator.php 2 years ago LogQueryBuilder.php 2 years ago LogTableTemporary.php 2 years ago Model.php 2 years ago RawLogDao.php 2 years ago TableMetadata.php 2 years ago

ArchiveTableDao.php

103 lines

1	<?php
2
3	/**
4	* Matomo - free/libre analytics platform
5	*
6	* @link https://matomo.org
7	* @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
8	*/
9	namespace Piwik\DataAccess;
10
11	use Piwik\Common;
12	use Piwik\Date;
13	use Piwik\Db;
14	use Piwik\Metrics\Formatter;
15	/**
16	* Data Access class for querying numeric & blob archive tables.
17	*/
18	class ArchiveTableDao
19	{
20	/**
21	* Analyzes numeric & blob tables for a single table date (ie, `'2015_01'`) and returns
22	* statistics including:
23	*
24	* - number of archives present
25	* - number of invalidated archives
26	* - number of temporary archives
27	* - number of error archives
28	* - number of segment archives
29	* - number of numeric rows
30	* - number of blob rows
31	*
32	* @param string $tableDate ie `'2015_01'`
33	* @return array
34	*/
35	public function getArchiveTableAnalysis($tableDate)
36	{
37	$numericQueryEmptyRow = array('count_archives' => '-', 'count_invalidated_archives' => '-', 'count_temporary_archives' => '-', 'count_error_archives' => '-', 'count_segment_archives' => '-', 'count_numeric_rows' => '-');
38	$tableDate = str_replace("`", "", $tableDate);
39	// for sanity
40	$numericTable = Common::prefixTable("archive_numeric_{$tableDate}");
41	$blobTable = Common::prefixTable("archive_blob_{$tableDate}");
42	// query numeric table
43	$sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n SUM(CASE WHEN name LIKE 'done%' THEN 1 ELSE 0 END) AS count_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_invalidated_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_temporary_archives,\n SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_error_archives,\n SUM(CASE WHEN name LIKE 'done%' AND CHAR_LENGTH(name) > 32 THEN 1 ELSE 0 END) AS count_segment_archives,\n SUM(CASE WHEN name NOT LIKE 'done%' THEN 1 ELSE 0 END) AS count_numeric_rows,\n 0 AS count_blob_rows\n FROM `{$numericTable}`\n GROUP BY idsite, date1, date2, period";
44	$rows = Db::fetchAll($sql, array(\Piwik\DataAccess\ArchiveWriter::DONE_INVALIDATED, \Piwik\DataAccess\ArchiveWriter::DONE_OK_TEMPORARY, \Piwik\DataAccess\ArchiveWriter::DONE_ERROR));
45	// index result
46	$result = array();
47	foreach ($rows as $row) {
48	$result[$row['label']] = $row;
49	}
50	// query blob table & manually merge results (no FULL OUTER JOIN in mysql)
51	$sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n COUNT(*) AS count_blob_rows,\n SUM(OCTET_LENGTH(value)) AS sum_blob_length\n FROM `{$blobTable}`\n GROUP BY idsite, date1, date1, period";
52	foreach (Db::fetchAll($sql) as $blobStatsRow) {
53	$label = $blobStatsRow['label'];
54	if (isset($result[$label])) {
55	$result[$label] = array_merge($result[$label], $blobStatsRow);
56	} else {
57	$result[$label] = $blobStatsRow + $numericQueryEmptyRow;
58	}
59	}
60	return $result;
61	}
62	/**
63	* Return invalidation queue table data
64	*
65	* @param bool $prettyTime
66	*
67	* @return array
68	* @throws \Exception
69	*/
70	public function getInvalidationQueueData(bool $prettyTime = false) : array
71	{
72	$invalidationsTable = Common::prefixTable("archive_invalidations");
73	$segmentsTable = Common::prefixTable("segment");
74	$sql = "\n SELECT ai.*, s.definition \n FROM `{$invalidationsTable}` ai \n LEFT JOIN `{$segmentsTable}` s ON SUBSTRING(ai.name, 5) = s.hash\n GROUP BY ai.idinvalidation\n ORDER BY ts_invalidated, idinvalidation ASC";
75	$invalidations = Db::fetchAll($sql);
76	$metricsFormatter = new Formatter();
77	$data = [];
78	foreach ($invalidations as $i) {
79	$waiting = (int) Date::now()->getTimestampUTC() - Date::factory($i['ts_invalidated'])->getTimestampUTC();
80	$processing = (int) $i['ts_started'] ? Date::now()->getTimestampUTC() - (int) $i['ts_started'] : '';
81	if ($prettyTime) {
82	$waiting = $metricsFormatter->getPrettyTimeFromSeconds($waiting, true);
83	if ($processing != '') {
84	$processing = $metricsFormatter->getPrettyTimeFromSeconds($processing, true);
85	}
86	}
87	$d = [];
88	$d['Invalidation'] = (int) $i['idinvalidation'];
89	$d['Segment'] = $i['definition'];
90	$d['Site'] = (int) $i['idsite'];
91	$d['Period'] = $i['period'] == 1 ? 'Day' : ($i['period'] == 2 ? 'Week' : ($i['period'] == 3 ? 'Month' : ($i['period'] == 4 ? 'Year' : 'Range')));
92	$d['Date'] = $i['period'] == 1 ? $i['date1'] : ($i['period'] == 3 ? substr($i['date1'], 0, 7) : ($i['period'] == 4 ? substr($i['date1'], 0, 4) : $i['date1'] . ' - ' . $i['date2']));
93	$d['TimeQueued'] = $i['ts_invalidated'];
94	$d['Waiting'] = $waiting;
95	$d['Started'] = $i['ts_started'];
96	$d['Processing'] = $processing;
97	$d['Status'] = $i['status'] == 1 ? 'Processing' : 'Queued';
98	$data[] = $d;
99	}
100	return $data;
101	}
102	}
103