aboutsummaryrefslogtreecommitdiff
path: root/app/Models/DatabaseDAO.php
AgeCommit message (Collapse)Author
2026-01-10Fix MySQL transliterator_transliterate fallback (#8427)Gravatar Alexandre Alapetite
The string syntax of `strtr()` cannot handle mutibytes characters, so need to be rewritten using an array map approach. Extend the fallback replacements to include the Windows/ISO charsets of the latin languages for which we have a translation
2026-01-10Fix tags ILIKE (#8425)Gravatar Alexandre Alapetite
fix https://github.com/FreshRSS/FreshRSS/issues/8424 Regression from https://github.com/FreshRSS/FreshRSS/issues/8329
2025-12-20More uniform SQL search and PHP search (#8329)Gravatar Alexandre Alapetite
* More uniform SQL search and PHP search The behaviour depends though on the database. Improve https://github.com/FreshRSS/FreshRSS/discussions/8265#discussioncomment-15278980 * Try to use transliterator_transliterate function instead
2025-12-07Fix minor backslashes (#8292)Gravatar Alexandre Alapetite
2025-12-04Scaling of user statistics (#8277)Gravatar Alexandre Alapetite
Fix https://github.com/FreshRSS/FreshRSS/issues/8268 To better support user management on FreshRSS instance with many users. SQL speed improved. On a reduced test with 5 users, including some large accounts (PostgreSQL on a very tiny and slow server), improving from ~2.3s to ~1.8s, which gives ~20% speed improvement. Then tested with 1000 users, with only the default feed (on my old desktop computer): ```sh for i in {1..1000}; do ./cli/create-user.php --user=freshrss$i --password=freshrss; done app/actualize_script.php cli/access-permissions.sh ``` SQLite: ```console $ time cli/user-info.php | wc -l 1001 real 0m1.366s user 0m0.908s sys 0m0.475s ``` PostgreSQL: ```console $ time cli/user-info.php | wc -l 1001 real 0m28.498s user 0m12.137s sys 0m2.217s ``` MariaDB: ```console # time ./cli/user-info.php | wc -l 1001 real 0m49.485s user 0m1.276s sys 0m2.258s ``` Yes, SQLite is much faster - not a surprise for such use-cases, where the TCP connection is not re-used. I have added some CLI options to disable some statistics: ```sh cli/user-info.php --no-db-size --no-db-counts ``` For the Web UI, I have disabled detailed user statistics if it takes too long, and retrieve missing user statistics asynchronously via JavaScript. Lazy loading of the user details based on IntersectionObserver, with maximum 10 requests in parallel. Web UI tested on 1000 users as well. Checked with SeaMonkey.
2025-12-03Fix MySQL / MariaDB database size calculation (#8282)Gravatar Alexandre Alapetite
The `_` is a special character in the SQL `LIKE` expression and was not escaped, leading to users counting for other users size. Stats for `freshrss` user included users `freshrss1` and `freshrss2` (but not vice versa). Discovered during https://github.com/FreshRSS/FreshRSS/pull/8277
2025-09-27Stable IDs during SQL import (#7988)Gravatar Alexandre Alapetite
* Stable IDs during SQL import Follow-up of https://github.com/FreshRSS/FreshRSS/pull/7949 Make sure that the original category IDs, feed IDs, and label IDs are kept identical during an SQL import. Avoid breaking everything referring to categories, feeds, labels by their IDs such as searches and third-party extensions. * Fix export of default category
2025-09-14Recovery: skip broken entries during CLI export/import (#7949)Gravatar Alexandre Alapetite
* Recovery: skip broken entries during CLI export/import fix https://github.com/FreshRSS/FreshRSS/discussions/7927 ``` 25605/25605 (48 broken) ``` Help with *database malformed* or other corruption. * Compatibility multiple databases
2025-09-05Change how files are included (#7916)Gravatar Alexis Degrugillier
1. `include`, `include_once`, `require` and `require_once` are expressions not functions, parentheses are not necessary. 2. to move up the directory tree, it's better to use the `dirname` function instead of relying on `/..`.
2025-06-06Install: add test PDO typing (#7651)Gravatar Alexandre Alapetite
fix https://github.com/FreshRSS/FreshRSS/issues/7647
2025-05-17Add info about PDO::ATTR_CLIENT_VERSION (#7591)Gravatar Alexandre Alapetite
Help with https://github.com/FreshRSS/FreshRSS/issues/7586
2025-01-10Fix sqlite_version (#7205)Gravatar Alexandre Alapetite
* Fix sqlite_version for https://github.com/FreshRSS/FreshRSS/pull/7161 * Fix PostgreSQL
2024-12-27PHPStan 2.0 (#7131)Gravatar Alexandre Alapetite
* PHPStan 2.0 fix https://github.com/FreshRSS/FreshRSS/issues/6989 https://github.com/phpstan/phpstan/releases/tag/2.0.0 https://github.com/phpstan/phpstan/blob/2.0.x/UPGRADING.md * More * More * Done * fix i18n CLI * Restore a PHPStan Next test For work towards PHPStan Level 10 * 4 more on Level 10 * fix getTagsForEntry * API at Level 10 * More Level 10 * Finish Minz at Level 10 * Finish CLI at Level 10 * Finish Controllers at Level 10 * More Level 10 * More * Pass bleedingEdge * Clean PHPStan options and add TODOs * Level 10 for main config * More * Consitency array vs. list * Sanitize themes get_infos * Simplify TagDAO->getTagsForEntries() * Finish reportAnyTypeWideningInVarTag * Prepare checkBenevolentUnionTypes and checkImplicitMixed * Fixes * Refix * Another fix * Casing of __METHOD__ constant
2024-09-11PHPStan booleansInConditions (#6793)Gravatar Alexandre Alapetite
* PHPStan booleansInConditions * Uniformisation
2024-09-06Regex search (#6706)Gravatar Alexandre Alapetite
* Regex search fix https://github.com/FreshRSS/FreshRSS/issues/3549 * Fix PHPStan * Fix escape * Fix ungreedy * Initial support for regex search in PostgreSQL and MySQL * Improvements, support MySQL * Fix multiline * Add support for SQLite * A few tests * Added author: and inurl: support, documentation * author example * Remove \b for now * Disable regex sanitization for now * Fix getInurlRegex * getNotInurlRegex * Quotes for inurl: * Fix test * Fix quoted tags + regex for tags https://github.com/FreshRSS/FreshRSS/issues/6761 * Fix wrong regex detection * Add MariaDB * Fix logic * Increase requirements for MySQL and MariaDB Check support for multiline mode in MySQL * Remove sanitizeRegexes() * Allow searching HTML code Allow searching for instance `/<pre>/` Fix https://github.com/FreshRSS/FreshRSS/issues/6775#issuecomment-2331769883 * Doc regex search HTML * Fix Doctype
2024-06-29Add quiet option to cli/db-backup.php (#6593)Gravatar hkcomori
* Add quiet option to cli/db-backup.php * Fix CI test error * Fix README
2024-06-09Pass PHPStan level 9 (#6544)Gravatar Alexandre Alapetite
* More PHPStan * More, passing * 4 more files * Update to PHPStan 1.11.4 Needed for fixed bug: Consider numeric-string types after string concat https://github.com/phpstan/phpstan/releases/tag/1.11.4 * Pass PHPStan level 9 Start tracking booleansInConditions * Fix mark as read * Fix doctype * ctype_digit
2024-05-13Auto-update 5038 (#6279)Gravatar Alexandre Alapetite
* Auto-update 5038 https://github.com/FreshRSS/FreshRSS/pull/5038 * PostgreSQL * Draft for MySQL * More draft MySQL * Finalise * A bit more robust
2024-05-12Fix SQLite imports produced before FreshRSS 1.20.0 (#6450)Gravatar Alexandre Alapetite
fix https://github.com/FreshRSS/FreshRSS/issues/6088
2024-04-30CLI database backup and restore (#6387)Gravatar Alexandre Alapetite
* CLI database backup and restore Can also be used to migrate from one database to another (e.g. MySQL to PostgreSQL) or to ease upgrade to a major PostgreSQL version (e.g. 15 to 16). * +x * Fix some cases * Update to docker-compose-v2 * More documentation
2023-12-18Pass PHPStan level 8 (#5946)Gravatar Alexandre Alapetite
* Pass PHPStan level 8 And prepare for PHPStan level 9 https://phpstan.org/user-guide/rule-levels * Revert wrong replace in comment * Fix PHPStan level 8 * Update PHPStan and other dev dependencies * Remove obsolete comment * noVariableVariables and towards bleedingEdge https://github.com/phpstan/phpstan-strict-rules https://phpstan.org/blog/what-is-bleeding-edge * More bleedingEdge * A bit more PHPStan level 9 * More PHPStan level 9 * Prepare for booleansInConditions Ignore int and null * Revert wrong line * More fixes * Fix keep_max_n_unread * Stricter attribute functions * Stricter callHooks and more PHPStan level 9 * More typing * A tiny more
2023-12-06Fix export (#5927)Gravatar Alexandre Alapetite
Regression from https://github.com/FreshRSS/FreshRSS/pull/5830
2023-11-18Fix PHP 7 compatibility strict_types (#5893)Gravatar Alexandre Alapetite
* Fix PHP 7 compatibility https://github.com/FreshRSS/FreshRSS/discussions/5892 * Multiple PHP 7 fixes * PHPStan
2023-11-16Use strict_types (#5830)Gravatar Luc SANCHEZ
* Little's optimisations and booleans in conditions * Apply strict type * Apply strict type * Apply strict type * Fix multiple bugs with PHP 8.2 and 8.3 * Many declares missing, more errors fixed * Apply strict type * Another approach * Stronger typing for Minz_Session * Fix case of SQLite --------- Co-authored-by: Luc <sanchezluc+freshrss@gmail.com> Co-authored-by: Alexandre Alapetite <alexandre@alapetite.fr>
2023-10-31Rework SQL field length (#5788)Gravatar Alexandre Alapetite
* SQL auto-update field length Follow-up of https://github.com/FreshRSS/FreshRSS/pull/5756 Only for PostgreSQL and MySQL / MariaDB. Not possible for SQLite * Account for MySQL 65535 Partial revert of https://github.com/FreshRSS/FreshRSS/pull/5756 > The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead.
2023-10-30Ready for year 2038 (#5570)Gravatar Alexandre Alapetite
* Ready for year 2038 Fix https://github.com/FreshRSS/FreshRSS/discussions/5569 Requires PHP on a 64-bit platform to take advantage of it. https://en.wikipedia.org/wiki/Year_2038_problem * Allows dates past 2038 Rework of https://github.com/FreshRSS/FreshRSS/pull/3259 https://github.com/FreshRSS/FreshRSS/issues/3258 * Auto alter columns * Changelog
2023-09-14Fix MariaDB size calculation (#5655)Gravatar Alexandre Alapetite
MariaDB requires an `ANALYZE TABLE` to refresh the size information in the metadata. At the same time, include `DATA_FREE` in the calculation. https://mariadb.com/kb/en/information-schema-tables-table/
2023-09-12SQL: clean old auto-updates (#5649)Gravatar Alexandre Alapetite
Should help with some DB lock issues. Complete https://github.com/FreshRSS/FreshRSS/pull/3558 after https://github.com/FreshRSS/FreshRSS/pull/5625 already cherry-picked from it. * Removed auto-update of MySQL GUID case sensitivity https://github.com/FreshRSS/FreshRSS/pull/2078 * Contributed to a DB lock in https://github.com/FreshRSS/FreshRSS/issues/5008 Also removed the following non-problematic auto-updates, simply because they were older than the above ones * Auto-create custom labels (1.12.0) https://github.com/FreshRSS/FreshRSS/pull/2027 * Auto-add JSON column for feeds (1.11.0) https://github.com/FreshRSS/FreshRSS/pull/1838 * Auto-create temporary tables (1.7.0) https://github.com/FreshRSS/FreshRSS/pull/1470
2023-07-07Chore/processing of depreciations and updating code to php72 minimum (#5504)Gravatar Luc SANCHEZ
* processing of depreciations and updating of code to php7.2 minimum * Autoformat many strange array indenting And revert a few unwanted changes --------- Co-authored-by: Luc <sanchezluc+freshrss@gmail.com> Co-authored-by: Alexandre Alapetite <alexandre@alapetite.fr>
2023-05-11A few additional PHPStan rules (#5388)Gravatar Alexandre Alapetite
A subset of https://github.com/phpstan/phpstan-strict-rules
2023-04-28PHPStan Level 7 for more DAO PDO (#5328)Gravatar Alexandre Alapetite
* PHPStan Level 7 for more DAO PDO With new function to address common type and check problems * A bit more * PHPStan Level 7 for FreshRSS_Entry
2023-04-17Complete PHPStan Level 6 (#5305)Gravatar Alexandre Alapetite
* Complete PHPStan Level 6 Fix https://github.com/FreshRSS/FreshRSS/issues/4112 And initiate PHPStan Level 7 * PHPStan Level 6 for tests * Use phpstan/phpstan-phpunit * Update to PHPStan version 1.10 * Fix mixed bug * Fix mixed return bug * Fix paginator bug * Fix FreshRSS_UserConfiguration * A couple more Minz_Configuration bug fixes * A few trivial PHPStan Level 7 fixes * A few more simple PHPStan Level 7 * More files passing PHPStan Level 7 Add interface to replace removed class from https://github.com/FreshRSS/FreshRSS/pull/5251 * A few more PHPStan Level 7 preparations * A few last details
2023-03-31PHPStan level 6 for all PDO and Exception classes (#5239)Gravatar Alexandre Alapetite
* PHPStan level 6 for all PDO and Exception classes Contributes to https://github.com/FreshRSS/FreshRSS/issues/4112 * Fix type * Now also our remaining own librairies * Motivation for a few more files * A few more DAO classes * Last interface
2022-06-19Update all test dependencies (#4419)Gravatar Alexandre Alapetite
* Update all test dependencies * Remove old false-positive * Minor update lock files * Increase PHPStan memory for Fedora https://github.com/FreshRSS/FreshRSS/pull/4400#issuecomment-1159514197 * Require PHP8+ for tests Due to small changes of signature in `ob_implicit_flush` and `simplexml_load_string`, cf. https://github.com/FreshRSS/FreshRSS/pull/4123 * Missing lint in CSS files
2022-02-05Draft:Add interface and add typehinting (#4130)Gravatar Luc SANCHEZ
* Add interface and add typehinting * Simplify and complete * inheritdoc Co-authored-by: Luc SANCHEZ <l.sanchez-ext@ubitransport.com> Co-authored-by: Alexandre Alapetite <alexandre@alapetite.fr>
2021-12-31Add PHPStan (#4021)Gravatar Alexandre Alapetite
* Add PHPStan #fix https://github.com/FreshRSS/FreshRSS/issues/4016 https://phpstan.org/ ```sh composer run-script phpstan ``` * More fixes * Fix global variables * Add .phtml * Fix merge https://github.com/FreshRSS/FreshRSS/pull/4090 * Fix more warnings * Fix view errors and enable in CI * ReturnTypeWillChange * Dynamic view type * Fix Minz static/self bug
2021-03-24Fix TT-RSS import (#3553)Gravatar Alexandre Alapetite
2021-03-23Check pdo_sqlite for export/import SQLite (#3545)Gravatar Alexandre Alapetite
2020-12-29Add a file for each PDO class (#3301)Gravatar Alexis Degrugillier
Before, we had 5 classes in the ModelPdo file. It was bad for 2 reasons. The first reason is that it is considered bad practice to have multiple class in one file. This is especially true when using autoloading. On top of that it is less readable considering the size of the file. The second reason is that so far we were lucky. Everytime we needed to access the database, it was through the ModelPdo class which loads all the other classes. If we want to access directly the connection, it wont be loaded. On top of that, the system is configured to work on a single database, but as we have every connection definition in a single file, all classes were loaded at the same time. Thus using memory and processing time for nothing. Now, we have a file for each class. To work with autoloading, classes were slightly renamed to match autoloading rules.
2020-12-28Revert "Add a file for each PDO class (#3297)"Gravatar Alexandre Alapetite
This reverts commit e1ee58816ba76734e4115fc12898b13de665b220.
2020-12-28Add a file for each PDO class (#3297)Gravatar Alexis Degrugillier
Before, we had 5 classes in the ModelPdo file. It was bad for 2 reasons. The first reason is that it is considered bad practice to have multiple class in one file. This is especially true when using autoloading. On top of that it is less readable considering the size of the file. The second reason is that so far we were lucky. Everytime we needed to access the database, it was through the ModelPdo class which loads all the other classes. If we want to access directly the connection, it wont be loaded. On top of that, the system is configured to work on a single database, but as we have every connection definition in a single file, all classes were loaded at the same time. Thus using memory and processing time for nothing. Now, we have a file for each class. To work with autoloading, classes were slightly renamed to match autoloading rules.
2020-10-06Minz allow parallel sessions (#3096)Gravatar Alexandre Alapetite
* Minz allow parallel sessions #fix https://github.com/FreshRSS/FreshRSS/issues/3093 * Array optimisation * Array optimisation missing * Reduce direct access to $_SESSION except in install process * Fix session start headers warning * Use cookie only the first time the session is started: `PHP Warning: session_start(): Cannot start session when headers already sent in /var/www/FreshRSS/lib/Minz/Session.php on line 39` * New concept of volatile session for API calls Optimisation: do not use cookies or local storage at all for API calls without a Web session Fix warning: ``` PHP Warning: session_destroy(): Trying to destroy uninitialized session in Unknown on line 0 ``` * Only call Minz_Session::init once in our index It was called twice (once indirectly via FreshRSS->init()) * Whitespace * Mutex for notifications Implement mutex for notifications https://github.com/FreshRSS/FreshRSS/pull/3208#discussion_r499509809 * Typo * Install script is not ready for using Minz_Session
2020-05-29Fix PostgreSQL database auto-create with limited rights (#3013)Gravatar Alexandre Alapetite
* Fix PostgreSQL database auto-create with limited rights #fix https://github.com/FreshRSS/FreshRSS/issues/3009 Install would fail if the user is not even allowed to connect to the default `postgres` database. * Confused by custom Minz_PDOConnectionException
2019-11-11Fix DB optimize for MySQL (#2647)Gravatar Alexandre Alapetite
`pdo->exec()` is not appropriate for MySQL `OPTIMIZE` because `OPTIMIZE` returns some data and not only a code and then fails.
2019-11-06More reset default category name (#2639)Gravatar Alexandre Alapetite
Improve https://github.com/FreshRSS/FreshRSS/pull/2638
2019-10-23Feature/new archiving (#2335)Gravatar Alexis Degrugillier
* Change archiving config page layout I've changed some wording and moved actions into a maintenance section. * Update purge action Now we have more control on the purge action. The configuration allows us to choose what to keep and what to discard in a more precise way. At the moment, the configuration applies for all feeds. * Add purge configuration on feed level Now the extend purge configuration is available on feed level. It is stored as attributes and will be used in the purge action. * Update purge action Now the purge action uses the feed configuration if it exists and defaults on user configuration if not. * Add empty option in period list * Fix configuration warnings * Add archiving configuration on categories See #2369 * Add user info back * Add explanations in UI * Fixes for SQLite + error + misc. * Fix invalid feed reference * Short array syntax Only for new code, so far * Fix prefix error * Query performance, default values Work in progress * Fix default values and confirm before leaving Form cancel and confirm changes before leaving were broken. And start taking advantage of the short echo syntax `<?= ?>` as we have moved to PHP 5.4+ * More work * Tuning SQL * Fix MariaDB + performance issue * SQL performance * Fix SQLite bug * Fix some attributes JSON encoding bugs Especially for SQLite export/import * More uniform, fix bugs More uniform between global, category, feed settings * Drop special cases for old articles during refresh Instead will use lastSeen date with the new archiving logic. This was generating problems anyway https://github.com/FreshRSS/FreshRSS/issues/2154 * Draft drop index keep_history Not needed anymore * MySQL typo Now properly tested with MySQL, PostgreSQL, SQLite * More work for legacy values Important to avoid overriding user's preference and risking deleting data erroneously * Fix PHP 7.3 / 7.4 warnings @aledeg "Trying to use values of type null, bool, int, float or resource as an array (such as $null["key"]) will now generate a notice. " https://php.net/migration74.incompatible * Reintroduce min articles and take care of legacy parameters * A few changes forgotten * Draft of migration + DROP of feed.keep_history * Fix several errors And give up using const for SQL to allow multiple database types (and we cannot redefine a const) * Add keep_min to categories + factorise archiving logic * Legacy fix * Fix bug yield from * Minor: Use JSON_UNESCAPED_SLASHE for attributes And make more uniform * Fix sign and missing variable * Fine tune the logic
2019-10-05MySQL entry content MEDIUMBLOB (#2551)Gravatar Alexandre Alapetite
Fix https://github.com/FreshRSS/FreshRSS/issues/2448
2019-09-29PDO refactoring for code simplification (#2522)Gravatar Alexandre Alapetite
* PDO refactor * Automatic prefix when using the syntax `_tableName` * Uniformity: MySQL is now PDO::ATTR_EMULATE_PREPARES = false just like SQLite and PostgreSQL, with consequences such as only one statement per query * Use PDO methods exec(), query(), prepare() + execute() in a more efficient way * Remove auto-update SQL code for versions older than FreshRSS 1.5 (3 years old) * The name of the default category is set in PHP instead of in the DB (simplies SQL and allows changing the name according to the FreshRSS language) * Rename `->bd` to `->pdo` (less of a frenshism, and more informative) * Fix some requests, which were not compatible with MySQL prepared statements * Whitespace * Fix syntax for PostgreSQL sequences + MySQL install * Minor formatting * Fix lastInsertId for PostgreSQL * Use PHP 5.6+ const Take advantage of https://github.com/FreshRSS/FreshRSS/pull/2527 https://www.php.net/manual/en/migration56.new-features.php * A bit of forgotten PHP 5.6 simplification for cURL * Forgotten $s * Mini fix custom user config https://github.com/FreshRSS/FreshRSS/pull/2490/files#r326290346 * More work on install.php but not finished * install.php working * More cleaning of PDO in install * Even more simplification Take advantage of PDO->exec() to run multiple statements * Disallow changing the name of the default category https://github.com/FreshRSS/FreshRSS/pull/2522#discussion_r326967724
2019-09-15CLI to export/import any database to/from SQLite (#2496)Gravatar Alexandre Alapetite
* CLI to export/import any database to/from SQLite Require PHP 5.5+ https://github.com/FreshRSS/FreshRSS/pull/2495 * Travis * Execution rights * Fix wrong static fields * Fix MySQL bad default buffering https://stackoverflow.com/questions/6895098/pdo-mysql-memory-consumption-with-large-result-set/6935271#6935271 https://php.net/manual/ref.pdo-mysql * Fix count on progression * Avoid static DB information To ease working with two DBs at the same time * Less static, simplify Needs some testing * Small corrections * Special case for SQLite to SQLite * Modify special case for SQLite * Remove special case for SQLite More uniform logic for the 3 databases. Fix wrong DROP TABLE for SQLite. * Drop indexes * Revert "Drop indexes" This reverts commit f28d2bae0935745c1c74ea38f2ee083f3fd4bf9d. * Fix deletion * Fix classic export * Update cli/README.md Co-Authored-By: Marien Fressinaud <dev@marienfressinaud.fr> * Addressing part of review * Remove goto :cry: * Travis * Comment for SQLite case * Fix missing fields when inserting
2018-10-26Introduce a routine for minor DB maintenance (#2080)Gravatar Alexandre Alapetite
generated by cgit v1.2.3 (git 2.39.1) at 2026-02-10 09:32:35 +0000