mirror of
https://github.com/FreshRSS/FreshRSS.git
synced 2026-05-20 09:30:36 +00:00
Alexandre Alapetite
aeb55693e4
* New SQL wrapper function `fetchInt()`
* Favour use of `fetchAssoc()`, `fetchInt()`, `fetchColumn()`
* Favour Nowdoc / Heredoc syntax for SQL
* Update indenting to PHP 8.1+ convention
* Favour `bindValue()` instead of position `?` when possible
* Favour `bindValue()` over `bindParam()`
* More uniform and robust syntax when using `bindValue()`, checking return code
473 lines
14 KiB
PHP
473 lines
14 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
|
|
class FreshRSS_TagDAO extends Minz_ModelPdo {
|
|
|
|
public function sqlIgnore(): string {
|
|
return 'IGNORE';
|
|
}
|
|
|
|
public function sqlResetSequence(): bool {
|
|
return true; // Nothing to do for MySQL
|
|
}
|
|
|
|
/**
|
|
* @param array{id?:int,name:string,attributes?:array<string,mixed>} $valuesTmp
|
|
*/
|
|
public function addTag(array $valuesTmp): int|false {
|
|
if (empty($valuesTmp['id'])) { // Auto-generated ID
|
|
$sql = <<<'SQL'
|
|
INSERT INTO `_tag`(name, attributes)
|
|
SELECT * FROM (SELECT :name1 AS name, :attributes AS attributes) t2
|
|
SQL;
|
|
} else {
|
|
$sql = <<<'SQL'
|
|
INSERT INTO `_tag`(id, name, attributes)
|
|
SELECT * FROM (SELECT 1*:id AS id, :name1 AS name, :attributes AS attributes) t2
|
|
SQL;
|
|
}
|
|
// No category of the same name
|
|
$sql .= "\n" . <<<'SQL'
|
|
WHERE NOT EXISTS (SELECT 1 FROM `_category` WHERE name = :name2)
|
|
SQL;
|
|
$stm = $this->pdo->prepare($sql);
|
|
|
|
$valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE, 'UTF-8');
|
|
if (!isset($valuesTmp['attributes'])) {
|
|
$valuesTmp['attributes'] = [];
|
|
}
|
|
|
|
if ($stm !== false &&
|
|
(empty($valuesTmp['id']) || $stm->bindValue(':id', $valuesTmp['id'], PDO::PARAM_INT)) &&
|
|
$stm->bindValue(':name1', $valuesTmp['name'], PDO::PARAM_STR) &&
|
|
$stm->bindValue(':name2', $valuesTmp['name'], PDO::PARAM_STR) &&
|
|
$stm->bindValue(':attributes', is_string($valuesTmp['attributes']) ? $valuesTmp['attributes'] :
|
|
json_encode($valuesTmp['attributes'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE), PDO::PARAM_STR) &&
|
|
$stm->execute() && $stm->rowCount() > 0) {
|
|
if (empty($valuesTmp['id'])) {
|
|
// Auto-generated ID
|
|
$tagId = $this->pdo->lastInsertId('`_tag_id_seq`');
|
|
return $tagId === false ? false : (int)$tagId;
|
|
}
|
|
$this->sqlResetSequence();
|
|
return $valuesTmp['id'];
|
|
} else {
|
|
$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public function addTagObject(FreshRSS_Tag $tag): int|false {
|
|
$tag0 = $this->searchByName($tag->name());
|
|
if ($tag0 === null) {
|
|
$values = [
|
|
'name' => $tag->name(),
|
|
'attributes' => $tag->attributes(),
|
|
];
|
|
return $this->addTag($values);
|
|
}
|
|
return $tag->id();
|
|
}
|
|
|
|
public function updateTagName(int $id, string $name): int|false {
|
|
// No category of the same name
|
|
$sql = <<<'SQL'
|
|
UPDATE `_tag` SET name = :name1 WHERE id = :id
|
|
AND NOT EXISTS (SELECT 1 FROM `_category` WHERE name = :name2)
|
|
SQL;
|
|
|
|
$name = mb_strcut(trim($name), 0, FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE, 'UTF-8');
|
|
$stm = $this->pdo->prepare($sql);
|
|
if ($stm !== false &&
|
|
$stm->bindValue(':id', $id, PDO::PARAM_INT) &&
|
|
$stm->bindValue(':name1', $name, PDO::PARAM_STR) &&
|
|
$stm->bindValue(':name2', $name, PDO::PARAM_STR) &&
|
|
$stm->execute()) {
|
|
return $stm->rowCount();
|
|
} else {
|
|
$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param array<string,mixed> $attributes
|
|
*/
|
|
public function updateTagAttributes(int $id, array $attributes): int|false {
|
|
$sql = <<<'SQL'
|
|
UPDATE `_tag` SET attributes=:attributes WHERE id=:id
|
|
SQL;
|
|
$stm = $this->pdo->prepare($sql);
|
|
if ($stm !== false &&
|
|
$stm->bindValue(':id', $id, PDO::PARAM_INT) &&
|
|
$stm->bindValue(':attributes', json_encode($attributes, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE), PDO::PARAM_STR) &&
|
|
$stm->execute()) {
|
|
return $stm->rowCount();
|
|
}
|
|
$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* @param non-empty-string $key
|
|
*/
|
|
public function updateTagAttribute(FreshRSS_Tag $tag, string $key, mixed $value): int|false {
|
|
$tag->_attribute($key, $value);
|
|
return $this->updateTagAttributes($tag->id(), $tag->attributes());
|
|
}
|
|
|
|
public function deleteTag(int $id): int|false {
|
|
if ($id <= 0) {
|
|
return false;
|
|
}
|
|
$sql = <<<'SQL'
|
|
DELETE FROM `_tag` WHERE id=:id
|
|
SQL;
|
|
$stm = $this->pdo->prepare($sql);
|
|
if ($stm !== false &&
|
|
$stm->bindValue(':id', $id, PDO::PARAM_INT) &&
|
|
$stm->execute()) {
|
|
return $stm->rowCount();
|
|
} else {
|
|
$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/** @return Traversable<array{id:int,name:string,attributes?:array<string,mixed>}> */
|
|
public function selectAll(): Traversable {
|
|
$sql = <<<'SQL'
|
|
SELECT id, name, attributes FROM `_tag`
|
|
SQL;
|
|
$stm = $this->pdo->query($sql);
|
|
if ($stm === false) {
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($this->pdo->errorInfo()));
|
|
return;
|
|
}
|
|
while (is_array($row = $stm->fetch(PDO::FETCH_ASSOC))) {
|
|
/** @var array{id:int,name:string,attributes?:array<string,mixed>} $row */
|
|
yield $row;
|
|
}
|
|
}
|
|
|
|
/** @return Traversable<array{id_tag:int,id_entry:int|numeric-string}> */
|
|
public function selectEntryTag(): Traversable {
|
|
$sql = <<<'SQL'
|
|
SELECT id_tag, id_entry FROM `_entrytag`
|
|
SQL;
|
|
$stm = $this->pdo->query($sql);
|
|
if ($stm === false) {
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($this->pdo->errorInfo()));
|
|
return;
|
|
}
|
|
while (is_array($row = $stm->fetch(PDO::FETCH_ASSOC))) {
|
|
/** @var array{id_tag:int,id_entry:int|numeric-string} $row */
|
|
yield $row;
|
|
}
|
|
}
|
|
|
|
public function updateEntryTag(int $oldTagId, int $newTagId): int|false {
|
|
$sql = <<<'SQL'
|
|
DELETE FROM `_entrytag` WHERE EXISTS (
|
|
SELECT 1 FROM `_entrytag` AS e
|
|
WHERE e.id_entry = `_entrytag`.id_entry AND e.id_tag = :new_tag_id AND `_entrytag`.id_tag = :old_tag_id)
|
|
SQL;
|
|
$stm = $this->pdo->prepare($sql);
|
|
if ($stm === false ||
|
|
!$stm->bindValue(':new_tag_id', $newTagId, PDO::PARAM_INT) ||
|
|
!$stm->bindValue(':old_tag_id', $oldTagId, PDO::PARAM_INT) ||
|
|
!$stm->execute()) {
|
|
$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . ' A ' . json_encode($info));
|
|
return false;
|
|
}
|
|
|
|
$sql = <<<'SQL'
|
|
UPDATE `_entrytag` SET id_tag = :new_tag_id WHERE id_tag = :old_tag_id
|
|
SQL;
|
|
$stm = $this->pdo->prepare($sql);
|
|
if ($stm !== false &&
|
|
$stm->bindValue(':new_tag_id', $newTagId, PDO::PARAM_INT) &&
|
|
$stm->bindValue(':old_tag_id', $oldTagId, PDO::PARAM_INT) &&
|
|
$stm->execute()) {
|
|
return $stm->rowCount();
|
|
}
|
|
$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . ' B ' . json_encode($info));
|
|
return false;
|
|
}
|
|
|
|
public function searchById(int $id): ?FreshRSS_Tag {
|
|
$res = $this->fetchAssoc('SELECT * FROM `_tag` WHERE id=:id', [':id' => $id]);
|
|
/** @var list<array{id:int,name:string,attributes?:string}>|null $res */
|
|
return $res === null ? null : (current(self::daoToTags($res)) ?: null);
|
|
}
|
|
|
|
public function searchByName(string $name): ?FreshRSS_Tag {
|
|
$res = $this->fetchAssoc('SELECT * FROM `_tag` WHERE name=:name', [':name' => $name]);
|
|
/** @var list<array{id:int,name:string,attributes?:string}>|null $res */
|
|
return $res === null ? null : (current(self::daoToTags($res)) ?: null);
|
|
}
|
|
|
|
/** @return array<int,FreshRSS_Tag> where the key is the label ID */
|
|
public function listTags(bool $precounts = false): array {
|
|
if ($precounts) {
|
|
$sql = <<<'SQL'
|
|
SELECT t.id, t.name, count(e.id) AS unreads
|
|
FROM `_tag` t
|
|
LEFT OUTER JOIN `_entrytag` et ON et.id_tag = t.id
|
|
LEFT OUTER JOIN `_entry` e ON et.id_entry = e.id AND e.is_read = 0
|
|
GROUP BY t.id
|
|
ORDER BY t.name
|
|
SQL;
|
|
} else {
|
|
$sql = <<<'SQL'
|
|
SELECT * FROM `_tag` ORDER BY name
|
|
SQL;
|
|
}
|
|
|
|
$res = $this->fetchAssoc($sql);
|
|
if ($res !== null) {
|
|
/** @var list<array{id:int,name:string,unreads:int}> $res */
|
|
return self::daoToTags($res);
|
|
} else {
|
|
$info = $this->pdo->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
|
|
return [];
|
|
}
|
|
}
|
|
|
|
/** @return array<string,string> */
|
|
public function listTagsNewestItemUsec(?int $id_tag = null): array {
|
|
$sql = <<<'SQL'
|
|
SELECT t.id AS id_tag, MAX(e.id) AS newest_item_us
|
|
FROM `_tag` t
|
|
LEFT OUTER JOIN `_entrytag` et ON et.id_tag = t.id
|
|
LEFT OUTER JOIN `_entry` e ON et.id_entry = e.id
|
|
SQL;
|
|
if ($id_tag === null) {
|
|
$sql .= "\n" . <<<'SQL'
|
|
GROUP BY t.id
|
|
SQL;
|
|
} else {
|
|
$sql .= "\n" . <<<SQL
|
|
WHERE t.id={$id_tag}
|
|
SQL;
|
|
}
|
|
$res = $this->fetchAssoc($sql);
|
|
if ($res == null) {
|
|
return [];
|
|
}
|
|
$newestItemUsec = [];
|
|
foreach ($res as $line) {
|
|
$newestItemUsec['t_' . $line['id_tag']] = (string)($line['newest_item_us']);
|
|
}
|
|
return $newestItemUsec;
|
|
}
|
|
|
|
public function count(): int {
|
|
$sql = <<<'SQL'
|
|
SELECT COUNT(*) AS count FROM `_tag`
|
|
SQL;
|
|
return $this->fetchInt($sql) ?? -1;
|
|
}
|
|
|
|
public function countEntries(int $id): int {
|
|
$sql = <<<'SQL'
|
|
SELECT COUNT(*) AS count FROM `_entrytag` WHERE id_tag=:id_tag
|
|
SQL;
|
|
return $this->fetchInt($sql, [':id_tag' => $id]) ?? -1;
|
|
}
|
|
|
|
public function countNotRead(?int $id = null): int {
|
|
$sql = <<<'SQL'
|
|
SELECT COUNT(*) AS count FROM `_entrytag` et
|
|
INNER JOIN `_entry` e ON et.id_entry=e.id
|
|
WHERE e.is_read=0
|
|
SQL;
|
|
$values = [];
|
|
if (null !== $id) {
|
|
$sql .= "\n" . <<<'SQL'
|
|
AND et.id_tag=:id_tag
|
|
SQL;
|
|
$values[':id_tag'] = $id;
|
|
}
|
|
return $this->fetchInt($sql, $values) ?? -1;
|
|
}
|
|
|
|
public function tagEntry(int $id_tag, string $id_entry, bool $checked = true): bool {
|
|
if ($checked) {
|
|
$ignore = $this->sqlIgnore();
|
|
$sql = <<<SQL
|
|
INSERT {$ignore} INTO `_entrytag`(id_tag, id_entry) VALUES(:id_tag, :id_entry)
|
|
SQL;
|
|
} else {
|
|
$sql = <<<'SQL'
|
|
DELETE FROM `_entrytag` WHERE id_tag=:id_tag AND id_entry=:id_entry
|
|
SQL;
|
|
}
|
|
$stm = $this->pdo->prepare($sql);
|
|
|
|
if ($stm !== false &&
|
|
$stm->bindValue(':id_tag', $id_tag, PDO::PARAM_INT) &&
|
|
$stm->bindValue(':id_entry', $id_entry, PDO::PARAM_STR) &&
|
|
$stm->execute()) {
|
|
return true;
|
|
}
|
|
$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* @param iterable<array{id_tag:int,id_entry:numeric-string|int}> $addLabels Labels to insert as batch
|
|
* @return int|false Number of new entries or false in case of error
|
|
*/
|
|
public function tagEntries(iterable $addLabels): int|false {
|
|
$hasValues = false;
|
|
$ignore = $this->sqlIgnore();
|
|
$sql = <<<SQL
|
|
INSERT {$ignore} INTO `_entrytag`(id_tag, id_entry) VALUES
|
|
SQL;
|
|
foreach ($addLabels as $addLabel) {
|
|
$id_tag = (int)($addLabel['id_tag'] ?? 0);
|
|
$id_entry = $addLabel['id_entry'] ?? '';
|
|
if ($id_tag > 0 && (is_int($id_entry) || ctype_digit($id_entry))) {
|
|
$sql .= "({$id_tag},{$id_entry}),";
|
|
$hasValues = true;
|
|
}
|
|
}
|
|
$sql = rtrim($sql, ',');
|
|
if (!$hasValues) {
|
|
return false;
|
|
}
|
|
|
|
$affected = $this->pdo->exec($sql);
|
|
if ($affected !== false) {
|
|
return $affected;
|
|
}
|
|
$info = $this->pdo->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* @return list<array{id:int,name:string,checked:bool}>
|
|
*/
|
|
public function getTagsForEntry(string $id_entry): array {
|
|
$sql = <<<'SQL'
|
|
SELECT t.id, t.name, et.id_entry IS NOT NULL as checked
|
|
FROM `_tag` t
|
|
LEFT OUTER JOIN `_entrytag` et ON et.id_tag = t.id AND et.id_entry=:id_entry
|
|
ORDER BY t.name
|
|
SQL;
|
|
$lines = $this->fetchAssoc($sql, [':id_entry' => $id_entry]);
|
|
if ($lines !== null) {
|
|
$result = [];
|
|
foreach ($lines as $line) {
|
|
/** @var array{id:int,name:string,checked:int} $line */
|
|
$result[] = [
|
|
'id' => (int)($line['id']),
|
|
'name' => $line['name'],
|
|
'checked' => !empty($line['checked']),
|
|
];
|
|
}
|
|
return $result;
|
|
}
|
|
$info = $this->pdo->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
|
|
return [];
|
|
}
|
|
|
|
/**
|
|
* @param list<FreshRSS_Entry|numeric-string> $entries
|
|
* @return list<array{id_entry:int|numeric-string,id_tag:int,name:string}>|null
|
|
*/
|
|
public function getTagsForEntries(array $entries): array|null {
|
|
$sql = <<<'SQL'
|
|
SELECT et.id_entry, et.id_tag, t.name
|
|
FROM `_tag` t
|
|
INNER JOIN `_entrytag` et ON et.id_tag = t.id
|
|
SQL;
|
|
|
|
$values = [];
|
|
if (count($entries) > 0) {
|
|
if (count($entries) > FreshRSS_DatabaseDAO::MAX_VARIABLE_NUMBER) {
|
|
// Split a query with too many variables parameters
|
|
$idsChunks = array_chunk($entries, FreshRSS_DatabaseDAO::MAX_VARIABLE_NUMBER);
|
|
foreach ($idsChunks as $idsChunk) {
|
|
$valuesChunk = $this->getTagsForEntries($idsChunk);
|
|
if (!is_array($valuesChunk)) {
|
|
return null;
|
|
}
|
|
$values = array_merge($values, $valuesChunk);
|
|
}
|
|
return $values;
|
|
}
|
|
$idPlaceholders = str_repeat('?,', count($entries) - 1) . '?';
|
|
$sql .= "\n" . <<<SQL
|
|
AND et.id_entry IN ({$idPlaceholders})
|
|
SQL;
|
|
foreach ($entries as $entry) {
|
|
$values[] = is_object($entry) ? $entry->id() : $entry;
|
|
}
|
|
}
|
|
$stm = $this->pdo->prepare($sql);
|
|
|
|
if ($stm !== false && $stm->execute($values)) {
|
|
$result = $stm->fetchAll(PDO::FETCH_ASSOC);
|
|
/** @var list<array{id_entry:int|numeric-string,id_tag:int,name:string}> $result; */
|
|
return $result;
|
|
}
|
|
$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
|
|
Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Produces an array: for each entry ID (prefixed by `e_`), associate a list of labels.
|
|
* Used by API and by JSON export, to speed up queries (would be very expensive to perform a label look-up on each entry individually).
|
|
* @param list<FreshRSS_Entry|numeric-string> $entries the list of entries for which to retrieve the labels.
|
|
* @return array<string,array<string>> An array of the shape `[e_id_entry => ["label 1", "label 2"]]`
|
|
*/
|
|
public function getEntryIdsTagNames(array $entries): array {
|
|
$result = [];
|
|
foreach ($this->getTagsForEntries($entries) ?? [] as $line) {
|
|
$entryId = 'e_' . $line['id_entry'];
|
|
$tagName = $line['name'];
|
|
if (empty($result[$entryId])) {
|
|
$result[$entryId] = [];
|
|
}
|
|
$result[$entryId][] = $tagName;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* @param iterable<array{id:int,name:string,attributes?:string,unreads?:int}> $listDAO
|
|
* @return array<int,FreshRSS_Tag> where the key is the label ID
|
|
*/
|
|
private static function daoToTags(iterable $listDAO): array {
|
|
$list = [];
|
|
foreach ($listDAO as $dao) {
|
|
if (empty($dao['id']) || empty($dao['name'])) {
|
|
continue;
|
|
}
|
|
$tag = new FreshRSS_Tag($dao['name']);
|
|
$tag->_id($dao['id']);
|
|
if (!empty($dao['attributes'])) {
|
|
$tag->_attributes($dao['attributes']);
|
|
}
|
|
if (isset($dao['unreads'])) {
|
|
$tag->_nbUnread($dao['unreads']);
|
|
}
|
|
$list[$tag->id()] = $tag;
|
|
}
|
|
return $list;
|
|
}
|
|
}
|