Age | Commit message (Collapse) | Author | Files | Lines |
|
Decided to change the naming scheme of files in util/updates/
|
|
This keeps the function body and the trigger declaration closer to each
other, so it's easier to tell when the function will be called and which
cases it needs to handle. If you ignore errors from existing
trigger declarations, the triggers.sql script is idempotent and can be
used in from scripts.
|
|
|
|
|
|
To my surprise, I actually managed to achieve acceptable performance by
just adding two indices. I totally expected I'd have to keep a cache
column in ulist_vns whether the row is private or not. The partial index
on the users table in fact improves the performance of the vote graph
query. A covering index improves that even further, but that requires
Postgres 11+, which the Docker image doesn't have yet (and isn't all
that crucial anyway).
There's a rather annoying potential for confusion regarding the private
flag on votes. The user page & list stats only look at whether the
'Voted' label is private, whereas the VN stats use the "proper" approach
of checking for any public label. Not entirely sure which of the two is
more intuitive.
|
|
That'd make it much easier to find fake accounts.
|
|
This adds the users.c_vns and c_wish columns and a function to update
the cache.
Unlike my previous cache update approaches, I did not use SQL triggers
here, as that seemed more complex and less efficient than updating the
cache manually. That's not to say that I'm happy with the current
approach, but meh...
The cache update function is not automatically run for all users, but
that could be added to Multi::Maintenance if it turns out that the
cached values will not be updated properly in some cases.
|
|
These ensure that, for each row in rlists, all the linked VNs have an
entry in ulist_vns. This implementation should be more robust than
before; It now isn't possible to remove an item from ulist_vns without
also removing all relevant rows from rlists, and adding more VNs to an
existing release entry will ensure that those VNs are also added to
ulist_vns.
|
|
It turned out that naively changing the existing queries to refer to
ulist_vns with a (WHERE vote IS NOT NULL) made them ultra slow (like,
DNF-slow), for a reason I haven't been able to figure out yet. I had to
rewrite the queries to use CTEs and in doing so figured that I could get
an extra performance improvement by combining the popularity and rating
updates in one query. The combined update function now runs faster than
the old queries.
|
|
|
|
To directly query the new list feature. I'll migrate the old
vnlist/wishlist/votelist APIs later to query the 'ulist' tables.
|
|
changes
|
|
rlisted VNs
|
|
|
|
|
|
Very much experimental. I'm not sure this is even the right model.
|
|
|
|
|
|
|
|
They don't do anything yet.
|
|
Same thing as 65ed955890c7ee7250a2bce4467c8c092c1bbbbe, with the same
limitations (except this does take hiding/unhiding characters into
account).
|
|
This has some limitations:
- tags.c_items is not updated, so that may be out of sync.
(The UPDATE takes about 400ms, so doing that more regularly from
Multi::Maintenance should be a viable option)
- When the hidden flag of a VN is changed, the tags will also be out of
sync.
- I don't see a way to do fast incremental updates when tag entries
themselves are changed, e.g. to handle changes in the tag tree or
searchable flag
|
|
Most importantly, downvoted parent tags are now still included for
searching as per https://vndb.org/t12750 - in particular, a downvoted
tag is considered to be exactly the same thing as a tag with no votes at
all.
Also made a few other adjustments:
- Removed the "ignore child tags/traits if parent has its own value"
rule. This was done because the 'spoiler' rating would get
inherited using the average, but that should just have been the
minimum (i.e. if a single child tag is not a spoiler, then neither is
the parent). https://vndb.org/t10082
- The score of a parent tag is now avg(avg(votes_on_child_tags)) rather
than avg(votes_on_child_tags), i.e. tags with more votes don't have
more weight than tags with fewer votes. I don't think vote counts
really matter in determining the parent score.
- Deleted VNs now don't count towards tag statistics (this is consistent
with traits).
- Performance improvements: Smaller indices and more clever querying.
|
|
|
|
This ensures that the email address linked to a user is always valid and
actually belong(s|ed) to that user.
|
|
This improves the password reset workflow a bit.
The users.passwd field is now no longer used for reset tokens, meaning
that the current password isn't affected until the user actually clicks
the link and changes it.
It is now also possible to have multiple active password reset tokens,
in case one of the emails got lost. All existing tokens are invalidated
when the user finally changes their password.
Tokens are now valid for at most 1 week instead of indefinitely.
|
|
This will be helpful when adding other types of sessions with different
expiration.
|
|
And for those who (like me) already applied update_20191003.sql, here's
a partial fix:
alter table users alter column spoilers drop default;
alter table users alter column spoilers type smallint using (case when spoilers then 1 else 0 end);
alter table users alter column spoilers set default 0;
But you'll still need to import the old preference for users who had
spoilers=2, because those will have been lost.
|
|
This bloats the users table a little bit, but that's fine. The main
advantage of this change is that we now have a proper schema for user
preferences, rather than the schemaless key-value mess we had before.
This commit also splits the 'tags_cat' preference up into tags_cont,
tags_ero and tags_tech bools, as that's more compact to store and easier
to work with.
This commit also changes the 'notify_nodbedit' preference to
'notify_dbedit' with inverted meaning. The reason the value was negated
in the first place was because the old schemaless approach did not
support positive defaults.
|
|
Sorry for re-using update_20190923.sql, but I'm not sure anyone even
uses those scripts. For documentation purposes it's neat to have similar
things in a short time window combined, anyway.
|
|
|
|
https://vndb.org/t12897.6
|
|
Has the inverted meaning. Keeping track of how long we've been unable to
find a certain shop link is useful in weeding out temporary
unavailability when querying for dead links.
|
|
|
|
|
|
These have no value anymore at this point. And if they have, they'll
still be in the git history.
|
|
To handle https://vndb.org/t12755.37
|
|
|
|
|
|
|
|
The current affiliate links format doesn't display as much information
as before but it's also a lot less messy.
|
|
|
|
Most of this code isn't new - there's been a hidden 'Affiliates.pm'
module for a while. This just consolidates the code with the other shops
and opens up the code. The API endpoint is hidden. :)
|
|
|
|
|
|
|
|
|
|
|
|
It's redundant, and normalization is actually easier when removed.
|
|
|