diff options
| -rw-r--r-- | lib/db.php | 403 | ||||
| -rw-r--r-- | lib/db/adapter.php | 72 | ||||
| -rw-r--r-- | lib/db/adapteroci8.php | 28 | ||||
| -rw-r--r-- | lib/db/adapterpgsql.php | 23 | ||||
| -rw-r--r-- | lib/db/adaptersqlite.php | 60 | ||||
| -rw-r--r-- | lib/db/adaptersqlsrv.php | 28 | ||||
| -rw-r--r-- | lib/db/connection.php | 197 | ||||
| -rw-r--r-- | lib/db/mdb2schemamanager.php | 8 | ||||
| -rw-r--r-- | lib/db/statementwrapper.php | 4 | ||||
| -rw-r--r-- | tests/lib/db.php | 2 |
10 files changed, 488 insertions, 337 deletions
diff --git a/lib/db.php b/lib/db.php index a96f4697235..ebd012c72f8 100644 --- a/lib/db.php +++ b/lib/db.php @@ -41,68 +41,25 @@ class DatabaseException extends Exception { * Doctrine with some adaptions. */ class OC_DB { - const BACKEND_DOCTRINE=2; - - static private $preparedQueries = array(); - static private $cachingEnabled = true; - /** - * @var \Doctrine\DBAL\Connection + * @var \OC\DB\Connection $connection */ - static private $connection; //the preferred connection to use, only Doctrine - static private $backend=null; - /** - * @var \Doctrine\DBAL\Connection - */ - static private $DOCTRINE=null; + static private $connection; //the prefered connection to use, only Doctrine static private $prefix=null; static private $type=null; /** - * check which backend we should use - * @return int BACKEND_DOCTRINE - */ - private static function getDBBackend() { - return self::BACKEND_DOCTRINE; - } - - /** * @brief connects to the database - * @param int $backend * @return bool true if connection can be established or false on error * * Connects to the database as specified in config.php */ - public static function connect($backend=null) { + public static function connect() { if(self::$connection) { return true; } - if(is_null($backend)) { - $backend=self::getDBBackend(); - } - if($backend==self::BACKEND_DOCTRINE) { - $success = self::connectDoctrine(); - self::$connection=self::$DOCTRINE; - self::$backend=self::BACKEND_DOCTRINE; - } - return $success; - } - /** - * connect to the database using doctrine - * - * @return bool - */ - public static function connectDoctrine() { - if(self::$connection) { - if(self::$backend!=self::BACKEND_DOCTRINE) { - self::disconnect(); - } else { - return true; - } - } - self::$preparedQueries = array(); // The global data we need $name = OC_Config::getValue( "dbname", "owncloud" ); $host = OC_Config::getValue( "dbhost", "" ); @@ -116,7 +73,7 @@ class OC_DB { } // do nothing if the connection already has been established - if(!self::$DOCTRINE) { + if (!self::$connection) { $config = new \Doctrine\DBAL\Configuration(); switch($type) { case 'sqlite': @@ -128,6 +85,7 @@ class OC_DB { 'path' => $datadir.'/'.$name.'.db', 'driver' => 'pdo_sqlite', ); + $connectionParams['adapter'] = '\OC\DB\AdapterSqlite'; break; case 'mysql': $connectionParams = array( @@ -139,6 +97,7 @@ class OC_DB { 'charset' => 'UTF8', 'driver' => 'pdo_mysql', ); + $connectionParams['adapter'] = '\OC\DB\Adapter'; break; case 'pgsql': $connectionParams = array( @@ -149,6 +108,7 @@ class OC_DB { 'dbname' => $name, 'driver' => 'pdo_pgsql', ); + $connectionParams['adapter'] = '\OC\DB\AdapterPgSql'; break; case 'oci': $connectionParams = array( @@ -162,6 +122,7 @@ class OC_DB { if (!empty($port)) { $connectionParams['port'] = $port; } + $connectionParams['adapter'] = '\OC\DB\AdapterOCI8'; break; case 'mssql': $connectionParams = array( @@ -173,12 +134,20 @@ class OC_DB { 'charset' => 'UTF8', 'driver' => 'pdo_sqlsrv', ); + $connectionParams['adapter'] = '\OC\DB\AdapterSQLSrv'; break; default: return false; } + $connectionParams['wrapperClass'] = 'OC\DB\Connection'; + $connectionParams['tablePrefix'] = OC_Config::getValue('dbtableprefix', 'oc_' ); try { - self::$DOCTRINE = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config); + self::$connection = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config); + if ($type === 'sqlite' || $type === 'sqlite3') { + // Sqlite doesn't handle query caching and schema changes + // TODO: find a better way to handle this + self::$connection->disableQueryStatementCaching(); + } } catch(\Doctrine\DBAL\DBALException $e) { OC_Log::write('core', $e->getMessage(), OC_Log::FATAL); OC_User::setUserId(null); @@ -194,12 +163,9 @@ class OC_DB { } /** - * get the database connection object - * - * @return \Doctrine\DBAL\Connection + * @return \OC\DB\Connection */ - private static function getConnection() - { + static public function getConnection() { self::connect(); return self::$connection; } @@ -226,25 +192,6 @@ class OC_DB { * SQL query via Doctrine prepare(), needs to be execute()'d! */ static public function prepare( $query , $limit = null, $offset = null, $isManipulation = null) { - - if (!is_null($limit) && $limit != -1) { - if ($limit === -1) { - $limit = null; - } - $platform = self::$connection->getDatabasePlatform(); - $query = $platform->modifyLimitQuery($query, $limit, $offset); - } else { - if (isset(self::$preparedQueries[$query]) and self::$cachingEnabled) { - return self::$preparedQueries[$query]; - } - } - $rawQuery = $query; - - // Optimize the query - $query = self::processQuery( $query ); - if(OC_Config::getValue( "log_query", false)) { - OC_Log::write('core', 'DB prepare : '.$query, OC_Log::DEBUG); - } self::connect(); if ($isManipulation === null) { @@ -253,45 +200,38 @@ class OC_DB { } // return the result - if (self::$backend == self::BACKEND_DOCTRINE) { - try { - $result=self::$connection->prepare($query); - } catch(\Doctrine\DBAL\DBALException $e) { - throw new \DatabaseException($e->getMessage(), $query); - } - // differentiate between query and manipulation - $result=new OC_DB_StatementWrapper($result, $isManipulation); - } - if ((is_null($limit) || $limit == -1) and self::$cachingEnabled ) { - $type = OC_Config::getValue( "dbtype", "sqlite" ); - if( $type != 'sqlite' && $type != 'sqlite3' ) { - self::$preparedQueries[$rawQuery] = $result; - } + try { + $result = self::$connection->prepare($query, $limit, $offset); + } catch (\Doctrine\DBAL\DBALException $e) { + throw new \DatabaseException($e->getMessage(), $query); } + // differentiate between query and manipulation + $result = new OC_DB_StatementWrapper($result, $isManipulation); return $result; } - + /** * tries to guess the type of statement based on the first 10 characters * the current check allows some whitespace but does not work with IF EXISTS or other more complex statements * * @param string $sql + * @return bool */ static public function isManipulation( $sql ) { - $selectOccurence = stripos ($sql, "SELECT"); - if ($selectOccurence !== false && $selectOccurence < 10) { + $selectOccurrence = stripos($sql, 'SELECT'); + if ($selectOccurrence !== false && $selectOccurrence < 10) { return false; } - $insertOccurence = stripos ($sql, "INSERT"); - if ($insertOccurence !== false && $insertOccurence < 10) { + $insertOccurrence = stripos($sql, 'INSERT'); + if ($insertOccurrence !== false && $insertOccurrence < 10) { return true; } - $updateOccurence = stripos ($sql, "UPDATE"); - if ($updateOccurence !== false && $updateOccurence < 10) { + $updateOccurrence = stripos($sql, 'UPDATE'); + if ($updateOccurrence !== false && $updateOccurrence < 10) { return true; } - $deleteOccurance = stripos ($sql, "DELETE"); - if ($deleteOccurance !== false && $deleteOccurance < 10) { + $deleteOccurrence = stripos($sql, 'DELETE'); + if ($deleteOccurrence !== false && $deleteOccurrence < 10) { return true; } return false; @@ -309,7 +249,7 @@ class OC_DB { static public function executeAudited( $stmt, array $parameters = null) { if (is_string($stmt)) { // convert to an array with 'sql' - if (stripos($stmt,'LIMIT') !== false) { //OFFSET requires LIMIT, se we only neet to check for LIMIT + if (stripos($stmt, 'LIMIT') !== false) { //OFFSET requires LIMIT, so we only need to check for LIMIT // TODO try to convert LIMIT OFFSET notation to parameters, see fixLimitClauseForMSSQL $message = 'LIMIT and OFFSET are forbidden for portability reasons,' . ' pass an array with \'limit\' and \'offset\' instead'; @@ -317,7 +257,7 @@ class OC_DB { } $stmt = array('sql' => $stmt, 'limit' => null, 'offset' => null); } - if (is_array($stmt)){ + if (is_array($stmt)) { // convert to prepared statement if ( ! array_key_exists('sql', $stmt) ) { $message = 'statement array must at least contain key \'sql\''; @@ -359,55 +299,49 @@ class OC_DB { */ public static function insertid($table=null) { self::connect(); - $type = OC_Config::getValue( "dbtype", "sqlite" ); - if( $type === 'pgsql' ) { - $result = self::executeAudited('SELECT lastval() AS id'); - $row = $result->fetchRow(); - self::raiseExceptionOnError($row, 'fetching row for insertid failed'); - return (int)$row['id']; - } else if( $type === 'mssql') { - if($table !== null) { - $prefix = OC_Config::getValue( "dbtableprefix", "oc_" ); - $table = str_replace( '*PREFIX*', $prefix, $table ); - } - return self::$connection->lastInsertId($table); - } - if( $type === 'oci' ) { - if($table !== null) { - $prefix = OC_Config::getValue( "dbtableprefix", "oc_" ); - $suffix = '_SEQ'; - $table = '"'.str_replace( '*PREFIX*', $prefix, $table ).$suffix.'"'; - } - return self::$connection->lastInsertId($table); - } else { - if($table !== null) { - $prefix = OC_Config::getValue( "dbtableprefix", "oc_" ); - $suffix = OC_Config::getValue( "dbsequencesuffix", "_id_seq" ); - $table = str_replace( '*PREFIX*', $prefix, $table ).$suffix; - } - $result = self::$connection->lastInsertId($table); - } - self::raiseExceptionOnError($result, 'insertid failed'); - return (int)$result; + return self::$connection->lastInsertId($table); + } + + /** + * @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 + * @return int number of updated rows + */ + public static function insertIfNotExist($table, $input) { + self::connect(); + return self::$connection->insertIfNotExist($table, $input); + } + + /** + * Start a transaction + */ + public static function beginTransaction() { + self::connect(); + self::$connection->beginTransaction(); + } + + /** + * Commit the database changes done during a transaction that is in progress + */ + public static function commit() { + self::connect(); + self::$connection->commit(); } /** * @brief Disconnect - * @return bool * * This is good bye, good bye, yeah! */ public static function disconnect() { // Cut connection if required if(self::$connection) { - self::$connection=false; - self::$DOCTRINE=false; + self::$connection->close(); } - - return true; } - /** else { + /** * @brief saves database schema to xml file * @param string $file name of file * @param int $mode @@ -415,7 +349,7 @@ class OC_DB { * * TODO: write more documentation */ - public static function getDbStructure( $file, $mode=MDB2_SCHEMA_DUMP_STRUCTURE) { + public static function getDbStructure( $file, $mode = 0) { $schemaManager = self::getMDB2SchemaManager(); return $schemaManager->getDbStructure($file); } @@ -451,172 +385,6 @@ class OC_DB { } /** - * @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 int number of updated rows - */ - public static function insertIfNotExist($table, $input) { - self::connect(); - $prefix = OC_Config::getValue( "dbtableprefix", "oc_" ); - $table = str_replace( '*PREFIX*', $prefix, $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; - } - - /** - * @brief does minor changes to query - * @param string $query Query string - * @return string corrected query string - * - * This function replaces *PREFIX* with the value of $CONFIG_DBTABLEPREFIX - * and replaces the ` with ' or " according to the database driver. - */ - private static function processQuery( $query ) { - self::connect(); - // We need Database type and table prefix - if(is_null(self::$type)) { - self::$type=OC_Config::getValue( "dbtype", "sqlite" ); - } - $type = self::$type; - if(is_null(self::$prefix)) { - self::$prefix=OC_Config::getValue( "dbtableprefix", "oc_" ); - } - $prefix = self::$prefix; - - // differences in escaping of table names ('`' for mysql) and getting the current timestamp - if( $type == 'sqlite' || $type == 'sqlite3' ) { - $query = str_replace( '`', '"', $query ); - $query = str_ireplace( 'NOW()', 'datetime(\'now\')', $query ); - $query = str_ireplace( 'UNIX_TIMESTAMP()', 'strftime(\'%s\',\'now\')', $query ); - } elseif( $type == 'pgsql' ) { - $query = str_replace( '`', '"', $query ); - $query = str_ireplace( 'UNIX_TIMESTAMP()', 'cast(extract(epoch from current_timestamp) as integer)', - $query ); - } elseif( $type == 'oci' ) { - $query = str_replace( '`', '"', $query ); - $query = str_ireplace( 'NOW()', 'CURRENT_TIMESTAMP', $query ); - $query = str_ireplace( 'UNIX_TIMESTAMP()', "(cast(sys_extract_utc(systimestamp) as date) - date'1970-01-01') * 86400", $query ); - }elseif( $type == 'mssql' ) { - $query = preg_replace( "/\`(.*?)`/", "[$1]", $query ); - $query = str_ireplace( 'NOW()', 'CURRENT_TIMESTAMP', $query ); - $query = str_replace( 'LENGTH(', 'LEN(', $query ); - $query = str_replace( 'SUBSTR(', 'SUBSTRING(', $query ); - $query = str_ireplace( 'UNIX_TIMESTAMP()', 'DATEDIFF(second,{d \'1970-01-01\'},GETDATE())', $query ); - - $query = self::fixLimitClauseForMSSQL($query); - } - - // replace table name prefix - $query = str_replace( '*PREFIX*', $prefix, $query ); - - return $query; - } - - private static function fixLimitClauseForMSSQL($query) { - $limitLocation = stripos ($query, "LIMIT"); - - if ( $limitLocation === false ) { - return $query; - } - - // total == 0 means all results - not zero results - // - // First number is either total or offset, locate it by first space - // - $offset = substr ($query, $limitLocation + 5); - $offset = substr ($offset, 0, stripos ($offset, ' ')); - $offset = trim ($offset); - - // check for another parameter - if (stripos ($offset, ',') === false) { - // no more parameters - $offset = 0; - $total = intval ($offset); - } else { - // found another parameter - $offset = intval ($offset); - - $total = substr ($query, $limitLocation + 5); - $total = substr ($total, stripos ($total, ',')); - - $total = substr ($total, 0, stripos ($total, ' ')); - $total = intval ($total); - } - - $query = trim (substr ($query, 0, $limitLocation)); - - if ($offset == 0 && $total !== 0) { - if (strpos($query, "SELECT") === false) { - $query = "TOP {$total} " . $query; - } else { - $query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP '.$total, $query); - } - } else if ($offset > 0) { - $query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(10000000) ', $query); - $query = 'SELECT * - FROM (SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.line2) AS line3 - FROM (SELECT 1 AS line2, sub1.* FROM (' . $query . ') AS sub1) as sub2) AS sub3'; - - if ($total > 0) { - $query .= ' WHERE line3 BETWEEN ' . ($offset + 1) . ' AND ' . ($offset + $total); - } else { - $query .= ' WHERE line3 > ' . $offset; - } - } - return $query; - } - - /** * @brief drop a table * @param string $tableName the table to drop */ @@ -644,22 +412,6 @@ class OC_DB { } /** - * Start a transaction - */ - public static function beginTransaction() { - self::connect(); - self::$connection->beginTransaction(); - } - - /** - * Commit the database changes done during a transaction that is in progress - */ - public static function commit() { - self::connect(); - self::$connection->commit(); - } - - /** * check if a result is an error, works with Doctrine * @param mixed $result * @return bool @@ -697,17 +449,9 @@ class OC_DB { * @return string */ public static function getErrorMessage($error) { - if (self::$backend==self::BACKEND_DOCTRINE and self::$DOCTRINE) { - $msg = self::$DOCTRINE->errorCode() . ': '; - $errorInfo = self::$DOCTRINE->errorInfo(); - if (is_array($errorInfo)) { - $msg .= 'SQLSTATE = '.$errorInfo[0] . ', '; - $msg .= 'Driver Code = '.$errorInfo[1] . ', '; - $msg .= 'Driver Message = '.$errorInfo[2]; - } - return $msg; + if (self::$connection) { + return self::$connection->getError(); } - return ''; } @@ -715,9 +459,10 @@ class OC_DB { * @param bool $enabled */ static public function enableCaching($enabled) { - if (!$enabled) { - self::$preparedQueries = array(); + if ($enabled) { + self::$connection->enableQueryStatementCaching(); + } else { + self::$connection->disableQueryStatementCaching(); } - self::$cachingEnabled = $enabled; } } diff --git a/lib/db/adapter.php b/lib/db/adapter.php new file mode 100644 index 00000000000..6b31f37dd98 --- /dev/null +++ b/lib/db/adapter.php @@ -0,0 +1,72 @@ +<?php +/** + * Copyright (c) 2013 Bart Visscher <bartv@thisnet.nl> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace OC\DB; + +/** + * This handles the way we use to write queries, into something that can be + * handled by the database abstraction layer. + */ +class Adapter { + + /** + * @var \OC\DB\Connection $conn + */ + protected $conn; + + public function __construct($conn) { + $this->conn = $conn; + } + + /** + * @param string $table name + * @return int id of last insert statement + */ + public function lastInsertId($table) { + return $this->conn->realLastInsertId($table); + } + + /** + * @param string $statement that needs to be changed so the db can handle it + * @return string changed statement + */ + public function fixupStatement($statement) { + return $statement; + } + + /** + * @brief 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).'? ' // 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 { + 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 ); + } + } +} diff --git a/lib/db/adapteroci8.php b/lib/db/adapteroci8.php new file mode 100644 index 00000000000..bc226e979ec --- /dev/null +++ b/lib/db/adapteroci8.php @@ -0,0 +1,28 @@ +<?php +/** + * Copyright (c) 2013 Bart Visscher <bartv@thisnet.nl> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + + +namespace OC\DB; + +class AdapterOCI8 extends Adapter { + public function lastInsertId($table) { + if($table !== null) { + $suffix = '_SEQ'; + $table = '"'.$table.$suffix.'"'; + } + return $this->conn->realLastInsertId($table); + } + + const UNIX_TIMESTAMP_REPLACEMENT = "(cast(sys_extract_utc(systimestamp) as date) - date'1970-01-01') * 86400"; + public function fixupStatement($statement) { + $statement = str_replace( '`', '"', $statement ); + $statement = str_ireplace( 'NOW()', 'CURRENT_TIMESTAMP', $statement ); + $statement = str_ireplace( 'UNIX_TIMESTAMP()', self::UNIX_TIMESTAMP_REPLACEMENT, $statement ); + return $statement; + } +} diff --git a/lib/db/adapterpgsql.php b/lib/db/adapterpgsql.php new file mode 100644 index 00000000000..990d71c9f29 --- /dev/null +++ b/lib/db/adapterpgsql.php @@ -0,0 +1,23 @@ +<?php +/** + * Copyright (c) 2013 Bart Visscher <bartv@thisnet.nl> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + + +namespace OC\DB; + +class AdapterPgSql extends Adapter { + public function lastInsertId($table) { + return $this->conn->fetchColumn('SELECT lastval()'); + } + + const UNIX_TIMESTAMP_REPLACEMENT = 'cast(extract(epoch from current_timestamp) as integer)'; + public function fixupStatement($statement) { + $statement = str_replace( '`', '"', $statement ); + $statement = str_ireplace( 'UNIX_TIMESTAMP()', self::UNIX_TIMESTAMP_REPLACEMENT, $statement ); + return $statement; + } +} diff --git a/lib/db/adaptersqlite.php b/lib/db/adaptersqlite.php new file mode 100644 index 00000000000..fa6d308ae32 --- /dev/null +++ b/lib/db/adaptersqlite.php @@ -0,0 +1,60 @@ +<?php +/** + * Copyright (c) 2013 Bart Visscher <bartv@thisnet.nl> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + + +namespace OC\DB; + +class AdapterSqlite extends Adapter { + public function fixupStatement($statement) { + $statement = str_replace( '`', '"', $statement ); + $statement = str_ireplace( 'NOW()', 'datetime(\'now\')', $statement ); + $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_values($input)); + } 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 ); + } + + 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() . '"<br />'; + $entry .= 'Offending command was: ' . $query.'<br />'; + \OC_Log::write('core', $entry, \OC_Log::FATAL); + error_log('DB error: ' . $entry); + \OC_Template::printErrorPage( $entry ); + } + + return $result; + } +} diff --git a/lib/db/adaptersqlsrv.php b/lib/db/adaptersqlsrv.php new file mode 100644 index 00000000000..d0a67af28a7 --- /dev/null +++ b/lib/db/adaptersqlsrv.php @@ -0,0 +1,28 @@ +<?php +/** + * Copyright (c) 2013 Bart Visscher <bartv@thisnet.nl> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + + +namespace OC\DB; + +class AdapterSQLSrv extends Adapter { + public function lastInsertId($table) { + if($table !== null) { + $table = $this->conn->replaceTablePrefix( $table ); + } + return $this->conn->lastInsertId($table); + } + + public function fixupStatement($statement) { + $statement = preg_replace( "/\`(.*?)`/", "[$1]", $statement ); + $statement = str_ireplace( 'NOW()', 'CURRENT_TIMESTAMP', $statement ); + $statement = str_replace( 'LENGTH(', 'LEN(', $statement ); + $statement = str_replace( 'SUBSTR(', 'SUBSTRING(', $statement ); + $statement = str_ireplace( 'UNIX_TIMESTAMP()', 'DATEDIFF(second,{d \'1970-01-01\'},GETDATE())', $statement ); + return $statement; + } +} diff --git a/lib/db/connection.php b/lib/db/connection.php new file mode 100644 index 00000000000..2581969dbd0 --- /dev/null +++ b/lib/db/connection.php @@ -0,0 +1,197 @@ +<?php +/** + * Copyright (c) 2013 Bart Visscher <bartv@thisnet.nl> + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace OC\DB; +use Doctrine\DBAL\Driver; +use Doctrine\DBAL\Configuration; +use Doctrine\DBAL\Cache\QueryCacheProfile; +use Doctrine\Common\EventManager; + +class Connection extends \Doctrine\DBAL\Connection { + /** + * @var string $tablePrefix + */ + protected $tablePrefix; + + /** + * @var \OC\DB\Adapter $adapter + */ + protected $adapter; + + /** + * @var \Doctrine\DBAL\Driver\Statement[] $preparedQueries + */ + protected $preparedQueries = array(); + + protected $cachingQueryStatementEnabled = true; + + /** + * Initializes a new instance of the Connection class. + * + * @param array $params The connection parameters. + * @param \Doctrine\DBAL\Driver $driver + * @param \Doctrine\DBAL\Configuration $config + * @param \Doctrine\Common\EventManager $eventManager + * @throws \Exception + */ + public function __construct(array $params, Driver $driver, Configuration $config = null, + EventManager $eventManager = null) + { + if (!isset($params['adapter'])) { + throw new \Exception('adapter not set'); + } + if (!isset($params['tablePrefix'])) { + throw new \Exception('tablePrefix not set'); + } + parent::__construct($params, $driver, $config, $eventManager); + $this->adapter = new $params['adapter']($this); + $this->tablePrefix = $params['tablePrefix']; + } + + /** + * Prepares an SQL statement. + * + * @param string $statement The SQL statement to prepare. + * @param int $limit + * @param int $offset + * @return \Doctrine\DBAL\Driver\Statement The prepared statement. + */ + public function prepare( $statement, $limit=null, $offset=null ) { + if ($limit === -1) { + $limit = null; + } + if (!is_null($limit)) { + $platform = $this->getDatabasePlatform(); + $statement = $platform->modifyLimitQuery($statement, $limit, $offset); + } else { + if (isset($this->preparedQueries[$statement]) && $this->cachingQueryStatementEnabled) { + return $this->preparedQueries[$statement]; + } + $origStatement = $statement; + } + $statement = $this->replaceTablePrefix($statement); + $statement = $this->adapter->fixupStatement($statement); + + if(\OC_Config::getValue( 'log_query', false)) { + \OC_Log::write('core', 'DB prepare : '.$statement, \OC_Log::DEBUG); + } + $result = parent::prepare($statement); + if (is_null($limit) && $this->cachingQueryStatementEnabled) { + $this->preparedQueries[$origStatement] = $result; + } + return $result; + } + + /** + * Executes an, optionally parameterized, SQL query. + * + * If the query is parameterized, a prepared statement is used. + * If an SQLLogger is configured, the execution is logged. + * + * @param string $query The SQL query to execute. + * @param array $params The parameters to bind to the query, if any. + * @param array $types The types the previous parameters are in. + * @param QueryCacheProfile $qcp + * @return \Doctrine\DBAL\Driver\Statement The executed statement. + * @internal PERF: Directly prepares a driver statement, not a wrapper. + */ + public function executeQuery($query, array $params = array(), $types = array(), QueryCacheProfile $qcp = null) + { + $query = $this->replaceTablePrefix($query); + $query = $this->adapter->fixupStatement($query); + return parent::executeQuery($query, $params, $types, $qcp); + } + + /** + * Executes an SQL INSERT/UPDATE/DELETE query with the given parameters + * and returns the number of affected rows. + * + * This method supports PDO binding types as well as DBAL mapping types. + * + * @param string $query The SQL query. + * @param array $params The query parameters. + * @param array $types The parameter types. + * @return integer The number of affected rows. + * @internal PERF: Directly prepares a driver statement, not a wrapper. + */ + public function executeUpdate($query, array $params = array(), array $types = array()) + { + $query = $this->replaceTablePrefix($query); + $query = $this->adapter->fixupStatement($query); + return parent::executeUpdate($query, $params, $types); + } + + /** + * Returns the ID of the last inserted row, or the last value from a sequence object, + * depending on the underlying driver. + * + * Note: This method may not return a meaningful or consistent result across different drivers, + * because the underlying database may not even support the notion of AUTO_INCREMENT/IDENTITY + * columns or sequences. + * + * @param string $seqName Name of the sequence object from which the ID should be returned. + * @return string A string representation of the last inserted ID. + */ + public function lastInsertId($seqName = null) + { + if ($seqName) { + $seqName = $this->replaceTablePrefix($seqName); + } + return $this->adapter->lastInsertId($seqName); + } + + // internal use + public function realLastInsertId($seqName = null) + { + 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 + * @return bool The return value from execute() + */ + public function insertIfNotExist($table, $input) { + return $this->adapter->insertIfNotExist($table, $input); + } + + /** + * returns the error code and message as a string for logging + * works with DoctrineException + * @return string + */ + public function getError() { + $msg = $this->errorCode() . ': '; + $errorInfo = $this->errorInfo(); + if (is_array($errorInfo)) { + $msg .= 'SQLSTATE = '.$errorInfo[0] . ', '; + $msg .= 'Driver Code = '.$errorInfo[1] . ', '; + $msg .= 'Driver Message = '.$errorInfo[2]; + } + return $msg; + } + + // internal use + /** + * @param string $statement + * @return string + */ + protected function replaceTablePrefix($statement) { + return str_replace( '*PREFIX*', $this->tablePrefix, $statement ); + } + + public function enableQueryStatementCaching() { + $this->cachingQueryStatementEnabled = true; + } + + public function disableQueryStatementCaching() { + $this->cachingQueryStatementEnabled = false; + $this->preparedQueries = array(); + } +} diff --git a/lib/db/mdb2schemamanager.php b/lib/db/mdb2schemamanager.php index a34bc9dae75..8e76f46c78f 100644 --- a/lib/db/mdb2schemamanager.php +++ b/lib/db/mdb2schemamanager.php @@ -10,12 +10,12 @@ namespace OC\DB; class MDB2SchemaManager { /** - * @var \Doctrine\DBAL\Connection $conn + * @var \OC\DB\Connection $conn */ protected $conn; /** - * @param \Doctrine\DBAL\Connection $conn + * @param \OC\DB\Connection $conn */ public function __construct($conn) { $this->conn = $conn; @@ -95,7 +95,7 @@ class MDB2SchemaManager { $toSchema = clone $fromSchema; $toSchema->dropTable($tableName); $sql = $fromSchema->getMigrateToSql($toSchema, $this->conn->getDatabasePlatform()); - $this->conn->execute($sql); + $this->conn->executeQuery($sql); } /** @@ -122,7 +122,7 @@ class MDB2SchemaManager { $apps = \OC_App::getAllApps(); $this->conn->beginTransaction(); // Delete the old tables - $this->removeDBStructure( OC::$SERVERROOT . '/db_structure.xml' ); + $this->removeDBStructure( \OC::$SERVERROOT . '/db_structure.xml' ); foreach($apps as $app) { $path = \OC_App::getAppPath($app).'/appinfo/database.xml'; diff --git a/lib/db/statementwrapper.php b/lib/db/statementwrapper.php index f7bc45e068f..b8da1afc0e5 100644 --- a/lib/db/statementwrapper.php +++ b/lib/db/statementwrapper.php @@ -53,7 +53,7 @@ class OC_DB_StatementWrapper { */ public function execute($input=array()) { if(OC_Config::getValue( "log_query", false)) { - $params_str = str_replace("\n"," ",var_export($input,true)); + $params_str = str_replace("\n", " ", var_export($input, true)); OC_Log::write('core', 'DB execute with arguments : '.$params_str, OC_Log::DEBUG); } $this->lastArguments = $input; @@ -134,7 +134,7 @@ class OC_DB_StatementWrapper { $host = OC_Config::getValue( "dbhost", "" ); $user = OC_Config::getValue( "dbuser", "" ); $pass = OC_Config::getValue( "dbpassword", "" ); - if (strpos($host,':')) { + if (strpos($host, ':')) { list($host, $port) = explode(':', $host, 2); } else { $port = false; diff --git a/tests/lib/db.php b/tests/lib/db.php index 79e05f30a1c..51edbf7b309 100644 --- a/tests/lib/db.php +++ b/tests/lib/db.php @@ -76,13 +76,11 @@ class Test_DB extends PHPUnit_Framework_TestCase { $query = OC_DB::prepare('INSERT INTO `*PREFIX*'.$this->table2.'` (`fullname`,`uri`) VALUES (?,?)'); $result1 = OC_DB::executeAudited($query, array('insertid 1','uri_1')); $id1 = OC_DB::insertid('*PREFIX*'.$this->table2); - $this->assertInternalType('int', $id1); // we don't know the id we should expect, so insert another row $result2 = OC_DB::executeAudited($query, array('insertid 2','uri_2')); $id2 = OC_DB::insertid('*PREFIX*'.$this->table2); // now we can check if the two ids are in correct order - $this->assertInternalType('int', $id2); $this->assertGreaterThan($id1, $id2); } |