PHP Db::exec方法代码示例

    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 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 test_UpdateCommand_ReturnsCorrectExitCode_WhenErrorOccurs()
 {
     // create a blob table, then drop it manually so update 2.10.0-b10 will fail
     $tableName = ArchiveTableCreator::getBlobTable(Date::factory('2015-01-01'));
     Db::exec("DROP TABLE {$tableName}");
     $result = $this->applicationTester->run(array('command' => 'core:update', '--yes' => true));
     $this->assertEquals(1, $result, $this->getCommandDisplayOutputErrorMessage());
     $this->assertContains("Piwik could not be updated! See above for more information.", $this->applicationTester->getDisplay());
 }

 /**
  * Create access table record for the user
  * THis will create a record for the user to access at least one site when he logs in first time
  * 
  * @param String    $userLogin  The user login string
  * @param Integer   $idSite     The ID of the site user is given access to
  * @return type Description
  */
 public function createAccess($userLogin, $idSite)
 {
     //TODO: get the list of user access to the sites and update the records accordingly
     //check if the record already exists
     $sql = "SELECT *\r\n                FROM " . Common::prefixTable($this->__PIWIK_ACCESS_TABLE) . " pa\r\n                WHERE pa.login = '" . $userLogin . "' AND pa.idsite = " . $idSite;
     if (!($access = Db::fetchRow($sql))) {
         $sql = "INSERT INTO " . Common::prefixTable($this->__PIWIK_ACCESS_TABLE) . " (login, idsite, access) \r\n                    VALUES('" . $userLogin . "', " . (int) $idSite . ", 'view')";
         Db::exec($sql);
     }
 }

 public function setUp()
 {
     parent::setUp();
     // create two myisam tables
     Db::exec("CREATE TABLE table1 (a INT) ENGINE=MYISAM");
     Db::exec("CREATE TABLE table2 (b INT) ENGINE=MYISAM");
     // create two innodb tables
     Db::exec("CREATE TABLE table3 (c INT) ENGINE=InnoDB");
     Db::exec("CREATE TABLE table4 (d INT) ENGINE=InnoDB");
 }

 /**
  * Incremental version update
  */
 public static function update()
 {
     foreach (self::getSql() as $sql => $errorCode) {
         try {
             Db::exec($sql);
         } catch (\Exception $e) {
             if (!Db::get()->isErrNo($e, '1091') && !Db::get()->isErrNo($e, '1060')) {
                 PiwikUpdater::handleQueryError($e, $sql, false, __FILE__);
             }
         }
     }
 }

 public function install()
 {
     try {
         $sql = "CREATE TABLE IF NOT EXISTS " . Common::prefixTable("snoopy") . " (\n                        id int(11) NOT NULL AUTO_INCREMENT,\n                        idvisitor varchar(45) DEFAULT NULL,\n                        score float DEFAULT NULL,\n                        updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n                        created_at datetime DEFAULT NULL,\n                        PRIMARY KEY (id),\n                        KEY idvisitor_idx (idvisitor),\n\t\t\t  \t\t\tKEY id_idvisitor_idx (id,idvisitor)\n                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ";
         Db::exec($sql);
         $sql = "CREATE TABLE IF NOT EXISTS " . Common::prefixTable("snoopy_visitors") . " (\n                        id int(11) NOT NULL AUTO_INCREMENT,\n                        idvisitor varchar(45) DEFAULT NULL,\n                        custom_1 varchar(255) DEFAULT NULL,\n                        custom_2 varchar(255) DEFAULT NULL,\n                        custom_3 varchar(255) DEFAULT NULL,\n                        custom_4 TEXT DEFAULT NULL,\n                        custom_5 TEXT DEFAULT NULL,\n                        updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n                        created_at datetime DEFAULT NULL,\n                        PRIMARY KEY (id),\n                        UNIQUE KEY (idvisitor)\n                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ";
         Db::exec($sql);
         $sql = "CREATE TABLE IF NOT EXISTS " . Common::prefixTable("snoopy_visitors_statuses") . "(\n\t\t\t\t\t\tid int(11) NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t\tidvisitor varchar(45) DEFAULT NULL,\n\t\t\t\t\t\tstatus varchar(45) DEFAULT NULL,\n\t\t\t\t\t\tPRIMARY KEY (id),\n\t\t\t\t\t\tUNIQUE KEY idvisitor_uniq (idvisitor)\n\t\t\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
         Db::exec($sql);
     } catch (Exception $e) {
         // ignore error if table already exists (1050 code is for 'table already exists')
         if (!Db::get()->isErrNo($e, '1050')) {
             throw $e;
         }
     }
 }

 /**
  * Test that purgeData works when there's no data.
  *
  * @group Integration
  */
 public function testPurgeDataDeleteLogsNoData()
 {
     \Piwik\DbHelper::truncateAllTables();
     foreach (ArchiveTableCreator::getTablesArchivesInstalled() as $table) {
         Db::exec("DROP TABLE {$table}");
     }
     // get purge data prediction
     $prediction = PrivacyManager::getPurgeEstimate();
     // perform checks on prediction
     $expectedPrediction = array();
     $this->assertEquals($expectedPrediction, $prediction);
     // purge data
     $this->_setTimeToRun();
     $this->assertTrue($this->instance->deleteLogData());
     $this->assertTrue($this->instance->deleteReportData());
     // perform checks
     $this->assertEquals(0, $this->_getTableCount('log_visit'));
     $this->assertEquals(0, $this->_getTableCount('log_conversion'));
     $this->assertEquals(0, $this->_getTableCount('log_link_visit_action'));
     $this->assertEquals(0, $this->_getTableCount('log_conversion_item'));
     $archiveTables = self::_getArchiveTableNames();
     $this->assertFalse($this->_tableExists($archiveTables['numeric'][0]));
     // January
     $this->assertFalse($this->_tableExists($archiveTables['numeric'][1]));
     // February
     $this->assertFalse($this->_tableExists($archiveTables['blob'][0]));
     // January
     $this->assertFalse($this->_tableExists($archiveTables['blob'][1]));
     // February
 }

 public function uninstall()
 {
     // add column hostname / hostname ext in the visit table
     $query = "ALTER TABLE `" . Common::prefixTable('log_visit') . "` DROP `location_provider`";
     Db::exec($query);
 }

 private function addDimensionToTable($table, $column, $type)
 {
     Db::exec("ALTER TABLE `" . Common::prefixTable($table) . "` ADD COLUMN {$column} {$type}");
 }

 /**
  * Batch insert into table from CSV (or other delimited) file.
  *
  * @param string $tableName Name of table
  * @param array $fields Field names
  * @param string $filePath Path name of a file.
  * @param array $fileSpec File specifications (delimiter, line terminator, etc)
  *
  * @throws Exception
  * @return bool  True if successful; false otherwise
  */
 public static function createTableFromCSVFile($tableName, $fields, $filePath, $fileSpec)
 {
     // Chroot environment: prefix the path with the absolute chroot path
     $chrootPath = Config::getInstance()->General['absolute_chroot_path'];
     if (!empty($chrootPath)) {
         $filePath = $chrootPath . $filePath;
     }
     // On Windows, MySQL expects forward slashes as directory separators
     if (SettingsServer::isWindows()) {
         $filePath = str_replace('\\', '/', $filePath);
     }
     $query = "\n\t\t\t\t'{$filePath}'\n\t\t\tREPLACE\n\t\t\tINTO TABLE\n\t\t\t\t`" . $tableName . "`";
     if (isset($fileSpec['charset'])) {
         $query .= ' CHARACTER SET ' . $fileSpec['charset'];
     }
     $fieldList = '(' . join(',', $fields) . ')';
     $query .= "\n\t\t\tFIELDS TERMINATED BY\n\t\t\t\t'" . $fileSpec['delim'] . "'\n\t\t\tENCLOSED BY\n\t\t\t\t'" . $fileSpec['quote'] . "'\n\t\t";
     if (isset($fileSpec['escape'])) {
         $query .= " ESCAPED BY '" . $fileSpec['escape'] . "'";
     }
     $query .= "\n\t\t\tLINES TERMINATED BY\n\t\t\t\t'" . $fileSpec['eol'] . "'\n\t\t\t{$fieldList}\n\t\t";
     /*
      * First attempt: assume web server and MySQL server are on the same machine;
      * this requires that the db user have the FILE privilege; however, since this is
      * a global privilege, it may not be granted due to security concerns
      */
     $keywords = array('');
     /*
      * Second attempt: using the LOCAL keyword means the client reads the file and sends it to the server;
      * the LOCAL keyword may trigger a known PHP PDO\MYSQL bug when MySQL not built with --enable-local-infile
      * @see http://bugs.php.net/bug.php?id=54158
      */
     $openBaseDir = ini_get('open_basedir');
     $safeMode = ini_get('safe_mode');
     if (empty($openBaseDir) && empty($safeMode)) {
         // php 5.x - LOAD DATA LOCAL INFILE is disabled if open_basedir restrictions or safe_mode enabled
         $keywords[] = 'LOCAL ';
     }
     $exceptions = array();
     foreach ($keywords as $keyword) {
         $queryStart = 'LOAD DATA ' . $keyword . 'INFILE ';
         $sql = $queryStart . $query;
         try {
             $result = @Db::exec($sql);
             if (empty($result) || $result < 0) {
                 continue;
             }
             return true;
         } catch (Exception $e) {
             //				echo $sql . ' ---- ' .  $e->getMessage();
             $code = $e->getCode();
             $message = $e->getMessage() . ($code ? "[{$code}]" : '');
             if (!Db::get()->isErrNo($e, '1148')) {
                 Log::info("LOAD DATA INFILE failed... Error was: %s", $message);
             }
             $exceptions[] = "\n  Try #" . (count($exceptions) + 1) . ': ' . $queryStart . ": " . $message;
         }
     }
     if (count($exceptions)) {
         throw new Exception(implode(",", $exceptions));
     }
     return false;
 }

 public function addCustomVariable()
 {
     $dbTable = $this->getDbTableName();
     $index = $this->getHighestCustomVarIndex() + 1;
     $maxLen = CustomVariables::getMaxLengthCustomVariables();
     Db::exec(sprintf('ALTER TABLE %s ', $dbTable) . sprintf('ADD COLUMN custom_var_k%d VARCHAR(%d) DEFAULT NULL,', $index, $maxLen) . sprintf('ADD COLUMN custom_var_v%d VARCHAR(%d) DEFAULT NULL;', $index, $maxLen));
     return $index;
 }

 /**
  * Uninstalls the dimension if a {@link $columnName} and {@link columnType} is set. In case you perform any custom
  * actions during {@link install()} - for instance adding an index - you should make sure to undo those actions by
  * overwriting this method. Make sure to call this parent method to make sure the uninstallation of the column
  * will be done.
  * @throws Exception
  * @api
  */
 public function uninstall()
 {
     if (empty($this->columnName) || empty($this->columnType)) {
         return;
     }
     try {
         $sql = "ALTER TABLE `" . Common::prefixTable($this->tableName) . "` DROP COLUMN `{$this->columnName}`";
         Db::exec($sql);
     } catch (Exception $e) {
         if (!Db::get()->isErrNo($e, '1091')) {
             throw $e;
         }
     }
 }

 /**
  * Truncates all tables then inserts the data in $tables into each
  * mapped table.
  *
  * @param array $tables Array mapping table names with arrays of row data.
  */
 protected static function restoreDbTables($tables)
 {
     $db = Db::fetchOne("SELECT DATABASE()");
     if (empty($db)) {
         Db::exec("USE " . Config::getInstance()->database_tests['dbname']);
     }
     DbHelper::truncateAllTables();
     // insert data
     $existingTables = DbHelper::getTablesInstalled();
     foreach ($tables as $table => $rows) {
         // create table if it's an archive table
         if (strpos($table, 'archive_') !== false && !in_array($table, $existingTables)) {
             $tableType = strpos($table, 'archive_numeric') !== false ? 'archive_numeric' : 'archive_blob';
             $createSql = DbHelper::getTableCreateSql($tableType);
             $createSql = str_replace(Common::prefixTable($tableType), $table, $createSql);
             Db::query($createSql);
         }
         if (empty($rows)) {
             continue;
         }
         $rowsSql = array();
         $bind = array();
         foreach ($rows as $row) {
             $values = array();
             foreach ($row as $value) {
                 if (is_null($value)) {
                     $values[] = 'NULL';
                 } else {
                     if (is_numeric($value)) {
                         $values[] = $value;
                     } else {
                         if (!ctype_print($value)) {
                             $values[] = "x'" . bin2hex($value) . "'";
                         } else {
                             $values[] = "?";
                             $bind[] = $value;
                         }
                     }
                 }
             }
             $rowsSql[] = "(" . implode(',', $values) . ")";
         }
         $sql = "INSERT INTO `{$table}` VALUES " . implode(',', $rowsSql);
         Db::query($sql, $bind);
     }
 }

 private static function checkLoadDataInfile(&$result)
 {
     // check if LOAD DATA INFILE works
     $optionTable = Common::prefixTable('option');
     $testOptionNames = array('test_system_check1', 'test_system_check2');
     $result['load_data_infile_available'] = false;
     try {
         $result['load_data_infile_available'] = \Piwik\Db\BatchInsert::tableInsertBatch($optionTable, array('option_name', 'option_value'), array(array($testOptionNames[0], '1'), array($testOptionNames[1], '2')), $throwException = true);
     } catch (\Exception $ex) {
         $result['load_data_infile_error'] = str_replace("\n", "<br/>", $ex->getMessage());
     }
     // delete the temporary rows that were created
     Db::exec("DELETE FROM `{$optionTable}` WHERE option_name IN ('" . implode("','", $testOptionNames) . "')");
 }