PHP owa_coreAPI::dbSingleton方法代码示例

 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');
//.........这里部分代码省略.........

 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();
 }