4
0

EntryDAOSQLite.php 6.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215
  1. <?php
  2. declare(strict_types=1);
  3. class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
  4. #[\Override]
  5. public static function isCompressed(): bool {
  6. return false;
  7. }
  8. #[\Override]
  9. public static function hasNativeHex(): bool {
  10. return false;
  11. }
  12. #[\Override]
  13. protected static function sqlConcat(string $s1, string $s2): string {
  14. return $s1 . '||' . $s2;
  15. }
  16. #[\Override]
  17. public static function sqlHexDecode(string $x): string {
  18. return $x;
  19. }
  20. #[\Override]
  21. public static function sqlIgnoreConflict(string $sql): string {
  22. return str_replace('INSERT INTO ', 'INSERT OR IGNORE INTO ', $sql);
  23. }
  24. #[\Override]
  25. protected static function sqlLimitAll(): string {
  26. // https://sqlite.org/lang_select.html#the_limit_clause
  27. return '-1';
  28. }
  29. #[\Override]
  30. public static function sqlRandom(): string {
  31. return 'RANDOM()';
  32. }
  33. #[\Override]
  34. protected static function sqlRegex(string $expression, string $regex, array &$values): string {
  35. $values[] = $regex;
  36. return "{$expression} REGEXP ?";
  37. }
  38. #[\Override]
  39. protected function registerSqlFunctions(string $sql): void {
  40. if (!str_contains($sql, ' REGEXP ')) {
  41. return;
  42. }
  43. // https://www.php.net/pdo-sqlite.createfunction
  44. // https://www.sqlite.org/lang_expr.html#the_like_glob_regexp_match_and_extract_operators
  45. if (class_exists(\Pdo\Sqlite::class) && $this->pdo instanceof \Pdo\Sqlite) {
  46. $this->pdo->createFunction('regexp',
  47. function (string $pattern, string $text): bool {
  48. return preg_match($pattern, $text) === 1;
  49. },
  50. 2
  51. );
  52. } else { // PHP < 8.4
  53. $this->pdo->sqliteCreateFunction('regexp',
  54. function (string $pattern, string $text): bool {
  55. return preg_match($pattern, $text) === 1;
  56. },
  57. 2
  58. );
  59. }
  60. }
  61. /** @param array{0:string,1:int,2:string} $errorInfo */
  62. #[\Override]
  63. protected function autoUpdateDb(array $errorInfo): bool {
  64. $columns = $this->fetchColumn("PRAGMA table_info('entry')", 1);
  65. if ($columns !== null) {
  66. foreach (['attributes', 'lastUserModified', 'lastModified'] as $column) {
  67. if (!in_array($column, $columns, true)) {
  68. return $this->addColumn($column);
  69. }
  70. }
  71. }
  72. return false;
  73. }
  74. #[\Override]
  75. public function commitNewEntries(): bool {
  76. $sql = <<<'SQL'
  77. DROP TABLE IF EXISTS `tmp`;
  78. CREATE TEMP TABLE `tmp` AS
  79. SELECT id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes
  80. FROM `_entrytmp`
  81. ORDER BY date, id;
  82. INSERT OR IGNORE INTO `_entry`
  83. (id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes)
  84. SELECT rowid + (SELECT MAX(id) - COUNT(*) FROM `tmp`) AS id,
  85. guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes
  86. FROM `tmp` t
  87. ORDER BY t.date, t.id;
  88. DELETE FROM `_entrytmp` WHERE id <= (SELECT MAX(id) FROM `tmp`);
  89. DROP TABLE IF EXISTS `tmp`;
  90. SQL;
  91. $hadTransaction = $this->pdo->inTransaction();
  92. if (!$hadTransaction) {
  93. $this->pdo->beginTransaction();
  94. }
  95. $result = $this->pdo->exec($sql) !== false;
  96. if (!$result) {
  97. Minz_Log::error('SQL error ' . __METHOD__ . json_encode($this->pdo->errorInfo()));
  98. }
  99. if (!$hadTransaction) {
  100. $this->pdo->commit();
  101. }
  102. return $result;
  103. }
  104. /**
  105. * Toggle the read marker on one or more article.
  106. * Then the cache is updated.
  107. *
  108. * @param numeric-string|list<numeric-string> $ids
  109. * @return int|false affected rows
  110. */
  111. #[\Override]
  112. public function markRead(array|string $ids, bool $is_read = true): int|false {
  113. if (is_array($ids)) { //Many IDs at once (used by API)
  114. return parent::markRead($ids, $is_read);
  115. } else {
  116. FreshRSS_UserDAO::touch();
  117. $this->pdo->beginTransaction();
  118. $sql = <<<'SQL'
  119. UPDATE `_entry` SET is_read=:is_read, `lastUserModified` = :last_user_modified
  120. WHERE id=:id AND is_read=:previous_is_read
  121. SQL;
  122. $stm = $this->pdo->prepare($sql);
  123. if ($stm === false ||
  124. !$stm->bindValue(':is_read', $is_read ? 1 : 0, PDO::PARAM_INT) ||
  125. !$stm->bindValue(':last_user_modified', time(), PDO::PARAM_INT) ||
  126. !$stm->bindValue(':id', $ids, PDO::PARAM_STR) || // TODO: Test PDO::PARAM_INT on 32-bit platform
  127. !$stm->bindValue(':previous_is_read', $is_read ? 0 : 1, PDO::PARAM_INT) ||
  128. !$stm->execute()) {
  129. $info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
  130. /** @var array{0:string,1:int,2:string} $info */
  131. if ($this->autoUpdateDb($info)) {
  132. return $this->markRead($ids, $is_read);
  133. } else {
  134. Minz_Log::error('SQL error ' . __METHOD__ . ' A ' . json_encode($info));
  135. $this->pdo->rollBack();
  136. return false;
  137. }
  138. }
  139. $affected = $stm->rowCount();
  140. if ($affected > 0) {
  141. $delta = $is_read ? '-1' : '+1';
  142. $sql = <<<SQL
  143. UPDATE `_feed` SET `cache_nbUnreads`=`cache_nbUnreads` {$delta}
  144. WHERE id=(SELECT e.id_feed FROM `_entry` e WHERE e.id=:id)
  145. SQL;
  146. $stm = $this->pdo->prepare($sql);
  147. if ($stm === false ||
  148. !$stm->bindValue(':id', $ids, PDO::PARAM_STR) ||
  149. !$stm->execute()) {
  150. $info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
  151. Minz_Log::error('SQL error ' . __METHOD__ . ' B ' . json_encode($info));
  152. $this->pdo->rollBack();
  153. return false;
  154. }
  155. }
  156. $this->pdo->commit();
  157. if ($affected > 0) {
  158. Minz_ExtensionManager::callHook(Minz_HookType::EntriesRead, [$ids], $is_read);
  159. }
  160. return $affected;
  161. }
  162. }
  163. /**
  164. * Mark all the articles in a tag as read.
  165. * @param int $id tag ID, or empty for targeting any tag
  166. * @param string $idMax max article ID
  167. * @return int|false affected rows
  168. */
  169. #[\Override]
  170. public function markReadTag(int $id = 0, string $idMax = '0', ?FreshRSS_BooleanSearch $filters = null, int $state = 0, bool $is_read = true): int|false {
  171. FreshRSS_UserDAO::touch();
  172. if ($idMax == 0) {
  173. $idMax = uTimeString();
  174. Minz_Log::debug('Calling markReadTag(0) is deprecated!');
  175. }
  176. $tagCondition = $id == 0 ? '' : 'WHERE et.id_tag = ?';
  177. $sql = <<<SQL
  178. UPDATE `_entry` SET is_read = ?, `lastUserModified` = ? WHERE is_read <> ? AND id <= ?
  179. AND id IN (SELECT et.id_entry FROM `_entrytag` et {$tagCondition})
  180. SQL;
  181. $values = [$is_read ? 1 : 0, time(), $is_read ? 1 : 0, $idMax];
  182. if ($id != 0) {
  183. $values[] = $id;
  184. }
  185. [$searchValues, $search] = $this->sqlListEntriesWhere(alias: '', state: $state, filters: $filters);
  186. $stm = $this->pdo->prepare($sql . $search);
  187. if ($stm === false || !$stm->execute(array_merge($values, $searchValues))) {
  188. $info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
  189. Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
  190. return false;
  191. }
  192. $affected = $stm->rowCount();
  193. if (($affected > 0) && (!$this->updateCacheUnreads(null, null))) {
  194. return false;
  195. }
  196. return $affected;
  197. }
  198. }