PHP Piwik\Db类代码示例

 /**
  * Connects to the DB
  *
  * @throws Exception if there was an error connecting the DB
  */
 public function connect()
 {
     if (self::$profiling) {
         $timer = $this->initProfiler();
     }
     $config = PiwikDb::getDatabaseConfig();
     $this->connection = @new PDO($this->dsn, $this->username, $this->password, $config['driver_options']);
     $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     // we may want to setAttribute(PDO::ATTR_TIMEOUT ) to a few seconds (default is 60) in case the DB is locked
     // the piwik.php would stay waiting for the database... bad!
     // we delete the password from this object "just in case" it could be printed
     $this->password = '';
     /*
      * Lazy initialization via MYSQL_ATTR_INIT_COMMAND depends
      * on mysqlnd support, PHP version, and OS.
      * see ZF-7428 and http://bugs.php.net/bug.php?id=47224
      */
     if (!empty($this->charset)) {
         $sql = "SET NAMES '" . $this->charset . "'";
         $this->connection->exec($sql);
     }
     if (self::$profiling && isset($timer)) {
         $this->recordQueryProfile('connect', $timer);
     }
 }

 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 static function setUpBeforeClass()
 {
     $dbName = false;
     if (!empty($GLOBALS['PIWIK_BENCHMARK_DATABASE'])) {
         $dbName = $GLOBALS['PIWIK_BENCHMARK_DATABASE'];
     }
     // connect to database
     self::createTestConfig();
     self::connectWithoutDatabase();
     // create specified fixture (global var not set, use default no-data fixture (see end of this file))
     if (empty($GLOBALS['PIWIK_BENCHMARK_FIXTURE'])) {
         $fixtureName = 'Piwik_Test_Fixture_EmptyOneSite';
     } else {
         $fixtureName = 'Piwik_Test_Fixture_' . $GLOBALS['PIWIK_BENCHMARK_FIXTURE'];
     }
     self::$fixture = new $fixtureName();
     // figure out if the desired fixture has already been setup, and if not empty the database
     $installedFixture = false;
     try {
         if (isset(self::$fixture->tablesPrefix)) {
             Config::getInstance()->database['tables_prefix'] = self::$fixture->tablesPrefix;
         }
         Db::query("USE " . $dbName);
         $installedFixture = \Piwik\Option::get('benchmark_fixture_name');
     } catch (Exception $ex) {
         // ignore
     }
     $createEmptyDatabase = $fixtureName != $installedFixture;
     parent::_setUpBeforeClass($dbName, $createEmptyDatabase);
     // if we created an empty database, setup the fixture
     if ($createEmptyDatabase) {
         self::$fixture->setUp();
         \Piwik\Option::set('benchmark_fixture_name', $fixtureName);
     }
 }

 private static function migrateConfigSuperUserToDb()
 {
     $config = Config::getInstance();
     if (!$config->existsLocalConfig()) {
         return;
     }
     try {
         $superUser = $config->superuser;
     } catch (\Exception $e) {
         $superUser = null;
     }
     if (!empty($superUser['bridge']) || empty($superUser) || empty($superUser['login'])) {
         // there is a super user which is not from the config but from the bridge, that means we already have
         // a super user in the database
         return;
     }
     $userApi = UsersManagerApi::getInstance();
     try {
         Db::get()->insert(Common::prefixTable('user'), array('login' => $superUser['login'], 'password' => $superUser['password'], 'alias' => $superUser['login'], 'email' => $superUser['email'], 'token_auth' => $userApi->getTokenAuth($superUser['login'], $superUser['password']), 'date_registered' => Date::now()->getDatetime(), 'superuser_access' => 1));
     } catch (\Exception $e) {
         echo "There was an issue, but we proceed: " . $e->getMessage();
     }
     if (array_key_exists('salt', $superUser)) {
         $salt = $superUser['salt'];
     } else {
         $salt = Common::generateUniqId();
     }
     $config->General['salt'] = $salt;
     $config->superuser = array();
     $config->forceSave();
 }

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

 private function createStorage($idSite = null)
 {
     if (!isset($idSite)) {
         $idSite = $this->idSite;
     }
     return new Storage(Db::get(), $idSite);
 }

 public function test_constructor_shouldNotEstablishADatabaseConnection()
 {
     Db::destroyDatabaseObject();
     $this->assertNotDbConnectionCreated();
     $this->createSettingsInstance();
     $this->assertNotDbConnectionCreated();
 }

 /**
  * Analyzes numeric & blob tables for a single table date (ie, `'2015_01'`) and returns
  * statistics including:
  *
  * - number of archives present
  * - number of invalidated archives
  * - number of temporary archives
  * - number of error archives
  * - number of segment archives
  * - number of numeric rows
  * - number of blob rows
  *
  * @param string $tableDate ie `'2015_01'`
  * @return array
  */
 public function getArchiveTableAnalysis($tableDate)
 {
     $numericQueryEmptyRow = array('count_archives' => '-', 'count_invalidated_archives' => '-', 'count_temporary_archives' => '-', 'count_error_archives' => '-', 'count_segment_archives' => '-', 'count_numeric_rows' => '-');
     $tableDate = str_replace("`", "", $tableDate);
     // for sanity
     $numericTable = Common::prefixTable("archive_numeric_{$tableDate}");
     $blobTable = Common::prefixTable("archive_blob_{$tableDate}");
     // query numeric table
     $sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n                       SUM(CASE WHEN name LIKE 'done%' THEN 1 ELSE 0 END) AS count_archives,\n                       SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_invalidated_archives,\n                       SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_temporary_archives,\n                       SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_error_archives,\n                       SUM(CASE WHEN name LIKE 'done%' AND CHAR_LENGTH(name) > 32 THEN 1 ELSE 0 END) AS count_segment_archives,\n                       SUM(CASE WHEN name NOT LIKE 'done%' THEN 1 ELSE 0 END) AS count_numeric_rows,\n                       0 AS count_blob_rows\n                  FROM `{$numericTable}`\n              GROUP BY idsite, date1, date2, period";
     $rows = Db::fetchAll($sql, array(ArchiveWriter::DONE_INVALIDATED, ArchiveWriter::DONE_OK_TEMPORARY, ArchiveWriter::DONE_ERROR));
     // index result
     $result = array();
     foreach ($rows as $row) {
         $result[$row['label']] = $row;
     }
     // query blob table & manually merge results (no FULL OUTER JOIN in mysql)
     $sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n                       COUNT(*) AS count_blob_rows\n                  FROM `{$blobTable}`\n              GROUP BY idsite, date1, date1, period";
     foreach (Db::fetchAll($sql) as $blobStatsRow) {
         $label = $blobStatsRow['label'];
         if (isset($result[$label])) {
             $result[$label] = array_merge($result[$label], $blobStatsRow);
         } else {
             $result[$label] = $blobStatsRow + $numericQueryEmptyRow;
         }
     }
     return $result;
 }

 public static function getUsersBySite($idsite)
 {
     $getRegularUsers = Db::fetchAll("SELECT login,\n        (SELECT email FROM " . Common::prefixTable('user') . " WHERE login = acc.login) AS email\n        FROM " . Common::prefixTable('access') . " AS acc WHERE idsite = ?", array($idsite));
     $getSuperUsers = Db::fetchAll("SELECT login,email FROM " . Common::prefixTable('user') . " WHERE superuser_access = 1");
     $getUsers = array_merge($getRegularUsers, $getSuperUsers);
     return $getUsers;
 }

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

 /**
  * @param $name
  * @param $type
  * @return string
  */
 private static function getIdActionMatchingNameAndType($name, $type)
 {
     $sql = TableLogAction::getSqlSelectActionId();
     $bind = array($name, $name, $type);
     $idAction = \Piwik\Db::fetchOne($sql, $bind);
     return $idAction;
 }

    /**
     * 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);
    }

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

 public static function disconnectCachedDbConnection()
 {
     // code redundancy w/ above is on purpose; above disconnectDatabase depends on method that can potentially be overridden
     if (!is_null(self::$db)) {
         self::$db->disconnect();
         self::$db = null;
     }
 }