Database.php 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505
  1. <?php
  2. /**
  3. * @copyright Copyright (c) 2016, ownCloud, Inc.
  4. *
  5. * @author Christoph Wurst <christoph@winzerhof-wurst.at>
  6. * @author Joas Schilling <coding@schilljs.com>
  7. * @author Johannes Leuker <j.leuker@hosting.de>
  8. * @author John Molakvoæ <skjnldsv@protonmail.com>
  9. * @author Loki3000 <github@labcms.ru>
  10. * @author Morris Jobke <hey@morrisjobke.de>
  11. * @author Roeland Jago Douma <roeland@famdouma.nl>
  12. * @author tgrant <tom.grant760@gmail.com>
  13. * @author Tom Grant <TomG736@users.noreply.github.com>
  14. *
  15. * @license AGPL-3.0
  16. *
  17. * This code is free software: you can redistribute it and/or modify
  18. * it under the terms of the GNU Affero General Public License, version 3,
  19. * as published by the Free Software Foundation.
  20. *
  21. * This program is distributed in the hope that it will be useful,
  22. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  23. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  24. * GNU Affero General Public License for more details.
  25. *
  26. * You should have received a copy of the GNU Affero General Public License, version 3,
  27. * along with this program. If not, see <http://www.gnu.org/licenses/>
  28. *
  29. */
  30. namespace OC\Group;
  31. use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
  32. use OCP\DB\QueryBuilder\IQueryBuilder;
  33. use OCP\Group\Backend\ABackend;
  34. use OCP\Group\Backend\IAddToGroupBackend;
  35. use OCP\Group\Backend\ICountDisabledInGroup;
  36. use OCP\Group\Backend\ICountUsersBackend;
  37. use OCP\Group\Backend\ICreateGroupBackend;
  38. use OCP\Group\Backend\IDeleteGroupBackend;
  39. use OCP\Group\Backend\IGetDisplayNameBackend;
  40. use OCP\Group\Backend\IGroupDetailsBackend;
  41. use OCP\Group\Backend\IRemoveFromGroupBackend;
  42. use OCP\Group\Backend\ISetDisplayNameBackend;
  43. use OCP\Group\Backend\INamedBackend;
  44. use OCP\IDBConnection;
  45. /**
  46. * Class for group management in a SQL Database (e.g. MySQL, SQLite)
  47. */
  48. class Database extends ABackend implements
  49. IAddToGroupBackend,
  50. ICountDisabledInGroup,
  51. ICountUsersBackend,
  52. ICreateGroupBackend,
  53. IDeleteGroupBackend,
  54. IGetDisplayNameBackend,
  55. IGroupDetailsBackend,
  56. IRemoveFromGroupBackend,
  57. ISetDisplayNameBackend,
  58. INamedBackend {
  59. /** @var string[] */
  60. private $groupCache = [];
  61. /** @var IDBConnection */
  62. private $dbConn;
  63. /**
  64. * \OC\Group\Database constructor.
  65. *
  66. * @param IDBConnection|null $dbConn
  67. */
  68. public function __construct(IDBConnection $dbConn = null) {
  69. $this->dbConn = $dbConn;
  70. }
  71. /**
  72. * FIXME: This function should not be required!
  73. */
  74. private function fixDI() {
  75. if ($this->dbConn === null) {
  76. $this->dbConn = \OC::$server->getDatabaseConnection();
  77. }
  78. }
  79. /**
  80. * Try to create a new group
  81. * @param string $gid The name of the group to create
  82. * @return bool
  83. *
  84. * Tries to create a new group. If the group name already exists, false will
  85. * be returned.
  86. */
  87. public function createGroup(string $gid): bool {
  88. $this->fixDI();
  89. try {
  90. // Add group
  91. $builder = $this->dbConn->getQueryBuilder();
  92. $result = $builder->insert('groups')
  93. ->setValue('gid', $builder->createNamedParameter($gid))
  94. ->setValue('displayname', $builder->createNamedParameter($gid))
  95. ->execute();
  96. } catch (UniqueConstraintViolationException $e) {
  97. $result = 0;
  98. }
  99. // Add to cache
  100. $this->groupCache[$gid] = [
  101. 'gid' => $gid,
  102. 'displayname' => $gid
  103. ];
  104. return $result === 1;
  105. }
  106. /**
  107. * delete a group
  108. * @param string $gid gid of the group to delete
  109. * @return bool
  110. *
  111. * Deletes a group and removes it from the group_user-table
  112. */
  113. public function deleteGroup(string $gid): bool {
  114. $this->fixDI();
  115. // Delete the group
  116. $qb = $this->dbConn->getQueryBuilder();
  117. $qb->delete('groups')
  118. ->where($qb->expr()->eq('gid', $qb->createNamedParameter($gid)))
  119. ->execute();
  120. // Delete the group-user relation
  121. $qb = $this->dbConn->getQueryBuilder();
  122. $qb->delete('group_user')
  123. ->where($qb->expr()->eq('gid', $qb->createNamedParameter($gid)))
  124. ->execute();
  125. // Delete the group-groupadmin relation
  126. $qb = $this->dbConn->getQueryBuilder();
  127. $qb->delete('group_admin')
  128. ->where($qb->expr()->eq('gid', $qb->createNamedParameter($gid)))
  129. ->execute();
  130. // Delete from cache
  131. unset($this->groupCache[$gid]);
  132. return true;
  133. }
  134. /**
  135. * is user in group?
  136. * @param string $uid uid of the user
  137. * @param string $gid gid of the group
  138. * @return bool
  139. *
  140. * Checks whether the user is member of a group or not.
  141. */
  142. public function inGroup($uid, $gid) {
  143. $this->fixDI();
  144. // check
  145. $qb = $this->dbConn->getQueryBuilder();
  146. $cursor = $qb->select('uid')
  147. ->from('group_user')
  148. ->where($qb->expr()->eq('gid', $qb->createNamedParameter($gid)))
  149. ->andWhere($qb->expr()->eq('uid', $qb->createNamedParameter($uid)))
  150. ->execute();
  151. $result = $cursor->fetch();
  152. $cursor->closeCursor();
  153. return $result ? true : false;
  154. }
  155. /**
  156. * Add a user to a group
  157. * @param string $uid Name of the user to add to group
  158. * @param string $gid Name of the group in which add the user
  159. * @return bool
  160. *
  161. * Adds a user to a group.
  162. */
  163. public function addToGroup(string $uid, string $gid): bool {
  164. $this->fixDI();
  165. // No duplicate entries!
  166. if (!$this->inGroup($uid, $gid)) {
  167. $qb = $this->dbConn->getQueryBuilder();
  168. $qb->insert('group_user')
  169. ->setValue('uid', $qb->createNamedParameter($uid))
  170. ->setValue('gid', $qb->createNamedParameter($gid))
  171. ->execute();
  172. return true;
  173. } else {
  174. return false;
  175. }
  176. }
  177. /**
  178. * Removes a user from a group
  179. * @param string $uid Name of the user to remove from group
  180. * @param string $gid Name of the group from which remove the user
  181. * @return bool
  182. *
  183. * removes the user from a group.
  184. */
  185. public function removeFromGroup(string $uid, string $gid): bool {
  186. $this->fixDI();
  187. $qb = $this->dbConn->getQueryBuilder();
  188. $qb->delete('group_user')
  189. ->where($qb->expr()->eq('uid', $qb->createNamedParameter($uid)))
  190. ->andWhere($qb->expr()->eq('gid', $qb->createNamedParameter($gid)))
  191. ->execute();
  192. return true;
  193. }
  194. /**
  195. * Get all groups a user belongs to
  196. * @param string $uid Name of the user
  197. * @return array an array of group names
  198. *
  199. * This function fetches all groups a user belongs to. It does not check
  200. * if the user exists at all.
  201. */
  202. public function getUserGroups($uid) {
  203. //guests has empty or null $uid
  204. if ($uid === null || $uid === '') {
  205. return [];
  206. }
  207. $this->fixDI();
  208. // No magic!
  209. $qb = $this->dbConn->getQueryBuilder();
  210. $cursor = $qb->select('gu.gid', 'g.displayname')
  211. ->from('group_user', 'gu')
  212. ->leftJoin('gu', 'groups', 'g', $qb->expr()->eq('gu.gid', 'g.gid'))
  213. ->where($qb->expr()->eq('uid', $qb->createNamedParameter($uid)))
  214. ->execute();
  215. $groups = [];
  216. while ($row = $cursor->fetch()) {
  217. $groups[] = $row['gid'];
  218. $this->groupCache[$row['gid']] = [
  219. 'gid' => $row['gid'],
  220. 'displayname' => $row['displayname'],
  221. ];
  222. }
  223. $cursor->closeCursor();
  224. return $groups;
  225. }
  226. /**
  227. * get a list of all groups
  228. * @param string $search
  229. * @param int $limit
  230. * @param int $offset
  231. * @return array an array of group names
  232. *
  233. * Returns a list with all groups
  234. */
  235. public function getGroups($search = '', $limit = null, $offset = null) {
  236. $this->fixDI();
  237. $query = $this->dbConn->getQueryBuilder();
  238. $query->select('gid')
  239. ->from('groups')
  240. ->orderBy('gid', 'ASC');
  241. if ($search !== '') {
  242. $query->where($query->expr()->iLike('gid', $query->createNamedParameter(
  243. '%' . $this->dbConn->escapeLikeParameter($search) . '%'
  244. )));
  245. $query->orWhere($query->expr()->iLike('displayname', $query->createNamedParameter(
  246. '%' . $this->dbConn->escapeLikeParameter($search) . '%'
  247. )));
  248. }
  249. $query->setMaxResults($limit)
  250. ->setFirstResult($offset);
  251. $result = $query->execute();
  252. $groups = [];
  253. while ($row = $result->fetch()) {
  254. $groups[] = $row['gid'];
  255. }
  256. $result->closeCursor();
  257. return $groups;
  258. }
  259. /**
  260. * check if a group exists
  261. * @param string $gid
  262. * @return bool
  263. */
  264. public function groupExists($gid) {
  265. $this->fixDI();
  266. // Check cache first
  267. if (isset($this->groupCache[$gid])) {
  268. return true;
  269. }
  270. $qb = $this->dbConn->getQueryBuilder();
  271. $cursor = $qb->select('gid', 'displayname')
  272. ->from('groups')
  273. ->where($qb->expr()->eq('gid', $qb->createNamedParameter($gid)))
  274. ->execute();
  275. $result = $cursor->fetch();
  276. $cursor->closeCursor();
  277. if ($result !== false) {
  278. $this->groupCache[$gid] = [
  279. 'gid' => $gid,
  280. 'displayname' => $result['displayname'],
  281. ];
  282. return true;
  283. }
  284. return false;
  285. }
  286. /**
  287. * get a list of all users in a group
  288. * @param string $gid
  289. * @param string $search
  290. * @param int $limit
  291. * @param int $offset
  292. * @return array an array of user ids
  293. */
  294. public function usersInGroup($gid, $search = '', $limit = -1, $offset = 0) {
  295. $this->fixDI();
  296. $query = $this->dbConn->getQueryBuilder();
  297. $query->select('g.uid')
  298. ->from('group_user', 'g')
  299. ->where($query->expr()->eq('gid', $query->createNamedParameter($gid)))
  300. ->orderBy('g.uid', 'ASC');
  301. if ($search !== '') {
  302. $query->leftJoin('g', 'users', 'u', $query->expr()->eq('g.uid', 'u.uid'))
  303. ->leftJoin('u', 'preferences', 'p', $query->expr()->andX(
  304. $query->expr()->eq('p.userid', 'u.uid'),
  305. $query->expr()->eq('p.appid', $query->expr()->literal('settings')),
  306. $query->expr()->eq('p.configkey', $query->expr()->literal('email')))
  307. )
  308. // sqlite doesn't like re-using a single named parameter here
  309. ->andWhere(
  310. $query->expr()->orX(
  311. $query->expr()->ilike('g.uid', $query->createNamedParameter('%' . $this->dbConn->escapeLikeParameter($search) . '%')),
  312. $query->expr()->ilike('u.displayname', $query->createNamedParameter('%' . $this->dbConn->escapeLikeParameter($search) . '%')),
  313. $query->expr()->ilike('p.configvalue', $query->createNamedParameter('%' . $this->dbConn->escapeLikeParameter($search) . '%'))
  314. )
  315. )
  316. ->orderBy('u.uid_lower', 'ASC');
  317. }
  318. if ($limit !== -1) {
  319. $query->setMaxResults($limit);
  320. }
  321. if ($offset !== 0) {
  322. $query->setFirstResult($offset);
  323. }
  324. $result = $query->execute();
  325. $users = [];
  326. while ($row = $result->fetch()) {
  327. $users[] = $row['uid'];
  328. }
  329. $result->closeCursor();
  330. return $users;
  331. }
  332. /**
  333. * get the number of all users matching the search string in a group
  334. * @param string $gid
  335. * @param string $search
  336. * @return int
  337. */
  338. public function countUsersInGroup(string $gid, string $search = ''): int {
  339. $this->fixDI();
  340. $query = $this->dbConn->getQueryBuilder();
  341. $query->select($query->func()->count('*', 'num_users'))
  342. ->from('group_user')
  343. ->where($query->expr()->eq('gid', $query->createNamedParameter($gid)));
  344. if ($search !== '') {
  345. $query->andWhere($query->expr()->like('uid', $query->createNamedParameter(
  346. '%' . $this->dbConn->escapeLikeParameter($search) . '%'
  347. )));
  348. }
  349. $result = $query->execute();
  350. $count = $result->fetchOne();
  351. $result->closeCursor();
  352. if ($count !== false) {
  353. $count = (int)$count;
  354. } else {
  355. $count = 0;
  356. }
  357. return $count;
  358. }
  359. /**
  360. * get the number of disabled users in a group
  361. *
  362. * @param string $search
  363. *
  364. * @return int
  365. */
  366. public function countDisabledInGroup(string $gid): int {
  367. $this->fixDI();
  368. $query = $this->dbConn->getQueryBuilder();
  369. $query->select($query->createFunction('COUNT(DISTINCT ' . $query->getColumnName('uid') . ')'))
  370. ->from('preferences', 'p')
  371. ->innerJoin('p', 'group_user', 'g', $query->expr()->eq('p.userid', 'g.uid'))
  372. ->where($query->expr()->eq('appid', $query->createNamedParameter('core')))
  373. ->andWhere($query->expr()->eq('configkey', $query->createNamedParameter('enabled')))
  374. ->andWhere($query->expr()->eq('configvalue', $query->createNamedParameter('false'), IQueryBuilder::PARAM_STR))
  375. ->andWhere($query->expr()->eq('gid', $query->createNamedParameter($gid), IQueryBuilder::PARAM_STR));
  376. $result = $query->execute();
  377. $count = $result->fetchOne();
  378. $result->closeCursor();
  379. if ($count !== false) {
  380. $count = (int)$count;
  381. } else {
  382. $count = 0;
  383. }
  384. return $count;
  385. }
  386. public function getDisplayName(string $gid): string {
  387. if (isset($this->groupCache[$gid])) {
  388. $displayName = $this->groupCache[$gid]['displayname'];
  389. if (isset($displayName) && trim($displayName) !== '') {
  390. return $displayName;
  391. }
  392. }
  393. $this->fixDI();
  394. $query = $this->dbConn->getQueryBuilder();
  395. $query->select('displayname')
  396. ->from('groups')
  397. ->where($query->expr()->eq('gid', $query->createNamedParameter($gid)));
  398. $result = $query->execute();
  399. $displayName = $result->fetchOne();
  400. $result->closeCursor();
  401. return (string) $displayName;
  402. }
  403. public function getGroupDetails(string $gid): array {
  404. $displayName = $this->getDisplayName($gid);
  405. if ($displayName !== '') {
  406. return ['displayName' => $displayName];
  407. }
  408. return [];
  409. }
  410. public function setDisplayName(string $gid, string $displayName): bool {
  411. if (!$this->groupExists($gid)) {
  412. return false;
  413. }
  414. $this->fixDI();
  415. $displayName = trim($displayName);
  416. if ($displayName === '') {
  417. $displayName = $gid;
  418. }
  419. $query = $this->dbConn->getQueryBuilder();
  420. $query->update('groups')
  421. ->set('displayname', $query->createNamedParameter($displayName))
  422. ->where($query->expr()->eq('gid', $query->createNamedParameter($gid)));
  423. $query->execute();
  424. return true;
  425. }
  426. /**
  427. * Backend name to be shown in group management
  428. * @return string the name of the backend to be shown
  429. * @since 21.0.0
  430. */
  431. public function getBackendName(): string {
  432. return 'Database';
  433. }
  434. }