LogQueryBuilder
3 months ago
Actions.php
6 months ago
ArchiveSelector.php
3 months ago
ArchiveTableCreator.php
3 months ago
ArchiveTableDao.php
3 months ago
ArchiveWriter.php
3 months ago
ArchivingDbAdapter.php
1 year ago
LogAggregator.php
3 months ago
LogQueryBuilder.php
6 months ago
LogTableTemporary.php
2 years ago
Model.php
3 months ago
RawLogDao.php
6 months ago
TableMetadata.php
1 year ago
ArchiveTableDao.php
104 lines
| 1 | <?php |
| 2 | |
| 3 | /** |
| 4 | * Matomo - free/libre analytics platform |
| 5 | * |
| 6 | * @link https://matomo.org |
| 7 | * @license https://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 = ['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 IN (?, ?) 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 ORDER BY idsite, period, date1, date2"; |
| 44 | $rows = Db::fetchAll($sql, array(\Piwik\DataAccess\ArchiveWriter::DONE_INVALIDATED, \Piwik\DataAccess\ArchiveWriter::DONE_OK_TEMPORARY, \Piwik\DataAccess\ArchiveWriter::DONE_ERROR, \Piwik\DataAccess\ArchiveWriter::DONE_ERROR_INVALIDATED)); |
| 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, date2, period ORDER BY idsite, period, date1, date2"; |
| 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 | // ensure rows without numeric entries have the |
| 58 | // same internal result array key order |
| 59 | $result[$label] = array_merge(['label' => $label], $numericQueryEmptyRow, $blobStatsRow); |
| 60 | } |
| 61 | } |
| 62 | return $result; |
| 63 | } |
| 64 | /** |
| 65 | * Return invalidation queue table data |
| 66 | * |
| 67 | * |
| 68 | * @return array |
| 69 | * @throws \Exception |
| 70 | */ |
| 71 | public function getInvalidationQueueData(bool $prettyTime = \false) : array |
| 72 | { |
| 73 | $invalidationsTable = Common::prefixTable("archive_invalidations"); |
| 74 | $segmentsTable = Common::prefixTable("segment"); |
| 75 | $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"; |
| 76 | $invalidations = Db::fetchAll($sql); |
| 77 | $metricsFormatter = new Formatter(); |
| 78 | $data = []; |
| 79 | foreach ($invalidations as $i) { |
| 80 | $waiting = (int) Date::now()->getTimestampUTC() - Date::factory($i['ts_invalidated'])->getTimestampUTC(); |
| 81 | $processing = (int) $i['ts_started'] ? Date::now()->getTimestampUTC() - (int) $i['ts_started'] : ''; |
| 82 | if ($prettyTime) { |
| 83 | $waiting = $metricsFormatter->getPrettyTimeFromSeconds($waiting, \true); |
| 84 | if ($processing != '') { |
| 85 | $processing = $metricsFormatter->getPrettyTimeFromSeconds($processing, \true); |
| 86 | } |
| 87 | } |
| 88 | $d = []; |
| 89 | $d['Invalidation'] = (int) $i['idinvalidation']; |
| 90 | $d['Segment'] = $i['definition']; |
| 91 | $d['Site'] = (int) $i['idsite']; |
| 92 | $d['Period'] = $i['period'] == 1 ? 'Day' : ($i['period'] == 2 ? 'Week' : ($i['period'] == 3 ? 'Month' : ($i['period'] == 4 ? 'Year' : 'Range'))); |
| 93 | $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'])); |
| 94 | $d['TimeQueued'] = $i['ts_invalidated']; |
| 95 | $d['Waiting'] = $waiting; |
| 96 | $d['Started'] = $i['ts_started']; |
| 97 | $d['Processing'] = $processing; |
| 98 | $d['Status'] = $i['status'] == 1 ? 'Processing' : 'Queued'; |
| 99 | $data[] = $d; |
| 100 | } |
| 101 | return $data; |
| 102 | } |
| 103 | } |
| 104 |