From 19b9c896340c572d9a42d033cd8824f33d18efa3 Mon Sep 17 00:00:00 2001 From: Bart Visscher Date: Tue, 26 Feb 2013 08:30:42 +0100 Subject: [PATCH] Move insertIfNotExist to Connection wrapper Real implementation is in DB\Adapter* classes --- lib/db.php | 53 +--------------------------------------- lib/db/adapter.php | 28 +++++++++++++++++++++ lib/db/adaptersqlite.php | 41 +++++++++++++++++++++++++++++++ lib/db/connection.php | 12 ++++++++- 4 files changed, 81 insertions(+), 53 deletions(-) diff --git a/lib/db.php b/lib/db.php index 0ad8a9b35d8..1f62326eea3 100644 --- a/lib/db.php +++ b/lib/db.php @@ -389,58 +389,7 @@ class OC_DB { */ public static function insertIfNotExist($table, $input) { self::connect(); - $table = self::$connection->replaceTablePrefix( $table ); - - if(is_null(self::$type)) { - self::$type=OC_Config::getValue( "dbtype", "sqlite" ); - } - $type = self::$type; - - $query = ''; - $inserts = array_values($input); - // differences in escaping of table names ('`' for mysql) and getting the current timestamp - if( $type == 'sqlite' || $type == 'sqlite3' ) { - // NOTE: For SQLite we have to use this clumsy approach - // otherwise all fieldnames used must have a unique key. - $query = 'SELECT * FROM `' . $table . '` WHERE '; - foreach($input as $key => $value) { - $query .= '`' . $key . '` = ? AND '; - } - $query = substr($query, 0, strlen($query) - 5); - try { - $result = self::executeAudited($query, $inserts); - } catch(DatabaseException $e) { - OC_Template::printExceptionErrorPage( $e ); - } - - if((int)$result->numRows() === 0) { - $query = 'INSERT INTO `' . $table . '` (`' - . implode('`,`', array_keys($input)) . '`) VALUES(' - . str_repeat('?,', count($input)-1).'? ' . ')'; - } else { - return 0; //no rows updated - } - } elseif( $type == 'pgsql' || $type == 'oci' || $type == 'mysql' || $type == 'mssql') { - $query = 'INSERT INTO `' .$table . '` (`' - . implode('`,`', array_keys($input)) . '`) SELECT ' - . str_repeat('?,', count($input)-1).'? ' // Is there a prettier alternative? - . 'FROM `' . $table . '` WHERE '; - - foreach($input as $key => $value) { - $query .= '`' . $key . '` = ? AND '; - } - $query = substr($query, 0, strlen($query) - 5); - $query .= ' HAVING COUNT(*) = 0'; - $inserts = array_merge($inserts, $inserts); - } - - try { - $result = self::executeAudited($query, $inserts); - } catch(\Doctrine\DBAL\DBALException $e) { - OC_Template::printExceptionErrorPage( $e ); - } - - return $result; + return self::$connection->insertIfNotExist($table, $input); } /** diff --git a/lib/db/adapter.php b/lib/db/adapter.php index 3b83853289a..3b950331191 100644 --- a/lib/db/adapter.php +++ b/lib/db/adapter.php @@ -22,4 +22,32 @@ class Adapter { public function fixupStatement($statement) { return $statement; } + + public function insertIfNotExist($table, $input) { + $query = 'INSERT INTO `' .$table . '` (`' + . implode('`,`', array_keys($input)) . '`) SELECT ' + . str_repeat('?,', count($input)-1).'? ' // Is there a prettier alternative? + . 'FROM `' . $table . '` WHERE '; + + foreach($input as $key => $value) { + $query .= '`' . $key . '` = ? AND '; + } + $query = substr($query, 0, strlen($query) - 5); + $query .= ' HAVING COUNT(*) = 0'; + $inserts = array_values($input); + $inserts = array_merge($inserts, $inserts); + + try { + $statement = $this->conn->prepare($query); + $result = $statement->execute($inserts); + } catch(\Doctrine\DBAL\DBALException $e) { + $entry = 'DB Error: "'.$e->getMessage() . '"
'; + $entry .= 'Offending command was: ' . $query.'
'; + OC_Log::write('core', $entry, OC_Log::FATAL); + error_log('DB error: ' . $entry); + OC_Template::printErrorPage( $entry ); + } + + return $result; + } } diff --git a/lib/db/adaptersqlite.php b/lib/db/adaptersqlite.php index f0057ab489f..252fd94b0c0 100644 --- a/lib/db/adaptersqlite.php +++ b/lib/db/adaptersqlite.php @@ -16,4 +16,45 @@ class AdapterSqlite extends Adapter { $statement = str_ireplace( 'UNIX_TIMESTAMP()', 'strftime(\'%s\',\'now\')', $statement ); return $statement; } + + public function insertIfNotExist($table, $input) { + // NOTE: For SQLite we have to use this clumsy approach + // otherwise all fieldnames used must have a unique key. + $query = 'SELECT COUNT(*) FROM `' . $table . '` WHERE '; + foreach($input as $key => $value) { + $query .= '`' . $key . '` = ? AND '; + } + $query = substr($query, 0, strlen($query) - 5); + try { + $stmt = $this->conn->prepare($query); + $result = $stmt->execute(array($input)); + } catch(\Doctrine\DBAL\DBALException $e) { + $entry = 'DB Error: "'.$e->getMessage() . '"
'; + $entry .= 'Offending command was: ' . $query . '
'; + OC_Log::write('core', $entry, OC_Log::FATAL); + error_log('DB error: '.$entry); + OC_Template::printErrorPage( $entry ); + } + + if ($stmt->fetchColumn() === 0) { + $query = 'INSERT INTO `' . $table . '` (`' + . implode('`,`', array_keys($input)) . '`) VALUES(' + . str_repeat('?,', count($input)-1).'? ' . ')'; + } else { + return 0; //no rows updated + } + + try { + $statement = $this->conn->prepare($query); + $result = $statement->execute(array_values($input)); + } catch(\Doctrine\DBAL\DBALException $e) { + $entry = 'DB Error: "'.$e->getMessage() . '"
'; + $entry .= 'Offending command was: ' . $query.'
'; + OC_Log::write('core', $entry, OC_Log::FATAL); + error_log('DB error: ' . $entry); + OC_Template::printErrorPage( $entry ); + } + + return $result; + } } diff --git a/lib/db/connection.php b/lib/db/connection.php index 8442efc47c9..e18062d78f3 100644 --- a/lib/db/connection.php +++ b/lib/db/connection.php @@ -138,8 +138,18 @@ class Connection extends \Doctrine\DBAL\Connection { return parent::lastInsertId($seqName); } + /** + * @brief Insert a row if a matching row doesn't exists. + * @param string $table. The table to insert into in the form '*PREFIX*tableName' + * @param array $input. An array of fieldname/value pairs + * @returns bool The return value from execute() + */ + public function insertIfNotExist($table, $input) { + return $this->adapter->insertIfNotExist($table, $input); + } + // internal use - public function replaceTablePrefix($statement) { + protected function replaceTablePrefix($statement) { return str_replace( '*PREFIX*', $this->table_prefix, $statement ); } -- 2.39.5