UserStatusMapper.php 6.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213
  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_message_timestamp', 'DESC')
  68. ->where($qb->expr()->andX(
  69. $qb->expr()->neq('status_message_timestamp', $qb->createNamedParameter(0, IQueryBuilder::PARAM_INT), IQueryBuilder::PARAM_INT),
  70. $qb->expr()->orX(
  71. $qb->expr()->notIn('status', $qb->createNamedParameter([IUserStatus::ONLINE, IUserStatus::AWAY, IUserStatus::OFFLINE], IQueryBuilder::PARAM_STR_ARRAY)),
  72. $qb->expr()->isNotNull('message_id'),
  73. $qb->expr()->isNotNull('custom_icon'),
  74. $qb->expr()->isNotNull('custom_message'),
  75. ),
  76. $qb->expr()->notLike('user_id', $qb->createNamedParameter($this->db->escapeLikeParameter('_') . '%'))
  77. ));
  78. if ($limit !== null) {
  79. $qb->setMaxResults($limit);
  80. }
  81. if ($offset !== null) {
  82. $qb->setFirstResult($offset);
  83. }
  84. return $this->findEntities($qb);
  85. }
  86. /**
  87. * @param string $userId
  88. * @return UserStatus
  89. * @throws \OCP\AppFramework\Db\DoesNotExistException
  90. */
  91. public function findByUserId(string $userId, bool $isBackup = false): UserStatus {
  92. $qb = $this->db->getQueryBuilder();
  93. $qb
  94. ->select('*')
  95. ->from($this->tableName)
  96. ->where($qb->expr()->eq('user_id', $qb->createNamedParameter($isBackup ? '_' . $userId : $userId, IQueryBuilder::PARAM_STR)));
  97. return $this->findEntity($qb);
  98. }
  99. /**
  100. * @param array $userIds
  101. * @return array
  102. */
  103. public function findByUserIds(array $userIds): array {
  104. $qb = $this->db->getQueryBuilder();
  105. $qb
  106. ->select('*')
  107. ->from($this->tableName)
  108. ->where($qb->expr()->in('user_id', $qb->createNamedParameter($userIds, IQueryBuilder::PARAM_STR_ARRAY)));
  109. return $this->findEntities($qb);
  110. }
  111. /**
  112. * @param int $olderThan
  113. * @param int $now
  114. */
  115. public function clearStatusesOlderThan(int $olderThan, int $now): void {
  116. $qb = $this->db->getQueryBuilder();
  117. $qb->update($this->tableName)
  118. ->set('status', $qb->createNamedParameter(IUserStatus::OFFLINE))
  119. ->set('is_user_defined', $qb->createNamedParameter(false, IQueryBuilder::PARAM_BOOL))
  120. ->set('status_timestamp', $qb->createNamedParameter($now, IQueryBuilder::PARAM_INT))
  121. ->where($qb->expr()->lte('status_timestamp', $qb->createNamedParameter($olderThan, IQueryBuilder::PARAM_INT)))
  122. ->andWhere($qb->expr()->neq('status', $qb->createNamedParameter(IUserStatus::OFFLINE)))
  123. ->andWhere($qb->expr()->orX(
  124. $qb->expr()->eq('is_user_defined', $qb->createNamedParameter(false, IQueryBuilder::PARAM_BOOL), IQueryBuilder::PARAM_BOOL),
  125. $qb->expr()->eq('status', $qb->createNamedParameter(IUserStatus::ONLINE))
  126. ));
  127. $qb->execute();
  128. }
  129. /**
  130. * Clear all statuses older than a given timestamp
  131. *
  132. * @param int $timestamp
  133. */
  134. public function clearOlderThanClearAt(int $timestamp): void {
  135. $qb = $this->db->getQueryBuilder();
  136. $qb->delete($this->tableName)
  137. ->where($qb->expr()->isNotNull('clear_at'))
  138. ->andWhere($qb->expr()->lte('clear_at', $qb->createNamedParameter($timestamp, IQueryBuilder::PARAM_INT)));
  139. $qb->execute();
  140. }
  141. /**
  142. * Deletes a user status so we can restore the backup
  143. *
  144. * @param string $userId
  145. * @param string $messageId
  146. * @return bool True if an entry was deleted
  147. */
  148. public function deleteCurrentStatusToRestoreBackup(string $userId, string $messageId): bool {
  149. $qb = $this->db->getQueryBuilder();
  150. $qb->delete($this->tableName)
  151. ->where($qb->expr()->eq('user_id', $qb->createNamedParameter($userId)))
  152. ->andWhere($qb->expr()->eq('message_id', $qb->createNamedParameter($messageId)))
  153. ->andWhere($qb->expr()->eq('is_backup', $qb->createNamedParameter(false, IQueryBuilder::PARAM_BOOL)));
  154. return $qb->executeStatement() > 0;
  155. }
  156. public function deleteByIds(array $ids): void {
  157. $qb = $this->db->getQueryBuilder();
  158. $qb->delete($this->tableName)
  159. ->where($qb->expr()->in('id', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)));
  160. $qb->executeStatement();
  161. }
  162. /**
  163. * @param string $userId
  164. * @return bool
  165. * @throws \OCP\DB\Exception
  166. */
  167. public function createBackupStatus(string $userId): bool {
  168. // Prefix user account with an underscore because user_id is marked as unique
  169. // in the table. Starting a username with an underscore is not allowed so this
  170. // shouldn't create any trouble.
  171. $qb = $this->db->getQueryBuilder();
  172. $qb->update($this->tableName)
  173. ->set('is_backup', $qb->createNamedParameter(true, IQueryBuilder::PARAM_BOOL))
  174. ->set('user_id', $qb->createNamedParameter('_' . $userId))
  175. ->where($qb->expr()->eq('user_id', $qb->createNamedParameter($userId)));
  176. return $qb->executeStatement() > 0;
  177. }
  178. public function restoreBackupStatuses(array $ids): void {
  179. $qb = $this->db->getQueryBuilder();
  180. $qb->update($this->tableName)
  181. ->set('is_backup', $qb->createNamedParameter(false, IQueryBuilder::PARAM_BOOL))
  182. ->set('user_id', $qb->func()->substring('user_id', $qb->createNamedParameter(2, IQueryBuilder::PARAM_INT)))
  183. ->where($qb->expr()->in('id', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)));
  184. $qb->executeStatement();
  185. }
  186. }