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