1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
<?php
/**
* Piwik - Open source web analytics
*
* @link http://piwik.org
* @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
*/
namespace Piwik\Updates;
use Piwik\ArchiveProcessor\Rules;
use Piwik\DataAccess\ArchiveWriter;
use Piwik\Date;
use Piwik\Db\BatchInsert;
use Piwik\Db;
use Piwik\Plugins\VisitFrequency\API as VisitFrequencyApi;
use Piwik\Segment;
use Piwik\Updater;
use Piwik\Updates;
/**
*/
class Updates_2_1_1_b11 extends Updates
{
static function update()
{
$returningMetrics = array(
'nb_visits_returning',
'nb_actions_returning',
'max_actions_returning',
'sum_visit_length_returning',
'bounce_count_returning',
'nb_visits_converted_returning',
'nb_uniq_visitors_returning'
);
$now = Date::factory('now')->getDatetime();
$archiveNumericTables = Db::get()->fetchCol("SHOW TABLES LIKE '%archive_numeric%'");
// for each numeric archive table, copy *_returning metrics to VisitsSummary metrics w/ the appropriate
// returning visit segment
foreach ($archiveNumericTables as $table) {
// get archives w/ *._returning
$sql = "SELECT idarchive, idsite, period, date1, date2
FROM $table
WHERE name IN ('" . implode("','", $returningMetrics) . "')
GROUP BY idarchive";
$idArchivesWithReturning = Db::fetchAll($sql);
// get archives for visitssummary returning visitor segment
$sql = "SELECT idarchive, idsite, period, date1, date2
FROM $table
WHERE name = ?
GROUP BY idarchive";
$visitSummaryReturningSegmentDone = Rules::getDoneFlagArchiveContainsOnePlugin(
new Segment(VisitFrequencyApi::RETURNING_VISITOR_SEGMENT, $idSites = array()), 'VisitsSummary');
$idArchivesWithVisitReturningSegment = Db::fetchAll($sql, array($visitSummaryReturningSegmentDone));
// collect info for new visitssummary archives have to be created to match archives w/ *._returning
// metrics
$missingIdArchives = array();
$idArchiveMappings = array();
foreach ($idArchivesWithReturning as $row) {
$withMetricsIdArchive = $row['idarchive'];
foreach ($idArchivesWithVisitReturningSegment as $segmentRow) {
if ($row['idsite'] == $segmentRow['idsite']
&& $row['period'] == $segmentRow['period']
&& $row['date1'] == $segmentRow['date1']
&& $row['date2'] == $segmentRow['date2']
) {
$idArchiveMappings[$withMetricsIdArchive] = $segmentRow['idarchive'];
}
}
if (!isset($idArchiveMappings[$withMetricsIdArchive])) {
$missingIdArchives[$withMetricsIdArchive] = $row;
}
}
// if there are missing idarchives, fill out new archive row values
if (!empty($missingIdArchives)) {
$newIdArchiveStart = Db::fetchOne("SELECT MAX(idarchive) FROM $table") + 1;
foreach ($missingIdArchives as $withMetricsIdArchive => &$rowToInsert) {
$idArchiveMappings[$withMetricsIdArchive] = $newIdArchiveStart;
$rowToInsert['idarchive'] = $newIdArchiveStart;
$rowToInsert['ts_archived'] = $now;
$rowToInsert['name'] = $visitSummaryReturningSegmentDone;
$rowToInsert['value'] = ArchiveWriter::DONE_OK;
++$newIdArchiveStart;
}
// add missing archives
try {
$params = array();
foreach ($missingIdArchives as $missingIdArchive) {
$params[] = array_values($missingIdArchive);
}
BatchInsert::tableInsertBatch($table, array_keys(reset($missingIdArchives)), $params, $throwException = false);
} catch (\Exception $ex) {
Updater::handleQueryError($ex, "<batch insert>", false, __FILE__);
}
}
// update idarchive & name columns in rows with *._returning metrics
$updateSqlPrefix = "UPDATE $table
SET idarchive = CASE idarchive ";
$updateSqlSuffix = " END, name = CASE name ";
foreach ($returningMetrics as $metric) {
$newMetricName = substr($metric, 0, strlen($metric) - strlen(VisitFrequencyApi::COLUMN_SUFFIX));
$updateSqlSuffix .= "WHEN '$metric' THEN '" . $newMetricName . "' ";
}
$updateSqlSuffix .= " END WHERE idarchive IN (%s)
AND name IN ('" . implode("','", $returningMetrics) . "')";
// update only 1000 rows at a time so we don't send too large an SQL query to MySQL
foreach (array_chunk($missingIdArchives, 1000, $preserveKeys = true) as $chunk) {
$idArchives = array();
$updateSql = $updateSqlPrefix;
foreach ($chunk as $withMetricsIdArchive => $row) {
$updateSql .= "WHEN $withMetricsIdArchive THEN {$row['idarchive']} ";
$idArchives[] = $withMetricsIdArchive;
}
$updateSql .= sprintf($updateSqlSuffix, implode(',', $idArchives));
Updater::executeMigrationQuery($updateSql, false, __FILE__);
}
}
}
}
|
