- Rename `info_updated` -> `info_updates` to be consistent with `updates`.
- Relax triggers to only update `info_updates` instead of both updates
and info_updates, and fix some trigger statements that were updating
the wrong value.
- Add table for tracking (multiple) pinned messages.
- Trigger info_updates update when pinning/unpinning.
The old Room response included the room token as "id", which is quite
awkward, and not something we want to propagate into the future API, so
we can't just start returning the extra details if we don't want to
maintain that misnaming.
This changes the API so that existing (deprecated) endpoints get the
deprecated field, and the new Room deserialization is free to break
backwards compat.
Uploaded room images shouldn't expire; this makes the expiry column
nullable and sets their expiry to null, and adds a trigger to expire
replaced room images.
Also fixes a panic in file deletion that made it not actually delete
files.
- "Deleted" messages should be set to null, not actually deleted
- Add automatic updating of "edited" to the message update trigger
- Accomodate janky deprecated Session compact_poll logic that expects
deletion lists even if it doesn't ask for them.
- Add a useful view for (admin direct sqlite) message querying.
- Fix message_details VIEW query
Completely overhauls the underlying database design:
- referential integrity
- data normalization
- using triggers for various updates to reduce the amount of data logic
in code.
- indexes
- one single database rather than 1 + 1/room
- various structural improvements for useful features such as:
- individually and default-assignable read/write/upload
- time-based permission expiries (e.g. restrict this permission for
x amount of time)
- distinguishing between admins (who can add/remove moderators) and
moderators (who can only moderate but not control the moderator
list)
- global server moderators/admins
- global server bans
- single VIEW for simple querying of a user's effective permissions
(i.e. with database-side coalescing of global/local permissions).
- pinned message via foreign key
- room icon as a regular uploaded file
- per-file file expiries (so that admins/mods can upload non-expiry
files, such as the room icon, or pinned downloads).
- message history so that when messages get edited or deleted a log
is kept of the old value (the idea being that mods would be able
to look this up).
- movable messages (e.g. so there could be a "bad posts" room that
only moderators have access to).
This is obnoxious because it is *impossible* to get rid of the room
without it getting recreated at every restart.
Note: this breaks file server mode, but that's okay, the DB refactor
completely breaks it anyway, and current version will work fine for the
global file server.
This was allowing old (deleted) room databases to persist and even be
recreated if clients were still polling them. Worse, they would grow
forever because files and old tokens are only cleaned up for rooms
listed in the main db.
These make the code more complex and make the the queries harder to
read.
Tables in global variables is an antipattern that stemmed from old PHP
(or similar) web hosts that came with a single MySQL table requiring you
use different prefixes to load multiple instances in the same table.
Without mysql or prefix configurability it's an antipattern that just
results in less readable code.
Jason Rhinelander
sakuramboo
Sean Darcy
Kee Jefferys