PHP Common::prefixTable方法代码示例

 public function getMigrations(Updater $updater)
 {
     $migrations = array('# ATTENTION: This update script will execute some more SQL queries than that below as it is necessary to rebuilt some archives #' => false);
     // update scheduled reports to use new plugin
     $reportsToReplace = array('UserSettings_getBrowserVersion' => 'DevicesDetection_getBrowserVersions', 'UserSettings_getBrowser' => 'DevicesDetection_getBrowsers', 'UserSettings_getOSFamily' => 'DevicesDetection_getOsFamilies', 'UserSettings_getOS' => 'DevicesDetection_getOsVersions', 'UserSettings_getMobileVsDesktop' => 'DevicesDetection_getType', 'UserSettings_getBrowserType' => 'DevicesDetection_getBrowserEngines', 'UserSettings_getWideScreen' => 'UserSettings_getScreenType');
     $reportTable = Common::prefixTable('report');
     foreach ($reportsToReplace as $old => $new) {
         $migrations[] = $this->migration->db->sql("UPDATE {$reportTable} SET reports = REPLACE(reports, '" . $old . "', '" . $new . "')");
     }
     // update dashboard to use new widgets
     $oldWidgets = array(array('module' => 'UserSettings', 'action' => 'getBrowserVersion', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getBrowser', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getOSFamily', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getOS', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getMobileVsDesktop', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getBrowserType', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getWideScreen', 'params' => array()));
     $newWidgets = array(array('module' => 'DevicesDetection', 'action' => 'getBrowserVersions', 'params' => array()), array('module' => 'DevicesDetection', 'action' => 'getBrowsers', 'params' => array()), array('module' => 'DevicesDetection', 'action' => 'getOsFamilies', 'params' => array()), array('module' => 'DevicesDetection', 'action' => 'getOsVersions', 'params' => array()), array('module' => 'DevicesDetection', 'action' => 'getType', 'params' => array()), array('module' => 'DevicesDetection', 'action' => 'getBrowserEngines', 'params' => array()), array('module' => 'UserSettings', 'action' => 'getScreenType', 'params' => array()));
     $allDashboards = Db::get()->fetchAll(sprintf("SELECT * FROM %s", Common::prefixTable('user_dashboard')));
     $dashboardTable = Common::prefixTable('user_dashboard');
     $dashboardQuery = "UPDATE {$dashboardTable} SET layout = ? WHERE iddashboard = ?";
     foreach ($allDashboards as $dashboard) {
         $dashboardLayout = json_decode($dashboard['layout']);
         $dashboardLayout = DashboardModel::replaceDashboardWidgets($dashboardLayout, $oldWidgets, $newWidgets);
         $newLayout = json_encode($dashboardLayout);
         if ($newLayout != $dashboard['layout']) {
             $migrations[] = $this->migration->db->boundSql($dashboardQuery, array($newLayout, $dashboard['iddashboard']));
         }
     }
     return $migrations;
 }

    static function getSql()
    {
        return array('ALTER TABLE `' . Common::prefixTable('log_visit') . '`
				 CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL' => false, 'ALTER TABLE `' . Common::prefixTable('log_conversion') . '`
				 CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL' => false, 'ALTER TABLE `' . Common::prefixTable('log_link_visit_action') . '`
				 CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL,
				 CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL' => false);
    }

 public function getMigrations(Updater $updater)
 {
     return array($this->migration->db->addColumn('goal', 'allow_multiple', 'TINYINT(4) NOT NULL', 'case_sensitive'), $this->migration->db->sql('ALTER TABLE `' . Common::prefixTable('log_conversion') . '`
                 ADD buster int unsigned NOT NULL AFTER revenue,
                 DROP PRIMARY KEY,
                 ADD PRIMARY KEY (idvisit, idgoal, buster)', Updater\Migration\Db::ERROR_CODE_DUPLICATE_COLUMN));
 }

 public function getAllByIdvisit($idvisit, $actionsLimit)
 {
     // Without "quoteIdentifier" postgresql will return column aliases
     // in lower case
     $sql = 'SELECT ' . "  'goal' AS type " . ' , g.name AS ' . $this->db->quoteIdentifier('goalName') . ' ' . ' , g.idgoal AS ' . $this->db->quoteIdentifier('goalId') . ' ' . ' , g.revenue AS revenue ' . ' , lc.idlink_va AS ' . $this->db->quoteIdentifier('goalPageId') . ' ' . ' , lc.server_time AS ' . $this->db->quoteIdentifier('serverTimePretty') . ' ' . ' , lc.url AS url ' . 'FROM ' . $this->table . ' AS lc ' . 'LEFT OUTER JOIN ' . Common::prefixTable('goal') . ' AS g ' . '    ON (g.idsite = lc.idsite AND g.idgoal = lc.idgoal) ' . '   AND g.deleted = 0 ' . 'WHERE lc.idvisit = ? AND lc.idgoal > 0 ' . 'ORDER BY ' . $this->db->quoteIdentifier('serverTimePretty') . ' ASC ' . "LIMIT {$actionsLimit} OFFSET 0";
     return $this->db->fetchAll($sql, array($idvisit));
 }

 public static function update($idvisitor, $name = false, $email = false, $phone = false, $comments = false)
 {
     if ($name == false && $email == false && $phone == false && $comments == false) {
         return false;
     }
     $buildQuery = "";
     $argSet = array();
     $argOnUpdate = array();
     $argSet[] = @Common::hex2bin($idvisitor);
     if ($name) {
         $argSet[] = $name;
         $argOnUpdate[] = $name;
         $buildQuery .= " name = ?,";
     }
     if ($email) {
         $argSet[] = $email;
         $argOnUpdate[] = $email;
         $buildQuery .= " email = ?,";
     }
     if ($phone) {
         $argSet[] = $phone;
         $argOnUpdate[] = $phone;
         $buildQuery .= " phone = ?,";
     }
     if ($comments) {
         $argSet[] = $comments;
         $argOnUpdate[] = $comments;
         $buildQuery .= " comments = ?,";
     }
     $buildQuery = trim(substr_replace($buildQuery, "", -1));
     $arguments = array_merge($argSet, $argOnUpdate);
     Db::query("INSERT INTO " . Common::prefixTable('chat_personnal_informations') . " SET idvisitor = ?, {$buildQuery} ON DUPLICATE KEY UPDATE {$buildQuery}", $arguments);
     return true;
 }

 public static function setUpBeforeClass()
 {
     parent::setUpBeforeClass();
     // note: not sure why I have to manually install plugin
     \Piwik\Plugin\Manager::getInstance()->loadPlugin('CustomAlerts')->install();
     $result = Fixture::updateDatabase();
     if ($result === false) {
         throw new \Exception("Failed to update pre-2.0 database (nothing to update).");
     }
     // truncate log tables so old data won't be re-archived
     foreach (array('log_visit', 'log_link_visit_action', 'log_conversion', 'log_conversion_item') as $table) {
         Db::query("TRUNCATE TABLE " . Common::prefixTable($table));
     }
     // add two visits from same visitor on dec. 29
     $t = Fixture::getTracker(1, '2012-12-29 01:01:30', $defaultInit = true);
     $t->setUrl('http://site.com/index.htm');
     $t->setIp('136.5.3.2');
     Fixture::checkResponse($t->doTrackPageView('incredible title!'));
     $t->setForceVisitDateTime('2012-12-29 03:01:30');
     $t->setUrl('http://site.com/other/index.htm');
     $t->DEBUG_APPEND_URL = '&_idvc=2';
     // make sure visit is marked as returning
     Fixture::checkResponse($t->doTrackPageView('other incredible title!'));
     // launch archiving
     VisitFrequencyApi::getInstance()->get(1, 'year', '2012-12-29');
 }

 static function getSql()
 {
     $logVisit = Common::prefixTable('log_visit');
     $logConversion = Common::prefixTable('log_conversion');
     $addColumns = "DROP `location_continent`,\n\t\t\t\t\t   ADD `location_region` CHAR(2) NULL AFTER `location_country`,\n\t\t\t\t\t   ADD `location_city` VARCHAR(255) NULL AFTER `location_region`,\n\t\t\t\t\t   ADD `location_latitude` FLOAT(10, 6) NULL AFTER `location_city`,\n\t\t\t           ADD `location_longitude` FLOAT(10, 6) NULL AFTER `location_latitude`";
     return array("ALTER TABLE `{$logVisit}` {$addColumns}" => 1091, "ALTER TABLE `{$logConversion}` {$addColumns}" => 1091);
 }

 /**
  * @param $idVisit
  * @param $idOrder
  * @param $actionsLimit
  * @return array
  * @throws \Exception
  */
 public function queryEcommerceItemsForOrder($idVisit, $idOrder, $actionsLimit)
 {
     $sql = "SELECT\n\t\t\t\t\t\t\tlog_action_sku.name as itemSKU,\n\t\t\t\t\t\t\tlog_action_name.name as itemName,\n\t\t\t\t\t\t\tlog_action_category.name as itemCategory,\n\t\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('price') . " as price,\n\t\t\t\t\t\t\tquantity as quantity\n\t\t\t\t\t\tFROM " . Common::prefixTable('log_conversion_item') . "\n\t\t\t\t\t\t\tINNER JOIN " . Common::prefixTable('log_action') . " AS log_action_sku\n\t\t\t\t\t\t\tON  idaction_sku = log_action_sku.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_name\n\t\t\t\t\t\t\tON  idaction_name = log_action_name.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_category\n\t\t\t\t\t\t\tON idaction_category = log_action_category.idaction\n\t\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\t\tAND idorder = ?\n\t\t\t\t\t\t\tAND deleted = 0\n\t\t\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t";
     $bind = array($idVisit, $idOrder);
     $itemsDetails = Db::fetchAll($sql, $bind);
     return $itemsDetails;
 }

 public function getMigrations(Updater $updater)
 {
     // Renaming old archived records now that the plugin is called Referrers
     $migrations = array();
     $tables = \Piwik\DbHelper::getTablesInstalled();
     foreach ($tables as $tableName) {
         if (strpos($tableName, 'archive_') !== false) {
             $migrations[] = $this->migration->db->sql('UPDATE `' . $tableName . '` SET `name`=REPLACE(`name`, \'Referers_\', \'Referrers_\') WHERE `name` LIKE \'Referers_%\'');
         }
     }
     $errorCodeTableNotFound = '1146';
     // Rename custom segments containing Referers segments
     $migrations[] = $this->migration->db->sql('UPDATE `' . Common::prefixTable('segment') . '` SET `definition`=REPLACE(`definition`, \'referer\', \'referrer\') WHERE `definition` LIKE \'%referer%\'', $errorCodeTableNotFound);
     // Rename Referrers reports within scheduled reports
     $query = 'UPDATE `' . Common::prefixTable('report') . '` SET `reports`=REPLACE(`reports`, \'Referer\', \'Referrer\') WHERE `reports` LIKE \'%Referer%\'';
     $migrations[] = $this->migration->db->sql($query, $errorCodeTableNotFound);
     // Rename Referrers widgets in custom dashboards
     $query = 'UPDATE `' . Common::prefixTable('user_dashboard') . '` SET `layout`=REPLACE(`layout`, \'Referer\', \'Referrer\') WHERE `layout` LIKE \'%Referer%\'';
     $migrations[] = $this->migration->db->sql($query, $errorCodeTableNotFound);
     $query = 'UPDATE `' . Common::prefixTable('option') . '` SET `option_name` = \'version_ScheduledReports\' WHERE `option_name` = \'version_PDFReports\' ';
     $migrations[] = $this->migration->db->sql($query, Updater\Migration\Db::ERROR_CODE_DUPLICATE_ENTRY);
     // http://forum.piwik.org/read.php?2,106895
     $migrations[] = $this->migration->plugin->activate('Referrers');
     $migrations[] = $this->migration->plugin->activate('ScheduledReports');
     return $migrations;
 }

    /**
     * Authenticates user
     *
     * @return AuthResult
     */
    public function authenticate()
    {
        $rootLogin = Config::getInstance()->superuser['login'];
        $rootPassword = Config::getInstance()->superuser['password'];
        $rootToken = API::getInstance()->getTokenAuth($rootLogin, $rootPassword);
        if (is_null($this->login)) {
            if ($this->token_auth === $rootToken) {
                return new AuthResult(AuthResult::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $this->token_auth);
            }
            $login = Db::fetchOne('SELECT login
                FROM ' . Common::prefixTable('user') . '
					WHERE token_auth = ?', array($this->token_auth));
            if (!empty($login)) {
                return new AuthResult(AuthResult::SUCCESS, $login, $this->token_auth);
            }
        } else {
            if (!empty($this->login)) {
                if ($this->login === $rootLogin && $this->getHashTokenAuth($rootLogin, $rootToken) === $this->token_auth || $rootToken === $this->token_auth) {
                    $this->setTokenAuth($rootToken);
                    return new AuthResult(AuthResult::SUCCESS_SUPERUSER_AUTH_CODE, $rootLogin, $this->token_auth);
                }
                $login = $this->login;
                $userToken = Db::fetchOne('SELECT token_auth
                FROM ' . Common::prefixTable('user') . '
					WHERE login = ?', array($login));
                if (!empty($userToken) && ($this->getHashTokenAuth($login, $userToken) === $this->token_auth || $userToken === $this->token_auth)) {
                    $this->setTokenAuth($userToken);
                    return new AuthResult(AuthResult::SUCCESS, $login, $userToken);
                }
            }
        }
        return new AuthResult(AuthResult::FAILURE, $this->login, $this->token_auth);
    }

 /**
  * Generate the join sql based on the needed tables
  * @throws Exception if tables can't be joined
  * @return array
  */
 public function generate()
 {
     /** @var LogTable[] $availableLogTables */
     $availableLogTables = array();
     $this->tables->sort(array($this, 'sortTablesForJoin'));
     foreach ($this->tables as $i => $table) {
         if (is_array($table)) {
             // join condition provided
             $alias = isset($table['tableAlias']) ? $table['tableAlias'] : $table['table'];
             $this->joinString .= " LEFT JOIN " . Common::prefixTable($table['table']) . " AS " . $alias . " ON " . $table['joinOn'];
             continue;
         }
         $tableSql = Common::prefixTable($table) . " AS {$table}";
         $logTable = $this->tables->getLogTable($table);
         if ($i == 0) {
             // first table
             $this->joinString .= $tableSql;
         } else {
             $join = $this->findJoinCriteriasForTables($logTable, $availableLogTables);
             if ($join === null) {
                 $availableLogTables[$table] = $logTable;
                 continue;
             }
             // the join sql the default way
             $this->joinString .= " LEFT JOIN {$tableSql} ON " . $join;
         }
         $availableLogTables[$table] = $logTable;
     }
 }

 public function execute()
 {
     $isPiwikInstalling = !Config::getInstance()->existsLocalConfig();
     if ($isPiwikInstalling) {
         // Skip the diagnostic if Piwik is being installed
         return array();
     }
     $label = $this->translator->translate('Installation_DatabaseAbilities');
     $optionTable = Common::prefixTable('option');
     $testOptionNames = array('test_system_check1', 'test_system_check2');
     $loadDataInfile = false;
     $errorMessage = null;
     try {
         $loadDataInfile = Db\BatchInsert::tableInsertBatch($optionTable, array('option_name', 'option_value'), array(array($testOptionNames[0], '1'), array($testOptionNames[1], '2')), $throwException = true);
     } catch (\Exception $ex) {
         $errorMessage = str_replace("\n", "<br/>", $ex->getMessage());
     }
     // delete the temporary rows that were created
     Db::exec("DELETE FROM `{$optionTable}` WHERE option_name IN ('" . implode("','", $testOptionNames) . "')");
     if ($loadDataInfile) {
         return array(DiagnosticResult::singleResult($label, DiagnosticResult::STATUS_OK, 'LOAD DATA INFILE'));
     }
     $comment = sprintf('LOAD DATA INFILE<br/>%s<br/>%s', $this->translator->translate('Installation_LoadDataInfileUnavailableHelp', array('LOAD DATA INFILE', 'FILE')), $this->translator->translate('Installation_LoadDataInfileRecommended'));
     if ($errorMessage) {
         $comment .= sprintf('<br/><strong>%s:</strong> %s<br/>%s', $this->translator->translate('General_Error'), $errorMessage, 'Troubleshooting: <a target="_blank" href="?module=Proxy&action=redirect&url=http://piwik.org/faq/troubleshooting/%23faq_194">FAQ on piwik.org</a>');
     }
     return array(DiagnosticResult::singleResult($label, DiagnosticResult::STATUS_WARNING, $comment));
 }

    public function install()
    {
        $queries[] = 'CREATE TABLE `' . Common::prefixTable('segment') . '` (
					`idsegment` INT(11) NOT NULL AUTO_INCREMENT,
					`name` VARCHAR(255) NOT NULL,
					`definition` TEXT NOT NULL,
					`login` VARCHAR(100) NOT NULL,
					`enable_all_users` tinyint(4) NOT NULL default 0,
					`enable_only_idsite` INTEGER(11) NULL,
					`auto_archive` tinyint(4) NOT NULL default 0,
					`ts_created` TIMESTAMP NULL,
					`ts_last_edit` TIMESTAMP NULL,
					`deleted` tinyint(4) NOT NULL default 0,
					PRIMARY KEY (`idsegment`)
				) DEFAULT CHARSET=utf8';
        try {
            foreach ($queries as $query) {
                Db::exec($query);
            }
        } catch (Exception $e) {
            if (!Db::get()->isErrNo($e, '1050')) {
                throw $e;
            }
        }
    }

 public function getMigrations(Updater $updater)
 {
     $migrations = array($this->migration->db->addColumn('log_visit', 'visit_goal_converted', 'TINYINT( 1 ) NOT NULL', 'visit_total_time'), $this->migration->db->sql('CREATE TABLE `' . Common::prefixTable('goal') . "` (\n                `idsite` int(11) NOT NULL,\n                `idgoal` int(11) NOT NULL,\n                `name` varchar(50) NOT NULL,\n                `match_attribute` varchar(20) NOT NULL,\n                `pattern` varchar(255) NOT NULL,\n                `pattern_type` varchar(10) NOT NULL,\n                `case_sensitive` tinyint(4) NOT NULL,\n                `revenue` float NOT NULL,\n                `deleted` tinyint(4) NOT NULL default '0',\n                PRIMARY KEY  (`idsite`,`idgoal`)\n            )", Updater\Migration\Db::ERROR_CODE_TABLE_EXISTS), $this->migration->db->sql('CREATE TABLE `' . Common::prefixTable('log_conversion') . '` (
             `idvisit` int(10) unsigned NOT NULL,
             `idsite` int(10) unsigned NOT NULL,
             `visitor_idcookie` char(32) NOT NULL,
             `server_time` datetime NOT NULL,
             `visit_server_date` date NOT NULL,
             `idaction` int(11) NOT NULL,
             `idlink_va` int(11) NOT NULL,
             `referer_idvisit` int(10) unsigned default NULL,
             `referer_type` int(10) unsigned default NULL,
             `referer_name` varchar(70) default NULL,
             `referer_keyword` varchar(255) default NULL,
             `visitor_returning` tinyint(1) NOT NULL,
             `location_country` char(3) NOT NULL,
             `location_continent` char(3) NOT NULL,
             `url` text NOT NULL,
             `idgoal` int(10) unsigned NOT NULL,
             `revenue` float default NULL,
             PRIMARY KEY  (`idvisit`,`idgoal`),
             KEY `index_idsite_date` (`idsite`,`visit_server_date`)
         )', Updater\Migration\Db::ERROR_CODE_TABLE_EXISTS));
     $tables = DbHelper::getTablesInstalled();
     foreach ($tables as $tableName) {
         if (preg_match('/archive_/', $tableName) == 1) {
             $columns = array('idsite', 'date1', 'date2', 'name', 'ts_archived');
             $tableNameUnprefixed = Common::unprefixTable($tableName);
             $migrations[] = $this->migration->db->addIndex($tableNameUnprefixed, $columns, 'index_all');
         }
     }
     return $migrations;
 }

 private function assertDuplicatesFixedInLogConversionItemTable()
 {
     $columns = array('idaction_sku', 'idaction_name', 'idaction_category', 'idaction_category2', 'idaction_category3', 'idaction_category4', 'idaction_category5');
     $rows = Db::fetchAll("SELECT " . implode(',', $columns) . " FROM " . Common::prefixTable('log_conversion_item'));
     $expectedRows = array(array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '1', 'idaction_category2' => '4', 'idaction_category3' => '5', 'idaction_category4' => '6', 'idaction_category5' => '5'), array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '5', 'idaction_category2' => '5', 'idaction_category3' => '8', 'idaction_category4' => '4', 'idaction_category5' => '6'));
     $this->assertEquals($expectedRows, $rows);
 }