1
0

Connection.php 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611
  1. <?php
  2. declare(strict_types=1);
  3. /**
  4. * @copyright Copyright (c) 2016, ownCloud, Inc.
  5. *
  6. * @author Bart Visscher <bartv@thisnet.nl>
  7. * @author Christoph Wurst <christoph@winzerhof-wurst.at>
  8. * @author Joas Schilling <coding@schilljs.com>
  9. * @author Julius Härtl <jus@bitgrid.net>
  10. * @author Morris Jobke <hey@morrisjobke.de>
  11. * @author Ole Ostergaard <ole.c.ostergaard@gmail.com>
  12. * @author Ole Ostergaard <ole.ostergaard@knime.com>
  13. * @author Philipp Schaffrath <github@philipp.schaffrath.email>
  14. * @author Robin Appelman <robin@icewind.nl>
  15. * @author Robin McCorkell <robin@mccorkell.me.uk>
  16. * @author Roeland Jago Douma <roeland@famdouma.nl>
  17. * @author Thomas Müller <thomas.mueller@tmit.eu>
  18. *
  19. * @license AGPL-3.0
  20. *
  21. * This code is free software: you can redistribute it and/or modify
  22. * it under the terms of the GNU Affero General Public License, version 3,
  23. * as published by the Free Software Foundation.
  24. *
  25. * This program is distributed in the hope that it will be useful,
  26. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  27. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  28. * GNU Affero General Public License for more details.
  29. *
  30. * You should have received a copy of the GNU Affero General Public License, version 3,
  31. * along with this program. If not, see <http://www.gnu.org/licenses/>
  32. *
  33. */
  34. namespace OC\DB;
  35. use Doctrine\Common\EventManager;
  36. use Doctrine\DBAL\Cache\QueryCacheProfile;
  37. use Doctrine\DBAL\Configuration;
  38. use Doctrine\DBAL\Driver;
  39. use Doctrine\DBAL\Exception;
  40. use Doctrine\DBAL\Platforms\MySQLPlatform;
  41. use Doctrine\DBAL\Platforms\OraclePlatform;
  42. use Doctrine\DBAL\Platforms\PostgreSQL94Platform;
  43. use Doctrine\DBAL\Platforms\SqlitePlatform;
  44. use Doctrine\DBAL\Result;
  45. use Doctrine\DBAL\Schema\Schema;
  46. use Doctrine\DBAL\Statement;
  47. use OCP\DB\QueryBuilder\IQueryBuilder;
  48. use OCP\Diagnostics\IEventLogger;
  49. use OCP\IRequestId;
  50. use OCP\PreConditionNotMetException;
  51. use OCP\Profiler\IProfiler;
  52. use OC\DB\QueryBuilder\QueryBuilder;
  53. use OC\SystemConfig;
  54. use Psr\Log\LoggerInterface;
  55. class Connection extends \Doctrine\DBAL\Connection {
  56. /** @var string */
  57. protected $tablePrefix;
  58. /** @var \OC\DB\Adapter $adapter */
  59. protected $adapter;
  60. /** @var SystemConfig */
  61. private $systemConfig;
  62. private LoggerInterface $logger;
  63. protected $lockedTable = null;
  64. /** @var int */
  65. protected $queriesBuilt = 0;
  66. /** @var int */
  67. protected $queriesExecuted = 0;
  68. /** @var DbDataCollector|null */
  69. protected $dbDataCollector = null;
  70. /**
  71. * Initializes a new instance of the Connection class.
  72. *
  73. * @throws \Exception
  74. */
  75. public function __construct(
  76. array $params,
  77. Driver $driver,
  78. ?Configuration $config = null,
  79. ?EventManager $eventManager = null
  80. ) {
  81. if (!isset($params['adapter'])) {
  82. throw new \Exception('adapter not set');
  83. }
  84. if (!isset($params['tablePrefix'])) {
  85. throw new \Exception('tablePrefix not set');
  86. }
  87. /**
  88. * @psalm-suppress InternalMethod
  89. */
  90. parent::__construct($params, $driver, $config, $eventManager);
  91. $this->adapter = new $params['adapter']($this);
  92. $this->tablePrefix = $params['tablePrefix'];
  93. $this->systemConfig = \OC::$server->getSystemConfig();
  94. $this->logger = \OC::$server->get(LoggerInterface::class);
  95. /** @var \OCP\Profiler\IProfiler */
  96. $profiler = \OC::$server->get(IProfiler::class);
  97. if ($profiler->isEnabled()) {
  98. $this->dbDataCollector = new DbDataCollector($this);
  99. $profiler->add($this->dbDataCollector);
  100. $debugStack = new BacktraceDebugStack();
  101. $this->dbDataCollector->setDebugStack($debugStack);
  102. $this->_config->setSQLLogger($debugStack);
  103. }
  104. }
  105. /**
  106. * @throws Exception
  107. */
  108. public function connect() {
  109. try {
  110. if ($this->_conn) {
  111. /** @psalm-suppress InternalMethod */
  112. return parent::connect();
  113. }
  114. // Only trigger the event logger for the initial connect call
  115. $eventLogger = \OC::$server->get(IEventLogger::class);
  116. $eventLogger->start('connect:db', 'db connection opened');
  117. /** @psalm-suppress InternalMethod */
  118. $status = parent::connect();
  119. $eventLogger->end('connect:db');
  120. return $status;
  121. } catch (Exception $e) {
  122. // throw a new exception to prevent leaking info from the stacktrace
  123. throw new Exception('Failed to connect to the database: ' . $e->getMessage(), $e->getCode());
  124. }
  125. }
  126. public function getStats(): array {
  127. return [
  128. 'built' => $this->queriesBuilt,
  129. 'executed' => $this->queriesExecuted,
  130. ];
  131. }
  132. /**
  133. * Returns a QueryBuilder for the connection.
  134. */
  135. public function getQueryBuilder(): IQueryBuilder {
  136. $this->queriesBuilt++;
  137. return new QueryBuilder(
  138. new ConnectionAdapter($this),
  139. $this->systemConfig,
  140. $this->logger
  141. );
  142. }
  143. /**
  144. * Gets the QueryBuilder for the connection.
  145. *
  146. * @return \Doctrine\DBAL\Query\QueryBuilder
  147. * @deprecated please use $this->getQueryBuilder() instead
  148. */
  149. public function createQueryBuilder() {
  150. $backtrace = $this->getCallerBacktrace();
  151. $this->logger->debug('Doctrine QueryBuilder retrieved in {backtrace}', ['app' => 'core', 'backtrace' => $backtrace]);
  152. $this->queriesBuilt++;
  153. return parent::createQueryBuilder();
  154. }
  155. /**
  156. * Gets the ExpressionBuilder for the connection.
  157. *
  158. * @return \Doctrine\DBAL\Query\Expression\ExpressionBuilder
  159. * @deprecated please use $this->getQueryBuilder()->expr() instead
  160. */
  161. public function getExpressionBuilder() {
  162. $backtrace = $this->getCallerBacktrace();
  163. $this->logger->debug('Doctrine ExpressionBuilder retrieved in {backtrace}', ['app' => 'core', 'backtrace' => $backtrace]);
  164. $this->queriesBuilt++;
  165. return parent::getExpressionBuilder();
  166. }
  167. /**
  168. * Get the file and line that called the method where `getCallerBacktrace()` was used
  169. *
  170. * @return string
  171. */
  172. protected function getCallerBacktrace() {
  173. $traces = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 2);
  174. // 0 is the method where we use `getCallerBacktrace`
  175. // 1 is the target method which uses the method we want to log
  176. if (isset($traces[1])) {
  177. return $traces[1]['file'] . ':' . $traces[1]['line'];
  178. }
  179. return '';
  180. }
  181. /**
  182. * @return string
  183. */
  184. public function getPrefix() {
  185. return $this->tablePrefix;
  186. }
  187. /**
  188. * Prepares an SQL statement.
  189. *
  190. * @param string $statement The SQL statement to prepare.
  191. * @param int|null $limit
  192. * @param int|null $offset
  193. *
  194. * @return Statement The prepared statement.
  195. * @throws Exception
  196. */
  197. public function prepare($statement, $limit = null, $offset = null): Statement {
  198. if ($limit === -1 || $limit === null) {
  199. $limit = null;
  200. } else {
  201. $limit = (int) $limit;
  202. }
  203. if ($offset !== null) {
  204. $offset = (int) $offset;
  205. }
  206. if (!is_null($limit)) {
  207. $platform = $this->getDatabasePlatform();
  208. $statement = $platform->modifyLimitQuery($statement, $limit, $offset);
  209. }
  210. $statement = $this->replaceTablePrefix($statement);
  211. $statement = $this->adapter->fixupStatement($statement);
  212. return parent::prepare($statement);
  213. }
  214. /**
  215. * Executes an, optionally parametrized, SQL query.
  216. *
  217. * If the query is parametrized, a prepared statement is used.
  218. * If an SQLLogger is configured, the execution is logged.
  219. *
  220. * @param string $sql The SQL query to execute.
  221. * @param array $params The parameters to bind to the query, if any.
  222. * @param array $types The types the previous parameters are in.
  223. * @param \Doctrine\DBAL\Cache\QueryCacheProfile|null $qcp The query cache profile, optional.
  224. *
  225. * @return Result The executed statement.
  226. *
  227. * @throws \Doctrine\DBAL\Exception
  228. */
  229. public function executeQuery(string $sql, array $params = [], $types = [], QueryCacheProfile $qcp = null): Result {
  230. $sql = $this->replaceTablePrefix($sql);
  231. $sql = $this->adapter->fixupStatement($sql);
  232. $this->queriesExecuted++;
  233. $this->logQueryToFile($sql);
  234. return parent::executeQuery($sql, $params, $types, $qcp);
  235. }
  236. /**
  237. * @throws Exception
  238. */
  239. public function executeUpdate(string $sql, array $params = [], array $types = []): int {
  240. $sql = $this->replaceTablePrefix($sql);
  241. $sql = $this->adapter->fixupStatement($sql);
  242. $this->queriesExecuted++;
  243. $this->logQueryToFile($sql);
  244. return parent::executeUpdate($sql, $params, $types);
  245. }
  246. /**
  247. * Executes an SQL INSERT/UPDATE/DELETE query with the given parameters
  248. * and returns the number of affected rows.
  249. *
  250. * This method supports PDO binding types as well as DBAL mapping types.
  251. *
  252. * @param string $sql The SQL query.
  253. * @param array $params The query parameters.
  254. * @param array $types The parameter types.
  255. *
  256. * @return int The number of affected rows.
  257. *
  258. * @throws \Doctrine\DBAL\Exception
  259. */
  260. public function executeStatement($sql, array $params = [], array $types = []): int {
  261. $sql = $this->replaceTablePrefix($sql);
  262. $sql = $this->adapter->fixupStatement($sql);
  263. $this->queriesExecuted++;
  264. $this->logQueryToFile($sql);
  265. return (int)parent::executeStatement($sql, $params, $types);
  266. }
  267. protected function logQueryToFile(string $sql): void {
  268. $logFile = $this->systemConfig->getValue('query_log_file');
  269. if ($logFile !== '' && is_writable(dirname($logFile)) && (!file_exists($logFile) || is_writable($logFile))) {
  270. $prefix = '';
  271. if ($this->systemConfig->getValue('query_log_file_requestid') === 'yes') {
  272. $prefix .= \OC::$server->get(IRequestId::class)->getId() . "\t";
  273. }
  274. file_put_contents(
  275. $this->systemConfig->getValue('query_log_file', ''),
  276. $prefix . $sql . "\n",
  277. FILE_APPEND
  278. );
  279. }
  280. }
  281. /**
  282. * Returns the ID of the last inserted row, or the last value from a sequence object,
  283. * depending on the underlying driver.
  284. *
  285. * Note: This method may not return a meaningful or consistent result across different drivers,
  286. * because the underlying database may not even support the notion of AUTO_INCREMENT/IDENTITY
  287. * columns or sequences.
  288. *
  289. * @param string $seqName Name of the sequence object from which the ID should be returned.
  290. *
  291. * @return string the last inserted ID.
  292. * @throws Exception
  293. */
  294. public function lastInsertId($seqName = null) {
  295. if ($seqName) {
  296. $seqName = $this->replaceTablePrefix($seqName);
  297. }
  298. return $this->adapter->lastInsertId($seqName);
  299. }
  300. /**
  301. * @internal
  302. * @throws Exception
  303. */
  304. public function realLastInsertId($seqName = null) {
  305. return parent::lastInsertId($seqName);
  306. }
  307. /**
  308. * Insert a row if the matching row does not exists. To accomplish proper race condition avoidance
  309. * it is needed that there is also a unique constraint on the values. Then this method will
  310. * catch the exception and return 0.
  311. *
  312. * @param string $table The table name (will replace *PREFIX* with the actual prefix)
  313. * @param array $input data that should be inserted into the table (column name => value)
  314. * @param array|null $compare List of values that should be checked for "if not exists"
  315. * If this is null or an empty array, all keys of $input will be compared
  316. * Please note: text fields (clob) must not be used in the compare array
  317. * @return int number of inserted rows
  318. * @throws \Doctrine\DBAL\Exception
  319. * @deprecated 15.0.0 - use unique index and "try { $db->insert() } catch (UniqueConstraintViolationException $e) {}" instead, because it is more reliable and does not have the risk for deadlocks - see https://github.com/nextcloud/server/pull/12371
  320. */
  321. public function insertIfNotExist($table, $input, array $compare = null) {
  322. return $this->adapter->insertIfNotExist($table, $input, $compare);
  323. }
  324. public function insertIgnoreConflict(string $table, array $values) : int {
  325. return $this->adapter->insertIgnoreConflict($table, $values);
  326. }
  327. private function getType($value) {
  328. if (is_bool($value)) {
  329. return IQueryBuilder::PARAM_BOOL;
  330. } elseif (is_int($value)) {
  331. return IQueryBuilder::PARAM_INT;
  332. } else {
  333. return IQueryBuilder::PARAM_STR;
  334. }
  335. }
  336. /**
  337. * Insert or update a row value
  338. *
  339. * @param string $table
  340. * @param array $keys (column name => value)
  341. * @param array $values (column name => value)
  342. * @param array $updatePreconditionValues ensure values match preconditions (column name => value)
  343. * @return int number of new rows
  344. * @throws \OCP\DB\Exception
  345. * @throws PreConditionNotMetException
  346. */
  347. public function setValues(string $table, array $keys, array $values, array $updatePreconditionValues = []): int {
  348. try {
  349. $insertQb = $this->getQueryBuilder();
  350. $insertQb->insert($table)
  351. ->values(
  352. array_map(function ($value) use ($insertQb) {
  353. return $insertQb->createNamedParameter($value, $this->getType($value));
  354. }, array_merge($keys, $values))
  355. );
  356. return $insertQb->executeStatement();
  357. } catch (\OCP\DB\Exception $e) {
  358. if (!in_array($e->getReason(), [
  359. \OCP\DB\Exception::REASON_CONSTRAINT_VIOLATION,
  360. \OCP\DB\Exception::REASON_UNIQUE_CONSTRAINT_VIOLATION,
  361. ])
  362. ) {
  363. throw $e;
  364. }
  365. // value already exists, try update
  366. $updateQb = $this->getQueryBuilder();
  367. $updateQb->update($table);
  368. foreach ($values as $name => $value) {
  369. $updateQb->set($name, $updateQb->createNamedParameter($value, $this->getType($value)));
  370. }
  371. $where = $updateQb->expr()->andX();
  372. $whereValues = array_merge($keys, $updatePreconditionValues);
  373. foreach ($whereValues as $name => $value) {
  374. if ($value === '') {
  375. $where->add($updateQb->expr()->emptyString(
  376. $name
  377. ));
  378. } else {
  379. $where->add($updateQb->expr()->eq(
  380. $name,
  381. $updateQb->createNamedParameter($value, $this->getType($value)),
  382. $this->getType($value)
  383. ));
  384. }
  385. }
  386. $updateQb->where($where);
  387. $affected = $updateQb->executeStatement();
  388. if ($affected === 0 && !empty($updatePreconditionValues)) {
  389. throw new PreConditionNotMetException();
  390. }
  391. return 0;
  392. }
  393. }
  394. /**
  395. * Create an exclusive read+write lock on a table
  396. *
  397. * @param string $tableName
  398. *
  399. * @throws \BadMethodCallException When trying to acquire a second lock
  400. * @throws Exception
  401. * @since 9.1.0
  402. */
  403. public function lockTable($tableName) {
  404. if ($this->lockedTable !== null) {
  405. throw new \BadMethodCallException('Can not lock a new table until the previous lock is released.');
  406. }
  407. $tableName = $this->tablePrefix . $tableName;
  408. $this->lockedTable = $tableName;
  409. $this->adapter->lockTable($tableName);
  410. }
  411. /**
  412. * Release a previous acquired lock again
  413. *
  414. * @throws Exception
  415. * @since 9.1.0
  416. */
  417. public function unlockTable() {
  418. $this->adapter->unlockTable();
  419. $this->lockedTable = null;
  420. }
  421. /**
  422. * returns the error code and message as a string for logging
  423. * works with DoctrineException
  424. * @return string
  425. */
  426. public function getError() {
  427. $msg = $this->errorCode() . ': ';
  428. $errorInfo = $this->errorInfo();
  429. if (!empty($errorInfo)) {
  430. $msg .= 'SQLSTATE = '.$errorInfo[0] . ', ';
  431. $msg .= 'Driver Code = '.$errorInfo[1] . ', ';
  432. $msg .= 'Driver Message = '.$errorInfo[2];
  433. }
  434. return $msg;
  435. }
  436. public function errorCode() {
  437. return -1;
  438. }
  439. public function errorInfo() {
  440. return [];
  441. }
  442. /**
  443. * Drop a table from the database if it exists
  444. *
  445. * @param string $table table name without the prefix
  446. *
  447. * @throws Exception
  448. */
  449. public function dropTable($table) {
  450. $table = $this->tablePrefix . trim($table);
  451. $schema = $this->getSchemaManager();
  452. if ($schema->tablesExist([$table])) {
  453. $schema->dropTable($table);
  454. }
  455. }
  456. /**
  457. * Check if a table exists
  458. *
  459. * @param string $table table name without the prefix
  460. *
  461. * @return bool
  462. * @throws Exception
  463. */
  464. public function tableExists($table) {
  465. $table = $this->tablePrefix . trim($table);
  466. $schema = $this->getSchemaManager();
  467. return $schema->tablesExist([$table]);
  468. }
  469. // internal use
  470. /**
  471. * @param string $statement
  472. * @return string
  473. */
  474. protected function replaceTablePrefix($statement) {
  475. return str_replace('*PREFIX*', $this->tablePrefix, $statement);
  476. }
  477. /**
  478. * Check if a transaction is active
  479. *
  480. * @return bool
  481. * @since 8.2.0
  482. */
  483. public function inTransaction() {
  484. return $this->getTransactionNestingLevel() > 0;
  485. }
  486. /**
  487. * Escape a parameter to be used in a LIKE query
  488. *
  489. * @param string $param
  490. * @return string
  491. */
  492. public function escapeLikeParameter($param) {
  493. return addcslashes($param, '\\_%');
  494. }
  495. /**
  496. * Check whether or not the current database support 4byte wide unicode
  497. *
  498. * @return bool
  499. * @since 11.0.0
  500. */
  501. public function supports4ByteText() {
  502. if (!$this->getDatabasePlatform() instanceof MySQLPlatform) {
  503. return true;
  504. }
  505. return $this->getParams()['charset'] === 'utf8mb4';
  506. }
  507. /**
  508. * Create the schema of the connected database
  509. *
  510. * @return Schema
  511. * @throws Exception
  512. */
  513. public function createSchema() {
  514. $migrator = $this->getMigrator();
  515. return $migrator->createSchema();
  516. }
  517. /**
  518. * Migrate the database to the given schema
  519. *
  520. * @param Schema $toSchema
  521. * @param bool $dryRun If true, will return the sql queries instead of running them.
  522. *
  523. * @throws Exception
  524. *
  525. * @return string|null Returns a string only if $dryRun is true.
  526. */
  527. public function migrateToSchema(Schema $toSchema, bool $dryRun = false) {
  528. $migrator = $this->getMigrator();
  529. if ($dryRun) {
  530. return $migrator->generateChangeScript($toSchema);
  531. } else {
  532. $migrator->migrate($toSchema);
  533. }
  534. }
  535. private function getMigrator() {
  536. // TODO properly inject those dependencies
  537. $random = \OC::$server->getSecureRandom();
  538. $platform = $this->getDatabasePlatform();
  539. $config = \OC::$server->getConfig();
  540. $dispatcher = \OC::$server->get(\OCP\EventDispatcher\IEventDispatcher::class);
  541. if ($platform instanceof SqlitePlatform) {
  542. return new SQLiteMigrator($this, $config, $dispatcher);
  543. } elseif ($platform instanceof OraclePlatform) {
  544. return new OracleMigrator($this, $config, $dispatcher);
  545. } elseif ($platform instanceof MySQLPlatform) {
  546. return new MySQLMigrator($this, $config, $dispatcher);
  547. } elseif ($platform instanceof PostgreSQL94Platform) {
  548. return new PostgreSqlMigrator($this, $config, $dispatcher);
  549. } else {
  550. return new Migrator($this, $config, $dispatcher);
  551. }
  552. }
  553. }