projects / bus.git / blobdiff
search:
summary | shortlog | log | commit | commitdiff | tree
side by side | plain
Add analytics
[bus.git] / busui / owa / modules / base / updates / 005.php
blob:a/busui/owa/modules/base/updates/005.php -> blob:b/busui/owa/modules/base/updates/005.php 
--- a/busui/owa/modules/base/updates/005.php
+++ b/busui/owa/modules/base/updates/005.php
@@ -1,1 +1,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 &copy; 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();
+		
+		return true;
+	}
+}
+
+?>
Unnamed repository; edit this file to name it for gitweb.
GitPHP by Chris Han