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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
|
<?php
/**
* Piwik - Open source web analytics
*
* @link http://piwik.org
* @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
*
* @category Piwik
* @package Updates
*/
use Piwik\Core\Common;
/**
* @package Updates
*/
class Piwik_Updates_1_8_4_b1 extends Piwik_Updates
{
static function isMajorUpdate()
{
return true;
}
static function getSql($schema = 'Myisam')
{
$action = Common::prefixTable('log_action');
$duplicates = Common::prefixTable('log_action_duplicates');
$visitAction = Common::prefixTable('log_link_visit_action');
$conversion = Common::prefixTable('log_conversion');
$visit = Common::prefixTable('log_visit');
return array(
// add url_prefix column
" ALTER TABLE `$action`
ADD `url_prefix` TINYINT(2) NULL AFTER `type`;
" => 1060, // ignore error 1060 Duplicate column name 'url_prefix'
// remove protocol and www and store information in url_prefix
" UPDATE `$action`
SET
url_prefix = IF (
LEFT(name, 11) = 'http://www.', 1, IF (
LEFT(name, 7) = 'http://', 0, IF (
LEFT(name, 12) = 'https://www.', 3, IF (
LEFT(name, 8) = 'https://', 2, NULL
)
)
)
),
name = IF (
url_prefix = 0, SUBSTRING(name, 8), IF (
url_prefix = 1, SUBSTRING(name, 12), IF (
url_prefix = 2, SUBSTRING(name, 9), IF (
url_prefix = 3, SUBSTRING(name, 13), name
)
)
)
),
hash = CRC32(name)
WHERE
type = 1 AND
url_prefix IS NULL;
" => false,
// find duplicates
" DROP TABLE IF EXISTS `$duplicates`;
" => false,
" CREATE TABLE `$duplicates` (
`before` int(10) unsigned NOT NULL,
`after` int(10) unsigned NOT NULL,
KEY `mainkey` (`before`)
) ENGINE=MyISAM;
" => false,
// grouping by name only would be case-insensitive, so we GROUP BY name,hash
// ON (action.type = 1 AND canonical.hash = action.hash) will use index (type, hash)
" INSERT INTO `$duplicates` (
SELECT
action.idaction AS `before`,
canonical.idaction AS `after`
FROM
(
SELECT
name,
hash,
MIN(idaction) AS idaction
FROM
`$action` AS action_canonical_base
WHERE
type = 1 AND
url_prefix IS NOT NULL
GROUP BY name, hash
HAVING COUNT(idaction) > 1
)
AS canonical
LEFT JOIN
`$action` AS action
ON (action.type = 1 AND canonical.hash = action.hash)
AND canonical.name = action.name
AND canonical.idaction != action.idaction
);
" => false,
// replace idaction in log_link_visit_action
" UPDATE
`$visitAction` AS link
LEFT JOIN
`$duplicates` AS duplicates_idaction_url
ON link.idaction_url = duplicates_idaction_url.before
SET
link.idaction_url = duplicates_idaction_url.after
WHERE
duplicates_idaction_url.after IS NOT NULL;
" => false,
" UPDATE
`$visitAction` AS link
LEFT JOIN
`$duplicates` AS duplicates_idaction_url_ref
ON link.idaction_url_ref = duplicates_idaction_url_ref.before
SET
link.idaction_url_ref = duplicates_idaction_url_ref.after
WHERE
duplicates_idaction_url_ref.after IS NOT NULL;
" => false,
// replace idaction in log_conversion
" UPDATE
`$conversion` AS conversion
LEFT JOIN
`$duplicates` AS duplicates
ON conversion.idaction_url = duplicates.before
SET
conversion.idaction_url = duplicates.after
WHERE
duplicates.after IS NOT NULL;
" => false,
// replace idaction in log_visit
" UPDATE
`$visit` AS visit
LEFT JOIN
`$duplicates` AS duplicates_entry
ON visit.visit_entry_idaction_url = duplicates_entry.before
SET
visit.visit_entry_idaction_url = duplicates_entry.after
WHERE
duplicates_entry.after IS NOT NULL;
" => false,
" UPDATE
`$visit` AS visit
LEFT JOIN
`$duplicates` AS duplicates_exit
ON visit.visit_exit_idaction_url = duplicates_exit.before
SET
visit.visit_exit_idaction_url = duplicates_exit.after
WHERE
duplicates_exit.after IS NOT NULL;
" => false,
// remove duplicates from log_action
" DELETE action FROM
`$action` AS action
LEFT JOIN
`$duplicates` AS duplicates
ON action.idaction = duplicates.before
WHERE
duplicates.after IS NOT NULL;
" => false,
// remove the duplicates table
" DROP TABLE `$duplicates`;
" => false
);
}
static function update()
{
try {
self::enableMaintenanceMode();
Piwik_Updater::updateDatabase(__FILE__, self::getSql());
self::disableMaintenanceMode();
} catch (Exception $e) {
self::disableMaintenanceMode();
throw $e;
}
}
}
|
