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
|
<?php
/**
* Matomo - free/libre analytics platform
*
* @link https://matomo.org
* @license https://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
*/
namespace Piwik\Plugins\CoreAdminHome\Commands;
use Piwik\Common;
use Piwik\Container\StaticContainer;
use Piwik\Option;
use Piwik\Plugin\ConsoleCommand;
use Piwik\Plugins\SitesManager\Model;
use Piwik\Updater;
use Piwik\Updater\Migration\Factory as MigrationFactory;
/**
* Command to migrate annotations from option table to a separate db table.
*/
class MigrateAnnotations extends ConsoleCommand
{
protected function configure()
{
$this->setName('core:matomo550-migrate-annotations');
$this->addOptionalValueOption('chunk-size', null, 'How many annotations to migrate per SQL insert per site', 20);
$this->setDescription(
'Only needed for Matomo 5.5.0-b2 upgrade. ' .
'Migrate annotations from option table to a separate annotations table. ' .
'By default creates inserts of 20 annotations per SQL insert per site. You can lower this if you have annotations with long content.'
);
}
protected function doExecute(): int
{
$chunkSize = $this->getInput()->getOption('chunk-size');
$migrated = self::migrate($chunkSize);
$this->getOutput()->writeln($migrated ? 'Done' : 'All existing annotations already migrated.');
return self::SUCCESS;
}
public static function migrate(int $chunkSize = 20): bool
{
$migration = StaticContainer::get(MigrationFactory::class);
$migrations = [];
// create new annotations table
$migrations[] = $migration->db->createTable('annotations', [
'id' => 'BIGINT UNSIGNED NOT NULL AUTO_INCREMENT',
'idsite' => 'INTEGER UNSIGNED NOT NULL',
'date' => 'DATETIME NOT NULL',
'note' => 'TEXT NOT NULL',
'starred' => 'TINYINT(1) NOT NULL DEFAULT 0',
'user' => 'VARCHAR(100) NOT NULL',
], $primaryKey = 'id');
$migrations[] = $migration->db->addIndex('annotations', ['idsite', 'date']);
// insert values from options table
$migrated = false;
foreach (self::getAnnotationInsertsAndBindValues($chunkSize) as $migrationEntry) {
$migrations[] = $migration->db->boundSql($migrationEntry['sql'], $migrationEntry['bind']);
$migrated = true;
}
$updater = StaticContainer::get(Updater::class);
$updater->executeMigrations(__FILE__, $migrations);
return $migrated;
}
private static function getAnnotationsForSite(int $idSite): array
{
$optionName = sprintf('%s_annotations', $idSite);
$serialized = Option::get($optionName);
if ($serialized !== false) {
$result = Common::safe_unserialize($serialized);
if (!empty($result)) {
return $result;
}
}
return [];
}
private static function getAnnotationInsertsAndBindValues(int $chunkSize): array
{
$table = Common::prefixTable('annotations');
$data = [];
$model = new Model();
foreach ($model->getSitesId() as $siteID) {
$annotations = self::getAnnotationsForSite($siteID);
$chunks = array_chunk($annotations, $chunkSize);
foreach ($chunks as $chunk) {
$bindValues = [];
$placeholders = [];
foreach ($chunk as $annotation) {
$bindValues[] = $values = [
$siteID,
$annotation['date'],
Common::unsanitizeInputValue($annotation['note']),
$annotation['starred'],
$annotation['user'],
];
$placeholders[] = Common::getSqlStringFieldsArray($values);
}
// chunk always has array items, so it's safe to assume we have some bind values and placeholders
$sql = sprintf(
'INSERT INTO `%s` (`idsite`, `date`, `note`, `starred`, `user`) VALUES (%s)',
$table,
implode('), (', $placeholders)
);
$data[] = ['sql' => $sql, 'bind' => Common::flattenArray($bindValues)];
}
}
return $data;
}
}
|
