summaryrefslogtreecommitdiff
path: root/util
AgeCommit message (Collapse)AuthorFilesLines
12 daysReleases: Add "official" flagYorhel1-0/+20
https://vndb.org/t15011
2020-11-10Add Persian languageYorhel1-0/+1
https://vndb.org/t950.821 Flag is based on https://commons.wikimedia.org/wiki/File:Farsi.svg with a light green brackground.
2020-11-09imageflag: Add images.c_uids cache + remove tablesample on few imagesYorhel1-0/+5
This fixes spurious "No more images to vote on!" messages when the user has already voted on almost all images and the tablesample doesn't catch the few images that they hadn't voted on yet. Since doing such a select without tablesample is *much* slower, this needs a more efficient way of finding images the user hadn't voted on yet, hence the c_uids cache. Still pretty inefficient - it needs a full table scan - but it'll have to do.
2020-10-15reviews: Allow anonymous voting + display overrule countingYorhel1-0/+4
Anonymous votes count for 0.01, regular votes for 1 and overrule votes for 1000. It's a bit crude, but ought to do the job. Reason for this change is that most reviews don't seem to get any votes at all. With this anonymous voting we can do some sort of scoring/ordering even with potentially less reliable votes.
2020-10-13notifications: Add subscribing for (un)applications of traitsYorhel1-0/+3
2020-10-08notifications: Add subscription management UI + finalize DB migrationYorhel1-0/+0
Seems to be working so far. I'll find the actual bugs in production.
2020-10-07notifications: Mark as read when opening page + delete when deleting ↵Yorhel1-0/+13
posts/reviews The mark as read functionality was already present in some form for threads, but is now made consistent among all notification types. This removes the need for the redirect from the notification listing. The deletion of notifications is intended to avoid pointless notifications, especially in case of spam.
2020-10-07notifications: Add backend for subscriptions and new notification typesYorhel1-0/+15
+ (un)subscription for new posts/edits on individual threads, reviews and DB entries. + Subscribing to VNs to get notifications for new reviews. + The users.notify_posts setting now also includes comments on reviews the user has commented on. While this is all very nice and such, it's just the data model and backend. The hard part is the user interface.
2020-10-05notifications: Consolidate and simplify creation of notificationsYorhel1-0/+17
Preparing to extend the notification types, but with the old individual-trigger-for-each-type that would easily turn into an unmanagaeble mess. The new approach also correctly handles events that may trigger multiple notification types. Also removed the title and uid cache from the notifications table, they don't simplify things and aren't necessary for performance. On the other hand, doing a generic information lookup for vndbids is kind of annoying. I now wrote an item_type() function to help with that, but it's not sufficient to solve the broader problem. One regression(?): Users will keep getting notifications for new posts on threads even if they haven't read older notifications for the same thread yet. I /think/ this behavior is more intuitive and expected, so maybe I'll keep it. (And possibly more regressions as well, who knows...)
2020-09-20Reviews: Add mod option to lock reviews for commentsYorhel1-0/+1
2020-09-05Notifications: Add notifications for thread posts and review commentsYorhel1-0/+6
2020-09-05Notifications: Modernize SQL schemaYorhel1-0/+10
By removing the ltype, using the vndbid type, renaming 'subid' to 'num' (as used elsewhere) and removing the reference to uid=0.
2020-09-03Reviews: Hide downvoted reviews & allow moderator overrulingYorhel1-0/+5
Seems like a better moderation tool than outright deleting reviews.
2020-09-02BBCode: Consolidate bb2html & bb2text + only convert ids in thread titlesYorhel1-24/+32
Fixing bb2html to only convert ids would complicate options a lot, adding a new formatting function to only convert ids would make sense, but then all formatting functions kind of look alike, so I figured a single bb_format() to support all use cases may be a better approach. Trigger for this was that people do (understandably) put [spoiler] in thread titles, and that should not be interpreted as the spoiler tag.
2020-08-31BBCode: Add support for w+ and w+.+ dblinksYorhel1-6/+6
2020-08-31unusedimages.pl: Also scan reviews and commentsYorhel1-0/+2
2020-08-25SQL/reviews: Two rather stupid fixups on reviews_postsYorhel1-0/+5
2020-08-24reviews: Get rid of summary field & split mini/full reviewsYorhel1-0/+6
Much to my disappointment, people don't write proper summaries and, as such, summaries are not useful to be considered a "short review" on their own right. This necessitates splitting the reviews onto different pages.
2020-08-19reviews: Cache vote & post statsYorhel1-0/+14
Not strictly necessary for the current use, but I plan to add some browsing features and those really will need it.
2020-08-17reviews: Make reviews mod-only for now to allow some beta testingYorhel1-3/+2
It's totally not finished yet, but it's useful to gather some feedback before I start with integration with the rest of the site.
2020-08-16Reports: Get rid of report_type, split vndbid/num + allow reporting reviews ↵Yorhel1-0/+7
and comments rtype is not necessary, the DB identifier is sufficient. The separate type column was supposed to simplify DB lookups (but that's all consolidated in a single function anyway, so it didn't help much) and easy filtering depending on what mods have access to (but there is no such filter, and even with vndbids that should be easy enough). Converting the object ID into a vndbid + num should make it easier to correlate reports if necessary.
2020-08-16reviews: Fix deleting users who have posted a reviewYorhel1-1/+1
2020-08-14reviews: Add schema & display for commentsYorhel1-0/+13
Not really sure how to integrate and handle this, to be honest. I'll just play around and see what works.
2020-08-07reviews: Add up/down voting on reviewsYorhel1-2/+3
2020-08-07reviews: Add initial SQL schema + "review" permission flagYorhel1-0/+51
The boardmod flag will also determine the review moderation permissions.
2020-08-07SQL: Sync production DB schema with the one in gitYorhel1-0/+14
Found by comparing the output of pg_dump --schema-only. There were a few minor differences, nothing that would cause any issues. The different credit_type definition in prod was odd, though. Most names of the primary key constraint are different, too, but I'll not bother with renaming those for now - it's not likely to cause issues anytime soon. Interestingly, pg_dump output for the threads_id_seq and threads.id DEFAULT is pretty different, though the only real difference that I could see was the different OWNER of threads_id_seq. Not likely to cause problems, anyway.
2020-08-07Discussions: Fix handling of user deletionYorhel1-0/+3
Which I broke by removing the DEFAULT clause on threads_posts.uid. That column still used the old uid=0 for deleted users rather than the uid=NULL that I was planning to migrate the entire DB to. So while I'm fixing up the threads schema, may as well update this too. There's still a bunch of columns relying on uid=0, but I can fix that later.
2020-08-06Discussions: Allow discontinuous post numbers + ignore hidden posts in ↵Yorhel1-0/+13
counts/lastpost This solves a few problems: - 'hidden' posts will no longer cause the thread to be bumped to the front page. - Deleting posts will no longer cause other posts to be renumbered (and hence will not break existing links to posts) - Numbers of deleted posts will no longer be re-used (except when they were the last post in the thread - fixing this would require an additional column in 'threads', but it didn't seem worth the trouble)
2020-08-06SQL: Use vndbid for thread identifiersYorhel2-1/+31
Also drop the thread_poll_votes.tid column, as it's not really necessary. And add ON DELETE CASCADEs to threads_posts and threads_boards to simplify thread deletion. Relatively invasive change, prolly broke something.
2020-07-30tracing: Keep track of whether the page transaction has a txidYorhel1-2/+3
This seems to be the most reliable measurement of "did this page modify the database in any way?", allowing me to get a better feel of the ratio between read-only and read-write pages.
2020-07-29Reports: Keep track of unseen, new and last updated reportsYorhel1-0/+5
2020-07-25Reports: Allow reporting DB entriesYorhel1-0/+2
2020-07-23Add new reporting featureYorhel1-0/+19
So that users can bring up problems to the moderators. Only used for forum posts for now, but I intend to extend it to other parts of the site as well.
2020-07-22BBCode: Add support for [b], [i], [s], and [u] formatting codesYorhel1-0/+16
2020-06-24Add revision-integrity.pl script to test DB consistencyYorhel1-0/+39
Or at least, one aspect of it. The script did not find a single problem on the production DB.
2020-06-15Releases: Allow custom resolutions to be enteredYorhel2-19/+39
The resolution field now works much like the engine field.
2020-06-13Chars: Add support for spoiler sex fieldYorhel1-0/+4
2020-06-04Cache VN popularity & rating rankingsYorhel1-0/+4
Should shave off about 10-30ms of the VN page generation times.
2020-05-28Add sampling-based trace loggingYorhel1-0/+43
This is intended to get some stats about resource usage (which pages/modules are requested often, how much time do they take, etc) to reduce server load and find pages to optimize, and to track usage of Elm modules in order to see how many pageviews actually need them so that I can (hopefully) create smaller JS bundles for most pages. The logging *should* not include any personally identifiable information, but paths and query strings could still reveal more information than necessary. Trace logging is intended to only be enabled for short periods of time or with low sample rates in order to avoid slowing down the server with too much logging. I also don't intend to keep the data around for longer than necessary. I'm not providing SQL migration scripts for these, this feature is going to require custom querying anyway.
2020-05-26Merge branch 'charedit'Yorhel1-1/+1
2020-05-15unusedimages.pl: Fix silly SQL bugYorhel1-1/+2
2020-05-15unusedimages.pl: Keep *all* referenced images in the DB + don't delete ↵Yorhel1-75/+78
recent uploads As a result, every image mentioned in the DB should now have an actual image file and every image file should now have a row in the DB. I'm planning to run this script more often to clean up after the new (coming) image upload forms - which may leave some unreferenced files - and to prevent VNDB from being abused as an image hosting service.
2020-05-13Chars::Edit: Add image editingYorhel1-1/+1
2020-05-11imgflag: Add sexual/violence user preferences + show/hide character imagesYorhel1-0/+4
2020-04-27Add minimal audit logging for user-related changesYorhel1-0/+11
Includes failed logins (but not through the API...), password changes, email changes, any user-related changes performed by a moderator and post edits/deletions performed by a moderator.
2020-04-26imgflag: Add separate "imgmod" permission flagYorhel1-0/+3
2020-04-25imgflag: Add moderator vote overrulingYorhel1-0/+4
Works exactly like tag vote overruling.
2020-04-16imgflag: Fix uid integrity + properly handle user account deletionYorhel1-0/+4
I made sure that the image_votes.uid column could be NULL, but then forgot to add the foreign key reference to make this machinery work. I've split the image_votes_cache trigger in two parts as a performance improvement - there's no need to update the cache then the uid or date changes. Otherwise deleting a user account with many image votes may take a few minutes.
2020-04-15devdump.pl: Uh, yeah, true and false arent integersYorhel1-1/+1
2020-04-15SQL: Split "perm" column into multiple booleansYorhel3-11/+35
This simplifies several actions and makes permission checks more obvious. This is also yack shaving for another feature I've been planning to add: boardmods should be able to set other users' "board" permission, tagmods for tags, etc. So that partial user bans don't need the full "usermod" permission.