Frontend BETA and Database optimizations - Change log 5.4 build 1628

Hello! This update brings some great database optimizations, beginning of Frontend BETA (Opt-in), as well as small fixes. Read more below
Special thanks to Kopo, Pingger & KasperiP for contributions to this update.

Change log
Database optimizations (Schema changes)
A lot of work was put into analyzing bottlenecks in queries and slow query logs sent in by users.

Benchmarks using 17k players are promising, as /players page now loads in 5s vs 2+min before.

Database patches will apply on first enable of the update to modify the database schema. This can take up to 4 minutes. Networks: Only enable one Plan server and wait for it to patch database before starting other servers.

• Changed database schema to optimize query speed and table size
  
• If you're using these columns via Query API you can replace your use case:
  • If you need uuid in ResultSet, use SELECT u.uuid ... JOIN plan_users u on u.id=<table>.user_id
  • If you have uuid or server uuid in WHERE, use server_id=(SELECT id FROM plan_servers WHERE uuid='<server uuid>')
• Some queries that were identified as slow were optimized to perform better by changing the query
  • Geolocations queries
  • Ping country table query
  • Player table query
  • Other queries are also sped up by the uuid -> user_id and server_uuid -> server_id changes, as comparing integers is faster than Strings.
• Extension data insert was made faster by keeping icon ids in memory for short duration
• MySQL insert performance may be improved since auto-commit was turned off
• Query page queries now use user ids instead of uuids - this fixes 'Max SQL size exceeded' issues.
• Added a new index to plan_ping table to speed up ping queries.
• Removed redundant uuid indexes that are now replaced with foreign key indexes for user_id and server_id, which reduces database size.

Frontend BETA (Opt-in)
Frontend is being rewritten in React one page at a time. This is to have more control over what is displayed on the website, since a lot of requested features will require more interaction or hiding features partially. - While doable with vanilla javascript, code would quickly become unmaintainable.

The goal is to get feature parity with the current frontend implementation by rewriting one page at a time and then switch over completely.

Some features will not use React frontend until everything is complete.

• To Opt-in set Plugin.Frontend_BETA to true
• Player page has been rewritten in React, other pages still use previous implementation.
• Html Customization is currently incompatible with the beta and is disabled automatically.
• Export is currently incompatible with the beta and old pages continue to be exported even if beta is enabled.
• Automated browser testing was improved a lot and all pages are now tested for basic javascript errors.

Data gathering

• Fixed AFK time not being gathered on Fabric, the movement detector was incorrectly firing every time a player position packet arrived (Happens once per second).

Config

• Default Player head image address was changed from cravatar to crafatar. Remove the whole row from the config and reload Plan if you wish to use the new setting.

Locale
Kopo did a massive locale system improvement in order to support translations on the frontend side, implementing yml conversion, namespacing all of the existing locale keys and writing json endpoints for frontend to load locale - as well as the code responsible for loading those using i18next.

• Locale files now use .yml format. Any locale files you may be using are converted automatically.
• The new React Frontend allows changing language on the frontend on a per-user basis with a dropdown menu - This is currently only available on the rewritten pages (player page)
  
• KasperiP fixed a typo in Finnish locale

Export

• Players page is now exported more frequently
• Exported pages should no longer show the Logout button

Extensions

• Essentials Economy was split from Essentials Extension. Now there are 2 extensions managed separately. This was done since the economy calls were triggering updates too frequently. If you have disabled Essentials Extension you may need to disable EssentialsEco Extension separately
• Fixed NPE related to LuckPerms Extension
• Fixed SQL syntax error related to LuckPerms Extension
• Fixed issues with KingdomsX Extension when using newer version of KingdomsX (They changed names of some of their packages)
• Pingger added support for 1.18 stone blocks to LogBlock Extension

QoL changes

• /plan servers command now lists Plan version on each server so it is easier to keep track of servers being out of date.

Bugfixes

• Fixed negative register dates in player dat files breaking the database
• Fixed ConcurrentModificationException related to AFK tracker
• Prevented use of incorrectly formatted WorldAlias regex settings
• Fixed Plan failing to enable if database query took too long during enable.