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.
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
* 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
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 `/..`.
* PHPStan 2.0
fix https://github.com/FreshRSS/FreshRSS/issues/6989https://github.com/phpstan/phpstan/releases/tag/2.0.0https://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
* 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
* 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
* 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-ruleshttps://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
* 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>
* 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>
* 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
* 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
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.
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.
* 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
* 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
* 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
* 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/2527https://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
* 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#6935271https://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 f28d2bae09.
* Fix deletion
* Fix classic export
* Update cli/README.md
Co-Authored-By: Marien Fressinaud <dev@marienfressinaud.fr>
* Addressing part of review
* Remove goto 😢
* Travis
* Comment for SQLite case
* Fix missing fields when inserting
* MySQL GUID case sensitive
latin1_bin
https://github.com/FreshRSS/FreshRSS/issues/2077
* Prepare update for existing bases
* Perform DB update during actualize
* Reduce frequency slightly
* No optimize at the same time
* Take advantage of the SQL modifications in 1.12
* Move higher up
* Move to purge, which all users can manually call
* First draft of custom tags
https://github.com/FreshRSS/FreshRSS/issues/928https://github.com/FreshRSS/FreshRSS/issues/1367
* SMALLINT to BIGINT for id_entry
And uppercase SQL types
* Fix layout for unreads
* Start UI menu
* Change menu order
* Clean database helpers
https://github.com/FreshRSS/FreshRSS/pull/2027#discussion_r217971535
* Travis rules do not understand PostgreSQL constants
Grrr
* Tag controller + UI
* Add column attributes to tags
* Use only favicon for now, for label
* Fix styling for different themes
* Constant for maximum InnoDB index length in Unicode
https://github.com/FreshRSS/FreshRSS/pull/2027#discussion_r219052200
(I would have personnally prefered keeping the readability of a real
value instead of a constant, in this case of many SQL fields)
* Use FreshRSS_Factory::createCategoryDao
* Add view of all articles containing any tag
* Fix search in tags
* Mark as read tags
* Partial auto-update unread tags
* More auto update tag unreads
* Add tag deletion
* Do not purge tagged articles
* Minor comment
* Fix SQLite and UI bug
* Google Reader API support for user tags
Add SQL check that tag names must be distinct from category names
* whitespace
* Add missing API for EasyRSS
* Compatibility SQLite
Problematic parentheses
* Add SQL DISTINCT for cases with multiple tags
* Fix for PostgreSQL
PostgreSQL needs some additional type hint to avoid "could not determine
data type of parameter $1"
http://www.postgresql-archive.org/Could-not-determine-data-type-of-parameter-1-tp2171092p2171094.html
