DBConfigService.php 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531
  1. <?php
  2. /**
  3. * @copyright Copyright (c) 2016, ownCloud, Inc.
  4. *
  5. * @author Arthur Schiwon <blizzz@arthur-schiwon.de>
  6. * @author Christoph Wurst <christoph@winzerhof-wurst.at>
  7. * @author Joas Schilling <coding@schilljs.com>
  8. * @author Morris Jobke <hey@morrisjobke.de>
  9. * @author Robin Appelman <robin@icewind.nl>
  10. * @author Robin McCorkell <robin@mccorkell.me.uk>
  11. * @author Roeland Jago Douma <roeland@famdouma.nl>
  12. *
  13. * @license AGPL-3.0
  14. *
  15. * This code is free software: you can redistribute it and/or modify
  16. * it under the terms of the GNU Affero General Public License, version 3,
  17. * as published by the Free Software Foundation.
  18. *
  19. * This program is distributed in the hope that it will be useful,
  20. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  21. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  22. * GNU Affero General Public License for more details.
  23. *
  24. * You should have received a copy of the GNU Affero General Public License, version 3,
  25. * along with this program. If not, see <http://www.gnu.org/licenses/>
  26. *
  27. */
  28. namespace OCA\Files_External\Service;
  29. use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
  30. use OCP\DB\QueryBuilder\IQueryBuilder;
  31. use OCP\IDBConnection;
  32. use OCP\Security\ICrypto;
  33. /**
  34. * Stores the mount config in the database
  35. */
  36. class DBConfigService {
  37. public const MOUNT_TYPE_ADMIN = 1;
  38. public const MOUNT_TYPE_PERSONAl = 2;
  39. public const APPLICABLE_TYPE_GLOBAL = 1;
  40. public const APPLICABLE_TYPE_GROUP = 2;
  41. public const APPLICABLE_TYPE_USER = 3;
  42. /**
  43. * @var IDBConnection
  44. */
  45. private $connection;
  46. /**
  47. * @var ICrypto
  48. */
  49. private $crypto;
  50. /**
  51. * DBConfigService constructor.
  52. *
  53. * @param IDBConnection $connection
  54. * @param ICrypto $crypto
  55. */
  56. public function __construct(IDBConnection $connection, ICrypto $crypto) {
  57. $this->connection = $connection;
  58. $this->crypto = $crypto;
  59. }
  60. public function getMountById(int $mountId): ?array {
  61. $builder = $this->connection->getQueryBuilder();
  62. $query = $builder->select(['mount_id', 'mount_point', 'storage_backend', 'auth_backend', 'priority', 'type'])
  63. ->from('external_mounts', 'm')
  64. ->where($builder->expr()->eq('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT)));
  65. $mounts = $this->getMountsFromQuery($query);
  66. if (count($mounts) > 0) {
  67. return $mounts[0];
  68. } else {
  69. return null;
  70. }
  71. }
  72. /**
  73. * Get all configured mounts
  74. *
  75. * @return array
  76. */
  77. public function getAllMounts() {
  78. $builder = $this->connection->getQueryBuilder();
  79. $query = $builder->select(['mount_id', 'mount_point', 'storage_backend', 'auth_backend', 'priority', 'type'])
  80. ->from('external_mounts');
  81. return $this->getMountsFromQuery($query);
  82. }
  83. public function getMountsForUser($userId, $groupIds) {
  84. $builder = $this->connection->getQueryBuilder();
  85. $query = $builder->select(['m.mount_id', 'mount_point', 'storage_backend', 'auth_backend', 'priority', 'm.type'])
  86. ->from('external_mounts', 'm')
  87. ->innerJoin('m', 'external_applicable', 'a', $builder->expr()->eq('m.mount_id', 'a.mount_id'))
  88. ->where($builder->expr()->orX(
  89. $builder->expr()->andX( // global mounts
  90. $builder->expr()->eq('a.type', $builder->createNamedParameter(self::APPLICABLE_TYPE_GLOBAL, IQueryBuilder::PARAM_INT)),
  91. $builder->expr()->isNull('a.value')
  92. ),
  93. $builder->expr()->andX( // mounts for user
  94. $builder->expr()->eq('a.type', $builder->createNamedParameter(self::APPLICABLE_TYPE_USER, IQueryBuilder::PARAM_INT)),
  95. $builder->expr()->eq('a.value', $builder->createNamedParameter($userId))
  96. ),
  97. $builder->expr()->andX( // mounts for group
  98. $builder->expr()->eq('a.type', $builder->createNamedParameter(self::APPLICABLE_TYPE_GROUP, IQueryBuilder::PARAM_INT)),
  99. $builder->expr()->in('a.value', $builder->createNamedParameter($groupIds, IQueryBuilder::PARAM_STR_ARRAY))
  100. )
  101. ));
  102. return $this->getMountsFromQuery($query);
  103. }
  104. public function modifyMountsOnUserDelete(string $uid): void {
  105. $this->modifyMountsOnDelete($uid, self::APPLICABLE_TYPE_USER);
  106. }
  107. public function modifyMountsOnGroupDelete(string $gid): void {
  108. $this->modifyMountsOnDelete($gid, self::APPLICABLE_TYPE_GROUP);
  109. }
  110. protected function modifyMountsOnDelete(string $applicableId, int $applicableType): void {
  111. $builder = $this->connection->getQueryBuilder();
  112. $query = $builder->select(['a.mount_id', $builder->func()->count('a.mount_id', 'count')])
  113. ->from('external_applicable', 'a')
  114. ->leftJoin('a', 'external_applicable', 'b', $builder->expr()->eq('a.mount_id', 'b.mount_id'))
  115. ->where($builder->expr()->andX(
  116. $builder->expr()->eq('b.type', $builder->createNamedParameter($applicableType, IQueryBuilder::PARAM_INT)),
  117. $builder->expr()->eq('b.value', $builder->createNamedParameter($applicableId))
  118. )
  119. )
  120. ->groupBy(['a.mount_id']);
  121. $stmt = $query->execute();
  122. $result = $stmt->fetchAll();
  123. $stmt->closeCursor();
  124. foreach ($result as $row) {
  125. if ((int)$row['count'] > 1) {
  126. $this->removeApplicable($row['mount_id'], $applicableType, $applicableId);
  127. } else {
  128. $this->removeMount($row['mount_id']);
  129. }
  130. }
  131. }
  132. /**
  133. * Get admin defined mounts
  134. *
  135. * @return array
  136. */
  137. public function getAdminMounts() {
  138. $builder = $this->connection->getQueryBuilder();
  139. $query = $builder->select(['mount_id', 'mount_point', 'storage_backend', 'auth_backend', 'priority', 'type'])
  140. ->from('external_mounts')
  141. ->where($builder->expr()->eq('type', $builder->expr()->literal(self::MOUNT_TYPE_ADMIN, IQueryBuilder::PARAM_INT)));
  142. return $this->getMountsFromQuery($query);
  143. }
  144. protected function getForQuery(IQueryBuilder $builder, $type, $value) {
  145. $query = $builder->select(['m.mount_id', 'mount_point', 'storage_backend', 'auth_backend', 'priority', 'm.type'])
  146. ->from('external_mounts', 'm')
  147. ->innerJoin('m', 'external_applicable', 'a', $builder->expr()->eq('m.mount_id', 'a.mount_id'))
  148. ->where($builder->expr()->eq('a.type', $builder->createNamedParameter($type, IQueryBuilder::PARAM_INT)));
  149. if (is_null($value)) {
  150. $query = $query->andWhere($builder->expr()->isNull('a.value'));
  151. } else {
  152. $query = $query->andWhere($builder->expr()->eq('a.value', $builder->createNamedParameter($value)));
  153. }
  154. return $query;
  155. }
  156. /**
  157. * Get mounts by applicable
  158. *
  159. * @param int $type any of the self::APPLICABLE_TYPE_ constants
  160. * @param string|null $value user_id, group_id or null for global mounts
  161. * @return array
  162. */
  163. public function getMountsFor($type, $value) {
  164. $builder = $this->connection->getQueryBuilder();
  165. $query = $this->getForQuery($builder, $type, $value);
  166. return $this->getMountsFromQuery($query);
  167. }
  168. /**
  169. * Get admin defined mounts by applicable
  170. *
  171. * @param int $type any of the self::APPLICABLE_TYPE_ constants
  172. * @param string|null $value user_id, group_id or null for global mounts
  173. * @return array
  174. */
  175. public function getAdminMountsFor($type, $value) {
  176. $builder = $this->connection->getQueryBuilder();
  177. $query = $this->getForQuery($builder, $type, $value);
  178. $query->andWhere($builder->expr()->eq('m.type', $builder->expr()->literal(self::MOUNT_TYPE_ADMIN, IQueryBuilder::PARAM_INT)));
  179. return $this->getMountsFromQuery($query);
  180. }
  181. /**
  182. * Get admin defined mounts for multiple applicable
  183. *
  184. * @param int $type any of the self::APPLICABLE_TYPE_ constants
  185. * @param string[] $values user_ids or group_ids
  186. * @return array
  187. */
  188. public function getAdminMountsForMultiple($type, array $values) {
  189. $builder = $this->connection->getQueryBuilder();
  190. $params = array_map(function ($value) use ($builder) {
  191. return $builder->createNamedParameter($value, IQueryBuilder::PARAM_STR);
  192. }, $values);
  193. $query = $builder->select(['m.mount_id', 'mount_point', 'storage_backend', 'auth_backend', 'priority', 'm.type'])
  194. ->from('external_mounts', 'm')
  195. ->innerJoin('m', 'external_applicable', 'a', $builder->expr()->eq('m.mount_id', 'a.mount_id'))
  196. ->where($builder->expr()->eq('a.type', $builder->createNamedParameter($type, IQueryBuilder::PARAM_INT)))
  197. ->andWhere($builder->expr()->in('a.value', $params));
  198. $query->andWhere($builder->expr()->eq('m.type', $builder->expr()->literal(self::MOUNT_TYPE_ADMIN, IQueryBuilder::PARAM_INT)));
  199. return $this->getMountsFromQuery($query);
  200. }
  201. /**
  202. * Get user defined mounts by applicable
  203. *
  204. * @param int $type any of the self::APPLICABLE_TYPE_ constants
  205. * @param string|null $value user_id, group_id or null for global mounts
  206. * @return array
  207. */
  208. public function getUserMountsFor($type, $value) {
  209. $builder = $this->connection->getQueryBuilder();
  210. $query = $this->getForQuery($builder, $type, $value);
  211. $query->andWhere($builder->expr()->eq('m.type', $builder->expr()->literal(self::MOUNT_TYPE_PERSONAl, IQueryBuilder::PARAM_INT)));
  212. return $this->getMountsFromQuery($query);
  213. }
  214. /**
  215. * Add a mount to the database
  216. *
  217. * @param string $mountPoint
  218. * @param string $storageBackend
  219. * @param string $authBackend
  220. * @param int $priority
  221. * @param int $type self::MOUNT_TYPE_ADMIN or self::MOUNT_TYPE_PERSONAL
  222. * @return int the id of the new mount
  223. */
  224. public function addMount($mountPoint, $storageBackend, $authBackend, $priority, $type) {
  225. if (!$priority) {
  226. $priority = 100;
  227. }
  228. $builder = $this->connection->getQueryBuilder();
  229. $query = $builder->insert('external_mounts')
  230. ->values([
  231. 'mount_point' => $builder->createNamedParameter($mountPoint, IQueryBuilder::PARAM_STR),
  232. 'storage_backend' => $builder->createNamedParameter($storageBackend, IQueryBuilder::PARAM_STR),
  233. 'auth_backend' => $builder->createNamedParameter($authBackend, IQueryBuilder::PARAM_STR),
  234. 'priority' => $builder->createNamedParameter($priority, IQueryBuilder::PARAM_INT),
  235. 'type' => $builder->createNamedParameter($type, IQueryBuilder::PARAM_INT)
  236. ]);
  237. $query->execute();
  238. return $query->getLastInsertId();
  239. }
  240. /**
  241. * Remove a mount from the database
  242. *
  243. * @param int $mountId
  244. */
  245. public function removeMount($mountId) {
  246. $builder = $this->connection->getQueryBuilder();
  247. $query = $builder->delete('external_mounts')
  248. ->where($builder->expr()->eq('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT)));
  249. $query->execute();
  250. $query = $builder->delete('external_applicable')
  251. ->where($builder->expr()->eq('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT)));
  252. $query->execute();
  253. $query = $builder->delete('external_config')
  254. ->where($builder->expr()->eq('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT)));
  255. $query->execute();
  256. $query = $builder->delete('external_options')
  257. ->where($builder->expr()->eq('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT)));
  258. $query->execute();
  259. }
  260. /**
  261. * @param int $mountId
  262. * @param string $newMountPoint
  263. */
  264. public function setMountPoint($mountId, $newMountPoint) {
  265. $builder = $this->connection->getQueryBuilder();
  266. $query = $builder->update('external_mounts')
  267. ->set('mount_point', $builder->createNamedParameter($newMountPoint))
  268. ->where($builder->expr()->eq('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT)));
  269. $query->execute();
  270. }
  271. /**
  272. * @param int $mountId
  273. * @param string $newAuthBackend
  274. */
  275. public function setAuthBackend($mountId, $newAuthBackend) {
  276. $builder = $this->connection->getQueryBuilder();
  277. $query = $builder->update('external_mounts')
  278. ->set('auth_backend', $builder->createNamedParameter($newAuthBackend))
  279. ->where($builder->expr()->eq('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT)));
  280. $query->execute();
  281. }
  282. /**
  283. * @param int $mountId
  284. * @param string $key
  285. * @param string $value
  286. */
  287. public function setConfig($mountId, $key, $value) {
  288. if ($key === 'password') {
  289. $value = $this->encryptValue($value);
  290. }
  291. try {
  292. $builder = $this->connection->getQueryBuilder();
  293. $builder->insert('external_config')
  294. ->setValue('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT))
  295. ->setValue('key', $builder->createNamedParameter($key, IQueryBuilder::PARAM_STR))
  296. ->setValue('value', $builder->createNamedParameter($value, IQueryBuilder::PARAM_STR))
  297. ->execute();
  298. } catch (UniqueConstraintViolationException $e) {
  299. $builder = $this->connection->getQueryBuilder();
  300. $query = $builder->update('external_config')
  301. ->set('value', $builder->createNamedParameter($value, IQueryBuilder::PARAM_STR))
  302. ->where($builder->expr()->eq('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT)))
  303. ->andWhere($builder->expr()->eq('key', $builder->createNamedParameter($key, IQueryBuilder::PARAM_STR)));
  304. $query->execute();
  305. }
  306. }
  307. /**
  308. * @param int $mountId
  309. * @param string $key
  310. * @param string $value
  311. */
  312. public function setOption($mountId, $key, $value) {
  313. try {
  314. $builder = $this->connection->getQueryBuilder();
  315. $builder->insert('external_options')
  316. ->setValue('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT))
  317. ->setValue('key', $builder->createNamedParameter($key, IQueryBuilder::PARAM_STR))
  318. ->setValue('value', $builder->createNamedParameter(json_encode($value), IQueryBuilder::PARAM_STR))
  319. ->execute();
  320. } catch (UniqueConstraintViolationException $e) {
  321. $builder = $this->connection->getQueryBuilder();
  322. $query = $builder->update('external_options')
  323. ->set('value', $builder->createNamedParameter(json_encode($value), IQueryBuilder::PARAM_STR))
  324. ->where($builder->expr()->eq('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT)))
  325. ->andWhere($builder->expr()->eq('key', $builder->createNamedParameter($key, IQueryBuilder::PARAM_STR)));
  326. $query->execute();
  327. }
  328. }
  329. public function addApplicable($mountId, $type, $value) {
  330. try {
  331. $builder = $this->connection->getQueryBuilder();
  332. $builder->insert('external_applicable')
  333. ->setValue('mount_id', $builder->createNamedParameter($mountId))
  334. ->setValue('type', $builder->createNamedParameter($type))
  335. ->setValue('value', $builder->createNamedParameter($value))
  336. ->execute();
  337. } catch (UniqueConstraintViolationException $e) {
  338. // applicable exists already
  339. }
  340. }
  341. public function removeApplicable($mountId, $type, $value) {
  342. $builder = $this->connection->getQueryBuilder();
  343. $query = $builder->delete('external_applicable')
  344. ->where($builder->expr()->eq('mount_id', $builder->createNamedParameter($mountId, IQueryBuilder::PARAM_INT)))
  345. ->andWhere($builder->expr()->eq('type', $builder->createNamedParameter($type, IQueryBuilder::PARAM_INT)));
  346. if (is_null($value)) {
  347. $query = $query->andWhere($builder->expr()->isNull('value'));
  348. } else {
  349. $query = $query->andWhere($builder->expr()->eq('value', $builder->createNamedParameter($value, IQueryBuilder::PARAM_STR)));
  350. }
  351. $query->execute();
  352. }
  353. private function getMountsFromQuery(IQueryBuilder $query) {
  354. $result = $query->execute();
  355. $mounts = $result->fetchAll();
  356. $uniqueMounts = [];
  357. foreach ($mounts as $mount) {
  358. $id = $mount['mount_id'];
  359. if (!isset($uniqueMounts[$id])) {
  360. $uniqueMounts[$id] = $mount;
  361. }
  362. }
  363. $uniqueMounts = array_values($uniqueMounts);
  364. $mountIds = array_map(function ($mount) {
  365. return $mount['mount_id'];
  366. }, $uniqueMounts);
  367. $mountIds = array_values(array_unique($mountIds));
  368. $applicable = $this->getApplicableForMounts($mountIds);
  369. $config = $this->getConfigForMounts($mountIds);
  370. $options = $this->getOptionsForMounts($mountIds);
  371. return array_map(function ($mount, $applicable, $config, $options) {
  372. $mount['type'] = (int)$mount['type'];
  373. $mount['priority'] = (int)$mount['priority'];
  374. $mount['applicable'] = $applicable;
  375. $mount['config'] = $config;
  376. $mount['options'] = $options;
  377. return $mount;
  378. }, $uniqueMounts, $applicable, $config, $options);
  379. }
  380. /**
  381. * Get mount options from a table grouped by mount id
  382. *
  383. * @param string $table
  384. * @param string[] $fields
  385. * @param int[] $mountIds
  386. * @return array [$mountId => [['field1' => $value1, ...], ...], ...]
  387. */
  388. private function selectForMounts($table, array $fields, array $mountIds) {
  389. if (count($mountIds) === 0) {
  390. return [];
  391. }
  392. $builder = $this->connection->getQueryBuilder();
  393. $fields[] = 'mount_id';
  394. $placeHolders = array_map(function ($id) use ($builder) {
  395. return $builder->createPositionalParameter($id, IQueryBuilder::PARAM_INT);
  396. }, $mountIds);
  397. $query = $builder->select($fields)
  398. ->from($table)
  399. ->where($builder->expr()->in('mount_id', $placeHolders));
  400. $result = $query->execute();
  401. $rows = $result->fetchAll();
  402. $result->closeCursor();
  403. $result = [];
  404. foreach ($mountIds as $mountId) {
  405. $result[$mountId] = [];
  406. }
  407. foreach ($rows as $row) {
  408. if (isset($row['type'])) {
  409. $row['type'] = (int)$row['type'];
  410. }
  411. $result[$row['mount_id']][] = $row;
  412. }
  413. return $result;
  414. }
  415. /**
  416. * @param int[] $mountIds
  417. * @return array [$id => [['type' => $type, 'value' => $value], ...], ...]
  418. */
  419. public function getApplicableForMounts($mountIds) {
  420. return $this->selectForMounts('external_applicable', ['type', 'value'], $mountIds);
  421. }
  422. /**
  423. * @param int[] $mountIds
  424. * @return array [$id => ['key1' => $value1, ...], ...]
  425. */
  426. public function getConfigForMounts($mountIds) {
  427. $mountConfigs = $this->selectForMounts('external_config', ['key', 'value'], $mountIds);
  428. return array_map([$this, 'createKeyValueMap'], $mountConfigs);
  429. }
  430. /**
  431. * @param int[] $mountIds
  432. * @return array [$id => ['key1' => $value1, ...], ...]
  433. */
  434. public function getOptionsForMounts($mountIds) {
  435. $mountOptions = $this->selectForMounts('external_options', ['key', 'value'], $mountIds);
  436. $optionsMap = array_map([$this, 'createKeyValueMap'], $mountOptions);
  437. return array_map(function (array $options) {
  438. return array_map(function ($option) {
  439. return json_decode($option);
  440. }, $options);
  441. }, $optionsMap);
  442. }
  443. /**
  444. * @param array $keyValuePairs [['key'=>$key, 'value=>$value], ...]
  445. * @return array ['key1' => $value1, ...]
  446. */
  447. private function createKeyValueMap(array $keyValuePairs) {
  448. $decryptedPairts = array_map(function ($pair) {
  449. if ($pair['key'] === 'password') {
  450. $pair['value'] = $this->decryptValue($pair['value']);
  451. }
  452. return $pair;
  453. }, $keyValuePairs);
  454. $keys = array_map(function ($pair) {
  455. return $pair['key'];
  456. }, $decryptedPairts);
  457. $values = array_map(function ($pair) {
  458. return $pair['value'];
  459. }, $decryptedPairts);
  460. return array_combine($keys, $values);
  461. }
  462. private function encryptValue($value) {
  463. return $this->crypto->encrypt($value);
  464. }
  465. private function decryptValue($value) {
  466. try {
  467. return $this->crypto->decrypt($value);
  468. } catch (\Exception $e) {
  469. return $value;
  470. }
  471. }
  472. }