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 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 | <?php // // Open Web Analytics - An Open Source Web Analytics Framework // // Copyright 2006 Peter Adams. All rights reserved. // // Licensed under GPL v2.0 http://www.gnu.org/copyleft/gpl.html // // Unless required by applicable law or agreed to in writing, software // distributed under the License is distributed on an "AS IS" BASIS, // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. // See the License for the specific language governing permissions and // limitations under the License. // // $Id$ // /** * 005 Update Class * * @author Peter Adams <peter@openwebanalytics.com> * @copyright Copyright © 2006 Peter Adams <peter@openwebanalytics.com> * @license http://www.gnu.org/copyleft/gpl.html GPL v2.0 * @category owa * @package owa * @version $Revision$ * @since owa 1.3 */ class owa_base_005_update extends owa_update { var $schema_version = 5; var $is_cli_mode_required = true; function up() { $tables = array('owa_session', 'owa_request', 'owa_click', 'owa_feed_request'); foreach ($tables as $table) { // add yyyymmdd column to owa_session $db = owa_coreAPI::dbSingleton(); $db->addColumn($table, 'yyyymmdd', 'INT'); $db->addIndex($table, 'yyyymmdd'); $ret = $db->query("update $table set yyyymmdd = concat(cast(year as CHAR), lpad(CAST(month AS CHAR), 2, '0'), lpad(CAST(day AS CHAR), 2, '0')) "); if ($ret == true) { $this->e->notice('Added yyyymmdd column to '.$table); } else { $this->e->notice('Failed to add yyyymmdd column to '.$table); return false; } } $visitor = owa_coreAPI::entityFactory('base.visitor'); $ret = $visitor->addColumn('num_prior_sessions'); if (!$ret) { $this->e->notice('Failed to add num_prior_sessions column to owa_visitor'); return false; } $ret = $visitor->addColumn('first_session_yyyymmdd'); if (!$ret) { $this->e->notice('Failed to add first_session_yyyymmdd column to owa_visitor'); return false; } $ret = $db->query("update owa_visitor set first_session_yyyymmdd = concat(cast(first_session_year as CHAR), lpad(CAST(first_session_month AS CHAR), 2, '0'), lpad(CAST(first_session_day AS CHAR), 2, '0')) "); if (!$ret) { $this->e->notice('Failed to populate first_session_yyyymmdd column in owa_visitor'); return false; } $request = owa_coreAPI::entityFactory('base.request'); $ret = $request->addColumn('prior_document_id'); if (!$ret) { $this->e->notice('Failed to add prior_document_id column to owa_request'); return false; } $ret = $request->addColumn('num_prior_sessions'); if (!$ret) { $this->e->notice('Failed to add num_prior_sessions column to owa_request'); return false; } $session = owa_coreAPI::entityFactory('base.session'); $ret = $session->addColumn('num_prior_sessions'); if (!$ret) { $this->e->notice('Failed to add num_prior_sessions column to owa_session'); return false; } $ret = $session->addColumn('is_bounce'); if (!$ret) { $this->e->notice('Failed to add is_bounce column to owa_session'); return false; } $ret = $db->query("update owa_session set is_bounce = true WHERE num_pageviews = 1"); if (!$ret) { $this->e->notice('Failed to populate is_bounce column in owa_session'); return false; } $ret = $session->addColumn('referring_search_term_id'); if (!$ret) { $this->e->notice('Failed to add referring_search_term_id column in owa_session'); return false; } $ret = $session->addColumn('days_since_prior_session'); if (!$ret) { $this->e->notice('Failed to add days_since_prior_session column in owa_session'); return false; } $ret = $db->query("update owa_session set days_since_prior_session = round(time_sinse_priorsession/(3600*24)) WHERE time_sinse_priorsession IS NOT NULL and time_sinse_priorsession > 0"); if (!$ret) { $this->e->notice('Failed to populate days_since_prior_session column in owa_session'); return false; } $ret = $session->addColumn('days_since_first_session'); if (!$ret) { $this->e->notice('Failed to add days_since_first_session column in owa_session'); return false; } $ret = $db->query("update owa_session, owa_visitor set owa_session.days_since_first_session = round((owa_session.timestamp - owa_visitor.first_session_timestamp)/(3600*24)) WHERE owa_session.visitor_id = owa_visitor.id AND owa_visitor.first_session_timestamp IS NOT NULL"); if (!$ret) { $this->e->notice('Failed to populate days_since_first_session column in owa_session'); return false; } // add api column $u = owa_coreAPI::entityFactory('base.user'); $ret = $u->addColumn('api_key'); if (!$ret) { $this->e->notice('Failed to add api_key column to owa_user'); return false; } // add uri column $d = owa_coreAPI::entityFactory('base.document'); $d->addColumn('uri'); $ret = $db->query("update owa_document set uri = substring_index(SUBSTR(url FROM 1+ length(substring_index(url, '/', 3))), '#', 1) "); if (!$ret) { $this->e->notice('Failed to add uri column to owa_document'); return false; } $a = owa_coreAPI::entityFactory('base.action_fact'); $ret = $a->createTable(); if ($ret === true) { $this->e->notice('Action fact entity table created'); } else { $this->e->notice('Action fact entity table creation failed'); return false; } $st = owa_coreAPI::entityFactory('base.search_term_dim'); $ret = $st->createTable(); if ($ret === true) { $this->e->notice('Search Term Dimension entity table created'); } else { $this->e->notice('Search Term Dimension entity table creation failed'); return false; } // migrate search terms to new table $ret = $db->query( "INSERT INTO owa_search_term_dim (id, terms, term_count) SELECT distinct(CRC32(LOWER(query_terms))) as id, query_terms as terms, length(query_terms) + 1 - length(replace(query_terms,' ','')) as term_count FROM owa_referer WHERE query_terms != ''" ); if (!$ret) { $this->e->notice('Failed to migrate search terms to new table.'); return false; } //populate search term foreign key in session table $ret = $db->query( "UPDATE owa_session as session, owa_referer as referer SET session.referring_search_term_id = (CRC32(LOWER(referer.query_terms))) WHERE session.referer_id = referer.id and session.referer_id != 0 AND referer.query_terms != ''" ); if (!$ret) { $this->e->notice('Failed to add referring_search_term_id values to owa_session'); return false; } //populate search source in session table $ret = $db->query( "UPDATE owa_session as session SET session.source = 'organic-search' WHERE session.referring_search_term_id IS NOT null" ); if (!$ret) { $this->e->notice('Failed to populate session.source values for organic-search'); return false; } //populate search source in session table $ret = $db->query( "UPDATE owa_session as session SET session.source = 'referral' WHERE session.referer_id != 0 AND session.referer_id != '' AND session.referer_id IS NOT null AND session.source != 'feed' AND session.source != 'organic-search'" ); if (!$ret) { $this->e->notice('Failed to populate session.source values for referral'); return false; } // add apiKeys to each user $users = $db->get_results("select user_id from owa_user"); foreach ($users as $user) { $u = owa_coreAPI::entityFactory('base.user'); $u->load($user['user_id'],'user_id'); if (!$u->get('api_key')) { $u->set('api_key', $u->generateTempPasskey($u->get('user_id'))); $u->update(); } } // change character encoding to UTF-8 $tables = array('owa_request', 'owa_session', 'owa_feed_request', 'owa_click', 'owa_document', 'owa_ua', 'owa_site', 'owa_user', 'owa_configuration', 'owa_visitor', 'owa_os', 'owa_impression', 'owa_host', 'owa_exit','owa_domstream'); foreach ($tables as $table) { // change snippet dtd $ret = $db->query(sprintf("ALTER TABLE %s CONVERT TO CHARACTER SET utf8", $table)); if (!$ret) { $this->e->notice('Failed to change table character encoding for: ' .$table); return false; } } // change snippet dtd $ret = $db->query("ALTER TABLE owa_referer MODIFY snippet MEDIUMTEXT"); if (!$ret) { $this->e->notice('Failed to modify snippet column of owa_referer'); return false; } // change snippet dtd $ret = $db->query("ALTER TABLE owa_domstream MODIFY page_url VARCHAR(255)"); if (!$ret) { $this->e->notice('Failed to modify page_url column of owa_domstream'); &nbs |