function action()
{
$db = owa_coreAPI::dbSingleton();
$db->selectFrom('owa_user');
$db->selectColumn("*");
$users = $db->getAllRows();
$this->set('users', $users);
$this->setView('base.options');
$this->setSubview('base.users');
}
function up()
{
$db = owa_coreAPI::dbSingleton();
$s =& owa_coreAPI::serviceSingleton();
$entities = $s->modules[$this->module_name]->getEntities();
foreach ($entities as $k => $v) {
$ret = $db->alterTableType($this->c->get('base', 'ns') . $v, 'InnoDB');
if ($ret == true) {
$this->e->notice(sprintf('Changed Table %s to InnoDB', $v));
} else {
$this->e->notice(sprintf('Change to Table %s failed', $v));
return false;
}
}
return true;
}
function action()
{
$db = owa_coreAPI::dbSingleton();
$db->selectColumn("distinct session.visitor_id as visitor_id, visitor.user_name, visitor.user_email");
$db->selectFrom('owa_session', 'session');
$db->join(OWA_SQL_JOIN_LEFT_OUTER, 'owa_visitor', 'visitor', 'visitor_id', 'visitor.id');
$db->where('site_id', $this->getParam('site_id'));
// make new timeperiod of a day
$period = owa_coreAPI::makeTimePeriod('day', array('startDate' => $this->getParam('first_session')));
$start = $period->getStartDate();
$end = $period->getEndDate();
//print_r($period);
// set new period so lables show up right.
$db->where('first_session_timestamp', array('start' => $start->getTimestamp(), 'end' => $end->getTimestamp()), 'BETWEEN');
$ret = $db->getAllRows();
$this->set('visitors', $ret);
$this->setSubview('base.reportVisitorsRoster');
$this->setTitle('New Visitors from', $period->getStartDate()->label);
}
/**
* @return array
*/
private function getAllUserRows()
{
$db = owa_coreAPI::dbSingleton();
$db->selectFrom('owa_user');
$db->selectColumn("*");
return $db->getAllRows();
}
function checkDbConnection()
{
// Check DB connection status
$db =& owa_coreAPI::dbSingleton();
$db->connect();
if ($db->connection_status === true) {
return true;
} else {
return false;
}
}
function __construct($queue_dir = '')
{
$this->db = owa_coreAPI::dbSingleton();
return parent::__construct();
}
/**
* Constructor
*
* @return owa_install
*/
function owa_install()
{
$this->owa_base();
$this->db =& owa_coreAPI::dbSingleton();
return;
}
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 = \n\t\t\t\t\t\tconcat(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 = \n\t\t\t\t\t\tconcat(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\n\t\t\t\towa_search_term_dim (id, terms, term_count) \n\t\t\tSELECT \n\t\t\t\tdistinct(CRC32(LOWER(query_terms))) as id, \n\t\t\t\tquery_terms as terms, \n\t\t\t\tlength(query_terms) + 1 - length(replace(query_terms,' ','')) as term_count \n\t\t\tFROM \n\t\t\t\towa_referer\n\t\t\tWHERE\n\t\t\t\tquery_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 \n\t\t\t\towa_session as session, owa_referer as referer\n\t\t\tSET\n \t\t\tsession.referring_search_term_id = (CRC32(LOWER(referer.query_terms))) \n\t\t\tWHERE\n \t\t\tsession.referer_id = referer.id and\n \t\t\tsession.referer_id != 0 AND\n \t\t\treferer.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 \n\t\t\t\towa_session as session\n\t\t\tSET\n \t\t\tsession.source = 'organic-search'\n\t\t\tWHERE\n \t\t\tsession.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 \n\t\t\t\towa_session as session\n\t\t\tSET\n \t\t\tsession.source = 'referral'\n\t\t\tWHERE\n \t\t\tsession.referer_id != 0 AND\n \t\t\tsession.referer_id != '' AND\n \t\t\tsession.referer_id IS NOT null AND\n \t\t\tsession.source != 'feed' AND\n \t\t\tsession.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;
}
public static function summarize($map)
{
$entity = owa_coreAPI::entityFactory($map['entity']);
$db = owa_coreAPI::dbSingleton();
$db->selectFrom($entity->getTableName(), $entity->getTableAlias());
foreach ($map['columns'] as $col => $action) {
switch ($action) {
case 'sum':
$col_def = sprintf("SUM(%s)", $col);
$name = $col . '_sum';
break;
case 'count':
$col_def = sprintf("COUNT(%s)", $col);
$name = $col . '_count';
break;
case 'count_distinct':
$col_def = sprintf("COUNT(distinct %s)", $col);
$name = $col . '_dcount';
break;
case 'max':
$col_def = sprintf("MAX(%s)", $col);
$name = $col . '_max';
break;
}
$db->selectColumn($col_def, $name);
}
foreach ($map['constraints'] as $con_col => $con_value) {
if (is_array($con_value)) {
$db->where($con_col, $con_value['value'], $con_value['operator']);
} else {
$db->where($con_col, $con_value);
}
}
$ret = $db->getOneRow();
return $ret;
}
function getSelect()
{
if ($this->select) {
// old style metrics populate this explicitly.
return $this->select;
} else {
$db = owa_coreAPI::dbSingleton();
switch ($this->type) {
case 'count':
$statement = $db->count($this->getColumn());
break;
case 'distinct_count':
$statement = $db->count($db->distinct($this->getColumn()));
break;
case 'sum':
$statement = $db->sum($this->getColumn());
break;
}
return array($statement, $this->getName());
}
}
function connect()
{
$this->db = owa_coreAPI::dbSingleton();
owa_coreAPI::debug('Connected to event queue.');
return true;
}
/**
* Returns collection of owa_user entities that are allowed for current user
* @return owa_user[]
*/
public function getAssignedUsers()
{
if (!$this->get('id')) {
throw new Exception('no site data loaded!');
}
if (!isset(self::$cachedAssignedUsers[$this->get('id')])) {
$db = owa_coreAPI::dbSingleton();
$db->selectFrom('owa_site_user');
$db->selectColumn('*');
$db->where('site_id', $this->get('id'));
$relations = $db->getAllRows();
$result = array();
if (is_array($relations)) {
foreach ($relations as $row) {
$userEntity = owa_coreApi::entityFactory('base.user');
$userEntity->load($row['user_id']);
$result[] = $userEntity;
}
}
self::$cachedAssignedUsers[$this->get('id')] = $result;
}
return self::$cachedAssignedUsers[$this->get('id')];
}
function down()
{
foreach ($this->entities as $entity => $operations) {
$e = owa_coreAPI::entityFactory($entity);
foreach ($operations as $operation => $items) {
foreach ($items as $item) {
if ($operation === 'addColumn') {
$operation = 'dropColumn';
}
$ret = $e->{$operation}($item);
if ($ret === true) {
$this->e->notice("Applied {$operation} on {$entity} for {$item}");
} else {
$this->e->notice("Applying {$operation} on {$entity} for {$item} failed.");
}
}
}
}
// drop indexes
$db = owa_coreAPI::dbSingleton();
$db->dropIndex('owa_action_fact', 'yyyymmdd');
$db->dropIndex('owa_action_fact', 'action_group');
$db->dropIndex('owa_commerce_transaction_fact', 'yyyymmdd');
$db->dropIndex('owa_commerce_line_item_fact', 'yyyymmdd');
$db->dropIndex('owa_queue_item', 'status');
$db->dropIndex('owa_queue_item', 'event_type');
$db->dropIndex('owa_queue_item', 'not_before_timestamp');
$db->dropIndex('owa_domstream', 'yyyymmdd');
$db->dropIndex('owa_domstream', 'domstream_guid');
$db->dropIndex('owa_domstream', 'document_id');
return true;
}
function down()
{
$session = owa_coreAPI::entityFactory('base.session');
// owa_session columns to drop
$session_columns = array('num_goals', 'num_goal_starts', 'goals_value', 'location_id', 'language', 'source_id', 'ad_id', 'campaign_id', 'latest_attributions', 'commerce_trans_count', 'commerce_trans_revenue', 'commerce_items_revenue', 'commerce_items_count', 'commerce_items_quantity', 'commerce_shipping_revenue', 'commerce_tax_revenue');
// add in goal related columns
$goals = owa_coreAPI::getSetting('base', 'numGoals');
for ($i = 1; $i <= $goals; $i++) {
$session_columns[] = 'goal_' . $i;
$session_columns[] = 'goal_' . $i . '_start';
$session_columns[] = 'goal_' . $i . '_value';
}
//drop columns from owa_session
foreach ($session_columns as $session_col_name) {
$session->dropColumn($session_col_name);
}
//rename col back to original
$session->renameColumn('medium', 'source', true);
//drop request columns
$request = owa_coreAPI::entityFactory('base.request');
$request_columns = array('location_id', 'language');
// add columns to owa_session
foreach ($request_columns as $request_col_name) {
$ret = $request->dropColumn($request_col_name);
}
$domstream = owa_coreAPI::entityFactory('base.domstream');
$domstream->dropColumn('domstream_guid');
$site = owa_coreAPI::entityFactory('base.site');
$site->dropColumn('settings');
//$site->modifyColumn('id');
$db = owa_coreAPI::dbSingleton();
$db->query('ALTER TABLE owa_site MODIFY id SERIAL');
$db->query('UPDATE owa_site SET id = id_1_3');
$ret = $db->query('ALTER TABLE owa_site MODIFY id INT');
$db->query('ALTER TABLE owa_site DROP id_1_3');
$click = owa_coreAPI::entityFactory('base.click');
$click->dropColumn('dom_element_class');
$click->dropColumn('dom_element_parent_id');
//drop tables
$new_entities = array('base.ad_dim', 'base.source_dim', 'base.campaign_dim', 'base.location_dim', 'base.commerce_transaction_fact', 'base.commerce_line_item_fact', 'base.queue_item');
foreach ($new_entities as $entity_name) {
$entity = owa_coreAPI::entityFactory($entity_name);
$ret = $entity->dropTable();
}
return true;
}
function __construct($db = '')
{
if ($db) {
$this->db = $db;
} else {
$this->db = owa_coreAPI::dbSingleton();
}
$this->formatters = array('timestamp' => array($this, 'formatSeconds'), 'percentage' => array($this, 'formatPercentage'), 'integer' => array($this, 'numberFormatter'), 'currency' => array($this, 'formatCurrency'));
return parent::__construct();
}
function getDomstream($domstream_guid)
{
if (!$domstream_guid) {
return;
}
// Fetch document object
$d = owa_coreAPI::entityFactory('base.domstream');
//$d->load($this->getParam('domstream_id'));
//$json = new Services_JSON();
//$d->set('events', $json->decode($d->get('events')));
$db = owa_coreAPI::dbSingleton();
$db->select('*');
$db->from($d->getTableName());
$db->where('domstream_guid', $domstream_guid);
$db->orderBy('timestamp', 'ASC');
$ret = $db->getAllRows();
//print_r($ret);
$combined = '';
foreach ($ret as $row) {
$combined = $this->mergeStreamEvents($row['events'], $combined);
}
$row['events'] = json_decode($combined);
$t = new owa_template();
$t->set_template('json.php');
//$json = new Services_JSON();
// set
// if not found look on the request scope.
$callback = owa_coreAPI::getRequestParam('jsonpCallback');
if (!$callback) {
$t->set('json', json_encode($row));
} else {
$body = sprintf("%s(%s);", $callback, json_encode($row));
$t->set('json', $body);
}
return $t->fetch();
}
function action()
{
// define db connection constants using values submitted
if (!defined('OWA_DB_TYPE')) {
define('OWA_DB_TYPE', $this->getParam('db_type'));
}
if (!defined('OWA_DB_HOST')) {
define('OWA_DB_HOST', $this->getParam('db_host'));
}
if (!defined('OWA_DB_NAME')) {
define('OWA_DB_NAME', $this->getParam('db_name'));
}
if (!defined('OWA_DB_USER')) {
define('OWA_DB_USER', $this->getParam('db_user'));
}
if (!defined('OWA_DB_PASSWORD')) {
define('OWA_DB_PASSWORD', $this->getParam('db_password'));
}
owa_coreAPI::setSetting('base', 'db_type', OWA_DB_TYPE);
owa_coreAPI::setSetting('base', 'db_host', OWA_DB_HOST);
owa_coreAPI::setSetting('base', 'db_name', OWA_DB_NAME);
owa_coreAPI::setSetting('base', 'db_user', OWA_DB_USER);
owa_coreAPI::setSetting('base', 'db_password', OWA_DB_PASSWORD);
// Check DB connection status
$db = owa_coreAPI::dbSingleton();
$db->connect();
if ($db->connection_status != true) {
$this->set('error_msg', $this->getMsg(3012));
$this->set('config', $this->params);
$this->setView('base.install');
$this->setSubview('base.installConfigEntry');
} else {
//create config file
$this->c->createConfigFile($this->params);
$this->setRedirectAction('base.installDefaultsEntry');
}
// Check socket connection
// Check permissions on log directory
return;
}
function renameTable($new_table_name)
{
$db = owa_coreAPI::dbSingleton();
$status = $db->renameTable($this->getTableName(), $new_table_name);
if ($status == true) {
return true;
} else {
return false;
}
return;
}
/**
* Loads internal $this->assignedSites member
*/
private function loadAssignedSites()
{
owa_coreAPI::debug('loading assigned sites');
if (!$this->user->get('id')) {
throw new Exception('no user object loaded!');
}
$site_ids = array();
$db = owa_coreAPI::dbSingleton();
$db->selectFrom('owa_site_user');
$db->selectColumn('*');
$db->where('user_id', $this->user->get('id'));
$site_ids = $db->getAllRows();
// filter array of site_ids.
$dispatch = owa_coreAPI::getEventDispatch();
$site_ids = $dispatch->filter('allowed_sites_list', $site_ids);
$this->setAllowedSitesList($site_ids);
}
function process_event_log($file)
{
// check to see if event log file exisits
if (!file_exists($file)) {
owa_coreAPI::debug("Event file does not exist at {$file}");
return false;
}
// check for access to db
$db = owa_coreAPI::dbSingleton();
$db->connect();
if (!$db->isConnectionEstablished()) {
owa_coreAPI::debug("Aborting processing of event log file. Could not connect to database.");
return false;
}
//create lock file
$this->create_lock_file();
// get event dispatcher
$dispatch = owa_coreAPI::getEventDispatch();
// Create a new log file name
$new_file_name = $this->queue_dir . time() . "." . getmypid();
$new_file = $new_file_name . ".processing";
// Rename current log file
rename($file, $new_file) or die("Could not rename file");
owa_coreAPI::debug('renamed event file.');
// open file for reading
$handle = @fopen($new_file, "r");
if ($handle) {
while (!feof($handle)) {
// Read row
$buffer = fgets($handle, 14096);
// big enough?
// Parse the row
$event = $this->parse_log_row($buffer);
// Log event to the event queue
if (!empty($event)) {
//print_r($event);
// debug
owa_coreAPI::debug(sprintf('Processing: %s (%s)', '', $event->guid));
// send event object to event queue
$ret = $dispatch->notify($event);
// is the dispatch was not successful then add the event back into the queue.
if ($ret != OWA_EHS_EVENT_HANDLED) {
$dispatch->asyncNotify($event);
}
} else {
owa_coreAPI::debug("No event found in log row. Must be end of file.");
}
}
//Close file
fclose($handle);
// rename file to mark it as processed
$processed_file_name = $new_file_name . ".processed";
rename($new_file, $processed_file_name) or die("Could not rename file");
owa_coreAPI::debug(sprintf('Processing Complete. Renaming File to %s', $processed_file_name));
//Delete processed file
unlink($processed_file_name);
owa_coreAPI::debug(sprintf('Deleting File %s', $processed_file_name));
//Delete Lock file
unlink($this->lock_file);
return true;
} else {
//could not open file for processing
owa_coreAPI::error(sprintf('Could not open file %s. Terminating Run.', $new_file));
}
}
/**
* Chooses a siteId from a list of AllowedSites
*
* needed jsut in case a siteId is not passed on the request.
* @return string
*/
protected function getDefaultSiteId()
{
$db = owa_coreAPI::dbSingleton();
$db->select('site_id');
$db->from('owa_site');
$db->limit(1);
$ret = $db->getOneRow();
return $ret['site_id'];
}
/**
* Constructor
*
* @return owa_install
*/
function __construct()
{
parent::__construct();
$this->db = owa_coreAPI::dbSingleton();
}
