1
0

UserStatusMapper.php 6.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212
  1. <?php
  2. declare(strict_types=1);
  3. /**
  4. * @copyright Copyright (c) 2020, Georg Ehrke
  5. *
  6. * @author Georg Ehrke <oc.list@georgehrke.com>
  7. *
  8. * @license GNU AGPL version 3 or any later version
  9. *
  10. * This program is free software: you can redistribute it and/or modify
  11. * it under the terms of the GNU Affero General Public License as
  12. * published by the Free Software Foundation, either version 3 of the
  13. * License, or (at your option) any later version.
  14. *
  15. * This program is distributed in the hope that it will be useful,
  16. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  17. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  18. * GNU Affero General Public License for more details.
  19. *
  20. * You should have received a copy of the GNU Affero General Public License
  21. * along with this program. If not, see <http://www.gnu.org/licenses/>.
  22. *
  23. */
  24. namespace OCA\UserStatus\Db;
  25. use OCP\AppFramework\Db\QBMapper;
  26. use OCP\DB\QueryBuilder\IQueryBuilder;
  27. use OCP\IDBConnection;
  28. use OCP\UserStatus\IUserStatus;
  29. /**
  30. * @template-extends QBMapper<UserStatus>
  31. */
  32. class UserStatusMapper extends QBMapper {
  33. /**
  34. * @param IDBConnection $db
  35. */
  36. public function __construct(IDBConnection $db) {
  37. parent::__construct($db, 'user_status');
  38. }
  39. /**
  40. * @param int|null $limit
  41. * @param int|null $offset
  42. * @return UserStatus[]
  43. */
  44. public function findAll(?int $limit = null, ?int $offset = null):array {
  45. $qb = $this->db->getQueryBuilder();
  46. $qb
  47. ->select('*')
  48. ->from($this->tableName);
  49. if ($limit !== null) {
  50. $qb->setMaxResults($limit);
  51. }
  52. if ($offset !== null) {
  53. $qb->setFirstResult($offset);
  54. }
  55. return $this->findEntities($qb);
  56. }
  57. /**
  58. * @param int|null $limit
  59. * @param int|null $offset
  60. * @return array
  61. */
  62. public function findAllRecent(?int $limit = null, ?int $offset = null): array {
  63. $qb = $this->db->getQueryBuilder();
  64. $qb
  65. ->select('*')
  66. ->from($this->tableName)
  67. ->orderBy('status_timestamp', 'DESC')
  68. ->where($qb->expr()->andX(
  69. $qb->expr()->orX(
  70. $qb->expr()->notIn('status', $qb->createNamedParameter([IUserStatus::ONLINE, IUserStatus::AWAY, IUserStatus::OFFLINE], IQueryBuilder::PARAM_STR_ARRAY)),
  71. $qb->expr()->isNotNull('message_id'),
  72. $qb->expr()->isNotNull('custom_icon'),
  73. $qb->expr()->isNotNull('custom_message'),
  74. ),
  75. $qb->expr()->notLike('user_id', $qb->createNamedParameter($this->db->escapeLikeParameter('_') . '%'))
  76. ));
  77. if ($limit !== null) {
  78. $qb->setMaxResults($limit);
  79. }
  80. if ($offset !== null) {
  81. $qb->setFirstResult($offset);
  82. }
  83. return $this->findEntities($qb);
  84. }
  85. /**
  86. * @param string $userId
  87. * @return UserStatus
  88. * @throws \OCP\AppFramework\Db\DoesNotExistException
  89. */
  90. public function findByUserId(string $userId, bool $isBackup = false):UserStatus {
  91. $qb = $this->db->getQueryBuilder();
  92. $qb
  93. ->select('*')
  94. ->from($this->tableName)
  95. ->where($qb->expr()->eq('user_id', $qb->createNamedParameter($isBackup ? '_' . $userId : $userId, IQueryBuilder::PARAM_STR)));
  96. return $this->findEntity($qb);
  97. }
  98. /**
  99. * @param array $userIds
  100. * @return array
  101. */
  102. public function findByUserIds(array $userIds): array {
  103. $qb = $this->db->getQueryBuilder();
  104. $qb
  105. ->select('*')
  106. ->from($this->tableName)
  107. ->where($qb->expr()->in('user_id', $qb->createNamedParameter($userIds, IQueryBuilder::PARAM_STR_ARRAY)));
  108. return $this->findEntities($qb);
  109. }
  110. /**
  111. * @param int $olderThan
  112. * @param int $now
  113. */
  114. public function clearStatusesOlderThan(int $olderThan, int $now): void {
  115. $qb = $this->db->getQueryBuilder();
  116. $qb->update($this->tableName)
  117. ->set('status', $qb->createNamedParameter(IUserStatus::OFFLINE))
  118. ->set('is_user_defined', $qb->createNamedParameter(false, IQueryBuilder::PARAM_BOOL))
  119. ->set('status_timestamp', $qb->createNamedParameter($now, IQueryBuilder::PARAM_INT))
  120. ->where($qb->expr()->lte('status_timestamp', $qb->createNamedParameter($olderThan, IQueryBuilder::PARAM_INT)))
  121. ->andWhere($qb->expr()->neq('status', $qb->createNamedParameter(IUserStatus::OFFLINE)))
  122. ->andWhere($qb->expr()->orX(
  123. $qb->expr()->eq('is_user_defined', $qb->createNamedParameter(false, IQueryBuilder::PARAM_BOOL), IQueryBuilder::PARAM_BOOL),
  124. $qb->expr()->eq('status', $qb->createNamedParameter(IUserStatus::ONLINE))
  125. ));
  126. $qb->execute();
  127. }
  128. /**
  129. * Clear all statuses older than a given timestamp
  130. *
  131. * @param int $timestamp
  132. */
  133. public function clearOlderThanClearAt(int $timestamp): void {
  134. $qb = $this->db->getQueryBuilder();
  135. $qb->delete($this->tableName)
  136. ->where($qb->expr()->isNotNull('clear_at'))
  137. ->andWhere($qb->expr()->lte('clear_at', $qb->createNamedParameter($timestamp, IQueryBuilder::PARAM_INT)));
  138. $qb->execute();
  139. }
  140. /**
  141. * Deletes a user status so we can restore the backup
  142. *
  143. * @param string $userId
  144. * @param string $messageId
  145. * @return bool True if an entry was deleted
  146. */
  147. public function deleteCurrentStatusToRestoreBackup(string $userId, string $messageId): bool {
  148. $qb = $this->db->getQueryBuilder();
  149. $qb->delete($this->tableName)
  150. ->where($qb->expr()->eq('user_id', $qb->createNamedParameter($userId)))
  151. ->andWhere($qb->expr()->eq('message_id', $qb->createNamedParameter($messageId)))
  152. ->andWhere($qb->expr()->eq('is_backup', $qb->createNamedParameter(false, IQueryBuilder::PARAM_BOOL)));
  153. return $qb->executeStatement() > 0;
  154. }
  155. public function deleteByIds(array $ids): void {
  156. $qb = $this->db->getQueryBuilder();
  157. $qb->delete($this->tableName)
  158. ->where($qb->expr()->in('id', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)));
  159. $qb->executeStatement();
  160. }
  161. /**
  162. * @param string $userId
  163. * @return bool
  164. * @throws \OCP\DB\Exception
  165. */
  166. public function createBackupStatus(string $userId): bool {
  167. // Prefix user account with an underscore because user_id is marked as unique
  168. // in the table. Starting a username with an underscore is not allowed so this
  169. // shouldn't create any trouble.
  170. $qb = $this->db->getQueryBuilder();
  171. $qb->update($this->tableName)
  172. ->set('is_backup', $qb->createNamedParameter(true, IQueryBuilder::PARAM_BOOL))
  173. ->set('user_id', $qb->createNamedParameter('_' . $userId))
  174. ->where($qb->expr()->eq('user_id', $qb->createNamedParameter($userId)));
  175. return $qb->executeStatement() > 0;
  176. }
  177. public function restoreBackupStatuses(array $ids): void {
  178. $qb = $this->db->getQueryBuilder();
  179. $qb->update($this->tableName)
  180. ->set('is_backup', $qb->createNamedParameter(false, IQueryBuilder::PARAM_BOOL))
  181. ->set('user_id', $qb->func()->substring('user_id', $qb->createNamedParameter(2, IQueryBuilder::PARAM_INT)))
  182. ->where($qb->expr()->in('id', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)));
  183. $qb->executeStatement();
  184. }
  185. }