nc-music /
music
| Total Complexity | 142 |
| Total Lines | 847 |
| Duplicated Lines | 0 % |
| Changes | 6 | ||
| Bugs | 0 | Features | 0 |
Complex classes like BaseMapper often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use BaseMapper, and based on these observations, apply Extract Interface, too.
| 1 | <?php declare(strict_types=1); |
||
| 37 | abstract class BaseMapper extends Mapper { |
||
| 38 | public const SQL_DATE_FORMAT = 'Y-m-d H:i:s.v'; |
||
| 39 | |||
| 40 | protected string $nameColumn; |
||
| 41 | protected ?array $uniqueColumns; |
||
| 42 | protected ?string $parentIdColumn; |
||
| 43 | protected string $dbType; // database type 'mysql', 'pgsql', or 'sqlite3' |
||
| 44 | |||
| 45 | /** |
||
| 46 | * @param ?string[] $uniqueColumns List of column names composing the unique constraint of the table. Null if there's no unique index. |
||
| 47 | * @phpstan-param class-string<EntityType> $entityClass |
||
| 48 | */ |
||
| 49 | public function __construct( |
||
| 50 | IDBConnection $db, IConfig $config, string $tableName, string $entityClass, |
||
| 51 | string $nameColumn, ?array $uniqueColumns=null, ?string $parentIdColumn=null) { |
||
| 52 | parent::__construct($db, $tableName, $entityClass); |
||
| 53 | $this->nameColumn = $nameColumn; |
||
| 54 | $this->uniqueColumns = $uniqueColumns; |
||
| 55 | $this->parentIdColumn = $parentIdColumn; |
||
| 56 | $this->dbType = $config->getSystemValue('dbtype'); |
||
| 57 | } |
||
| 58 | |||
| 59 | /** |
||
| 60 | * Create an empty object of the entity class bound to this mapper |
||
| 61 | * @phpstan-return EntityType |
||
| 62 | */ |
||
| 63 | public function createEntity() : Entity { |
||
| 64 | return new $this->entityClass(); |
||
| 65 | } |
||
| 66 | |||
| 67 | public function unprefixedTableName() : string { |
||
| 68 | return \str_replace('*PREFIX*', '', $this->getTableName()); |
||
| 69 | } |
||
| 70 | |||
| 71 | /** |
||
| 72 | * Find a single entity by id and user_id |
||
| 73 | * @throws DoesNotExistException if the entity does not exist |
||
| 74 | * @throws MultipleObjectsReturnedException if more than one entity exists |
||
| 75 | * @phpstan-return EntityType |
||
| 76 | */ |
||
| 77 | public function find(int $id, string $userId) : Entity { |
||
| 78 | $sql = $this->selectUserEntities("`{$this->getTableName()}`.`id` = ?"); |
||
| 79 | return $this->findEntity($sql, [$userId, $id]); |
||
| 80 | } |
||
| 81 | |||
| 82 | /** |
||
| 83 | * Find all entities matching the given IDs. Specifying the owning user is optional. |
||
| 84 | * @param integer[] $ids IDs of the entities to be found |
||
| 85 | * @param string|null $userId |
||
| 86 | * @return Entity[] |
||
| 87 | * @phpstan-return EntityType[] |
||
| 88 | */ |
||
| 89 | public function findById(array $ids, ?string $userId=null) : array { |
||
| 90 | $count = \count($ids); |
||
| 91 | $condition = "`{$this->getTableName()}`.`id` IN ". $this->questionMarks($count); |
||
| 92 | |||
| 93 | if (empty($userId)) { |
||
| 94 | $sql = $this->selectEntities($condition); |
||
| 95 | } else { |
||
| 96 | $sql = $this->selectUserEntities($condition); |
||
| 97 | $ids = \array_merge([$userId], $ids); |
||
| 98 | } |
||
| 99 | |||
| 100 | return $this->findEntities($sql, $ids); |
||
| 101 | } |
||
| 102 | |||
| 103 | /** |
||
| 104 | * Find all user's entities |
||
| 105 | * @param string|null $createdMin Optional minimum `created` timestamp. |
||
| 106 | * @param string|null $createdMax Optional maximum `created` timestamp. |
||
| 107 | * @param string|null $updatedMin Optional minimum `updated` timestamp. |
||
| 108 | * @param string|null $updatedMax Optional maximum `updated` timestamp. |
||
| 109 | * @return Entity[] |
||
| 110 | * @phpstan-return EntityType[] |
||
| 111 | */ |
||
| 112 | public function findAll(string $userId, int $sortBy=SortBy::Name, ?int $limit=null, ?int $offset=null, |
||
| 113 | ?string $createdMin=null, ?string $createdMax=null, ?string $updatedMin=null, ?string $updatedMax=null) : array { |
||
| 114 | $sorting = $this->formatSortingClause($sortBy); |
||
| 115 | [$condition, $params] = $this->formatTimestampConditions($createdMin, $createdMax, $updatedMin, $updatedMax); |
||
| 116 | $sql = $this->selectUserEntities($condition, $sorting); |
||
| 117 | \array_unshift($params, $userId); |
||
| 118 | return $this->findEntities($sql, $params, $limit, $offset); |
||
| 119 | } |
||
| 120 | |||
| 121 | /** |
||
| 122 | * Find all user's entities matching the given name |
||
| 123 | * @param string|null $createdMin Optional minimum `created` timestamp. |
||
| 124 | * @param string|null $createdMax Optional maximum `created` timestamp. |
||
| 125 | * @param string|null $updatedMin Optional minimum `updated` timestamp. |
||
| 126 | * @param string|null $updatedMax Optional maximum `updated` timestamp. |
||
| 127 | * @return Entity[] |
||
| 128 | * @phpstan-return EntityType[] |
||
| 129 | */ |
||
| 130 | public function findAllByName( |
||
| 131 | ?string $name, string $userId, int $matchMode=MatchMode::Exact, ?int $limit=null, ?int $offset=null, |
||
| 132 | ?string $createdMin=null, ?string $createdMax=null, ?string $updatedMin=null, ?string $updatedMax=null) : array { |
||
| 133 | |||
| 134 | $params = [$userId]; |
||
| 135 | |||
| 136 | [$condition, $nameParams] = $this->formatNameConditions($name, $matchMode); |
||
| 137 | $params = \array_merge($params, $nameParams); |
||
| 138 | |||
| 139 | [$timestampConds, $timestampParams] = $this->formatTimestampConditions($createdMin, $createdMax, $updatedMin, $updatedMax); |
||
| 140 | if (!empty($timestampConds)) { |
||
| 141 | $condition .= ' AND ' . $timestampConds; |
||
| 142 | $params = \array_merge($params, $timestampParams); |
||
| 143 | } |
||
| 144 | |||
| 145 | $sql = $this->selectUserEntities($condition, $this->formatSortingClause(SortBy::Name)); |
||
| 146 | |||
| 147 | return $this->findEntities($sql, $params, $limit, $offset); |
||
| 148 | } |
||
| 149 | |||
| 150 | /** |
||
| 151 | * Find all user's starred entities. It is safe to call this also on entity types |
||
| 152 | * not supporting starring in which case an empty array will be returned. |
||
| 153 | * @return Entity[] |
||
| 154 | * @phpstan-return EntityType[] |
||
| 155 | */ |
||
| 156 | public function findAllStarred(string $userId, ?int $limit=null, ?int $offset=null) : array { |
||
| 157 | if (\property_exists($this->entityClass, 'starred')) { |
||
| 158 | $sql = $this->selectUserEntities( |
||
| 159 | "`{$this->getTableName()}`.`starred` IS NOT NULL", |
||
| 160 | $this->formatSortingClause(SortBy::Name) |
||
| 161 | ); |
||
| 162 | return $this->findEntities($sql, [$userId], $limit, $offset); |
||
| 163 | } else { |
||
| 164 | return []; |
||
| 165 | } |
||
| 166 | } |
||
| 167 | |||
| 168 | /** |
||
| 169 | * Find IDSs of all user's starred entities. It is safe to call this also on entity types |
||
| 170 | * not supporting starring in which case an empty array will be returned. |
||
| 171 | * @return int[] |
||
| 172 | */ |
||
| 173 | public function findAllStarredIds(string $userId) : array { |
||
| 174 | if (\property_exists($this->entityClass, 'starred')) { |
||
| 175 | $sql = "SELECT `id` FROM `{$this->getTableName()}` WHERE `starred` IS NOT NULL AND `user_id` = ?"; |
||
| 176 | $result = $this->execute($sql, [$userId]); |
||
| 177 | $return = \array_map('intval', $result->fetchAll(\PDO::FETCH_COLUMN)); |
||
|
|
|||
| 178 | $result->closeCursor(); |
||
| 179 | return $return; |
||
| 180 | } else { |
||
| 181 | return []; |
||
| 182 | } |
||
| 183 | } |
||
| 184 | |||
| 185 | /** |
||
| 186 | * Find all entities with user-given rating 1-5 |
||
| 187 | * @return Entity[] |
||
| 188 | * @phpstan-return EntityType[] |
||
| 189 | */ |
||
| 190 | public function findAllRated(string $userId, ?int $limit=null, ?int $offset=null) : array { |
||
| 191 | if (\property_exists($this->entityClass, 'rating')) { |
||
| 192 | $sql = $this->selectUserEntities( |
||
| 193 | "`{$this->getTableName()}`.`rating` > 0", |
||
| 194 | $this->formatSortingClause(SortBy::Rating) |
||
| 195 | ); |
||
| 196 | return $this->findEntities($sql, [$userId], $limit, $offset); |
||
| 197 | } else { |
||
| 198 | return []; |
||
| 199 | } |
||
| 200 | } |
||
| 201 | |||
| 202 | /** |
||
| 203 | * Find all entities matching multiple criteria, as needed for the Ampache API method `advanced_search` |
||
| 204 | * @param string $conjunction Operator to use between the rules, either 'and' or 'or' |
||
| 205 | * @param array $rules Array of arrays: [['rule' => string, 'operator' => string, 'input' => string], ...] |
||
| 206 | * Here, 'rule' has dozens of possible values depending on the business layer in question |
||
| 207 | * (see https://ampache.org/api/api-advanced-search#available-search-rules, alias names not supported here), |
||
| 208 | * 'operator' is one of |
||
| 209 | * ['contain', 'notcontain', 'start', 'end', 'is', 'isnot', 'sounds', 'notsounds', 'regexp', 'notregexp', |
||
| 210 | * '>=', '<=', '=', '!=', '>', '<', 'before', 'after', 'true', 'false', 'equal', 'ne', 'limit'], |
||
| 211 | * 'input' is the right side value of the 'operator' (disregarded for the operators 'true' and 'false') |
||
| 212 | * @return Entity[] |
||
| 213 | * @phpstan-return EntityType[] |
||
| 214 | */ |
||
| 215 | public function findAllAdvanced(string $conjunction, array $rules, string $userId, int $sortBy=SortBy::Name, ?int $limit=null, ?int $offset=null) : array { |
||
| 216 | $sqlConditions = []; |
||
| 217 | $sqlParams = [$userId]; |
||
| 218 | |||
| 219 | foreach ($rules as $rule) { |
||
| 220 | list('op' => $sqlOp, 'conv' => $sqlConv, 'param' => $param) = $this->advFormatSqlOperator($rule['operator'], (string)$rule['input'], $userId); |
||
| 221 | $cond = $this->advFormatSqlCondition($rule['rule'], $sqlOp, $sqlConv); |
||
| 222 | $sqlConditions[] = $cond; |
||
| 223 | // On some conditions, the parameter may need to be repeated several times |
||
| 224 | $paramCount = \substr_count($cond, '?'); |
||
| 225 | for ($i = 0; $i < $paramCount; ++$i) { |
||
| 226 | $sqlParams[] = $param; |
||
| 227 | } |
||
| 228 | } |
||
| 229 | $sqlConditions = \implode(" $conjunction ", $sqlConditions); |
||
| 230 | |||
| 231 | $sql = $this->selectUserEntities($sqlConditions, $this->formatSortingClause($sortBy)); |
||
| 232 | return $this->findEntities($sql, $sqlParams, $limit, $offset); |
||
| 233 | } |
||
| 234 | |||
| 235 | /** |
||
| 236 | * Optionally, limit to given IDs which may be used to check the validity of those IDs. |
||
| 237 | * @return int[] |
||
| 238 | */ |
||
| 239 | public function findAllIds(string $userId, ?array $ids = null) : array { |
||
| 240 | $sql = "SELECT `id` FROM `{$this->getTableName()}` WHERE `user_id` = ?"; |
||
| 241 | $params = [$userId]; |
||
| 242 | |||
| 243 | if ($ids !== null) { |
||
| 244 | $sql .= ' AND `id` IN ' . $this->questionMarks(\count($ids)); |
||
| 245 | $params = \array_merge($params, $ids); |
||
| 246 | } |
||
| 247 | |||
| 248 | $result = $this->execute($sql, $params); |
||
| 249 | |||
| 250 | $return = \array_map('intval', $result->fetchAll(\PDO::FETCH_COLUMN)); |
||
| 251 | $result->closeCursor(); |
||
| 252 | return $return; |
||
| 253 | } |
||
| 254 | |||
| 255 | /** |
||
| 256 | * Find all entity IDs grouped by the given parent entity IDs. Not applicable on all entity types. |
||
| 257 | * @param int[] $parentIds |
||
| 258 | * @return array<int, int[]> like [parentId => childIds[]]; some parents may have an empty array of children |
||
| 259 | * @throws \DomainException if the entity type handled by this mapper doesn't have a parent relation |
||
| 260 | */ |
||
| 261 | public function findAllIdsByParentIds(string $userId, array $parentIds) : ?array { |
||
| 262 | if ($this->parentIdColumn === null) { |
||
| 263 | throw new \DomainException("Finding by parent is not applicable for the table {$this->getTableName()}"); |
||
| 264 | } |
||
| 265 | |||
| 266 | $return = []; |
||
| 267 | if (\count($parentIds) > 0) { |
||
| 268 | $sql = "SELECT `id`, `{$this->parentIdColumn}` AS `parent_id` FROM `{$this->getTableName()}` |
||
| 269 | WHERE `user_id` = ? AND `{$this->parentIdColumn}` IN " . $this->questionMarks(\count($parentIds)); |
||
| 270 | $params = \array_merge([$userId], $parentIds); |
||
| 271 | $result = $this->execute($sql, $params); |
||
| 272 | $rows = $result->fetchAll(); |
||
| 273 | $result->closeCursor(); |
||
| 274 | |||
| 275 | // ensure that the result contains also "parents" with no children and has the same order as $parentIds |
||
| 276 | $return = \array_fill_keys($parentIds, []); |
||
| 277 | foreach ($rows as $row) { |
||
| 278 | $return[(int)$row['parent_id']][] = (int)$row['id']; |
||
| 279 | } |
||
| 280 | } |
||
| 281 | |||
| 282 | return $return; |
||
| 283 | } |
||
| 284 | |||
| 285 | /** |
||
| 286 | * Find all IDs and names of user's entities of this kind. |
||
| 287 | * Optionally, limit results based on a parent entity (not applicable for all entity types) or update/insert times or name |
||
| 288 | * @param bool $excludeChildless Exclude entities having no child-entities if applicable for this business layer (eg. artists without albums) |
||
| 289 | * @return array of arrays like ['id' => string, 'name' => ?string] |
||
| 290 | */ |
||
| 291 | public function findAllIdsAndNames(string $userId, ?int $parentId, ?int $limit=null, ?int $offset=null, |
||
| 292 | ?string $createdMin=null, ?string $createdMax=null, ?string $updatedMin=null, ?string $updatedMax=null, |
||
| 293 | bool $excludeChildless=false, ?string $name=null) : array { |
||
| 294 | $sql = "SELECT `id`, `{$this->nameColumn}` AS `name` FROM `{$this->getTableName()}` WHERE `user_id` = ?"; |
||
| 295 | $params = [$userId]; |
||
| 296 | if ($parentId !== null) { |
||
| 297 | if ($this->parentIdColumn === null) { |
||
| 298 | throw new \DomainException("The parentId filtering is not applicable for the table {$this->getTableName()}"); |
||
| 299 | } else { |
||
| 300 | $sql .= " AND {$this->parentIdColumn} = ?"; |
||
| 301 | $params[] = $parentId; |
||
| 302 | } |
||
| 303 | } |
||
| 304 | |||
| 305 | [$timestampConds, $timestampParams] = $this->formatTimestampConditions($createdMin, $createdMax, $updatedMin, $updatedMax); |
||
| 306 | if (!empty($timestampConds)) { |
||
| 307 | $sql .= " AND $timestampConds"; |
||
| 308 | $params = \array_merge($params, $timestampParams); |
||
| 309 | } |
||
| 310 | |||
| 311 | if ($excludeChildless) { |
||
| 312 | $sql .= ' AND ' . $this->formatExcludeChildlessCondition(); |
||
| 313 | } |
||
| 314 | |||
| 315 | if (!empty($name)) { |
||
| 316 | [$nameCond, $nameParams] = $this->formatNameConditions($name, MatchMode::Substring); |
||
| 317 | $sql .= " AND $nameCond"; |
||
| 318 | $params = \array_merge($params, $nameParams); |
||
| 319 | } |
||
| 320 | |||
| 321 | $sql .= ' ' . $this->formatSortingClause(SortBy::Name); |
||
| 322 | |||
| 323 | if ($limit !== null) { |
||
| 324 | $sql .= ' LIMIT ?'; |
||
| 325 | $params[] = $limit; |
||
| 326 | } |
||
| 327 | if ($offset !== null) { |
||
| 328 | $sql .= ' OFFSET ?'; |
||
| 329 | $params[] = $offset; |
||
| 330 | } |
||
| 331 | |||
| 332 | $result = $this->execute($sql, $params); |
||
| 333 | $rows = $result->fetchAll(); |
||
| 334 | $result->closeCursor(); |
||
| 335 | |||
| 336 | return $rows; |
||
| 337 | } |
||
| 338 | |||
| 339 | /** |
||
| 340 | * Find IDs of all users owning any entities of this mapper |
||
| 341 | * @return string[] |
||
| 342 | */ |
||
| 343 | public function findAllUsers() : array { |
||
| 344 | $sql = "SELECT DISTINCT(`user_id`) FROM `{$this->getTableName()}`"; |
||
| 345 | $result = $this->execute($sql); |
||
| 346 | $rows = $result->fetchAll(\PDO::FETCH_COLUMN); |
||
| 347 | $result->closeCursor(); |
||
| 348 | return $rows; |
||
| 349 | } |
||
| 350 | |||
| 351 | /** |
||
| 352 | * Delete all entities with given IDs without specifying the user |
||
| 353 | * @param integer[] $ids IDs of the entities to be deleted |
||
| 354 | */ |
||
| 355 | public function deleteById(array $ids) : void { |
||
| 356 | $count = \count($ids); |
||
| 357 | if ($count === 0) { |
||
| 358 | return; |
||
| 359 | } |
||
| 360 | $this->deleteByCond('`id` IN ' . $this->questionMarks($count), $ids); |
||
| 361 | } |
||
| 362 | |||
| 363 | /** |
||
| 364 | * Delete all entities matching the given SQL condition |
||
| 365 | * @param string $condition SQL 'WHERE' condition (without the keyword 'WHERE') |
||
| 366 | * @param array $params SQL parameters for the condition |
||
| 367 | */ |
||
| 368 | protected function deleteByCond(string $condition, array $params) : void { |
||
| 369 | $sql = "DELETE FROM `{$this->getTableName()}` WHERE ". $condition; |
||
| 370 | $result = $this->execute($sql, $params); |
||
| 371 | $result->closeCursor(); |
||
| 372 | } |
||
| 373 | |||
| 374 | /** |
||
| 375 | * Delete all entities of the given user |
||
| 376 | */ |
||
| 377 | public function deleteAll(string $userId) : void { |
||
| 378 | $sql = "DELETE FROM `{$this->getTableName()}` WHERE `user_id` = ?"; |
||
| 379 | $result = $this->execute($sql, [$userId]); |
||
| 380 | $result->closeCursor(); |
||
| 381 | } |
||
| 382 | |||
| 383 | /** |
||
| 384 | * Tests if entity with given ID and user ID exists in the database |
||
| 385 | */ |
||
| 386 | public function exists(int $id, string $userId) : bool { |
||
| 387 | $sql = "SELECT 1 FROM `{$this->getTableName()}` WHERE `id` = ? AND `user_id` = ?"; |
||
| 388 | $result = $this->execute($sql, [$id, $userId]); |
||
| 389 | $row = $result->fetch(); |
||
| 390 | $result->closeCursor(); |
||
| 391 | return (bool)$row; |
||
| 392 | } |
||
| 393 | |||
| 394 | /** |
||
| 395 | * Count all entities of a user |
||
| 396 | */ |
||
| 397 | public function count(string $userId) : int { |
||
| 398 | $sql = "SELECT COUNT(*) AS count FROM `{$this->getTableName()}` WHERE `user_id` = ?"; |
||
| 399 | $result = $this->execute($sql, [$userId]); |
||
| 400 | $row = $result->fetch(); |
||
| 401 | $result->closeCursor(); |
||
| 402 | return \intval($row['count']); |
||
| 403 | } |
||
| 404 | |||
| 405 | /** |
||
| 406 | * Get the largest entity ID of the user |
||
| 407 | */ |
||
| 408 | public function maxId(string $userId) : ?int { |
||
| 409 | $sql = "SELECT MAX(`id`) AS max_id FROM `{$this->getTableName()}` WHERE `user_id` = ?"; |
||
| 410 | $result = $this->execute($sql, [$userId]); |
||
| 411 | $row = $result->fetch(); |
||
| 412 | $result->closeCursor(); |
||
| 413 | $max = $row['max_id']; |
||
| 414 | return $max === null ? null : (int)$max; |
||
| 415 | } |
||
| 416 | |||
| 417 | /** |
||
| 418 | * {@inheritDoc} |
||
| 419 | * @see Mapper::insert() |
||
| 420 | * @phpstan-param EntityType $entity |
||
| 421 | * @phpstan-return EntityType |
||
| 422 | */ |
||
| 423 | public function insert(\OCP\AppFramework\Db\Entity $entity) : Entity { |
||
| 424 | if (!($entity instanceof Entity)) { |
||
| 425 | // Because of Liskov Substitution Principle, this class must technically accept any platform Entity. |
||
| 426 | // However, the function only works correctly for our own Entity type. The return type can be narrowed |
||
| 427 | // from the parent, thanks to the covariance rules of PHP 7.4 and later. |
||
| 428 | throw new \BadMethodCallException('$entity must be of type ' . Entity::class); |
||
| 429 | } |
||
| 430 | |||
| 431 | $now = new \DateTime(); |
||
| 432 | $nowStr = $now->format(self::SQL_DATE_FORMAT); |
||
| 433 | $entity->setCreated($nowStr); |
||
| 434 | $entity->setUpdated($nowStr); |
||
| 435 | |||
| 436 | try { |
||
| 437 | return parent::insert($entity); |
||
| 438 | } catch (\Doctrine\DBAL\Exception\UniqueConstraintViolationException $e) { |
||
| 439 | throw new UniqueConstraintViolationException($e->getMessage(), $e->getCode(), $e); |
||
| 440 | } catch (\OCP\DB\Exception $e) { |
||
| 441 | // Nextcloud 21+ |
||
| 442 | if ($e->getReason() == \OCP\DB\Exception::REASON_UNIQUE_CONSTRAINT_VIOLATION) { |
||
| 443 | throw new UniqueConstraintViolationException($e->getMessage(), $e->getCode(), $e); |
||
| 444 | } else { |
||
| 445 | throw $e; |
||
| 446 | } |
||
| 447 | } |
||
| 448 | } |
||
| 449 | |||
| 450 | /** |
||
| 451 | * {@inheritDoc} |
||
| 452 | * @see Mapper::update() |
||
| 453 | * @phpstan-param EntityType $entity |
||
| 454 | * @phpstan-return EntityType |
||
| 455 | */ |
||
| 456 | public function update(\OCP\AppFramework\Db\Entity $entity) : Entity { |
||
| 457 | if (!($entity instanceof Entity)) { |
||
| 458 | // Because of Liskov Substitution Principle, this class must technically accept any platform Entity. |
||
| 459 | // However, the function only works correctly for our own Entity type. The return type can be narrowed |
||
| 460 | // from the parent, thanks to the covariance rules of PHP 7.4 and later. |
||
| 461 | throw new \BadMethodCallException('$entity must be of type ' . Entity::class); |
||
| 462 | } |
||
| 463 | |||
| 464 | $now = new \DateTime(); |
||
| 465 | $entity->setUpdated($now->format(self::SQL_DATE_FORMAT)); |
||
| 466 | return parent::update($entity); |
||
| 467 | } |
||
| 468 | |||
| 469 | /** |
||
| 470 | * Insert an entity, or if an entity with the same identity already exists, |
||
| 471 | * update the existing entity. |
||
| 472 | * Note: The functions insertOrUpdate and updateOrInsert get the exactly same thing done. The only difference is |
||
| 473 | * that the former is optimized for cases where the entity doesn't exist and the latter for cases where it does exist. |
||
| 474 | * @return Entity The inserted or updated entity, containing also the id field |
||
| 475 | * @phpstan-param EntityType $entity |
||
| 476 | * @phpstan-return EntityType |
||
| 477 | */ |
||
| 478 | public function insertOrUpdate(Entity $entity) : Entity { |
||
| 479 | try { |
||
| 480 | return $this->insert($entity); |
||
| 481 | } catch (UniqueConstraintViolationException $ex) { |
||
| 482 | $existingId = $this->findIdOfConflict($entity); |
||
| 483 | $entity->setId($existingId); |
||
| 484 | // The previous call to $this->insert has set the `created` property of $entity. |
||
| 485 | // Set it again using the data from the existing entry. |
||
| 486 | $entity->setCreated($this->getCreated($existingId)); |
||
| 487 | return $this->update($entity); |
||
| 488 | } |
||
| 489 | } |
||
| 490 | |||
| 491 | /** |
||
| 492 | * Update an entity whose unique constraint fields match the given entity. If such entity is not found, |
||
| 493 | * a new entity is inserted. |
||
| 494 | * Note: The functions insertOrUpdate and updateOrInsert get the exactly same thing done. The only difference is |
||
| 495 | * that the former is optimized for cases where the entity doesn't exist and the latter for cases where it does exist. |
||
| 496 | * @param Entity $entity |
||
| 497 | * @return Entity The inserted or updated entity, containing also the id field |
||
| 498 | * @phpstan-param EntityType $entity |
||
| 499 | * @phpstan-return EntityType |
||
| 500 | */ |
||
| 501 | public function updateOrInsert(Entity $entity) : Entity { |
||
| 502 | try { |
||
| 503 | $existingId = $this->findIdOfConflict($entity); |
||
| 504 | $entity->setId($existingId); |
||
| 505 | return $this->update($entity); |
||
| 506 | } catch (DoesNotExistException $ex) { |
||
| 507 | return $this->insertOrUpdate($entity); |
||
| 508 | } |
||
| 509 | } |
||
| 510 | |||
| 511 | /** |
||
| 512 | * Set the "starred" column of the given entities |
||
| 513 | * @param \DateTime|null $date |
||
| 514 | * @param integer[] $ids |
||
| 515 | * @param string $userId |
||
| 516 | * @return int number of modified entities |
||
| 517 | */ |
||
| 518 | public function setStarredDate(?\DateTime $date, array $ids, string $userId) : int { |
||
| 519 | $count = \count($ids); |
||
| 520 | if (!empty($date)) { |
||
| 521 | $date = $date->format(self::SQL_DATE_FORMAT); |
||
| 522 | } |
||
| 523 | |||
| 524 | $sql = "UPDATE `{$this->getTableName()}` SET `starred` = ? |
||
| 525 | WHERE `id` IN {$this->questionMarks($count)} AND `user_id` = ?"; |
||
| 526 | $params = \array_merge([$date], $ids, [$userId]); |
||
| 527 | $result = $this->execute($sql, $params); |
||
| 528 | $modCount = $result->rowCount(); |
||
| 529 | $result->closeCursor(); |
||
| 530 | |||
| 531 | return $modCount; |
||
| 532 | } |
||
| 533 | |||
| 534 | public function latestInsertTime(string $userId) : ?\DateTime { |
||
| 535 | $sql = "SELECT MAX(`{$this->getTableName()}`.`created`) FROM `{$this->getTableName()}` WHERE `user_id` = ?"; |
||
| 536 | $result = $this->execute($sql, [$userId]); |
||
| 537 | $createdTime = $result->fetch(\PDO::FETCH_COLUMN); |
||
| 538 | $result->closeCursor(); |
||
| 539 | |||
| 540 | return ($createdTime === null) ? null : new \DateTime($createdTime); |
||
| 541 | } |
||
| 542 | |||
| 543 | public function latestUpdateTime(string $userId) : ?\DateTime { |
||
| 550 | } |
||
| 551 | |||
| 552 | /** |
||
| 553 | * helper creating a string like '(?,?,?)' with the specified number of elements |
||
| 554 | */ |
||
| 555 | protected function questionMarks(int $count) : string { |
||
| 556 | $questionMarks = []; |
||
| 557 | for ($i = 0; $i < $count; $i++) { |
||
| 558 | $questionMarks[] = '?'; |
||
| 559 | } |
||
| 560 | return '(' . \implode(',', $questionMarks) . ')'; |
||
| 561 | } |
||
| 562 | |||
| 563 | /** |
||
| 564 | * Build a SQL SELECT statement which selects all entities of the given user, |
||
| 565 | * and optionally applies other conditions, too. |
||
| 566 | * This is built upon `selectEntities` which may be overridden by the derived class. |
||
| 567 | * @param string|null $condition Optional extra condition. This will get automatically |
||
| 568 | * prefixed with ' AND ', so don't include that. |
||
| 569 | * @param string|null $extension Any extension (e.g. ORDER BY, LIMIT) to be added after |
||
| 570 | * the conditions in the SQL statement |
||
| 571 | */ |
||
| 572 | protected function selectUserEntities(?string $condition=null, ?string $extension=null) : string { |
||
| 573 | $allConditions = "`{$this->getTableName()}`.`user_id` = ?"; |
||
| 574 | |||
| 575 | if (!empty($condition)) { |
||
| 576 | $allConditions .= " AND ($condition)"; |
||
| 577 | } |
||
| 578 | |||
| 579 | return $this->selectEntities($allConditions, $extension); |
||
| 580 | } |
||
| 581 | |||
| 582 | /** |
||
| 583 | * Build a SQL SELECT statement which selects all entities matching the given condition. |
||
| 584 | * The derived class may override this if necessary. |
||
| 585 | * @param string $condition This will get automatically prefixed with ' WHERE ' |
||
| 586 | * @param string|null $extension Any extension (e.g. ORDER BY, LIMIT) to be added after |
||
| 587 | * the conditions in the SQL statement |
||
| 588 | */ |
||
| 589 | protected function selectEntities(string $condition, ?string $extension=null) : string { |
||
| 590 | return "SELECT * FROM `{$this->getTableName()}` WHERE $condition $extension "; |
||
| 591 | } |
||
| 592 | |||
| 593 | /** |
||
| 594 | * @return array with two values: The SQL condition as string and the SQL parameters as string[] |
||
| 595 | */ |
||
| 596 | protected function formatNameConditions(?string $name, int $matchMode) : array { |
||
| 597 | $params = []; |
||
| 598 | $nameCol = "`{$this->getTableName()}`.`{$this->nameColumn}`"; |
||
| 599 | if ($name === null) { |
||
| 600 | $condition = "$nameCol IS NULL"; |
||
| 601 | } else { |
||
| 602 | if ($matchMode === MatchMode::Exact) { |
||
| 603 | $condition = "LOWER($nameCol) = LOWER(?)"; |
||
| 604 | } else { |
||
| 605 | $condition = "LOWER($nameCol) LIKE LOWER(?)"; |
||
| 606 | } |
||
| 607 | if ($matchMode === MatchMode::Substring) { |
||
| 608 | $params[] = self::prepareSubstringSearchPattern($name); |
||
| 609 | } else { |
||
| 610 | $params[] = $name; |
||
| 611 | } |
||
| 612 | } |
||
| 613 | return [$condition, $params]; |
||
| 614 | } |
||
| 615 | |||
| 616 | /** |
||
| 617 | * @return array with two values: The SQL condition as string and the SQL parameters as string[] |
||
| 618 | */ |
||
| 619 | protected function formatTimestampConditions(?string $createdMin, ?string $createdMax, ?string $updatedMin, ?string $updatedMax) : array { |
||
| 620 | $conditions = []; |
||
| 621 | $params = []; |
||
| 622 | |||
| 623 | if (!empty($createdMin)) { |
||
| 624 | $conditions[] = "`{$this->getTableName()}`.`created` >= ?"; |
||
| 625 | $params[] = $createdMin; |
||
| 626 | } |
||
| 627 | |||
| 628 | if (!empty($createdMax)) { |
||
| 629 | $conditions[] = "`{$this->getTableName()}`.`created` <= ?"; |
||
| 630 | $params[] = $createdMax; |
||
| 631 | } |
||
| 632 | |||
| 633 | if (!empty($updatedMin)) { |
||
| 634 | $conditions[] = "`{$this->getTableName()}`.`updated` >= ?"; |
||
| 635 | $params[] = $updatedMin; |
||
| 636 | } |
||
| 637 | |||
| 638 | if (!empty($updatedMax)) { |
||
| 639 | $conditions[] = "`{$this->getTableName()}`.`updated` <= ?"; |
||
| 640 | $params[] = $updatedMax; |
||
| 641 | } |
||
| 642 | |||
| 643 | return [\implode(' AND ', $conditions), $params]; |
||
| 644 | } |
||
| 645 | |||
| 646 | /** |
||
| 647 | * Convert given sorting condition to an SQL clause. Derived class may override this if necessary. |
||
| 648 | * @param int $sortBy One of the constants defined in the class SortBy |
||
| 649 | */ |
||
| 650 | protected function formatSortingClause(int $sortBy, bool $invertSort = false) : ?string { |
||
| 651 | $table = $this->getTableName(); |
||
| 652 | if ($sortBy == SortBy::Name) { |
||
| 653 | $dir = $invertSort ? 'DESC' : 'ASC'; |
||
| 654 | return "ORDER BY LOWER(`$table`.`{$this->nameColumn}`) $dir"; |
||
| 655 | } elseif ($sortBy == SortBy::Newest) { |
||
| 656 | $dir = $invertSort ? 'ASC' : 'DESC'; |
||
| 657 | return "ORDER BY `$table`.`id` $dir"; // abuse the fact that IDs are ever-incrementing values |
||
| 658 | } elseif ($sortBy == SortBy::Rating) { |
||
| 659 | if (\property_exists($this->entityClass, 'rating')) { |
||
| 660 | $dir = $invertSort ? 'ASC' : 'DESC'; |
||
| 661 | return "ORDER BY `$table`.`rating` $dir"; |
||
| 662 | } else { |
||
| 663 | return null; |
||
| 664 | } |
||
| 665 | } else { |
||
| 666 | return null; |
||
| 667 | } |
||
| 668 | } |
||
| 669 | |||
| 670 | /** |
||
| 671 | * Return an SQL condition to exclude entities having no children. The default implementation is empty |
||
| 672 | * and derived classes may override this if applicable. |
||
| 673 | */ |
||
| 674 | protected function formatExcludeChildlessCondition() : string { |
||
| 675 | return '1=1'; |
||
| 676 | } |
||
| 677 | |||
| 678 | protected static function prepareSubstringSearchPattern(string $input) : string { |
||
| 692 | } |
||
| 693 | |||
| 694 | /** |
||
| 695 | * Format SQL operator, conversion, and parameter matching the given advanced search operator. |
||
| 696 | * @return array{op: string, conv: string, param: string|int|null} |
||
| 697 | */ |
||
| 698 | protected function advFormatSqlOperator(string $ruleOperator, string $ruleInput, string $userId) { |
||
| 699 | if ($this->dbType == 'sqlite3' && ($ruleOperator == 'regexp' || $ruleOperator == 'notregexp')) { |
||
| 700 | $this->registerRegexpFuncForSqlite(); |
||
| 701 | } |
||
| 702 | |||
| 703 | $pgsql = ($this->dbType == 'pgsql'); |
||
| 704 | |||
| 705 | switch ($ruleOperator) { |
||
| 706 | case 'contain': return ['op' => 'LIKE', 'conv' => 'LOWER', 'param' => "%$ruleInput%"]; |
||
| 707 | case 'notcontain': return ['op' => 'NOT LIKE', 'conv' => 'LOWER', 'param' => "%$ruleInput%"]; |
||
| 708 | case 'start': return ['op' => 'LIKE', 'conv' => 'LOWER', 'param' => "$ruleInput%"]; |
||
| 709 | case 'end': return ['op' => 'LIKE', 'conv' => 'LOWER', 'param' => "%$ruleInput"]; |
||
| 710 | case 'is': return ['op' => '=', 'conv' => 'LOWER', 'param' => "$ruleInput"]; |
||
| 711 | case 'isnot': return ['op' => '!=', 'conv' => 'LOWER', 'param' => "$ruleInput"]; |
||
| 712 | case 'sounds': return ['op' => '=', 'conv' => 'SOUNDEX', 'param' => $ruleInput]; // requires extension `fuzzystrmatch` on PgSQL |
||
| 713 | case 'notsounds': return ['op' => '!=', 'conv' => 'SOUNDEX', 'param' => $ruleInput]; // requires extension `fuzzystrmatch` on PgSQL |
||
| 714 | case 'regexp': return ['op' => $pgsql ? '~' : 'REGEXP', 'conv' => 'LOWER', 'param' => $ruleInput]; |
||
| 715 | case 'notregexp': return ['op' => $pgsql ? '!~' : 'NOT REGEXP', 'conv' => 'LOWER', 'param' => $ruleInput]; |
||
| 716 | case 'true': return ['op' => 'IS NOT NULL', 'conv' => '', 'param' => null]; |
||
| 717 | case 'false': return ['op' => 'IS NULL', 'conv' => '', 'param' => null]; |
||
| 718 | case 'equal': return ['op' => '', 'conv' => '', 'param' => $ruleInput]; |
||
| 719 | case 'ne': return ['op' => 'NOT', 'conv' => '', 'param' => $ruleInput]; |
||
| 720 | case 'limit': return ['op' => (string)(int)$ruleInput, 'conv' => '', 'param' => $userId]; // this is a bit hacky, userId needs to be passed as an SQL param while simple sanitation suffices for the limit |
||
| 721 | case 'before': return ['op' => '<', 'conv' => '', 'param' => $ruleInput]; |
||
| 722 | case 'after': return ['op' => '>', 'conv' => '', 'param' => $ruleInput]; |
||
| 723 | default: return ['op' => self::sanitizeNumericOp($ruleOperator), 'conv' => '', 'param' => (int)$ruleInput]; // all numerical operators fall here |
||
| 724 | } |
||
| 725 | } |
||
| 726 | |||
| 727 | protected static function sanitizeNumericOp(string $comparisonOperator) : string { |
||
| 728 | if (\in_array($comparisonOperator, ['>=', '<=', '=', '!=', '>', '<'])) { |
||
| 729 | return $comparisonOperator; |
||
| 730 | } else { |
||
| 731 | throw new \DomainException("Bad advanced search operator: $comparisonOperator"); |
||
| 732 | } |
||
| 733 | } |
||
| 734 | |||
| 735 | /** |
||
| 736 | * Format SQL condition matching the given advanced search rule and SQL operator. |
||
| 737 | * Derived classes should override this to provide support for table-specific rules. |
||
| 738 | * @param string $rule Identifier of the property which is the target of the SQL condition. The identifiers match the Ampache API specification. |
||
| 739 | * @param string $sqlOp SQL (comparison) operator to be used |
||
| 740 | * @param string $conv SQL conversion function to be applied on the target column and the parameter (e.g. "LOWER") |
||
| 741 | * @return string SQL condition statement to be used in the "WHERE" clause |
||
| 742 | */ |
||
| 743 | protected function advFormatSqlCondition(string $rule, string $sqlOp, string $conv) : string { |
||
| 759 | } |
||
| 760 | } |
||
| 761 | |||
| 762 | protected function sqlConcat(string ...$parts) : string { |
||
| 763 | if ($this->dbType == 'sqlite3') { |
||
| 764 | return '(' . \implode(' || ', $parts) . ')'; |
||
| 765 | } else { |
||
| 766 | return 'CONCAT(' . \implode(', ', $parts) . ')'; |
||
| 767 | } |
||
| 768 | } |
||
| 769 | |||
| 770 | protected function sqlGroupConcat(string $column) : string { |
||
| 771 | if ($this->dbType == 'pgsql') { |
||
| 772 | return "string_agg($column, ',')"; |
||
| 773 | } else { |
||
| 774 | return "GROUP_CONCAT($column)"; |
||
| 775 | } |
||
| 776 | } |
||
| 777 | |||
| 778 | protected function sqlCoalesce(string $value, string $replacement) : string { |
||
| 779 | if ($this->dbType == 'pgsql') { |
||
| 780 | return "COALESCE($value, $replacement)"; |
||
| 781 | } else { |
||
| 782 | return "IFNULL($value, $replacement)"; |
||
| 783 | } |
||
| 784 | } |
||
| 785 | |||
| 786 | /** |
||
| 787 | * @param string $datetime Date-and-time string in UTC |
||
| 788 | */ |
||
| 789 | protected function sqlDateToEpoch(string $datetime) : string { |
||
| 790 | if ($this->dbType == 'pgsql') { |
||
| 791 | return "DATE_PART('EPOCH', $datetime)"; |
||
| 792 | } elseif ($this->dbType == 'sqlite3') { |
||
| 793 | return "CAST(strftime('%s', $datetime) AS INT)"; |
||
| 794 | } else { // 'mysql' |
||
| 795 | // MySQL function UNIX_TIMESTAMP "helpfully" converts given datetime from session timezone to UTC |
||
| 796 | // and there's no way to prevent this. Hence, we need to convert the UTC first to the session time zone. |
||
| 797 | // In addition to being stupid, this is ambiguous for one hour per year when the DST ends. As result, this |
||
| 798 | // function is slightly bugged and the result is off-by-one-hour during this DST shift hour. |
||
| 799 | return "UNIX_TIMESTAMP(CONVERT_TZ($datetime, '+00:00', @@session.time_zone))"; |
||
| 800 | } |
||
| 801 | } |
||
| 802 | |||
| 803 | /** |
||
| 804 | * SQLite connects the operator REGEXP to the function of the same name but doesn't ship the function itself. |
||
| 805 | * Hence, we need to register it as a user-function. This happens by creating a suitable wrapper for the PHP |
||
| 806 | * native preg_match function. Based on https://stackoverflow.com/a/18484596. |
||
| 807 | */ |
||
| 808 | private function registerRegexpFuncForSqlite() : void { |
||
| 828 | } |
||
| 829 | ); |
||
| 830 | } |
||
| 831 | } |
||
| 832 | } |
||
| 833 | |||
| 834 | private function funcExistsInSqlite(string $funcName) : bool { |
||
| 835 | // If the SQLite version is very old, it may not have the `pragma_function_list` table available. In such cases, |
||
| 836 | // assume that the queried function doesn't exist. It doesn't really make any harm if that leads to registering |
||
| 837 | // the same function again. |
||
| 838 | try { |
||
| 839 | $result = $this->execute('SELECT EXISTS(SELECT 1 FROM `pragma_function_list` WHERE `NAME` = ?)', [$funcName]); |
||
| 840 | $row = $result->fetch(); |
||
| 841 | $result->closeCursor(); |
||
| 842 | return (bool)\current($row); |
||
| 843 | } catch (\Exception $e) { |
||
| 844 | return false; |
||
| 845 | } |
||
| 846 | } |
||
| 847 | |||
| 848 | /** |
||
| 849 | * Find ID of an existing entity which conflicts with the unique constraint of the given entity |
||
| 850 | */ |
||
| 851 | private function findIdOfConflict(Entity $entity) : int { |
||
| 872 | } |
||
| 873 | |||
| 874 | private function getCreated(int $id) : string { |
||
| 875 | $sql = "SELECT `created` FROM {$this->getTableName()} WHERE `id` = ?"; |
||
| 876 | $result = $this->execute($sql, [$id]); |
||
| 877 | /** @var string|false $created */ // phpdoc for \Doctrine\DBAL\Driver\Statement::fetchColumn is erroneous and omits the `false` |
||
| 884 | } |
||
| 885 | } |
||
| 886 |
Loading history...
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.