| <?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'); | |
| return false; | |
| } | |
| // change snippet dtd | |
| $ret = $db->query("ALTER TABLE owa_domstream MODIFY events MEDIUMTEXT"); | |
| if (!$ret) { | |
| $this->e->notice('Failed to modify events column of owa_domstream'); | |
| return false; | |
| } | |
| // change snippet dtd | |
| $ret = $db->query("ALTER TABLE owa_site MODIFY description MEDIUMTEXT"); | |
| if (!$ret) { | |
| $this->e->notice('Failed to modify description column of owa_site'); | |
| return false; | |
| } | |
| // check for bad permissions on config file | |
| if (file_exists(OWA_DIR . 'owa-config.php')) { | |
| @chmod(OWA_DIR . 'owa-config.php', 0750); | |
| } | |
| if (file_exists(OWA_DIR . 'conf/owa-config.php')) { | |
| @chmod(OWA_DIR . 'conf/owa-config.php', 0750); | |
| } | |
| if (file_exists(OWA_DIR . 'cli.php')) { | |
| @chmod(OWA_DIR . 'cli.php', 0700); | |
| } | |
| // must return true | |
| return true; | |
| } | |
| function down() { | |
| $visitor = owa_coreAPI::entityFactory('base.visitor'); | |
| $visitor->dropColumn('num_prior_sessions'); | |
| $visitor->dropColumn('first_session_yyyymmdd'); | |
| $session = owa_coreAPI::entityFactory('base.session'); | |
| $session->dropColumn('yyyymmdd'); | |
| $session->dropColumn('is_bounce'); | |
| $session->dropColumn('referring_search_term_id'); | |
| $session->dropColumn('days_since_first_session'); | |
| $session->dropColumn('days_since_prior_session'); | |
| $session->dropColumn('num_prior_sessions'); | |
| $request = owa_coreAPI::entityFactory('base.request'); | |
| $request->dropColumn('yyyymmdd'); | |
| $request->dropColumn('prior_document_id'); | |
| $request->dropColumn('num_prior_sessions'); | |
| $click = owa_coreAPI::entityFactory('base.click'); | |
| $click->dropColumn('yyyymmdd'); | |
| $feed_request = owa_coreAPI::entityFactory('base.feed_request'); | |
| $feed_request->dropColumn('yyyymmdd'); | |
| $u = owa_coreAPI::entityFactory('base.user'); | |
| $u->dropColumn('api_key'); | |
| $u = owa_coreAPI::entityFactory('base.document'); | |
| $u->dropColumn('uri'); | |
| $af = owa_coreAPI::entityFactory('base.action_fact'); | |
| $af->dropTable(); | |
| $st = owa_coreAPI::entityFactory('base.search_term_dim'); | |
| $st->dropTable(); | |