PHP Piwik\DbHelper类代码示例

 /**
  * Performs a batch insert into a specific table using either LOAD DATA INFILE or plain INSERTs,
  * as a fallback. On MySQL, LOAD DATA INFILE is 20x faster than a series of plain INSERTs.
  *
  * @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name
  * @param array $fields array of unquoted field names
  * @param array $values array of data to be inserted
  * @param bool $throwException Whether to throw an exception that was caught while trying
  *                                LOAD DATA INFILE, or not.
  * @throws Exception
  * @return bool  True if the bulk LOAD was used, false if we fallback to plain INSERTs
  */
 public static function tableInsertBatch($tableName, $fields, $values, $throwException = false)
 {
     $filePath = PIWIK_USER_PATH . '/tmp/assets/' . $tableName . '-' . Common::generateUniqId() . '.csv';
     $filePath = SettingsPiwik::rewriteTmpPathWithInstanceId($filePath);
     $loadDataInfileEnabled = Config::getInstance()->General['enable_load_data_infile'];
     if ($loadDataInfileEnabled && Db::get()->hasBulkLoader()) {
         try {
             $fileSpec = array('delim' => "\t", 'quote' => '"', 'escape' => '\\\\', 'escapespecial_cb' => function ($str) {
                 return str_replace(array(chr(92), chr(34)), array(chr(92) . chr(92), chr(92) . chr(34)), $str);
             }, 'eol' => "\r\n", 'null' => 'NULL');
             // hack for charset mismatch
             if (!DbHelper::isDatabaseConnectionUTF8() && !isset(Config::getInstance()->database['charset'])) {
                 $fileSpec['charset'] = 'latin1';
             }
             self::createCSVFile($filePath, $fileSpec, $values);
             if (!is_readable($filePath)) {
                 throw new Exception("File {$filePath} could not be read.");
             }
             $rc = self::createTableFromCSVFile($tableName, $fields, $filePath, $fileSpec);
             if ($rc) {
                 unlink($filePath);
                 return true;
             }
         } catch (Exception $e) {
             Log::info("LOAD DATA INFILE failed or not supported, falling back to normal INSERTs... Error was: %s", $e->getMessage());
             if ($throwException) {
                 throw $e;
             }
         }
     }
     // if all else fails, fallback to a series of INSERTs
     @unlink($filePath);
     self::tableInsertBatchIterate($tableName, $fields, $values);
     return false;
 }

 protected function makeUpdate(InputInterface $input, OutputInterface $output, $doDryRun)
 {
     $this->checkAllRequiredOptionsAreNotEmpty($input);
     $updater = $this->makeUpdaterInstance($output);
     $componentsWithUpdateFile = $updater->getComponentUpdates();
     if (empty($componentsWithUpdateFile)) {
         throw new NoUpdatesFoundException("Everything is already up to date.");
     }
     $output->writeln(array("", "    *** " . Piwik::translate('CoreUpdater_UpdateTitle') . " ***"));
     // handle case of existing database with no tables
     if (!DbHelper::isInstalled()) {
         $this->handleCoreError($output, Piwik::translate('CoreUpdater_EmptyDatabaseError', Config::getInstance()->database['dbname']));
         return;
     }
     $output->writeln(array("", "    " . Piwik::translate('CoreUpdater_DatabaseUpgradeRequired'), "", "    " . Piwik::translate('CoreUpdater_YourDatabaseIsOutOfDate')));
     if ($this->isUpdatingCore($componentsWithUpdateFile)) {
         $currentVersion = $this->getCurrentVersionForCore($updater);
         $output->writeln(array("", "    " . Piwik::translate('CoreUpdater_PiwikWillBeUpgradedFromVersionXToVersionY', array($currentVersion, Version::VERSION))));
     }
     $pluginsToUpdate = $this->getPluginsToUpdate($componentsWithUpdateFile);
     if (!empty($pluginsToUpdate)) {
         $output->writeln(array("", "    " . Piwik::translate('CoreUpdater_TheFollowingPluginsWillBeUpgradedX', implode(', ', $pluginsToUpdate))));
     }
     $dimensionsToUpdate = $this->getDimensionsToUpdate($componentsWithUpdateFile);
     if (!empty($dimensionsToUpdate)) {
         $output->writeln(array("", "    " . Piwik::translate('CoreUpdater_TheFollowingDimensionsWillBeUpgradedX', implode(', ', $dimensionsToUpdate))));
     }
     $output->writeln("");
     if ($doDryRun) {
         $this->doDryRun($updater, $output);
     } else {
         $this->doRealUpdate($updater, $componentsWithUpdateFile, $output);
     }
 }

 /**
  * Performs a batch insert into a specific table using either LOAD DATA INFILE or plain INSERTs,
  * as a fallback. On MySQL, LOAD DATA INFILE is 20x faster than a series of plain INSERTs.
  *
  * @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name
  * @param array $fields array of unquoted field names
  * @param array $values array of data to be inserted
  * @param bool $throwException Whether to throw an exception that was caught while trying
  *                                LOAD DATA INFILE, or not.
  * @throws Exception
  * @return bool  True if the bulk LOAD was used, false if we fallback to plain INSERTs
  */
 public static function tableInsertBatch($tableName, $fields, $values, $throwException = false)
 {
     $filePath = StaticContainer::get('path.tmp') . '/assets/' . $tableName . '-' . Common::generateUniqId() . '.csv';
     $loadDataInfileEnabled = Config::getInstance()->General['enable_load_data_infile'];
     if ($loadDataInfileEnabled && Db::get()->hasBulkLoader()) {
         try {
             $fileSpec = array('delim' => "\t", 'quote' => '"', 'escape' => '\\\\', 'escapespecial_cb' => function ($str) {
                 return str_replace(array(chr(92), chr(34)), array(chr(92) . chr(92), chr(92) . chr(34)), $str);
             }, 'eol' => "\r\n", 'null' => 'NULL');
             // hack for charset mismatch
             if (!DbHelper::isDatabaseConnectionUTF8() && !isset(Config::getInstance()->database['charset'])) {
                 $fileSpec['charset'] = 'latin1';
             }
             self::createCSVFile($filePath, $fileSpec, $values);
             if (!is_readable($filePath)) {
                 throw new Exception("File {$filePath} could not be read.");
             }
             $rc = self::createTableFromCSVFile($tableName, $fields, $filePath, $fileSpec);
             if ($rc) {
                 unlink($filePath);
                 return true;
             }
         } catch (Exception $e) {
             if ($throwException) {
                 throw $e;
             }
         }
     }
     // if all else fails, fallback to a series of INSERTs
     @unlink($filePath);
     self::tableInsertBatchIterate($tableName, $fields, $values);
     return false;
 }

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

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

    public function getMigrationQueries(Updater $updater)
    {
        $sqlarray = array('ALTER TABLE `' . Common::prefixTable('log_visit') . '`
				ADD `visit_goal_converted` VARCHAR( 1 ) NOT NULL AFTER `visit_total_time`' => 1060, 'ALTER TABLE `' . Common::prefixTable('log_visit') . '`
				CHANGE `visit_goal_converted` `visit_goal_converted` TINYINT(1) NOT NULL' => 1060, 'CREATE TABLE `' . Common::prefixTable('goal') . "` (\n\t\t\t\t`idsite` int(11) NOT NULL,\n\t\t\t\t`idgoal` int(11) NOT NULL,\n\t\t\t\t`name` varchar(50) NOT NULL,\n\t\t\t\t`match_attribute` varchar(20) NOT NULL,\n\t\t\t\t`pattern` varchar(255) NOT NULL,\n\t\t\t\t`pattern_type` varchar(10) NOT NULL,\n\t\t\t\t`case_sensitive` tinyint(4) NOT NULL,\n\t\t\t\t`revenue` float NOT NULL,\n\t\t\t\t`deleted` tinyint(4) NOT NULL default '0',\n\t\t\t\tPRIMARY KEY  (`idsite`,`idgoal`)\n\t\t\t)" => 1050, '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`)
			)' => 1050);
        $tables = DbHelper::getTablesInstalled();
        foreach ($tables as $tableName) {
            if (preg_match('/archive_/', $tableName) == 1) {
                $sqlarray['CREATE INDEX index_all ON ' . $tableName . ' (`idsite`,`date1`,`date2`,`name`,`ts_archived`)'] = 1072;
            }
        }
        return $sqlarray;
    }

 /**
  * Get the SQL to create a specific Piwik table
  *
  * @param string $tableName
  * @throws Exception
  * @return string  SQL
  */
 public function getTableCreateSql($tableName)
 {
     $tables = DbHelper::getTablesCreateSql();
     if (!isset($tables[$tableName])) {
         throw new Exception("The table '{$tableName}' SQL creation code couldn't be found.");
     }
     return $tables[$tableName];
 }

 /**
  * @return string[]
  */
 private function getDirectories()
 {
     $directoriesToCheck = array($this->tmpPath, $this->tmpPath . '/assets/', $this->tmpPath . '/cache/', $this->tmpPath . '/climulti/', $this->tmpPath . '/latest/', $this->tmpPath . '/logs/', $this->tmpPath . '/sessions/', $this->tmpPath . '/tcpdf/', $this->tmpPath . '/templates_c/');
     if (!DbHelper::isInstalled()) {
         // at install, need /config to be writable (so we can create config.ini.php)
         $directoriesToCheck[] = '/config/';
     }
     return $directoriesToCheck;
 }

 public static function install()
 {
     $tableAlert = "`idalert` INT NOT NULL PRIMARY KEY ,\n                       `name` VARCHAR(100) NOT NULL ,\n                       `login` VARCHAR(100) NOT NULL ,\n                       `period` VARCHAR(5) NOT NULL ,\n                       `report` VARCHAR(150) NOT NULL ,\n                       `report_condition` VARCHAR(50) ,\n                       `report_matched` VARCHAR(255) ,\n                       `metric` VARCHAR(150) NOT NULL ,\n                       `metric_condition` VARCHAR(50) NOT NULL ,\n                       `metric_matched` FLOAT NOT NULL ,\n                       `compared_to` SMALLINT (4) UNSIGNED NOT NULL DEFAULT 1 ,\n                       `email_me` BOOLEAN NOT NULL DEFAULT '0',\n                       `additional_emails` TEXT ,\n                       `phone_numbers` TEXT ";
     DbHelper::createTable('alert', $tableAlert);
     $tableAlertSite = "`idalert` INT( 11 ) NOT NULL ,\n                           `idsite` INT( 11 ) NOT NULL ,\n                           PRIMARY KEY ( idalert, idsite )";
     DbHelper::createTable('alert_site', $tableAlertSite);
     $tableAlertLog = "`idtriggered` BIGINT unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t              `idalert` INT( 11 ) NOT NULL ,\n\t\t\t              `idsite` INT( 11 ) NOT NULL ,\n\t\t\t              `ts_triggered` timestamp NOT NULL default CURRENT_TIMESTAMP,\n\t\t\t              `ts_last_sent` timestamp NULL DEFAULT NULL,\n\t\t\t              `value_old` DECIMAL (20,3) DEFAULT NULL,\n\t\t\t              `value_new` DECIMAL (20,3) DEFAULT NULL,\n                          `name` VARCHAR(100) NOT NULL ,\n\t\t\t              `login` VARCHAR(100) NOT NULL ,\n\t\t\t              `period` VARCHAR(5) NOT NULL ,\n\t\t\t              `report` VARCHAR(150) NOT NULL ,\n\t\t\t              `report_condition` VARCHAR(50) ,\n\t\t\t              `report_matched` VARCHAR(1000) ,\n\t\t\t              `metric` VARCHAR(150) NOT NULL ,\n\t\t\t              `metric_condition` VARCHAR(50) NOT NULL ,\n\t\t\t              `metric_matched` FLOAT NOT NULL ,\n\t\t\t              `compared_to` SMALLINT NOT NULL DEFAULT 1 ,\n\t\t\t              `email_me` BOOLEAN NOT NULL  DEFAULT '0',\n\t\t\t              `additional_emails` TEXT ,\n\t\t\t              `phone_numbers` TEXT ,\n\t\t\t              PRIMARY KEY (idtriggered)";
     DbHelper::createTable('alert_triggered', $tableAlertLog);
 }

 /**
  * anonymous = in the session
  * authenticated user = in the session
  */
 public function saveLanguage()
 {
     $language = Common::getRequestVar('language');
     // Prevent CSRF only when piwik is not installed yet (During install user can change language)
     if (DbHelper::isInstalled()) {
         $this->checkTokenInUrl();
     }
     LanguagesManager::setLanguageForSession($language);
     Url::redirectToReferrer();
 }

 public static function setUpBeforeClass()
 {
     parent::setUpBeforeClass();
     DbHelper::createAnonymousUser();
     // the api_internal_call.php uses idSite=7, so we create 7 sites
     for ($i = 0; $i != 7; ++$i) {
         Fixture::createWebsite("2011-01-01 00:00:00", $ecommerce = 1, $siteName = "Site #{$i}");
     }
     // the script uses anonymous token auth, so give the anonymous user access
     \Piwik\Plugins\UsersManager\API::getInstance()->setUserAccess('anonymous', 'view', array(7));
 }

 /**
  * Installs the plugin. Derived classes should implement this class if the plugin
  * needs to:
  *
  * - create tables
  * - update existing tables
  * - etc.
  *
  * @throws \Exception if installation of fails for some reason.
  */
 public function install()
 {
     try {
         DbHelper::createTable('bannerstats', "\n            `label` varchar(100) not null,\n            `content_name_id` int not null,\n            `impression`    int not null,\n            `interaction`   int not null,\n            `referrer`      varchar(200),\n            `target`        varchar(200),\n            `date`          date,\n            `custom_var_v1`\tvarchar(200),\n            `custom_var_v2`\tvarchar(200),\n            `custom_var_v3`\tvarchar(200),\n            `custom_var_v4`\tvarchar(200),\n            `custom_var_v5`\tvarchar(200),\n\n            UNIQUE KEY `unique_combination` (`date`, `label`, `content_name_id`, `referrer`, `target`)\n       ");
     } catch (Exception $e) {
         // ignore error if table already exists (1050 code is for 'table already exists')
         if (!Db::get()->isErrNo($e, '1050')) {
             throw $e;
         }
     }
 }

 private function ensureTargetTableExists($archiveTable)
 {
     $data = $this->targetDb->getAdapter()->fetchCol("SHOW TABLES LIKE '" . $this->targetDb->prefixTable($archiveTable) . "'");
     if (count($data) == 0) {
         $tableType = strpos($archiveTable, 'blob') ? 'archive_blob' : 'archive_numeric';
         $sql = PiwikDbHelper::getTableCreateSql($tableType);
         $sql = str_replace($tableType, $archiveTable, $sql);
         $sql = str_replace($this->sourceDb->prefixTable($tableType), $this->targetDb->prefixTable($tableType), $sql);
         $this->targetDb->getAdapter()->query($sql);
     }
 }

 public function setUp()
 {
     parent::setUp();
     Option::set('version_core', self::VERSION_TO_UPDATE_FROM);
     $this->oldScriptName = $_SERVER['SCRIPT_NAME'];
     $_SERVER['SCRIPT_NAME'] = $_SERVER['SCRIPT_NAME'] . " console";
     // update won't execute w/o this, see Common::isRunningConsoleCommand()
     ArchiveTableCreator::clear();
     DbHelper::getTablesInstalled($forceReload = true);
     // force reload so internal cache in Mysql.php is refreshed
     Updates_2_10_0_b5::$archiveBlobTables = null;
 }

 /**
  * @expectedException \Zend_Db_Statement_Exception
  * @expectedExceptionMessage custom_dimensions
  */
 public function test_shouldBeAbleToUninstallConfigTable()
 {
     $this->config->uninstall();
     try {
         DbHelper::getTableColumns($this->tableName);
         // doesn't work anymore as table was removed
     } catch (Zend_Db_Statement_Exception $e) {
         $this->config->install();
         throw $e;
     }
     $this->config->install();
 }