PHP Connection::executeUpdate方法代码示例

 /**
  * insert the @input values when they do not exist yet
  * @param string $table name
  * @param array $input key->value pair, key has to be sanitized properly
  * @throws \OC\HintException
  * @return int count of inserted rows
  */
 public function insertIfNotExist($table, $input)
 {
     $query = 'INSERT INTO `' . $table . '` (`' . implode('`,`', array_keys($input)) . '`) SELECT ' . str_repeat('?,', count($input) - 1) . '? ' . 'FROM `' . $table . '` WHERE ';
     $inserts = array_values($input);
     foreach ($input as $key => $value) {
         $query .= '`' . $key . '`';
         if (is_null($value)) {
             $query .= ' IS NULL AND ';
         } else {
             $inserts[] = $value;
             $query .= ' = ? AND ';
         }
     }
     $query = substr($query, 0, strlen($query) - 5);
     $query .= ' HAVING COUNT(*) = 0';
     try {
         return $this->conn->executeUpdate($query, $inserts);
     } catch (\Doctrine\DBAL\DBALException $e) {
         $entry = 'DB Error: "' . $e->getMessage() . '"<br />';
         $entry .= 'Offending command was: ' . $query . '<br />';
         \OC_Log::write('core', $entry, \OC_Log::FATAL);
         $l = \OC::$server->getL10N('lib');
         throw new \OC\HintException($l->t('Database Error'), $l->t('Please contact your system administrator.'), 0, $e);
     }
 }

 /**
  * insert the @input values when they do not exist yet
  * @param string $table name
  * @param array $input key->value pairs
  * @return int count of inserted rows
  */
 public function insertIfNotExist($table, $input)
 {
     $query = 'INSERT INTO `' . $table . '` (`' . implode('`,`', array_keys($input)) . '`) SELECT ' . str_repeat('?,', count($input) - 1) . '? ' . 'FROM `' . $table . '` WHERE ';
     $inserts = array_values($input);
     foreach ($input as $key => $value) {
         $query .= '`' . $key . '`';
         if (is_null($value)) {
             $query .= ' IS NULL AND ';
         } else {
             $inserts[] = $value;
             $query .= ' = ? AND ';
         }
     }
     $query = substr($query, 0, strlen($query) - 5);
     $query .= ' HAVING COUNT(*) = 0';
     try {
         return $this->conn->executeUpdate($query, $inserts);
     } catch (\Doctrine\DBAL\DBALException $e) {
         $entry = 'DB Error: "' . $e->getMessage() . '"<br />';
         $entry .= 'Offending command was: ' . $query . '<br />';
         \OC_Log::write('core', $entry, \OC_Log::FATAL);
         error_log('DB error: ' . $entry);
         \OC_Template::printErrorPage($entry);
     }
 }

 /**
  * @param \OC\DB\Connection $connection
  */
 private function createDatabase($connection)
 {
     try {
         $name = $this->dbName;
         $user = $this->dbUser;
         //we cant use OC_BD functions here because we need to connect as the administrative user.
         $query = "CREATE DATABASE IF NOT EXISTS `{$name}` CHARACTER SET utf8 COLLATE utf8_bin;";
         $connection->executeUpdate($query);
         //this query will fail if there aren't the right permissions, ignore the error
         $query = "GRANT ALL PRIVILEGES ON `{$name}` . * TO '{$user}'";
         $connection->executeUpdate($query);
     } catch (\Exception $ex) {
         $this->logger->error('Database creation failed: {error}', ['app' => 'mysql.setup', 'error' => $ex->getMessage()]);
     }
 }

 /**
  * Insert a row if the matching row does not exists.
  *
  * @param string $table The table name (will replace *PREFIX* with the actual prefix)
  * @param array $input data that should be inserted into the table  (column name => value)
  * @param array|null $compare List of values that should be checked for "if not exists"
  *				If this is null or an empty array, all keys of $input will be compared
  *				Please note: text fields (clob) must not be used in the compare array
  * @return int number of inserted rows
  * @throws \Doctrine\DBAL\DBALException
  */
 public function insertIfNotExist($table, $input, array $compare = null)
 {
     if (empty($compare)) {
         $compare = array_keys($input);
     }
     $query = 'INSERT INTO `' . $table . '` (`' . implode('`,`', array_keys($input)) . '`) SELECT ' . str_repeat('?,', count($input) - 1) . '? ' . 'FROM `' . $table . '` WHERE ';
     $inserts = array_values($input);
     foreach ($compare as $key) {
         $query .= '`' . $key . '`';
         if (is_null($input[$key])) {
             $query .= ' IS NULL AND ';
         } else {
             $inserts[] = $input[$key];
             $query .= ' = ? AND ';
         }
     }
     $query = substr($query, 0, strlen($query) - 5);
     $query .= ' HAVING COUNT(*) = 0';
     return $this->conn->executeUpdate($query, $inserts);
 }

 /**
  * sets a value in the preferences
  * @param string $user user
  * @param string $app app
  * @param string $key key
  * @param string $value value
  * @param string $preCondition only set value if the key had a specific value before
  * @return bool true if value was set, otherwise false
  *
  * Adds a value to the preferences. If the key did not exist before, it
  * will be added automagically.
  */
 public function setValue($user, $app, $key, $value, $preCondition = null)
 {
     // Check if the key does exist
     $query = 'SELECT `configvalue` FROM `*PREFIX*preferences`' . ' WHERE `userid` = ? AND `appid` = ? AND `configkey` = ?';
     $oldValue = $this->conn->fetchColumn($query, array($user, $app, $key));
     $exists = $oldValue !== false;
     if ($oldValue === strval($value)) {
         // no changes
         return true;
     }
     $affectedRows = 0;
     if (!$exists && $preCondition === null) {
         $data = array('userid' => $user, 'appid' => $app, 'configkey' => $key, 'configvalue' => $value);
         $affectedRows = $this->conn->insert('*PREFIX*preferences', $data);
     } elseif ($exists) {
         $data = array($value, $user, $app, $key);
         $sql = "UPDATE `*PREFIX*preferences` SET `configvalue` = ?" . " WHERE `userid` = ? AND `appid` = ? AND `configkey` = ?";
         if ($preCondition !== null) {
             if (\OC_Config::getValue('dbtype', 'sqlite') === 'oci') {
                 //oracle hack: need to explicitly cast CLOB to CHAR for comparison
                 $sql .= " AND to_char(`configvalue`) = ?";
             } else {
                 $sql .= " AND `configvalue` = ?";
             }
             $data[] = $preCondition;
         }
         $affectedRows = $this->conn->executeUpdate($sql, $data);
     }
     // only add to the cache if we already loaded data for the user
     if ($affectedRows > 0 && isset($this->cache[$user])) {
         if (!isset($this->cache[$user][$app])) {
             $this->cache[$user][$app] = array();
         }
         $this->cache[$user][$app][$key] = $value;
     }
     return $affectedRows > 0 ? true : false;
 }